Active_prgs/Gromulus/Gromulus_Lib.py

"""Core data access methods for Gromulus."""

import configparser
import os
import shutil
import sqlite3
import xml.etree.ElementTree as ET

import Tool_Box.NinoGenTools


config = configparser.ConfigParser()
config.read("/home/nino/.gromulus/gromulus_cfg.ini")

#conn = sqlite3.connect(config.get('RootConfig','databasePath'))


class DatabaseManager:
    def __init__(self):
        self.db_path = config.get('RootConfig','databasePath')  # Hardcoded DB path
        self.conn = sqlite3.connect(self.db_path)

    def get_connection(self):
        return self.conn

    def close(self):
        if self.conn:
            self.conn.close()
            self.conn = None


def _column_exists(conn, table_name, column_name):
    columns = [row[1] for row in conn.execute(f"PRAGMA table_info({table_name})")]
    return column_name in columns


def EnsureMetadataSchema(conn):
    """Adds metadata-scoped columns/tables required by current import and matching workflow."""
    conn.execute(
        """
        CREATE TABLE IF NOT EXISTS dat_import_history(
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            app_system_id INTEGER NOT NULL,
            source TEXT NOT NULL,
            dat_name TEXT,
            dat_description TEXT,
            dat_version TEXT,
            imported_at TEXT NOT NULL,
            entry_count INTEGER NOT NULL DEFAULT 0,
            UNIQUE(app_system_id, source),
            FOREIGN KEY(app_system_id) REFERENCES main_app_system(id)
        )
        """
    )

    if not _column_exists(conn, "no_intro_main", "app_system_id"):
        conn.execute("ALTER TABLE no_intro_main ADD COLUMN app_system_id INTEGER")
    if not _column_exists(conn, "tosec_main", "app_system_id"):
        conn.execute("ALTER TABLE tosec_main ADD COLUMN app_system_id INTEGER")

    # Backfill app_system_id from matched file hashes where possible.
    conn.execute(
        """
        UPDATE no_intro_main
        SET app_system_id = (
            SELECT ma.system_console
            FROM main_app_file_hash fh
            JOIN main_app ma ON ma.id = fh.container_file_id
            WHERE lower(fh.file_md5_hash) = lower(no_intro_main.md5)
            GROUP BY ma.system_console
            ORDER BY COUNT(*) DESC
            LIMIT 1
        )
        WHERE app_system_id IS NULL
        """
    )
    conn.execute(
        """
        UPDATE tosec_main
        SET app_system_id = (
            SELECT ma.system_console
            FROM main_app_file_hash fh
            JOIN main_app ma ON ma.id = fh.container_file_id
            WHERE lower(fh.file_md5_hash) = lower(tosec_main.md5)
            GROUP BY ma.system_console
            ORDER BY COUNT(*) DESC
            LIMIT 1
        )
        WHERE app_system_id IS NULL
        """
    )

    conn.execute("CREATE INDEX IF NOT EXISTS idx_no_intro_system_md5 ON no_intro_main(app_system_id, md5)")
    conn.execute("CREATE INDEX IF NOT EXISTS idx_tosec_system_md5 ON tosec_main(app_system_id, md5)")
    conn.execute("CREATE INDEX IF NOT EXISTS idx_dat_import_system_source ON dat_import_history(app_system_id, source)")
    conn.commit()


def _record_dat_import(conn, app_system_id, source, dat_name, dat_description, dat_version, entry_count):
    conn.execute(
        """
        INSERT INTO dat_import_history(
            app_system_id, source, dat_name, dat_description, dat_version, imported_at, entry_count
        ) VALUES (?, ?, ?, ?, ?, datetime('now'), ?)
        ON CONFLICT(app_system_id, source) DO UPDATE SET
            dat_name=excluded.dat_name,
            dat_description=excluded.dat_description,
            dat_version=excluded.dat_version,
            imported_at=excluded.imported_at,
            entry_count=excluded.entry_count
        """,
        (app_system_id, source, dat_name, dat_description, dat_version, entry_count),
    )


