· 5 years ago · Jun 10, 2020, 02:32 PM
1CREATE EXTENSION IF NOT EXISTS citext;
2SET TIME ZONE 'UTC';
3
4drop table IF EXISTS users CASCADE;
5drop table IF EXISTS forums CASCADE;
6drop table IF EXISTS threads CASCADE;
7drop table IF EXISTS posts CASCADE;
8drop table IF EXISTS votes CASCADE;
9drop type IF EXISTS voice;
10drop table IF EXISTS forumUsers CASCADE;
11
12
13
14create TABLE users
15(
16-- id BIGSERIAL PRIMARY KEY,
17 id SERIAL PRIMARY KEY,
18 nickname CITEXT COLLATE "C" UNIQUE,
19 fullname VARCHAR NOT NULL,
20 about VARCHAR NOT NULL,
21 email CITEXT NOT NULL
22);
23
24
25create TABLE forums
26(
27 id SERIAL PRIMARY KEY,
28 title VARCHAR NOT NULL,
29 "user" CITEXT NOT NULL REFERENCES users (nickname),
30 slug CITEXT NOT NULL UNIQUE,
31-- posts BIGINT DEFAULT 0,
32 posts INTEGER DEFAULT 0,
33 threads INTEGER DEFAULT 0
34);
35
36
37create TABLE threads
38(
39 id SERIAL PRIMARY KEY,
40 title VARCHAR NOT NULL,
41 author CITEXT NOT NULL REFERENCES users (nickname),
42 forum CITEXT NOT NULL REFERENCES forums (slug),
43 message VARCHAR NOT NULL,
44 votes INTEGER DEFAULT 0,
45 slug CITEXT,
46 created TIMESTAMPTZ
47);
48--CREATE INDEX threadsForum ON threads(forum);
49CREATE INDEX threadsForumCreated ON threads(forum, created);
50CREATE UNIQUE INDEX threadsSlug ON threads(slug);
51
52create TABLE posts
53(
54-- id BIGSERIAL PRIMARY KEY,
55 id SERIAL PRIMARY KEY,
56-- parent BIGINT DEFAULT 0,
57 parent INTEGER DEFAULT 0,
58 author CITEXT NOT NULL REFERENCES users (nickname),
59 message VARCHAR NOT NULL,
60 "isEdited" BOOLEAN NOT NULL DEFAULT FALSE,
61 forum CITEXT NOT NULL REFERENCES forums (slug),
62 thread INTEGER NOT NULL REFERENCES threads (id),
63 created TIMESTAMPTZ NOT NULL DEFAULT NOW(),
64 path integer[] DEFAULT '{}'
65);
66--CREATE INDEX postsThread ON posts(thread);
67CREATE INDEX postsParent ON posts((path[1]));
68CREATE INDEX postsThreadIdCreated ON posts(thread, id, created);
69CREATE INDEX postsThreadCreatedId ON posts(thread, created, id);
70CREATE INDEX postsThreadPathId ON posts(thread, path, id);
71CREATE INDEX postsThreadParentId ON posts(thread, parent, id);
72
73
74create TABLE votes
75(
76-- id BIGSERIAL PRIMARY KEY,
77 id SERIAL PRIMARY KEY,
78 thread INTEGER NOT NULL REFERENCES threads (id),
79 nickname CITEXT NOT NULL REFERENCES users (nickname),
80 voice INTEGER NOT NULL,
81 UNIQUE (thread, nickname)
82);
83--CREATE INDEX voteNicknames on votes (nickname);
84
85CREATE TABLE forumUsers
86(
87 forumSlug citext REFERENCES forums (slug),
88 userNickname citext COLLATE "C" REFERENCES users (nickname)
89);
90CREATE UNIQUE INDEX idxForumUser ON forumUsers (forumSlug, userNickname);
91
92
93
94
95------------------------------ TRIGGERS ----------------------------------
96
97
98-- UPDATE POST PATH
99CREATE OR REPLACE FUNCTION update_post_path() RETURNS TRIGGER AS
100$$
101BEGIN
102 IF NEW.parent != 0 THEN
103 UPDATE posts
104 SET path = prnt.path || NEW.id
105 FROM (
106 SELECT path
107 FROM posts
108 WHERE id = NEW.parent
109 ) AS prnt
110 WHERE posts.id = NEW.id;
111 ELSE
112 UPDATE posts SET path=ARRAY [NEW.id] WHERE id = NEW.id;
113 END IF;
114 return NEW;
115END;
116$$ LANGUAGE plpgsql;
117
118
119DROP TRIGGER IF EXISTS trigger_update_post_path ON posts;
120CREATE TRIGGER trigger_update_post_path
121 AFTER INSERT
122 ON posts
123 FOR EACH ROW
124EXECUTE PROCEDURE update_post_path();
125
126-- UPDATE FORUM POST COUNTER
127create or replace function update_forum_posts() RETURNS trigger AS
128$$
129begin
130 update forums set posts=posts + 1 where slug = NEW.forum;
131 return NEW;
132end;
133$$ LANGUAGE plpgsql;
134
135drop trigger IF EXISTS trigger_update_forum_posts ON posts;
136create trigger trigger_update_forum_posts
137 after insert
138 on posts
139 for each row
140EXECUTE procedure update_forum_posts();
141
142
143
144-- UPDATE FORUM THREADS COUNTER
145create or replace function update_forum_threads() RETURNS trigger AS
146$$
147begin
148 update forums set threads=threads + 1 where slug = NEW.forum;
149 return NEW;
150end;
151$$ LANGUAGE plpgsql;
152
153drop trigger IF EXISTS trigger_update_forum_threads ON threads;
154create trigger trigger_update_forum_threads
155 after insert
156 on threads
157 for each row
158EXECUTE procedure update_forum_threads();
159
160
161
162-- UPDATE THREAD VOTES SUM
163create or replace function update_thread_votes() RETURNS trigger AS
164$$
165begin
166 if tg_op = 'INSERT' then
167 update threads set votes=votes + NEW.voice where threads.id = NEW.thread;
168 ELSEIF tg_op = 'UPDATE' THEN
169 update threads set votes=votes - OLD.voice + NEW.voice where threads.id = NEW.thread;
170 end if;
171 return NEW;
172end ;
173$$ LANGUAGE plpgsql;
174
175drop trigger IF EXISTS trigger_update_thread_votes ON votes;
176create trigger trigger_update_thread_votes
177 after insert or update
178 on votes
179 for each row
180EXECUTE procedure update_thread_votes();
181
182
183-- UPDATE FORUM USERS
184create or replace function update_forum_users() RETURNS trigger AS
185$$
186begin
187 INSERT INTO forumusers (forumSlug, userNickname)
188 VALUES (NEW.forum, NEW.author)
189 ON CONFLICT DO NOTHING;
190 return NEW;
191end;
192$$ LANGUAGE plpgsql;
193
194drop trigger IF EXISTS trigger_update_forum_users ON threads;
195drop trigger IF EXISTS trigger_update_forum_users ON posts;
196
197create trigger trigger_update_forum_users
198 after insert
199 on threads
200 for each row
201EXECUTE procedure update_forum_users();
202create trigger trigger_update_forum_users
203 after insert
204 on posts
205 for each row
206EXECUTE procedure update_forum_users();
207
208
209-- ANALYZE
210CREATE OR REPLACE FUNCTION call_analyze() RETURNS TRIGGER AS
211$$
212BEGIN
213 IF NEW.id = 1500000 THEN
214 ANALYZE;
215 END IF;
216 return NEW;
217END;
218$$ LANGUAGE plpgsql;
219
220
221DROP TRIGGER IF EXISTS trigger_call_analyze ON posts;
222CREATE TRIGGER trigger_call_analyze
223 AFTER INSERT
224 ON posts
225 FOR EACH ROW
226EXECUTE PROCEDURE call_analyze();
227
228
229-- UPDATE IS EDITED FLAG ON POST IF MESSAGE UPDATED
230--create or replace function update_post_status() RETURNS trigger AS
231--$$
232--begin
233-- update posts set posts.isEdited= true WHERE id = NEW.id;
234-- return NEW;
235--end;
236--$$ LANGUAGE plpgsql;
237--
238--DROP trigger IF EXISTS trigger_update_post_status ON posts;
239--create trigger trigger_update_post_status
240-- after update of message
241-- on posts
242-- for each row
243--EXECUTE procedure update_post_status();