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.
#	Content
1	"""
2	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	"""
5
6	import sqlite3
7	import os
8	import shutil
9	import json
10
11
12	class GromulusDatabaseUtilities:
13	def __init__(self, db_path: str):
14	self.db_path = db_path
15	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	app_system_id INTEGER,
65	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	system_id INTEGER,
77	app_system_id INTEGER,
78	FOREIGN KEY(system_id) REFERENCES no_intro_system(id)
79	)
80	""",
81	"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	}
96
97	def create_database(self):
98	"""Creates a fresh Gromulus SQLite database using the canonical schema."""
99	if os.path.exists(self.db_path):
100	os.remove(self.db_path)
101
102	conn = sqlite3.connect(self.db_path)
103	cursor = conn.cursor()
104
105	for ddl in self._canonical_tables.values():
106	cursor.execute(ddl)
107	self._create_indexes(cursor)
108
109	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	)
126	if cursor.fetchone() is None:
127	cursor.execute(ddl)
128
129	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	"crc", "md5", "sha1", "sha256", "status", "no_intro_system_id", "app_system_id",
148	],
149	)
150	self._ensure_columns(
151	cursor,
152	"tosec_main",
153	["game_name", "description", "rom_name", "crc", "md5", "sha1", "system_id", "app_system_id"],
154	)
155	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
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	)
186	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
214	# 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	)
220	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
226	# 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	)
232	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	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
259	# 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	)
265	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	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
287	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	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
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	"tosec_main", "dat_import_history"
313	]:
314	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	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}")