Part 6 - Resident sqlite process

SQLite にはDB接続中に限り有効なものがある。たとえば SQLite 関数 last_insert_rowid() は、その接続で直近に INSERT した行の rowid が返るが、接続を切ると消えるため sqlite3 プロセスを終了した時点で消える。また、一時テーブル (TEMPORARY TABLE) は接続中のみ有効なテーブルで一時的な結果保存に使えるが、これまた sqlite3 プロセス終了時に消える。

SQLite データベースにアクセスする際に、その都度その都度 sqlite3 プロセスを起動するのではなく、先行する問い合わせで得られた状態を使いたい場合もある。ここでは、シェルスクリプト全体で1つの SQLite 接続(sqlite3 プロセス)を利用する方法について説明する。

名前つきパイプ

sqlite3 へのSQL問い合わせを行なうには、文の入力と結果出力の 2つのチャンネルが必要である。通常はそれぞれ標準入力と標準出力が割り当てられる。名前つきパイプ(FIFO)は、複数のプロセス間で入出力を共有できる機構の一つで mkfifo コマンドで作成できる。簡単な使用例で働きを見て、 sqlite3 への適用方法を考察しよう。

名前つきパイプの使用実験

名前つきパイプは、書き込み可能なディレクトリに任意のファイル名で作成でき、そこへのデータの書き込みと読み出しは普通のファイルへ行なうのと同様の手順でできる。名前つきパイプにバイト列を書き込んでcloseすると、続いて同じ名前つきパイプから読み込みを行なうと全く同じものが読み出せる。

これを実験するために、2つの端末を起動し同じディレクトリにcdしておく。それぞれ「左」と「右」で表すことにする。

この例で分かるように、名前つきパイプは以下の性質がある。

名前つきパイプからのSQL文送信

では名前つきパイプを用いて起動状態の sqlite3 に問い合わせ文を送り続けるにはどうしたらよいだろう。先ほどの実験に続けて左右の端末で以下のように起動する。

左の端末	右の端末
mkfifo fifo
cat fifo (止まる)
	echo Hello > fifo
Hello (出力されcat終了)
(もう一度catする) cat fifo (止まる)
右端末の入力に1行ずつ反応し C-d タイプと同時にcat終了 abc def 12345	cat > fifo abc def 12345 `[C-d]`

左の端末	右の端末
sqlite3 < fifo (止まる)
	echo .help > fifo
(ヘルプメッセージが出力されsqlite3終了)
(もう一度起動する) sqlite3 < fifo (止まる)
右端末の入力に1行ずつ反応し C-d タイプと同時にsqlite3終了	cat > fifo .timer on create table foo(a,b); .schema `[C-d]`

cat のとき同様 fifo ファイルへの書き込みを close した瞬間に sqlite3 へ入力も終端に達しプロセス終了となる。シェルスクリプト起動中の任意のタイミングで、同一プロセスで動き続ける sqlite3 に文を送り続けるためには、名前つきパイプのファイルを close しないようにすればよい。このためにはシェルのリダイレクト構文で名前つきパイプファイルをopenし、ファイル記述子を割り当てておく。それには以下のように exec とリダイレクトの組み合わせを用いる。

これによりファイル記述子の3番で fifo ファイルが開き、明示的に close するかシェルプロセス終了まで fifo 書き込みが close されないので、それを読み込む sqlite3 プロセスも終了しない。同じ実験をファイル記述子3を用いてやりなおしてみる。

名前つきパイプからの問い合わせ結果受信

上記の実行例では、SQL文を打ち込んだシェルに結果が返って来ていず、それでは問い合わせ結果を右側端末のシェルが受け取れないことになる。 sqlite3 コマンドからの結果を名前つきパイプ経由で受け取ることを考えよう。

左の端末	右の端末
sqlite3 < fifo (止まる)
	(fifoファイルをファイル記述子3に) exec 3> fifo
	echo .help >&3
ヘルプメッセージが出力されるが sqlite3 は終了しない。
右端末の入力に1行ずつ反応し C-d をタイプしてもsqlite3継続	cat >&3 .timer on create table foo(a,b); .schema `[C-d]`
	ファイル記述子3を閉じる exec 3>&-
sqlite3 コマンドも fifo 読み出し終端で終了

