h0v1n8-web/rotbot/common/queries.py

import random, secrets, datetime, uuid, random
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):
    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(    # Correct capitalisation and update last event.
            '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)

    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', slug=new_slug)   # 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', slug=new_slug)
            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 + str(uuid.uuid4())
            slug_exists = self.db.one('SELECT id FROM rotbot_' + table + ' WHERE slug=%(slug)s', slug=new_slug)
            log.warning('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':
            fields += ', autojoin, key, games, statistic_commands, chat'
            values += ', %(autojoin)s, %(key)s, %(games)s, %(statistic_commands'
            self.db.run(    # Create record.
                'INSERT INTO rotbot_' + table + ' (' + fields + ') VALUES (' + values + ')s, %(chat)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,
                chat=False,
            )
        else:
            fields += ', xp_spent, level, coin, coin_given, coin_spent, ap_spent, karma_correction, xp_correction, no_chat'
            values += ', %(xp_spent)s, %(level)s, %(coin)s, %(coin_given)s, %(coin_spent)s, %(ap_spent)s, %(karma_correction)s, %(xp_correction)s, %(no_chat)s'
            self.db.run(    # Create record.
                '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,
                xp_spent=0,
                level=0,
                coin=0,
                coin_given=0,
                coin_spent=0,
                ap_spent=0,
                karma_correction=0,
                xp_correction=0,
                no_chat=False,
            )
        record = self.db.one('SELECT * FROM rotbot_' + table + ' WHERE slug=%(slug)s', slug=new_slug)   # Lookup newly created record to return to call..
    return record


# def get_owners(self):
#     return self.db.all('SELECT * FROM rotbot_owner')

def is_owner(self, hostmask):
    return self.db.one('SELECT id FROM rotbot_owner WHERE hostmask=%(hostmask)s', hostmask=hostmask)

# Channel
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_id):
    return self.db.one('SELECT statistic_commands FROM rotbot_channel WHERE id=%(channel_id)s', channel_id=channel_id)

def get_channel_setting_games_commands(self, channel_id):
    return self.db.one('SELECT games FROM rotbot_channel WHERE id=%(channel_id)s', channel_id=channel_id)

def get_channel_setting_chat(self, channel_id):
    return self.db.one('SELECT chat FROM rotbot_channel WHERE id=%(channel_id)s', channel_id=channel_id)

def get_autojoin_channels(self):
    return self.db.all('SELECT name FROM rotbot_channel WHERE network_id=%(network_id)s AND autojoin=True', network_id=self.network.id)

def get_channel_last_greet(self, channel_id):
    return self.db.one('SELECT last_greet FROM rotbot_channel WHERE id=%(channel_id)s', channel_id=channel_id)

def update_channel_last_greet(self, channel_id):
    return self.db.run('UPDATE rotbot_channel SET last_greet=NOW() WHERE id=%(channel_id)s', channel_id=channel_id)

def get_channel_last_lame(self, channel_id):
    return self.db.one('SELECT last_lame FROM rotbot_channel WHERE id=%(channel_id)s', channel_id=channel_id)

def update_channel_last_lame(self, channel_id):
    return self.db.run('UPDATE rotbot_channel SET last_greet=NOW() WHERE id=%(channel_id)s', channel_id=channel_id)

def get_channel_key(self, channel_name):
    channel_id = get_channel_id(self, channel_name)     # Inperfect, make a index of the channel_name field.
    return self.db.one('SELECT key FROM rotbot_channel WHERE channel_id=%(channel_id)', channel_id=channel_id)

def save_channel_key(self, channel_name, channel_key):
    channel_id = get_channel_id(self, channel_name)     # Inperfect, make a index of the channel_name field.
    self.db.run('UPDATE key FROM rotbot_channel WHERE channel_id=%(channel_id)', channel_id=channel_id)

#def update_channel_last_event(self, channel_id, 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, channel_id=channel_id)

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


