superlink/tools/migrate.php

<?php
/**
 * 数据库迁移执行器（批次1「迁移版本管理」基座）
 *
 * 用法：
 *   php tools/migrate.php          # 等同 up
 *   php tools/migrate.php up       # 顺序执行 sql/migration_*.sql 中未应用的迁移
 *   php tools/migrate.php status   # 列出已应用 / 待应用
 *   php tools/migrate.php down <version>  # 移除 v<=version 的版本记录（仅记录，SQL 不自动回滚）
 *
 * 规则：
 *   - 迁移文件命名 sql/migration_v1.0.XX.sql，按版本号升序执行（natsort）
 *   - 版本表 schema_versions 由 sql/schema_versions.sql 引导建表
 *   - 每个迁移记录 version + 文件 md5（checksum），已应用文件被改动会告警并跳过
 *   - 只支持正向迁移（down 仅为运维便利，SQL 变更需手工回滚）
 */

// 命令行运行保护
if (PHP_SAPI !== 'cli') {
    fwrite(STDERR, "[错误] 请通过命令行运行：php tools/migrate.php\n");
    exit(1);
}

$cfg = require __DIR__ . '/../config/database.php';
$dsn = sprintf('mysql:host=%s;port=%d;dbname=%s;charset=%s', $cfg['host'], $cfg['port'], $cfg['dbname'], $cfg['charset']);
try {
    $pdo = new PDO($dsn, $cfg['username'], $cfg['password'], [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);
} catch (PDOException $e) {
    fwrite(STDERR, "[错误] 数据库连接失败：{$e->getMessage()}\n");
    exit(1);
}

$sqlDir    = __DIR__ . '/../sql';
$schemaSql = file_get_contents($sqlDir . '/schema_versions.sql');

/** 建版本表（不存在才建） */
function ensureSchemaTable(PDO $pdo, $schemaSql)
{
    try {
        $pdo->query("SELECT COUNT(*) FROM schema_versions");
    } catch (PDOException $e) {
        $pdo->exec($schemaSql);
        echo "[初始化] 已创建 schema_versions 表\n";
    }
}

/** 读取已应用记录：version => checksum */
function appliedVersions(PDO $pdo)
{
    $map = [];
    foreach ($pdo->query("SELECT version, checksum FROM schema_versions")->fetchAll() as $r) {
        $map[$r['version']] = $r['checksum'];
    }
    return $map;
}

/** 扫描迁移文件：返回 [version => absolutePath] 升序 */
function migrationFiles($sqlDir)
{
    $files = glob($sqlDir . '/migration_*.sql');
    natsort($files);
    $map = [];
    foreach ($files as $f) {
        $base = basename($f, '.sql');          // migration_v1.0.51
        $ver  = substr($base, strlen('migration_')); // v1.0.51
        $map[$ver] = $f;
    }
    return $map;
}

$action = $argv[1] ?? 'up';

switch ($action) {
    case 'up':
        ensureSchemaTable($pdo, $schemaSql);
        $applied = appliedVersions($pdo);
        $files   = migrationFiles($sqlDir);
        $pending = 0;
        foreach ($files as $ver => $path) {
            $sum = md5_file($path);
            if (isset($applied[$ver])) {
                if ($applied[$ver] !== $sum) {
                    fwrite(STDERR, "[警告] {$ver} 已应用但文件被改动（checksum 不一致），已跳过。如需重跑请先手动清理 schema_versions。\n");
                }
                continue;
            }
            $pending++;
            echo "[迁移] 应用 {$ver} ... ";
            try {
                $pdo->exec(file_get_contents($path));
                $ins = $pdo->prepare("INSERT INTO schema_versions (version, applied_at, checksum) VALUES (?, NOW(), ?)");
                $ins->execute([$ver, $sum]);
                echo "OK\n";
            } catch (PDOException $e) {
                fwrite(STDERR, "失败：{$e->getMessage()}\n");
                exit(1);
            }
        }
        if ($pending === 0) {
            echo "数据库已是最新版本（共 " . count($applied) . " 个迁移）。\n";
        } else {
            echo "完成，本次应用 {$pending} 个迁移。\n";
        }
        break;

    case 'status':
        ensureSchemaTable($pdo, $schemaSql);
        $applied = appliedVersions($pdo);
        $files   = migrationFiles($sqlDir);
        echo str_pad('版本', 12) . str_pad('状态', 10) . "文件\n";
        echo str_repeat('-', 60) . "\n";
        foreach ($files as $ver => $path) {
            $state = isset($applied[$ver]) ? '已应用' : '待应用';
            printf("%-12s %-10s %s\n", $ver, $state, basename($path));
        }
        break;

    case 'down':
        $target = $argv[2] ?? '';
        if ($target === '') {
            fwrite(STDERR, "用法：php tools/migrate.php down <version>\n");
            exit(1);
        }
        ensureSchemaTable($pdo, $schemaSql);
        $del = $pdo->prepare("DELETE FROM schema_versions WHERE version >= ?");
        $del->execute([$target]);
        echo "已移除 version >= {$target} 的迁移记录（SQL 变更不会自动回滚，请自行处理）。\n";
        break;

    default:
        fwrite(STDERR, "未知命令：{$action}（支持 up / status / down <version>）\n");
        exit(1);
}