openMSX romdb download

By Vampier

Prophet (2235)

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 (2235)

Vampier's picture

07-07-2018, 07:42

the webpage has been updated with bootstrap 4.

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

By DrWh0

Paladin (714)

DrWh0's picture

31-08-2018, 18:40

Can I add in newers releases of my program "screenshotter" an option open the browser to check your online database?

My program (can be useful for your database in order to add screenshots to the list):

https://mega.nz/#F!ldkxSZpY!wwLxfwoygdNBnKwDTzWr9g

By Vampier

Prophet (2235)

Vampier's picture

01-09-2018, 06:12

you can download the database for use in the program, it's just a simple SQLLite3 database. If you need a view for specific data I can provide that too. But the database is the same as the XML file... the zip file on romdb.vampier.net contains both.

By Vampier

Prophet (2235)

Vampier's picture

02-09-2018, 07:54

I sent you an e-mail on your outlook e-mail.

By DrWh0

Paladin (714)

DrWh0's picture

02-09-2018, 17:20

Replyed Vampier, thank you very much!

I will add the function too in addition of a little fix in version 0.8 Smile

My MSX profile