[Scummvm-git-logs] scummvm-sites integrity -> c503b1248367957c2bdf120a9f018e438b7156e3
sev-
noreply at scummvm.org
Tue Jun 17 13:20:26 UTC 2025
This automated email contains information about 1 new commit which have been
pushed to the 'scummvm-sites' repo located at https://api.github.com/repos/scummvm/scummvm-sites .
Summary:
c503b12483 INTEGRITY: Run pre-commit hook for ruff with formatter and linter.
Commit: c503b1248367957c2bdf120a9f018e438b7156e3
https://github.com/scummvm/scummvm-sites/commit/c503b1248367957c2bdf120a9f018e438b7156e3
Author: ShivangNagta (shivangnag at gmail.com)
Date: 2025-06-17T15:20:24+02:00
Commit Message:
INTEGRITY: Run pre-commit hook for ruff with formatter and linter.
Changed paths:
.gitignore
dat_parser.py
db_functions.py
fileset.py
megadata.py
pagination.py
schema.py
user_fileset_functions.py
diff --git a/.gitignore b/.gitignore
index 08646ae..15f33bf 100644
--- a/.gitignore
+++ b/.gitignore
@@ -2,3 +2,4 @@
mysql_config.json
__pycache__
.DS_Store
+.pre-commit-config.yaml
\ No newline at end of file
diff --git a/dat_parser.py b/dat_parser.py
index 11a57b2..eab7fae 100644
--- a/dat_parser.py
+++ b/dat_parser.py
@@ -1,42 +1,44 @@
import re
import os
-import sys
-from db_functions import db_insert, populate_matching_games, match_fileset
+from db_functions import db_insert, match_fileset
import argparse
+
def remove_quotes(string):
# Remove quotes from value if they are present
- if string and string[0] == "\"":
+ if string and string[0] == '"':
string = string[1:-1]
return string
+
def map_checksum_data(content_string):
arr = []
-
- content_string = content_string.strip().strip('()').strip()
-
+
+ content_string = content_string.strip().strip("()").strip()
+
tokens = re.split(r'\s+(?=(?:[^"]*"[^"]*")*[^"]*$)', content_string)
-
+
current_rom = {}
i = 0
while i < len(tokens):
- if tokens[i] == 'name':
- current_rom['name'] = tokens[i + 1].strip('"')
+ if tokens[i] == "name":
+ current_rom["name"] = tokens[i + 1].strip('"')
i += 2
- elif tokens[i] == 'size':
- current_rom['size'] = int(tokens[i + 1])
+ elif tokens[i] == "size":
+ current_rom["size"] = int(tokens[i + 1])
i += 2
else:
checksum_key = tokens[i]
checksum_value = tokens[i + 1] if len(tokens) >= 6 else "0"
current_rom[checksum_key] = checksum_value
i += 2
-
+
arr.append(current_rom)
-
+
return arr
+
def map_key_values(content_string, arr):
# Split by newline into different pairs
temp = content_string.splitlines()
@@ -51,14 +53,15 @@ def map_key_values(content_string, arr):
# Handle duplicate keys (if the key is rom) and add values to a array instead
if pair[0] == "rom":
- if 'rom' not in arr:
- arr['rom'] = []
- arr['rom'].extend(map_checksum_data(pair[1]))
+ if "rom" not in arr:
+ arr["rom"] = []
+ arr["rom"].extend(map_checksum_data(pair[1]))
else:
arr[pair[0]] = pair[1].replace("\\", "")
-
+
return arr
-
+
+
def match_outermost_brackets(input):
"""
Parse DAT file and separate the contents each segment into an array
@@ -72,20 +75,21 @@ def match_outermost_brackets(input):
for i in range(len(input)):
char = input[i]
- if char == '(' and not inside_quotes:
+ if char == "(" and not inside_quotes:
if depth == 0:
cur_index = i
depth += 1
- elif char == ')' and not inside_quotes:
+ elif char == ")" and not inside_quotes:
depth -= 1
if depth == 0:
- match = input[cur_index:i+1]
+ match = input[cur_index : i + 1]
matches.append((match, cur_index))
- elif char == '"' and input[i - 1] != '\\':
+ elif char == '"' and input[i - 1] != "\\":
inside_quotes = not inside_quotes
return matches
+
def parse_dat(dat_filepath):
"""
Take DAT filepath as input and return parsed data in the form of
@@ -105,27 +109,36 @@ def parse_dat(dat_filepath):
matches = match_outermost_brackets(content)
if matches:
for data_segment in matches:
- if "clrmamepro" in content[data_segment[1] - 11: data_segment[1]] or \
- "scummvm" in content[data_segment[1] - 8: data_segment[1]]:
+ if (
+ "clrmamepro" in content[data_segment[1] - 11 : data_segment[1]]
+ or "scummvm" in content[data_segment[1] - 8 : data_segment[1]]
+ ):
header = map_key_values(data_segment[0], header)
- elif "game" in content[data_segment[1] - 5: data_segment[1]]:
+ elif "game" in content[data_segment[1] - 5 : data_segment[1]]:
temp = {}
temp = map_key_values(data_segment[0], temp)
game_data.append(temp)
- elif "resource" in content[data_segment[1] - 9: data_segment[1]]:
+ elif "resource" in content[data_segment[1] - 9 : data_segment[1]]:
temp = {}
temp = map_key_values(data_segment[0], temp)
resources[temp["name"]] = temp
# print(header, game_data, resources)
return header, game_data, resources, dat_filepath
+
def main():
- parser = argparse.ArgumentParser(description="Process DAT files and interact with the database.")
- parser.add_argument('--upload', nargs='+', help='Upload DAT file(s) to the database')
- parser.add_argument('--match', nargs='+', help='Populate matching games in the database')
- parser.add_argument('--user', help='Username for database')
- parser.add_argument('-r', help="Recurse through directories", action='store_true')
- parser.add_argument('--skiplog', help="Skip logging dups", action='store_true')
+ parser = argparse.ArgumentParser(
+ description="Process DAT files and interact with the database."
+ )
+ parser.add_argument(
+ "--upload", nargs="+", help="Upload DAT file(s) to the database"
+ )
+ parser.add_argument(
+ "--match", nargs="+", help="Populate matching games in the database"
+ )
+ parser.add_argument("--user", help="Username for database")
+ parser.add_argument("-r", help="Recurse through directories", action="store_true")
+ parser.add_argument("--skiplog", help="Skip logging dups", action="store_true")
args = parser.parse_args()
@@ -137,5 +150,6 @@ def main():
for filepath in args.match:
match_fileset(parse_dat(filepath), args.user)
+
if __name__ == "__main__":
main()
diff --git a/db_functions.py b/db_functions.py
index 8677164..54606a2 100644
--- a/db_functions.py
+++ b/db_functions.py
@@ -11,45 +11,48 @@ import re
SPECIAL_SYMBOLS = '/":*|\\?%<>\x7f'
+
def db_connect():
base_dir = os.path.dirname(os.path.abspath(__file__))
- config_path = os.path.join(base_dir, 'mysql_config.json')
+ config_path = os.path.join(base_dir, "mysql_config.json")
with open(config_path) as f:
mysql_cred = json.load(f)
-
+
conn = pymysql.connect(
host=mysql_cred["servername"],
user=mysql_cred["username"],
password=mysql_cred["password"],
db=mysql_cred["dbname"],
- charset='utf8mb4',
+ charset="utf8mb4",
cursorclass=pymysql.cursors.DictCursor,
- autocommit=False
+ autocommit=False,
)
-
+
return conn
+
def get_checksum_props(checkcode, checksum):
checksize = 0
checktype = checkcode
- if '-' in checkcode:
- exploded_checkcode = checkcode.split('-')
+ if "-" in checkcode:
+ exploded_checkcode = checkcode.split("-")
last = exploded_checkcode.pop()
- if last == '1M' or last.isdigit():
+ if last == "1M" or last.isdigit():
checksize = last
- checktype = '-'.join(exploded_checkcode)
+ checktype = "-".join(exploded_checkcode)
# Detection entries have checktypes as part of the checksum prefix
- if ':' in checksum:
- prefix = checksum.split(':')[0]
+ if ":" in checksum:
+ prefix = checksum.split(":")[0]
checktype += "-" + prefix
- checksum = checksum.split(':')[1]
+ checksum = checksum.split(":")[1]
return checksize, checktype, checksum
+
def insert_game(engine_name, engineid, title, gameid, extra, platform, lang, conn):
# Set @engine_last if engine already present in table
exists = False
@@ -63,15 +66,31 @@ def insert_game(engine_name, engineid, title, gameid, extra, platform, lang, con
# Insert into table if not present
if not exists:
with conn.cursor() as cursor:
- cursor.execute(f"INSERT INTO engine (name, engineid) VALUES ('{escape_string(engine_name)}', '{engineid}')")
+ cursor.execute(
+ f"INSERT INTO engine (name, engineid) VALUES ('{escape_string(engine_name)}', '{engineid}')"
+ )
cursor.execute("SET @engine_last = LAST_INSERT_ID()")
# Insert into game
with conn.cursor() as cursor:
- cursor.execute(f"INSERT INTO game (name, engine, gameid, extra, platform, language) VALUES ('{escape_string(title)}', @engine_last, '{gameid}', '{escape_string(extra)}', '{platform}', '{lang}')")
+ cursor.execute(
+ f"INSERT INTO game (name, engine, gameid, extra, platform, language) VALUES ('{escape_string(title)}', @engine_last, '{gameid}', '{escape_string(extra)}', '{platform}', '{lang}')"
+ )
cursor.execute("SET @game_last = LAST_INSERT_ID()")
-def insert_fileset(src, detection, key, megakey, transaction, log_text, conn, ip='', username=None, skiplog=None):
+
+def insert_fileset(
+ src,
+ detection,
+ key,
+ megakey,
+ transaction,
+ log_text,
+ conn,
+ ip="",
+ username=None,
+ skiplog=None,
+):
status = "detection" if detection else src
game = "NULL"
key = "NULL" if key == "" else f"'{key}'"
@@ -80,7 +99,7 @@ def insert_fileset(src, detection, key, megakey, transaction, log_text, conn, ip
if detection:
status = "detection"
game = "@game_last"
-
+
if status == "user":
game = "@game_last"
@@ -97,23 +116,29 @@ def insert_fileset(src, detection, key, megakey, transaction, log_text, conn, ip
existing_entry = cursor.fetchone()
if existing_entry is not None:
- existing_entry = existing_entry['id']
+ existing_entry = existing_entry["id"]
with conn.cursor() as cursor:
cursor.execute(f"SET @fileset_last = {existing_entry}")
cursor.execute(f"DELETE FROM file WHERE fileset = {existing_entry}")
- cursor.execute(f"UPDATE fileset SET `timestamp` = FROM_UNIXTIME(@fileset_time_last) WHERE id = {existing_entry}")
- cursor.execute(f"UPDATE fileset SET status = 'detection' WHERE id = {existing_entry} AND status = 'obsolete'")
+ cursor.execute(
+ f"UPDATE fileset SET `timestamp` = FROM_UNIXTIME(@fileset_time_last) WHERE id = {existing_entry}"
+ )
+ cursor.execute(
+ f"UPDATE fileset SET status = 'detection' WHERE id = {existing_entry} AND status = 'obsolete'"
+ )
cursor.execute(f"SELECT status FROM fileset WHERE id = {existing_entry}")
- status = cursor.fetchone()['status']
- if status == 'user':
+ status = cursor.fetchone()["status"]
+ if status == "user":
add_usercount(existing_entry, conn)
category_text = f"Updated Fileset:{existing_entry}"
log_text = f"Updated Fileset:{existing_entry}, {log_text}"
- user = f'cli:{getpass.getuser()}' if username is None else username
+ user = f"cli:{getpass.getuser()}" if username is None else username
if not skiplog:
- log_last = create_log(escape_string(category_text), user, escape_string(log_text), conn)
+ log_last = create_log(
+ escape_string(category_text), user, escape_string(log_text), conn
+ )
update_history(existing_entry, existing_entry, conn, log_last)
-
+
return True
# $game and $key should not be parsed as a mysql string, hence no quotes
@@ -125,23 +150,28 @@ def insert_fileset(src, detection, key, megakey, transaction, log_text, conn, ip
category_text = f"Uploaded from {src}"
with conn.cursor() as cursor:
cursor.execute("SELECT @fileset_last")
- fileset_last = cursor.fetchone()['@fileset_last']
+ fileset_last = cursor.fetchone()["@fileset_last"]
log_text = f"Created Fileset:{fileset_last}, {log_text}"
- if src == 'user':
+ if src == "user":
log_text = f"Created Fileset:{fileset_last}, from user: IP {ip}, {log_text}"
- user = f'cli:{getpass.getuser()}' if username is None else username
+ user = f"cli:{getpass.getuser()}" if username is None else username
if not skiplog:
- log_last = create_log(escape_string(category_text), user, escape_string(log_text), conn)
+ log_last = create_log(
+ escape_string(category_text), user, escape_string(log_text), conn
+ )
update_history(fileset_last, fileset_last, conn, log_last)
else:
update_history(0, fileset_last, conn)
with conn.cursor() as cursor:
- cursor.execute(f"INSERT INTO transactions (`transaction`, fileset) VALUES ({transaction}, {fileset_last})")
+ cursor.execute(
+ f"INSERT INTO transactions (`transaction`, fileset) VALUES ({transaction}, {fileset_last})"
+ )
return True
+
def insert_file(file, detection, src, conn):
# Find full md5, or else use first checksum value
checksum = ""
@@ -158,8 +188,10 @@ def insert_file(file, detection, src, conn):
if not detection:
checktype = "None"
detection = 0
- detection_type = f"{checktype}-{checksize}" if checktype != "None" else f"{checktype}"
- if punycode_need_encode(file['name']):
+ detection_type = (
+ f"{checktype}-{checksize}" if checktype != "None" else f"{checktype}"
+ )
+ if punycode_need_encode(file["name"]):
query = f"INSERT INTO file (name, size, checksum, fileset, detection, detection_type, `timestamp`) VALUES ('{encode_punycode(file['name'])}', '{file['size']}', '{checksum}', @fileset_last, {detection}, '{detection_type}', NOW())"
else:
query = f"INSERT INTO file (name, size, checksum, fileset, detection, detection_type, `timestamp`) VALUES ('{escape_string(file['name'])}', '{file['size']}', '{checksum}', @fileset_last, {detection}, '{detection_type}', NOW())"
@@ -168,10 +200,13 @@ def insert_file(file, detection, src, conn):
if detection:
with conn.cursor() as cursor:
- cursor.execute(f"UPDATE fileset SET detection_size = {checksize} WHERE id = @fileset_last AND detection_size IS NULL")
+ cursor.execute(
+ f"UPDATE fileset SET detection_size = {checksize} WHERE id = @fileset_last AND detection_size IS NULL"
+ )
with conn.cursor() as cursor:
cursor.execute("SET @file_last = LAST_INSERT_ID()")
+
def insert_filechecksum(file, checktype, conn):
if checktype not in file:
return
@@ -183,11 +218,13 @@ def insert_filechecksum(file, checktype, conn):
with conn.cursor() as cursor:
cursor.execute(query)
+
def delete_filesets(conn):
query = "DELETE FROM fileset WHERE `delete` = TRUE"
with conn.cursor() as cursor:
cursor.execute(query)
-
+
+
def my_escape_string(s: str) -> str:
"""
Escape strings
@@ -207,7 +244,7 @@ def my_escape_string(s: str) -> str:
new_name += char
return escape_string(new_name)
-
+
def encode_punycode(orig):
"""
Punyencode strings
@@ -226,6 +263,7 @@ def encode_punycode(orig):
return "xn--" + encoded
return orig
+
def punycode_need_encode(orig):
"""
A filename needs to be punyencoded when it:
@@ -233,14 +271,13 @@ def punycode_need_encode(orig):
- contains a char that should be escaped or
- ends with a dot or a space.
"""
- if not all(
- (0x20 <= ord(c) < 0x80) and
- c not in SPECIAL_SYMBOLS for c in orig):
+ if not all((0x20 <= ord(c) < 0x80) and c not in SPECIAL_SYMBOLS for c in orig):
return True
if orig[-1] in " .":
return True
return False
+
def create_log(category, user, text, conn):
query = f"INSERT INTO log (`timestamp`, category, user, `text`) VALUES (FROM_UNIXTIME({int(time.time())}), '{escape_string(category)}', '{escape_string(user)}', '{escape_string(text)}')"
with conn.cursor() as cursor:
@@ -253,9 +290,10 @@ def create_log(category, user, text, conn):
log_last = None
else:
cursor.execute("SELECT LAST_INSERT_ID()")
- log_last = cursor.fetchone()['LAST_INSERT_ID()']
+ log_last = cursor.fetchone()["LAST_INSERT_ID()"]
return log_last
+
def update_history(source_id, target_id, conn, log_last=None):
query = f"INSERT INTO history (`timestamp`, fileset, oldfileset, log) VALUES (NOW(), {target_id}, {source_id}, {log_last if log_last is not None else 0})"
with conn.cursor() as cursor:
@@ -268,77 +306,96 @@ def update_history(source_id, target_id, conn, log_last=None):
log_last = None
else:
cursor.execute("SELECT LAST_INSERT_ID()")
- log_last = cursor.fetchone()['LAST_INSERT_ID()']
+ log_last = cursor.fetchone()["LAST_INSERT_ID()"]
return log_last
+
def get_all_related_filesets(fileset_id, conn, visited=None):
if visited is None:
visited = set()
if fileset_id in visited or fileset_id == 0:
return []
-
+
visited.add(fileset_id)
related_filesets = [fileset_id]
try:
with conn.cursor() as cursor:
- cursor.execute(f"SELECT fileset, oldfileset FROM history WHERE fileset = {fileset_id} OR oldfileset = {fileset_id}")
+ cursor.execute(
+ f"SELECT fileset, oldfileset FROM history WHERE fileset = {fileset_id} OR oldfileset = {fileset_id}"
+ )
history_records = cursor.fetchall()
for record in history_records:
- if record['fileset'] not in visited:
- related_filesets.extend(get_all_related_filesets(record['fileset'], conn, visited))
- if record['oldfileset'] not in visited:
- related_filesets.extend(get_all_related_filesets(record['oldfileset'], conn, visited))
+ if record["fileset"] not in visited:
+ related_filesets.extend(
+ get_all_related_filesets(record["fileset"], conn, visited)
+ )
+ if record["oldfileset"] not in visited:
+ related_filesets.extend(
+ get_all_related_filesets(record["oldfileset"], conn, visited)
+ )
except pymysql.err.InterfaceError:
- print("Connection lost, reconnecting...")
- try:
- conn = db_connect() # Reconnect if the connection is lost
- except Exception as e:
- print(f"Failed to reconnect: {e}")
-
+ print("Connection lost, reconnecting...")
+ try:
+ conn = db_connect() # Reconnect if the connection is lost
+ except Exception as e:
+ print(f"Failed to reconnect: {e}")
+
except Exception as e:
print(f"Error fetching related filesets: {e}")
return related_filesets
+
def convert_log_text_to_links(log_text):
- log_text = re.sub(r'Fileset:(\d+)', r'<a href="/fileset?id=\1">Fileset:\1</a>', log_text)
- log_text = re.sub(r'user:(\w+)', r'<a href="/log?search=user:\1">user:\1</a>', log_text)
- log_text = re.sub(r'Transaction:(\d+)', r'<a href="/transaction?id=\1">Transaction:\1</a>', log_text)
+ log_text = re.sub(
+ r"Fileset:(\d+)", r'<a href="/fileset?id=\1">Fileset:\1</a>', log_text
+ )
+ log_text = re.sub(
+ r"user:(\w+)", r'<a href="/log?search=user:\1">user:\1</a>', log_text
+ )
+ log_text = re.sub(
+ r"Transaction:(\d+)",
+ r'<a href="/transaction?id=\1">Transaction:\1</a>',
+ log_text,
+ )
return log_text
+
def calc_key(fileset):
key_string = ""
for key, value in fileset.items():
- if key in ['engineid', 'gameid', 'rom']:
+ if key in ["engineid", "gameid", "rom"]:
continue
- key_string += ':' + str(value)
+ key_string += ":" + str(value)
- files = fileset['rom']
+ files = fileset["rom"]
for file in files:
for key, value in file.items():
- key_string += ':' + str(value)
+ key_string += ":" + str(value)
- key_string = key_string.strip(':')
+ key_string = key_string.strip(":")
return hashlib.md5(key_string.encode()).hexdigest()
+
def calc_megakey(fileset):
key_string = f":{fileset['platform']}:{fileset['language']}"
- if 'rom' in fileset.keys():
- for file in fileset['rom']:
+ if "rom" in fileset.keys():
+ for file in fileset["rom"]:
for key, value in file.items():
- key_string += ':' + str(value)
- elif 'files' in fileset.keys():
- for file in fileset['files']:
+ key_string += ":" + str(value)
+ elif "files" in fileset.keys():
+ for file in fileset["files"]:
for key, value in file.items():
- key_string += ':' + str(value)
+ key_string += ":" + str(value)
- key_string = key_string.strip(':')
+ key_string = key_string.strip(":")
return hashlib.md5(key_string.encode()).hexdigest()
+
def db_insert(data_arr, username=None, skiplog=False):
header = data_arr[0]
game_data = data_arr[1]
@@ -360,22 +417,22 @@ def db_insert(data_arr, username=None, skiplog=False):
src = "dat" if author not in ["scan", "scummvm"] else author
- detection = (src == "scummvm")
+ detection = src == "scummvm"
status = "detection" if detection else src
conn.cursor().execute(f"SET @fileset_time_last = {int(time.time())}")
with conn.cursor() as cursor:
cursor.execute("SELECT MAX(`transaction`) FROM transactions")
- temp = cursor.fetchone()['MAX(`transaction`)']
- if temp == None:
+ temp = cursor.fetchone()["MAX(`transaction`)"]
+ if temp is None:
temp = 0
transaction_id = temp + 1
category_text = f"Uploaded from {src}"
log_text = f"Started loading DAT file, size {os.path.getsize(filepath)}, author {author}, version {version}. State {status}. Transaction: {transaction_id}"
- user = f'cli:{getpass.getuser()}' if username is None else username
+ user = f"cli:{getpass.getuser()}" if username is None else username
create_log(escape_string(category_text), user, escape_string(log_text), conn)
for fileset in game_data:
@@ -388,16 +445,28 @@ def db_insert(data_arr, username=None, skiplog=False):
platform = fileset["platform"]
lang = fileset["language"]
- insert_game(engine_name, engineid, title, gameid, extra, platform, lang, conn)
+ insert_game(
+ engine_name, engineid, title, gameid, extra, platform, lang, conn
+ )
elif src == "dat":
- if 'romof' in fileset and fileset['romof'] in resources:
+ if "romof" in fileset and fileset["romof"] in resources:
fileset["rom"] = fileset["rom"] + resources[fileset["romof"]]["rom"]
key = calc_key(fileset) if not detection else ""
megakey = calc_megakey(fileset) if detection else ""
log_text = f"size {os.path.getsize(filepath)}, author {author}, version {version}. State {status}."
- if insert_fileset(src, detection, key, megakey, transaction_id, log_text, conn, username=username, skiplog=skiplog):
+ if insert_fileset(
+ src,
+ detection,
+ key,
+ megakey,
+ transaction_id,
+ log_text,
+ conn,
+ username=username,
+ skiplog=skiplog,
+ ):
for file in fileset["rom"]:
insert_file(file, detection, src, conn)
for key, value in file.items():
@@ -405,20 +474,25 @@ def db_insert(data_arr, username=None, skiplog=False):
insert_filechecksum(file, key, conn)
if detection:
- conn.cursor().execute("UPDATE fileset SET status = 'obsolete' WHERE `timestamp` != FROM_UNIXTIME(@fileset_time_last) AND status = 'detection'")
+ conn.cursor().execute(
+ "UPDATE fileset SET status = 'obsolete' WHERE `timestamp` != FROM_UNIXTIME(@fileset_time_last) AND status = 'detection'"
+ )
cur = conn.cursor()
-
+
try:
- cur.execute(f"SELECT COUNT(fileset) from transactions WHERE `transaction` = {transaction_id}")
- fileset_insertion_count = cur.fetchone()['COUNT(fileset)']
+ cur.execute(
+ f"SELECT COUNT(fileset) from transactions WHERE `transaction` = {transaction_id}"
+ )
+ fileset_insertion_count = cur.fetchone()["COUNT(fileset)"]
category_text = f"Uploaded from {src}"
log_text = f"Completed loading DAT file, filename {filepath}, size {os.path.getsize(filepath)}, author {author}, version {version}. State {status}. Number of filesets: {fileset_insertion_count}. Transaction: {transaction_id}"
except Exception as e:
print("Inserting failed:", e)
else:
- user = f'cli:{getpass.getuser()}' if username is None else username
+ user = f"cli:{getpass.getuser()}" if username is None else username
create_log(escape_string(category_text), user, escape_string(log_text), conn)
+
def compare_filesets(id1, id2, conn):
with conn.cursor() as cursor:
cursor.execute(f"SELECT name, size, checksum FROM file WHERE fileset = '{id1}'")
@@ -440,9 +514,11 @@ def compare_filesets(id1, id2, conn):
return True
+
def status_to_match(status):
order = ["detection", "dat", "scan", "partialmatch", "fullmatch", "user"]
- return order[:order.index(status)]
+ return order[: order.index(status)]
+
def find_matching_game(game_files):
matching_games = [] # All matching games
@@ -470,8 +546,10 @@ def find_matching_game(game_files):
# Check if there is a fileset_id that is present in all results
for key, value in Counter(matching_filesets).items():
with conn.cursor() as cursor:
- cursor.execute(f"SELECT COUNT(file.id) FROM file JOIN fileset ON file.fileset = fileset.id WHERE fileset.id = '{key}'")
- count_files_in_fileset = cursor.fetchone()['COUNT(file.id)']
+ cursor.execute(
+ f"SELECT COUNT(file.id) FROM file JOIN fileset ON file.fileset = fileset.id WHERE fileset.id = '{key}'"
+ )
+ count_files_in_fileset = cursor.fetchone()["COUNT(file.id)"]
# We use < instead of != since one file may have more than one entry in the fileset
# We see this in Drascula English version, where one entry is duplicated
@@ -479,7 +557,9 @@ def find_matching_game(game_files):
continue
with conn.cursor() as cursor:
- cursor.execute(f"SELECT engineid, game.id, gameid, platform, language, `key`, src, fileset.id as fileset FROM game JOIN fileset ON fileset.game = game.id JOIN engine ON engine.id = game.engine WHERE fileset.id = '{key}'")
+ cursor.execute(
+ f"SELECT engineid, game.id, gameid, platform, language, `key`, src, fileset.id as fileset FROM game JOIN fileset ON fileset.game = game.id JOIN engine ON engine.id = game.engine WHERE fileset.id = '{key}'"
+ )
records = cursor.fetchall()
matching_games.append(records[0])
@@ -489,26 +569,34 @@ def find_matching_game(game_files):
# Check the current fileset priority with that of the match
with conn.cursor() as cursor:
- cursor.execute(f"SELECT id FROM fileset, ({query}) AS res WHERE id = file_fileset AND status IN ({', '.join(['%s']*len(game_files[3]))})", status_to_match(game_files[3]))
+ cursor.execute(
+ f"SELECT id FROM fileset, ({query}) AS res WHERE id = file_fileset AND status IN ({', '.join(['%s'] * len(game_files[3]))})",
+ status_to_match(game_files[3]),
+ )
records = cursor.fetchall()
# If priority order is correct
if len(records) != 0:
return matching_games
- if compare_filesets(matching_games[0]['fileset'], game_files[0][0], conn):
+ if compare_filesets(matching_games[0]["fileset"], game_files[0][0], conn):
with conn.cursor() as cursor:
- cursor.execute(f"UPDATE fileset SET `delete` = TRUE WHERE id = {game_files[0][0]}")
+ cursor.execute(
+ f"UPDATE fileset SET `delete` = TRUE WHERE id = {game_files[0][0]}"
+ )
return []
return matching_games
+
def merge_filesets(detection_id, dat_id):
conn = db_connect()
try:
with conn.cursor() as cursor:
- cursor.execute(f"SELECT DISTINCT(filechecksum.checksum), checksize, checktype FROM filechecksum JOIN file on file.id = filechecksum.file WHERE fileset = '{detection_id}'")
+ cursor.execute(
+ f"SELECT DISTINCT(filechecksum.checksum), checksize, checktype FROM filechecksum JOIN file on file.id = filechecksum.file WHERE fileset = '{detection_id}'"
+ )
detection_files = cursor.fetchall()
for file in detection_files:
@@ -516,14 +604,22 @@ def merge_filesets(detection_id, dat_id):
checksize = file[1]
checktype = file[2]
- cursor.execute(f"DELETE FROM file WHERE checksum = '{checksum}' AND fileset = {detection_id} LIMIT 1")
- cursor.execute(f"UPDATE file JOIN filechecksum ON filechecksum.file = file.id SET detection = TRUE, checksize = {checksize}, checktype = '{checktype}' WHERE fileset = '{dat_id}' AND filechecksum.checksum = '{checksum}'")
+ cursor.execute(
+ f"DELETE FROM file WHERE checksum = '{checksum}' AND fileset = {detection_id} LIMIT 1"
+ )
+ cursor.execute(
+ f"UPDATE file JOIN filechecksum ON filechecksum.file = file.id SET detection = TRUE, checksize = {checksize}, checktype = '{checktype}' WHERE fileset = '{dat_id}' AND filechecksum.checksum = '{checksum}'"
+ )
- cursor.execute(f"INSERT INTO history (`timestamp`, fileset, oldfileset) VALUES (FROM_UNIXTIME({int(time.time())}), {dat_id}, {detection_id})")
+ cursor.execute(
+ f"INSERT INTO history (`timestamp`, fileset, oldfileset) VALUES (FROM_UNIXTIME({int(time.time())}), {dat_id}, {detection_id})"
+ )
cursor.execute("SELECT LAST_INSERT_ID()")
- history_last = cursor.fetchone()['LAST_INSERT_ID()']
+ history_last = cursor.fetchone()["LAST_INSERT_ID()"]
- cursor.execute(f"UPDATE history SET fileset = {dat_id} WHERE fileset = {detection_id}")
+ cursor.execute(
+ f"UPDATE history SET fileset = {dat_id} WHERE fileset = {detection_id}"
+ )
cursor.execute(f"DELETE FROM fileset WHERE id = {detection_id}")
conn.commit()
@@ -544,7 +640,9 @@ def populate_matching_games():
unmatched_filesets = []
with conn.cursor() as cursor:
- cursor.execute("SELECT fileset.id, filechecksum.checksum, src, status FROM fileset JOIN file ON file.fileset = fileset.id JOIN filechecksum ON file.id = filechecksum.file WHERE fileset.game IS NULL AND status != 'user'")
+ cursor.execute(
+ "SELECT fileset.id, filechecksum.checksum, src, status FROM fileset JOIN file ON file.fileset = fileset.id JOIN filechecksum ON file.id = filechecksum.file WHERE fileset.game IS NULL AND status != 'user'"
+ )
unmatched_files = cursor.fetchall()
# Splitting them into different filesets
@@ -560,7 +658,7 @@ def populate_matching_games():
for fileset in unmatched_filesets:
matching_games = find_matching_game(fileset)
- if len(matching_games) != 1: # If there is no match/non-unique match
+ if len(matching_games) != 1: # If there is no match/non-unique match
continue
matched_game = matching_games[0]
@@ -573,7 +671,7 @@ def populate_matching_games():
status = "fullmatch"
# Convert NULL values to string with value NULL for printing
- matched_game = {k: 'NULL' if v is None else v for k, v in matched_game.items()}
+ matched_game = {k: "NULL" if v is None else v for k, v in matched_game.items()}
category_text = f"Matched from {fileset[0][2]}"
log_text = f"Matched game {matched_game['engineid']}:\n{matched_game['gameid']}-{matched_game['platform']}-{matched_game['language']}\nvariant {matched_game['key']}. State {status}. Fileset:{fileset[0][0]}."
@@ -584,21 +682,33 @@ def populate_matching_games():
history_last = merge_filesets(matched_game["fileset"], fileset[0][0])
if cursor.execute(query):
- user = f'cli:{getpass.getuser()}'
+ user = f"cli:{getpass.getuser()}"
- create_log("Fileset merge", user, escape_string(f"Merged Fileset:{matched_game['fileset']} and Fileset:{fileset[0][0]}"), conn)
+ create_log(
+ "Fileset merge",
+ user,
+ escape_string(
+ f"Merged Fileset:{matched_game['fileset']} and Fileset:{fileset[0][0]}"
+ ),
+ conn,
+ )
# Matching log
- log_last = create_log(escape_string(conn, category_text), user, escape_string(conn, log_text))
+ log_last = create_log(
+ escape_string(conn, category_text), user, escape_string(conn, log_text)
+ )
# Add log id to the history table
- cursor.execute(f"UPDATE history SET log = {log_last} WHERE id = {history_last}")
+ cursor.execute(
+ f"UPDATE history SET log = {log_last} WHERE id = {history_last}"
+ )
try:
conn.commit()
- except:
+ except Exception:
print("Updating matched games failed")
-
+
+
def match_fileset(data_arr, username=None):
header, game_data, resources, filepath = data_arr
@@ -616,30 +726,57 @@ def match_fileset(data_arr, username=None):
return
src = "dat" if author not in ["scan", "scummvm"] else author
- detection = (src == "scummvm")
+ detection = src == "scummvm"
source_status = "detection" if detection else src
conn.cursor().execute(f"SET @fileset_time_last = {int(time.time())}")
with conn.cursor() as cursor:
cursor.execute("SELECT MAX(`transaction`) FROM transactions")
- transaction_id = cursor.fetchone()['MAX(`transaction`)']
+ transaction_id = cursor.fetchone()["MAX(`transaction`)"]
transaction_id = transaction_id + 1 if transaction_id else 1
category_text = f"Uploaded from {src}"
log_text = f"Started loading DAT file, size {os.path.getsize(filepath)}, author {author}, version {version}. State {source_status}. Transaction: {transaction_id}"
- user = f'cli:{getpass.getuser()}' if username is None else username
+ user = f"cli:{getpass.getuser()}" if username is None else username
create_log(escape_string(category_text), user, escape_string(log_text), conn)
for fileset in game_data:
- process_fileset(fileset, resources, detection, src, conn, transaction_id, filepath, author, version, source_status, user)
- finalize_fileset_insertion(conn, transaction_id, src, filepath, author, version, source_status, user)
+ process_fileset(
+ fileset,
+ resources,
+ detection,
+ src,
+ conn,
+ transaction_id,
+ filepath,
+ author,
+ version,
+ source_status,
+ user,
+ )
+ finalize_fileset_insertion(
+ conn, transaction_id, src, filepath, author, version, source_status, user
+ )
+
-def process_fileset(fileset, resources, detection, src, conn, transaction_id, filepath, author, version, source_status, user):
+def process_fileset(
+ fileset,
+ resources,
+ detection,
+ src,
+ conn,
+ transaction_id,
+ filepath,
+ author,
+ version,
+ source_status,
+ user,
+):
if detection:
insert_game_data(fileset, conn)
- elif src == "dat" and 'romof' in fileset and fileset['romof'] in resources:
+ elif src == "dat" and "romof" in fileset and fileset["romof"] in resources:
fileset["rom"] += resources[fileset["romof"]]["rom"]
key = calc_key(fileset) if not detection else ""
@@ -650,14 +787,28 @@ def process_fileset(fileset, resources, detection, src, conn, transaction_id, fi
else:
matched_map = matching_set(fileset, conn)
-
- insert_new_fileset(fileset, conn, detection, src, key, megakey, transaction_id, log_text, user)
+ insert_new_fileset(
+ fileset, conn, detection, src, key, megakey, transaction_id, log_text, user
+ )
with conn.cursor() as cursor:
cursor.execute("SET @fileset_last = LAST_INSERT_ID()")
cursor.execute("SELECT LAST_INSERT_ID()")
- fileset_last = cursor.fetchone()['LAST_INSERT_ID()']
+ fileset_last = cursor.fetchone()["LAST_INSERT_ID()"]
if matched_map:
- handle_matched_filesets(fileset_last, matched_map, fileset, conn, detection, src, key, megakey, transaction_id, log_text, user)
+ handle_matched_filesets(
+ fileset_last,
+ matched_map,
+ fileset,
+ conn,
+ detection,
+ src,
+ key,
+ megakey,
+ transaction_id,
+ log_text,
+ user,
+ )
+
def insert_game_data(fileset, conn):
engine_name = fileset["engine"]
@@ -669,6 +820,7 @@ def insert_game_data(fileset, conn):
lang = fileset["language"]
insert_game(engine_name, engineid, title, gameid, extra, platform, lang, conn)
+
def find_matching_filesets(fileset, conn, status):
matched_map = defaultdict(list)
if status != "user":
@@ -682,7 +834,9 @@ def find_matching_filesets(fileset, conn, status):
if key not in ["name", "size", "sha1", "crc"]:
checksum = file[key]
checktype = key
- checksize, checktype, checksum = get_checksum_props(checktype, checksum)
+ checksize, checktype, checksum = get_checksum_props(
+ checktype, checksum
+ )
query = f"""SELECT DISTINCT fs.id AS fileset_id
FROM fileset fs
JOIN file f ON fs.id = f.fileset
@@ -693,13 +847,14 @@ def find_matching_filesets(fileset, conn, status):
records = cursor.fetchall()
if records:
for record in records:
- matched_set.add(record['fileset_id'])
+ matched_set.add(record["fileset_id"])
for id in matched_set:
matched_map[id].append(file)
-
+
return matched_map
+
def matching_set(fileset, conn):
matched_map = defaultdict(list)
with conn.cursor() as cursor:
@@ -721,50 +876,86 @@ def matching_set(fileset, conn):
records = cursor.fetchall()
if records:
for record in records:
- matched_set.add(record['fileset_id'])
+ matched_set.add(record["fileset_id"])
for id in matched_set:
matched_map[id].append(file)
return matched_map
-def handle_matched_filesets(fileset_last, matched_map, fileset, conn, detection, src, key, megakey, transaction_id, log_text, user):
+
+def handle_matched_filesets(
+ fileset_last,
+ matched_map,
+ fileset,
+ conn,
+ detection,
+ src,
+ key,
+ megakey,
+ transaction_id,
+ log_text,
+ user,
+):
matched_list = sorted(matched_map.items(), key=lambda x: len(x[1]), reverse=True)
is_full_matched = False
with conn.cursor() as cursor:
for matched_fileset_id, matched_count in matched_list:
if is_full_matched:
break
- cursor.execute(f"SELECT status FROM fileset WHERE id = {matched_fileset_id}")
- status = cursor.fetchone()['status']
- cursor.execute(f"SELECT COUNT(file.id) FROM file WHERE fileset = {matched_fileset_id}")
- count = cursor.fetchone()['COUNT(file.id)']
-
- if status in ['detection', 'obsolete'] and count == len(matched_count):
+ cursor.execute(
+ f"SELECT status FROM fileset WHERE id = {matched_fileset_id}"
+ )
+ status = cursor.fetchone()["status"]
+ cursor.execute(
+ f"SELECT COUNT(file.id) FROM file WHERE fileset = {matched_fileset_id}"
+ )
+ count = cursor.fetchone()["COUNT(file.id)"]
+
+ if status in ["detection", "obsolete"] and count == len(matched_count):
is_full_matched = True
- update_fileset_status(cursor, matched_fileset_id, 'full' if src != "dat" else "partial")
+ update_fileset_status(
+ cursor, matched_fileset_id, "full" if src != "dat" else "partial"
+ )
populate_file(fileset, matched_fileset_id, conn, detection)
- log_matched_fileset(src, fileset_last, matched_fileset_id, 'full' if src != "dat" else "partial", user, conn)
+ log_matched_fileset(
+ src,
+ fileset_last,
+ matched_fileset_id,
+ "full" if src != "dat" else "partial",
+ user,
+ conn,
+ )
delete_original_fileset(fileset_last, conn)
- elif status == 'full' and len(fileset['rom']) == count:
+ elif status == "full" and len(fileset["rom"]) == count:
is_full_matched = True
- log_matched_fileset(src, fileset_last, matched_fileset_id, 'full', user, conn)
+ log_matched_fileset(
+ src, fileset_last, matched_fileset_id, "full", user, conn
+ )
delete_original_fileset(fileset_last, conn)
return
- elif (status == 'partial') and count == len(matched_count):
+ elif (status == "partial") and count == len(matched_count):
is_full_matched = True
- update_fileset_status(cursor, matched_fileset_id, 'full')
+ update_fileset_status(cursor, matched_fileset_id, "full")
populate_file(fileset, matched_fileset_id, conn, detection)
- log_matched_fileset(src, fileset_last, matched_fileset_id, 'full', user, conn)
+ log_matched_fileset(
+ src, fileset_last, matched_fileset_id, "full", user, conn
+ )
delete_original_fileset(fileset_last, conn)
- elif status == 'scan' and count == len(matched_count):
- log_matched_fileset(src, fileset_last, matched_fileset_id, 'full', user, conn)
- elif src == 'dat':
- log_matched_fileset(src, fileset_last, matched_fileset_id, 'partial matched', user, conn)
+ elif status == "scan" and count == len(matched_count):
+ log_matched_fileset(
+ src, fileset_last, matched_fileset_id, "full", user, conn
+ )
+ elif src == "dat":
+ log_matched_fileset(
+ src, fileset_last, matched_fileset_id, "partial matched", user, conn
+ )
+
def delete_original_fileset(fileset_id, conn):
with conn.cursor() as cursor:
cursor.execute(f"DELETE FROM file WHERE fileset = {fileset_id}")
cursor.execute(f"DELETE FROM fileset WHERE id = {fileset_id}")
-
+
+
def update_fileset_status(cursor, fileset_id, status):
cursor.execute(f"""
UPDATE fileset SET
@@ -773,18 +964,23 @@ def update_fileset_status(cursor, fileset_id, status):
WHERE id = {fileset_id}
""")
+
def populate_file(fileset, fileset_id, conn, detection):
with conn.cursor() as cursor:
cursor.execute(f"SELECT * FROM file WHERE fileset = {fileset_id}")
target_files = cursor.fetchall()
target_files_dict = {}
for target_file in target_files:
- cursor.execute(f"SELECT * FROM filechecksum WHERE file = {target_file['id']}")
+ cursor.execute(
+ f"SELECT * FROM filechecksum WHERE file = {target_file['id']}"
+ )
target_checksums = cursor.fetchall()
for checksum in target_checksums:
- target_files_dict[checksum['checksum']] = target_file
- target_files_dict[target_file['id']] = f"{checksum['checktype']}-{checksum['checksize']}"
- for file in fileset['rom']:
+ target_files_dict[checksum["checksum"]] = target_file
+ target_files_dict[target_file["id"]] = (
+ f"{checksum['checktype']}-{checksum['checksize']}"
+ )
+ for file in fileset["rom"]:
file_exists = False
checksum = ""
checksize = 5000
@@ -799,65 +995,94 @@ def populate_file(fileset, fileset_id, conn, detection):
if not detection:
checktype = "None"
detection = 0
- detection_type = f"{checktype}-{checksize}" if checktype != "None" else f"{checktype}"
- if punycode_need_encode(file['name']):
- print(encode_punycode(file['name']))
+ detection_type = (
+ f"{checktype}-{checksize}" if checktype != "None" else f"{checktype}"
+ )
+ if punycode_need_encode(file["name"]):
+ print(encode_punycode(file["name"]))
query = f"INSERT INTO file (name, size, checksum, fileset, detection, detection_type, `timestamp`) VALUES ('{encode_punycode(file['name'])}', '{file['size']}', '{checksum}', @fileset_last, {detection}, '{detection_type}', NOW())"
else:
query = f"INSERT INTO file (name, size, checksum, fileset, detection, detection_type, `timestamp`) VALUES ('{escape_string(file['name'])}', '{file['size']}', '{checksum}', @fileset_last, {detection}, '{detection_type}', NOW())"
cursor.execute(query)
cursor.execute("SET @file_last = LAST_INSERT_ID()")
cursor.execute("SELECT @file_last AS file_id")
- file_id = cursor.fetchone()['file_id']
+ file_id = cursor.fetchone()["file_id"]
target_id = None
for key, value in file.items():
if key not in ["name", "size"]:
insert_filechecksum(file, key, conn)
if value in target_files_dict and not file_exists:
file_exists = True
- target_id = target_files_dict[value]['id']
- cursor.execute(f"DELETE FROM file WHERE id = {target_files_dict[value]['id']}")
-
+ target_id = target_files_dict[value]["id"]
+ cursor.execute(
+ f"DELETE FROM file WHERE id = {target_files_dict[value]['id']}"
+ )
+
if file_exists:
cursor.execute(f"UPDATE file SET detection = 1 WHERE id = {file_id}")
- cursor.execute(f"UPDATE file SET detection_type = '{target_files_dict[target_id]}' WHERE id = {file_id}")
+ cursor.execute(
+ f"UPDATE file SET detection_type = '{target_files_dict[target_id]}' WHERE id = {file_id}"
+ )
else:
- cursor.execute(f"UPDATE file SET detection_type = 'None' WHERE id = {file_id}")
-
-def insert_new_fileset(fileset, conn, detection, src, key, megakey, transaction_id, log_text, user, ip=''):
- if insert_fileset(src, detection, key, megakey, transaction_id, log_text, conn, username=user, ip=ip):
+ cursor.execute(
+ f"UPDATE file SET detection_type = 'None' WHERE id = {file_id}"
+ )
+
+
+def insert_new_fileset(
+ fileset, conn, detection, src, key, megakey, transaction_id, log_text, user, ip=""
+):
+ if insert_fileset(
+ src,
+ detection,
+ key,
+ megakey,
+ transaction_id,
+ log_text,
+ conn,
+ username=user,
+ ip=ip,
+ ):
for file in fileset["rom"]:
insert_file(file, detection, src, conn)
for key, value in file.items():
if key not in ["name", "size", "sha1", "crc"]:
insert_filechecksum(file, key, conn)
+
def log_matched_fileset(src, fileset_last, fileset_id, state, user, conn):
category_text = f"Matched from {src}"
log_text = f"Matched Fileset:{fileset_id}. State {state}."
- log_last = create_log(escape_string(category_text), user, escape_string(log_text), conn)
+ log_last = create_log(
+ escape_string(category_text), user, escape_string(log_text), conn
+ )
update_history(fileset_last, fileset_id, conn, log_last)
-def finalize_fileset_insertion(conn, transaction_id, src, filepath, author, version, source_status, user):
+
+def finalize_fileset_insertion(
+ conn, transaction_id, src, filepath, author, version, source_status, user
+):
with conn.cursor() as cursor:
- cursor.execute(f"SELECT COUNT(fileset) from transactions WHERE `transaction` = {transaction_id}")
- fileset_insertion_count = cursor.fetchone()['COUNT(fileset)']
+ cursor.execute(
+ f"SELECT COUNT(fileset) from transactions WHERE `transaction` = {transaction_id}"
+ )
+ fileset_insertion_count = cursor.fetchone()["COUNT(fileset)"]
category_text = f"Uploaded from {src}"
- if src != 'user':
+ if src != "user":
log_text = f"Completed loading DAT file, filename {filepath}, size {os.path.getsize(filepath)}, author {author}, version {version}. State {source_status}. Number of filesets: {fileset_insertion_count}. Transaction: {transaction_id}"
- create_log(escape_string(category_text), user, escape_string(log_text), conn)
+ create_log(
+ escape_string(category_text), user, escape_string(log_text), conn
+ )
# conn.close()
+
def user_integrity_check(data, ip, game_metadata=None):
src = "user"
source_status = src
new_files = []
for file in data["files"]:
- new_file = {
- "name": file["name"],
- "size": file["size"]
- }
+ new_file = {"name": file["name"], "size": file["size"]}
for checksum in file["checksums"]:
checksum_type = checksum["type"]
checksum_value = checksum["checksum"]
@@ -872,111 +1097,146 @@ def user_integrity_check(data, ip, game_metadata=None):
except Exception as e:
print(f"Failed to connect to database: {e}")
return
-
+
conn.cursor().execute(f"SET @fileset_time_last = {int(time.time())}")
try:
with conn.cursor() as cursor:
cursor.execute("SELECT MAX(`transaction`) FROM transactions")
- transaction_id = cursor.fetchone()['MAX(`transaction`)'] + 1
+ transaction_id = cursor.fetchone()["MAX(`transaction`)"] + 1
category_text = f"Uploaded from {src}"
log_text = f"Started loading file, State {source_status}. Transaction: {transaction_id}"
- user = f'cli:{getpass.getuser()}'
+ user = f"cli:{getpass.getuser()}"
+
+ create_log(
+ escape_string(category_text), user, escape_string(log_text), conn
+ )
- create_log(escape_string(category_text), user, escape_string(log_text), conn)
-
matched_map = find_matching_filesets(data, conn, src)
-
+
# show matched, missing, extra
extra_map = defaultdict(list)
missing_map = defaultdict(list)
extra_set = set()
missing_set = set()
-
+
for fileset_id in matched_map.keys():
cursor.execute(f"SELECT * FROM file WHERE fileset = {fileset_id}")
target_files = cursor.fetchall()
target_files_dict = {}
for target_file in target_files:
- cursor.execute(f"SELECT * FROM filechecksum WHERE file = {target_file['id']}")
+ cursor.execute(
+ f"SELECT * FROM filechecksum WHERE file = {target_file['id']}"
+ )
target_checksums = cursor.fetchall()
for checksum in target_checksums:
- target_files_dict[checksum['checksum']] = target_file
+ target_files_dict[checksum["checksum"]] = target_file
# target_files_dict[target_file['id']] = f"{checksum['checktype']}-{checksum['checksize']}"
-
+
# Collect all the checksums from data['files']
data_files_set = set()
for file in data["files"]:
for checksum_info in file["checksums"]:
checksum = checksum_info["checksum"]
checktype = checksum_info["type"]
- checksize, checktype, checksum = get_checksum_props(checktype, checksum)
+ checksize, checktype, checksum = get_checksum_props(
+ checktype, checksum
+ )
data_files_set.add(checksum)
-
+
# Identify missing files
matched_names = set()
for checksum, target_file in target_files_dict.items():
if checksum not in data_files_set:
- if target_file['name'] not in matched_names:
- missing_set.add(target_file['name'])
+ if target_file["name"] not in matched_names:
+ missing_set.add(target_file["name"])
else:
- missing_set.discard(target_file['name'])
+ missing_set.discard(target_file["name"])
else:
- matched_names.add(target_file['name'])
-
+ matched_names.add(target_file["name"])
+
for tar in missing_set:
- missing_map[fileset_id].append({'name': tar})
+ missing_map[fileset_id].append({"name": tar})
# Identify extra files
- for file in data['files']:
+ for file in data["files"]:
file_exists = False
for checksum_info in file["checksums"]:
checksum = checksum_info["checksum"]
checktype = checksum_info["type"]
- checksize, checktype, checksum = get_checksum_props(checktype, checksum)
+ checksize, checktype, checksum = get_checksum_props(
+ checktype, checksum
+ )
if checksum in target_files_dict and not file_exists:
file_exists = True
if not file_exists:
- extra_set.add(file['name'])
-
+ extra_set.add(file["name"])
+
for extra in extra_set:
- extra_map[fileset_id].append({'name': extra})
+ extra_map[fileset_id].append({"name": extra})
if game_metadata:
- platform = game_metadata['platform']
- lang = game_metadata['language']
- gameid = game_metadata['gameid']
- engineid = game_metadata['engineid']
- extra_info = game_metadata['extra']
+ platform = game_metadata["platform"]
+ lang = game_metadata["language"]
+ gameid = game_metadata["gameid"]
+ engineid = game_metadata["engineid"]
+ extra_info = game_metadata["extra"]
engine_name = " "
title = " "
- insert_game(engine_name, engineid, title, gameid, extra_info, platform, lang, conn)
-
+ insert_game(
+ engine_name,
+ engineid,
+ title,
+ gameid,
+ extra_info,
+ platform,
+ lang,
+ conn,
+ )
+
# handle different scenarios
- if len(matched_map) == 0:
- insert_new_fileset(data, conn, None, src, key, None, transaction_id, log_text, user, ip)
+ if len(matched_map) == 0:
+ insert_new_fileset(
+ data, conn, None, src, key, None, transaction_id, log_text, user, ip
+ )
return matched_map, missing_map, extra_map
- matched_list = sorted(matched_map.items(), key=lambda x: len(x[1]), reverse=True)
- most_matched = matched_list[0]
- matched_fileset_id, matched_count = most_matched[0], most_matched[1]
- cursor.execute(f"SELECT status FROM fileset WHERE id = {matched_fileset_id}")
- status = cursor.fetchone()['status']
-
- cursor.execute(f"SELECT COUNT(file.id) FROM file WHERE fileset = {matched_fileset_id}")
- count = cursor.fetchone()['COUNT(file.id)']
+ matched_list = sorted(
+ matched_map.items(), key=lambda x: len(x[1]), reverse=True
+ )
+ most_matched = matched_list[0]
+ matched_fileset_id, matched_count = most_matched[0], most_matched[1]
+ cursor.execute(
+ f"SELECT status FROM fileset WHERE id = {matched_fileset_id}"
+ )
+ status = cursor.fetchone()["status"]
+
+ cursor.execute(
+ f"SELECT COUNT(file.id) FROM file WHERE fileset = {matched_fileset_id}"
+ )
+ count = cursor.fetchone()["COUNT(file.id)"]
if status == "full" and count == matched_count:
- log_matched_fileset(src, matched_fileset_id, matched_fileset_id, 'full', user, conn)
+ log_matched_fileset(
+ src, matched_fileset_id, matched_fileset_id, "full", user, conn
+ )
elif status == "partial" and count == matched_count:
populate_file(data, matched_fileset_id, conn, None, src)
- log_matched_fileset(src, matched_fileset_id, matched_fileset_id, 'partial', user, conn)
+ log_matched_fileset(
+ src, matched_fileset_id, matched_fileset_id, "partial", user, conn
+ )
elif status == "user" and count == matched_count:
add_usercount(matched_fileset_id, conn)
- log_matched_fileset(src, matched_fileset_id, matched_fileset_id, 'user', user, conn)
+ log_matched_fileset(
+ src, matched_fileset_id, matched_fileset_id, "user", user, conn
+ )
else:
- insert_new_fileset(data, conn, None, src, key, None, transaction_id, log_text, user, ip)
- finalize_fileset_insertion(conn, transaction_id, src, None, user, 0, source_status, user)
+ insert_new_fileset(
+ data, conn, None, src, key, None, transaction_id, log_text, user, ip
+ )
+ finalize_fileset_insertion(
+ conn, transaction_id, src, None, user, 0, source_status, user
+ )
except Exception as e:
conn.rollback()
print(f"Error processing user data: {e}")
@@ -987,10 +1247,15 @@ def user_integrity_check(data, ip, game_metadata=None):
# conn.close()
return matched_map, missing_map, extra_map
+
def add_usercount(fileset, conn):
with conn.cursor() as cursor:
- cursor.execute(f"UPDATE fileset SET user_count = COALESCE(user_count, 0) + 1 WHERE id = {fileset}")
+ cursor.execute(
+ f"UPDATE fileset SET user_count = COALESCE(user_count, 0) + 1 WHERE id = {fileset}"
+ )
cursor.execute(f"SELECT user_count from fileset WHERE id = {fileset}")
- count = cursor.fetchone()['user_count']
+ count = cursor.fetchone()["user_count"]
if count >= 3:
- cursor.execute(f"UPDATE fileset SET status = 'ReadyForReview' WHERE id = {fileset}")
+ cursor.execute(
+ f"UPDATE fileset SET status = 'ReadyForReview' WHERE id = {fileset}"
+ )
diff --git a/fileset.py b/fileset.py
index 497a9b6..9455624 100644
--- a/fileset.py
+++ b/fileset.py
@@ -1,13 +1,29 @@
-from flask import Flask, request, render_template, redirect, url_for, render_template_string, jsonify, flash
+from flask import (
+ Flask,
+ request,
+ redirect,
+ url_for,
+ render_template_string,
+ jsonify,
+)
import pymysql.cursors
import json
-import re
import os
-from user_fileset_functions import user_calc_key, file_json_to_array, user_insert_queue, user_insert_fileset, match_and_merge_user_filesets
+from user_fileset_functions import (
+ user_insert_fileset,
+ match_and_merge_user_filesets,
+)
from pagination import create_page
import difflib
from pymysql.converters import escape_string
-from db_functions import find_matching_filesets, get_all_related_filesets, convert_log_text_to_links, user_integrity_check, db_connect,create_log
+from db_functions import (
+ find_matching_filesets,
+ get_all_related_filesets,
+ convert_log_text_to_links,
+ user_integrity_check,
+ db_connect,
+ create_log,
+)
from collections import defaultdict
app = Flask(__name__)
@@ -15,7 +31,7 @@ app = Flask(__name__)
secret_key = os.urandom(24)
base_dir = os.path.dirname(os.path.abspath(__file__))
-config_path = os.path.join(base_dir, 'mysql_config.json')
+config_path = os.path.join(base_dir, "mysql_config.json")
with open(config_path) as f:
mysql_cred = json.load(f)
@@ -24,12 +40,13 @@ conn = pymysql.connect(
user=mysql_cred["username"],
password=mysql_cred["password"],
db=mysql_cred["dbname"],
- charset='utf8mb4',
+ charset="utf8mb4",
cursorclass=pymysql.cursors.DictCursor,
- autocommit=False
+ autocommit=False,
)
- at app.route('/')
+
+ at app.route("/")
def index():
html = """
<!DOCTYPE html>
@@ -55,36 +72,39 @@ def index():
"""
return render_template_string(html)
- at app.route('/fileset', methods=['GET', 'POST'])
+
+ at app.route("/fileset", methods=["GET", "POST"])
def fileset():
- id = request.args.get('id', default=1, type=int)
- widetable = request.args.get('widetable', default='partial', type=str)
+ id = request.args.get("id", default=1, type=int)
+ widetable = request.args.get("widetable", default="partial", type=str)
# Load MySQL credentials from a JSON file
base_dir = os.path.dirname(os.path.abspath(__file__))
- config_path = os.path.join(base_dir, 'mysql_config.json')
+ config_path = os.path.join(base_dir, "mysql_config.json")
with open(config_path) as f:
mysql_cred = json.load(f)
# Create a connection to the MySQL server
- connection = pymysql.connect(host=mysql_cred["servername"],
- user=mysql_cred["username"],
- password=mysql_cred["password"],
- db=mysql_cred["dbname"],
- charset='utf8mb4',
- cursorclass=pymysql.cursors.DictCursor)
+ connection = pymysql.connect(
+ host=mysql_cred["servername"],
+ user=mysql_cred["username"],
+ password=mysql_cred["password"],
+ db=mysql_cred["dbname"],
+ charset="utf8mb4",
+ cursorclass=pymysql.cursors.DictCursor,
+ )
try:
with connection.cursor() as cursor:
# Get the minimum id from the fileset table
cursor.execute("SELECT MIN(id) FROM fileset")
- min_id = cursor.fetchone()['MIN(id)']
+ min_id = cursor.fetchone()["MIN(id)"]
# Get the id from the GET parameters, or use the minimum id if it's not provided
- id = request.args.get('id', default=min_id, type=int)
+ id = request.args.get("id", default=min_id, type=int)
# Get the maximum id from the fileset table
cursor.execute("SELECT MAX(id) FROM fileset")
- max_id = cursor.fetchone()['MAX(id)']
+ max_id = cursor.fetchone()["MAX(id)"]
# Ensure the id is between the minimum and maximum id
id = max(min_id, min(id, max_id))
@@ -94,10 +114,12 @@ def fileset():
if cursor.rowcount == 0:
# If the id doesn't exist, get a new id from the history table
cursor.execute(f"SELECT fileset FROM history WHERE oldfileset = {id}")
- id = cursor.fetchone()['fileset']
+ id = cursor.fetchone()["fileset"]
# Get the history for the current id
- cursor.execute(f"SELECT `timestamp`, oldfileset, log FROM history WHERE fileset = {id} ORDER BY `timestamp`")
+ cursor.execute(
+ f"SELECT `timestamp`, oldfileset, log FROM history WHERE fileset = {id} ORDER BY `timestamp`"
+ )
history = cursor.fetchall()
# Display fileset details
@@ -123,21 +145,23 @@ def fileset():
print(result)
html += "<h3>Fileset details</h3>"
html += "<table>\n"
- if result['game']:
- cursor.execute(f"SELECT game.name as 'game name', engineid, gameid, extra, platform, language FROM fileset JOIN game ON game.id = fileset.game JOIN engine ON engine.id = game.engine WHERE fileset.id = {id}")
+ if result["game"]:
+ cursor.execute(
+ f"SELECT game.name as 'game name', engineid, gameid, extra, platform, language FROM fileset JOIN game ON game.id = fileset.game JOIN engine ON engine.id = game.engine WHERE fileset.id = {id}"
+ )
result = {**result, **cursor.fetchone()}
else:
# result.pop('key', None)
# result.pop('status', None)
- result.pop('delete', None)
+ result.pop("delete", None)
for column in result.keys():
- if column != 'id' and column != 'game':
+ if column != "id" and column != "game":
html += f"<th>{column}</th>\n"
html += "<tr>\n"
for column, value in result.items():
- if column != 'id' and column != 'game':
+ if column != "id" and column != "game":
html += f"<td>{value}</td>"
html += "</tr>\n"
html += "</table>\n"
@@ -146,9 +170,9 @@ def fileset():
html += "<h3>Files in the fileset</h3>"
html += "<form>"
for k, v in request.args.items():
- if k != 'widetable':
+ if k != "widetable":
html += f"<input type='hidden' name='{k}' value='{v}'>"
- if widetable == 'partial':
+ if widetable == "partial":
html += "<input class='hidden' name='widetable' value='full' />"
html += "<input type='submit' value='Expand Table' />"
else:
@@ -156,49 +180,66 @@ def fileset():
html += "<input type='submit' value='Hide extra checksums' />"
html += "</form>"
- html += f"""<form method="POST" action="{url_for('delete_files', id=id)}">"""
+ html += (
+ f"""<form method="POST" action="{url_for("delete_files", id=id)}">"""
+ )
# Table
html += "<table>\n"
- sort = request.args.get('sort')
- order = ''
- md5_columns = ['md5-t-5000', 'md5-0', 'md5-5000', 'md5-1M']
- share_columns = ['name', 'size', 'checksum', 'detection', 'detection_type', 'timestamp']
+ sort = request.args.get("sort")
+ order = ""
+ md5_columns = ["md5-t-5000", "md5-0", "md5-5000", "md5-1M"]
+ share_columns = [
+ "name",
+ "size",
+ "checksum",
+ "detection",
+ "detection_type",
+ "timestamp",
+ ]
if sort:
- column = sort.split('-')[0]
+ column = sort.split("-")[0]
valid_columns = share_columns + md5_columns
if column in valid_columns:
order = f"ORDER BY {column}"
- if 'desc' in sort:
+ if "desc" in sort:
order += " DESC"
- columns_to_select = "file.id, name, size, checksum, detection, detection_type, `timestamp`"
+ columns_to_select = (
+ "file.id, name, size, checksum, detection, detection_type, `timestamp`"
+ )
columns_to_select += ", ".join(md5_columns)
- print(f"SELECT file.id, name, size, checksum, detection, detection_type, `timestamp` FROM file WHERE fileset = {id} {order}")
- cursor.execute(f"SELECT file.id, name, size, checksum, detection, detection_type, `timestamp` FROM file WHERE fileset = {id} {order}")
+ print(
+ f"SELECT file.id, name, size, checksum, detection, detection_type, `timestamp` FROM file WHERE fileset = {id} {order}"
+ )
+ cursor.execute(
+ f"SELECT file.id, name, size, checksum, detection, detection_type, `timestamp` FROM file WHERE fileset = {id} {order}"
+ )
result = cursor.fetchall()
all_columns = list(result[0].keys()) if result else []
temp_set = set()
- if widetable == 'full':
- file_ids = [file['id'] for file in result]
- cursor.execute(f"SELECT file, checksum, checksize, checktype FROM filechecksum WHERE file IN ({','.join(map(str, file_ids))})")
+ if widetable == "full":
+ file_ids = [file["id"] for file in result]
+ cursor.execute(
+ f"SELECT file, checksum, checksize, checktype FROM filechecksum WHERE file IN ({','.join(map(str, file_ids))})"
+ )
checksums = cursor.fetchall()
checksum_dict = {}
for checksum in checksums:
- if checksum['checksize'] != 0:
+ if checksum["checksize"] != 0:
key = f"{checksum['checktype']}-{checksum['checksize']}"
- if checksum['file'] not in checksum_dict:
- checksum_dict[checksum['file']] = {}
- checksum_dict[checksum['file']][key] = checksum['checksum']
+ if checksum["file"] not in checksum_dict:
+ checksum_dict[checksum["file"]] = {}
+ checksum_dict[checksum["file"]][key] = checksum["checksum"]
temp_set.add(key)
for index, file in enumerate(result):
- if file['id'] in checksum_dict:
- result[index].update(checksum_dict[file['id']])
+ if file["id"] in checksum_dict:
+ result[index].update(checksum_dict[file["id"]])
all_columns.extend(list(temp_set))
counter = 1
@@ -209,8 +250,10 @@ def fileset():
sortable_columns = share_columns + list(temp_set)
for column in sortable_columns:
- if column not in ['id']:
- vars = "&".join([f"{k}={v}" for k, v in request.args.items() if k != 'sort'])
+ if column not in ["id"]:
+ vars = "&".join(
+ [f"{k}={v}" for k, v in request.args.items() if k != "sort"]
+ )
sort_link = f"{column}"
if sort == column:
sort_link += "-desc"
@@ -223,10 +266,15 @@ def fileset():
html += f"<td>{counter}.</td>\n"
html += f"<td><input type='checkbox' name='file_ids' value='{row['id']}' /></td>\n" # Checkbox for selecting file
for column in all_columns:
- if column != 'id':
- value = row.get(column, '')
- if column == row.get('detection_type') and row.get('detection') == 1:
- html += f"<td style='background-color: yellow;'>{value}</td>\n"
+ if column != "id":
+ value = row.get(column, "")
+ if (
+ column == row.get("detection_type")
+ and row.get("detection") == 1
+ ):
+ html += (
+ f"<td style='background-color: yellow;'>{value}</td>\n"
+ )
else:
html += f"<td>{value}</td>\n"
html += "</tr>\n"
@@ -241,19 +289,23 @@ def fileset():
html += f"<button id='delete-button' type='button' onclick='delete_id({id})'>Mark Fileset for Deletion</button>"
html += f"<button id='match-button' type='button' onclick='match_id({id})'>Match and Merge Fileset</button>"
- if 'delete' in request.form:
- cursor.execute(f"UPDATE fileset SET `delete` = TRUE WHERE id = {request.form['delete']}")
+ if "delete" in request.form:
+ cursor.execute(
+ f"UPDATE fileset SET `delete` = TRUE WHERE id = {request.form['delete']}"
+ )
connection.commit()
html += "<p id='delete-confirm'>Fileset marked for deletion</p>"
- if 'match' in request.form:
- match_and_merge_user_filesets(request.form['match'])
- return redirect(url_for('fileset', id=request.form['match']))
+ if "match" in request.form:
+ match_and_merge_user_filesets(request.form["match"])
+ return redirect(url_for("fileset", id=request.form["match"]))
# Generate the HTML for the fileset history
- cursor.execute(f"SELECT `timestamp`, category, `text`, id FROM log WHERE `text` REGEXP 'Fileset:{id}' ORDER BY `timestamp` DESC, id DESC")
+ cursor.execute(
+ f"SELECT `timestamp`, category, `text`, id FROM log WHERE `text` REGEXP 'Fileset:{id}' ORDER BY `timestamp` DESC, id DESC"
+ )
# cursor.execute(f"SELECT `timestamp`, fileset, oldfileset FROM history WHERE fileset = {id} ORDER BY `timestamp` DESC")
-
+
logs = cursor.fetchall()
html += "<h3>Fileset history</h3>"
@@ -265,26 +317,30 @@ def fileset():
related_filesets = get_all_related_filesets(id, conn)
- cursor.execute(f"SELECT * FROM history WHERE fileset IN ({','.join(map(str, related_filesets))}) OR oldfileset IN ({','.join(map(str, related_filesets))})")
+ cursor.execute(
+ f"SELECT * FROM history WHERE fileset IN ({','.join(map(str, related_filesets))}) OR oldfileset IN ({','.join(map(str, related_filesets))})"
+ )
history = cursor.fetchall()
print(f"History: {history}")
for h in history:
- cursor.execute(f"SELECT `timestamp`, category, `text`, id FROM log WHERE `text` LIKE 'Fileset:{h['oldfileset']}' ORDER BY `timestamp` DESC, id DESC")
+ cursor.execute(
+ f"SELECT `timestamp`, category, `text`, id FROM log WHERE `text` LIKE 'Fileset:{h['oldfileset']}' ORDER BY `timestamp` DESC, id DESC"
+ )
logs = cursor.fetchall()
print(f"Logs: {logs}")
- if h['fileset'] == h['oldfileset']:
+ if h["fileset"] == h["oldfileset"]:
continue
- if h['oldfileset'] == 0:
+ if h["oldfileset"] == 0:
html += "<tr>\n"
html += f"<td>{h['timestamp']}</td>\n"
- html += f"<td>create</td>\n"
+ html += "<td>create</td>\n"
html += f"<td>Created fileset <a href='fileset?id={h['fileset']}'>Fileset {h['fileset']}</a></td>\n"
# html += f"<td><a href='logs?id={h['log']}'>Log {h['log']}</a></td>\n"
- if h['log']:
+ if h["log"]:
cursor.execute(f"SELECT `text` FROM log WHERE id = {h['log']}")
- log_text = cursor.fetchone()['text']
+ log_text = cursor.fetchone()["text"]
log_text = convert_log_text_to_links(log_text)
html += f"<td><a href='logs?id={h['log']}'>Log {h['log']}</a>: {log_text}</td>\n"
else:
@@ -294,12 +350,12 @@ def fileset():
html += "<tr>\n"
html += f"<td>{h['timestamp']}</td>\n"
- html += f"<td>merge</td>\n"
+ html += "<td>merge</td>\n"
html += f"<td><a href='fileset?id={h['oldfileset']}'>Fileset {h['oldfileset']}</a> merged into fileset <a href='fileset?id={h['fileset']}'>Fileset {h['fileset']}</a></td>\n"
# html += f"<td><a href='logs?id={h['log']}'>Log {h['log']}</a></td>\n"
- if h['log']:
+ if h["log"]:
cursor.execute(f"SELECT `text` FROM log WHERE id = {h['log']}")
- log_text = cursor.fetchone()['text']
+ log_text = cursor.fetchone()["text"]
log_text = convert_log_text_to_links(log_text)
html += f"<td><a href='logs?id={h['log']}'>Log {h['log']}</a>: {log_text}</td>\n"
else:
@@ -311,56 +367,65 @@ def fileset():
finally:
connection.close()
- at app.route('/fileset/<int:id>/match', methods=['GET'])
+
+ at app.route("/fileset/<int:id>/match", methods=["GET"])
def match_fileset_route(id):
base_dir = os.path.dirname(os.path.abspath(__file__))
- config_path = os.path.join(base_dir, 'mysql_config.json')
+ config_path = os.path.join(base_dir, "mysql_config.json")
with open(config_path) as f:
mysql_cred = json.load(f)
- connection = pymysql.connect(host=mysql_cred["servername"],
- user=mysql_cred["username"],
- password=mysql_cred["password"],
- db=mysql_cred["dbname"],
- charset='utf8mb4',
- cursorclass=pymysql.cursors.DictCursor)
+ connection = pymysql.connect(
+ host=mysql_cred["servername"],
+ user=mysql_cred["username"],
+ password=mysql_cred["password"],
+ db=mysql_cred["dbname"],
+ charset="utf8mb4",
+ cursorclass=pymysql.cursors.DictCursor,
+ )
try:
with connection.cursor() as cursor:
cursor.execute(f"SELECT * FROM fileset WHERE id = {id}")
fileset = cursor.fetchone()
- fileset['rom'] = []
+ fileset["rom"] = []
if not fileset:
return f"No fileset found with id {id}", 404
- cursor.execute(f"SELECT file.id, name, size, checksum, detection, detection_type FROM file WHERE fileset = {id}")
+ cursor.execute(
+ f"SELECT file.id, name, size, checksum, detection, detection_type FROM file WHERE fileset = {id}"
+ )
result = cursor.fetchall()
file_ids = {}
for file in result:
- file_ids[file['id']] = (file['name'], file['size'])
- cursor.execute(f"SELECT file, checksum, checksize, checktype FROM filechecksum WHERE file IN ({','.join(map(str, file_ids.keys()))})")
-
+ file_ids[file["id"]] = (file["name"], file["size"])
+ cursor.execute(
+ f"SELECT file, checksum, checksize, checktype FROM filechecksum WHERE file IN ({','.join(map(str, file_ids.keys()))})"
+ )
+
files = cursor.fetchall()
- checksum_dict = defaultdict(lambda: {"name": "", "size": 0, "checksums": {}})
+ checksum_dict = defaultdict(
+ lambda: {"name": "", "size": 0, "checksums": {}}
+ )
for i in files:
file_id = i["file"]
file_name, file_size = file_ids[file_id]
checksum_dict[file_name]["name"] = file_name
checksum_dict[file_name]["size"] = file_size
- checksum_key = f"{i['checktype']}-{i['checksize']}" if i['checksize'] != 0 else i['checktype']
+ checksum_key = (
+ f"{i['checktype']}-{i['checksize']}"
+ if i["checksize"] != 0
+ else i["checktype"]
+ )
checksum_dict[file_name]["checksums"][checksum_key] = i["checksum"]
fileset["rom"] = [
- {
- "name": value["name"],
- "size": value["size"],
- **value["checksums"]
- }
+ {"name": value["name"], "size": value["size"], **value["checksums"]}
for value in checksum_dict.values()
]
- matched_map = find_matching_filesets(fileset, connection, fileset['status'])
+ matched_map = find_matching_filesets(fileset, connection, fileset["status"])
html = f"""
<!DOCTYPE html>
@@ -381,8 +446,10 @@ def match_fileset_route(id):
for fileset_id, match_count in matched_map.items():
if fileset_id == id:
continue
- cursor.execute(f"SELECT COUNT(file.id) FROM file WHERE fileset = {fileset_id}")
- count = cursor.fetchone()['COUNT(file.id)']
+ cursor.execute(
+ f"SELECT COUNT(file.id) FROM file WHERE fileset = {fileset_id}"
+ )
+ count = cursor.fetchone()["COUNT(file.id)"]
html += f"""
<tr>
<td>{fileset_id}</td>
@@ -407,14 +474,15 @@ def match_fileset_route(id):
return render_template_string(html)
finally:
connection.close()
-
- at app.route('/fileset/<int:id>/merge', methods=['GET', 'POST'])
+
+
+ at app.route("/fileset/<int:id>/merge", methods=["GET", "POST"])
def merge_fileset(id):
- if request.method == 'POST':
- search_query = request.form['search']
-
+ if request.method == "POST":
+ search_query = request.form["search"]
+
base_dir = os.path.dirname(os.path.abspath(__file__))
- config_path = os.path.join(base_dir, 'mysql_config.json')
+ config_path = os.path.join(base_dir, "mysql_config.json")
with open(config_path) as f:
mysql_cred = json.load(f)
@@ -423,8 +491,8 @@ def merge_fileset(id):
user=mysql_cred["username"],
password=mysql_cred["password"],
db=mysql_cred["dbname"],
- charset='utf8mb4',
- cursorclass=pymysql.cursors.DictCursor
+ charset="utf8mb4",
+ cursorclass=pymysql.cursors.DictCursor,
)
try:
@@ -464,12 +532,12 @@ def merge_fileset(id):
for result in results:
html += f"""
<tr>
- <td>{result['id']}</td>
- <td>{result['game_name']}</td>
- <td>{result['game_platform']}</td>
- <td>{result['game_language']}</td>
- <td>{result['extra']}</td>
- <td><a href="/fileset/{id}/merge/confirm?target_id={result['id']}">Select</a></td>
+ <td>{result["id"]}</td>
+ <td>{result["game_name"]}</td>
+ <td>{result["game_platform"]}</td>
+ <td>{result["game_language"]}</td>
+ <td>{result["extra"]}</td>
+ <td><a href="/fileset/{id}/merge/confirm?target_id={result["id"]}">Select</a></td>
</tr>
"""
html += "</table>\n"
@@ -480,7 +548,7 @@ def merge_fileset(id):
finally:
connection.close()
- return '''
+ return """
<!DOCTYPE html>
<html>
<head>
@@ -494,14 +562,19 @@ def merge_fileset(id):
</form>
</body>
</html>
- '''
-
- at app.route('/fileset/<int:id>/merge/confirm', methods=['GET', 'POST'])
+ """
+
+
+ at app.route("/fileset/<int:id>/merge/confirm", methods=["GET", "POST"])
def confirm_merge(id):
- target_id = request.args.get('target_id', type=int) if request.method == 'GET' else request.form.get('target_id')
+ target_id = (
+ request.args.get("target_id", type=int)
+ if request.method == "GET"
+ else request.form.get("target_id")
+ )
base_dir = os.path.dirname(os.path.abspath(__file__))
- config_path = os.path.join(base_dir, 'mysql_config.json')
+ config_path = os.path.join(base_dir, "mysql_config.json")
with open(config_path) as f:
mysql_cred = json.load(f)
@@ -510,8 +583,8 @@ def confirm_merge(id):
user=mysql_cred["username"],
password=mysql_cred["password"],
db=mysql_cred["dbname"],
- charset='utf8mb4',
- cursorclass=pymysql.cursors.DictCursor
+ charset="utf8mb4",
+ cursorclass=pymysql.cursors.DictCursor,
)
try:
@@ -555,11 +628,15 @@ def confirm_merge(id):
source_highlighted = ""
target_highlighted = ""
for d in diff:
- if d.startswith('-'):
- source_highlighted += f"<span style='color: green;'>{d[2:]}</span>"
- elif d.startswith('+'):
- target_highlighted += f"<span style='color: red;'>{d[2:]}</span>"
- elif d.startswith(' '):
+ if d.startswith("-"):
+ source_highlighted += (
+ f"<span style='color: green;'>{d[2:]}</span>"
+ )
+ elif d.startswith("+"):
+ target_highlighted += (
+ f"<span style='color: red;'>{d[2:]}</span>"
+ )
+ elif d.startswith(" "):
source_highlighted += d[2:]
target_highlighted += d[2:]
return source_highlighted, target_highlighted
@@ -578,11 +655,13 @@ def confirm_merge(id):
for column in source_fileset.keys():
source_value = str(source_fileset[column])
target_value = str(target_fileset[column])
- if column == 'id':
+ if column == "id":
html += f"<tr><td>{column}</td><td><a href='/fileset?id={source_value}'>{source_value}</a></td><td><a href='/fileset?id={target_value}'>{target_value}</a></td></tr>"
continue
if source_value != target_value:
- source_highlighted, target_highlighted = highlight_differences(source_value, target_value)
+ source_highlighted, target_highlighted = highlight_differences(
+ source_value, target_value
+ )
html += f"<tr><td>{column}</td><td>{source_highlighted}</td><td>{target_highlighted}</td></tr>"
else:
html += f"<tr><td>{column}</td><td>{source_value}</td><td>{target_value}</td></tr>"
@@ -600,18 +679,24 @@ def confirm_merge(id):
</body>
</html>
"""
- return render_template_string(html, source_fileset=source_fileset, target_fileset=target_fileset, id=id)
+ return render_template_string(
+ html,
+ source_fileset=source_fileset,
+ target_fileset=target_fileset,
+ id=id,
+ )
finally:
connection.close()
- at app.route('/fileset/<int:id>/merge/execute', methods=['POST'])
+
+ at app.route("/fileset/<int:id>/merge/execute", methods=["POST"])
def execute_merge(id, source=None, target=None):
- source_id = request.form['source_id'] if not source else source
- target_id = request.form['target_id'] if not target else target
+ source_id = request.form["source_id"] if not source else source
+ target_id = request.form["target_id"] if not target else target
base_dir = os.path.dirname(os.path.abspath(__file__))
- config_path = os.path.join(base_dir, 'mysql_config.json')
+ config_path = os.path.join(base_dir, "mysql_config.json")
with open(config_path) as f:
mysql_cred = json.load(f)
@@ -620,8 +705,8 @@ def execute_merge(id, source=None, target=None):
user=mysql_cred["username"],
password=mysql_cred["password"],
db=mysql_cred["dbname"],
- charset='utf8mb4',
- cursorclass=pymysql.cursors.DictCursor
+ charset="utf8mb4",
+ cursorclass=pymysql.cursors.DictCursor,
)
try:
@@ -629,19 +714,18 @@ def execute_merge(id, source=None, target=None):
cursor.execute(f"SELECT * FROM fileset WHERE id = {source_id}")
source_fileset = cursor.fetchone()
cursor.execute(f"SELECT * FROM fileset WHERE id = {target_id}")
- target_fileset = cursor.fetchone()
- if source_fileset['status'] == 'detection':
+ if source_fileset["status"] == "detection":
cursor.execute(f"""
UPDATE fileset SET
- game = '{source_fileset['game']}',
- status = '{source_fileset['status']}',
- `key` = '{source_fileset['key']}',
- megakey = '{source_fileset['megakey']}',
- `timestamp` = '{source_fileset['timestamp']}'
+ game = '{source_fileset["game"]}',
+ status = '{source_fileset["status"]}',
+ `key` = '{source_fileset["key"]}',
+ megakey = '{source_fileset["megakey"]}',
+ `timestamp` = '{source_fileset["timestamp"]}'
WHERE id = {target_id}
""")
-
+
cursor.execute(f"DELETE FROM file WHERE fileset = {target_id}")
cursor.execute(f"SELECT * FROM file WHERE fileset = {source_id}")
@@ -650,63 +734,78 @@ def execute_merge(id, source=None, target=None):
for file in source_files:
cursor.execute(f"""
INSERT INTO file (name, size, checksum, fileset, detection, `timestamp`)
- VALUES ('{escape_string(file['name']).lower()}', '{file['size']}', '{file['checksum']}', {target_id}, {file['detection']}, NOW())
+ VALUES ('{escape_string(file["name"]).lower()}', '{file["size"]}', '{file["checksum"]}', {target_id}, {file["detection"]}, NOW())
""")
cursor.execute("SELECT LAST_INSERT_ID() as file_id")
- new_file_id = cursor.fetchone()['file_id']
-
- cursor.execute(f"SELECT * FROM filechecksum WHERE file = {file['id']}")
+ new_file_id = cursor.fetchone()["file_id"]
+
+ cursor.execute(
+ f"SELECT * FROM filechecksum WHERE file = {file['id']}"
+ )
file_checksums = cursor.fetchall()
for checksum in file_checksums:
cursor.execute(f"""
INSERT INTO filechecksum (file, checksize, checktype, checksum)
- VALUES ({new_file_id}, '{checksum['checksize']}', '{checksum['checktype']}', '{checksum['checksum']}')
+ VALUES ({new_file_id}, '{checksum["checksize"]}', '{checksum["checktype"]}', '{checksum["checksum"]}')
""")
- elif source_fileset['status'] in ['scan', 'dat']:
+ elif source_fileset["status"] in ["scan", "dat"]:
cursor.execute(f"""
UPDATE fileset SET
- status = '{source_fileset['status'] if source_fileset['status'] != 'dat' else "partial"}',
- `key` = '{source_fileset['key']}',
- `timestamp` = '{source_fileset['timestamp']}'
+ status = '{source_fileset["status"] if source_fileset["status"] != "dat" else "partial"}',
+ `key` = '{source_fileset["key"]}',
+ `timestamp` = '{source_fileset["timestamp"]}'
WHERE id = {target_id}
""")
cursor.execute(f"SELECT * FROM file WHERE fileset = {source_id}")
source_files = cursor.fetchall()
-
+
cursor.execute(f"SELECT * FROM file WHERE fileset = {target_id}")
target_files = cursor.fetchall()
target_files_dict = {}
for target_file in target_files:
- cursor.execute(f"SELECT * FROM filechecksum WHERE file = {target_file['id']}")
+ cursor.execute(
+ f"SELECT * FROM filechecksum WHERE file = {target_file['id']}"
+ )
target_checksums = cursor.fetchall()
for checksum in target_checksums:
- target_files_dict[checksum['checksum']] = target_file
-
+ target_files_dict[checksum["checksum"]] = target_file
+
for source_file in source_files:
- cursor.execute(f"SELECT * FROM filechecksum WHERE file = {source_file['id']}")
+ cursor.execute(
+ f"SELECT * FROM filechecksum WHERE file = {source_file['id']}"
+ )
source_checksums = cursor.fetchall()
file_exists = False
for checksum in source_checksums:
- print(checksum['checksum'])
- if checksum['checksum'] in target_files_dict.keys():
- target_file = target_files_dict[checksum['checksum']]
- source_file['detection'] = target_file['detection']
-
- cursor.execute(f"DELETE FROM file WHERE id = {target_file['id']}")
+ print(checksum["checksum"])
+ if checksum["checksum"] in target_files_dict.keys():
+ target_file = target_files_dict[checksum["checksum"]]
+ source_file["detection"] = target_file["detection"]
+
+ cursor.execute(
+ f"DELETE FROM file WHERE id = {target_file['id']}"
+ )
file_exists = True
break
print(file_exists)
cursor.execute(f"""INSERT INTO file (name, size, checksum, fileset, detection, `timestamp`) VALUES (
- '{source_file['name']}', '{source_file['size']}', '{source_file['checksum']}', {target_id}, {source_file['detection']}, NOW())""")
+ '{source_file["name"]}', '{source_file["size"]}', '{source_file["checksum"]}', {target_id}, {source_file["detection"]}, NOW())""")
new_file_id = cursor.lastrowid
for checksum in source_checksums:
# TODO: Handle the string
- cursor.execute("INSERT INTO filechecksum (file, checksize, checktype, checksum) VALUES (%s, %s, %s, %s)",
- (new_file_id, checksum['checksize'], f"{checksum['checktype']}-{checksum['checksize']}", checksum['checksum']))
+ cursor.execute(
+ "INSERT INTO filechecksum (file, checksize, checktype, checksum) VALUES (%s, %s, %s, %s)",
+ (
+ new_file_id,
+ checksum["checksize"],
+ f"{checksum['checktype']}-{checksum['checksize']}",
+ checksum["checksum"],
+ ),
+ )
cursor.execute(f"""
INSERT INTO history (`timestamp`, fileset, oldfileset)
@@ -715,12 +814,13 @@ def execute_merge(id, source=None, target=None):
connection.commit()
- return redirect(url_for('fileset', id=target_id))
+ return redirect(url_for("fileset", id=target_id))
finally:
connection.close()
-
- at app.route('/fileset/<int:id>/mark_full', methods=['POST'])
+
+
+ at app.route("/fileset/<int:id>/mark_full", methods=["POST"])
def mark_as_full(id):
try:
conn = db_connect()
@@ -731,13 +831,14 @@ def mark_as_full(id):
conn.commit()
except Exception as e:
print(f"Error updating fileset status: {e}")
- return jsonify({'error': 'Failed to mark fileset as full'}), 500
+ return jsonify({"error": "Failed to mark fileset as full"}), 500
finally:
conn.close()
- return redirect(f'/fileset?id={id}')
+ return redirect(f"/fileset?id={id}")
+
- at app.route('/validate', methods=['POST'])
+ at app.route("/validate", methods=["POST"])
def validate():
error_codes = {
"unknown": -1,
@@ -749,28 +850,25 @@ def validate():
json_object = request.get_json()
ip = request.remote_addr
- ip = '.'.join(ip.split('.')[:3]) + '.X'
+ ip = ".".join(ip.split(".")[:3]) + ".X"
- game_metadata = {k: v for k, v in json_object.items() if k != 'files'}
+ game_metadata = {k: v for k, v in json_object.items() if k != "files"}
- json_response = {
- 'error': error_codes['success'],
- 'files': []
- }
+ json_response = {"error": error_codes["success"], "files": []}
if not game_metadata:
- if not json_object.get('files'):
- json_response['error'] = error_codes['empty']
- del json_response['files']
- json_response['status'] = 'empty_fileset'
+ if not json_object.get("files"):
+ json_response["error"] = error_codes["empty"]
+ del json_response["files"]
+ json_response["status"] = "empty_fileset"
return jsonify(json_response)
- json_response['error'] = error_codes['no_metadata']
- del json_response['files']
- json_response['status'] = 'no_metadata'
-
+ json_response["error"] = error_codes["no_metadata"]
+ del json_response["files"]
+ json_response["status"] = "no_metadata"
+
fileset_id = user_insert_fileset(json_object, ip, conn)
- json_response['fileset'] = fileset_id
+ json_response["fileset"] = fileset_id
print(f"Response: {json_response}")
return jsonify(json_response)
@@ -778,71 +876,83 @@ def validate():
missing_map = {}
extra_map = {}
- file_object = json_object['files']
+ file_object = json_object["files"]
if not file_object:
- json_response['error'] = error_codes['empty']
- json_response['status'] = 'empty_fileset'
+ json_response["error"] = error_codes["empty"]
+ json_response["status"] = "empty_fileset"
return jsonify(json_response)
try:
- matched_map, missing_map, extra_map = user_integrity_check(json_object, ip, game_metadata)
+ matched_map, missing_map, extra_map = user_integrity_check(
+ json_object, ip, game_metadata
+ )
except Exception as e:
- json_response['error'] = -1
- json_response['status'] = 'processing_error'
- json_response['fileset'] = 'unknown_fileset'
- json_response['message'] = str(e)
+ json_response["error"] = -1
+ json_response["status"] = "processing_error"
+ json_response["fileset"] = "unknown_fileset"
+ json_response["message"] = str(e)
print(f"Response: {json_response}")
return jsonify(json_response)
print(f"Matched: {matched_map}")
print(len(matched_map))
- if (len(matched_map) == 0):
- json_response['error'] = error_codes['unknown']
- json_response['status'] = 'unknown_fileset'
- json_response['fileset'] = 'unknown_fileset'
+ if len(matched_map) == 0:
+ json_response["error"] = error_codes["unknown"]
+ json_response["status"] = "unknown_fileset"
+ json_response["fileset"] = "unknown_fileset"
return jsonify(json_response)
- matched_map = list(sorted(matched_map.items(), key=lambda x: len(x[1]), reverse=True))[0]
+ matched_map = list(
+ sorted(matched_map.items(), key=lambda x: len(x[1]), reverse=True)
+ )[0]
matched_id = matched_map[0]
# find the same id in the missing_map and extra_map
for fileset_id, count in missing_map.items():
if fileset_id == matched_id:
missing_map = (fileset_id, count)
break
-
+
for fileset_id, count in extra_map.items():
if fileset_id == matched_id:
extra_map = (fileset_id, count)
break
-
+
for file in matched_map[1]:
for key, value in file.items():
if key == "name":
- json_response['files'].append({'status': 'ok', 'fileset_id':matched_id, 'name': value})
+ json_response["files"].append(
+ {"status": "ok", "fileset_id": matched_id, "name": value}
+ )
break
for file in missing_map[1]:
for key, value in file.items():
if key == "name":
- json_response['files'].append({'status': 'missing', 'fileset_id':matched_id, 'name': value})
+ json_response["files"].append(
+ {"status": "missing", "fileset_id": matched_id, "name": value}
+ )
break
for file in extra_map[1]:
for key, value in file.items():
if key == "name":
- json_response['files'].append({'status': 'unknown_file', 'fileset_id':matched_id, 'name': value})
+ json_response["files"].append(
+ {"status": "unknown_file", "fileset_id": matched_id, "name": value}
+ )
break
print(f"Response: {json_response}")
return jsonify(json_response)
-
-
- at app.route('/user_games_list')
+
+
+ at app.route("/user_games_list")
def user_games_list():
- url = f"fileset_search?extra=&platform=&language=&megakey=&status=user"
+ url = "fileset_search?extra=&platform=&language=&megakey=&status=user"
return redirect(url)
- at app.route('/ready_for_review')
+
+ at app.route("/ready_for_review")
def ready_for_review():
- url = f"fileset_search?extra=&platform=&language=&megakey=&status=ReadyForReview"
+ url = "fileset_search?extra=&platform=&language=&megakey=&status=ReadyForReview"
return redirect(url)
- at app.route('/games_list')
+
+ at app.route("/games_list")
def games_list():
filename = "games_list"
records_table = "game"
@@ -861,30 +971,36 @@ def games_list():
"platform": "game",
"language": "game",
"name": "game",
- 'status': 'fileset'
+ "status": "fileset",
}
mapping = {
- 'engine.id': 'game.engine',
- 'game.id': 'fileset.game',
+ "engine.id": "game.engine",
+ "game.id": "fileset.game",
}
- return render_template_string(create_page(filename, 25, records_table, select_query, order, filters, mapping))
+ return render_template_string(
+ create_page(filename, 25, records_table, select_query, order, filters, mapping)
+ )
- at app.route('/logs')
+
+ at app.route("/logs")
def logs():
filename = "logs"
records_table = "log"
select_query = "SELECT id, `timestamp`, category, user, `text` FROM log"
order = "ORDER BY `timestamp` DESC, id DESC"
filters = {
- 'id': 'log',
- 'timestamp': 'log',
- 'category': 'log',
- 'user': 'log',
- 'text': 'log'
+ "id": "log",
+ "timestamp": "log",
+ "category": "log",
+ "user": "log",
+ "text": "log",
}
- return render_template_string(create_page(filename, 25, records_table, select_query, order, filters))
+ return render_template_string(
+ create_page(filename, 25, records_table, select_query, order, filters)
+ )
+
- at app.route('/fileset_search')
+ at app.route("/fileset_search")
def fileset_search():
filename = "fileset_search"
records_table = "fileset"
@@ -902,16 +1018,19 @@ def fileset_search():
"platform": "game",
"language": "game",
"megakey": "fileset",
- "status": "fileset"
+ "status": "fileset",
}
mapping = {
- 'game.id': 'fileset.game',
+ "game.id": "fileset.game",
}
- return render_template_string(create_page(filename, 25, records_table, select_query, order, filters, mapping))
+ return render_template_string(
+ create_page(filename, 25, records_table, select_query, order, filters, mapping)
+ )
+
- at app.route('/delete_files/<int:id>', methods=['POST'])
+ at app.route("/delete_files/<int:id>", methods=["POST"])
def delete_files(id):
- file_ids = request.form.getlist('file_ids')
+ file_ids = request.form.getlist("file_ids")
if file_ids:
# Convert the list to comma-separated string for SQL
ids_to_delete = ",".join(file_ids)
@@ -923,8 +1042,9 @@ def delete_files(id):
# Commit the deletions
connection.commit()
- return redirect(url_for('fileset', id=id))
+ return redirect(url_for("fileset", id=id))
+
-if __name__ == '__main__':
+if __name__ == "__main__":
app.secret_key = secret_key
- app.run(debug=True, host='0.0.0.0')
+ app.run(debug=True, host="0.0.0.0")
diff --git a/megadata.py b/megadata.py
index d489391..0b4b3af 100644
--- a/megadata.py
+++ b/megadata.py
@@ -1,6 +1,5 @@
import os
-import time
-import compute_hash
+
class Megadata:
def __init__(self, file_path):
@@ -14,11 +13,13 @@ class Megadata:
pass
def __eq__(self, other):
- return (self.hash == other.hash and
- self.size == other.size and
- self.creation_time == other.creation_time and
- self.modification_time == other.modification_time)
-
+ return (
+ self.hash == other.hash
+ and self.size == other.size
+ and self.creation_time == other.creation_time
+ and self.modification_time == other.modification_time
+ )
+
def record_megadata(directory):
file_megadata = {}
@@ -28,6 +29,7 @@ def record_megadata(directory):
file_megadata[file_path] = Megadata(file_path)
return file_megadata
+
def check_for_updates(old_megadata, current_directory):
current_megadata = record_megadata(current_directory)
updates = []
@@ -35,4 +37,4 @@ def check_for_updates(old_megadata, current_directory):
for current_path, current_data in current_megadata.items():
if old_data == current_data and old_path != current_path:
updates.append((old_path, current_path))
- return updates
\ No newline at end of file
+ return updates
diff --git a/pagination.py b/pagination.py
index 57caead..f9f8ecd 100644
--- a/pagination.py
+++ b/pagination.py
@@ -1,4 +1,4 @@
-from flask import Flask, request, render_template_string
+from flask import Flask, request
import pymysql
import json
import re
@@ -6,52 +6,68 @@ import os
app = Flask(__name__)
-stylesheet = 'style.css'
-jquery_file = 'https://code.jquery.com/jquery-3.7.0.min.js'
-js_file = 'js_functions.js'
+stylesheet = "style.css"
+jquery_file = "https://code.jquery.com/jquery-3.7.0.min.js"
+js_file = "js_functions.js"
+
def get_join_columns(table1, table2, mapping):
for primary, foreign in mapping.items():
- primary = primary.split('.')
- foreign = foreign.split('.')
- if (primary[0] == table1 and foreign[0] == table2) or (primary[0] == table2 and foreign[0] == table1):
+ primary = primary.split(".")
+ foreign = foreign.split(".")
+ if (primary[0] == table1 and foreign[0] == table2) or (
+ primary[0] == table2 and foreign[0] == table1
+ ):
return f"{primary[0]}.{primary[1]} = {foreign[0]}.{foreign[1]}"
return "No primary-foreign key mapping provided. Filter is invalid"
-def create_page(filename, results_per_page, records_table, select_query, order, filters={}, mapping={}):
+
+def create_page(
+ filename,
+ results_per_page,
+ records_table,
+ select_query,
+ order,
+ filters={},
+ mapping={},
+):
base_dir = os.path.dirname(os.path.abspath(__file__))
- config_path = os.path.join(base_dir, 'mysql_config.json')
+ config_path = os.path.join(base_dir, "mysql_config.json")
with open(config_path) as f:
mysql_cred = json.load(f)
-
+
conn = pymysql.connect(
host=mysql_cred["servername"],
user=mysql_cred["username"],
password=mysql_cred["password"],
db=mysql_cred["dbname"],
- charset='utf8mb4',
- cursorclass=pymysql.cursors.DictCursor
+ charset="utf8mb4",
+ cursorclass=pymysql.cursors.DictCursor,
)
with conn.cursor() as cursor:
# Handle sorting
- sort = request.args.get('sort')
+ sort = request.args.get("sort")
if sort:
- column = sort.split('-')
+ column = sort.split("-")
order = f"ORDER BY {column[0]}"
- if 'desc' in sort:
+ if "desc" in sort:
order += " DESC"
-
- if set(request.args.keys()).difference({'page', 'sort'}):
+
+ if set(request.args.keys()).difference({"page", "sort"}):
condition = "WHERE "
tables = []
for key, value in request.args.items():
- if key in ['page', 'sort'] or value == '':
+ if key in ["page", "sort"] or value == "":
continue
tables.append(filters[key])
- if value == '':
- value = '.*'
- condition += f" AND {filters[key]}.{key} REGEXP '{value}'" if condition != "WHERE " else f"{filters[key]}.{key} REGEXP '{value}'"
+ if value == "":
+ value = ".*"
+ condition += (
+ f" AND {filters[key]}.{key} REGEXP '{value}'"
+ if condition != "WHERE "
+ else f"{filters[key]}.{key} REGEXP '{value}'"
+ )
if condition == "WHERE ":
condition = ""
@@ -63,37 +79,43 @@ def create_page(filename, results_per_page, records_table, select_query, order,
if table == records_table:
continue
from_query += f" JOIN {table} ON {get_join_columns(records_table, table, mapping)}"
- cursor.execute(f"SELECT COUNT({records_table}.id) AS count FROM {from_query} {condition}")
- num_of_results = cursor.fetchone()['count']
-
+ cursor.execute(
+ f"SELECT COUNT({records_table}.id) AS count FROM {from_query} {condition}"
+ )
+ num_of_results = cursor.fetchone()["count"]
+
elif "JOIN" in records_table:
first_table = records_table.split(" ")[0]
cursor.execute(f"SELECT COUNT({first_table}.id) FROM {records_table}")
- num_of_results = cursor.fetchone()[f'COUNT({first_table}.id)']
+ num_of_results = cursor.fetchone()[f"COUNT({first_table}.id)"]
else:
cursor.execute(f"SELECT COUNT(id) FROM {records_table}")
- num_of_results = cursor.fetchone()['COUNT(id)']
+ num_of_results = cursor.fetchone()["COUNT(id)"]
num_of_pages = (num_of_results + results_per_page - 1) // results_per_page
print(f"Num of results: {num_of_results}, Num of pages: {num_of_pages}")
if num_of_results == 0:
return "No results for given filters"
- page = int(request.args.get('page', 1))
+ page = int(request.args.get("page", 1))
page = max(1, min(page, num_of_pages))
offset = (page - 1) * results_per_page
# Fetch results
- if set(request.args.keys()).difference({'page'}):
+ if set(request.args.keys()).difference({"page"}):
condition = "WHERE "
for key, value in request.args.items():
if key not in filters:
continue
value = pymysql.converters.escape_string(value)
- if value == '':
- value = '.*'
- condition += f" AND {filters[key]}.{key} REGEXP '{value}'" if condition != "WHERE " else f"{filters[key]}.{key} REGEXP '{value}'"
+ if value == "":
+ value = ".*"
+ condition += (
+ f" AND {filters[key]}.{key} REGEXP '{value}'"
+ if condition != "WHERE "
+ else f"{filters[key]}.{key} REGEXP '{value}'"
+ )
if condition == "WHERE ":
condition = ""
@@ -105,11 +127,11 @@ def create_page(filename, results_per_page, records_table, select_query, order,
results = cursor.fetchall()
# Generate HTML
- html = f"""
+ html = """
<!DOCTYPE html>
<html>
<head>
- <link rel="stylesheet" type="text/css" href="{{{{ url_for('static', filename='style.css') }}}}">
+ <link rel="stylesheet" type="text/css" href="{{ url_for('static', filename='style.css') }}">
</head>
<body>
<form id='filters-form' method='GET' onsubmit='remove_empty_inputs()'>
@@ -130,10 +152,12 @@ def create_page(filename, results_per_page, records_table, select_query, order,
html += "<th></th>"
for key in results[0].keys():
- if key == 'fileset':
+ if key == "fileset":
continue
- vars = "&".join([f"{k}={v}" for k, v in request.args.items() if k != 'sort'])
- sort = request.args.get('sort', '')
+ vars = "&".join(
+ [f"{k}={v}" for k, v in request.args.items() if k != "sort"]
+ )
+ sort = request.args.get("sort", "")
if sort == key:
vars += f"&sort={key}-desc"
else:
@@ -154,7 +178,7 @@ def create_page(filename, results_per_page, records_table, select_query, order,
filter_value = request.args.get(key, "")
if records_table != "log":
- fileset_id = row['fileset']
+ fileset_id = row["fileset"]
html += f"<tr class='games_list' onclick='hyperlink(\"fileset?id={fileset_id}\")'>\n"
html += f"<td><a href='fileset?id={fileset_id}'>{counter}.</a></td>\n"
else:
@@ -162,7 +186,7 @@ def create_page(filename, results_per_page, records_table, select_query, order,
html += f"<td>{counter}.</td>\n"
for key, value in row.items():
- if key == 'fileset':
+ if key == "fileset":
continue
# Add links to fileset in logs table
@@ -171,7 +195,10 @@ def create_page(filename, results_per_page, records_table, select_query, order,
if matches:
fileset_id = matches.group(1)
fileset_text = matches.group(0)
- value = value.replace(fileset_text, f"<a href='fileset?id={fileset_id}'>{fileset_text}</a>")
+ value = value.replace(
+ fileset_text,
+ f"<a href='fileset?id={fileset_id}'>{fileset_text}</a>",
+ )
html += f"<td>{value}</td>\n"
html += "</tr>\n"
@@ -181,29 +208,31 @@ def create_page(filename, results_per_page, records_table, select_query, order,
html += "</table></form>"
# Pagination
- vars = "&".join([f"{k}={v}" for k, v in request.args.items() if k != 'page'])
+ vars = "&".join([f"{k}={v}" for k, v in request.args.items() if k != "page"])
if num_of_pages > 1:
html += "<form method='GET'>"
for key, value in request.args.items():
- if key != 'page':
+ if key != "page":
html += f"<input type='hidden' name='{key}' value='{value}'>"
html += "<div class='pagination'>"
if page > 1:
html += f"<a href='{filename}?{vars}'>â®â®</a>"
- html += f"<a href='{filename}?page={page-1}&{vars}'>â®</a>"
+ html += f"<a href='{filename}?page={page - 1}&{vars}'>â®</a>"
if page - 2 > 1:
html += "<div class='more'>...</div>"
for i in range(page - 2, page + 3):
if 1 <= i <= num_of_pages:
if i == page:
- html += f"<a class='active' href='{filename}?page={i}&{vars}'>{i}</a>"
+ html += (
+ f"<a class='active' href='{filename}?page={i}&{vars}'>{i}</a>"
+ )
else:
html += f"<a href='{filename}?page={i}&{vars}'>{i}</a>"
if page + 2 < num_of_pages:
html += "<div class='more'>...</div>"
if page < num_of_pages:
- html += f"<a href='{filename}?page={page+1}&{vars}'>â¯</a>"
+ html += f"<a href='{filename}?page={page + 1}&{vars}'>â¯</a>"
html += f"<a href='{filename}?page={num_of_pages}&{vars}'>â¯â¯</a>"
html += "<input type='text' name='page' placeholder='Page No'>"
html += "<input type='submit' value='Submit'>"
diff --git a/schema.py b/schema.py
index 4a50e86..8244743 100644
--- a/schema.py
+++ b/schema.py
@@ -7,7 +7,7 @@ import os
# Load MySQL credentials
base_dir = os.path.dirname(os.path.abspath(__file__))
-config_path = os.path.join(base_dir, 'mysql_config.json')
+config_path = os.path.join(base_dir, "mysql_config.json")
with open(config_path) as f:
mysql_cred = json.load(f)
@@ -21,9 +21,9 @@ conn = pymysql.connect(
host=servername,
user=username,
password=password,
- charset='utf8mb4',
+ charset="utf8mb4",
cursorclass=pymysql.cursors.DictCursor,
- autocommit=False
+ autocommit=False,
)
# Check connection
@@ -131,7 +131,7 @@ tables = {
`transaction` INT NOT NULL,
fileset INT NOT NULL
)
- """
+ """,
}
for table, definition in tables.items():
@@ -148,36 +148,40 @@ indices = {
"engineid": "CREATE INDEX engineid ON engine (engineid)",
"key": "CREATE INDEX fileset_key ON fileset (`key`)",
"status": "CREATE INDEX status ON fileset (status)",
- "fileset": "CREATE INDEX fileset ON history (fileset)"
+ "fileset": "CREATE INDEX fileset ON history (fileset)",
}
try:
cursor.execute("ALTER TABLE file ADD COLUMN detection_type VARCHAR(20);")
-except:
+except Exception:
# if aleady exists, change the length of the column
cursor.execute("ALTER TABLE file MODIFY COLUMN detection_type VARCHAR(20);")
try:
cursor.execute("ALTER TABLE file ADD COLUMN `timestamp` TIMESTAMP NOT NULL;")
-except:
+except Exception:
# if aleady exists, change the length of the column
cursor.execute("ALTER TABLE file MODIFY COLUMN `timestamp` TIMESTAMP NOT NULL;")
try:
cursor.execute("ALTER TABLE fileset ADD COLUMN `user_count` INT;")
-except:
+except Exception:
# if aleady exists, change the length of the column
cursor.execute("ALTER TABLE fileset MODIFY COLUMN `user_count` INT;")
-
+
try:
cursor.execute("ALTER TABLE file ADD COLUMN punycode_name VARCHAR(200);")
-except:
+except Exception:
cursor.execute("ALTER TABLE file MODIFY COLUMN punycode_name VARCHAR(200);")
-
+
try:
- cursor.execute("ALTER TABLE file ADD COLUMN encoding_type VARCHAR(20) DEFAULT 'UTF-8';")
-except:
- cursor.execute("ALTER TABLE file MODIFY COLUMN encoding_type VARCHAR(20) DEFAULT 'UTF-8';")
+ cursor.execute(
+ "ALTER TABLE file ADD COLUMN encoding_type VARCHAR(20) DEFAULT 'UTF-8';"
+ )
+except Exception:
+ cursor.execute(
+ "ALTER TABLE file MODIFY COLUMN encoding_type VARCHAR(20) DEFAULT 'UTF-8';"
+ )
for index, definition in indices.items():
try:
@@ -186,46 +190,90 @@ for index, definition in indices.items():
except pymysql.Error as err:
print(f"Error creating index for '{index}': {err}")
+
# Insert random data into tables
def random_string(length=10):
- return ''.join(random.choices(string.ascii_letters + string.digits, k=length))
+ return "".join(random.choices(string.ascii_letters + string.digits, k=length))
+
def insert_random_data():
for _ in range(1000):
# Insert data into engine
- cursor.execute("INSERT INTO engine (name, engineid) VALUES (%s, %s)", (random_string(), random_string()))
-
+ cursor.execute(
+ "INSERT INTO engine (name, engineid) VALUES (%s, %s)",
+ (random_string(), random_string()),
+ )
+
# Insert data into game
- cursor.execute("INSERT INTO game (name, engine, gameid, extra, platform, language) VALUES (%s, %s, %s, %s, %s, %s)",
- (random_string(), 1, random_string(), random_string(), random_string(), random_string()))
-
+ cursor.execute(
+ "INSERT INTO game (name, engine, gameid, extra, platform, language) VALUES (%s, %s, %s, %s, %s, %s)",
+ (
+ random_string(),
+ 1,
+ random_string(),
+ random_string(),
+ random_string(),
+ random_string(),
+ ),
+ )
+
# Insert data into fileset
- cursor.execute("INSERT INTO fileset (game, status, src, `key`, `megakey`, `timestamp`, detection_size) VALUES (%s, %s, %s, %s, %s, %s, %s)",
- (1, 'user', random_string(), random_string(), random_string(), datetime.now(), random.randint(1, 100)))
-
+ cursor.execute(
+ "INSERT INTO fileset (game, status, src, `key`, `megakey`, `timestamp`, detection_size) VALUES (%s, %s, %s, %s, %s, %s, %s)",
+ (
+ 1,
+ "user",
+ random_string(),
+ random_string(),
+ random_string(),
+ datetime.now(),
+ random.randint(1, 100),
+ ),
+ )
+
# Insert data into file
- cursor.execute("INSERT INTO file (name, size, checksum, fileset, detection) VALUES (%s, %s, %s, %s, %s)",
- (random_string(), random.randint(1000, 10000), random_string(), 1, True))
-
+ cursor.execute(
+ "INSERT INTO file (name, size, checksum, fileset, detection) VALUES (%s, %s, %s, %s, %s)",
+ (random_string(), random.randint(1000, 10000), random_string(), 1, True),
+ )
+
# Insert data into filechecksum
- cursor.execute("INSERT INTO filechecksum (file, checksize, checktype, checksum) VALUES (%s, %s, %s, %s)",
- (1, random_string(), random_string(), random_string()))
-
+ cursor.execute(
+ "INSERT INTO filechecksum (file, checksize, checktype, checksum) VALUES (%s, %s, %s, %s)",
+ (1, random_string(), random_string(), random_string()),
+ )
+
# Insert data into queue
- cursor.execute("INSERT INTO queue (time, notes, fileset, userid, commit) VALUES (%s, %s, %s, %s, %s)",
- (datetime.now(), random_string(), 1, random.randint(1, 100), random_string()))
-
+ cursor.execute(
+ "INSERT INTO queue (time, notes, fileset, userid, commit) VALUES (%s, %s, %s, %s, %s)",
+ (
+ datetime.now(),
+ random_string(),
+ 1,
+ random.randint(1, 100),
+ random_string(),
+ ),
+ )
+
# Insert data into log
- cursor.execute("INSERT INTO log (`timestamp`, category, user, `text`) VALUES (%s, %s, %s, %s)",
- (datetime.now(), random_string(), random_string(), random_string()))
-
+ cursor.execute(
+ "INSERT INTO log (`timestamp`, category, user, `text`) VALUES (%s, %s, %s, %s)",
+ (datetime.now(), random_string(), random_string(), random_string()),
+ )
+
# Insert data into history
- cursor.execute("INSERT INTO history (`timestamp`, fileset, oldfileset, log) VALUES (%s, %s, %s, %s)",
- (datetime.now(), 1, 2, 1))
-
+ cursor.execute(
+ "INSERT INTO history (`timestamp`, fileset, oldfileset, log) VALUES (%s, %s, %s, %s)",
+ (datetime.now(), 1, 2, 1),
+ )
+
# Insert data into transactions
- cursor.execute("INSERT INTO transactions (`transaction`, fileset) VALUES (%s, %s)",
- (random.randint(1, 100), 1))
+ cursor.execute(
+ "INSERT INTO transactions (`transaction`, fileset) VALUES (%s, %s)",
+ (random.randint(1, 100), 1),
+ )
+
+
# for testing locally
# insert_random_data()
diff --git a/user_fileset_functions.py b/user_fileset_functions.py
index 80e0c1a..91ad3d9 100644
--- a/user_fileset_functions.py
+++ b/user_fileset_functions.py
@@ -1,31 +1,43 @@
import hashlib
import time
-from db_functions import db_connect, insert_fileset, insert_file, insert_filechecksum, find_matching_game, merge_filesets, create_log, calc_megakey
+from db_functions import (
+ db_connect,
+ insert_fileset,
+ insert_file,
+ insert_filechecksum,
+ find_matching_game,
+ merge_filesets,
+ create_log,
+ calc_megakey,
+)
import getpass
import pymysql
+
def user_calc_key(user_fileset):
key_string = ""
for file in user_fileset:
for key, value in file.items():
- if key != 'checksums':
- key_string += ':' + str(value)
+ if key != "checksums":
+ key_string += ":" + str(value)
continue
for checksum_pair in value:
- key_string += ':' + checksum_pair['checksum']
- key_string = key_string.strip(':')
+ key_string += ":" + checksum_pair["checksum"]
+ key_string = key_string.strip(":")
return hashlib.md5(key_string.encode()).hexdigest()
+
def file_json_to_array(file_json_object):
res = {}
for key, value in file_json_object.items():
- if key != 'checksums':
+ if key != "checksums":
res[key] = value
continue
for checksum_pair in value:
- res[checksum_pair['type']] = checksum_pair['checksum']
+ res[checksum_pair["type"]] = checksum_pair["checksum"]
return res
+
def user_insert_queue(user_fileset, conn):
query = f"INSERT INTO queue (time, notes, fileset, ticketid, userid, commit) VALUES ({int(time.time())}, NULL, @fileset_last, NULL, NULL, NULL)"
@@ -33,28 +45,32 @@ def user_insert_queue(user_fileset, conn):
cursor.execute(query)
conn.commit()
+
def user_insert_fileset(user_fileset, ip, conn):
- src = 'user'
+ src = "user"
detection = False
- key = ''
+ key = ""
megakey = calc_megakey(user_fileset)
with conn.cursor() as cursor:
cursor.execute("SELECT MAX(`transaction`) FROM transactions")
- transaction_id = cursor.fetchone()['MAX(`transaction`)'] + 1
+ transaction_id = cursor.fetchone()["MAX(`transaction`)"] + 1
log_text = "from user submitted files"
cursor.execute("SET @fileset_time_last = %s", (int(time.time())))
- if insert_fileset(src, detection, key, megakey, transaction_id, log_text, conn, ip):
- for file in user_fileset['files']:
+ if insert_fileset(
+ src, detection, key, megakey, transaction_id, log_text, conn, ip
+ ):
+ for file in user_fileset["files"]:
file = file_json_to_array(file)
insert_file(file, detection, src, conn)
for key, value in file.items():
if key not in ["name", "size"]:
insert_filechecksum(file, key, conn)
cursor.execute("SELECT @fileset_last")
- fileset_id = cursor.fetchone()['@fileset_last']
+ fileset_id = cursor.fetchone()["@fileset_last"]
conn.commit()
return fileset_id
+
def match_and_merge_user_filesets(id):
conn = db_connect()
@@ -62,7 +78,9 @@ def match_and_merge_user_filesets(id):
unmatched_filesets = []
with conn.cursor() as cursor:
- cursor.execute(f"SELECT fileset.id, filechecksum.checksum, src, status FROM fileset JOIN file ON file.fileset = fileset.id JOIN filechecksum ON file.id = filechecksum.file WHERE status = 'user' AND fileset.id = {id}")
+ cursor.execute(
+ f"SELECT fileset.id, filechecksum.checksum, src, status FROM fileset JOIN file ON file.fileset = fileset.id JOIN filechecksum ON file.id = filechecksum.file WHERE status = 'user' AND fileset.id = {id}"
+ )
unmatched_files = cursor.fetchall()
# Splitting them into different filesets
@@ -78,15 +96,15 @@ def match_and_merge_user_filesets(id):
for fileset in unmatched_filesets:
matching_games = find_matching_game(fileset)
- if len(matching_games) != 1: # If there is no match/non-unique match
+ if len(matching_games) != 1: # If there is no match/non-unique match
continue
matched_game = matching_games[0]
- status = 'full'
+ status = "full"
# Convert NULL values to string with value NULL for printing
- matched_game = {k: 'NULL' if v is None else v for k, v in matched_game.items()}
+ matched_game = {k: "NULL" if v is None else v for k, v in matched_game.items()}
category_text = f"Matched from {fileset[0][2]}"
log_text = f"Matched game {matched_game['engineid']}:\n{matched_game['gameid']}-{matched_game['platform']}-{matched_game['language']}\nvariant {matched_game['key']}. State {status}. Fileset:{fileset[0][0]}."
@@ -97,37 +115,53 @@ def match_and_merge_user_filesets(id):
history_last = merge_filesets(matched_game["fileset"], fileset[0][0])
if cursor.execute(query):
- user = f'cli:{getpass.getuser()}'
+ user = f"cli:{getpass.getuser()}"
# Merge log
- create_log("Fileset merge", user, pymysql.escape_string(conn, f"Merged Fileset:{matched_game['fileset']} and Fileset:{fileset[0][0]}"))
+ create_log(
+ "Fileset merge",
+ user,
+ pymysql.escape_string(
+ conn,
+ f"Merged Fileset:{matched_game['fileset']} and Fileset:{fileset[0][0]}",
+ ),
+ )
# Matching log
- log_last = create_log(pymysql.escape_string(conn, category_text), user, pymysql.escape_string(conn, log_text))
+ log_last = create_log(
+ pymysql.escape_string(conn, category_text),
+ user,
+ pymysql.escape_string(conn, log_text),
+ )
# Add log id to the history table
- cursor.execute(f"UPDATE history SET log = {log_last} WHERE id = {history_last}")
+ cursor.execute(
+ f"UPDATE history SET log = {log_last} WHERE id = {history_last}"
+ )
if not conn.commit():
print("Updating matched games failed")
with conn.cursor() as cursor:
- cursor.execute("""
+ cursor.execute(
+ """
SELECT fileset.id, filechecksum.checksum, src, status
FROM fileset
JOIN file ON file.fileset = fileset.id
JOIN filechecksum ON file.id = filechecksum.file
WHERE status = 'user' AND fileset.id = %s
- """, (id,))
+ """,
+ (id,),
+ )
unmatched_files = cursor.fetchall()
unmatched_filesets = []
cur_fileset = None
temp = []
for file in unmatched_files:
- if cur_fileset is None or cur_fileset != file['id']:
+ if cur_fileset is None or cur_fileset != file["id"]:
if temp:
unmatched_filesets.append(temp)
- cur_fileset = file['id']
+ cur_fileset = file["id"]
temp = []
temp.append(file)
if temp:
@@ -138,8 +172,10 @@ def match_and_merge_user_filesets(id):
if len(matching_games) != 1:
continue
matched_game = matching_games[0]
- status = 'full'
- matched_game = {k: ("NULL" if v is None else v) for k, v in matched_game.items()}
+ status = "full"
+ matched_game = {
+ k: ("NULL" if v is None else v) for k, v in matched_game.items()
+ }
category_text = f"Matched from {fileset[0]['src']}"
log_text = f"Matched game {matched_game['engineid']}: {matched_game['gameid']}-{matched_game['platform']}-{matched_game['language']} variant {matched_game['key']}. State {status}. Fileset:{fileset[0]['id']}."
query = """
@@ -147,11 +183,20 @@ def match_and_merge_user_filesets(id):
SET game = %s, status = %s, `key` = %s
WHERE id = %s
"""
- history_last = merge_filesets(matched_game["fileset"], fileset[0]['id'])
+ history_last = merge_filesets(matched_game["fileset"], fileset[0]["id"])
with conn.cursor() as cursor:
- cursor.execute(query, (matched_game["id"], status, matched_game["key"], fileset[0]['id']))
- user = 'cli:' + getpass.getuser()
- create_log("Fileset merge", user, f"Merged Fileset:{matched_game['fileset']} and Fileset:{fileset[0]['id']}")
+ cursor.execute(
+ query,
+ (matched_game["id"], status, matched_game["key"], fileset[0]["id"]),
+ )
+ user = "cli:" + getpass.getuser()
+ create_log(
+ "Fileset merge",
+ user,
+ f"Merged Fileset:{matched_game['fileset']} and Fileset:{fileset[0]['id']}",
+ )
log_last = create_log(category_text, user, log_text)
- cursor.execute("UPDATE history SET log = %s WHERE id = %s", (log_last, history_last))
+ cursor.execute(
+ "UPDATE history SET log = %s WHERE id = %s", (log_last, history_last)
+ )
conn.commit()
More information about the Scummvm-git-logs
mailing list