Taeluf.com

LilMigrations.php

<?php

namespace Tlf;

/**
 * A minimal class for handling sql migration. Create a migrations dir. Then create dirs like `v1`, `v2` & create files `up.sql`, `down.sql` in each versioned dir. Migrating from 1 to 2 will execute `v2/up.sql`. From 3 down to 1 will execute `v2/down.sql` and `v1/down.sql`. You may also make files like `v1/up-1.sql`, `v1/up-2.sql` to execute multiple files in order.
 *
 * You can create `.config/ldb.json` file with the relative `'dir'` and `'db'` settings
 *
 * @tagline Easy to use SQL Migrations from versioned directories
 */
class LilMigrations {

    /**
     * a pdo instance
     */
    public \PDO $pdo;

    /**
     * the dir for migrations scripts.
     */
    public string $dir;

    /**
     * array<string, mixed> of variables to `extract` and make available to `up.php` and `down.php` migration files.
     */
    public array $migration_vars = [];

    /**
     * In $dir, there should be directories named 'v1', 'v2', 'v3', and so on.
     * In the v1/v2/v3 dirs, there should be up.sql & down.sql files with valid SQL statements for whatever database you're using
     *
     * @param $pdo a pdo instance
     * @param $dir a directory path
     */
    public function __construct(\PDO $pdo, string $dir){
        $this->pdo = $pdo;
        $this->dir = $dir;
    }

    /**
     * Convenience method to initialize sqlite db in memory
     * @return Tlf\LilDb
     */
    static public function sqlite(string $dbName = ':memory:'){
        $pdo = new \PDO('sqlite:'.$dbName);
        $pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
        $ldb = new static($pdo);
        return $ldb;
    }


    /**
     *
     * Migrate from old version to new
     *
     * @param $old the current version of the database
     * @param $new the new version of the database to go to
     */
    public function migrate(int $old, int $new){

        if ($old < $new){
            for ($i=$old+1; $i <= $new; $i++){
                $this->run_migration_version($i, 'up');
            }
        } else if ($old > $new) {
            for ($i=$old-1; $i >= $new; $i--){
                $this->run_migration_version($i, 'down');
            }
        } else {
            $this->run_migration_version($i, 'up');
        }
    }

    public function run_migration_version($version, $up_or_down){
        $i = $version;
        $file = $up_or_down;

        $v_dir = "v$i/";
        $migrate_dir = $this->dir.'/'.$v_dir;
        $files = is_dir($migrate_dir) ? scandir($migrate_dir) : false;
        if ($files==false){
            echo "\nMigrations dir $v_dir does not exist. Continuing.";
            return;
        }
        $files = array_filter($files, 
            function($v) use ($file){
                if (substr($v,0,strlen($file))==$file)return true;
                return false;
            });

        //@todo test the file sorting
        // // for testing
        // rsort($files);
        //
        // $files = ['up-9.sql', 'up-2.sql', 'up-13.sql', 'up-0.sql', 'up.sql', 'up-1.sql'];
        usort($files,
            function($v1, $v2){
                $pos1 = strpos($v1,'-');
                if ($pos1==false)$index1 = -1;
                else $index1 = (int)substr($v1,$pos1+1,-4);


                $pos2 = strpos($v2,'-');
                if ($pos2==false)$index2 = -1;
                else $index2 = (int)substr($v2,$pos2+1,-4);

                return $index1-$index2;
            }
        );

        foreach ($files as $f){
            $rel = $v_dir.$f;
            $exec_file = $this->dir.'/'.$rel;
            // $exec_file = $this->dir.'/'.$rel;

            $ext = pathinfo($exec_file, PATHINFO_EXTENSION);

            if ($ext=='php'){
                $migrate_vars = $this->migration_vars;
                extract($migrate_vars);
                $pdo = $this->pdo;
                require($exec_file);
                return;
            }

            if ($ext!='sql'){
                echo "\nFile '$rel' is not an sql file.";
                return;
            }
            if (!file_exists($exec_file)){
                echo "\nFile '$rel' was not found for migrations.";
                continue;
            }
            echo "\nExecute '$rel'";
            $sql = file_get_contents($exec_file);
            $this->pdo->exec($sql);

            if ($this->pdo->errorCode()!='00000'){
                echo "\nSQL Error in '$rel':\n";
                print_r($this->pdo->errorInfo());
                return;
            }
        }

    }
}