async def _tag_all_text_mode(self, ctx):
query = """SELECT tag_lookup.id,
tag_lookup.name,
tag_lookup.owner_id,
tags.uses,
$2 OR $3 = tag_lookup.owner_id AS "can_delete",
LOWER(tag_lookup.name) <> LOWER(tags.name) AS "is_alias"
FROM tag_lookup
INNER JOIN tags ON tags.id = tag_lookup.tag_id
WHERE tag_lookup.location_id=$1
ORDER BY tags.uses DESC;
"""
bypass_owner_check = (ctx.author.id == self.bot.owner_id
or ctx.author.guild_permissions.manage_messages)
rows = await ctx.db.fetch(query, ctx.guild.id, bypass_owner_check,
ctx.author.id)
if not rows:
return await ctx.send("This server has no server-specific tags.")
table = formats.TabularData()
table.set_columns(list(rows[0].keys()))
table.add_rows(list(r.values()) for r in rows)
fp = io.BytesIO(table.render().encode("utf-8"))
await ctx.send(file=discord.File(fp, "tags.txt"))
async def command_history_log(self, ctx, days=7):
"""Command history log for the last N days."""
query = """SELECT command, COUNT(*)
FROM commands
WHERE used > (CURRENT_TIMESTAMP - $1::interval)
GROUP BY command
ORDER BY 2 DESC
"""
all_commands = {c.qualified_name: 0 for c in self.bot.walk_commands()}
records = await ctx.db.fetch(query, datetime.timedelta(days=days))
for name, uses in records:
if name in all_commands:
all_commands[name] = uses
as_data = sorted(all_commands.items(),
key=lambda t: t[1],
reverse=True)
table = formats.TabularData()
table.set_columns(["Command", "Uses"])
table.add_rows(tup for tup in as_data)
render = table.render()
embed = discord.Embed(title="Summary", colour=discord.Colour.green())
embed.set_footer(text="Since").timestamp = datetime.datetime.utcnow(
) - datetime.timedelta(days=days)
top_ten = "\n".join(f"{command}: {uses}"
for command, uses in records[:10])
bottom_ten = "\n".join(f"{command}: {uses}"
for command, uses in records[-10:])
embed.add_field(name="Top 10", value=top_ten)
embed.add_field(name="Bottom 10", value=bottom_ten)
unused = ", ".join(name for name, uses in as_data if uses == 0)
if len(unused) > 1024:
unused = "Way too many..."
embed.add_field(name="Unused", value=unused, inline=False)
await ctx.send(
embed=embed,
file=discord.File(io.BytesIO(render.encode()),
filename="full_results.txt"),
)
async def tabulate_query(self, ctx, query, *args):
records = await ctx.db.fetch(query, *args)
if len(records) == 0:
return await ctx.send("No results found.")
headers = list(records[0].keys())
table = formats.TabularData()
table.set_columns(headers)
table.add_rows(list(r.values()) for r in records)
render = table.render()
fmt = f"```\n{render}\n```"
if len(fmt) > 2000:
fp = io.BytesIO(fmt.encode("utf-8"))
await ctx.send("Too many results...",
file=discord.File(fp, "results.txt"))
else:
await ctx.send(fmt)
async def sql_table(self, ctx: Context, *, table_name: str) -> None:
"""Runs a query describing the table schema."""
query = """SELECT column_name, data_type, column_default, is_nullable
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = $1
"""
results = await ctx.db.fetch(query, table_name)
headers = list(results[0].keys())
table = formats.TabularData()
table.set_columns(headers)
table.add_rows(list(r.values()) for r in results)
render = table.render()
fmt = f"```\n{render}\n```"
if len(fmt) > 2000:
filep = io.BytesIO(fmt.encode("utf-8"))
await ctx.send("Too many results...", file=discord.File(filep, "results.txt"))
else:
await ctx.send(fmt)
async def sql(self, ctx: Context, *,
query: str) -> Optional[discord.Message]:
"""Run some SQL."""
query = self.cleanup_code(query)
is_multistatement = query.count(";") > 1
if is_multistatement:
# fetch does not support multiple statements
strategy = ctx.db.execute
else:
strategy = ctx.db.fetch
try:
start = time.perf_counter()
results = await strategy(query)
dati = (time.perf_counter() - start) * 1000.0
except Exception:
return await ctx.send(f"```py\n{traceback.format_exc()}\n```")
rows = len(results)
if is_multistatement or rows == 0:
return await ctx.send(f"`{dati:.2f}ms: {results}`")
headers = list(results[0].keys())
table = formats.TabularData()
table.set_columns(headers)
table.add_rows(list(r.values()) for r in results)
render = table.render()
fmt = (
f"```\n{render}\n```\n*Returned {formats.plural(rows):row} in {dati:.2f}ms*"
)
if len(fmt) > 2000:
filep = io.BytesIO(fmt.encode("utf-8"))
await ctx.send("Too many results...",
file=discord.File(filep, "results.txt"))
else:
await ctx.send(fmt)
async def command_history_cog(self,
ctx,
days: typing.Optional[int] = 7,
*,
cog: str = None):
"""Command history for a cog or grouped by a cog."""
interval = datetime.timedelta(days=days)
if cog is not None:
cog = self.bot.get_cog(cog)
if cog is None:
return await ctx.send(f"Unknown cog: {cog}")
query = """SELECT *, t.success + t.failed AS "total"
FROM (
SELECT command,
SUM(CASE WHEN failed THEN 0 ELSE 1 END) AS "success",
SUM(CASE WHEN failed THEN 1 ELSE 0 END) AS "failed"
FROM commands
WHERE command = any($1::text[])
AND used > (CURRENT_TIMESTAMP - $2::interval)
GROUP BY command
) AS t
ORDER BY "total" DESC
LIMIT 30;
"""
return await self.tabulate_query(
ctx, query, [c.qualified_name for c in cog.walk_commands()],
interval)
# A more manual query with a manual grouper.
query = """SELECT *, t.success + t.failed AS "total"
FROM (
SELECT command,
SUM(CASE WHEN failed THEN 0 ELSE 1 END) AS "success",
SUM(CASE WHEN failed THEN 1 ELSE 0 END) AS "failed"
FROM commands
WHERE used > (CURRENT_TIMESTAMP - $1::interval)
GROUP BY command
) AS t;
"""
class Count:
__slots__ = ("success", "failed", "total")
def __init__(self):
self.success = 0
self.failed = 0
self.total = 0
def add(self, record):
self.success += record["success"]
self.failed += record["failed"]
self.total += record["total"]
data = defaultdict(Count)
records = await ctx.db.fetch(query, interval)
for record in records:
command = self.bot.get_command(record["command"])
if command is None or command.cog is None:
data["No Cog"].add(record)
else:
data[command.cog.qualified_name].add(record)
table = formats.TabularData()
table.set_columns(["Cog", "Success", "Failed", "Total"])
data = sorted(
[(cog, e.success, e.failed, e.total) for cog, e in data.items()],
key=lambda t: t[-1],
reverse=True,
)
table.add_rows(data)
render = table.render()
await ctx.safe_send(f"```\n{render}\n```")