Appendix A. Game Database Schema

Table: mame

CREATE TABLE mame (
        build,
        debug DEFAULT 'no' CHECK (debug in ('yes', 'no')));

Table: game

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);

Table: biosset

CREATE TABLE biosset (
        game NOT NULL,
        name NOT NULL,
        description NOT NULL,
        default_ DEFAULT 'no' CHECK (default_ in ('yes', 'no')));

Table: rom

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')));

Table: disk

CREATE TABLE disk (
        game NOT NULL,
        name NOT NULL,
        md5,
        sha1,
        merge,
        region,
        index_,
        status DEFAULT 'good' CHECK (status in ('baddump', 'nodump', 'good')));

Table: sample

CREATE TABLE sample (
        game NOT NULL,
        name NOT NULL);

Table: chip

CREATE TABLE chip (
        game NOT NULL,
        name NOT NULL,
        type NOT NULL CHECK (type in ('cpu', 'audio')),
        clock);

Table: display

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);

Table: control

CREATE TABLE control (
        game NOT NULL,
        type NOT NULL,
        minimum,
        maximum,
        sensitivity,
        keydelta,
        reverse DEFAULT 'no' CHECK (reverse in ('yes', 'no')));

Table: dipvalue

CREATE TABLE dipvalue (
        game NOT NULL,
        dipswitch NOT NULL,
        name NOT NULL,
        default_ DEFAULT 'no' CHECK (default_ in ('yes', 'no')));

Table: playback

CREATE TABLE playback (
        name NOT NULL,
        inode NOT NULL,
        comment);

Table: window

CREATE TABLE window (
	name PRIMARY KEY,
	x,
	y,
	width,
	height,
	maximized);

View: available

CREATE VIEW available AS SELECT
        *,
        getcategory(name) AS category,
        isfavorite(name) AS favorite FROM game
        WHERE (romset IN ('good', 'best available')
        AND isbios = 'no');