1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
|
PRAGMA foreign_keys = ON;
CREATE TABLE instances (
id INTEGER PRIMARY KEY,
uuid TEXT NOT NULL,
name TEXT NOT NULL,
is_self BOOLEAN NOT NULL,
last_changed INTEGER NOT NULL
);
CREATE TABLE users (
id INTEGER PRIMARY KEY,
uuid TEXT NOT NULL,
name TEXT NOT NULL,
home INTEGER NOT NULL,
last_changed INTEGER NOT NULL,
FOREIGN KEY(home) REFERENCES instances(id)
);
CREATE TABLE recipes (
id INTEGER PRIMARY KEY,
uuid TEXT NOT NULL
);
CREATE TABLE recipe_versions (
id INTEGER PRIMARY KEY,
parent INTEGER NOT NULL,
recipe INTEGER NOT NULL,
author INTEGER NOT NULL,
instance INTEGER NOT NULL,
language TEXT NOT NULL,
title TEXT NOT NULL,
portions INTEGER NOT NULL,
url TEXT NOT NULL,
notes TEXT NOT NULL,
last_changed INTEGER NOT NULL,
FOREIGN KEY(parent) REFERENCES recipe_versions(id),
FOREIGN KEY(recipe) REFERENCES recipes(id),
FOREIGN KEY(author) REFERENCES users(id),
FOREIGN KEY(instance) REFERENCES instances(id)
);
CREATE TABLE steps (
id INTEGER PRIMARY KEY,
text TEXT NOT NULL
);
CREATE TABLE ingredients (
id INTEGER PRIMARY KEY,
ingredient_id TEXT NOT NULL,
amount REAL NOT NULL,
unit INTEGER NOT NULL -- must match unit enum from application code
);
CREATE TABLE foodstuffs (
id INTEGER PRIMARY KEY,
uuid TEXT NOT NULL,
name TEXT NOT NULL, -- in US english
plural TEXT -- in US english or NULL if uncountable
);
CREATE TABLE products (
id INTEGER PRIMARY KEY,
uuid TEXT NOT NULL,
name TEXT NOT NULL,
gtin TEXT
);
|
