CREATE TABLE IF NOT EXISTS rgroup ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE(name) ); CREATE TRIGGER IF NOT EXISTS rgroup_upd_trig AFTER UPDATE ON rgroup BEGIN update rgroup SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; INSERT INTO rgroup (name) VALUES ('public'), ('logged_in'); CREATE TABLE IF NOT EXISTS user ( id INTEGER PRIMARY KEY, username TEXT NOT NULL, password TEXT NOT NULL, salt TEXT NOT NULL, rgroup_id INTEGER REFERENCES rgroup(id), created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE(username) ); CREATE TRIGGER IF NOT EXISTS user_upd_trig AFTER UPDATE ON user BEGIN update user SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; CREATE TABLE IF NOT EXISTS user_recipe_for_later ( id INTEGER PRIMARY KEY, preview TEXT NOT NULL, recipe_id INTEGER NOT NULL REFERENCES recipe(id) ON DELETE CASCADE, user_id INTEGER NOT NULL REFERENCES user(id) ON DELETE CASCADE, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE(recipe_id, user_id) ); CREATE TRIGGER IF NOT EXISTS user_recipe_for_later_upd_trig AFTER UPDATE ON user_recipe_for_later BEGIN update user_recipe_for_later SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; CREATE TABLE IF NOT EXISTS user_later_notes ( id INTEGER PRIMARY KEY, notes TEXT NOT NULL, user_id INTEGER REFERENCES user(id) ON DELETE CASCADE, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE(user_id) ); CREATE TRIGGER IF NOT EXISTS user_later_notes_upd_trig AFTER UPDATE ON user_later_notes BEGIN update user_later_notes SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; CREATE TABLE IF NOT EXISTS session ( id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES user(id), token TEXT NOT NULL, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, expires TIMESTAMP NOT NULL ); CREATE TRIGGER IF NOT EXISTS session_upd_trig AFTER UPDATE ON session BEGIN update session SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; CREATE TABLE IF NOT EXISTS role ( id INTEGER PRIMARY KEY, role VARCHAR(256) NOT NULL, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE(role) ); CREATE TRIGGER IF NOT EXISTS role_upd_trig AFTER UPDATE ON role BEGIN update role SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; -- e.g. everyone has anonymous role, even those without an account INSERT INTO role (role) VALUES ('ANONYMOUS') ON CONFLICT DO NOTHING; INSERT INTO role (role) VALUES ('USER') ON CONFLICT DO NOTHING; INSERT INTO role (role) VALUES ('ADMIN') ON CONFLICT DO NOTHING; CREATE TABLE IF NOT EXISTS user_role ( id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES user(id) ON UPDATE CASCADE ON DELETE CASCADE, role_id INTEGER NOT NULL REFERENCES role(id) ON UPDATE CASCADE ON DELETE CASCADE, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE(user_id, role_id) ); CREATE TRIGGER IF NOT EXISTS user_role_upd_trig AFTER UPDATE ON user_role BEGIN update user_role SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; CREATE TABLE IF NOT EXISTS recipe ( id INTEGER PRIMARY KEY, title TEXT NOT NULL, user_id INTEGER NOT NULL REFERENCES user(id) ON UPDATE CASCADE ON DELETE CASCADE, ingredients TEXT NOT NULL, instructions TEXT NOT NULL, by TEXT, source TEXT, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE(title, user_id) ); CREATE TRIGGER IF NOT EXISTS recipe_upd_trig AFTER UPDATE ON recipe BEGIN update recipe SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id; END; CREATE VIRTUAL TABLE IF NOT EXISTS recipe_idx_fts5 USING fts5( id UNINDEXED, title, user_id UNINDEXED, ingredients, instructions, by, source, content='recipe', content_rowid='id', tokenize='trigram' ); -- Triggers to keep the FTS index up to date. CREATE TRIGGER IF NOT EXISTS recipe_ai AFTER INSERT ON recipe BEGIN INSERT INTO recipe_idx_fts5(rowid, title, ingredients, instructions, by, source) VALUES (new.id, new.title, new.ingredients, new.instructions, new.by, new.source); END; CREATE TRIGGER IF NOT EXISTS recipe_ad AFTER DELETE ON recipe BEGIN INSERT INTO recipe_idx_fts5(recipe_idx_fts5, rowid, title, ingredients, instructions, by, source) VALUES ('delete', old.id, old.title, old.ingredients, old.instructions, old.by, old.source); END; CREATE TRIGGER IF NOT EXISTS recipe_au AFTER UPDATE ON recipe BEGIN INSERT INTO recipe_idx_fts5(recipe_idx_fts5, rowid, title, ingredients, instructions, by, source) VALUES ('delete', old.id, old.title, old.ingredients, old.instructions, old.by, old.source); INSERT INTO recipe_idx_fts5(rowid, title, ingredients, instructions, by, source) VALUES (new.id, new.title, new.ingredients, new.instructions, new.by, new.source); END; CREATE TABLE IF NOT EXISTS user_rgroup ( id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES user(id) ON UPDATE CASCADE ON DELETE CASCADE, rgroup_id INTEGER NOT NULL REFERENCES rgroup(id) ON UPDATE CASCADE ON DELETE CASCADE, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE(user_id, rgroup_id) ); CREATE TABLE IF NOT EXISTS recipe_rgroup ( id INTEGER PRIMARY KEY, recipe_id INTEGER NOT NULL REFERENCES recipe(id) ON UPDATE CASCADE ON DELETE CASCADE, rgroup_id INTEGER NOT NULL REFERENCES rgroup(id) ON UPDATE CASCADE ON DELETE CASCADE, created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE(recipe_id, rgroup_id) );