Skip to content

ebd

Jump to bottom
Davide64-dev edited this page Jan 28, 2024 · 1 revision

EBD: Database Specification

This section is dedicated to the database design and implementation process, which involves translating the UML specifications into a relational schema and subsequently implementing it in PostgreSQL.

A4. Conceptual Data Model

In this subsection, we'll explore the conceptual data model, a vital step in our database design process. We use UML specifications to define data elements and relationships, creating a high-level data overview. This model guides the construction of our PostgreSQL relational schema, helping us understand key data modeling concepts and principles for this report.

uml

A5. Relational Schema, Validation and Schema Refinement

This section serves as the bridge between the conceptual model represented by UML and the practical implementation of the database in SQL. The relational schema acts as an intermediary abstraction layer, facilitating the transition from the conceptual representation to the concrete database structure in SQL.

1. Relational Schema

Name Definition
R01 user(id,username UK NN, password NN, image, email UK NN, academic_status, display_name, is_private NN DF true, role, email_verified_at)
R02 post(id, author $\rightarrow$ user, title NN, content NN, date NN, attachment, group_id $\rightarrow$ group, is_private NN)
R03 friend(id_friend_1 $\rightarrow$ user, id_friend_2 $\rightarrow$ user)
R04 group(id, name NN, description NN, is_private NN DF true)
R05 group_owner(user_id $\rightarrow$ user, group_id $\rightarrow$ group)
R06 group_request(id, user_id $\rightarrow$ user, group_id $\rightarrow$ group, is_accepted NN DF false, date NN CK date ≤ today)
R07 group_user(user_id $\rightarrow$ user, group_id $\rightarrow$ group)
R08 friend_request(id, user_id $\rightarrow$ user, friend_id $\rightarrow$ user, is_accepted NN DF false, date NN CK date ≤ today)
R09 comment(id, post_id $\rightarrow$ post, author $\rightarrow$ user, date NN CK date ≤ today, content NN)
R10 reaction(id, date, post_id $\rightarrow$ post, comment_id $\rightarrow$ comment, author_id $\rightarrow$ user, CK post_id NN or comment_id NN, type NN)
R11 post_tag(id,post_id $\rightarrow$ post, user_id $\rightarrow$ user)
R12 post_tag_not(id, post_tag_id $\rightarrow$ post_tag, date NN)
R13 group_request_not(id, group_req_id $\rightarrow$ group_request, date NN)
R14 friend_request_not(id, friend_req_id $\rightarrow$ friend_request, date NN)
R15 comment_not(id, comment $\rightarrow$ comment, date NN)
R16 reaction_not(id, reaction_id $\rightarrow$ reaction, date NN)
R17 group_ban(id, group $\rightarrow$ group, banned $\rightarrow$ user, banner $\rightarrow$ group_owner, reason NN, date NN CK date ≤ today, appeal_id $\rightarrow$ appeal)
R18 app_ban(id, banned $\rightarrow$ user, banner $\rightarrow$ admin, reason NN, date NN CK date ≤ today, appeal_id $\rightarrow$ appeal)
R19 appeal(id, reason)
R20 password_reset_tokens(email, token, created_at)

We've decided to use user roles within the users relation due to the lack of completeness for child entities. In contrast, for the notifications and bans tables, which are complete and have distinct attributes for each child entity, we've created separate relations for each child while inheriting common attributes from the parent.

2. Domains

Name Definition
Today DATE DEFAULT CURRENT_DATE
ReactionType ENUM ('LIKE', 'DISLIKE', 'HEART', 'STAR');

3. Schema validation

