скрипт миграции БД для Mysql, Zend.

migration.php

<?php
// git://gist.github.com/868907.git
// Простейший механизм миграции для БД Mysql.
// В корне проекта добавляется папочка db, туда помещается этот скрипт и файлы миграций с шаблоном для имени: 20110314145307_действие_объект_контекст.sql, цифры в имени обеспечивают порядок применения.
// Понимает 4 команды: show, backup, apply, restore
// Базу модифицируют apply и restore, которые запускаются только после того как был сделан backup, не раньше чем час назад.
// Реквизиты доступа к бд должны находится в configs/common.ini (Zend)
// Запуск на выполнение производить из корня проекта: php db/migtation.php 
$params = parse_ini_file('configs/common.ini');
$migrations = new SimpleDbMigrations($params['params.host'], $params['params.username'], $params['params.password'], $params['params.dbname']);
$migrations->main($argv);

class SimpleDbMigrations {
  private $applied_migrations;
  private $migrations_in_folder;
  private $not_applied_migrations;
  private $host;
  private $user;
  private $pass;
  private $db;
  private $debug = false;

  function __construct($host, $user, $pass, $db) {
    $this->host = $host;
    $this->user = $user;
    $this->pass = $pass;
    $this->db = $db;
  }

  public function main($argv) {
    echo "\nSimple PHP DB migrations 0.0.1\n";
    echo "Author: vglebov (2010)\n\n";

    try {
      $this->migrations_in_folder = $this->get_migrations_in_folder();
      $this->applied_migrations = $this->get_applied_migrations();
      $this->not_applied_migrations = $this->detect_not_applied_migrations();

      if (count($argv) == 2 && $argv[1] == 'show') {
        $this->show_not_applied_migrations();
      } elseif (count($argv) == 2 && $argv[1] == 'apply') {
        $this->apply_not_applied_migrations();
      } elseif (count($argv) == 2 && $argv[1] == 'backup') {
        $this->backup_db();
      } elseif (count($argv) == 2 && $argv[1] == 'restore') {
        $this->restore_db();
      } else {
        $this->show_usage();
      }
    } catch (Exception $e) {
      echo $e->getMessage();
      echo "\n\n";
      $this->show_usage();
    }
  }

  function show_not_applied_migrations() {
    $count = count($this->not_applied_migrations);
    if ($count == 0) {
      echo "There is no not applied migrations.\n\n";
      return;
    } elseif ($count == 1) {
      echo "There is one not applied migration:\n";
    } else {
      echo "There are {$count} not applied migrations:\n";
    }
    foreach ($this->not_applied_migrations as $id) {
      $migration = $this->get_migration_from_file_by_id($id);
      echo sprintf("%s_%s\n", $migration['id'], $migration['title']);
    }
    echo "\nFor apply all this migrations run: php migrations.php apply\n\n";
  }

  function apply_not_applied_migrations() {
    $count = count($this->not_applied_migrations);
    if ($count == 0) {
      echo "There is no not applied migrations.\n\n";
      return;
    }
    if ($this->is_db_backup_old()) {
      $this->show_do_backup_before_apply();
      return;
    }
    $report = $this->do_apply();
    $this->show_applied_report($report);
    $this->show_you_can_restore();
  }

  function backup_db() {
    $time = time();
    if (file_exists('db/backup.sql')) {
      $this->system("mv db/backup.sql db/backup-$time.sql", $return_var);
    }
    $this->system("mysqldump -h {$this->host} -u \"{$this->user}\" -p\"{$this->pass}\" \"{$this->db}\" > db/backup.sql", $return_var);
    if ($return_var == 0) {
      echo "Backup created successfully in db/backup.sql.\n\n";
    } else {
      echo "Backup failed: mysql returned code {$return_var}\n\n";
    }
  }

  function restore_db() {
    if (file_exists('db/backup.sql')) {
      $this->drop_tables();
      echo "Restore from db/backup.sql\n";
      $this->system("mysql -h {$this->host} -u \"{$this->user}\" -p\"{$this->pass}\" \"{$this->db}\" < db/backup.sql", $return_var);
      if ($return_var == 0) {
        echo "Restored successfully.\n\n";
      } else {
        echo "Restore failed: mysql returned code {$return_var}\n\n";
      }
    } else {
      echo "Restore failed: can't find file db/backup.sql\n\n";
    }
  }

  function get_migrations_in_folder() {
    $ls = `ls db/*.sql`;
    if (!$ls) {
      throw new Exception("Can't open migration folder");
    }
    $migrations = array();
    $files = split("\n", $ls);
    foreach ($files as $file) {
      if (preg_match("/(?P<id>\d+)\D(?P<title>.*?)\.sql$/", $file, $parts)) {
        $migrations[] = $parts['id'];
      }
    }
    sort($migrations);
    return $migrations;
  }