今度は左側端末で sqlite3 の出力を fifo ファイルにして、右側端末で fifo ファイルを cat で読み取る。sqlite3 では問い合わせ結果の出力先をドットコマンド .output で変更できる。

注意すべきは、右端末で「cat fifo」が終わるタイミングで、名前つきパイプから問い合わせ結果をもらう場合は、出力側(sqlite3 コマンド)が出力を閉じるまで読み取りがブロックしてしまうという点である。この問題を回避するため、sqlite3 のドットコマンド .output で出力を別のものに切り替え、fifo への出力を close する。

まとめると、sqlite3 に名前つきパイプへの結果出力を行なわせる場合は、以下の順序で問い合わせ文を送出する。

左の端末	右の端末
	cat fifo
sqlite .output fifo
.help	ヘルプが出力されるが、出力が終わっても cat は終了しない。
.output stdout (出力を標準出力に戻す)	cat コマンドが終了しプロンプトに戻る。

.output 名前つきパイプ
SQL文
.output stdout

最後の stdout は標準出力を意味するキーワードである。stdout への切り替えは名前つきパイプへの出力を close するためのものなので別のダミーファイルの名前でも構わない。

また、SQLite 3.8.5 以降ではドットコマンド .once が使用できる。これは、「次の1つの問い合わせ文からの出力」に限り、指定したファイルに出力するためのもので、これを利用するなら上記の手順は

.once 名前つきパイプ
SQL文

常駐 sqlite3 プロセスとの相互通信

以上をまとめると、1つのシェルスクリプトで sqlite3 コマンドを常駐させ、つねにそのプロセスに SQL 文を送ってそこから結果を得るスクリプトは以下のような構成となる。

resid-sq3.sh

#!/bin/sh
tmpd=`mktemp -d -t resid.XXXXXX`	# 一時ディレクトリの作成
# 一時ディレクトリ作成に失敗したらエラーメッセージを出して abort
: ${tmpd:?Cannot create temporary directory}
ipipe=$tmpd/in		# SQL問い合わせ送出用名前つきパイプ名
opipe=$tmpd/out		# 結果受け取り用名前つきパイプ名
mkfifo $ipipe $opipe	# 2つの名前つきパイプを作成する
finalize() {		# 終了処理の関数
  query2 ".quit"
  wait			# .quit を送ってバックグラウンドプロセス終了を待つ
  rm -r $tmpd		# 一時ディレクトリ抹消
}
trap finalize EXIT INT QUIT TERM
db=${DB:-resid.sq3}

sqlite3 $db < $ipipe &	# $ipipe を入力として起動(先にしないと次がブロックする)
exec 3> $ipipe		# シェルのファイル記述子3で ipipe を open
rm $ipipe		# オープンしたあとはファイルを消してもアクセスできる

query1() {
  sqlite3 "$db" "$@"	# その都度 sqlite3 コマンドを呼ぶ方式
}
query2() {		# 常駐 sqlite3 と通信する方式
  echo ".output $opipe" >&3	# 結果出力用に $opipe を open
  if [ -z "$1" ]; then	# query関数に引数がなければ
    cat			# SQL文を標準入力から読み取り、
  else			# query関数に引数があれば
    echo "$@"		# 引数自体をSQL文として
  fi >&3		# ファイル記述子3に送り込む
  echo ".output stdout" >&3	# これで $opipe が閉じられる
  cat $opipe		# $opipe は close されたので cat してもブロックしない
}

query1 "CREATE TABLE IF NOT EXISTS re(a,b);"
query1<<EOF
INSERT INTO re values(1,2);	-- 無意味にデータを入れておく
INSERT INTO re values(3,4);
SELECT last_insert_rowid();
EOF
while true; do
  echo -n "問い合わせ文を入れてください(1)> " >&2
  read x || break	# C-d ならループを抜けて終了
  query1 "$x"		# query1 関数に問い合わせ文を送る
  lir=`query1 "SELECT last_insert_rowid();"`
  echo "last_insert_rowid() = $lir"
done

echo "常駐 sqlite3 に切り替えます。"
while true; do
  echo -n "問い合わせ文を入れてください(2)> " >&2
  read x || break	# C-d ならループを抜けて終了
  query2 "$x"		# query2 関数に問い合わせ文を送る
  lir=`query2 "SELECT last_insert_rowid();"`
  echo "last_insert_rowid() = $lir"
