Active_prgs/Gromulus/GromulusDatabaseUtilities.py

"""
This allows for some of the lower level db utilities, such as creating the inital Gromulus database, db backup, export, import for migration, etc. I'm assuming I'll use the create database once and then maybe each time I update the schema.
I'm not sure how I'll best do data migration manually.
"""

import sqlite3
import os
import shutil
import json


class GromulusDatabaseUtilities:
    def __init__(self, db_path: str):
        self.db_path = db_path
        self._canonical_tables = {
            "main_app_system": """
                CREATE TABLE main_app_system(
                    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                    name TEXT,
                    short_name TEXT,
                    relative_file_path TEXT
                )
            """,
            "main_app": """
                CREATE TABLE main_app(
                    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                    game_name TEXT,
                    game_name_scraped TEXT,
                    container_file_name TEXT,
                    description TEXT,
                    description_scraped TEXT,
                    system_console INTEGER,
                    system_console_scraped TEXT,
                    path_to_screenshot_box TEXT,
                    path_to_screenshot_title TEXT,
                    path_to_screenshot_ingame TEXT,
                    path_to_video TEXT,
                    user_notes TEXT,
                    container_md5_hash TEXT,
                    version TEXT
                )
            """,
            "no_intro_system": """
                CREATE TABLE no_intro_system(
                    id INTEGER NOT NULL PRIMARY KEY,
                    name TEXT,
                    description TEXT,
                    dat_version TEXT
                )
            """,
            "no_intro_main": """
                CREATE TABLE no_intro_main(
                    game_name TEXT,
                    no_intro_id TEXT,
                    clone_of_id TEXT,
                    description TEXT,
                    rom_name TEXT,
                    crc TEXT,
                    md5 TEXT,
                    sha1 TEXT,
                    sha256 TEXT,
                    status TEXT,
                    no_intro_system_id INTEGER NOT NULL,
                    app_system_id INTEGER,
                    FOREIGN KEY(no_intro_system_id) REFERENCES no_intro_system(id)
                )
            """,
            "tosec_main": """
                CREATE TABLE tosec_main(
                    game_name TEXT,
                    description TEXT,
                    rom_name TEXT,
                    crc TEXT,
                    md5 TEXT,
                    sha1 TEXT,
                    system_id INTEGER,
                    app_system_id INTEGER,
                    FOREIGN KEY(system_id) REFERENCES no_intro_system(id)
                )
            """,
            "dat_import_history": """
                CREATE TABLE 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)
                )
            """,
        }

    def create_database(self):
        """Creates a fresh Gromulus SQLite database using the canonical schema."""
        if os.path.exists(self.db_path):
            os.remove(self.db_path)

        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        for ddl in self._canonical_tables.values():
            cursor.execute(ddl)
        self._create_indexes(cursor)

        conn.commit()
        conn.close()
        print("Database created successfully at:", self.db_path)

    def normalize_schema(self):
        """
        Normalizes an existing DB to the canonical schema without dropping data.
        Adds missing tables/columns and backfills known old column names.
        """
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        for table_name, ddl in self._canonical_tables.items():
            cursor.execute(
                "SELECT name FROM sqlite_master WHERE type='table' AND name=?",
                (table_name,),
            )
            if cursor.fetchone() is None:
                cursor.execute(ddl)

        self._ensure_columns(
            cursor,
            "main_app",
            [
                "game_name", "game_name_scraped", "container_file_name", "description",
                "description_scraped", "system_console", "system_console_scraped",
                "path_to_screenshot_box", "path_to_screenshot_title",
                "path_to_screenshot_ingame", "path_to_video", "user_notes",
                "container_md5_hash", "version",
            ],
        )
        self._ensure_columns(cursor, "main_app_system", ["name", "short_name", "relative_file_path"])
        self._ensure_columns(cursor, "no_intro_system", ["name", "description", "dat_version"])
        self._ensure_columns(
            cursor,
            "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",
            ],
        )
        self._ensure_columns(
            cursor,
            "tosec_main",
            ["game_name", "description", "rom_name", "crc", "md5", "sha1", "system_id", "app_system_id"],
        )
        self._ensure_columns(
            cursor,
            "dat_import_history",
            ["app_system_id", "source", "dat_name", "dat_description", "dat_version", "imported_at", "entry_count"],
        )

        self._backfill_aliases(cursor)
        self._create_indexes(cursor)
        conn.commit()
        conn.close()
        print("Schema normalized successfully at:", self.db_path)

    def _ensure_columns(self, cursor, table_name, required_columns):
        cursor.execute(f"PRAGMA table_info({table_name})")
        existing_columns = {row[1] for row in cursor.fetchall()}
        for column_name in required_columns:
            if column_name not in existing_columns:
                cursor.execute(f"ALTER TABLE {table_name} ADD COLUMN {column_name} TEXT")

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

    def _backfill_aliases(self, cursor):
        # main_app alias mapping
        if self._column_exists(cursor, "main_app", "file_name"):
            cursor.execute(
                "UPDATE main_app SET container_file_name=file_name "
                "WHERE (container_file_name IS NULL OR container_file_name='') "
                "AND file_name IS NOT NULL"
            )
        if self._column_exists(cursor, "main_app", "md5"):
            cursor.execute(
                "UPDATE main_app SET container_md5_hash=md5 "
                "WHERE (container_md5_hash IS NULL OR container_md5_hash='') AND md5 IS NOT NULL"
            )
        if self._column_exists(cursor, "main_app", "system_id"):
            cursor.execute(
                "UPDATE main_app SET system_console=system_id "
                "WHERE (system_console IS NULL OR system_console='') AND system_id IS NOT NULL"
            )
        if self._column_exists(cursor, "main_app", "rom_filename"):
            cursor.execute(
                "UPDATE main_app SET container_file_name=rom_filename "
                "WHERE (container_file_name IS NULL OR container_file_name='') "
                "AND rom_filename IS NOT NULL"
            )
        if self._column_exists(cursor, "main_app", "rom_hash"):
            cursor.execute(
                "UPDATE main_app SET container_md5_hash=rom_hash "
                "WHERE (container_md5_hash IS NULL OR container_md5_hash='') "
                "AND rom_hash IS NOT NULL"
            )
        if self._column_exists(cursor, "main_app", "notes"):
            cursor.execute(
                "UPDATE main_app SET user_notes=notes "
                "WHERE (user_notes IS NULL OR user_notes='') AND notes IS NOT NULL"
            )

        # system table alias mapping
        if self._column_exists(cursor, "no_intro_system", "system_name"):
            cursor.execute(
                "UPDATE no_intro_system SET name=system_name "
                "WHERE (name IS NULL OR name='') AND system_name IS NOT NULL"
            )
        if self._column_exists(cursor, "no_intro_system", "system_description"):
            cursor.execute(
                "UPDATE no_intro_system SET description=system_description "
                "WHERE (description IS NULL OR description='') AND system_description IS NOT NULL"
            )

        # no_intro_main alias mapping
        if self._column_exists(cursor, "no_intro_main", "rom_hash"):
            cursor.execute(
                "UPDATE no_intro_main SET md5=rom_hash "
                "WHERE (md5 IS NULL OR md5='') AND rom_hash IS NOT NULL"
            )
        if self._column_exists(cursor, "no_intro_main", "rom_filename"):
            cursor.execute(
                "UPDATE no_intro_main SET rom_name=rom_filename "
                "WHERE (rom_name IS NULL OR rom_name='') AND rom_filename IS NOT NULL"
            )
        if self._column_exists(cursor, "no_intro_main", "system_id"):
            cursor.execute(
                "UPDATE no_intro_main SET no_intro_system_id=system_id "
                "WHERE (no_intro_system_id IS NULL OR no_intro_system_id='') AND system_id IS NOT NULL"
            )
        if self._column_exists(cursor, "no_intro_main", "app_system_id"):
            cursor.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
                """
            )

        # tosec_main alias mapping
        if self._column_exists(cursor, "tosec_main", "rom_hash"):
            cursor.execute(
                "UPDATE tosec_main SET md5=rom_hash "
                "WHERE (md5 IS NULL OR md5='') AND rom_hash IS NOT NULL"
            )
        if self._column_exists(cursor, "tosec_main", "rom_filename"):
            cursor.execute(
                "UPDATE tosec_main SET rom_name=rom_filename "
                "WHERE (rom_name IS NULL OR rom_name='') AND rom_filename IS NOT NULL"
            )
        if self._column_exists(cursor, "tosec_main", "app_system_id"):
            cursor.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
                """
            )

    def _create_indexes(self, cursor):
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_main_app_md5 ON main_app(container_md5_hash)")
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_main_app_system ON main_app(system_console)")
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_no_intro_md5 ON no_intro_main(md5)")
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_tosec_md5 ON tosec_main(md5)")
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_no_intro_system_md5 ON no_intro_main(app_system_id, md5)")
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_tosec_system_md5 ON tosec_main(app_system_id, md5)")
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_dat_import_system_source ON dat_import_history(app_system_id, source)")

    def backup_database(self, backup_path):
        """Copies the current database to a new location."""
        if not os.path.exists(self.db_path):
            raise FileNotFoundError("Database file not found.")
        shutil.copy2(self.db_path, backup_path)
        print(f"Database backed up to: {backup_path}")

    def export_data_to_json(self, export_path):
        """Exports all table data to a JSON file."""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        data = {}
        for table in [
            "main_app_system", "main_app",
            "no_intro_system", "no_intro_main",
            "tosec_main", "dat_import_history"
        ]:
            cursor.execute(
                "SELECT name FROM sqlite_master WHERE type='table' AND name=?",
                (table,),
            )
            if cursor.fetchone() is None:
                continue
            cursor.execute(f"SELECT * FROM {table}")
            columns = [description[0] for description in cursor.description]
            rows = cursor.fetchall()
            data[table] = [dict(zip(columns, row)) for row in rows]

        with open(export_path, "w", encoding="utf-8") as f:
            json.dump(data, f, indent=2)

        conn.close()
        print(f"Exported data to JSON: {export_path}")

    def import_data_from_json(self, import_path):
        """Imports table data from a JSON file (assumes schema is already created)."""
        if not os.path.exists(import_path):
            raise FileNotFoundError("Import file not found.")

        with open(import_path, "r", encoding="utf-8") as f:
            data = json.load(f)

        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        for table, rows in data.items():
            if not rows:
                continue
            columns = rows[0].keys()
            placeholders = ", ".join(["?"] * len(columns))
            col_list = ", ".join(columns)

            for row in rows:
                values = tuple(row[col] for col in columns)
                cursor.execute(f"INSERT INTO {table} ({col_list}) VALUES ({placeholders})", values)

        conn.commit()
        conn.close()
        print(f"Imported data from JSON: {import_path}")
