[PHP]PostgreSQLからMySQLへ移行する

2013/2/14 記事

わりと急ぎの案件でシステムのデータベースをPostgreSQLからMySQLに置き換えるお仕事をいただきました。

かなり古いソースで、Pear::DBやPDOではなくPostgreSQL関数を利用しているため、単純なDBの移行ができません。しかも基本的に手続き型のソースなので、いたるところにクエリが散在していて修正箇所は数千箇所に上ります。

ここまでくると１から書き直したほうが良さそうですが、なにしろ仕様書も残っていないシステムなので短期間で新たに実装し直すのはちょっと怖いですよね。

そのため、まずは手続き型の現在のソースを残してDBの置き換えと、緊急で修正したい箇所の修正を行ったあと・・・時間を掛けてCakePHPでリニュアルすることになりました。

PostgreSQLとMySQLではDB自体の作りが異なるのと、PHP側の関数が完全に互換しているわけではないので、その違いを埋めるための修正を行う必要があります。

今回はそこら辺のTipsをまとめます。

2013/2/13
その他の関数を追記しました
データベース接続のソースコードでmysqli_connectの第２引数がDB_NAMEだったのをDB_USERに修正しました

どのAPIを使うか

参考：PHP: どの API を使うか – Manual

PHPにはMySQLに接続するためのAPIが３つ用意されています。

ext/mysql
PDO_MySQL
ext/mysqli

ext/mysqlは古いAPIで、機能的に他の２つのAPIに劣り、PHP5.5では削除されることが予定されているので却下。*1

本来はPDOで書いたほうが汎用性があっていいと思うのですが、今回はリニュアルが控えているのと、手続き型からの移行が大変なので却下。

mysqliは手続き型にも対応しているため、今回の移行作業ではmysqliを利用することにしました。

以下の文中ではPostgreSQL関数をpg、MySQLiをmysqliと記述します。

データベース接続

変更前のソースをコメントアウトで、変更後を続く行に記載します。

コメントアウトされたソースは基本的にpg_関数を利用して書かれた原文、それに続くコメントアウトされていない行がmysqliに置き換えたコードになります。*2

データベースへの接続処理は、
pg_connectをmysqli_connectに置き換えます。

	// DBへの接続
	function connect() {
		if( $this->con == null ) {
//			$this->con = pg_connect("host=".DB_HOST.
//									" dbname=".DB_NAME.
//									" user=".DB_USER.
//									" password=".DB_PASS);
			$this->con = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME);

			if ($this->con !== FALSE) {
//				pg_query($this->con, "SET client_encoding TO 'UNICODE'");
				mysqli_set_charset($this->con, 'utf8');
			}
		}
		return $this->con;
    }

pg_connectはテキストでパラメータを渡しますが、mysqli_connectでは、それぞれの引数で渡します。

また、pgでは文字エンコードをセットする関数がないので、pg_query($this->con, "SET client_encoding TO 'UNICODE'");でSET client_encodingのクエリを実行していますが、mysqliではmysqli_set_charsetという関数が用意されているのでこれを利用してクライアントのデフォルト文字セットを設定します。

クエリの実行

pg_queryをmysqli_queryに置き換えます。
pg_fetch_assocをmysqli_fetch_assocに置き換えます。
pg_closeをmysqli_closeに置き換えます。

	$db = $this->connect();		// DB接続
	$sql = 'SELECT * FROM hoge';
//	$res = pg_query( $db, $sql );
//	while( $row[] = pg_fetch_assoc( $res ) ) {}
//	ver_dump($row);
//	pg_close( $db );
	$res = mysqli_query($db, $sql);
	while( $row[] = mysqli_fetch_assoc( $res ) ) {}
	ver_dump($row);
	mysqli_close( $db );

嬉しいことにpg_queryとmysqli_queryは引数が共通*3 なので、関数だけ置換すればOKです。

pg_fetch_assocやpg_closeも引数は共通なので、そのままmysqli_fetch_assoc、mysqli_closeに置き換えることができます。

トランザクション

pg関数にはトランザクションの関数がないため、pgでトランザクションを利用している場合は

pg_query( $db, "BEGIN" );
pg_query( $db, "ROLLBACK" );
pg_query( $db, "COMMIT" );

と書かれていると思います。*4

