warehouse/framework/Database/Migration/SqliteMigration.php

<?php

namespace Framework\Database\Migration;

use Framework\Database\Connection\SqliteConnection;
use Framework\Database\Exception\MigrationException;
use Framework\Database\Migration\Field\Field;
use Framework\Database\Migration\Field\BoolField;
use Framework\Database\Migration\Field\DateTimeField;
use Framework\Database\Migration\Field\FloatField;
use Framework\Database\Migration\Field\IdField;
use Framework\Database\Migration\Field\IntField;
use Framework\Database\Migration\Field\StringField;
use Framework\Database\Migration\Field\TextField;

class SqliteMigration extends Migration
{
    protected SqliteConnection $connection;
    protected string $table;
    protected string $type;
    protected array $drops = [];

    public function __construct(SqliteConnection $connection, string $table, string $type)
    {
        $this->connection = $connection;
        $this->table = $table;
        $this->type = $type;
    }

    public function execute()
    {
        if ($this->type === 'create') {
            $this->executeCreate();
            return;
        }

        $hasAlteredField = array_reduce($this->fields, fn($carry, $field) => $carry || $field->alter, false);

        if (count($this->drops) > 0 || $hasAlteredField) {
            $this->executeRebuild();
            return;
        }

        $this->executeAdd();
    }

    private function executeCreate()
    {
        $fields = join(',' . PHP_EOL, array_map(fn($field) => $this->columnDefinition($field), $this->fields));

        $query = "
            CREATE TABLE \"{$this->table}\" (
                {$fields}
            );
        ";

        $this->connection->pdo()->prepare($query)->execute();
    }

    private function executeAdd()
    {
        $fields = join(';' . PHP_EOL, array_map(
            fn($field) => "ADD COLUMN {$this->columnDefinition($field)}",
            $this->fields
        ));

        $query = "
            ALTER TABLE \"{$this->table}\"
            {$fields};
        ";

        $this->connection->pdo()->prepare($query)->execute();
    }

    /**
     * SQLite can't alter or drop columns in place, so the standard approach is to
     * rebuild the table: create a new table with the desired schema, copy the
     * surviving data across, then swap it in for the original.
     */
    private function executeRebuild()
    {
        $pdo = $this->connection->pdo();

        $existingColumns = $pdo->query("PRAGMA table_info(\"{$this->table}\")")->fetchAll();

        $alteredFields = [];
        foreach ($this->fields as $field) {
            if ($field->alter) {
                $alteredFields[$field->name] = $field;
            }
        }

        $newFields = array_filter($this->fields, fn($field) => !$field->alter);

        $keptColumnNames = [];
        $definitions = [];

        foreach ($existingColumns as $column) {
            $name = $column['name'];

            if (in_array($name, $this->drops)) {
                continue;
            }

            $keptColumnNames[] = $name;

            $definitions[] = isset($alteredFields[$name])
                ? $this->columnDefinition($alteredFields[$name])
                : $this->existingColumnDefinition($column);
        }

        foreach ($newFields as $field) {
            $definitions[] = $this->columnDefinition($field);
        }

        $tempTable = "{$this->table}_migration_tmp";
        $definitionList = join(',' . PHP_EOL, $definitions);
        $columnList = join(', ', array_map(fn($name) => "\"{$name}\"", $keptColumnNames));

        $pdo->beginTransaction();

        $pdo->exec("CREATE TABLE \"{$tempTable}\" ({$definitionList})");
        $pdo->exec("INSERT INTO \"{$tempTable}\" ({$columnList}) SELECT {$columnList} FROM \"{$this->table}\"");
        $pdo->exec("DROP TABLE \"{$this->table}\"");
        $pdo->exec("ALTER TABLE \"{$tempTable}\" RENAME TO \"{$this->table}\"");

        $pdo->commit();
    }

    private function existingColumnDefinition(array $column): string
    {
        $name = $column['name'];
        $type = $column['type'];

        if ($column['pk'] == 1 && $type === 'INTEGER') {
            return "\"{$name}\" INTEGER PRIMARY KEY AUTOINCREMENT";
        }

        $definition = "\"{$name}\" {$type}";

        if ($column['notnull'] == 1) {
            $definition .= " NOT NULL";
        }

        if ($column['dflt_value'] !== null) {
            $definition .= " DEFAULT {$column['dflt_value']}";
        }

        return $definition;
    }

    private function columnDefinition(Field $field): string
    {
        if ($field instanceof BoolField) {
            $template = "\"{$field->name}\" INTEGER";

            if (!$field->nullable) {
                $template .= " NOT NULL";
            }

            if ($field->default !== null) {
                $default = (int) $field->default;
                $template .= " DEFAULT {$default}";
            }

            return $template;
        }

        if ($field instanceof DateTimeField) {
            $template = "\"{$field->name}\" TEXT";

            if (!$field->nullable) {
                $template .= " NOT NULL";
            }

            if ($field->default === 'CURRENT_TIMESTAMP') {
                $template .= " DEFAULT CURRENT_TIMESTAMP";
            } else if ($field->default !== null) {
                $template .= " DEFAULT '{$field->default}'";
            }

            return $template;
        }

        if ($field instanceof FloatField) {
            $template = "\"{$field->name}\" REAL";

            if (!$field->nullable) {
                $template .= " NOT NULL";
            }

            if ($field->default !== null) {
                $template .= " DEFAULT {$field->default}";
            }

            return $template;
        }

        if ($field instanceof IdField) {
            return "\"{$field->name}\" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE";
        }

        if ($field instanceof IntField) {
            $template = "\"{$field->name}\" INTEGER";

            if (!$field->nullable) {
                $template .= " NOT NULL";
            }

            if ($field->default !== null) {
                $template .= " DEFAULT {$field->default}";
            }

            return $template;
        }

        if ($field instanceof StringField || $field instanceof TextField) {
            $template = "\"{$field->name}\" TEXT";

            if (!$field->nullable) {
                $template .= " NOT NULL";
            }

            if ($field->default !== null) {
                $template .= " DEFAULT '{$field->default}'";
            }

            return $template;
        }

        throw new MigrationException("Unrecognised field type for {$field->name}");
    }

    public function dropColumn(string $name): static
    {
        $this->drops[] = $name;
        return $this;
    }
}