Table R01 (users)
Keys {id, username, email}
Functional Dependencies
FD0101 {id} $\rightarrow$ {username, password, email, academic_status_ display_name, is_private, image, email_verified_at}
FD0102 {username} $\rightarrow$ {id, password, email, academic_status, display_name, is_private, image, email_verified_at}
FD0103 {email} $\rightarrow$ {id, username, password, academic_status, display_name, is_private, image, email_verified_at}
Normal Form BCNF
Table R02 (post)
Keys {id}
Functional Dependencies
FD0201 {id} $\rightarrow$ {title, content, date, attachment, group_id, is_private}
Normal Form BCNF
Table R03 (friend)
Keys {id_friend_1, id_friend_2}
Functional Dependencies none
Normal Form BCNF
Table R04 (group)
Keys {id, name}
Functional Dependencies
FD0401 {id} $\rightarrow$ {name, description, is_private}
FD0402 {name} $\rightarrow$ {id, description, is_private}
Normal Form BCNF
Table R05 (group_owner)
Keys {user_id, group_id}
Functional Dependencies none
Normal Form BCNF
Table R06 (group_request)
Keys {id}
Functional Dependencies
FD0601 {id} $\rightarrow$ {user_id, group_id, is_accepted, false}
Normal Form BCNF
Table R07 (group_user)
Keys {user_id,group_id}
Functional Dependencies none
Normal Form BCNF
Table R08 (friend_request)
Keys {id}
Functional Dependencies
FD0801 {id} $\rightarrow$ {user_id, friend_id, is_accepted, date}
Normal Form BCNF
Table R09 (comment)
Keys {id}
Functional Dependencies
FD0901 {id} $\rightarrow$ {post_id, author, date, content}
Normal Form BCNF
Table R10 (reaction)
Keys {id}
Functional Dependencies
FD1001 {id} $\rightarrow$ { date, post_id, comment_id, author_id, type}
Normal Form BCNF
Table R11 (post_tag)
Keys {id}
Functional Dependencies
FD1101 {id} $\rightarrow$ {post_id, user_id}
Normal Form BCNF
Table R12 (post_tag_not)
Keys {id}
Functional Dependencies
FD1201 {id} $\rightarrow$ {post_tag_id, date}
Normal Form BCNF
Table R13 (group_request_not)
Keys {id}
Functional Dependencies
FD1301 {id} $\rightarrow$ {group_req_id, date}
Normal Form BCNF
Table R14 (friend_request_not)
Keys {id}
Functional Dependencies
FD1401 {id} $\rightarrow$ {friend_req_id, date}
Normal Form BCNF
Table R15 (comment_not)
Keys {id}
Functional Dependencies
FD1501 {id} $\rightarrow$ {comment_id, date}
Normal Form BCNF
Table R16 (reaction_not)
Keys {id}
Functional Dependencies
FD1601 {id} $\rightarrow$ {reaction_id, date}
Normal Form BCNF
Table R17 (group_ban)
Keys {id}
Functional Dependencies
FD1701 {id} $\rightarrow$ {group, banned, banner, reason, date}
Normal Form BCNF
Table R18 (app_ban)
Keys {id}
Functional Dependencies
FD1801 {id} $\rightarrow$ {banned, banner, reason, date}
Normal Form BCNF
Table R19 (appeal)
Keys {id}
Functional Dependencies
FD1901 {id} $\rightarrow$ {reason}
Normal Form BCNF
Table R20 (password_reset_tokens)
Keys {email}
Functional Dependencies
FD2001 {email} \rightarrow {token, created_at}
Normal Form BCNF

Since all the attributes in each non-trivial relation can be accessed through a superkey, we can say that these relations are in Boyce-Codd Normal Form (BCNF).

A6. Indexes, Triggers, Transactions and Database Population

1. Workload

Relation Relation name Order of magnitude Estimated growth
R01 user 1k 10 / day
R02 post 100k 1k / day
R03 friend 100 10 / day
R04 group 100 1 / day
R05 group_owner 100 1 / day
R06 group_request 1k 10 / day
R07 group_user 10k 10 / day
R08 friend_request 1k 10 / day
R09 comment 100k 1k / day
R10 reaction 100k 1k / day
R11 post_tag 10k 1k / day
R12 post_tag_not 10k 1k / day
R13 group_request_not 1k 10 / day
R14 friend_request_not 1k 10 / day
R15 comment_not 100k 1k / day
R16 reaction_not 100k 1k / day
R17 group_ban 100 1 / day
R18 app_ban 100 1 / day
R19 appeal 100 1 / day
R20 password_reset_tokens 500 10/day

2. Performance Indexes
Index IDX01
Index Relation users
Index attribute id
Index type Hash
Cardinality High
Clustering None
Justification The majority of searches on this table will use the id of the user as the parameter to select the respective row, and because we only want the attributes of one row, which at average will be an O(1) operation on an hash set, we selected that data structure instead of the BTrees.
SQL Code CREATE INDEX user_index ON users USING hash(id)
Index IDX02
Index Relation posts
Index attribute author
Index type B-Tree
Cardinality High
Clustering No
Justification We decided against clustering because the post table is a table with frequent inserts since posts are expected to be added daily. In addition, we chose to go with a B-tree because an author has many posts, and doing a hash table per author would not be feasible.
SQL Code CREATE INDEX author_post ON post USING btree(author)
Index IDX03
Index Relation comment
Index attribute post_id
Index type B-Tree
Cardinality Medium
Clustering No
Justification There is no clustering because the frequency of new comments is expected to be fairly high.
SQL Code CREATE INDEX post_comment ON comment USING btree(post_id)

4. Full Text Search Indexes

Index IDX04
Relation users
Attributes username, display_name
Type GIN
Clustering No
Justification The usernames and display names of the users will not be updated often, so we chose to go with a GIN index. 
ALTER TABLE users ADD COLUMN tsvectors TSVECTOR;

CREATE FUNCTION update_users_search() RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        NEW.tsvectors = setweight(to_tsvector('english', NEW.username), 'A')
         || setweight(to_tsvector('english', NEW.display_name), 'B');
    END IF;
    IF TG_OP = 'UPDATE' THEN
        IF (NEW.username <> OLD.username) THEN
            NEW.tsvectors = setweight(to_tsvector('english', NEW.username), 'A')
         || setweight(to_tsvector('english', NEW.display_name), 'B');
        END IF;
    END IF;
END;

CREATE TRIGGER update_users_search
    BEFORE INSERT OR UPDATE ON users
    FOR EACH ROW
    EXECUTE PROCEDURE update_users_search();

CREATE_INDEX user_search_idx ON users USING GIN (tsvectors);
Index IDX05
Relation post
Attributes title, content
Type GIN
Clustering No
Justification Full text search for posts based on the title of the post, its content and its author. As posts are not updated often, we chose to use GIN index, which has faster lookups. 
ALTER TABLE post ADD COLUMN tsvectors TSVECTOR;