mysqliにはトランザクション用の関数が用意されているので、こちらに置き換えることも可能です。

mysqli_autocommit($db, false);	// falseでオートコミットを無効に、trueで有効にする。
mysqli_commit($db);
mysqli_rollback($db);

ですが、トランザクションの関数を使うと引数が異なるためソースを書き直さないといけなくなります。

当然mysqliでも、mysqli_query($db, 'BEGIN');のようにトランザクションの開始をクエリで渡してもいいので、面倒を省くためにpg_queryをmysqli_queryで対応します。

プリペアドステートメント

そのまま置き換えられない厄介なのがプリペアドステートメントです。

pgの場合*5 

$db = $this->connect();		// DB接続

$sql = 'SELECT * FROM shops WHERE name = $1';

// 実行するクエリの準備
$result = pg_prepare($db, "stmt_name", $sql);

// プリペアドクエリを実行する。文字列 "Joe's Widgets" はエスケープの必要がないことに注意
$result = pg_execute($db, "stmt_name", array("Joe's Widgets"));

// 同一プリペアドクエリを別のパラメータで実行する
$result = pg_execute($db, "stmt_name", array("Clothes Clothes Clothes"));

と書きますが、mysqliでは全く構文が異なります。

mysqliの場合

$db = $this->connect();		// DB接続

// パラメータは$iではなく?（クエスチョンマーク）
$sql = "SELECT * FROM products WHERE id = ?";

// ステートメントを初期化
$stmt = mysqli_prepare($db, $sql);

// マーカにパラメータをバインドします
// パラメータが複数ある場合は第３引数以降に続けて指定する
mysqli_stmt_bind_param($stmt, "s", $val1);


// パラメータを指定
$val1 = "Joe's Widgets";	// シングルクォートにエスケープははいらない（はず）

// クエリを実行します
mysqli_stmt_execute($stmt);

// 別のパラメータをセット
$val1 = "Clothes Clothes Clothes";

// 別のパラメータでクエリを実行します
mysqli_stmt_execute($stmt);

// ステートメントを閉じる
mysqli_stmt_close($stmt);

このように手順がかなり異なるため、単純な置き換えでは対応できそうにありません。

今回の案件では、できるだけ短時間で効率よく移行することが目的なので、pgのプリペアドステートメントの書き方で通る独自のメソッドを親クラスに用意して対応できないか考えてみたいと思います。*6

その他の関数

上記以外で、今回の案件で出てきた関数の置き換えをメモしておきます。

pg_affected_rows

直前のクエリで変更された行数を取得する。

pg_affected_rowsを、mysqli_affected_rowsに置き換える。

ただし、pgは引数にクエリ結果リソースをとるのに対して、mysqliはコネクションの返り値をとる点に注意する。

pg_affected_rows($results);
// ↓引数をDBコネクションリンクにする
mysqli_affected_rows($db);	// $dbはmysqli_connectの返り値

pg_escape_string

SQL 文で使用する文字列の特殊文字をエスケープする。

pg_escape_stringを、mysqli_real_escape_stringに置き換える。

ただし、pg_escape_stringの第１引数（DBのコネクション）が省略されている場合は、置き換えの際に第１引数の（DBコネクションのリンク）を必ず指定すること。

pg_escape_stirng($escapeString);
// ↓第１引数にDBコネクションリンクを指定する
mysqli_real_escape_string($db, $escapeString);

pg_num_rows

行数を返す。

pg_num_rowsを、mysqli_num_rowsに置き換える。

引数は共通。前述の

pg_last_error

直近のエラーメッセージを返す。
pg_last_errorは、mysqli_errorに置き換える。

引数は共通。*7

pg_close

データベース接続を閉じる。

pg_closeは、mysqli_closeに置き換える。

引数は共通。

その他の作業

関数の置き換え以外の対応が必要な箇所についてのメモです。

PostgreSQLのタイムスタンプ型のデフォルト値 NOW()

移行元のpgのテーブルでタイムスタンプ型でデフォルト値にNOW関数で登録日時を保存するフィールドが存在するのですが、データベースをMySQLに移行する際に、datetime型に変更しました。

ただ、MySQLではtimestamp型であればデフォルト値にCURRENT_TIMESTAMP関数を使用できるのですが、datetime型では使用できないのです。

