Ejemplo n.º 1
0
def handle_group_migration_or_join(update, context):
    if update.message is not None:
        if update.message.new_chat_members is not None:
            for new_member in update.message.new_chat_members:
                if new_member.id == context.bot.id:
                    chat_id = update.effective_chat.id
                    logging.getLogger('botlog').info(
                        f'Group with id #{chat_id} will be added to database after adding bot to it'
                    )
                    hhelp(update, context, 'Добро пожаловать!')
                    ChatsManager().add_new_chat(chat_id)
        if update.message.migrate_to_chat_id is not None:
            old_chat_id = update.effective_chat.id
            new_chat_id = update.message.migrate_to_chat_id

            logging.getLogger('botlog').info(
                f'Migrating chat from #{old_chat_id} to #{new_chat_id}')

            db = DBUtils()

            tables = ['chats', 'karma', 'stats']
            for table in tables:
                db.run_single_update_query(
                    f'update {table} set chat_id = %s where chat_id = %s',
                    (new_chat_id, old_chat_id))
Ejemplo n.º 2
0
def create_announcements_table(dbu: DBUtils):
    tables = dbu.run_single_query("SHOW TABLES;")
    if tuple('announcements') in tables:
        raise DatabaseError("Table 'announcements' already exists")

    dbu.run_single_update_query("""create table announcements
                                   (
                                     id int auto_increment,
                                     text longtext not null,
                                     constraint announcements_pk
                                      primary key (id)
                                   );""")
Ejemplo n.º 3
0
def create_error_table(dbu: DBUtils):
    tables = dbu.run_single_query("SHOW TABLES;")
    if tuple('errors') in tables:
        raise DatabaseError("Table 'errors' already exists")

    dbu.run_single_update_query("""create table errors
                               (
                                 id int auto_increment,
                                 name text not null,
                                 stacktrace longtext null,
                                 constraint errors_pk
                                  primary key (id)
                               );""")
Ejemplo n.º 4
0
def create_karma_table(dbu: DBUtils):
    tables = dbu.run_single_query("SHOW TABLES;")
    if tuple('karma') in tables:
        raise DatabaseError("Table 'karma' already exists")

    dbu.run_single_update_query("""create table karma
                                   (
                                        id int auto_increment,
                                        chat_id text not null,
                                        user_id text not null,
                                        karma int default 0 not null,
                                        constraint karma_pk
                                            primary key (id)
                                   );""")
Ejemplo n.º 5
0
def create_messages_table(dbu: DBUtils):
    tables = dbu.run_single_query("SHOW TABLES;")
    if tuple('messages') in tables:
        raise DatabaseError("Table 'messages' already exists")

    dbu.run_single_update_query("""create table messages
                                   (
                                     id int auto_increment,
                                     message_id text null,
                                     chat_id text not null,
                                     user_id text not null,
                                     constraint messages_pk
                                      primary key (id)
                                   );""")
Ejemplo n.º 6
0
class MessagesManager(metaclass=SingletonMeta):
    """Api to work with messages table in database"""

    blog = logging.getLogger('botlog')
    db: DBUtils = DBUtils()

    def is_user_changed_karma_on_message(self, chat_id: int, user_id: int,
                                         message_id: int):
        """Checks that user already have changed karma due to given message"""
        self.blog.debug(
            f'Checking if message in messages table for message #{message_id} in chat #{chat_id} '
            f'for user {user_id}')

        return len(
            self.db.run_single_query(
                'select id from messages '
                'where user_id = %s and chat_id = %s and message_id = %s',
                (user_id, chat_id, message_id))) != 0

    def mark_message_as_used(self, chat_id: int, user_id: int,
                             message_id: int):
        """Mark that user already have changed karma due to given message"""
        self.blog.debug(
            f'Adding message to messages table; message #{message_id} in chat #{chat_id} '
            f'for user {user_id}')

        self.db.run_single_update_query(
            'insert into messages (message_id, chat_id, user_id) values (%s, %s, %s)',
            (message_id, chat_id, user_id))
