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
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
|
-- All tables should be declared STRICT, as I actually like to have types checking (and a
-- db that doesn't lie to me).
CREATE TABLE version (
-- The `namespace` is only useful, if other tools ever build on this database
namespace TEXT NOT NULL,
-- The version.
number INTEGER UNIQUE NOT NULL PRIMARY KEY,
-- The validity of this version as UNIX time stamp
valid_from INTEGER NOT NULL CHECK (valid_from < valid_to),
-- If set to `NULL`, represents the current version
valid_to INTEGER UNIQUE CHECK (valid_to > valid_from)
) STRICT;
-- Encodes the tree structure of the product parents.
-- A product parent cannot be a product, but can have parents on it's own.
-- TODO: Fix the possibility for cyclic parent-ship entries <2025-09-05>
CREATE TABLE product_parents (
id TEXT UNIQUE NOT NULL PRIMARY KEY,
parent TEXT DEFAULT NULL CHECK (
id IS NOT parent
),
name TEXT UNIQUE NOT NULL,
description TEXT,
FOREIGN KEY(parent) REFERENCES product_parents(id)
) STRICT;
-- Encodes the tree structure of the recipe parents.
-- TODO: Fix the possibility for cyclic parent-ship entries <2025-09-05>
CREATE TABLE recipe_parents (
id TEXT UNIQUE NOT NULL PRIMARY KEY,
parent TEXT DEFAULT NULL CHECK (
id IS NOT parent
),
name TEXT UNIQUE NOT NULL,
description TEXT,
FOREIGN KEY(parent) REFERENCES recipe_parents(id)
) STRICT;
-- Stores the registered users.
CREATE TABLE users (
id TEXT UNIQUE NOT NULL PRIMARY KEY,
name TEXT UNIQUE NOT NULL,
password_hash TEXT NOT NULL,
description TEXT
) STRICT;
-- Record with barcodes were bought, and how much of this buy is already used up.
CREATE TABLE buys (
buy_id TEXT UNIQUE NOT NULL PRIMARY KEY,
barcode_id INTEGER NOT NULL,
used_amount INTEGER DEFAULT NULL CHECK (used_amount > 0),
timestamp INTEGER NOT NULL,
FOREIGN KEY(barcode_id) REFERENCES barcodes(id)
) STRICT;
CREATE TRIGGER used_amount_lower_or_equal_than_actual_amount_update
BEFORE UPDATE OF used_amount ON buys
FOR EACH ROW
BEGIN
SELECT RAISE(FAIL, "Used amount higher than actual amount")
FROM barcodes
WHERE NEW.barcode_id = barcodes.id
AND NEW.used_amount > barcodes.amount;
END;
CREATE TRIGGER used_amount_lower_or_equal_than_actual_amount_insert
BEFORE INSERT ON buys
FOR EACH ROW
BEGIN
SELECT RAISE(FAIL, "Used amount higher than actual amount")
FROM barcodes
WHERE NEW.barcode_id = barcodes.id
AND NEW.used_amount > barcodes.amount;
END;
CREATE TABLE products (
id TEXT UNIQUE NOT NULL PRIMARY KEY,
name TEXT UNIQUE NOT NULL,
description TEXT,
parent TEXT DEFAULT NULL,
unit_property TEXT NOT NULL,
FOREIGN KEY(parent) REFERENCES product_parents(id),
FOREIGN KEY(unit_property) REFERENCES unit_properties(id)
) STRICT;
CREATE TABLE barcodes (
id INTEGER UNIQUE NOT NULL PRIMARY KEY,
product_id TEXT NOT NULL,
amount INTEGER NOT NULL,
unit TEXT NOT NULL,
FOREIGN KEY(product_id) REFERENCES products(id),
FOREIGN KEY(unit) REFERENCES units(id)
) STRICT;
CREATE TRIGGER unit_has_property_of_product_update
BEFORE UPDATE OF product_id,unit ON barcodes
FOR EACH ROW
BEGIN
SELECT RAISE(FAIL, "Used different unit_property for product_id and unit")
FROM products, units
WHERE NEW.product_id = products.id
AND NEW.unit = units.id
AND units.unit_property != products.unit_property;
END;
CREATE TRIGGER unit_has_property_of_product_insert
BEFORE INSERT ON barcodes
FOR EACH ROW
BEGIN
SELECT RAISE(FAIL, "Used different unit_property for product_id and unit")
FROM products, units
WHERE NEW.product_id = products.id
AND NEW.unit = units.id
AND units.unit_property != products.unit_property;
END;
CREATE TABLE units (
id TEXT UNIQUE NOT NULL PRIMARY KEY,
unit_property TEXT NOT NULL,
full_name_singular TEXT UNIQUE NOT NULL,
full_name_plural TEXT UNIQUE NOT NULL,
short_name TEXT UNIQUE NOT NULL,
description TEXT,
FOREIGN KEY(unit_property) REFERENCES unit_properties(id)
) STRICT;
CREATE TABLE unit_properties (
id TEXT UNIQUE NOT NULL PRIMARY KEY,
name TEXT UNIQUE NOT NULL,
description TEXT
) STRICT;
CREATE TABLE recipies (
id TEXT UNIQUE NOT NULL PRIMARY KEY,
name TEXT UNIQUE NOT NULL,
parent TEXT,
content TEXT NOT NULL,
FOREIGN KEY(parent) REFERENCES recipe_parents(id)
) STRICT;
-- Encodes unit conversions:
-- {factor} {from_unit} = 1 {to_unit}
-- E.g.: 1000 g = 1 kg
CREATE TABLE unit_conversions (
from_unit TEXT NOT NULL,
to_unit TEXT NOT NULL,
factor REAL NOT NULL,
FOREIGN KEY(from_unit) REFERENCES units(id),
FOREIGN KEY(to_unit) REFERENCES units(id)
) STRICT;
CREATE TRIGGER both_units_have_same_unit_property_update
BEFORE UPDATE OF from_unit,to_unit ON unit_conversions
FOR EACH ROW
BEGIN
SELECT RAISE(FAIL, "Used not the same unit_property")
FROM units, units AS units2
WHERE NEW.from_unit = units.id
AND NEW.to_unit = units2.id
AND units.unit_property != units2.unit_property;
END;
CREATE TRIGGER both_units_have_same_unit_property_insert
BEFORE INSERT ON unit_conversions
FOR EACH ROW
BEGIN
SELECT RAISE(FAIL, "Used not the same unit_property")
FROM units, units AS units2
WHERE NEW.from_unit = units.id
AND NEW.to_unit = units2.id
AND units.unit_property != units2.unit_property;
END;
-- Log of all the applied operations to this db.
CREATE TABLE txn_log (
timestamp INTEGER NOT NULL,
operation TEXT NOT NULL
) STRICT;
|
