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 [Quantity] (
  50     [id] INTEGER PRIMARY KEY,
  51     [value] REAL,
  52     [unit] TEXT
  53 );
  54
  55 CREATE TABLE [Ingredient] (
  56     [id] INTEGER PRIMARY KEY,
  57     [name] TEXT NOT NULL,
  58     [quantity_id] INTEGER,
  59     [input_step_id] INTEGER NOT NULL,
  60
  61     FOREIGN KEY([quantity_id]) REFERENCES Quantity([id]) ON DELETE CASCADE,
  62     FOREIGN KEY([input_step_id]) REFERENCES Step([id]) ON DELETE CASCADE
  63 );
  64
  65 CREATE TABLE [Group] (
  66     [id] INTEGER PRIMARY KEY,
  67     [order] INTEGER NOT NULL DEFAULT 0,
  68     [recipe_id] INTEGER,
  69     name TEXT,
  70
  71     FOREIGN KEY([recipe_id]) REFERENCES [Recipe]([id]) ON DELETE CASCADE
  72 );
  73
  74 CREATE INDEX [Group_order_index] ON [Group] ([order]);
  75
  76 CREATE TABLE [Step] (
  77     [id] INTEGER PRIMARY KEY,
  78     [order] INTEGER NOT NULL DEFAULT 0,
  79     [action] TEXT NOT NULL,
  80     [group_id] INTEGER NOT NULL,
  81
  82     FOREIGN KEY(group_id) REFERENCES [Group](id) ON DELETE CASCADE
  83 );
  84
  85 CREATE INDEX [Step_order_index] ON [Group] ([order]);
  86
  87 CREATE TABLE [IntermediateSubstance] (
  88     [id] INTEGER PRIMARY KEY,
  89     [name] TEXT NOT NULL,
  90     [quantity_id] INTEGER,
  91     [output_step_id] INTEGER NOT NULL,
  92     [input_step_id] INTEGER NOT NULL,
  93
  94     FOREIGN KEY([quantity_id]) REFERENCES [Quantity]([id]) ON DELETE CASCADE,
  95     FOREIGN KEY([output_step_id]) REFERENCES [Step]([id]) ON DELETE CASCADE,
  96     FOREIGN KEY([input_step_id]) REFERENCES [Step]([id]) ON DELETE CASCADE
  97 );