litejs/app/db/0002-hello.sql

CREATE TABLE device (
	id      INTEGER PRIMARY KEY,
	ua      INTEGER,
	hash    BLOB UNIQUE
);

CREATE TABLE device_ua (
	id      INTEGER PRIMARY KEY,
	text    TEXT
);

CREATE TABLE session (
	id      INTEGER PRIMARY KEY,
	device  INTEGER NOT NULL REFERENCES device(id) ON UPDATE CASCADE,
	ip      BLOB
);

CREATE TABLE window (
	id      INTEGER PRIMARY KEY AUTOINCREMENT,
	created INTEGER,
	session INTEGER NOT NULL REFERENCES session(id) ON UPDATE CASCADE,
	deleted INTEGER default NULL,
	opener  BLOB
);

CREATE INDEX window_s ON window(session) WHERE deleted IS NULL;

CREATE TABLE login (
	id      INTEGER PRIMARY KEY,
	created INTEGER,
	deleted INTEGER,
	window  INTEGER,
	type    INTEGER,
	user    INTEGER
);

CREATE INDEX login_w ON login(window) WHERE deleted IS NULL;

CREATE TABLE login_type (
	id      INTEGER PRIMARY KEY,
	text    TEXT
);

CREATE TABLE window_log (
	id      INTEGER PRIMARY KEY,
	time    INTEGER default (CAST(((julianday('now') - 2440587.5)*86400000) as INTEGER)),
	window  INTEGER,
	event   BLOB,
	data    BLOB
);

CREATE TABLE remember (
	device  INTEGER NOT NULL REFERENCES device(id) ON UPDATE CASCADE,
	user    INTEGER NOT NULL,
	login   INTEGER NOT NULL REFERENCES login(id) ON UPDATE CASCADE,
	PRIMARY KEY(device, user)
);

CREATE TABLE invite (
	id      INTEGER PRIMARY KEY,
	created INTEGER,
	hash    BLOB UNIQUE,
	role    INTEGER
);

CREATE TABLE recover (
	id      INTEGER PRIMARY KEY,
	created INTEGER default (CAST(((julianday('now') - 2440587.5)*86400000) as INTEGER)),
	hash    BLOB UNIQUE,
	user    INTEGER
);

-- Down

DROP TABLE device;
DROP TABLE session;
DROP TABLE window;
DROP TABLE window_log;
DROP TABLE login;
DROP TABLE login_type;
DROP TABLE invite;
DROP TABLE recover;

DROP INDEX window_s;
DROP INDEX login_w;