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
  46     FOREIGN KEY([user_id]) REFERENCES [User]([id]) ON DELETE SET NULL
  47 );
  48
  49 CREATE TABLE [RecipeTag] (
  50     [id] INTEGER PRIMARY KEY,
  51
  52     [recipe_id] INTEGER NOT NULL,
  53     [tag_id] INTEGER NO NULL,
  54
  55     FOREIGN KEY([recipe_id]) REFERENCES [Recipe]([id]) ON DELETE CASCADE,
  56     FOREIGN KEY([tag_id]) REFERENCES [Tag]([id]) ON DELETE CASCADE
  57 );
  58
  59 CREATE TABLE [Tag] (
  60     [id] INTEGER PRIMARY KEY,
  61     [recipe_tag_id] INTEGER,
  62     [name] TEXT NOT NULL,
  63
  64     FOREIGN KEY([recipe_tag_id]) REFERENCES [RecipeTag]([id]) ON DELETE SET NULL
  65 );
  66
  67 CREATE UNIQUE INDEX [Tag_name_index] ON [Tag] ([name]);
  68
  69 CREATE TABLE [Quantity] (
  70     [id] INTEGER PRIMARY KEY,
  71     [value] REAL,
  72     [unit] TEXT
  73 );
  74
  75 CREATE TABLE [Ingredient] (
  76     [id] INTEGER PRIMARY KEY,
  77     [name] TEXT NOT NULL,
  78     [quantity_id] INTEGER,
  79     [input_step_id] INTEGER NOT NULL,
  80
  81     FOREIGN KEY([quantity_id]) REFERENCES Quantity([id]) ON DELETE CASCADE,
  82     FOREIGN KEY([input_step_id]) REFERENCES Step([id]) ON DELETE CASCADE
  83 );
  84
  85 CREATE TABLE [Group] (
  86     [id] INTEGER PRIMARY KEY,
  87     [order] INTEGER NOT NULL DEFAULT 0,
  88     [recipe_id] INTEGER,
  89     name TEXT,
  90
  91     FOREIGN KEY([recipe_id]) REFERENCES [Recipe]([id]) ON DELETE CASCADE
  92 );
  93
  94 CREATE INDEX [Group_order_index] ON [Group] ([order]);
  95
  96 CREATE TABLE [Step] (
  97     [id] INTEGER PRIMARY KEY,
  98     [order] INTEGER NOT NULL DEFAULT 0,
  99     [action] TEXT NOT NULL,
 100     [group_id] INTEGER NOT NULL,
 101
 102     FOREIGN KEY(group_id) REFERENCES [Group](id) ON DELETE CASCADE
 103 );
 104
 105 CREATE INDEX [Step_order_index] ON [Group] ([order]);
 106
 107 CREATE TABLE [IntermediateSubstance] (
 108     [id] INTEGER PRIMARY KEY,
 109     [name] TEXT NOT NULL,
 110     [quantity_id] INTEGER,
 111     [output_step_id] INTEGER NOT NULL,
 112     [input_step_id] INTEGER NOT NULL,
 113
 114     FOREIGN KEY([quantity_id]) REFERENCES [Quantity]([id]) ON DELETE CASCADE,
 115     FOREIGN KEY([output_step_id]) REFERENCES [Step]([id]) ON DELETE CASCADE,
 116     FOREIGN KEY([input_step_id]) REFERENCES [Step]([id]) ON DELETE CASCADE
 117 );