如何为 PHP/MySQL 应用程序自动迁移(模式和数据) [英] How to automate migration (schema and data) for PHP/MySQL application

问题描述

我有一个 PHP/MySQL 应用程序.我正在寻找一种自动方式升级应用程序背后的数据库.升级后我不需要兼容旧版本.

I have an application in PHP/MySQL. I am searching for an automated way upgrading database behind the application. I don't need to have the compatibility with older versions once it is upgraded.

我已阅读 jeff's 和 K.Scott Allen 的 文章.

I have read jeff's and K. Scott Allen's articles on this.

我仍然不确定如何为 PHP/MySQL 应用程序实现这一点.

I am still not sure how to implement this for a PHP/MySQL application.

有没有什么简单又好的流程呢?

Is there any simple and good process for this?

推荐答案

我有一个我使用的架构"对象 - 但你可以在没有类的情况下做同样的事情..

I have a "Schema" object that I use - but you could do the same without classes..

您要做的是创建一个db_schema_versions"表:

What you want to do is create a 'db_schema_versions' table:

CREATE TABLE db_schema_versions (
  `table` varchar(255) NOT NULL PRIMARY KEY, 
  `version` INT NOT NULL
)

在您的数据库可以跟踪它所在的版本之后 - 它可以自动进行 SQL 升级.

After your database can track what version # it is on - it can do SQL upgrades automatically.

您应该在升级架构时锁定您的架构表.这样您就不会同时有两个请求尝试升级您的架构.

You should lock your schema table while upgrading schema. This way you wont have two requests at the same moment trying to upgrade your schema.

所以 - 跟踪您要升级的版本 - 构建一个大交换机 - 像这样:

So - keep track of the version you are upgrading from - build a big switch - something like this:

class SNTrack_Db_Schema extends MW_Db_Schema_Abstract {
  protected $table = "sntrack_db_schema";
  protected $version = 5;

  protected function upgrade($fromVersion) {
    // don't break
    switch($fromVersion) {
      case 0:
        $this->db->query('CREATE TABLE sntrack_inbound_shipment (
            `id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
            `from` VARCHAR(255) NOT NULL,
            `date` DATE NOT NULL,
            `invoice` VARCHAR(255) NOT NULL,
            `notes` TEXT
          )');
        $this->setVersion(1);
      case 1:
        $this->db->query('ALTER TABLE sntrack_details ADD `shipment_id` INT');
        $this->db->query('ALTER TABLE sntrack_product ADD `inventory` INT NOT NULL DEFAULT 0');
        $this->db->query('CREATE TABLE sntrack_inventory_shipment (
            `shipment_id` INT NOT NULL,
            `product_id` INT NOT NULL,
            `qty` INT NOT NULL,
            PRIMARY KEY (`shipment_id`, `product_id`)
          )');
        $this->setVersion(2);
...etc

这篇关于如何为 PHP/MySQL 应用程序自动迁移(模式和数据)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！