def get_shard_server(self, tables, key, scope=SCOPE_LOCAL, mode=None):
"""Get MySQL server information for a particular shard
Raises DatabaseError when the table is unknown or when tables are not
on the same shard. ValueError is raised when there is a problem
with the methods arguments. InterfaceError is raised for other errors.
"""
if not isinstance(tables, (list, tuple)):
raise ValueError("tables should be a sequence")
groups = []
for dbobj in tables:
try:
database, table = dbobj.split('.')
except ValueError:
raise ValueError(
"tables should be given as <database>.<table>, "
"was {0}".format(dbobj))
entry = self._cache.sharding_search(database, table)
if not entry:
self.get_sharding_information((table,), database)
entry = self._cache.sharding_search(database, table)
if not entry:
raise DatabaseError(
errno=errorcode.ER_BAD_TABLE_ERROR,
msg="Unknown table '{database}.{table}'".format(
database=database, table=table))
if scope == 'GLOBAL':
return self.get_group_server(entry.global_group, mode=mode)
if entry.shard_type == 'RANGE':
partitions = sorted(entry.partitioning.keys())
index = partitions[bisect(partitions, int(key)) - 1]
partition = entry.partitioning[index]
elif entry.shard_type == 'HASH':
md5key = md5(str(key))
partition_keys = sorted(
entry.partitioning.keys(), reverse=True)
index = partition_keys[-1]
for partkey in partition_keys:
if md5key.digest() >= b16decode(partkey):
index = partkey
break
partition = entry.partitioning[index]
else:
raise InterfaceError(
"Unsupported sharding type {0}".format(entry.shard_type))
groups.append(partition['group'])
if not all(group == groups[0] for group in groups):
raise DatabaseError(
"Tables are located in different shards.")
return self.get_group_server(groups[0], mode=mode)
def run_single_update_query(self, operation: str, params=(), connection_id: int = 0) -> None:
"""
Run query to db that do update DB. Use run_single_query instead
if you are doing SELECT query .
Arguments will be passed to MySQLCursor.execute().
ProgrammingError will be raised on error.
Consider using 'params' argument instead of others string building methods
to avoid SQL injections. You can report SQL injections problems found in
the project at https://github.com/sandsbit/skarmabot/security/advisories/new.
TODO: remove bad link
"""
self.blog.debug('Running single NOT select query: ' + operation + 'with params: ' + pformat(params))
if connection_id not in self._botdb:
msg = f"There's no connection with id #{connection_id}"
self.blog.error(msg)
raise DatabaseError(msg)
cursor_ = self._botdb[connection_id].cursor()
cursor_.execute(operation, params)
self._botdb[connection_id].commit()
cursor_.close()
def run_single_query(self, operation: str, params=(), connection_id: int = 0) -> List[Tuple[Any]]:
"""
Run SELECT query to db that don't update DB. Use run_single_update_query
if your query updated DB.
Arguments will be passed to MySQLCursor.execute().
ProgrammingError will be raised on error.
Consider using 'params' argument instead of others string building methods
to avoid SQL injections. You can report SQL injections problems found in
the project at https://github.com/sandsbit/skarmabot/security/advisories/new.
TODO: remove bad link
"""
self.blog.debug('Running single SELECT query: ' + operation + 'with params: ' + pformat(params))
if connection_id not in self._botdb:
msg = f"There's no connection with id #{connection_id}"
self.blog.error(msg)
raise DatabaseError(msg)
cursor_ = self._botdb[connection_id].cursor()
cursor_.execute(operation, params)
res_ = cursor_.fetchall()
self.blog.debug(f'Got {cursor_.rowcount} rows in response')
cursor_.close()
return res_
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_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 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)
);""")
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 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 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));"
)
def create_mention_banned_users(dbu: DBUtils):
tables = dbu.run_single_query("SHOW TABLES;")
if tuple('mention_banned_users') in tables:
raise DatabaseError("Table 'mention_banned_users' already exists")
dbu.run_single_update_query("""create table mention_banned_users
(
id int auto_increment,
chat_id text not null,
user_id text not null,
ban_mentions bool default false not null,
constraint mention_banned_users_pk
primary key (id)
);""")
dbu.run_single_update_query(
'alter table mention_banned_users add unique unique_index(chat_id(255), user_id(255));'
)
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 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)
def create_violations(dbu: DBUtils):
tables = dbu.run_single_query("SHOW TABLES;")
if tuple('mention_banned_users') in tables:
raise DatabaseError("Table 'mention_banned_users' already exists")
dbu.run_single_update_query("""create table violations
(
id int auto_increment,
chat_id text not null,
user_id text not null,
today date not null,
violations_today int null,
violations_month int null,
violations_all int null,
last_violation_against text not null,
constraint violations_pk
primary key (id)
);""")
dbu.run_single_update_query(
'alter table violations add unique unique_index(chat_id(255), user_id(255));'
)
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)