done

このスクリプトでは、比較のために2つの sqlite3 呼び出し関数を定義している。 query1 は問い合わせごとに毎回 sqlite3 を呼ぶもの、query2 は名前つきパイプを入力として常駐している sqlite3 と通信するものである。起動すると、C-d (EOF)のタイプまで連続して SQL 文を入力できる繰り返しが 2回実行される。それぞれ query1、query2 を利用する。

./resid-sq3.sh
2				← 2行入れたあとの last_insert_rowid()
問い合わせ文を入れてください(1)> INSERT INTO re VALUES(1,1);
last_insert_rowid() = 0		← 別プロセスなので0になる
問い合わせ文を入れてください(1)> C-d常駐 sqlite3 に切り替えます。
問い合わせ文を入れてください(2)> INSERT INTO re VALUES(2,2);
last_insert_rowid() = 4
問い合わせ文を入れてください(2)> INSERT INTO re VALUES(3,3);
last_insert_rowid() = 5
問い合わせ文を入れてください(2)> C-d

このように、常駐 sqlite3 を利用することで、シェルスクリプトの実行時に 1つのSQLiteデータベース接続を最後まで持ち越せるため、一時テーブルなども利用できる。また、プロセス起動の遅いシステムでは問い合わせごとの sqlite3 呼び出しを節約できる。

常駐プロセス利用のもう一つのメリットにシェルスクリプトの処理の離れた箇所でトランザクション処理の開始と終了を行なえることが挙げられる。その都度呼び出しでは、

query1<<EOF
BEGIN;		-- トランザクション開始
SQL文1
SQL文2
  :
COMMIT;		-- トランザクション終了
EOF

のように、COMMIT まで書き切る必要があるが、常駐 sqlite3 であれば

query2<<EOF
BEGIN;		-- トランザクション開始
SQL文1
EOF
 :
 : 別の処理(たとえば複雑なSQL文を発行する別のシェル関数を呼んだり、
 :          ifによって発行するSQL文を変えたりする処理など)
 :
query2<<EOF
SQL文
COMMIT;		-- トランザクション終了
EOF

擬似分散データベースサーバ

クライアント/サーバ方式のRDBMSは、データを格納するホストと問い合わせを行なうホストを分離できる。これにより複数のホストからデータベースにアクセスできる。とくに、クライアント側でデータベースの参照(読み取り)だけ行ないたい場合などではデータベース本体への無駄な書き込み権限を与えずに済む。

SQLiteの特徴でもある「DBユーザの概念がない」、「DBはファイル単位」という形態は手軽さというメリットをもたらすと同時に、デメリットとなりえる次のような性質を持つ。

1について、読み取り専用アクセスのみを許したいのであれば DBの所有者とは違うユーザIDで sqlite3 プロセスを動かせばよいので、これは解決できる。

2について、たとえばWebインタフェースでデータベースの一部を更新できるサービスを動かしているような場合に、Webクライアントからは更新できないマスターレコードの類を書き変えるために SQLite データベースに直接アクセスしたいとなったら、サーバ上にあるデータベースファイルにアクセスする必要がある。このようなときにはどうしたらよいだろうか。

これはうまく行かない。SQLite データベースファイルの排他制御は、異なるホストで同じファイルを同時にアクセスした場合にうまく機能しない。必ずデータベースファイルに直接アクセスするホストは1つにしなければならない。ということで解決策としては、

SSHによるリモート SQLite サーバの設定

ここでは、SQLite データベース保持サーバと、クライアントホスト双方で OpenSSH が利用できる状態だと仮定する。SSHの運用のしくみなど、詳細は OpenSSH のマニュアルを参照してほしい。

このような設定によってサーバ上の sqlite3 コマンドでデータベースファイルが使えるが、副作用として sqlite3 コマンド経由でサーバ上のシェルも起動できるようになる。仮にシェルを起動して任意のコマンドを起動したとしても、Web サーバ関連のファイルを破壊できないようなユーザ権限で sqlite3 を動かすことが重要となる。

繰り返しになるが、Web サービスの一部として SQLite を利用するシステムでは、

Webサーバでのデータベースファイル所有ユーザ	squser
上記ユーザのホームディレクトリ	/home/squser