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     password TEXT NOT NULL, -- Hashed and salted.
  12     name TEXT NOT NULL
  13 );
  14
  15 CREATE TABLE Recipe (
  16     id INTEGER PRIMARY KEY,
  17     user_id INTEGER NOT NULL,
  18     title TEXT NOT NULL,
  19     estimate_time INTEGER,
  20     description DATETIME,
  21
  22     FOREIGN KEY(user_id) REFERENCES User(id)
  23 );
  24
  25 CREATE TABLE Quantity (
  26     id INTEGER PRIMARY KEY,
  27     value REAL,
  28     unit TEXT
  29 );
  30
  31 CREATE TABLE Ingredient (
  32     id INTEGER PRIMARY KEY,
  33     name TEXT NOT NULL,
  34     quantity_id INTEGER,
  35     input_step_id INTEGER NOT NULL,
  36
  37     FOREIGN KEY(quantity_id) REFERENCES Quantity(id),
  38     FOREIGN KEY(input_step_id) REFERENCES Step(id)
  39 );
  40
  41 CREATE TABLE [Group] (
  42     id INTEGER PRIMARY KEY,
  43     name TEXT
  44 );
  45
  46 CREATE TABLE Step (
  47     id INTEGER PRIMARY KEY,
  48     action TEXT NOT NULL,
  49     group_id INTEGER NOT NULL,
  50
  51     FOREIGN KEY(group_id) REFERENCES [Group](id)
  52 );
  53
  54 CREATE TABLE IntermediateSubstance (
  55     id INTEGER PRIMARY KEY,
  56     name TEXT NOT NULL,
  57     quantity_id INTEGER,
  58     output_step_id INTEGER NOT NULL,
  59     input_step_id INTEGER NOT NULL,
  60
  61     FOREIGN KEY(quantity_id) REFERENCES Quantity(id),
  62     FOREIGN KEY(output_step_id) REFERENCES Step(id),
  63     FOREIGN KEY(input_step_id) REFERENCES Step(id)
  64 );