如何为 PHP/MySQL 应用程序自动迁移(架构和数据)

2021-12-26 00:00:00 deployment migration php mysql

我有一个 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?

推荐答案

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

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

相关文章