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.
19 [is_admin
] INTEGER NOT NULL DEFAULT FALSE
22 CREATE UNIQUE INDEX [User_email_index
] ON [User] ([email
]);
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.
30 [ip
] TEXT, -- Can be ipv4 or ipv6
33 FOREIGN KEY([user_id
]) REFERENCES [User]([id]) ON DELETE CASCADE
36 CREATE INDEX [UserLoginToken_token_index
] ON [UserLoginToken
] ([token]);
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,
44 [servings
] INTEGER DEFAULT 4,
46 FOREIGN KEY([user_id
]) REFERENCES [User]([id]) ON DELETE SET NULL
49 CREATE TABLE [Quantity
] (
50 [id] INTEGER PRIMARY KEY,
55 CREATE TABLE [Ingredient
] (
56 [id] INTEGER PRIMARY KEY,
58 [quantity_id
] INTEGER,
59 [input_step_id
] INTEGER NOT NULL,
61 FOREIGN KEY([quantity_id
]) REFERENCES Quantity([id]) ON DELETE CASCADE,
62 FOREIGN KEY([input_step_id
]) REFERENCES Step([id]) ON DELETE CASCADE
65 CREATE TABLE [Group] (
66 [id] INTEGER PRIMARY KEY,
67 [order] INTEGER NOT NULL DEFAULT 0,
71 FOREIGN KEY([recipe_id
]) REFERENCES [Recipe
]([id]) ON DELETE CASCADE
74 CREATE INDEX [Group_order_index
] ON [Group] ([order]);
77 [id] INTEGER PRIMARY KEY,
78 [order] INTEGER NOT NULL DEFAULT 0,
79 [action] TEXT NOT NULL,
80 [group_id
] INTEGER NOT NULL,
82 FOREIGN KEY(group_id
) REFERENCES [Group](id) ON DELETE CASCADE
85 CREATE INDEX [Step_order_index
] ON [Group] ([order]);
87 CREATE TABLE [IntermediateSubstance
] (
88 [id] INTEGER PRIMARY KEY,
90 [quantity_id
] INTEGER,
91 [output_step_id
] INTEGER NOT NULL,
92 [input_step_id
] INTEGER NOT NULL,
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