X-Git-Url: http://git.euphorik.ch/?a=blobdiff_plain;f=backend%2Fsql%2Fversion_1.sql;h=c3488dbcac5cf64ae31291940dc34aa4591c2bc5;hb=642dd8a80ce2e1212b8e30c1edabb32bdb416cfc;hp=aaa8a9f4ed396436deccef0fdf972038f17f0468;hpb=aedfae1d17d2fd39b3b1f4889723d627fcc79218;p=recipes.git

diff --git a/backend/sql/version_1.sql b/backend/sql/version_1.sql
index aaa8a9f..c3488db 100644
--- a/backend/sql/version_1.sql
+++ b/backend/sql/version_1.sql
@@ -14,7 +14,9 @@ CREATE TABLE [User] (
     [password] TEXT NOT NULL, -- argon2(password_plain, salt).
 
     [creation_datetime] DATETIME NOT NULL, -- Updated when the validation email is sent.
-    [validation_token] TEXT -- If not null then the user has not validated his account yet.
+    [validation_token] TEXT, -- If not null then the user has not validated his account yet.
+
+    [is_admin] INTEGER NOT NULL DEFAULT FALSE
 );
 
 CREATE UNIQUE INDEX [User_email_index] ON [User] ([email]);
@@ -28,22 +30,53 @@ CREATE TABLE [UserLoginToken] (
     [ip] TEXT, -- Can be ipv4 or ipv6
     [user_agent] TEXT,
 
-    FOREIGN KEY([user_id]) REFERENCES [User]([id])
+    FOREIGN KEY([user_id]) REFERENCES [User]([id]) ON DELETE CASCADE
 );
 
 CREATE INDEX [UserLoginToken_token_index] ON [UserLoginToken] ([token]);
 
 CREATE TABLE [Recipe] (
     [id] INTEGER PRIMARY KEY,
-    [user_id] INTEGER NOT NULL,
+    [user_id] INTEGER, -- Can be null if a user is deleted.
     [title] TEXT NOT NULL,
     [estimate_time] INTEGER,
     [description] TEXT,
     [servings] INTEGER DEFAULT 4,
+    [is_published] INTEGER NOT NULL DEFAULT FALSE,
+
+    FOREIGN KEY([user_id]) REFERENCES [User]([id]) ON DELETE SET NULL
+);
+
+CREATE TABLE [Image] (
+    [Id] INTEGER PRIMARY KEY,
+    [recipe_id] INTEGER NOT NULL,
+    [name] TEXT,
+    [description] TEXT,
+    [image] BLOB,
+
+    FOREIGN KEY([recipe_id]) REFERENCES [Recipe]([id]) ON DELETE CASCADE
+);
 
-    FOREIGN KEY([user_id]) REFERENCES [User]([id])
+CREATE TABLE [RecipeTag] (
+    [id] INTEGER PRIMARY KEY,
+
+    [recipe_id] INTEGER NOT NULL,
+    [tag_id] INTEGER NO NULL,
+
+    FOREIGN KEY([recipe_id]) REFERENCES [Recipe]([id]) ON DELETE CASCADE,
+    FOREIGN KEY([tag_id]) REFERENCES [Tag]([id]) ON DELETE CASCADE
 );
 
+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
+);
+
+CREATE UNIQUE INDEX [Tag_name_index] ON [Tag] ([name]);
+
 CREATE TABLE [Quantity] (
     [id] INTEGER PRIMARY KEY,
     [value] REAL,
@@ -56,8 +89,8 @@ CREATE TABLE [Ingredient] (
     [quantity_id] INTEGER,
     [input_step_id] INTEGER NOT NULL,
 
-    FOREIGN KEY([quantity_id]) REFERENCES Quantity([id]),
-    FOREIGN KEY([input_step_id]) REFERENCES Step([id])
+    FOREIGN KEY([quantity_id]) REFERENCES Quantity([id]) ON DELETE CASCADE,
+    FOREIGN KEY([input_step_id]) REFERENCES Step([id]) ON DELETE CASCADE
 );
 
 CREATE TABLE [Group] (
@@ -66,7 +99,7 @@ CREATE TABLE [Group] (
     [recipe_id] INTEGER,
     name TEXT,
 
-    FOREIGN KEY([recipe_id]) REFERENCES [Recipe]([id])
+    FOREIGN KEY([recipe_id]) REFERENCES [Recipe]([id]) ON DELETE CASCADE
 );
 
 CREATE INDEX [Group_order_index] ON [Group] ([order]);
@@ -77,7 +110,7 @@ CREATE TABLE [Step] (
     [action] TEXT NOT NULL,
     [group_id] INTEGER NOT NULL,
 
-    FOREIGN KEY(group_id) REFERENCES [Group](id)
+    FOREIGN KEY(group_id) REFERENCES [Group](id) ON DELETE CASCADE
 );
 
 CREATE INDEX [Step_order_index] ON [Group] ([order]);
@@ -89,7 +122,7 @@ CREATE TABLE [IntermediateSubstance] (
     [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])
+    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
 );