ace-to-sqlite

Automation to export the latest releases of ACEmulator data as SQLite databases served via Datasette.

See it live at https://acedb.treestats.net. If you want to use the output in your own application, see the latest releases. You can use the latest tag to float or you can follow versioned tags. Versioned tags match the tags on ACE-World-16PY-Patches so you know which version of the database you're using.

Databases

The following data sources are included:

• ace_world_base: https://github.com/ACEmulator/ACE-World-16PY
• ace_world_patches: https://github.com/ACEmulator/ACE-World-16PY-Patches
• ace_pcap_exports: https://github.com/ACEmulator/ACE-PCAP-Exports
• ace: https://github.com/ACEmulator/ACE (via dogsheep)

Rationale

People sometimes want to know if certain items are in the game or build things on top of ACE data. The best way to do this is to query the ACE database. This makes that (and more) easier.

Methodology

I didn't find a good tool that can directly convert MySQL DDL (What ACE stores its data as) to SQLite DDL (what we need for loading into Datasette). After trying to some things, I settled on the following set of steps:

• Spin up a MySQL instance
• Load MySQL DDL files in
• Run a custom version of db-to-sqlite
• Publish with Datasette to a VPS running Dokku.

Updates

The databases are updated automatically. A GitHub Actions workflow runs daily, checks each upstream ACEmulator repo for new releases, and — if anything changed — exports fresh SQLite databases, publishes them to the latest release, and redeploys https://acedb.treestats.net.

Using the Databases

The SQLite databases are published as assets on each GitHub Release. The latest release always contains the most recent versions.

Download via browser: Go to the latest release and download whichever .db files you need.

Download via gh CLI:

gh release download latest --repo amoeba/ace-to-sqlite --pattern "*.db"

Or download a specific database:

gh release download latest --repo amoeba/ace-to-sqlite --pattern "ace_world_patches.db"

Once downloaded, query with any SQLite tool:

sqlite3 ace_world_patches.db "SELECT * FROM weenie LIMIT 10;"

Or explore interactively with Datasette:

pip install datasette
datasette ace_world_patches.db

Running This Yourself

This automation is a GitHub Action so please look at https://github.com/amoeba/ace-to-sqlite/blob/main/.github/workflows/export.yml for the steps.

If you're interested in running the automation yourself and in another environment, look at https://github.com/amoeba/ace-to-sqlite/blob/main/scripts/generate_world_database.sh which will download and convert the latest ACE World release to SQLite. Its requirements are:

• A local MySQL server instance with the root password off
• bash
• curl
• jq
• unzip
• Python
• db-to-sqlite (Installed from my fork)

Contributing

Please file an Issue if you have any questions or commands. An example of a good type of issue to file would be if you want a datasource included here that isn't or if you find either a data or documentation issue.