def ImportNewNoIntroDat(datPath, app_system_id, conn):
    """Replace No-Intro data using a DAT file."""
    EnsureMetadataSchema(conn)
    tree = ET.parse(datPath)
    root = tree.getroot()
    cursor = conn.cursor()

    header = root.find("header")
    if header is None:
        raise ValueError("Invalid DAT file: missing <header> section.")

    headerID = header.findtext("id") or ""
    headerName = header.findtext("name") or ""
    headerDesc = header.findtext("description") or ""
    headerVer = header.findtext("version") or ""

    cursor.execute("DELETE FROM no_intro_main WHERE app_system_id = ?", (app_system_id,))
    insert_sql = """
        INSERT INTO no_intro_main(
            game_name, no_intro_id, clone_of_id, description, rom_name,
            crc, md5, sha1, sha256, status, no_intro_system_id, app_system_id
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    """
    imported_count = 0
    for elm in root.findall("game"):
        gameName = elm.get("name")
        gameID = elm.get("id") or ""
        gameCloneOfID = elm.get("cloneofid")
        gameDesc = elm.findtext("description")

        rom = elm.find("rom")
        if rom is None:
            continue
        romName = rom.get("name")
        romCRC = rom.get("crc")
        romMD5 = rom.get("md5")
        romSHA1 = rom.get("sha1")
        romSHA256 = rom.get("sha256")
        romStatus = rom.get("status")

        cursor.execute(
            insert_sql,
            (
                gameName,
                gameID,
                gameCloneOfID,
                gameDesc,
                romName,
                romCRC,
                romMD5,
                romSHA1,
                romSHA256,
                romStatus,
                int(headerID) if headerID.isdigit() else 0,
                app_system_id,
            ),
        )
        imported_count += 1

    _record_dat_import(
        conn,
        app_system_id,
        "no_intro",
        headerName,
        headerDesc,
        headerVer,
        imported_count,
    )

    conn.commit()


def ImportNewTosecDat(datPath, app_system_id, conn):
    """Replace TOSEC data using a DAT file."""
    EnsureMetadataSchema(conn)
    cursor = conn.cursor()
    tree = ET.parse(datPath)
    root = tree.getroot()

    header = root.find("header")
    if header is None:
        raise ValueError("Invalid DAT file: missing <header> section.")
    system_name = header.findtext("name") or ""
    system_desc = header.findtext("description") or ""
    version = header.findtext("version") or ""

    cursor.execute("DELETE FROM tosec_main WHERE app_system_id = ?", (app_system_id,))
    insert_sql = """
        INSERT INTO tosec_main(game_name, description, rom_name, crc, md5, sha1, system_id, app_system_id)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    """
    imported_count = 0
    for game in root.findall("game"):
        game_name = game.attrib.get("name", "")
        game_desc = game.findtext("description")
        rom = game.find("rom")
        if rom is None:
            continue
        cursor.execute(
            insert_sql,
            (
                game_name,
                game_desc,
                rom.attrib.get("name", ""),
                rom.attrib.get("crc", ""),
                rom.attrib.get("md5", ""),
                rom.attrib.get("sha1", ""),
                app_system_id,
                app_system_id,
            ),
        )
        imported_count += 1

    _record_dat_import(
        conn,
        app_system_id,
        "tosec",
        system_name,
        system_desc,
        version,
        imported_count,
    )

    conn.commit()
    print("TOSEC DAT imported successfully.")


def AddNewSystem(systemName, shortName, relativePath, conn):
    """Adding a single system, with path where the ROMs will live."""
    conn.execute(
        'INSERT INTO main_app_system("name", "short_name", "relative_file_path") VALUES (?, ?, ?)',
        (systemName, shortName, relativePath),
    )
    conn.commit()


def AddNewRoms(pathToRoms, systemID, conn, testOnly = False):
    """Adding additional roms."""
    importedCount = 0
    alreadyExistsCount = 0
    errImportCount = 0

    system_row = conn.execute(
        "SELECT relative_file_path FROM main_app_system WHERE id = ?",
        (systemID,),
    ).fetchone()
    if not system_row:
        raise ValueError(f"System id {systemID} not found in main_app_system.")

    systemRomPath = system_row[0]
    fullRomPath = os.path.join(config.get("RootConfig", "softwareBackupStartDir"), systemRomPath)
    os.makedirs(fullRomPath, exist_ok=True)

    currentHashList = {
        row[0]
        for row in conn.execute("SELECT container_md5_hash FROM main_app WHERE container_md5_hash IS NOT NULL")
    }
    print(len(currentHashList))

    hshr = Tool_Box.NinoGenTools.HashFileContents("md5")
    for testFile in os.listdir(pathToRoms):
        source_path = os.path.join(pathToRoms, testFile)
        if not os.path.isfile(source_path):
            continue

        print(f"\n\nNow analyzing {testFile}...")
        hashVal = hshr.HashFile(source_path)
        if hashVal in currentHashList:
            print("This file is already in your collection, skipping.")
            alreadyExistsCount +=1
        else:
            print("This file is unique.  Adding to collection.")
            fileNameInc = 0
            targetFileName = testFile
            while os.path.isfile(os.path.join(fullRomPath,targetFileName)):
                fileNameInc +=1
                testFileNamePart, testFileExt = os.path.splitext(testFile)
                targetFileName = testFileNamePart +"_"+ str(fileNameInc) + testFileExt
            if testOnly:
                pass
            else:
                try:
                    shutil.copyfile(source_path, os.path.join(fullRomPath, targetFileName))
                    conn.execute(
                        'INSERT INTO main_app("container_file_name", "system_console", "container_md5_hash") VALUES(?, ?, ?)',
                        (targetFileName, systemID, hashVal),
                    )
                    conn.commit()
                except Exception:
                    errImportCount += 1
                    continue
            currentHashList.add(hashVal)
            importedCount +=1
    return importedCount, alreadyExistsCount, errImportCount


