PHP and SQLite: How to do database migrations and upgrades

PHP and SQLite: How to perform database migration and upgrade

Database migration and upgrade is a very common task when developing web applications. For developers using PHP and SQLite, this process may be more complicated. This article will introduce how to use PHP and SQLite for database migration and upgrade, and provide some code samples for reference.

1. Create SQLite database

First, we need to create a SQLite database. Using SQLite database is very convenient, we can create and operate the database directly in PHP code.

// 创建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);

2. Data migration

When we need to migrate the database, we may need to create new tables, modify the table structure, or import data. Below is a simple example showing how to create a new table.

// 创建新的表格
$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);

3. Database upgrade

When we need to upgrade the database, we may need to add new fields, modify field types, or delete fields. Below is an example showing how to change the type of a field from VARCHAR to TEXT.

// 将字段类型从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);

In this example, we first rename the old table to users_old, then create a new users table and import data from the old table. Finally, we delete the old table.

4. Database version management

In order to better manage database migration and upgrade, we can use version control management. We can create a version table and update the version number after every migration or upgrade.

// 创建版本表格
$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)");
}

In this example, we first create a version table. Then, we get the current version number and execute the corresponding migration or upgrade code based on the current version number. Finally, we update the version number.

Summary

Database migration and upgrade using PHP and SQLite can be complicated. However, with proper planning and code organization, we can easily manage the database migration and upgrade process. Hopefully the sample code provided in this article will help you better understand and apply these concepts.

The above is the detailed content of PHP and SQLite: How to do database migrations and upgrades. For more information, please follow other related articles on the PHP Chinese website!