Defacto2 releases daily exports of the MySQL database for download. You will need Docker or a preconfigured and running database server that can handle MySQL syntax.
There's also a repository for a Docker, Defacto2 Postgres database.
The recommended and easiest way to create the database is with Docker Compose. Which will download and setup a database server with a website admin interface.
# download this repository
git clone git@github.com:Defacto2/database.git
# change to the database directory
cd database
# run the import and start the database
# (tap Ctrl+c to later stop)
docker compose upAfterwards, the web interface for the database is found at http://localhost:8080/?server=db&username=root&db=defacto2-inno.
- System
MySQL - Server
db - Username
root - Password
example - Database
defacto2-inno
Congratulations, you now have a running database server with the Defacto2 data.
To stop the database server, tap Ctrl+c in the terminal.
The rest of the readme can be ignored. It's for those who want to create the database from scratch.
The simplist way to reset the database is to delete the container and start again. This will delete all the data and the database container.
cd database
# force-stop the database container
docker compose kill
# delete all the containers and associated volumes
docker compose rm -v sql_dump database web_adminThen rerun the Docker instructions above.
Using your preferred database application, you can use one of the following SQL statements to create the database and import the data.
Either of these database applications should be suitable.
Create is a one-time statement that initializes a new database, file table, and table columns.
create-table.sql
Insert is the daily export that uses an INSERT statement to create all new data.
d2-sql-insert.sql
The daily export update uses REPLACE INTO statements to update any existing data.
d2-sql-update.sql
In a terminal, use the MySQL client to import the data:
# download both the create table and insert data SQL statements
curl https://raw.githubusercontent.com/Defacto2/database/main/sql/create-table.sql --output create-table.sql
curl https://defacto2.net/sql/d2-sql-insert.sql --output d2-sql-insert.sql
# import the create table statement to the database using
# the root account and prompt the root password
mysql -u root -p < create-table.sql
# import the SQL data to the new defacto2-inno database
mysql -u root -p --database defacto2-inno < d2-sql-insert.sqlTo test the table creation and data.
mysql -u root -p --database defacto2-innomysql> SELECT COUNT(*) FROM files;
+----------+
| COUNT(*) |
+----------+
| 41384 |
+----------+
1 row in set (0.01 sec)
mysql> exitThe data and these exports are under a Creative Commons Attribution 4.0 International (CC BY 4.0) license.
The data gets separated into three tables, files, groups, and netresources.
filesthat form the site's core and compose of tens of thousands rows of hosted data.groupscomprise of only initialisms and acronyms for scene groups.netresourcesare online links to other scene resources, usually websites.
| Column | Description | Example value |
|---|---|---|
| id | Primary key | 8968 |
| uuid | Unique identifier used as the stored file and images name | b826e39b-66c6-4929-8e5a-f59b07ffaa00 |
| list_relations | List of associated Defacto2 records | Alternative;a84626 |
| web_id_github | Github repository Id | |
| web_id_youtube | YouTube video Id | |
| web_id_pouet | Pouët record Id | |
| web_id_demozoo | Demozoo record Id | 158448 |
| group_brand_for | Group or brand authorship | The Dream Team |
| group_brand_by | Group or brand authorship | |
| record_title | Production title or magazine issue | Midwinter II |
| date_issued_year | Published year | 1992 |
| date_issued_month | Published month | 3 |
| date_issued_day | Published day | 26 |
| credit_text | Writing credits | |
| credit_program | Programming credits | |
| credit_illustration | Artist credits | |
| credit_audio | Composer credits | |
| filename | Filename of the download | MID2TDT1.ZIP |
| filesize | Size of the download | 30923 |
| list_links | List of associated URLs | |
| file_security_alert_url | A URL to the results of a virus scan | |
| file_zip_content | List of files and directories contained in the download archive | DREAM.NFO INTRO.EXE MUSIC TEXT |
| file_magic_type | File type metadata | Zip archive data, at least v1.0 to extract |
| preview_image | The name of a file within the archive that was used as a screenshot | |
| file_integrity_strong | SHA386 hash value of the download | 22370b5e81... |
| file_integrity_weak | MD5 hash value of the download | c00fccc640... |
| file_last_modified | Last modified date value of the download | 2017-03-19 05:49:14 |
| platform | Computer platform tag | dOS |
| section | Category tag | releaseadvert |
| comment | Description of the download | |
| deletedat | When this record was disabled | |
| deletedby | The id of the account which disabled this record | |
| createdat | When this record was created | 2014-10-13 12:08:52 |
| dosee_run_program | Program filename to run in DOSee, the MS-DOS emulator | |
| retrotxt_readme | The filename of a text file to display in the browser | DREAM.NFO |
| retrotxt_no_readme | Toggle to disable retrotxt_readme | |
| dosee_hardware_cpu | DOSee CPU emulation selection | 486 |
| dosee_hardware_graphic | DOSee graphic card selection | vga |
| dosee_hardware_audio | DOSee audio card selection | covox |
| dosee_no_aspect_ratio_fix | DOSee aspect-ratio toggle | |
| dosee_incompatible | Flag this record as incompatible with DOSee | |
| dosee_no_ems | DOSee Expanded memory toggle | 1 |
| dosee_no_xms | DOSee Extended memory toggle | 1 |
| dosee_no_umb | DOSee Upper memory toggle | 1 |
| dosee_load_utilities | Load DOSee utilities | |
| updatedby | The id of the account which updated this record | ADB7C2BF-7221-467B-B813-3636FE4AE16B |
| updatedat | When this record was last updated | 2017-03-19 05:57:12 |
| Column | Description | Example value |
|---|---|---|
| id | Primary key | 9 |
| pubname | Unique group id | Razor 1911 |
| initialisms | Acronym or initialism value | RZR |
| Column | Description | Example value |
|---|---|---|
| id | Primary key | 675 |
| uuid | Unique identifier | 6c43b299-d7da-4670-a687-70c642b3a33e |
| legacyid | Callback to the original database used by Defacto2 | |
| httpstatuscode | HTTP code results from a ping | 200 |
| httpstatustext | HTTP text results from a ping | OK |
| httplocation | Unused | |
| httpetag | HTTP etag result that's used for cache | |
| httplastmodified | HTTP lastmodified result that's used for cache | |
| metatitle | Title metatag | Demozoo |
| metadescription | Description metatag | |
| metaauthors | Authors metatag | |
| metakeywords | Keywords metatag | |
| uriref | Web address | http://demozoo.org |
| title | Name of the site | Demozoo |
| date_issued_year | Publication year | |
| date_issued_year | Publication month | |
| date_issued_year | Publication day | |
| comment | About this web resource | The world's largest database of scene productions. |
| categorykey | Category tag | cracktro |
| categorysort | Subcategory tag | gallery |
| deletedat | When this record was disabled | |
| deletedatcomment | The reason this record was disabled | |
| createdat | When this record was created | 2016-05-07 20:43:36 |
| updatedat | When this record was last updated | 2017-01-27 22:11:23 |