Ejemplo n.º 7
0
class UsernamesManager:
    """Associate user's id with username"""

    blog = logging.getLogger('botlog')
    db: DBUtils = DBUtils()

    def get_username_by_id(self, id_: int) -> str:
        """Get user's name from database by his id. NoSuchUser will be thrown if there is no such user id in database"""
        self.blog.info(f'Getting username of user with id #{id_}')

        result = self.db.run_single_query(
            'select name from usernames where user_id = %s', [id_])

        if len(result) == 0:
            raise NoSuchUser
        elif len(result) != 1:
            msg = f"Too many names associated with user with id #{id_}"
            self.blog.error(msg)
            raise DatabaseError(msg)
        else:
            return result[0][0]

    def set_username(self, id_: int, name: str) -> None:
        """Set name of user with given id"""
        self.blog.info(f'Setting username of user with id #{id_} to "{name}"')

        self.db.run_single_update_query(
            'insert into usernames (user_id, name) values (%(id)s, %(name)s) '
            'on duplicate key update name = %(name)s', {
                'id': id_,
                'name': name
            })
Ejemplo n.º 8
0
class AnnouncementsManager(metaclass=SingletonMeta):
    """Add or get announcements from database"""

    blog = logging.getLogger('botlog')
    db: DBUtils = DBUtils()

    def __init__(self):
        self.blog.info('Creating AnnouncementsManager instance')
        self.db.setup_new_connection(1)

    def get_all_announcements(self) -> List[Tuple[int, str]]:
        """Returns list of tuples, that store announcements' IDs and messages"""
        self.blog.debug(f'Getting list of all announcements')

        return self.db.run_single_query('select * from announcements',
                                        connection_id=1)

    def add_new_announcement(self, msg: str) -> None:
        """Add new announcement to database"""
        self.blog.debug(f'Adding new announcement')

        self.db.run_single_update_query(
            'insert into skarma.announcements (text) VALUES (%s)', [msg],
            connection_id=1)

    def delete_announcement(self, id_: int) -> None:
        """Delete announcement from database by its id"""
        self.blog.debug(f'Removing announcement with id = {id_}')

        self.db.run_single_update_query(
            'delete from announcements where id = %s', [id_], connection_id=1)
Ejemplo n.º 9
0
class ChatsManager(metaclass=SingletonMeta):
    """Store list of all bot's chats in database"""

    blog = logging.getLogger('botlog')
    db: DBUtils = DBUtils()

    def get_all_chats(self) -> List[int]:
        """Returns list of IDs of all bot's chats"""
        self.blog.debug('Getting list of all chats')
        resp = self.db.run_single_query('select * from chats')
        result = [i[1] for i in resp]
        return result

    def add_new_chat(self, id_: int) -> None:
        """Add new bot's chat id"""
        self.blog.info(f'Adding new chat with id #{id_}')
        result = self.db.run_single_query(
            'select * from chats where chat_id = %s', [id_])
        if len(result) == 0:
            self.db.run_single_update_query(
                'insert into skarma.chats (chat_id) VALUES (%s)', [id_])
        else:
            self.blog.debug(
                f'Adding new chat with id #{id_} aborted: chat already exists')

    def remove_chat(self, chat_id: int) -> None:
        """Remove chat from database. Can be used even if this chat is already deleted"""
        self.blog.info(f'Removing from database chat with id #{chat_id}')
        self.db.run_single_update_query(
            'delete from skarma.chats where chat_id = %s', [chat_id])
Ejemplo n.º 10
0
def create_usernames_table(dbu: DBUtils):
    tables = dbu.run_single_query("SHOW TABLES;")
    if tuple('usernames') in tables:
        raise DatabaseError("Table 'usernames' already exists")

    dbu.run_single_update_query("""create table usernames
                                   (
                                     id int auto_increment,
                                     user_id text not null,
                                     name text not null,
                                     constraint usernames_pk
                                      primary key (id)
                                   );""")

    dbu.run_single_update_query(
        "create unique index usernames_user_id_uindex on usernames (user_id(255));"
    )
