def all_waiting_for_responses(_c: Cursor) -> None: """ Delete alle entries of waiting for responses :param _c: Database cursor (privided by decorator) """ # Delete all entries _c.execute('DELETE FROM waiting_for_responses')
def guild(_c: Cursor, guild_id: int, welcome_channel_id: int = None, cpr_channel_id: int = None, pr_settings_id: int = None, pr_category_id: int = None, mod_log_id: int = None, support_log_id: int = None, ticket_category_id: int = None, mute_role_id: int = None) -> None: """ Insert guild into db. :param _c: Database cursor (provided by decorator) :param guild_id: Discord GuildID :param welcome_channel_id: Discord TextChannelID :param cpr_channel_id: Discord VoiceChannelID (cpr: create private room) :param pr_settings_id: Discord TextChannelID (pr: private room) :param pr_category_id: Discord CategoryChannelID (pr: private room) :param mod_log_id: Discord TextChannelID :param support_log_id: Discord TextChannelID :param ticket_category_id: Discord CategoryID :param mute_role_id: ID of Mute role """ # Insert into db _c.execute('''INSERT INTO guilds VALUES (:guild_id, :welcome_channel_id, :cpr_channel_id, :pr_settings_id, :pr_category_id, :mod_log_id, :support_log_id, :ticket_category_id, :mute_role_id)''', {'guild_id': guild_id, 'welcome_channel_id': welcome_channel_id, 'cpr_channel_id': cpr_channel_id, 'pr_settings_id': pr_settings_id, 'pr_category_id': pr_category_id, 'mod_log_id': mod_log_id, 'support_log_id': support_log_id, 'ticket_category_id': ticket_category_id, 'mute_role_id': mute_role_id})
def default_pr_settings(_c: Cursor, guild_id: int, name: str = None, game_activity: bool = False, locked: bool = False, user_limit: int = 0, hidden: bool = False) -> None: """ Insert private room settings into db :param _c: Database cursor (provided by decorator) :param guild_id: Guild of default settings (Table: guilds) :param name: Name of private room :param game_activity: Whether to show game activity in the name :param locked: Whether the private room is locked :param user_limit: Limit of private room (0 for no limit) :param hidden: Whether the private room is hidden """ # Parse bool to int locked = int(locked) hidden = int(hidden) game_activity = int(game_activity) # Insert into db _c.execute('INSERT INTO default_pr_settings VALUES (:id, :name, :game_activity, :locked, :user_limit, :hidden, ' ':guild_id)', {'id': generate_new_id(table='pr_settings', identifier='pr_settings_id'), 'name': name, 'game_activity': game_activity, 'locked': locked, 'user_limit': user_limit, 'hidden': hidden, 'guild_id': guild_id} )
def mute(_c: Cursor, temp: bool, user_id: int, mod_id: int, date: datetime.datetime, guild_id: int, reason: str = None, until_date: datetime.datetime = None) -> None: """ Insert ban into db. :param _c: Database cursor (provided by decorator) :param temp: Whether the mute is temporary :param user_id: Discord UserID :param mod_id: Discord UserID :param date: Date of mute :param guild_id: Discord GuildID :param reason: Reason for mute :param until_date: Date until the mute lasts :return: None """ # Parse arguments into correct data types for db temp = int(temp) date = date.strftime('%Y-%m-%d %H:%M:%S') if until_date: until_date = until_date.strftime('%Y-%m-%d %H:%M:%S') # Insert into db _c.execute('INSERT INTO mutes VALUES (:mute_id, :temp, :user_id, :mod_id, :reason, :date, :until_date, :guild_id)', {'mute_id': generate_new_id(table='mutes', identifier='mute_id'), 'temp': temp, 'user_id': user_id, 'mod_id': mod_id, 'reason': reason, 'date': date, 'until_date': until_date, 'guild_id': guild_id })
def generate_new_id(_c: Cursor, table: str, identifier: str) -> str: """ Generate a unique ID in table for identifier :param _c: Cursor (provided by decorator) :param table: Database table :param identifier: Identifier of id attribute :return: Unique ID """ unique = False count = 0 random_id = None # Create new IDs until the id is unique while not unique: # Generate new ID random_id = util.random_base_16_code() # Get entries that have the same ID _c.execute("SELECT * FROM {} WHERE {}==? LIMIT 1".format(table, identifier), (random_id,)) result = _c.fetchone() # Check whether the ID is already used if not result: unique = True # No infinite loop count += 1 if count >= 1000000: raise DatabaseError('No unique code could be generated for table: {}'.format(table)) return random_id
def ticket_user(_c: Cursor, ticket_id: str, user_id: str) -> None: """ Deletes an entry in table by keyword :param _c: Database cursor (provided by decorator) :param ticket_id: TicketID of the ticket_user :param user_id: UserID of the ticket_user """ # Delete entry _c.execute("DELETE FROM ticket_users WHERE ticket_id==? AND user_id==?", (ticket_id, user_id))
def delete( self, db: Cursor, qq: int ) -> None: # unbind method, not really delete user record db.execute( "UPDATE accounts SET code=NULL WHERE qq=?", (qq,) )
def inner(_c: Cursor, user_id: str, guild_id: str) -> None: """ Deletes all entries of a specific mod operation of user on guild :param _c: Database cursor (provided by decorator) :param user_id: UserID of the mod_operations :param guild_id: GuildID of the mod_operations """ # Delete entries _c.execute("DELETE FROM ? WHERE user_id==? AND guild_id==?", (table, user_id, guild_id))
def guild_settings(_c: Cursor, guild_id: int, prefix: str = None, color: hex = None, welcome_messages: bool = False, leave_messages: bool = False, welcome_dms: bool = False, welcome_dm: str = None, pr_text_channel: bool = False, pr_name: bool = True, pr_privacy: bool = True, pr_limit: bool = True, pr_visibility: bool = False) -> None: """ Insert guild_settings to db. :param _c: Database cursor (provided by decorator) :param guild_id: Discord GuildID :param prefix: Prefix for the guild :param color: Color for the guild :param welcome_messages: Whether welcome messages are activated :param leave_messages: Whether leave messages are activated :param welcome_dms: Whether welcome direct messages are activated :param welcome_dm: The text that will be send to new members :param pr_text_channel: Whether textchannels for private rooms are activated on the guild :param pr_name: Whether the names of private rooms can be changed :param pr_privacy: Whether the private rooms can be locked :param pr_limit: Whether a user limit can be set for private rooms :param pr_visibility: Whether a private room can be made invisible """ # Parse bool into int welcome_messages = int(welcome_messages) leave_messages = int(leave_messages) welcome_dms = int(welcome_dms) pr_text_channel = int(pr_text_channel) pr_name = int(pr_name) pr_privacy = int(pr_privacy) pr_limit = int(pr_limit) pr_visibility = int(pr_visibility) # Insert into db _c.execute('''INSERT INTO guild_settings VALUES (:setting_id, :prefix, :color, :welcome_messages, :leave_messages, :welcome_dms, :welcome_dm, :pr_text_channel, :pr_name, :pr_privacy, :pr_limit, :pr_visibility, :guild_id)''', {'setting_id': generate_new_id(table='guild_settings', identifier='setting_id'), 'prefix': prefix, 'color': color, 'welcome_messages': welcome_messages, 'leave_messages': leave_messages, 'welcome_dms': welcome_dms, 'welcome_dm': welcome_dm, 'pr_text_channel': pr_text_channel, 'pr_name': pr_name, 'pr_privacy': pr_privacy, 'pr_limit': pr_limit, 'pr_visibility': pr_visibility, 'guild_id': guild_id })
def _delete_by_keyword(_c: Cursor, argument, **kwargs) -> None: """ Deletes an entry in table by keyword :param _c: Database cursor (provided by decorator) :param argument: Value for keyword (search condition) :kwargs: Additional conditions """ # Set up statement statement = "DELETE FROM {} WHERE {}=='{}'".format( table, keyword, argument) for k, v in kwargs.items(): statement += " AND {}=='{}'".format(k, v) # Delete entry _c.execute(statement)
def role(_c: Cursor, role_id: int, type_: str, guild_id: int) -> None: """ Insert role into db. :param _c: Database cursor (provided by decorator) :param role_id: Discord RoleID :param type_: Type of role ("MODERATOR", "ADMIN", "AUTOROLE" or "SUPPORTER") :param guild_id: Discord GuildID :return: None """ # Check type_ for requirements if not (type_ == 'MODERATOR' or type_ == 'ADMIN' or type_ == 'SUPPORTER' or type_ == 'AUTOROLE'): raise DatabaseAttributeError('type_', False, type_, "type_ has to be 'MODERATOR', 'ADMIN', 'AUTOROLE' or 'SUPPORTER'.") # Insert into db _c.execute('INSERT INTO roles VALUES (:role_id, :type, :guild_id)', {'role_id': role_id, 'type': type_, 'guild_id': guild_id})
def ticket_user(_c: Cursor, user_id: int, ticket_id: str, is_mod: bool = False) -> None: """ Insert ticket user into db :param _c: Database cursor (provided by decorator) :param user_id: Discord UserID :param ticket_id: From table tickets :param is_mod: Whether the user is a mod of the ticket :return: None """ # Parse arguments into correct type is_mod = int(is_mod) # Insert ticket user into db _c.execute('INSERT INTO ticket_users VALUES (:user_id, :is_mod, :ticket_id)', {'user_id': user_id, 'is_mod': is_mod, 'ticket_id': ticket_id })
def queryRecords(self, dbCursor: Cursor) -> Cursor: """- 查询数据库。 - param - `dbCursor` 数据库游标 - return 游标 """ sql: str = """ select c.`fid` id, c.`内容` data, t.`标题` title from `资料库` c left join `标题` t on t.`ID` = c.`fid` where t.`标题` != '说明文档' and data is not null """ dbCursor.execute(sql) logging.info("Records fetched %s, %s, %s." \ % (dbCursor.arraysize, dbCursor.rowcount, dbCursor.lastrowid)) return dbCursor
def export_submissions(cur: Cursor, contest: str = 'ahc001') -> None: data: List[Dict[str, Union[str, int, float]]] = [] user_last_submission_id_map: Dict[Tuple[str, str], int] = { } # [user_name, task] => submission_id for row in cur.execute( 'SELECT submission_id, task, time_unix, user_name, score, status, magnification ' 'FROM submissions WHERE contest = ? AND time_unix >= (' ' SELECT start_time_unix FROM contests WHERE contest_slug = ?' ') AND time_unix < (' ' SELECT end_time_unix FROM contests WHERE contest_slug = ?' ')' 'ORDER BY submission_id ASC', (contest, contest, contest)): submission_id: int = row[0] task: str = row[1] user_name: str = row[3] if user_name == 'wata_admin': continue data.append({ 'submission_id': submission_id, 'task': task, 'time_unix': row[2], 'user_name': user_name, 'score': row[4] if row[6] == 1 else row[4] / row[6], 'status': row[5] }) key: Tuple[str, str] = (user_name, task) if not (key in user_last_submission_id_map): user_last_submission_id_map[key] = submission_id else: if submission_id > user_last_submission_id_map[key]: user_last_submission_id_map[key] = submission_id last_submission_id_set: Set[int] = set( user_last_submission_id_map.values()) # for ahc001 if contest == 'ahc001': provisional_score_mapper = AHCScoresCSV('./lib/result_ahc001.csv') data = provisional_score_mapper.fix_data(data, last_submission_id_set) # for hokudai-hitachi2020, etc elif contest in score_fix_ratio: problems: Dict[str, float] = score_fix_ratio[contest] for d in data: # for all submissions if not (d['submission_id'] in last_submission_id_set): continue # assert d['submission_id'] in last_submission_id_set assert isinstance(d['task'], str) if not (d['task'] in problems): continue # assert d['task'] in problems ratio: float = problems[d['task']] if isinstance(d['score'], int) or isinstance(d['score'], float): d['score'] *= ratio with open( f'../atcoder-marathon-replay-frontend/public/submissions/{contest}.json', mode='wt', encoding='utf-8') as f: json.dump(data, f, separators=(',', ':'))
def update_by_keyword_id(_c: Cursor, argument: int, value=None) -> None: """ Updates the attribute in the table to value by guild_id. :param _c: Database cursor (provided by decorator) :param value: New value for the attribute :param argument: Value for keyword (search condition) :return: None """ if type(value) == bool: value = int(value) # Update the attribute in table if value is not None: # Set to value if value != None _c.execute(f"UPDATE {table} SET {attribute}=? WHERE {keyword}==?", (value, argument)) else: # Set to NULL if value == None _c.execute( f"UPDATE {table} SET {attribute}=NULL WHERE {keyword}==?", (argument, ))
def waiting_for_reponse(_c: Cursor, user_id: int, channel_id: int, guild_id: int) -> str: """ Insert waiting for response :param _c: Database cursor (provided by decorator) :param user_id: Discord UserID :param channel_id: Discord TextChaannelID :param guild_id: Guild of waiting :return: Id of created entry """ waiting_id = generate_new_id(table='waiting_for_responses', identifier='id') # Insert waiting into db _c.execute('INSERT INTO waiting_for_responses VALUES (:id, :user_id, :channel_id, NULL, :guild_id)', {'id': waiting_id, 'user_id': user_id, 'channel_id': channel_id, 'guild_id': guild_id }) return waiting_id
def update( self, db: Cursor, qq: int, code: Optional[str] = None, is_active: Optional[bool] = None, best30_type: Optional[str] = None, recent_type: Optional[str] = None ) -> None: update_dict = { "code": code, "is_active": is_active, "recent_type": recent_type, "b30_type": best30_type } db_arg_columns, db_arg_values = zip( *[(f"{i}=?", val) for i, val in update_dict.items() if val != None]) db.execute( f"UPDATE accounts SET {','.join(db_arg_columns)} WHERE qq=?", (*db_arg_values, qq))
def create( self, db: Cursor, qq: int, code: str ) -> None: user_dict = { "qq": qq, "code": code, "created_time": f"{datetime.now():%F %X}", "is_active": True, "recent_type": config.DEFAULT_RECENT_TYPE, "b30_type": config.DEFAULT_BEST30_TYPE } db_arg_columns = [i for i in user_dict.keys()] db_arg_values = [str(i) for i in user_dict.values()] db.execute( f"""INSERT INTO accounts ({','.join(db_arg_columns)}) VALUES ({','.join(list('?' * len(user_dict)))})""", db_arg_values)
def get_by_qq( self, db: Cursor, qq: int ) -> Optional[schema.User]: user = db.execute( "SELECT * FROM accounts WHERE qq=?", (qq,) ).fetchone() if not user: return None return self.model(**user)
def get_by_code( self, db: Cursor, code: str ) -> Optional[schema.User]: user = db.execute( "SELECT * FROM accounts WHERE code=?", (code,) ).fetchone() if not user: return None return self.model(**user)
def crawl_task(conn: Connection, cur: Cursor, contest: ContestListPage.Contest) -> bool: slug: str = contest.contest_slug cur.execute('SELECT COUNT(*) FROM tasks WHERE contest_slug = ?', (slug, )) count_result = cur.fetchone() exists_in_table = (count_result[0] > 0) if exists_in_table: print(f' -> There already exists in table') return False tlprr: TaskListPageRequestResult = TaskListPageRequestResult.create_from_request( slug) if tlprr.is_closed: print(f' -> Task list: 404') return True print(f' -> Task size: {len(tlprr.task_list_page.tasks)}') seq_of_parameters: List[TaskDBInsertData] = tlprr.generate_insert_data() cur.executemany('INSERT INTO tasks VALUES (?,?,?,' '?,?,?)', seq_of_parameters) conn.commit() return True
def all_entries_of_guild(_c: Cursor, guild_id: str) -> None: """ Deletes everything related to guild_id out of database. :param _c: Database cursor (provided by decorator) :param guild_id: ID of guild that should be deleted """ # Delete all pr_settings _c.execute( '''DELETE FROM pr_settings WHERE room_id IN ( SELECT room_id FROM private_rooms WHERE guild_id==? )''', (guild_id, )) # Delete all ticket_users _c.execute( '''DELETE FROM ticket_users WHERE ticket_id IN ( SELECT ticket_id FROM tickets WHERE guild_id==? )''', (guild_id, )) # Delete all entries of tables with guild_id attribute tables = [ 'guilds', 'guild_settings', 'roles', 'bans', 'mutes', 'warns', 'reports', 'private_rooms', 'tickets', 'waiting_for_responses', 'default_pr_settings' ] for table in tables: _c.execute("DELETE FROM ? WHERE guild_id==?", (table, guild_id))
def get_users(cur: Cursor, contest: str = 'ahc001') -> List[str]: users: List[str] = [] for row in cur.execute( 'SELECT DISTINCT user_name ' 'FROM submissions WHERE contest = ? AND time_unix >= (' ' SELECT start_time_unix FROM contests WHERE contest_slug = ?' ') AND time_unix < (' ' SELECT end_time_unix FROM contests WHERE contest_slug = ?' ')', (contest, contest, contest)): user_name: str = row[0] if user_name != 'wata_admin': users.append(user_name) return users
def ticket(_c: Cursor, main_user_id: int, text_channel_id: int, guild_id: int, voice_channel_id: int = None, topic: str = None) -> None: """ Insert support ticket into db :param _c: Database cursor (provided by decorator) :param main_user_id: Discord UserID of the user who created the ticket :param text_channel_id: Discord TextChannelID :param guild_id: Discord GuildID :param voice_channel_id: Discord VoiceChannel ID :param topic: Topic of the support ticket :return: None """ # Insert ticket into db _c.execute('''INSERT INTO tickets VALUES (:ticket_id, :main_user_id, :text_channel_id, :voice_channel_id, :topic, :guild_id)''', {'ticket_id': generate_new_id(table='tickets', identifier='ticket_id'), 'main_user_id': main_user_id, 'text_channel_id': text_channel_id, 'voice_channel_id': voice_channel_id, 'topic': topic, 'guild_id': guild_id })
def warn(_c: Cursor, user_id: int, mod_id: int, date: datetime.datetime, guild_id: int, reason: str = None) -> None: """ Insert ban into db. :param _c: Database cursor (provided by decorator) :param user_id: Discord UserID :param mod_id: Discord UserID :param date: Date of warn :param guild_id: Discord GuildID :param reason: Reason for warn :return: None """ # Parse arguments into correct data types for db date = date.strftime('%Y-%m-%d %H:%M:%S') # Insert into db _c.execute('INSERT INTO warns VALUES (:warn_id, :user_id, :mod_id, :reason, :date, :guild_id)', {'warn_id': generate_new_id(table='warns', identifier='warn_id'), 'user_id': user_id, 'mod_id': mod_id, 'reason': reason, 'date': date, 'guild_id': guild_id })
def private_room(_c: Cursor, room_channel_id: int, owner_id: int, guild_id: int, move_channel_id: int = None, text_channel_id: int = None) -> str: """ Insert private_room into db :param _c: Database cursor (provided by decorator) :param room_channel_id: Discord VoiceChannelID :param move_channel_id: Discord VoiceChannelID :param text_channel_id: Discord TextChannelID :param owner_id: Discord UserID :param guild_id: Discord UserID """ room_id = generate_new_id(table='private_rooms', identifier='room_id') # Insert into db _c.execute('INSERT INTO private_rooms VALUES (:room_id, :room_channel_id, :move_channel_id, :text_channel_id, ' ':owner_id, :guild_id)', {'room_id': room_id, 'room_channel_id': room_channel_id, 'move_channel_id': move_channel_id, 'text_channel_id': text_channel_id, 'owner_id': owner_id, 'guild_id': guild_id }) return room_id
def get_contests_after_ahc001(cur: Cursor) -> List[str]: """AHC001 以降のコンテスト slng 一覧を返す. Args: cur (Cursor): [description] Returns: List[str]: コンテスト slng 一覧 """ contest_slugs: List[str] = [] for row in cur.execute( 'SELECT contest_slug FROM contests WHERE start_time_unix >= 1614999600 ' 'ORDER BY end_time_unix ASC, contest_slug ASC'): contest_slugs.append(row[0]) return contest_slugs
def crawl(conn: Connection, cur: Cursor) -> None: clprr: ContestListPageRequestResult = ContestListPageRequestResult.create_from_request( ) # slugs: List[str] = [contest.contest_slug for contest in clprr.contest_list_page.contests] slugs_crawled: Set[str] = set([ row[0] for row in cur.execute( 'SELECT contest_slug FROM contests WHERE crawl_completed = 1') ]) for contest in clprr.contest_list_page.contests: if contest.contest_slug in slugs_crawled: continue print(f'[START {contest.contest_slug}]') if crawl_task(conn, cur, contest): time.sleep(3) crawl_contest(conn, cur, contest) print(f'[END {contest.contest_slug}]') time.sleep(3)
def export_contests(cur: Cursor) -> List[Dict[str, Union[str, int]]]: data: List[Dict[str, Union[str, int]]] = [] for row in cur.execute( 'SELECT contest_slug, contest_name, start_time_unix, end_time_unix FROM contests ' 'WHERE crawl_completed = 1 AND closed = 0 ' 'ORDER BY end_time_unix DESC, contest_slug DESC'): data.append({ 'contest_slug': row[0], 'contest_name': row[1], 'start_time_unix': row[2], 'end_time_unix': row[3], }) with open( '../atcoder-marathon-replay-frontend/public/contests/contests.json', mode='wt', encoding='utf-8') as f: json.dump(data, f, separators=(',', ':')) return data
def export_tasks(cur: Cursor, contest_slug: str = 'ahc001') -> None: data: List[Dict[str, Union[str, int, float]]] = [] for row in cur.execute( 'SELECT contest_slug, task_slug, label, name, time_limit_sec, memory_limit_mb ' 'FROM tasks WHERE contest_slug = ? ' 'ORDER BY label ASC', (contest_slug, )): data.append({ 'contest_slug': row[0], 'task_slug': row[1], 'label': row[2], 'name': row[3], 'time_limit_sec': row[4], 'memory_limit_mb': row[5] }) with open( f'../atcoder-marathon-replay-frontend/public/tasks/{contest_slug}.json', mode='wt', encoding='utf-8') as f: json.dump(data, f, separators=(',', ':'))