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
  20 CREATE UNIQUE INDEX [User_email_index] ON [User] ([email]);
  21
  22 CREATE TABLE [UserLoginToken] (
  23     [id] INTEGER PRIMARY KEY,
  24     [user_id] INTEGER NOT NULL,
  25     [last_login_datetime] DATETIME,
  26     [token] TEXT NOT NULL, --  24 alphanumeric character token. Can be stored in a cookie to be able to authenticate without a password.
  27
  28     [ip] TEXT, -- Can be ipv4 or ipv6
  29     [user_agent] TEXT,
  30
  31     FOREIGN KEY([user_id]) REFERENCES [User]([id])
  32 );
  33
  34 CREATE INDEX [UserLoginToken_token_index] ON [UserLoginToken] ([token]);
  35
  36 CREATE TABLE [Recipe] (
  37     [id] INTEGER PRIMARY KEY,
  38     [user_id] INTEGER NOT NULL,
  39     [title] TEXT NOT NULL,
  40     [estimate_time] INTEGER,
  41     [description] TEXT,
  42     [servings] INTEGER DEFAULT 4,
  43
  44     FOREIGN KEY([user_id]) REFERENCES [User]([id])
  45 );
  46
  47 CREATE TABLE [Quantity] (
  48     [id] INTEGER PRIMARY KEY,
  49     [value] REAL,
  50     [unit] TEXT
  51 );
  52
  53 CREATE TABLE [Ingredient] (
  54     [id] INTEGER PRIMARY KEY,
  55     [name] TEXT NOT NULL,
  56     [quantity_id] INTEGER,
  57     [input_step_id] INTEGER NOT NULL,
  58
  59     FOREIGN KEY([quantity_id]) REFERENCES Quantity([id]),
  60     FOREIGN KEY([input_step_id]) REFERENCES Step([id])
  61 );
  62
  63 CREATE TABLE [Group] (
  64     [id] INTEGER PRIMARY KEY,
  65     [order] INTEGER NOT NULL DEFAULT 0,
  66     [recipe_id] INTEGER,
  67     name TEXT,
  68
  69     FOREIGN KEY([recipe_id]) REFERENCES [Recipe]([id])
  70 );
  71
  72 CREATE INDEX [Group_order_index] ON [Group] ([order]);
  73
  74 CREATE TABLE [Step] (
  75     [id] INTEGER PRIMARY KEY,
  76     [order] INTEGER NOT NULL DEFAULT 0,
  77     [action] TEXT NOT NULL,
  78     [group_id] INTEGER NOT NULL,
  79
  80     FOREIGN KEY(group_id) REFERENCES [Group](id)
  81 );
  82
  83 CREATE INDEX [Step_order_index] ON [Group] ([order]);
  84
  85 CREATE TABLE [IntermediateSubstance] (
  86     [id] INTEGER PRIMARY KEY,
  87     [name] TEXT NOT NULL,
  88     [quantity_id] INTEGER,
  89     [output_step_id] INTEGER NOT NULL,
  90     [input_step_id] INTEGER NOT NULL,
  91
  92     FOREIGN KEY([quantity_id]) REFERENCES [Quantity]([id]),
  93     FOREIGN KEY([output_step_id]) REFERENCES [Step]([id]),
  94     FOREIGN KEY([input_step_id]) REFERENCES [Step]([id])
  95 );