tinynote/bin/simple-db-migrator.php

<?php

/*
 * ॐ  Om Brahmarppanam  ॐ
 *
 * simple-db-migrator.php
 * Created at: Thu Jul 20 2022 19:34:40 GMT+0530 (GMT+05:30)
 *
 * Copyright 2022 Harish Karumuthil <harish2704@gmail.com>
 *
 * Use of this source code is governed by an MIT-style
 * license that can be found in the LICENSE file or at
 * https://opensource.org/licenses/MIT.
 */

require __DIR__ . '/../vendor/autoload.php';

\App\Utils\Env::loadDotEnv();

$DB = new \App\Utils\DB();

chdir(__DIR__ . '/../public');

$dbConf = [
    'dsn' => $DB->getConnection()->getDsn(),
    'user' => $DB->getConfig()->getUsername(),
    'password' => $DB->getConfig()->getPassword()
];
// $dbConf = [ "dsn" => "mysql:host=172.20.1.3;dbname=migrationtest", "user" => "root", "password" => "xxxxxx", ];
// $dbConf = [ "dsn" => "pgsql:host=172.17.0.2;dbname=migrationtest", "user" => "postgres", "password" => "xxxxxx", ];
$MIGRAION_ROOT = __DIR__ . '/../migrations';
$L;

class Logger
{
    private $levels = ['error', 'warning', 'info', 'log', 'debug'];

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

    private function _log($level, $args)
    {
        echo date('D M d, Y G:i') . " [$level] : " . implode(', ', $args) . "\n";
    }

    public function __call($name, $arguments)
    {
        $targetLevel = array_search($name, $this->levels);
        if ($targetLevel !== false && $targetLevel <= $this->level) {
            $this->_log($name, $arguments);
        }
    }
}

class MigrationItem
{
    /**
     * @param $v {int} Version number
     */
    public function __construct($v)
    {
        global $MIGRAION_ROOT;

        $this->v = $v;
        $this->upFile = sprintf('%s/up/%03d.sql', $MIGRAION_ROOT, $v);
        $this->downFile = sprintf('%s/down/%03d.sql', $MIGRAION_ROOT, $v);
    }

    public function getSQL($fname)
    {
        return file_get_contents($fname);
    }

    public function getUpSQL()
    {
        return $this->getSQL($this->upFile);
    }

    public function getDownSql()
    {
        return $this->getSQL($this->downFile);
    }
}

class Migrator
{
    public function __construct()
    {
        global $dbConf, $L;

        $this->db = new PDO($dbConf['dsn'], $dbConf['user'], $dbConf['password']);
        $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $this->L = $L;
    }

    private function runSQLTransaction($sql)
    {
        $this->L->debug('Runing SQL');
        $this->L->debug($sql);
        $res = $this->db->exec("BEGIN;\n" . $sql . "\nCOMMIT;");
    }

    /*
     * Get array of pending version numbers
     * @return int[]
     */
    public function getPendingMigrations()
    {
        $lastRanMigration = $this->getLastRanMigration();
        $availableMigrations = $this->getAvailableMigrations();

        if ($lastRanMigration == 0) {
            return $availableMigrations;
        }

        $lastMigrationIdx = array_search($lastRanMigration, $availableMigrations);

        if ($lastMigrationIdx === null) {
            throw new Exception(
                'Inconsistent state: Last migration is missing in filesystem'
            );
        }

        return array_slice($availableMigrations, $lastMigrationIdx + 1);
    }

    /*
     * Get array of available verion numbers
     * @return int[]
     */
    private function getAvailableMigrations()
    {
        global $MIGRAION_ROOT;

        $files = scandir("$MIGRAION_ROOT/up");
        $out = [];
        foreach ($files as $fname) {
            $match = [];
            $matches = preg_match('/^([0-9]*).sql$/', $fname, $match);
            if ($matches > 0) {
                $out[] = (int) $match[1];
            }
        }
        sort($out);

        return $out;
    }

    /*
     * @return int
     */
    private function getLastRanMigration()
    {
        try {
            $result = $this
                ->db
                ->query(
                    'SELECT version from db_migrations order by version desc limit 1',
                    PDO::FETCH_ASSOC
                )
                ->fetchAll();
        } catch (Exception $e) {
            $this->L->error("db_migrations table doesn't exists. Please run setup");
            throw $e;
        }
        if ($result) {
            return $result[0]['version'];
        }

        return 0;
    }

