Contents
実行環境
••
•
• ogr_fdw 1.0.2
•
手順まとめ
(1)SQLite3-- 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
OGR_FDW について
第以前のバージョンは読み込み専用でしたが、5
OGR_FDW 1.0.2 インストール
ソースはGitHubyum.postgresql.org/packages.php
Windows
winnie.postgis.net/download/windows/pg95/buildbot/extras/
zip
(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
ただし
↓ 参考まで、OGR_FDW
$ 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
以前のバージョンをインストール済みの場合
以前のバージョンとは、最初のSQLite のテーブルを IMPORT FOREIGN SCHEMA で一括インポート
以下、インストール後の簡単なテスト。psql# 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 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)
次に
# 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 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)
一つ目のゼロ行の状態から、PostgreSQL
# 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)
↓ 元テーブルにない
# 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)
二つ目の# 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)
前項も含め、結果の確認。元テーブルを
$ 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 失敗
先に引用したDELETE
# delete from test_sqlite.tb1 ;ERROR: foreign table "tb1" does not allow deletes
DELETE
# 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)
このように現状は