backend/sql/version_1.sql

   1 -- Version 1 is the initial structure.
   2 CREATE TABLE [Version] (
   3     [id] INTEGER PRIMARY KEY,
   4     [version] INTEGER NOT NULL UNIQUE,
   5     [datetime] DATETIME
   6 );
   7
   8 CREATE TABLE [User] (
   9     [id] INTEGER PRIMARY KEY,
  10     [email] TEXT NOT NULL,
  11     [name] TEXT,
  12     [default_servings] INTEGER DEFAULT 4,
  13
  14     [password] TEXT NOT NULL, -- argon2(password_plain, salt).
  15
  16     [creation_datetime] DATETIME NOT NULL, -- Updated when the validation email is sent.
  17     [validation_token] TEXT, -- If not null then the user has not validated his account yet.
  18
  19     [is_admin] INTEGER NOT NULL DEFAULT FALSE
  20 );
  21
  22 CREATE UNIQUE INDEX [User_email_index] ON [User] ([email]);
  23
  24 CREATE TABLE [UserLoginToken] (
  25     [id] INTEGER PRIMARY KEY,
  26     [user_id] INTEGER NOT NULL,
  27     [last_login_datetime] DATETIME,
  28     [token] TEXT NOT NULL, --  24 alphanumeric character token. Can be stored in a cookie to be able to authenticate without a password.
  29
  30     [ip] TEXT, -- Can be ipv4 or ipv6
  31     [user_agent] TEXT,
  32
  33     FOREIGN KEY([user_id]) REFERENCES [User]([id]) ON DELETE CASCADE
  34 );
  35
  36 CREATE INDEX [UserLoginToken_token_index] ON [UserLoginToken] ([token]);
  37
  38 CREATE TABLE [Recipe] (
  39     [id] INTEGER PRIMARY KEY,
  40     [user_id] INTEGER, -- Can be null if a user is deleted.
  41     [title] TEXT NOT NULL,
  42     [estimate_time] INTEGER,
  43     [description] TEXT,
  44     [servings] INTEGER DEFAULT 4,
  45     [is_published] INTEGER NOT NULL DEFAULT FALSE,
  46
  47     FOREIGN KEY([user_id]) REFERENCES [User]([id]) ON DELETE SET NULL
  48 );
  49
  50 CREATE TABLE [Image] (
  51     [Id] INTEGER PRIMARY KEY,
  52     [recipe_id] INTEGER NOT NULL,
  53     [name] TEXT,
  54     [description] TEXT,
  55     [image] BLOB,
  56
  57     FOREIGN KEY([recipe_id]) REFERENCES [Recipe]([id]) ON DELETE CASCADE
  58 );
  59
  60 CREATE TABLE [RecipeTag] (
  61     [id] INTEGER PRIMARY KEY,
  62
  63     [recipe_id] INTEGER NOT NULL,
  64     [tag_id] INTEGER NO NULL,
  65
  66     FOREIGN KEY([recipe_id]) REFERENCES [Recipe]([id]) ON DELETE CASCADE,
  67     FOREIGN KEY([tag_id]) REFERENCES [Tag]([id]) ON DELETE CASCADE
  68 );
  69
  70 CREATE TABLE [Tag] (
  71     [id] INTEGER PRIMARY KEY,
  72     [recipe_tag_id] INTEGER,
  73     [name] TEXT NOT NULL,
  74
  75     FOREIGN KEY([recipe_tag_id]) REFERENCES [RecipeTag]([id]) ON DELETE SET NULL
  76 );
  77
  78 CREATE UNIQUE INDEX [Tag_name_index] ON [Tag] ([name]);
  79
  80 CREATE TABLE [Quantity] (
  81     [id] INTEGER PRIMARY KEY,
  82     [value] REAL,
  83     [unit] TEXT
  84 );
  85
  86 CREATE TABLE [Ingredient] (
  87     [id] INTEGER PRIMARY KEY,
  88     [name] TEXT NOT NULL,
  89     [quantity_id] INTEGER,
  90     [input_step_id] INTEGER NOT NULL,
  91
  92     FOREIGN KEY([quantity_id]) REFERENCES Quantity([id]) ON DELETE CASCADE,
  93     FOREIGN KEY([input_step_id]) REFERENCES Step([id]) ON DELETE CASCADE
  94 );
  95
  96 CREATE TABLE [Group] (
  97     [id] INTEGER PRIMARY KEY,
  98     [order] INTEGER NOT NULL DEFAULT 0,
  99     [recipe_id] INTEGER,
 100     name TEXT,
 101
 102     FOREIGN KEY([recipe_id]) REFERENCES [Recipe]([id]) ON DELETE CASCADE
 103 );
 104
 105 CREATE INDEX [Group_order_index] ON [Group] ([order]);
 106
 107 CREATE TABLE [Step] (
 108     [id] INTEGER PRIMARY KEY,
 109     [order] INTEGER NOT NULL DEFAULT 0,
 110     [action] TEXT NOT NULL,
 111     [group_id] INTEGER NOT NULL,
 112
 113     FOREIGN KEY(group_id) REFERENCES [Group](id) ON DELETE CASCADE
 114 );
 115
 116 CREATE INDEX [Step_order_index] ON [Group] ([order]);
 117
 118 CREATE TABLE [IntermediateSubstance] (
 119     [id] INTEGER PRIMARY KEY,
 120     [name] TEXT NOT NULL,
 121     [quantity_id] INTEGER,
 122     [output_step_id] INTEGER NOT NULL,
 123     [input_step_id] INTEGER NOT NULL,
 124
 125     FOREIGN KEY([quantity_id]) REFERENCES [Quantity]([id]) ON DELETE CASCADE,
 126     FOREIGN KEY([output_step_id]) REFERENCES [Step]([id]) ON DELETE CASCADE,
 127     FOREIGN KEY([input_step_id]) REFERENCES [Step]([id]) ON DELETE CASCADE
 128 );