# User
def get_user(self, user_name):
    return self.db.one('SELECT * 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_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_user_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)

def get_user_name(self, user_id):
    return self.db.one('SELECT name FROM rotbot_user WHERE id=%(id)s', id=user_id)

def get_user_total_joins(self, user_id):
    recordset = self.db.all('SELECT amount FROM rotbot_join WHERE user_id=%(user_id)s AND network_id=%(network_id)s', user_id=user_id, network_id=self.network.id)
    return total_amount_in_recordset(recordset)

def get_user_total_kicks(self, user_id):
    recordset = self.db.all('SELECT amount FROM rotbot_kick WHERE kicker_id=%(user_id)s AND network_id=%(network_id)s', user_id=user_id, network_id=self.network.id)
    return total_amount_in_recordset(recordset)

def get_user_total_kicked(self, user_id):
    recordset = self.db.all('SELECT amount FROM rotbot_kick WHERE kicked_id=%(user_id)s AND network_id=%(network_id)s', user_id=user_id, network_id=self.network.id)
    return total_amount_in_recordset(recordset)

def get_user_total_messages(self, user_id):
    recordset = self.db.all('SELECT amount FROM rotbot_message WHERE user_id=%(user_id)s AND network_id=%(network_id)s', user_id=user_id, network_id=self.network.id)
    return total_amount_in_recordset(recordset)

def get_user_total_actions(self, user_id):
    recordset = self.db.all('SELECT amount FROM rotbot_action WHERE user_id=%(user_id)s AND network_id=%(network_id)s', user_id=user_id, network_id=self.network.id)
    return total_amount_in_recordset(recordset)

def get_user_total_notices(self, user_id):
    recordset = self.db.all('SELECT amount FROM rotbot_notice WHERE user_id=%(user_id)s AND network_id=%(network_id)s', user_id=user_id, network_id=self.network.id)
    return total_amount_in_recordset(recordset)

def get_user_total_cursewords_added(self, user_id):
    recordset = self.db.all('SELECT id FROM rotbot_curseword WHERE irc_user_id=%(user_id)s ', user_id=user_id)
    return recordset.count(recordset)

def get_user_total_curseadjectives_added(self, user_id):
    recordset = self.db.all('SELECT id FROM rotbot_curseadjective WHERE irc_user_id=%(user_id)s', user_id=user_id)
    return recordset.count(recordset)

def get_user_total_cursewords_banned(self, user_id):
    recordset = self.db.all('SELECT id FROM rotbot_curseword WHERE banned=True AND irc_user_id=%(user_id)s ', user_id=user_id)
    return recordset.count(recordset)

def get_user_total_curseadjectives_banned(self, user_id):
    recordset = self.db.all('SELECT id FROM rotbot_curseadjective WHERE banned=True AND irc_user_id=%(user_id)s', user_id=user_id)
    return recordset.count(recordset)

def get_user_last_greet(self, user_id):
    return self.db.one('SELECT last_greet FROM rotbot_user WHERE id=%(user_id)s', user_id=user_id)

def update_user_last_greet(self, user_id):
    return self.db.run('UPDATE rotbot_user SET last_greet=NOW() WHERE id=%(user_id)s', user_id=user_id)

def get_user_last_lame(self, user_id):
    return self.db.one('SELECT last_lame FROM rotbot_user WHERE id=%(user_id)s', user_id=user_id)

def update_user_last_lame(self, user_id):
    return self.db.run('UPDATE rotbot_user SET last_lame=NOW() WHERE id=%(user_id)s', user_id=user_id)


# Userchannel
def get_user_channel_joins(self, user_id, channel_id):
    return self.db.one('SELECT amount FROM rotbot_join WHERE channel_id=%(channel_id)s AND user_id=%(user_id)s', channel_id=channel_id, user_id=user_id)

def get_user_channel_kicks(self, user_id, channel_id):
    return self.db.one('SELECT amount FROM rotbot_kick WHERE channel_id=%(channel_id)s AND user_id=%(user_id)s', channel_id=channel_id, user_id=user_id)


# Chat
def random_curse(self):
    adjective = self.db.one('SELECT word FROM rotbot_curseadjective WHERE banned=False ORDER BY RANDOM() LIMIT 1')
    curse = self.db.one('SELECT word FROM rotbot_curseword WHERE banned=False ORDER BY RANDOM() LIMIT 1')

    # Try to hint the user of my capabilities.
    if not adjective:
        log.notice('No adjectives in the database, please add some for more colourfull language.')
    if not curse:
        log.notice('No curses in the database, please add some for more colourfull language.')

    return '%s %s' % (adjective, curse)

def random_adjective(self):
    adjective = self.db.one('SELECT word FROM rotbot_curseadjective ORDER BY RANDOM() LIMIT 1')

    # Try to hint the user of my capabilities.
    if not adjective:
        log.notice('No adjectives in database, please add some for more colourfull language.')

    return self.db.one('SELECT word FROM rotbot_curseadjective ORDER BY RANDOM() LIMIT 1')

def get_curse(self, word):
    return self.db.one('SELECT * FROM rotbot_curseword WHERE LOWER(word)=LOWER(%(word)s)')

def add_curseword(self, word, user_id):
    user_id = get_user_id(self, user_name)
    return self.db.run('INSERT INTO rotbot_curseword (word, created, irc_user_id, banned) VALUES (LOWER(%(word)s), %(created)s, %(irc_user_id)s, %(banned)s)', word=word, created=datetime.datetime.now, irc_user_id=user_id, banned=False)


# Statistics
def update_message_statistics(self, type, channel_id, user_id):
    if not self.db.one('SELECT id FROM rotbot_' + type + ' WHERE channel_id=%(channel_id)s AND user_id=%(user_id)s', channel_id=channel_id ,user_id=user_id):  # Not on record.
        self.db.run('INSERT INTO rotbot_' + type + ' (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:   # On record.
        self.db.run('UPDATE rotbot_' + type + ' SET amount = amount +1 WHERE channel_id=%(channel_id)s AND user_id=%(user_id)s', channel_id=channel_id ,user_id=user_id)  # Increment record.

def increment_join(self, channel_id, user_id):
    if not self.db.one('SELECT id FROM rotbot_join WHERE channel_id=%(channel_id)s AND user_id=%(user_id)s', 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 channel_id=%(channel_id)s AND user_id=%(user_id)s', channel_id=channel_id, user_id=user_id)   # Update existing record.

def increment_kick(self, channel_id, kicker_id, kicked_id):
    if not self.db.one('SELECT id FROM rotbot_kick WHERE channel_id=%(channel_id)s AND kicker_id=%(kicker_id)s AND kicked_id=%(kicked_id)s', channel_id=channel_id, kicker_id=kicker_id, kicked_id=kicked_id): # No records for kicker channel combination.
        self.db.run('INSERT INTO rotbot_kick (network_id, channel_id, kicker_id, kicked_id, amount) VALUES (%(network_id)s, %(channel_id)s, %(kicker_id)s, %(kicked_id)s, 1)', network_id=self.network.id, channel_id=channel_id, kicker_id=kicker_id, kicked_id=kicked_id)  # Create record.
    else:
        self.db.run('UPDATE rotbot_kick SET amount = amount + 1 WHERE channel_id=%(channel_id)s AND kicker_id=%(kicker_id)s AND kicked_id=%(kicked_id)s', channel_id=channel_id, kicker_id=kicker_id, kicked_id=kicked_id) # Update record.


# Game
def get_top_users(self, sort):
    return self.db.all('SELECT id, name, level, xp_spent, coin FROM rotbot_user WHERE network_id=%(network_id)s ORDER BY ' + sort + ' DESC LIMIT 3 ', network_id=self.network.id)

def get_user_gamestats(self, user_id):
    return self.db.one('SELECT xp_spent, level, coin, coin_given, coin_spent, ap_spent, karma_correction FROM rotbot_user WHERE id=%(id)s', id=user_id)

def levelup_user(self, user_id, xp):
    self.db.run('UPDATE rotbot_user SET level=level+1, xp_spent=xp_spent+%(xp)s, ap_spent=ap_spent+1 WHERE id=%(user_id)s', xp=xp, user_id=user_id)
    coin = random.uniform(0.1, 0.9)
    payday(self, coin)

def punish_user(self, user_id, coin, karma):
    self.db.run('UPDATE website_users SET coin=coin-%(coin)s, karma_correction=karma_correction-%(karma)s WHERE id=%(user_id)s', coin=coin, karma=karma, user_id=user_id)

def payday(self, coin):
    log.info('Game payday: %s coin.' % coin)
    self.db.run('UPDATE rotbot_user SET coin=coin+%(coin)s WHERE network_id=%(network_id)s AND level>0', coin=coin, network_id=self.network.id)

def cointransfer(self, sender_id, receiver_id, coin):
    self.db.run('UPDATE rotbot_user SET coin=coin-%(coin)s, coin_spent=coin_spent+%(coin)s, coin_given=coin_given+%(coin)s, ap_spent=ap_spent+1 WHERE id=%(sender_id)s', coin=coin, sender_id=sender_id)
    self.db.run("UPDATE rotbot_user SET coin=coin+%(coin)s WHERE id=%(user_id)s", coin=coin, id=receiver_id)
    coin = random.uniform(0.1, 0.3)
    payday(self, coin)


# Common.
def total_amount_in_recordset(recordset):
    amount = 0
    for record in recordset:
        #amount += record.amount
        amount += record
    return amount