Ejemplo n.º 11
0
class ErrorManager(metaclass=SingletonMeta):
    """
    Manage errors and exceptions.

    Errors are stored in 'errors' table in DB.
    """

    blog = logging.getLogger('botlog')

    _dbu: DBUtils = DBUtils()

    report_by_email = True

    def get_all_errors(self) -> List[Tuple[int, str, str]]:
        """Get list of all reported errors from DB"""

        return self._dbu.run_single_query('select * from errors')

    def report_error(self, name: str, stacktrace: str) -> None:
        """Report new error to DB"""
        self.blog.info('Reporting new error: ' + name)
        self._dbu.run_single_update_query('insert into skarma.errors (name, stacktrace) VALUES (%s, %s)', (name, stacktrace))

        if self.report_by_email:
            try:
                self._report_via_email(name, stacktrace)
            except Exception as e:
                self.blog.exception(e)
                self.report_by_email = False
                self.report_exception(e)

    def report_exception(self, e: Exception) -> None:
        """Report new error to DB"""
        self.report_error(repr(e), ' '.join(traceback.format_exception(etype=type(e), value=e, tb=e.__traceback__)))

    def get_number_of_errors(self) -> int:
        """Returns number of reported errors in DB"""
        res_ = self._dbu.run_single_query("select count(*) from errors")

        if len(res_) != 1 or (len(res_[0]) != 1) or type(res_[0][0]) is not int:
            msg = 'Invalid response from DB (getting number of errors): ' + pprint.pformat(res_)
            self.blog.error(msg)
            raise DatabaseError(msg)

        return res_[0][0]

    def clear_all_errors(self) -> None:
        """Clear all reported errors from database"""
        self.blog.debug('Removing all errors from DB')

        self._dbu.run_single_update_query('delete from errors')

    @staticmethod
    def _report_via_email(name: str, stacktrace: str) -> None:
        """Send error report to email. See email.conf for more information"""
        email_utils.send_email(EmailInfo().user_to, 'Error in SKarma: ' + name, stacktrace)  # TODO: Replce name
Ejemplo n.º 12
0
def create_stats_table(dbu: DBUtils):
    tables = dbu.run_single_query("SHOW TABLES;")
    if tuple('stats') in tables:
        raise DatabaseError("Table 'stats' already exists")

    dbu.run_single_update_query("""create table stats
                                   (
                                     id int auto_increment,
                                     chat_id text not null,
                                     user_id text not null,
                                     last_karma_change datetime not null,
                                     today date not null,
                                     today_karma_changes int not null,
                                     constraint stats_pk
                                      primary key (id)
                                   );""")

    dbu.run_single_update_query(
        'alter table stats add unique unique_index(chat_id(255), user_id(255));'
    )
Ejemplo n.º 13
0
def status(update, context):
    """Send information about bot status"""
    blog = logging.getLogger('botlog')
    blog.info('Printing status info to chat with id ' +
              str(update.effective_chat.id))

    number_of_errors = ErrorManager().get_number_of_errors()
    message = f"Status: Running in DEBUG mode ({'Stable' if number_of_errors == 0 else 'Unstable'})\n" \
              f"Unexpected errors: {number_of_errors} (/clear_errors)\n" \
              f"Logging status: " + ("logging normally\n" if len(blog.handlers) != 0 else "logging init failed\n") + \
              f"Database connection status: " + ("connected" if DBUtils().is_connected() else "disconnected (error)")
    context.bot.send_message(chat_id=update.effective_chat.id, text=message)
Ejemplo n.º 14
0
    dbu.run_single_update_query("""create table messages
                                   (
                                     id int auto_increment,
                                     message_id text null,
                                     chat_id text not null,
                                     user_id text not null,
                                     constraint messages_pk
                                      primary key (id)
                                   );""")


def _run_functions_and_print_db_errors(functions: List[Callable[[DBUtils],
                                                                None]],
                                       dbu: DBUtils):
    for fun in functions:
        try:
            fun(dbu)
        except DatabaseError as e:
            print(e.msg)


if __name__ == '__main__':
    dbu = DBUtils()

    _run_functions_and_print_db_errors([
        create_error_table, create_karma_table, create_chats_table,
        create_announcements_table, create_usernames_table, create_stats_table,
        create_messages_table
    ], dbu)
    print('Done.')