Revision:	978
Committed:	Fri Feb 13 22:49:37 2026 UTC (6 weeks ago) by nino.borges
Content type:	text/x-python
File size:	14829 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	795	"""
2	nino.borges	905	This allows for some of the lower level db utilities, such as creating the inital Gromulus database, db backup, export, import for migration, etc. I'm assuming I'll use the create database once and then maybe each time I update the schema.
3			I'm not sure how I'll best do data migration manually.
4	nino.borges	795	"""
5
6			import sqlite3
7	nino.borges	905	import os
8			import shutil
9			import json
10	nino.borges	795
11
12	nino.borges	905	class GromulusDatabaseUtilities:
13			def __init__(self, db_path: str):
14			self.db_path = db_path
15	nino.borges	976	self._canonical_tables = {
16			"main_app_system": """
17			CREATE TABLE main_app_system(
18			id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
19			name TEXT,
20			short_name TEXT,
21			relative_file_path TEXT
22			)
23			""",
24			"main_app": """
25			CREATE TABLE main_app(
26			id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
27			game_name TEXT,
28			game_name_scraped TEXT,
29			container_file_name TEXT,
30			description TEXT,
31			description_scraped TEXT,
32			system_console INTEGER,
33			system_console_scraped TEXT,
34			path_to_screenshot_box TEXT,
35			path_to_screenshot_title TEXT,
36			path_to_screenshot_ingame TEXT,
37			path_to_video TEXT,
38			user_notes TEXT,
39			container_md5_hash TEXT,
40			version TEXT
41			)
42			""",
43			"no_intro_system": """
44			CREATE TABLE no_intro_system(
45			id INTEGER NOT NULL PRIMARY KEY,
46			name TEXT,
47			description TEXT,
48			dat_version TEXT
49			)
50			""",
51			"no_intro_main": """
52			CREATE TABLE no_intro_main(
53			game_name TEXT,
54			no_intro_id TEXT,
55			clone_of_id TEXT,
56			description TEXT,
57			rom_name TEXT,
58			crc TEXT,
59			md5 TEXT,
60			sha1 TEXT,
61			sha256 TEXT,
62			status TEXT,
63			no_intro_system_id INTEGER NOT NULL,
64	nino.borges	978	app_system_id INTEGER,
65	nino.borges	976	FOREIGN KEY(no_intro_system_id) REFERENCES no_intro_system(id)
66			)
67			""",
68			"tosec_main": """
69			CREATE TABLE tosec_main(
70			game_name TEXT,
71			description TEXT,
72			rom_name TEXT,
73			crc TEXT,
74			md5 TEXT,
75			sha1 TEXT,
76	nino.borges	978	system_id INTEGER,
77			app_system_id INTEGER,
78	nino.borges	976	FOREIGN KEY(system_id) REFERENCES no_intro_system(id)
79			)
80			""",
81	nino.borges	978	"dat_import_history": """
82			CREATE TABLE dat_import_history(
83			id INTEGER PRIMARY KEY AUTOINCREMENT,
84			app_system_id INTEGER NOT NULL,
85			source TEXT NOT NULL,
86			dat_name TEXT,
87			dat_description TEXT,
88			dat_version TEXT,
89			imported_at TEXT NOT NULL,
90			entry_count INTEGER NOT NULL DEFAULT 0,
91			UNIQUE(app_system_id, source),
92			FOREIGN KEY(app_system_id) REFERENCES main_app_system(id)
93			)
94			""",
95	nino.borges	976	}
96	nino.borges	795
97	nino.borges	905	def create_database(self):
98	nino.borges	976	"""Creates a fresh Gromulus SQLite database using the canonical schema."""
99	nino.borges	905	if os.path.exists(self.db_path):
100			os.remove(self.db_path)
101	nino.borges	795
102	nino.borges	905	conn = sqlite3.connect(self.db_path)
103			cursor = conn.cursor()
104	nino.borges	795
105	nino.borges	976	for ddl in self._canonical_tables.values():
106			cursor.execute(ddl)
107			self._create_indexes(cursor)
108	nino.borges	805
109	nino.borges	976	conn.commit()
110			conn.close()
111			print("Database created successfully at:", self.db_path)
112
113			def normalize_schema(self):
114			"""
115			Normalizes an existing DB to the canonical schema without dropping data.
116			Adds missing tables/columns and backfills known old column names.
117			"""
118			conn = sqlite3.connect(self.db_path)
119			cursor = conn.cursor()
120
121			for table_name, ddl in self._canonical_tables.items():
122			cursor.execute(
123			"SELECT name FROM sqlite_master WHERE type='table' AND name=?",
124			(table_name,),
125	nino.borges	905	)
126	nino.borges	976	if cursor.fetchone() is None:
127			cursor.execute(ddl)
128	nino.borges	795
129	nino.borges	976	self._ensure_columns(
130			cursor,
131			"main_app",
132			[
133			"game_name", "game_name_scraped", "container_file_name", "description",
134			"description_scraped", "system_console", "system_console_scraped",
135			"path_to_screenshot_box", "path_to_screenshot_title",
136			"path_to_screenshot_ingame", "path_to_video", "user_notes",
137			"container_md5_hash", "version",
138			],
139			)
140			self._ensure_columns(cursor, "main_app_system", ["name", "short_name", "relative_file_path"])
141			self._ensure_columns(cursor, "no_intro_system", ["name", "description", "dat_version"])
142			self._ensure_columns(
143			cursor,
144			"no_intro_main",
145			[
146			"game_name", "no_intro_id", "clone_of_id", "description", "rom_name",
147	nino.borges	978	"crc", "md5", "sha1", "sha256", "status", "no_intro_system_id", "app_system_id",
148	nino.borges	976	],
149			)
150			self._ensure_columns(
151			cursor,
152			"tosec_main",
153	nino.borges	978	["game_name", "description", "rom_name", "crc", "md5", "sha1", "system_id", "app_system_id"],
154	nino.borges	976	)
155	nino.borges	978	self._ensure_columns(
156			cursor,
157			"dat_import_history",
158			["app_system_id", "source", "dat_name", "dat_description", "dat_version", "imported_at", "entry_count"],
159			)
160	nino.borges	976
161			self._backfill_aliases(cursor)
162			self._create_indexes(cursor)
163			conn.commit()
164			conn.close()
165			print("Schema normalized successfully at:", self.db_path)
166
167			def _ensure_columns(self, cursor, table_name, required_columns):
168			cursor.execute(f"PRAGMA table_info({table_name})")
169			existing_columns = {row[1] for row in cursor.fetchall()}
170			for column_name in required_columns:
171			if column_name not in existing_columns:
172			cursor.execute(f"ALTER TABLE {table_name} ADD COLUMN {column_name} TEXT")
173
174			def _column_exists(self, cursor, table_name, column_name):
175			cursor.execute(f"PRAGMA table_info({table_name})")
176			return any(row[1] == column_name for row in cursor.fetchall())
177
178			def _backfill_aliases(self, cursor):
179			# main_app alias mapping
180			if self._column_exists(cursor, "main_app", "file_name"):
181			cursor.execute(
182			"UPDATE main_app SET container_file_name=file_name "
183			"WHERE (container_file_name IS NULL OR container_file_name='') "
184			"AND file_name IS NOT NULL"
185	nino.borges	905	)
186	nino.borges	976	if self._column_exists(cursor, "main_app", "md5"):
187			cursor.execute(
188			"UPDATE main_app SET container_md5_hash=md5 "
189			"WHERE (container_md5_hash IS NULL OR container_md5_hash='') AND md5 IS NOT NULL"
190			)
191			if self._column_exists(cursor, "main_app", "system_id"):
192			cursor.execute(
193			"UPDATE main_app SET system_console=system_id "
194			"WHERE (system_console IS NULL OR system_console='') AND system_id IS NOT NULL"
195			)
196			if self._column_exists(cursor, "main_app", "rom_filename"):
197			cursor.execute(
198			"UPDATE main_app SET container_file_name=rom_filename "
199			"WHERE (container_file_name IS NULL OR container_file_name='') "
200			"AND rom_filename IS NOT NULL"
201			)
202			if self._column_exists(cursor, "main_app", "rom_hash"):
203			cursor.execute(
204			"UPDATE main_app SET container_md5_hash=rom_hash "
205			"WHERE (container_md5_hash IS NULL OR container_md5_hash='') "
206			"AND rom_hash IS NOT NULL"
207			)
208			if self._column_exists(cursor, "main_app", "notes"):
209			cursor.execute(
210			"UPDATE main_app SET user_notes=notes "
211			"WHERE (user_notes IS NULL OR user_notes='') AND notes IS NOT NULL"
212			)
213	nino.borges	806
214	nino.borges	976	# system table alias mapping
215			if self._column_exists(cursor, "no_intro_system", "system_name"):
216			cursor.execute(
217			"UPDATE no_intro_system SET name=system_name "
218			"WHERE (name IS NULL OR name='') AND system_name IS NOT NULL"
219	nino.borges	905	)
220	nino.borges	976	if self._column_exists(cursor, "no_intro_system", "system_description"):
221			cursor.execute(
222			"UPDATE no_intro_system SET description=system_description "
223			"WHERE (description IS NULL OR description='') AND system_description IS NOT NULL"
224			)
225	nino.borges	806
226	nino.borges	976	# no_intro_main alias mapping
227			if self._column_exists(cursor, "no_intro_main", "rom_hash"):
228			cursor.execute(
229			"UPDATE no_intro_main SET md5=rom_hash "
230			"WHERE (md5 IS NULL OR md5='') AND rom_hash IS NOT NULL"
231	nino.borges	905	)
232	nino.borges	976	if self._column_exists(cursor, "no_intro_main", "rom_filename"):
233			cursor.execute(
234			"UPDATE no_intro_main SET rom_name=rom_filename "
235			"WHERE (rom_name IS NULL OR rom_name='') AND rom_filename IS NOT NULL"
236			)
237			if self._column_exists(cursor, "no_intro_main", "system_id"):
238			cursor.execute(
239			"UPDATE no_intro_main SET no_intro_system_id=system_id "
240			"WHERE (no_intro_system_id IS NULL OR no_intro_system_id='') AND system_id IS NOT NULL"
241			)
242	nino.borges	978	if self._column_exists(cursor, "no_intro_main", "app_system_id"):
243			cursor.execute(
244			"""
245			UPDATE no_intro_main
246			SET app_system_id = (
247			SELECT ma.system_console
248			FROM main_app_file_hash fh
249			JOIN main_app ma ON ma.id = fh.container_file_id
250			WHERE lower(fh.file_md5_hash) = lower(no_intro_main.md5)
251			GROUP BY ma.system_console
252			ORDER BY COUNT(*) DESC
253			LIMIT 1
254			)
255			WHERE app_system_id IS NULL
256			"""
257			)
258	nino.borges	806
259	nino.borges	976	# tosec_main alias mapping
260			if self._column_exists(cursor, "tosec_main", "rom_hash"):
261			cursor.execute(
262			"UPDATE tosec_main SET md5=rom_hash "
263			"WHERE (md5 IS NULL OR md5='') AND rom_hash IS NOT NULL"
264	nino.borges	905	)
265	nino.borges	976	if self._column_exists(cursor, "tosec_main", "rom_filename"):
266			cursor.execute(
267			"UPDATE tosec_main SET rom_name=rom_filename "
268			"WHERE (rom_name IS NULL OR rom_name='') AND rom_filename IS NOT NULL"
269			)
270	nino.borges	978	if self._column_exists(cursor, "tosec_main", "app_system_id"):
271			cursor.execute(
272			"""
273			UPDATE tosec_main
274			SET app_system_id = (
275			SELECT ma.system_console
276			FROM main_app_file_hash fh
277			JOIN main_app ma ON ma.id = fh.container_file_id
278			WHERE lower(fh.file_md5_hash) = lower(tosec_main.md5)
279			GROUP BY ma.system_console
280			ORDER BY COUNT(*) DESC
281			LIMIT 1
282			)
283			WHERE app_system_id IS NULL
284			"""
285			)
286	nino.borges	806
287	nino.borges	976	def _create_indexes(self, cursor):
288			cursor.execute("CREATE INDEX IF NOT EXISTS idx_main_app_md5 ON main_app(container_md5_hash)")
289			cursor.execute("CREATE INDEX IF NOT EXISTS idx_main_app_system ON main_app(system_console)")
290			cursor.execute("CREATE INDEX IF NOT EXISTS idx_no_intro_md5 ON no_intro_main(md5)")
291			cursor.execute("CREATE INDEX IF NOT EXISTS idx_tosec_md5 ON tosec_main(md5)")
292	nino.borges	978	cursor.execute("CREATE INDEX IF NOT EXISTS idx_no_intro_system_md5 ON no_intro_main(app_system_id, md5)")
293			cursor.execute("CREATE INDEX IF NOT EXISTS idx_tosec_system_md5 ON tosec_main(app_system_id, md5)")
294			cursor.execute("CREATE INDEX IF NOT EXISTS idx_dat_import_system_source ON dat_import_history(app_system_id, source)")
295	nino.borges	905
296			def backup_database(self, backup_path):
297			"""Copies the current database to a new location."""
298			if not os.path.exists(self.db_path):
299			raise FileNotFoundError("Database file not found.")
300			shutil.copy2(self.db_path, backup_path)
301			print(f"Database backed up to: {backup_path}")
302
303			def export_data_to_json(self, export_path):
304			"""Exports all table data to a JSON file."""
305			conn = sqlite3.connect(self.db_path)
306			cursor = conn.cursor()
307
308			data = {}
309			for table in [
310			"main_app_system", "main_app",
311			"no_intro_system", "no_intro_main",
312	nino.borges	978	"tosec_main", "dat_import_history"
313	nino.borges	905	]:
314	nino.borges	976	cursor.execute(
315			"SELECT name FROM sqlite_master WHERE type='table' AND name=?",
316			(table,),
317			)
318			if cursor.fetchone() is None:
319			continue
320	nino.borges	905	cursor.execute(f"SELECT * FROM {table}")
321			columns = [description[0] for description in cursor.description]
322			rows = cursor.fetchall()
323			data[table] = [dict(zip(columns, row)) for row in rows]
324
325			with open(export_path, "w", encoding="utf-8") as f:
326			json.dump(data, f, indent=2)
327
328			conn.close()
329			print(f"Exported data to JSON: {export_path}")
330
331			def import_data_from_json(self, import_path):
332			"""Imports table data from a JSON file (assumes schema is already created)."""
333			if not os.path.exists(import_path):
334			raise FileNotFoundError("Import file not found.")
335
336			with open(import_path, "r", encoding="utf-8") as f:
337			data = json.load(f)
338
339			conn = sqlite3.connect(self.db_path)
340			cursor = conn.cursor()
341
342			for table, rows in data.items():
343			if not rows:
344			continue
345			columns = rows[0].keys()
346			placeholders = ", ".join(["?"] * len(columns))
347			col_list = ", ".join(columns)
348
349			for row in rows:
350			values = tuple(row[col] for col in columns)
351			cursor.execute(f"INSERT INTO {table} ({col_list}) VALUES ({placeholders})", values)
352
353			conn.commit()
354			conn.close()
355			print(f"Imported data from JSON: {import_path}")