· 8 years ago · Apr 04, 2018, 12:16 AM
1# A6: Indices, triggers, user functions and population
2SegFault is a collaborative platform for programmers to learn, discuss different approaches, present ideas and share knowledge in a Q&A style.
3
4To this end, the following sections provide detailed insight into the inner workings of the project's database. The first section depicts the expected workload on the system, the second section specifies and explains the proposed indices to the database, and the third section comprises the database's triggers.
5
6## 1. Database Workload
7
8> A study of the predicted system load (database load), organized in subsections.
9
10### 1.1. Tuple Estimation
11
12> Estimate of tuples at each relation.
13
14
15| Relation reference | Relation Name | Order of magnitude | Estimated growth |
16| ------------------ | ------------- | ------------------------- | ---------------- |
17| R01 | Table1 | units|dozens|hundreds|etc | order per time |
18| R02 | Table2 | units|dozens|hundreds|etc | dozens per month |
19| R03 | Table3 | units|dozens|hundreds|etc | hundreds per day |
20| R04 | Table4 | units|dozens|hundreds|etc | no growth |
21
22
23### 1.2. Frequent Queries
24
25> Most important queries (SELECT) and their frequency.
26
27| Query reference | SELECT01 |
28| Query description | One sentence describing the query goal |
29| Query frequency | magnitude per time |
30| ----------------- | -------------------------------------- |
31| SQL code |
32
33
34### 1.3. Frequent Updates
35
36> Most important updates (INSERT, UPDATE, DELETE) and their frequency.
37
38| Query reference | UPDATE01 |
39| Query description | One sentence describing the query goal |
40| Query frequency | magnitude per time |
41| ----------------- | -------------------------------------- |
42| SQL code |
43
44
45## 2. Proposed Indices
46This section presents the proposed indices on the database. It is important to note that many indices, mainly on high cardinality, would theoretically be better off being implemented as hash indices. We purposefuly did not choose these, because the PostgreSQL documentation actively discourages the usage of hash indices, as seen on the warning below.
47
48
49
50
51### 2.1. Performance Indices
52
53| | |
54| --------------- | --------------------------------------------------- |
55| Index reference | IDX01 |
56| Related queries | SELECT01 |
57| Index relation | comment |
58| Index attribute | commentable_id |
59| Index type | B-tree |
60| Cardinality | medium |
61| Clustering | yes |
62| Justification | The Table is very large, and query SELECT01 must run efficiently as it is executed several times. It doesn't need range query support, and is a good candidate for clustering as its cardinality is medium. |
63```sql
64CREATE INDEX comment_commentable ON comment USING btree(commentable_id);
65```
66(This index could be implemented as a hash index, but, as explained in this section's introduction, this is actively discouraged.)
67
68| | |
69| --------------- | --------------------------------------------------- |
70| Index reference | IDX02 |
71| Related queries | SELECT01, SELECT02, SELECT04, SELECT06, SELECT07 |
72| Index relation | message_version |
73| Index attribute | message_id |
74| Index type | B-tree |
75| Cardinality | medium |
76| Clustering | yes |
77| Justification | The Table is very large, and the corresponding queries are abundant and recurrent, thus must run efficiently. It doesn't need range query support, and is a good candidate for clustering as its cardinality is medium. |
78```sql
79CREATE INDEX message_version_message ON message_version USING btree(message_id);
80```
81
82
83
84
85### 2.2. Full-text Search Indices
86
87| Index reference | IDX01 |
88| Related queries | SELECT01, ... |
89| Index relation | Relation where the index is applied |
90| Index attribute | Attribute where the index is applied |
91| Index type | B-tree, Hash, GiST or GIN |
92| Clustering | Clustering of the index |
93| Justification | Justification for the proposed index |
94
95
96### 2.3. Contraint-enforcing Indices
97
98
99
100## 3. Triggers
101
102> User-defined functions and trigger procedures that add control structures to the SQL language or perform complex computations, are identified and described to be trusted by the database server. Every kind of function (SQL functions, Stored procedures, Trigger procedures) can take base types, composite types, or combinations of these as arguments (parameters). In addition, every kind of function can return a base type or a composite type. Functions can also be defined to return sets of base or composite values.
103
104| Trigger reference | TRIGGER01 |
105| Trigger description | A message is banned if the amount of reports exceeds the limit define in BR08 |
106| ------------------- | ----------------------------------------------------------------------- |
107```sql
108 CREATE FUNCTION ban_message() RETURNS TRIGGER AS $$
109 BEGIN
110 UPDATE message
111 SET is_banned = TRUE
112 WHERE NEW.id = message.id;
113 RETURN NEW;
114 END;
115 $$ LANGUAGE plpgsql;
116
117 CREATE TRIGGER ban_message
118 AFTER UPDATE OF num_reports ON message
119 FOR EACH ROW
120 WHEN ( NEW.num_reports >= 5 + NEW.score^(1/3) )
121 EXECUTE PROCEDURE ban_message();
122```
123
124| Trigger reference | TRIGGER02 |
125| Trigger description | An answer can only be marked as correct if it's an answer of that question |
126| ------------------- | ----------------------------------------------------------------------- |
127```sql
128 CREATE FUNCTION check_correct() RETURNS TRIGGER AS $$
129 BEGIN
130 IF NEW.correct_answer IS NOT NULL AND
131 NOT EXISTS (SELECT * FROM answer WHERE NEW.correct_answer = id AND NEW.id = question_id) THEN
132 RAISE EXCEPTION 'An answer can only be marked as correct if it is an answer of the question';
133 END IF;
134 RETURN NEW;
135 END;
136 $$ LANGUAGE plpgsql;
137
138 CREATE TRIGGER check_correct
139 BEFORE UPDATE OF correct_answer ON question
140 FOR EACH ROW EXECUTE PROCEDURE check_correct();
141```
142
143| Trigger reference | TRIGGER03 |
144| Trigger description | A question must have between 1 and 5 categories |
145| ------------------- | ----------------------------------------------------------------------- |
146```sql
147 CREATE FUNCTION check_categories() RETURNS TRIGGER AS $$
148 DECLARE num_categories SMALLINT;
149 DECLARE current RECORD;
150 BEGIN
151 IF TG_OP = 'INSERT' THEN
152 current = NEW;
153 ELSE
154 current = OLD;
155 END IF;
156 SELECT INTO num_categories count(*)
157 FROM question_category
158 WHERE current.question_id = question_category.question_id;
159 IF num_categories > 5 THEN
160 RAISE EXCEPTION 'A question can only have a maximum of 5 categories';
161 ELSIF num_categories < 1 THEN
162 RAISE EXCEPTION 'A question must have at least 1 category';
163 END IF;
164 RETURN NEW;
165 END;
166 $$ LANGUAGE plpgsql;
167
168 CREATE TRIGGER check_categories
169 AFTER INSERT OR DELETE ON question_category
170 FOR EACH ROW EXECUTE PROCEDURE check_categories();
171```
172
173| Trigger reference | TRIGGER04 |
174| Trigger description | Update the number of posts a category is tagged in when another one is inserted |
175| ------------------- | ----------------------------------------------------------------------- |
176```sql
177 CREATE FUNCTION insert_category() RETURNS TRIGGER AS $$
178 BEGIN
179 UPDATE category
180 SET num_posts = num_posts + 1
181 WHERE NEW.category_id = category.id;
182 RETURN NEW;
183 END;
184 $$ LANGUAGE plpgsql;
185
186 CREATE TRIGGER insert_category
187 AFTER INSERT ON question_category
188 FOR EACH ROW EXECUTE PROCEDURE insert_category();
189```
190
191| Trigger reference | TRIGGER05 |
192| Trigger description | Update the message's score once a vote is modified |
193| ------------------- | ----------------------------------------------------------------------- |
194```sql
195 CREATE FUNCTION update_score_vote() RETURNS TRIGGER AS $$
196 BEGIN
197 IF NEW.positive AND NOT OLD.positive THEN
198 UPDATE message
199 SET score = score + 2
200 WHERE NEW.message_id = message.id;
201 ELSIF NOT NEW.positive AND OLD.positive THEN
202 UPDATE message
203 SET score = score - 2
204 WHERE NEW.message_id = message.id;
205 END IF;
206 RETURN NEW;
207 END;
208 $$ LANGUAGE plpgsql;
209
210 CREATE TRIGGER update_score_vote
211 BEFORE UPDATE ON Vote
212 FOR EACH ROW EXECUTE PROCEDURE update_score_vote();
213```
214
215| Trigger reference | TRIGGER06 |
216| Trigger description | Update the message's score once a vote is inserted |
217| ------------------- | ----------------------------------------------------------------------- |
218```sql
219 CREATE FUNCTION insert_score_vote() RETURNS TRIGGER AS $$
220 BEGIN
221 IF NEW.positive THEN
222 UPDATE message
223 SET score = score + 1
224 WHERE NEW.message_id = message.id;
225 ELSIF NOT NEW.positive THEN
226 UPDATE message
227 SET score = score - 1
228 WHERE NEW.message_id = message.id;
229 END IF;
230 RETURN NEW;
231 END;
232 $$ LANGUAGE plpgsql;
233
234 CREATE TRIGGER insert_score_vote
235 BEFORE INSERT ON Vote
236 FOR EACH ROW EXECUTE PROCEDURE insert_score_vote();
237```
238
239| Trigger reference | TRIGGER07 |
240| Trigger description | Update the message's score once a vote is deleted |
241| ------------------- | ----------------------------------------------------------------------- |
242```sql
243 CREATE FUNCTION delete_score_vote() RETURNS TRIGGER AS $$
244 BEGIN
245 IF OLD.positive THEN
246 UPDATE message
247 SET score = score - 1
248 WHERE OLD.message_id = message.id;
249 ELSIF NOT OLD.positive THEN
250 UPDATE message
251 SET score = score + 1
252 WHERE OLD.message_id = message.id;
253 END IF;
254 RETURN NEW;
255 END;
256 $$ LANGUAGE plpgsql;
257
258 CREATE TRIGGER delete_score_vote
259 BEFORE DELETE ON Vote
260 FOR EACH ROW EXECUTE PROCEDURE delete_score_vote();
261```
262
263| Trigger reference | TRIGGER08 |
264| Trigger description | Update a user's reputation when one of its messages is reported as defined in BR03 |
265| ------------------- | ----------------------------------------------------------------------- |
266```sql
267 CREATE FUNCTION update_reputation_reports() RETURNS TRIGGER AS $$
268 BEGIN
269 UPDATE "user"
270 SET reputation = reputation - (NEW.num_reports - OLD.num_reports)*10
271 WHERE NEW.author = "user".id;
272 RETURN NEW;
273 END;
274 $$ LANGUAGE plpgsql;
275
276 CREATE TRIGGER update_reputation_reports
277 BEFORE UPDATE OF num_reports ON message
278 FOR EACH ROW EXECUTE PROCEDURE update_reputation_reports();
279```
280
281| Trigger reference | TRIGGER09 |
282| Trigger description | Update a user's reputation when one of its messages is voted by another user as defined in BR03 |
283| ------------------- | ----------------------------------------------------------------------- |
284```sql
285 CREATE FUNCTION update_reputation_scores() RETURNS TRIGGER AS $$
286 BEGIN
287 IF EXISTS (SELECT * FROM commentable WHERE NEW.id = commentable.id) THEN
288 UPDATE "user"
289 SET reputation = reputation + (NEW.score - OLD.score)
290 WHERE NEW.author = "user".id;
291 ELSIF EXISTS (SELECT * FROM comment WHERE NEW.id = comment.id) THEN
292 UPDATE "user"
293 SET reputation = reputation + (NEW.score - OLD.score)/2.0
294 WHERE NEW.author = "user".id;
295 END IF;
296 RETURN NEW;
297 END;
298 $$ LANGUAGE plpgsql;
299
300 CREATE TRIGGER update_reputation_scores
301 BEFORE UPDATE OF score ON message
302 FOR EACH ROW EXECUTE PROCEDURE update_reputation_scores();
303```
304
305| Trigger reference | TRIGGER10 |
306| Trigger description | A user is awarded a "trusted" badge when they've correctly answered at least 50 questions |
307| ------------------- | ----------------------------------------------------------------------- |
308```sql
309 CREATE FUNCTION award_trusted() RETURNS TRIGGER AS $$
310 DECLARE answer_author BIGINT;
311 DECLARE trusted_id SMALLINT;
312 DECLARE num_correct_answers INTEGER;
313 BEGIN
314 SELECT INTO answer_author author
315 FROM message
316 WHERE message.id = NEW.correct_answer;
317 SELECT INTO trusted_id id FROM trusted_badge;
318 IF NOT EXISTS
319 (SELECT *
320 FROM badge_attainment
321 WHERE answer_author = badge_attainment.user_id AND trusted_id = badge_attainment.badge_id)
322 THEN
323 SELECT INTO num_correct_answers count(*)
324 FROM message, question
325 WHERE message.id = question.correct_answer AND message.author = answer_author;
326 IF num_correct_answers >= 50 THEN
327 INSERT INTO badge_attainment (user_id, badge_id) VALUES (answer_author, trusted_id);
328 END IF;
329 END IF;
330 RETURN NEW;
331 END;
332 $$ LANGUAGE plpgsql;
333
334 CREATE TRIGGER award_trusted
335 AFTER UPDATE OF correct_answer ON question
336 FOR EACH ROW EXECUTE PROCEDURE award_trusted();
337```
338
339| Trigger reference | TRIGGER11 |
340| Trigger description | A user is awarded a "moderator" badge when they've been awarded the "trusted" badge and then achieved at least 500 reputation points |
341| ------------------- | ----------------------------------------------------------------------- |
342```sql
343 CREATE FUNCTION award_moderator_reputation() RETURNS TRIGGER AS $$
344 DECLARE moderator_id SMALLINT;
345 DECLARE trusted_id SMALLINT;
346 BEGIN
347 SELECT INTO moderator_id id FROM moderator_badge;
348 SELECT INTO trusted_id id FROM trusted_badge;
349 IF NOT EXISTS
350 (SELECT *
351 FROM badge_attainment
352 WHERE NEW.id = badge_attainment.user_id AND moderator_id = badge_attainment.badge_id)
353 AND EXISTS
354 (SELECT *
355 FROM badge_attainment
356 WHERE NEW.id = badge_attainment.user_id AND trusted_id = badge_attainment.badge_id)
357 AND NEW.reputation >= 500 THEN
358 INSERT INTO badge_attainment (user_id, badge_id) VALUES (NEW.id, moderator_id);
359 INSERT INTO moderator (id) VALUES (NEW.id);
360 END IF;
361 RETURN NEW;
362 END;
363 $$ LANGUAGE plpgsql;
364
365 CREATE TRIGGER award_moderator_reputation
366 AFTER UPDATE OF reputation ON "user"
367 FOR EACH ROW EXECUTE PROCEDURE award_moderator_reputation();
368```
369
370| Trigger reference | TRIGGER12 |
371| Trigger description | A user is awarded a "moderator" badge when they've achieved at least 500 reputation points and then were awarded the "trusted" badge |
372| ------------------- | ----------------------------------------------------------------------- |
373```sql
374 CREATE FUNCTION award_moderator_trusted() RETURNS TRIGGER AS $$
375 DECLARE moderator_id SMALLINT;
376 DECLARE trusted_id SMALLINT;
377 DECLARE rep REAL;
378 BEGIN
379 SELECT INTO moderator_id id FROM moderator_badge;
380 SELECT INTO trusted_id id FROM trusted_badge;
381 SELECT INTO rep reputation FROM "user" WHERE "user".id = NEW.user_id;
382 IF NEW.badge_id = trusted_id
383 AND NOT EXISTS
384 (SELECT *
385 FROM badge_attainment
386 WHERE NEW.user_id = badge_attainment.user_id AND moderator_id = badge_attainment.badge_id)
387 AND rep >= 500 THEN
388 INSERT INTO badge_attainment (user_id, badge_id) VALUES (NEW.user_id, moderator_id);
389 INSERT INTO moderator (id) VALUES (NEW.user_id);
390 END IF;
391 RETURN NEW;
392 END;
393 $$ LANGUAGE plpgsql;
394
395 CREATE TRIGGER award_moderator_trusted
396 AFTER INSERT ON badge_attainment
397 FOR EACH ROW EXECUTE PROCEDURE award_moderator_trusted();
398```
399
400| Trigger reference | TRIGGER13 |
401| Trigger description | A user can't vote their own messages as stated in BR02 |
402| ------------------- | ----------------------------------------------------------------------- |
403```sql
404 CREATE FUNCTION check_own_vote() RETURNS TRIGGER AS $$
405 DECLARE message_author BIGINT;
406 BEGIN
407 SELECT INTO message_author author
408 FROM message
409 WHERE message.id = NEW.message_id;
410 IF message_author = NEW.user_id THEN
411 RAISE EXCEPTION 'A user is not allowed to vote their own messages';
412 END IF;
413 RETURN NEW;
414 END;
415 $$ LANGUAGE plpgsql;
416
417 CREATE TRIGGER check_own_vote
418 BEFORE INSERT ON Vote
419 FOR EACH ROW EXECUTE PROCEDURE check_own_vote();
420```
421
422| Trigger reference | TRIGGER14 |
423| Trigger description | Update the number of reports in a message when one is made to it |
424| ------------------- | ----------------------------------------------------------------------- |
425```sql
426 CREATE FUNCTION insert_report() RETURNS TRIGGER AS $$
427 BEGIN
428 UPDATE message
429 SET num_reports = num_reports + 1
430 WHERE NEW.message_id = message.id;
431 RETURN NEW;
432 END;
433 $$ LANGUAGE plpgsql;
434
435 CREATE TRIGGER insert_report
436 BEFORE INSERT ON report
437 FOR EACH ROW EXECUTE PROCEDURE insert_report();
438```
439
440| Trigger reference | TRIGGER15 |
441| Trigger description | Update the number of reports in a message when one is removed |
442| ------------------- | ----------------------------------------------------------------------- |
443```sql
444 CREATE FUNCTION delete_report() RETURNS TRIGGER AS $$
445 BEGIN
446 UPDATE message
447 SET num_reports = num_reports - 1
448 WHERE NEW.message_id = message.id;
449 RETURN NEW;
450 END;
451 $$ LANGUAGE plpgsql;
452
453 CREATE TRIGGER delete_report
454 BEFORE DELETE ON report
455 FOR EACH ROW EXECUTE PROCEDURE delete_report();
456```
457
458| Trigger reference | TRIGGER16 |
459| Trigger description | A comment made to commentable item generates a notification towards the author of said commentable item |
460| ------------------- | ----------------------------------------------------------------------- |
461```sql
462 CREATE FUNCTION gen_comment_notification() RETURNS TRIGGER AS $$
463 DECLARE current_id BIGINT;
464 DECLARE notified_user BIGINT;
465 BEGIN
466 SELECT INTO current_id nextval(pg_get_serial_sequence('notification', 'id'));
467 SELECT INTO notified_user author FROM message WHERE message.id = NEW.commentable_id;
468 INSERT INTO notification (id, user_id) VALUES (current_id, notified_user);
469 INSERT INTO commentable_notification (id, notified_msg, trigger_msg) VALUES (current_id, NEW.commentable_id, NEW.id);
470 RETURN NEW;
471 END;
472 $$ LANGUAGE plpgsql;
473
474 CREATE TRIGGER gen_comment_notification
475 AFTER INSERT ON comment
476 FOR EACH ROW EXECUTE PROCEDURE gen_comment_notification();
477```
478
479| Trigger reference | TRIGGER17 |
480| Trigger description | An answer to a question generates a notification towards the author of the question |
481| ------------------- | ----------------------------------------------------------------------- |
482```sql
483 CREATE FUNCTION gen_answer_notification() RETURNS TRIGGER AS $$
484 DECLARE current_id BIGINT;
485 DECLARE notified_user BIGINT;
486 BEGIN
487 SELECT INTO current_id nextval(pg_get_serial_sequence('notification', 'id'));
488 SELECT INTO notified_user author FROM message WHERE message.id = NEW.question_id;
489 INSERT INTO notification (id, user_id) VALUES (current_id, notified_user);
490 INSERT INTO commentable_notification (id, notified_msg, trigger_msg) VALUES (current_id, NEW.question_id, NEW.id);
491 RETURN NEW;
492 END;
493 $$ LANGUAGE plpgsql;
494
495 CREATE TRIGGER gen_answer_notification
496 AFTER INSERT ON answer
497 FOR EACH ROW EXECUTE PROCEDURE gen_answer_notification();
498```
499
500| Trigger reference | TRIGGER18 |
501| Trigger description | When a badge is awarded to a user a notification to that user is generated |
502| ------------------- | ----------------------------------------------------------------------- |
503```sql
504 CREATE FUNCTION gen_badge_notification() RETURNS TRIGGER AS $$
505 DECLARE current_id BIGINT;
506 BEGIN
507 SELECT INTO current_id nextval(pg_get_serial_sequence('notification', 'id'));
508 INSERT INTO notification (id, user_id) VALUES (current_id, NEW.user_id);
509 INSERT INTO badge_notification (id, badge_id) VALUES (current_id, NEW.badge_id);
510 RETURN NEW;
511 END;
512 $$ LANGUAGE plpgsql;
513
514 CREATE TRIGGER gen_badge_notification
515 AFTER INSERT ON badge_attainment
516 FOR EACH ROW EXECUTE PROCEDURE gen_badge_notification();
517```
518
519
520## 4. Complete SQL Code
521
522> The database script must also include the SQL to populate a database with test data with an amount of tuples suitable for testing and with plausible values for the fields of the database.
523> This code should also be included in the group's github repository as an SQL script, and a link include here.
524
525
526## Revision history
527
528Changes made to the first submission:
5291. Item 1
5301. Item 2
531
532***
533
534GROUP1763, 03/04/2018
535
536> André Cruz, up201503776@fe.up.pt
537> Daniel Marques, up201503822@fe.up.pt
538> Edgar Carneiro, up201503784@fe.up.pt
539> João Carvalho, up201504875@fe.up.pt