commit f247ca4c4f7afe925b0e3b1cd02e73cd65840b4f
parent 73eefeafdc839fb654e8deebcdd988390ff48d6f
Author: Asher Morgan <59518073+ashermorgan@users.noreply.github.com>
Date: Sat, 27 Apr 2024 16:37:15 -0700
Reorganize files and cogs
Diffstat:
12 files changed, 817 insertions(+), 778 deletions(-)
diff --git a/README.md b/README.md
@@ -22,7 +22,7 @@ LOG_LEVEL=INFO
Initialize the PostgreSQL database
```
-psql 'postgresql://...' -f models/tables.sql -f models/utilities.sql -f models/analytics.sql
+psql 'postgresql://...' -f models/ddl.sql -f models/dml-utils.sql -f models/dml-core.sql -f models/dml-analytics.sql
```
Run the bot
diff --git a/countdown_bot/analyticsCog.py b/countdown_bot/analyticsCog.py
@@ -251,7 +251,6 @@ class Analytics(commands.Cog):
data = cur.fetchall()
if not data:
- print(countdown, userID, data)
raise CommandError("The countdown doesn't have enough messages yet")
# Create heatmap matrix
diff --git a/countdown_bot/bot.py b/countdown_bot/bot.py
@@ -5,7 +5,7 @@ import logging
# Import modules
-from . import analyticsCog, utilitiesCog
+from . import analyticsCog, coreCog, helpCog
from .botUtilities import addMessage, COLORS, CountdownNotFound, ContributorNotFound, CommandError, getPrefix
@@ -27,7 +27,8 @@ class CountdownBot(commands.Bot):
async def setup_hook(self):
- await self.add_cog(utilitiesCog.Utilities(self, self.db_connection))
+ await self.add_cog(helpCog.Help(self))
+ await self.add_cog(coreCog.Core(self, self.db_connection))
await self.add_cog(analyticsCog.Analytics(self, self.db_connection))
@@ -37,40 +38,12 @@ class CountdownBot(commands.Bot):
- async def on_guild_join(self, guild):
- # Print status
- self.logger.info(f"Added to {guild} (ID {guild.id})")
-
- # Create embed
- embed=discord.Embed(title=":rocket: Getting Started with countdown-bot", color=COLORS["embed"])
- embed.description = f"Thanks for adding me to your server! Here are some steps for getting started:\n"
- embed.description += f"**1.** View help information using the `{self.prefixes[0]}help` command\n"
- embed.description += f"**2.** Activate a new countdown channel using the `{self.prefixes[0]}activate` command\n"
- embed.description += f"**3.** Change my settings using the `{self.prefixes[0]}config` command\n"
- embed.description += f"**4.** View countdown analytics using the `{self.prefixes[0]}analytics` command\n"
-
- # Send embed
- await guild.system_channel.send(embed=embed)
-
-
-
async def on_message(self, obj):
- # Respond to @mentions
- if self.user in obj.mentions:
- embed=discord.Embed(title="countdown-bot", description=f"Use `{(await self.get_prefix(obj))[0]}help` to view help information", color=COLORS["embed"])
- await obj.channel.send(embed=embed)
-
- # Parse countdown message
- with self.db_connection.cursor() as cur:
- if (await addMessage(cur, obj)):
- self.db_connection.commit()
-
- # Run commands
try:
# Make command prefixes, names, and arguments case insensitive
obj.content = obj.content.lower()
- # Execute command
+ # Run commands
await self.process_commands(obj)
except:
pass
diff --git a/countdown_bot/botUtilities.py b/countdown_bot/botUtilities.py
@@ -163,7 +163,7 @@ def getPrefix(conn, ctx, default):
"""
with conn.cursor() as cur:
- cur.execute("SELECT * FROM getPrefixes(%s, %s);",
+ cur.execute("SELECT * FROM getServerPrefixes(%s, %s);",
(ctx.channel.guild.id if ctx.channel.guild else None, ctx.channel.id))
prefixes = cur.fetchall()
return [x["prefix"] for x in prefixes] if prefixes else default
diff --git a/countdown_bot/coreCog.py b/countdown_bot/coreCog.py
@@ -0,0 +1,201 @@
+# Import dependencies
+import discord
+from discord.ext import commands
+
+# Import modules
+from .botUtilities import COLORS, CommandError, CountdownNotFound, isCountdown, loadCountdown, getContextCountdown, addMessage
+
+
+
+class Core(commands.Cog):
+ def __init__(self, bot, db_connection):
+ self.bot = bot
+ self.db_connection = db_connection
+
+
+
+ @commands.Cog.listener()
+ async def on_message(self, obj):
+ # Parse countdown message
+ with self.db_connection.cursor() as cur:
+ if (await addMessage(cur, obj)):
+ self.db_connection.commit()
+
+
+
+ @commands.command()
+ async def activate(self, ctx):
+ """
+ Turns a channel into a countdown
+ """
+
+ with self.db_connection.cursor() as cur:
+ # Check if channel is already a countdown
+ if (isCountdown(cur, ctx.channel.id)):
+ raise CommandError("This channel is already a countdown")
+
+ # Check if channel is a DM
+ if (not isinstance(ctx.channel, discord.channel.TextChannel)):
+ raise CommandError("This command must be run inside a server")
+
+ # Check if user isn't authorized
+ if (not ctx.message.author.guild_permissions.administrator):
+ raise CommandError("You must be an administrator to turn a channel into a countdown")
+
+ # Create countdown
+ cur.execute("CALL createCountdown(%s, %s, %s);",
+ (ctx.channel.id, ctx.channel.guild.id, self.bot.prefixes[0]))
+
+ # Send initial response
+ self.bot.logger.info(f"Activated {self.bot.get_channel(ctx.channel.id)} (ID {ctx.channel.id}) as a countdown")
+ embed = discord.Embed(title=":clock3: Loading Countdown", description="This channel is now a countdown\nPlease wait to start counting", color=COLORS["embed"])
+ msg = await ctx.send(embed=embed)
+
+ # Load countdown
+ await loadCountdown(self.bot, ctx.channel.id)
+ self.db_connection.commit()
+
+ # Send final response
+ embed = discord.Embed(title=":white_check_mark: Countdown Activated", description="This channel is now a countdown\nYou may start counting!", color=COLORS["embed"])
+ await msg.edit(embed=embed)
+
+
+
+ @commands.command()
+ async def config(self, ctx, key=None, *args):
+ """
+ Shows and modifies countdown settings
+ """
+
+ # Create embed
+ embed = discord.Embed(title=":gear: Countdown Settings", color=COLORS["embed"])
+
+ # Make sure context is in a server
+ 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.db_connection.cursor() as cur:
+ # Get countdown channel
+ countdown = getContextCountdown(cur, ctx)
+
+ if not countdown:
+ raise CountdownNotFound()
+
+ # Get / set settings
+ if (key is None):
+ embed.description = f"**Countdown Channel:** <#{countdown}>\n"
+
+ cur.execute("SELECT * from getPrefixes(%s);", (countdown,))
+ prefixes = [x["prefix"] for x in cur.fetchall()]
+ embed.description += f"**Command Prefixes:** `{'`, `'.join(prefixes)}`\n"
+
+ cur.execute("CALL getTimezone(%s, null);", (countdown,))
+ timezone = cur.fetchone()["_timezone"]
+ if (timezone >= 0):
+ embed.description += f"**Countdown Timezone:** UTC+{timezone:.2f}\n"
+ else:
+ embed.description += f"**Countdown Timezone:** UTC-{abs(timezone):.2f}\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 reversed(list(set([x["number"] for x in reactions]))):
+ embed.description += f"**-** #{number}: {', '.join([x["value"] for x in reactions if x["number"] == 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:
+ timezone = float(args[0])
+ except:
+ raise CommandError(f"Invalid timezone: `{args[0]}`")
+ else:
+ cur.execute("CALL setTimezone(%s, %s);", (countdown, timezone))
+ if (timezone >= 0):
+ embed.description = f"Timezone set to UTC+{timezone:.2f}\n"
+ else:
+ embed.description = f"Timezone set to UTC-{abs(timezone):.2f}\n"
+ elif (key in ["prefix", "prefixes"]):
+ cur.execute("CALL setPrefixes(%s, %s);", (countdown, list(args)))
+ embed.description = f"Prefixes updated"
+ elif (key in ["react"]):
+ try:
+ number = int(args[0])
+ except:
+ raise CommandError(f"Invalid number: `{args[0]}`")
+ if (number < 0):
+ raise CommandError("Number must be greater than zero")
+ 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
+ self.db_connection.commit()
+
+ # Send embed
+ await ctx.send(embed=embed)
+
+
+
+ @commands.command()
+ async def deactivate(self, ctx):
+ """
+ Deactivates a countdown channel
+ """
+
+ with self.db_connection.cursor() as cur:
+ # Check if channel isn't a countdown
+ if (not isCountdown(cur, ctx.channel.id)):
+ raise CommandError("This channel isn't a countdown")
+
+ # Check if user isn't authorized
+ if (not ctx.author.guild_permissions.administrator):
+ raise CommandError("You must be an administrator to deactivate a countdown channel")
+
+ # Delete countdown
+ cur.execute("CALL deleteCountdown(%s);",
+ (ctx.channel.id,))
+ self.db_connection.commit()
+
+ # Send response
+ self.bot.logger.info(f"Deactivated {self.bot.get_channel(ctx.channel.id)} (ID {ctx.channel.id}) as a countdown")
+ embed = discord.Embed(title=":octagonal_sign: Countdown Deactivated", description="This channel is no longer a countdown", color=COLORS["embed"])
+ await ctx.send(embed=embed)
+
+
+
+ @commands.command()
+ async def reload(self, ctx):
+ """
+ Reloads the countdown cache
+ """
+
+ with self.db_connection.cursor() as cur:
+ # Check if channel isn't a countdown
+ if (not isCountdown(cur, ctx.channel.id)):
+ raise CommandError("Countdown not found\nThis command must be used in a countdown channel")
+
+ # Send initial response
+ embed = discord.Embed(title=":clock3: Reloading Countdown Cache", description="Please wait to continue counting", color=COLORS["embed"])
+ msg = await ctx.channel.send(embed=embed)
+
+ # Reload messages
+ await loadCountdown(self.bot, ctx.channel.id)
+ self.db_connection.commit()
+
+ # Send final response
+ self.bot.logger.info(f"Reloaded messages from {self.bot.get_channel(ctx.channel.id)} (ID {ctx.channel.id})")
+ embed = discord.Embed(title=":white_check_mark: Countdown Cache Reloaded", description="Done! You may continue counting!", color=COLORS["embed"])
+ await msg.edit(embed=embed)
diff --git a/countdown_bot/helpCog.py b/countdown_bot/helpCog.py
@@ -0,0 +1,274 @@
+# Import dependencies
+import discord
+from discord.ext import commands
+
+# Import modules
+from .botUtilities import COLORS, CommandError
+
+
+
+class Help(commands.Cog):
+ def __init__(self, bot):
+ self.bot = bot
+ self.bot.remove_command("help")
+
+
+
+ @commands.Cog.listener()
+ async def on_guild_join(self, guild):
+ # Log status
+ self.logger.info(f"Added to {guild} (ID {guild.id})")
+
+ # Create embed
+ embed=discord.Embed(title=":rocket: Getting Started with countdown-bot", color=COLORS["embed"])
+ embed.description = f"Thanks for adding me to your server! Here are some steps for getting started:\n"
+ embed.description += f"**1.** View help information using the `{self.bot.prefixes[0]}help` command\n"
+ embed.description += f"**2.** Activate a new countdown channel using the `{self.bot.prefixes[0]}activate` command\n"
+ embed.description += f"**3.** Change my settings using the `{self.bot.prefixes[0]}config` command\n"
+ embed.description += f"**4.** View countdown analytics using the `{self.bot.prefixes[0]}analytics` command\n"
+
+ # Send embed
+ await ctx.guild.system_channel.send(embed=embed)
+
+
+
+ @commands.Cog.listener()
+ async def on_message(self, obj):
+ # Respond to @mentions
+ if self.bot.user in obj.mentions:
+ embed=discord.Embed(title="countdown-bot", description=f"Use `{(await self.bot.get_prefix(obj))[0]}help` to view help information", color=COLORS["embed"])
+ await obj.channel.send(embed=embed)
+
+
+
+ @commands.command(aliases=["h", ""])
+ async def help(self, ctx, command=None):
+ """
+ Shows help information
+ """
+
+ # Initialize help information
+ prefixes = await self.bot.get_prefix(ctx)
+ help_text = {
+ "prefixes":
+ f"`{'`, `'.join(prefixes)}`",
+ "utility-commands":
+ "**-** `activate`: Turns a channel into a countdown\n" \
+ "**-** `config`: Shows and modifies bot and countdown settings\n" \
+ "**-** `deactivate`: Deactivates a countdown channel\n" \
+ "**-** `help`, `h`: Shows help information\n" \
+ "**-** `ping`: Pings the bot\n" \
+ "**-** `reload`: Reloads the countdown cache\n",
+ "analytics-commands":
+ "**-** `analytics`, `a`: Shows all countdown analytics\n" \
+ "**-** `contributors`, `c`: Shows information about countdown contributors\n" \
+ "**-** `eta`, `e`: Shows information about the estimated completion date\n" \
+ "**-** `heatmap`: Shows a heatmap of when messages are sent\n" \
+ "**-** `leaderboard`, `l`: Shows the countdown leaderboard\n" \
+ "**-** `progress`, `p`: Shows information about countdown progress\n" \
+ "**-** `speed`, `s`: Shows information about countdown speed\n",
+ "behavior":
+ "**-** Reacts with :no_entry: when a user counts out of turn\n" \
+ "**-** Reacts with :x: when a user counts incorrectly\n" \
+ "**-** Ignores messages that don't start with a (positive) number\n" \
+ "**-** Pins numbers every 2% if the countdown started at 500 or higher\n",
+ "getting-started":
+ f"**1.** View help information using the `{prefixes[0]}help` command\n" \
+ f"**2.** Activate a new countdown channel using the `{prefixes[0]}activate` command\n" \
+ f"**3.** Change my settings using the `{prefixes[0]}config` command\n" \
+ f"**4.** View countdown analytics using the `{prefixes[0]}analytics` command\n",
+ "troubleshooting":
+ f"**1.** Run `{prefixes[0]}ping` to make sure that I'm online\n" \
+ f"**2.** If I reacted incorrectly to a message, remove my incorrect reaction(s)\n" \
+ f"**3.** Run `{prefixes[0]}reload` in the countdown channel\n",
+ "activate":
+ "**Name:** activate\n" \
+ "**Description:** Turns a channel into a countdown\n" \
+ f"**Usage:** `{prefixes[0]}activate`\n" \
+ "**Aliases:** none\n" \
+ "**Arguments:** none\n" \
+ "**Examples:**\n" \
+ f"**-** `{prefixes[0]}activate`\n" \
+ "**Notes:** Users must have admin permissions to turn a channel into a countdown\n",
+ "analytics":
+ "**Name:** analytics\n" \
+ "**Description:** Shows all countdown analytics\n" \
+ f"**Usage:** `{prefixes[0]}analytics|a`\n" \
+ "**Aliases:** `a`\n" \
+ "**Arguments: none**\n" \
+ "**Examples:**\n" \
+ f"**-** `{prefixes[0]}analytics`\n" \
+ "**Notes:** none\n",
+ "config":
+ "**Name:** config\n" \
+ "**Description:** Shows and modifies countdown settings\n" \
+ f"**Usage:** `{prefixes[0]}config [<key> <value>...]`\n" \
+ "**Aliases:** none\n" \
+ "**Arguments:**\n" \
+ "**-** `<key>`: The name of the setting to modify. If no key is supplied, all settings will be shown\n" \
+ "**-** `<value>`: The new value(s) for the setting\n" \
+ "**Available Settings:**\n" \
+ "**-** `prefix`, `prefixes`: The prefix(es) for the bot\n" \
+ "**-** `tz`, `timezone`: The UTC offset in hours\n" \
+ "**-** `react`: The reactions for a certain number\n" \
+ "**Examples:**\n" \
+ f"**-** `{prefixes[0]}config`\n" \
+ f"**-** `{prefixes[0]}config prefixes prefix1 prefix2 prefix3`\n" \
+ f"**-** `{prefixes[0]}config timezone -1.5`\n" \
+ f"**-** `{prefixes[0]}config react 0 :partying_face: :smile:`\n" \
+ "**Notes:** Users must have admin permissions to modify settings\n",
+ "contributors":
+ "**Name:** contributors\n" \
+ "**Description:** Shows information about countdown contributors\n" \
+ f"**Usage:** `{prefixes[0]}contributors|c [history|h]`\n" \
+ "**Aliases:** `c`\n" \
+ "**Arguments:**\n" \
+ "**-** `history`, `h`: Shows historical data about countdown contributors\n" \
+ "**Examples:**\n" \
+ f"**-** `{prefixes[0]}contributors`\n" \
+ f"**-** `{prefixes[0]}contributors history`\n" \
+ "**Notes:** The contributors embed will only show the top 20 contributors\n",
+ "deactivate":
+ "**Name:** deactivate\n" \
+ "**Description:** Deactivates a countdown channel\n" \
+ f"**Usage:** `{prefixes[0]}deactivate`\n" \
+ "**Aliases:** none\n" \
+ "**Arguments:** none\n" \
+ "**Examples:**\n" \
+ f"**-** `{prefixes[0]}deactivate`\n" \
+ "**Notes:** Users must have admin permissions to deactivate a countdown channel\n",
+ "eta":
+ "**Name:** eta\n" \
+ "**Description:** Shows information about the estimated completion date\n" \
+ f"**Usage:** `{prefixes[0]}eta|e`\n" \
+ "**Aliases:** `e`\n" \
+ "**Arguments:** none\n" \
+ "**Examples:**\n" \
+ f"**-** `{prefixes[0]}eta`\n" \
+ "**Notes:** none\n",
+ "heatmap":
+ "**Name:** heatmap\n" \
+ "**Description:** Shows a heatmap of when countdown messages are sent\n" \
+ f"**Usage:** `{prefixes[0]}heatmap [<user>]`\n" \
+ "**Aliases:** none\n" \
+ "**Arguments:**\n" \
+ "**-** `<user>`: The user to view heatmap information about. If no value is supplied, the general heatmap will be shown\n" \
+ "**Examples:**\n" \
+ f"**-** `{prefixes[0]}heatmap`\n" \
+ f"**-** `{prefixes[0]}heatmap @Alice`\n" \
+ "**Notes:** none\n",
+ "help":
+ "**Name:** help\n" \
+ "**Description:** Shows help information\n" \
+ f"**Usage:** `{prefixes[0]}help|h [<command>]`\n" \
+ "**Aliases:** `h`\n" \
+ "**Arguments:**\n" \
+ "**-** `<command>`: The command to view help information about. If no value is supplied, general help information will be shown\n" \
+ "**Examples:**\n" \
+ f"**-** `{prefixes[0]}help`\n" \
+ f"**-** `{prefixes[0]}help config`\n" \
+ "**Notes:** none\n",
+ "leaderboard":
+ "**Name:** leaderboard\n" \
+ "**Description:** Shows the countdown leaderboard\n" \
+ f"**Usage:** `{prefixes[0]}leaderboard|l [<user>]`\n" \
+ "**Aliases:** `l`\n" \
+ "**Arguments:**\n" \
+ "**-** `<user>`: The user to view leaderboard information about. If no value is supplied, the whole leaderboard will be shown\n" \
+ "**Examples:**\n" \
+ f"**-** `{prefixes[0]}leaderboard`\n" \
+ f"**-** `{prefixes[0]}leaderboard @Alice`\n" \
+ "**Notes:** The leaderboard embed will only show the top 20 contributors\n",
+ "ping":
+ "**Name:** ping\n" \
+ "**Description:** Pings the bot\n" \
+ f"**Usage:** `{prefixes[0]}ping`\n" \
+ "**Aliases:** none\n" \
+ "**Arguments:** none\n" \
+ "**Examples:**\n" \
+ f"**-** `{prefixes[0]}ping`\n" \
+ "**Notes:** none\n",
+ "progress":
+ "**Name:** progress\n" \
+ "**Description:** Shows information about countdown progress\n" \
+ f"**Usage:** `{prefixes[0]}progress|p`\n" \
+ "**Aliases:** `p`\n" \
+ "**Arguments:** none\n" \
+ "**Examples:**\n" \
+ f"**-** `{prefixes[0]}progress`\n" \
+ "**Notes:** none\n",
+ "reload":
+ "**Name:** reload\n" \
+ "**Description:** Reloads the countdown cache\n" \
+ f"**Usage:** `{prefixes[0]}reload`\n" \
+ "**Aliases:** none\n" \
+ "**Arguments:** none\n" \
+ "**Examples:**\n" \
+ f"**-** `{prefixes[0]}reload`\n" \
+ "**Notes:** This command must be used in a countdown channel\n",
+ "speed":
+ "**Name:** speed\n" \
+ "**Description:** Shows information about countdown speed\n" \
+ f"**Usage:** `{prefixes[0]}speed|s [<period>]`\n" \
+ "**Aliases:** `s`\n" \
+ "**Arguments:**\n" \
+ "**-** `<period>`: The size of the period in hours (the default is 24 hours)\n" \
+ "**Examples:**\n" \
+ f"**-** `{prefixes[0]}speed`\n" \
+ f"**-** `{prefixes[0]}speed 48`\n" \
+ "**Notes:** none\n",
+ }
+
+ # Create embed
+ embed=discord.Embed(title=":grey_question: countdown-bot Help", color=COLORS["embed"])
+ if (command is None):
+ embed.add_field(name="Command Prefixes :gear:", value=help_text["prefixes"], inline=False)
+ embed.add_field(name="Utility Commands :wrench:", value=help_text["utility-commands"], inline=False)
+ embed.add_field(name="Analytics Commands :bar_chart:", value=help_text["analytics-commands"], inline=False)
+ embed.add_field(name="Behavior in Countdown Channels :robot:", value=help_text["behavior"], inline=False)
+ embed.add_field(name="Getting Started :rocket:", value=help_text["getting-started"], inline=False)
+ embed.add_field(name="Troubleshooting :screwdriver:", value=help_text["troubleshooting"], inline=False)
+ embed.description = f"Use `{prefixes[0]}help <command>` to get more info on a command"
+ elif (command.lower() in ["activate"]):
+ embed.description = help_text["activate"]
+ elif (command.lower() in ["a", "analytics"]):
+ embed.description = help_text["analytics"]
+ elif (command.lower() in ["config"]):
+ embed.description = help_text["config"]
+ elif (command.lower() in ["c", "contributors"]):
+ embed.description = help_text["contributors"]
+ elif (command.lower() in ["deactivate"]):
+ embed.description = help_text["deactivate"]
+ elif (command.lower() in ["e", "eta"]):
+ embed.description = help_text["eta"]
+ elif (command.lower() in ["heatmap"]):
+ embed.description = help_text["heatmap"]
+ elif (command.lower() in ["h", "help"]):
+ embed.description = help_text["help"]
+ elif (command.lower() in ["l", "leaderboard"]):
+ embed.description = help_text["leaderboard"]
+ elif (command.lower() in ["ping"]):
+ embed.description = help_text["ping"]
+ elif (command.lower() in ["p", "progress"]):
+ embed.description = help_text["progress"]
+ elif (command.lower() in ["reload"]):
+ embed.description = help_text["reload"]
+ elif (command.lower() in ["s", "speed"]):
+ embed.description = help_text["speed"]
+ else:
+ raise CommandError(f"Command not found: `{command}`")
+
+ # Send embed
+ await ctx.send(embed=embed)
+
+
+
+ @commands.command()
+ async def ping(self, ctx):
+ """
+ Pings the bot
+ """
+
+ embed=discord.Embed(title=":ping_pong: Pong!", color=COLORS["embed"])
+ embed.description = f"**Latency:** {round(self.bot.latency * 1000)} ms\n"
+ await ctx.send(embed=embed)
diff --git a/countdown_bot/utilitiesCog.py b/countdown_bot/utilitiesCog.py
@@ -1,427 +0,0 @@
-# Import dependencies
-import discord
-from discord.ext import commands
-
-# Import modules
-from .botUtilities import COLORS, CommandError, CountdownNotFound, isCountdown, loadCountdown, getContextCountdown
-
-
-
-class Utilities(commands.Cog):
- def __init__(self, bot, db_connection):
- self.bot = bot
- self.db_connection = db_connection
- self.bot.remove_command("help")
-
-
-
- @commands.command()
- async def activate(self, ctx):
- """
- Turns a channel into a countdown
- """
-
- with self.db_connection.cursor() as cur:
- # Check if channel is already a countdown
- if (isCountdown(cur, ctx.channel.id)):
- raise CommandError("This channel is already a countdown")
-
- # Check if channel is a DM
- if (not isinstance(ctx.channel, discord.channel.TextChannel)):
- raise CommandError("This command must be run inside a server")
-
- # Check if user isn't authorized
- if (not ctx.message.author.guild_permissions.administrator):
- raise CommandError("You must be an administrator to turn a channel into a countdown")
-
- # Create countdown
- cur.execute("CALL createCountdown(%s, %s, %s);",
- (ctx.channel.id, ctx.channel.guild.id, self.bot.prefixes[0]))
-
- # Send initial response
- self.bot.logger.info(f"Activated {self.bot.get_channel(ctx.channel.id)} (ID {ctx.channel.id}) as a countdown")
- embed = discord.Embed(title=":clock3: Loading Countdown", description="This channel is now a countdown\nPlease wait to start counting", color=COLORS["embed"])
- msg = await ctx.send(embed=embed)
-
- # Load countdown
- await loadCountdown(self.bot, ctx.channel.id)
- self.db_connection.commit()
-
- # Send final response
- embed = discord.Embed(title=":white_check_mark: Countdown Activated", description="This channel is now a countdown\nYou may start counting!", color=COLORS["embed"])
- await msg.edit(embed=embed)
-
-
-
- @commands.command()
- async def config(self, ctx, key=None, *args):
- """
- Shows and modifies countdown settings
- """
-
- # Create embed
- embed = discord.Embed(title=":gear: Countdown Settings", color=COLORS["embed"])
-
- # Make sure context is in a server
- 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.db_connection.cursor() as cur:
- # Get countdown channel
- countdown = getContextCountdown(cur, ctx)
-
- if not countdown:
- raise CountdownNotFound()
-
- # Get / set settings
- if (key is None):
- embed.description = f"**Countdown Channel:** <#{countdown}>\n"
-
- cur.execute("SELECT * from getprefixes(null, %s);", (countdown,))
- prefixes = [x["prefix"] for x in cur.fetchall()]
- embed.description += f"**Command Prefixes:** `{'`, `'.join(prefixes)}`\n"
-
- cur.execute("CALL getTimezone(%s, null);", (countdown,))
- timezone = cur.fetchone()["_timezone"]
- if (timezone >= 0):
- embed.description += f"**Countdown Timezone:** UTC+{timezone:.2f}\n"
- else:
- embed.description += f"**Countdown Timezone:** UTC-{abs(timezone):.2f}\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 reversed(list(set([x["number"] for x in reactions]))):
- embed.description += f"**-** #{number}: {', '.join([x["value"] for x in reactions if x["number"] == 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:
- timezone = float(args[0])
- except:
- raise CommandError(f"Invalid timezone: `{args[0]}`")
- else:
- cur.execute("CALL setTimezone(%s, %s);", (countdown, timezone))
- if (timezone >= 0):
- embed.description = f"Timezone set to UTC+{timezone:.2f}\n"
- else:
- embed.description = f"Timezone set to UTC-{abs(timezone):.2f}\n"
- elif (key in ["prefix", "prefixes"]):
- cur.execute("CALL setPrefixes(%s, %s);", (countdown, list(args)))
- embed.description = f"Prefixes updated"
- elif (key in ["react"]):
- try:
- number = int(args[0])
- except:
- raise CommandError(f"Invalid number: `{args[0]}`")
- if (number < 0):
- raise CommandError("Number must be greater than zero")
- 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
- self.db_connection.commit()
-
- # Send embed
- await ctx.send(embed=embed)
-
-
-
- @commands.command()
- async def deactivate(self, ctx):
- """
- Deactivates a countdown channel
- """
-
- with self.db_connection.cursor() as cur:
- # Check if channel isn't a countdown
- if (not isCountdown(cur, ctx.channel.id)):
- raise CommandError("This channel isn't a countdown")
-
- # Check if user isn't authorized
- if (not ctx.author.guild_permissions.administrator):
- raise CommandError("You must be an administrator to deactivate a countdown channel")
-
- # Delete countdown
- cur.execute("CALL deleteCountdown(%s);",
- (ctx.channel.id,))
- self.db_connection.commit()
-
- # Send response
- self.bot.logger.info(f"Deactivated {self.bot.get_channel(ctx.channel.id)} (ID {ctx.channel.id}) as a countdown")
- embed = discord.Embed(title=":octagonal_sign: Countdown Deactivated", description="This channel is no longer a countdown", color=COLORS["embed"])
- await ctx.send(embed=embed)
-
-
-
- @commands.command(aliases=["h", ""])
- async def help(self, ctx, command=None):
- """
- Shows help information
- """
-
- # Initialize help information
- prefixes = await self.bot.get_prefix(ctx)
- help_text = {
- "prefixes":
- f"`{'`, `'.join(prefixes)}`",
- "utility-commands":
- "**-** `activate`: Turns a channel into a countdown\n" \
- "**-** `config`: Shows and modifies bot and countdown settings\n" \
- "**-** `deactivate`: Deactivates a countdown channel\n" \
- "**-** `help`, `h`: Shows help information\n" \
- "**-** `ping`: Pings the bot\n" \
- "**-** `reload`: Reloads the countdown cache\n",
- "analytics-commands":
- "**-** `analytics`, `a`: Shows all countdown analytics\n" \
- "**-** `contributors`, `c`: Shows information about countdown contributors\n" \
- "**-** `eta`, `e`: Shows information about the estimated completion date\n" \
- "**-** `heatmap`: Shows a heatmap of when messages are sent\n" \
- "**-** `leaderboard`, `l`: Shows the countdown leaderboard\n" \
- "**-** `progress`, `p`: Shows information about countdown progress\n" \
- "**-** `speed`, `s`: Shows information about countdown speed\n",
- "behavior":
- "**-** Reacts with :no_entry: when a user counts out of turn\n" \
- "**-** Reacts with :x: when a user counts incorrectly\n" \
- "**-** Ignores messages that don't start with a (positive) number\n" \
- "**-** Pins numbers every 2% if the countdown started at 500 or higher\n",
- "getting-started":
- f"**1.** View help information using the `{prefixes[0]}help` command\n" \
- f"**2.** Activate a new countdown channel using the `{prefixes[0]}activate` command\n" \
- f"**3.** Change my settings using the `{prefixes[0]}config` command\n" \
- f"**4.** View countdown analytics using the `{prefixes[0]}analytics` command\n",
- "troubleshooting":
- f"**1.** Run `{prefixes[0]}ping` to make sure that I'm online\n" \
- f"**2.** If I reacted incorrectly to a message, remove my incorrect reaction(s)\n" \
- f"**3.** Run `{prefixes[0]}reload` in the countdown channel\n",
- "activate":
- "**Name:** activate\n" \
- "**Description:** Turns a channel into a countdown\n" \
- f"**Usage:** `{prefixes[0]}activate`\n" \
- "**Aliases:** none\n" \
- "**Arguments:** none\n" \
- "**Examples:**\n" \
- f"**-** `{prefixes[0]}activate`\n" \
- "**Notes:** Users must have admin permissions to turn a channel into a countdown\n",
- "analytics":
- "**Name:** analytics\n" \
- "**Description:** Shows all countdown analytics\n" \
- f"**Usage:** `{prefixes[0]}analytics|a`\n" \
- "**Aliases:** `a`\n" \
- "**Arguments: none**\n" \
- "**Examples:**\n" \
- f"**-** `{prefixes[0]}analytics`\n" \
- "**Notes:** none\n",
- "config":
- "**Name:** config\n" \
- "**Description:** Shows and modifies countdown settings\n" \
- f"**Usage:** `{prefixes[0]}config [<key> <value>...]`\n" \
- "**Aliases:** none\n" \
- "**Arguments:**\n" \
- "**-** `<key>`: The name of the setting to modify. If no key is supplied, all settings will be shown\n" \
- "**-** `<value>`: The new value(s) for the setting\n" \
- "**Available Settings:**\n" \
- "**-** `prefix`, `prefixes`: The prefix(es) for the bot\n" \
- "**-** `tz`, `timezone`: The UTC offset in hours\n" \
- "**-** `react`: The reactions for a certain number\n" \
- "**Examples:**\n" \
- f"**-** `{prefixes[0]}config`\n" \
- f"**-** `{prefixes[0]}config prefixes prefix1 prefix2 prefix3`\n" \
- f"**-** `{prefixes[0]}config timezone -1.5`\n" \
- f"**-** `{prefixes[0]}config react 0 :partying_face: :smile:`\n" \
- "**Notes:** Users must have admin permissions to modify settings\n",
- "contributors":
- "**Name:** contributors\n" \
- "**Description:** Shows information about countdown contributors\n" \
- f"**Usage:** `{prefixes[0]}contributors|c [history|h]`\n" \
- "**Aliases:** `c`\n" \
- "**Arguments:**\n" \
- "**-** `history`, `h`: Shows historical data about countdown contributors\n" \
- "**Examples:**\n" \
- f"**-** `{prefixes[0]}contributors`\n" \
- f"**-** `{prefixes[0]}contributors history`\n" \
- "**Notes:** The contributors embed will only show the top 20 contributors\n",
- "deactivate":
- "**Name:** deactivate\n" \
- "**Description:** Deactivates a countdown channel\n" \
- f"**Usage:** `{prefixes[0]}deactivate`\n" \
- "**Aliases:** none\n" \
- "**Arguments:** none\n" \
- "**Examples:**\n" \
- f"**-** `{prefixes[0]}deactivate`\n" \
- "**Notes:** Users must have admin permissions to deactivate a countdown channel\n",
- "eta":
- "**Name:** eta\n" \
- "**Description:** Shows information about the estimated completion date\n" \
- f"**Usage:** `{prefixes[0]}eta|e`\n" \
- "**Aliases:** `e`\n" \
- "**Arguments:** none\n" \
- "**Examples:**\n" \
- f"**-** `{prefixes[0]}eta`\n" \
- "**Notes:** none\n",
- "heatmap":
- "**Name:** heatmap\n" \
- "**Description:** Shows a heatmap of when countdown messages are sent\n" \
- f"**Usage:** `{prefixes[0]}heatmap [<user>]`\n" \
- "**Aliases:** none\n" \
- "**Arguments:**\n" \
- "**-** `<user>`: The user to view heatmap information about. If no value is supplied, the general heatmap will be shown\n" \
- "**Examples:**\n" \
- f"**-** `{prefixes[0]}heatmap`\n" \
- f"**-** `{prefixes[0]}heatmap @Alice`\n" \
- "**Notes:** none\n",
- "help":
- "**Name:** help\n" \
- "**Description:** Shows help information\n" \
- f"**Usage:** `{prefixes[0]}help|h [<command>]`\n" \
- "**Aliases:** `h`\n" \
- "**Arguments:**\n" \
- "**-** `<command>`: The command to view help information about. If no value is supplied, general help information will be shown\n" \
- "**Examples:**\n" \
- f"**-** `{prefixes[0]}help`\n" \
- f"**-** `{prefixes[0]}help config`\n" \
- "**Notes:** none\n",
- "leaderboard":
- "**Name:** leaderboard\n" \
- "**Description:** Shows the countdown leaderboard\n" \
- f"**Usage:** `{prefixes[0]}leaderboard|l [<user>]`\n" \
- "**Aliases:** `l`\n" \
- "**Arguments:**\n" \
- "**-** `<user>`: The user to view leaderboard information about. If no value is supplied, the whole leaderboard will be shown\n" \
- "**Examples:**\n" \
- f"**-** `{prefixes[0]}leaderboard`\n" \
- f"**-** `{prefixes[0]}leaderboard @Alice`\n" \
- "**Notes:** The leaderboard embed will only show the top 20 contributors\n",
- "ping":
- "**Name:** ping\n" \
- "**Description:** Pings the bot\n" \
- f"**Usage:** `{prefixes[0]}ping`\n" \
- "**Aliases:** none\n" \
- "**Arguments:** none\n" \
- "**Examples:**\n" \
- f"**-** `{prefixes[0]}ping`\n" \
- "**Notes:** none\n",
- "progress":
- "**Name:** progress\n" \
- "**Description:** Shows information about countdown progress\n" \
- f"**Usage:** `{prefixes[0]}progress|p`\n" \
- "**Aliases:** `p`\n" \
- "**Arguments:** none\n" \
- "**Examples:**\n" \
- f"**-** `{prefixes[0]}progress`\n" \
- "**Notes:** none\n",
- "reload":
- "**Name:** reload\n" \
- "**Description:** Reloads the countdown cache\n" \
- f"**Usage:** `{prefixes[0]}reload`\n" \
- "**Aliases:** none\n" \
- "**Arguments:** none\n" \
- "**Examples:**\n" \
- f"**-** `{prefixes[0]}reload`\n" \
- "**Notes:** This command must be used in a countdown channel\n",
- "speed":
- "**Name:** speed\n" \
- "**Description:** Shows information about countdown speed\n" \
- f"**Usage:** `{prefixes[0]}speed|s [<period>]`\n" \
- "**Aliases:** `s`\n" \
- "**Arguments:**\n" \
- "**-** `<period>`: The size of the period in hours (the default is 24 hours)\n" \
- "**Examples:**\n" \
- f"**-** `{prefixes[0]}speed`\n" \
- f"**-** `{prefixes[0]}speed 48`\n" \
- "**Notes:** none\n",
- }
-
- # Create embed
- embed=discord.Embed(title=":grey_question: countdown-bot Help", color=COLORS["embed"])
- if (command is None):
- embed.add_field(name="Command Prefixes :gear:", value=help_text["prefixes"], inline=False)
- embed.add_field(name="Utility Commands :wrench:", value=help_text["utility-commands"], inline=False)
- embed.add_field(name="Analytics Commands :bar_chart:", value=help_text["analytics-commands"], inline=False)
- embed.add_field(name="Behavior in Countdown Channels :robot:", value=help_text["behavior"], inline=False)
- embed.add_field(name="Getting Started :rocket:", value=help_text["getting-started"], inline=False)
- embed.add_field(name="Troubleshooting :screwdriver:", value=help_text["troubleshooting"], inline=False)
- embed.description = f"Use `{prefixes[0]}help command` to get more info on a command"
- elif (command.lower() in ["activate"]):
- embed.description = help_text["activate"]
- elif (command.lower() in ["a", "analytics"]):
- embed.description = help_text["analytics"]
- elif (command.lower() in ["config"]):
- embed.description = help_text["config"]
- elif (command.lower() in ["c", "contributors"]):
- embed.description = help_text["contributors"]
- elif (command.lower() in ["deactivate"]):
- embed.description = help_text["deactivate"]
- elif (command.lower() in ["e", "eta"]):
- embed.description = help_text["eta"]
- elif (command.lower() in ["heatmap"]):
- embed.description = help_text["heatmap"]
- elif (command.lower() in ["h", "help"]):
- embed.description = help_text["help"]
- elif (command.lower() in ["l", "leaderboard"]):
- embed.description = help_text["leaderboard"]
- elif (command.lower() in ["ping"]):
- embed.description = help_text["ping"]
- elif (command.lower() in ["p", "progress"]):
- embed.description = help_text["progress"]
- elif (command.lower() in ["reload"]):
- embed.description = help_text["reload"]
- elif (command.lower() in ["s", "speed"]):
- embed.description = help_text["speed"]
- else:
- raise CommandError(f"Command not found: `{command}`")
-
- # Send embed
- await ctx.send(embed=embed)
-
-
-
- @commands.command()
- async def ping(self, ctx):
- """
- Pings the bot
- """
-
- embed=discord.Embed(title=":ping_pong: Pong!", color=COLORS["embed"])
- embed.description = f"**Latency:** {round(self.bot.latency * 1000)} ms\n"
- await ctx.send(embed=embed)
-
-
-
- @commands.command()
- async def reload(self, ctx):
- """
- Reloads the countdown cache
- """
-
- with self.db_connection.cursor() as cur:
- # Check if channel isn't a countdown
- if (not isCountdown(cur, ctx.channel.id)):
- raise CommandError("Countdown not found\nThis command must be used in a countdown channel")
-
- # Send initial response
- embed = discord.Embed(title=":clock3: Reloading Countdown Cache", description="Please wait to continue counting", color=COLORS["embed"])
- msg = await ctx.channel.send(embed=embed)
-
- # Reload messages
- await loadCountdown(self.bot, ctx.channel.id)
- self.db_connection.commit()
-
- # Send final response
- self.bot.logger.info(f"Reloaded messages from {self.bot.get_channel(ctx.channel.id)} (ID {ctx.channel.id})")
- embed = discord.Embed(title=":white_check_mark: Countdown Cache Reloaded", description="Done! You may continue counting!", color=COLORS["embed"])
- await msg.edit(embed=embed)
diff --git a/models/tables.sql b/models/ddl.sql
diff --git a/models/analytics.sql b/models/dml-analytics.sql
diff --git a/models/dml-core.sql b/models/dml-core.sql
@@ -0,0 +1,233 @@
+-- countdown-bot core functions and procedures
+
+DROP PROCEDURE IF EXISTS setTimezone;
+DROP PROCEDURE IF EXISTS getTimezone;
+DROP PROCEDURE IF EXISTS setReactions;
+DROP FUNCTION IF EXISTS getReactions;
+DROP PROCEDURE IF EXISTS addMessage;
+DROP TYPE IF EXISTS addMessageResults;
+DROP PROCEDURE IF EXISTS deleteCountdown;
+DROP PROCEDURE IF EXISTS createCountdown;
+DROP PROCEDURE IF EXISTS clearCountdown;
+DROP PROCEDURE IF EXISTS setPrefixes;
+DROP FUNCTION IF EXISTS getPrefixes;
+
+-- Get the active prefixes for a countdown channel
+CREATE FUNCTION getPrefixes (
+ _countdownID BIGINT -- The countdown channel ID
+)
+RETURNS TABLE (
+ prefix VARCHAR(8) -- An active prefix
+)
+LANGUAGE plpgsql AS $$
+BEGIN
+ RETURN QUERY
+ SELECT value
+ FROM prefixes
+ WHERE prefixes.countdownID = _countdownID;
+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
+$$;
+
+-- Delete all messages in a countdown
+CREATE PROCEDURE clearCountdown (
+ _countdownID IN BIGINT -- The countdown channel ID
+)
+LANGUAGE plpgsql AS $$
+BEGIN
+ DELETE
+ FROM messages
+ WHERE countdownID = _countdownID;
+END
+$$;
+
+-- Create a new countdown
+CREATE PROCEDURE createCountdown (
+ _countdownID IN BIGINT, -- The countdown channel ID
+ _serverID IN BIGINT, -- The server ID
+ prefix IN VARCHAR(8) -- The initial prefix
+)
+LANGUAGE plpgsql AS $$
+BEGIN
+ INSERT INTO countdowns (countdownID, serverID)
+ VALUES (_countdownID, _serverID);
+ INSERT INTO prefixes (countdownID, value)
+ VALUES (_countdownID, prefix);
+END
+$$;
+
+-- Delete a countdown
+CREATE PROCEDURE deleteCountdown (
+ _countdownID IN BIGINT -- The countdown channel ID
+)
+LANGUAGE plpgsql AS $$
+BEGIN
+ DELETE
+ FROM countdowns
+ WHERE countdownID = _countdownID;
+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;
+
+ -- Initialize pin and reactions
+ pin := FALSE;
+ reactions := FALSE;
+
+ -- 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;
+ END IF;
+
+ -- Check if message has custom reactions
+ IF EXISTS(SELECT 1 FROM reactions
+ WHERE countdownID = _countdownID AND number = _value
+ ) THEN
+ reactions := TRUE;
+ 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 (or NULL for all numbers)
+)
+RETURNS TABLE (
+ value VARCHAR(8), -- A custom reaction
+ number INT -- The number
+)
+LANGUAGE plpgsql AS $$
+BEGIN
+ RETURN QUERY
+ 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
+$$;
+
+-- Get the timezone of a countdown
+CREATE PROCEDURE getTimezone (
+ _countdownID IN BIGINT, -- The countdown channel ID
+ _timezone OUT DECIMAL -- The timezone as a UTC offest
+)
+LANGUAGE plpgsql AS $$
+BEGIN
+ SELECT extract(minute FROM timezone) / 60
+ INTO _timezone
+ FROM countdowns
+ WHERE countdownID = _countdownID;
+END
+$$;
+
+-- Set the timezone of a countdown
+CREATE PROCEDURE setTimezone (
+ _countdownID IN BIGINT, -- The countdown channel ID
+ _timezone IN FLOAT -- The timezone as a UTC offest
+)
+LANGUAGE plpgsql AS $$
+BEGIN
+ UPDATE countdowns
+ SET timezone = make_interval(0,0,0,0, floor(_timezone)::integer,
+ (_timezone * 60)::integer % 60)
+ WHERE countdownID = _countdownID;
+END
+$$;
diff --git a/models/dml-utils.sql b/models/dml-utils.sql
@@ -0,0 +1,103 @@
+-- countdown-bot utility functions and procedures
+
+DROP PROCEDURE IF EXISTS isCountdown;
+DROP PROCEDURE IF EXISTS getUserContextCountdown;
+DROP PROCEDURE IF EXISTS getServerContextCountdown;
+DROP FUNCTION IF EXISTS getServerPrefixes;
+
+-- Get the active prefixes for a server
+CREATE FUNCTION getServerPrefixes (
+ _serverID BIGINT, -- The server ID
+ channelID BIGINT -- The channel ID
+)
+RETURNS TABLE (
+ prefix VARCHAR(8) -- An active prefix
+)
+LANGUAGE plpgsql AS $$
+BEGIN
+ IF EXISTS(
+ SELECT 1
+ FROM countdowns
+ WHERE countdownID = channelID
+ ) THEN
+ -- Filter prefixes if channel is a countdown
+ RETURN QUERY
+ SELECT value
+ FROM prefixes
+ WHERE prefixes.countdownID = channelID;
+ ELSE
+ -- Return all server prefixes if channel is not a countdown
+ RETURN QUERY
+ SELECT DISTINCT value
+ FROM prefixes
+ JOIN countdowns ON countdowns.countdownID = prefixes.countdownID
+ WHERE countdowns.serverID = _serverID;
+ END IF;
+END
+$$;
+
+-- Get the most relevant countdown to a server channel
+CREATE PROCEDURE getServerContextCountdown (
+ _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
+ -- Check if the channel is a countdown
+ SELECT countdowns.countdownID
+ INTO countdownID
+ FROM countdowns
+ WHERE countdowns.countdownID = channelID
+
+ UNION ALL
+ (
+ -- Get server countdowns by prefix sorted by most recent activity
+ SELECT countdowns.countdownID
+ FROM countdowns
+ LEFT OUTER JOIN messages
+ ON messages.countdownID = countdowns.countdownID
+ JOIN prefixes
+ ON prefixes.countdownID = countdowns.countdownID
+ WHERE serverID = _serverID AND prefixes.value = prefix
+ GROUP BY countdowns.countdownID
+ ORDER BY max(messages.timestamp) DESC NULLS LAST
+ )
+ LIMIT 1;
+END
+$$;
+
+-- Get the most relevant countdown to a user
+CREATE PROCEDURE getUserContextCountdown (
+ _userID IN BIGINT, -- The user ID
+ countdownID OUT BIGINT -- The ID of the most relevant countdown
+)
+LANGUAGE plpgsql AS $$
+BEGIN
+ -- Get user countdowns sorted by most recent activity
+ SELECT countdowns.countdownID
+ INTO countdownID
+ FROM countdowns
+ LEFT OUTER JOIN messages ON messages.countdownID = countdowns.countdownID
+ WHERE userID = _userID
+ GROUP BY countdowns.countdownID
+ ORDER BY max(messages.timestamp) DESC NULLS LAST
+ LIMIT 1;
+END
+$$;
+
+-- Determine if a channel is a countdown
+CREATE PROCEDURE isCountdown (
+ channelID IN BIGINT, -- The channel ID
+ result OUT BOOLEAN -- Whether the channel is a countdown
+)
+LANGUAGE plpgsql AS $$
+BEGIN
+ SELECT EXISTS(
+ SELECT 1
+ FROM countdowns
+ WHERE countdownID = channelID
+ ) INTO result;
+END
+$$;
diff --git a/models/utilities.sql b/models/utilities.sql
@@ -1,317 +0,0 @@
--- countdown-bot utility procedures
-
-DROP PROCEDURE IF EXISTS setTimezone;
-DROP PROCEDURE IF EXISTS getTimezone;
-DROP PROCEDURE IF EXISTS setReactions;
-DROP FUNCTION IF EXISTS getReactions;
-DROP PROCEDURE IF EXISTS addMessage;
-DROP TYPE IF EXISTS addMessageResults;
-DROP PROCEDURE IF EXISTS deleteCountdown;
-DROP PROCEDURE IF EXISTS createCountdown;
-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
-CREATE FUNCTION getPrefixes (
- _serverID BIGINT, -- The server ID
- channelID BIGINT -- The channel ID
-)
-RETURNS TABLE (
- prefix VARCHAR(8) -- An active prefix
-)
-LANGUAGE plpgsql AS $$
-BEGIN
- IF EXISTS(
- SELECT 1
- FROM countdowns
- WHERE countdownID = channelID
- ) THEN
- -- Filter prefixes if channel is a countdown
- RETURN QUERY
- SELECT value
- FROM prefixes
- WHERE prefixes.countdownID = channelID;
- ELSE
- -- Return all server prefixes if channel is not a countdown
- RETURN QUERY
- SELECT DISTINCT value
- FROM prefixes
- JOIN countdowns ON countdowns.countdownID = prefixes.countdownID
- WHERE countdowns.serverID = _serverID;
- END IF;
-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
- 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
- -- Check if the channel is a countdown
- SELECT countdowns.countdownID
- INTO countdownID
- FROM countdowns
- WHERE countdowns.countdownID = channelID
-
- UNION ALL
- (
- -- Get server countdowns by prefix sorted by most recent activity
- SELECT countdowns.countdownID
- FROM countdowns
- LEFT OUTER JOIN messages
- ON messages.countdownID = countdowns.countdownID
- JOIN prefixes
- ON prefixes.countdownID = countdowns.countdownID
- WHERE serverID = _serverID AND prefixes.value = prefix
- GROUP BY countdowns.countdownID
- ORDER BY max(messages.timestamp) DESC NULLS LAST
- )
- LIMIT 1;
-END
-$$;
-
--- Get the most relevant countdown to a user
-CREATE PROCEDURE getUserContextCountdown (
- _userID IN BIGINT, -- The user ID
- countdownID OUT BIGINT -- The ID of the most relevant countdown
-)
-LANGUAGE plpgsql AS $$
-BEGIN
- -- Get user countdowns sorted by most recent activity
- SELECT countdowns.countdownID
- INTO countdownID
- FROM countdowns
- LEFT OUTER JOIN messages ON messages.countdownID = countdowns.countdownID
- WHERE userID = _userID
- GROUP BY countdowns.countdownID
- ORDER BY max(messages.timestamp) DESC NULLS LAST
- LIMIT 1;
-END
-$$;
-
--- Determine if a channel is a countdown
-CREATE PROCEDURE isCountdown (
- channelID IN BIGINT, -- The channel ID
- result OUT BOOLEAN -- Whether the channel is a countdown
-)
-LANGUAGE plpgsql AS $$
-BEGIN
- SELECT EXISTS(
- SELECT 1
- FROM countdowns
- WHERE countdownID = channelID
- ) INTO result;
-END
-$$;
-
--- Delete all messages in a countdown
-CREATE PROCEDURE clearCountdown (
- _countdownID IN BIGINT -- The countdown channel ID
-)
-LANGUAGE plpgsql AS $$
-BEGIN
- DELETE
- FROM messages
- WHERE countdownID = _countdownID;
-END
-$$;
-
--- Create a new countdown
-CREATE PROCEDURE createCountdown (
- _countdownID IN BIGINT, -- The countdown channel ID
- _serverID IN BIGINT, -- The server ID
- prefix IN VARCHAR(8) -- The initial prefix
-)
-LANGUAGE plpgsql AS $$
-BEGIN
- INSERT INTO countdowns (countdownID, serverID)
- VALUES (_countdownID, _serverID);
- INSERT INTO prefixes (countdownID, value)
- VALUES (_countdownID, prefix);
-END
-$$;
-
--- Delete a countdown
-CREATE PROCEDURE deleteCountdown (
- _countdownID IN BIGINT -- The countdown channel ID
-)
-LANGUAGE plpgsql AS $$
-BEGIN
- DELETE
- FROM countdowns
- WHERE countdownID = _countdownID;
-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;
-
- -- Initialize pin and reactions
- pin := FALSE;
- reactions := FALSE;
-
- -- 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;
- END IF;
-
- -- Check if message has custom reactions
- IF EXISTS(SELECT 1 FROM reactions
- WHERE countdownID = _countdownID AND number = _value
- ) THEN
- reactions := TRUE;
- 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 (or NULL for all numbers)
-)
-RETURNS TABLE (
- value VARCHAR(8), -- A custom reaction
- number INT -- The number
-)
-LANGUAGE plpgsql AS $$
-BEGIN
- RETURN QUERY
- 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
-$$;
-
--- Get the timezone of a countdown
-CREATE PROCEDURE getTimezone (
- _countdownID IN BIGINT, -- The countdown channel ID
- _timezone OUT DECIMAL -- The timezone as a UTC offest
-)
-LANGUAGE plpgsql AS $$
-BEGIN
- SELECT extract(minute FROM timezone) / 60
- INTO _timezone
- FROM countdowns
- WHERE countdownID = _countdownID;
-END
-$$;
-
--- Set the timezone of a countdown
-CREATE PROCEDURE setTimezone (
- _countdownID IN BIGINT, -- The countdown channel ID
- _timezone IN FLOAT -- The timezone as a UTC offest
-)
-LANGUAGE plpgsql AS $$
-BEGIN
- UPDATE countdowns
- SET timezone = make_interval(0,0,0,0, floor(_timezone)::integer,
- (_timezone * 60)::integer % 60)
- WHERE countdownID = _countdownID;
-END
-$$;
