RelationalDesign: buildboard-ddl.2.sql

File buildboard-ddl.2.sql, 10.0 KB (added by 225144, 10 months ago)
Line 
1--- Trigger before update/insert za check na iminjata topic/discussion -> OK
2--- Trigger za ko ke adnit dete na topic thread sho e vo proekt, da go dodajt kako belongs_to vo proektot
3--- Trigger za check dali reply na discussion thread pripagjat na ist topic thread kako na toj so mu pret reply
4--- IMENUVANJE: triggeri so provervat nesto prefix = check, funkcii za istite prefix = validate
5--- Nemame contraint sho velit deka sekoj topic thread trebat da e moderiran
6DROP TABLE IF EXISTS users CASCADE;
7DROP TABLE IF EXISTS moderator CASCADE;
8DROP TABLE IF EXISTS developer CASCADE;
9DROP TABLE IF EXISTS project_manager CASCADE;
10DROP TABLE IF EXISTS thread CASCADE;
11DROP TABLE IF EXISTS likes CASCADE;
12DROP TABLE IF EXISTS topic_threads_moderators CASCADE;
13DROP TABLE IF EXISTS tag CASCADE;
14DROP TABLE IF EXISTS tag_threads CASCADE;
15DROP TABLE IF EXISTS topic_thread CASCADE;
16DROP TABLE IF EXISTS topic_belongs_to_project CASCADE;
17DROP TABLE IF EXISTS blacklisted_user CASCADE;
18DROP TABLE IF EXISTS project_thread CASCADE;
19DROP TABLE IF EXISTS discussion_thread CASCADE;
20DROP TABLE IF EXISTS developer_associated_with_project CASCADE;
21DROP TABLE IF EXISTS permissions CASCADE;
22DROP TABLE IF EXISTS project_roles CASCADE;
23DROP TABLE IF EXISTS users_project_roles CASCADE;
24DROP TABLE IF EXISTS project_roles_permissions CASCADE;
25DROP TABLE IF EXISTS project_request CASCADE;
26DROP TABLE IF EXISTS report CASCADE;
27DROP TABLE IF EXISTS channel CASCADE;
28DROP TABLE IF EXISTS messages CASCADE;
29DROP TABLE IF EXISTS threads_moderators CASCADE;
30DROP TYPE IF EXISTS status;
31DROP VIEW IF EXISTS v_topic_thread CASCADE;
32DROP VIEW IF EXISTS v_project_thread CASCADE;
33DROP VIEW IF EXISTS v_discussion_thread CASCADE;
34DROP VIEW IF EXISTS v_developer CASCADE;
35DROP VIEW IF EXISTS v_project_owner CASCADE;
36DROP VIEW IF EXISTS v_moderator CASCADE;
37DROP FUNCTION IF EXISTS fn_project_insert_child_topic CASCADE;
38DROP FUNCTION IF EXISTS fn_validate_same_parent CASCADE;
39DROP TRIGGER IF EXISTS validate_same_parent ON discussion_thread CASCADE;
40---- DDL
41CREATE TABLE users
42(
43 id SERIAL PRIMARY KEY,
44 username VARCHAR(32) UNIQUE NOT NULL,
45 is_activate bool,
46 password VARCHAR(72),
47 description VARCHAR(200),
48 registered_at TIMESTAMP,
49 sex VARCHAR(1)
50);
51CREATE TABLE moderator
52(
53 id INT PRIMARY KEY REFERENCES users (id) on delete cascade
54);
55CREATE TABLE developer
56(
57 id INT PRIMARY KEY REFERENCES users (id) on delete cascade
58);
59CREATE TABLE project_manager
60(
61 id INT PRIMARY KEY REFERENCES users (id) on delete cascade
62);
63CREATE TABLE thread
64(
65 id SERIAL PRIMARY KEY,
66 content TEXT,
67 user_id INT REFERENCES users (id) NOT NULL
68);
69CREATE TABLE topic_thread
70(
71 title VARCHAR(32) NOT NULL,
72 guidelines jsonb,
73 parent_id INT REFERENCES thread (id),
74 id INT PRIMARY KEY REFERENCES thread (id) on delete cascade
75);
76CREATE TABLE discussion_thread
77(
78 parent_id INT REFERENCES thread (id) NOT NULL,
79 id INT PRIMARY KEY REFERENCES thread (id) on delete cascade
80);
81CREATE TABLE project_thread
82(
83 title VARCHAR(32) NOT NULL,
84 repo_url TEXT,
85 id INT PRIMARY KEY REFERENCES thread (id) on delete cascade
86);
87CREATE TABLE likes
88(
89 user_id INT REFERENCES users (id),
90 thread_id INT REFERENCES thread (id),
91 PRIMARY KEY (user_id, thread_id)
92);
93CREATE TABLE topic_threads_moderators
94(
95 thread_id INT REFERENCES thread (id) ON DELETE CASCADE,
96 user_id INT REFERENCES users (id) ON DELETE CASCADE,
97 PRIMARY KEY (thread_id, user_id)
98);
99CREATE TABLE tag
100(
101 name VARCHAR(64) PRIMARY KEY
102);
103CREATE TABLE tag_threads
104(
105 thread_id INT REFERENCES thread (id),
106 tag_name VARCHAR(64) REFERENCES tag (name),
107 PRIMARY KEY (thread_id, tag_name)
108);
109
110CREATE TABLE topic_belongs_to_project
111(
112 topic_id INT REFERENCES thread (id) ON DELETE CASCADE,
113 project_id INT REFERENCES thread (id) ON DELETE CASCADE,
114 PRIMARY KEY (topic_id, project_id)
115);
116CREATE TABLE blacklisted_user
117(
118 topic_id INT REFERENCES thread (id) ON DELETE CASCADE,
119 user_id INT REFERENCES users (id) ON DELETE CASCADE,
120 moderator_id INT REFERENCES users (id) ON DELETE CASCADE,
121 start_date TIMESTAMP,
122 end_date TIMESTAMP,
123 reason TEXT,
124 PRIMARY KEY (user_id, moderator_id, topic_id, start_date)
125);
126
127CREATE TABLE developer_associated_with_project
128(
129 project_id INT REFERENCES thread (id),
130 developer_id INT REFERENCES users (id),
131 started_at TIMESTAMP,
132 ended_at TIMESTAMP,
133 PRIMARY KEY (project_id, developer_id, started_at)
134);
135CREATE TABLE permissions
136(
137 name VARCHAR(32) PRIMARY KEY
138);
139CREATE TABLE project_roles
140(
141 name VARCHAR(32),
142 project_id INT REFERENCES thread (id) ON DELETE CASCADE,
143 description TEXT,
144 PRIMARY KEY (name, project_id)
145);
146CREATE TABLE users_project_roles
147(
148 user_id INT REFERENCES users (id),
149 project_id INT,
150 role_name VARCHAR(32),
151 FOREIGN KEY (role_name, project_id)
152 REFERENCES project_roles (name, project_id),
153 PRIMARY KEY (user_id, project_id, role_name)
154);
155CREATE TABLE project_roles_permissions
156(
157 permission_name VARCHAR(32) REFERENCES permissions (name),
158 role_name VARCHAR(32),
159 project_id INT,
160 PRIMARY KEY (permission_name, role_name, project_id),
161 FOREIGN KEY (role_name, project_id)
162 REFERENCES project_roles (name, project_id)
163);
164CREATE TYPE status AS ENUM ('ACCEPTED', 'DENIED', 'PENDING');
165CREATE TABLE project_request
166(
167 id SERIAL PRIMARY KEY,
168 description VARCHAR(200),
169 status status NOT NULL,
170 user_id INT REFERENCES users (id) NOT NULL,
171 project_id INT REFERENCES thread (id) NOT NULL
172);
173CREATE TABLE report
174(
175 id SERIAL,
176 created_at TIMESTAMP,
177 description VARCHAR(200) NOT NULL,
178 status status,
179 thread_id INT REFERENCES thread (id),
180 for_user_id INT REFERENCES users (id),
181 by_user_id INT REFERENCES users (id),
182 PRIMARY KEY (id, thread_id, for_user_id, by_user_id)
183);
184CREATE TABLE channel
185(
186 name VARCHAR(64),
187 description VARCHAR(200),
188 project_id INT REFERENCES thread (id) ON DELETE CASCADE,
189 developer_id INT REFERENCES users (id),
190 PRIMARY KEY (name, project_id)
191);
192CREATE TABLE messages
193(
194 sent_at TIMESTAMP,
195 content VARCHAR(200) NOT NULL,
196 sent_by INT REFERENCES users (id),
197 project_id INT,
198 channel_name VARCHAR(64),
199 FOREIGN KEY (channel_name, project_id)
200 REFERENCES channel (name, project_id) ON DELETE CASCADE,
201 PRIMARY KEY (channel_name, project_id, sent_at, sent_by)
202);
203------------------------VIEWS-----------------------------
204CREATE OR REPLACE VIEW v_project_thread
205AS
206SELECT thread.id, content, user_id, title, repo_url
207FROM project_thread project
208 JOIN thread
209 ON project.id = thread.id;
210CREATE OR REPLACE VIEW v_discussion_thread
211AS
212SELECT thread.id, content, user_id,parent_id
213FROM discussion_thread discussion
214 JOIN thread
215 ON discussion.id = thread.id;
216CREATE OR REPLACE VIEW v_topic_thread
217AS
218SELECT thread.id, content, user_id, title, guidelines, parent_id
219FROM topic_thread topic
220 JOIN thread
221 ON topic.id = thread.id;
222CREATE OR REPLACE VIEW v_moderator
223AS
224SELECT users.id, username, is_activate, password, description, registered_at, sex
225FROM moderator
226 JOIN users ON moderator.id = users.id;
227
228CREATE OR REPLACE VIEW v_developer
229AS
230SELECT users.id, username, is_activate, password, description, registered_at, sex
231FROM developer
232 JOIN users ON developer.id = users.id;
233CREATE OR REPLACE VIEW v_project_owner
234AS
235SELECT users.id, username, is_activate, password, description, registered_at, sex
236FROM project_manager
237 JOIN users ON project_manager.id = users.id;
238CREATE OR REPLACE VIEW v_moderator
239AS
240SELECT users.id, username, is_activate, password, description, registered_at, sex
241FROM moderator
242 JOIN users ON moderator.id = users.id;
243
244CREATE OR REPLACE VIEW v_developer
245AS
246SELECT users.id, username, is_activate, password, description, registered_at, sex
247FROM developer
248 JOIN users ON developer.id = users.id;
249CREATE OR REPLACE VIEW v_project_owner
250AS
251SELECT users.id, username, is_activate, password, description, registered_at, sex
252FROM project_manager
253 JOIN users ON project_manager.id = users.id;
254-------------------------- FUNCTIONS ----------------------
255CREATE OR REPLACE FUNCTION fn_validate_topic_title()
256 RETURNS TRIGGER
257 LANGUAGE plpgsql
258AS
259$$
260BEGIN
261 IF new.title IN
262 (SELECT title
263 FROM topic_thread
264 AS t
265 WHERE t.parent_id = new.parent_id OR (t.parent_id IS NULL AND new.parent_id IS NULL))
266 THEN
267 RAISE EXCEPTION 'There already exists a topic with title % in parent topic with id %',new.title,new.parent_id;
268END IF;
269RETURN new;
270END;
271$$;
272CREATE OR REPLACE FUNCTION fn_insert_topics_creator_as_moderator()
273 RETURNS TRIGGER
274 LANGUAGE plpgsql
275AS
276$$
277DECLARE
278v_user_id INT;
279BEGIN
280SELECT v_topic_thread.user_id
281INTO v_user_id
282FROM v_topic_thread
283WHERE v_topic_thread.id = new.id;
284INSERT INTO topic_threads_moderators(thread_id, user_id) VALUES (new.id, v_user_id);
285RETURN NEW;
286END;
287$$;
288
289CREATE OR REPLACE FUNCTION fn_insert_project_manager()
290RETURNS TRIGGER
291LANGUAGE plpgsql
292AS
293 $$
294 DECLARE usrId INT;
295BEGIN
296SELECT user_id INTO usrId FROM v_project_thread p WHERE NEW.id = p.id;
297INSERT INTO developer VALUES (usrId);
298INSERT INTO project_manager VALUES (usrId);
299RETURN NEW;
300END;
301 $$;
302-------------------------- TRIGGERS ----------------------
303CREATE OR REPLACE TRIGGER tr_check_topic_name
304 BEFORE INSERT OR UPDATE
305 ON topic_thread
306 FOR EACH ROW
307 EXECUTE FUNCTION fn_validate_topic_title();
308CREATE OR REPLACE TRIGGER tr_insert_topics_creator_as_moderator
309 AFTER INSERT
310 ON topic_thread
311 FOR EACH ROW
312EXECUTE FUNCTION fn_insert_topics_creator_as_moderator();
313CREATE OR REPLACE TRIGGER tr_insert_project_manager
314 AFTER INSERT
315 ON project_thread
316 FOR EACH ROW
317 EXECUTE FUNCTION fn_insert_project_manager();
318