source: sql/old/01_DDL_nutritioneer.sql

Last change on this file was 2e0035f, checked in by Acko <lavurovski.a65@…>, 8 days ago

Initial Commit
  • Property mode set to 100644
File size: 11.4 KB
Line 
1-- ---------------------------------------------------------------------
2-- Првобитни queries
3-- ---------------------------------------------------------------------
4DROP TABLE IF EXISTS intake_planner_save_to_list_post CASCADE;
5DROP TABLE IF EXISTS grocery_list_single_add_ingredient CASCADE;
6DROP TABLE IF EXISTS grocery_list_bulk_add_ingredient CASCADE;
7DROP TABLE IF EXISTS recipe_contains_restriction CASCADE;
8DROP TABLE IF EXISTS ingredient_contains_nutrient CASCADE;
9DROP TABLE IF EXISTS recipe_contains_ingredient CASCADE;
10DROP TABLE IF EXISTS biometrics CASCADE;
11DROP TABLE IF EXISTS intake_planner CASCADE;
12DROP TABLE IF EXISTS grocery_list CASCADE;
13DROP TABLE IF EXISTS comment CASCADE;
14DROP TABLE IF EXISTS post CASCADE;
15DROP TABLE IF EXISTS recipe CASCADE;
16DROP TABLE IF EXISTS restriction CASCADE;
17DROP TABLE IF EXISTS nutrient CASCADE;
18DROP TABLE IF EXISTS ingredient CASCADE;
19DROP TABLE IF EXISTS "user" CASCADE;
20
21DROP TYPE IF EXISTS post_status CASCADE;
22DROP TYPE IF EXISTS restriction_type CASCADE;
23DROP TYPE IF EXISTS nutrient_type CASCADE;
24DROP TYPE IF EXISTS nutrient_unit CASCADE;
25DROP TYPE IF EXISTS ingredient_type CASCADE;
26DROP TYPE IF EXISTS user_role CASCADE;
27
28
29
30CREATE TYPE user_role AS ENUM (
31 'user',
32 'administrator',
33 'trainer'
34);
35
36CREATE TYPE ingredient_type AS ENUM (
37 'dairy',
38 'meat',
39 'fish',
40 'herb',
41 'carb',
42 'vegetable',
43 'fruit',
44 'legume',
45 'nut',
46 'seed',
47 'oil',
48 'spice',
49 'beverage',
50 'other'
51);
52
53CREATE TYPE nutrient_unit AS ENUM (
54 'milligram',
55 'gram',
56 'microgram'
57);
58
59CREATE TYPE nutrient_type AS ENUM (
60 'macro',
61 'micro'
62);
63
64CREATE TYPE restriction_type AS ENUM (
65 'vegan',
66 'vegetarian',
67 'halal',
68 'kosher',
69 'pescatarian',
70 'allergen',
71 'keto',
72 'paleo',
73 'gluten_free',
74 'lactose_free',
75 'diabetic',
76 'low_sodium',
77 'other'
78);
79
80CREATE TYPE post_status AS ENUM (
81 'published',
82 'draft',
83 'archived'
84);
85
86
87
88CREATE TABLE "user" (
89 email TEXT PRIMARY KEY,
90 username TEXT NOT NULL,
91 password TEXT NOT NULL, -- BCrypt hash, never plaintext
92 role user_role NOT NULL DEFAULT 'user',
93
94 CONSTRAINT uq_user_username UNIQUE (username),
95 CONSTRAINT ck_user_email_format CHECK (email LIKE '%_@_%.__%'),
96 CONSTRAINT ck_user_username_len CHECK (length(username) BETWEEN 3 AND 40),
97 CONSTRAINT ck_user_password_len CHECK (length(password) >= 8)
98);
99
100CREATE TABLE ingredient (
101 name TEXT NOT NULL PRIMARY KEY,
102 description TEXT NOT NULL,
103 energy NUMERIC NOT NULL, -- kJ / 100 g
104 kcal NUMERIC NOT NULL, -- kcal / 100 g
105 type ingredient_type NOT NULL DEFAULT 'other',
106
107 CONSTRAINT ck_ingredient_energy CHECK (energy >= 0),
108 CONSTRAINT ck_ingredient_kcal CHECK (kcal >= 0),
109 CONSTRAINT ck_ingredient_name CHECK (length(trim(name)) > 0)
110);
111
112CREATE TABLE nutrient (
113 id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
114 quantity NUMERIC NOT NULL, -- reference daily intake
115 description TEXT NOT NULL, -- nutrient name, e.g. 'Protein'
116 unit nutrient_unit NOT NULL,
117 type nutrient_type NOT NULL,
118
119 CONSTRAINT uq_nutrient_description UNIQUE (description),
120 CONSTRAINT ck_nutrient_quantity CHECK (quantity > 0)
121);
122
123CREATE TABLE restriction (
124 id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
125 description TEXT NOT NULL,
126 type restriction_type NOT NULL,
127
128 CONSTRAINT uq_restriction_description UNIQUE (description)
129);
130
131
132CREATE TABLE recipe (
133 id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
134 name TEXT NOT NULL,
135 guide TEXT NOT NULL,
136 kcal_sum NUMERIC NOT NULL,
137 servings NUMERIC NOT NULL DEFAULT 1,
138 user_email TEXT NOT NULL REFERENCES "user"(email) ON DELETE CASCADE,
139
140 CONSTRAINT uq_recipe_owner_name UNIQUE (user_email, name),
141 CONSTRAINT ck_recipe_kcal_sum CHECK (kcal_sum >= 0),
142 CONSTRAINT ck_recipe_servings CHECK (servings > 0)
143);
144
145CREATE TABLE post (
146 id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
147 media BYTEA,
148 media_type TEXT,
149 media_name TEXT,
150 is_private BOOLEAN NOT NULL DEFAULT FALSE,
151 is_favourite BOOLEAN NOT NULL DEFAULT FALSE,
152 status post_status NOT NULL DEFAULT 'draft',
153 created_at TIMESTAMP NOT NULL DEFAULT NOW(),
154 created_by TEXT NOT NULL REFERENCES "user"(email) ON DELETE CASCADE,
155 recipe_id BIGINT REFERENCES recipe(id) ON DELETE SET NULL,
156
157 CONSTRAINT ck_post_media_type CHECK (media IS NULL OR media_type IS NOT NULL),
158
159 CONSTRAINT uq_post_recipe UNIQUE (recipe_id)
160);
161
162CREATE TABLE comment (
163 id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
164 description TEXT NOT NULL,
165 created_at TIMESTAMP NOT NULL DEFAULT NOW(),
166 post_id BIGINT NOT NULL REFERENCES post(id) ON DELETE CASCADE,
167 created_by TEXT NOT NULL REFERENCES "user"(email) ON DELETE CASCADE,
168
169 CONSTRAINT ck_comment_not_empty CHECK (length(trim(description)) > 0)
170);
171
172CREATE TABLE grocery_list (
173 id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
174 date_time TIMESTAMP NOT NULL DEFAULT NOW(),
175 notes TEXT,
176 is_bought BOOLEAN NOT NULL DEFAULT FALSE,
177 kcal NUMERIC NOT NULL,
178 user_email TEXT NOT NULL REFERENCES "user"(email) ON DELETE CASCADE,
179
180 CONSTRAINT ck_grocery_list_kcal CHECK (kcal >= 0)
181);
182
183CREATE TABLE intake_planner (
184 id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
185 date_time TIMESTAMP NOT NULL DEFAULT NOW(),
186 notes TEXT,
187 kcal NUMERIC NOT NULL,
188 is_consumed BOOLEAN NOT NULL DEFAULT FALSE,
189 user_email TEXT NOT NULL REFERENCES "user"(email) ON DELETE CASCADE,
190
191 CONSTRAINT ck_intake_planner_kcal CHECK (kcal >= 0)
192);
193
194
195CREATE TABLE biometrics (
196 id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
197 date DATE NOT NULL,
198 weight NUMERIC, -- kg
199 height NUMERIC, -- cm
200 age NUMERIC, -- years
201 muscle_fat_ratio NUMERIC, -- lean mass / fat mass
202 user_email TEXT NOT NULL REFERENCES "user"(email) ON DELETE CASCADE,
203
204 CONSTRAINT uq_biometrics_user_date UNIQUE (user_email, date),
205 CONSTRAINT ck_biometrics_weight CHECK (weight IS NULL OR weight BETWEEN 20 AND 400),
206 CONSTRAINT ck_biometrics_height CHECK (height IS NULL OR height BETWEEN 50 AND 260),
207 CONSTRAINT ck_biometrics_age CHECK (age IS NULL OR age BETWEEN 5 AND 120),
208 CONSTRAINT ck_biometrics_ratio CHECK (muscle_fat_ratio IS NULL OR muscle_fat_ratio > 0)
209);
210
211
212
213CREATE TABLE recipe_contains_ingredient (
214 recipe_id BIGINT NOT NULL REFERENCES recipe(id) ON DELETE CASCADE,
215 ingredient_name TEXT NOT NULL REFERENCES ingredient(name) ON DELETE CASCADE,
216 ingredient_quantity NUMERIC NOT NULL,
217 PRIMARY KEY (recipe_id, ingredient_name),
218
219 CONSTRAINT ck_rci_quantity CHECK (ingredient_quantity > 0)
220);
221
222
223CREATE TABLE ingredient_contains_nutrient (
224 ingredient_name TEXT NOT NULL REFERENCES ingredient(name) ON DELETE CASCADE,
225 nutrient_id BIGINT NOT NULL REFERENCES nutrient(id) ON DELETE CASCADE,
226 quantity NUMERIC NOT NULL, -- amount per 100 g, in nutrient.unit
227 PRIMARY KEY (ingredient_name, nutrient_id),
228
229 CONSTRAINT ck_icn_quantity CHECK (quantity >= 0)
230);
231
232
233CREATE TABLE recipe_contains_restriction (
234 recipe_id BIGINT NOT NULL REFERENCES recipe(id) ON DELETE CASCADE,
235 restriction_id BIGINT NOT NULL REFERENCES restriction(id) ON DELETE CASCADE,
236 PRIMARY KEY (recipe_id, restriction_id)
237);
238
239
240CREATE TABLE grocery_list_bulk_add_ingredient (
241 list_id BIGINT NOT NULL REFERENCES grocery_list(id) ON DELETE CASCADE,
242 recipe_id BIGINT NOT NULL REFERENCES recipe(id) ON DELETE CASCADE,
243 PRIMARY KEY (list_id, recipe_id)
244);
245
246
247CREATE TABLE grocery_list_single_add_ingredient (
248 list_id BIGINT NOT NULL REFERENCES grocery_list(id) ON DELETE CASCADE,
249 ingredient_name TEXT NOT NULL REFERENCES ingredient(name) ON DELETE CASCADE,
250 buy_quantity NUMERIC NOT NULL, -- grams to purchase
251 PRIMARY KEY (list_id, ingredient_name),
252
253 CONSTRAINT ck_glsai_quantity CHECK (buy_quantity > 0)
254);
255
256
257CREATE TABLE intake_planner_save_to_list_post (
258 planner_id BIGINT NOT NULL REFERENCES intake_planner(id) ON DELETE CASCADE,
259 post_id BIGINT NOT NULL REFERENCES post(id) ON DELETE CASCADE,
260 PRIMARY KEY (planner_id, post_id)
261);
262
263
264
265CREATE INDEX idx_recipe_user ON recipe(user_email);
266CREATE INDEX idx_post_user ON post(created_by);
267CREATE INDEX idx_post_recipe ON post(recipe_id);
268CREATE INDEX idx_post_feed ON post(status, created_at DESC);
269CREATE INDEX idx_comment_post ON comment(post_id);
270CREATE INDEX idx_comment_user ON comment(created_by);
271CREATE INDEX idx_grocery_list_user ON grocery_list(user_email);
272CREATE INDEX idx_intake_planner_user ON intake_planner(user_email, date_time);
273CREATE INDEX idx_biometrics_user ON biometrics(user_email, date DESC);
274CREATE INDEX idx_rci_ingredient ON recipe_contains_ingredient(ingredient_name);
275CREATE INDEX idx_icn_nutrient ON ingredient_contains_nutrient(nutrient_id);
276CREATE INDEX idx_rcr_restriction ON recipe_contains_restriction(restriction_id);
277CREATE INDEX idx_glbai_recipe ON grocery_list_bulk_add_ingredient(recipe_id);
278CREATE INDEX idx_glsai_ingredient ON grocery_list_single_add_ingredient(ingredient_name);
279CREATE INDEX idx_ipsp_post ON intake_planner_save_to_list_post(post_id);
280
281
282
283COMMENT ON TABLE "user" IS 'Every account of the system, regardless of role.';
284COMMENT ON COLUMN "user".password IS 'BCrypt hash of the password - never store plaintext.';
285COMMENT ON COLUMN ingredient.energy IS 'Energy in kilojoules per 100 g of edible portion.';
286COMMENT ON COLUMN ingredient.kcal IS 'Energy in kilocalories per 100 g of edible portion.';
287COMMENT ON COLUMN nutrient.quantity IS 'Reference daily intake (RDI) for an average adult.';
288COMMENT ON COLUMN recipe.kcal_sum IS 'Derived: SUM(ingredient.kcal * quantity / 100) for the WHOLE recipe.';
289COMMENT ON COLUMN recipe.servings IS 'Portions the recipe yields; kcal per portion = kcal_sum / servings.';
290COMMENT ON COLUMN post.recipe_id IS 'Recipe this post is about; NULL for a plain photo/note post.';
291COMMENT ON COLUMN ingredient_contains_nutrient.quantity
292 IS 'Amount of the nutrient per 100 g of the ingredient, in nutrient.unit.';
293COMMENT ON COLUMN recipe_contains_ingredient.ingredient_quantity
294 IS 'Grams of the ingredient used by the whole recipe.';
295
296
Note: See TracBrowser for help on using the repository browser.