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 [RecipeTag
] (
50 [id] INTEGER PRIMARY KEY,
52 [recipe_id
] INTEGER NOT NULL,
53 [tag_id
] INTEGER NO NULL,
55 FOREIGN KEY([recipe_id
]) REFERENCES [Recipe
]([id]) ON DELETE CASCADE,
56 FOREIGN KEY([tag_id
]) REFERENCES [Tag
]([id]) ON DELETE CASCADE
60 [id] INTEGER PRIMARY KEY,
61 [recipe_tag_id
] INTEGER,
64 FOREIGN KEY([recipe_tag_id
]) REFERENCES [RecipeTag
]([id]) ON DELETE SET NULL
67 CREATE UNIQUE INDEX [Tag_name_index
] ON [Tag
] ([name]);
69 CREATE TABLE [Quantity
] (
70 [id] INTEGER PRIMARY KEY,
75 CREATE TABLE [Ingredient
] (
76 [id] INTEGER PRIMARY KEY,
78 [quantity_id
] INTEGER,
79 [input_step_id
] INTEGER NOT NULL,
81 FOREIGN KEY([quantity_id
]) REFERENCES Quantity([id]) ON DELETE CASCADE,
82 FOREIGN KEY([input_step_id
]) REFERENCES Step([id]) ON DELETE CASCADE
85 CREATE TABLE [Group] (
86 [id] INTEGER PRIMARY KEY,
87 [order] INTEGER NOT NULL DEFAULT 0,
91 FOREIGN KEY([recipe_id
]) REFERENCES [Recipe
]([id]) ON DELETE CASCADE
94 CREATE INDEX [Group_order_index
] ON [Group] ([order]);
97 [id] INTEGER PRIMARY KEY,
98 [order] INTEGER NOT NULL DEFAULT 0,
99 [action] TEXT NOT NULL,
100 [group_id
] INTEGER NOT NULL,
102 FOREIGN KEY(group_id
) REFERENCES [Group](id) ON DELETE CASCADE
105 CREATE INDEX [Step_order_index
] ON [Group] ([order]);
107 CREATE TABLE [IntermediateSubstance
] (
108 [id] INTEGER PRIMARY KEY,
109 [name] TEXT NOT NULL,
110 [quantity_id
] INTEGER,
111 [output_step_id
] INTEGER NOT NULL,
112 [input_step_id
] INTEGER NOT NULL,
114 FOREIGN KEY([quantity_id
]) REFERENCES [Quantity
]([id]) ON DELETE CASCADE,
115 FOREIGN KEY([output_step_id
]) REFERENCES [Step
]([id]) ON DELETE CASCADE,
116 FOREIGN KEY([input_step_id
]) REFERENCES [Step
]([id]) ON DELETE CASCADE