async def remove_expired_revenges(cls):
now = datetime.now().timestamp()
async with aiosqlite.connect(Database.connection_string()) as db:
await db.execute(
'UPDATE Scores SET Revenge = ?, RevengeTime = ? WHERE RevengeTime < ?',
(None, None, now))
await db.commit()
async def get_random(cls):
async with aiosqlite.connect(Database.connection_string()) as db:
db.row_factory = aiosqlite.Row
async with db.execute('SELECT * FROM CarePackageRwds ORDER BY RANDOM() LIMIT 1') as cursor:
row = await cursor.fetchone()
if row:
return cls(row['id'], row['Name'], row['Description'])
async def from_database(cls, id: int):
async with aiosqlite.connect(Database.connection_string()) as db:
db.row_factory = aiosqlite.Row
async with db.execute('SELECT * FROM CarePackageRwds WHERE id = ?', (id,)) as cursor:
row = await cursor.fetchone()
if row:
return cls(row['id'], row['Name'], row['Description'])
async def remove_user(cls, id, guild):
async with aiosqlite.connect(Database.connection_string()) as db:
await db.execute(
'DELETE FROM Scores WHERE UserID = ? AND Guild = ?',
(id, guild))
await db.execute(
'DELETE FROM SnipingMods WHERE UserID = ? AND Guild = ?',
(id, guild))
await db.commit()
async def register_self(self):
async with aiosqlite.connect(Database.connection_string()) as db:
await db.execute(
'INSERT OR IGNORE INTO Scores(UserID, Guild, Name) VALUES (?, ?, ?)',
(self.id, self.guild, self.display_name))
await db.execute(
'INSERT OR IGNORE INTO SnipingMods(UserID, Guild, Name) VALUES (?, ?, ?)',
(self.id, self.guild, self.display_name))
await db.commit()
async def _load_killstreak_record(self):
async with aiosqlite.connect(Database.connection_string()) as db:
db.row_factory = aiosqlite.Row
query = 'SELECT Name, KillstreakRecord FROM Scores ORDER BY KillstreakRecord DESC LIMIT 1'
async with db.execute(query) as cursor:
row = await cursor.fetchone()
if row:
self.killstreak_record_holder = row['Name'][0:8]
self.killstreak_record = row['KillstreakRecord']
async def get_all(cls, guild):
async with aiosqlite.connect(Database.connection_string()) as db:
db.row_factory = aiosqlite.Row
async with db.execute('SELECT * FROM CarePackage WHERE Guild = ?',
(guild, )) as cursor:
rows = await cursor.fetchall()
carepackages = [
Package(row['Guild'], row['Key'], row['Expiration'],
row['Hint']) for row in rows
]
return carepackages
async def get_carepackage_rewards(self, ctx: commands.Context):
rewards = []
async with aiosqlite.connect(Database.connection_string()) as db:
async with db.execute(
'SELECT Name, Description FROM CarePackageRwds') as cursor:
rewards = await cursor.fetchall()
sendingStr = ''
for reward in rewards:
sendingStr += f'{reward[0]}\n\t\u2192 {reward[1]}\n'
await ctx.send('```' + sendingStr + '```')
async def get_respawns(cls):
async with aiosqlite.connect(Database.connection_string()) as db:
date = datetime.now().timestamp()
async with db.execute(
'SELECT Guild, UserID FROM Scores WHERE Respawn < ?',
(date, )) as cursor:
rows = await cursor.fetchall()
await db.execute(
'UPDATE Scores SET Respawn = ? WHERE Respawn < ?',
(None, date))
await db.commit()
return rows
async def get_all(cls, guild):
async with aiosqlite.connect(Database.connection_string()) as db:
db.row_factory = aiosqlite.Row
async with db.execute('SELECT * FROM Soapbox WHERE Guild = ? ORDER BY Date ASC', (guild,)) as cursor:
rows = await cursor.fetchall()
soapbox_entries = []
for row in rows:
soapbox_entry = cls(id=row['ID'], name=row['Presenter'], date=datetime.fromtimestamp(
row['Date']), topic=row['Topic'])
soapbox_entries.append(soapbox_entry)
return soapbox_entries
async def exists(cls, id, guild):
async with aiosqlite.connect(Database.connection_string()) as db:
async with db.execute(
'SELECT 1 FROM Scores WHERE UserID = ? AND Guild = ?',
(id, guild)) as cursor:
row = await cursor.fetchone()
if row is not None:
return True
async with db.execute(
'SELECT 1 FROM SnipingMods WHERE UserID = ? AND Guild = ?',
(id, guild)) as cursor:
row = await cursor.fetchone()
return row is not None
async def update(self):
scores_info = (self.display_name, self.points, self.snipes,
self.deaths, self.respawn, self.revenge,
self.revenge_time, self.killstreak,
self.killstreak_record, self.id, self.guild)
snipingmods_info = (self.display_name, self.multiplier,
self.multi_expiration, self.smokebomb,
self.immunity, self.id, self.guild)
scores_query = 'UPDATE Scores SET Name = ?, Points = ?, Snipes = ?, Deaths = ?, Respawn = ?, Revenge = ?, RevengeTime = ?, Killstreak = ?, KillstreakRecord = ? WHERE UserID = ? AND Guild = ?'
snipingmods_query = 'UPDATE SnipingMods SET Name = ?, Multiplier = ?, MultiExpiration = ?, SmokeBomb = ?, Immunity = ? WHERE UserID = ? AND Guild = ?'
async with aiosqlite.connect(Database.connection_string()) as db:
await db.execute(scores_query, scores_info)
await db.execute(snipingmods_query, snipingmods_info)
await db.commit()
async def remove_expired(cls):
now = datetime.now().timestamp()
async with aiosqlite.connect(Database.connection_string()) as db:
db.row_factory = aiosqlite.Row
async with db.execute(
'SELECT Guild, Key FROM CarePackage WHERE Expiration < ?',
(now, )) as cursor:
rows = await cursor.fetchall()
if len(rows) > 0:
await db.execute(
'DELETE FROM CarePackage WHERE Expiration < ?', (now, ))
await db.commit()
return rows
async def is_keyword(cls, guess, guild):
async with aiosqlite.connect(Database.connection_string()) as db:
async with db.execute(
'SELECT 1 FROM CarePackage WHERE Key = ? AND Guild = ?',
(guess, guild)) as cursor:
row = await cursor.fetchone()
if row is None:
return False
await db.execute(
'DELETE FROM CarePackage WHERE Key = ? AND Guild = ?',
(guess, guild))
await db.commit()
return True
async def get_expired_immunes(cls):
now = datetime.now().timestamp()
async with aiosqlite.connect(Database.connection_string()) as db:
db.row_factory = aiosqlite.Row
async with db.execute(
'SELECT Guild, UserID FROM SnipingMods WHERE Immunity < ?',
(now, )) as cursor:
rows = await cursor.fetchall()
if len(rows) > 0:
await db.execute(
'UPDATE SnipingMods SET Immunity = ? WHERE Immunity < ?', (
None,
now,
))
await db.commit()
return rows
async def from_database(cls, id: int):
instance = cls()
instance.id = id
async with aiosqlite.connect(Database.connection_string()) as db:
db.row_factory = aiosqlite.Row
async with db.execute('SELECT * FROM Soapbox WHERE id = ?', (id,)) as cursor:
row = await cursor.fetchone()
if row is None:
return None
instance.name = row['Presenter']
instance.date = datetime.fromtimestamp(row['Date'])
instance.topic = row['Topic']
instance.guild = row['Guild']
return instance
async def get_exploded_potatoes(cls):
pointDeduction = 3
now = datetime.now().timestamp()
async with aiosqlite.connect(Database.connection_string()) as db:
db.row_factory = aiosqlite.Row
async with db.execute(
'SELECT Guild, Owner FROM HotPotato WHERE Explosion < ?',
(now, )) as cursor:
rows = await cursor.fetchall()
if len(rows) > 0:
await db.execute('DELETE FROM HotPotato WHERE Explosion < ?',
(now, ))
for row in rows:
await db.execute(
'UPDATE Scores SET Points = MAX(0, Points - ?), Deaths = Deaths + 1 WHERE UserID = ?',
(pointDeduction, row['Owner']))
await db.commit()
return rows
async def from_database(cls, id, guild, name, register=False):
if isinstance(guild, discord.Guild):
guild = guild.id
sniper = cls(id, guild, name)
if register:
await sniper.register_self()
async with aiosqlite.connect(Database.connection_string()) as db:
db.row_factory = aiosqlite.Row
query = 'SELECT * FROM Scores s LEFT JOIN SnipingMods sm ON sm.UserID = s.UserID AND sm.Guild = s.Guild WHERE s.UserID = ? AND s.Guild = ?'
async with db.execute(query, (id, guild)) as cursor:
row = await cursor.fetchone()
if row is None:
return None
sniper.points = row['Points']
sniper.snipes = row['snipes']
sniper.deaths = row['Deaths']
sniper.respawn = row['Respawn']
sniper.revenge = row['Revenge']
sniper.revenge_time = row['RevengeTime']
sniper.killstreak = row['Killstreak']
sniper.killstreak_record = row['KillstreakRecord']
sniper.multiplier = row['Multiplier'] or 1
sniper.multi_expiration = row['MultiExpiration']
sniper.smokebomb = row['SmokeBomb']
sniper.immunity = row['Immunity']
query = 'SELECT 1 FROM HotPotato WHERE Owner = ? AND Guild = ? LIMIT 1'
async with db.execute(query, (id, guild)) as cursor:
sniper.has_potato = await cursor.fetchone() is not None
return sniper
async def commit_new(self):
async with aiosqlite.connect(Database.connection_string()) as db:
await db.execute('INSERT INTO Soapbox (Guild, Presenter, Topic, Date) VALUES (?, ?, ?, ?)', (self.guild, self.name, self.topic, self.date.timestamp()))
await db.commit()
async def commit_delete(self):
async with aiosqlite.connect(Database.connection_string()) as db:
await db.execute('DELETE FROM Soapbox WHERE id = ?', (self.id,))
await db.commit()
async def commit_update(self):
async with aiosqlite.connect(Database.connection_string()) as db:
await db.execute('UPDATE Soapbox SET Presenter = ?, date = ?, topic = ? WHERE id = ? AND Guild = ?', (self.name, self.date.timestamp(), self.topic, self.id, self.guild))
await db.commit()
async def switchDB(self, ctx: commands.Context, env: Environment):
Database.switch_database(env)
await ctx.send('Database successfully changed.')
print('Database: ' + Database.connection_string())
async def _load_rows(self):
async with aiosqlite.connect(Database.connection_string()) as db:
db.row_factory = aiosqlite.Row
query = 'SELECT UserID, Name, Points, Snipes, Deaths FROM Scores ORDER BY Points DESC, Snipes DESC, Deaths ASC LIMIT 10'
async with db.execute(query) as cursor:
self.rows = await cursor.fetchall()
async def _load_user_count(self):
async with aiosqlite.connect(Database.connection_string()) as db:
async with db.execute('SELECT COUNT(rowid) FROM Scores') as cursor:
row = await cursor.fetchone()
self.users = row[0]
async def on_ready(self):
log.info('Bot started: Database: ' + Database.connection_string())
print('Ready. Database: ' + Database.connection_string())
async def save(self):
async with aiosqlite.connect(Database.connection_string()) as db:
await db.execute(
'INSERT INTO CarePackage (Guild, Key, Expiration, Hint) VALUES (?,?,?,?)',
(self.guild, self.keyword, self.expiration, self.hint))
await db.commit()
async def give_potato(self, explosion):
async with aiosqlite.connect(Database.connection_string()) as db:
await db.execute(
'INSERT INTO HotPotato (Owner, Explosion) VALUES (?, ?)',
(self.id, explosion))
await db.commit()
async def pass_potato(self, target):
async with aiosqlite.connect(Database.connection_string()) as db:
await db.execute(
'UPDATE HotPotato SET Owner = ? WHERE Owner = ? AND Guild = ?',
(target.id, self.id, self.guild))
await db.commit()