Active_prgs/Gromulus/Gromulus_Lib.py

"""
This creates the main library of methods for Gromulus.

"""

import sqlite3, configparser, shutil
import xml.etree.ElementTree as ET
import Tool_Box.NinoGenTools, os


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

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


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

    def get_connection(self):
        return self.conn

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


def ImportNewNoIntroDat(datPath, conn):
    """Deletes all of the data in the two No_Intro tables and load the info from the new dat."""
    tree = ET.parse(datPath)
    root = tree.getroot()

    ##  First gather information from header
    for elm in root.findall('header'):
        headerID = elm.find('id').text
        headerName = elm.find('name').text
        headerDesc = elm.find('description').text
        headerVer = elm.find('version').text
    
    #print(headerID)
    #print (headerName)
    #print(headerDesc)
    #print(headerVer)
    conn.execute("""DELETE FROM no_intro_system""" )
    conn.execute(f'INSERT INTO no_intro_system VALUES ({headerID}, "{headerName}", "{headerDesc}", "{headerVer}")')
    
    conn.execute("""DELETE FROM no_intro_main""" )
    for elm in root.findall('game'):
        gameName = elm.get('name')
        gameID = elm.get('id')
        gameCloneOfID = elm.get('cloneofid')
        gameDesc = elm.find('description').text

        romName = elm.find('rom').get('name')
        romSize = elm.find('rom').get('size')
        romCRC = elm.find('rom').get('crc')
        romMD5 = elm.find('rom').get('md5')
        romSHA1 = elm.find('rom').get('sha1')
        romSHA256 = elm.find('rom').get('sha256')
        romStatus = elm.find('rom').get('status')
        conn.execute(f'INSERT INTO no_intro_main VALUES ("{gameName}", "{gameID}", "{gameCloneOfID}", "{gameDesc}", "{romName}", "{romCRC}", "{romMD5}", "{romSHA1}", "{romSHA256}", "{romStatus}", {headerID})')

    conn.commit()
    #conn.close()


def ImportNewTosecDat(datPath, conn):
    """Imports a TOSEC DAT file and populates the tosec_main and tosec_system tables."""
    cursor = conn.cursor()
    tree = ET.parse(datPath)
    root = tree.getroot()

    # Extract header info
    header = root.find("header")
    system_name = header.findtext("name")
    system_description = header.findtext("description")

    # Replace system info
    cursor.execute("DELETE FROM tosec_system")
    cursor.execute("INSERT INTO tosec_system (system_name, system_description) VALUES (?, ?)",
                   (system_name, system_description))
    system_id = cursor.execute("SELECT id FROM tosec_system WHERE system_name = ?", (system_name,)).fetchone()[0]

    # Replace game entries
    cursor.execute("DELETE FROM tosec_main")
    for game in root.findall("game"):
        game_name = game.attrib.get("name", "")
        rom = game.find("rom")
        if rom is None:
            continue
        rom_filename = rom.attrib.get("name", "")
        rom_size = int(rom.attrib.get("size", "0"))
        rom_md5 = rom.attrib.get("md5", "")

        cursor.execute("""
            INSERT INTO tosec_main (game_name, rom_hash, rom_filename, rom_size, system_id)
            VALUES (?, ?, ?, ?, ?)""",
            (game_name, rom_md5, rom_filename, rom_size, system_id)
        )

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


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

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

    sysResult = conn.execute(f'SELECT relative_file_path FROM main_app_system WHERE id={systemID}')
    for s in sysResult:
        systemRomPath = s[0]
    fullRomPath = os.path.join(config.get('RootConfig','softwareBackupStartDir'),systemRomPath)
    
    res = conn.execute('SELECT md5 FROM main_app')
    currentHashList = []
    for h in res:
        #print(h[0])
        currentHashList.append(h[0])
    #print(currentHashList)
    print(len(currentHashList))

    hshr = Tool_Box.NinoGenTools.HashFileContents('md5')
    for testFile in os.listdir(pathToRoms):
        print(f"\n\nNow analyzing {testFile}...")
        hashVal = hshr.HashFile(os.path.join(pathToRoms,testFile))
        if hashVal in currentHashList:
            print("This file is already in your collection, skipping.")
            alreadyExistsCount +=1
        else:
            print("This file is unique.  Adding to collection.")
            fileNameInc = 0
            targetFileName = testFile
            while os.path.isfile(os.path.join(fullRomPath,targetFileName)):
                fileNameInc +=1
                testFileNamePart, testFileExt = os.path.splitext(testFile)
                targetFileName = testFileNamePart +"_"+ str(fileNameInc) + testFileExt
            if testOnly:
                pass
            else:
                shutil.copyfile(os.path.join(pathToRoms,testFile),os.path.join(fullRomPath,targetFileName))
                conn.execute(f'INSERT INTO main_app("file_name", "system_console", "md5")VALUES("{testFile}","{systemID}","{hashVal}")')
                conn.commit()
            importedCount +=1
    #conn.close()
    return importedCount, alreadyExistsCount, errImportCount

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

