commit 6dcdbfa0671ebae27e65523fb2c3c91685765773
parent 6a5dfb367dde0d98e314ae5358684f90bc360d56
Author: Asher Morgan <59518073+ashermorgan@users.noreply.github.com>
Date: Sun, 21 Apr 2024 19:48:22 -0700
Implement reactions table
Diffstat:
2 files changed, 119 insertions(+), 64 deletions(-)
diff --git a/models/tables.sql b/models/tables.sql
@@ -1,5 +1,6 @@
-- countdown-bot tabe definitions
+DROP TABLE IF EXISTS reactions;
DROP TABLE IF EXISTS prefixes;
DROP TABLE IF EXISTS messages;
DROP TABLE IF EXISTS countdowns;
@@ -15,15 +16,24 @@ CREATE TABLE messages (
messageID BIGINT PRIMARY KEY, -- The Discord message ID
countdownID BIGINT NOT NULL, -- The countdown ID
userID BIGINT NOT NULL, -- The author's Discord user ID
- value INT NOT NULL, -- The message's numeric value
- timestamp TIMESTAMPTZ NOT NULL, -- The message timestamp
+ value INT NOT NULL, -- The message's numeric value
+ timestamp TIMESTAMPTZ NOT NULL, -- The message timestamp
FOREIGN KEY (countdownID) REFERENCES countdowns(countdownID)
);
-- Records bot command prefixes
CREATE table prefixes (
prefixID SERIAL PRIMARY KEY, -- The prefix ID
- countdownID BIGINT NOT NULL, -- The countdown ID
+ countdownID BIGINT NOT NULL, -- The countdown ID
value VARCHAR(8) NOT NULL, -- The prefix
FOREIGN KEY (countdownID) REFERENCES countdowns(countdownID)
);
+
+-- Records custom countdown reactions
+CREATE table reactions (
+ prefixID SERIAL PRIMARY KEY, -- The reaction ID
+ countdownID BIGINT NOT NULL, -- The countdown ID
+ number INT NOT NULL, -- The prefix
+ value CHAR NOT NULL, -- The reaction
+ FOREIGN KEY (countdownID) REFERENCES countdowns(countdownID)
+);
diff --git a/models/utilities.sql b/models/utilities.sql
@@ -1,72 +1,16 @@
-- countdown-bot utility procedures
+DROP FUNCTION IF EXISTS getReactions;
+DROP PROCEDURE IF EXISTS addMessage;
+DROP TYPE IF EXISTS addMessageResults;
DROP PROCEDURE IF EXISTS getUserContextCountdown;
DROP PROCEDURE IF EXISTS getServerContextCountdown;
DROP FUNCTION IF EXISTS getPrefixes;
-DROP PROCEDURE IF EXISTS addMessage;
-DROP TYPE IF EXISTS addMessageResults;
-
--- Possible results of the addMessage procedure
-CREATE TYPE addMessageResults AS ENUM (
- 'badCountdown', -- Countdown doesn't exist or has ended
- 'badNumber', -- Message number is incorrect
- 'badUser', -- User sent consecutive messages
- 'good' -- Message was successfully added
-);
-
--- Validate and add a new countdown message
-CREATE PROCEDURE addMessage (
- _messageID IN BIGINT, -- The message ID
- _countdownID IN BIGINT, -- The message countdown ID
- _userID IN BIGINT, -- The message user ID
- _value IN INT, -- The message value
- _timestamp IN TIMESTAMPTZ, -- The message timestamp
- result OUT addMessageResults -- The operation result
-)
-LANGUAGE plpgsql AS $$
-DECLARE
- lastMessage record;
-BEGIN
- -- Get last countdown message
- SELECT countdowns.countdownID, messageID, userID, value, timestamp
- INTO lastMessage
- FROM countdowns
-
- -- Still return a row if the countdown is empty
- LEFT OUTER JOIN messages
- ON messages.countdownID = countdowns.countdownID
-
- WHERE countdowns.countdownID = _countdownID
- ORDER BY messages.value ASC
- LIMIT 1;
-
- -- Validate message
- IF lastMessage.countdownID IS NULL OR lastMessage.value = 0 THEN
- -- Countdown doesn't exist or has ended
- result := 'badCountdown';
-
- ELSEIF lastMessage.value IS NOT NULL AND
- lastMessage.value != _value + 1 THEN
- -- Message contains the wrong number
- result := 'badNumber';
-
- ELSEIF lastMessage.userID = _userID THEN
- -- User sent consecutive messages
- result := 'badUser';
-
- ELSE
- -- Message is valid, insert it into messages
- INSERT INTO messages (messageID, userID, countdownID, value, timestamp)
- VALUES (_messageID, _userID, _countdownID, _value, _timestamp);
- result := 'good';
- END IF;
-END
-$$;
-- Get the active prefixes for a server
CREATE FUNCTION getPrefixes (
- _serverID IN BIGINT, -- The server ID
- channelID IN BIGINT -- The channel ID
+ _serverID BIGINT, -- The server ID
+ channelID BIGINT -- The channel ID
)
RETURNS TABLE (
prefix VARCHAR(8) -- An active prefix
@@ -144,3 +88,104 @@ BEGIN
LIMIT 1;
END
$$;
+
+-- Possible results of the addMessage procedure
+CREATE TYPE addMessageResults AS ENUM (
+ 'badCountdown', -- Countdown doesn't exist or has ended
+ 'badNumber', -- Message number is incorrect
+ 'badUser', -- User sent consecutive messages
+ 'good' -- Message was successfully added
+);
+
+-- Validate and add a new countdown message
+CREATE PROCEDURE addMessage (
+ _messageID IN BIGINT, -- The message ID
+ _countdownID IN BIGINT, -- The message countdown ID
+ _userID IN BIGINT, -- The message user ID
+ _value IN INT, -- The message value
+ _timestamp IN TIMESTAMPTZ, -- The message timestamp
+ result OUT addMessageResults, -- The operation result
+ pin OUT BOOLEAN, -- Whether the message should be pinned
+ reactions OUT BOOLEAN -- Whether the message has custom reactions
+)
+LANGUAGE plpgsql AS $$
+DECLARE
+ lastMessage record;
+ total INT;
+BEGIN
+ -- Get last countdown message
+ SELECT countdowns.countdownID, messageID, userID, value, timestamp
+ INTO lastMessage
+ FROM countdowns
+
+ -- Still return a row if the countdown is empty
+ LEFT OUTER JOIN messages
+ ON messages.countdownID = countdowns.countdownID
+
+ WHERE countdowns.countdownID = _countdownID
+ ORDER BY messages.value ASC
+ LIMIT 1;
+
+ -- Validate message
+ IF lastMessage.countdownID IS NULL OR lastMessage.value = 0 THEN
+ -- Countdown doesn't exist or has ended
+ result := 'badCountdown';
+
+ ELSEIF lastMessage.value IS NOT NULL AND
+ lastMessage.value != _value + 1 THEN
+ -- Message contains the wrong number
+ result := 'badNumber';
+
+ ELSEIF lastMessage.userID = _userID THEN
+ -- User sent consecutive messages
+ result := 'badUser';
+
+ ELSE
+ -- Message is valid, insert it into messages
+ INSERT INTO messages (messageID, userID, countdownID, value, timestamp)
+ VALUES (_messageID, _userID, _countdownID, _value, _timestamp);
+ result := 'good';
+
+ -- Get total from first message
+ SELECT value
+ INTO total
+ FROM messages
+ WHERE countdownID = _countdownID
+ ORDER BY timestamp ASC
+ LIMIT 1;
+
+ -- Check if message should be pinned
+ IF total >= 500 AND _value % (total / 50) = 0 AND _value != 0 THEN
+ pin := TRUE;
+ ELSE
+ pin := FALSE;
+ END IF;
+
+ -- Check if message has custom reactions
+ IF EXISTS(SELECT 1 FROM reactions
+ WHERE countdownID = _countdownID AND number = _value
+ ) THEN
+ reactions := TRUE;
+ ELSE
+ reactions := FALSE;
+ END IF;
+ END IF;
+END
+$$;
+
+-- Get the custom reactions for a number in a countdown
+CREATE FUNCTION getReactions (
+ _countdownID BIGINT, -- The countdown channel ID
+ _number INT -- The number
+)
+RETURNS TABLE (
+ value CHAR -- A custom reaction
+)
+LANGUAGE plpgsql AS $$
+BEGIN
+ RETURN QUERY
+ SELECT reactions.value
+ FROM reactions
+ WHERE countdownID = _countdownID AND number = _number;
+END
+$$;
