lucinda/migrations

Lightweight PHP7.1 library for performing cross-environment migrations

v2.0.4 2022-12-25 17:51 UTC

Requires

• php: ^8.1
• lucinda/console: ~2.0

Requires (Dev)

• lucinda/unit-testing: ^2.0

MIT

• Lucian Popescu

• migrations

README

Table of contents:

• About
  • Methodology Used
  • Implementation
• Installation
  • Setting Cache
  • Execution
• Console Commands
  • generate
  • migrate
  • up
  • down
• Reference Guide
  • Cache
  • Script
  • Result
  • Status
  • Wrapper
  • ConsoleExecutor

About

This API serves as platform able to automate data migration between development environments (eg: changes in database structure) useful in systems powered by continuous integration/delivery. Modeled on Symfony DoctrineMigrationsBundle operations, it performs its task in a starkly different way:

• like other Lucinda libraries, it is built with simplicity, flexibility and efficiency in mind (thus light weight as an effect). It is completely independent, not bundled to any library or framework, thus can be used in any PHP context whether Lucinda Framework is used or not
• it works like a skeleton onto which various migration patterns need to be built on (eg: SQL tables migration). This means it doesn't assume by default that database is to be migrated: anything subject of being synchronized between development environments falls subject to migrations

Methodology Used

When developing this API I asked myself: what do all migrations, regardless of what they migrate, have in common? Following aspects came to my mind:

• a migration should a class able to execute one or more related operations on multiple environments
• class operations should be transactional, meaning it must have COMMIT/ROLLBACK actions
• API needs an ability to generate a migration class on developer request
• developer must program generated migration (eg: with queries)
• API needs an ability to find and execute migrations.
• API needs an ability to track migrations progress across environments
• tracking requires assigning every migration class to a status: PENDING, FAILED, PASSED
• once developer executed migrations on his environment and made sure all is fine, he must commit it (eg: to GIT)
• other developers pull new changes, see that new migrations were added, then execute migrations to keep their environment updated
• continuous integration/deployment system (eg: TeamCity) will automatically execute migrations on every build, thus keeping all environments up to date

This API only performs the logistics for API-level operations above without taking any assumption on:

• what is the subject of migrations (eg: is it SQL table related?)
• what is the storage medium of tracking migrations progress (eg: is it SQL table?)

Migration operations supported will be:

• generating migration
• running all migrations (whose status is PENDING or FAILED), which equates to a global commit on each
• committing individual migration (whose status is PENDING or FAILED)
• rolling back individual migration (whose status is PASSED)

Note that unlike DoctrineMigrationsBundle, diff and dump-schema operations will not be supported by default because they make both subject and implementation assumption (that SQL database is subject and that DAO implementation uses ORM model).

Implementation

True to its goal of creating a migration skeleton for specialization to be built upon, this API only defines common logistics:

• Cache: blueprint for structure where migration progress is saved and tracked
• Script: blueprint for a migration class supporting commit/rollback operations
• Result: class that implements results of a migration (class operation) execution
  • Status: enum that collects possible migration statuses (PENDING, PASSED, FAILED)
• Wrapper: class that binds all four above in order to find and execute migration operations
• ConsoleExecutor: class that envelopes Wrapper to display migration operation results on console.

API is fully PSR-4 compliant, only requiring PHP 8.1+ interpreter and Console Table API (for displaying migration results on console). All classes inside belong to namespace Lucinda\Migration! To quickly see how it works, check:

• installation: downloading API using composer, creating folder to store migrations into
• setting cache: setting up a Cache that stores migration progress
• execution: using Wrapper to run migrations or ConsoleExecutor to display results in console and one or more migration Scripts

To insure reliability, API has been fully unit tested using Unit Testing API, as you can see in tests folder. To run unit tests by yourselves, run these commands:

cd vendor/lucinda/migrations
php test.php

Installation

To install this API, go to your project root and run:

composer require lucinda/migrations

