Spludlow Mame Web Header

Spludlow MAME - MAME Database Downloads


MAME: 0.235 - Released: 2021-08-26T08:11:29 - Machines: 43758 - rom: 327814 - disk: 1179 - Lists: 646 - Software: 129993 - rom: 215582 - disk: 10354

Downloads

Request a MAME database product



(Optional) Please let us know what you're doing with the data set, out of interest.


Please enter the 3 digit code then click the button

MAME Database Downloads Details

Machine Flat Filter Table

This is a single table designed to make custom queries as simple as possible without having to do and database joins.

Available as tab-delimited text, JSON, and Microsoft Access.

NOTE: In tab-delimited text format the second line is datatype, so you probably want to delete that row in a text editor before importing elsewhere.

Columns exist for most things that a query might target, just look through the data.

The table has already had the following filtered (so no columns for these)

·         cloneof IS NULL (parents only)

·         isbios = 'no'

·         isdevice = 'no'

·         ismechanical = 'no'

·         runnable = 'yes'

Queries can be built very quickly using Microsoft access, for example to see 6 button machines with a vertical screen and a good status:

To build a query in Microsoft Access (Video coming soon)

·         Open the access database

·         Double click “MachineFilter” table on the top left to browse the whole table (Just to have a look)

·         Click Menu “Create” -> “Query Design”

·         Click “Add” in the “Show Table” dialogue to add the “MachineFilter” table

·         Click “Close” in the “Show Table” dialogue

·         Double click “*” in the “MachineFilter” table in “Query1” design area (You can drag this table bigger to see more columns)

·         Click the View icon table top left to see the current filter data

·         Click the View design icon top left to switch back to design.

·         Double click on a column you are interested on (e.g. year) it will appear in the query at the bottom

·         Set a value (e.g. 1999) in the criteria

·         Click the View icon table top left to see the filter applied.

·         Click the View design icon top left to switch back to design and improve the filter

Note strings should be in double quotes, numbers should not, Booleans True or False.

MAME databases in Microsoft Access

The MAME data sets are available in Microsoft Access format.

Microsoft Access is an excellent way of quickly familiarizing yourself with any dataset. It also makes building queries very simple and will generate the SQL that you can use it elsewhere (without having to write it yourself).

You can use it stand alone with the data stored in the .accdb or as a front end, using linked tables, to connect to a database server (with ODBC drivers).

MAME databases in MySQL Dump

The MAME data sets are available in MySQL Dump format.

To load the data on the server:

·         Start MySQL Workbench and connect to a server instance

·         Create an empty schema (Navigator -> Schemas -> Right click -> Create schema…)

·         Run the SQL script against the newly created schema (File menu -> Run SQL Script… -> Select SQL File -> Change Default Schema)

To create a database diagram:

·         Start MySQL Workbench and connect to a server instance

·         Database Menu -> Reverse engineer…

·         Click through with next selecting your schema

MAME Databases in SQL Server BAK

The MAME data sets are available in Microsoft SQL Server BAK format.

Restoring from an SQL Server BAK file

NOTE: The BAK was created on version 2017 (14) you will not be able to restore to previous server versions.

Restoring a SQL BAK from another machine is not straightforward, but when you have figured it out it is very quick.

First of all, make sure you understand it’s the server not the client that is accessing the BAK file. If your server is on another machine (not what you are running SSMS on) then the file needs to be on the server. Also, the server process is running as a different user than your user so even on the same machine, due to filesystem permissions, the server still can’t access files for example on your desktop.

Create a work folder

This should be created on the server machine. The SQL Server user will need to be given permission.

The user name is “NT SERVICE\MSSQLSERVER” for a default instance, or “NT SERVICE\MSSQL$InstanceName” for named instance. Give it full control.

Restoring a BAK file

NOTE: All values here a on a default installation, you will have to substitute values for your requirements.

·         “MyMameMachine” &” MyMameSoftware” are the target database names you require, they are also present as part of the target physical filenames.

·         “C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\” is the path to the server datafiles, ensure you can browse to it and replace if required.

·         “C:\SQLBACKUP\MameMachine0.224.bak” is the source BAK file wherever you have it.

·         Don’t change “MameMachineTemp” &” MameSoftwareTemp” they are the logical names within the BAK.

Copy the BAK files to the work directory “C:\SQLBACKUP”.

Machine - Run on the master database:

RESTORE DATABASE [MyMameMachine] FROM DISK='C:\SQLBACKUP\MameMachine0.224.bak' WITH REPLACE,

MOVE 'MameMachineTemp' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MyMameMachine.mdf',

MOVE 'MameMachineTemp_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MyMameMachine_log.ldf';

 

Software - Run on the master database:

RESTORE DATABASE [MyMameSoftware] FROM DISK='C:\SQLBACKUP\MameSoftware0.224.bak' WITH REPLACE,

MOVE 'MameSoftwareTemp' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MyMameSoftware.mdf',

MOVE 'MameSoftwareTemp_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MyMameSoftware_log.ldf';

 

Set the dbo - database owner

Database need to have an owner user, if they don’t they will play up. Attempting to generate a diagram in SSMS won’t work for a start.

As the backup was made on another computer that user does not exist on the restore computer.

Run this on each both databases (Machine & Software) substitute your host or domain name and username:

sp_changedbowner 'HOSTORDOMAIN\Fred';

Fix the logical filenames (optional)

This step is not necessary, everything will work fine without it, it fixes the logical data filenames from what they are on the source computer to what you want them to be.

Machine - Run on the master database:

ALTER DATABASE [MyMameMachine] MODIFY FILE (NAME='MameMachineTemp', NEWNAME='MyMameMachine');

ALTER DATABASE [MyMameMachine] MODIFY FILE (NAME='MameMachineTemp_log', NEWNAME='MyMameMachine_log');

 

Software - Run on the master database:

ALTER DATABASE [MyMameSoftware] MODIFY FILE (NAME='MameSoftwareTemp', NEWNAME='MyMameSoftware');

ALTER DATABASE [MyMameSoftware] MODIFY FILE (NAME='MameSoftwareTemp_log', NEWNAME='MyMameSoftware_log');

 

Create a database diagram

In SSMS expand the database and right click on Database Diagrams.

MAME Databases in SQLite format

The MAME data sets are available in SQLite format.

Example query

./sqlite3.exe "MameMachine0.227.sqlite" "SELECT machine.name, rom.* FROM machine INNER JOIN rom ON machine.machine_Id = rom.machine_id WHERE machine.name='mrdo';"

 



Spludlow Mame Web Footer