Engine - Database - Schema

[ollieread]

TheGamePanel requires an abstraction of the database schema so that migrations for the creation and subsequent updating of the database.

Features

The following are features that the schema migration subcomponent should have.

Schema Abstraction

It should contain an easy-to-use abstraction of the database schema, taking an object-first approach, but also allowing to fall back to raw SQL where needed.

Migration System

It should also contain a system that can run a series of migrations against the database. Migrations should be broken into 3 steps, where each step for every migration is run together before progressing to the next. These steps are:

• Schema - For the creation and modification of tables, etc.
• Relations - For the specifying of foreign keys and other relation-based things.
• Data - For modifying actual data.

When migrating, these are run as schema, relations and data. When rolling back a migration, they are run in reverse as data, relations, and `schema. Migrations are split into these 3 steps so that modules may do things like to create database tables that have a foreign key to another modules' database table, without enforcing a specific order to the migrations.

Migrations will have an up and down, to allow for making the changes and rolling them back. Migration status also needs to be stored.