831日のPostgreSQL → SQLiteエクスポートが若干面倒だったので、書き込み可能な外部データラッパ(FDW)を探したらOGR_FDWの最新版1.0.2がありました。今日は最初の準備と簡単なテスト結果。UPDATEDELETEは無理っぽいけどINSERTは普通にでき、結構使えそうです。

※ 追記94日:TEXT型のINSERTには要注意と判明しました。詳細はこちら
Contents


実行環境

• Windows7 x64 + Cygwin 2.5.1 + ConEmu 150813g
• WindowsPostgreSQL 9.5.3 + Cygwinpsql
• CygwinSQLite 3.14.1
•  ogr_fdw 1.0.2
• Windowsは管理者権限ユーザ、PostgreSQL接続はスーパーユーザ


手順まとめ

(1)SQLite3DBを準備(既にあれば不要)、(2)OGR_FDW 1.0.2インストール、(3)SQLiteのテーブルをPostgreSQLの外部テーブル化、(4)テスト、に分かれます。概要は下記のとおり。
-- 1) prepare SQLite3 database
$ sqlite3 r:/tmp/foobar.db
sqlite> create table tb1 (col1, col2);
sqlite> create table tb2 (id int primary key, str text not null); 
sqlite> .q

-- 2) install ogr_fdw 1.0.2

-- 3) import SQLite tables as FOREIGN TABLE
$ psql -d test_ogr_fdw
# create extension ogr_fdw;
# create server foobar
    foreign data wrapper ogr_fdw
    options(
        datasource 'r:/tmp/foobar.db',
        format 'SQLite');
# create schema test_sqlite;
# import foreign schema ogr_all
    from server foobar
    into test_sqlite;

-- 4) read from and write to SQLite tables on Postgres
# select * from test_sqlite.tb1 values; 
# insert into test_sqlite.tb1 values (...); 
# insert into test_sqlite.tb2 values (...); 
# ...


SQLite3の操作は適当なテーブルを作るだけ。今回は上に記述のとおり2テーブル作成しました。列の型指定を省ける(一つ目のテーブル)のがSQLiteならでは。


OGR_FDWについて

7PostgreSQLアンカンファレンスでのスライドを見て下さい。この時の最後に、SQLiteに接続できる件だけ紹介しました。今日はその実践の入口になります。

以前のバージョンは読み込み専用でしたが、517日に出た1.0.2で、SQLite3に限らず(というか主眼であるGISの)様々なデータフォーマットに書き込み可能になりました。言い換えるとWRITABLEな外部テーブルを作れます。


OGR_FDW 1.0.2インストール

ソースはGitHubrelesesから。パッケージは、例えばYumPostgreSQL 9.5関連リストなら下記で。今回はWindows用バイナリを使ったので試してません。

yum.postgresql.org/packages.php
=> yum.postgresql.org/9.5/redhat/rhel-7-x86_64/repoview/
   => ogr_fdw95-1.0.2-1.rhel7


Windows用バイナリは ↓ いつものPostGISサイト内のextrasフォルダから。ちょっと分かりにくくて、ファイル名にバージョン1.0.2と付くのは無いです。日付が1.0.2リリースの517日以降のものを選択。(1.0.1とあるのは古い読み込み専用)。

winnie.postgis.net/download/windows/pg95/buildbot/extras/
=> ogrfdw-pg95-binaries-1.0w32gcc481.zip
=> ogrfdw-pg95-binaries-1.0w64gcc48.zip


zipの中にある ↓ ogrfdw_README.mdを開いてWriteable FDW Tablesの項があればOK(文書の後半。writableでなくwriteableとも書くらしい)。ogrfdw_version.txtでは分かりません。
(ogrfdw_README.md)
...
## Advanced Features

### Writeable FDW Tables

If the OGR driver you are using supports it, you can insert/update/delete records from your FDW tables.
(ogrfdw_version.txt)

OGR_FDW: 1.0
PostgreSQL: 9.5 w64
Built: 20160602
GIT_REPO: https://github.com/pramsey/pgsql-ogr-fdw
GIT_BRANCH: origin/master
GIT_REVISION: 7c5699a20371eb1230e28cd1d3be12c76ee0035c 
GDAL_VER: 2.1.0


Windows用バイナリのインストールは、binなど各フォルダをPostgreSQLのプログラムフォルダにコピーするだけ。環境変数の設定とサーバ再起動は不要です(PL/PythonPL/Rと違って)。

ただしPostGISのエクストラ的な位置づけなので、PostGISで入ってる前提の2つのDLL(libgcc_s_seh-1.dll、libstdc++-6.dll)が同梱されてません。
先ほどのextrasフォルダの上位階層からPostGISzipをダウンロードすれば入ってます。

