class UIDStack(object): """Thin convenience wrapper around gevent.queue.LifoQueue. Each entry in the stack is a pair (uid, metadata), where the metadata may be None.""" def __init__(self): self._lifoqueue = LifoQueue() def empty(self): return self._lifoqueue.empty() def get(self): return self._lifoqueue.get_nowait() def peek(self): # This should be LifoQueue.peek_nowait(), which is currently buggy in # gevent. Can update with gevent version 1.0.2. return self._lifoqueue.queue[-1] def put(self, uid, metadata): self._lifoqueue.put((uid, metadata)) def discard(self, objects): self._lifoqueue.queue = [ item for item in self._lifoqueue.queue if item not in objects ] def qsize(self): return self._lifoqueue.qsize() def __iter__(self): for item in self._lifoqueue.queue: yield item
class UIDStack(object): """Thin convenience wrapper around gevent.queue.LifoQueue. Each entry in the stack is a pair (uid, metadata), where the metadata may be None.""" def __init__(self): self._lifoqueue = LifoQueue() def empty(self): return self._lifoqueue.empty() def get(self): return self._lifoqueue.get_nowait() def peek(self): # This should be LifoQueue.peek_nowait(), which is currently buggy in # gevent. Can update with gevent version 1.0.2. return self._lifoqueue.queue[-1] def put(self, uid, metadata): self._lifoqueue.put((uid, metadata)) def discard(self, objects): self._lifoqueue.queue = [item for item in self._lifoqueue.queue if item not in objects] def qsize(self): return self._lifoqueue.qsize() def __iter__(self): for item in self._lifoqueue.queue: yield item
class Stack(object): """Thin convenience wrapper around gevent.queue.LifoQueue.""" def __init__(self, key, initial_elements=None): self.key = key self._lifoqueue = LifoQueue() if initial_elements is not None: self._lifoqueue.queue = sorted(list(initial_elements), key=self.key) def empty(self): return self._lifoqueue.empty() def get(self): return self._lifoqueue.get_nowait() def peek(self): # This should be LifoQueue.peek_nowait(), which is currently buggy in # gevent. Can update with gevent version 1.0.2. return self._lifoqueue.queue[-1] def put(self, obj): self._lifoqueue.put(obj) def update_from(self, objects): for obj in sorted(list(objects), key=self.key): self._lifoqueue.put(obj) def discard(self, objects): self._lifoqueue.queue = [item for item in self._lifoqueue.queue if item not in objects] def qsize(self): return self._lifoqueue.qsize() def __iter__(self): for item in self._lifoqueue.queue: yield item
class SQLPlugin(Plugin): global_plugin = True def load(self, ctx): self.models = ctx.get('models', {}) self.backfills = {} self.user_updates = LifoQueue(maxsize=4096) super(SQLPlugin, self).load(ctx) def unload(self, ctx): ctx['models'] = self.models super(SQLPlugin, self).unload(ctx) @Plugin.schedule(15, init=False) def update_users(self): already_updated = set() while True: # Only update so many at a time if len(already_updated) > 10000: return try: user_id, data = self.user_updates.get_nowait() except Empty: return if user_id in already_updated: continue already_updated.add(user_id) try: User.update(**data).where(User.user_id == user_id).execute() except: self.log.exception('Failed to update user %s: ', user_id) @Plugin.listen('VoiceStateUpdate', priority=Priority.BEFORE) def on_voice_state_update(self, event): pre_state = self.state.voice_states.get(event.session_id) GuildVoiceSession.create_or_update(pre_state, event.state) @Plugin.listen('PresenceUpdate') def on_presence_update(self, event): updates = {} if event.user.avatar != UNSET: updates['avatar'] = event.user.avatar if event.user.username != UNSET: updates['username'] = event.user.username if event.user.discriminator != UNSET: updates['discriminator'] = int(event.user.discriminator) if not updates: return self.user_updates.put((event.user.id, updates)) @Plugin.listen('MessageCreate') def on_message_create(self, event): Message.from_disco_message(event.message) @Plugin.listen('MessageUpdate') def on_message_update(self, event): Message.from_disco_message_update(event.message) @Plugin.listen('MessageDelete') def on_message_delete(self, event): Message.update(deleted=True).where(Message.id == event.id).execute() @Plugin.listen('MessageDeleteBulk') def on_message_delete_bulk(self, event): Message.update(deleted=True).where((Message.id << event.ids)).execute() @Plugin.listen('MessageReactionAdd', priority=Priority.BEFORE) def on_message_reaction_add(self, event): Reaction.from_disco_reaction(event) @Plugin.listen('MessageReactionRemove', priority=Priority.BEFORE) def on_message_reaction_remove(self, event): Reaction.delete().where( (Reaction.message_id == event.message_id) & (Reaction.user_id == event.user_id) & (Reaction.emoji_id == (event.emoji.id or None)) & (Reaction.emoji_name == (event.emoji.name or None))).execute() @Plugin.listen('MessageReactionRemoveAll') def on_message_reaction_remove_all(self, event): Reaction.delete().where((Reaction.message_id == event.message_id)).execute() @Plugin.listen('GuildEmojisUpdate', priority=Priority.BEFORE) def on_guild_emojis_update(self, event): ids = [] for emoji in event.emojis: GuildEmoji.from_disco_guild_emoji(emoji, event.guild_id) ids.append(emoji.id) GuildEmoji.update(deleted=True).where( (GuildEmoji.guild_id == event.guild_id) & (~(GuildEmoji.emoji_id << ids)) ).execute() @Plugin.listen('GuildCreate') def on_guild_create(self, event): for channel in list(event.channels.values()): Channel.from_disco_channel(channel) for emoji in list(event.emojis.values()): GuildEmoji.from_disco_guild_emoji(emoji, guild_id=event.guild.id) @Plugin.listen('GuildDelete') def on_guild_delete(self, event): if event.deleted: Channel.update(deleted=True).where( Channel.guild_id == event.id ).execute() @Plugin.listen('ChannelCreate') def on_channel_create(self, event): Channel.from_disco_channel(event.channel) @Plugin.listen('ChannelUpdate') def on_channel_update(self, event): Channel.from_disco_channel(event.channel) @Plugin.listen('ChannelDelete') def on_channel_delete(self, event): Channel.update(deleted=True).where(Channel.channel_id == event.channel.id).execute() @Plugin.command('sql', level=-1, global_=True) def command_sql(self, event): conn = database.obj.get_conn() try: tbl = MessageTable(codeblock=False) with conn.cursor() as cur: start = time.time() cur.execute(event.codeblock.format(e=event)) dur = time.time() - start if not cur.description: return event.msg.reply('_Query took {}ms - no result._'.format(int(dur * 1000))) tbl.set_header(*[desc[0] for desc in cur.description]) for row in cur.fetchall(): tbl.add(*row) result = tbl.compile() if len(result) > 1900: return event.msg.reply( '_Query took {}ms_'.format(int(dur * 1000)), attachments=[('sql_result_{}.txt'.format(event.msg.id), result)] ) event.msg.reply(u'```{}```_Query took {}ms_'.format(result, int(dur * 1000))) except psycopg2.Error as e: event.msg.reply(u'```{}```'.format(e.pgerror)) @Plugin.command('init', '<entity:user|channel>', level=-1, group='markov', global_=True) def command_markov(self, event, entity): if isinstance(entity, DiscoUser): q = Message.select().where(Message.author_id == entity.id).limit(500000) else: q = Message.select().where(Message.channel_id == entity.id).limit(500000) text = [msg.content for msg in q] self.models[entity.id] = markovify.NewlineText('\n'.join(text)) event.msg.reply(u':ok_hand: created markov model for {} using {} messages'.format(entity, len(text))) @Plugin.command('one', '<entity:user|channel>', level=-1, group='markov', global_=True) def command_markov_one(self, event, entity): if entity.id not in self.models: return event.msg.reply(':warning: no model created yet for {}'.format(entity)) sentence = self.models[entity.id].make_sentence(max_overlap_ratio=1, max_overlap_total=500) if not sentence: event.msg.reply(':warning: not enough data :(') return event.msg.reply(u'{}: {}'.format(entity, sentence)) @Plugin.command('many', '<entity:user|channel> [count:int]', level=-1, group='markov', global_=True) def command_markov_many(self, event, entity, count=5): if entity.id not in self.models: return event.msg.reply(':warning: no model created yet for {}'.format(entity)) for _ in range(int(count)): sentence = self.models[entity.id].make_sentence(max_overlap_total=500) if not sentence: event.msg.reply(':warning: not enough data :(') return event.msg.reply(u'{}: {}'.format(entity, sentence)) @Plugin.command('list', level=-1, group='markov', global_=True) def command_markov_list(self, event): event.msg.reply(u'`{}`'.format(', '.join(map(str, self.models.keys())))) @Plugin.command('delete', '<oid:snowflake>', level=-1, group='markov', global_=True) def command_markov_delete(self, event, oid): if oid not in self.models: return event.msg.reply(':warning: no model with that ID') del self.models[oid] event.msg.reply(':ok_hand: deleted model') @Plugin.command('clear', level=-1, group='markov', global_=True) def command_markov_clear(self, event): self.models = {} event.msg.reply(':ok_hand: cleared models') @Plugin.command('message', '<channel:snowflake> <message:snowflake>', level=-1, group='backfill', global_=True) def command_backfill_message(self, event, channel, message): channel = self.state.channels.get(channel) Message.from_disco_message(channel.get_message(message)) return event.msg.reply(':ok_hand: backfilled') @Plugin.command('reactions', '<message:snowflake>', level=-1, group='backfill', global_=True) def command_sql_reactions(self, event, message): try: message = Message.get(id=message) except Message.DoesNotExist: return event.msg.reply(':warning: no message found') message = self.state.channels.get(message.channel_id).get_message(message.id) for reaction in message.reactions: for users in message.get_reactors(reaction.emoji, bulk=True): Reaction.from_disco_reactors(message.id, reaction, (i.id for i in users)) @Plugin.command('global', '<duration:str> [pool:int]', level=-1, global_=True, context={'mode': 'global'}, group='recover') @Plugin.command('here', '<duration:str> [pool:int]', level=-1, global_=True, context={'mode': 'here'}, group='recover') def command_recover(self, event, duration, pool=4, mode=None): if mode == 'global': channels = list(self.state.channels.values()) else: channels = list(event.guild.channels.values()) start_at = parse_duration(duration, negative=True) pool = Pool(pool) total = len(channels) msg = event.msg.reply('Recovery Status: 0/{}'.format(total)) recoveries = [] def updater(): last = len(recoveries) while True: if last != len(recoveries): last = len(recoveries) msg.edit('Recovery Status: {}/{}'.format(len(recoveries), total)) gevent.sleep(5) u = self.spawn(updater) try: for channel in channels: pool.wait_available() r = Recovery(self.log, channel, start_at) pool.spawn(r.run) recoveries.append(r) finally: pool.join() u.kill() msg.edit('RECOVERY COMPLETED ({} total messages)'.format( sum([i._recovered for i in recoveries]) )) @Plugin.command('backfill channel', '[channel:snowflake]', level=-1, global_=True) def command_backfill_channel(self, event, channel=None): channel = self.state.channels.get(channel) if channel else event.channel backfill_channel.queue(channel.id) event.msg.reply(':ok_hand: enqueued channel to be backfilled') @Plugin.command('backfill guild', '[guild:guild] [concurrency:int]', level=-1, global_=True) def command_backfill_guild(self, event, guild=None, concurrency=1): guild = guild or event.guild backfill_guild.queue(guild.id) event.msg.reply(':ok_hand: enqueued guild to be backfilled') @Plugin.command('usage', '<word:str> [unit:str] [amount:int]', level=-1, group='words') def words_usage(self, event, word, unit='days', amount=7): sql = ''' SELECT date, coalesce(count, 0) AS count FROM generate_series( NOW() - interval %s, NOW(), %s ) AS date LEFT OUTER JOIN ( SELECT date_trunc(%s, timestamp) AS dt, count(*) AS count FROM messages WHERE timestamp >= (NOW() - interval %s) AND timestamp < (NOW()) AND guild_id=%s AND (SELECT count(*) FROM regexp_matches(content, %s)) >= 1 GROUP BY dt ) results ON (date_trunc(%s, date) = results.dt); ''' msg = event.msg.reply(':alarm_clock: One moment pls...') start = time.time() tuples = list(Message.raw( sql, '{} {}'.format(amount, unit), '1 {}'.format(unit), unit, '{} {}'.format(amount, unit), event.guild.id, '\s?{}\s?'.format(word), unit ).tuples()) sql_duration = time.time() - start start = time.time() chart = pygal.Line() chart.title = 'Usage of {} Over {} {}'.format( word, amount, unit, ) if unit == 'days': chart.x_labels = [i[0].strftime('%a %d') for i in tuples] elif unit == 'minutes': chart.x_labels = [i[0].strftime('%X') for i in tuples] else: chart.x_labels = [i[0].strftime('%x %X') for i in tuples] chart.x_labels = [i[0] for i in tuples] chart.add(word, [i[1] for i in tuples]) pngdata = cairosvg.svg2png( bytestring=chart.render(), dpi=72) chart_duration = time.time() - start event.msg.reply( '_SQL: {}ms_ - _Chart: {}ms_'.format( int(sql_duration * 1000), int(chart_duration * 1000), ), attachments=[('chart.png', pngdata)]) msg.delete() @Plugin.command('top', '<target:user|channel|guild>', level=-1, group='words') def words_top(self, event, target): if isinstance(target, DiscoUser): q = 'author_id' elif isinstance(target, DiscoChannel): q = 'channel_id' elif isinstance(target, DiscoGuild): q = 'guild_id' else: raise Exception("You should not be here") sql = """ SELECT word, count(*) FROM ( SELECT regexp_split_to_table(content, '\s') as word FROM messages WHERE {}=%s LIMIT 3000000 ) t GROUP BY word ORDER BY 2 DESC LIMIT 30 """.format(q) t = MessageTable() t.set_header('Word', 'Count') for word, count in Message.raw(sql, target.id).tuples(): if '```' in word: continue t.add(word, count) event.msg.reply(t.compile())
class AbstractDatabaseConnectionPool(object): def __init__(self, maxsize=100, maxwait=1.0, expires=None, cleanup=None): """ The pool manages opened connections to the database. The main strategy is to keep the smallest number of alive connections which are required for best web service performance. In most cases connections are taken from the pool. In case of views-peeks, pool creates some extra resources preventing service gone unavailable. In time of low traffic (night) unnecessary connections are released. Parameters ---------- maxsize : int Soft limit of the number of created connections. After reaching this limit taking the next connection first waits `maxwait` time for any returned slot. maxwait : float The time in seconds which is to be wait before creating new connection after the pool gets empty. It may be 0 then immediate connections are created til `maxoverflow` is reached. expires : float The time in seconds indicates how long connection should stay alive. It is also used to close unneeded slots. """ if not isinstance(maxsize, integer_types): raise TypeError('Expected integer, got %r' % (maxsize, )) self._maxsize = maxsize self._maxwait = maxwait self._expires = expires self._cleanup = cleanup self._created_at = {} self._latest_use = {} self._pool = LifoQueue() self._size = 0 self._latest_cleanup = 0 if self._expires or self._cleanup else 0xffffffffffffffff self._interval_cleanup = min( self._expires or self._cleanup, self._cleanup or self._expires) if self._expires or self._cleanup else 0 self._cleanup_lock = Semaphore(value=1) def create_connection(self): raise NotImplementedError() def close_connection(self, item): try: self._size -= 1 self._created_at.pop(id(item), None) self._latest_use.pop(id(item), None) item.close() except Exception: pass def cleanup(self): self._cleanup_queue(time.time()) def _cleanup_queue(self, now): if self._latest_cleanup > now: return with self._cleanup_lock: if self._latest_cleanup > now: return self._latest_cleanup = now + self._interval_cleanup cleanup = now - self._cleanup if self._cleanup else None expires = now - self._expires if self._expires else None # Instead of creating new LIFO for self._pool, the ole one is reused, # beacuse some othere might wait for connetion on it. fresh_slots = [] try: # try to fill self._pool ASAP, preventing creation of new connections. # because after this loop LIFO will be in reversed order while not self._pool.empty(): item = self._pool.get_nowait() if cleanup and self._latest_use.get(id(item), 0) < cleanup: self.close_connection(item) elif expires and self._created_at.get(id(item), 0) < expires: self.close_connection(item) else: fresh_slots.append(item) except Empty: pass # Reverse order back (frestest connections should be at the begining) for conn in reversed(fresh_slots): self._pool.put_nowait(conn) def get(self): try: return self._pool.get_nowait() except Empty: pass if self._size >= self._maxsize: try: return self._pool.get(timeout=self._maxwait) except Empty: pass # It is posiible that after waiting self._maxwait time, non connection has been returned # because of cleaning up old ones on put(), so there is not connection but also LIFO is not full. # In that case new connection shouls be created, otherwise exception is risen. if self._size >= self._maxsize: raise OperationalError( "Too many connections created: {} (maxsize is {})".format( self._size, self._maxsize)) try: self._size += 1 conn = self.create_connection() except: self._size -= 1 raise now = time.time() self._created_at[id(conn)] = now self._latest_use[id(conn)] = now return conn def put(self, conn): now = time.time() self._pool.put(conn) self._latest_use[id(conn)] = now self._cleanup_queue(now) def closeall(self): while not self._pool.empty(): conn = self._pool.get_nowait() try: conn.close() except Exception: pass self._size = 0 @contextlib.contextmanager def connection(self, isolation_level=None): conn = self.get() try: if isolation_level is not None: if conn.isolation_level == isolation_level: isolation_level = None else: conn.set_isolation_level(isolation_level) yield conn except: if conn.closed: conn = None self.closeall() else: conn = self._rollback(conn) raise else: if conn.closed: raise OperationalError( "Cannot commit because connection was closed: %r" % (conn, )) conn.commit() finally: if conn is not None and not conn.closed: if isolation_level is not None: conn.set_isolation_level(isolation_level) self.put(conn) @contextlib.contextmanager def cursor(self, *args, **kwargs): isolation_level = kwargs.pop('isolation_level', None) with self.connection(isolation_level) as conn: yield conn.cursor(*args, **kwargs) def _rollback(self, conn): try: conn.rollback() except: gevent.get_hub().handle_error(conn, *sys.exc_info()) return return conn def execute(self, *args, **kwargs): with self.cursor(**kwargs) as cursor: cursor.execute(*args) return cursor.rowcount def fetchone(self, *args, **kwargs): with self.cursor(**kwargs) as cursor: cursor.execute(*args) return cursor.fetchone() def fetchall(self, *args, **kwargs): with self.cursor(**kwargs) as cursor: cursor.execute(*args) return cursor.fetchall() def fetchiter(self, *args, **kwargs): with self.cursor(**kwargs) as cursor: cursor.execute(*args) while True: items = cursor.fetchmany() if not items: break for item in items: yield item