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)
);