CREATE FUNCTION update_post_search() RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        NEW.tsvectors = setweight(to_tsvector('english', NEW.title), 'A') ||
            setweight(to_tsvector('english', NEW.content), 'B') ||
            setweight(to_tsvector('english', NEW.username), 'C');
    END IF;
    IF TG_OP = 'UPDATE' THEN
        IF (NEW.title <> OLD.title OR NEW.content <> OLD.content OR NEW.username <> OLD.username) THEN
            NEW.tsvectors = setweight(to_tsvector('english', NEW.title), 'A') ||
                setweight(to_tsvector('english', NEW.content), 'B') ||
                setweight(to_tsvector('english', NEW.username), 'C');
        END IF;
    END IF;
END;

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

CREATE_INDEX post_search_idx ON post USING GIN(tsvectors);

5. Triggers

Trigger TRIGGER01
Description Alter the user id to the default one when a user is deleted 
CREATE OR REPLACE FUNCTION update_deleted_user() RETURNS TRIGGER AS 
$BODY$
BEGIN
    DELETE FROM post_tag_not WHERE id = (
        SELECT post_tag.id 
        FROM post_tag_not JOIN post_tag ON post_tag_not.post_id = post_tag.id
        JOIN users ON users.id = post_tag.user_id
        WHERE users.id = OLD.id
    );
    DELETE FROM post_tag_not WHERE id = (
        SELECT post_tag.id 
        FROM post_tag_not JOIN post_tag ON post_tag_not.post_id = post_tag.id
        JOIN users ON users.id = post_tag.user_id
        WHERE users.id = OLD.id
    );
    DELETE FROM group_request_not WHERE id = (
        SELECT group_request.id 
        FROM group_request_not JOIN group_request ON group_request_not.group_req_id = group_request.id
        JOIN users ON users.id = group_request.user_id
        WHERE users.id = OLD.id
    );
    DELETE FROM friend_request_not WHERE id = (
        SELECT friend_request.id 
        FROM friend_request_not JOIN friend_request ON friend_request_not.friend_req_id = friend_request.id
        JOIN users ON users.id = friend_request.user_id OR users.id = friend_request.friend_id
        WHERE users.id = OLD.id
    );
    DELETE FROM comment_not WHERE comment = (
        SELECT comment.id 
        FROM comment_not JOIN comment ON comment_not.comment = comment.id
        JOIN users ON users.id = comment.author
        WHERE users.id = OLD.id
    );
    DELETE FROM reaction_not WHERE reaction_id = (
        SELECT reaction.id 
        FROM reaction_not JOIN reaction ON reaction_not.reaction_id = reaction.id
        JOIN users ON users.id = reaction.author_id
        WHERE users.id = OLD.id
    );
    UPDATE post SET author = 0 WHERE author = OLD.id;
    UPDATE comment SET author = 0 WHERE user_id = OLD.id;
    UPDATE reaction SET author = 0 WHERE user_id = OLD.id;
    DELETE FROM group_owner WHERE user_id = OLD.id;
    RETURN OLD;
END
$BODY$ 
LANGUAGE plpgsql;

CREATE TRIGGER update_deleted_user_trigger
    AFTER DELETE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_deleted_user();
Trigger TRIGGER02
Description When a comment is added, a notification will be inserted 
CREATE OR REPLACE FUNCTION update_comment_not() RETURNS TRIGGER AS
$BODY$
BEGIN 
    INSERT INTO comment_not (comment_id, date) 
    VALUES (NEW.id, now());
    RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER update_comment_not_trigger
    AFTER INSERT ON comment
    FOR EACH ROW
    EXECUTE FUNCTION update_comment_not();
Trigger TRIGGER03
Description When a user is tagged in a post, a notification will be inserted 
CREATE OR REPLACE FUNCTION update_tag_not() RETURNS TRIGGER AS
$BODY$
BEGIN 
    INSERT ON post_tag_not (post_tag_id, date) 
    VALUES (NEW.id, now());
    RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER update_tag_not_trigger
    AFTER INSERT ON post_tag
    FOR EACH ROW
    EXECUTE FUNCTION update_comment_not();
Trigger TRIGGER04
Description When a reaction is added, a notification will be inserted 
CREATE OR REPLACE FUNCTION update_reaction_not() RETURNS TRIGGER AS
$BODY$
BEGIN 
    INSERT INTO reaction_not (reaction_id, date) 
    VALUES (NEW.id, now());
    RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER update_reaction_not_trigger
    AFTER INSERT ON reaction
    FOR EACH ROW
    EXECUTE FUNCTION update_reaction_not();
Trigger TRIGGER05
Description When a group request is added, a notification will be inserted 
CREATE OR REPLACE FUNCTION update_group_request_not() RETURNS TRIGGER AS
$BODY$
BEGIN 
    INSERT INTO group_request_not (group_request_id, date) 
    VALUES (NEW.id, now());
    RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER update_group_request_not_trigger
    AFTER INSERT ON group_request
    FOR EACH ROW
    EXECUTE FUNCTION update_group_request_not();
Trigger TRIGGER06
Description When a friend request is added, a notification will be inserted 
CREATE OR REPLACE FUNCTION update_friend_request_not() RETURNS TRIGGER AS
$BODY$
BEGIN 
    INSERT INTO friend_request_not (friend_request, date) 
    VALUES (NEW.id, now());
    RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER update_friend_request_not_trigger
    AFTER INSERT ON friend_request
    FOR EACH ROW
    EXECUTE FUNCTION update_friend_request_not();
