-
Notifications
You must be signed in to change notification settings - Fork 1
ebd
Assembling a community-driven database of knowledge by asking and answering questions.
This component describes the artefacts that will support the storage and retrival of information described in the requirements specification.
The goal of this artefact is to present the relational schema obtained from the UML along with the SQL code.
The following Relational Schema is in the 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, password*, email* UK, 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) |
* These are null when a user deletes its account.
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 effiecent query.
The E/R style was also chosen to map Post, Quesiton, Answer and Comment, since this generalization is overlapping and has a decent number of subclasses.
Lastly, the E/R method was used aswell when mapping the Notification hierarchy because it is overlapping.
Domain Name | Domain Specification |
---|---|
Today | DATE DEFAULT CURRENT_DATE |
ReportState | ENUM ('Pending', 'Approved', 'Rejected') |
To validate the Relational Schema obtained from the Conceptual Model, all functional dependencies are identified and the normalization of all relation schemas is accomplished.
TABLE R01 | user |
---|---|
Keys | { id }, { email }, { username } |
Functional Dependencies: | |
FD0101 | id → {email, name, password, about, picture} |
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.
This artefact contains tuple estimations, the most frequent queries, most frequent updates, indices, triggers and transactions.
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 |
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);
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
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 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)
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
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") |
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) |
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;
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;
val := (SELECT sum(value)
FROM post JOIN vote ON (post.id = vote.id_post)
WHERE id = post_id);
-- update the question score
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);
val := (SELECT count(*)
FROM post
JOIN "user" ON (post.id_owner = "user".id)
WHERE "user".id = owner_id);
UPDATE "user" as u
SET reputation = val
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 ON vote CASCADE;
CREATE TRIGGER update_score
AFTER DELETE OR INSERT OR UPDATE
ON vote
FOR EACH ROW EXECUTE FUNCTION on_score_change();
Trigger | TRIGGER02 |
---|---|
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 | TRIGGER03 |
---|---|
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 | TRIGGER04 |
---|---|
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 its 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 reeaching 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',
username = NULL,
search = '',
password = NULL,
email = NULL,
about = NULL,
picture = NULL,
reputation = NULL
WHERE id = Old.id;
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. |
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);
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 | Get questions of a Answer |
---|---|
Justification | When loading a user's answer, it is useful to retreive the exact number of posts and 10 most recent posts. |
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;
-- CLEANUP
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;
-- DOMAINS
CREATE DOMAIN Today AS DATE DEFAULT CURRENT_DATE;
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 NOT NULL,
password TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
about TEXT,
picture TEXT,
reputation INTEGER NOT NULL DEFAULT 0
);
-- 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_DATE), -- 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_DATE), -- 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_DATE), -- 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_DATE), -- 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_DATE), -- 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)
);
-- 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),
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),
CONSTRAINT fk_question
FOREIGN KEY(id_question)
REFERENCES question(id)
);
-- 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),
CONSTRAINT fk_question
FOREIGN KEY(id_question)
REFERENCES question(id),
CONSTRAINT fk_answer
FOREIGN KEY(id_answer)
REFERENCES answer(id)
);
-- 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),
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),
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)
);
-- R18
CREATE TABLE report(
id_post INTEGER,
reporter INTEGER,
"date" Today NOT NULL CHECK ("date" <= CURRENT_DATE), -- reports can't be made in the future
reason TEXT,
PRIMARY KEY(id_post, reporter),
"state" report_state,
reviewer INTEGER,
CONSTRAINT fk_post
FOREIGN KEY(id_post)
REFERENCES post(id),
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_DATE), -- 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),
CONSTRAINT fk_achievement
FOREIGN KEY(id_achievement)
REFERENCES achievement(id)
);
-- R21
CREATE TABLE notification_post(
id INTEGER PRIMARY KEY,
id_post INTEGER NOT NULL,
CONSTRAINT fk_notification
FOREIGN KEY(id)
REFERENCES notification(id),
CONSTRAINT fk_post
FOREIGN KEY(id_post)
REFERENCES post(id)
);
-- 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;
post_id integer;
BEGIN
IF (TG_OP = 'DELETE') THEN
val := -OLD.value;
post_id := OLD.id_post;
ELSIF (TG_OP = 'UPDATE') THEN
val := -OLD.value + NEW.value;
post_id := NEW.id_post;
ELSIF (TG_OP = 'INSERT') THEN
val := NEW.value;
post_id := NEW.id_post;
END IF;
-- update the question score
UPDATE post
SET score = score + val
WHERE id = post_id;
-- update the question's owner reputation
UPDATE "user" as u
SET reputation = reputation + val
FROM post as p
WHERE p.id = post_id and p.id_owner = 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'));
NEW.search = (setweight(to_tsvector('english', NEW.username), 'A'));
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'));
END IF;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION question_search_update()
RETURNS TRIGGER
AS $$
DECLARE
b text;
BEGIN
-- get the body
b := (SELECT body
FROM post
WHERE post.id = NEW.id);
IF (TG_OP = 'INSERT') THEN
NEW.search = (setweight(to_tsvector('english', NEW.title), 'A') || setweight(to_tsvector('english', b), 'B'));
ELSIF (TG_OP = 'UPDATE') THEN
IF NEW.title <> OLD.title THEN
NEW.search = (setweight(to_tsvector('english', NEW.title), 'A') || setweight(to_tsvector('english', b), 'B'));
END IF;
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;
-- TRIGGERS
DROP TRIGGER IF EXISTS update_score ON vote CASCADE;
CREATE TRIGGER update_score
AFTER DELETE OR INSERT OR UPDATE
ON vote
FOR EACH ROW EXECUTE FUNCTION on_score_change();
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();
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();
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();
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();
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();
-- TRANSACTIONS
CREATE OR REPLACE PROCEDURE create_question
(
OwnerUser INT,
Body TEXT,
DatePost DATE,
Title TEXT,
Bounty INT,
Closed BOOLEAN
)
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 question(id, accepted_answer, title, bounty, closed)
VALUES (currval(pg_get_serial_sequence('post','id')), NULL, Title, Bounty, Closed);
END
$$;
CREATE OR REPLACE PROCEDURE create_comment
(
OwnerUser INT,
Body TEXT,
DatePost DATE,
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 DATE,
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
$$;
-- R01
insert into "user" (id, name, username, password, email, about, picture, reputation) values
(1, 'Torrance Jerrom', 'tjerrom0', 'YeBGgmC', 'tjerrom0@deliciousdays.com', 'Dental Hygienist', 'http://dummyimage.com/102x100.png/ff4444/ffffff', 612),
(2, 'Keenan O''Bruen', 'kobruen1', 'BTFYEdJ', 'kobruen1@yolasite.com', 'Tax Accountant', 'http://dummyimage.com/135x100.png/dddddd/000000', 215),
(3, 'Joey Kores', 'jkores2', 'hSpFcNF', 'jkores2@parallels.com', 'Administrative Officer', 'http://dummyimage.com/169x100.png/5fa2dd/ffffff', 449),
(4, 'Berky Shakespeare', 'bshakespeare3', 'UlBGb5', 'bshakespeare3@deliciousdays.com', 'Biostatistician IV', 'http://dummyimage.com/110x100.png/dddddd/000000', 901),
(5, 'Ives Shinn', 'ishinn4', 'ct4ESru', 'ishinn4@topsy.com', 'Programmer Analyst IV', 'http://dummyimage.com/226x100.png/cc0000/ffffff', 741),
(6, 'Ian Franklyn', 'ifranklyn5', 'zLrS0M6kj', 'ifranklyn5@oracle.com', 'Senior Quality Engineer', 'http://dummyimage.com/231x100.png/ff4444/ffffff', 712),
(7, 'Pernell Danelut', 'pdanelut6', 'Ev58adsWg', 'pdanelut6@shareasale.com', 'Marketing Manager', 'http://dummyimage.com/233x100.png/ff4444/ffffff', 84),
(8, 'Malachi Rilings', 'mrilings7', 'KzNfOzPGNf0', 'mrilings7@tinypic.com', 'Nurse', 'http://dummyimage.com/248x100.png/ff4444/ffffff', 850),
(9, 'Gill Lehrian', 'glehrian8', '5so0kMf9N7D9', 'glehrian8@sina.com.cn', 'Junior Executive', 'http://dummyimage.com/170x100.png/ff4444/ffffff', 153),
(10, 'Baily Bernet', 'bbernet9', 'TTXdHTpMu3N', 'bbernet9@weather.com', 'Chemical Engineer', 'http://dummyimage.com/199x100.png/ff4444/ffffff', 669),
(11, 'Miner Abazi', 'mabazia', 'EllP4J8DVM', 'mabazia@mashable.com', 'Payment Adjustment Coordinator', 'http://dummyimage.com/141x100.png/dddddd/000000', 345),
(12, 'Kleon Olech', 'kolechb', 'Nau0PYH5ZS', 'kolechb@studiopress.com', 'Environmental Specialist', 'http://dummyimage.com/198x100.png/5fa2dd/ffffff', 788),
(13, 'Emmy Sisley', 'esisleyc', 'VICwW4yX', 'esisleyc@noaa.gov', 'Account Representative II', 'http://dummyimage.com/174x100.png/5fa2dd/ffffff', 104),
(14, 'Olav Zanetto', 'ozanettod', 'OJJLPwNPIIf', 'ozanettod@hostgator.com', 'Senior Developer', 'http://dummyimage.com/116x100.png/dddddd/000000', 655),
(15, 'Paulina Habbeshaw', 'phabbeshawe', 'QZkZLn', 'phabbeshawe@time.com', 'Librarian', 'http://dummyimage.com/224x100.png/ff4444/ffffff', 91),
(16, 'Karil Peoples', 'kpeoplesf', '9mLVnY1Fkksm', 'kpeoplesf@behance.net', 'Occupational Therapist', 'http://dummyimage.com/200x100.png/5fa2dd/ffffff', 968),
(17, 'Daisi Worters', 'dwortersg', 'WEwGru55Bl4a', 'dwortersg@icio.us', 'Help Desk Operator', 'http://dummyimage.com/143x100.png/dddddd/000000', 953),
(18, 'Cathee Carthy', 'ccarthyh', 'rhfqZj3kV02', 'ccarthyh@twitter.com', 'Senior Quality Engineer', 'http://dummyimage.com/177x100.png/ff4444/ffffff', 360),
(19, 'Dusty Maxwaile', 'dmaxwailei', 'oZJFF9', 'dmaxwailei@google.nl', 'Systems Administrator II', 'http://dummyimage.com/235x100.png/ff4444/ffffff', 455),
(20, 'Tybalt Russan', 'trussanj', '1rlJ9S3', 'trussanj@linkedin.com', 'Editor', 'http://dummyimage.com/179x100.png/ff4444/ffffff', 349),
(21, 'Rhona Kemmett', 'rkemmettk', 'xCrizjpI', 'rkemmettk@lulu.com', 'Information Systems Manager', 'http://dummyimage.com/120x100.png/ff4444/ffffff', 221),
(22, 'Rubetta Molesworth', 'rmolesworthl', 'HebsnJ7jD1nH', 'rmolesworthl@hexun.com', 'Geologist I', 'http://dummyimage.com/227x100.png/ff4444/ffffff', 375),
(23, 'Magdaia Volcker', 'mvolckerm', '7B8AVrNiv7FK', 'mvolckerm@bluehost.com', 'VP Product Management', 'http://dummyimage.com/122x100.png/dddddd/000000', 739),
(24, 'Eleen Bullin', 'ebullinn', 'LrZT0WxXi', 'ebullinn@vk.com', 'Software Engineer IV', 'http://dummyimage.com/172x100.png/5fa2dd/ffffff', 525),
(25, 'Anallese Thoma', 'athomao', 'DBlMQm', 'athomao@flickr.com', 'Research Assistant III', 'http://dummyimage.com/150x100.png/5fa2dd/ffffff', 330),
(26, 'Gib Kipping', 'gkippingp', 'f0GXcvW1l', 'gkippingp@joomla.org', 'Operator', 'http://dummyimage.com/120x100.png/cc0000/ffffff', 812),
(27, 'Jasun Deverock', 'jdeverockq', 'T7ELVdowbl0', 'jdeverockq@addthis.com', 'Statistician IV', 'http://dummyimage.com/115x100.png/cc0000/ffffff', 493),
(28, 'Jodee Burmaster', 'jburmasterr', 'mffWNq', 'jburmasterr@foxnews.com', 'Biostatistician I', 'http://dummyimage.com/148x100.png/5fa2dd/ffffff', 934),
(29, 'Glynn Baytrop', 'gbaytrops', 'r4HP1rmMuAfo', 'gbaytrops@ed.gov', 'Sales Representative', 'http://dummyimage.com/218x100.png/5fa2dd/ffffff', 778),
(30, 'Zilvia Marvell', 'zmarvellt', 'uudLf0', 'zmarvellt@cbslocal.com', 'Senior Quality Engineer', 'http://dummyimage.com/139x100.png/ff4444/ffffff', 928);
-- R02
insert into moderator (id) values (1), (2), (3);
-- R03
insert into administrator (id) values (1);
-- 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, id_user, id_admin, topic_name, "date", reason, accepted) VALUES
(1, 3, NULL, 'zig', '2020-03-28', 'interesting language', false),
(2, 1, 1, 'c++', '2020-03-28','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');
-- R08
INSERT INTO achieved(id_user, id_achievement, "date") VALUES (1, 1, '2008-12-17');
INSERT INTO achieved(id_user, id_achievement, "date") VALUES (5, 1, '2008-09-25');
INSERT INTO achieved(id_user, id_achievement, "date") VALUES (9, 1, '2011-04-23');
INSERT INTO achieved(id_user, id_achievement, "date") VALUES (12, 1, '2021-03-28');
INSERT INTO achieved(id_user, id_achievement, "date") VALUES (15, 1, '2019-08-22');
INSERT INTO achieved(id_user, id_achievement, "date") VALUES (3, 2, '2010-05-27');
INSERT INTO achieved(id_user, id_achievement, "date") VALUES (10, 2, '2011-04-23');
INSERT INTO achieved(id_user, id_achievement, "date") VALUES (16, 2, '2020-09-20');
-- R09
INSERT INTO post(id, id_owner, body, "date") VALUES
(1, 1, 'If Python does not have a ternary conditional operator, is it possible to simulate one using other language constructs?', '2008-12-17'),
(2, 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.', '2013-01-10'),
(3, 3, '<expression 2> if <condition> else <expression 1>', '2010-05-27'),
(4, 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.', '2010-10-04'),
(5, 5, 'What is the difference between a function decorated with @staticmethod and one decorated with @classmethod?', '2008-09-25'),
(6, 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.', '2018-07-11'),
(7, 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.','2008-09-25'),
(8, 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', '2017-09-20'),
(9, 9, '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.', '2011-04-23'),
(10, 10, 'Find the index of the array element you want to remove using indexOf, and then remove that index with splice.', '2011-04-23'),
(11, 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.)', '2020-09-10'),
(12, 12, 'I''m trying to use the code from this repository. The problem is that it isn''t a package (I think?) because it can''t be found on PyPI and there''s no setup.py file (so I can''t use pip install git+<repo link>). How would I then be able to use this code in my project?', '2021-03-28'),
(13, 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.', '2021-03-28'),
(14, 14, 'Exactly, the repo is also under an MIT License, which really even allows keeping the source files in the project directly.', '2021-03-28'),
(15, 15, 'How do you set, clear, and toggle a bit?', '2019-08-22'),
(16, 16, 'Use the bitwise OR operator (|) to set a bit.', '2020-09-20'),
(17, 17, 'You are very dumb haha','2020-09-20');
-- R10
INSERT INTO answer(id) VALUES (3);
INSERT INTO answer(id) VALUES (7);
INSERT INTO answer(id) VALUES (8);
INSERT INTO answer(id) VALUES (10);
INSERT INTO answer(id) VALUES (13);
INSERT INTO answer(id) VALUES (16);
-- R11
INSERT INTO question(id, accepted_answer, title, bounty, closed) VALUES (1, 3, 'Does Python have a ternary conditional operator?', 0, true);
INSERT INTO question(id, accepted_answer, title, bounty, closed) VALUES (5, NULL, 'Difference between staticmethod and classmethod', 20, false);
INSERT INTO question(id, accepted_answer, title, bounty, closed) VALUES (9, 10, 'How can I remove a specific item from an array?', 0, true);
INSERT INTO question(id, accepted_answer, title, bounty, closed) VALUES (12, NULL, 'How to use non-packaged Python code from GitHub?', 30, false);
INSERT INTO question(id, accepted_answer, title, bounty, closed) VALUES (15, 16, 'How do you set, clear, and toggle a single bit?', 0, true);
-- R12
INSERT INTO answer_question(id_answer, id_question) VALUES (3, 1);
INSERT INTO answer_question(id_answer, id_question) VALUES (10, 9);
INSERT INTO answer_question(id_answer, id_question) VALUES (16, 15);
INSERT INTO answer_question(id_answer, id_question) VALUES (7, 5);
INSERT INTO answer_question(id_answer, id_question) VALUES (8, 5);
INSERT INTO answer_question(id_answer, id_question) VALUES (13, 12);
-- R13
INSERT INTO comment(id, id_question, id_answer) VALUES (2, 1, NULL);
INSERT INTO comment(id, id_question, id_answer) VALUES (4, NULL, 3);
INSERT INTO comment(id, id_question, id_answer) VALUES (6, 5, NULL);
INSERT INTO comment(id, id_question, id_answer) VALUES (11, NULL, 10);
INSERT INTO comment(id, id_question, id_answer) VALUES (14, 12, NULL);
INSERT INTO comment(id, id_question, id_answer) VALUES (17, 15, NULL);
-- R14
INSERT INTO vote(id_post, id_user, value) VALUES
(1, 1, 1),
(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, id_post, id_user, id_moderator, body) VALUES
(1, 3, 5, NULL, '<expression 2> if <condition> else <expression 1>');
-- R16
INSERT INTO topic (id, name) VALUES (1, 'cpp');
INSERT INTO topic (id, name) VALUES (2, 'python');
INSERT INTO topic (id, name) VALUES (3, 'javascript');
INSERT INTO topic (id, name) VALUES (4, 'c');
INSERT INTO topic (id, name) VALUES (5, 'sql');
INSERT INTO topic (id, name) VALUES (6, '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);
-- R18
INSERT INTO report (id_post, reporter, "date", reason, state, reviewer) VALUES (15, 15, '2020-07-29', 'He called me dumb', 'pending', 2);
-- R19
INSERT INTO notification (id, "date", title, body, recipient) VALUES (1, '2021-03-01', 'New achievement', 'You have achieved: ', 1);
INSERT INTO notification (id, "date", title, body, recipient) VALUES (2, '2021-03-02', 'New achievement', 'You have achieved: ', 5);
INSERT INTO notification (id, "date", title, body, recipient) VALUES (3, '2021-03-03', 'New achievement', 'You have achieved: ', 9);
INSERT INTO notification (id, "date", title, body, recipient) VALUES (4, '2021-03-04', 'New achievement', 'You have achieved: ', 12);
INSERT INTO notification (id, "date", title, body, recipient) VALUES (5, '2021-03-05', 'New achievement', 'You have achieved: ', 15);
INSERT INTO notification (id, "date", title, body, recipient) VALUES (6, '2021-03-06', 'New achievement', 'You have achieved: ', 3);
INSERT INTO notification (id, "date", title, body, recipient) VALUES (7, '2021-03-07', 'New achievement', 'You have achieved: ', 10);
INSERT INTO notification (id, "date", title, body, recipient) VALUES (8, '2021-03-08', 'New achievement', 'You have achieved: ', 16);
INSERT INTO notification (id, "date", title, body, recipient) VALUES (9, '2021-03-10', 'New answer', 'Someone answered your question: ', 1);
INSERT INTO notification (id, "date", title, body, recipient) VALUES (10, '2021-03-11', 'New answer', 'Someone answered your question: ', 9);
INSERT INTO notification (id, "date", title, body, recipient) VALUES (11, '2021-03-12', 'New answer', 'Someone answered your question: ', 15);
INSERT INTO notification (id, "date", title, body, recipient) VALUES (12, '2021-03-13', 'New answer', 'Someone answered your question: ', 5);
INSERT INTO notification (id, "date", title, body, recipient) VALUES (13, '2021-03-14', 'New answer', 'Someone answered your question: ', 5);
INSERT INTO notification (id, "date", title, body, recipient) VALUES (14, '2021-03-15', 'New answer', 'Someone answered your question: ', 12);
-- R20
INSERT INTO notification_achievement (id, id_achievement) VALUES (1, 1);
INSERT INTO notification_achievement (id, id_achievement) VALUES (2, 1);
INSERT INTO notification_achievement (id, id_achievement) VALUES (3, 1);
INSERT INTO notification_achievement (id, id_achievement) VALUES (4, 1);
INSERT INTO notification_achievement (id, id_achievement) VALUES (5, 1);
INSERT INTO notification_achievement (id, id_achievement) VALUES (6, 1);
INSERT INTO notification_achievement (id, id_achievement) VALUES (7, 2);
INSERT INTO notification_achievement (id, id_achievement) VALUES (8, 2);
INSERT INTO notification_achievement (id, id_achievement) VALUES (9, 2);
-- R21
INSERT INTO notification_post (id, id_post) VALUES (9, 1);
INSERT INTO notification_post (id, id_post) VALUES (10, 9);
INSERT INTO notification_post (id, id_post) VALUES (11, 15);
INSERT INTO notification_post (id, id_post) VALUES (12, 5);
INSERT INTO notification_post (id, id_post) VALUES (13, 5);
INSERT INTO notification_post (id, id_post) VALUES (14, 12);
Changes made to the first submission:
None
GROUP2113, 18/04/2021
- Ana Inês Oliveira de Barros, up201806593@fe.up.pt
- João de Jesus Costa, up201806560@fe.up.pt
- João Lucas Silva Martins, up201806436@fe.up.pt
- Ricardo Jorge Cruz Fontão, up201806317@fe.up.pt (Editor)