Skip to content

Latest commit

 

History

History
6036 lines (4528 loc) · 115 KB

ebd.md

File metadata and controls

6036 lines (4528 loc) · 115 KB

EBD: Database Specification Component

A4: Conceptual Data Model

This section contains the description of the entities and relationships that exist to the onlyFEUP project and its database specification.

4.1 Class diagram

The UML diagram in Figure 6 presents the main organizational entities, the relationships between them, attributes and their domains, and the multiplicity of relationships for the OnlyFEUP platform.

UML Figure 6: OnlyFEUP conceptual data model in UML

4.2 Additional Business Rules

Additional business rules or restrictions that cannot be conveyed in the UML class diagram of OnlyFEUP’s system.

Identifier

Description
BR07

A user can only like a post once or like posts from groups to which they belong or like comment in posts from public users or users they follow
BR08

A user can only like a comment once
BR09

A user can only post to a group that they belong to
BR10

A user cannot follow themselves
BR11

In post, media and content cant both be null
BR12

A user can only comment on posts from public users, posts from users they follow or on posts from groups to which they belong
BR13

A group owner is also a member of your group
BR14

A user cannot request to follow a user that he/she already follow
BR15

A user cannot request to follow themselves
BR16

A user cannot request to join a group that he/she is already a part of

Table 13: Additional Business Rules

A5: Relational Schema, validation and schema refinement

This section contains the relational schema obtained from the conceptual data model by analyzing it. It shows each relational schema, attributes, domains, primary keys, foreign keys and other integrity rules necessary such as unique, default, not null and check.

5.1 Relational Schema

Relation reference

Relation Compact Notation
R01

users(id PK, username UK NN, password NN, email UK NN, name NN, is_public NN DF true, remember_token DF null)
R02

admin(id->users PK)
R03

groups(id PK, owner_id->users NN, name NN, is_public NN DF false, description)
R04

post(id PK, owner_id->users NN, group_id->groups, content, date NN CK date <= today, previous DF null)
R05

comment(id PK, author_id->users NN, post_id->post NN, content, date NN CK date <= today, previous DF null)
R06

blocked(id->users PK)
R07

notification(id PK, date NN CK date <= today, notified_user->users NN, emitter_user->users NN, viewed NN DF false)
R08

comment_notification(id->notification PK, comment_id->comment)
R09

user_notification(id->notification PK, notification_type NN CK notification_type IN user_notification_types)
R10

group_notification(id->notification PK, group_id->groups NN, notification_type NN CK notification_type IN group_notification_types)
R11

post_notification(id->notification PK, post_id->post NN, notification_type NN CK notification_type IN post_notification_types)
R12

member(user_id->users, group_id->groups, is_favorite NN DF false)
R13

follow_request(req_id->users, rcv_id->users)
R14

follows(follower_id->users, followed_id->users)
R15

post_likes(user_id->users, post_id->post)
R16

comment_likes(user_id->users, comment_id->comment)
R17

group_join_request(user_id->users, group_id->groups)
R18

configuration(user_id->users, notification_type NN, active NN DF true)
R19

message(id PK, emitter_id->users, receiver_id->users, content NN, date NN CK date <= today, viewed NN DF false)

Table 14: OnlyFEUP Relational Schema

Legend:

  • UK = UNIQUE;
  • NN = NOT NULL;
  • DF = DEFAULT;
  • CK = CHECK;

5.2 Domains

Specification of additional domains.

Domain Name

Domain Specification

today

 DATE DEFAULT CURRENT_DATE

user_notification_types

 ENUM (‘request_follow’, ‘started_following’, ‘accepted_follow’)

group_notification_types

 ENUM (‘request_join’, ‘joined_group’, ‘accept_join’)

post_notification_types

 ENUM (‘liked_post’, ‘comment_post’)

comment_notification_types

 ENUM (‘liked_comment’, ‘comment_post’, ‘reply_comment’, ‘comment_tagging’)

Table 15: OnlyFEUP Domains

5.3 Schema validation

All functional dependencies are identified and the normalization of all relation schemas is accomplished.

TABLE R01

users

Keys

 {id}

Functional Dependencies:
FD0101 {id} → {username, password, email, name, is_public}

NORMAL FORM

 BCNF

Table 16: user schema validation

TABLE R02

admin

Keys

 {id}

Functional Dependencies:

none

NORMAL FORM

 BCNF

Table 17: admin schema validation

TABLE R03

groups

Keys

 {id}

Functional Dependencies:
FD0301 {id} → {owner_id, name, is_public, description}

NORMAL FORM

 BCNF

Table 18: group schema validation

TABLE R04

post

Keys

 {id}

Functional Dependencies:
FD0401 {id} → {user_id, group_id, content, date}

NORMAL FORM

 BCNF

Table 19: post schema validation

TABLE R05

comment

Keys

 {id}

Functional Dependencies:
FD0501 {id} → {author_id, post_id, content, date}

NORMAL FORM

 BCNF

Table 20: comment schema validation

TABLE R06

blocked

Keys

 {id}

Functional Dependencies:

none

NORMAL FORM

 BCNF

Table 21: media schema validation

TABLE R07

notification

Keys

 {id}

Functional Dependencies:
FD0701 {id} → {date, notified_user, emitter_user, viewed}

NORMAL FORM

 BCNF

Table 22: notification schema validation

TABLE R08

comment_notification

Keys

 {id}

Functional Dependencies:
FD0801 {id} → {comment_id}

NORMAL FORM

 BCNF

Table 23: comment_notification schema validation

TABLE R09

user_notification

Keys

 {id}

Functional Dependencies:
FD0901 {id} → {notification_type}

NORMAL FORM

 BCNF

Table 24: user_notification schema validation

TABLE R10

group_notification

Keys

 {id}

Functional Dependencies:
FD1001 {id} → {group_id, notification_type}

NORMAL FORM

 BCNF

Table 25: group_notification schema validation

TABLE R11

post_notification

Keys

 {id}

Functional Dependencies:
FD1101 {id} → {post_id, notification_type}

NORMAL FORM

 BCNF

Table 26: post_notification schema validation

TABLE R12

member

Keys

 {user_id, group_id}

Functional Dependencies:
FD1201 {user_id, group_id} →{is_favorite}

NORMAL FORM

 BCNF

Table 27: member schema validation

TABLE R13

follow_request

Keys

 {req_id, rcv_id}

Functional Dependencies:

none

NORMAL FORM

 BCNF

Table 28: follow_request schema validation

TABLE R14

follows

Keys

 {follower_id, followed_id}

Functional Dependencies:

none

NORMAL FORM

 BCNF

Table 29: follows schema validation

TABLE R15

post_likes

Keys

 {user_id, post_id}

Functional Dependencies:

none

NORMAL FORM

 BCNF

Table 30: post_likes schema validation

TABLE R16

post_likes

Keys

 {user_id, post_id}

Functional Dependencies:

none

NORMAL FORM

 BCNF

Table 31: comment_likes schema validation

TABLE R17

group_join_request

Keys

 {user_id, group_id}

Functional Dependencies:

none

NORMAL FORM

 BCNF

Table 32: group_join_request schema validation

TABLE R18

configuration

Keys

 {id}

Functional Dependencies:
FD1801 {id} -> {user_id, notification_type, active}

NORMAL FORM

 BCNF

Table 33: configuration schema validation

TABLE R19

message

Keys

 {id}

Functional Dependencies:
FD1901 {id} -> {emitter_user, receiver_user, content, date, viewed}

NORMAL FORM

 BCNF

Table 34: message schema validation

Because all relations are in the Boyce–Codd Normal Form (BCNF), the relational schema is also in the BCNF and, therefore, the schema does not need to be further normalized.

A6: Indexes, triggers, transactions and database population

The A6 artifact contains the postgres sql code. In other words, It contains the physical schema of the database and its population, the support of data integrity rules with triggers, the identification and characterisation of the indexes and the definition of the database user-defined functions.

It also shows the transactions needed to ensure correctness of the data after accesses and/or changes to the database itself. Furthermore the isolation level is explained as required.

1. Database Workload

In order to develop a database with good design, it is essential to comprehend the growth of a table and how many times it will be accessed. The table below shows said predictions:

Identifier Relation Name Order of magnitude Estimated growth
RS01 user 10 k 10
RS02 admin 1 1
RS03 group 1 k 10
RS04
 post 10 k 1 k
RS05 comment 10 k 100
RS06 blocked 100 10
RS07 notification 1 k 1 k
RS08 comment_notification 100 100
RS09 user_notification 100 100
RS10 group_notification 100 100
RS11 post_notification 100 100
RS12 member 100 100
RS13 follow_request 10 10
RS14 follows 10 10
RS15 post_likes 1 k 1 k
RS16 comment_likes 1 k 1 k
RS17 group_join_request 10 10
RS18 configuration 10k 10
RS19 message 100k 1k

Table 35: OnlyFEUP workload

2. Proposed Indexes

We used Indexes to increase the database performance by letting It to find and retrieve specific rows in more rapidly ways. An index defined on a column that is part of a join condition can also significantly speed up queries with joins. Moreover, indexes can also benefit UPDATE and DELETE commands with search conditions.

2.1. Performance indexes

Some queries would normally take a long time to execute. With performance indexes, we can improve the performance of SELECT queries at the expense of increased execution time for INSERT, UPDATE and DELETE operations. Despite this, some tables can benefit from the increased speed in searches. The following tables show the performance indexes used:

Note that the first and second indexes relate to the same queries, the difference being that they relate to different columns in notification, as such they can be considered the same.

Index IDX01
Index relation notification
Index attribute notified_user
Index type B-tree
Cardinality Medium
Clustering Yes
Justification

Table ‘notification’ is very large. Several queries related to notifications in comments, posts and groups need to frequently find out the user notified. This is done by exact match, thus an hash type index would be best suited. However, since we also want to apply clustering based on this index, and clustering is not possible on hash type indexes, we opted for a b-tree index. Update frequency is low and cardinality is medium so it's a good candidate for clustering.
SQL code

CREATE INDEX notified_user_notification ON notification USING btree (notified_user);

CLUSTER notification USING notified_user_notification;

Table 36: Notified User Index

Index
IDX02
Index relation
notification
Index attributes
emitter_user
Index type
B-tree
Cardinality
Medium
Clustering
Yes
Justification

Table ‘notification’ is very large. Several queries related to notifications in comments, posts and groups need to frequently find out who made a notification . This is done by exact match, thus an hash type index would be best suited. However, since we also want to apply clustering based on this index, and clustering is not possible on hash type indexes, we opted for a b-tree index. Update frequency is low and cardinality is medium so it's a good candidate for clustering.
SQL code

