8月31日のPostgreSQL → SQLiteエクスポートが若干面倒だったので、書き込み可能な外部データラッパ（FDW）を探したらOGR_FDWの最新版1.0.2がありました。今日は最初の準備と簡単なテスト結果。UPDATEとDELETEは無理っぽいけどINSERTは普通にでき※、結構使えそうです。 ※ 追記9月4日：TEXT型のINSERTには要注意と判明しました。詳細はこちら

実行環境

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

手順まとめ

（1）SQLite3のDBを準備（既にあれば不要）、（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について

第7回PostgreSQLアンカンファレンスでのスライドを見て下さい。この時の最後に、SQLiteに接続できる件だけ紹介しました。今日はその実践の入口になります。以前のバージョンは読み込み専用でしたが、5月17日に出た1.0.2で、SQLite3に限らず（というか主眼であるGISの）様々なデータフォーマットに書き込み可能になりました。言い換えるとWRITABLEな外部テーブルを作れます。

OGR_FDW 1.0.2インストール

ソースはGitHubのrelesesから。パッケージは、例えばYumのPostgreSQL 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リリースの5月17日以降のものを選択。（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/PythonやPL/Rと違って）。ただしPostGISのエクストラ的な位置づけなので、PostGISで入ってる前提の2つのDLL（libgcc_s_seh-1.dll、libstdc++-6.dll）が同梱されてません。先ほどのextrasフォルダの上位階層からPostGISのzipをダウンロードすれば入ってます。 ↓ 参考まで、OGR_FDWのzip内の全ファイルについて依存するDLLを列挙してまとめ表示するコマンドと結果。... の部分はobjdumpのWarning（バイナリのファイルじゃない旨）がばーっと出ます。

$ 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です。どちらかをDBにExtensionとして登録済みだった場合、普通はExtensionも更新することになりますが（ALTER EXTENSION ... UPDATE）、今のところOGR_FDW 1.0.xはExtension上は同じもの（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のデータベースファイルをデータソースにします。ファイル指定は絶対パスの他、PostgreSQLのbinフォルダからの相対パスでも可。ファイルが見つからないと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型です。ゼロ行の状態から、PostgreSQLでINSERTすると ↓ 普通にできました。列の型制約は実質なく、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として定義しました。でも型制約はやっぱり実質ない模様。PostgreSQLのINSERTで ↓ 普通に文字列が入ります。ただし主キーとしての一意制約は有効で、同じ値（ここでは文字列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

現状ではDELETEとUPDATE失敗

先に引用したOGR_FDWのREADME.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に書き込めました。次回は実用的なテストで、8月31日にpg_dumpを経由して行ったPostgreSQL → SQLite3のデータエクスポートをできるか~~試します~~書きました。