PHP 및 SQLite: 데이터베이스 마이그레이션 및 업그레이드 방법

// 创建SQLite数据库文件
$databaseFile = 'database.sqlite';

// 创建数据库连接
$pdo = new PDO('sqlite:' . $databaseFile);

// 创建表格
$createTableSql = "CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(100),
    email VARCHAR(100)
)";
$pdo->exec($createTableSql);

// 创建新的表格
$createTableSql = "CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(100),
    price DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";
$pdo->exec($createTableSql);

// 将字段类型从VARCHAR修改为TEXT
$alterTableSql = "ALTER TABLE users
                  RENAME TO users_old";
$pdo->exec($alterTableSql);

$createTableSql = "CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    email VARCHAR(100)
)";
$pdo->exec($createTableSql);

$insertDataSql = "INSERT INTO users (name, email)
                  SELECT name, email FROM users_old";
$pdo->exec($insertDataSql);

$dropTableSql = "DROP TABLE users_old";
$pdo->exec($dropTableSql);

// 创建版本表格
$createTableSql = "CREATE TABLE IF NOT EXISTS migrations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    version INTEGER,
    migrated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";
$pdo->exec($createTableSql);

// 获取当前版本号
$currentVersion = 0;
$query = $pdo->query("SELECT MAX(version) FROM migrations");
$result = $query->fetch();
if ($result !== false) {
    $currentVersion = $result[0];
}

// 迁移到下一个版本
$nextVersion = $currentVersion + 1;
if ($nextVersion === 1) {
    // 将字段类型从VARCHAR修改为TEXT
    // 升级代码...
    
    // 更新版本号
    $pdo->exec("INSERT INTO migrations (version) VALUES ($nextVersion)");
}

// 迁移到下一个版本
$nextVersion = $currentVersion + 1;
if ($nextVersion === 2) {
    // 添加新的表格
    // 升级代码...
    
    // 更新版本号
    $pdo->exec("INSERT INTO migrations (version) VALUES ($nextVersion)");
}