DatabaseCreation: DDL_cooksy.sql

File DDL_cooksy.sql, 8.2 KB (added by 231118, 3 weeks ago)
Line 
1-- ===== LOOKUP TABLES =====
2CREATE TABLE role (
3 id SERIAL PRIMARY KEY,
4 name VARCHAR(50) NOT NULL UNIQUE
5);
6
7CREATE TABLE cuisine (
8 id SERIAL PRIMARY KEY,
9 name VARCHAR(100) NOT NULL UNIQUE
10);
11
12CREATE TABLE category (
13 id SERIAL PRIMARY KEY,
14 name VARCHAR(100) NOT NULL UNIQUE
15);
16
17CREATE TABLE tags (
18 id SERIAL PRIMARY KEY,
19 text VARCHAR(100) NOT NULL UNIQUE
20);
21
22CREATE TABLE allergen (
23 id SERIAL PRIMARY KEY,
24 name VARCHAR(100) NOT NULL UNIQUE
25);
26
27CREATE TABLE ingredient (
28 id SERIAL PRIMARY KEY,
29 name VARCHAR(100) NOT NULL UNIQUE
30);
31
32-- ===== USER =====
33CREATE TABLE "user" (
34 id SERIAL PRIMARY KEY,
35 username VARCHAR(100) NOT NULL UNIQUE,
36 email VARCHAR(255) NOT NULL UNIQUE
37 CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
38 password VARCHAR(255) NOT NULL
39 CHECK (
40 password ~ '[A-Z]' AND
41 password ~ '[a-z]' AND
42 password ~ '[0-9]' AND
43 length(password) >= 8
44 ),
45 created_at TIMESTAMP NOT NULL DEFAULT NOW()
46);
47
48CREATE TABLE user_role (
49 user_id INT NOT NULL,
50 role_id INT NOT NULL,
51 PRIMARY KEY (user_id, role_id),
52
53 FOREIGN KEY (user_id)
54 REFERENCES "user"(id)
55 ON DELETE CASCADE
56 ON UPDATE CASCADE,
57
58 FOREIGN KEY (role_id)
59 REFERENCES role(id)
60 ON DELETE RESTRICT -- da ne mozhe da se izbrishe role ako se koristi
61 ON UPDATE CASCADE
62);
63
64CREATE TABLE user_allergen (
65 user_id INT NOT NULL,
66 allergen_id INT NOT NULL,
67 PRIMARY KEY (user_id, allergen_id),
68
69 FOREIGN KEY (user_id)
70 REFERENCES "user"(id)
71 ON DELETE CASCADE
72 ON UPDATE CASCADE,
73
74 FOREIGN KEY (allergen_id)
75 REFERENCES allergen(id)
76 ON DELETE RESTRICT
77 ON UPDATE CASCADE
78);
79
80-- ===== PREFERENCES =====
81CREATE TABLE preferences (
82 id SERIAL PRIMARY KEY,
83 user_id INT NOT NULL UNIQUE,
84 calorie_limit INT CHECK (calorie_limit >= 0),
85 spice_level INT CHECK (spice_level BETWEEN 1 AND 5),
86 max_proteins INT CHECK (max_proteins >= 0),
87 max_cooking_time INT CHECK (max_cooking_time >= 0),
88
89 FOREIGN KEY (user_id)
90 REFERENCES "user"(id)
91 ON DELETE CASCADE
92 ON UPDATE CASCADE
93);
94
95CREATE TABLE preferences_cuisine (
96 preferences_id INT NOT NULL,
97 cuisine_id INT NOT NULL,
98 PRIMARY KEY (preferences_id, cuisine_id),
99
100 FOREIGN KEY (preferences_id)
101 REFERENCES preferences(id)
102 ON DELETE CASCADE
103 ON UPDATE CASCADE,
104
105 FOREIGN KEY (cuisine_id)
106 REFERENCES cuisine(id)
107 ON DELETE RESTRICT
108 ON UPDATE CASCADE
109);
110
111-- ===== RECIPE =====
112CREATE TABLE recipe (
113 id SERIAL PRIMARY KEY,
114 name VARCHAR(255) NOT NULL,
115 description TEXT,
116 image VARCHAR(500),
117 servings INT CHECK (servings > 0),
118 proteins INT CHECK (proteins >= 0),
119 fat INT CHECK (fat >= 0),
120 carbs INT CHECK (carbs >= 0),
121 spice_level INT CHECK (spice_level BETWEEN 1 AND 5),
122 total_time INT CHECK (total_time >= 0),
123 calories INT CHECK (calories >= 0),
124 created_at TIMESTAMP NOT NULL DEFAULT NOW(),
125 cuisine_id INT,
126
127 FOREIGN KEY (cuisine_id)
128 REFERENCES cuisine(id)
129 ON DELETE SET NULL
130 ON UPDATE CASCADE
131);
132
133CREATE TABLE recipe_category (
134 recipe_id INT NOT NULL,
135 category_id INT NOT NULL,
136 PRIMARY KEY (recipe_id, category_id),
137
138 FOREIGN KEY (recipe_id)
139 REFERENCES recipe(id)
140 ON DELETE CASCADE
141 ON UPDATE CASCADE,
142
143 FOREIGN KEY (category_id)
144 REFERENCES category(id)
145 ON DELETE RESTRICT
146 ON UPDATE CASCADE
147);
148
149CREATE TABLE recipe_tags (
150 recipe_id INT NOT NULL,
151 tag_id INT NOT NULL,
152 PRIMARY KEY (recipe_id, tag_id),
153
154 FOREIGN KEY (recipe_id)
155 REFERENCES recipe(id)
156 ON DELETE CASCADE
157 ON UPDATE CASCADE,
158
159 FOREIGN KEY (tag_id)
160 REFERENCES tags(id)
161 ON DELETE RESTRICT
162 ON UPDATE CASCADE
163);
164
165CREATE TABLE recipe_ingredient (
166 recipe_id INT NOT NULL,
167 ingredient_id INT NOT NULL,
168 quantity VARCHAR(100),
169
170 PRIMARY KEY (recipe_id, ingredient_id),
171
172 FOREIGN KEY (recipe_id)
173 REFERENCES recipe(id)
174 ON DELETE CASCADE
175 ON UPDATE CASCADE,
176
177 FOREIGN KEY (ingredient_id)
178 REFERENCES ingredient(id)
179 ON DELETE RESTRICT
180 ON UPDATE CASCADE
181);
182
183CREATE TABLE ingredient_allergen (
184 ingredient_id INT NOT NULL,
185 allergen_id INT NOT NULL,
186 PRIMARY KEY (ingredient_id, allergen_id),
187
188 FOREIGN KEY (ingredient_id)
189 REFERENCES ingredient(id)
190 ON DELETE CASCADE
191 ON UPDATE CASCADE,
192
193 FOREIGN KEY (allergen_id)
194 REFERENCES allergen(id)
195 ON DELETE RESTRICT
196 ON UPDATE CASCADE
197);
198
199-- ===== INSTRUCTIONS =====
200CREATE TABLE instruction (
201 id SERIAL PRIMARY KEY,
202 recipe_id INT NOT NULL,
203 step_number INT NOT NULL CHECK (step_number > 0),
204 text TEXT NOT NULL,
205
206 UNIQUE (recipe_id, step_number),
207
208 FOREIGN KEY (recipe_id)
209 REFERENCES recipe(id)
210 ON DELETE CASCADE
211 ON UPDATE CASCADE
212);
213
214CREATE TABLE instruction_media (
215 id SERIAL PRIMARY KEY,
216 instruction_id INT NOT NULL,
217 url VARCHAR(500) NOT NULL,
218 type VARCHAR(20) NOT NULL CHECK (type IN ('image', 'video')),
219 created_at TIMESTAMP DEFAULT NOW(),
220
221 FOREIGN KEY (instruction_id)
222 REFERENCES instruction(id)
223 ON DELETE CASCADE
224 ON UPDATE CASCADE
225);
226
227-- ===== FORUM =====
228CREATE TABLE forumpost (
229 id SERIAL PRIMARY KEY,
230 user_id INT NOT NULL,
231 recipe_id INT,
232 created_at TIMESTAMP NOT NULL DEFAULT NOW(),
233
234 FOREIGN KEY (user_id)
235 REFERENCES "user"(id)
236 ON DELETE CASCADE
237 ON UPDATE CASCADE,
238
239 FOREIGN KEY (recipe_id)
240 REFERENCES recipe(id)
241 ON DELETE SET NULL
242 ON UPDATE CASCADE
243);
244
245CREATE TABLE forumpost_like (
246 user_id INT NOT NULL,
247 forumpost_id INT NOT NULL,
248 PRIMARY KEY (user_id, forumpost_id),
249
250 FOREIGN KEY (user_id)
251 REFERENCES "user"(id)
252 ON DELETE CASCADE
253 ON UPDATE CASCADE,
254
255 FOREIGN KEY (forumpost_id)
256 REFERENCES forumpost(id)
257 ON DELETE CASCADE
258 ON UPDATE CASCADE
259);
260
261CREATE TABLE forumpost_comment (
262 id SERIAL PRIMARY KEY,
263 user_id INT NOT NULL,
264 forumpost_id INT NOT NULL,
265 parent_comment_id INT CHECK (parent_comment_id IS NULL OR parent_comment_id != id),
266 content TEXT NOT NULL,
267 created_at TIMESTAMP NOT NULL DEFAULT NOW(),
268
269 FOREIGN KEY (user_id)
270 REFERENCES "user"(id)
271 ON DELETE CASCADE
272 ON UPDATE CASCADE,
273
274 FOREIGN KEY (forumpost_id)
275 REFERENCES forumpost(id)
276 ON DELETE CASCADE
277 ON UPDATE CASCADE,
278
279 FOREIGN KEY (parent_comment_id)
280 REFERENCES forumpost_comment(id)
281 ON DELETE CASCADE
282 ON UPDATE CASCADE
283);
284
285-- ===== USER-RECIPE ACTIONS =====
286CREATE TABLE recipe_favorite (
287 user_id INT NOT NULL,
288 recipe_id INT NOT NULL,
289 PRIMARY KEY (user_id, recipe_id),
290
291 FOREIGN KEY (user_id)
292 REFERENCES "user"(id)
293 ON DELETE CASCADE
294 ON UPDATE CASCADE,
295
296 FOREIGN KEY (recipe_id)
297 REFERENCES recipe(id)
298 ON DELETE CASCADE
299 ON UPDATE CASCADE
300);
301
302CREATE TABLE recipe_attempt (
303 user_id INT NOT NULL,
304 recipe_id INT NOT NULL,
305 attempted_at TIMESTAMP DEFAULT NOW(),
306
307 PRIMARY KEY (user_id, recipe_id),
308
309 FOREIGN KEY (user_id)
310 REFERENCES "user"(id)
311 ON DELETE CASCADE
312 ON UPDATE CASCADE,
313
314 FOREIGN KEY (recipe_id)
315 REFERENCES recipe(id)
316 ON DELETE CASCADE
317 ON UPDATE CASCADE
318);
319
320CREATE TABLE recipe_review (
321 id SERIAL PRIMARY KEY,
322 user_id INT NOT NULL,
323 recipe_id INT NOT NULL,
324 rating INT CHECK (rating BETWEEN 1 AND 5),
325 comment TEXT,
326 created_at TIMESTAMP NOT NULL DEFAULT NOW(),
327
328 UNIQUE (user_id, recipe_id),
329
330 FOREIGN KEY (user_id, recipe_id)
331 REFERENCES recipe_attempt(user_id, recipe_id)
332 ON DELETE CASCADE ON UPDATE CASCADE
333);
334
335CREATE TABLE recipe_view (
336 user_id INT NOT NULL,
337 recipe_id INT NOT NULL,
338 viewed_at TIMESTAMP NOT NULL DEFAULT NOW(),
339
340 PRIMARY KEY (user_id, recipe_id, viewed_at),
341
342 FOREIGN KEY (user_id)
343 REFERENCES "user"(id)
344 ON DELETE CASCADE
345 ON UPDATE CASCADE,
346
347 FOREIGN KEY (recipe_id)
348 REFERENCES recipe(id)
349 ON DELETE CASCADE
350 ON UPDATE CASCADE
351);
352
353CREATE TABLE recipe_dislike (
354 user_id INT NOT NULL,
355 recipe_id INT NOT NULL,
356 PRIMARY KEY (user_id, recipe_id),
357
358 FOREIGN KEY (user_id)
359 REFERENCES "user"(id)
360 ON DELETE CASCADE
361 ON UPDATE CASCADE,
362
363 FOREIGN KEY (recipe_id)
364 REFERENCES recipe(id)
365 ON DELETE CASCADE
366 ON UPDATE CASCADE
367);