CREATE INDEX emitter_user_notification ON notification USING btree (emitter_user);

CLUSTER notification USING emitter_user_notification;

Table 37: Emitter User Index

Index
IDX03
Index relation
 post
Index attributes
owner_id
Index type
 Hash
Cardinality
Medium
Clustering
 No
Justification

In a social network, posts are essencial and therefore the table ‘post’ will be quite large. As such, for each post it is frequent and necessary to search for the user who made it. This is done by exact match in those ids. Update frequency is extremely low and cardinality is medium so it's a good candidate for hash tables.
SQL code

CREATE INDEX owner_id_post ON post USING hash (owner_id);

Table 38: Post Owner Index

Index
IDX04
Index relation
 comment
Index attributes
owner_id
Index type
Hash
Cardinality
Medium
Clustering
No
Justification

In a social network, comments are essencial and therefore the table ‘comment’ will be quite large. As such, for each comment is frequent and necessary to search for the user who made it. This is done by exact match in those ids. Update frequency is extremely low and cardinality is medium so it's a good candidate for hash tables
SQL code

CREATE INDEX owner_id_comment ON comment USING hash (owner_id);

Table 39: Comment FTS index

2.2. Full-text Search Indices

As required by the project’s specifications indexes for full-text search must be developed. Hence, to improve text search time, we created Full-Text Search (FTS) indexes on the tables and attributes we thought would be queried the most. Those indexes can be found in the following tables:

Index
IDX05
Index relation
group
Index attributes
 name, description
Index type
 GIN
Clustering
No
Justification

To provide full-text search features to look for groups based on matching names or descriptions. The index type is GIN because the indexed fields are not expected to change often.
SQL code

-- Add column to group to store computed ts_vectors.

ALTER TABLE groupADD

COLUMN tsvectors TSVECTOR;

-- Create a function to automatically update ts_vectors.

CREATE FUNCTION group_search_update() RETURNS TRIGGER AS $$

BEGIN

IF TG_OP = 'INSERT' THEN

NEW.tsvectors = (

setweight(to_tsvector('portuguese', NEW.name), 'A') ||

setweight(to_tsvector('portuguese', NEW.description), 'B')

);

END IF;

IF TG_OP = 'UPDATE' THEN

IF (NEW.name <> OLD.name OR NEW.description <> OLD.description) THEN

NEW.tsvectors = (

setweight(to_tsvector('portuguese', NEW.name), 'A') ||

setweight(to_tsvector('portuguese', NEW.description), 'B')

);

END IF;

END IF;

RETURN NEW;

END $$

LANGUAGE plpgsql;

-- Create a trigger before insert or update on group.

CREATE TRIGGER group_search_update

BEFORE INSERT OR UPDATE ON group

FOR EACH ROW

EXECUTE PROCEDURE group_search_update();

-- Create a GIN index for ts_vectors.

CREATE INDEX search_group ON group USING GIN (tsvectors);

Table 40:Group FTS index

Index
IDX06
Index relation
 user
Index attributes
name, username
Index type
GIN
Clustering
No
Justification

To provide full-text search features to look for users based on matching names or usernames. The index type is GIN because the indexed fields are not expected to change often.
SQL code

-- Add column to user to store computed ts_vectors.

ALTER TABLE users

ADD COLUMN tsvectors TSVECTOR;

-- Create a function to automatically update ts_vectors.

CREATE FUNCTION user_search_update() RETURNS TRIGGER AS $$

BEGIN

IF TG_OP = 'INSERT' THEN

NEW.tsvectors = (

setweight(to_tsvector('portuguese', NEW.name), 'A') ||

setweight(to_tsvector('portuguese', NEW.username), 'B')

);

END IF;

IF TG_OP = 'UPDATE' THEN

IF (NEW.name <> OLD.name OR NEW.username <> OLD.username) THEN

NEW.tsvectors = (

setweight(to_tsvector('portuguese', NEW.name), 'A') ||

setweight(to_tsvector('portuguese', NEW.username), 'B')

);

END IF;

END IF;

RETURN NEW;

END $$

LANGUAGE plpgsql;

-- Create a trigger before insert or update on user

CREATE TRIGGER user_search_update

BEFORE INSERT OR UPDATE ON users

FOR EACH ROW

EXECUTE PROCEDURE user_search_update();

-- Create a GIN index for ts_vectors.

CREATE INDEX search_user ON users USING GIN (tsvectors);

Table 41: User FTS index

Index
IDX07
Index relation
 post
Index attributes
 content
Index type
GIN
Clustering
No
Justification

To provide full-text search features to look for posts based on matching content. The index type is GIN because the indexed fields are not expected to change often.
SQL code

-- Add column to post to store computed ts_vectors.

ALTER TABLE post

ADD COLUMN tsvectors TSVECTOR;

-- Create a function to automatically update ts_vectors.

CREATE FUNCTION post_search_update() RETURNS TRIGGER AS $$

BEGIN

IF TG_OP = 'INSERT' THEN

NEW.tsvectors = to_tsvector('portuguese', NEW.content);

END IF;

IF TG_OP = 'UPDATE' THEN

IF (NEW.content <> OLD.content) THEN

NEW.tsvectors = to_tsvector('portuguese', NEW.content);

END IF;

END IF;

RETURN NEW;

END $$

LANGUAGE plpgsql;

-- Create a trigger before insert or update on post

CREATE TRIGGER post_search_update

BEFORE INSERT OR UPDATE ON post

FOR EACH ROW

EXECUTE PROCEDURE post_search_update();

-- Create a GIN index for ts_vectors.

CREATE INDEX search_post ON post USING GIN (tsvectors);

Table 42: Post FTS index

Index
IDX08
Index relation
 comment
Index attributes
content
Index type
GIN
Clustering
No
Justification

To provide full-text search features to look for comments based on matching content. The index type is GIN because the indexed fields are not expected to change often.
SQL code

-- Add column to comment to store computed ts_vectors.

ALTER TABLE comment

ADD COLUMN tsvectors TSVECTOR;

-- Create a function to automatically update ts_vectors.

CREATE FUNCTION comment_search_update() RETURNS TRIGGER AS $$

BEGIN

IF TG_OP = 'INSERT' THEN

NEW.tsvectors = to_tsvector('portuguese', NEW.content);

END IF;

IF TG_OP = 'UPDATE' THEN

IF (NEW.content <> OLD.content) THEN

NEW.tsvectors = to_tsvector('portuguese', NEW.content);

END IF;

END IF;

RETURN NEW;

END $$

LANGUAGE plpgsql;

-- Create a trigger before insert or update on comments

CREATE TRIGGER comment_search_update

BEFORE INSERT OR UPDATE ON comment

FOR EACH ROW

EXECUTE PROCEDURE comment_search_update();

-- Create a GIN index for ts_vectors.

CREATE INDEX search_comment ON comment USING GIN (tsvectors);

Table 43: Comment FTS index

3. Triggers

To enforce integrity rules that cannot be achieved in a simpler way, the necessary triggers are identified and described by presenting the event, the condition, and the activation code. Triggers are also used to keep full-text indexes updated.

Trigger TRIGGER01
Description

A user can only like a post once, or like posts from groups to which they belong or like comments in posts from public users or users they follow (business rule BR07).
SQL code

CREATE FUNCTION verify_post_likes() RETURNS TRIGGER AS

$BODY$

BEGIN

IF EXISTS (SELECT * FROM post_likes WHERE NEW.user_id = user_id AND NEW.post_id = post_id) THEN

RAISE EXCEPTION 'A user can only like a post once';

END IF;

IF EXISTS (SELECT * FROM post WHERE NEW.post_id = post.id AND post.group_id IS NOT NULL)

AND NOT EXISTS (SELECT * FROM post,member WHERE NEW.post_id = post.id AND post.group_id = member.group_id AND NEW.user_id = member.user_id) THEN

RAISE EXCEPTION 'A user can only like posts from groups to which they belong';

END IF;

IF EXISTS (SELECT * FROM users,post WHERE NEW.post_id = post.id AND post.owner_id = users.id AND NOT users.is_public AND post.group_id IS NULL)

AND NOT EXISTS (SELECT * FROM post,follows WHERE NEW.post_id = post.id AND NEW.user_id = follows.follower_id AND follows.followed_id = post.owner_id) THEN

RAISE EXCEPTION 'A user can only like comment posts from public users or users they follow';

END IF;

RETURN NEW;

END

$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER verify_post_likes

BEFORE INSERT OR UPDATE ON post_likes

FOR EACH ROW

EXECUTE PROCEDURE verify_post_likes();

Table 44: Verify post likes trigger

Trigger
TRIGGER02
Description

A user can only like a comment once (business rule BR08).
SQL code

CREATE FUNCTION verify_comment_likes() RETURNS TRIGGER AS

$BODY$

BEGIN

IF EXISTS (SELECT * FROM comment_likes WHERE NEW.user_id = user_id AND NEW.post_id = post_id) THEN

RAISE EXCEPTION 'A user can only like a comment once';

END IF;

RETURN NEW;

END

$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER verify_comment_likes

BEFORE INSERT OR UPDATE ON comment_likes

FOR EACH ROW

EXECUTE PROCEDURE verify_comment_likes();

Table 45: Verify comment likes trigger

Trigger
TRIGGER03
Description

A user can only post to a group that they belong to (business rule BR09).
SQL code

CREATE FUNCTION verify_group_post() RETURNS TRIGGER AS

$BODY$

BEGIN

IF NOT EXISTS (SELECT * FROM member WHERE NEW.owner_id = user_id AND NEW.group_id = group_id)

AND NEW.group_id IS NOT NULL THEN

RAISE EXCEPTION 'A user can only post to a group that they belong to';

END IF;

RETURN NEW;

END

$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER verify_group_post

BEFORE INSERT OR UPDATE ON post

FOR EACH ROW

EXECUTE PROCEDURE verify_group_post();

Table 46: Verify group post trigger

Trigger
TRIGGER04
Description

A user cannot follow themselves (business rule BR10).
SQL code

CREATE FUNCTION verify_self_follow() RETURNS TRIGGER AS

$BODY$

BEGIN

IF NEW.follower_id = NEW.followed_id THEN

RAISE EXCEPTION 'A user can not follow itself';

END IF;

RETURN NEW;

END

$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER verify_self_follow

BEFORE INSERT OR UPDATE ON follows

FOR EACH ROW

EXECUTE PROCEDURE verify_self_follow();

Table 47: Verify self follow trigger

Trigger
TRIGGER05
Description

A user can only comment on posts from public users, posts from users they follow or on posts from groups to which they belong (business rule BR12).
SQL code

CREATE FUNCTION verify_comment() RETURNS TRIGGER AS

$BODY$