def GetGameListBySystem(systemName, conn):
    res = conn.execute("SELECT main_app.id, main_app.game_name, no_intro_main.game_name, tosec_main.game_name,main_app.file_name FROM main_app LEFT JOIN no_intro_main ON main_app.md5 = no_intro_main.md5 LEFT JOIN tosec_main ON main_app.md5 = tosec_main.md5")
    #res = conn.execute("SELECT main_app.id, main_app.game_name, no_intro_main.game_name, main_app.file_name FROM main_app LEFT JOIN no_intro_main ON main_app.md5 = no_intro_main.md5")
    gameNamesMatrix = {}
    for h in res:
        if h[1]:
            gameNamesMatrix[h[1]] = h[0]
        elif h[2]:
            gameNamesMatrix[h[2]] = h[0]
        elif h[3]:
            gameNamesMatrix[h[3]] = h[0]
        else:
            gameNamesMatrix[h[4]] = h[0]

    #for g in gameNamesList:
    #    print(g)
    return gameNamesMatrix

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

    cursor.execute("""
        SELECT ma.id, ma.rom_filename, ma.rom_hash, ma.rom_filepath,
               sys.system_name, sys.system_description
        FROM main_app ma
        JOIN main_app_system sys ON ma.system_id = sys.id
        WHERE ma.id = ?
    """, (game_id,))
    game = cursor.fetchone()

    no_intro = cursor.execute("""
        SELECT nis.system_name, nim.game_name
        FROM no_intro_main nim
        JOIN no_intro_system nis ON nim.system_id = nis.id
        WHERE nim.rom_hash = ?
    """, (game[2],)).fetchone()

    tosec = cursor.execute("""
        SELECT ts.system_name, tm.game_name
        FROM tosec_main tm
        JOIN tosec_system ts ON tm.system_id = ts.id
        WHERE tm.rom_hash = ?
    """, (game[2],)).fetchone()

    conn.close()

    return {
        "id": game[0],
        "filename": game[1],
        "hash": game[2],
        "path": game[3],
        "system_name": game[4],
        "system_description": game[5],
        "no_intro_system": no_intro[0] if no_intro else None,
        "no_intro_game": no_intro[1] if no_intro else None,
        "tosec_system": tosec[0] if tosec else None,
        "tosec_game": tosec[1] if tosec else None,
    }

    
if __name__ == '__main__':
    pass
    #test = GetSystemList()
    #print(test.keys())
    #AddNewSystem("Super Nintendo", "SNES", r"Roms/Snes/Gromulus")
    #ImportNewNoIntroDat("/home/nino/MyCode/Python/Active_prgs/Gromulus/_data/_dats/Nintendo - Super Nintendo Entertainment System (20230516-033614).dat")
    #AddNewRoms('/mnt/smb/HomeData/ninoborges/Emulation/Roms/Snes/Random2',"1",testOnly=True)
    #res = conn.execute("SELECT * FROM main_app LEFT JOIN no_intro_main ON main_app.md5 = no_intro_main.md5")
    #for h in res:
    #    print(h)
    #    print("\n")
    #GetGameListBySystem('snes')
    #GetSingleGameById('224')

