commit 0d2b5230fb4fd7833783beb8628a2c9fe775a490
parent 3961e7ffe852ab629c329bf5d5ee05252eb3a4de
Author: Asher Morgan <59518073+ashermorgan@users.noreply.github.com>
Date: Thu, 25 Apr 2024 18:29:02 -0700
Update config command to use postgresql db
Diffstat:
3 files changed, 81 insertions(+), 32 deletions(-)
diff --git a/countdown_bot/utilitiesCog.py b/countdown_bot/utilitiesCog.py
@@ -3,7 +3,7 @@ import discord
from discord.ext import commands
# Import modules
-from .botUtilities import COLORS, CommandError, isCountdown, getContextCountdown, getCountdown, loadCountdown
+from .botUtilities import COLORS, CommandError, isCountdown, getContextCountdown, getCountdown, loadCountdown, getContextCountdown2, CountdownNotFound
from .models import Countdown, Prefix, Reaction
@@ -68,56 +68,67 @@ class Utilities(commands.Cog):
if (not isinstance(ctx.channel, discord.channel.TextChannel)):
raise CommandError("This command must be run in a countdown channel or a server with a countdown channel")
- with self.databaseSessionMaker() as session:
+ with self.db_connection.cursor() as cur:
# Get countdown channel
- countdown = getContextCountdown(session, ctx)
+ countdown = getContextCountdown2(cur, ctx)
+
+ if not countdown:
+ raise CountdownNotFound()
# Get / set settings
if (key is None):
- embed.description = f"**Countdown Channel:** <#{countdown.id}>\n"
- embed.description += f"**Command Prefixes:** `{'`, `'.join([x.value for x in countdown.prefixes])}`\n"
- embed.description += f"**Countdown Timezone:** {countdown.getTimezone()}\n"
- if (len(countdown.reactions) == 0):
+ embed.description = f"**Countdown Channel:** <#{countdown}>\n"
+
+ cur.execute("SELECT * FROM getPrefixes(NULL, %s);", (countdown,))
+ prefixes = [x[0] for x in cur.fetchall()]
+ embed.description += f"**Command Prefixes:** `{'`, `'.join(prefixes)}`\n"
+
+ # embed.description += f"**Countdown Timezone:** {countdown.getTimezone()}\n"
+
+ cur.execute("SELECT * FROM getReactions(%s, NULL);", (countdown,))
+ reactions = cur.fetchall()
+ if (len(reactions) == 0):
embed.description += f"**Reactions:** none\n"
else:
embed.description += f"**Reactions:**\n"
- for number in list(dict.fromkeys([x.number for x in countdown.reactions])):
- embed.description += f"**-** #{number}: {', '.join([x.value for x in countdown.reactions if x.number == number])}\n"
+ for number in reversed(list(set([x[1] for x in reactions]))):
+ embed.description += f"**-** #{number}: {', '.join([x[0] for x in reactions if x[1] == number])}\n"
+
embed.description += f"\nUse `{ctx.prefix}help config` to view more information about settings\n"
embed.description += f"Use `{ctx.prefix}config <key> <value>` to modify settings\n"
elif (not ctx.message.author.guild_permissions.administrator):
raise CommandError("You must be an administrator to modify settings")
elif (len(args) == 0):
raise CommandError("Please provide a value for the setting")
- elif (key in ["tz", "timezone"]):
- try:
- countdown.timezone = float(args[0])
- except:
- raise CommandError(f"Invalid timezone: `{args[0]}`")
- else:
- embed.description = f"Timezone set to {countdown.getTimezone()}"
+ # elif (key in ["tz", "timezone"]):
+ # try:
+ # countdown.timezone = float(args[0])
+ # except:
+ # raise CommandError(f"Invalid timezone: `{args[0]}`")
+ # else:
+ # embed.description = f"Timezone set to {countdown.getTimezone()}"
elif (key in ["prefix", "prefixes"]):
- countdown.prefixes = [Prefix(countdown_id=ctx.channel.id, value=x) for x in args]
+ cur.execute("CALL setPrefixes(%s, %s);", (countdown, list(args)))
embed.description = f"Prefixes updated"
elif (key in ["react"]):
try:
number = int(args[0])
- if (number < 0):
- raise CommandError("Number must be greater than zero")
- elif (len(args) == 1):
- countdown.reactions = [x for x in countdown.reactions if x.number != number]
- embed.description = f"Removed reactions for #{number}"
- else:
- countdown.reactions = [x for x in countdown.reactions if x.number != number]
- countdown.reactions += [Reaction(countdown_id=countdown.id, number=number, value=x) for x in args[1:]]
- embed.description = f"Updated reactions for #{number}"
except:
raise CommandError(f"Invalid number: `{args[0]}`")
+ if (number < 0):
+ raise CommandError("Number must be greater than zero")
+ print(list(args[1:]))
+ cur.execute("CALL setReactions(%s, %s, %s);",
+ (countdown, number, list(args[1:])))
+ if (len(args) == 1):
+ embed.description = f"Removed reactions for #{number}"
+ else:
+ embed.description = f"Updated reactions for #{number}"
else:
raise CommandError(f"Setting not found: `{key}`")
# Save changes
- session.commit()
+ self.db_connection.commit()
# Send embed
await ctx.send(embed=embed)
diff --git a/models/tables.sql b/models/tables.sql
@@ -37,8 +37,8 @@ CREATE table prefixes (
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
+ number INT NOT NULL, -- The prefix
+ value VARCHAR(8) NOT NULL, -- The reaction
FOREIGN KEY (countdownID)
REFERENCES countdowns(countdownID)
ON DELETE CASCADE
diff --git a/models/utilities.sql b/models/utilities.sql
@@ -1,5 +1,6 @@
-- countdown-bot utility procedures
+DROP PROCEDURE IF EXISTS setReactions;
DROP FUNCTION IF EXISTS getReactions;
DROP PROCEDURE IF EXISTS addMessage;
DROP TYPE IF EXISTS addMessageResults;
@@ -9,6 +10,7 @@ DROP PROCEDURE IF EXISTS clearCountdown;
DROP PROCEDURE IF EXISTS isCountdown;
DROP PROCEDURE IF EXISTS getUserContextCountdown;
DROP PROCEDURE IF EXISTS getServerContextCountdown;
+DROP PROCEDURE IF EXISTS setPrefixes;
DROP FUNCTION IF EXISTS getPrefixes;
-- Get the active prefixes for a server
@@ -42,6 +44,22 @@ BEGIN
END
$$;
+-- Set the command prefixes used by a countdown channel
+CREATE PROCEDURE setPrefixes (
+ _countdownID BIGINT, -- The countdown channel ID
+ _prefixes VARCHAR(8)[] -- The prefix values
+)
+LANGUAGE plpgsql AS $$
+BEGIN
+ DELETE FROM prefixes
+ WHERE countdownID = _countdownID;
+
+ INSERT INTO prefixes (countdownID, value)
+ SELECT _countdownID, *
+ FROM unnest(_prefixes);
+END
+$$;
+
-- Get the most relevant countdown to a server channel
CREATE PROCEDURE getServerContextCountdown (
_serverID IN BIGINT, -- The server ID
@@ -234,16 +252,36 @@ $$;
-- Get the custom reactions for a number in a countdown
CREATE FUNCTION getReactions (
_countdownID BIGINT, -- The countdown channel ID
- _number INT -- The number
+ _number INT -- The number (or NULL for all numbers)
)
RETURNS TABLE (
- value CHAR -- A custom reaction
+ value VARCHAR(8), -- A custom reaction
+ number INT -- The number
)
LANGUAGE plpgsql AS $$
BEGIN
RETURN QUERY
- SELECT reactions.value
+ SELECT reactions.value, reactions.number
FROM reactions
+ WHERE countdownID = _countdownID
+ AND (reactions.number = _number OR _number IS NULL)
+ ORDER BY reactions.number DESC;
+END
+$$;
+
+-- Set the custom reactions for a number in a countdown
+CREATE PROCEDURE setReactions (
+ _countdownID BIGINT, -- The countdown channel ID
+ _number INT, -- The number
+ _reactions VARCHAR(8)[] -- The custom reactions
+)
+LANGUAGE plpgsql AS $$
+BEGIN
+ DELETE FROM reactions
WHERE countdownID = _countdownID AND number = _number;
+
+ INSERT INTO reactions (countdownID, number, value)
+ SELECT _countdownID, _number, *
+ FROM unnest(_reactions);
END
$$;