BEGIN

IF EXISTS (SELECT * FROM post WHERE NEW.post_id = post.id AND post.group_id IS NOT NULL)

AND NOT EXISTS (SELECT * FROM post,member WHERE NEW.post_id = post.id AND post.group_id = member.group_id AND NEW.owner_id = member.user_id) THEN

RAISE EXCEPTION 'A user can only comment on posts from groups to which they belong';

END IF;

IF EXISTS (SELECT * FROM users,post WHERE NEW.post_id = post.id AND post.owner_id = users.id AND NOT users.is_public AND post.group_id IS NULL)

AND NOT EXISTS (SELECT * FROM post,follows WHERE NEW.post_id = post.id AND NEW.owner_id = follows.follower_id AND follows.followed_id = post.owner_id) THEN

RAISE EXCEPTION 'A user can only comment posts from public users or users they follow';

END IF;

RETURN NEW;

END

$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER verify_comment

BEFORE INSERT OR UPDATE ON comment

FOR EACH ROW

EXECUTE PROCEDURE verify_comment();

Table 48: Verify comment trigger

Trigger
TRIGGER06
Description

A group owner is also a member of your group (business rule BR13).
SQL code

CREATE FUNCTION group_owner() RETURNS TRIGGER AS

$BODY$

BEGIN

INSERT INTO member (user_id, group_id, is_favorite)

VALUES (NEW.owner_id, NEW.id, True);

RETURN NEW;

END

$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER group_owner

BEFORE INSERT OR UPDATE ON groups

FOR EACH ROW

EXECUTE PROCEDURE group_owner();

Table 49: Group owner trigger

Trigger
TRIGGER07
Description

A user cannot request to follow a user that he/she already follow (business rule BR14).
SQL code

CREATE FUNCTION check_follow_request() RETURNS TRIGGER AS

$BODY$

BEGIN

IF EXISTS

(SELECT * FROM follows WHERE NEW.req_id = follower_id AND NEW.rcv_id = followed_id)

THEN RAISE EXCEPTION 'Can not make a follow request to someone you already follow';

END IF;

RETURN NEW;

END

$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER check_follow_request

BEFORE INSERT ON follow_request

FOR EACH ROW

EXECUTE PROCEDURE check_follow_request();

Table 50: Follow request trigger

Trigger
TRIGGER08
Description

A user cannot request to follow themselves (business rule BR15).
SQL code

CREATE FUNCTION verify_self_follow_req() RETURNS TRIGGER AS

$BODY$

BEGIN

IF NEW.req_id = NEW.rcv_id THEN

RAISE EXCEPTION 'A user can not request to follow themselves';

END IF;

RETURN NEW;

END

$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER verify_self_follow_req

BEFORE INSERT OR UPDATE ON follow_request

FOR EACH ROW

EXECUTE PROCEDURE verify_self_follow_req();

Table 51: Self follow request trigger

Trigger
TRIGGER09
Description

A user cannot request to join a group that he/she is already a part of (business rule BR16).
SQL code

CREATE FUNCTION check_group_join_req() RETURNS TRIGGER AS

$BODY$

BEGIN

IF EXISTS

(SELECT * FROM member WHERE NEW.user_id = user_id AND NEW.group_id = group_id)

THEN RAISE EXCEPTION 'Can not request to join a group you are already a part of';

END IF;

RETURN NEW;

END

$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER check_group_join_req

BEFORE INSERT ON group_join_request

FOR EACH ROW

EXECUTE PROCEDURE check_group_join_req();

Table 52: Request join trigger

Trigger
TRIGGER10
Description

When deleting a post it also deletes its comments, subcomments, likes and notifications (business rule BR17)..
SQL code

CREATE FUNCTION delete_post_action() RETURNS TRIGGER AS

$BODY$

BEGIN

DELETE FROM post_likes WHERE OLD.id = post_likes.post_id;

DELETE FROM post_notification WHERE OLD.id = post_notification.post_id;

DELETE FROM comment WHERE OLD.id = comment.post_id;

RETURN OLD;

END

$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER delete_post_action

BEFORE INSERT ON post

FOR EACH ROW

EXECUTE PROCEDURE delete_post_action();

Table 53: Delete post data trigger

Trigger
TRIGGER11
Description

When deleting a comment it also deletes its likes, subcomments and notifications (business rule BR18)..
SQL code

CREATE FUNCTION delete_comment_action() RETURNS TRIGGER AS

$BODY$

BEGIN

DELETE FROM comment_likes WHERE OLD.id = comment_likes.comment_id;

DELETE FROM comment_notification WHERE OLD.id = comment_notification.comment_id;

DELETE FROM comment WHERE OLD.id = comment.previous;

RETURN OLD;

END

$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER delete_comment_action

BEFORE INSERT ON comment

FOR EACH ROW

EXECUTE PROCEDURE delete_comment_action();

Table 54: Delete comment data trigger

Trigger
TRIGGER12
Description

When deleting a group it also deletes its posts, members, likes, comments, subcomments, notifications and group_notifications (business rule BR19). .
SQL code

CREATE FUNCTION delete_group_action() RETURNS TRIGGER AS

$BODY$

BEGIN

DELETE FROM post WHERE OLD.id = post.group_id;

DELETE FROM member WHERE OLD.id = member.group_id;

DELETE FROM group_join_request WHERE OLD.id = group_join_request.group_id;

DELETE FROM group_notification WHERE OLD.id = group_notification.group_id;

RETURN OLD;

END

$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER delete_group_action

BEFORE INSERT ON groups

FOR EACH ROW

EXECUTE PROCEDURE delete_group_action();

Table 55: Delete group data trigger

Trigger
TRIGGER13
Description

After deleting a subnotification, delete main notification table entry.
SQL code

CREATE FUNCTION delete_mainnotification_action() RETURNS TRIGGER AS

$BODY$

BEGIN

DELETE FROM notification WHERE OLD.id = notification.id;

END

$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER delete_main_post_notification_action

AFTER DELETE ON post_notification

FOR EACH ROW

EXECUTE PROCEDURE delete_mainnotification_action();

CREATE TRIGGER delete_main_comment_notification_action

AFTER DELETE ON comment_notification

FOR EACH ROW

EXECUTE PROCEDURE delete_mainnotification_action();

CREATE TRIGGER delete_main_group_notification_action

AFTER DELETE ON group_notification

FOR EACH ROW

EXECUTE PROCEDURE delete_mainnotification_action();

CREATE TRIGGER delete_main_user_notification_action

AFTER DELETE ON user_notification

FOR EACH ROW

EXECUTE PROCEDURE delete_mainnotification_action();

Table 56: Delete notifications trigger

Trigger
TRIGGER14
Description

When a new user appears, he initially gets all kinds of notification (business rule BR20)..
SQL code

CREATE FUNCTION configuration_action() RETURNS TRIGGER AS

$BODY$

BEGIN

INSERT INTO configuration (user_id, notification_type) VALUES

(NEW.id, 'request_follow'),

(NEW.id, 'started_following'),

(NEW.id, 'accepted_follow'),

(NEW.id, 'requested_join'),

(NEW.id, 'joined_group'),

(NEW.id, 'accepted_join'),

(NEW.id, 'leave_group'),

(NEW.id, 'invite'),

(NEW.id, 'ban'),

(NEW.id, 'group_ownership'),

(NEW.id, 'liked_post'),

(NEW.id, 'post_tagging'),

(NEW.id, 'liked_comment'),

(NEW.id, 'comment_post'),

(NEW.id, 'reply_comment'),

(NEW.id, 'comment_tagging');

RETURN NEW;

END

$BODY$

LANGUAGE plpgsql;

CREATE TRIGGER configuration_action

AFTER INSERT ON users

FOR EACH ROW

EXECUTE PROCEDURE configuration_action();

Table 57: User configurations trigger

4. Transactions

The transactions below are used to assure the integrity of data when more than one operation is performed and necessary. All transactions below are written in PHP language:

Transaction TRAN01
Description New comment notification
Justification The isolation level is Repeatable Read, because, otherwise, an update of the notification table could happen, due to an insert in the table post_notification committed by a concurrent transaction, and as a result, inconsistent data would be stored.
Isolation level REPEATABLE READ 
Notification::insert([
  'emitter_user' => Auth::user()->id,
  'notified_user' => $post->owner_id,
  'date' => date('Y-m-d H:i'),
  'viewed' => false,
]);



$newNotification = Notification::select('notification.id')
                      ->where('emitter_user', Auth::user()->id)
                      ->where('notified_user', $post->owner_id)->get()  ->last();

CommentNotification::insert([
      'id' => $newNotification->id,
      'comment_id' => $comment->id,
      'notification_type' => 'comment_post'
]);

$previous = Comment::find($comment->previous);

if (!$previous || $previous->owner_id == Auth::user()->id) 
  return redirect()->back()->with('success', 'Comment successfully created');

Notification::insert([

  'emitter_user' => Auth::user()->id,

  'notified_user' => $previous->owner_id,

  'date' => date('Y-m-d H:i'),

  'viewed' => false,

]);


$newNotification = Notification::select('notification.id')
                      ->where('emitter_user', Auth::user()->id)
                      ->where('notified_user', $previous->owner_id)->get  ()->last();

CommentNotification::insert([
      'id' => $newNotification->id,
      'comment_id' => $comment->id,
      'notification_type' => 'reply_comment'
    ]);

Table 58: New comment notification transaction

Transaction TRAN02
Description New comment like notification
Justification The isolation level is Repeatable Read, because, otherwise, an update of the notification table could happen, due to an insert in the table comment_notification and comment_like tables committed by a concurrent transaction, and as a result, inconsistent data would be stored.
Isolation level REPEATABLE READ 
If (CommentLike::where([
 'user_id' => Auth::user()->id,
 'comment_id' => $comment->id,
])->exists()) return;



CommentLike::insert([

  'user_id' => Auth::user()->id,

  'comment_id' => $comment->id,

]);

if (Auth::user()->id == $comment->owner_id) return;

DB::beginTransaction();



Notification::insert([

  'emitter_user' => Auth::user()->id,

  'notified_user' => $comment->owner_id,

  'date' => date('Y-m-d H:i'),

  'viewed' => false,

]);



$newNotification = Notification::select('notification.id')

                      ->where('emitter_user', Auth::user()->id)

                      ->where('notified_user', $comment->owner_id)->get()->last();



CommentNotification::insert([

      'id' => $newNotification->id,

      'comment_id' => $comment->id,

      'notification_type' => 'liked_comment'

    ]);

Table 59: New comment like notification transaction

