Uma Musume REST API

This is a community REST API based on Uma Musume: Pretty Derby's meta and master.mdb files that is read from a MariaDB database. This is based on the Swagger UI/OpenAPI specification. I'm using MariaDB instead of MySQL for the sake of keeping as much of this as open-source as possible. The API is available here for your viewing pleasure.

I've written a loader app that allows you to load the meta and master.mdb's data from the DMM version of this game into either SQL Server or MySQL/MariaDB databases. The reason for both is due to change in database specifications midway through development, but I didn't want to let my hard work go to waste.

Features

This API not only has endpoints to the tables, but I've created views for basic info for frequently used tables. I've even implemented stored procedures for a few particular calculations like parent compatibility and recommendations.

The overall endpoint structure is:

• Raw table data (imported straight from the meta and master.mdb files)
• Basic indexing of frequently used tables (endpoints prefixed with Basic)
• Condensed data of foreign keys to allow easier reporting (endpoints prefixed with Condensed)
• Nicely displayed data of frequently used info (endpoints prefixed with Nice)
• Stored procedures (endpoints prefixed with Sp)

The stored procedures are:

• SpSuccessionGrandparentRecommendation
  • This is the recommended grandparents based on the child's parent
• SpSuccessionParentRecommendation
  • This is the recommended parents based on the child
• SpSuccessionPointSum
  • This is the overall calculation based on the child's compatibility with the proposed parents and grandparents

Translation Endpoint

I've created the TextDataEnglish endpoint by importing Noccu's translation repo that holds .json files of translated data based on the original text_data table.

In addition, I've created multiple Basic endpoints for TextDataEnglish that could help with indexing or basic text searching. The most interesting endpoint is the POST request that searches Japanese AND English text.

curl -X 'POST' \
  'https://www.tracenacademy.com/api/BasicTextDataEnglish' \
  -H 'accept: text/plain' \
  -H 'Content-Type: application/json' \
  -d '{
  "searchQuery": "Special Week",
  "isEnglishQuery": true
}'

curl -X 'POST' \
  'https://www.tracenacademy.com/api/BasicTextDataEnglish' \
  -H 'accept: text/plain' \
  -H 'Content-Type: application/json' \
  -d '{
  "searchQuery": "スペシャルウィーク",
  "isEnglishQuery": false
}'

Initial Setup

Under UmaMusumeAPI/Properties/launchSettings.json, set the MARIA_CONNECTION_STRING environment variable to your MariaDB database for "development" and on the hosting site's config variables section for "release".

Simplified launchSettings.json Example:

"profiles": {
  "IIS Express": {
    "environmentVariables": {
      "ASPNETCORE_ENVIRONMENT": "Development",
      "MARIA_CONNECTION_STRING": "user id=;password=;host=;database=;character set=utf8mb4"
    }
  }
}

Database Setup

Use the scripts in UmaMusumeAPI/SqlScripts to generate everything you need for the database. As mentioned before, if you want to load all of the data from the DMM version of this game, use my loader app and point the connection string to your new database.

Make sure the MariaDB database and all of its objects have the character set of utf8mb4 and collation of utf8mb4_general_ci as that is the official UTF-8 specification. There are not only so many articles on this topic, but the devs from the Pomelo.EntityFrameworkCore.MySql project recommends this personally and in this issue. I'm using their EF Core library to help scaffold the models and controllers since it's far more active and stable than the Oracle equivalent.

Scaffold Commands

In case if you need to scaffold anything, here are some commands that may be useful

Models and DbContext

This is a single-line command using the "Package Manager Console" in Visual Studio that allows you to generate ALL of the models and the DbContext class.

Scaffold-DbContext 'User Id=;Password=;Host=;Database=;Character Set=utf8mb4' Pomelo.EntityFrameworkCore.MySql -OutputDir Models -ContextDir Context

If you only need the model and context of a SINGLE table, here's the single-line command for that.

Scaffold-DbContext 'User Id=;Password=;Host=;Database=;Character Set=utf8mb4' Pomelo.EntityFrameworkCore.MySql -OutputDir Models -ContextDir Context -T <TABLE_NAME_HERE>