1 PRAGMA foreign_keys=ON;
    2 
    3 -- Controlling organisation.
    4 CREATE TABLE company (
    5 	-- Name of the organisation.
    6 	name TEXT NOT NULL,
    7 	id INTEGER PRIMARY KEY,
    8 	-- Simply a check for null values.
    9 	somenum INTEGER
   10 );
   11 
   12 -- A regular user.
   13 CREATE TABLE user (
   14 	-- A foreign key reference.
   15 	cid INTEGER NOT NULL,
   16 	-- User's birth sex.
   17 	sex INTEGER NOT NULL,
   18 	-- Password hash.
   19 	--     This is passed to inserts and updates as a password,
   20 	--     then hashed within the implementation and extracted
   21 	--     (in listings and searches) as the hash value.
   22 	hash TEXT NOT NULL,
   23 	-- Unique e-mail address.
   24 	email TEXT NOT NULL,
   25 	-- A PNG image or something.
   26 	image BLOB,
   27 	-- User's full name.
   28 	name TEXT NOT NULL,
   29 	uid INTEGER PRIMARY KEY,
   30 	FOREIGN KEY(cid) REFERENCES company(id) ON DELETE CASCADE
   31 );
   32 
   33 CREATE UNIQUE INDEX unique_user__email ON user(email);
   34 
   35 -- Authenticated session.
   36 CREATE TABLE session (
   37 	-- Associated user.
   38 	userid INTEGER NOT NULL,
   39 	-- Random cookie.
   40 	token INTEGER NOT NULL,
   41 	-- (Stored as a UNIX epoch value.)
   42 	mtime INTEGER NOT NULL,
   43 	id INTEGER PRIMARY KEY,
   44 	FOREIGN KEY(userid) REFERENCES user(uid)
   45 );
   46