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] TEXT
   6 ) STRICT;
   7
   8 CREATE TABLE [User] (
   9     [id] INTEGER PRIMARY KEY,
  10     [email] TEXT NOT NULL,
  11     [name] TEXT NOT NULL DEFAULT '',
  12     [default_servings] INTEGER DEFAULT 4,
  13
  14     [password] TEXT NOT NULL, -- argon2(password_plain, salt).
  15
  16     [creation_datetime] TEXT 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 ) STRICT;
  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] TEXT,
  28
  29     -- 24 alphanumeric character token.
  30     -- Can be stored in a cookie to be able to authenticate without a password.
  31     [token] TEXT NOT NULL,
  32
  33     [ip] TEXT, -- Can be ipv4 or ipv6
  34     [user_agent] TEXT,
  35
  36     FOREIGN KEY([user_id]) REFERENCES [User]([id]) ON DELETE CASCADE
  37 ) STRICT;
  38
  39 CREATE INDEX [UserLoginToken_token_index] ON [UserLoginToken]([token]);
  40
  41 CREATE TABLE [Recipe] (
  42     [id] INTEGER PRIMARY KEY,
  43     [user_id] INTEGER, -- Can be null if a user is deleted.
  44     [title] TEXT NOT NULL,
  45     [estimate_time] INTEGER,
  46     [description] TEXT NOT NULL DEFAULT '',
  47     [difficulty] INTEGER NOT NULL DEFAULT 0,
  48     [servings] INTEGER DEFAULT 4,
  49     [is_published] INTEGER NOT NULL DEFAULT FALSE,
  50
  51     FOREIGN KEY([user_id]) REFERENCES [User]([id]) ON DELETE SET NULL
  52 ) STRICT;
  53
  54 CREATE TABLE [Image] (
  55     [Id] INTEGER PRIMARY KEY,
  56     [recipe_id] INTEGER NOT NULL,
  57     [name] TEXT NOT NULL DEFAULT '',
  58     [description] TEXT NOT NULL DEFAULT '',
  59     [image] BLOB,
  60
  61     FOREIGN KEY([recipe_id]) REFERENCES [Recipe]([id]) ON DELETE CASCADE
  62 ) STRICT;
  63
  64 CREATE TABLE [RecipeTag] (
  65     [id] INTEGER PRIMARY KEY,
  66
  67     [recipe_id] INTEGER NOT NULL,
  68     [tag_id] INTEGER NOT NULL,
  69
  70     FOREIGN KEY([recipe_id]) REFERENCES [Recipe]([id]) ON DELETE CASCADE,
  71     FOREIGN KEY([tag_id]) REFERENCES [Tag]([id]) ON DELETE CASCADE
  72 ) STRICT;
  73
  74 CREATE TABLE [Tag] (
  75     [id] INTEGER PRIMARY KEY,
  76     [recipe_tag_id] INTEGER,
  77     [name] TEXT NOT NULL,
  78
  79     FOREIGN KEY([recipe_tag_id]) REFERENCES [RecipeTag]([id]) ON DELETE SET NULL
  80 ) STRICT;
  81
  82 CREATE UNIQUE INDEX [Tag_name_index] ON [Tag] ([name]);
  83
  84 CREATE TABLE [Ingredient] (
  85     [id] INTEGER PRIMARY KEY,
  86     [name] TEXT NOT NULL,
  87     [quantity_value] REAL,
  88     [quantity_unit] TEXT NOT NULL DEFAULT '',
  89     [input_group_id] INTEGER NOT NULL,
  90
  91     FOREIGN KEY([input_group_id]) REFERENCES [Group]([id]) ON DELETE CASCADE
  92 ) STRICT;
  93
  94 CREATE TABLE [Group] (
  95     [id] INTEGER PRIMARY KEY,
  96     [order] INTEGER NOT NULL DEFAULT 0,
  97     [recipe_id] INTEGER,
  98     [name] TEXT NOT NULL DEFAULT '',
  99
 100     FOREIGN KEY([recipe_id]) REFERENCES [Recipe]([id]) ON DELETE CASCADE
 101 ) STRICT;
 102
 103 CREATE INDEX [Group_order_index] ON [Group]([order]);
 104
 105 CREATE TABLE [Step] (
 106     [id] INTEGER PRIMARY KEY,
 107     [order] INTEGER NOT NULL DEFAULT 0,
 108     [action] TEXT NOT NULL DEFAULT '',
 109     [group_id] INTEGER NOT NULL,
 110
 111     FOREIGN KEY(group_id) REFERENCES [Group](id) ON DELETE CASCADE
 112 ) STRICT;
 113
 114 CREATE INDEX [Step_order_index] ON [Group]([order]);
 115
 116 CREATE TABLE [IntermediateSubstance] (
 117     [id] INTEGER PRIMARY KEY,
 118     [name] TEXT NOT NULL DEFAULT '',
 119     [quantity_value] REAL,
 120     [quantity_unit] TEXT NOT NULL DEFAULT '',
 121     [output_group_id] INTEGER NOT NULL,
 122     [input_group_id] INTEGER NOT NULL,
 123
 124     FOREIGN KEY([output_group_id]) REFERENCES [group]([id]) ON DELETE CASCADE,
 125     FOREIGN KEY([input_group_id]) REFERENCES [group]([id]) ON DELETE CASCADE
 126 ) STRICT;