-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathpostgresql-setup.txt
400 lines (300 loc) · 16.9 KB
/
postgresql-setup.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
CREATE TABLE games (id character(5) NOT NULL, answer_cards integer[], question_cards integer[], q_cards_left integer, a_cards_left integer, czar_order integer[], current_czar integer, current_q_card integer, in_round boolean, waiting_for_answers boolean, mystery_player boolean, trade_in_cards boolean, num_cards_to_trade integer, pick_worst boolean, num_cards_in_hand integer, points_to_win integer, last_modified timestamp without time zone);
ALTER TABLE ONLY games ADD CONSTRAINT games_p_key PRIMARY KEY (id);
CREATE TABLE users (id integer NOT NULL, chat_id bigint NOT NULL, first_name character varying(32), last_name character varying(32), username character varying(32), points integer, cards_in_hand integer[], current_answer text, display_name character varying(64), waiting_for_response character varying(8), setting_status character varying(8));
ALTER TABLE ONLY users ADD CONSTRAINT users_p_key PRIMARY KEY (id);
ALTER TABLE ONLY games ADD CONSTRAINT games_current_czar_f_key FOREIGN KEY (current_czar) REFERENCES users(id);
CREATE TABLE players (game_id character(5) NOT NULL, user_id integer NOT NULL);
ALTER TABLE ONLY players ADD CONSTRAINT players_p_key PRIMARY KEY (game_id, user_id);
ALTER TABLE ONLY players ADD CONSTRAINT players_game_id_f_key FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE;
ALTER TABLE ONLY players ADD CONSTRAINT players_user_id_f_key FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
CREATE EXTENSION intarray;
CREATE OR REPLACE FUNCTION shuffle_answer_cards(game_id char(5)) RETURNS void AS $$
DECLARE arr integer[];
DECLARE tmp integer;
DECLARE rand integer;
BEGIN
SELECT answer_cards INTO arr FROM games WHERE games.id = game_id;
FOR i IN 1..icount(arr) LOOP
rand := floor(random() * icount(arr) + 1);
tmp := arr[i];
arr[i] := arr[rand];
arr[rand] := tmp;
END LOOP;
UPDATE games SET (answer_cards, a_cards_left) = (arr, icount(arr)) WHERE games.id = game_id;
END;
$$ LANGUAGE plpgsql VOLATILE;
CREATE OR REPLACE FUNCTION shuffle_question_cards(game_id char(5)) RETURNS void AS $$
DECLARE arr integer[];
DECLARE tmp integer;
DECLARE rand integer;
BEGIN
SELECT question_cards INTO arr FROM games WHERE games.id = game_id;
FOR i IN 1..icount(arr) LOOP
rand := floor(random() * icount(arr) + 1);
tmp := arr[i];
arr[i] := arr[rand];
arr[rand] := tmp;
END LOOP;
UPDATE games SET (question_cards, q_cards_left) = (arr, icount(arr)) WHERE games.id = game_id;
END;
$$ LANGUAGE plpgsql VOLATILE;
CREATE OR REPLACE FUNCTION add_cards_to_all_in_game(game_id char(5), num_cards integer) RETURNS void AS $$
DECLARE id players.user_id%TYPE;
BEGIN
FOR id IN SELECT players.user_id FROM players, games WHERE players.game_id = game_id AND games.id = game_id AND players.user_id != games.current_czar LOOP
PERFORM "add_cards_to_user_hand"(id, num_cards);
END LOOP;
END;
$$ LANGUAGE plpgsql VOLATILE;
CREATE OR REPLACE FUNCTION add_cards_to_user_hand(user_id integer, num_cards integer) RETURNS void AS $$
UPDATE users SET cards_in_hand = cards_in_hand + subarray(games.answer_cards, games.a_cards_left - num_cards + 1, num_cards) FROM games, players WHERE games.id = players.game_id AND players.user_id = users.id AND users.id = user_id;
UPDATE games SET a_cards_left = a_cards_left - num_cards FROM players WHERE games.id = players.game_id AND players.user_id = user_id;
$$ LANGUAGE SQL VOLATILE;
CREATE OR REPLACE FUNCTION add_game(game_id char(5), q_cards integer[], a_cards integer[], user_create_id integer) RETURNS void AS $$
BEGIN
INSERT INTO games(id, question_cards, answer_cards, q_cards_left, a_cards_left, czar_order, current_czar, current_q_card, waiting_for_answers, mystery_player, trade_in_cards, num_cards_to_trade, pick_worst, num_cards_in_hand, points_to_win, last_modified, in_round) VALUES(game_id, q_cards, a_cards, array_length(q_cards, 1), array_length(a_cards, 1), '{}', user_create_id, -1, false, false, false, 0, false, 7, 7, transaction_timestamp(), false);
PERFORM shuffle_answer_cards(game_id);
PERFORM shuffle_question_cards(game_id);
END;
$$ LANGUAGE plpgsql VOLATILE;
CREATE OR REPLACE FUNCTION add_player_to_game(game_id char(5), user_id integer) RETURNS void AS $$
DECLARE game_info record;
BEGIN
SELECT answer_cards, a_cards_left, num_cards_in_hand INTO game_info FROM games WHERE id = game_id;
IF game_info.a_cards_left < game_info.num_cards_in_hand THEN
PERFORM shuffle_answer_cards(game_id);
SELECT answer_cards, a_cards_left, num_cards_in_hand INTO game_info FROM games WHERE id = game_id;
END IF;
INSERT INTO players(game_id, user_id) VALUES(game_id, user_id);
UPDATE users SET cards_in_hand = subarray(game_info.answer_cards, game_info.a_cards_left - game_info.num_cards_in_hand + 1, game_info.num_cards_in_hand) WHERE users.id = user_id;
UPDATE games SET (czar_order, a_cards_left) = (czar_order + user_id, game_info.a_cards_left - game_info.num_cards_in_hand) WHERE games.id = game_id;
END;
$$ LANGUAGE plpgsql VOLATILE;
CREATE OR REPLACE FUNCTION add_user(user_id integer, chat_id bigint, first_name varchar(32), last_name varchar(32), username varchar(32), display_name varchar(64)) RETURNS void AS $$
INSERT INTO users (id, chat_id, first_name, last_name, username, display_name, points, cards_in_hand, current_answer, waiting_for_response, setting_status) VALUES(add_user.user_id, add_user.chat_id, add_user.first_name, add_user.last_name,add_user. username, add_user.display_name, 0, NULL, '', '', '');
$$ LANGUAGE SQL VOLATILE;
CREATE OR REPLACE FUNCTION change_pick_worst_setting(game_id char(5), change_to boolean) RETURNS VOID AS $$
UPDATE games SET pick_worst = change_to WHERE id = game_id;
$$ LANGUAGE SQL VOLATILE;
CREATE OR REPLACE FUNCTION check_game_exists(game_id char(5)) RETURNS boolean as $$
DECLARE id TEXT;
BEGIN
SELECT games.id INTO id FROM games WHERE games.id = game_id;
RETURN (id != '');
END;
$$ LANGUAGE plpgsql VOLATILE;
CREATE OR REPLACE FUNCTION clean_up_old_games() RETURNS TABLE(game_id char(5), user_id integer) AS $$
BEGIN
RETURN QUERY
SELECT players.game_id, players.user_id FROM players, games WHERE games.last_modified < NOW() - INTERVAL '1 MINUTE' AND games.id = players.game_id;
FOR game_id IN SELECT games.id FROM games WHERE games.last_modified < NOW() - INTERVAL '1 MINUTE' LOOP
PERFORM end_game(game_id);
END LOOP;
END;
$$ LANGUAGE plpgsql VOLATILE;
CREATE OR REPLACE FUNCTION czar_chose_answer(game_id char(5), answer text) RETURNS text[] AS $$
DECLARE ans record;
DECLARE info text[];
DECLARE czar_update text;
BEGIN
UPDATE users SET points = points + 1 WHERE current_answer = answer;
SELECT users.display_name, games.points_to_win, users.points, games.pick_worst INTO ans FROM games, users WHERE games.id = game_id AND users.current_answer = answer;
IF ans.pick_worst THEN
SELECT waiting_for_response INTO czar_update FROM users, games WHERE users.id = games.current_czar;
IF czar_update = 'czarBest' THEN
UPDATE users SET waiting_for_response = 'czarWorst' FROM games WHERE current_czar = users.id AND games.id = game_id;
END IF;
ELSE
UPDATE users SET waiting_for_response = '' FROM games WHERE current_czar = users.id AND games.id = game_id;
END IF;
info[1] := ans.display_name;
info[2] := (ans.points_to_win = ans.points)::text;
info[3] := ans.pick_worst::text;
RETURN info;
END;
$$ LANGUAGE plpgsql VOLATILE;
CREATE OR REPLACE FUNCTION czar_id(game_id char(5), status varchar(8)) RETURNS integer AS $$
DECLARE czar_id integer;
BEGIN
SELECT current_czar INTO czar_id FROM games WHERE id = game_id;
PERFORM update_user_status(users.id, status, '') FROM users WHERE users.id = czar_id;
RETURN czar_id;
END;
$$ LANGUAGE plpgsql VOLATILE;
CREATE OR REPLACE FUNCTION do_we_have_all_answers(game_id char(5)) RETURNS integer AS $$
DECLARE ans users.current_answer%TYPE;
BEGIN
FOR ans IN
SELECT users.current_answer FROM users, players, games WHERE users.id = players.user_id AND players.game_id = game_id AND games.id = game_id AND users.id != games.current_czar
LOOP
IF ans = '' THEN
RETURN 0;
END IF;
END LOOP;
RETURN 1;
END;
$$ LANGUAGE plpgsql VOLATILE;
CREATE OR REPLACE FUNCTION does_user_exist(user_id integer) RETURNS boolean as $$
DECLARE id int = -1;
BEGIN
SELECT users.id INTO id FROM users WHERE users.id = user_id;
RETURN (id != -1);
END;
$$ LANGUAGE plpgsql VOLATILE;
CREATE OR REPLACE FUNCTION end_game(game_id char(5)) RETURNS TABLE(name varchar(64), points text) AS $$
BEGIN
RETURN QUERY
SELECT users.display_name, users.points::text FROM players, games, users WHERE games.id = end_game.game_id AND games.id = players.game_id AND players.user_id = users.id;
UPDATE users SET (cards_in_hand, waiting_for_response, current_answer, points) = ('{}', '', '', 0) FROM players WHERE players.game_id = end_game.game_id;
DELETE FROM games WHERE games.id = end_game.game_id;
END;
$$ LANGUAGE plpgsql VOLATILE;
CREATE OR REPLACE FUNCTION end_round(game_id char(5)) RETURNS void AS $$
UPDATE games SET (current_q_card, current_czar, waiting_for_answers, in_round) = (-1, czar_order[((idx(czar_order, current_czar) + icount(czar_order)) % (icount(czar_order))) + 1], false, false) WHERE games.id = game_id;
UPDATE users SET current_answer = '' FROM players WHERE players.game_id = game_id AND players.user_id = users.id;
$$ LANGUAGE SQL VOLATILE;
CREATE OR REPLACE FUNCTION game_settings(game_id char(5)) RETURNS text[] AS $$
DECLARE settings text[];
DECLARE ans record;
BEGIN
SELECT mystery_player, trade_in_cards, num_cards_to_trade, pick_worst, num_cards_in_hand, points_to_win INTO ans FROM games WHERE games.id = game_id;
settings[1] := 'Mystery player enabled: ' || ans.mystery_player::text;
settings[2] := 'Trade in cards after every round: ' || ans.trade_in_cards::text;
settings[3] := 'Number of cards to trade in: ' || ans.num_cards_to_trade::text;
settings[4] := 'Pick the worst answer also: ' || ans.pick_worst::text;
settings[5] := 'Number of cards in each players hand: ' || ans.num_cards_in_hand::text;
settings[6] := 'Number of points needed to win: ' || ans.points_to_win::text;
RETURN settings;
END;
$$ LANGUAGE plpgsql VOLATILE;
CREATE OR REPLACE FUNCTION get_answers(game_id char(5)) RETURNS text[] AS $$
DECLARE answers text[];
DECLARE num_answers int = 1;
DECLARE ans record;
BEGIN
FOR ans IN SELECT current_answer FROM users, players, games WHERE players.game_id = game_id AND players.user_id = users.id AND games.id = game_id AND games.current_czar != players.user_id LOOP
answers[num_answers] := ans.current_answer || '+=+';
num_answers := num_answers + 1;
END LOOP;
RETURN answers;
END;
$$ LANGUAGE plpgsql VOLATILE;
CREATE OR REPLACE FUNCTION get_current_answer(user_id integer) RETURNS text AS $$
SELECT current_answer FROM users WHERE users.id = user_id;
$$ LANGUAGE SQL VOLATILE;
CREATE OR REPLACE FUNCTION get_display_name(user_id integer) RETURNS text AS $$
SELECT display_name FROM users WHERE id = user_id;
$$ LANGUAGE SQL VOLATILE;
CREATE OR REPLACE FUNCTION get_game_id(user_id integer, chat_id bigint) RETURNS character(5) AS $$
DECLARE c_id bigint;
DECLARE g_id character(5);
BEGIN
SELECT users.chat_id INTO c_id FROM users WHERE users.id = get_game_id.user_id;
IF c_id != chat_id THEN
UPDATE users SET users.chat_id = chat_id WHERE users.id = user_id;
END IF;
SELECT players.game_id INTO g_id FROM players, users WHERE players.user_id = get_game_id.user_id AND players.user_id = users.id;
RETURN g_id;
END;
$$ LANGUAGE plpgsql VOLATILE;
CREATE OR REPLACE FUNCTION get_player_scores(game_id char(5)) RETURNS TABLE(display_name varchar(64), points text) AS $$
SELECT users.display_name, users.points::text FROM users, players WHERE players.game_id = game_id AND players.user_id = users.id;
$$ LANGUAGE SQL VOLATILE;
CREATE OR REPLACE FUNCTION get_question_card(game_id char(5)) RETURNS integer AS $$
SELECT current_q_card FROM games WHERE games.id = game_id;
$$ LANGUAGE SQL VOLATILE;
CREATE OR REPLACE FUNCTION get_user_cards(user_id integer) RETURNS integer[] AS $$
SELECT cards_in_hand FROM users WHERE id = user_id;
$$ LANGUAGE SQL VOLATILE;
CREATE OR REPLACE FUNCTION get_user_setting_status(user_id integer) RETURNS varchar(8) as $$
SELECT setting_status FROM users WHERE id = user_id;
$$ LANGUAGE SQL VOLATILE;
CREATE OR REPLACE FUNCTION get_user_status(user_id integer) RETURNS text as $$
SELECT waiting_for_response FROM users WHERE id = user_id;
$$ LANGUAGE SQL VOLATILE;
CREATE OR REPLACE FUNCTION get_chat_ids_for_game(game_id char(5)) RETURNS SETOF bigint
AS $$
SELECT users.chat_id FROM players, users WHERE players.game_id = game_id AND users.id = players.user_id;
$$ LANGUAGE SQL VOLATILE;
CREATE OR REPLACE FUNCTION get_user_ids_for_game(game_id char(5)) RETURNS SETOF integer
AS $$
SELECT players.user_id FROM players, users WHERE players.game_id = game_id AND users.id = players.user_id;
$$ LANGUAGE SQL VOLATILE;
CREATE OR REPLACE FUNCTION get_user_ids_we_need_answer(game_id char(5)) RETURNS SETOF integer AS $$
UPDATE users SET waiting_for_response = 'answer' FROM players, games WHERE users.id = players.user_id AND players.game_id = game_id AND users.current_answer = '' AND users.id != games.current_czar AND games.id = game_id;
SELECT players.user_id FROM players, users WHERE players.game_id = game_id AND users.id = players.user_id AND users.waiting_for_response = 'answer';
$$ LANGUAGE SQL VOLATILE;
CREATE OR REPLACE FUNCTION is_player_in_game(user_id integer, game_id char(5)) RETURNS boolean AS $$
DECLARE num int;
BEGIN
SELECT COUNT(*) INTO num FROM players, games WHERE players.game_id = is_player_in_game.game_id AND players.user_id = is_player_in_game.user_id;
RETURN (num != 0);
END;
$$ LANGUAGE plpgsql VOLATILE;
CREATE OR REPLACE FUNCTION is_game_in_round(game_id char(5)) RETURNS boolean AS $$
SELECT in_round FROM games WHERE id = game_id;
$$ LANGUAGE SQL VOLATILE;
CREATE OR REPLACE FUNCTION num_players_in_game(game_id char(5)) RETURNS bigint AS $$
SELECT COUNT(*) FROM players WHERE players.game_id = game_id;
$$ LANGUAGE SQL VOLATILE;
CREATE OR REPLACE FUNCTION received_answer_from_user(user_id integer, answer_index integer, answer text, finished boolean) RETURNS void AS $$
DECLARE user_cards integer[];
BEGIN
SELECT cards_in_hand INTO user_cards FROM users WHERE users.id = user_id;
UPDATE users SET (cards_in_hand, current_answer) = (user_cards - answer_index, answer) WHERE users.id = user_id;
IF finished THEN
PERFORM "add_cards_to_user_hand"(user_id, 1);
UPDATE users SET waiting_for_response = '' WHERE users.id = user_id;
END IF;
END;
$$ LANGUAGE plpgsql VOLATILE;
CREATE OR REPLACE FUNCTION remove_player_from_game(user_id integer) RETURNS TABLE(name varchar(64), points text) AS $$
DECLARE czar_array int[];
DECLARE czar int;
BEGIN
RETURN QUERY
SELECT users.display_name, users.points::text FROM players, games, users WHERE games.id = players.game_id AND players.user_id = remove_player_from_game.user_id;
SELECT czar_order, current_czar INTO czar_array, czar FROM games, players WHERE games.id = players.game_id AND players.user_id = remove_player_from_game.user_id;
IF czar = user_id THEN
czar := czar_array[idx(czar_array, user_id) + 1];
END IF;
czar_array := czar_array - user_id;
UPDATE games SET (current_czar, czar_order) = (czar, czar_array) FROM players WHERE games.id = players.game_id AND players.user_id = remove_player_from_game.user_id;
UPDATE users SET (cards_in_hand, waiting_for_response, current_answer) = ('{}', '', '') FROM players WHERE players.user_id = remove_player_from_game.user_id;
DELETE FROM players WHERE players.user_id = remove_player_from_game.user_id;
END;
$$ LANGUAGE plpgsql VOLATILE;
CREATE OR REPLACE FUNCTION remove_user(user_id integer) RETURNS void AS $$
DELETE FROM users WHERE id = user_id;
$$ LANGUAGE SQL VOLATILE;
CREATE OR REPLACE FUNCTION start_round(game_id char(5)) RETURNS SETOF integer AS $$
DECLARE status users%ROWTYPE;
BEGIN
FOR status IN SELECT users.* FROM users, players WHERE players.user_id = users.id AND players.game_id = game_id LOOP
IF status.waiting_for_response != '' THEN
RETURN NEXT -1;
RETURN NEXT status.id;
RETURN;
END IF;
END LOOP;
UPDATE games SET (current_q_card, q_cards_left, waiting_for_answers, in_round) = (question_cards[q_cards_left], q_cards_left - 1, true, true) WHERE games.id = game_id;
RETURN QUERY SELECT "get_user_ids_we_need_answer"(game_id);
END;
$$ LANGUAGE plpgsql VOLATILE;
CREATE OR REPLACE FUNCTION update_timestamp() RETURNS trigger AS $$
BEGIN
NEW.last_modified := transaction_timestamp();
RETURN NEW;
END;
$$ LANGUAGE plpgsql VOLATILE;
CREATE TRIGGER update_timestamp BEFORE INSERT OR UPDATE ON games
FOR EACH ROW EXECUTE PROCEDURE update_timestamp();
CREATE OR REPLACE FUNCTION waiting_for_answers(game_id char(5)) RETURNS boolean AS $$
SELECT waiting_for_answers FROM games WHERE id = game_id;
$$ LANGUAGE SQL VOLATILE;
CREATE OR REPLACE FUNCTION who_is_czar(game_id char(5)) RETURNS text AS $$
SELECT users.display_name FROM players, games, users WHERE games.id = game_id AND players.game_id = games.id AND players.user_id = games.current_czar AND users.id = players.user_id;
$$ LANGUAGE SQL VOLATILE;
CREATE OR REPLACE FUNCTION update_user_status(user_id integer, response_status varchar(8), user_setting_status varchar(8)) RETURNS void AS $$
UPDATE users SET (waiting_for_response, setting_status) = (response_status, user_setting_status) WHERE users.id = user_id;
$$ LANGUAGE SQL VOLATILE;