  function get_applied_migrations() {
    $sql = 'select id from migrations order by id';
    $connection = mysql_connect($this->host, $this->user, $this->pass);
    if (!$connection) {
      throw new Exception(mysql_error());
    }
    mysql_select_db($this->db, $connection);
    $result = $this->mysql_query($sql, $connection);
    $res = array();
    if ($result) {
      while ($row = mysql_fetch_assoc($result)) {
        $res[] = $row['id'];
      }
    }
    mysql_close($connection);
    return $res;
  }

  function detect_not_applied_migrations() {
    return array_diff($this->migrations_in_folder, $this->applied_migrations);
  }

  function get_migration_from_file_by_id($id) {
    $files = split("\n", `ls db/*.sql`);
    foreach ($files as $file) {
      if (preg_match("/(?P<id>\d+)\D(?P<title>.*?)\.sql$/", $file, $parts)) {
        if ($parts['id'] == $id) {
          return array(
            'id' => $parts['id'],
            'title' => $parts['title'],
            'file' => $file,
            'content' => trim(file_get_contents($file))
          );
        }
      }
    }
  }

  function is_db_backup_old() {
    $modified_time = @filemtime('db/backup.sql');
    $current_time = time();
    return !$modified_time || $current_time - $modified_time > 3600;
  }

  function drop_tables() {
    $connection = mysql_connect($this->host, $this->user, $this->pass);
    mysql_select_db($this->db, $connection);
    $result = $this->mysql_query("show tables;", $connection);
    if ($result) {
      $this->mysql_query("SET FOREIGN_KEY_CHECKS = 0", $connection);
      try {
        while ($row = mysql_fetch_row($result)) {
          $table = $row[0];
          echo "Drop table {$table}\n";
          if (!$this->mysql_query("DROP TABLE IF EXISTS `{$table}`", $connection)) {
            throw new Exception(mysql_error());
          }
        }
      } catch (Exception $e) {
        $this->mysql_query("SET FOREIGN_KEY_CHECKS = 1", $connection);
        mysql_close($connection);
        throw $e;
      }
      $this->mysql_query("SET FOREIGN_KEY_CHECKS = 1", $connection);
    }
    mysql_close($connection);
  }

  function do_apply() {
    $report = array();
    foreach ($this->not_applied_migrations as $id) {
      $migration = $this->get_migration_from_file_by_id($id);
      echo sprintf("==apply %s_%s==\nexec SQL: %s", $migration['id'], $migration['title'], $migration['content']);
      echo "\n";
      $this->system("mysql -h {$this->host} -u \"{$this->user}\" -p\"{$this->pass}\" \"{$this->db}\" < \"{$migration['file']}\"", $return_var);
      if ($return_var == 0) {
        $connection = mysql_connect($this->host, $this->user, $this->pass);
        mysql_select_db($this->db, $connection);
        if ($this->mysql_query("INSERT INTO migrations (id, title) VALUES ({$migration['id']}, '{$migration['title']}');", $connection)) {
          echo "==migration applied==\n";
          $report[$id] = 'Applied';
        } else {
          $err = mysql_error();
          echo "==not applied: {$err}==\n";
          $report[$id] = "Migration sql was executed successful but when it register in migration tables got error {$err}";
        }
        mysql_close($connection);
      } else {
        echo "==not applied: return code was {$return_var}==\n";
        $report[$id] = "Migration sql was failed";
      }
      echo "\n";
    }
    echo "\nApplying finished.\n\n";
    return $report;
  }

  function system($str, &$return_var) {
    $this->debug("--system: [{$str}]");
    $ret = system($str, $return_var);
    $this->debug(" code: [$return_var]\n");
    return $ret;
  }

  function mysql_query($query, $resource) {
    $this->debug("--sql: [{$query}]");
    $ret = mysql_query($query, $resource);
    $this->debug(" result: [{$ret}]\n");
    return $ret;
  }

  private function debug($string) {
    if($this->debug) {
      echo $string;
    }
  }

  function show_usage() {
    echo <<<END
# you must be in the root folder of your project

SYNOPSIS

php db/migrations.php show
php db/migrations.php backup
php db/migrations.php apply
php db/migrations.php restore\n\n
END;
  }

  function show_you_can_restore() {
    echo <<<END

Please check site functionality and revert DB if need. To revert run:
php db/migration/php restore\n\n
END;
  }

  function show_do_backup_before_apply() {
    echo <<< END
Before apply migrations you have to do backup. Call migration for backup:
php db/migrations.sql backup

Apply failed\n\n
END;
  }

  function show_applied_report($report) {
    foreach ($this->not_applied_migrations as $id) {
      $migration = $this->get_migration_from_file_by_id($id);
      echo sprintf("%s: %s_%s\n", $report[$id], $migration['id'], $migration['title']);
    }
  }
}