↓ 参考まで、OGR_FDWzip内の全ファイルについて依存するDLLを列挙してまとめ表示するコマンドと結果。... の部分はobjdumpWarning(バイナリのファイルじゃない旨)がばーっと出ます。
$ cd ogrfdw-pg95-binaries-1.0w64gcc48
$ find | xargs objdump -x | grep "DLL Name" | sort -u 
...
DLL Name: KERNEL32.dll
DLL Name: LIBEAY32.dll
DLL Name: ODBC32.dll
DLL Name: ODBCCP32.dll
DLL Name: SSLEAY32.dll
DLL Name: USER32.dll
DLL Name: WLDAP32.dll
DLL Name: WS2_32.dll
DLL Name: libcurl-4.dll
DLL Name: libexpat-1.dll
DLL Name: libfreexl-1.dll
DLL Name: libgcc_s_seh-1.dll <== 
DLL Name: libgdal-20.dll
DLL Name: libsqlite3-0.dll
DLL Name: libstdc++-6.dll    <==
DLL Name: msvcrt.dll


以前のバージョンをインストール済みの場合

以前のバージョンとは、最初の1.0、次の1.0.1です。どちらかをDBExtensionとして登録済みだった場合、普通はExtensionも更新することになりますが(ALTER EXTENSION ... UPDATE)、今のところOGR_FDW 1.0.xExtension上は同じもの(1.0)なので不要。つまりストアド関数などDBのオブジェクトは変化なく、実行時に呼ばれるライブラリやファイルだけが新しくなってます。


SQLiteのテーブルをIMPORT FOREIGN SCHEMAで一括インポート

以下、インストール後の簡単なテスト。psqlで適当なDBを作って接続した所から始めます。まずExtensionとしてORG_FDWを登録、結果の確認を \dx で。前項で書いたようにExtension上のバージョンは1.0です。↓
# create extension ogr_fdw;
# \dx
                        List of installed extensions
+---------+---------+------------+------------------------------------------+ 
|  Name   | Version |   Schema   |               Description                |
+---------+---------+------------+------------------------------------------+
| ogr_fdw | 1.0     | public     | foreign-data wrapper for GIS data access |
| plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language             |
+---------+---------+------------+------------------------------------------+
(2 rows)


続いてCREATE SERVER。
記事の最初に作ったSQLite3のデータベースファイルをデータソースにします。ファイル指定は絶対パスの他、PostgreSQLbinフォルダからの相対パスでも可。ファイルが見つからないとCREATE SERVERできません。またフォーマット名はSQLite(末尾に3を付けない)。
# create server foobar
    foreign data wrapper ogr_fdw
    options(
        datasource 'r:/tmp/foobar.db',
        format 'SQLite');

# \des
          List of foreign servers
+--------+----------+----------------------+ 
|  Name  |  Owner   | Foreign-data wrapper |
+--------+----------+----------------------+
| foobar | postgres | ogr_fdw              |
+--------+----------+----------------------+
(1 row)


次にSQLiteのテーブルを外部テーブル化。
SQLiteでのDDLを見て一つずつ定義してもいいけど面倒なので、PostgreSQL 9.5から使えるIMPORT FOREIGN SCHEMAで一括実行します(その前に、作業用スキーマとしてtest_sqliteを作成)。結果を\det+コマンドで確認すると ↓ SQLiteで作った2テーブルが確かに外部テーブルに。
# create schema test_sqlite;
# import foreign schema ogr_all
    from server foobar
    into test_sqlite;

# \det+ t*.*
                    List of foreign tables
+-------------+-------+--------+---------------+-------------+ 
|   Schema    | Table | Server |  FDW Options  | Description |
+-------------+-------+--------+---------------+-------------+
| test_sqlite | tb1   | foobar | (layer 'tb1') |             |
| test_sqlite | tb2   | foobar | (layer 'tb2') |             |
+-------------+-------+--------+---------------+-------------+
(2 rows)


外部テーブルの列構成を\dコマンドで見ると ↓ 元になかった列fidがあります。これはOGR_FDW特有で、IMPORT FOREIGN SCHEMAすると自動的に付く連番のID列。元データの各レコードを一意に識別できます。他の列は全てvarchar型。
# \d t*.*
            Foreign table "test_sqlite.tb1"
+--------+-------------------+-----------+-------------+ 
| Column |       Type        | Modifiers | FDW Options |
+--------+-------------------+-----------+-------------+
| fid    | bigint            |           |             |
| col1   | character varying |           |             |
| col2   | character varying |           |             |
+--------+-------------------+-----------+-------------+
Server: foobar
FDW Options: (layer 'tb1')

            Foreign table "test_sqlite.tb2"
+--------+-------------------+-----------+-------------+
| Column |       Type        | Modifiers | FDW Options |
+--------+-------------------+-----------+-------------+
| fid    | bigint            |           |             |
| id     | character varying |           |             |
| str    | character varying |           |             |
+--------+-------------------+-----------+-------------+
Server: foobar
FDW Options: (layer 'tb2')


