h0v1n8-web/rotbot/common/queries.py

import random, secrets, datetime, uuid
from slugify import slugify
from common import log

def create_or_get_and_update_last_event(self, table, event_type, channel_name=None, user_name=None, event_content=None, event_subject_name=None):
    # EVENT_TYPE_CHOICES = [
    #     ('pm', 'private message'),
    #     ('pa', 'private action'),
    #     ('pn', 'private notice'),
    #     ('cm', 'channel message')
    #     ('ca', 'channel action'),
    #     ('cn', 'channel notice'),
    #     ('ct', 'channel topic'),
    #     ('ck', 'channel password'),
    #     ('ci', 'channel invite'),
    #     ('cj', 'channel join'),
    #     ('cp', 'channel part'),
    #     ('ck', 'channel kick'),
    #     ('kd', 'channel kicked'),
    #     ('mc', 'mode change'),
    #     ('nc', 'nick change'),
    #     ('sq', 'quit'),
    # ]
    if table == 'channel':
        name = channel_name
    if table == 'user':
        name = user_name
    record = self.db.one('SELECT * FROM rotbot_' + table + ' WHERE LOWER(name)=LOWER(%(name)s) AND network_id=%(network_id)s', name=name, network_id=self.network.id)   # Look up the record.

    if record:  # On record.
        fields = 'name=%(name)s, last_event_type=%(last_event_type)s' # All required fields
        event_channel_id = None
        event_user_id = None
        event_subject_id = None
        if 'event_channel' in locals():
            event_channel_id = get_channel_id(self, channel_name)
            fields += ', last_event_channel_id=%(last_event_channel_id)s'
        if 'event_user' in locals():
            event_user_id = get_user_id(self, user_name)
            fields += ', last_event_user_id=%(last_event_user_id)s'
        if 'event_content' in locals():
            fields += ', last_event_content=%(last_event_content)s'
        if 'event_subject' in locals():
            event_subject_id = get_user_id(self, user_name)
            fields +- ', last_event_subject_id=%(last_event_subject_id)s'
        self.db.run('UPDATE rotbot_' + table + ' SET ' + fields + ' WHERE LOWER(name)=LOWER(%(name)s) AND network_id=%(network_id)s', name=name, network_id=self.network.id, last_event_type=event_type, last_event_channel_id=event_channel_id, last_event_user_id=event_user_id, last_event_content=event_content, last_event_subject_id=event_subject_id) # Correct capitalisation and update last event.

    else:   # Not on record.
        new_slug = slugify('%s-%s' % (name, self.network.name), max_length=50) # Create a slug from the name.
        slug_exists = self.db.one('SELECT id FROM rotbot_' + table + ' WHERE slug=%(slug)s AND network_id=%(network_id)s', slug=new_slug, network_id=self.network.id)   # Try to lookup a record with the proposed slug.

        # Keep adding random letters from the name to the slug until it is unique.
        while slug_exists and slug_exists <= 50:
            new_slug = new_slug + random.choice(name)
            slug_exists = self.db.one('SELECT id FROM rotbot_' + table + ' WHERE slug=%(slug)s AND network_id=%(network_id)s', slug=new_slug, network_id=self.network.id)
            log.notice('Requested slug already taken on table %s with network %s: %s' % (table, self.network.name, new_slug))
        while slug_exists:
            new_slug = name + uuid.uuid4()
            slug_exists = self.db.one('SELECT id FROM rotbot_' + table + ' WHERE slug=%(slug)s AND network_id=%(network_id)s', slug=new_slug, network_id=self.network.id)
            log.warn('Please clean database. Newly generated uuid4 already taken on table %s with network %s: %s' % (table, self.network.name, new_slug))

        fields = 'name, network_id, slug, last_event_type, last_event_datetime'
        values = '%(name)s, %(network_id)s, %(slug)s, %(last_event_type)s, %(last_event_datetime)s'
        event_datetime = datetime.datetime.now
        event_channel_id = None
        event_user_id = None
        event_subject_id = None
        if 'event_channel' in locals():
            event_channel_id = get_channel_id(self, channel_name)
            fields += ', last_event_channel_id'
            values += ', %(last_event_channel_id)s'
        if 'event_user' in locals():
            event_user_id = get_user_id(self, user_name)
            fields += ', last_event_user_id'
            values += ', %(last_event_user_id)s'
        if 'event_content' in locals():
            fields += ', last_event_content'
            values += ', %(last_event_content)s'
        if 'event_subject' in locals():
            event_subject_id = get_user_id(self, user_name)
            fields += ', last_event_subject_id'
            values += ', %(last_event_subject_id)s'
        if table == 'channel':
            self.db.run('INSERT INTO rotbot_' + table + ' (' + fields + ', autojoin, key, games, statistic_commands) VALUES (' + values + ', %(autojoin)s, %(key)s, %(games)s, %(statistic_commands)s)', name=name, network_id=self.network.id, slug=new_slug, last_event_type=event_type, last_event_datetime=datetime.datetime.now(), last_event_channel_id=event_channel_id, last_event_user_id=event_user_id, last_event_content=event_content, last_event_subject_id=event_subject_id, autojoin=False, key='', games=False, statistic_commands=False)  # Create record.
        else:
            self.db.run('INSERT INTO rotbot_' + table + ' (' + fields + ') VALUES (' + values + ')', name=name, network_id=self.network.id, slug=new_slug, last_event_type=event_type, last_event_datetime=datetime.datetime.now(), last_event_channel_id=event_channel_id, last_event_user_id=event_user_id, last_event_content=event_content, last_event_subject_id=event_subject_id)  # Create record.
        record = self.db.one('SELECT * FROM rotbot_' + table + ' WHERE slug=%(slug)s', slug=new_slug)   # Lookup newly created record to return to call.

        if event_type in ('cm', 'ca', 'cn'):    # It's a public message, action or notice
            MessageStatistics.update(self, event, 'message', user, channel) # Update message statistics.
    return record


