旧ブログ(Seesaa)の本文検索をPHP + SQLite3で作る際、少し困った点。検索対象のHTMLデータがPostgreSQLにあり、改行や引用符を含むのでCSV or TSV経由で渡せない状況でした。結局、pg_dumpINSERT文モードで実行 → 出力されたダンプから不要部分を削除 → SQLiteクライアントで実行、で解決。今後FDWでもっと簡単にできないか調べるけど、とりあえず今回の手順をメモしておきます。

※ 追記94日:OGR_FDWが使えました。⇒ その1 / その2
Contents


実行環境

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


エクスポート対象のデータ

825日に使ったテーブル ↓ にある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)


bodyHTMLのうち記事部分。改行や引用符(シングル・ダブル)がたくさん入ってます。下のクエリは、シングルクォートの出現位置が先頭に近い順で表示するもの。含まれてない行(position関数の結果がゼロ)を後ろに回すためnullifを入れました。
# select body2
    from kenpg_seesaa_list
    order by nullif(position('''' in body2), 0); 


改行や引用符のない「普通の」データなら、CSVTSVで出力して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

※ 追記94日:OGR_FDWが使えました。⇒ その1 / その2