Transaction TRAN03
Description Remove comment like notification
Justification The isolation level is Repeatable Read, because, otherwise, an update of the notification table could happen, due to an delete in the table comment_notification committed by a concurrent transaction, and as a result, inconsistent data would be stored.
Isolation level REPEATABLE READ 
DB::beginTransaction();

CommentLike::where([
  'user_id' => Auth::user()->id,
  'comment_id' => $comment->id,
])->delete();

$oldNotification = Notification::leftJoin('comment_notification', 'notification.id', '=', 'comment_notification.id')
                                ->select('notification.id')
                                ->where('notification.emitter_user', Auth::user()->id)
                                ->where('notification.notified_user', $comment->owner_id)
                                ->where('comment_notification.comment_id', $comment->id)
                                ->where('comment_notification.notification_type', 'liked_comment')
                                ->get()->last();

if ($oldNotification) {
  CommentNotification::where('id', $oldNotification->id)->delete();
  Notification::where('id', $oldNotification->id)->delete();  
}

DB::commit();

Table 60: Remove comment like notification transaction

Transaction TRAN04
Description Remove member from group notification
Justification The isolation level is Repeatable Read, because, otherwise, an delete of the notification table or member table could happen, due to an delete in the table group_notification committed by a concurrent transaction, and as a result, inconsistent data would be stored.
Isolation level REPEATABLE READ 
DB::beginTransaction();

Member::where('group_id', $group->id)
    ->where('user_id', $member->id)
    ->delete();

Notification::insert([
    'emitter_user' => $group->owner_id,
    'notified_user' => $member->id,
    'date' => date('Y-m-d H:i'),
    'viewed' => false,
]); 

$newNotification = Notification::select('notification.id')
                                ->where('emitter_user', $group->owner_id)
                                ->where('notified_user', $member->id)->get()->last();

GroupNotification::insert([
    'id' => $newNotification->id,
    'group_id' => $group->id,
    'notification_type' => 'ban',
]);

DB::commit();

Table 61: Remove member from group notification transaction

Transaction TRAN05
Description Join group notification
Justification The isolation level is Repeatable Read, because, otherwise, an insert of the group_notification table could happen, due to an insert in the table member committed by a concurrent transaction, and as a result, inconsistent data would be stored.
Isolation level REPEATABLE READ 
DB::beginTransaction();

Member::insert([
   'user_id' => Auth::user()->id,
   'group_id' => $group->id,
   'is_favorite' => false
]);

Notification::insert([
   'emitter_user' => Auth::user()->id,
   'notified_user' => $group->owner_id,
   'date' => date('Y-m-d H:i'),
   'viewed' => false,
]); 

$newNotification = Notification::select('notification.id')
                           ->where('emitter_user', Auth::user()->id)
                           ->where('notified_user', $group->owner_id)->get()->last();

GroupNotification::insert([
   'id' => $newNotification->id,
   'group_id' => $group->id,
   'notification_type' => 'joined_group',
]);

DB::commit();

Table 62: Join group notification transaction

Transaction TRAN06
Description Leave group notification
Justification The isolation level is Repeatable Read, because, otherwise, an delete/insert of the notification or group_notification tables could happen, due to an insert in the table group_notification committed by a concurrent transaction, and as a result, inconsistent data would be stored.
Isolation level REPEATABLE READ
DB::beginTransaction(); 
Member::where('group_id', $group->id)
      ->where('user_id', Auth::user()->id)->delete();

$oldNotification = Notification::leftJoin('group_notification',  'notification.id', '=', 'group_notification.id')
      ->select('notification.id')
      ->where('notification.emitter_user', Auth::user()->id)
      ->where('notification.notified_user', $group->owner_id)
      ->where('group_notification.notification_type', 'joined_group')
      ->get()->last();

if ($oldNotification) {
    GroupNotification::where('id', $oldNotification->id)->delete();
    Notification::where('id', $oldNotification->id)->delete();
}

Notification::insert([
    'emitter_user' => Auth::user()->id,
    'notified_user' => $group->owner_id,
    'date' => date('Y-m-d H:i'),
    'viewed' => false,
]); 

