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] TEXT,
  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     [order] INTEGER NOT NULL DEFAULT 0,
  44     [recipe_id] INTEGER,
  45     name TEXT,
  46
  47     FOREIGN KEY([recipe_id]) REFERENCES [Recipe]([id])
  48 );
  49
  50 CREATE INDEX [Group_order_index] ON [Group] ([order]);
  51
  52 CREATE TABLE [Step] (
  53     [id] INTEGER PRIMARY KEY,
  54     [order] INTEGER NOT NULL DEFAULT 0,
  55     [action] TEXT NOT NULL,
  56     [group_id] INTEGER NOT NULL,
  57
  58     FOREIGN KEY(group_id) REFERENCES [Group](id)
  59 );
  60
  61 CREATE INDEX [Step_order_index] ON [Group] ([order]);
  62
  63 CREATE TABLE [IntermediateSubstance] (
  64     [id] INTEGER PRIMARY KEY,
  65     [name] TEXT NOT NULL,
  66     [quantity_id] INTEGER,
  67     [output_step_id] INTEGER NOT NULL,
  68     [input_step_id] INTEGER NOT NULL,
  69
  70     FOREIGN KEY([quantity_id]) REFERENCES [Quantity]([id]),
  71     FOREIGN KEY([output_step_id]) REFERENCES [Step]([id]),
  72     FOREIGN KEY([input_step_id]) REFERENCES [Step]([id])
  73 );