openMSX romdb download

By Vampier

Prophet (2193)

Vampier's picture

20-05-2018, 08:17

I've added the romdb sqllite3 database and XML file for download @ https://romdb.vampier.net/archive.php

The following objects are most important:

[Table] msxdb_company (company_id) << Yeah I know should have been CompanyID, for those with OCD.
[Table] msxdb_romdetails (GameID)
[Table] msxdb_rominfo (GameID, CompanyID1, CompanyID2)

Or take a look at the SQL I used in the sql views.

Anyway I assume you know how to write SQL. Do with this database what you want. You could write a nice ROM sorting and renaming script like this for example ;) and yes it's a quick and dirty prototype which I will improve on soon.

# Import the os module, for the os.walk function
import os
import sys
import hashlib
import sqlite3
from shutil import copyfile
import unicodedata

conn = sqlite3.connect('RomDB.db')
cur = conn.cursor()
platform = ''


def getRomInfo(sha1value):
	
		global platform
		sql = "SELECT GameName,Year,SHA1,ifnull(shortname,'') as CompanyName,IFNULL(Remark,'') as Remark,IFNULL(Meta,'') as Meta,case when upper(Dump) = 'GOODMSX' then 'original' when upper(Dump) = 'AUTHOR' then 'original'  else '' end as Dump,Platform  FROM msxdb_rominfo t1 join msxdb_romdetails t2 on t1.GameID=t2.GameID join msxdb_company t3 on t1.CompanyID1=t3.company_id  where trim(upper(sha1))=upper('"+sha1value+"')"
		cur.execute(sql)

		row = cur.fetchone()

		if row is None:
			return 'NotFound'
		
		if row[0] is None :
			row0=''
		else: row0=row[0]
		
		if row[3] is None :
			row3=''
		else:row3=row[3]	
			
		if row[1] is None :
			row1=''
		else:row1=row[1]		
		
		if row[4] is None :
			row4=''
		else:row4=row[4]
			
		if row[5] is None :
			row5=''
		else: row5=row[5]
		
		if row[6] is None :
			row6=''
		else: row6=row[6]
		
		if row[7] is None :
			platform=''
		else: platform=row[7]

		#print sql
		fname =row0 +' - '+ row3 +' ('+row1+') ' +' ['+row4+'-'+row5+'] ['+row6+']'
		
		fname=fname.replace("?", "")
		fname=fname.replace("!", "")
		fname=fname.replace("#", "")
		fname=fname.replace("[-", "[")
		fname=fname.replace("-]", "]")
		fname=fname.replace("[[", "[")
		fname=fname.replace("]]", "]")
		fname=fname.replace("[-]", "")
		fname=fname.replace("[-", "[")
		fname=fname.replace("-]", "]")
		fname=fname.replace("[]", "")
		fname=fname.replace("/", "-")
		fname=fname.replace("\\", "-")
		fname=fname.replace(u"\u00f8", "o")
		fname=fname.replace(u"\u00e9", "e")
		fname=fname.replace(u"\u00e1", "a")
		fname=fname.replace(u"\u02BC", "'")
		fname=fname.replace("()", "")
		fname=fname.replace("  ", " ")
		fname=fname.replace("  ", " ")
		fname=fname.replace("  ", " ")
		fname=fname.replace(":", "")
		fname=fname.replace("'", "\'")
		
		#print fname
		
		return fname.strip()

def sha1value(fname):
	BLOCKSIZE = 65536 #64Kb
	hasher = hashlib.sha1()
	with open(fname, 'rb') as afile:
		buf = afile.read(BLOCKSIZE)
		while len(buf) > 0:
			hasher.update(buf)
			buf = afile.read(BLOCKSIZE)
	afile.close()
	return (hasher.hexdigest())
	

def handlefiles(dirname,fname):
	#print fname
	global platform
	try:
		if fname[-4:].upper()==".ROM":
			hash=sha1value(dirName+'\\'+fname).upper()
			newfilename = getRomInfo(hash)
			#too bad... let's move this one to the unknown map
			if newfilename=="NotFound":
				print "NotFound:"+fname+" "+hash
				print hash
				copyfile(dirName+'\\'+fname,"./notfound/"+fname)
				os.remove(dirName+'\\'+fname)
				return
			#great! rom found
			print platform+" >> "+dirName+'\\'+fname+" copy to "+"./sorted/"+platform+"/"+newfilename+".rom"
			copyfile(dirName+'\\'+fname,"./sorted/"+platform+"/"+newfilename+".rom" )
			#And delete original file
			os.remove(dirName+'\\'+fname)
	except Exception as e: print(e)
	

# Set the directory you want to start from
rootDir = './unsorted'
for dirName, subdirList, fileList in os.walk(rootDir):
    #print('Found directory: %s' % )
    for fname in fileList:
        handlefiles(dirName,fname)
		
conn.close()
Login or register to post comments

By Vampier

Prophet (2193)

Vampier's picture

07-07-2018, 07:42

the webpage has been updated with bootstrap 4.

https://romdb.vampier.net/index.php?platform=msx

My MSX profile