def increment_join(self, channel, user):
    if not self.db.one('SELECT id FROM rotbot_join WHERE network_id=%(network_id)s AND channel_id=%(channel_id)s AND user_id=%(user_id)s', network_id=self.network.id, channel_id=channel.id, user_id=user.id):   # No record yet
        self.db.run('INSERT INTO rotbot_join (network_id, channel_id, user_id, amount) VALUES (%(network_id)s, %(channel_id)s, %(user_id)s, 1)', network_id=self.network.id, channel_id=channel.id, user_id=user.id)   # Create record.
    else:
        self.db.run('UPDATE rotbot_join SET amount = amount + 1 WHERE network_id=%(network_id)s AND channel_id=%(channel_id)s AND user_id=%(user_id)s', network_id=self.network.id, channel_id=channel.id, user_id=user.id)   # Update existing record.

def get_channel_id(self, channel_name):
    return self.db.one('SELECT id FROM rotbot_channel WHERE network_id=%(network_id)s AND LOWER(name)=LOWER(%(channel_name)s)', network_id=self.network.id, channel_name=channel_name)

def get_channel_slug(self, channel_name):
    return self.db.one('SELECT slug FROM rotbot_channel WHERE network_id=%(network_id)s AND LOWER(name)=LOWER(%(channel_name)s)', network_id=self.network.id, channel_name=channel_name)

def get_channel_setting_statistic_commands(self, channel_name):
    return self.db.one('SELECT statistic_commands FROM rotbot_channel WHERE LOWER(name)=LOWER(%(channel_name)s) AND network_id=%(network_id)s', channel_name=channel_name, network_id=self.network.id)

def get_channel_setting_game_commands(self, channel_name):
    return self.db.one('SELECT games FROM rotbot_channel WHERE name=%(channel_name)s AND network_id=%(network_id)', channel_name=channel_name, network_id=self.network.id)

def update_channel_last_event(self, channel, event_type, event_content):
    self.db.run('UPDATE rotbot_channel SET last_event_type=%(last_event_type)s, last_event_content=%(last_event_content)s WHERE channel.id=%(channel_id)s', last_event_type=event_type, last_event_content=event_content)

def create_tempchannelkey(self, channel_id):
    temp_key = secrets.token_urlsafe(40)[:40]
    self.db.run('INSERT INTO rotbot_tempchannelkey (key, network_id, channel_id, created) VALUES (%(key)s, %(network_id)s, %(channel_id)s, %(created)s)', key=temp_key, network_id=self.network.id, channel_id=channel_id, created=datetime.datetime.now())
    return temp_key


def get_user_id(self, user_name):
    return self.db.one('SELECT id FROM rotbot_user WHERE network_id=%(network_id)s AND LOWER(name)=LOWER(%(user_name)s)', network_id=self.network.id, user_name=user_name)

def get_userl_slug(self, user_name):
    return self.db.one('SELECT slug FROM rotbot_user WHERE network_id=%(network_id)s AND LOWER(name)=LOWER(%(user_name)s)', network_id=self.network.id, user_name=user_name)