旧ブログ（Seesaa）の本文検索をPHP + SQLite3で作る際、少し困った点。検索対象のHTMLデータがPostgreSQLにあり、改行や引用符を含むのでCSV or TSV経由で渡せない状況でした。結局、pg_dumpをINSERT文モードで実行 → 出力されたダンプから不要部分を削除 → SQLiteクライアントで実行、で解決。今後FDWでもっと簡単にできないか調べるけど※、とりあえず今回の手順をメモしておきます。
※ 追記9月4日：OGR_FDWが使えました。⇒ その1 / その2
実行環境
• Windows7 x64 + Cygwin 2.5.1 + ConEmu 150813g
• Windows版PostgreSQL 9.5.3 + Cygwin版psql & pg_dump
• Cygwin版SQLite 3.14.1
• Windowsは管理者権限ユーザ、PostgreSQL接続はスーパーユーザ
エクスポート対象のデータ
8月25日に使ったテーブル ↓ にある4列です。各行が旧ブログ記事で、日付、タイトル、タグ、本文をSQLiteに持っていき検索に使う流れ。
# \d kenpg_seesaa_list
Table "public.kenpg_seesaa_list"
+-----------+------+-----------+
| Column | Type | Modifiers |
+-----------+------+-----------+
| ymd | date | not null | <=
| url | text | not null |
| title | text | not null | <=
| tags | text | not null | <=
| html_file | text | not null |
| html | text | not null |
| body | text | | <=
+-----------+------+-----------+
Indexes:
"kenpg_seesaa_list_pkey" PRIMARY KEY, btree (ymd)
"kenpg_seesaa_list_url_idx" UNIQUE, btree (url)
列bodyはHTMLのうち記事部分。改行や引用符（シングル・ダブル）がたくさん入ってます。下のクエリは、シングルクォートの出現位置が先頭に近い順で表示するもの。含まれてない行（position関数の結果がゼロ）を後ろに回すためnullifを入れました。
# select body2
from kenpg_seesaa_list
order by nullif(position('''' in body2), 0);
改行や引用符のない「普通の」データなら、CSVかTSVで出力してSQLiteの.importコマンドで簡単に取り込み可能。今回は違う状況なので、以下の手順にしました。
pg_dumpでテーブルデータだけ、INSERT文でダンプ
pg_dumpはオプション -t で特定テーブルのみ対象にできます。列の絞り込みはできないので、必要な列だけの一時テーブルを作ってそれをSQLファイルにダンプ。オプション -a でデータのみ、--inserts で出力形式をINSERT文に。でないとCOPY文になりSQLiteで使えません。
# create unlogged table tmp
select ymd, title, tags, body2
from kenpg_seesaa_list;
# \! pg_dump -d works08 -h ::1 -U postgres -t tmp -f r:/tmp.sql -a --inserts
-- check output file
# \! ls -l r:/tmp.sql
-rw-r--r-- 1 Administrator None 3476959 Aug 31 16:53 r:/tmp.sql
上ではpsqlから外部コマンドとしてpg_dumpを実行してます。シェルから実行するのと同様に接続文字列（データベース、ホスト、ユーザ、必要ならパスワード）を指定。
SQLファイルをSQLite用に編集し .readコマンドで実行
ダンプ結果（SQLファイル）は ↓ こんな感じ。INSERTに続くVALUES句に、改行や引用符を含むデータもそのまま（ただしシングルクォートは2重になって）入ってます。
--
-- PostgreSQL database dump
--
-- Dumped from database version 9.5.3
-- Dumped by pg_dump version 9.5.4
SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;
SET search_path = public, pg_catalog;
--
-- Data for Name: tmp; Type: TABLE DATA; Schema: public; Owner: postgres
--
INSERT INTO tmp VALUES (...);
INSERT INTO tmp VALUES (...);
...
--
-- PostgreSQL database dump complete
--
これをSQLiteで実行できるよう（1）冒頭のSET文を削除（2）CREATE TABLE文を付加、と編集。コメントは残しても消してもよく、必要なコマンドは ↓ のみです。
create table tmp (ymd date, title text, tags text, body2 blob);
INSERT INTO tmp VALUES (...);
INSERT INTO tmp VALUES (...);
...
シェルに移り、適当なファイル名を指定してSQLite3を起動、.readコマンドで上のSQLファイルを実行すれば終わり。↓ 後半は確認で、行数が元と同じ693（旧ブログの全記事数）。あと記事本文の長さの最小・最大を出してます。
$ sqlite3 tmp.db
SQLite version 3.14.1 2016-08-11 18:53:32
Enter ".help" for usage hints.
sqlite> .read tmp2.sql
sqlite> .schema
CREATE TABLE tmp (ymd date, title text, tags text, body2 blob);
sqlite> select count(*) from tmp;
693
sqlite> select min(length(body2)) from tmp;
178
sqlite> select max(length(body2)) from tmp;
20649
この後、SQLiteを操作するPHPスクリプトを書いてサーバに上げ、前回UPした旧ブログ記事リストに組み込みました。PostgreSQLからSQLiteに持っていったデータは、今のところ特に問題ない模様。
実行コマンドまとめ
テンプレート的に書くと ↓ わずか4ステップ。でもpsqlとシェルとエディタとSQLiteクライアントを行き来するので、意外と面倒に感じました。SQLite3に書き込めるFDWがあれば試してみたいです※。今回と違う普通のデータでも役に立つと思うし。
# create unlogged table tmp as
select column1, column2, ... from table ;
$ pg_dump -d database -h host -U user -t tmp -f tmp.sql -a --inserts
(edit tmp.sql)
$ sqlite3 file.db
sqlite> .read tmp.sql
※ 追記9月4日：OGR_FDWが使えました。⇒ その1 / その2