Ejemplo n.º 15
0
class KarmaManager(metaclass=SingletonMeta):
    """Api to work with karma table in database"""

    blog = logging.getLogger('botlog')
    db: DBUtils = DBUtils()

    def get_user_karma(self, chat_id: int, user_id: int) -> int:
        self.blog.debug(f'Getting karma of user #{user_id} in chat #{chat_id}')
        result = self.db.run_single_query(
            'select karma from karma where chat_id = %s and user_id = %s',
            (chat_id, user_id))
        if len(result) == 0:
            return 0

        if (len(result) != 1) or (len(result[0]) != 1):
            msg = 'Invalid database response for getting user karma: ' + pformat(
                result)
            self.blog.error(
                'Invalid database response for getting user karma: ' +
                pformat(result))
            raise DatabaseError(msg)

        return result[0][0]

    def set_user_karma(self, chat_id: int, user_id: int) -> None:  # TODO
        pass

    def clean_user_karma(self, chat_id: int, user_id: int) -> None:
        pass

    def clean_chat_karma(self, chat_id: int) -> None:
        pass

    def change_user_karma(self, chat_id: int, user_id: int,
                          change: int) -> None:
        self.blog.debug(
            f'Changing karma of user #{user_id} in chat #{chat_id}. change = {change}'
        )

        result = self.db.run_single_query(
            'select * from karma where chat_id = %s and user_id = %s',
            (chat_id, user_id))
        if len(result) == 0:
            self.db.run_single_update_query(
                'insert into skarma.karma (chat_id, user_id, karma) VALUES (%s, %s, %s)',
                (chat_id, user_id, change))
        else:
            self.db.run_single_update_query(
                'update karma set karma = karma + %s where chat_id = %s and user_id = %s',
                (change, chat_id, user_id))

    def increase_user_karma(self, chat_id: int, user_id: int,
                            up_change: int) -> None:
        self.change_user_karma(chat_id, user_id, up_change)

    def decrease_user_karma(self, chat_id: int, user_id: int,
                            down_change: int) -> None:
        self.change_user_karma(chat_id, user_id, -down_change)

    def get_ordered_karma_top(self,
                              chat_id: int,
                              amount: int = 5,
                              biggest: bool = True) -> List[Tuple[int, int]]:
        """
        Get ordered *amount* people from chat with biggedt (biggest = True) or smallest (biggest = False) karma.
        Returns list with tuples, which contain users' IDs and karma
        """
        self.blog.debug(
            f'Getting chat #{chat_id} TOP. amount = {amount}, biggest = {biggest}'
        )

        order = 'desc' if biggest else 'asc'
        symbol = '>' if biggest else '<'
        return self.db.run_single_query(
            f'select distinct user_id, karma from karma where chat_id = %s and karma {symbol} 0 '
            f'order by karma {order} limit %s', [chat_id, amount])

    class CHECK(Enum):
        OK = 0  # user can change karma
        TIMEOUT = 1  # user change karma too often
        CHANGE_DENIED = 2  # user can't raise or lower karma
        DAY_MAX_EXCEED = 3  # day karma change limit exceed

    def check_could_user_change_karma(self, chat_id: int, user_id: int,
                                      raise_: bool) -> Tuple[CHECK, int]:
        """Check if user can change karma and return tuple with CHECK enum code with error number
        and karma change size"""

        karma = self.get_user_karma(chat_id, user_id)
        kr = KarmaRangesManager().get_range_by_karma(karma)
        sm = StatsManager()

        last_karma_change_time = sm.get_last_karma_change_time(
            chat_id, user_id)
        time_since_last_karma_change = 0
        if last_karma_change_time is not None:
            time_since_last_karma_change = datetime.datetime.utcnow(
            ) - last_karma_change_time
        if (last_karma_change_time
                is not None) and (time_since_last_karma_change < kr.timeout):
            return self.CHECK.TIMEOUT, 0
        if (raise_ and not kr.enable_plus) or (not raise_
                                               and not kr.enable_minus):
            return self.CHECK.CHANGE_DENIED, 0
        if sm.get_karma_changes_today(chat_id, user_id) >= kr.day_max:
            return self.CHECK.DAY_MAX_EXCEED, 0

        if raise_:
            return self.CHECK.OK, kr.plus_value
        else:
            return self.CHECK.OK, kr.minus_value
