ZXDB

ZXDB is an open database containing historical information of software, hardware, magazines and books about ZX-Spectrum and related machines.

It was created by Einar Saukas, starting from the full content of Martijn van der Heide's Original WorldOfSpectrum, Jim Grimwood's SPOT/SPEX/TTFn, Daren Pearcy's RZX Archive, and Chris Bourne's ZXSR repositories (all of them imported with consent, directly from their internal files). Afterwards it was expanded with literally tens of thousands of corrections, additions, and integration from many other sources. It's currently the most widely used Sinclair related database, feeding several Spectrum websites, an open API at ZXInfo, and the mobile application Zx App that uses this API. It's also used as index reference by a dozen different websites and services.

For further details, visit the ZXDB forum section at Spectrum Computing.

Getting Started

Simply download the latest database content, then load it into MySQL/MariaDB:

• ZXDB_mysql.sql.zip - The latest complete ZXDB database script for MySQL/MariaDB. That's all you really need!

Optionally you can execute one of the provided scripts to convert file ZXDB_mysql.sql above to a different RDBMS:

• scripts/ZXDB_to_SQLServer.ps1 - Powershell script to convert ZXDB into SQL Server compatible T-SQL

• scripts/ZXDB_to_SQLite.py - Python script to convert ZXDB into SQLite compatible SQL

• scripts/ZXDB_to_generic.groovy - Groovy script to convert ZXDB into a (more) generic SQL

There's also an optional script to create auxiliary tables, that can be used to help database searches. Ideally these tables must be repopulated whenever ZXDB content changes, or defined as materialized views in a RDBMS that supports it:

• scripts/ZXDB_help_search.sql - Script to create auxiliary tables prefixed with search_by_

The ZXDB distribution already includes links and references to integrated sites like Speccy Screenshot Maps and ZX-Spectrum Reviews (ZXSR), but this information can also be updated independently. There are separate scripts to reimport them into ZXDB:

