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,
11 [name] TEXT NOT NULL DEFAULT '',
12 [default_servings
] INTEGER DEFAULT 4,
14 [password] TEXT NOT NULL, -- argon2(password_plain, salt).
16 [creation_datetime
] TEXT 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
] TEXT,
29 -- 24 alphanumeric character token.
30 -- Can be stored in a cookie to be able to authenticate without a password.
31 [token] TEXT NOT NULL,
33 [ip
] TEXT, -- Can be ipv4 or ipv6
36 FOREIGN KEY([user_id
]) REFERENCES [User]([id]) ON DELETE CASCADE
39 CREATE INDEX [UserLoginToken_token_index
] ON [UserLoginToken
]([token]);
41 CREATE TABLE [Recipe
] (
42 [id] INTEGER PRIMARY KEY,
43 [user_id
] INTEGER, -- Can be null if a user is deleted.
44 [title
] TEXT NOT NULL,
45 [estimate_time
] INTEGER,
46 [description
] TEXT NOT NULL DEFAULT '',
47 [difficulty
] INTEGER NOT NULL DEFAULT 0,
48 [servings
] INTEGER DEFAULT 4,
49 [is_published
] INTEGER NOT NULL DEFAULT FALSE,
51 FOREIGN KEY([user_id
]) REFERENCES [User]([id]) ON DELETE SET NULL
54 CREATE TABLE [Image
] (
55 [Id] INTEGER PRIMARY KEY,
56 [recipe_id
] INTEGER NOT NULL,
57 [name] TEXT NOT NULL DEFAULT '',
58 [description
] TEXT NOT NULL DEFAULT '',
61 FOREIGN KEY([recipe_id
]) REFERENCES [Recipe
]([id]) ON DELETE CASCADE
64 CREATE TABLE [RecipeTag
] (
65 [id] INTEGER PRIMARY KEY,
67 [recipe_id
] INTEGER NOT NULL,
68 [tag_id
] INTEGER NOT NULL,
70 FOREIGN KEY([recipe_id
]) REFERENCES [Recipe
]([id]) ON DELETE CASCADE,
71 FOREIGN KEY([tag_id
]) REFERENCES [Tag
]([id]) ON DELETE CASCADE
75 [id] INTEGER PRIMARY KEY,
76 [recipe_tag_id
] INTEGER,
79 FOREIGN KEY([recipe_tag_id
]) REFERENCES [RecipeTag
]([id]) ON DELETE SET NULL
82 CREATE UNIQUE INDEX [Tag_name_index
] ON [Tag
] ([name]);
84 CREATE TABLE [Ingredient
] (
85 [id] INTEGER PRIMARY KEY,
87 [quantity_value
] REAL,
88 [quantity_unit
] TEXT NOT NULL DEFAULT '',
89 [input_group_id
] INTEGER NOT NULL,
91 FOREIGN KEY([input_group_id
]) REFERENCES [Group]([id]) ON DELETE CASCADE
94 CREATE TABLE [Group] (
95 [id] INTEGER PRIMARY KEY,
96 [order] INTEGER NOT NULL DEFAULT 0,
98 [name] TEXT NOT NULL DEFAULT '',
100 FOREIGN KEY([recipe_id
]) REFERENCES [Recipe
]([id]) ON DELETE CASCADE
103 CREATE INDEX [Group_order_index
] ON [Group]([order]);
105 CREATE TABLE [Step
] (
106 [id] INTEGER PRIMARY KEY,
107 [order] INTEGER NOT NULL DEFAULT 0,
108 [action] TEXT NOT NULL DEFAULT '',
109 [group_id
] INTEGER NOT NULL,
111 FOREIGN KEY(group_id
) REFERENCES [Group](id) ON DELETE CASCADE
114 CREATE INDEX [Step_order_index
] ON [Group]([order]);
116 CREATE TABLE [IntermediateSubstance
] (
117 [id] INTEGER PRIMARY KEY,
118 [name] TEXT NOT NULL DEFAULT '',
119 [quantity_value
] REAL,
120 [quantity_unit
] TEXT NOT NULL DEFAULT '',
121 [output_group_id
] INTEGER NOT NULL,
122 [input_group_id
] INTEGER NOT NULL,
124 FOREIGN KEY([output_group_id
]) REFERENCES [group]([id]) ON DELETE CASCADE,
125 FOREIGN KEY([input_group_id
]) REFERENCES [group]([id]) ON DELETE CASCADE