Ejemplo n.º 16
0
class StatsManager(metaclass=SingletonMeta):
    """Api to work with stats table in database"""

    blog = logging.getLogger('botlog')
    db: DBUtils = DBUtils()

    def handle_user_change_karma(self, chat_id: int, user_id: int) -> None:
        """Update information in database after user change someone's karma"""
        self.blog.info(
            f'Updating information in stats table for user #{user_id} in chat #{chat_id}'
        )

        row_id_query = self.db.run_single_query(
            'select id from stats where chat_id = %s and user_id = %s',
            (chat_id, user_id))

        if len(row_id_query) > 1:
            msg = f'Invalid database response for getting stats for user #{user_id} in chat #{chat_id}'
            self.blog.error(msg)
            raise DatabaseError(msg)

        if len(row_id_query) == 0:
            self.blog.debug(
                f'No stats saves for user #{user_id} in chat #{chat_id}')
            self.db.run_single_update_query(
                'insert into stats(chat_id, user_id, last_karma_change, today, '
                'today_karma_changes) values (%s, %s, '
                'UTC_TIMESTAMP(), UTC_DATE(), 1)', (chat_id, user_id))
        else:
            row_id = row_id_query[0][0]

            user_date = self.db.run_single_query(
                'select today from stats where id = %s', [row_id])

            if len(user_date) > 1:
                msg = f'Invalid database response for getting today date for user #{user_id} in chat #{chat_id}'
                self.blog.error(msg)
                raise DatabaseError(msg)

            user_date = user_date[0][0]

            if datetime.datetime.utcnow().date() == user_date:
                self.db.run_single_update_query(
                    'update stats set today_karma_changes = today_karma_changes + 1, '
                    'last_karma_change = UTC_TIMESTAMP where id = %s',
                    [row_id])
            else:
                self.blog.debug(
                    f'Updating date for user #{user_id} in chat #{chat_id}')
                self.db.run_single_update_query(
                    'update stats set today = UTC_DATE, today_karma_changes = 1, '
                    'last_karma_change = UTC_TIMESTAMP where id = %s',
                    [row_id])

    def get_karma_changes_today(self, chat_id: int, user_id: int) -> int:
        """Return how many times user changed someone's karma in this chat"""
        self.blog.info(
            f'Getting karma changes count for user #{user_id} in chat {chat_id}'
        )

        query_result = self.db.run_single_query(
            'select today, today_karma_changes from stats '
            'where chat_id = %s and user_id = %s', [chat_id, user_id])

        if len(query_result) == 0:
            self.blog.debug(
                f'No stats saves for user #{user_id} in chat #{chat_id}')
            return 0
        elif len(query_result) == 1:
            today, count = query_result[0]
            if datetime.datetime.utcnow().date() == today:
                return count
            else:
                return 0
        else:
            msg = f'Invalid database response for getting karma changes count for user #{user_id} in chat #{chat_id}'
            self.blog.error(msg)
            raise DatabaseError(msg)

    def get_last_karma_change_time(
            self, chat_id: int, user_id: int) -> Optional[datetime.datetime]:
        """Get date and time when user last changed someone's karma in this chat. Returns None if no data stored"""
        self.blog.info(
            f'Getting last karma change time for user #{user_id} in chat {chat_id}'
        )

        query_result = self.db.run_single_query(
            'select last_karma_change from stats '
            'where chat_id = %s and user_id = %s', [chat_id, user_id])

        if len(query_result) == 0:
            self.blog.debug(
                f'No stats saves for user #{user_id} in chat #{chat_id}')
            return None
        elif len(query_result) == 1:
            return query_result[0][0]
        else:
            msg = f'Invalid database response for last karma changed time for user #{user_id} in chat #{chat_id}'
            self.blog.error(msg)
            raise DatabaseError(msg)