dml-utils.sql (2991B)
1 -- countdown-bot utility functions and procedures 2 3 DROP PROCEDURE IF EXISTS isCountdown; 4 DROP PROCEDURE IF EXISTS getUserContextCountdown; 5 DROP PROCEDURE IF EXISTS getServerContextCountdown; 6 DROP FUNCTION IF EXISTS getServerPrefixes; 7 8 -- Get the active prefixes for a server 9 CREATE FUNCTION getServerPrefixes ( 10 _serverID BIGINT, -- The server ID 11 channelID BIGINT -- The channel ID 12 ) 13 RETURNS TABLE ( 14 prefix VARCHAR(8) -- An active prefix 15 ) 16 LANGUAGE plpgsql AS $$ 17 BEGIN 18 IF EXISTS( 19 SELECT 1 20 FROM countdowns 21 WHERE countdownID = channelID 22 ) THEN 23 -- Filter prefixes if channel is a countdown 24 RETURN QUERY 25 SELECT value 26 FROM prefixes 27 WHERE prefixes.countdownID = channelID; 28 ELSE 29 -- Return all server prefixes if channel is not a countdown 30 RETURN QUERY 31 SELECT DISTINCT value 32 FROM prefixes 33 JOIN countdowns ON countdowns.countdownID = prefixes.countdownID 34 WHERE countdowns.serverID = _serverID; 35 END IF; 36 END 37 $$; 38 39 -- Get the most relevant countdown to a server channel 40 CREATE PROCEDURE getServerContextCountdown ( 41 _serverID IN BIGINT, -- The server ID 42 channelID IN BIGINT, -- The channel ID 43 prefix IN VARCHAR(8), -- The prefix used to invoke the bot 44 countdownID OUT BIGINT -- The ID of the most relevant countdown 45 ) 46 LANGUAGE plpgsql AS $$ 47 BEGIN 48 -- Check if the channel is a countdown 49 SELECT countdowns.countdownID 50 INTO countdownID 51 FROM countdowns 52 WHERE countdowns.countdownID = channelID 53 54 UNION ALL 55 ( 56 -- Get server countdowns by prefix sorted by most recent activity 57 SELECT countdowns.countdownID 58 FROM countdowns 59 LEFT OUTER JOIN messages 60 ON messages.countdownID = countdowns.countdownID 61 JOIN prefixes 62 ON prefixes.countdownID = countdowns.countdownID 63 WHERE serverID = _serverID AND prefixes.value = prefix 64 GROUP BY countdowns.countdownID 65 ORDER BY max(messages.timestamp) DESC NULLS LAST 66 ) 67 LIMIT 1; 68 END 69 $$; 70 71 -- Get the most relevant countdown to a user 72 CREATE PROCEDURE getUserContextCountdown ( 73 _userID IN BIGINT, -- The user ID 74 countdownID OUT BIGINT -- The ID of the most relevant countdown 75 ) 76 LANGUAGE plpgsql AS $$ 77 BEGIN 78 -- Get user countdowns sorted by most recent activity 79 SELECT countdowns.countdownID 80 INTO countdownID 81 FROM countdowns 82 LEFT OUTER JOIN messages ON messages.countdownID = countdowns.countdownID 83 WHERE userID = _userID 84 GROUP BY countdowns.countdownID 85 ORDER BY max(messages.timestamp) DESC NULLS LAST 86 LIMIT 1; 87 END 88 $$; 89 90 -- Determine if a channel is a countdown 91 CREATE PROCEDURE isCountdown ( 92 channelID IN BIGINT, -- The channel ID 93 result OUT BOOLEAN -- Whether the channel is a countdown 94 ) 95 LANGUAGE plpgsql AS $$ 96 BEGIN 97 SELECT EXISTS( 98 SELECT 1 99 FROM countdowns 100 WHERE countdownID = channelID 101 ) INTO result; 102 END 103 $$;