仮にtimestamp型にしたとしても、データベースから取得するデータが変わってしまう（と思う）ので、こちらの方が影響範囲が大きそうです。SELECTするときに関数で日時に置き換えることも可能だと思いますが、SELECTの方が更新系よりもずっと回数が多いので更新系を直したほうがいいと判断しました。*8

なので、pgでデフォルト値にNOW関数を指定していたフィールドは、更新時に渡すクエリデータの空文字をCURRENT_TIMESTAMP()に変更して対応したいと思います。

PostgreSQL関数のto_char置換

参考：to_char

ここから自分のメモです。

to_char\((.*?),.*?'YYYY\/MM\/DD'\)
↓
date_format($1, '%Y-%m-%d')

to_char\((.*?),.*?'YY\/MM\/DD'\)
↓
date_format($1, '%y-%m-%d')

to_char\((.*?),.*?'YYYY\/MM\/DD HH24:MI:SS'\)
↓
date_format($1, '%Y-%m-%d %H:%i:%s')

to_char\((.*?),.*?'YY\/MM\/DD HH24:MI:SS'\)
↓
date_format($1, '%y-%m-%d %H:%i:%s')

to_char\((.*?),.*?'YYYY'\)
↓
date_format($1, '%Y')

to_char\((.*?),.*?'YYYY\/MM\/DD HH24:MI'\)
↓
date_format($1, '%Y-%m-%d %H:%i')

to_char\((.*?),.*?'YYYY\/MM\/DD HH24'\)
↓
date_format($1, '%Y-%m-%d %H')

おわりに

実は、この記事をまとめてから実際の移行作業を始めようと思っていたので、実作業はこれからです。（この記事を書いた段階では）

自社案件ではPostgreSQLを使ったことが無いので、この案件が決まったときは何をどうすればいいかが見えなくて不安だったのですが、こうして記事にまとめてみると何とかなりそうです。

それよりも、こうした案件で一番キツイのは、仕様書も残っていない他人（他社）の書いたソースを１から読んで、それを新たに組み直す作業です。

弊社（株式会社アスタ）の強みは、既存のシステムのリニュアルや修正案件を多く手がけているので、こうした泥沼案件に強いってことですね（笑）

今回は、結構大きめのシステムでPostgreSQL→MySQLの案件の実績ができた（る）ので、もし「データベースを移行したいんだけど・・・」という方がいらっしゃいましたらお気軽にお声がけください。

タイトな案件が２つほど重なっているので、「[CakePHP]Article-Pluginを作ってみる」シリーズの記事が止まっちゃいましたが、機能としては記事を登録できるところまでできています。*9
こちらもせっかく書き始めた記事なので、今の仕事が落ち着いたらcloneして動かせるようになるまでは続けたいと思います。

  1. リニュアルを控えているので別に使ってもいいですが、pgでプリペアドステートメントを使っていたりするのでmysqliを選択。手続き型なんてとんと書いていないですし、mysqliも使ったことが無かったので素のPHPを復習する意味でもいいかと思いました。 [↩]
  2. コメントアウトされたソースについては私のソースではないのでノータッチでおねがいしますね(^_-) [↩]
  3. どちらの関数も第１引数にデータベースconnect関数が返すそれぞれの値（厳密にはpgとmysqliは返り値が違うけど）、第２引数にクエリを指定する。ちなみにmysql_queryは引数が逆になっているため、ソースを修正しなければならない。 [↩]
  4. 他の書き方があるかもしれませんが一般的には [↩]
  5. PHP: pg_prepare – Manualより一部参照 [↩]
  6. 実装したら記事に追記します。パラメータ変換文字の$1→?と、パラメータの渡し方の変換がキモですね。 [↩]
  7. 前述のpg_affected_rowsの引数はresultで、mysqli_num_rowsの引数もresultなので、こちらに置き換えてしまってもいいのかもしれない。 [↩]
  8. ここら辺はきちんと学んだわけではないので自信がありませんが、データベースに格納されるデータ形式が近いほうが問題は起こらないだろうという思惑です。 [↩]
  9. まだGitHubにはpushしてなかったと思うけど^^; [↩]
スポンサーリンク
336x280_1
336x280_1

シェアする

  • このエントリーをはてなブックマークに追加

フォローする

, , suzuki

スポンサーリンク
336x280_1