EthnaでMigrateできるか実験
概要
S2JDBC-Genを触ってみてPHPでも似たようなのあると便利だなーって思ってEthnaで実験してみました。
似たのないか探す
http://tech.li-pton.com/php/cakephp_migrate_1.html
CakePHP1.2でマイグレーションを使う 1
Cakeであった! でも安定版じゃない1.2用でまだまだ作成中かな?
基本構成を考えてみる
とりあえず最終的に使うものではないので、方針的には簡単に実験だけしてみます。実装はEthnaからませるためにEthnaコマンドで実装! DBはPEAR::DBを使うことにします。
事前準備
etc/****-iniでDBに接続できるように設定します。
DBの取得ともろもろの初期化
<?php $this->db =& $controller->getBackend()->getDB()->db; $this->db->setFetchMode(DB_FETCHMODE_ASSOC); switch( $this->db->phptype ){ case 'mysql': case 'mysqli': $this->db->query( "SET NAMES utf8" ); $create_table = $this->createSqlMysql(); break; }
とりあえずMySQLだけです。UTF-8でいいのかな? ここで指定するのも違う気がします(笑)
DBはEthna_DBだと細かい関数とかが使えないので実態を参照しています。
DDL作成
<?php function createSqlMysql(){ $create_table = ''; $table_list = $this->db->getTables(); foreach( $table_list as $table_name ){ $result = $this->db->getRow( 'SHOW CREATE TABLE ' . $table_name ); $create_table .= '-- ' . $result['Table'] . "\n"; $create_table .= $result['Create Table'] . "\n"; $create_table .= "\n"; } return $create_table; }
CakeのみたらDBの状態からがんばってDDLを生成していました。細かいインデックスとかコメントとか考えるとDB種別別に作ってそのリストから外れたらtableInfo()の情報から生成かな。
BASE/db/create_table.sqlを作成します。
データ抜き出し
<?php function writeData(){ $table_list = $this->db->getTables(); foreach( $table_list as $table_name ){ $path = $this->db_dir . "/" . $table_name . '.csv'; $fp = fopen( $path, "w" ); if( !$fp ){ printf("[warning] file creation failed [%s]\n", $path); continue; } // head出力 $tableInfo = $this->db->tableInfo($table_name); $row = array(); foreach( $tableInfo as $item ){ $row[] = $this->_escapeCSV( $item['name'] ); } $line = implode( ",", $row ) . "\n"; fwrite( $fp, $line, strlen($line) ); // データ $result = $this->db->query( 'SELECT * FROM ' . $table_name ); while( $row = $result->fetchRow()) { foreach( $row as $key => $item ){ $row[$key] = $this->_escapeCSV( $item ); } $line = implode( ",", $row ) . "\n"; fwrite( $fp, $line, strlen($line) ); printf("migrate successfully created [%s]\n", $path); } } }
BASE/db/****.csvを作成します。ここは全部のDBで共通で使えそうです。
CREATE TABLE文
-- mantis_bug_file_table CREATE TABLE `mantis_bug_file_table` ( `id` int(10) unsigned NOT NULL auto_increment, `bug_id` int(10) unsigned NOT NULL default '0', `title` varchar(250) NOT NULL default '', `description` varchar(250) NOT NULL default '', `diskfile` varchar(250) NOT NULL default '', `filename` varchar(250) NOT NULL default '', `folder` varchar(250) NOT NULL default '', `filesize` int(11) NOT NULL default '0', `file_type` varchar(250) NOT NULL default '', `date_added` datetime NOT NULL default '1970-01-01 00:00:01', `content` longblob, PRIMARY KEY (`id`), KEY `idx_bug_file_bug_id` (`bug_id`), KEY `idx_diskfile` (`diskfile`) ) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
こんな感じのが出力されます。
CSV
"id","user_id","bug_id","date_modified","field_name","old_value","new_value","type" "517","4","105","2008-08-13 11:46:21","handler_id","0","3","0" "3","4","2","2008-08-05 14:17:45",,,,"1" "4","4","3","2008-08-05 14:21:10",,,,"1" "5","4","3","2008-08-05 14:23:27",,,,"6" "6","4","3","2008-08-05 14:25:14",,,,"6" "7","4","4","2008-08-05 14:31:06",,,,"1" "8","4","3","2008-08-05 14:32:13",,,,"6" "9","4","5","2008-08-05 14:50:38",,,,"1" "10","4","6","2008-08-05 14:55:32",,,,"1"
1行目はカラム名で、2行目から””で囲まれたデータ列になります。
全文
<?php // vim: foldmethod=marker /** * Ethna_Plugin_Handle_Migrate.php * * @author akiraneko <akirainfoml at gmail.com> * @license http://www.opensource.org/licenses/bsd-license.php The BSD License * @package Ethna * @version $Id: Ethna_Plugin_Handle_Migrate.php 0 200x-xx-xx xx:xx:xx $ */ require_once ETHNA_BASE . '/class/Ethna_PearWrapper.php'; // {{{ Ethna_Plugin_Handle_Migrate /** * migrate handler * * @author akiraneko <akirainfoml at gmail.com> * @access public * @package Ethna */ class Ethna_Plugin_Handle_Migrate extends Ethna_Plugin_Handle { var $db; var $tmp_dir; var $db_dir; /** * clear cache files. * * @access public */ function perform() { $r =& $this->_getopt(array('basedir=', 'mode',)); if (Ethna::isError($r)) { return $r; } list($args,) = $r; $basedir = isset($args['basedir']) ? realpath(end($args['basedir'])) : getcwd(); $controller =& Ethna_Handle::getAppController($basedir); if (Ethna::isError($controller)) { return $controller; } $this->tmp_dir = $controller->getDirectory('tmp'); $this->db =& $controller->getBackend()->getDB()->db; $this->db->setFetchMode(DB_FETCHMODE_ASSOC); switch( $this->db->phptype ){ case 'mysql': case 'mysqli': $this->db->query( "SET NAMES utf8" ); $create_table = $this->createSqlMysql(); break; } $app_dir = $controller->getDirectory('app'); $this->db_dir = BASE . "/db"; $path = $this->db_dir . "/create_table.sql"; Ethna_Util::mkdir(dirname($path), 0755); if (file_exists($path)) { printf("file [%s] already exists -> skip\n", $path); } else if ($this->_fileWrite($path,$create_table) == false) { printf("[warning] file creation failed [%s]\n", $path); } else { printf("migrate successfully created [%s]\n", $path); } $this->writeData(); return true; } // {{{ getDescription() /** * @access public */ function getDescription() { return <<<EOS migrate database: {$this->id} [-b|--basedir=dir] [-m|--mode] EOS; } // }}} // {{{ getUsage() /** * @access public */ function getUsage() { return <<<EOS ethna {$this->id} [-b|--basedir=dir] [-a|--any-tmp-files] [-s|--smarty] [-p|--pear] [-c|--cachemanager] EOS; } // }}} /** * mysql create sql * * @access public */ function createSqlMysql(){ $create_table = ''; $table_list = $this->db->getTables(); foreach( $table_list as $table_name ){ $result = $this->db->getRow( 'SHOW CREATE TABLE ' . $table_name ); $create_table .= '-- ' . $result['Table'] . "\n"; $create_table .= $result['Create Table'] . ";\n"; $create_table .= "\n"; } return $create_table; } /** * mysql create sql * * @access public */ function writeData(){ $table_list = $this->db->getTables(); foreach( $table_list as $table_name ){ $path = $this->db_dir . "/" . $table_name . '.csv'; $fp = fopen( $path, "w" ); if( !$fp ){ printf("[warning] file creation failed [%s]\n", $path); continue; } // head出力 $tableInfo = $this->db->tableInfo($table_name); $row = array(); foreach( $tableInfo as $item ){ $row[] = $this->_escapeCSV( $item['name'] ); } $line = implode( ",", $row ) . "\n"; fwrite( $fp, $line, strlen($line) ); // データ $result = $this->db->query( 'SELECT * FROM ' . $table_name ); while( $row = $result->fetchRow()) { foreach( $row as $key => $item ){ $row[$key] = $this->_escapeCSV( $item ); } $line = implode( ",", $row ) . "\n"; fwrite( $fp, $line, strlen($line) ); printf("migrate successfully created [%s]\n", $path); } } } /** * ファイルを書き出す * * @access private */ function _fileWrite($file, $body){ $fp = fopen( $file, "w" ); if( $fp ){ fwrite( $fp, $body, strlen($body) ); // ファイルへの書き込み fclose($fp); return true; } return false; } /** * CSVエスケープ * * @access private */ function _escapeCSV($csv){ if( $csv == "" ){ return; } $csv = preg_replace('/"/u', '\"', $csv); $csv = preg_replace('/,/u', '\,', $csv); return '"' . $csv . '"'; } } // }}}
感想
意外にいけそうです。本当はDBの構成とか番号とかつけて特定のバージョンとか特定のデータセットに変更できる機能が必要になります。あとデータの読み込みとテーブルのドロップもいまないか。。。