CREATE TABLE [Version] (
[id] INTEGER PRIMARY KEY,
[version] INTEGER NOT NULL UNIQUE,
- [datetime] DATETIME
-);
+ [datetime] TEXT
+) STRICT;
CREATE TABLE [User] (
[id] INTEGER PRIMARY KEY,
[email] TEXT NOT NULL,
- [name] TEXT,
+ [name] TEXT NOT NULL DEFAULT '',
[default_servings] INTEGER DEFAULT 4,
[password] TEXT NOT NULL, -- argon2(password_plain, salt).
- [creation_datetime] DATETIME NOT NULL, -- Updated when the validation email is sent.
+ [creation_datetime] TEXT NOT NULL, -- Updated when the validation email is sent.
[validation_token] TEXT, -- If not null then the user has not validated his account yet.
[is_admin] INTEGER NOT NULL DEFAULT FALSE
-);
+) STRICT;
-CREATE UNIQUE INDEX [User_email_index] ON [User] ([email]);
+CREATE UNIQUE INDEX [User_email_index] ON [User]([email]);
CREATE TABLE [UserLoginToken] (
[id] INTEGER PRIMARY KEY,
[user_id] INTEGER NOT NULL,
- [last_login_datetime] DATETIME,
- [token] TEXT NOT NULL, -- 24 alphanumeric character token. Can be stored in a cookie to be able to authenticate without a password.
+ [last_login_datetime] TEXT,
+
+ -- 24 alphanumeric character token.
+ -- Can be stored in a cookie to be able to authenticate without a password.
+ [token] TEXT NOT NULL,
[ip] TEXT, -- Can be ipv4 or ipv6
[user_agent] TEXT,
FOREIGN KEY([user_id]) REFERENCES [User]([id]) ON DELETE CASCADE
-);
+) STRICT;
-CREATE INDEX [UserLoginToken_token_index] ON [UserLoginToken] ([token]);
+CREATE INDEX [UserLoginToken_token_index] ON [UserLoginToken]([token]);
CREATE TABLE [Recipe] (
[id] INTEGER PRIMARY KEY,
[user_id] INTEGER, -- Can be null if a user is deleted.
[title] TEXT NOT NULL,
[estimate_time] INTEGER,
- [description] TEXT,
+ [description] TEXT NOT NULL DEFAULT '',
+ [difficulty] INTEGER NOT NULL DEFAULT 0,
[servings] INTEGER DEFAULT 4,
+ [is_published] INTEGER NOT NULL DEFAULT FALSE,
FOREIGN KEY([user_id]) REFERENCES [User]([id]) ON DELETE SET NULL
-);
+) STRICT;
+
+CREATE TABLE [Image] (
+ [Id] INTEGER PRIMARY KEY,
+ [recipe_id] INTEGER NOT NULL,
+ [name] TEXT NOT NULL DEFAULT '',
+ [description] TEXT NOT NULL DEFAULT '',
+ [image] BLOB,
+
+ FOREIGN KEY([recipe_id]) REFERENCES [Recipe]([id]) ON DELETE CASCADE
+) STRICT;
+
+CREATE TABLE [RecipeTag] (
+ [id] INTEGER PRIMARY KEY,
-CREATE TABLE [Quantity] (
+ [recipe_id] INTEGER NOT NULL,
+ [tag_id] INTEGER NOT NULL,
+
+ FOREIGN KEY([recipe_id]) REFERENCES [Recipe]([id]) ON DELETE CASCADE,
+ FOREIGN KEY([tag_id]) REFERENCES [Tag]([id]) ON DELETE CASCADE
+) STRICT;
+
+CREATE TABLE [Tag] (
[id] INTEGER PRIMARY KEY,
- [value] REAL,
- [unit] TEXT
-);
+ [recipe_tag_id] INTEGER,
+ [name] TEXT NOT NULL,
+
+ FOREIGN KEY([recipe_tag_id]) REFERENCES [RecipeTag]([id]) ON DELETE SET NULL
+) STRICT;
+
+CREATE UNIQUE INDEX [Tag_name_index] ON [Tag] ([name]);
CREATE TABLE [Ingredient] (
[id] INTEGER PRIMARY KEY,
[name] TEXT NOT NULL,
- [quantity_id] INTEGER,
- [input_step_id] INTEGER NOT NULL,
+ [quantity_value] REAL,
+ [quantity_unit] TEXT NOT NULL DEFAULT '',
+ [input_group_id] INTEGER NOT NULL,
- FOREIGN KEY([quantity_id]) REFERENCES Quantity([id]) ON DELETE CASCADE,
- FOREIGN KEY([input_step_id]) REFERENCES Step([id]) ON DELETE CASCADE
-);
+ FOREIGN KEY([input_group_id]) REFERENCES [Group]([id]) ON DELETE CASCADE
+) STRICT;
CREATE TABLE [Group] (
[id] INTEGER PRIMARY KEY,
[order] INTEGER NOT NULL DEFAULT 0,
[recipe_id] INTEGER,
- name TEXT,
+ [name] TEXT NOT NULL DEFAULT '',
FOREIGN KEY([recipe_id]) REFERENCES [Recipe]([id]) ON DELETE CASCADE
-);
+) STRICT;
-CREATE INDEX [Group_order_index] ON [Group] ([order]);
+CREATE INDEX [Group_order_index] ON [Group]([order]);
CREATE TABLE [Step] (
[id] INTEGER PRIMARY KEY,
[order] INTEGER NOT NULL DEFAULT 0,
- [action] TEXT NOT NULL,
+ [action] TEXT NOT NULL DEFAULT '',
[group_id] INTEGER NOT NULL,
FOREIGN KEY(group_id) REFERENCES [Group](id) ON DELETE CASCADE
-);
+) STRICT;
-CREATE INDEX [Step_order_index] ON [Group] ([order]);
+CREATE INDEX [Step_order_index] ON [Group]([order]);
CREATE TABLE [IntermediateSubstance] (
[id] INTEGER PRIMARY KEY,
- [name] TEXT NOT NULL,
- [quantity_id] INTEGER,
- [output_step_id] INTEGER NOT NULL,
- [input_step_id] INTEGER NOT NULL,
-
- FOREIGN KEY([quantity_id]) REFERENCES [Quantity]([id]) ON DELETE CASCADE,
- FOREIGN KEY([output_step_id]) REFERENCES [Step]([id]) ON DELETE CASCADE,
- FOREIGN KEY([input_step_id]) REFERENCES [Step]([id]) ON DELETE CASCADE
-);
+ [name] TEXT NOT NULL DEFAULT '',
+ [quantity_value] REAL,
+ [quantity_unit] TEXT NOT NULL DEFAULT '',
+ [output_group_id] INTEGER NOT NULL,
+ [input_group_id] INTEGER NOT NULL,
+
+ FOREIGN KEY([output_group_id]) REFERENCES [group]([id]) ON DELETE CASCADE,
+ FOREIGN KEY([input_group_id]) REFERENCES [group]([id]) ON DELETE CASCADE
+) STRICT;