async def sql(self, ctx, *, query: str):
"""Run some SQL."""
# the imports are here because I imagine some people would want to use
# this cog as a base for their other cog, and since this one is kinda
# odd and unnecessary for most people, I will make it easy to remove
# for those people.
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)
dt = (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'`{dt:.2f}ms: {results}`')
headers = list(results[0].keys())
table = 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 {rows} rows in {dt:.2f}ms*'
return await self.safe_send(ctx, fmt)
async def sql_indexes(self, ctx, *, table: str):
query = """SELECT tablename, indexname FROM pg_indexes WHERE tablename = $1;"""
results = await ctx.db.fetch(query, table)
headers = list(results[0].keys())
table = TabularData()
table.set_columns(headers)
table.add_rows(list(r.values()) for r in results)
render = table.render()
fmt = f'```\n{render}\n```'
await self.safe_send(ctx, fmt)
async def sql_tables(self, ctx):
query = """SELECT table_name
FROM information_schema.tables
WHERE table_schema='public'
AND table_type='BASE TABLE';
"""
results = await ctx.db.fetch(query)
headers = list(results[0].keys())
table = TabularData()
table.set_columns(headers)
table.add_rows(list(r.values()) for r in results)
render = table.render()
fmt = f'```\n{render}\n```'
return await self.safe_send(ctx, fmt)
async def sql_table(self, ctx, *, table_name: str):
"""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 = TabularData()
table.set_columns(headers)
table.add_rows(list(r.values()) for r in results)
render = table.render()
fmt = f'```\n{render}\n```'
return await self.safe_send(ctx, fmt)
async def sql_constraints(self, ctx, *, table: str):
query = """SELECT conname
FROM pg_constraint
WHERE conrelid =
(SELECT oid
FROM pg_class
WHERE relname LIKE $1
);
"""
results = await ctx.db.fetch(query, table)
headers = list(results[0].keys())
table = TabularData()
table.set_columns(headers)
table.add_rows(list(r.values()) for r in results)
render = table.render()
fmt = f'```\n{render}\n```'
await self.safe_send(ctx, fmt)
async def sql(self, ctx, *, query: str):
"""Run some SQL."""
results, timer = await self.execute_sql(ctx, query)
if not timer:
return await self.safe_send(ctx, results)
rows = len(results)
if rows == 0:
return await ctx.send(f'`{timer:.2f}ms: {results}`')
headers = list(results[0].keys())
table = 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 {rows} rows in {timer:.2f}ms*'
return await self.safe_send(ctx, fmt)
async def info_season(self, ctx):
"""Get Season IDs and start/finish times and info."""
query = "SELECT id, start, finish FROM seasons ORDER BY id DESC"
fetch = await ctx.db.fetch(query)
table = TabularData()
table.set_columns(['ID', 'Start', 'Finish'])
for n in fetch:
table.add_row([n[0], n[1].strftime('%d-%b-%Y'), n[2].strftime('%d-%b-%Y')])
e = discord.Embed(colour=self.bot.colour,
description=f'```\n{table.render()}\n```',
title='Season Info',
timestamp=datetime.utcnow()
)
e.add_field(name='Current Season',
value=readable_time((fetch[0][2] - datetime.utcnow()).total_seconds())[:-4] + ' left',
inline=False)
await ctx.send(embed=e)
async def info_events(self, ctx):
"""GET Event IDs and start/finish times for events."""
if not await self.bot.is_owner(ctx.author):
query = "SELECT id, event_name, start, finish FROM events WHERE guild_id = $1 ORDER BY start DESC"
fetch = await ctx.db.fetch(query, ctx.guild.id)
else:
query = "SELECT id, event_name, start, finish FROM events ORDER BY start DESC"
fetch = await ctx.db.fetch(query)
table = TabularData()
table.set_columns(['ID', 'Name', 'Start', 'Finish'])
for n in fetch:
table.add_row([n[0], n[1], n[2].strftime('%d-%b-%Y'), n[3].strftime('%d-%b-%Y')])
e = discord.Embed(colour=self.bot.colour,
description=f'```\n{table.render()}\n```',
title='Event Info',
timestamp=datetime.utcnow()
)
await ctx.send(embed=e)