雑文発散
2012-05-16 [長年日記]
▼ [MySQL][PostgreSQL] MySQL のテーブルを PostgreSQL に移植する
MySQL ベースにいつの間にかなっていた CandyCane を PostgreSQL で動くようにしてみている。元々が MySQL と PostgreSQL に両対応している Redmine (Rails) のデータ構造であり、同じく両対応している CakePHP で使うことを前提としているので、そんなに特殊なものじゃないという前置き。
MySQL でダンプされたデータには「5.1.30」とあったので、たぶんこのバージョンの MySQL を利用。移植先は PostgreSQL は 9.1.3 を利用している。
| # | MySQL | PostgreSQL | 備考 |
|---|---|---|---|
| 1 | CREATE TABLE IF NOT EXISTS `attachments` ( 略 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ; | CREATE TABLE attachments ( 略 ); | PostgreSQL 9.1 以降なら IF NOT EXISTS 入りでも OK。ENGINE などはガッツリ削除。テーブル名のバッククォートも削除。 |
| 2 | `id` int(11) NOT NULL AUTO_INCREMENT | id SERIAL NOT NULL | AUTO_INCREMENT は SERIAL で代替。カラム名のバッククォートは削除。以降の行はカラム名省略。 |
| 3 | int(11) NOT NULL DEFAULT '0' | INTEGER NOT NULL DEFAULT 0 | 数値のゼロなのであえて '0' にはしない |
| 4 | varchar(30) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' | VARCHAR(30) NOT NULL DEFAULT '' | COLLATE は無視。VARCHAR の数値はそのまま。 |
| 5 | datetime DEFAULT NULL | TIMESTAMP DEFAULT NULL | TIMESTAMP 型に変更 |
| 6 | tinyint(1) NOT NULL DEFAULT '0' | INTEGER NOT NULL DEFAULT 0 | Rails 的には tinyint(1) を BOOLEAN 的に使っているとか？（要出展）。今は INTEGER にしてしまっているが、PostgreSQL 的には BOOLEAN が正しい？ |
| 7 | CREATE TABLE IF NOT EXISTS `boards` ( 略 KEY `boards_project_id` (`project_id`)) 略 | CREATE TABLE boards ( 略 ); CREATE INDEX boards_project_id ON boards (project_id); | CREATE TABLE 内の KEY は、外出しして CREATE INDEX へ変換。 |
| 8 | text COLLATE utf8_unicode_ci | TEXT | COLLATE は無視 |
| 9 | CREATE TABLE IF EXISTS `sample` ( `id` int(11) NOT NULL AUTO_INCREMENT); INSERT INTO `sample` VALUES (1); | CREATE TABLE sample (id SERIAL NOT NULL); INSERT INTO sample VALUES (nextval(`sample_id_seq`)); | INSERT時にSERIAL用のシーケンスを利用する必要がある。 |
| 10 | INSERT INTO `roles` ( permissions ) VALUES ('--- \n- :add_issues\n'); | INSERT INTO roles ( permissions ) VALUES ('--- - :add_issues '); | PostgreSQLでは '\n' は改行ではなく \n という文字なので、本当の改行を入れてやる。 |
| 11 | date | DATE | そのまま |
今回やってみたのはだいたいこんなところ。
ORM 経由での DB アクセスなので、テーブル構造だけ移植すれば修正すればいいかなと思ってたけど、うまく動かない箇所もあった。そこは CakePHP の ORM ではうまく表現できないところみたいで、生 SQL が書かれていた。その辺のアプリケーション部分の修正をしたところ、なんとなく CandyCane を PostgreSQL 9.1.3 で動かせたんじゃないかと思ってる。まだ全然詳細まで動作確認していないので、残りものが多数ありそうだけど（笑）
PostgreSQLの booleanだと数値の0/1を受け付けてくれないのが悩ましいですね
そこら辺はフレームワーク側で意図しているデータ形式にするのが正しいのかなとか思い始めました。 CakeSchema で 'type' => 'boolean' として利用されるデータ形式に合わせるのかな、とか。