如何自动化PHP / MySQL应用程序的迁移（架构和数据） [英] How to automate migration (schema and data) for PHP/MySQL application

问题描述

我在PHP / MySQL中有一个应用程序。我正在寻找在应用程序后面的自动化方式升级数据库。
升级后，我不需要与旧版本兼容。

我已经阅读了 jeff's and K。 Scott Allen的文章。

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

有没有一个简单而好的过程？

解决方案

我有一个Schema对象，使用 - 但是您可以在没有类的情况下执行相同操作。

您要做的是创建一个'$ code> db_schema_versions 'table：

  CREATE TABLE db_schema_versions（
`table` varchar（255）NOT NULL PRIMARY KEY，
`version` INT NOT NULL 
）
  

你的数据库可以跟踪什么版本＃是 - 它可以自动执行SQL升级。

您应该在升级模式时锁定模式表。这样你就不会在尝试升级你的架构的同一时刻有两个请求。

所以 - 跟踪你正在升级的版本 - 建立一个大的开关 - 一些东西像这样：

  class SNTrack_Db_Schema extends MW_Db_Schema_Abstract {
 protected $ table =sntrack_db_schema; 
 protected $ version = 5; 
 
保护功能升级（$ fromVersion）{
 //不要破坏
开关（$ 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`shipping_id` INT'）; 
 $ this-> db-> query（'ALTER TABLE sntrack_product ADD`inventory` INT NOT NULL DEFAULT 0'）; 
 $ this-> db-> query（'CREATE TABLE sntrack_inventory_shipment（
`shipping_id` INT NOT NULL，
`product_id` INT NOT NULL，
`qty` INT NOT NULL，
 PRIMARY KEY（`shipping_id`，`product_id`）
）'）; 
 $ this-> setVersion（2）; 
 ... etc 
  

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.

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

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..

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
)

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屋！