PHP数据迁移与版本控制工具

PHP数据迁移与版本控制工具

数据库迁移是管理数据库结构变更的标准方式。每次修改都记录在迁移文件中，团队按顺序执行。今天说说PHP中数据迁移的实现。

迁移管理器的实现。

```php
class Migration
{
protected PDO $pdo;

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

public function up(): void {}
public function down(): void {}
}

class MigrationRunner
{
private PDO $pdo;
private string $migrationDir;
private string $table = 'migrations';

public function __construct(PDO $pdo, string $migrationDir)
{
$this->pdo = $pdo;
$this->migrationDir = rtrim($migrationDir, '/');
$this->initTable();
}

private function initTable(): void
{
$this->pdo->exec("
CREATE TABLE IF NOT EXISTS {$this->table} (
id INT AUTO_INCREMENT PRIMARY KEY,
migration VARCHAR(255) NOT NULL,
batch INT NOT NULL,
executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
");
}

public function migrate(): void
{
$executed = $this->getExecuted();
$files = $this->getMigrationFiles();
$batch = $this->getNextBatch();
$pending = array_diff($files, $executed);

if (empty($pending)) {
echo "所有迁移已完成\n";
return;
}

foreach ($pending as $file) {
echo "执行: $file\n";
$migration = $this->loadMigration($file);

try {
$this->pdo->beginTransaction();
$migration->up();
$this->record($file, $batch);
$this->pdo->commit();
echo " 完成\n";
} catch (Exception $e) {
$this->pdo->rollBack();
echo " 失败: {$e->getMessage()}\n";
}
}
}

public function rollback(): void
{
$lastBatch = $this->getLastBatch();
if ($lastBatch === 0) {
echo "没有可回滚的迁移\n";
return;
}

$migrations = $this->getBatchMigrations($lastBatch);
foreach (array_reverse($migrations) as $file) {
echo "回滚: $file\n";
$migration = $this->loadMigration($file);
$migration->down();
$this->remove($file);
}
}

private function getExecuted(): array
{
return $this->pdo->query("SELECT migration FROM {$this->table}")->fetchAll(PDO::FETCH_COLUMN);
}

private function getMigrationFiles(): array
{
$files = glob($this->migrationDir . '/*.php');
sort($files);
return array_map('basename', $files);
}

private function getNextBatch(): int
{
return (int)$this->pdo->query("SELECT COALESCE(MAX(batch), 0) FROM {$this->table}")->fetchColumn() + 1;
}

private function getLastBatch(): int
{
return (int)$this->pdo->query("SELECT COALESCE(MAX(batch), 0) FROM {$this->table}")->fetchColumn();
}

private function getBatchMigrations(int $batch): array
{
$stmt = $this->pdo->prepare("SELECT migration FROM {$this->table} WHERE batch = ? ORDER BY id ASC");
$stmt->execute([$batch]);
return $stmt->fetchAll(PDO::FETCH_COLUMN);
}

private function loadMigration(string $file): Migration
{
require_once $this->migrationDir . '/' . $file;
$className = pathinfo($file, PATHINFO_FILENAME);
$className = $this->formatClassName($className);
return new $className($this->pdo);
}

private function formatClassName(string $name): string
{
preg_match('/^\d{4}_\d{2}_\d{2}_\d{6}_(.+)$/', $name, $matches);
if (isset($matches[1])) {
return implode('', array_map('ucfirst', explode('_', $matches[1])));
}
return $name;
}

private function record(string $file, int $batch): void
{
$this->pdo->prepare("INSERT INTO {$this->table} (migration, batch) VALUES (?, ?)")->execute([$file, $batch]);
}

private function remove(string $file): void
{
$this->pdo->prepare("DELETE FROM {$this->table} WHERE migration = ?")->execute([$file]);
}
}
?>

迁移文件示例。

```php
// 2024_01_01_000001_create_users_table.php
class CreateUsersTable extends Migration
{
public function up(): void
{
$this->pdo->exec("
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
status VARCHAR(20) DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
");
}

public function down(): void
{
$this->pdo->exec("DROP TABLE IF EXISTS users");
}
}
?>
```

迁移系统的核心是记录已执行的迁移，确保每个迁移只执行一次。回滚功能可以撤销之前的变更。团队协作时，按顺序执行迁移保持数据库结构一致。