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))
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)
);""")
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)
);""")
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)
);""")
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)
);""")
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))
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
})
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)
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])
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));"
)
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
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));'
)
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)
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.')
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
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)