fid列は前のバージョンからありましたが、今回の書き込み機能でも使われてます(無い場合は書き込み不可)。
(https://github.com/pramsey/pgsql-ogr-fdw/blob/master/README.md)
...
Writeable FDW Tables
...
Writeable tables only work if you have included a fid column in your table definition. By default, tables imported by IMPORT FOREIGN SCHEMA or using the example SQL code from ogr_fdw_info include a fid column.


OGR_FDWで書き込み可能な外部テーブルを作る際、手動で1テーブルずつ定義する場合はfid列を忘れないようにしないといけません。でもIMPORT FOREIGN SCHEMAを使えば自動でやってくれるので便利。


テーブル読み込み・書き込みテスト(1)

一つ目のSQLiteテーブル。列col1, col2を、型を省略して定義しました。前項で見たとおりPostgreSQLの外部テーブルではvarchar型です。

ゼロ行の状態から、PostgreSQLINSERTすると ↓ 普通にできました。列の型制約は実質なく、INTでもFLOATでもタイムスタンプでも、自動的に文字列に変換して入力する模様。
# select * from test_sqlite.tb1; 
+-----+------+------+
| fid | col1 | col2 |
+-----+------+------+
+-----+------+------+
(0 rows)

# insert into test_sqlite.tb1 values (1, 2, 3);
# insert into test_sqlite.tb1 values (2, 'foo', 'bar');
# insert into test_sqlite.tb1 values (3, now(), random());

# select * from test_sqlite.tb1;
+-----+-------------------------------+-------------------+ 
| fid |             col1              |       col2        |
+-----+-------------------------------+-------------------+
|   1 | 2                             | 3                 |
|   2 | foo                           | bar               |
|   3 | 2016-09-02 17:47:36.425361+09 | 0.556451540440321 |
+-----+-------------------------------+-------------------+
(3 rows)


↓ 元テーブルにないfid列が実質SERIAL型の一意な列。INSERT時に省略でき、自動的に最大値+1が入ります。重複する値を入れるクエリはエラー。
# insert into test_sqlite.tb1 (col1, col2) values (4, 5); 
(ok)

# insert into test_sqlite.tb1 values (3, 4, 5); 
ERROR:  failure writing OGR feature
HINT:  sqlite3_step() failed:
  UNIQUE constraint failed: tb1.rowid (19)


テーブル読み込み・書き込みテスト(2)

二つ目のSQLiteのテーブル。id列をINT PRIMARY KEY、str列をTEXTとして定義しました。でも型制約はやっぱり実質ない模様。PostgreSQLINSERTで ↓ 普通に文字列が入ります。ただし主キーとしての一意制約は有効で、同じ値(ここでは文字列foo)を入れるクエリはエラーに。
# select * from test_sqlite.tb2;
+-----+----+-----+
| fid | id | str |
+-----+----+-----+
+-----+----+-----+
(0 rows)

# insert into test_sqlite.tb2 values (1, 'foo', 'bar'); 
# insert into test_sqlite.tb2 values (2, pi(), exp(1));
# insert into test_sqlite.tb2 values (3, 'foo', 'bar');
ERROR:  failure writing OGR feature
HINT:  sqlite3_step() failed:
  UNIQUE constraint failed: tb2.id (19)

# select * from test_sqlite.tb2;
+-----+------------------+------------------+
| fid |        id        |       str        |
+-----+------------------+------------------+
|   1 | foo              | bar              |
|   2 | 3.14159265358979 | 2.71828182845905 |
+-----+------------------+------------------+
(2 rows)


前項も含め、結果の確認。元テーブルをSQLiteクライアントで開くと ↓ 確かにPostgreSQLからのINSERTが実行されてました。
$ sqlite3 r:/tmp/foobar.db
SQLite version 3.14.1 2016-08-11 18:53:32
Enter ".help" for usage hints.

sqlite> select * from tb1;
2|3
foo|bar
2016-09-02 17:47:36.425361+09|0.556451540440321 
4|5

sqlite> select * from tb2;
foo|bar
3.14159265358979|2.71828182845905


現状ではDELETEUPDATE失敗

先に引用したOGR_FDWREADME.mdではINSERTだけでなくDELETE, UPDATEもできそうな感じですが、今回のSQLiteでは不可でした。OGR_FDWでなくOGR自体とSQLiteとの関係によるのかも(今後調べます)。

DELETEの場合 ↓
# delete from test_sqlite.tb1 ;
ERROR:  foreign table "tb1" does not allow deletes 


DELETEが単にnot allowで拒否されるだけなのに対し、UPDATEは ↓ database is lockedで失敗します。この後はINSERTもできなくなる状況で、いったんDB接続を切って再接続したらINSERTは可能な状態に復帰。
# update test_sqlite.tb1
    set col1 = 'updated on FDW'
    where fid = 1; 
ERROR:  failure writing back OGR feature
HINT:  sqlite3_step() failed:
  database is locked

# insert into test_sqlite.tb1 values (9, 'foo', 'bar');
ERROR:  failure writing OGR feature
HINT:  sqlite3_step() failed:
  database is locked (5)


このように現状はINSERTのみですが、OGR_FDW 1.0.2を使ってPostgreSQLからSQLiteに書き込めました。次回は実用的なテストで、
831日にpg_dumpを経由して行ったPostgreSQL → SQLite3のデータエクスポートをできるか試します書きました。