X-Git-Url: http://git.euphorik.ch/?a=blobdiff_plain;f=backend%2Fsql%2Fversion_1.sql;h=b1f231fb31215b314a04253ab850b846a572ed4e;hb=cc2e5b6893b582b4b5c4e7a93e914a189f6a959b;hp=d68982dac208db01767a2ff5674fbb1b746b227e;hpb=c951ba6d37d525f00c6fb92eb5ab72e1ae311791;p=recipes.git

diff --git a/backend/sql/version_1.sql b/backend/sql/version_1.sql
index d68982d..b1f231f 100644
--- a/backend/sql/version_1.sql
+++ b/backend/sql/version_1.sql
@@ -1,64 +1,126 @@
 -- Version 1 is the initial structure.
-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;
 
 CREATE TABLE [Group] (
-    id INTEGER PRIMARY KEY,
-    name TEXT
-);
-
-CREATE TABLE Step (
-    id INTEGER PRIMARY KEY,
-    action TEXT NOT NULL,
-    group_id INTEGER NOT NULL,
-
-    FOREIGN KEY(group_id) REFERENCES [Group](id)
-);
-
-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),
-    FOREIGN KEY(output_step_id) REFERENCES Step(id),
-    FOREIGN KEY(input_step_id) REFERENCES Step(id)
-);
\ No newline at end of file
+    [id] INTEGER PRIMARY KEY,
+    [order] INTEGER NOT NULL DEFAULT 0,
+    [recipe_id] INTEGER,
+    [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 TABLE [Step] (
+    [id] INTEGER PRIMARY KEY,
+    [order] INTEGER NOT NULL DEFAULT 0,
+    [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 TABLE [IntermediateSubstance] (
+    [id] INTEGER PRIMARY KEY,
+    [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;