async def insert_initial_bot_configs(self, bot): usersSql = UsersSql(self.bot) botConfigsSql = BotConfigsSql(self.bot) rs_user = await usersSql.get_user(bot.settings['author_id']) if len(rs_user) == 0: author = bot.get_user(bot.settings['author_id']) author_avatar_url = str(author.avatar_url) sql = f"""INSERT INTO users (discord_user_id, user_name, avatar_url) VALUES ( {bot.settings['author_id']}, '{bot.settings['author']}', '{author_avatar_url}' );""" databases = Databases(self.bot) await databases.execute(sql) rs_config = await botConfigsSql.get_bot_configs() if len(rs_config) == 0: sql = f"""INSERT INTO bot_configs (author_id, url, description) VALUES ( {bot.settings['author_id']}, '{constants.BOT_WEBPAGE_URL}', '{constants.DESCRIPTION}' );""" databases = Databases(self.bot) await databases.execute(sql)
async def update_gw2_last_session(self, discord_server_id: int, new_status: str): sql = f"""UPDATE gw2_configs SET last_session = '{new_status}' WHERE discord_server_id = {discord_server_id};""" databases = Databases(self.bot) await databases.execute(sql)
async def delete_server_user_api_key(self, discord_server_id: int, discord_user_id: int): sql = f"""DELETE from gw2_Keys where discord_server_id = {discord_server_id} and discord_user_id = {discord_user_id};""" databases = Databases(self.bot) await databases.execute(sql)
async def get_gw2_server_role(self, discord_server_id: int, role_name: str): sql = f"""SELECT * FROM gw2_roles WHERE discord_server_id = {discord_server_id} AND role_name = '{role_name}';""" databases = Databases(self.bot) return await databases.select(sql)
async def update_mute_admins(self, discord_server_id: int, new_status: str): sql = f"""UPDATE server_configs SET mute_admins = '{new_status}' WHERE discord_server_id = {discord_server_id};""" databases = Databases(self.bot) await databases.execute(sql)
async def execute_all_sql_files(self): dirpath = constants.SQL_DIRPATH + "/" if not os.path.isdir(dirpath): msg = "Cant find SQL dir path: " self.bot.log.info(msg + dirpath) return self.bot.log.info( "\n==> WARNING: EXECUTING SQL FILES INSIDE DATA/SQL DIR <==") all_dir_files = os.listdir(dirpath) self.bot.temp = dict() for filename in all_dir_files: if filename[-4:].lower() == ".sql": file = open(dirpath + filename, encoding="utf-8", mode="r") sql = file.read() file.close() self.bot.temp["sqlFileName"] = str(filename) self.bot.temp["sql"] = str(sql) try: self.bot.log.info("File: " + str(filename)) databases = Databases(self.log) await databases.execute(sql) except Error as e: sql = sql.replace('\n', '') e_message = str(e.args[0].args[0]).strip(',') if len(sql) > 500: sql = "sql too big to fit the screen, check the file for more info..." error_msg = f"FILE SQL ERROR:({filename})({e_message})({sql})" self.bot.log.error(error_msg) self.bot.temp.clear() del self.bot.temp
async def delete_gw2_server_roles(self, discord_server_id: int, role_name: str): sql = f"""DELETE from gw2_roles WHERE role_name = '{role_name}' AND discord_server_id = {discord_server_id};""" databases = Databases(self.bot) await databases.execute(sql)
async def get_all_server_profanity_filter_channels(self, discord_server_id: int): sql = f"""SELECT * FROM profanity_filters where discord_server_id = {discord_server_id} ORDER BY channel_name ASC;""" databases = Databases(self.bot) return await databases.select(sql)
async def update_command(self, discord_server_id: int, command_name: str, description: str): sql = f"""UPDATE commands SET description = '{description}' WHERE discord_server_id = {discord_server_id} and command_name = '{command_name}';""" databases = Databases(self.bot) await databases.execute(sql)
async def update_gw2_role_timer(self, discord_server_id: int, role_timer: int): sql = f"""UPDATE gw2_configs SET role_timer = '{role_timer}' WHERE discord_server_id = {discord_server_id};""" databases = Databases(self.bot) await databases.execute(sql)
async def update_mention_everyone_pool_cmd(self, discord_server_id: int, new_status: str): sql = f"""UPDATE server_configs SET mention_everyone_pool_cmd = '{new_status}' WHERE discord_server_id = {discord_server_id};""" databases = Databases(self.bot) await databases.execute(sql)
async def get_user_dice_rolls(self, user: discord.User, dice_size: int): sql = f"""SELECT * FROM dice_rolls where discord_server_id= {user.guild.id} and discord_user_id= {user.id} and dice_size= {dice_size};""" databases = Databases(self.bot) return await databases.select(sql)
async def update_block_invis_members(self, discord_server_id: int, new_status: str): sql = f"""UPDATE server_configs SET block_invis_members = '{new_status}' WHERE discord_server_id = {discord_server_id};""" databases = Databases(self.bot) await databases.execute(sql)
async def get_all_user_dice_rolls(self, discord_server_id: int, discord_user_id: int): sql = f"""SELECT * FROM dice_rolls where discord_server_id= {discord_server_id} and discord_user_id= {discord_user_id} ORDER BY dice_size ASC;""" databases = Databases(self.bot) return await databases.select(sql)
async def update_user_changes(self, after: discord.Member): full_name = f"{after.display_name}#{after.discriminator}".replace("'", "''") sql = f"""UPDATE users SET user_name = '{full_name}', avatar_url = '{after.avatar_url}' WHERE discord_user_id = {after.id};""" databases = Databases(self.bot) await databases.execute(sql)
async def update_user_dice_roll(self, user: discord.User, dice_size: int, roll: int): sql = f"""UPDATE dice_rolls SET roll = {roll} WHERE discord_server_id = {user.guild.id} and discord_user_id = {user.id} and dice_size = {dice_size};""" databases = Databases(self.bot) await databases.execute(sql)
async def insert_user(self, user: discord.User): # if user.bot is False: current_user = await self.get_user(user.id) if len(current_user) == 0: full_name = f"{user.display_name}#{user.discriminator}".replace("'", "''") sql = f"""INSERT INTO users (discord_user_id, user_name, avatar_url) VALUES ({user.id}, '{full_name}', '{user.avatar_url}');""" databases = Databases(self.bot) await databases.execute(sql)
async def insert_default_initial_gw2_server_configs( self, servers: discord.Guild, role_timer: int): databases = Databases(self.bot) for server in servers: current_server = await self.get_gw2_server_configs(server.id) if len(current_server) == 0: sql = f"""INSERT INTO gw2_configs (discord_server_id, role_timer) VALUES ({server.id}, '{role_timer}');""" await databases.execute(sql)
async def get_all_servers(self): sql = """ SELECT servers.*, users.user_name as owner_name FROM servers, users WHERE servers.server_owner_id = users.discord_user_id ORDER BY server_name ASC;""" databases = Databases(self.bot) return await databases.select(sql)
async def insert_user_dice_roll(self, user: discord.User, dice_size: int, roll: int): sql = f"""INSERT INTO dice_rolls (discord_server_id, discord_user_id, dice_size, roll) VALUES ( {user.guild.id}, {user.id}, {dice_size}, {roll});""" databases = Databases(self.bot) await databases.execute(sql)
async def insert_profanity_filter_channel(self, channel: discord.TextChannel): sql = f"""INSERT INTO profanity_filters (channel_id, discord_server_id, channel_name) VALUES ( {channel.id}, {channel.guild.id}, '{channel.name}' );\n""" databases = Databases(self.bot) await databases.execute(sql)
async def get_all_commands(self, discord_server_id: int): sql = f"""SELECT commands.*, users.user_name FROM commands, users WHERE commands.discord_server_id = {discord_server_id} AND users.discord_user_id = commands.discord_author_id ORDER BY commands.command_name ASC;""" databases = Databases(self.bot) return await databases.select(sql)
async def get_server_by_id(self, discord_server_id: int): sql = f""" SELECT servers.*, users.user_name as owner_name FROM servers, users WHERE servers.server_owner_id = users.discord_user_id and servers.discord_server_id = {discord_server_id} ORDER BY server_name ASC;""" databases = Databases(self.bot) return await databases.select(sql)
async def insert_gw2_server_role(self, discord_server_id: int, role_name: str): sql = f"""INSERT INTO gw2_roles( discord_server_id, role_name )VALUES( {discord_server_id}, '{role_name}' );""" databases = Databases(self.bot) await databases.execute(sql)
async def update_default_text_channel(self, discord_server_id: int, text_channel: str): sql = "UPDATE server_configs" if text_channel == "None": sql += " SET default_text_channel = Null" else: sql += f" SET default_text_channel = '{text_channel}'" sql += f" WHERE discord_server_id = {discord_server_id};" databases = Databases(self.bot) await databases.execute(sql)
async def insert_command(self, user: discord.User, command_name: str, text: str): todays_date = BotUtils.get_current_date_time_str() sql = f""" INSERT INTO commands (discord_server_id, discord_author_id, command_name, description, date) VALUES ( {user.guild.id}, {user.id}, '{command_name}', '{text}', '{todays_date}');""" databases = Databases(self.bot) await databases.execute(sql)
async def get_all_server_dice_rolls(self, discord_server_id: int, dice_size: int): sql = f"""SELECT dice_rolls.*, users.user_name FROM dice_rolls, users WHERE dice_rolls.discord_server_id = {discord_server_id} AND dice_rolls.dice_size = {dice_size} AND dice_rolls.discord_user_id = users.discord_user_id ORDER BY roll DESC;""" databases = Databases(self.bot) return await databases.select(sql)
async def update_api_key(self, updateObject: object): sql = f"""UPDATE gw2_Keys SET gw2_acc_name = '{updateObject.gw2_acc_name}', server_name = '{updateObject.server_name}', key_name = '{updateObject.key_name}', permissions = '{updateObject.permissions}', key = '{updateObject.key}' WHERE discord_server_id = {updateObject.discord_server_id} and discord_user_id = {updateObject.discord_user_id}; """ databases = Databases(self.bot) await databases.execute(sql)
async def update_server_changes(self, before: discord.Guild, after: discord.Guild): if str(before.name) != str(after.name) \ or str(before.region) != str(after.region) \ or str(before.icon_url) != str(after.icon_url) \ or str(before.owner_id) != str(after.owner_id): icon_url = str(after.icon_url) sql = f"""UPDATE servers SET server_name = '{after.name.replace("'","''")}', region = '{after.region}', icon_url = '{icon_url}', server_owner_id = {after.owner_id} WHERE discord_server_id = {after.id};""" databases = Databases(self.bot) await databases.execute(sql)
async def get_mute_user(self, discord_user_id: int): sql = f"""SELECT mutes.*, users.user_name, servers.server_name, (SELECT users.user_name FROM users WHERE users.discord_user_id = mutes.discord_author_id) AS author_name FROM mutes, servers, users WHERE mutes.discord_user_id = {discord_user_id} AND mutes.discord_server_id = servers.discord_server_id AND mutes.discord_user_id = users.discord_user_id;""" databases = Databases(self.bot) return await databases.select(sql)