-CREATE TABLE Version (
- id INTEGER PRIMARY KEY,
- version INTEGER NOT NULL UNIQUE,
- datetime DATETIME
-);
-
-CREATE TABLE User (
- id INTEGER PRIMARY KEY,
- email TEXT NOT NULL,
- password TEXT NOT NULL, -- Hashed and salted.
- name TEXT NOT NULL
-);
-
-CREATE TABLE Recipe (
- id INTEGER PRIMARY KEY,
- user_id INTEGER NOT NULL,
- title TEXT NOT NULL,
- estimate_time INTEGER,
- description DATETIME,
-
- FOREIGN KEY(user_id) REFERENCES User(id)
-);
-
-CREATE TABLE Quantity (
- id INTEGER PRIMARY KEY,
- value REAL,
- unit TEXT
-);
-
-CREATE TABLE Ingredient (
- id INTEGER PRIMARY KEY,
- name TEXT NOT NULL,
- quantity_id INTEGER,
- input_step_id INTEGER NOT NULL,
-
- FOREIGN KEY(quantity_id) REFERENCES Quantity(id),
- FOREIGN KEY(input_step_id) REFERENCES Step(id)
-);
+CREATE TABLE [Version] (
+ [id] INTEGER PRIMARY KEY,
+ [version] INTEGER NOT NULL UNIQUE,
+ [datetime] TEXT
+) STRICT;
+
+CREATE TABLE [User] (
+ [id] INTEGER PRIMARY KEY,
+ [email] TEXT NOT NULL,
+ [name] TEXT NOT NULL DEFAULT '',
+ [default_servings] INTEGER DEFAULT 4,
+
+ [password] TEXT NOT NULL, -- argon2(password_plain, salt).
+
+ [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 TABLE [UserLoginToken] (
+ [id] INTEGER PRIMARY KEY,
+ [user_id] INTEGER NOT NULL,
+ [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 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 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,
+
+ [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,
+ [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_value] REAL,
+ [quantity_unit] TEXT NOT NULL DEFAULT '',
+ [input_group_id] INTEGER NOT NULL,
+
+ FOREIGN KEY([input_group_id]) REFERENCES [Group]([id]) ON DELETE CASCADE
+) STRICT;