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,
                    favorite_game INTEGER,
                    release_date TEXT,
                    release_date_scraped TEXT,
                    game_genre TEXT,
                    game_genre_scraped TEXT,
                    cooperative TEXT,
                    cooperative_scraped TEXT,
                    max_players TEXT,
                    max_players_scraped 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", "favorite_game", "release_date",
                "release_date_scraped", "game_genre", "game_genre_scraped",
                "cooperative", "cooperative_scraped", "max_players", "max_players_scraped",
            ],
        )
        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:	991
Committed:	Mon Mar 9 21:52:02 2026 UTC (2 weeks, 4 days ago) by nino.borges
Content type:	text/x-python
File size:	15410 byte(s)
Log Message:	I meant to have this as the commit message for 1.5 but I had an issue wiht the message. so I added some spaces to the files to force another commit. Gromulus v1.5: user metadata save workflow, schema expansion, and UI refactor - Added user-editable game metadata workflow with explicit Save button (no autosave). - Added dirty-state tracking and Save/Discard/Cancel prompts when navigating away or closing with unsaved changes. - Added DB schema support for new metadata fields: - favorite_game - release_date, release_date_scraped - game_genre, game_genre_scraped - cooperative, cooperative_scraped - max_players, max_players_scraped - Wired UI to user-first metadata precedence with _scraped fallback for release date, genre, cooperative, max players, and description. - Added release date display/storage conversion: - GUI display MM-DD-YYYY - DB storage YYYY-MM-DD - Refactored main game info panel: - moved hash/file/No-Intro/TOSEC detail fields into Reports -> Game Properties modal - added compact TOSEC/NoIntro match indicators - added Favorite, Release Date, Genre, Cooperative, Max Players, Description, and User Notes controls - Enhanced artwork previews from prior update: - larger preview boxes - aspect-ratio-preserving scaling - click-to-open full-size modal viewer - Updated schema/documentation files to stay aligned: - models.py - Database Dictonary.md - canonical DB utility schema/migration logic
#	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	nino.borges	990	version TEXT,
41			favorite_game INTEGER,
42			release_date TEXT,
43			release_date_scraped TEXT,
44			game_genre TEXT,
45			game_genre_scraped TEXT,
46			cooperative TEXT,
47			cooperative_scraped TEXT,
48			max_players TEXT,
49			max_players_scraped TEXT
50	nino.borges	976	)
51			""",
52			"no_intro_system": """
53			CREATE TABLE no_intro_system(
54			id INTEGER NOT NULL PRIMARY KEY,
55			name TEXT,
56			description TEXT,
57			dat_version TEXT
58			)
59			""",
60			"no_intro_main": """
61			CREATE TABLE no_intro_main(
62			game_name TEXT,
63			no_intro_id TEXT,
64			clone_of_id TEXT,
65			description TEXT,
66			rom_name TEXT,
67			crc TEXT,
68			md5 TEXT,
69			sha1 TEXT,
70			sha256 TEXT,
71			status TEXT,
72			no_intro_system_id INTEGER NOT NULL,
73	nino.borges	978	app_system_id INTEGER,
74	nino.borges	976	FOREIGN KEY(no_intro_system_id) REFERENCES no_intro_system(id)
75			)
76			""",
77			"tosec_main": """
78			CREATE TABLE tosec_main(
79			game_name TEXT,
80			description TEXT,
81			rom_name TEXT,
82			crc TEXT,
83			md5 TEXT,
84			sha1 TEXT,
85	nino.borges	978	system_id INTEGER,
86			app_system_id INTEGER,
87	nino.borges	976	FOREIGN KEY(system_id) REFERENCES no_intro_system(id)
88			)
89			""",
90	nino.borges	978	"dat_import_history": """
91			CREATE TABLE dat_import_history(
92			id INTEGER PRIMARY KEY AUTOINCREMENT,
93			app_system_id INTEGER NOT NULL,
94			source TEXT NOT NULL,
95			dat_name TEXT,
96			dat_description TEXT,
97			dat_version TEXT,
98			imported_at TEXT NOT NULL,
99			entry_count INTEGER NOT NULL DEFAULT 0,
100			UNIQUE(app_system_id, source),
101			FOREIGN KEY(app_system_id) REFERENCES main_app_system(id)
102			)
103			""",
104	nino.borges	976	}
105	nino.borges	795
106	nino.borges	905	def create_database(self):
107	nino.borges	976	"""Creates a fresh Gromulus SQLite database using the canonical schema."""
108	nino.borges	905	if os.path.exists(self.db_path):
109			os.remove(self.db_path)
110	nino.borges	795
111	nino.borges	905	conn = sqlite3.connect(self.db_path)
112			cursor = conn.cursor()
113	nino.borges	795
114	nino.borges	976	for ddl in self._canonical_tables.values():
115			cursor.execute(ddl)
116			self._create_indexes(cursor)
117	nino.borges	805
118	nino.borges	976	conn.commit()
119			conn.close()
120			print("Database created successfully at:", self.db_path)
121
122			def normalize_schema(self):
123			"""
124			Normalizes an existing DB to the canonical schema without dropping data.
125			Adds missing tables/columns and backfills known old column names.
126			"""
127			conn = sqlite3.connect(self.db_path)
128			cursor = conn.cursor()
129
130			for table_name, ddl in self._canonical_tables.items():
131			cursor.execute(
132			"SELECT name FROM sqlite_master WHERE type='table' AND name=?",
133			(table_name,),
134	nino.borges	905	)
135	nino.borges	976	if cursor.fetchone() is None:
136			cursor.execute(ddl)
137	nino.borges	795
138	nino.borges	976	self._ensure_columns(
139			cursor,
140			"main_app",
141			[
142			"game_name", "game_name_scraped", "container_file_name", "description",
143			"description_scraped", "system_console", "system_console_scraped",
144			"path_to_screenshot_box", "path_to_screenshot_title",
145			"path_to_screenshot_ingame", "path_to_video", "user_notes",
146	nino.borges	990	"container_md5_hash", "version", "favorite_game", "release_date",
147			"release_date_scraped", "game_genre", "game_genre_scraped",
148			"cooperative", "cooperative_scraped", "max_players", "max_players_scraped",
149	nino.borges	976	],
150			)
151			self._ensure_columns(cursor, "main_app_system", ["name", "short_name", "relative_file_path"])
152			self._ensure_columns(cursor, "no_intro_system", ["name", "description", "dat_version"])
153			self._ensure_columns(
154			cursor,
155			"no_intro_main",
156			[
157			"game_name", "no_intro_id", "clone_of_id", "description", "rom_name",
158	nino.borges	978	"crc", "md5", "sha1", "sha256", "status", "no_intro_system_id", "app_system_id",
159	nino.borges	976	],
160			)
161			self._ensure_columns(
162			cursor,
163			"tosec_main",
164	nino.borges	978	["game_name", "description", "rom_name", "crc", "md5", "sha1", "system_id", "app_system_id"],
165	nino.borges	976	)
166	nino.borges	978	self._ensure_columns(
167			cursor,
168			"dat_import_history",
169			["app_system_id", "source", "dat_name", "dat_description", "dat_version", "imported_at", "entry_count"],
170			)
171	nino.borges	976
172			self._backfill_aliases(cursor)
173			self._create_indexes(cursor)
174			conn.commit()
175			conn.close()
176			print("Schema normalized successfully at:", self.db_path)
177
178			def _ensure_columns(self, cursor, table_name, required_columns):
179			cursor.execute(f"PRAGMA table_info({table_name})")
180			existing_columns = {row[1] for row in cursor.fetchall()}
181			for column_name in required_columns:
182			if column_name not in existing_columns:
183			cursor.execute(f"ALTER TABLE {table_name} ADD COLUMN {column_name} TEXT")
184
185			def _column_exists(self, cursor, table_name, column_name):
186			cursor.execute(f"PRAGMA table_info({table_name})")
187			return any(row[1] == column_name for row in cursor.fetchall())
188
189			def _backfill_aliases(self, cursor):
190			# main_app alias mapping
191			if self._column_exists(cursor, "main_app", "file_name"):
192			cursor.execute(
193			"UPDATE main_app SET container_file_name=file_name "
194			"WHERE (container_file_name IS NULL OR container_file_name='') "
195			"AND file_name IS NOT NULL"
196	nino.borges	905	)
197	nino.borges	976	if self._column_exists(cursor, "main_app", "md5"):
198			cursor.execute(
199			"UPDATE main_app SET container_md5_hash=md5 "
200			"WHERE (container_md5_hash IS NULL OR container_md5_hash='') AND md5 IS NOT NULL"
201			)
202			if self._column_exists(cursor, "main_app", "system_id"):
203			cursor.execute(
204			"UPDATE main_app SET system_console=system_id "
205			"WHERE (system_console IS NULL OR system_console='') AND system_id IS NOT NULL"
206			)
207			if self._column_exists(cursor, "main_app", "rom_filename"):
208			cursor.execute(
209			"UPDATE main_app SET container_file_name=rom_filename "
210			"WHERE (container_file_name IS NULL OR container_file_name='') "
211			"AND rom_filename IS NOT NULL"
212			)
213			if self._column_exists(cursor, "main_app", "rom_hash"):
214			cursor.execute(
215			"UPDATE main_app SET container_md5_hash=rom_hash "
216			"WHERE (container_md5_hash IS NULL OR container_md5_hash='') "
217			"AND rom_hash IS NOT NULL"
218			)
219			if self._column_exists(cursor, "main_app", "notes"):
220			cursor.execute(
221			"UPDATE main_app SET user_notes=notes "
222			"WHERE (user_notes IS NULL OR user_notes='') AND notes IS NOT NULL"
223			)
224	nino.borges	806
225	nino.borges	976	# system table alias mapping
226			if self._column_exists(cursor, "no_intro_system", "system_name"):
227			cursor.execute(
228			"UPDATE no_intro_system SET name=system_name "
229			"WHERE (name IS NULL OR name='') AND system_name IS NOT NULL"
230	nino.borges	905	)
231	nino.borges	976	if self._column_exists(cursor, "no_intro_system", "system_description"):
232			cursor.execute(
233			"UPDATE no_intro_system SET description=system_description "
234			"WHERE (description IS NULL OR description='') AND system_description IS NOT NULL"
235			)
236	nino.borges	806
237	nino.borges	976	# no_intro_main alias mapping
238			if self._column_exists(cursor, "no_intro_main", "rom_hash"):
239			cursor.execute(
240			"UPDATE no_intro_main SET md5=rom_hash "
241			"WHERE (md5 IS NULL OR md5='') AND rom_hash IS NOT NULL"
242	nino.borges	905	)
243	nino.borges	976	if self._column_exists(cursor, "no_intro_main", "rom_filename"):
244			cursor.execute(
245			"UPDATE no_intro_main SET rom_name=rom_filename "
246			"WHERE (rom_name IS NULL OR rom_name='') AND rom_filename IS NOT NULL"
247			)
248			if self._column_exists(cursor, "no_intro_main", "system_id"):
249			cursor.execute(
250			"UPDATE no_intro_main SET no_intro_system_id=system_id "
251			"WHERE (no_intro_system_id IS NULL OR no_intro_system_id='') AND system_id IS NOT NULL"
252			)
253	nino.borges	978	if self._column_exists(cursor, "no_intro_main", "app_system_id"):
254			cursor.execute(
255			"""
256			UPDATE no_intro_main
257			SET app_system_id = (
258			SELECT ma.system_console
259			FROM main_app_file_hash fh
260			JOIN main_app ma ON ma.id = fh.container_file_id
261			WHERE lower(fh.file_md5_hash) = lower(no_intro_main.md5)
262			GROUP BY ma.system_console
263			ORDER BY COUNT(*) DESC
264			LIMIT 1
265			)
266			WHERE app_system_id IS NULL
267			"""
268			)
269	nino.borges	806
270	nino.borges	991
271	nino.borges	976	# tosec_main alias mapping
272			if self._column_exists(cursor, "tosec_main", "rom_hash"):
273			cursor.execute(
274			"UPDATE tosec_main SET md5=rom_hash "
275			"WHERE (md5 IS NULL OR md5='') AND rom_hash IS NOT NULL"
276	nino.borges	905	)
277	nino.borges	976	if self._column_exists(cursor, "tosec_main", "rom_filename"):
278			cursor.execute(
279			"UPDATE tosec_main SET rom_name=rom_filename "
280			"WHERE (rom_name IS NULL OR rom_name='') AND rom_filename IS NOT NULL"
281			)
282	nino.borges	978	if self._column_exists(cursor, "tosec_main", "app_system_id"):
283			cursor.execute(
284			"""
285			UPDATE tosec_main
286			SET app_system_id = (
287			SELECT ma.system_console
288			FROM main_app_file_hash fh
289			JOIN main_app ma ON ma.id = fh.container_file_id
290			WHERE lower(fh.file_md5_hash) = lower(tosec_main.md5)
291			GROUP BY ma.system_console
292			ORDER BY COUNT(*) DESC
293			LIMIT 1
294			)
295			WHERE app_system_id IS NULL
296			"""
297			)
298	nino.borges	806
299	nino.borges	976	def _create_indexes(self, cursor):
300			cursor.execute("CREATE INDEX IF NOT EXISTS idx_main_app_md5 ON main_app(container_md5_hash)")
301			cursor.execute("CREATE INDEX IF NOT EXISTS idx_main_app_system ON main_app(system_console)")
302			cursor.execute("CREATE INDEX IF NOT EXISTS idx_no_intro_md5 ON no_intro_main(md5)")
303			cursor.execute("CREATE INDEX IF NOT EXISTS idx_tosec_md5 ON tosec_main(md5)")
304	nino.borges	978	cursor.execute("CREATE INDEX IF NOT EXISTS idx_no_intro_system_md5 ON no_intro_main(app_system_id, md5)")
305			cursor.execute("CREATE INDEX IF NOT EXISTS idx_tosec_system_md5 ON tosec_main(app_system_id, md5)")
306			cursor.execute("CREATE INDEX IF NOT EXISTS idx_dat_import_system_source ON dat_import_history(app_system_id, source)")
307	nino.borges	905
308			def backup_database(self, backup_path):
309			"""Copies the current database to a new location."""
310			if not os.path.exists(self.db_path):
311			raise FileNotFoundError("Database file not found.")
312			shutil.copy2(self.db_path, backup_path)
313			print(f"Database backed up to: {backup_path}")
314
315			def export_data_to_json(self, export_path):
316			"""Exports all table data to a JSON file."""
317			conn = sqlite3.connect(self.db_path)
318			cursor = conn.cursor()
319
320			data = {}
321			for table in [
322			"main_app_system", "main_app",
323			"no_intro_system", "no_intro_main",
324	nino.borges	978	"tosec_main", "dat_import_history"
325	nino.borges	905	]:
326	nino.borges	976	cursor.execute(
327			"SELECT name FROM sqlite_master WHERE type='table' AND name=?",
328			(table,),
329			)
330			if cursor.fetchone() is None:
331			continue
332	nino.borges	905	cursor.execute(f"SELECT * FROM {table}")
333			columns = [description[0] for description in cursor.description]
334			rows = cursor.fetchall()
335			data[table] = [dict(zip(columns, row)) for row in rows]
336
337			with open(export_path, "w", encoding="utf-8") as f:
338			json.dump(data, f, indent=2)
339
340			conn.close()
341			print(f"Exported data to JSON: {export_path}")
342
343			def import_data_from_json(self, import_path):
344			"""Imports table data from a JSON file (assumes schema is already created)."""
345			if not os.path.exists(import_path):
346			raise FileNotFoundError("Import file not found.")
347
348			with open(import_path, "r", encoding="utf-8") as f:
349			data = json.load(f)
350
351			conn = sqlite3.connect(self.db_path)
352			cursor = conn.cursor()
353
354			for table, rows in data.items():
355			if not rows:
356			continue
357			columns = rows[0].keys()
358			placeholders = ", ".join(["?"] * len(columns))
359			col_list = ", ".join(columns)
360
361			for row in rows:
362			values = tuple(row[col] for col in columns)
363			cursor.execute(f"INSERT INTO {table} ({col_list}) VALUES ({placeholders})", values)
364
365			conn.commit()
366			conn.close()
367			print(f"Imported data from JSON: {import_path}")