G6cgqG4M

1CREATE EXTENSION IF NOT EXISTS pgcrypto;
2
3CREATE DOMAIN Id as INTEGER
4NOT NULL;
5
6CREATE DOMAIN IdType AS TEXT
7NOT NULL
8CHECK(VALUE in(
9    'action',
10    'member',
11    'project',
12    'authority'
13));
14
15CREATE TABLE Identifiers( 
16    id Id PRIMARY KEY,
17    idType IdType
18);
19
20CREATE DOMAIN VoteInt AS INTEGER
21NOT NULL
22DEFAULT 0;
23
24CREATE TABLE Members( 
25    memberId Id PRIMARY KEY REFERENCES Identifiers(id),
26    passwordHash TEXT,
27    lastActivity TIMESTAMP,
28    upvotes VoteInt,
29    downvotes VoteInt
30);
31
32CREATE TABLE Leaders( 
33    memberId Id PRIMARY KEY REFERENCES Members(memberId)
34);
35
36CREATE VIEW Trolls AS (
37    SELECT memberId, upvotes, downvotes, lastActivity
38    FROM Members
39    WHERE downvotes > upvotes
40    ORDER BY downvotes - upvotes DESC, memberId ASC
41);
42
43
44CREATE TABLE Projects( 
45    projectId Id PRIMARY KEY REFERENCES Identifiers(id),
46    authorityId Id REFERENCES Identifiers(id)
47);
48
49CREATE DOMAIN ActionType AS TEXT
50NOT NULL
51CHECK(VALUE in(
52    'support',
53    'protest'
54));
55
56CREATE TABLE Actions( 
57    actionId Id PRIMARY KEY REFERENCES Identifiers(id),
58    actionType ActionType,
59    projectId Id REFERENCES Projects(projectId),
60    memberId Id REFERENCES Members(memberId),
61    upvotes VoteInt,
62    downvotes VoteInt
63);
64
65CREATE DOMAIN VoteType AS SMALLINT
66NOT NULL
67CHECK(VALUE in(
68    -1,
69    +1
70));
71
72CREATE TABLE Votes( 
73    actionId Id REFERENCES Actions(actionId),
74    memberId Id REFERENCES Members(memberId),
75    voteType VoteType,
76    PRIMARY KEY(actionId, memberId)
77);
78
79CREATE FUNCTION votesTriggerFunction() RETURNS TRIGGER AS $X$
80BEGIN
81    UPDATE Actions 
82    SET upvotes = upvotes + 1 
83    WHERE actionId = NEW.actionId AND NEW.voteType = 1;
84
85    UPDATE Actions 
86    SET downvotes = downvotes + 1 
87    WHERE actionId = NEW.actionId AND NEW.voteType = -1;
88    RETURN NEW;
89END
90$X$ LANGUAGE plpgsql;
91
92CREATE TRIGGER votesTrigger AFTER INSERT ON Votes FOR EACH ROW
93EXECUTE PROCEDURE votesTriggerFunction();
94
95CREATE FUNCTION actionsTriggerFunction() RETURNS TRIGGER AS $X$
96BEGIN
97    UPDATE Members 
98    SET upvotes = upvotes - OLD.upvotes + NEW.upvotes 
99    WHERE memberId = NEW.memberId;
100
101    UPDATE Members 
102    SET downvotes = downvotes - OLD.downvotes + NEW.downvotes 
103    WHERE memberId = NEW.memberId;
104    RETURN NEW;
105END
106$X$ LANGUAGE plpgsql;
107
108CREATE TRIGGER actionsTrigger AFTER UPDATE ON Actions FOR EACH ROW
109EXECUTE PROCEDURE actionsTriggerFunction();
110
111CREATE USER app WITH ENCRYPTED PASSWORD 'md596d1b2d8ca22e9afe63b1fc7bb10b9de';
112GRANT SELECT, INSERT ON TABLE Identifiers TO app;
113GRANT SELECT, INSERT, UPDATE ON TABLE Members TO app;
114GRANT SELECT ON TABLE Leaders TO app;
115GRANT SELECT, INSERT ON TABLE Projects TO app;
116GRANT SELECT, INSERT, UPDATE ON TABLE Actions TO app;
117GRANT SELECT, INSERT ON TABLE Votes TO app;
118GRANT SELECT ON TABLE Trolls TO app;