• scripts/*-ZXDB_import_*.sql - Scripts to reimport information from integrated sites (additional details are explained in each file)

Finally there's a script for health checking, that validates ZXDB consistency rules that cannot be enforced by check constraints:

• scripts/ZXDB_health_check.sql - Script to identify data inconsistencies in ZXDB

Database model

The ZXDB schema is described below:

PRIMARY TABLES

• entries - Spectrum-related items (programs, books, computers and peripherals)

• labels - individuals and companies (authors, publishers, development teams, copyright holders)

• magazines - published magazines (printed or electronic). The magazine link mask follows this convention:

  • {i#} - magazine issue number, with (at least) # digits
  • {v#} - magazine issue volume number, with (at least) # digits
  • {y#} - magazine issue year, with (at least) # digits
  • {m#} - magazine issue month, with (at least) # digits
  • {M#} - magazine issue month name, with exactly # letters (starting with uppercase)
  • {d#} - magazine issue day, with (at least) # digits
  • {p#} - page number, with (at least) # digits
  • {s#} - magazine special issue string, preceded by character '#'
  • {u#} - magazine issue supplement string, preceded by character '#'

• tools - Spectrum-related cross-platform utilities and development tools (emulators, compilers, editors, etc)

• websites - main websites that provide information about items (MobyGames, Tipshop, Wikipedia, etc). The website link mask follows this convention:

  • {e#} - entry ID, with (at least) # digits

SECONDARY TABLES

• aliases - alternate titles for items (sometimes generic, sometimes just for a specific release and/or language)

• articles - online articles about authors (profile, interview, memoir, etc)

• downloads - available material related to a specific entry/release (screenshot, tape image, inlay, map, instructions, etc)

• features - magazine sections that featured certain entry or label references

• files - available material related to a label (photos, posters, advertisements, etc), magazine issue (electronic magazine files, printed magazine scans, covertape music, etc), or cross-platform tool (installation files, instructions, etc)

• hosts - main services that provide information about certain features

• issues - each published issue of a magazine

• licenses - inspirations or tie-in licenses (from arcades, books, movies, etc)

• notes - additional information about each entry (known errors, received awards, etc)

• nvgs - oldest files preserved from ftp.nvg.unit.no

• ports - Spectrum programs also released on other platforms

• releases - each release of an item (date, price, publisher, etc)

  • release_seq=0 - original release
  • release_seq=1 - 1st re-release
  • release_seq=2 - 2nd re-release
  • ...

• remakes - modern remakes of Spectrum programs

• scores - average score received by each entry at main websites

• scraps - obsolete files from the Original WorldOfSpectrum

• tags - sets of programs with similar characteristics (participants in the same competition, based on the same original game, etc)

• topics - catalogue of magazine sections

RELATIONSHIP TABLES

• authors - associate entries to their authors

  • author_seq=1 - 1st author (or only author)
  • author_seq=2 - 2nd author
  • ...

• booktypeins - associate typed-in programs to the books that published them

• contents - associate list of programs contained in compilations, covertapes or electronic magazines

• licensors - associate licenses to their license owners

• magrefs - associate entries or labels to pages from magazine issues about them (magazine references)

• magreffeats - associate magazine references to features

• magreflinks - associate magazine references to links about them

• members - associate tags to their list of programs

  • series_seq - required for sequenced series, optional for competitions and demoparties

• permissions - associate labels to distribution permissions granted to websites

• publishers - associate entries to their publishers

  • publisher_seq=1 - 1st publisher of a specific release (or unique publisher)
  • publisher_seq=2 - 2nd publisher (only if same release has multiple publishers)
  • ...

• relatedlicenses - associate programs to their inspirations or tie-in licenses

• relations - relationships between programs (inspired by, authored with, etc)

• roles - associate authors to their roles (for each entry)

• webrefs - associate programs to webpages about them at other main websites

DOMAIN TABLES

• articletypes - list of article types (profile, interview, memoir, etc)

• availabletypes - list of availability status for entries:

  • MIA - released products that are not (yet) found/preserved anywhere
  • Available - released products preserved here
  • Not archived (available elsewhere) - released products only available for download elsewhere
  • Distribution denied - products whose author/owner explicitly prohibited free distribution
  • Distribution denied - still for sale - products whose author/owner explicitly prohibited free distribution because they are still sold somewhere
  • Never released - products never released (for whatever reason)
  • Never released - recovered - products never officially released but later recovered/preserved

• casetypes - list of media case types (jewel, clamshell, cardbox, etc)

• categories - list of competition or demoparty categories

• contenttypes - list of content types in compilations, covertapes or electronic magazines (full version, demo, soundtrack only, etc)

• countries - list of countries (using ISO 3166-1 Alpha-2 standard codes)

• currencies - list of `currencies (using ISO 4217 standard codes)

• extensions - list of supported filename extensions in ZXDB

• filetypes - list of file types (screenshot, tape image, inlay, photo, poster, etc)

• genretypes - list of entry types (program type, book type, hardware type, etc)

• labeltypes - list of label types (person, nickname, companies)

• languages - list of languages (using ISO 639-1 standard codes)

• licensetypes - list of license types (arcade coin-up, book, movie, etc)

• machinetypes - list of machine types required for each program:

  • ZX-Spectrum 16K - programs that require (at least) 16K
  • ZX-Spectrum 16K/48K - programs that work on (at least) 16K, but provide additional features in 48K
  • ZX-Spectrum 48K - programs that require (at least) 48K
  • ZX-Spectrum 48K/128K - programs that work on (at least) 48K, but provide additional features in 128K (AY music, more levels, etc)
  • ZX-Spectrum 128K - programs that require (at least) 128K
  • ZX-Spectrum 128K (load in USR0 mode) - programs that require (at least) 128K, and must be loaded in USR0 mode
  • ...

• magtypes - list of magazine types (paper, electronic, etc)

• notetypes - list of note types (awards, errors, etc)

• origintypes - list of indirect original publication types (covertape from magazine, type-in from book, etc)

• permissiontypes - permission types:

  • Allowed - copyright owner allowed distribution permission for all titles
  • Denied - copyright owner denied distribution permiss