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 query_on_StatusDB(hash_of_apk):
dbname = "adhrit.db"
conn = dbconnection(dbname)
create_status_table(conn)
Cursor = conn.cursor()
query = "SELECT Hash, Manifest, Bytecode, Secrets from `StatusDB` WHERE `Hash` = '%s'" % str(hash_of_apk)
Cursor.execute(query)
data=Cursor.fetchone()
if data is None:
query = f"INSERT INTO StatusDB(Hash, Manifest, Bytecode, Secrets) values('{hash_of_apk}', 'incomplete', 'incomplete', 'incomplete')"
Cursor.execute(query)
conn.commit()
query = "SELECT Hash, Manifest, Bytecode, Secrets from `StatusDB` WHERE `Hash` = '%s'" % str(hash_of_apk)
Cursor.execute(query)
data=Cursor.fetchone()
result ={'hash_of_apk': data[0], 'manifest':data[1], 'bytecode':data[2], 'secrets' : data[3]}
return result
else:
result ={'hash_of_apk': data[0], 'manifest':data[1], 'bytecode':data[2], 'secrets' : data[3]}
return result
def _ensure_buffer(cursor: Cursor, buf_id: int, filetype: str) -> None:
cursor.execute(sql("select", "buffer_by_id"), {"rowid": buf_id})
if cursor.fetchone():
cursor.execute(
sql("update", "buffer"),
{
"rowid": buf_id,
"filetype": filetype
},
)
else:
cursor.execute(
sql("insert", "buffer"),
{
"rowid": buf_id,
"filetype": filetype
},
)
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 cursor(cur: Cursor):
cur.execute("SELECT count(id) FROM boxes WHERE box_name=?",
(boxName, ))
return cur.fetchone()[0] == 1
def cursor(cur: Cursor):
cur.execute("SELECT id FROM boxes WHERE box_name=?", (boxName, ))
rez = cur.fetchone()
return rez[0] if rez else None
def crawl_contest(conn: Connection, cur: Cursor,
contest: ContestListPage.Contest) -> None:
slug: str = contest.contest_slug
# 開始するページ番号の決定
cur.execute('SELECT MAX(pagenum) FROM submissions WHERE contest = ?',
(slug, ))
pagenum_max_result: Tuple[Optional[int]] = cur.fetchone()
pagenum_max: Optional[int] = pagenum_max_result[0]
pagenum: int = 1
if pagenum_max is not None:
pagenum = pagenum_max + 1
# return
while True:
# ページ取得
result: SubmissionListPageRequestResult = SubmissionListPageRequestResult.create_from_request(
slug, pagenum)
# print(result)
# exit()
count_result: Tuple[Optional[int]]
exists_in_table: bool
if result.is_closed:
print(f' -> Page {result.pagenum}: 404')
# コンテスト情報挿入
cur.execute('SELECT COUNT(*) FROM contests WHERE contest_slug = ?',
(slug, ))
count_result = cur.fetchone()
exists_in_table = (count_result[0] == 1)
if not exists_in_table:
cur.execute('INSERT INTO contests VALUES (?,?,?,?,?,?)',
(slug, contest.contest_name, contest.time_unix,
int((contest.time +
timedelta(minutes=contest.duration_minutes)
).timestamp()), 1, 1))
conn.commit()
break
else:
print(
f' -> Page {result.pagenum}: size={len(result.submission_list_page.submissions)}, '
f'min={result.submission_list_page.submissions[0].time}, max={result.submission_list_page.submissions[-1].time}'
)
# コンテスト情報挿入
cur.execute('SELECT COUNT(*) FROM contests WHERE contest_slug = ?',
(slug, ))
count_result = cur.fetchone()
exists_in_table = (count_result[0] == 1)
if not exists_in_table:
cur.execute(
'INSERT INTO contests VALUES (?,?,?,?,?,?)',
(slug, result.submission_list_page.contest_title,
result.submission_list_page.contest_starttime_unix,
result.submission_list_page.contest_endtime_unix, 0, 0))
# 提出情報挿入
seq_of_parameters: List[DBInsertData] = result.generate_insert_data()
try:
cur.executemany(
'INSERT INTO submissions VALUES (?,?,?,?,'
'?,?,?,?,?,?,?,?,?,?)', seq_of_parameters)
except sqlite3.Error as e:
print(e)
break
conn.commit()
# 最後のページなら抜ける
if result.is_last_page:
break
pagenum += 1
time.sleep(3)
cur.execute(
'UPDATE contests SET crawl_completed = 1 WHERE contest_slug = ?',
(slug, ))
conn.commit()