MAME: 0.271 - Released: 2024-10-30T15:59:16 - Machines: 47358 - rom: 354314 - disk: 1336 - Lists: 717 - Software: 138541 - rom: 234702 - disk: 11183
Generate the data yourself
You can use the open source project MAME-AO to generate data in various formats without having to download them here.
Downloads
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';"