def GetSystemList(conn):
    res = conn.execute("SELECT id, name, short_name, relative_file_path FROM main_app_system ")
    systemNamesMatrix = {}
    for h in res:
        systemNamesMatrix[h[1]] = [h[0],h[2],h[3]]
    return systemNamesMatrix

def GetGameListBySystem(systemName, conn):
    system_row = conn.execute(
        """
        SELECT id
        FROM main_app_system
        WHERE lower(short_name) = lower(?)
           OR lower(name) = lower(?)
        """,
        (systemName, systemName),
    ).fetchone()
    if not system_row:
        return {}

    system_id = system_row[0]
    no_intro_scoped = _column_exists(conn, "no_intro_main", "app_system_id")
    tosec_scoped = _column_exists(conn, "tosec_main", "app_system_id")

    no_intro_join = (
        "LEFT JOIN no_intro_main nim ON lower(fh.file_md5_hash) = lower(nim.md5) "
        "AND nim.app_system_id = ma.system_console"
        if no_intro_scoped
        else "LEFT JOIN no_intro_main nim ON lower(fh.file_md5_hash) = lower(nim.md5)"
    )
    tosec_join = (
        "LEFT JOIN tosec_main tm ON lower(fh.file_md5_hash) = lower(tm.md5) "
        "AND tm.app_system_id = ma.system_console"
        if tosec_scoped
        else "LEFT JOIN tosec_main tm ON lower(fh.file_md5_hash) = lower(tm.md5)"
    )

    res = conn.execute(
        f"""
        SELECT ma.id,
               ma.game_name,
               nim.game_name,
               tm.game_name,
               COALESCE(fh.file_name, ma.container_file_name)
        FROM main_app ma
        LEFT JOIN main_app_file_hash fh
            ON fh.id = (
                SELECT MIN(fh2.id)
                FROM main_app_file_hash fh2
                WHERE fh2.container_file_id = ma.id
            )
        {no_intro_join}
        {tosec_join}
        WHERE ma.system_console = ?
        """,
        (system_id,),
    )
    gameNamesMatrix = {}
    duplicate_counter = {}
    for h in res:
        if h[1]:
            display_name = h[1]
        elif h[2]:
            display_name = h[2]
        elif h[3]:
            display_name = h[3]
        else:
            display_name = h[4] or f"Unknown-{h[0]}"

        # Keep titles unique in the listbox mapping.
        if display_name in gameNamesMatrix:
            duplicate_counter[display_name] = duplicate_counter.get(display_name, 1) + 1
            unique_name = f"{display_name} ({duplicate_counter[display_name]})"
            gameNamesMatrix[unique_name] = h[0]
        else:
            duplicate_counter[display_name] = 1
            gameNamesMatrix[display_name] = h[0]

    return gameNamesMatrix


def GetSingleGameById(game_id, conn):
    """Fetches full metadata for a single ROM entry, including No-Intro and TOSEC info if available."""
    cursor = conn.cursor()

    cursor.execute("""
        SELECT ma.id,
               ma.game_name,
               ma.container_file_name,
               sys.name,
               sys.short_name,
               sys.relative_file_path,
               ma.container_md5_hash,
               ma.system_console
        FROM main_app ma
        LEFT JOIN main_app_system sys ON ma.system_console = sys.id
        WHERE ma.id = ?
    """, (game_id,))
    game = cursor.fetchone()
    if not game:
        return None

    file_hash_row = cursor.execute(
        """
        SELECT file_name, file_md5_hash
        FROM main_app_file_hash
        WHERE container_file_id = ?
        ORDER BY id ASC
        LIMIT 1
        """,
        (game[0],),
    ).fetchone()

    resolved_file_name = file_hash_row[0] if file_hash_row and file_hash_row[0] else game[2]
    resolved_hash = file_hash_row[1] if file_hash_row and file_hash_row[1] else game[6]

    no_intro_scoped = _column_exists(conn, "no_intro_main", "app_system_id")
    if no_intro_scoped:
        no_intro = cursor.execute(
            """
            SELECT nim.game_name
            FROM no_intro_main nim
            WHERE lower(nim.md5) = lower(?)
              AND nim.app_system_id = ?
            LIMIT 1
            """,
            (resolved_hash, game[7]),
        ).fetchone()
    else:
        no_intro = cursor.execute(
            """
            SELECT nim.game_name
            FROM no_intro_main nim
            WHERE lower(nim.md5) = lower(?)
            LIMIT 1
            """,
            (resolved_hash,),
        ).fetchone()

    tosec_scoped = _column_exists(conn, "tosec_main", "app_system_id")
    if tosec_scoped:
        tosec = cursor.execute(
            """
            SELECT tm.game_name
            FROM tosec_main tm
            WHERE lower(tm.md5) = lower(?)
              AND tm.app_system_id = ?
            LIMIT 1
            """,
            (resolved_hash, game[7]),
        ).fetchone()
    else:
        tosec = cursor.execute(
            """
            SELECT tm.game_name
            FROM tosec_main tm
            WHERE lower(tm.md5) = lower(?)
            LIMIT 1
            """,
            (resolved_hash,),
        ).fetchone()

    if game[5]:
        file_path = os.path.join(
            config.get("RootConfig", "softwareBackupStartDir"),
            game[5],
            resolved_file_name or "",
        )
    else:
        file_path = resolved_file_name or ""

    return {
        "id": game[0],
        "game_name": game[1],
        "hash": resolved_hash,
        "filename": resolved_file_name,
        "path": file_path,
        "system_name": game[3],
        "system_short_name": game[4],
        "no_intro_system": game[3],
        "no_intro_game": no_intro[0] if no_intro else None,
        "tosec_system": game[3],
        "tosec_game": tosec[0] if tosec else None,
    }

    