Trigger TRIGGER07
Description A user can only add posts to groups which he belongs 
CREATE OR REPLACE FUNCTION check_belongs_group() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF (NOT EXISTS (SELECT * FROM group_user where group_user.user_id = NEW.author and group_user.group_id = NEW.group_id)
        AND (NEW.group_id <> null)) THEN
        RAISE EXCEPTION 'The user must belong to the group to add a post';
	END IF;
    RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER check_belongs_group_trigger
    BEFORE INSERT ON post
    FOR EACH ROW
    EXECUTE FUNCTION check_belongs_group();
Trigger TRIGGER08
Description A user cannot send a friend request to himself 
CREATE OR REPLACE FUNCTION check_friend_himself() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF (NEW.user_id = NEW.friend_id) THEN
        RAISE EXCEPTION 'A user cannot send friend request to himself.';
	END IF;
    RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER check_friend_of_himself
    BEFORE INSERT ON friend_request
    EXECUTE FUNCTION check_friend_himself();
Trigger TRIGGER09
Description A friend request must only be sent to non-friends. 
CREATE OR REPLACE FUNCTION check_friendship_exists() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF EXISTS (SELECT * FROM friends where (friend1 = NEW.user_id and friend2 = NEW.friend_id) or 
                                           (friend2 = NEW.user_id and friend1 = NEW.friend_id)) THEN
        RAISE EXCEPTION 'The users are already friends';
    END IF;
    RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER check_friendship_exists
    BEFORE INSERT ON friend_request
    EXECUTE FUNCTION check_friendship_exists();
Trigger TRIGGER10
Description A group request must only be sent to non-members of group. 
CREATE OR REPLACE FUNCTION check_group_request() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF EXISTS (SELECT * FROM group_user where user_id = NEW.user_id and group_id = NEW.group_id) THEN
        RAISE EXCEPTION 'The user is already a member of the group';
    END IF;
    RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER check_group_request
    BEFORE INSERT ON group_request
    EXECUTE FUNCTION check_group_request();
Trigger TRIGGER11
Description When a group request is accepted, the user is added to the group. 
CREATE OR REPLACE FUNCTION add_user_to_group() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF NEW.is_accepted = true THEN
        INSERT INTO group_user (user_id, group_id) VALUES (NEW.user_id, NEW.group_id);
    END IF;
    RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER add_user_to_group
    AFTER INSERT OR UPDATE ON group_request
    FOR EACH ROW
    WHEN (NEW.is_accepted = true)
    EXECUTE FUNCTION add_user_to_group();
Trigger TRIGGER12
Description When a friend request is accepted, the users are now friends. 
CREATE OR REPLACE FUNCTION add_friend() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF NEW.is_accepted = true THEN
        INSERT INTO friends (friend1, friend2) VALUES (NEW.user_id, NEW.friend_id);
    END IF;
    RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER add_friend
    AFTER INSERT OR UPDATE ON friend_request
    FOR EACH ROW
    WHEN (NEW.is_accepted = true)
    EXECUTE FUNCTION add_friend();

6. Transactions

Transaction TRAN01
Description When a User is deleted, his posts must become anonymous
Justification Due to the fact that a user deletes his account, his data must be kept accessible for the other users. However, this posts must be anonymized
Isolation Level SERIALIZABLE READ ONLY, to ensure that the data accessed during the transaction remains stable and consistent 
BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY;

UPDATE post 
SET author = 0 
WHERE author = OLD.id;

UPDATE comment 
SET author = 0 
WHERE user_id = OLD.id;

UPDATE reaction 
SET author = 0 
WHERE user_id = OLD.id;

DELETE 
FROM group_owner 
WHERE user_id = OLD.id;

DELETE 
FROM post_tag_not ptn 
JOIN post p ON(ptn.post_id = p.id) 
JOIN users u ON (p.author = u.id) 
WHERE u.id = OLD.id;

DELETE 
FROM group_request_not grn 
JOIN group_request gr ON(grn.group_request_id = gr.id) 
JOIN users u ON (g.user_id = u.id) 
WHERE u.id = OLD.id;

DELETE 
FROM friend_request_not frn 
JOIN friend_request fr ON(frn.friend_request = fr.id) 
JOIN users u ON (fr.user_id = u.id OR fr.friend_id = u.id) 
WHERE u.id = OLD.id;

DELETE 
FROM comment_not cn 
JOIN comment c ON(cn.comment_id = c.id) 
JOIN users u ON (c.author = u.id) 
WHERE u.id = OLD.id;

DELETE 
FROM reaction_not rn 
JOIN reaction r ON(rn.reaction_id = r.id) 
JOIN users u ON (r.author = u.id) 
WHERE u.id = OLD.id;

DELETE 
FROM group_user 
WHERE user_id = OLD.id;

END TRANSACTION;

Revision history

Changes made to the first submission:

  1. Added password_reset_tokens to the relational schema and the schema validation (23th november 2023)

GROUP2391, 23/10/2023

Editors

Annex A. SQL Code

A.1. Database schema

