Appendix A. Game Database Schema
CREATE TABLE mame (
build,
debug DEFAULT 'no' CHECK (debug in ('yes', 'no')));
CREATE TABLE game (
name PRIMARY KEY,
sourcefile,
isbios DEFAULT 'no' CHECK (isbios in ('yes', 'no')),
runnable DEFAULT 'yes' CHECK (runnable in ('yes', 'no')),
cloneof,
romof,
romset CHECK (romset in ('good', 'best available', 'bad')),
sampleof,
sampleset CHECK (sampleset in ('good', 'best available', 'bad')),
description NOT NULL,
year,
manufacturer NOT NULL,
sound_channels,
input_service DEFAULT 'no' CHECK (input_service in ('yes', 'no')),
input_tilt DEFAULT 'no' CHECK (input_tilt in ('yes', 'no')),
input_players,
input_buttons,
input_coins,
driver_status CHECK (driver_status in ('good', 'imperfect', 'preliminary')),
driver_emulation CHECK (driver_emulation in ('good', 'imperfect', 'preliminary')),
driver_color CHECK (driver_color in ('good', 'imperfect', 'preliminary')),
driver_sound CHECK (driver_sound in ('good', 'imperfect', 'preliminary')),
driver_graphic CHECK (driver_graphic in ('good', 'imperfect', 'preliminary')),
driver_cocktail CHECK (driver_cocktail in ('good', 'imperfect', 'preliminary')),
driver_protection CHECK (driver_protection in ('good', 'imperfect', 'preliminary')),
driver_savestate CHECK (driver_savestate in ('supported', 'unsupported')),
driver_palettesize);
CREATE TABLE biosset (
game NOT NULL,
name NOT NULL,
description NOT NULL,
default_ DEFAULT 'no' CHECK (default_ in ('yes', 'no')));
CREATE TABLE rom (
game NOT NULL,
name NOT NULL,
bios,
size NOT NULL,
crc,
md5,
sha1,
merge,
region,
offset,
status DEFAULT 'good' CHECK (status in ('baddump', 'nodump', 'good')),
dispose DEFAULT 'no' CHECK (dispose in ('yes', 'no')));
CREATE TABLE disk (
game NOT NULL,
name NOT NULL,
md5,
sha1,
merge,
region,
index_,
status DEFAULT 'good' CHECK (status in ('baddump', 'nodump', 'good')));
CREATE TABLE sample (
game NOT NULL,
name NOT NULL);
CREATE TABLE chip (
game NOT NULL,
name NOT NULL,
type NOT NULL CHECK (type in ('cpu', 'audio')),
clock);
CREATE TABLE display (
game NOT NULL,
type NOT NULL CHECK (type in ('raster', 'vector', 'lcd', 'unknown')),
rotate NOT NULL CHECK (rotate in ('0', '90', '180', '270')),
flipx DEFAULT 'no' CHECK (flipx in ('yes', 'no')),
width,
height,
refresh NOT NULL,
pixclock,
htotal,
hbend,
hbstart,
vtotal,
vbend
vbstart);
CREATE TABLE control (
game NOT NULL,
type NOT NULL,
minimum,
maximum,
sensitivity,
keydelta,
reverse DEFAULT 'no' CHECK (reverse in ('yes', 'no')));
CREATE TABLE dipvalue (
game NOT NULL,
dipswitch NOT NULL,
name NOT NULL,
default_ DEFAULT 'no' CHECK (default_ in ('yes', 'no')));
CREATE TABLE playback (
name NOT NULL,
inode NOT NULL,
comment);
CREATE TABLE window (
name PRIMARY KEY,
x,
y,
width,
height,
maximized);
CREATE VIEW available AS SELECT
*,
getcategory(name) AS category,
isfavorite(name) AS favorite FROM game
WHERE (romset IN ('good', 'best available')
AND isbios = 'no');