Skip to content

ebd

Jump to bottom
JoaoCostaIFG edited this page May 24, 2021 · 129 revisions

EBD: Database Specification Component

Assembling a community-driven database of knowledge by asking and answering questions.

A4: Conceptual Data Model

This component describes the artefacts that will support the storage and retrieval of information described in the requirements specification.

1. Class diagram

UML conceptual data model

A5: Relational Schema, validation and schema refinement

The goal of this artefact is to present the relational schema obtained from the UML along with the SQL code.

1. Relational Schema

The following Relational Schema is in compact notation. It includes the relation schemas, attributes, domains, primary keys, foreign keys, and other integrity rules, such as UNIQUE, DEFAULT, NOT NULL, and CHECK.

Relation reference Relation Compact Notation
R01 user(id, name*, username* UK NN, password*, email* UK NN, about*, picture* UK)
R02 moderator(id -> user)
R03 administrator(id -> moderator)
R04 ban(id_user -> user, id_admin -> administrator, date NN DF Today CK date <= today, reason)
R05 news(id, author -> administrator NN, title NN, body, date NN DF Today CK date <= today)
R06 topic_proposal(id, id_user -> user NN , id_admin -> administrator, topic_name NN, date NN DF Today CK date <= today, reason, accepted NN DF false)
R07 achievement(id, title NN UK, body NN)
R08 achieved(id_user -> User, id_achievement -> achievement, date NN DF Today CK date <= today)
R09 post(id, id_owner -> user NN, body NN, date NN DF Today CK date <= today)
R10 answer(id -> post)
R11 question(id -> post, accepted_answer -> answer, title UK NN, bounty NN CK bounty>=0 AND bounty <= 500, closed NN DF false
R12 answer_question(id_answer -> answer, id_question -> question)
R13 comment(id -> post, id_question -> question, id_answer -> answer CK (id_question=NULL and id_answer!=NULL) or (id_answer=NULL and id_question!=NULL)
R14 vote(id_post -> post, id_user -> user, value CK value=1 OR value=-1)
R15 edit_proposal(id, id_post -> post NN, id_user -> user NN, id_moderator -> moderator, body NN, accepted NN DF false)
R16 topic(id, name NN UK)
R17 topic_question(id_topic -> topic, id_question -> question)
R18 report(id_post -> post, reporter -> user, date NN DF Today CK date <= today, reason, state NN DF Pending, reviewer -> moderator)
R19 notification(id, date NN DF Today CK date <= today, title NN, body, recipient -> user NN)
R20 notification_achievement(id -> notification, id_achievement -> achievement NN)
R21 notification_post(id -> notification, id_post -> post NN)

1.1 Generalisation Mapping

The E/R style was chosen to map Users, Moderators, and Admins. It was chosen because it allows hierarchization in a direct way. Ex: A moderator reviewing a topic proposal can either be a moderator or an administrator, and this type of schema allows for a direct and efficient query.

The E/R style was also chosen to map Post, Question, Answer, and Comment, since this generalization is overlapping and has a decent number of subclasses.

Lastly, the E/R method was used as well when mapping the Notification hierarchy because it is overlapping.

2. Domains

Domain Name Domain Specification
Today DATE DEFAULT CURRENT_DATE
ReportState ENUM ('Pending', 'Approved', 'Rejected')

3. Schema validation

To validate the Relational Schema obtained from the Conceptual Model, all functional dependencies are identified and the normalization of all relation schemas are accomplished.

TABLE R01 user
Keys { id }, { email }, { username }
Functional Dependencies:
FD0101 id → {email, name, password, about, picture}
FD0102 email → {id}
FD0103 username → {id}
NORMAL FORM BCNF
TABLE R02 moderator
Keys { id }
Functional Dependencies: ---
NORMAL FORM BCNF
TABLE R03 administrator
Keys { id }
Functional Dependencies: ---
NORMAL FORM BCNF
TABLE R04 ban
Keys { id_user }
Functional Dependencies:
FD0401 id_user → {id_admin, date, reason}
NORMAL FORM BCNF
TABLE R05 news
Keys { id }
Functional Dependencies:
FD0501 id → {author, title, body, date}
NORMAL FORM BCNF
TABLE R06 topic_proposal
Keys { id }
Functional Dependencies:
FD0601 id → {id_user, id_admin, topic_name, date, reason, accepted}
NORMAL FORM BCNF
TABLE R07 achievement
Keys { id, title }
Functional Dependencies:
FD0701 id → {title, body}
FD0702 title → {id}
NORMAL FORM BCNF
TABLE R08 achieved
Keys { {id_user, id_achievement} }
Functional Dependencies:
FD0801 id_user, id_achievement → {date}
NORMAL FORM BCNF
TABLE R09 post
Keys { id }
Functional Dependencies:
FD0901 id → {id_owner, body, date}
NORMAL FORM BCNF
TABLE R10 answer
Keys { id }
Functional Dependencies:
NORMAL FORM BCNF
TABLE R11 question
Keys { id, title }
Functional Dependencies:
FD1401 id → {accepted_answer, title, bounty, closed}
FD1402 title → {id}
NORMAL FORM BCNF
TABLE R12 answer_question
Keys { {id_answer, id_question} }
Functional Dependencies:
NORMAL FORM BCNF
TABLE R13 comment
Keys { id }
Functional Dependencies:
FD1301 id → {id_question, id_answer}
NORMAL FORM BCNF
TABLE R14 vote
Keys { {id_post, id_user} }
Functional Dependencies:
FD1001 id_post, id_user → {value}
NORMAL FORM BCNF
TABLE R15 edit_proposal
Keys { id }
Functional Dependencies:
FD1101 id → {id_post, id_user, id_moderator, body, accepted}
NORMAL FORM BCNF
TABLE R16 topic
Keys { id, name }
Functional Dependencies:
FD1501 id → {name}
FD1502 name → {id}
NORMAL FORM BCNF
TABLE R17 topic_question
Keys { {id_topic, id_question} }
Functional Dependencies: ---
NORMAL FORM BCNF
TABLE R18 report
Keys { {id_post, reporter} }
Functional Dependencies:
FD1701 id_post, reporter → {date, reason, state, reviewer}
NORMAL FORM BCNF
TABLE R19 notification
Keys { id }
Functional Dependencies:
FD1801 id → {date, title, body, recipient}
NORMAL FORM BCNF
TABLE R20 notification_achievement
Keys { id }
Functional Dependencies:
FD1901 id → {id_achievement}
NORMAL FORM BCNF
TABLE R21 notification_post
Keys { id }
Functional Dependencies:
FD2001 id → {id_post}
NORMAL FORM BCNF

Justification of the BCNF:
The schema is in the Boyce–Codd Normal Form (BCNF) because all its relations are also in the BCNF.

A6: Indexes, triggers, user functions, transactions and population

This artefact contains tuple estimations, the most frequent queries, most frequent updates, indices, triggers, and transactions.

1. Database Workload

1.1. Tuple Estimation

Relation reference Relation Name Order of magnitude Estimated growth
R01 user tens of thousands dozens per day
R02 moderator hundreds units per week
R03 administrator dozens units per month
R04 ban hundreds units per week
R05 news dozens units per month
R06 topic_proposal hundreds units per week
R07 achievement dozens units per year
R08 achieved tens of thousands units per day
R09 post hundreds of thousands dozens per day
R10 answer tens of thousands dozens per day
R11 question tens of thousands dozens per day
R12 answer_question tens of thousands units per day
R13 comment tens of thousands units per day
R14 vote hundreds of thousands hundreds per day
R15 edit_proposal hundreds units per day
R16 topic hundreds units per month
R17 topic_question tens of thousands dozens per day
R18 report thousands units per day
R19 notification hundreds of thousands hundreds per day
R20 notification_achievement hundreds of thousands dozens per day
R21 notification_post tens of thousands dozens per day

1.2. Frequent Queries

Query SELECT01
Description Search question by topic
Frequency hundreds per day 
SELECT post.id, question.title, ts_rank(topic.search, query) AS rank
FROM question JOIN post ON(question.id = post.id), topic JOIN topic_question ON(topic.id = topic_question.id_topic),
  to_tsquery($topic_query) as query
WHERE question.id = topic_question.id_question AND
  topic.search @@ query
ORDER BY rank;
Query SELECT02
Description Search question by its content and owner
Frequency hundreds per day 
SELECT "user".id, name, username, email, about, picture, reputation,
	ts_rank_cd("user".search, user_query) AS user_rank, ts_rank_cd(question.search, question_query) AS question_rank
FROM "user" JOIN post ON(post.id_owner = "user".id) JOIN question ON(question.id = post.id),
	to_tsquery($user_query) as user_query, to_tsquery($question_query) as question_query
WHERE "user".search @@ user_query AND
  question.search @@ question_query
ORDER BY question_rank, user_rank;
Query SELECT03
Description Get answers of a question
Frequency hundreds per day 
SELECT answer.id, post.body, post.date
FROM answer
	JOIN answer_question
    	ON(answer_question.id_answer = answer.id)
	JOIN post ON post.id = answer.id
WHERE answer_question.id_question = $idQuestion;
Query SELECT04
Description Get comments of a question
Frequency hundreds per day 
SELECT comment.id, comment_post.body
FROM comment JOIN post AS comment_post USING(id)
WHERE comment.id_question = $idQuestion;
Query SELECT05
Description Get comments of an answer
Frequency hundreds per day 
SELECT comment.id, comment_post.body
FROM comment JOIN post AS comment_post USING(id)
WHERE comment.id_answer = $idAnswer

User Related Queries

Query SELECT06
Description Get User Information
Frequency hundreds per day 
SELECT id, name, username, email, about, picture, reputation
FROM "user"
WHERE "user".id = $userId;
Query SELECT07
Description Get User Achievements
Frequency dozens per month 
SELECT achievement.title, achievement.body
FROM Achievement
  JOIN Achieved ON (Achievement.id = Achieved.id_achievement)
WHERE user_id = $userId;
Query SELECT08
Description Get User Notifications
Frequency hundreds per month 
SELECT id, title, body
FROM notification
WHERE notification.recipient = $userId;
Query SELECT09
Description Get Pending Topic Proposals
Frequency dozens per month 
SELECT id, topic_name, date, reason
FROM topic_proposal
WHERE accepted = false;
Query SELECT10
Description Get All Pending Reports
Frequency dozens per month 
SELECT id_post, reporter, date, reason
FROM report
WHERE state = 'Pending';
Query SELECT11
Description Get All Posts within a date range
Frequency dozens per day 
SELECT body, score, date
FROM Post
WHERE date between $start_date AND $end_date
Query SELECT12
Description Get All Posts of a User
Frequency dozens per day 
SELECT body, score, date
FROM Post
WHERE id_owner = $id_user;
Query SELECT13
Description Search user by his personal information
Frequency units per day 
SELECT "user".id, name, username, email, about, picture, reputation
FROM "user" JOIN post ON(post.id_owner = "user".id) JOIN question ON(question.id = post.id)
WHERE "user".search @@ to_tsquery($user_info);

1.3. Frequent Updates

Query UPDATE01
Description Accept an answer
Frequency dozens per month 
UPDATE question SET
  accepted_answer = $accepted_answer, closed = TRUE
  WHERE id = $id
Query UPDATE02
Description Update user information
Frequency dozens per month 
UPDATE "user" SET
  name = $name, about = $about, picture = $picture
  WHERE id = $id
Query UPDATE03
Description Update vote
Frequency hundreds per day 
UPDATE vote SET
  value = $value
  WHERE id_post = $id_post AND id_user = $id_user

1.4. Frequent Inserts

Query INSERT01
Description Creates new post
Frequency dozens per day 
INSERT INTO post (id_owner, body) VALUES
  ($id_user, $body)
Query INSERT02
Description Create new question
Frequency dozens per day 
INSERT INTO question (id, title, bounty) VALUES
  ($id, $title, $bounty)
Query INSERT03
Description Create new answer
Frequency dozens per day 
INSERT INTO answer (id) VALUES
  ($id)
Query INSERT04
Description Create new comment to a question
Frequency units per day 
INSERT INTO comment (id, id_question) VALUES
  ($id, $id_question)
Query INSERT05
Description Create a new comment to an answer
Frequency units per day 
INSERT INTO comment (id, id_answer) VALUES
  ($id, $id_answer)
Query INSERT06
Description New user registered*
Frequency dozens per day 
INSERT INTO "user" (username, password, email) VALUES
  ($username, $password, $email)
Query INSERT07
Description Votes on a post
Frequency hundreds per day 
INSERT INTO vote (id_post, id_user, value) VALUES
  ($id_post, $id_user, $value)

1.5. Frequent Deletes

Query DELETE01
Description Delete vote
Frequency units per day 
DELETE FROM vote
  WHERE id_post = $id_post AND id_user = $id_user
Query DELETE02
Description Delete post
Frequency units per day 
DELETE FROM post
  WHERE id = $id

2. Proposed Indices

2.1. Performance Indices

Index IDX01
Related queries SELECT11
Relation post
Attribute date
Type B-tree
Cardinality high
Clustering No
Justification To allow the search of post by range faster; B-Tree to allow ranged queries
SQL code CREATE INDEX date_idx ON post USING btree ("date")
Index IDX02
Related queries SELECT03
Relation answer_question
Attribute id_question
Type Hash
Cardinality medium
Clustering Yes
Justification To allow searching for all answers of a specific question; Used when loading a question page, so executed very frequently
SQL code CREATE INDEX question_idx ON answer_question USING hash (id_question)
Index IDX03
Related queries SELECT12
Relation post
Attribute id_owner
Type Hash
Cardinality high
Clustering No
Justification To allow searching for all posts of a given user. Highly used when requesting a user's profile
SQL code CREATE INDEX owner_idx ON post USING hash (id_owner)
Index IDX04
Related queries SELECT08
Relation achieved
Attribute id_user
Type Hash
Cardinality high
Clustering No
Justification To allow searching for all achievements of a given user. Highly used when requesting a user's profile
SQL code CREATE INDEX user_idx ON achieved USING hash (id_user)
Index IDX05
Related queries SELECT11
Relation report
Attribute state
Type Hash
Cardinality Small
Clustering Yes
Justification To allow searching for all pending reports. Highly used on the moderation page
SQL code CREATE INDEX state_idx ON report USING hash ("state")

2.2. Full-text Search Indices

Index IDX01
Related queries SELECT13
Relation user
Attribute name and username
Type GiST
Clustering No
Justification The webpage is centered in finding the information you need. The search feature includes user search.
SQL code CREATE INDEX user_search_idx ON "user" USING GiST (search)
Index IDX02
Related queries SELECT02
Relation question
Attribute title and body
Type GiST
Clustering No
Justification The webpage is centered in finding the information you need. The search feature includes question searchs (by title).
SQL code CREATE INDEX question_search_idx ON question USING GiST (search)
Index IDX03
Related queries SELECT01
Relation topic
Attribute name
Type GIN
Clustering No
Justification The webpage is centered in finding the information you need. The search feature includes topic searchs.
SQL code CREATE INDEX topic_search_idx ON topic USING GIN (search)

3. Triggers

Trigger TRIGGER01
Description The score of a post is the sum of its votes 
CREATE OR REPLACE FUNCTION on_score_change()
RETURNS TRIGGER
AS $$
  DECLARE
    val integer;
    val2 integer;
    post_id integer;
    owner_id integer;
  BEGIN
    IF (TG_OP = 'DELETE') THEN
      post_id := OLD.id_post;
    ELSIF (TG_OP = 'UPDATE') THEN
      post_id := NEW.id_post;
    ELSIF (TG_OP = 'INSERT') THEN
      post_id := NEW.id_post;
    END IF;

    -- update the question score
    val := (SELECT sum(value)
            FROM post JOIN vote ON (post.id = vote.id_post)
            WHERE id = post_id);
    IF val IS NULL THEN
      val := 0;
    END IF;
    UPDATE post
    SET score = val
    WHERE id = post_id;

    -- update the question's owner reputation
    owner_id := (SELECT id_owner
                  FROM post
                  WHERE id = post_id);
    -- score obtaining by votes on the user's posts
    val := (SELECT sum(score)
            FROM post
            JOIN "user" ON (post.id_owner = "user".id)
            WHERE "user".id = owner_id);
    IF val IS NULL THEN
      val := 0;
    END IF;
    -- score obtained by achievements (each is worth 10 reputation)
    val2 := (SELECT count(*)
              FROM "user"
              JOIN achieved ON (achieved.id_user = "user".id)
              JOIN achievement ON (achieved.id_achievement = achievement.id)
              WHERE "user".id = owner_id) * 10;
    UPDATE "user" as u
    SET reputation = val + val2
    WHERE owner_id = u.id;

    RETURN NULL; -- result is ignored since this is an AFTER trigger
  END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS update_score_vote ON vote CASCADE;
CREATE TRIGGER update_score_vote
AFTER DELETE OR INSERT OR UPDATE ON vote
FOR EACH ROW
EXECUTE FUNCTION on_score_change();
Trigger TRIGGER02
Description Updates the user reputation whenever they receive an achievement: reputation is based on achievements and score. 
CREATE OR REPLACE FUNCTION on_achievement_change()
RETURNS TRIGGER
AS $$
  DECLARE
    val_delta integer;
    owner_id integer;
  BEGIN
    IF (TG_OP = 'DELETE') THEN
      owner_id := OLD.id_user;
      val_delta = -10;
    ELSIF (TG_OP = 'UPDATE') THEN
      owner_id := NEW.id_user;
      val_delta = 0;
    ELSIF (TG_OP = 'INSERT') THEN
      owner_id := NEW.id_user;
      val_delta = 10;
    END IF;

    UPDATE "user" as u
    SET reputation = u.reputation + val_delta
    WHERE owner_id = u.id;

    RETURN NULL; -- result is ignored since this is an AFTER trigger
  END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS update_score_achievement ON vote CASCADE;
CREATE TRIGGER update_score_achievement
AFTER DELETE OR INSERT OR UPDATE ON achieved
FOR EACH ROW
EXECUTE FUNCTION on_achievement_change();
Trigger TRIGGER03
Description Used in order to achieve better search results for users 
CREATE OR REPLACE FUNCTION user_search_update()
RETURNS TRIGGER
AS $$
  BEGIN
  IF (TG_OP = 'INSERT') THEN
    NEW.search = (setweight(to_tsvector('english', NEW.username), 'A') || setweight(to_tsvector('english', COALESCE(NEW.name, '')), 'B'));
  ELSIF (TG_OP = 'UPDATE') THEN
    IF NEW.username <> OLD.username or NEW.name <> OLD.name THEN
      NEW.search = (setweight(to_tsvector('english', NEW.username), 'A') || setweight(to_tsvector('english', COALESCE(NEW.name, '')), 'B'));
    END IF;
  END IF;

  RETURN NEW;
  END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS user_search_update_trigger ON "user" CASCADE;
CREATE TRIGGER user_search_update_trigger
BEFORE INSERT OR UPDATE
ON "user"
FOR EACH ROW EXECUTE FUNCTION user_search_update();
Trigger TRIGGER04
Description Used in order to achieve better search results for questions 
CREATE OR REPLACE FUNCTION question_search_update()
RETURNS TRIGGER
AS $$
  DECLARE
    b text;
    topics text;
  BEGIN
    -- get the body
    b := (SELECT body
          FROM post
          WHERE post.id = NEW.id);

    -- get the topics
    topics := (SELECT string_agg(name, ' ')
                FROM topic JOIN topic_question ON (topic.id = topic_question.id_topic)
                  JOIN question ON (question.id = topic_question.id_question)
                WHERE question.id = NEW.id);

    IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
      NEW.search = (setweight(to_tsvector('english', NEW.title), 'A') ||
                    setweight(to_tsvector('english', b), 'B') ||
                    setweight(to_tsvector('english', COALESCE(topics, '')), 'C'));
    END IF;

    RETURN NEW;
  END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS question_search_update_trigger ON question CASCADE;
CREATE TRIGGER question_search_update_trigger
BEFORE INSERT OR UPDATE
ON question
FOR EACH ROW EXECUTE FUNCTION question_search_update();
Trigger TRIGGER05
Description Used in order to achieve better search results for topics 
CREATE OR REPLACE FUNCTION topic_search_update()
RETURNS TRIGGER
AS $$
  BEGIN
    IF (TG_OP = 'INSERT') THEN
      NEW.search = to_tsvector('english', NEW.name);
    ELSIF (TG_OP = 'UPDATE') THEN
      IF NEW.name <> OLD.name THEN
        NEW.seach = to_tsvector('english', NEW.name);
      END IF;
    END IF;

    RETURN NEW;
  END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS topic_search_update_trigger ON topic CASCADE;
CREATE TRIGGER topic_search_update_trigger
BEFORE INSERT OR UPDATE
ON topic
FOR EACH ROW EXECUTE FUNCTION topic_search_update();
Trigger TRIGGER06
Description A closed question cannot be re-opened. 
CREATE OR REPLACE FUNCTION reopen_question() RETURNS TRIGGER AS
$$
BEGIN
    IF (OLD.closed <> NEW.closed and OLD.closed = TRUE) THEN
      RAISE EXCEPTION 'A closed question can not be re-opened.';
    END IF;
    RETURN NEW;
END
$$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS reopen_question_trigger ON question CASCADE;
CREATE TRIGGER reopen_question_trigger
BEFORE UPDATE ON question
FOR EACH ROW
EXECUTE PROCEDURE reopen_question();
Trigger TRIGGER07
Description A user cannot vote on their own post. 
CREATE OR REPLACE FUNCTION vote() RETURNS TRIGGER
AS $$
  DECLARE
    owner integer;
  BEGIN
      owner := (SELECT id_owner FROM post WHERE id = NEW.id_post);

      IF (owner = NEW.id_user) THEN
        RAISE EXCEPTION 'A user can not vote on its own post.';
      END IF;
      RETURN NEW;
  END
$$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS vote_trigger ON vote CASCADE;
CREATE TRIGGER vote_trigger
BEFORE INSERT OR UPDATE ON vote
FOR EACH ROW
EXECUTE PROCEDURE vote();
Trigger TRIGGER08
Description Notification generalization must be disjoint. Notification_achievement id cannot exist already. 
CREATE OR REPLACE FUNCTION notification_generalization() RETURNS TRIGGER
AS $$
  BEGIN
      IF EXISTS (SELECT *
        FROM notification_post, notification_achievement
        WHERE notification_post.id = New.id OR notification_achievement.id = New.id) THEN
        RAISE EXCEPTION 'Notification must be disjoint.';
      END IF;
      RETURN NEW;
  END
$$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS notification_achievement_generalization_trigger ON notification_achievement CASCADE;
CREATE TRIGGER notification_achievement_generalization_trigger
BEFORE INSERT OR UPDATE ON notification_achievement
FOR EACH ROW
EXECUTE PROCEDURE notification_generalization();
Trigger TRIGGER09
Description Notification generalization must be disjoint. Notification_post id cannot exist already. 
DROP TRIGGER IF EXISTS notification_post_generalization_trigger ON notification_post CASCADE;
CREATE TRIGGER notification_post_generalization_trigger
BEFORE INSERT OR UPDATE ON notification_post
FOR EACH ROW
EXECUTE PROCEDURE notification_generalization();
Trigger TRIGGER10
Description Post generalization must be disjoint. Answer id cannot exist already. 
CREATE OR REPLACE FUNCTION post_generalization() RETURNS TRIGGER
AS $$
  BEGIN
      IF EXISTS (SELECT *
        FROM question, answer, comment
        WHERE question.id = New.id OR answer.id = New.id OR comment.id = New.id) THEN
        RAISE EXCEPTION 'Post must be disjoint.';
      END IF;
      RETURN NEW;
  END
$$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS post_answer_generalization_trigger ON answer CASCADE;
CREATE TRIGGER post_answer_generalization_trigger
BEFORE INSERT OR UPDATE ON answer
FOR EACH ROW
EXECUTE PROCEDURE post_generalization();
Trigger TRIGGER11
Description Post generalization must be disjoint. Question id cannot exist already. 
DROP TRIGGER IF EXISTS post_question_generalization_trigger ON question CASCADE;
CREATE TRIGGER post_question_generalization_trigger
BEFORE INSERT ON question
FOR EACH ROW
EXECUTE PROCEDURE post_generalization();
Trigger TRIGGER12
Description Post generalization must be disjoint. Comment id cannot exist already. 
DROP TRIGGER IF EXISTS post_comment_generalization_trigger ON comment CASCADE;
CREATE TRIGGER post_comment_generalization_trigger
BEFORE INSERT OR UPDATE ON comment
FOR EACH ROW
EXECUTE PROCEDURE post_generalization();
Trigger TRIGGER13
Description Trigger for achievement 1: First post of a user 
CREATE OR REPLACE FUNCTION achievement_first_question() RETURNS TRIGGER
AS $$
  DECLARE
    owner_id integer;
    question_amount integer;
  BEGIN
      owner_id := (SELECT post.id_owner AS owner_id FROM post JOIN question ON(post.id = question.id) WHERE post.id = NEW.id);
      question_amount := (SELECT COUNT(*) FROM post JOIN question ON(post.id = question.id) WHERE post.id_owner = owner_id);

      IF (question_amount = 1) THEN
        IF NOT EXISTS (SELECT * FROM achieved WHERE id_user = owner_id AND id_achievement = 1) THEN
          INSERT INTO achieved(id_user, id_achievement) VALUES (owner_id, 1);
        END IF;
      END IF;
      RETURN NEW;
  END
$$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS achievement_first_question_trigger ON question CASCADE;
CREATE TRIGGER achievement_first_question_trigger
AFTER INSERT ON question
FOR EACH ROW
EXECUTE PROCEDURE achievement_first_question();
Trigger TRIGGER14
Description Trigger for achievement 2: First accepted answer of a user 
CREATE OR REPLACE FUNCTION achievement_first_accepted_answer() RETURNS TRIGGER
AS $$
  DECLARE
    accepted_answer_id integer;
    answer_owner_id integer;
  BEGIN
      accepted_answer_id := NEW.accepted_answer;

      IF (accepted_answer_id IS NOT NULL) THEN
        answer_owner_id := (SELECT id_owner FROM post WHERE id = accepted_answer_id);
        IF NOT EXISTS (SELECT * FROM achieved WHERE id_user = answer_owner_id AND id_achievement = 2) THEN
          INSERT INTO achieved(id_user, id_achievement) VALUES (answer_owner_id, 2);
        END IF;
      END IF;
      RETURN NEW;
  END
$$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS achievement_first_accepted_answer_trigger ON question CASCADE;
CREATE TRIGGER achievement_first_accepted_answer_trigger
AFTER INSERT OR UPDATE ON question
FOR EACH ROW
EXECUTE PROCEDURE achievement_first_accepted_answer();
Trigger TRIGGER15
Description Trigger for achievements 3: Notification of a user reaching a reputation threshold 
CREATE OR REPLACE FUNCTION achievement_reputation() RETURNS TRIGGER
AS $$
  BEGIN
    IF (NEW.reputation < 100) THEN
      RETURN NEW;
    END IF;
    IF (NEW.reputation >= 100) THEN
      IF (NOT EXISTS (SELECT * FROM achieved WHERE id_user = New.id AND id_achievement = 3)) THEN
        INSERT INTO achieved(id_user, id_achievement) VALUES (New.id, 3);
      END IF;
    END IF;
    IF (NEW.reputation >= 200) THEN
      IF (NOT EXISTS (SELECT * FROM achieved WHERE id_user = New.id AND id_achievement = 4)) THEN
        INSERT INTO achieved(id_user, id_achievement) VALUES (New.id, 4);
      END IF;
    END IF;
    IF (NEW.reputation >= 500) THEN
      IF (NOT EXISTS (SELECT * FROM achieved WHERE id_user = New.id AND id_achievement = 5)) THEN
        INSERT INTO achieved(id_user, id_achievement) VALUES (New.id, 5);
      END IF;
    END IF;
    IF (NEW.reputation >= 1000) THEN
      IF (NOT EXISTS (SELECT * FROM achieved WHERE id_user = New.id AND id_achievement = 6)) THEN
        INSERT INTO achieved(id_user, id_achievement) VALUES (New.id, 6);
      END IF;
    END IF;
    RETURN NEW;
  END
$$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS achievement_reputation_trigger ON "user" CASCADE;
CREATE TRIGGER achievement_reputation_trigger
AFTER INSERT OR UPDATE ON "user"
FOR EACH ROW
EXECUTE PROCEDURE achievement_reputation();
Trigger TRIGGER16
Description Trigger for notifications 1: Notification of receiving an achievement 
CREATE OR REPLACE FUNCTION add_achievement_notification() RETURNS TRIGGER
AS $$
  BEGIN
      INSERT INTO notification (title, body, recipient) VALUES ('New achievement', 'You have achieved: ', NEW.id_user);
      INSERT INTO notification_achievement (id, id_achievement) VALUES (currval(pg_get_serial_sequence('notification', 'id')), NEW.id_achievement);
      RETURN NEW;
  END
$$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS add_achievement_notification ON achieved CASCADE;
CREATE TRIGGER add_achievement_notification
AFTER INSERT ON achieved
FOR EACH ROW
EXECUTE PROCEDURE add_achievement_notification();
Trigger TRIGGER17
Description Trigger for notifications 2: Notification of someone answering one of the user's questions 
CREATE OR REPLACE FUNCTION add_new_answer_notification() RETURNS TRIGGER
AS $$
  BEGIN
      INSERT INTO notification (title, body, recipient) VALUES ('New answer', 'Someone answered your question: ', NEW.id_question);
      INSERT INTO notification_post (id, id_post) VALUES (currval(pg_get_serial_sequence('notification', 'id')), NEW.id_question);
      RETURN NEW;
  END
$$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS add_new_answer_notification ON answer_question CASCADE;
CREATE TRIGGER add_new_answer_notification
AFTER INSERT ON answer_question
FOR EACH ROW
EXECUTE PROCEDURE add_new_answer_notification();
Trigger TRIGGER18
Description Trigger for notifications 3: Notification of a new comment on a user's post 
CREATE OR REPLACE FUNCTION add_new_comment_notification() RETURNS TRIGGER
AS $$
  BEGIN
      IF (NEW.id_question IS NOT NULL) THEN
        INSERT INTO notification (title, body, recipient) VALUES ('New Comment', 'Someone commented your question: ', NEW.id_question);
        INSERT INTO notification_post (id, id_post) VALUES (currval(pg_get_serial_sequence('notification', 'id')), NEW.id_question);
      END IF;
      IF (NEW.id_answer IS NOT NULL) THEN
        INSERT INTO notification (title, body, recipient) VALUES ('New Comment', 'Someone commented your answer: ', NEW.id_answer);
        INSERT INTO notification_post (id, id_post) VALUES (currval(pg_get_serial_sequence('notification', 'id')), NEW.id_answer);
      END IF;
      RETURN NEW;
  END
$$
LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS add_new_comment_notification ON comment CASCADE;
CREATE TRIGGER add_new_comment_notification
AFTER INSERT ON comment
FOR EACH ROW
EXECUTE PROCEDURE add_new_comment_notification();
Trigger TRIGGER19
Description Trigger for preserving user information when deleted 
DROP RULE IF EXISTS remove_user ON "user" CASCADE;
CREATE RULE remove_user
AS ON DELETE TO "user"
DO INSTEAD(
    UPDATE "user"
    SET
      name = 'Deleted User' || Old.id,
      username = 'DeletedUser' || Old.id,
      search = to_tsvector(''),
      password = NULL,
      email = 'deleteduser' || Old.id || '@segmentationfault.com',
      about = NULL,
      picture = NULL,
      reputation = 0
    WHERE id = Old.id;
    DELETE FROM "achieved" WHERE id_user = Old.id;
    DELETE FROM "notification" WHERE id = Old.id;
);
Trigger TRIGGER20
Description Updates the user reputation whenever their answer was accepted (if the question had a bounty)

4. Transactions

SQL Reference Insert a new Question
Justification When inserting a question, inserting a new Post tuple is required, due to the E/R mapping of this generalization. To avoid inconsistent data, both insertions must be captured in a transaction statement. The user's reputation will also be subtracted if the question has a set bounty.
Isolation level REPEATABLE READ
Complete SQL Code 
BEGIN TRANSACTION;
INSERT INTO post(id_owner, body, "date") VALUES(OwnerUser, Body, DatePost);
INSERT INTO question(id, accepted_answer, title, bounty, closed)
  VALUES (currval(pg_get_serial_sequence('post','id')), NULL, Title, Bounty, Closed);
  rep := (SELECT "user".reputation FROM "user" WHERE id = OwnerUser);
  IF rep < Bounty THEN
    RAISE EXCEPTION 'User has smaller reputation (%) than the question bounty (%)', rep, Bounty;
  END IF;

  UPDATE "user" SET reputation = rep - Bounty WHERE id = OwnerUser;

  INSERT INTO post(id_owner, body, "date") VALUES(OwnerUser, Body, DatePost)
    RETURNING id INTO inserted_id;
  -- INSERT INTO question(id, accepted_answer, title, bounty, closed) SELECT(1, NULL, Title, Bounty, Closed);
  INSERT INTO question(id, accepted_answer, title, bounty, closed)
  	VALUES (inserted_id, NULL, Title, Bounty, Closed)
END TRANSACTION;
SQL Reference Insert a new Comment
Justification When inserting a comment, inserting a new Post tuple is required, due to the E/R mapping of this generalization. To avoid inconsistent data, both insertions must be captured in a transaction statement.
Isolation level REPEATABLE READ
Complete SQL Code 
BEGIN TRANSACTION;
  INSERT INTO post(id_owner, body, "date") VALUES(OwnerUser, Body, DatePost);
  INSERT INTO comment(id, id_question, id_answer) VALUES (currval(pg_get_serial_sequence('post','id')), IdQuestion, IdAnswer);
END TRANSACTION;
SQL Reference Insert a new Answer
Justification When inserting an answer, inserting a new Post tuple is required, due to the E/R mapping of this generalization. To avoid inconsistent data, both insertions must be captured in a transaction statement.
Isolation level REPEATABLE READ
Complete SQL Code 
BEGIN TRANSACTION;
  INSERT INTO post(id_owner, body, "date") VALUES(OwnerUser, Body, DatePost);
  INSERT INTO answer(id) VALUES (currval(pg_get_serial_sequence('post','id')));
  INSERT INTO answer_question(id_answer, id_question) VALUES(currval(pg_get_serial_sequence('post','id')), IdQuestion);
END TRANSACTION;
SQL Reference Insert a new Moderator
Justification When inserting a new moderator, inserting a new User tuple is required, due to the E/R mapping of this generalization. To avoid inconsistent data, both insertions must be captured in a transaction statement.
Isolation level REPEATABLE READ
Complete SQL Code 
BEGIN TRANSACTION;
  INSERT INTO "user" (name, username, password, email, about, picture) VALUES(Name, Username, Password, Email, About, Picture)
  INSERT INTO moderator (id) VALUES(currval(pg_get_serial_sequence('user','id')));
END TRANSACTION;
SQL Reference Insert a new Admin
Justification When inserting a new admin, inserting new User and Moderator tuples are required, due to the E/R mapping of this generalization. To avoid inconsistent data, both insertions must be captured in a transaction statement.
Isolation level REPEATABLE READ
Complete SQL Code 
BEGIN TRANSACTION;
  INSERT INTO "user" (name, username, password, email, about, picture) VALUES(Name, Username, Password, Email, About, Picture)
  INSERT INTO moderator (id) VALUES(currval(pg_get_serial_sequence('user','id')));
  INSERT INTO administrator (id) VALUES(currval(pg_get_serial_sequence('user','id')));
END TRANSACTION;
SQL Reference Ban a User
Justification When banning a user from the site, it is intended that we mark the user as deleted and that we insert a new tuple into the ban table.
Isolation level REPEATABLE READ
Complete SQL Code 
BEGIN TRANSACTION;
  DELETE FROM "user" WHERE id=idUser;
  INSERT INTO ban(id_user, id_admin, "date", reason) VALUES(idUser, idAdmin, date, reason);
END TRANSACTION;

Annex A. SQL Code

A.1. Database schema

-- CREATE
DROP DOMAIN IF EXISTS Today CASCADE;
DROP TYPE IF EXISTS report_state CASCADE;
DROP TABLE IF EXISTS "user" CASCADE;
DROP TABLE IF EXISTS moderator CASCADE;
DROP TABLE IF EXISTS administrator CASCADE;
DROP TABLE IF EXISTS ban CASCADE;
DROP TABLE IF EXISTS news CASCADE;
DROP TABLE IF EXISTS topic_proposal CASCADE;
DROP TABLE IF EXISTS achievement CASCADE;
DROP TABLE IF EXISTS achieved CASCADE;
DROP TABLE IF EXISTS post CASCADE;
DROP TABLE IF EXISTS answer CASCADE;
DROP TABLE IF EXISTS question CASCADE;
DROP TABLE IF EXISTS answer_question;
DROP TABLE IF EXISTS comment CASCADE;
DROP TABLE IF EXISTS vote CASCADE;
DROP TABLE IF EXISTS edit_proposal CASCADE;
DROP TABLE IF EXISTS topic CASCADE;
DROP TABLE IF EXISTS topic_question CASCADE;
DROP TABLE IF EXISTS report CASCADE;
DROP TABLE IF EXISTS notification CASCADE;
DROP TABLE IF EXISTS notification_achievement CASCADE;
DROP TABLE IF EXISTS notification_post CASCADE;
DROP TABLE IF EXISTS password_resets CASCADE;


-- DOMAINS
CREATE DOMAIN Today AS TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
CREATE TYPE report_state AS ENUM ('pending', 'approved', 'rejected');


-- TABLES
-- R01
CREATE TABLE "user"(
  id SERIAL PRIMARY KEY,
  name TEXT,
  username TEXT UNIQUE NOT NULL,
  search TSVECTOR,
  password TEXT,
  email TEXT UNIQUE NOT NULL,
  about TEXT,
  picture TEXT,
  reputation INTEGER NOT NULL DEFAULT 0,
  isdeleted boolean NOT NULL DEFAULT false,
  remember_token TEXT
);

-- R02
CREATE TABLE moderator(
  id INTEGER PRIMARY KEY,
  CONSTRAINT fk_user
    FOREIGN KEY(id)
      REFERENCES "user"(id)
);

-- R03
CREATE TABLE administrator(
  id INTEGER PRIMARY KEY,
  CONSTRAINT fk_moderator
    FOREIGN KEY(id)
      REFERENCES moderator(id)
);

-- R04
CREATE TABLE ban(
  id_user INTEGER PRIMARY KEY,
  id_admin INTEGER NOT NULL,
  "date" Today NOT NULL CHECK ("date" <= CURRENT_TIMESTAMP), -- bans can't have happened in the future
  reason TEXT,
  CONSTRAINT fk_user
    FOREIGN KEY(id_user)
      REFERENCES "user"(id),
  CONSTRAINT fk_admin
    FOREIGN KEY(id_admin)
      REFERENCES Administrator(id)
);

-- R05
CREATE TABLE news(
  id SERIAL PRIMARY KEY,
  author INTEGER NOT NULL,
  title TEXT NOT NULL,
  subtitle TEXT,
  body TEXT,
  "date" Today NOT NULL CHECK ("date" <= CURRENT_TIMESTAMP), -- news can't be posted in the future
  CONSTRAINT fk_author
    FOREIGN KEY(author)
      REFERENCES administrator(id)
);

-- R06
CREATE TABLE topic_proposal(
  id SERIAL PRIMARY KEY,
  id_user INTEGER NOT NULL,
  id_admin INTEGER,
  topic_name TEXT NOT NULL,
  "date" Today NOT NULL CHECK ("date" <= CURRENT_TIMESTAMP), -- proposals can't have happened in the future
  reason TEXT,
  accepted boolean NOT NULL DEFAULT false,
  CONSTRAINT fk_user
    FOREIGN KEY(id_user)
      REFERENCES "user"(id),
  CONSTRAINT fk_admin
    FOREIGN KEY(id_admin)
      REFERENCES administrator(id)
);

-- R07
CREATE TABLE achievement(
  id SERIAL PRIMARY KEY,
  title TEXT UNIQUE NOT NULL,
  body TEXT NOT NULL
);

-- R08
CREATE TABLE achieved(
  id_user INTEGER,
  id_achievement INTEGER,
  "date" Today NOT NULL CHECK ("date" <= CURRENT_TIMESTAMP), -- achievements can't be acquired in the future
  PRIMARY KEY (id_user, id_achievement),
  CONSTRAINT fk_user
    FOREIGN KEY(id_user)
      REFERENCES "user"(id),
  CONSTRAINT fk_achievement
    FOREIGN KEY(id_achievement)
      REFERENCES achievement(id)
);

-- R09
CREATE TABLE post(
  id SERIAL PRIMARY KEY,
  id_owner INTEGER NOT NULL,
  body TEXT NOT NULL,
  score INTEGER NOT NULL DEFAULT 0,
  "date" Today NOT NULL CHECK ("date" <= CURRENT_TIMESTAMP), -- posts can't be made in the future
  CONSTRAINT fk_owner
    FOREIGN KEY(id_owner)
      REFERENCES "user"(id)
);

-- R10
CREATE TABLE answer(
  id INTEGER PRIMARY KEY,
  CONSTRAINT fk_post
    FOREIGN KEY(id)
      REFERENCES post(id)
        ON DELETE CASCADE
);

-- R11
CREATE TABLE question(
  id INTEGER PRIMARY KEY,
  accepted_answer INTEGER,
  title TEXT UNIQUE NOT NULL,
  search TSVECTOR NOT NULL,
  bounty smallint NOT NULL CHECK (
    bounty >= 0
    AND bounty <= 500
  ),
  closed boolean NOT NULL DEFAULT false,
  CONSTRAINT fk_post
    FOREIGN KEY(id)
      REFERENCES post(id)
        ON DELETE CASCADE,
  CONSTRAINT fk_answer
    FOREIGN KEY(accepted_answer)
      REFERENCES answer(id)
);

-- R12
CREATE TABLE answer_question(
  id_answer INTEGER,
  id_question INTEGER,
  PRIMARY KEY(id_answer, id_question),
  CONSTRAINT fk_answer
    FOREIGN KEY(id_answer)
      REFERENCES answer(id)
        ON DELETE CASCADE,
  CONSTRAINT fk_question
    FOREIGN KEY(id_question)
      REFERENCES question(id)
        ON DELETE CASCADE
);

-- R13
CREATE TABLE comment(
  id INTEGER PRIMARY KEY,
  id_question INTEGER,
  id_answer INTEGER,
  CHECK (
    (
      id_question = NULL
      AND id_answer != NULL
    )
    OR (
      id_question != NULL
      AND id_answer = NULL
    )
  ),
  CONSTRAINT fk_post
    FOREIGN KEY(id)
      REFERENCES post(id)
        ON DELETE CASCADE,
  CONSTRAINT fk_question
    FOREIGN KEY(id_question)
      REFERENCES question(id)
        ON DELETE CASCADE,
  CONSTRAINT fk_answer
    FOREIGN KEY(id_answer)
      REFERENCES answer(id)
        ON DELETE CASCADE
);

-- R14
CREATE TABLE vote(
  id_post INTEGER,
  id_user INTEGER,
  value smallint NOT NULL CHECK (
    value = 1
    OR value = -1
  ),
  PRIMARY KEY (id_post, id_user),
  CONSTRAINT fk_post
    FOREIGN KEY(id_post)
      REFERENCES post(id)
        ON DELETE CASCADE,
  CONSTRAINT fk_user
    FOREIGN KEY(id_user)
      REFERENCES "user"(id)
);

-- R15
CREATE TABLE edit_proposal(
  id SERIAL PRIMARY KEY,
  id_post INTEGER NOT NULL,
  id_user INTEGER NOT NULL,
  id_moderator INTEGER,
  body TEXT NOT NULL,
  accepted boolean NOT NULL DEFAULT false,
  CONSTRAINT fk_post
    FOREIGN KEY(id_post)
      REFERENCES post(id)
        ON DELETE CASCADE,
  CONSTRAINT fk_user
    FOREIGN KEY(id_user)
      REFERENCES "user"(id),
  CONSTRAINT fk_moderator
    FOREIGN KEY(id_moderator)
      REFERENCES moderator(id)
);

-- R16
CREATE TABLE topic(
  id SERIAL PRIMARY KEY,
  name TEXT UNIQUE NOT NULL,
  search TSVECTOR NOT NULL
);

-- R17
CREATE TABLE topic_question(
  id_topic INTEGER,
  id_question INTEGER,
  PRIMARY KEY(id_topic, id_question),
  CONSTRAINT fk_topic
    FOREIGN KEY(id_topic)
      REFERENCES topic(id),
  CONSTRAINT fk_question
    FOREIGN KEY(id_question)
      REFERENCES question(id)
        ON DELETE CASCADE
);

-- R18
CREATE TABLE report(
  id_post INTEGER,
  reporter INTEGER,
  "date" Today NOT NULL CHECK ("date" <= CURRENT_TIMESTAMP), -- reports can't be made in the future
  reason TEXT,
  PRIMARY KEY(id_post, reporter),
  "state" report_state DEFAULT 'pending',
  reviewer INTEGER,
  CONSTRAINT fk_post
    FOREIGN KEY(id_post)
      REFERENCES post(id)
        ON DELETE CASCADE,
  CONSTRAINT fk_reporter
    FOREIGN KEY(reporter)
      REFERENCES "user"(id),
  CONSTRAINT fk_reviewer
    FOREIGN KEY(reviewer)
      REFERENCES "moderator"(id)
);

-- R19
CREATE TABLE notification(
  id SERIAL PRIMARY KEY,
  "date" Today NOT NULL CHECK ("date" <= CURRENT_TIMESTAMP), -- can't be notified in the future
  title TEXT NOT NULL,
  body TEXT,
  recipient INTEGER,
  CONSTRAINT fk_recipient
    FOREIGN KEY(recipient)
      REFERENCES "user"(id)
);

-- R20
CREATE TABLE notification_achievement(
  id INTEGER PRIMARY KEY,
  id_achievement INTEGER NOT NULL,
  CONSTRAINT fk_notification
    FOREIGN KEY(id)
      REFERENCES notification(id)
      ON DELETE CASCADE,
  CONSTRAINT fk_achievement
    FOREIGN KEY(id_achievement)
      REFERENCES achievement(id)
      ON DELETE CASCADE
);

-- R21
CREATE TABLE notification_post(
  id INTEGER PRIMARY KEY,
  id_post INTEGER NOT NULL,
  CONSTRAINT fk_notification
    FOREIGN KEY(id)
      REFERENCES notification(id)
      ON DELETE CASCADE,
  CONSTRAINT fk_post
    FOREIGN KEY(id_post)
      REFERENCES post(id)
      ON DELETE CASCADE
);

-- PASSWORD RESET TABLE REQUIRED BY LARAVEL
CREATE TABLE password_resets(
  email TEXT PRIMARY KEY,
  token TEXT NOT NULL,
  created_at TIMESTAMP
);


-- INDEXES
DROP INDEX IF EXISTS date_idx CASCADE;
DROP INDEX IF EXISTS question_idx CASCADE;
DROP INDEX IF EXISTS owner_idx CASCADE;
DROP INDEX IF EXISTS user_idx CASCADE;
DROP INDEX IF EXISTS state_idx CASCADE;
DROP INDEX IF EXISTS user_search_idx CASCADE;
DROP INDEX IF EXISTS question_search_idx CASCADE;
DROP INDEX IF EXISTS topic_search_idx CASCADE;

CREATE INDEX date_idx ON post USING btree ("date");
CREATE INDEX question_idx ON answer_question USING hash (id_question);
CREATE INDEX owner_idx ON post USING hash (id_owner);
CREATE INDEX user_idx ON achieved USING hash (id_user);
CREATE INDEX state_idx ON report USING hash ("state");
CREATE INDEX user_search_idx ON "user" USING GiST (search);
CREATE INDEX question_search_idx ON question USING GiST (search);
CREATE INDEX topic_search_idx ON topic USING GIN (search);


-- FUNCTIONS
-- used to store the derived attribute 'score' (see comment in vote table)
CREATE OR REPLACE FUNCTION on_score_change()
RETURNS TRIGGER
AS $$
  DECLARE
    val integer;
    val2 integer;
    post_id integer;
    owner_id integer;
  BEGIN
    IF (TG_OP = 'DELETE') THEN
      post_id := OLD.id_post;
    ELSIF (TG_OP = 'UPDATE') THEN
      post_id := NEW.id_post;
    ELSIF (TG_OP = 'INSERT') THEN
      post_id := NEW.id_post;
    END IF;

    -- update the question score
    val := (SELECT sum(value)
            FROM post JOIN vote ON (post.id = vote.id_post)
            WHERE id = post_id);
    IF val IS NULL THEN
      val := 0;
    END IF;
    UPDATE post
    SET score = val
    WHERE id = post_id;

    -- update the question's owner reputation
    owner_id := (SELECT id_owner
                  FROM post
                  WHERE id = post_id);
    -- score obtaining by votes on the user's posts
    val := (SELECT sum(score)
            FROM post
            JOIN "user" ON (post.id_owner = "user".id)
            WHERE "user".id = owner_id);
    IF val IS NULL THEN
      val := 0;
    END IF;
    -- score obtained by achievements (each is worth 10 reputation)
    val2 := (SELECT count(*)
              FROM "user"
              JOIN achieved ON (achieved.id_user = "user".id)
              JOIN achievement ON (achieved.id_achievement = achievement.id)
              WHERE "user".id = owner_id) * 10;
    UPDATE "user" as u
    SET reputation = val + val2
    WHERE owner_id = u.id;

    RETURN NULL; -- result is ignored since this is an AFTER trigger
  END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION on_accepting_answer()
RETURNS TRIGGER
AS $$
  DECLARE
    val_delta integer;
    answer_owner_id integer;
  BEGIN
  	val_delta := NEW.bounty;
    IF val_delta IS NULL THEN
      val_delta := 0;
    END IF;
   	answer_owner_id := (SELECT post.id_owner FROM post WHERE post.id = NEW.accepted_answer);

    UPDATE "user" as u
    SET reputation = u.reputation + val_delta
    WHERE answer_owner_id = u.id;

    RETURN NULL; -- result is ignored since this is an AFTER trigger
  END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION on_achievement_change()
RETURNS TRIGGER
AS $$
  DECLARE
    val_delta integer;
    owner_id integer;
  BEGIN
    IF (TG_OP = 'DELETE') THEN
      owner_id := OLD.id_user;
      val_delta = -10;
    ELSIF (TG_OP = 'UPDATE') THEN
      owner_id := NEW.id_user;
      val_delta = 0;
    ELSIF (TG_OP = 'INSERT') THEN
      owner_id := NEW.id_user;
      val_delta = 10;
    END IF;

    UPDATE "user" as u
    SET reputation = u.reputation + val_delta
    WHERE owner_id = u.id;

    RETURN NULL; -- result is ignored since this is an AFTER trigger
  END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION user_search_update()
RETURNS TRIGGER
AS $$
  BEGIN
  IF (TG_OP = 'INSERT') THEN
    NEW.search = (setweight(to_tsvector('english', NEW.username), 'A') ||
      setweight(to_tsvector('english', COALESCE(NEW.name, '')), 'B'));
  ELSIF (TG_OP = 'UPDATE') THEN
    IF NEW.username <> OLD.username or NEW.name <> OLD.name THEN
      NEW.search = (setweight(to_tsvector('english', NEW.username), 'A') ||
        setweight(to_tsvector('english', COALESCE(NEW.name, '')), 'B'));
    END IF;
  END IF;

  RETURN NEW;
  END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION question_search_update()
RETURNS TRIGGER
AS $$
  DECLARE
    b text;
    topics text;
  BEGIN
    -- get the body
    b := (SELECT body
          FROM post
          WHERE post.id = NEW.id);

    -- get the topics
    topics := (SELECT string_agg(name, ' ')
                FROM topic JOIN topic_question ON (topic.id = topic_question.id_topic)
                  JOIN question ON (question.id = topic_question.id_question)
                WHERE question.id = NEW.id);

    IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
      NEW.search = (setweight(to_tsvector('english', NEW.title), 'A') ||
                    setweight(to_tsvector('english', b), 'B') ||
                    setweight(to_tsvector('english', COALESCE(topics, '')), 'C'));
    END IF;

    RETURN NEW;
  END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION topic_search_update()
RETURNS TRIGGER
AS $$
  BEGIN
    IF (TG_OP = 'INSERT') THEN
      NEW.search = to_tsvector('english', NEW.name);
    ELSIF (TG_OP = 'UPDATE') THEN
      IF NEW.name <> OLD.name THEN
        NEW.seach = to_tsvector('english', NEW.name);
      END IF;
    END IF;

    RETURN NEW;
  END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION reopen_question() RETURNS TRIGGER AS
$$
BEGIN
    IF (OLD.closed <> NEW.closed and OLD.closed = TRUE) THEN
      RAISE EXCEPTION 'A closed question can not be re-opened.';
    END IF;
    RETURN NEW;
END
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION vote() RETURNS TRIGGER
AS $$
  DECLARE
    owner integer;
  BEGIN
      owner := (SELECT id_owner FROM post WHERE id = NEW.id_post);

      IF (owner = NEW.id_user) THEN
        RAISE EXCEPTION 'A user can not vote on its own post.';
      END IF;
      RETURN NEW;
  END
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION notification_generalization() RETURNS TRIGGER
AS $$
  BEGIN
      IF EXISTS (SELECT *
        FROM notification_post, notification_achievement
        WHERE notification_post.id = New.id OR notification_achievement.id = New.id) THEN
        RAISE EXCEPTION 'Notification must be disjoint.';
      END IF;
      RETURN NEW;
  END
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION post_generalization() RETURNS TRIGGER
AS $$
  BEGIN
      IF EXISTS (SELECT *
        FROM question, answer, comment
        WHERE question.id = New.id OR answer.id = New.id OR comment.id = New.id) THEN
        RAISE EXCEPTION 'Post must be disjoint.';
      END IF;
      RETURN NEW;
  END
$$
LANGUAGE plpgsql;

-- Achievements
CREATE OR REPLACE FUNCTION achievement_first_question() RETURNS TRIGGER
AS $$
  DECLARE
    owner_id integer;
    question_amount integer;
  BEGIN
      owner_id := (SELECT post.id_owner AS owner_id FROM post JOIN question ON(post.id = question.id) WHERE post.id = NEW.id);
      question_amount := (SELECT COUNT(*) FROM post JOIN question ON(post.id = question.id) WHERE post.id_owner = owner_id);

      IF (question_amount = 1) THEN
        IF NOT EXISTS (SELECT * FROM achieved WHERE id_user = owner_id AND id_achievement = 1) THEN
          INSERT INTO achieved(id_user, id_achievement) VALUES (owner_id, 1);
        END IF;
      END IF;
      RETURN NEW;
  END
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION achievement_first_accepted_answer() RETURNS TRIGGER
AS $$
  DECLARE
    accepted_answer_id integer;
    answer_owner_id integer;
  BEGIN
    accepted_answer_id := NEW.accepted_answer;

    IF (accepted_answer_id IS NOT NULL) THEN
      answer_owner_id := (SELECT id_owner FROM post WHERE id = accepted_answer_id);
      IF NOT EXISTS (SELECT * FROM achieved WHERE id_user = answer_owner_id AND id_achievement = 2) THEN
        INSERT INTO achieved(id_user, id_achievement) VALUES (answer_owner_id, 2);
      END IF;
    END IF;
    RETURN NEW;
  END
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION achievement_reputation() RETURNS TRIGGER
AS $$
  BEGIN
    IF (NEW.reputation < 100) THEN
      RETURN NEW;
    END IF;
    IF (NEW.reputation >= 100) THEN
      IF (NOT EXISTS (SELECT * FROM achieved WHERE id_user = New.id AND id_achievement = 3)) THEN
        INSERT INTO achieved(id_user, id_achievement) VALUES (New.id, 3);
      END IF;

      -- Give moderator priveliges
      IF (NOT EXISTS (SELECT * FROM moderator WHERE id = NEW.id)) THEN
        INSERT INTO moderator(id) VALUES(NEW.id);
      END IF;
    END IF;
    IF (NEW.reputation >= 200) THEN
      IF (NOT EXISTS (SELECT * FROM achieved WHERE id_user = New.id AND id_achievement = 4)) THEN
        INSERT INTO achieved(id_user, id_achievement) VALUES (New.id, 4);
      END IF;
    END IF;
    IF (NEW.reputation >= 500) THEN
      IF (NOT EXISTS (SELECT * FROM achieved WHERE id_user = New.id AND id_achievement = 5)) THEN
        INSERT INTO achieved(id_user, id_achievement) VALUES (New.id, 5);
      END IF;
    END IF;
    IF (NEW.reputation >= 1000) THEN
      IF (NOT EXISTS (SELECT * FROM achieved WHERE id_user = New.id AND id_achievement = 6)) THEN
        INSERT INTO achieved(id_user, id_achievement) VALUES (New.id, 6);
      END IF;
    END IF;
    RETURN NEW;
  END
$$
LANGUAGE plpgsql;

-- NOTIFICATIONS
CREATE OR REPLACE FUNCTION add_achievement_notification() RETURNS TRIGGER
AS $$
  BEGIN
      INSERT INTO notification (title, body, recipient) VALUES ('New achievement', 'You have achieved: ', NEW.id_user);
      INSERT INTO notification_achievement (id, id_achievement) VALUES (currval(pg_get_serial_sequence('notification', 'id')), NEW.id_achievement);
      RETURN NEW;
  END
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION add_new_answer_notification() RETURNS TRIGGER
AS $$
  BEGIN
      INSERT INTO notification (title, body, recipient) VALUES ('New answer', 'Someone answered your question: ', NEW.id_question);
      INSERT INTO notification_post (id, id_post) VALUES (currval(pg_get_serial_sequence('notification', 'id')), NEW.id_question);
      RETURN NEW;
  END
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION add_new_comment_notification() RETURNS TRIGGER
AS $$
  BEGIN
      IF (NEW.id_question IS NOT NULL) THEN
        INSERT INTO notification (title, body, recipient) VALUES ('New Comment', 'Someone commented your question: ', NEW.id_question);
        INSERT INTO notification_post (id, id_post) VALUES (currval(pg_get_serial_sequence('notification', 'id')), NEW.id_question);
      END IF;
      IF (NEW.id_answer IS NOT NULL) THEN
        INSERT INTO notification (title, body, recipient) VALUES ('New Comment', 'Someone commented your answer: ', NEW.id_answer);
        INSERT INTO notification_post (id, id_post) VALUES (currval(pg_get_serial_sequence('notification', 'id')), NEW.id_answer);
      END IF;
      RETURN NEW;
  END
$$
LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION delete_post() RETURNS TRIGGER
AS $$
  BEGIN
    DELETE FROM post WHERE id = OLD.id;
    RETURN NEW;
  END
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION delete_answer() RETURNS TRIGGER
AS $$
  BEGIN
    DELETE FROM answer WHERE id = OLD.id_answer;
    RETURN NEW;
  END
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION delete_notification() RETURNS TRIGGER
AS $$
  BEGIN
    DELETE FROM notification WHERE id = OLD.id;
    RETURN NEW;
  END
$$
LANGUAGE plpgsql;


-- TRIGGERS

-- Update score
-- on vote
DROP TRIGGER IF EXISTS update_score_vote ON vote CASCADE;
CREATE TRIGGER update_score_vote
AFTER DELETE OR INSERT OR UPDATE ON vote
FOR EACH ROW
EXECUTE FUNCTION on_score_change();
-- on accepting answer
DROP TRIGGER IF EXISTS update_accepting_answer ON question CASCADE;
CREATE TRIGGER update_accepting_answer
AFTER INSERT OR UPDATE OF accepted_answer ON question
FOR EACH ROW
EXECUTE FUNCTION on_accepting_answer();
-- on achievement
DROP TRIGGER IF EXISTS update_score_achievement ON vote CASCADE;
CREATE TRIGGER update_score_achievement
AFTER DELETE OR INSERT OR UPDATE ON achieved
FOR EACH ROW
EXECUTE FUNCTION on_achievement_change();

-- Search user

DROP TRIGGER IF EXISTS user_search_update_trigger ON "user" CASCADE;
CREATE TRIGGER user_search_update_trigger
BEFORE INSERT OR UPDATE ON "user"
FOR EACH ROW
EXECUTE FUNCTION user_search_update();

-- Search question

DROP TRIGGER IF EXISTS question_search_update_trigger ON question CASCADE;
CREATE TRIGGER question_search_update_trigger
BEFORE INSERT OR UPDATE ON question
FOR EACH ROW
EXECUTE FUNCTION question_search_update();

-- Search topic

DROP TRIGGER IF EXISTS topic_search_update_trigger ON topic CASCADE;
CREATE TRIGGER topic_search_update_trigger
BEFORE INSERT OR UPDATE ON topic
FOR EACH ROW
EXECUTE FUNCTION topic_search_update();

-- Reopen question

DROP TRIGGER IF EXISTS reopen_question_trigger ON question CASCADE;
CREATE TRIGGER reopen_question_trigger
BEFORE UPDATE ON question
FOR EACH ROW
EXECUTE PROCEDURE reopen_question();

-- Can't vote on their own post

DROP TRIGGER IF EXISTS vote_trigger ON vote CASCADE;
CREATE TRIGGER vote_trigger
BEFORE INSERT OR UPDATE ON vote
FOR EACH ROW
EXECUTE PROCEDURE vote();

-- Notification Generalization

DROP TRIGGER IF EXISTS notification_achievement_generalization_trigger ON notification_achievement CASCADE;
CREATE TRIGGER notification_achievement_generalization_trigger
BEFORE INSERT OR UPDATE ON notification_achievement
FOR EACH ROW
EXECUTE PROCEDURE notification_generalization();

DROP TRIGGER IF EXISTS notification_post_generalization_trigger ON notification_post CASCADE;
CREATE TRIGGER notification_post_generalization_trigger
BEFORE INSERT OR UPDATE ON notification_post
FOR EACH ROW
EXECUTE PROCEDURE notification_generalization();

--- Post generalization

DROP TRIGGER IF EXISTS post_answer_generalization_trigger ON answer CASCADE;
CREATE TRIGGER post_answer_generalization_trigger
BEFORE INSERT OR UPDATE ON answer
FOR EACH ROW
EXECUTE PROCEDURE post_generalization();

-- This trigger cannot be run on update because when we want to set or change the accepted answer it will not allow it
DROP TRIGGER IF EXISTS post_question_generalization_trigger ON question CASCADE;
CREATE TRIGGER post_question_generalization_trigger
BEFORE INSERT ON question
FOR EACH ROW
EXECUTE PROCEDURE post_generalization();

DROP TRIGGER IF EXISTS post_comment_generalization_trigger ON comment CASCADE;
CREATE TRIGGER post_comment_generalization_trigger
BEFORE INSERT OR UPDATE ON comment
FOR EACH ROW
EXECUTE PROCEDURE post_generalization();


-- DELETE POSTS IF A QUESTION, QUESTION OR COMMENT IS DELETED
DROP TRIGGER IF EXISTS delete_post_trigger ON comment CASCADE;
CREATE TRIGGER delete_post_trigger
AFTER DELETE ON comment
FOR EACH ROW
EXECUTE PROCEDURE delete_post();

DROP TRIGGER IF EXISTS delete_post_trigger ON answer CASCADE;
CREATE TRIGGER delete_post_trigger
AFTER DELETE ON answer
FOR EACH ROW
EXECUTE PROCEDURE delete_post();

DROP TRIGGER IF EXISTS delete_post_trigger ON question CASCADE;
CREATE TRIGGER delete_post_trigger
AFTER DELETE ON question
FOR EACH ROW
EXECUTE PROCEDURE delete_post();

-- DELETE QUESTION WHEN ANSWER_QUESTION IS DELETED
DROP TRIGGER IF EXISTS delete_answer_trigger ON answer_question CASCADE;
CREATE TRIGGER delete_answer_trigger
AFTER DELETE ON answer_question
FOR EACH ROW
EXECUTE PROCEDURE delete_answer();

--DELETE NOTICATION WHEN A CHILD NOTIFICATION IS REMOVED
DROP TRIGGER IF EXISTS delete_notification_trigger ON notification_achievement CASCADE;
CREATE TRIGGER delete_notification_trigger
AFTER DELETE ON notification_achievement
FOR EACH ROW
EXECUTE PROCEDURE delete_notification();

DROP TRIGGER IF EXISTS delete_notification_trigger ON notification_post CASCADE;
CREATE TRIGGER delete_notification_trigger
AFTER DELETE ON notification_post
FOR EACH ROW
EXECUTE PROCEDURE delete_notification();


DROP RULE IF EXISTS remove_user ON "user" CASCADE;
CREATE RULE remove_user
AS ON DELETE TO "user"
DO INSTEAD(
    UPDATE "user"
    SET
      name = 'Deleted User' || Old.id,
      username = 'DeletedUser' || Old.id,
      search = to_tsvector(''),
      password = NULL,
      email = 'deleteduser' || Old.id || '@segmentationfault.com',
      about = NULL,
      picture = NULL,
      reputation = 0,
      isdeleted = true
    WHERE id = Old.id;
    DELETE FROM "achieved" WHERE id_user = Old.id;
    DELETE FROM "notification" WHERE id = Old.id;
);

---- Achievements

DROP TRIGGER IF EXISTS achievement_first_question_trigger ON question CASCADE;
CREATE TRIGGER achievement_first_question_trigger
AFTER INSERT ON question
FOR EACH ROW
EXECUTE PROCEDURE achievement_first_question();

DROP TRIGGER IF EXISTS achievement_first_accepted_answer_trigger ON question CASCADE;
CREATE TRIGGER achievement_first_accepted_answer_trigger
AFTER INSERT OR UPDATE ON question
FOR EACH ROW
EXECUTE PROCEDURE achievement_first_accepted_answer();

DROP TRIGGER IF EXISTS achievement_reputation_trigger ON "user" CASCADE;
CREATE TRIGGER achievement_reputation_trigger
AFTER INSERT OR UPDATE ON "user"
FOR EACH ROW
EXECUTE PROCEDURE achievement_reputation();

---- NOTIFICATIONS

DROP TRIGGER IF EXISTS add_achievement_notification ON achieved CASCADE;
CREATE TRIGGER add_achievement_notification
AFTER INSERT ON achieved
FOR EACH ROW
EXECUTE PROCEDURE add_achievement_notification();

DROP TRIGGER IF EXISTS add_new_answer_notification ON answer_question CASCADE;
CREATE TRIGGER add_new_answer_notification
AFTER INSERT ON answer_question
FOR EACH ROW
EXECUTE PROCEDURE add_new_answer_notification();

DROP TRIGGER IF EXISTS add_new_comment_notification ON comment CASCADE;
CREATE TRIGGER add_new_comment_notification
AFTER INSERT ON comment
FOR EACH ROW
EXECUTE PROCEDURE add_new_comment_notification();

-- TRANSACTIONS
CREATE OR REPLACE PROCEDURE create_question
(
  OwnerUser INT,
  Body TEXT,
  DatePost TIMESTAMP,
  Title TEXT,
  Bounty INT,
  Closed BOOLEAN
)
LANGUAGE plpgsql
AS
$$
DECLARE
  rep integer;
  inserted_id integer;
BEGIN
  rep := (SELECT "user".reputation FROM "user" WHERE id = OwnerUser);
  IF rep < Bounty THEN
    RAISE EXCEPTION 'User has smaller reputation (%) than the question bounty (%)', rep, Bounty;
  END IF;

  UPDATE "user" SET reputation = rep - Bounty WHERE id = OwnerUser;

  INSERT INTO post(id_owner, body, "date") VALUES(OwnerUser, Body, DatePost)
    RETURNING id INTO inserted_id;
  -- INSERT INTO question(id, accepted_answer, title, bounty, closed) SELECT(1, NULL, Title, Bounty, Closed);
  INSERT INTO question(id, accepted_answer, title, bounty, closed)
  	VALUES (inserted_id, NULL, Title, Bounty, Closed);
END
$$;

CREATE OR REPLACE PROCEDURE create_comment
(
  OwnerUser INT,
  Body TEXT,
  DatePost TIMESTAMP,
  IdQuestion INT,
  IdAnswer INT
)
LANGUAGE plpgsql
AS
$$
DECLARE
BEGIN
  INSERT INTO post(id_owner, body, "date") VALUES(OwnerUser, Body, DatePost);
  -- INSERT INTO question(id, accepted_answer, title, bounty, closed) SELECT(1, NULL, Title, Bounty, Closed);
  INSERT INTO comment(id, id_question, id_answer) VALUES (currval(pg_get_serial_sequence('post','id')), IdQuestion, IdAnswer);
END
$$;

CREATE OR REPLACE PROCEDURE create_answer
(
  OwnerUser INT,
  Body TEXT,
  DatePost TIMESTAMP,
  IdQuestion INT
)
LANGUAGE plpgsql
AS
$$
DECLARE
BEGIN
  INSERT INTO post(id_owner, body, "date") VALUES(OwnerUser, Body, DatePost);
  -- INSERT INTO question(id, accepted_answer, title, bounty, closed) SELECT(1, NULL, Title, Bounty, Closed);
  INSERT INTO answer(id) VALUES (currval(pg_get_serial_sequence('post','id')));
  INSERT INTO answer_question(id_answer, id_question) VALUES(currval(pg_get_serial_sequence('post','id')), IdQuestion);
END
$$;

CREATE OR REPLACE PROCEDURE create_moderator
(
  Name TEXT,
  Username TEXT,
  Password TEXT,
  Email TEXT,
  About TEXT,
  Picture TEXT
)
LANGUAGE plpgsql
AS
$$
DECLARE
BEGIN
  INSERT INTO "user" (name, username, password, email, about, picture) VALUES(Name, Username, Password, Email, About, Picture);
  INSERT INTO moderator (id) VALUES(currval(pg_get_serial_sequence('user','id')));
END
$$;

CREATE OR REPLACE PROCEDURE create_admin
(
  Name TEXT,
  Username TEXT,
  Password TEXT,
  Email TEXT,
  About TEXT,
  Picture TEXT
)
LANGUAGE plpgsql
AS
$$
DECLARE
BEGIN
  INSERT INTO "user" (name, username, password, email, about, picture) VALUES(Name, Username, Password, Email, About, Picture);
  INSERT INTO moderator (id) VALUES(currval(pg_get_serial_sequence('user','id')));
  INSERT INTO administrator (id) VALUES(currval(pg_get_serial_sequence('user','id')));
END
$$;

CREATE OR REPLACE PROCEDURE ban_user
(
  idUser INT,
  idAdmin INT,
  reason TEXT
)
LANGUAGE plpgsql
AS
$$
DECLARE
BEGIN
  DELETE FROM "user" WHERE id=idUser;
  INSERT INTO ban(id_user, id_admin, reason) VALUES(idUser, idAdmin, reason);
END
$$;




-- POPULATE

-- R01
insert into "user" (name, username, password, email, about, picture) values
  ('Torrance Jerrom', 'tjerrom0', '$2y$10$VTbK/xpteDPvaoxGuuyN8ulxU26mz/xJEwlE.Wx724.xGwou4tjzO', 'tjerrom0@deliciousdays.com', 'Dental Hygienist', 'default.jpg'),
  ('Keenan O''Bruen', 'kobruen1', '$2y$10$VTbK/xpteDPvaoxGuuyN8ulxU26mz/xJEwlE.Wx724.xGwou4tjzO', 'kobruen1@yolasite.com', 'Tax Accountant', 'default.jpg'),
  ('Joey Kores', 'jkores2', '$2y$10$VTbK/xpteDPvaoxGuuyN8ulxU26mz/xJEwlE.Wx724.xGwou4tjzO', 'jkores2@parallels.com', 'Administrative Officer', 'default.jpg'),
  ('Berky Shakespeare', 'bshakespeare3', '$2y$10$VTbK/xpteDPvaoxGuuyN8ulxU26mz/xJEwlE.Wx724.xGwou4tjzO', 'bshakespeare3@deliciousdays.com', 'Biostatistician IV', 'default.jpg'),
  ('Ives Shinn', 'ishinn4', '$2y$10$VTbK/xpteDPvaoxGuuyN8ulxU26mz/xJEwlE.Wx724.xGwou4tjzO', 'ishinn4@topsy.com', 'Programmer Analyst IV', 'default.jpg'),
  ('Ian Franklyn', 'ifranklyn5', '$2y$10$VTbK/xpteDPvaoxGuuyN8ulxU26mz/xJEwlE.Wx724.xGwou4tjzO', 'ifranklyn5@oracle.com', 'Senior Quality Engineer', 'default.jpg'),
  ('Pernell Danelut', 'pdanelut6', '$2y$10$VTbK/xpteDPvaoxGuuyN8ulxU26mz/xJEwlE.Wx724.xGwou4tjzO', 'pdanelut6@shareasale.com', 'Marketing Manager', 'default.jpg'),
  ('Malachi Rilings', 'mrilings7', '$2y$10$VTbK/xpteDPvaoxGuuyN8ulxU26mz/xJEwlE.Wx724.xGwou4tjzO', 'mrilings7@tinypic.com', 'Nurse', 'default.jpg'),
  ('Gill Lehrian', 'glehrian8', '$2y$10$VTbK/xpteDPvaoxGuuyN8ulxU26mz/xJEwlE.Wx724.xGwou4tjzO', 'glehrian8@sina.com.cn', 'Junior Executive', 'default.jpg'),
  ('Baily Bernet', 'bbernet9', '$2y$10$VTbK/xpteDPvaoxGuuyN8ulxU26mz/xJEwlE.Wx724.xGwou4tjzO', 'bbernet9@weather.com', 'Chemical Engineer', 'default.jpg'),
  ('Miner Abazi', 'mabazia', '$2y$10$VTbK/xpteDPvaoxGuuyN8ulxU26mz/xJEwlE.Wx724.xGwou4tjzO', 'mabazia@mashable.com', 'Payment Adjustment Coordinator', 'default.jpg'),
  ('Kleon Olech', 'kolechb', '$2y$10$VTbK/xpteDPvaoxGuuyN8ulxU26mz/xJEwlE.Wx724.xGwou4tjzO', 'kolechb@studiopress.com', 'Environmental Specialist', 'default.jpg'),
  ('Emmy Sisley', 'esisleyc', '$2y$10$VTbK/xpteDPvaoxGuuyN8ulxU26mz/xJEwlE.Wx724.xGwou4tjzO', 'esisleyc@noaa.gov', 'Account Representative II', 'default.jpg'),
  ('Olav Zanetto', 'ozanettod', '$2y$10$VTbK/xpteDPvaoxGuuyN8ulxU26mz/xJEwlE.Wx724.xGwou4tjzO', 'ozanettod@hostgator.com', 'Senior Developer', 'default.jpg'),
  ('Paulina Habbeshaw', 'phabbeshawe', '$2y$10$VTbK/xpteDPvaoxGuuyN8ulxU26mz/xJEwlE.Wx724.xGwou4tjzO', 'phabbeshawe@time.com', 'Librarian', 'default.jpg'),
  ('Karil Peoples', 'kpeoplesf', '$2y$10$VTbK/xpteDPvaoxGuuyN8ulxU26mz/xJEwlE.Wx724.xGwou4tjzO', 'kpeoplesf@behance.net', 'Occupational Therapist', 'default.jpg'),
  ('Daisi Worters', 'dwortersg', '$2y$10$VTbK/xpteDPvaoxGuuyN8ulxU26mz/xJEwlE.Wx724.xGwou4tjzO', 'dwortersg@icio.us', 'Help Desk Operator', 'default.jpg'),
  ('Cathee Carthy', 'ccarthyh', '$2y$10$VTbK/xpteDPvaoxGuuyN8ulxU26mz/xJEwlE.Wx724.xGwou4tjzO', 'ccarthyh@twitter.com', 'Senior Quality Engineer', 'default.jpg'),
  ('Dusty Maxwaile', 'dmaxwailei', '$2y$10$VTbK/xpteDPvaoxGuuyN8ulxU26mz/xJEwlE.Wx724.xGwou4tjzO', 'dmaxwailei@google.nl', 'Systems Administrator II', 'default.jpg'),
  ('Tybalt Russan', 'trussanj', '$2y$10$VTbK/xpteDPvaoxGuuyN8ulxU26mz/xJEwlE.Wx724.xGwou4tjzO', 'trussanj@linkedin.com', 'Editor', 'default.jpg'),
  ('Rhona Kemmett', 'rkemmettk', '$2y$10$VTbK/xpteDPvaoxGuuyN8ulxU26mz/xJEwlE.Wx724.xGwou4tjzO', 'rkemmettk@lulu.com', 'Information Systems Manager', 'default.jpg'),
  ('Rubetta Molesworth', 'rmolesworthl', '$2y$10$VTbK/xpteDPvaoxGuuyN8ulxU26mz/xJEwlE.Wx724.xGwou4tjzO', 'rmolesworthl@hexun.com', 'Geologist I', 'default.jpg'),
  ('Magdaia Volcker', 'mvolckerm', '$2y$10$VTbK/xpteDPvaoxGuuyN8ulxU26mz/xJEwlE.Wx724.xGwou4tjzO', 'mvolckerm@bluehost.com', 'VP Product Management', 'default.jpg'),
  ('Eleen Bullin', 'ebullinn', '$2y$10$VTbK/xpteDPvaoxGuuyN8ulxU26mz/xJEwlE.Wx724.xGwou4tjzO', 'ebullinn@vk.com', 'Software Engineer IV', 'default.jpg'),
  ('Anallese Thoma', 'athomao', '$2y$10$VTbK/xpteDPvaoxGuuyN8ulxU26mz/xJEwlE.Wx724.xGwou4tjzO', 'athomao@flickr.com', 'Research Assistant III', 'default.jpg'),
  ('Gib Kipping', 'gkippingp', '$2y$10$VTbK/xpteDPvaoxGuuyN8ulxU26mz/xJEwlE.Wx724.xGwou4tjzO', 'gkippingp@joomla.org', 'Operator', 'default.jpg'),
  ('Jasun Deverock', 'jdeverockq', '$2y$10$VTbK/xpteDPvaoxGuuyN8ulxU26mz/xJEwlE.Wx724.xGwou4tjzO', 'jdeverockq@addthis.com', 'Statistician IV', 'default.jpg'),
  ('Jodee Burmaster', 'jburmasterr', '$2y$10$VTbK/xpteDPvaoxGuuyN8ulxU26mz/xJEwlE.Wx724.xGwou4tjzO', 'jburmasterr@foxnews.com', 'Biostatistician I', 'default.jpg'),
  ('Glynn Baytrop', 'gbaytrops', '$2y$10$VTbK/xpteDPvaoxGuuyN8ulxU26mz/xJEwlE.Wx724.xGwou4tjzO', 'gbaytropj,s@ed.gov', 'Sales Representative', 'default.jpg'),
  ('Zilvia Marvell', 'zmarvellt', '$2y$10$VTbK/xpteDPvaoxGuuyN8ulxU26mz/xJEwlE.Wx724.xGwou4tjzO', 'zmarvellt@cbslocal.com', 'Senior Quality Engineer', 'default.jpg'),
  ('1', '1', '$2y$10$VTbK/xpteDPvaoxGuuyN8ulxU26mz/xJEwlE.Wx724.xGwou4tjzO', '1', '1', 'default.jpg'),
  ('admin', 'admin', '$2y$10$VTbK/xpteDPvaoxGuuyN8ulxU26mz/xJEwlE.Wx724.xGwou4tjzO', 'up201806317@edu.fe.up.pt', 'admin', 'default.jpg');

-- R02
insert into moderator (id) values (1), (2), (3), (32);

-- R03
insert into administrator (id) values (1), (32);

-- R04
insert into ban (id_user, id_admin, "date", reason) values
  (10, 1, '2021-03-23', 'Verbal abuse.'),
  (11, 1, '2021-03-22', 'Racist comments.');

-- R05
insert into news (id, author, title, subtitle, body, date) values
  (1, 1, 'Hello and welcome.', 'The website is open.', 'You can now join and use our community.', '2021-03-15'),
  (2, 1, 'We have reached 100 members.', 'Our community is growing.', 'Thank you everyone for trusting us.', '2021-03-15');

-- R06
INSERT INTO topic_proposal (id_user, id_admin, topic_name, "date", reason, accepted) VALUES
  (3, NULL, 'zig', '2020-03-28', 'interesting language', false),
  (3, NULL, 'bazinga', '2021-03-03', 'big bog theorom', false),
  (1, 1, 'c++', '2020-01-11','i like c plus plus', false);

-- R07
INSERT INTO achievement(id, title, body) VALUES
  (1, 'Post first question', 'You posted your first question on Segmentation Fault'),
  (2, 'Get first accepted answer', 'An answer you posted was chosen as the accepted answer'),
  (3, 'Reached 100 reputation', 'You have reached 100 reputation! Thank you for your contribution'),
  (4, 'Reached 200 reputation', 'You have reached 200 reputation! Thank you for your contribution'),
  (5, 'Reached 500 reputation', 'You have reached 500 reputation! Thank you for your contribution'),
  (6, 'Reached 1000 reputation', 'You have reached 1000 reputation! Thank you for your contribution');

UPDATE "user" SET reputation = 550 WHERE id = 1;
UPDATE "user" SET reputation = 1050 WHERE id = 2;
UPDATE "user" SET reputation = 150 WHERE id = 3;
UPDATE "user" SET reputation = 700 WHERE id = 5;
UPDATE "user" SET reputation = 100 WHERE id = 12;

-- CREATE OR REPLACE PROCEDURE create_question(OwnerUser INT, Body TEXT, DatePost DATE, Title TEXT, Bounty INT, Closed BOOLEAN)
-- CREATE OR REPLACE PROCEDURE create_answer(OwnerUser INT, Body TEXT, DatePost DATE, IdQuestion INT)
-- CREATE OR REPLACE PROCEDURE create_comment(OwnerUser INT, Body TEXT, DatePost DATE, IdQuestion INT, IdAnswer INT)
CALL create_question(1, 'If Python does not have a ternary conditional operator, is it possible to simulate one using other language constructs?', TIMESTAMP '2008-12-17 00:00:00', 'Does Python have a ternary conditional operator?', 0, true);
CALL create_comment(2, 'In the Python 3.0 official documentation referenced in a comment above, this is referred to as "conditional_expressions" and is very cryptically defined. That documentation doesn''t even include the term "ternary", so you would be hard-pressed to find it via Google unless you knew exactly what to look for. The version 2 documentation is somewhat more helpful and includes a link to "PEP 308", which includes a lot of interesting historical context related to this question.', TIMESTAMP '2013-01-10 00:00:00', 1, NULL);
CALL create_answer(3, '<expression 2> if <condition> else <expression 1>', TIMESTAMP '2010-05-27 00:00:00', 1);
CALL create_comment(4, 'This one emphasizes the primary intent of the ternary operator: value selection. It also shows that more than one ternary can be chained together into a single expression.', TIMESTAMP '2010-10-04 00:00:00', NULL, 3);
CALL create_question(5, 'What is the difference between a function decorated with @staticmethod and one decorated with @classmethod?', TIMESTAMP '2008-09-25 00:00:00', 'Difference between staticmethod and classmethod', 50, false);
CALL create_comment(6, 'tl;dr >> when compared to normal methods, the static methods and class methods can also be accessed using the class but unlike class methods, static methods are immutable via inheritance.', TIMESTAMP '2018-07-11 00:00:00', 5, NULL);
CALL create_answer(7, 'Basically @classmethod makes a method whose first argument is the class it''s called from (rather than the class instance), @staticmethod does not have any implicit arguments.',TIMESTAMP '2008-09-25 00:00:00', 5);
CALL create_answer(8, '@classmethod : can be used to create a shared global access to all the instances created of that class..... like updating a record by multiple users.... I particulary found it use ful when creating singletons as well..:)\n@static method: has nothing to do with the class or instance being associated with ...but for readability can use static method', TIMESTAMP '2017-09-20 00:00:00', 5);
CALL create_question(9, 'If Python does not have a ternary conditional operator, is it possible to simulate one using other language constructs?', '2008-12-17 00:00:00', 'How can I remove a specific item from an array?', 0, true);
CALL create_answer(10, 'Find the index of the array element you want to remove using indexOf, and then remove that index with splice.', TIMESTAMP '2011-04-23 00:00:00', 9);
CALL create_comment(11, 'Serious question: why doesn''t JavaScript allow the simple and intuitive method of removing an element at an index? A simple, elegant, myArray.remove(index); seems to be the best solution and is implemented in many other languages (a lot of them older than JavaScript.)', TIMESTAMP '2020-09-10 00:00:00', NULL, 10);
CALL create_question(12, 'I have an array of numbers and I''m using the .push() method to add elements to it.\nIs there a simple way to remove a specific element from an array?\nI''m looking for the equivalent of something like:\narray.remove(number);\nI have to use core JavaScript. Frameworks are not allowed.', TIMESTAMP '2011-04-23 00:00:00', 'How to use non-packaged Python code from GitHub?', 30, false);
CALL create_answer(13, 'That repository doesn''t seem to be properly packaged for library use at all. I''d recommend forking it, making the changes you need to make it usable (moving the files into a package, adding a setup.py) and then using that as a git+https:// style requirement.', TIMESTAMP '2021-03-28 00:00:00', 12);
CALL create_comment(14, 'Exactly, the repo is also under an MIT License, which really even allows keeping the source files in the project directly.', TIMESTAMP '2021-03-28 00:00:00', 12, NULL);
CALL create_question(15, 'How do you set, clear, and toggle a bit?', TIMESTAMP '2019-08-22 00:00:00', 'How do you set, clear, and toggle a single bit?', 0, true);
CALL create_answer(16, 'Use the bitwise OR operator (|) to set a bit.', TIMESTAMP '2020-09-20 00:00:00', 15);
CALL create_comment(17, 'You are very dumb haha',TIMESTAMP '2020-09-20 00:00:00', 15, NULL);
CALL create_question(1, 'How do you post a question', TIMESTAMP '2019-08-22 00:00:00', 'is it here?', 0, false);
CALL create_answer(1, 'Someone delete this please', TIMESTAMP '2021-02-21 00:00:00', 18);

UPDATE question SET accepted_answer = 3 WHERE id = 1;
UPDATE question SET accepted_answer = 10 WHERE id = 9;
UPDATE question SET accepted_answer = 16 WHERE id = 15;

-- R14
INSERT INTO vote(id_post, id_user, value) VALUES
  (1, 2, -1),
  (1, 3, 1),
  (1, 4, -1),
  (3, 6, 1),
  (3, 7, -1),
  (5, 10, 1),
  (5, 15, 1),
  (5, 11, 1);

-- R15
INSERT INTO edit_proposal(id_post, id_user, id_moderator, body, accepted) VALUES
  (3, 5, NULL, '<expression 1> if <condition> else <expression 2>', false),
  (2, 2, NULL, 'Example Proposal', false);


-- R16
INSERT INTO topic (name) VALUES ('cpp');
INSERT INTO topic (name) VALUES ('python');
INSERT INTO topic (name) VALUES ('javascript');
INSERT INTO topic (name) VALUES ('c');
INSERT INTO topic (name) VALUES ('sql');
INSERT INTO topic (name) VALUES ('prolog');

--R17
INSERT INTO topic_question (id_topic, id_question) VALUES (2, 1);
INSERT INTO topic_question (id_topic, id_question) VALUES (2, 5);
INSERT INTO topic_question (id_topic, id_question) VALUES (3, 9);
INSERT INTO topic_question (id_topic, id_question) VALUES (2, 12);
INSERT INTO topic_question (id_topic, id_question) VALUES (1, 15);
INSERT INTO topic_question (id_topic, id_question) VALUES (4, 15);
INSERT INTO topic_question (id_topic, id_question) VALUES (1, 18);
INSERT INTO topic_question (id_topic, id_question) VALUES (2, 18);
INSERT INTO topic_question (id_topic, id_question) VALUES (6, 18);

-- R18
INSERT INTO report (id_post, reporter, "date", reason, state, reviewer) VALUES
  (2, 1, '2020-07-29', 'He called me dumb', 'pending', 2),
  (7, 5, '2021-01-09', 'He called me not cool', 'pending', 2),
  (10, 10, '2020-09-20', 'He was bad', 'approved', 2),
  (15, 15, '2020-11-12', 'He is sheldon cooper', 'rejected', 2);

Revision history

Changes made to the first submission:

  • Restored 'NOT NULL' constraints to username and email of each user.
  • Added 'isdeleted' boolean to users to signify deletion.
  • Updated 'remove_user' rule.
  • Fixed bug on reputation based on the user's posts' scores.
  • Achievements are now considered when calculating a user's reputation.
  • Added accepting answer trigger (updates reputation based on bounty)
  • Changed adding answer to subtract bounty from user's reputation
  • Changed all dates to timestamps to track time more accurately
  • Added condition to achievement trigger so that on 100 reputation a user becomes a moderator
  • Added on delete cascade to comment table to account for answer deletion
  • Added trigger to delete the post when a comment, answer or question are deleted
  • Added trigger to delete answer when an answer_question is deleted
  • Added trigger to delete notifications when a notification_post or notification_achievement is deleted
  • Added password_reset table for laravel reset password function and remember_token field to user table
  • Removed date from ban_user procedure

GROUP2113, 18/04/2021

Clone this wiki locally