EthnaでMigrateできるか実験２

すこしだけ改造してみました。

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

こんな感じの出力になります。PEAR::MDB2だとこんな情報からCREATE TABLEできるらしい。。。

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上で組むかな。。。