1 -- Version 1 is the initial structure.
2 CREATE TABLE [Version] (
3 [id] INTEGER PRIMARY KEY,
4 [version] INTEGER NOT NULL UNIQUE,
9 [id] INTEGER PRIMARY KEY,
10 [email
] TEXT NOT NULL,
12 [default_servings
] INTEGER DEFAULT 4,
14 [password] TEXT NOT NULL, -- argon2(password_plain, salt).
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.
20 CREATE UNIQUE INDEX [User_email_index
] ON [User] ([email
]);
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.
31 FOREIGN KEY([user_id
]) REFERENCES [User]([id])
34 CREATE INDEX [UserLoginToken_token_index
] ON [UserLoginToken
] ([token]);
36 CREATE TABLE [Recipe
] (
37 [id] INTEGER PRIMARY KEY,
38 [user_id
] INTEGER NOT NULL,
39 [title
] TEXT NOT NULL,
40 [estimate_time
] INTEGER,
42 [servings
] INTEGER DEFAULT 4,
44 FOREIGN KEY([user_id
]) REFERENCES [User]([id])
47 CREATE TABLE [Quantity
] (
48 [id] INTEGER PRIMARY KEY,
53 CREATE TABLE [Ingredient
] (
54 [id] INTEGER PRIMARY KEY,
56 [quantity_id
] INTEGER,
57 [input_step_id
] INTEGER NOT NULL,
59 FOREIGN KEY([quantity_id
]) REFERENCES Quantity([id]),
60 FOREIGN KEY([input_step_id
]) REFERENCES Step([id])
63 CREATE TABLE [Group] (
64 [id] INTEGER PRIMARY KEY,
65 [order] INTEGER NOT NULL DEFAULT 0,
69 FOREIGN KEY([recipe_id
]) REFERENCES [Recipe
]([id])
72 CREATE INDEX [Group_order_index
] ON [Group] ([order]);
75 [id] INTEGER PRIMARY KEY,
76 [order] INTEGER NOT NULL DEFAULT 0,
77 [action] TEXT NOT NULL,
78 [group_id
] INTEGER NOT NULL,
80 FOREIGN KEY(group_id
) REFERENCES [Group](id)
83 CREATE INDEX [Step_order_index
] ON [Group] ([order]);
85 CREATE TABLE [IntermediateSubstance
] (
86 [id] INTEGER PRIMARY KEY,
88 [quantity_id
] INTEGER,
89 [output_step_id
] INTEGER NOT NULL,
90 [input_step_id
] INTEGER NOT NULL,
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])