DatabaseProgramming: functions_procedures_triggers.sql

File functions_procedures_triggers.sql, 12.8 KB (added by 231058, 5 days ago)
Line 
1-- 1. accept_or_reject_friendship
2CREATE OR REPLACE PROCEDURE public.accept_or_reject_friendship(IN p_responder_id bigint, IN p_requester_id bigint, IN p_action text)
3 LANGUAGE plpgsql
4AS $procedure$
5DECLARE
6 v_low BIGINT;
7 v_high BIGINT;
8 v_status_id BIGINT;
9 v_status_name VARCHAR(50);
10 v_req_by BIGINT;
11 v_action_status_id BIGINT;
12 v_type_id BIGINT;
13BEGIN
14 v_low := LEAST(p_responder_id, p_requester_id);
15 v_high := GREATEST(p_responder_id, p_requester_id);
16
17 SELECT f.status_id, fs.name, f.requested_by_user_id
18 INTO v_status_id, v_status_name, v_req_by
19 FROM friendships f
20 JOIN friendship_statuses fs ON fs.id = f.status_id
21 WHERE f.user_id_low = v_low
22 AND f.user_id_high = v_high;
23
24 IF NOT FOUND THEN
25 RAISE EXCEPTION 'There is no friend request between these users.';
26 END IF;
27
28 IF v_req_by = p_responder_id THEN
29 RAISE EXCEPTION 'Can not respond to your own request.';
30 END IF;
31
32 IF p_action IN ('accept', 'reject') AND v_status_name <> 'pending' THEN
33 RAISE EXCEPTION 'Only pending friend requests can be accepted or rejected.';
34 END IF;
35
36 IF p_action = 'accept' THEN
37 SELECT id INTO v_action_status_id FROM friendship_statuses WHERE name = 'accepted';
38 SELECT id INTO v_type_id FROM notification_types WHERE name = 'friend_request';
39
40 UPDATE friendships
41 SET status_id = v_action_status_id,
42 updated_at = CURRENT_TIMESTAMP
43 WHERE user_id_low = v_low
44 AND user_id_high = v_high;
45
46 INSERT INTO notifications (id, user_id, type_id, title, message)
47 VALUES (
48 coalesce((select max(id) from notifications), 0) + 1,
49 p_requester_id,
50 v_type_id,
51 'Friend request accepted!',
52 'User ' || p_responder_id || ' accepted your friend request.'
53 );
54 RAISE NOTICE 'Friend request accepted.';
55
56 ELSIF p_action = 'reject' THEN
57 DELETE FROM friendships
58 WHERE user_id_low = v_low
59 AND user_id_high = v_high;
60 RAISE NOTICE 'Friend request rejected.';
61
62 ELSIF p_action = 'block' THEN
63 SELECT id INTO v_action_status_id FROM friendship_statuses WHERE name = 'blocked';
64
65 UPDATE friendships
66 SET status_id = v_action_status_id,
67 updated_at = CURRENT_TIMESTAMP
68 WHERE user_id_low = v_low
69 AND user_id_high = v_high;
70 RAISE NOTICE 'The user is blocked.';
71
72 ELSE
73 RAISE EXCEPTION 'Invalid action: %', p_action;
74 END IF;
75END;
76$procedure$
77;
78
79-- 2. add_match_participants
80CREATE OR REPLACE PROCEDURE public.add_match_participants(IN p_match_id bigint, IN p_user_ids bigint[], IN p_team_ids bigint[])
81 LANGUAGE plpgsql
82AS $procedure$
83DECLARE
84 v_ongoing_id BIGINT;
85 v_started_at TIMESTAMP;
86BEGIN
87 SELECT id INTO v_ongoing_id FROM match_statuses WHERE name = 'in_progress';
88
89 SELECT started_at INTO v_started_at
90 FROM matches
91 WHERE id = p_match_id AND status_id = v_ongoing_id;
92
93 IF v_started_at IS NULL THEN
94 RAISE EXCEPTION 'Match % is not active, does not exist, or status mismatch.', p_match_id;
95 END IF;
96
97 IF cardinality(p_user_ids) != cardinality(p_team_ids) THEN
98 RAISE EXCEPTION 'The number of user IDs must match the number of team IDs.';
99 END IF;
100
101 INSERT INTO match_teams (match_id, match_started_at, team_id, side_label)
102 SELECT DISTINCT p_match_id, v_started_at, t_id, 'Team_' || t_id
103 FROM unnest(p_team_ids) AS t_id
104 ON CONFLICT DO NOTHING;
105
106 INSERT INTO match_participants (id, match_id, match_started_at, user_id, team_id)
107 SELECT
108 coalesce((select max(id) from match_participants), 0) + row_number() OVER (),
109 p_match_id,
110 v_started_at,
111 arr.u_id,
112 arr.t_id
113 FROM unnest(p_user_ids, p_team_ids) AS arr(u_id, t_id);
114
115 RAISE NOTICE 'Participants successfully added to match %.', p_match_id;
116END;
117$procedure$
118;
119
120-- 3. auto_create_profile
121CREATE OR REPLACE FUNCTION public.auto_create_profile_fn()
122 RETURNS trigger
123 LANGUAGE plpgsql
124AS $function$
125BEGIN
126 INSERT INTO profiles (user_id, avatar_url, bio, rank_points)
127 VALUES (NEW.id, NULL, NULL, 1000);
128 RETURN NEW;
129END;
130$function$
131;
132
133CREATE OR REPLACE TRIGGER auto_create_profile
134AFTER INSERT ON users
135FOR EACH ROW
136EXECUTE FUNCTION auto_create_profile_fn();
137
138-- 4. create_match
139CREATE OR REPLACE PROCEDURE public.create_match(
140 IN p_match_id bigint,
141 IN p_game_id bigint,
142 IN p_game_mode_id bigint,
143 IN p_tournament_id bigint,
144 IN p_server_id bigint,
145 IN p_participants jsonb,
146 OUT p_started_at timestamp
147)
148 LANGUAGE plpgsql
149AS $procedure$
150DECLARE
151 v_status_id BIGINT;
152BEGIN
153 IF NOT EXISTS (SELECT 1 FROM servers WHERE id = p_server_id AND is_active = TRUE) THEN
154 RAISE EXCEPTION 'Server % is not active or does not exist.', p_server_id;
155 END IF;
156
157 IF NOT EXISTS (SELECT 1 FROM tournaments WHERE id = p_tournament_id AND game_id = p_game_id) THEN
158 RAISE EXCEPTION 'Tournament % is not connected to game %.', p_tournament_id, p_game_id;
159 END IF;
160
161 SELECT id INTO v_status_id FROM match_statuses WHERE name = 'in_progress';
162 p_started_at := CURRENT_TIMESTAMP;
163
164 INSERT INTO matches (id, game_id, game_mode_id, tournament_id, server_id, status_id, started_at)
165 VALUES (p_match_id, p_game_id, p_game_mode_id, p_tournament_id, p_server_id, v_status_id, p_started_at);
166
167 RAISE NOTICE 'The match % is created in partition window %.', p_match_id, p_started_at;
168END;
169$procedure$
170;
171
172-- 5. notify_on_achievement
173CREATE OR REPLACE FUNCTION public.notify_on_achievement_fn()
174 RETURNS trigger
175 LANGUAGE plpgsql
176AS $function$
177DECLARE
178 a_title VARCHAR(100);
179 a_game_title VARCHAR(100);
180 v_type_id BIGINT;
181BEGIN
182 SELECT a.title, g.title
183 INTO a_title, a_game_title
184 FROM achievements a
185 JOIN games g ON g.id = a.game_id
186 WHERE a.id = NEW.achievement_id;
187
188 SELECT id INTO v_type_id FROM notification_types WHERE name = 'achievement';
189
190 INSERT INTO notifications (id, user_id, type_id, title, message, is_read)
191 VALUES (
192 coalesce((select max(id) from notifications), 0) + 1,
193 NEW.user_id,
194 v_type_id,
195 'New Achievement!',
196 'You got the achievement "' || a_title || '" in the game ' || a_game_title || '.',
197 FALSE
198 );
199 RETURN NEW;
200END;
201$function$
202;
203
204CREATE OR REPLACE TRIGGER notify_on_achievement
205AFTER INSERT ON user_achievements
206FOR EACH ROW
207EXECUTE FUNCTION notify_on_achievement_fn();
208
209-- 6. register_match_result
210CREATE OR REPLACE FUNCTION public.register_match_result(r_match_id bigint, r_participant_ids bigint[], r_results text[], r_scores integer[], r_elo_changes integer[])
211 RETURNS text
212 LANGUAGE plpgsql
213AS $function$
214DECLARE
215 r_status_name text;
216 v_started_at TIMESTAMP;
217 v_comp_id BIGINT;
218BEGIN
219 IF cardinality(r_participant_ids) != cardinality(r_results) OR
220 cardinality(r_participant_ids) != cardinality(r_scores) OR
221 cardinality(r_participant_ids) != cardinality(r_elo_changes) THEN
222 RETURN 'Error: Input arrays must have matching dimensions.';
223 END IF;
224
225 SELECT ms.name, m.started_at INTO r_status_name, v_started_at
226 FROM matches m
227 JOIN match_statuses ms ON ms.id = m.status_id
228 WHERE m.id = r_match_id;
229
230 IF v_started_at IS NULL THEN
231 RETURN 'Error: Match does not exist.';
232 END IF;
233
234 IF r_status_name NOT IN ('in_progress', 'scheduled') THEN
235 RETURN 'Error: The match is over or canceled.';
236 END IF;
237
238 UPDATE match_participants AS mp
239 SET result_id = (SELECT id FROM participant_results WHERE name = arr.res),
240 score = arr.scr,
241 elo_change = arr.elo
242 FROM unnest(r_participant_ids, r_results, r_scores, r_elo_changes) AS arr(part_id, res, scr, elo)
243 WHERE mp.id = arr.part_id
244 AND mp.match_id = r_match_id;
245
246 SELECT id INTO v_comp_id FROM match_statuses WHERE name = 'completed';
247
248 UPDATE matches
249 SET status_id = v_comp_id,
250 ended_at = CURRENT_TIMESTAMP
251 WHERE id = r_match_id
252 AND started_at = v_started_at;
253
254 RETURN 'OK: Match is over.';
255END;
256$function$
257;
258
259-- 7. register_user
260CREATE OR REPLACE PROCEDURE public.register_user(IN p_id bigint, IN p_username character varying, IN p_email character varying, IN p_password_hash text, IN p_location_id bigint DEFAULT 0)
261 LANGUAGE plpgsql
262AS $procedure$
263BEGIN
264 IF char_length(p_username) < 3 OR char_length(p_username) > 30 THEN
265 RAISE EXCEPTION 'Username must have between 3 and 30 characters.';
266 END IF;
267
268 IF EXISTS (SELECT 1 FROM users WHERE username = p_username) THEN
269 RAISE EXCEPTION 'This username is taken.';
270 END IF;
271
272 INSERT INTO users (id, username, email, password_hash, location_id)
273 VALUES (p_id, p_username, p_email, p_password_hash, p_location_id);
274 RAISE NOTICE 'User % is successfully registered.', p_username;
275END;
276$procedure$
277;
278
279-- 8. send_friend_request
280CREATE OR REPLACE FUNCTION public.send_friend_request(requester_id bigint, target_id bigint)
281 RETURNS text
282 LANGUAGE plpgsql
283AS $function$
284DECLARE
285 low BIGINT;
286 high BIGINT;
287 v_status_id BIGINT;
288BEGIN
289 IF requester_id = target_id THEN
290 RETURN 'Error: Can not send request to self.';
291 END IF;
292
293 low := LEAST(requester_id, target_id);
294 high := GREATEST(requester_id, target_id);
295
296 IF EXISTS (
297 SELECT 1 FROM friendships
298 WHERE user_id_low = low AND user_id_high = high
299 ) THEN
300 IF EXISTS (
301 SELECT 1 FROM friendships f
302 JOIN friendship_statuses fs ON fs.id = f.status_id
303 WHERE f.user_id_low = low AND f.user_id_high = high AND fs.name = 'blocked'
304 ) THEN
305 RETURN 'Error: The user is blocked.';
306 END IF;
307
308 RETURN 'Error: The request exists or the users are already friends.';
309 END IF;
310
311 SELECT id INTO v_status_id FROM friendship_statuses WHERE name = 'pending';
312
313 INSERT INTO friendships (id, user_id_low, user_id_high, requested_by_user_id, status_id)
314 VALUES (coalesce((select max(id) from friendships), 0) + 1, low, high, requester_id, v_status_id);
315
316 RETURN 'OK: The friend request is sent.';
317END;
318$function$
319;
320
321-- 9. subscribe_user
322CREATE OR REPLACE PROCEDURE public.subscribe_user(IN p_subscription_id bigint, IN p_user_id bigint, IN p_plan_name text, IN p_payment_id bigint, IN p_amount numeric, IN p_currency character DEFAULT 'EUR'::bpchar)
323 LANGUAGE plpgsql
324AS $procedure$
325DECLARE
326 subs_end_at TIMESTAMP;
327 v_plan_id BIGINT;
328BEGIN
329 subs_end_at := CASE p_plan_name
330 WHEN 'weekly' THEN CURRENT_TIMESTAMP + INTERVAL '7 days'
331 WHEN 'monthly' THEN CURRENT_TIMESTAMP + INTERVAL '30 days'
332 ELSE CURRENT_TIMESTAMP + INTERVAL '30 days'
333 END;
334
335 SELECT id INTO v_plan_id FROM subscription_plans WHERE name = p_plan_name;
336 IF NOT FOUND THEN
337 RAISE EXCEPTION 'Subscription plan % does not exist.', p_plan_name;
338 END IF;
339
340 UPDATE subscriptions
341 SET is_active = FALSE,
342 end_at = CURRENT_TIMESTAMP
343 WHERE user_id = p_user_id
344 AND is_active = TRUE;
345
346 INSERT INTO subscriptions (id, user_id, plan_id, start_at, end_at, is_active)
347 VALUES (p_subscription_id, p_user_id, v_plan_id, CURRENT_TIMESTAMP, subs_end_at, TRUE);
348
349 INSERT INTO payments (id, user_id, subscription_id, amount, currency_code, paid_at)
350 VALUES (p_payment_id, p_user_id, p_subscription_id, p_amount, p_currency, CURRENT_TIMESTAMP);
351
352 RAISE NOTICE 'Subscription % for user % is activated till %', p_plan_name, p_user_id, subs_end_at;
353END;
354$procedure$
355;
356
357-- 10. unsubscribe_user
358CREATE OR REPLACE PROCEDURE public.unsubscribe_user(IN p_user_id bigint)
359 LANGUAGE plpgsql
360AS $procedure$
361BEGIN
362 UPDATE subscriptions
363 SET is_active = FALSE,
364 end_at = CURRENT_TIMESTAMP
365 WHERE user_id = p_user_id
366 AND is_active = TRUE;
367
368 IF NOT FOUND THEN
369 RAISE EXCEPTION 'User % does not have an active subscription.', p_user_id;
370 ELSE
371 RAISE NOTICE 'Subscription for user % has been successfully canceled.', p_user_id;
372 END IF;
373END;
374$procedure$
375;
376
377-- 11. update_rank
378CREATE OR REPLACE FUNCTION public.update_rank()
379 RETURNS trigger
380 LANGUAGE plpgsql
381AS $function$
382BEGIN
383 IF OLD.result_id IS NULL AND NEW.result_id IS NOT NULL THEN
384 UPDATE profiles
385 SET rank_points = GREATEST(0, rank_points + NEW.elo_change)
386 WHERE user_id = NEW.user_id;
387 END IF;
388 RETURN NEW;
389END;
390$function$
391;
392
393CREATE OR REPLACE TRIGGER update_rank_after_match
394AFTER UPDATE OF result_id ON match_participants
395FOR EACH ROW
396EXECUTE FUNCTION public.update_rank();