CREATE SCHEMA IF not exists lbaw2391;

DROP TABLE IF exists users CASCADE;
DROP TABLE IF exists post CASCADE;
DROP TABLE IF exists groups CASCADE;
DROP TABLE IF exists group_owner CASCADE;
DROP TABLE IF exists group_request CASCADE;
DROP TABLE IF exists group_user CASCADE;
DROP TABLE IF exists friend_request CASCADE;
DROP TABLE IF exists comment CASCADE;
DROP TABLE IF exists reaction CASCADE;
DROP TABLE IF exists post_tag_not CASCADE;
DROP TABLE IF exists group_request_not CASCADE;
DROP TABLE IF exists friend_request_not CASCADE;
DROP TABLE IF exists post_tag CASCADE;
DROP TABLE IF exists comment_not CASCADE;
DROP TABLE IF exists reaction_not CASCADE;
DROP TABLE IF exists group_ban CASCADE;
DROP TABLE IF exists app_ban CASCADE;
DROP TABLE IF exists friends CASCADE;
DROP TABLE IF exists appeal CASCADE;

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

DROP TYPE if exists reaction_types;
CREATE TYPE reaction_types AS ENUM ('LIKE', 'DISLIKE', 'HEART', 'STAR');

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

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username TEXT NOT NULL CONSTRAINT unique_username UNIQUE,
    email TEXT NOT NULL CONSTRAINT unique_email UNIQUE,
    password TEXT NOT NULL,
    image TEXT,
    academic_status TEXT,
    display_name TEXT,
    is_private BOOLEAN DEFAULT true NOT NULL,
    role INTEGER NOT NULL
);

CREATE TABLE groups (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL CONSTRAINT unique_group_name UNIQUE,
    is_private BOOLEAN DEFAULT true NOT NULL,
    description TEXT NOT NULL
);

CREATE TABLE post (
    id SERIAL PRIMARY KEY,
    author INTEGER REFERENCES users(id),
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    attachment TEXT,
    group_id INTEGER REFERENCES groups(id),
    is_private BOOLEAN NOT NULL,
    date TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL CHECK (date <= now())
);

CREATE TABLE friends (
    friend1 INTEGER REFERENCES users(id),
    friend2 INTEGER REFERENCES users(id),
    PRIMARY KEY (friend1, friend2)
);