$newNotification = Notification::select('notification.id')
                            ->where('emitter_user', Auth::user()->id)
                            ->where('notified_user',   $group->owner_id->get()->last();

GroupNotification::insert([
    'id' => $newNotification->id,
    'group_id' => $group->id,
    'notification_type' => 'leave_group',
]);

DB::commit();

Table 63: Leave group notification transaction

Transaction TRAN07
Description Do join request notification
Justification The isolation level is Repeatable Read, because, otherwise, an update of the notification table could happen, due to an insert in the table group_notification or group_join_request table committed by a concurrent transaction, and as a result, inconsistent data would be stored.
Isolation level REPEATABLE READ 
DB::beginTransaction();

GroupJoinRequest::insert([
    'user_id' => Auth::user()->id,
    'group_id' => $group->id
]);

Notification::insert([
    'emitter_user' => Auth::user()->id,
    'notified_user' => $group->owner_id,
    'date' => date('Y-m-d H:i'),
    'viewed' => false,
]);

$newNotification = Notification::select('notification.id')
            ->where('emitter_user', Auth::user()->id)
            ->where('notified_user', $group->owner_id)->get()->last();

GroupNotification::insert([
    'id' => $newNotification->id,
    'group_id' => $group->id,
    'notification_type' => 'requested_join',
]);

DB::commit();

Table 64:Do join request notification transaction

Transaction TRAN08
Description Cancel join request
Justification The isolation level is Repeatable Read, because, otherwise, an update of the notification table could happen, due to an delete in the table group_notification committed by a concurrent transaction, and as a result, inconsistent data would be stored.
Isolation level REPEATABLE READ 
DB::beginTransaction();

GroupJoinRequest::where([
    'user_id' => Auth::user()->id,
    'group_id' => $group->id
])->delete();

$oldNotification = Notification::leftJoin('group_notification', 'notification.id', '=', 'group_notification.id')
      ->select('notification.id')
      ->where('notification.emitter_user', Auth::user()->id)
      ->where('notification.notified_user', $group->owner_id)
      ->where('group_notification.notification_type', 'requested_join')
      ->get()->last();

GroupNotification::where('id', $oldNotification->id)->delete();

Notification::where('id', $oldNotification->id)->delete();

DB::commit();

Table 65: Cancel join request transaction

Transaction TRAN09
Description Accept join request
Justification The isolation level is Repeatable Read, because, otherwise, an update of the notification table could happen, due to an insert in the table group_notification committed by a concurrent transaction, and as a result, inconsistent data would be stored.
Isolation level REPEATABLE READ 
DB::beginTransaction();

GroupJoinRequest::where([
    'user_id' => $request->user_id,
    'group_id' => $group->id
])->delete();

$oldNotification = Notification::leftJoin('group_notification', 'notification.id', '=', 'group_notification.id')
      ->select('notification.id')
      ->where('notification.emitter_user', $request->user_id)
      ->where('notification.notified_user', Auth::user()->id)
      ->where('group_notification.notification_type', 'requested_join')
      ->get()->last();

GroupNotification::where('group_notification.id', $oldNotification->id)
        ->update(['group_notification.notification_type' => 'joined_group']);

Notification::insert([
    'emitter_user' => Auth::user()->id,
    'notified_user' => $request->user_id,
    'date' => date('Y-m-d H:i'),
    'viewed' => false,
]);

$newNotification = Notification::select('notification.id')
        ->where('emitter_user', Auth::user()->id)
        ->where('notified_user', $request->user_id)->get()->last();

GroupNotification::insert([
    'id' => $newNotification->id,
    'group_id' => $group->id,
    'notification_type' => 'accepted_join',
]);

Member::insert([
    'group_id' => $group->id,
    'user_id' => $request->user_id,
]);

DB::commit();

Table 66: Accept join request transaction

Transaction TRAN10
Description Reject Join Request Notification
Justification The isolation level is Repeatable Read, because, otherwise, an update of the notification table could happen, due to an delete in the table group_notification committed by a concurrent transaction, and as a result, inconsistent data would be stored.
Isolation level REPEATABLE READ 
DB::beginTransaction();

GroupJoinRequest::where([

    'user_id' => $request->user_id,

    'group_id' => $group->id

])->delete();

$oldNotification = Notification::leftJoin('group_notification', 'notification.id', '=', 'group_notification.id')
    ->select('notification.id')
    ->where('notification.emitter_user', $request->user_id)
    ->where('notification.notified_user', $group->owner_id)
    ->where('group_notification.notification_type', 'requested_join')
    ->get()->last();

if ($oldNotification) {
    GroupNotification::where('id', $oldNotification->id)->delete();
    Notification::where('id', $oldNotification->id)->delete();
}

DB::commit();

Table 67: Reject Join Request Notification transaction

Transaction TRAN11
Description Invite notification
Justification The isolation level is Repeatable Read, because, otherwise, an update of the notification table could happen, due to an insert in the table group_notification committed by a concurrent transaction, and as a result, inconsistent data would be stored.
Isolation level REPEATABLE READ 
DB::beginTransaction();

Notification::insert([
    'emitter_user' => $group->owner_id,
    'notified_user' => $request->user_id,
    'date' => date('Y-m-d H:i'),
    'viewed' => false,
]);

$newNotification = Notification::select('notification.id')
            ->where('emitter_user', $group->owner_id)
            ->where('notified_user', $request->user_id)->get()->last();

GroupNotification::insert([
    'id' => $newNotification->id,
    'group_id' => $group->id,
    'notification_type' => 'invite',
]);

DB::commit();

Table 68: Invite notification transaction

Transaction TRAN12
Description Cancel Invite Notification
Justification The isolation level is Repeatable Read, because, otherwise, delete of the notification table could happen, due to an delete in the table group_notification committed by a concurrent transaction, and as a result, inconsistent data would be stored.
Isolation level REPEATABLE READ 
DB::beginTransaction();

$oldNotification = Notification::leftJoin('group_notification', 'notification.id', '=', 'group_notification.id')
        ->select('notification.id')
        ->where('notification.emitter_user', $group->owner_id)
        ->where('notification.notified_user', $request->user_id)
        ->where('group_notification.notification_type', 'invite')
        ->get()->last();

if ($oldNotification)  {
    GroupNotification::where('id', $oldNotification->id)->delete();
    Notification::where('id', $oldNotification->id)->delete();
}

DB::commit();

Table 69: Cancel Invite Notification transaction

Transaction TRAN13
Description Reject Invite Request
Justification The isolation level is Repeatable Read, because, otherwise, an update of the notification table could happen, due to an delete in the table group_notification committed by a concurrent transaction, and as a result, inconsistent data would be stored.
Isolation level REPEATABLE READ 
DB::beginTransaction();

$oldNotification = Notification::leftJoin('group_notification', 'notification.id', '=', 'group_notification.id')
            ->select('notification.id')
            ->where('notification.emitter_user', $group->owner_id)
            ->where('notification.notified_user', Auth::user()->id)
            ->where('group_notification.notification_type', 'invite')
            ->get()->last();

if ($oldNotification) {
    GroupNotification::where('id', $oldNotification->id)->delete();
    Notification::where('id', $oldNotification->id)->delete();
}

DB::commit();

Table 70: Reject Invite Request transaction

Transaction TRAN14
Description Accept Invite
Justification The isolation level is Repeatable Read, because, otherwise, an update of the notification table could happen, due to an insert in the table member or group_notification committed by a concurrent transaction, and as a result, inconsistent data would be stored.
Isolation level REPEATABLE READ 
DB::beginTransaction();

$oldNotification = Notification::leftJoin('group_notification', 'notification.id', '=', 'group_notification.id')
      ->select('notification.id')
      ->where('notification.emitter_user', $group->owner_id)
      ->where('notification.notified_user', Auth::user()->id)
      ->where('group_notification.notification_type', 'invite')
      ->get()->last();

GroupNotification::where('id', $oldNotification->id)->delete();

Notification::where('id', $oldNotification->id)->delete();

Member::insert([
    'group_id' => $group->id,
    'user_id' => Auth::user()->id
]);

Notification::insert([
    'emitter_user' => Auth::user()->id,
    'notified_user' => $group->owner_id,
    'date' => date('Y-m-d H:i'),
    'viewed' => false,
]);

$newNotification = Notification::select('notification.id')
        ->where('emitter_user', Auth::user()->id)
        ->where('notified_user', $group->owner_id)->get()->last();

GroupNotification::insert([
    'id' => $newNotification->id,
    'group_id' => $group->id,
    'notification_type' => 'joined_group',
]);

 DB::commit();

Table 71: Accept Invite transaction

Transaction TRAN15
Description Give ownership notification
Justification The isolation level is Repeatable Read, because, otherwise, an update of the notification table and member table could happen, due to an insert in the table group_notification committed by a concurrent transaction, and as a result, inconsistent data would be stored.
Isolation level REPEATABLE READ 
DB::beginTransaction();

$group->owner_id = $request->input('member_id');

$group->save();

Notification::insert([
    'emitter_user' => Auth::user()->id,
    'notified_user' => $request->member_id,
    'date' => date('Y-m-d H:i'),
    'viewed' => false,
]);

$newNotification = Notification::select('notification.id')
        ->where('emitter_user', Auth::user()->id)
        ->where('notified_user', $request->member_id)->get()->last();

GroupNotification::insert([
    'id' => $newNotification->id,
    'group_id' => $group->id,
    'notification_type' => 'group_ownership',
]);

DB::commit();

Table 72: Give ownership notification transaction

Transaction TRAN16
Description Like post notification
Justification The isolation level is Repeatable Read, because, otherwise, an update of the notification table could happen, due to an insert in the table post_notification committed by a concurrent transaction, and as a result, inconsistent data would be stored.
Isolation level REPEATABLE READ 
If   (PostLike::where([
 'user_id' => Auth::user()->id,
 'post_id' => $post->id,
])->exists()) return;

PostLike::insert([
  'user_id' => Auth::user()->id,
  'post_id' => $post->id,
]);

if (Auth::user()->id == $post->owner_id) return;

DB::beginTransaction();

Notification::insert([
  'emitter_user' => Auth::user()->id,
  'notified_user' => $post->owner_id,
  'date' => date('Y-m-d H:i'),
  'viewed' => false,
]);

$newNotification = Notification::select('notification.id')
                      ->where('emitter_user', Auth::user()->id)
                      ->where('notified_user', $post->owner_id)->get() ->last();

PostNotification::insert([
      'id' => $newNotification->id,
      'post_id' => $post->id,
      'notification_type' => 'liked_post'
]);

DB::commit();

Table 73: Like post notification transaction

Transaction TRAN17
Description Unlike post notification
Justification The isolation level is Repeatable Read, because, otherwise, an update of the notification table could happen, due to an insert in the table post_notification committed by a concurrent transaction, and as a result, inconsistent data would be stored.
Isolation level REPEATABLE READ 
DB::beginTransaction();

PostLike::where([
  'user_id' => Auth::user()->id,
  'post_id' => $post->id,
])->delete();

$oldNotification = Notification::leftJoin('post_notification',  'notification.id', '=', 'post_notification.id')
                    ->select('notification.id')
                    ->where('notification.emitter_usAuth::user()->id)
                    ->where('notificnotified_user',   $post->owner_id)
                    ->where('post_notification.post_$post->id)
                    ->where('post_notificanotification_type', 'liked_post')
                    ->get()->last();

if ($oldNotification) {
  PostNotification::where('id', $oldNotification->id)->delete();
  Notification::where('id', $oldNotification->id)->delete();  
}

DB::commit();

Table 74: Unlike post notification transaction

Transaction TRAN18
Description Delete user
Justification The isolation level is Repeatable Read, because, otherwise, an update of the notification table could happen, due to an delete in many tables committed by a concurrent transaction, and as a result, inconsistent data would be stored.
Isolation level REPEATABLE READ 
DB::beginTransaction();

// Update user information

$user->name = "deleted";
$user->username = "deleted" . $id;
$user->password = "deleted" . $id;
$user->email = "deleted" . $id;
$user->description = "";
$user->is_public = false;

// Block user

Blocked::insert(['id' => $id]);

// Delete user photo

ImageController::delete($id, 'profile');

// Delete follows

Follow::where('followed_id', $id)->orWhere('follower_id', $id)->delet();

RequestFollow::where('rcv_id', $id)->delete();

// Delete Notifications

Notification::where('notified_user', $id)->delete();

// Delete Configurations

Configuration::where('user_id', $id)->delete();

// Delete groups (and members, photos, posts, comments, subcomments,invites)

$groups = Group::where('owner_id', $id)->get();

foreach ($groups as $group) {
    $group->delete();
    ImageController::delete($group->id, 'groups');
}

$user->save();

DB::commit();

Table 75: Delete user transaction

Transaction TRAN19
Description Follow notification
Justification The isolation level is Repeatable Read, because, otherwise, an update of the notification table could happen, due to an insert in the table user_notification committed by a concurrent transaction, and as a result, inconsistent data would be stored.
Isolation level REPEATABLE READ 
DB::beginTransaction();

Follow::insert([
    'follower_id' => Auth::user()->id,
    'followed_id' => $request->id,
]);

Notification::insert([
    'emitter_user' => Auth::user()->id,
    'notified_user' => $request->id,
    'date' => date('Y-m-d H:i'),
    'viewed' => false,
]);

$newNotification = Notification::select('notification.id')
                            ->where('emitter_user', Auth::user()->id)
                            ->where('notified_user', $request->id)->get()->last();

UserNotification::insert([
    'id' => $newNotification->id,
    'notification_type' => 'started_following',
]);

DB::commit();

Table 76: Follow notification transaction

Transaction TRAN20
Description Unfollow notification
Justification The isolation level is Repeatable Read, because, otherwise, an update of the notification table could happen, due to an insert in the table user_notification committed by a concurrent transaction, and as a result, inconsistent data would be stored.
Isolation level REPEATABLE READ 
DB::beginTransaction();

Follow::where('followed_id', $request->id)
      ->where('follower_id', Auth::user()->id)->delete();

$oldNotification = Notification::join('user_notification', 'notification.id', '=', 'user_notification.id')
                    ->select('notification.id')
                    ->where('notification.emitter_user', Auth::user()->id)
                    ->where('notification.notified_user', $request->id)
                    ->where('user_notification.notification_type', 'started_following')
                     ->get()->last();

if ($oldNotification) {
    UserNotification::where('id', $oldNotification->id)->delete();
    Notification::where('id', $oldNotification->id)->delete();
}

DB::commit();

Table 77: Unfollow notification transaction

Transaction TRAN21
Description Do follow request notification
Justification The isolation level is Repeatable Read, because, otherwise, an update of the notification table could happen, due to an insert in the table user_notification committed by a concurrent transaction, and as a result, inconsistent data would be stored.
Isolation level REPEATABLE READ 
DB::beginTransaction();

RequestFollow::insert([
    'req_id' => Auth::user()->id,
    'rcv_id' => $request->id,
]);

Notification::insert([
    'emitter_user' => Auth::user()->id,
    'notified_user' => $request->id,
    'date' => date('Y-m-d H:i'),
    'viewed' => false,
]);

$newNotification = Notification::select('notification.id')
                            ->where('emitter_user', Auth::user()->id)
                            ->where('notified_user', $request->id)->get()->last();

UserNotification::insert([
    'id' => $newNotification->id,
    'notification_type' => 'request_follow',
]);

DB::commit();

Table 78: Do follow request notification transaction

Transaction TRAN22
Description Cancel follow request notification
Justification The isolation level is Repeatable Read, because, otherwise, an update of the notification table could happen, due to an insert in the table user_notification committed by a concurrent transaction, and as a result, inconsistent data would be stored.
Isolation level REPEATABLE READ 
DB::beginTransaction();

RequestFollow::where('req_id', Auth::user()->id)
                ->where('rcv_id', $request->id)->delete();

$oldNotification = Notification::join('user_notification', 'notification.id', '=', 'user_notification.id')
                        ->select('notification.id')
                        ->where('notification.emitter_user', Auth::user()->id)
                        ->where('notification.notified_user', $request->id)
                        ->where('user_notification.notification_type', 'request_follow')
                        ->get()->last();

UserNotification::where('id', $oldNotification->id)->delete();

DB::commit();

Table 79: Cancel follow request notification transaction

Transaction TRAN23
Description Accept follow request notification
Justification The isolation level is Repeatable Read, because, otherwise, an update of the notification table could happen, due to an insert in the table user_notification committed by a concurrent transaction, and as a result, inconsistent data would be stored.
Isolation level REPEATABLE READ 
DB::beginTransaction();

RequestFollow::where('req_id', Auth::user()->id)
                ->where('rcv_id', $request->id)->delete();

Follow::insert([
    'follower_id' => $request->id,
    'followed_id' => Auth::user()->id,
]);
   

$oldNotification = Notification::join('user_notification', 'notification.id', '=', 'user_notification.id')
                        ->select('notification.id')
                        ->where('notification.emitter_user', $request->id)
                        ->where('notification.notified_user', Auth::user()->id)
                        ->where('user_notification.notification_type', 'request_follow')
                        ->get()->last();

UserNotification::where('user_notification.id', $oldNotification->id)
                ->update(['user_notification.notification_type' => 'started_following']);

Notification::insert([
    'emitter_user' => Auth::user()->id,
    'notified_user' => $request->id,
    'date' => date('Y-m-d H:i'),
    'viewed' => false,
]);

$newNotification = Notification::select('notification.id')
                    ->where('emitter_user', Auth::user()->id)
                    ->where('notified_user', $request->id)->get()->last();

UserNotification::insert([
    'id' => $newNotification->id,
    'notification_type' => 'accepted_follow',
]);

DB::commit();

Table 80: Accept follow request notification transaction

Transaction TRAN24
Description Reject Follow Request
Justification The isolation level is Repeatable Read, because, otherwise, an update of the notification table could happen, due to an insert in the table user_notification committed by a concurrent transaction, and as a result, inconsistent data would be stored.
Isolation level REPEATABLE READ 
DB::beginTransaction();

RequestFollow::where('req_id', $request->id)
                ->where('rcv_id', Auth::user()->id)->delete();

$oldNotification = Notification::join('user_notification', 'notification.id', '=', 'user_notification.id')
                        ->select('notification.id')
                        ->where('notification.emitter_user', $request->id)
                        ->where('notification.notified_user', Auth::user()->id)
                        ->where('user_notification.notification_type', 'request_follow')
                        ->get()->last();

UserNotification::where('id', $oldNotification->id)->delete();

DB::commit();

Table 81: Reject Follow Request transaction

Transaction TRAN25
Description Delete main notification
Justification The isolation level is Repeatable Read, because, otherwise, an update or insert of the notification table could happen, due to a delete in the main table notification or others generalization committed by a concurrent transaction, and as a result, inconsistent data would be stored.
Isolation level REPEATABLE READ 
DB::beginTransaction();

CommentNotification::where('id', $request->id)->delete();

UserNotification::where('id', $request->id)->delete();

GroupNotification::where('id', $request->id)->delete();

PostNotification::where('id', $request->id)->delete();

Notification::where('id', $request->id)->delete();

DB::commit();

Table 83: Delete main notification transaction

5. Database Schema

OnlyFEUP Database Schema is available in Annexes and in the main code repository.

6. Database Population

OnlyFEUP Database Population is available in Annexes and in the main code repository.

create schema if not exists lbaw2255;

SET DateStyle TO European;

-----------------------------------------
-- Drop old schema
-----------------------------------------

DROP TABLE IF EXISTS group_join_request CASCADE;
DROP TABLE IF EXISTS comment_notification CASCADE;
DROP TABLE IF EXISTS user_notification CASCADE;
DROP TABLE IF EXISTS group_notification CASCADE;
DROP TABLE IF EXISTS post_notification CASCADE;
DROP TABLE IF EXISTS member CASCADE;
DROP TABLE IF EXISTS blocked CASCADE;
DROP TABLE IF EXISTS follow_request CASCADE;
DROP TABLE IF EXISTS follows CASCADE;
DROP TABLE IF EXISTS post_likes CASCADE;
DROP TABLE IF EXISTS comment_likes CASCADE;
DROP TABLE IF EXISTS admin CASCADE;
DROP TABLE IF EXISTS notification CASCADE;
DROP TABLE IF EXISTS comment CASCADE;
DROP TABLE IF EXISTS post CASCADE;
DROP TABLE IF EXISTS groups CASCADE;
DROP TABLE IF EXISTS users CASCADE;
DROP TABLE IF EXISTS configuration CASCADE;
DROP TABLE IF EXISTS message CASCADE;

DROP TYPE IF EXISTS user_notification_types;
DROP TYPE IF EXISTS group_notification_types;
DROP TYPE IF EXISTS post_notification_types;
DROP TYPE IF EXISTS comment_notification_types;

DROP FUNCTION IF EXISTS group_search_update CASCADE;
DROP FUNCTION IF EXISTS user_search_update CASCADE;
DROP FUNCTION IF EXISTS comment_search_update CASCADE;
DROP FUNCTION IF EXISTS post_search_update CASCADE;
DROP FUNCTION IF EXISTS verify_post_likes CASCADE;
DROP FUNCTION IF EXISTS verify_comment_likes CASCADE;
DROP FUNCTION IF EXISTS verify_group_post CASCADE;
DROP FUNCTION IF EXISTS verify_comment CASCADE;
DROP FUNCTION IF EXISTS group_owner CASCADE;
DROP FUNCTION IF EXISTS check_follow_request CASCADE;
DROP FUNCTION IF EXISTS verify_self_follow_req CASCADE;
DROP FUNCTION IF EXISTS check_group_join_req CASCADE;
DROP FUNCTION IF EXISTS verify_self_follow CASCADE;
DROP FUNCTION IF EXISTS delete_post_action CASCADE;
DROP FUNCTION IF EXISTS delete_comment_action CASCADE;
DROP FUNCTION IF EXISTS delete_group_action CASCADE;
DROP FUNCTION IF EXISTS delete_user_action CASCADE;
DROP FUNCTION IF EXISTS delete_mainnotification_action CASCADE;
DROP FUNCTION IF EXISTS configuration_action CASCADE;

-----------------------------------------
-- Types
-----------------------------------------

CREATE TYPE user_notification_types AS ENUM ('request_follow', 'started_following', 'accepted_follow');
CREATE TYPE group_notification_types AS ENUM ('requested_join', 'joined_group', 'accepted_join', 'leave_group', 'invite', 'ban', 'group_ownership');
CREATE TYPE post_notification_types AS ENUM ('liked_post', 'post_tagging');
CREATE TYPE comment_notification_types AS ENUM ('liked_comment', 'comment_post', 'reply_comment', 'comment_tagging');

-----------------------------------------
-- Tables
-----------------------------------------

CREATE TABLE users (
   id SERIAL PRIMARY KEY,
   username VARCHAR(256) UNIQUE NOT NULL,
   password VARCHAR(256) NOT NULL,
   email VARCHAR(256) UNIQUE NOT NULL,
   name VARCHAR(256) NOT NULL,
   description VARCHAR(512),
   is_public BOOLEAN NOT NULL DEFAULT TRUE,
   remember_token VARCHAR(256) DEFAULT NULL
);

CREATE TABLE admin (
   id INTEGER PRIMARY KEY REFERENCES users (id) ON UPDATE CASCADE
);

CREATE TABLE groups (
   id SERIAL PRIMARY KEY,
   owner_id INTEGER NOT NULL REFERENCES users (id) ON UPDATE CASCADE,
   name VARCHAR(256) UNIQUE NOT NULL,
   description VARCHAR(256),
   is_public BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE post (
   id SERIAL PRIMARY KEY,
   owner_id INTEGER NOT NULL REFERENCES users (id) ON UPDATE CASCADE,
   group_id INTEGER REFERENCES groups (id) ON UPDATE CASCADE,
   is_public BOOLEAN NOT NULL DEFAULT TRUE,
   content VARCHAR(256),
   date TIMESTAMP NOT NULL CHECK (date <= now())
);

CREATE TABLE comment (
   id SERIAL PRIMARY KEY,
   owner_id INTEGER NOT NULL REFERENCES users (id) ON UPDATE CASCADE,
   post_id INTEGER NOT NULL REFERENCES post (id) ON UPDATE CASCADE,
   previous INTEGER DEFAULT NULL REFERENCES comment (id) ON UPDATE CASCADE,
   content VARCHAR(256) NOT NULL,
   date TIMESTAMP NOT NULL CHECK (date <= now())
);

CREATE TABLE notification (
   id SERIAL PRIMARY KEY,
   date TIMESTAMP NOT NULL CHECK (date <= now()),
   notified_user INTEGER NOT NULL REFERENCES users (id) ON UPDATE CASCADE,
   emitter_user INTEGER NOT NULL REFERENCES users (id) ON UPDATE CASCADE,
   viewed BOOLEAN NOT NULL DEFAULT FALSE
);

CREATE TABLE comment_notification (
   id SERIAL PRIMARY KEY REFERENCES notification (id) ON UPDATE CASCADE,
   comment_id INTEGER NOT NULL REFERENCES comment (id) ON UPDATE CASCADE,
   notification_type comment_notification_types NOT NULL
);

CREATE TABLE user_notification (
   id INTEGER PRIMARY KEY REFERENCES notification (id) ON UPDATE CASCADE,
   notification_type user_notification_types NOT NULL
);

CREATE TABLE group_notification (
   id INTEGER PRIMARY KEY REFERENCES notification (id) ON UPDATE CASCADE,
   group_id INTEGER NOT NULL REFERENCES groups (id) ON UPDATE CASCADE,
   notification_type group_notification_types NOT NULL
);

CREATE TABLE post_notification (
   id INTEGER PRIMARY KEY REFERENCES notification (id) ON UPDATE CASCADE,
   post_id INTEGER NOT NULL REFERENCES post (id) ON UPDATE CASCADE,
   notification_type post_notification_types NOT NULL
);

CREATE TABLE member (
   user_id INTEGER REFERENCES users (id) ON UPDATE CASCADE,
   group_id INTEGER REFERENCES groups (id) ON UPDATE CASCADE,
   is_favorite BOOLEAN NOT NULL DEFAULT FALSE,
   PRIMARY KEY (user_id, group_id)
);

CREATE TABLE follow_request (
   req_id INTEGER REFERENCES users (id) ON UPDATE CASCADE,
   rcv_id INTEGER REFERENCES users (id) ON UPDATE CASCADE,
   PRIMARY KEY (req_id, rcv_id)
);

CREATE TABLE follows (
   follower_id INTEGER REFERENCES users (id) ON UPDATE CASCADE,
   followed_id INTEGER REFERENCES users (id) ON UPDATE CASCADE,
   PRIMARY KEY (follower_id, followed_id)
);

CREATE TABLE group_join_request (
   user_id INTEGER REFERENCES users (id) ON UPDATE CASCADE,
   group_id INTEGER REFERENCES groups (id) ON UPDATE CASCADE,
   PRIMARY KEY (user_id, group_id)
);

CREATE TABLE post_likes (
   user_id INTEGER REFERENCES users (id) ON UPDATE CASCADE,
   post_id INTEGER REFERENCES post (id) ON UPDATE CASCADE,
   PRIMARY KEY (user_id, post_id)
);

CREATE TABLE comment_likes (
   user_id INTEGER REFERENCES users (id) ON UPDATE CASCADE,
   comment_id INTEGER REFERENCES comment (id) ON UPDATE CASCADE,
   PRIMARY KEY (user_id, comment_id)
);

CREATE TABLE blocked (
   id INTEGER REFERENCES users (id) ON UPDATE CASCADE,
   PRIMARY kEY (id)
);

CREATE TABLE configuration (
   user_id INTEGER REFERENCES users (id) ON UPDATE CASCADE,
   notification_type VARCHAR(20) NOT NULL,
   active BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE message (
   id SERIAL PRIMARY KEY,
   emitter_id INTEGER REFERENCES users (id) ON UPDATE CASCADE,
   receiver_id INTEGER REFERENCES users (id) ON UPDATE CASCADE,
   content VARCHAR(256) NOT NULL,
   date TIMESTAMP NOT NULL CHECK (date <= now()),
   viewed BOOLEAN NOT NULL DEFAULT FALSE
);

-----------------------------------------
-- INDEXES
-----------------------------------------

CREATE INDEX notified_user_notification ON notification USING btree (notified_user);
CLUSTER notification USING notified_user_notification;

CREATE INDEX emitter_user_notification ON notification USING btree (emitter_user);
CLUSTER notification USING emitter_user_notification;

CREATE INDEX owner_id_post ON post USING hash (owner_id);

CREATE INDEX owner_id_comment ON comment USING hash (owner_id);

-----------------------------------------
-- FTS INDEXES
-----------------------------------------

-- Add column to group to store computed ts_vectors.
ALTER TABLE groups
ADD COLUMN tsvectors TSVECTOR;

-- Create a function to automatically update ts_vectors.
CREATE FUNCTION group_search_update() RETURNS TRIGGER AS $$
BEGIN
 IF TG_OP = 'INSERT' THEN
        NEW.tsvectors = (
         setweight(to_tsvector('simple', NEW.name), 'A') ||
         setweight(to_tsvector('simple', NEW.description), 'B')
        );
 END IF;
 IF TG_OP = 'UPDATE' THEN
         IF (NEW.name <> OLD.name OR NEW.description <> OLD.description) THEN
           NEW.tsvectors = (
             setweight(to_tsvector('simple', NEW.name), 'A') ||
             setweight(to_tsvector('simple', NEW.description), 'B')
           );
         END IF;
 END IF;
 RETURN NEW;
END $$
LANGUAGE plpgsql;

-- Create a trigger before insert or update on group
CREATE TRIGGER group_search_update
 BEFORE INSERT OR UPDATE ON groups
 FOR EACH ROW
 EXECUTE PROCEDURE group_search_update();

-- Create a GIN index for ts_vectors.
CREATE INDEX search_group ON groups USING GIN (tsvectors);

-- Add column to user to store computed ts_vectors.
ALTER TABLE users
ADD COLUMN tsvectors TSVECTOR;

-- Create a function to automatically update ts_vectors.
CREATE FUNCTION user_search_update() RETURNS TRIGGER AS $$
BEGIN
 IF TG_OP = 'INSERT' THEN
        NEW.tsvectors = (
         setweight(to_tsvector('simple', NEW.name), 'A') ||
         setweight(to_tsvector('simple', NEW.username), 'B')
        );
 END IF;
 IF TG_OP = 'UPDATE' THEN
         IF (NEW.name <> OLD.name OR NEW.username <> OLD.username) THEN
           NEW.tsvectors = (
             setweight(to_tsvector('simple', NEW.name), 'A') ||
             setweight(to_tsvector('simple', NEW.username), 'B')
           );
         END IF;
 END IF;
 RETURN NEW;
END $$
LANGUAGE plpgsql;

-- Create a trigger before insert or update on user
CREATE TRIGGER user_search_update
 BEFORE INSERT OR UPDATE ON users
 FOR EACH ROW
 EXECUTE PROCEDURE user_search_update();

-- Create a GIN index for ts_vectors.
CREATE INDEX search_user ON users USING GIN (tsvectors);

-- Add column to post to store computed ts_vectors.
ALTER TABLE post
ADD COLUMN tsvectors TSVECTOR;

-- Create a function to automatically update ts_vectors.
CREATE FUNCTION post_search_update() RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
        NEW.tsvectors = to_tsvector('simple', NEW.content);
  END IF;
  IF TG_OP = 'UPDATE' THEN
      IF (NEW.content <> OLD.content) THEN
           NEW.tsvectors = to_tsvector('simple', NEW.content);
      END IF;
 END IF;
   RETURN NEW;
END $$
LANGUAGE plpgsql;

-- Create a trigger before insert or update on post
CREATE TRIGGER post_search_update
 BEFORE INSERT OR UPDATE ON post
 FOR EACH ROW
 EXECUTE PROCEDURE post_search_update();

-- Create a GIN index for ts_vectors.
CREATE INDEX search_post ON post USING GIN (tsvectors);

-- Add column to comment to store computed ts_vectors.
ALTER TABLE comment
ADD COLUMN tsvectors TSVECTOR;

-- Create a function to automatically update ts_vectors.
CREATE FUNCTION comment_search_update() RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
        NEW.tsvectors = to_tsvector('simple', NEW.content);
  END IF;
  IF TG_OP = 'UPDATE' THEN
      IF (NEW.content <> OLD.content) THEN
           NEW.tsvectors = to_tsvector('simple', NEW.content);
      END IF;
 END IF;
   RETURN NEW;
END $$
LANGUAGE plpgsql;

-- Create a trigger before insert or update on comments
CREATE TRIGGER comment_search_update
 BEFORE INSERT OR UPDATE ON comment
 FOR EACH ROW
 EXECUTE PROCEDURE comment_search_update();

-- Create a GIN index for ts_vectors.
CREATE INDEX search_comment ON comment USING GIN (tsvectors);

-----------------------------------------
-- TRIGGERS
-----------------------------------------

-- TRIGGER01
-- A user can only like a post once, or like posts from groups to which they belong or like comment in posts from public users or users they follow (business rule BR07)

CREATE FUNCTION verify_post_likes() RETURNS TRIGGER AS
$BODY$
BEGIN
   IF EXISTS (SELECT * FROM post_likes WHERE NEW.user_id = user_id AND NEW.post_id = post_id) THEN
      RAISE EXCEPTION 'A user can only like a post once';
   END IF;
   IF EXISTS (SELECT * FROM post WHERE NEW.post_id = post.id AND post.group_id IS NOT NULL)
      AND NOT EXISTS (SELECT * FROM post,member WHERE NEW.post_id = post.id AND post.group_id = member.group_id
                  AND NEW.user_id = member.user_id) THEN
      RAISE EXCEPTION 'A user can only like posts from groups to which they belong';
   END IF;
   IF EXISTS (SELECT * FROM users,post WHERE NEW.post_id = post.id AND post.owner_id = users.id AND NOT users.is_public AND post.group_id IS NULL AND NEW.user_id <> post.owner_id)
      AND NOT EXISTS (SELECT * FROM post,follows WHERE NEW.post_id = post.id AND NEW.user_id = follows.follower_id AND follows.followed_id = post.owner_id) THEN
      RAISE EXCEPTION 'A user can only like posts from public users or users they follow';
   END IF;
   RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER verify_post_likes
   BEFORE INSERT OR UPDATE ON post_likes
   FOR EACH ROW
   EXECUTE PROCEDURE verify_post_likes();

-- TRIGGER02
-- A user can only like a comment once (business rule BR08)

CREATE FUNCTION verify_comment_likes() RETURNS TRIGGER AS
$BODY$
BEGIN
   IF EXISTS
      (SELECT * FROM comment_likes WHERE NEW.user_id = user_id AND NEW.comment_id = comment_id)
      THEN RAISE EXCEPTION 'A user can only like a comment once';
   END IF;

   IF EXISTS
      (SELECT * FROM post,comment WHERE NEW.comment_id = comment.id AND comment.post_id = post.id AND post.group_id IS NOT NULL)
   AND NOT EXISTS
      (SELECT * FROM post,member,comment WHERE NEW.comment_id = comment.id AND comment.post_id = post.id AND post.group_id = member.group_id AND NEW.user_id = member.user_id)
   THEN RAISE EXCEPTION 'Can not like a comment of a post of a group you do not belong to';
   END IF;

   IF EXISTS
      (SELECT * FROM users,post,comment WHERE NEW.comment_id = comment.id AND comment.post_id = post.id AND post.owner_id = users.id AND NOT users.is_public AND post.group_id IS NULL AND comment.owner_id <> post.owner_id)
   AND NOT EXISTS
      (SELECT * FROM post,follows,comment WHERE NEW.comment_id = comment.id AND comment.post_id = post.id AND NEW.user_id = follows.follower_id AND follows.followed_id = post.owner_id)
   THEN RAISE EXCEPTION 'Can not like comments in posts from private users you do not follow';
   END IF;

   RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER verify_comment_likes
   BEFORE INSERT OR UPDATE ON comment_likes
   FOR EACH ROW
   EXECUTE PROCEDURE verify_comment_likes();

-- TRIGGER03
-- A user can only post to a group that they belong to (business rule BR09)

CREATE FUNCTION verify_group_post() RETURNS TRIGGER AS
$BODY$
BEGIN
   IF NOT EXISTS (SELECT * FROM member WHERE NEW.owner_id = user_id AND NEW.group_id = group_id)
      AND NEW.group_id IS NOT NULL THEN
         RAISE EXCEPTION 'A user can only post to a group that they belong to';
   END IF;
   RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER verify_group_post
   BEFORE INSERT OR UPDATE ON post
   FOR EACH ROW
   EXECUTE PROCEDURE verify_group_post();

-- TRIGGER04
-- A user cannot follow themselves (business rule BR10)

CREATE FUNCTION verify_self_follow() RETURNS TRIGGER AS
$BODY$
BEGIN
   IF NEW.follower_id = NEW.followed_id THEN
         RAISE EXCEPTION 'A user can not follow themselves';
   END IF;
   RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER verify_self_follow
   BEFORE INSERT OR UPDATE ON follows
   FOR EACH ROW
   EXECUTE PROCEDURE verify_self_follow();

-- TRIGGER05
-- A user can only comment on posts from public users, posts from users they follow or on posts from groups to which they belong (business rule BR12)

CREATE FUNCTION verify_comment() RETURNS TRIGGER AS
$BODY$
BEGIN
   IF EXISTS (SELECT * FROM post WHERE NEW.post_id = post.id AND post.group_id IS NOT NULL)
      AND NOT EXISTS (SELECT * FROM post,member WHERE NEW.post_id = post.id AND post.group_id = member.group_id
                  AND NEW.owner_id = member.user_id) THEN
      RAISE EXCEPTION 'A user can only comment on posts from groups to which they belong';
   END IF;
   IF EXISTS (SELECT * FROM users,post WHERE NEW.post_id = post.id AND post.owner_id = users.id AND NOT users.is_public AND post.group_id IS NULL AND NEW.owner_id <> post.owner_id)
      AND NOT EXISTS (SELECT * FROM post,follows WHERE NEW.post_id = post.id AND NEW.owner_id = follows.follower_id AND follows.followed_id = post.owner_id)
      THEN RAISE EXCEPTION 'A user can only comment posts from public users or users they follow';
   END IF;
   RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER verify_comment
   BEFORE INSERT OR UPDATE ON comment
   FOR EACH ROW
   EXECUTE PROCEDURE verify_comment();

-- TRIGGER06
-- A group owner is also a member of your group (business rule BR13)

CREATE FUNCTION group_owner() RETURNS TRIGGER AS
$BODY$
BEGIN
   INSERT INTO member (user_id, group_id, is_favorite) VALUES (NEW.owner_id, NEW.id, True);
   RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER group_owner
   AFTER INSERT ON groups
   FOR EACH ROW
   EXECUTE PROCEDURE group_owner();

-- TRIGGER07
-- A user cannot request to follow a user that he/she already follow (business rule BR14)

CREATE FUNCTION check_follow_request() RETURNS TRIGGER AS
$BODY$
BEGIN
   IF EXISTS
      (SELECT * FROM follows WHERE NEW.req_id = follower_id AND NEW.rcv_id = followed_id)
      THEN RAISE EXCEPTION 'Can not make a follow request to someone you already follow';
   END IF;
   RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER check_follow_request
   BEFORE INSERT ON follow_request
   FOR EACH ROW
   EXECUTE PROCEDURE check_follow_request();

-- TRIGGER08
-- A user cannot request to follow themselves (business rule BR15)

CREATE FUNCTION verify_self_follow_req() RETURNS TRIGGER AS
$BODY$
BEGIN
   IF NEW.req_id = NEW.rcv_id THEN
         RAISE EXCEPTION 'A user can not request to follow themselves';
   END IF;
   RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER verify_self_follow_req
   BEFORE INSERT OR UPDATE ON follow_request
   FOR EACH ROW
   EXECUTE PROCEDURE verify_self_follow_req();

-- TRIGGER09
-- A user cannot request to join a group that he/she is already a part of (business rule BR16)

CREATE FUNCTION check_group_join_req() RETURNS TRIGGER AS
$BODY$
BEGIN
   IF EXISTS
      (SELECT * FROM member WHERE NEW.user_id = user_id AND NEW.group_id = group_id)
      THEN RAISE EXCEPTION 'Can not request to join a group you are already a part of';
   END IF;
   RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER check_group_join_req
   BEFORE INSERT ON group_join_request
   FOR EACH ROW
   EXECUTE PROCEDURE check_group_join_req();

-- TRIGGER10
-- When deleting a post it also deletes its comments, subcomments, likes and notifications (business rule BR17)

CREATE FUNCTION delete_post_action() RETURNS TRIGGER AS
$BODY$
BEGIN
   DELETE FROM post_likes WHERE OLD.id = post_likes.post_id;
   DELETE FROM post_notification WHERE OLD.id = post_notification.post_id;
   DELETE FROM comment WHERE OLD.id = comment.post_id;
   RETURN OLD;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER delete_post_action
   BEFORE DELETE ON post
   FOR EACH ROW
   EXECUTE PROCEDURE delete_post_action();

-- TRIGGER11
-- When deleting a comment it also deletes its likes, subcomments and notifications (business rule BR18)

CREATE FUNCTION delete_comment_action() RETURNS TRIGGER AS
$BODY$
BEGIN
   DELETE FROM comment_likes WHERE OLD.id = comment_likes.comment_id;
   DELETE FROM comment_notification WHERE OLD.id = comment_notification.comment_id;
   DELETE FROM comment WHERE OLD.id = comment.previous;
   RETURN OLD;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER delete_comment_action
   BEFORE DELETE ON comment
   FOR EACH ROW
   EXECUTE PROCEDURE delete_comment_action();

-- TRIGGER12
-- When deleting a group it also deletes its posts, members, likes, comments, subcomments, notifications and group_notifications  (business rule BR19)

CREATE FUNCTION delete_group_action() RETURNS TRIGGER AS
$BODY$
BEGIN
   DELETE FROM post WHERE OLD.id = post.group_id;
   DELETE FROM member WHERE OLD.id = member.group_id;
   DELETE FROM group_join_request WHERE OLD.id = group_join_request.group_id;
   DELETE FROM group_notification WHERE OLD.id = group_notification.group_id;
   RETURN OLD;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER delete_group_action
   BEFORE DELETE ON groups
   FOR EACH ROW
   EXECUTE PROCEDURE delete_group_action();

-- TRIGGER13
-- After deleting a subnotification, delete main notification table entry

CREATE FUNCTION delete_mainnotification_action() RETURNS TRIGGER AS
$BODY$
BEGIN
   DELETE FROM notification WHERE OLD.id = notification.id;
   RETURN OLD;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER delete_main_post_notification_action
   AFTER DELETE ON post_notification
   FOR EACH ROW
   EXECUTE PROCEDURE delete_mainnotification_action();

CREATE TRIGGER delete_main_comment_notification_action
   AFTER DELETE ON comment_notification
   FOR EACH ROW
   EXECUTE PROCEDURE delete_mainnotification_action();

CREATE TRIGGER delete_main_group_notification_action
   AFTER DELETE ON group_notification
   FOR EACH ROW
   EXECUTE PROCEDURE delete_mainnotification_action();

CREATE TRIGGER delete_main_user_notification_action
   AFTER DELETE ON user_notification
   FOR EACH ROW
   EXECUTE PROCEDURE delete_mainnotification_action();

-- TRIGGER14
-- When new user appears, he initially gets all kinds of notification (business rule BR20)

CREATE FUNCTION configuration_action() RETURNS TRIGGER AS
$BODY$
BEGIN
   INSERT INTO configuration (user_id, notification_type) VALUES
      (NEW.id, 'request_follow'),
      (NEW.id, 'started_following'),
      (NEW.id, 'accepted_follow'),
      (NEW.id, 'requested_join'),
      (NEW.id, 'joined_group'),
      (NEW.id, 'accepted_join'),
      (NEW.id, 'leave_group'),
      (NEW.id, 'invite'),
      (NEW.id, 'ban'),
      (NEW.id, 'group_ownership'),
      (NEW.id, 'liked_post'),
      (NEW.id, 'post_tagging'),
      (NEW.id, 'liked_comment'),
      (NEW.id, 'comment_post'),
      (NEW.id, 'reply_comment'),
      (NEW.id, 'comment_tagging');
   RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER configuration_action
   AFTER INSERT ON users
   FOR EACH ROW
   EXECUTE PROCEDURE configuration_action();

A.2. Database population (populate.sql)

-- Only two examples per table

INSERT INTO users (username, password, email, name, is_public) VALUES
    ('eduardanascimento', 'c90a22f9bd27177d1c29a848143c791b692b9780', '[email protected]', 'Eduarda Nascimento', TRUE),
    ('noahpinho', '37dc56b04e264ed9ed48d8a6a53f83d135cc627b', '[email protected]', 'Noah Pinho', TRUE);

INSERT INTO admin (id) VALUES (1), (2);

INSERT INTO groups (owner_id, name, is_public, description) VALUES
    (1, 'LBAW G2255', True, 'Work group for lbaw'),
    (2, 'Projeto FEUP 22/23', False, 'Engenharia Social');

INSERT INTO member (user_id, group_id) VALUES
    (2	,   1),
    (3	,   2);

INSERT INTO post (owner_id, group_id, content, date) VALUES
    (1,	   1,   'I like FEUP!',    '8/10/17 0:00'),
    (1,	   1,   'When is the PFL exam, does someone know???',    '4/5/19 0:00');


INSERT INTO comment (owner_id, post_id, content, date) VALUES
    (1,      1,        'Great stuff here. Good job',   '10/10/22 0:00'),
    (2,      3,        'Great stuff here. Good job',   '11/10/22 0:00');


INSERT INTO follows (follower_id, followed_id) VALUES
    (77,   	34),
    (16,   	 3);

INSERT INTO follow_request (req_id, rcv_id) VALUES 
    (98,    116),
    (77,   	124);

INSERT INTO group_join_request (user_id, group_id) VALUES
    (5,     1),
    (34,    2);

INSERT INTO post_likes (user_id,post_id) VALUES 
    (68,     68),
    (10,    119);

INSERT INTO comment_likes (user_id, comment_id) VALUES
    (70,	22),
    (56,	50);

INSERT INTO message (emitter_id, receiver_id, content, date) VALUES
   (4, 1, 'Uma simples mensagem de teste!' ,'29/10/16 0:00'),
   (3, 1, 'Uma simples mensagem de teste!' ,'29/10/16 0:00');

Revision history

Artifacts: A4

Editor: André Costa

October 6:
Started class diagram
by:
André Costa
Fábio Sá
Lourenço Gonçalves
Marcos Ferreira Pinto

October 7:
Continued class diagram. Started additional business rules
by:
André Costa
Fábio Sá
Lourenço Gonçalves
Marcos Ferreira Pinto

October 13:
Finished the class diagram and business rules
by:
André Costa
Fábio Sá
Lourenço Gonçalves
Marcos Ferreira Pinto

Artifacts: A5

Editor: Fábio Sá

October 7:
Started relational schema
by:
André Costa
Fábio Sá
Lourenço Gonçalves
Marcos Ferreira Pinto

October 15:
Finished relational schema. Schema validation and domains.
by:
André Costa
Fábio Sá
Lourenço Gonçalves
Marcos Ferreira Pinto

October 18:
Started SQL schema.
by:
André Costa
Fábio Sá
Lourenço Gonçalves
Marcos Ferreira Pinto

Artifacts: A6

Editor: Marcos William

October 18:
Continue SQL schema. Indexes. Started database population.
by:
André Costa
Fábio Sá
Lourenço Gonçalves
Marcos Ferreira Pinto

October 19:
More population.
by:
André Costa
Fábio Sá
Lourenço Gonçalves
Marcos Ferreira Pinto

October 20:
More population. Started performance indexes and FTS indexes.
by:
André Costa
Fábio Sá
Lourenço Gonçalves
Marcos Ferreira Pinto

October 24:
Even more population. Continued triggers.
by:
André Costa
Fábio Sá
Lourenço Gonçalves
Marcos Ferreira Pinto

October 27:
Finished population (except transactions). Finished triggers.
by:
André Costa
Fábio Sá
Lourenço Gonçalves
Marcos Ferreira Pinto

October 29:
Finished all indexes and worked on transactions.
by:
André Costa
Fábio Sá
Lourenço Gonçalves
Marcos Ferreira Pinto

October 30:
Finished transactions and report.
by:
André Costa
Fábio Sá
Lourenço Gonçalves
Marcos Ferreira Pinto

Group lbaw2255, 30/10/2022