雑文発散

過去の日記

2013-01-27 [長年日記]

▼ [PostgreSQL][MySQL] mysql より psql の補完が好きなので MySQL のテーブルを PostgreSQL から操作してみたよ

このところ MySQL の CUI であるところの mysql コマンドを利用しているんだけど、テーブル名などの補完機能が PostgreSQL の psql コマンドと違っていてウニャウニャした気持ちになっている。

だったら psql コマンドで MySQL のテーブルを操作できたらいいんじゃね？と思って、mysql_fdw を試してみた。

最近の PostgreSQL には Foreign Data Wrapper（FDW : 外部データラッパ）という機能があって、PostgreSQL の外部に存在するデータベースからデータを引っ張ってきたりできるようになっている。

mysql_fdw というのは、この FDW を使って「PostgreSQL から MySQL を操作しちゃおうぜ」という野心的な拡張。

では、インストールしていく。

MySQL は、dotdeb を使ってこの辺をインストールした。libmysqlclient-dev は、mysql_fdw をコンパイルする際に必要だった。

mysql-server
- 5.5.29-1~dotdeb.0
libmysqlclient-dev
- 6.1.3-1

PostgreSQL は、postgresql.org の Debian 用パッケージを利用して、この辺のバージョンをインストール。

postgresql-9.2
- 9.2.2-1.pgdg60+1

mysql_fdw は GitHub から clone してきた。そしてコンパイルして、インストール。

$ git clone https://github.com/dpage/mysql_fdw.git
$ cd mysql_fdw
$ PATH=/usr/local/pgsql/bin/:/usr/local/mysql/bin:$PATH make USE_PGXS=1 
$ sudo PATH=/usr/local/pgsql/bin/:/usr/local/mysql/bin:$PATH make USE_PGXS=1 install

これで準備は完了。

次にデータベース側の設定を行なう。

MySQL 側には次のようなデータベースとテーブルが存在しているとする。

データベース名
- rentacar
テーブル名
- location

テーブル構造はこんな感じ。これ、何かというと、書籍「効率的なWebアプリケーションの作り方」で解説されているサンプルで使われているテーブル。

mysql> SHOW COLUMNS FROM location;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| id         | int(11)      | NO   | PRI | NULL    |       |
| name       | varchar(255) | NO   |     | NULL    |       |
| created_at | datetime     | NO   |     | NULL    |       |
| updated_at | datetime     | NO   |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

そして location テーブルの中身はこちら。神楽坂とか青山とか、いい所だよね。

mysql> SELECT * FROM location;
+----+-----------+-------------------------+-------------------------+
| id | name      | created_at              | updated_at              |
+----+-----------+-------------------------+-------------------------+
|  1 | 神楽坂    | 2013-01-09 10 : 12 : 53 | 2013-01-09 10 : 12 : 53 |
|  2 | 青山      | 2013-01-09 10 : 12 : 53 | 2013-01-09 10 : 12 : 53 |
+----+-----------+-------------------------+-------------------------+
2 rows in set (0.00 sec)

（: と : に挟まれた文字列がアイコンと判定されてしまったので、時刻部分へ意図的にスペース入れてる）

これを PostgreSQL から使えるようにしてみる。PostgreSQL 側のデータベース名などはなんでも良いんだけど、MySQL 側と区別するために、ちょっとだけ変えて次のようにしてみる。

データベース名
- p_rentacar
テーブル名
- p_location

データベースを作る。PostgreSQL のユーザ作成とかその辺は端折る。

$ createdb p_rentacar

psql コマンドで p_rentacar データベースへ入る。

$ psql p_rentacar

ここから psql 上の操作。

まずはデータベースへ mysql_fdw 拡張を読み込む。

p_rentacar=# CREATE EXTENSION mysql_fdw;
CREATE EXTENSION

次に MySQL のサーバ設定をする。127.0.0.1:3306 で稼働している場合の設定。mysql_svr は任意の名前。