    public function runUp()
    {
        $this->L->warning('Running up');
        $pendingMigrations = $this->getPendingMigrations();
        $this->L->warning('Pending migrations ' . json_encode($pendingMigrations));
        foreach ($pendingMigrations as $migrationV) {
            $this->L->warning('Running migration ' . $migrationV);
            $migrationItem = new MigrationItem($migrationV);
            $sql = $migrationItem->getUpSQL();
            $this->runSQLTransaction($sql);
            $this
                ->db
                ->prepare(
                    'INSERT INTO db_migrations
             (version, created_at, up_sql, down_sql) VALUES (?, ?, ?, ?)'
                )
                ->execute([
                    $migrationV,
                    date('Y-m-d H:i:s'),
                    $sql,
                    $migrationItem->getDownSql(),
                ]);
        }
        $this->L->warning('executed all pending migrations');
    }

    public function setup()
    {
        try {
            $result = $this
                ->db
                ->query(
                    'SELECT version from db_migrations order by version desc limit 1',
                    PDO::FETCH_ASSOC
                )
                ->fetchAll();
        } catch (Exception $e) {
            $this->L->warning("db_migrations table doesn't exists.");
            $this->L->info('Creating db_migrations table ...');

            return $this->db->query('
      CREATE TABLE db_migrations (
        version int NOT NULL,
        created_at VARCHAR(20) DEFAULT NULL,
        up_sql text DEFAULT NULL,
        down_sql text DEFAULT NULL,
        PRIMARY KEY (version)
      )');
        }
        $this->L->warning('db_migrations table already exists. Skipping setup');
    }

    /*
     * @return string
     */
    private function getDownSqlFromDb($v)
    {
        $res = $this
            ->db
            ->query(
                "select down_sql from db_migrations where version = $v",
                PDO::FETCH_ASSOC
            )
            ->fetchAll();

        return $res[0]['down_sql'];
    }

    public function runDown()
    {
        $this->L->warning('Rolling back last migration ...');
        $lastRanMigration = $this->getLastRanMigration();
        if (!$lastRanMigration) {
            throw new Exception('There is no migration to rollback');
        }
        $this->L->warning("last migration is $lastRanMigration");
        $migrationItem = new MigrationItem($lastRanMigration);
        $downSqlFromDisk = $migrationItem->getDownSql();
        $downSqlFromDb = $this->getDownSqlFromDb($lastRanMigration);
        if ($downSqlFromDisk != $downSqlFromDb) {
            $this->L->error(
                'rollback sql stored in db does not match with the sql in filesystem'
            );
            $this->L->error('SQL from db');
            $this->L->error($downSqlFromDb);
            $this->L->error('SQL from filesystem');
            $this->L->error($downSqlFromDisk);
            $this->L->error('Please manually fix this error and run again');
            throw new Exception(
                'rollback sql stored in db does not match with the sql in filesystem'
            );
        }

        $this->runSQLTransaction($downSqlFromDisk);
        $this
            ->db
            ->prepare('DELETE FROM db_migrations WHERE version = ?')
            ->execute([$lastRanMigration]);
        $this->L->warning('Rollback completed');
    }

    function currentStatus()
    {
        $lastRanMigration = $this->getLastRanMigration();
        $this->L->warning("last migration is $lastRanMigration");
        $pendingMigrations = $this->getPendingMigrations();
        $this->L->warning('Pending migrations ' . json_encode($pendingMigrations));
    }
}

class Application
{
    public function __construct()
    {
        $this->opts = getopt('ldsvh', ['list', 'down', 'setup', 'verbose', 'help']);
        $this->logLevel = 1;
        $verbose = $this->getOption('verbose');
        if ($verbose) {
            if ($verbose === true) {
                $verbose = [$verbose];
            }
            $this->logLevel += count($verbose);
        }
    }

    private function getOption($name)
    {
        foreach ([$name[0], $name] as $k) {
            if (isset($this->opts[$k])) {
                return $this->opts[$k] === false ? true : $this->opts[$k];
            }
        }
    }

    function help()
    {
        echo 'Description:
  A simple database migration tool

Usage:
  php simple-db-migrator.php [options]

Options:
  -l, --list            Show the current status of applied migrations
  -s, --setup           Create db_migrations table in db and run all pending migrations
  -d, --down            Roll back last migration
  -h, --help            Display help for the given command. When no command is given display help for the db:migrate command
  -v|vv|vvv, --verbose  Increase the verbosity of messages: 1-3 => info,log,debug
';
    }

    function execute()
    {
        global $L;

        if ($this->getOption('help')) {
            return $this->help();
        }
        $L = new Logger($this->logLevel);
        $L->info('Starting migrator');
        $migrator = new Migrator();

        if ($this->getOption('list')) {
            $migrator->currentStatus();

            return;
        }

        if ($this->getOption('setup')) {
            $migrator->setup();
        }
        if ($this->getOption('down')) {
            $migrator->runDown();
        } else {
            $migrator->runUp();
        }

        return 0;
    }
}

(new Application())->execute();