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)
