Service for editing/creating recipe

[recipes.git] / backend / sql / version_1.sql

diff --git a/backend/sql/version_1.sql b/backend/sql/version_1.sql
index c3488db..b1f231f 100644 (file)
--- a/backend/sql/version_1.sql
+++ b/backend/sql/version_1.sql
@@ -2,70 +2,74 @@
 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,
-    [description] TEXT,
+    [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 NO 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,
@@ -73,56 +77,50 @@ CREATE TABLE [Tag] (
     [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 [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,
+    [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;