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=(',', ':'))