Then create a migrations.php script that will execute migrations:

require(__DIR__."/vendor/autoload.php");

// defines folder to store migrations and creates it if not exists
$folder = "migrations";
if (!file_exists($folder)) {
  mkdir($folder);
}

// TODO: instance a Lucinda\Migration\Cache into $cache variable

// run migrations based on console input
$executor = new Lucinda\Migration\ConsoleExecutor($folder, $cache);
$executor->execute((isset($argv[1])?$argv[1]:"migrate"), (isset($argv[2])?$argv[2]:""));

Setting Cache

Implementing migration Cache is outside the scope of skeleton API, which makes no assumption on what is the subject of migrations or how should cache be stored (eg: could be MySQL, could be Amazon DynamoDB).

An example of a Cache implementation binding to SQL Data Access API , using a MySQL table to store info:

class TableCache implements \Lucinda\Migration\Cache
{    
    private $tableName;

    public function __construct(string $tableName)
    {
        $this->tableName = $tableName;
    }

    public function exists(): bool
    {
        return !empty(SQL("SHOW TABLES LIKE '".$this->tableName."'")->toRow());
    }

    public function create(): void
    {
        SQL("
        CREATE TABLE ".$this->tableName."
        (
        id INT UNSIGNED NOT NULL AUTO_INCREMENT,
        class_name VARCHAR(255) NOT NULL,
        is_successful BOOLEAN NOT NULL DEFAULT TRUE,
        date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY(id),
        UNIQUE(class_name)
        ) Engine=INNODB");
    }

    public function read(): array
    {
        return SQL("SELECT class_name, IF(is_successful=0,2,3) AS status FROM ".$this->tableName."")->toMap("class_name", "status");
    }

    public function add(string $className, int $statusCode): void
    {
        $isSuccessful = ($statusCode==\Lucinda\Migration\Status::PASSED);
        $results = SQL("UPDATE ".$this->tableName." SET is_successful=:status, date=NOW() WHERE class_name=:name", [
            ":status"=>$isSuccessful,
            ":name"=>$className            
        ])->getAffectedRows();
        if ($results == 0) {
            SQL("INSERT INTO ".$this->tableName." SET is_successful=:status, class_name=:name", [
                ":status"=>$isSuccessful,
                ":name"=>$className
            ]);
        }
    }

    public function remove(string $className): void
    {
        SQL("DELETE FROM ".$this->tableName." WHERE class_name=:name", [":name"=>$className]);
    }
}

Code for SQL function used above:

function SQL(string $query, array $boundParameters = array()): \Lucinda\SQL\StatementResults
{
    $preparedStatement = \Lucinda\SQL\ConnectionSingleton::getInstance()->createPreparedStatement();
    $preparedStatement->prepare($query);
    return $preparedStatement->execute($boundParameters);
}

Execution

Once a Cache is implemented, you can complete migration script and perform migrations. Example using the two classes in previous section:

require(__DIR__."/vendor/autoload.php");

// loads dependencies that bind Cache and Script to SQL Data Access API
require(__DIR__."/TableCache.php");
require(__DIR__."/SQL.php");

// defines folder to store migrations and creates it if not exists
$folder = "migrations";
if (!file_exists($folder)) {
  mkdir($folder);
}

// sets up Lucinda SQL Data Access API for current development environment based on XML
new Lucinda\SQL\Wrapper(simplexml_load_file("xml/servers.xml"), getenv("ENVIRONMENT"));

// run migrations based on console input, saving cache to SQL table "migrations"
$executor = new Lucinda\Migration\ConsoleExecutor($folder, new TableCache("migrations"));
$executor->execute((isset($argv[1])?$argv[1]:"migrate"), (isset($argv[2])?$argv[2]:""));

Above example will work if:

• an environment variable called "ENVIRONMENT" is set already, whose value is your development environment name
• mysql connection credentials are set for current development environment as required in configuration section of SQL Data Access API
• current database user has full rights on target schema (incl. CREATE). If not, run code @ create method manually!

You can now go to folder where API was installed and execute generate commands, go to migrations folder and fill up/down methods accordingly. Once at least one migration Script is filled, you will be able to execute migrations from console.

To maximize flexibility, developers that do not want to use console output provided by ConsoleExecutor can work with Wrapper directly and handle output by themselves.

Console Commands

API allows following console commands:

How does generate command work

This will generate a migration Script class in migrations folder. Open that class and fill up and down methods with queries, as in this example:

class Version20210205105634 implements \Lucinda\Migration\Script
{
  public function up(): void
  {
    SQL("
      CREATE TABLE test(
      id INT UNSIGNED NOT NULL AUTO_INCREMENT,
      primary key(id)
      ) ENGINE=INNODB
      ")
  }
  public function up(): void
  {
    SQL("DROP TABLE test");
  }
}

Example:

> php migrations.php generate

This will output name of Script created in migrations folder whose up/down methods you must fill.

How does migrate command work

When migrate is ran, API will locate all Scripts classes in migrations folder and match each to Cache:

• if found in Cache and migration Status is PASSED: up is skipped (since it ran already)
• otherwise: up is ran

If up throws no Throwable, migration is saved in Cache with PASSED status. Otherwise it's saved with FAILED status and Throwable message will be shown to caller in results summary.

Example:

> php migrations.php migrate

The end will be a console table with following columns:

• Script class name
• Status of up method execution
• error message, if Script threw a Throwable

How does up command work

When up (commit) command is ran, API will first check if Script name received as 2nd argument exists on disk in migrations folder:

• if found on disk
  • if found in Cache and migration Status is PASSED: up is skipped (since it ran already)
  • otherwise: up is ran
• otherwise: program exits with error

Example:

> php migrations.php up Version20210205105634

The end result will be a console table with following columns:

• Status of Script's up method execution
• error message, if Script threw a Throwable

How does down command work

When down (rollback) command is ran, API will first check if Script name received as 2nd argument exists on disk in migrations folder:

• if found on disk
  • if found in Cache and migration Status is PASSED: down is ran
  • otherwise: down is skipped
• otherwise: program exits with error

Example:

> php migrations.php down Version20210205105634

The end result will be a console table with following columns:

• Status of Script's up method execution
• error message, if Script threw a Throwable

Reference Guide

This guide includes all classes, enums and interfaces used by API.

Cache

Interface Lucinda\Migration\Cache defines operations a cache that saves migration Lucinda\Migration\Scripts execution progress must implement. It defines following methods:

Script

Interface Lucinda\Migration\Script defines operations a migration script must implement, corresponding to following methods:

In case an error has occurred, methods are expected to bubble a Throwable, which will inform API that they ended with an error. Following recommendations apply:

• if you need multiple operations inside up/down, they MUST be transactioned (to insure data integrity)
• ideally, a migration SHOULD perform a single operation (to prevent conflicts)

Result

Class Lucinda\Migration\Result encapsulates results of a Lucinda\Migration\Script execution. Following public methods are relevant for developers:

Generally you won't need to use this class unless you're building your own results displayer on top of Wrapper!

Status

Enum Lucinda\Migration\Status contains list of migration execution Result statuses:

Wrapper

Class Lucinda\Migration\Wrapper performs API task of creating, locating and executing migration Scripts, updating Cache as a result. Following public methods are defined:

To increase reusability, API makes no assumptions how results will be displayed so this class is strictly a model on whom various displayers may be built!

ConsoleExecutor

Class Lucinda\Migration\ConsoleExecutor makes the assumption you will like to envelop Wrapper methods so their results are displayed on console/terminal using commands that map them (see more). It comes with following public methods:

When execute method is used:

• $operation value MUST correspond to a Wrapper method name (minus constructor)
• $className value MUST be present only IF $operation is up / down