if __name__ == '__main__':
    pass
    #test = GetSystemList()
    #print(test.keys())
    #AddNewSystem("Super Nintendo", "SNES", r"Roms/Snes/Gromulus")
    #ImportNewNoIntroDat("/home/nino/MyCode/Python/Active_prgs/Gromulus/_data/_dats/Nintendo - Super Nintendo Entertainment System (20230516-033614).dat")
    #AddNewRoms('/mnt/smb/HomeData/ninoborges/Emulation/Roms/Snes/Random2',"1",testOnly=True)
    #res = conn.execute("SELECT * FROM main_app LEFT JOIN no_intro_main ON main_app.md5 = no_intro_main.md5")
    #for h in res:
    #    print(h)
    #    print("\n")
    #GetGameListBySystem('snes')
    #GetSingleGameById('224')
Revision:	978
Committed:	Fri Feb 13 22:49:37 2026 UTC (6 weeks, 1 day ago) by nino.borges
Content type:	text/x-python
File size:	16492 byte(s)
Log Message:	System-scoped DAT import refactor and metadata matching improvements Refactored DAT import workflow so No-Intro and TOSEC imports are tied to a selected local main_app_system instead of global table replacement. Updated UI import flow to prompt for target system before DAT import. Added schema support for per-system DAT scoping (app_system_id) and DAT import tracking (dat_import_history with source/version/date/count). Updated No-Intro/TOSEC import logic to replace rows only for the selected system and upsert last-import metadata. Switched game metadata matching to use main_app_file_hash.file_md5_hash (via container_file_id) instead of container hash. Updated game list/detail lookups to use file-level hash matching, including case-insensitive MD5 comparison. Improved TOSEC/No-Intro lookup accuracy and kept read paths non-destructive (schema writes only during migration/import paths). Added/updated indexing and normalization hooks to support system-scoped matching performance and backward compatibility.
#	User	Rev	Content
1	nino.borges	976	"""Core data access methods for Gromulus."""
2	nino.borges	795
3	nino.borges	976	import configparser
4			import os
5			import shutil
6			import sqlite3
7	nino.borges	795	import xml.etree.ElementTree as ET
8
9	nino.borges	976	import Tool_Box.NinoGenTools
10	nino.borges	795
11	nino.borges	976
12	nino.borges	805	config = configparser.ConfigParser()
13			config.read("/home/nino/.gromulus/gromulus_cfg.ini")
14	nino.borges	795
15	nino.borges	907	#conn = sqlite3.connect(config.get('RootConfig','databasePath'))
16	nino.borges	795
17
18	nino.borges	907	class DatabaseManager:
19			def __init__(self):
20			self.db_path = config.get('RootConfig','databasePath') # Hardcoded DB path
21			self.conn = sqlite3.connect(self.db_path)
22
23			def get_connection(self):
24			return self.conn
25
26			def close(self):
27			if self.conn:
28			self.conn.close()
29			self.conn = None
30
31
32	nino.borges	978	def _column_exists(conn, table_name, column_name):
33			columns = [row[1] for row in conn.execute(f"PRAGMA table_info({table_name})")]
34			return column_name in columns
35
36
37			def EnsureMetadataSchema(conn):
38			"""Adds metadata-scoped columns/tables required by current import and matching workflow."""
39			conn.execute(
40			"""
41			CREATE TABLE IF NOT EXISTS dat_import_history(
42			id INTEGER PRIMARY KEY AUTOINCREMENT,
43			app_system_id INTEGER NOT NULL,
44			source TEXT NOT NULL,
45			dat_name TEXT,
46			dat_description TEXT,
47			dat_version TEXT,
48			imported_at TEXT NOT NULL,
49			entry_count INTEGER NOT NULL DEFAULT 0,
50			UNIQUE(app_system_id, source),
51			FOREIGN KEY(app_system_id) REFERENCES main_app_system(id)
52			)
53			"""
54			)
55
56			if not _column_exists(conn, "no_intro_main", "app_system_id"):
57			conn.execute("ALTER TABLE no_intro_main ADD COLUMN app_system_id INTEGER")
58			if not _column_exists(conn, "tosec_main", "app_system_id"):
59			conn.execute("ALTER TABLE tosec_main ADD COLUMN app_system_id INTEGER")
60
61			# Backfill app_system_id from matched file hashes where possible.
62			conn.execute(
63			"""
64			UPDATE no_intro_main
65			SET app_system_id = (
66			SELECT ma.system_console
67			FROM main_app_file_hash fh
68			JOIN main_app ma ON ma.id = fh.container_file_id
69			WHERE lower(fh.file_md5_hash) = lower(no_intro_main.md5)
70			GROUP BY ma.system_console
71			ORDER BY COUNT(*) DESC
72			LIMIT 1
73			)
74			WHERE app_system_id IS NULL
75			"""
76			)
77			conn.execute(
78			"""
79			UPDATE tosec_main
80			SET app_system_id = (
81			SELECT ma.system_console
82			FROM main_app_file_hash fh
83			JOIN main_app ma ON ma.id = fh.container_file_id
84			WHERE lower(fh.file_md5_hash) = lower(tosec_main.md5)
85			GROUP BY ma.system_console
86			ORDER BY COUNT(*) DESC
87			LIMIT 1
88			)
89			WHERE app_system_id IS NULL
90			"""
91			)
92
93			conn.execute("CREATE INDEX IF NOT EXISTS idx_no_intro_system_md5 ON no_intro_main(app_system_id, md5)")
94			conn.execute("CREATE INDEX IF NOT EXISTS idx_tosec_system_md5 ON tosec_main(app_system_id, md5)")
95			conn.execute("CREATE INDEX IF NOT EXISTS idx_dat_import_system_source ON dat_import_history(app_system_id, source)")
96			conn.commit()
97
98
99			def _record_dat_import(conn, app_system_id, source, dat_name, dat_description, dat_version, entry_count):
100			conn.execute(
101			"""
102			INSERT INTO dat_import_history(
103			app_system_id, source, dat_name, dat_description, dat_version, imported_at, entry_count
104			) VALUES (?, ?, ?, ?, ?, datetime('now'), ?)
105			ON CONFLICT(app_system_id, source) DO UPDATE SET
106			dat_name=excluded.dat_name,
107			dat_description=excluded.dat_description,
108			dat_version=excluded.dat_version,
109			imported_at=excluded.imported_at,
110			entry_count=excluded.entry_count
111			""",
112			(app_system_id, source, dat_name, dat_description, dat_version, entry_count),
113			)
114
115
116			def ImportNewNoIntroDat(datPath, app_system_id, conn):
117	nino.borges	976	"""Replace No-Intro data using a DAT file."""
118	nino.borges	978	EnsureMetadataSchema(conn)
119	nino.borges	795	tree = ET.parse(datPath)
120			root = tree.getroot()
121	nino.borges	976	cursor = conn.cursor()
122	nino.borges	795
123	nino.borges	976	header = root.find("header")
124			if header is None:
125			raise ValueError("Invalid DAT file: missing <header> section.")
126	nino.borges	795
127	nino.borges	978	headerID = header.findtext("id") or ""
128			headerName = header.findtext("name") or ""
129			headerDesc = header.findtext("description") or ""
130			headerVer = header.findtext("version") or ""
131	nino.borges	795
132	nino.borges	978	cursor.execute("DELETE FROM no_intro_main WHERE app_system_id = ?", (app_system_id,))
133	nino.borges	976	insert_sql = """
134			INSERT INTO no_intro_main(
135			game_name, no_intro_id, clone_of_id, description, rom_name,
136	nino.borges	978	crc, md5, sha1, sha256, status, no_intro_system_id, app_system_id
137			) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
138	nino.borges	976	"""
139	nino.borges	978	imported_count = 0
140	nino.borges	976	for elm in root.findall("game"):
141			gameName = elm.get("name")
142	nino.borges	978	gameID = elm.get("id") or ""
143	nino.borges	976	gameCloneOfID = elm.get("cloneofid")
144			gameDesc = elm.findtext("description")
145
146			rom = elm.find("rom")
147			if rom is None:
148			continue
149			romName = rom.get("name")
150			romCRC = rom.get("crc")
151			romMD5 = rom.get("md5")
152			romSHA1 = rom.get("sha1")
153			romSHA256 = rom.get("sha256")
154			romStatus = rom.get("status")
155
156			cursor.execute(
157			insert_sql,
158			(
159			gameName,
160			gameID,
161			gameCloneOfID,
162			gameDesc,
163			romName,
164			romCRC,
165			romMD5,
166			romSHA1,
167			romSHA256,
168			romStatus,
169	nino.borges	978	int(headerID) if headerID.isdigit() else 0,
170			app_system_id,
171	nino.borges	976	),
172			)
173	nino.borges	978	imported_count += 1
174	nino.borges	976
175	nino.borges	978	_record_dat_import(
176			conn,
177			app_system_id,
178			"no_intro",
179			headerName,
180			headerDesc,
181			headerVer,
182			imported_count,
183			)
184
185	nino.borges	795	conn.commit()
186
187
188	nino.borges	978	def ImportNewTosecDat(datPath, app_system_id, conn):
189	nino.borges	976	"""Replace TOSEC data using a DAT file."""
190	nino.borges	978	EnsureMetadataSchema(conn)
191	nino.borges	907	cursor = conn.cursor()
192	nino.borges	806	tree = ET.parse(datPath)
193			root = tree.getroot()
194
195	nino.borges	907	header = root.find("header")
196	nino.borges	976	if header is None:
197			raise ValueError("Invalid DAT file: missing <header> section.")
198			system_name = header.findtext("name") or ""
199	nino.borges	978	system_desc = header.findtext("description") or ""
200			version = header.findtext("version") or ""
201	nino.borges	906
202	nino.borges	978	cursor.execute("DELETE FROM tosec_main WHERE app_system_id = ?", (app_system_id,))
203	nino.borges	976	insert_sql = """
204	nino.borges	978	INSERT INTO tosec_main(game_name, description, rom_name, crc, md5, sha1, system_id, app_system_id)
205			VALUES (?, ?, ?, ?, ?, ?, ?, ?)
206	nino.borges	976	"""
207	nino.borges	978	imported_count = 0
208	nino.borges	907	for game in root.findall("game"):
209			game_name = game.attrib.get("name", "")
210	nino.borges	976	game_desc = game.findtext("description")
211	nino.borges	907	rom = game.find("rom")
212			if rom is None:
213			continue
214	nino.borges	976	cursor.execute(
215			insert_sql,
216			(
217			game_name,
218			game_desc,
219			rom.attrib.get("name", ""),
220			rom.attrib.get("crc", ""),
221			rom.attrib.get("md5", ""),
222			rom.attrib.get("sha1", ""),
223	nino.borges	978	app_system_id,
224			app_system_id,
225	nino.borges	976	),
226	nino.borges	907	)
227	nino.borges	978	imported_count += 1
228	nino.borges	907
229	nino.borges	978	_record_dat_import(
230			conn,
231			app_system_id,
232			"tosec",
233			system_name,
234			system_desc,
235			version,
236			imported_count,
237			)
238
239	nino.borges	806	conn.commit()
240	nino.borges	907	print("TOSEC DAT imported successfully.")
241	nino.borges	806
242	nino.borges	906
243	nino.borges	907	def AddNewSystem(systemName, shortName, relativePath, conn):
244	nino.borges	805	"""Adding a single system, with path where the ROMs will live."""
245	nino.borges	976	conn.execute(
246			'INSERT INTO main_app_system("name", "short_name", "relative_file_path") VALUES (?, ?, ?)',
247			(systemName, shortName, relativePath),
248			)
249	nino.borges	805	conn.commit()
250
251	nino.borges	976
252	nino.borges	907	def AddNewRoms(pathToRoms, systemID, conn, testOnly = False):
253	nino.borges	795	"""Adding additional roms."""
254	nino.borges	805	importedCount = 0
255			alreadyExistsCount = 0
256			errImportCount = 0
257
258	nino.borges	976	system_row = conn.execute(
259			"SELECT relative_file_path FROM main_app_system WHERE id = ?",
260			(systemID,),
261			).fetchone()
262			if not system_row:
263			raise ValueError(f"System id {systemID} not found in main_app_system.")
264
265			systemRomPath = system_row[0]
266			fullRomPath = os.path.join(config.get("RootConfig", "softwareBackupStartDir"), systemRomPath)
267			os.makedirs(fullRomPath, exist_ok=True)
268
269			currentHashList = {
270			row[0]
271			for row in conn.execute("SELECT container_md5_hash FROM main_app WHERE container_md5_hash IS NOT NULL")
272			}
273	nino.borges	795	print(len(currentHashList))
274	nino.borges	805
275	nino.borges	976	hshr = Tool_Box.NinoGenTools.HashFileContents("md5")
276	nino.borges	795	for testFile in os.listdir(pathToRoms):
277	nino.borges	976	source_path = os.path.join(pathToRoms, testFile)
278			if not os.path.isfile(source_path):
279			continue
280
281	nino.borges	805	print(f"\n\nNow analyzing {testFile}...")
282	nino.borges	976	hashVal = hshr.HashFile(source_path)
283	nino.borges	795	if hashVal in currentHashList:
284			print("This file is already in your collection, skipping.")
285	nino.borges	805	alreadyExistsCount +=1
286	nino.borges	795	else:
287			print("This file is unique. Adding to collection.")
288	nino.borges	806	fileNameInc = 0
289			targetFileName = testFile
290			while os.path.isfile(os.path.join(fullRomPath,targetFileName)):
291			fileNameInc +=1
292			testFileNamePart, testFileExt = os.path.splitext(testFile)
293			targetFileName = testFileNamePart +"_"+ str(fileNameInc) + testFileExt
294			if testOnly:
295			pass
296	nino.borges	805	else:
297	nino.borges	976	try:
298			shutil.copyfile(source_path, os.path.join(fullRomPath, targetFileName))
299			conn.execute(
300			'INSERT INTO main_app("container_file_name", "system_console", "container_md5_hash") VALUES(?, ?, ?)',
301			(targetFileName, systemID, hashVal),
302			)
303			conn.commit()
304			except Exception:
305			errImportCount += 1
306			continue
307			currentHashList.add(hashVal)
308	nino.borges	806	importedCount +=1
309	nino.borges	805	return importedCount, alreadyExistsCount, errImportCount
310	nino.borges	795
311	nino.borges	976
312	nino.borges	907	def GetSystemList(conn):
313	nino.borges	805	res = conn.execute("SELECT id, name, short_name, relative_file_path FROM main_app_system ")
314			systemNamesMatrix = {}
315			for h in res:
316			systemNamesMatrix[h[1]] = [h[0],h[2],h[3]]
317			return systemNamesMatrix
318
319	nino.borges	907	def GetGameListBySystem(systemName, conn):
320	nino.borges	976	system_row = conn.execute(
321			"""
322			SELECT id
323			FROM main_app_system
324			WHERE lower(short_name) = lower(?)
325			OR lower(name) = lower(?)
326			""",
327			(systemName, systemName),
328			).fetchone()
329			if not system_row:
330			return {}
331
332			system_id = system_row[0]
333	nino.borges	978	no_intro_scoped = _column_exists(conn, "no_intro_main", "app_system_id")
334			tosec_scoped = _column_exists(conn, "tosec_main", "app_system_id")
335
336			no_intro_join = (
337			"LEFT JOIN no_intro_main nim ON lower(fh.file_md5_hash) = lower(nim.md5) "
338			"AND nim.app_system_id = ma.system_console"
339			if no_intro_scoped
340			else "LEFT JOIN no_intro_main nim ON lower(fh.file_md5_hash) = lower(nim.md5)"
341			)
342			tosec_join = (
343			"LEFT JOIN tosec_main tm ON lower(fh.file_md5_hash) = lower(tm.md5) "
344			"AND tm.app_system_id = ma.system_console"
345			if tosec_scoped
346			else "LEFT JOIN tosec_main tm ON lower(fh.file_md5_hash) = lower(tm.md5)"
347			)
348
349	nino.borges	976	res = conn.execute(
350	nino.borges	978	f"""
351	nino.borges	976	SELECT ma.id,
352			ma.game_name,
353			nim.game_name,
354			tm.game_name,
355			COALESCE(fh.file_name, ma.container_file_name)
356			FROM main_app ma
357			LEFT JOIN main_app_file_hash fh
358			ON fh.id = (
359			SELECT MIN(fh2.id)
360			FROM main_app_file_hash fh2
361			WHERE fh2.container_file_id = ma.id
362			)
363	nino.borges	978	{no_intro_join}
364			{tosec_join}
365	nino.borges	976	WHERE ma.system_console = ?
366			""",
367			(system_id,),
368			)
369	nino.borges	795	gameNamesMatrix = {}
370	nino.borges	976	duplicate_counter = {}
371	nino.borges	795	for h in res:
372			if h[1]:
373	nino.borges	976	display_name = h[1]
374	nino.borges	795	elif h[2]:
375	nino.borges	976	display_name = h[2]
376	nino.borges	806	elif h[3]:
377	nino.borges	976	display_name = h[3]
378	nino.borges	795	else:
379	nino.borges	976	display_name = h[4] or f"Unknown-{h[0]}"
380	nino.borges	795
381	nino.borges	976	# Keep titles unique in the listbox mapping.
382			if display_name in gameNamesMatrix:
383			duplicate_counter[display_name] = duplicate_counter.get(display_name, 1) + 1
384			unique_name = f"{display_name} ({duplicate_counter[display_name]})"
385			gameNamesMatrix[unique_name] = h[0]
386			else:
387			duplicate_counter[display_name] = 1
388			gameNamesMatrix[display_name] = h[0]
389
390	nino.borges	795	return gameNamesMatrix
391
392	nino.borges	976
393	nino.borges	907	def GetSingleGameById(game_id, conn):
394			"""Fetches full metadata for a single ROM entry, including No-Intro and TOSEC info if available."""
395			cursor = conn.cursor()
396
397			cursor.execute("""
398	nino.borges	976	SELECT ma.id,
399			ma.game_name,
400			ma.container_file_name,
401			sys.name,
402			sys.short_name,
403			sys.relative_file_path,
404	nino.borges	978	ma.container_md5_hash,
405			ma.system_console
406	nino.borges	907	FROM main_app ma
407	nino.borges	976	LEFT JOIN main_app_system sys ON ma.system_console = sys.id
408	nino.borges	907	WHERE ma.id = ?
409			""", (game_id,))
410			game = cursor.fetchone()
411	nino.borges	976	if not game:
412			return None
413	nino.borges	907
414	nino.borges	976	file_hash_row = cursor.execute(
415			"""
416			SELECT file_name, file_md5_hash
417			FROM main_app_file_hash
418			WHERE container_file_id = ?
419			ORDER BY id ASC
420			LIMIT 1
421			""",
422			(game[0],),
423			).fetchone()
424
425			resolved_file_name = file_hash_row[0] if file_hash_row and file_hash_row[0] else game[2]
426			resolved_hash = file_hash_row[1] if file_hash_row and file_hash_row[1] else game[6]
427
428	nino.borges	978	no_intro_scoped = _column_exists(conn, "no_intro_main", "app_system_id")
429			if no_intro_scoped:
430			no_intro = cursor.execute(
431			"""
432			SELECT nim.game_name
433			FROM no_intro_main nim
434			WHERE lower(nim.md5) = lower(?)
435			AND nim.app_system_id = ?
436			LIMIT 1
437			""",
438			(resolved_hash, game[7]),
439			).fetchone()
440			else:
441			no_intro = cursor.execute(
442			"""
443			SELECT nim.game_name
444			FROM no_intro_main nim
445			WHERE lower(nim.md5) = lower(?)
446			LIMIT 1
447			""",
448			(resolved_hash,),
449			).fetchone()
450	nino.borges	907
451	nino.borges	978	tosec_scoped = _column_exists(conn, "tosec_main", "app_system_id")
452			if tosec_scoped:
453			tosec = cursor.execute(
454			"""
455			SELECT tm.game_name
456			FROM tosec_main tm
457			WHERE lower(tm.md5) = lower(?)
458			AND tm.app_system_id = ?
459			LIMIT 1
460			""",
461			(resolved_hash, game[7]),
462			).fetchone()
463			else:
464			tosec = cursor.execute(
465			"""
466			SELECT tm.game_name
467			FROM tosec_main tm
468			WHERE lower(tm.md5) = lower(?)
469			LIMIT 1
470			""",
471			(resolved_hash,),
472			).fetchone()
473	nino.borges	907
474	nino.borges	976	if game[5]:
475			file_path = os.path.join(
476			config.get("RootConfig", "softwareBackupStartDir"),
477			game[5],
478			resolved_file_name or "",
479			)
480			else:
481			file_path = resolved_file_name or ""
482	nino.borges	907
483			return {
484			"id": game[0],
485	nino.borges	976	"game_name": game[1],
486			"hash": resolved_hash,
487			"filename": resolved_file_name,
488			"path": file_path,
489			"system_name": game[3],
490			"system_short_name": game[4],
491	nino.borges	978	"no_intro_system": game[3],
492			"no_intro_game": no_intro[0] if no_intro else None,
493			"tosec_system": game[3],
494			"tosec_game": tosec[0] if tosec else None,
495	nino.borges	907	}
496
497	nino.borges	795
498
499
500			if __name__ == '__main__':
501	nino.borges	806	pass
502	nino.borges	805	#test = GetSystemList()
503			#print(test.keys())
504			#AddNewSystem("Super Nintendo", "SNES", r"Roms/Snes/Gromulus")
505	nino.borges	795	#ImportNewNoIntroDat("/home/nino/MyCode/Python/Active_prgs/Gromulus/_data/_dats/Nintendo - Super Nintendo Entertainment System (20230516-033614).dat")
506	nino.borges	806	#AddNewRoms('/mnt/smb/HomeData/ninoborges/Emulation/Roms/Snes/Random2',"1",testOnly=True)
507	nino.borges	795	#res = conn.execute("SELECT * FROM main_app LEFT JOIN no_intro_main ON main_app.md5 = no_intro_main.md5")
508			#for h in res:
509			# print(h)
510			# print("\n")
511			#GetGameListBySystem('snes')
512	nino.borges	805	#GetSingleGameById('224')