ViewVC Help

View File | Revision Log | Show Annotations | View Changeset | Root Listing

root/ns_dev/Python/NinoCode/Active_prgs/Gromulus/GromulusDatabaseUtilities.py

Comparing Python/NinoCode/Active_prgs/Gromulus/GromulusDatabaseUtilities.py (file contents):
Revision 905 by nino.borges, Fri Jun 6 22:14:57 2025 UTC vs.
Revision 976 by nino.borges, Fri Feb 13 02:00:16 2026 UTC

#	Line 12 | Line 12 | import json
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	+	FOREIGN KEY(no_intro_system_id) REFERENCES no_intro_system(id)
65	+	)
66	+	""",
67	+	"tosec_main": """
68	+	CREATE TABLE tosec_main(
69	+	game_name TEXT,
70	+	description TEXT,
71	+	rom_name TEXT,
72	+	crc TEXT,
73	+	md5 TEXT,
74	+	sha1 TEXT,
75	+	system_id INTEGER NOT NULL,
76	+	FOREIGN KEY(system_id) REFERENCES no_intro_system(id)
77	+	)
78	+	""",
79	+	}
80
81		def create_database(self):
82	<	"""Creates the Gromulus SQLite database with all required tables."""
82	>	"""Creates a fresh Gromulus SQLite database using the canonical schema."""
83		if os.path.exists(self.db_path):
84		os.remove(self.db_path)
85
86		conn = sqlite3.connect(self.db_path)
87		cursor = conn.cursor()
88
89	<	cursor.execute("""
90	<	CREATE TABLE main_app_system (
91	<	id INTEGER PRIMARY KEY AUTOINCREMENT,
27	<	system_name TEXT,
28	<	system_description TEXT
29	<	)
30	<	""")
31	<
32	<	cursor.execute("""
33	<	CREATE TABLE main_app (
34	<	id INTEGER PRIMARY KEY AUTOINCREMENT,
35	<	rom_filename TEXT,
36	<	rom_hash TEXT,
37	<	rom_filepath TEXT,
38	<	system_id INTEGER,
39	<	is_duplicate INTEGER,
40	<	is_missing_metadata INTEGER,
41	<	notes TEXT,
42	<	FOREIGN KEY(system_id) REFERENCES main_app_system(id)
43	<	)
44	<	""")
45	<
46	<	cursor.execute("""
47	<	CREATE TABLE no_intro_system (
48	<	id INTEGER PRIMARY KEY AUTOINCREMENT,
49	<	system_name TEXT,
50	<	system_description TEXT
51	<	)
52	<	""")
53	<
54	<	cursor.execute("""
55	<	CREATE TABLE no_intro_main (
56	<	id INTEGER PRIMARY KEY AUTOINCREMENT,
57	<	game_name TEXT,
58	<	rom_hash TEXT,
59	<	rom_filename TEXT,
60	<	rom_size INTEGER,
61	<	system_id INTEGER,
62	<	FOREIGN KEY(system_id) REFERENCES no_intro_system(id)
63	<	)
64	<	""")
65	<
66	<	cursor.execute("""
67	<	CREATE TABLE tosec_system (
68	<	id INTEGER PRIMARY KEY AUTOINCREMENT,
69	<	system_name TEXT,
70	<	system_description TEXT
71	<	)
72	<	""")
73	<
74	<	cursor.execute("""
75	<	CREATE TABLE tosec_main (
76	<	id INTEGER PRIMARY KEY AUTOINCREMENT,
77	<	game_name TEXT,
78	<	rom_hash TEXT,
79	<	rom_filename TEXT,
80	<	rom_size INTEGER,
81	<	system_id INTEGER,
82	<	FOREIGN KEY(system_id) REFERENCES tosec_system(id)
83	<	)
84	<	""")
89	>	for ddl in self._canonical_tables.values():
90	>	cursor.execute(ddl)
91	>	self._create_indexes(cursor)
92
93		conn.commit()
94		conn.close()
95		print("Database created successfully at:", self.db_path)
96
97	+	def normalize_schema(self):
98	+	"""
99	+	Normalizes an existing DB to the canonical schema without dropping data.
100	+	Adds missing tables/columns and backfills known old column names.
101	+	"""
102	+	conn = sqlite3.connect(self.db_path)
103	+	cursor = conn.cursor()
104	+
105	+	for table_name, ddl in self._canonical_tables.items():
106	+	cursor.execute(
107	+	"SELECT name FROM sqlite_master WHERE type='table' AND name=?",
108	+	(table_name,),
109	+	)
110	+	if cursor.fetchone() is None:
111	+	cursor.execute(ddl)
112	+
113	+	self._ensure_columns(
114	+	cursor,
115	+	"main_app",
116	+	[
117	+	"game_name", "game_name_scraped", "container_file_name", "description",
118	+	"description_scraped", "system_console", "system_console_scraped",
119	+	"path_to_screenshot_box", "path_to_screenshot_title",
120	+	"path_to_screenshot_ingame", "path_to_video", "user_notes",
121	+	"container_md5_hash", "version",
122	+	],
123	+	)
124	+	self._ensure_columns(cursor, "main_app_system", ["name", "short_name", "relative_file_path"])
125	+	self._ensure_columns(cursor, "no_intro_system", ["name", "description", "dat_version"])
126	+	self._ensure_columns(
127	+	cursor,
128	+	"no_intro_main",
129	+	[
130	+	"game_name", "no_intro_id", "clone_of_id", "description", "rom_name",
131	+	"crc", "md5", "sha1", "sha256", "status", "no_intro_system_id",
132	+	],
133	+	)
134	+	self._ensure_columns(
135	+	cursor,
136	+	"tosec_main",
137	+	["game_name", "description", "rom_name", "crc", "md5", "sha1", "system_id"],
138	+	)
139	+
140	+	self._backfill_aliases(cursor)
141	+	self._create_indexes(cursor)
142	+	conn.commit()
143	+	conn.close()
144	+	print("Schema normalized successfully at:", self.db_path)
145	+
146	+	def _ensure_columns(self, cursor, table_name, required_columns):
147	+	cursor.execute(f"PRAGMA table_info({table_name})")
148	+	existing_columns = {row[1] for row in cursor.fetchall()}
149	+	for column_name in required_columns:
150	+	if column_name not in existing_columns:
151	+	cursor.execute(f"ALTER TABLE {table_name} ADD COLUMN {column_name} TEXT")
152	+
153	+	def _column_exists(self, cursor, table_name, column_name):
154	+	cursor.execute(f"PRAGMA table_info({table_name})")
155	+	return any(row[1] == column_name for row in cursor.fetchall())
156	+
157	+	def _backfill_aliases(self, cursor):
158	+	# main_app alias mapping
159	+	if self._column_exists(cursor, "main_app", "file_name"):
160	+	cursor.execute(
161	+	"UPDATE main_app SET container_file_name=file_name "
162	+	"WHERE (container_file_name IS NULL OR container_file_name='') "
163	+	"AND file_name IS NOT NULL"
164	+	)
165	+	if self._column_exists(cursor, "main_app", "md5"):
166	+	cursor.execute(
167	+	"UPDATE main_app SET container_md5_hash=md5 "
168	+	"WHERE (container_md5_hash IS NULL OR container_md5_hash='') AND md5 IS NOT NULL"
169	+	)
170	+	if self._column_exists(cursor, "main_app", "system_id"):
171	+	cursor.execute(
172	+	"UPDATE main_app SET system_console=system_id "
173	+	"WHERE (system_console IS NULL OR system_console='') AND system_id IS NOT NULL"
174	+	)
175	+	if self._column_exists(cursor, "main_app", "rom_filename"):
176	+	cursor.execute(
177	+	"UPDATE main_app SET container_file_name=rom_filename "
178	+	"WHERE (container_file_name IS NULL OR container_file_name='') "
179	+	"AND rom_filename IS NOT NULL"
180	+	)
181	+	if self._column_exists(cursor, "main_app", "rom_hash"):
182	+	cursor.execute(
183	+	"UPDATE main_app SET container_md5_hash=rom_hash "
184	+	"WHERE (container_md5_hash IS NULL OR container_md5_hash='') "
185	+	"AND rom_hash IS NOT NULL"
186	+	)
187	+	if self._column_exists(cursor, "main_app", "notes"):
188	+	cursor.execute(
189	+	"UPDATE main_app SET user_notes=notes "
190	+	"WHERE (user_notes IS NULL OR user_notes='') AND notes IS NOT NULL"
191	+	)
192	+
193	+	# system table alias mapping
194	+	if self._column_exists(cursor, "no_intro_system", "system_name"):
195	+	cursor.execute(
196	+	"UPDATE no_intro_system SET name=system_name "
197	+	"WHERE (name IS NULL OR name='') AND system_name IS NOT NULL"
198	+	)
199	+	if self._column_exists(cursor, "no_intro_system", "system_description"):
200	+	cursor.execute(
201	+	"UPDATE no_intro_system SET description=system_description "
202	+	"WHERE (description IS NULL OR description='') AND system_description IS NOT NULL"
203	+	)
204	+
205	+	# no_intro_main alias mapping
206	+	if self._column_exists(cursor, "no_intro_main", "rom_hash"):
207	+	cursor.execute(
208	+	"UPDATE no_intro_main SET md5=rom_hash "
209	+	"WHERE (md5 IS NULL OR md5='') AND rom_hash IS NOT NULL"
210	+	)
211	+	if self._column_exists(cursor, "no_intro_main", "rom_filename"):
212	+	cursor.execute(
213	+	"UPDATE no_intro_main SET rom_name=rom_filename "
214	+	"WHERE (rom_name IS NULL OR rom_name='') AND rom_filename IS NOT NULL"
215	+	)
216	+	if self._column_exists(cursor, "no_intro_main", "system_id"):
217	+	cursor.execute(
218	+	"UPDATE no_intro_main SET no_intro_system_id=system_id "
219	+	"WHERE (no_intro_system_id IS NULL OR no_intro_system_id='') AND system_id IS NOT NULL"
220	+	)
221	+
222	+	# tosec_main alias mapping
223	+	if self._column_exists(cursor, "tosec_main", "rom_hash"):
224	+	cursor.execute(
225	+	"UPDATE tosec_main SET md5=rom_hash "
226	+	"WHERE (md5 IS NULL OR md5='') AND rom_hash IS NOT NULL"
227	+	)
228	+	if self._column_exists(cursor, "tosec_main", "rom_filename"):
229	+	cursor.execute(
230	+	"UPDATE tosec_main SET rom_name=rom_filename "
231	+	"WHERE (rom_name IS NULL OR rom_name='') AND rom_filename IS NOT NULL"
232	+	)
233	+
234	+	def _create_indexes(self, cursor):
235	+	cursor.execute("CREATE INDEX IF NOT EXISTS idx_main_app_md5 ON main_app(container_md5_hash)")
236	+	cursor.execute("CREATE INDEX IF NOT EXISTS idx_main_app_system ON main_app(system_console)")
237	+	cursor.execute("CREATE INDEX IF NOT EXISTS idx_no_intro_md5 ON no_intro_main(md5)")
238	+	cursor.execute("CREATE INDEX IF NOT EXISTS idx_tosec_md5 ON tosec_main(md5)")
239	+
240		def backup_database(self, backup_path):
241		"""Copies the current database to a new location."""
242		if not os.path.exists(self.db_path):
#	Line 103 | Line 253 | class GromulusDatabaseUtilities:
253		for table in [
254		"main_app_system", "main_app",
255		"no_intro_system", "no_intro_main",
256	<	"tosec_system", "tosec_main"
256	>	"tosec_main"
257		]:
258	+	cursor.execute(
259	+	"SELECT name FROM sqlite_master WHERE type='table' AND name=?",
260	+	(table,),
261	+	)
262	+	if cursor.fetchone() is None:
263	+	continue
264		cursor.execute(f"SELECT * FROM {table}")
265		columns = [description[0] for description in cursor.description]
266		rows = cursor.fetchall()

Diff Legend

–	Removed lines
+	Added lines
<	Changed lines (old)
>	Changed lines (new)