I created a public API that exposes WCA competition results
The World Cube Association governs competitions for mechanical puzzles that are operated by twisting groups of pieces, commonly known as ‘twisty puzzles’.
The WCA’s website contains rankings, records, statistics, and a lot of other interesting data, but at the time of writing, there’s no official API available for developers to analyze competition information for statistical or personal purposes. That’s why I decided to create one myself.
Some research
Before setting up the whole project, I had to do some research. The only thing I knew beforehand was that the WCA published results in the form of an SQL file or several CSV files for you to download. The documentation states:
## Allowed Use
The information in this file may be re-published, in whole or in part, as long as users are clearly notified of the following: …
This meant that I could use the data, create an API and publish it somewhere on the web.
I was prepared to sink some time into this project, but I wasn’t planning on paying for any hosting because it could get costly, very fast. That’s why I decided to create an API served through static JSON files hosted on GitHub. Other reasons for doing so are:
- The WCA data doesn’t change that much, max once a day
- A static file-based API is (or should be) very fast
This approach also has several downsides, the biggest ones being that the data is not real-time and the structure of the endpoints has limitations:
- Paging query parameters are not possible
- Filter query params are not possible
- Dynamic page sizes are not possible
At this point, I was ready to start building the thing.
The thing
Analyzing the database
After a quick glance, I was able to identify the tables and the relations between them:
Fairly straightforward if you ask me.
Building the static files
Next up was determining the endpoints I wanted to expose. I had to work around the fact I wasn’t able to use query parameters. So I came up with this initial set of endpoints:
# General endpoints
api/continents.json
api/countries.json
api/events.json
# Cometition endpoints
api/competitions{-page-[0-9]}.json
api/competitions/{country}.json
api/competitions/{year}.json
api/competitions/{year}/{month}.json
api/competitions/{year}/{month}/{day}.json
api/competitions/{event}{-page-[0-9]}.json
api/competitions/{id}.json
# Championship endpoints
api/championships{-page-[0-9]}.json
api/championships/{id}.json
# Person endpoints
api/persons{-page-[0-9]}.json
api/persons/{id}.json
# Rank endpoints
api/rank/{region}/{type}/{event}.json
# Results endpoint
api/results/{competition}.json
api/results/{competition}/{event}.jsonI made sure that every “overview” endpoint had the same structure:
{
"pagination": {
"page": 1,
"size": 1000
},
"total": 3465,
"items": []
}pagination- Contains page info for the current requesttotal- Indicates the total number of items available for this modelìtems- The list of items for the current page
To determine the total number of pages, you’ll need to divide the total number of items by the page size (which is hardcoded to 1000).
To navigate through the pages, you can adjust the page parameter in subsequent requests. For example, to retrieve the second page of data, you would set competitions-page-2.json.
The actual API implementation is written in PHP using DDD and the command pattern.
Local build script
Next, I came up with a build script that allowed me to
- Check if there was a new WCA export version available
- Download and unzip the most recent WCA export
- Import all data into a database
- Add indexes for faster processing
- Build the actual JSON files
- Publish the new API build
#!/usr/bin/env bash
# ... Left out some irrelevant code
# Check if there's a new version
NEW_VERSION=$(curl -s -L https://www.worldcubeassociation.org/api/v0/export/public)
CURRENT_VERSION="`cat api/version.json 2>/dev/null`"
if [ "$NEW_VERSION" == "$CURRENT_VERSION" ]; then
echo "No new version detected, exiting, bye."
exit 0
fi
# Download and unzip WCA export.
echo "Downloading WCA export..."
curl https://www.worldcubeassociation.org/export/results/WCA_export.sql.zip --silent --output "wca-export/export.zip" > /dev/null
echo "Unzipping WCA export..."
unzip wca-export/export.zip -d wca-export
# Import SQL file into db.
echo "Importing WCA export to database..."
mysql --host="host.docker.internal" --user=root --password=root --port=3307 wca < wca-export/WCA_export.sql
# Add indexes for faster processing.
mysql --host="host.docker.internal" --user=root --password=root --port=3307 wca -e "CREATE INDEX personId_index ON Persons (id)"
# Left out all the other indexes.
# Build API.
bin/console app:api:build
# Push new API build to remote.
git add .
git commit -m"New API build"
git pushBuilding and publishing a new API version takes about 70 minutes. Considering there are over 250 000 files to be generated, I was quite happy with this result.
GitHub actions
Running the local build script was still a manual action and I didn’t want to be bothered with doing this every day. That’s where GitHub actions came into play.
GitHub Actions makes it easy to automate all your software workflows, now with world-class CI/CD. Build, test, and deploy your code right from GitHub. Make code reviews, branch management, and issue triaging work the way you want.
Apart from having a Continuous Integration workflow, I also added a Build API workflow that was very similar to the local build script:
name: Build API
on:
workflow_dispatch:
schedule:
- cron: '30 13 * * *'
jobs:
build-api:
name: Build API
runs-on: ubuntu-latest
steps:
# https://github.com/marketplace/actions/setup-php-action
- name: Setup PHP 8.2
uses: shivammathur/setup-php@v2
with:
php-version: '8.2'
extensions: intl
# https://github.com/marketplace/actions/checkout
- name: Checkout code
uses: actions/checkout@v3
- name: Copy .env file
run: cp .env.github-actions .env
- name: Install dependencies
run: composer install --prefer-dist
- name: Build the new api
run: |
set -e
# Check if there's a new version
NEW_VERSION=$(curl -s -L https://www.worldcubeassociation.org/api/v0/export/public)
CURRENT_VERSION=$(curl -s -L https://raw.githubusercontent.com/robiningelbrecht/wca-rest-api/master/api/version.json)
echo $NEW_VERSION
echo $CURRENT_VERSION
if [ "$NEW_VERSION" != "$CURRENT_VERSION" ]; then
# Left out, same as local build script.
else
echo "No new version detected, exiting, bye."
fi
- name: Commit and push changes
run: |
git config --global user.name 'robiningelbrecht'
git config --global user.email 'robiningelbrecht@users.noreply.github.com'
git add .
git status
git diff --staged --quiet || git commit -m"New API build"
git push
Done 🥳! Apart from the documentation… 😔
The documentation
What’s an API without documentation, right? Next to useless if you ask me.
There are a lot of great tools available to generate documentation from OpenAPI specifications. The most known is Swagger UI, but I decided to use Redoc. I felt that this UI was more user-friendly and it was responsive as well.
The full documentation and specs are available at https://wca-rest-api.robiningelbrecht.be/
Open-Source
I’m a big advocate of open-source software development. That’s the reason why the repository that contains the source code as well as all the generated JSON files, is public.
If you’d like to help with the development of this project, or you just want to run it locally, run the following commands:
# Clone repo
git clone git@github.com:robiningelbrecht/wca-rest-api.git
# Setup .env file
cp .env.dist .env
# Build docker containers
docker-compose up -d --build
# Install dependencies
docker-compose run --rm php-cli composer install
# Build all the static API files.
docker-compose run --rm php-cli bin/build-new-api.sh "continent,country,event,competition,championship,person,rank,result,version"