def opted_in(self, user=None, user_id=None):
"""
ID takes priority over user if provided
User: Logged username in DB
ID: ID of user
Returns true if user is opted in, false if not
"""
try:
cursor.fetchall() # we do this just to make sure we don't get any erorrs from MySQL later
except mysql.connector.errors.InterfaceError:
pass
if user_id is None:
get_user = "******"
else:
get_user = "******"
user = user_id
cursor.execute(get_user, (user,))
results = cursor.fetchall()
try:
if results[0][0] != 1:
return False
except IndexError:
return False
return results[0][1]
async def rename(self, ctx, role_name=None, new_name=None):
"""
Changes the name of a role
Params:
role_name : name of the role to be changed
new_name : name the role should be
"""
# Removes excess spaces at the beginning of a role name
if role_name[0] == '"' and role_name[-1] == '"':
role_name = role_name[1:-1]
role_check = get_role(ctx.guild.id, role_name)
em = discord.Embed(title='Success',
description="Renamed {} to {}".format(
role_name, new_name),
color=green)
if role_check is None:
em = discord.Embed(
title="Error",
description="{} is not in the DB".format(role_name),
color=red)
else:
query = "UPDATE `gssp`.`roles` SET `role_name` = %s WHERE (`role_name` = %s AND `guild_id` = %s);"
cursor.execute(query, (new_name, role_name, ctx.guild.id))
cnx.commit()
return await ctx.channel.send(embed=em)
async def pingable(self, ctx, *, role_name):
"""Change a role from not pingable to pingable or vice versa"""
if role_name[0] == '"' and role_name[-1] == '"':
role_name = role_name[1:-1]
role = get_role(ctx.guild.id, role_name)
if role is None:
return await ctx.channel.send(
embed=discord.Embed(title='Error',
description='Could not find that role',
color=red))
if role['is_pingable'] == 1:
update_query = "UPDATE `gssp`.`roles` SET `is_pingable`='0' WHERE `role_id`=%s AND `guild_id` = %s;"
text = "not pingable"
else:
update_query = "UPDATE `gssp`.`roles` SET `is_pingable`='1' WHERE `role_id`=%s AND `guild_id` = %s;"
text = "pingable"
cursor.execute(update_query, (
role['role_id'],
ctx.guild.id,
))
cnx.commit()
await ctx.channel.send(
embed=discord.Embed(title="SUCCESS",
description="Set {} ({}) to {}".format(
role['role_name'], role['role_id'], text),
color=green))
async def get_messages(self, user_id, limit: int, server=False):
"""
user_id : ID of user you want to get messages for
Returns:
messages: list of all messages from a user
channels: list of all channels relevant to messages, in same order
"""
if server:
get_messages = "SELECT `contents`, `channel_id` FROM `messages_detailed` ORDER BY TIME DESC LIMIT " + str(
int(limit))
cursor.execute(get_messages)
else:
get_messages = "SELECT `contents`, `channel_id` FROM `messages_detailed` WHERE `user_id` = %s ORDER BY TIME DESC LIMIT " + str(
int(limit))
cursor.execute(get_messages, (user_id,))
results = cursor.fetchall()
messages = []
channels = []
if server is True:
blocklist = []
else:
blocklist = await self.get_blocklist(user_id)
for result in results:
valid = True
for word in result[0].split(" "):
if word in blocklist:
valid = False
if valid:
messages.append(result[0])
channels.append(result[1])
return messages, channels
def add_message_to_db(self, message):
from ags_experiments.client_tools import ClientTools
self.client_tools = ClientTools(self.client)
try:
is_allowed = self.client_tools.channel_allowed(
message.channel.id, message.channel, message.channel.is_nsfw())
except AttributeError:
is_allowed = False # in PMs, and other channels, NSFW isn't an option
if is_allowed:
try:
while True:
result = cursor.fetchone()
if result is not None:
logger.debug(result + " - < Unread result")
else:
break
cursor.execute(add_message_custom, (
int(message.id), message.author.id, str(
message.channel.id),
message.created_at.strftime('%Y-%m-%d %H:%M:%S'),
message.content,))
except mysql.connector.errors.IntegrityError:
pass
except mysql.connector.errors.DataError:
logger.warn(
"Couldn't insert {} - likely a time issue".format(message.id))
cnx.commit()
async def joinable(self, ctx, *, role_name):
"""
Toggles whether a role is joinable
"""
if role_name[0] == '"' and role_name[-1] == '"':
role_name = role_name[1:-1]
role = get_role(ctx.guild.id, role_name)
if role is None:
em = discord.Embed(
title="Error",
description="Could not find role {}".format(role_name),
color=red)
return await ctx.channel.send(embed=em)
if role['is_joinable'] == 1:
update_query = "UPDATE `gssp`.`roles` SET `is_joinable`='0' WHERE `role_id`=%s;"
text = "not joinable"
else:
update_query = "UPDATE `gssp`.`roles` SET `is_joinable`='1' WHERE `role_id`=%s;"
text = "joinable"
cursor.execute(update_query, (role['role_id'], ))
em = discord.Embed(title="Success",
description="Set {} ({} to {}".format(
role['role_name'], role['role_id'], text),
color=green)
cnx.commit()
await ctx.channel.send(embed=em)
async def get_blocklist(self, user_id):
user_id = str(user_id)
get = "SELECT blocklist FROM blocklists WHERE user_id = %s"
cursor.execute(get, (user_id,))
resultset = cursor.fetchall()
if not resultset:
# add a blank blocklist
create_user = "******"
cursor.execute(create_user, (user_id,))
return []
return json.loads(resultset[0][0])
def add_user(self, member):
try:
cursor.execute(insert_users, (member.id,))
cnx.commit()
except mysql.connector.errors.IntegrityError:
pass # we pass because we just want to make sure we add any new users, so we expect some already here
try:
cursor.execute(insert_settings, (member.id,))
cnx.commit()
except mysql.connector.errors.IntegrityError:
pass # see above
async def optout_user(self, user):
"""
Opt a user out of experiments, and delete their data
Returns number of messages deleted
"""
logger.info("Deleting data for user ID {}".format(user.id))
cursor.execute("DELETE FROM users WHERE user_id = %s", (user.id, ))
result = cursor.execute(
"DELETE FROM messages_detailed WHERE user_id = %s", (user.id, ))
cnx.commit()
logger.info("Data deleted.")
def __init__(self, client):
self.client = client
self.client_tools = ClientTools(client)
self.database_tools = DatabaseTools(client)
insert_channel = "INSERT INTO channels (channel_id, channel_name) VALUES (%s, %s)"
update_channel = "UPDATE `gssp_logging`.`channels` SET `channel_name`=%s WHERE `channel_id`=%s;"
if not bool(config['discord'].get("skip_scrape")):
for guild in client.guilds:
logger.info("{}: Updating channels".format(str(guild)))
for channel in guild.text_channels:
try:
cursor.execute(
insert_channel,
(channel.id, emoji.demojize(channel.name)))
logger.debug("Inserted {} to DB".format(
emoji.demojize(channel.name)))
except mysql.connector.errors.IntegrityError:
cursor.execute(
update_channel,
(emoji.demojize(channel.name), channel.id))
logger.debug("Updated {}".format(
emoji.demojize(channel.name)))
logger.info("{}: Updating users".format(str(guild)))
for member in tqdm(guild.members,
total=len(guild.members),
desc="Adding users for {}".format(
str(guild))):
self.database_tools.add_user(member)
logger.info("{}: Finished {} users".format(
str(guild), len(guild.members)))
logger.info("{}: Updating roles".format(str(guild)))
for role in guild.roles:
if role.name != "@everyone":
try:
cursor.execute(insert_role,
(role.id, emoji.demojize(role.name),
guild.id, int(role.mentionable)))
except mysql.connector.errors.IntegrityError:
cursor.execute(update_role, (emoji.demojize(
role.name), int(role.mentionable), role.id))
# this is designed to assist with migration, by moving old discord role members over to the new
# system seamlessly
member_ids = []
for member in role.members:
member_ids.append(member.id)
role_db = DbRole(role.id,
role.name,
0,
members=member_ids)
role_db.save_members()
logger.info("{}: Finished {} roles".format(
guild, len(guild.roles)))
cnx.commit()
else:
logger.warn(
"Skipping scraping data from existing servers - data may be out of date"
)
async def delete(self, ctx, *, role_name):
"""Deletes a role - cannot be undone!"""
if role_name[0]=='"' and role_name[-1] == '"':
role_name=role_name[1:-1]
role_check = get_role(ctx.guild.id, role_name)
em = discord.Embed(
title="Success", description="Deleted role {}".format(role_name), color=green)
if role_check is None:
em = discord.Embed(
title="Error", description="{} is not in the DB".format(role_name), color=red)
else:
query = "DELETE FROM `gssp`.`roles` WHERE `role_name` = %s AND `guild_id` = %s"
cursor.execute(query, (role_name, ctx.guild.id))
cnx.commit()
return await ctx.channel.send(embed=em)
def is_automated(self, user):
"""
Returns true if user is opted in to automation, false if not
"""
cnx.commit()
get_user = "******"
cursor.execute(get_user, (user.id,))
results = cursor.fetchall()
cnx.commit()
try:
if results[0][0] != 1:
return False
except IndexError:
return False
return True
async def add(self, ctx, *, role_name):
"""Add a role. Note: by default, it isn't joinable"""
if role_name[0]=='"' and role_name[-1] == '"':
role_name=role_name[1:-1]
role_check = get_role(ctx.guild.id, role_name)
em = discord.Embed(
title="Success", description="Created role {}".format(role_name), color=green)
if role_check is not None:
em = discord.Embed(
title="Error", description="Role is already in the DB", color=red)
else:
query = "INSERT INTO `gssp`.`roles` (`role_name`, `guild_id`) VALUES (%s, %s);"
cursor.execute(query, (role_name, ctx.guild.id))
cnx.commit()
return await ctx.channel.send(embed=em)
async def save_markov(self, model, user_id):
"""
Save a model to markov table
user_id : user's ID we want to save for
model: Markov model object
"""
save = "INSERT INTO `markovs` (`user`, `markov_json`) VALUES (%s, %s);"
save_update = "UPDATE `markovs` SET `markov_json`=%s WHERE `user`=%s;"
try:
cursor.execute(save, (user_id, model.to_json()))
except mysql.connector.errors.IntegrityError:
cursor.execute(save_update, (model.to_json(), user_id))
cnx.commit()
return
async def get_times(self, user_id=None):
"""
username : user you want to get messages for
Returns:
times: list of all timestamps of users messages
"""
if user_id is None:
get_time = "SELECT `time` FROM `messages_detailed` ORDER BY TIME ASC"
cursor.execute(get_time)
else:
get_time = "SELECT `time` FROM `messages_detailed` WHERE `user_id` = %s ORDER BY TIME ASC"
cursor.execute(get_time, (user_id,))
timesA = cursor.fetchall()
times = []
for time in timesA:
times.append(time[0])
return times
async def automated(self, ctx):
"""
Opt in to automated messages. Run this again to opt out.
"""
if not self.database_tools.opted_in(user_id=ctx.author.id):
return await ctx.channel.send(embed=discord.Embed(title="Error", description=strings['tagger']['errors']['not_opted_in'], color=colours.red))
if self.database_tools.is_automated(ctx.author):
output = await ctx.channel.send("Opting you out of automation.")
query = "UPDATE `users` SET `automate_opted_in`=b'0' WHERE `user_id`=%s;"
cursor.execute(query, (ctx.author.id,))
cnx.commit()
await output.delete()
return await ctx.channel.send(embed=discord.Embed(title="Success", description='You will be removed from the pool on the next refresh (IE: when the bot goes back around in a loop again)'))
else:
output = await ctx.channel.send("Opting you into automation")
query = "UPDATE`users` SET `automate_opted_in`=b'1' WHERE `user_id`=%s;"
cursor.execute(query, (ctx.author.id,))
cnx.commit()
await output.delete()
return await ctx.channel.send(embed=discord.Embed(title="Success", description='Opted in!', color=colours.green))
async def experiments(self, ctx):
"""
Opt into data analysis and experiments.
"""
message = ctx.message
channel = message.channel
author = message.author
create_user = "******"
try:
cursor.execute(create_user, (author.id, author.name))
cnx.commit()
em = discord.Embed(
title=strings['data_collection']['opt_in_title'], description=opt_in_message)
em.set_footer(text=strings['data_collection']['opt_in_footer'])
return await channel.send(embed=em)
except mysql.connector.errors.IntegrityError:
get_user = "******"
cursor.execute(get_user, (author.id,))
opt_in_user = "******"
cursor.execute(opt_in_user, (author.id,))
await channel.send(strings['data_collection']['data_track_start'] + " for " + str(ctx.message.author))
await self.client_tools.build_data_profile([author])
await channel.send(strings['data_collection']['complete'].format(author.name))
async def resync_roles(self, ctx):
"""
Force refresh the roles in the database with the roles discord has.
"""
for guild in self.client.guilds:
for role in guild.roles:
if role.name != "@everyone":
try:
cursor.execute(insert_role, (role.id, role.name))
except mysql.connector.errors.IntegrityError:
pass
# this is designed to assist with migration, by moving old discord role members over to the new
# system seamlessly
member_ids = []
for member in role.members:
member_ids.append(member.id)
role_db = DbRole(role.id, role.name, 0, members=member_ids)
role_db.save_members()
cursor.execute(
update_role, (emoji.demojize(role.name), role.id))
await ctx.send(embed=discord.Embed(title="Success", description="Resynced roles.", color=green))
async def process_message(self, message):
await self.check_flags(message)
user_exp = self.database_tools.opted_in(user_id=message.author.id)
if user_exp is not False:
self.database_tools.add_message_to_db(message)
logger.debug("Message from {}".format(user_exp))
# this records analytical data - don't adjust this without reading
# Discord TOS first
try:
cursor.execute(add_message,
(int(message.id), str(message.channel.id),
message.created_at.strftime('%Y-%m-%d %H:%M:%S')))
cnx.commit()
except mysql.connector.errors.IntegrityError:
pass
try:
# if its double(or more) prefixed then it cant be a command (?word is a command, ????? is not)
if message.content[len(config['discord']
['prefix'])] == config['discord']['prefix']:
return
except IndexError:
return
async def blocklist(self, ctx, command=None, word=None):
"""
Prevents words from being shown publicly through methods such as markov and markov_server.
Note: they will still be logged, and this just prevents them being shown in chat.
Command: option to use
Word: Word to add or remove from blocklist
"""
pm_channel = (discord.channel.DMChannel == type(ctx.channel))
if not pm_channel:
try:
await ctx.message.delete()
except discord.errors.Forbidden:
logger.warn(
"Could not delete blacklist command, lacking permissions")
if command is None:
return await ctx.send("""
No subcommand selected - please enter a subcommand for your blocklist.
?blocklist add [word] : Add word to blocklist
?blocklist remove [word] : Remove word from blocklist
?blocklist get : Get PM of current blocklist
""")
# fetch current blocklist
blockL = await self.database_tools.get_blocklist(ctx.author.id)
update_blocklist = "UPDATE blocklists SET blocklist = %s WHERE user_id = %s"
if command == "add":
if word is None:
return await ctx.send(strings['blocklist']['status']['no_word'],
delete_after=config['discord']['delete_timeout'])
msg = await ctx.send(strings['blocklist']['status']['adding'])
# check if the word is already on the list. throw error if it is
if word.lower() not in blockL:
# if its not then add it
blockL.append(word.lower())
# update DB with new list
new_json = json.dumps(blockL)
cursor.execute(update_blocklist, (new_json, ctx.author.id,))
else:
await msg.delete()
return await ctx.send(strings['blocklist']['status']['exist'])
elif command == "remove":
if word is None:
return await ctx.send(strings['blocklist']['status']['no_word'],
delete_after=config['discord']['delete_timeout'])
msg = await ctx.send(strings['blocklist']['status']['removing'])
# try and remove it from list (use a try statement, catching ValueError)
try:
blockL.remove(word.lower())
except ValueError:
return await msg.edit(content=strings['blocklist']['status']['not_exist'])
# update DB with new list
new_json = json.dumps(blockL)
cursor.execute(update_blocklist, (new_json, ctx.author.id,))
elif command == "get":
# make it nice to look at
if blockL == []:
msg = strings['blocklist']['status']['empty']
else:
msg = strings['blocklist']['status']['list']
for item in blockL:
# done so that the merge with the long string is only done once per word
part = ' ' + item + ','
msg += part
msg = msg[:-1] # trim off the trailing ,
# send a private message with the nice to look at blocklist
# this prevents the next commands from running
return await ctx.author.send(msg)
else:
return await ctx.send("""
No subcommand selected - please enter a subcommand for your blocklist.
?blocklist add [word] : Add word to blocklist
?blocklist remove [word] : Remove word from blocklist
?blocklist get : Get PM of current blocklist
""")
await msg.edit(content=strings['blocklist']['status']['complete'])