p_rentacar=# CREATE SERVER mysql_svr 
p_rentacar-#     FOREIGN DATA WRAPPER mysql_fdw 
p_rentacar-#     OPTIONS (address '127.0.0.1', port '3306');
CREATE SERVER

上で設定した mysql_svr へ接続する MySQL 側のユーザを設定する。USERNAME と PASSWORD は自分で使っているアカウントに変更して実行して欲しい。

p_rentacar=# CREATE USER MAPPING FOR PUBLIC 
p_rentacar-#     SERVER mysql_svr 
p_rentacar-#     OPTIONS (username 'USERNAME', password 'PASSWORD');
CREATE USER MAPPING

ここまでで準備は完了。では PostgreSQL のテーブルを作成する。id や name などのデータ型は、MySQL 側と合うように定義しておく。

p_rentacar=# CREATE FOREIGN TABLE p_location (
p_rentacar(#     id INTEGER NOT NULL,
p_rentacar(#     name TEXT NOT NULL,
p_rentacar(#     created_at TIMESTAMP NOT NULL,
p_rentacar(#     updated_at TIMESTAMP NOT NULL
p_rentacar(# )
p_rentacar-# SERVER mysql_svr
p_rentacar-# OPTIONS (table 'rentacar.location');
CREATE FOREIGN TABLE

「CREATE FOREIGN TABLE」となっているところに注目。SERVER には上記で作成した mysql_svr を指定し、OPTIONS の中で MySQL の rentacar データベースの location テーブルを参照するように指定している。

これで MySQL のデータを PostgreSQL から参照できるようになった。実行してみよう。

p_rentacar=# SELECT * FROM p_location ;
 id |  name  |     created_at          |     updated_at          
----+--------+-------------------------+-------------------------
  1 | 神楽坂 | 2013-01-09 10 : 12 : 53 | 2013-01-09 10 : 12 : 53
  2 | 青山   | 2013-01-09 10 : 12 : 53 | 2013-01-09 10 : 12 : 53
(2 行)

先ほどの MySQL での実行結果を並べておこう。同じ結果だよね。

mysql> SELECT * FROM location;
+----+-----------+-------------------------+-------------------------+
| id | name      | created_at              | updated_at              |
+----+-----------+-------------------------+-------------------------+
|  1 | 神楽坂    | 2013-01-09 10 : 12 : 53 | 2013-01-09 10 : 12 : 53 |
|  2 | 青山      | 2013-01-09 10 : 12 : 53 | 2013-01-09 10 : 12 : 53 |
+----+-----------+-------------------------+-------------------------+
2 rows in set (0.00 sec)

データの追加もできるかな？と思って実行してみたけど、これは対応していない様子。FDW のことは、あまり理解していないのだけど、たぶん参照のみしかできないような。

p_rentacar=# INSERT INTO p_location (id, name, created_at, updated_at)
p_rentacar-#     VALUES (3, '中野坂上', now(), now());
ERROR:  外部テーブル "p_location"を変更できません

とはいえ、これで psql の補完機能が使えるわけだ。

ちなみに何が嬉しいかというと、mysql コマンドの場合は、次のように入力したところで TAB キーを2回押すと、補完候補が出てくる。出てきてくれるのはありがたいのだけど、表示されるだけだ。

mysql> SELECT * FROM lo
load data from       location             location.created_at  location.id          location.name        location.updated_at  lock tables

これが psql コマンドの場合、このように入力したところで TAB キーを1回押すと、

p_rentacar=# SELECT * FROM p_lo

このように補完される。

p_rentacar=# SELECT * FROM p_location

これに慣れちゃってるので、mysql コマンドでの補完が物足りないのであった。

【追記】FDW は、次のバージョンから更新も可能になるみたい！！

@suzuki ちなみにFDWは次のバージョンから更新可能になるのではないかと思われます。これで安心ですね！
— Satoshi Nagayasuさん (@snaga) 2013年1月27日

[ツッコミを入れる]