sqlite_schema

CREATE TABLE sensors(
id integer primary key AUTOINCREMENT,
sn numeric unique,
note varchar
);
CREATE TABLE sqlite_sequence(name,seq);
CREATE TABLE data(
id integer primary key AUTOINCREMENT,
sn numeric,
ts numeric,
pq varchar, -- physical quantity
value varchar,
systemts DATETIME DEFAULT (strftime('%s','now'))
);
CREATE TABLE input (
id integer primary key AUTOINCREMENT,
sn varchar,
variable varchar,
value numeric,
type varchar
);
CREATE INDEX tsindex ON data (sn, pq, systemts);
CREATE INDEX stsindex ON data (systemts);
CREATE INDEX pqindex ON data (pq);
CREATE INDEX snindex ON data (sn);
CREATE TABLE session (
acc varchar,
sessid varchar primary key,
username varchar
);

create table house (
id integer primary key AUTOINCREMENT,
name varchar,
address varchar,
location varchar,
);

create table room (
id integer primary key AUTOINCREMENT,
house numeric,
name varchar
);

create table devices (
id integer primary key AUTOINCREMENT,
name varchar,
type varchar,
image varchar,
room integer,
note varchar
);