Revision:	907
Committed:	Sat Jun 7 03:32:11 2025 UTC (9 months, 3 weeks ago) by nino.borges
Content type:	text/x-python
File size:	8936 byte(s)
Log Message:	Updated to fix the issue where I'm opening the connection on import but closing at the end of each function. added a database manager class and added the OnExit in the UI mainloop to close the db connection.
#	User	Rev	Content
1	nino.borges	795	"""
2			This creates the main library of methods for Gromulus.
3
4			"""
5
6	nino.borges	805	import sqlite3, configparser, shutil
7	nino.borges	795	import xml.etree.ElementTree as ET
8			import Tool_Box.NinoGenTools, os
9
10
11	nino.borges	805	config = configparser.ConfigParser()
12			config.read("/home/nino/.gromulus/gromulus_cfg.ini")
13	nino.borges	795
14	nino.borges	907	#conn = sqlite3.connect(config.get('RootConfig','databasePath'))
15	nino.borges	795
16
17	nino.borges	907	class DatabaseManager:
18			def __init__(self):
19			self.db_path = config.get('RootConfig','databasePath') # Hardcoded DB path
20			self.conn = sqlite3.connect(self.db_path)
21
22			def get_connection(self):
23			return self.conn
24
25			def close(self):
26			if self.conn:
27			self.conn.close()
28			self.conn = None
29
30
31			def ImportNewNoIntroDat(datPath, conn):
32	nino.borges	795	"""Deletes all of the data in the two No_Intro tables and load the info from the new dat."""
33			tree = ET.parse(datPath)
34			root = tree.getroot()
35
36			## First gather information from header
37			for elm in root.findall('header'):
38			headerID = elm.find('id').text
39			headerName = elm.find('name').text
40			headerDesc = elm.find('description').text
41			headerVer = elm.find('version').text
42
43			#print(headerID)
44			#print (headerName)
45			#print(headerDesc)
46			#print(headerVer)
47			conn.execute("""DELETE FROM no_intro_system""" )
48			conn.execute(f'INSERT INTO no_intro_system VALUES ({headerID}, "{headerName}", "{headerDesc}", "{headerVer}")')
49
50			conn.execute("""DELETE FROM no_intro_main""" )
51			for elm in root.findall('game'):
52			gameName = elm.get('name')
53			gameID = elm.get('id')
54			gameCloneOfID = elm.get('cloneofid')
55			gameDesc = elm.find('description').text
56
57			romName = elm.find('rom').get('name')
58			romSize = elm.find('rom').get('size')
59			romCRC = elm.find('rom').get('crc')
60			romMD5 = elm.find('rom').get('md5')
61			romSHA1 = elm.find('rom').get('sha1')
62			romSHA256 = elm.find('rom').get('sha256')
63			romStatus = elm.find('rom').get('status')
64			conn.execute(f'INSERT INTO no_intro_main VALUES ("{gameName}", "{gameID}", "{gameCloneOfID}", "{gameDesc}", "{romName}", "{romCRC}", "{romMD5}", "{romSHA1}", "{romSHA256}", "{romStatus}", {headerID})')
65
66			conn.commit()
67	nino.borges	907	#conn.close()
68	nino.borges	795
69
70	nino.borges	907	def ImportNewTosecDat(datPath, conn):
71			"""Imports a TOSEC DAT file and populates the tosec_main and tosec_system tables."""
72			cursor = conn.cursor()
73	nino.borges	806	tree = ET.parse(datPath)
74			root = tree.getroot()
75
76	nino.borges	907	# Extract header info
77			header = root.find("header")
78			system_name = header.findtext("name")
79			system_description = header.findtext("description")
80	nino.borges	906
81	nino.borges	907	# Replace system info
82			cursor.execute("DELETE FROM tosec_system")
83			cursor.execute("INSERT INTO tosec_system (system_name, system_description) VALUES (?, ?)",
84			(system_name, system_description))
85			system_id = cursor.execute("SELECT id FROM tosec_system WHERE system_name = ?", (system_name,)).fetchone()[0]
86	nino.borges	906
87	nino.borges	907	# Replace game entries
88			cursor.execute("DELETE FROM tosec_main")
89			for game in root.findall("game"):
90			game_name = game.attrib.get("name", "")
91			rom = game.find("rom")
92			if rom is None:
93			continue
94			rom_filename = rom.attrib.get("name", "")
95			rom_size = int(rom.attrib.get("size", "0"))
96			rom_md5 = rom.attrib.get("md5", "")
97	nino.borges	806
98	nino.borges	907	cursor.execute("""
99			INSERT INTO tosec_main (game_name, rom_hash, rom_filename, rom_size, system_id)
100			VALUES (?, ?, ?, ?, ?)""",
101			(game_name, rom_md5, rom_filename, rom_size, system_id)
102			)
103
104	nino.borges	806	conn.commit()
105	nino.borges	907	#conn.close()
106			print("TOSEC DAT imported successfully.")
107	nino.borges	806
108	nino.borges	906
109	nino.borges	907	def AddNewSystem(systemName, shortName, relativePath, conn):
110	nino.borges	805	"""Adding a single system, with path where the ROMs will live."""
111			conn.execute(f'INSERT INTO main_app_system("name", "short_name", "relative_file_path")VALUES ("{systemName}", "{shortName}", "{relativePath}")')
112			conn.commit()
113	nino.borges	907	#conn.close()
114	nino.borges	805
115	nino.borges	795	## Next gather game information
116	nino.borges	907	def AddNewRoms(pathToRoms, systemID, conn, testOnly = False):
117	nino.borges	795	"""Adding additional roms."""
118	nino.borges	805	importedCount = 0
119			alreadyExistsCount = 0
120			errImportCount = 0
121
122			sysResult = conn.execute(f'SELECT relative_file_path FROM main_app_system WHERE id={systemID}')
123			for s in sysResult:
124			systemRomPath = s[0]
125			fullRomPath = os.path.join(config.get('RootConfig','softwareBackupStartDir'),systemRomPath)
126
127	nino.borges	795	res = conn.execute('SELECT md5 FROM main_app')
128			currentHashList = []
129			for h in res:
130	nino.borges	805	#print(h[0])
131	nino.borges	795	currentHashList.append(h[0])
132			#print(currentHashList)
133			print(len(currentHashList))
134	nino.borges	805
135	nino.borges	795	hshr = Tool_Box.NinoGenTools.HashFileContents('md5')
136			for testFile in os.listdir(pathToRoms):
137	nino.borges	805	print(f"\n\nNow analyzing {testFile}...")
138	nino.borges	795	hashVal = hshr.HashFile(os.path.join(pathToRoms,testFile))
139			if hashVal in currentHashList:
140			print("This file is already in your collection, skipping.")
141	nino.borges	805	alreadyExistsCount +=1
142	nino.borges	795	else:
143			print("This file is unique. Adding to collection.")
144	nino.borges	806	fileNameInc = 0
145			targetFileName = testFile
146			while os.path.isfile(os.path.join(fullRomPath,targetFileName)):
147			fileNameInc +=1
148			testFileNamePart, testFileExt = os.path.splitext(testFile)
149			targetFileName = testFileNamePart +"_"+ str(fileNameInc) + testFileExt
150			if testOnly:
151			pass
152	nino.borges	805	else:
153	nino.borges	806	shutil.copyfile(os.path.join(pathToRoms,testFile),os.path.join(fullRomPath,targetFileName))
154			conn.execute(f'INSERT INTO main_app("file_name", "system_console", "md5")VALUES("{testFile}","{systemID}","{hashVal}")')
155			conn.commit()
156			importedCount +=1
157	nino.borges	907	#conn.close()
158	nino.borges	805	return importedCount, alreadyExistsCount, errImportCount
159	nino.borges	795
160	nino.borges	907	def GetSystemList(conn):
161	nino.borges	805	res = conn.execute("SELECT id, name, short_name, relative_file_path FROM main_app_system ")
162			systemNamesMatrix = {}
163			for h in res:
164			systemNamesMatrix[h[1]] = [h[0],h[2],h[3]]
165			return systemNamesMatrix
166
167	nino.borges	907	def GetGameListBySystem(systemName, conn):
168	nino.borges	806	res = conn.execute("SELECT main_app.id, main_app.game_name, no_intro_main.game_name, tosec_main.game_name,main_app.file_name FROM main_app LEFT JOIN no_intro_main ON main_app.md5 = no_intro_main.md5 LEFT JOIN tosec_main ON main_app.md5 = tosec_main.md5")
169			#res = conn.execute("SELECT main_app.id, main_app.game_name, no_intro_main.game_name, main_app.file_name FROM main_app LEFT JOIN no_intro_main ON main_app.md5 = no_intro_main.md5")
170	nino.borges	795	gameNamesMatrix = {}
171			for h in res:
172			if h[1]:
173			gameNamesMatrix[h[1]] = h[0]
174			elif h[2]:
175			gameNamesMatrix[h[2]] = h[0]
176	nino.borges	806	elif h[3]:
177			gameNamesMatrix[h[3]] = h[0]
178	nino.borges	795	else:
179	nino.borges	806	gameNamesMatrix[h[4]] = h[0]
180	nino.borges	795
181			#for g in gameNamesList:
182			# print(g)
183			return gameNamesMatrix
184
185	nino.borges	907	def GetSingleGameById(game_id, conn):
186			"""Fetches full metadata for a single ROM entry, including No-Intro and TOSEC info if available."""
187			cursor = conn.cursor()
188
189			cursor.execute("""
190			SELECT ma.id, ma.rom_filename, ma.rom_hash, ma.rom_filepath,
191			sys.system_name, sys.system_description
192			FROM main_app ma
193			JOIN main_app_system sys ON ma.system_id = sys.id
194			WHERE ma.id = ?
195			""", (game_id,))
196			game = cursor.fetchone()
197
198			no_intro = cursor.execute("""
199			SELECT nis.system_name, nim.game_name
200			FROM no_intro_main nim
201			JOIN no_intro_system nis ON nim.system_id = nis.id
202			WHERE nim.rom_hash = ?
203			""", (game[2],)).fetchone()
204
205			tosec = cursor.execute("""
206			SELECT ts.system_name, tm.game_name
207			FROM tosec_main tm
208			JOIN tosec_system ts ON tm.system_id = ts.id
209			WHERE tm.rom_hash = ?
210			""", (game[2],)).fetchone()
211
212			conn.close()
213
214			return {
215			"id": game[0],
216			"filename": game[1],
217			"hash": game[2],
218			"path": game[3],
219			"system_name": game[4],
220			"system_description": game[5],
221			"no_intro_system": no_intro[0] if no_intro else None,
222			"no_intro_game": no_intro[1] if no_intro else None,
223			"tosec_system": tosec[0] if tosec else None,
224			"tosec_game": tosec[1] if tosec else None,
225			}
226
227	nino.borges	795
228
229
230			if __name__ == '__main__':
231	nino.borges	806	pass
232	nino.borges	805	#test = GetSystemList()
233			#print(test.keys())
234			#AddNewSystem("Super Nintendo", "SNES", r"Roms/Snes/Gromulus")
235	nino.borges	795	#ImportNewNoIntroDat("/home/nino/MyCode/Python/Active_prgs/Gromulus/_data/_dats/Nintendo - Super Nintendo Entertainment System (20230516-033614).dat")
236	nino.borges	806	#AddNewRoms('/mnt/smb/HomeData/ninoborges/Emulation/Roms/Snes/Random2',"1",testOnly=True)
237	nino.borges	795	#res = conn.execute("SELECT * FROM main_app LEFT JOIN no_intro_main ON main_app.md5 = no_intro_main.md5")
238			#for h in res:
239			# print(h)
240			# print("\n")
241			#GetGameListBySystem('snes')
242	nino.borges	805	#GetSingleGameById('224')
243	nino.borges	795