EthnaでMigrateできるか実験2
すこしだけ改造してみました。
YAML出力
<?php /** * DB構成の書き出し * * @access private */ function writeYaml(){ $table_data = array(); $table_list = $this->db->getTables(); foreach( $table_list as $table_name ){ $table_data[$table_name] = $this->db->tableInfo($table_name); } $path = $this->db_dir . '/database.yaml'; $fp = fopen( $path, "w" ); if( !$fp ){ printf("[warning] file creation failed [%s]\n", $path); return; } require_once('spyc.php'); $body = Spyc::YAMLDump($table_data); fwrite( $fp, $body, strlen($body) ); fclose($fp); printf("migrate successfully created [%s]\n", $path); }
内容的にはPEAR::DBのtableInfoをそのまま出力しています。
--- mantis_bug_file_table: - table: mantis_bug_file_table name: id type: int len: 10 flags: > not_null primary_key unsigned auto_increment - table: mantis_bug_file_table name: bug_id type: int len: 10 flags: not_null multiple_key unsigned - table: mantis_bug_file_table name: title type: string len: 750 flags: not_null - table: mantis_bug_file_table name: description type: string len: 750 flags: not_null - table: mantis_bug_file_table name: diskfile type: string len: 750 flags: not_null multiple_key - table: mantis_bug_file_table name: filename type: string len: 750 flags: not_null - table: mantis_bug_file_table name: folder type: string len: 750 flags: not_null - table: mantis_bug_file_table name: filesize type: int len: 11 flags: not_null - table: mantis_bug_file_table name: file_type type: string len: 750 flags: not_null - table: mantis_bug_file_table name: date_added type: datetime len: 19 flags: not_null binary - table: mantis_bug_file_table name: content type: blob len: -1 flags: blob binary
INSERT文作成
<?php /** * mysql create sql * * @access public */ function writeData(){ $path2 = $this->db_dir . '/insert_data.sql'; $fp2 = fopen( $path2, "w" ); if( !$fp2 ){ printf("[warning] file creation failed [%s]\n", $path2); return; } $data_path = $this->db_dir . "/data"; Ethna_Util::mkdir($data_path, 0755); if (file_exists($data_path)) { printf("file [%s] already exists -> skip\n", $data_path); } else { printf("migrate successfully created [%s]\n", $data_path); } $table_list = $this->db->getTables(); foreach( $table_list as $table_name ){ $path = $data_path . "/" . $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()) { $row2 = array(); foreach( $row as $key => $item ){ $row[$key] = $this->_escapeCSV( $item ); $row2[$key] = $this->db->quoteSmart( $item ); } $line = implode( ",", $row ) . "\n"; $line2 = "INSERT INTO " . $table_name . " VALUES( " . implode( ",", $row2 ) . " );\n"; fwrite( $fp, $line, strlen($line) ); fwrite( $fp2, $line2, strlen($line2) ); } fclose( $fp ); printf("migrate successfully created [%s]\n", $path); } fclose( $fp2 ); printf("migrate successfully created [%s]\n", $path2); }
データ出力の時にINSERT文も作成しています。通常はこれを読み込ませればいいかな。。。
DROPとTRUNCATE文作成
<?php /** * DROP TABLEの書き出し * * @access private */ function writeDrop(){ $path = $this->db_dir . '/drop_table.sql'; $fp = fopen( $path, "w" ); if( !$fp ){ printf("[warning] file creation failed [%s]\n", $path); return; } $table_list = $this->db->getTables(); foreach( $table_list as $table_name ){ $body = 'DROP TABLE ' . $table_name . ";\n"; fwrite( $fp, $body, strlen($body) ); } fclose($fp); printf("migrate successfully created [%s]\n", $path); // TRUNCATE $path = $this->db_dir . '/truncate_table.sql'; $fp = fopen( $path, "w" ); if( !$fp ){ printf("[warning] file creation failed [%s]\n", $path); return; } $table_list = $this->db->getTables(); foreach( $table_list as $table_name ){ $body = 'TRUNCATE TABLE ' . $table_name . ";\n"; fwrite( $fp, $body, strlen($body) ); } fclose($fp); printf("migrate successfully created [%s]\n", $path); }
TRUNCATE文は要らないけれど、DROPの前に実行するかデータリロードの際に利用します。
PATHの整理
db │ create_table.sql │ database.yaml │ drop_table.sql │ insert_data.sql │ truncate_table.sql │ └─data mantis_bugnote_table.csv ... mantis_user_table.csv
こんな感じにdataフォルダを追加しました。CSVが大量にあるって邪魔ですもんね。
全ソース
<?php // vim: foldmethod=marker /** * Ethna_Plugin_Handle_Migrate.php * * @author akiraneko <akirainfoml@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@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=', 'dump',)); if (Ethna::isError($r)) { return $r; } list($opt_list,$arg_list) = $r; $basedir = isset($opt_list['basedir']) ? realpath(end($opt_list['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); if( 1 || array_search( 'dump', $arg_list ) !== FALSE ){ 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->writeDrop(); $this->writeYaml(); $this->writeData(); } else { echo $this->getUsage(); return false; } return true; } // {{{ getDescription() /** * @access public */ function getDescription() { return <<<EOS migrate database: {$this->id} [-b|--basedir=dir] [dump] EOS; } // }}} // {{{ getUsage() /** * @access public */ function getUsage() { return <<<EOS ethna {$this->id} [-b|--basedir=dir] [dump] 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(){ $path2 = $this->db_dir . '/insert_data.sql'; $fp2 = fopen( $path2, "w" ); if( !$fp2 ){ printf("[warning] file creation failed [%s]\n", $path2); return; } $data_path = $this->db_dir . "/data"; Ethna_Util::mkdir($data_path, 0755); if (file_exists($data_path)) { printf("file [%s] already exists -> skip\n", $data_path); } else { printf("migrate successfully created [%s]\n", $data_path); } $table_list = $this->db->getTables(); foreach( $table_list as $table_name ){ $path = $data_path . "/" . $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()) { $row2 = array(); foreach( $row as $key => $item ){ $row[$key] = $this->_escapeCSV( $item ); $row2[$key] = $this->db->quoteSmart( $item ); } $line = implode( ",", $row ) . "\n"; $line2 = "INSERT INTO " . $table_name . " VALUES( " . implode( ",", $row2 ) . " );\n"; fwrite( $fp, $line, strlen($line) ); fwrite( $fp2, $line2, strlen($line2) ); } fclose( $fp ); printf("migrate successfully created [%s]\n", $path); } fclose( $fp2 ); printf("migrate successfully created [%s]\n", $path2); } /** * ファイルを書き出す * * @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構成の書き出し * * @access private */ function writeYaml(){ $table_data = array(); $table_list = $this->db->getTables(); foreach( $table_list as $table_name ){ $table_data[$table_name] = $this->db->tableInfo($table_name); } $path = $this->db_dir . '/database.yaml'; $fp = fopen( $path, "w" ); if( !$fp ){ printf("[warning] file creation failed [%s]\n", $path); return; } require_once('spyc.php'); $body = Spyc::YAMLDump($table_data); fwrite( $fp, $body, strlen($body) ); fclose($fp); printf("migrate successfully created [%s]\n", $path); } /** * DROP TABLEの書き出し * * @access private */ function writeDrop(){ $path = $this->db_dir . '/drop_table.sql'; $fp = fopen( $path, "w" ); if( !$fp ){ printf("[warning] file creation failed [%s]\n", $path); return; } $table_list = $this->db->getTables(); foreach( $table_list as $table_name ){ $body = 'DROP TABLE ' . $table_name . ";\n"; fwrite( $fp, $body, strlen($body) ); } fclose($fp); printf("migrate successfully created [%s]\n", $path); // TRUNCATE $path = $this->db_dir . '/truncate_table.sql'; $fp = fopen( $path, "w" ); if( !$fp ){ printf("[warning] file creation failed [%s]\n", $path); return; } $table_list = $this->db->getTables(); foreach( $table_list as $table_name ){ $body = 'TRUNCATE TABLE ' . $table_name . ";\n"; fwrite( $fp, $body, strlen($body) ); } fclose($fp); printf("migrate successfully created [%s]\n", $path); } }
感想
んー、dumpの部分しか実装していない。。。でもデータロードとかってphpMyAdmin使っているからこれで事足りる。。。
細かいところを実装しようかと思ったけれど、いろいろ気になるところを発見。まずDBってEthnaは変えられるからgetDBで取ってきたものがPEAR::DBである保障ないのよね。。。
DBの関数を見た限りMDB2じゃないと細かい制御をデータベースごとに書かないといけないから、本当はMDB2で書き直したいかな。。。
機能としては面白いし、複数人数の場合データを変更してロードしたりといろいろできることはあるんだけれど、いまいちプロジェクトとしてコード組んでいないからこの手のユーティリティいらないのよね(笑)
そろそろ何かEthna上で組むかな。。。