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'])