commit d5235c149c83811e3f26e44e790969cc5f309bf8
parent 3b2ff9bc791e33765ef01839030aa51b86afafa0
Author: Asher Morgan <59518073+ashermorgan@users.noreply.github.com>
Date: Sun, 21 Apr 2024 15:09:45 -0700
Update schema to use BIGINT IDs
Diffstat:
3 files changed, 31 insertions(+), 31 deletions(-)
diff --git a/models/analytics.sql b/models/analytics.sql
@@ -11,11 +11,11 @@ DROP FUNCTION IF EXISTS contributorData;
-- Get overall contributor data for a countdown
CREATE FUNCTION contributorData (
- _countdownID INT -- The countdown channel ID
+ _countdownID BIGINT -- The countdown channel ID
)
RETURNS TABLE (
ranking BIGINT, -- The user's (1-based) contribution ranking
- userID INT, -- The user ID
+ userID BIGINT, -- The user ID
contributions BIGINT, -- The user's number of contributions
percentage FLOAT -- The user's percentage of all contributions
)
@@ -43,7 +43,7 @@ $$;
-- Calculate the current ETA for each message in a countdown
CREATE FUNCTION etaData (
- _countdownID INT -- The countdown channel ID
+ _countdownID BIGINT -- The countdown channel ID
)
RETURNS TABLE (
_timestamp TIMESTAMPTZ, -- The timestamp of the message
@@ -78,8 +78,8 @@ $$;
-- Count the number of contributions in a countdown for each day/hour zone
CREATE FUNCTION heatmapData (
- _countdownID INT, -- The countdown channel ID
- _userID INT -- The user ID to filter by (or NULL for all users)
+ _countdownID BIGINT, -- The countdown channel ID
+ _userID BIGINT -- The user ID to filter by (or NULL for all users)
)
RETURNS TABLE (
dow NUMERIC, -- The day of the week (0-6 for Sunday-Saturday)
@@ -101,11 +101,11 @@ $$;
-- Calculate each user's contribution percentage at each message in a countdown
CREATE FUNCTION historicalContributorData (
- _countdownID INT -- The countdown channel ID
+ _countdownID BIGINT -- The countdown channel ID
)
RETURNS TABLE (
progress INT, -- The current countdown progress (0-total)
- userID INT, -- The user ID
+ userID BIGINT, -- The user ID
percentage FLOAT -- The user's percentage of all contributions so far
)
LANGUAGE plpgsql AS $$
@@ -140,12 +140,12 @@ $$;
-- Get the current leaderboard data for a countdown
CREATE FUNCTION leaderboardData (
- _countdownID INT, -- The countdown channel ID
- _userID INT -- The user ID to filter by (or NULL for all users)
+ _countdownID BIGINT, -- The countdown channel ID
+ _userID BIGINT -- The user ID to filter by (or NULL for all users)
)
RETURNS TABLE (
ranking BIGINT, -- The user's (1-based) leaderboard ranking
- userID INT, -- The user ID
+ userID BIGINT, -- The user ID
total BIGINT, -- The user's total leaderboard points
contributions BIGINT, -- The user's number of contributions
percentage FLOAT, -- The user's percentage of all contributions
@@ -229,7 +229,7 @@ $$;
-- Get the current progress for each message in a countdown
CREATE FUNCTION progressData (
- _countdownID INT -- The countdown channel ID
+ _countdownID BIGINT -- The countdown channel ID
)
RETURNS TABLE (
_timestamp TIMESTAMPTZ, -- The timestamp of the message
@@ -247,7 +247,7 @@ $$;
-- Get general progress-related statistics for a countdown
CREATE PROCEDURE progressStats (
- _countdownID IN INT, -- The countdown channel ID
+ _countdownID IN BIGINT, -- The countdown channel ID
total OUT INT, -- The starting value
current OUT INT, -- The current value
progress OUT INT, -- The countdown progress (0-total)
@@ -310,8 +310,8 @@ $$;
-- Calculate the number of contributions per period in a countdown
CREATE FUNCTION speedData (
- _countdownID INT, -- The countdown channel ID
- hours INT -- The period size, in hours
+ _countdownID BIGINT, -- The countdown channel ID
+ hours INT -- The period size, in hours
)
RETURNS TABLE (
periodStart TIMESTAMPTZ, -- The start of the period
diff --git a/models/tables.sql b/models/tables.sql
@@ -6,15 +6,15 @@ DROP TABLE IF EXISTS countdowns;
-- Records countdown channels
CREATE TABLE countdowns (
- countdownID INT PRIMARY KEY, -- The Discord channel ID
- serverID INT NOT NULL -- The channel's Discord server ID
+ countdownID BIGINT PRIMARY KEY, -- The Discord channel ID
+ serverID BIGINT NOT NULL -- The channel's Discord server ID
);
-- Records contributions to countdowns
CREATE TABLE messages (
- messageID INT PRIMARY KEY, -- The Discord message ID
- countdownID INT NOT NULL, -- The countdown ID
- userID INT NOT NULL, -- The author's Discord user ID
+ 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
FOREIGN KEY (countdownID) REFERENCES countdowns(countdownID)
@@ -23,7 +23,7 @@ CREATE TABLE messages (
-- Records bot command prefixes
CREATE table prefixes (
prefixID SERIAL PRIMARY KEY, -- The prefix ID
- countdownID INT 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)
);
diff --git a/models/utilities.sql b/models/utilities.sql
@@ -16,9 +16,9 @@ CREATE TYPE addMessageResults AS ENUM (
-- Validate and add a new countdown message
CREATE PROCEDURE addMessage (
- _messageID IN INT, -- The message ID
- _countdownID IN INT, -- The message countdown ID
- _userID IN INT, -- The message user ID
+ _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
@@ -65,8 +65,8 @@ $$;
-- Get the active prefixes for a server
CREATE FUNCTION getPrefixes (
- _serverID IN INT, -- The server ID
- channelID IN INT -- The channel ID
+ _serverID IN BIGINT, -- The server ID
+ channelID IN BIGINT -- The channel ID
)
RETURNS TABLE (
prefix VARCHAR(8) -- An active prefix
@@ -96,10 +96,10 @@ $$;
-- Get the most relevant countdown to a server channel
CREATE PROCEDURE getServerContextCountdown (
- _serverID IN INT, -- The server ID
- channelID IN INT, -- The channel ID
- prefix IN VARCHAR(8), -- The prefix used to invoke the bot
- countdownID OUT INT -- The ID of the most relevant countdown
+ _serverID IN BIGINT, -- The server ID
+ channelID IN BIGINT, -- The channel ID
+ prefix IN VARCHAR(8), -- The prefix used to invoke the bot
+ countdownID OUT BIGINT -- The ID of the most relevant countdown
)
LANGUAGE plpgsql AS $$
BEGIN
@@ -128,8 +128,8 @@ $$;
-- Get the most relevant countdown to a user
CREATE PROCEDURE getUserContextCountdown (
- _userID IN INT, -- The user ID
- countdownID OUT INT -- The ID of the most relevant countdown
+ _userID IN BIGINT, -- The user ID
+ countdownID OUT BIGINT -- The ID of the most relevant countdown
)
LANGUAGE plpgsql AS $$
BEGIN