CREATE TABLE group_owner(
    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 group_request(
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON UPDATE CASCADE,
    group_id INTEGER REFERENCES groups(id) ON UPDATE CASCADE,
    is_accepted BOOLEAN DEFAULT false NOT NULL,
    date TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL CHECK (date <= now())
);

CREATE TABLE group_user(
    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 friend_request(
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON UPDATE CASCADE,
    friend_id INTEGER REFERENCES users(id) ON UPDATE CASCADE,
    is_accepted BOOLEAN DEFAULT false,
    date TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL CHECK (date <= now())
);

CREATE TABLE comment(
    id SERIAL PRIMARY KEY,
    post_id INTEGER REFERENCES post(id) ON UPDATE CASCADE,
    author INTEGER REFERENCES users(id) ON UPDATE CASCADE,
    date TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL CHECK (date <= now()),
    content TEXT NOT NULL
);

CREATE TABLE reaction (
    id SERIAL PRIMARY KEY,
    post_id INTEGER REFERENCES post(id) ON UPDATE CASCADE,
    comment_id INTEGER REFERENCES comment(id) ON UPDATE CASCADE,
    author INTEGER REFERENCES users(id),
    type reaction_types NOT NULL,
    CONSTRAINT valid_post_and_comment_ck CHECK((post_id IS NULL and comment_id IS NOT NULL) or (post_id IS NOT NULL and comment_id IS NULL))
);

CREATE TABLE post_tag_not(
    id SERIAL PRIMARY KEY, 
    post_id INTEGER REFERENCES post(id) ON UPDATE CASCADE,
    date TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL CHECK (date <= now())
);

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

CREATE TABLE group_request_not(
    id SERIAL PRIMARY KEY, 
    group_request_id INTEGER REFERENCES group_request(id) ON UPDATE CASCADE,
    date TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL CHECK(date <= now())
);

CREATE TABLE friend_request_not(
    id SERIAL PRIMARY KEY, 
    friend_request INTEGER REFERENCES friend_request(id) ON UPDATE CASCADE,
    date TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL CHECK(date <= now())
);

CREATE TABLE comment_not(
    id SERIAL PRIMARY KEY, 
    comment_id INTEGER REFERENCES comment(id) ON UPDATE CASCADE,
    date TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL CHECK(date <= now())
);

CREATE TABLE reaction_not(
    id SERIAL PRIMARY KEY, 
    reaction_id INTEGER REFERENCES reaction(id) ON UPDATE CASCADE,
    date TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL CHECK(date <= now())
);

CREATE TABLE appeal(
    id SERIAL PRIMARY KEY,
    reason TEXT NOT NULL
);

CREATE TABLE group_ban(
    id SERIAL PRIMARY KEY,
    reason TEXT NOT NULL,
    group_owner_id INTEGER REFERENCES users(id) ON UPDATE CASCADE,
    banned_user_id INTEGER REFERENCES users(id) ON UPDATE CASCADE,
    group_id INTEGER REFERENCES groups(id) ON UPDATE CASCADE,
    appeal INTEGER REFERENCES appeal(id) ON UPDATE CASCADE,
    date TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL CHECK(date <= now())
);

CREATE TABLE app_ban(
    id SERIAL PRIMARY KEY,
    reason TEXT NOT NULL,
    admin_id INTEGER REFERENCES users(id) ON UPDATE CASCADE,
    banned_user_id INTEGER REFERENCES users(id) ON UPDATE CASCADE,

    appeal INTEGER REFERENCES appeal(id) ON UPDATE CASCADE,

    date TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL CHECK(date <= now())
);


CREATE INDEX user_index ON users USING hash(id);
CREATE INDEX post_comment ON comment USING btree(post_id);
CREATE INDEX author_post ON post USING btree(author);

-----------------------------------------
-- Full-text search
-----------------------------------------

-- Search users by username
ALTER TABLE users ADD COLUMN tsvectors TSVECTOR;

CREATE OR REPLACE FUNCTION update_users_search() RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        NEW.tsvectors = to_tsvector('english', NEW.username);
    END IF;
    IF TG_OP = 'UPDATE' THEN
        IF (NEW.username <> OLD.username) THEN
            NEW.tsvectors = to_tsvector('english', NEW.username);
        END IF;
    END IF;
    RETURN NEW;
END $$
LANGUAGE plpgsql;

CREATE TRIGGER update_users_search
    BEFORE INSERT OR UPDATE ON users
    FOR EACH ROW
    EXECUTE PROCEDURE update_users_search();

CREATE INDEX user_search_idx ON users USING GIN (tsvectors);

-----------------------------------------

-- Search posts
ALTER TABLE post ADD COLUMN tsvectors TSVECTOR;

CREATE OR REPLACE FUNCTION update_post_search() RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        NEW.tsvectors = setweight(to_tsvector('english', NEW.title), 'A') 
            || setweight(to_tsvector('english', NEW.content), 'B');
    END IF;
    IF TG_OP = 'UPDATE' THEN
        IF (OLD.title <> NEW.title OR NEW.content <> OLD.content) THEN
            NEW.tsvectors = setweight(to_tsvector('english', NEW.title), 'A') 
            || setweight(to_tsvector('english', NEW.content), 'B');
        END IF;
    END IF;
    RETURN NEW;
END$$
LANGUAGE plpgsql;

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

CREATE INDEX post_search_idx ON post USING GIN(tsvectors);

-- Search groups
ALTER TABLE groups ADD COLUMN tsvectors TSVECTOR;

CREATE OR REPLACE FUNCTION update_groups_search() RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        NEW.tsvectors = setweight(to_tsvector('english', NEW.name), 'A') ||
            setweight(to_tsvector('english', 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('english', NEW.name), 'A') ||
                setweight(to_tsvector('english', NEW.description), 'B');
        END IF;
    END IF;
    RETURN NEW;
END$$
LANGUAGE plpgsql;

CREATE TRIGGER update_groups_search
    BEFORE INSERT OR UPDATE ON groups
    FOR EACH ROW
    EXECUTE PROCEDURE update_groups_search();

CREATE INDEX groups_search_idx ON groups USING GIN(tsvectors);

-----------------------------------------
-- Triggers
-----------------------------------------

-- (TRIGGER01) If a user is deleted, it will change all his activity to anonymous
CREATE OR REPLACE FUNCTION update_deleted_user() RETURNS TRIGGER AS 
$BODY$
BEGIN
    DELETE FROM post_tag_not WHERE id = (
        SELECT post_tag.id 
        FROM post_tag_not JOIN post_tag ON post_tag_not.post_id = post_tag.id
        JOIN users ON users.id = post_tag.user_id
        WHERE users.id = OLD.id
    );
    DELETE FROM post_tag_not WHERE id = (
        SELECT post_tag.id 
        FROM post_tag_not JOIN post_tag ON post_tag_not.post_id = post_tag.id
        JOIN users ON users.id = post_tag.user_id
        WHERE users.id = OLD.id
    );
    DELETE FROM group_request_not WHERE id = (
        SELECT group_request.id 
        FROM group_request_not JOIN group_request ON group_request_not.group_req_id = group_request.id
        JOIN users ON users.id = group_request.user_id
        WHERE users.id = OLD.id
    );
    DELETE FROM friend_request_not WHERE id = (
        SELECT friend_request.id 
        FROM friend_request_not JOIN friend_request ON friend_request_not.friend_req_id = friend_request.id
        JOIN users ON users.id = friend_request.user_id OR users.id = friend_request.friend_id
        WHERE users.id = OLD.id
    );
    DELETE FROM comment_not WHERE comment = (
        SELECT comment.id 
        FROM comment_not JOIN comment ON comment_not.comment = comment.id
        JOIN users ON users.id = comment.author
        WHERE users.id = OLD.id
    );
    DELETE FROM reaction_not WHERE reaction_id = (
        SELECT reaction.id 
        FROM reaction_not JOIN reaction ON reaction_not.reaction_id = reaction.id
        JOIN users ON users.id = reaction.author_id
        WHERE users.id = OLD.id
    );
    UPDATE post SET author = 0 WHERE author = OLD.id;
    UPDATE comment SET author = 0 WHERE user_id = OLD.id;
    UPDATE reaction SET author = 0 WHERE user_id = OLD.id;
    DELETE FROM group_owner WHERE user_id = OLD.id;
    RETURN OLD;
END
$BODY$ 
LANGUAGE plpgsql;

CREATE TRIGGER update_deleted_user_trigger
    AFTER DELETE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_deleted_user();

-----------------------------------------

-- (TRIGGER02) Insert a notification when a comment is made in owner post
CREATE OR REPLACE FUNCTION update_comment_not() RETURNS TRIGGER AS
$BODY$
BEGIN 
    INSERT INTO comment_not (comment_id, date) 
    VALUES (NEW.id, now());
    RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER update_comment_not_trigger
    AFTER INSERT ON comment
    FOR EACH ROW
    EXECUTE FUNCTION update_comment_not();

-----------------------------------------

-- (TRIGGER03) When a user is tagged, a tagged notification is created
CREATE OR REPLACE FUNCTION update_tag_not() RETURNS TRIGGER AS
$BODY$
BEGIN 
    INSERT INTO post_tag_not (post_tag_id, date) 
    VALUES (NEW.id, now());
    RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER update_tag_not_trigger
    AFTER INSERT ON post_tag
    FOR EACH ROW
    EXECUTE FUNCTION update_comment_not();

-----------------------------------------

-- (TRIGGER04) When a reaction is made, a reaction notification is created
CREATE OR REPLACE FUNCTION update_reaction_not() RETURNS TRIGGER AS
$BODY$
BEGIN 
    INSERT INTO reaction_not (reaction_id, date) 
    VALUES (NEW.id, now());
    RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER update_reaction_not_trigger
    AFTER INSERT ON reaction
    FOR EACH ROW
    EXECUTE FUNCTION update_reaction_not();


-----------------------------------------

-- (TRIGGER05) When a user receives a group request, a notification is created
CREATE OR REPLACE FUNCTION update_group_request_not() RETURNS TRIGGER AS
$BODY$
BEGIN 
    INSERT INTO group_request_not (group_request_id, date) 
    VALUES (NEW.id, now());
    RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER update_group_request_not_trigger
    AFTER INSERT ON group_request
    FOR EACH ROW
    EXECUTE FUNCTION update_group_request_not();

-----------------------------------------

-- (TRIGGER06) When a friend request is added, a notification will be inserted
CREATE OR REPLACE FUNCTION update_friend_request_not() RETURNS TRIGGER AS
$BODY$
BEGIN 
    INSERT INTO friend_request_not (friend_request, date) 
    VALUES (NEW.id, now());
    RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER update_friend_request_not_trigger
    AFTER INSERT ON friend_request
    FOR EACH ROW
    EXECUTE FUNCTION update_friend_request_not();

-----------------------------------------

-- (TRIGGER07)  A user can only add posts to groups which he belongs
CREATE OR REPLACE FUNCTION check_belongs_group() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF (NOT EXISTS (SELECT * FROM group_user where group_user.user_id = NEW.author and group_user.group_id = NEW.group_id)
        AND (NEW.group_id <> null)) THEN
        RAISE EXCEPTION 'The user must belong to the group to add a post';
	END IF;
    RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER check_belongs_group_trigger
    BEFORE INSERT ON post
    FOR EACH ROW
    EXECUTE FUNCTION check_belongs_group();

-----------------------------------------

-- (TRIGGER08) A user cannot be friend with himself
CREATE OR REPLACE FUNCTION check_friend_himself() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF (NEW.user_id = NEW.friend_id) THEN
        RAISE EXCEPTION 'A user cannot send friend request to himself.';
	END IF;
    RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER check_friend_of_himself
    BEFORE INSERT ON friend_request
    EXECUTE FUNCTION check_friend_himself();


-----------------------------------------

-- (TRIGGER09)  A friend request must only be sent to non-friends
CREATE OR REPLACE FUNCTION check_friendship_exists() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF EXISTS (SELECT * FROM friends where (friend1 = NEW.user_id and friend2 = NEW.friend_id) or 
                                           (friend2 = NEW.user_id and friend1 = NEW.friend_id)) THEN
        RAISE EXCEPTION 'The users are already friends';
    END IF;
    RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER check_friendship_exists
    BEFORE INSERT ON friend_request
    EXECUTE FUNCTION check_friendship_exists();

-----------------------------------------

-- (TRIGGER10) A group request must only be sent to non-members of group
CREATE OR REPLACE FUNCTION check_group_request() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF EXISTS (SELECT * FROM group_user where user_id = NEW.user_id and group_id = NEW.group_id) THEN
        RAISE EXCEPTION 'The user is already a member of the group';
    END IF;
    RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER check_group_request
    BEFORE INSERT ON group_request
    EXECUTE FUNCTION check_group_request();

-----------------------------------------
    
-- (TRIGGER11) When a group request is accepted, the user is added to the groupr
CREATE OR REPLACE FUNCTION add_user_to_group() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF NEW.is_accepted = true THEN
        INSERT INTO group_user (user_id, group_id) VALUES (NEW.user_id, NEW.group_id);
    END IF;
    RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER add_user_to_group
    AFTER INSERT OR UPDATE ON group_request
    FOR EACH ROW
    WHEN (NEW.is_accepted = true)
    EXECUTE FUNCTION add_user_to_group();

----------------------------------------
    
-- (TRIGGER12) When a friend request is accepted, the users are now friends

CREATE OR REPLACE FUNCTION add_friend() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF NEW.is_accepted = true THEN
        INSERT INTO friends (friend1, friend2) VALUES (NEW.user_id, NEW.friend_id);
    END IF;
    RETURN NEW;
END
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER add_friend
    AFTER INSERT OR UPDATE ON friend_request
    FOR EACH ROW
    WHEN (NEW.is_accepted = true)
    EXECUTE FUNCTION add_friend();

A.2. Database population

INSERT INTO users (id, username, email, password, academic_status, display_name, is_private, role) VALUES 
    (1, 'johndoe', 'johndoe@example.com', 'password1', 'Undergraduate', 'John Doe', true, 2),
    (2, 'alanturing', 'alanturing@example.com', 'password2', 'Professor', 'Alan Turing', false, 2),
    (3, 'adalovelace', 'adalovelace@example.com', 'password3', 'Graduate', 'Ada Lovelace', true, 2),
    (4, 'admin', 'admin@example.com', 'adminpassword', 'Administrator', 'Admin User', false, 1);

INSERT INTO friend_request(user_id, friend_id, is_accepted, date) VALUES
    (2, 3, true, '1940-01-28 12:00:00'),
    (1, 4, true, '2023-05-17 15:30:00');

INSERT INTO groups(id, name, description, is_private) VALUES 
    (1, 'Prolog Enthusiasts', 'A community for discussing Prolog programming language and related topics', false),
    (2, 'Tech Enthusiasts', 'A group dedicated to discussing the latest technology trends and innovations', true);

INSERT INTO group_user (user_id, group_id) VALUES
    (1, 2),
    (1, 1),
    (2, 1),
    (2, 2);

INSERT INTO group_owner (group_id, user_id) VALUES
    (1, 1),
    (2, 2);

INSERT INTO post (id, author, title, content, attachment, group_id, is_private, date) VALUES
    (1, 1, 'Exciting AI Research Findings', 'Exciting new research findings in the field of artificial intelligence!', 'ai_research.pdf', 1, false, NOW() - INTERVAL '1 day'),
    (2, 2, 'Renewable Energy Discussion', 'Important discussion on renewable energy solutions for the future.', 'renewable_energy.png', 2, true, NOW() - INTERVAL '1 day'),
    (3, 3, 'Quantum Computing Paper Published', 'Just published my new research paper on quantum computing!', 'quantum_paper.pdf', null, false, NOW() - INTERVAL '3 days'),
    (4, 1, 'SpaceXs Mars Colonization Plans', 'Exciting news for all tech enthusiasts - SpaceX plans to colonize Mars!', null, null, true, NOW() - INTERVAL '4 days'),
    (5, 2, 'Exploring 6G Technology', 'Discussing the potential of 6G technology and its impact on communication.', null, 2, false, NOW() - INTERVAL '5 days'),
    (6, 1, 'History of Computer Science Lecture', 'Attended a fascinating lecture on the history of computer science today.', null, 1, false, NOW() - INTERVAL '6 days'),
    (7, 3, 'Challenges in Quantum Computing', 'Exploring the challenges and opportunities in the field of quantum computing.', null, 2, false, NOW() - INTERVAL '7 days'),
    (8, 4, 'Future of AI and Society', 'A sneak peek into the future of AI and its implications for society.', null, null, true, NOW() - INTERVAL '8 days'),
    (9, 2, 'Python 4.0 Announcement', 'Exciting times ahead for programmers with the launch of the new Python 4.0!', null, 1, false, NOW() - INTERVAL '9 days');


INSERT INTO comment (id, post_id, author, content, date) VALUES
    (1, 1, 3, 'This is amazing! Can you share more details about the AI research findings?', NOW() - INTERVAL '1 day'),
    (2, 1, 4, 'Im eager to read the research paper. Please share the link when its available!', NOW() - INTERVAL '23 hours'),
    (3, 2, 1, 'Renewable energy is the future, and we need to invest more in it.', NOW() - INTERVAL '1 day'),
    (4, 4, 3, 'The smartphone industry is advancing rapidly. Any thoughts on sustainability?', NOW() - INTERVAL '2 days'),
    (5, 7, 2, 'AIs impact on society is a crucial discussion. Lets explore it further.', NOW() - INTERVAL '2 days'),
    (6, 8, 4, 'Python 4.0 sounds exciting! What are the new features in this version?', NOW() - INTERVAL '3 days'),
    (7, 5, 1, 'Id love to hear more about the history of computer science. Please share insights!', NOW() - INTERVAL '4 days'),
    (8, 3, 2, 'Congratulations on your new research paper on quantum computing!', NOW() - INTERVAL '4 days');

INSERT INTO reaction (post_id, comment_id ,author, type) VALUES
    (1, NULL, 3, 'LIKE'),
    (NULL, 1, 2, 'HEART'),
    (1, NULL, 1, 'LIKE'),
    (3, NULL, 2, 'HEART'), 
    (NULL, 5, 4, 'STAR'),
    (6, NULL, 3, 'DISLIKE'),
    (NULL, 7, 2, 'DISLIKE');
Clone this wiki locally