この章の目標

SQLite3によるバイナリデータの出し入れ

写真に限らずバイナリデータの格納もSQLite3 + シェルスクリプトで可能である。Web 利用による写真日記システムの設計の前に、SQLite3 データベースへのバイナリデータの出し入れの方法を説明する。

バイナリデータの16進クォート

データ中にASCIIのNULL文字(0x00)を含むデータは、 16進文字列にエンコードすることでSQLite3データベースに挿入することができる。 簡単なテーブルを作成してデータ挿入を行なってみる。

sqlite3 binary.sq3
CREATE TABLE files(filename PRIMARY KEY, content);
INSERT INTO files VALUES('foo', X'414243');
INSERT INTO files VALUES('bar', X'41004243');

INSERT 文にある「X'...'」という表記はクォート内の文字列を1バイトごとの 16進表記の並びとみなし、それらをその文字コードに直したバイト列に 変換される。一つめの X'414243' は文字コード 0x41、0x42、0x43 の並びの ABC であり、二つめの X'41004243' は ABC の A と B の間に 0x00(NULL文字)を挟むバイト列になる。

NULL文字は通常のSELECTでは取り出せないため、 hex 関数で16進化して取り出す。

SELECT filename, content FROM files;
foo|ABC
bar|A
SELECT filename, hex(content) FROM files;
foo|414243
bar|41004243

また、quote関数を用いると、結果を別のSQL文への入力に使える クォート形式で得られる。 

SELECT filename, quote(content) FROM files;
foo|X'414243'
bar|X'41004243'

16進クォート文字列の変換

バイナリファイルをデータベースに出し入れするスクリプトを作る。 バイナリファイルを読み込んで16進文字列を作成するには様々な方法がある。 標準入出力を介することのできる3つの方法を示す。

逆に、16進文字列をバイナリデータに戻す方法を2つ示す。

このうち xxd はテキストエディタ vim に添付されるコマンドである。 オプションだけで操作できるのでコマンドラインで使うには重宝する。 本稿では既にPerlの使用を前提としているので Perl を利用するシェル関数を作成する。Perl が利用できない場合は 他の選択肢で置き換えればよい。

バイナリデータのデータベース入出力

以上をふまえてバイナリファイルをデータベースに出し入れする 場合に有用なシェル関数を作成する。

hexize/unhexize関数

hexize() {
  perl -ne 'print unpack("H*", $_)'
  # hexdump -ve '1/1 "%.2x"'
  # xxd -p
}
unhexize() {
  perl -n -e 's/([0-9a-f]{2})/print chr hex $1/gie'
  # xxd -r -p
}

このシェル関数を利用して、上記 binary.sq3 ファイルに入れた 2つめのデータを取り出す例を示す。

sqlite3 binary.sq3 "SELECT quote(content) FROM files 
	WHERE filename='bar'" | unhexize > out.bin
od -t x1 -c out.bin
0000000    41  00  42  43                                                
           A  \0   B   C                                                
0000004

また、バイナリファイルをデータベースに挿入する場合はシェル関数 hexize を用いると以下のように行なえる。

jpgfile=hoge.jpg
  :
sqlite3 binary.sq3<<EOF
REPLACE INTO files VALUES('$jpgfile', X'`hexize < $jpgfile`');
EOF

コマンドラインに指定する引数には長さ制限があり、長大な SQL 文はコマンドラインには入り切らない場合がある。長くなりそうな SQL 文は sqlite3 コマンドの標準入力に送り込む形を取るのが望ましい。

練習問題: バイナリファイルの出し入れ

バイナリ/テキスト種別を問わず、 データベースファイルに複数のファイルを格納でき、 ファイル名指定で取り出せるようなものを作ってみよう。

任意のファイルを SQLite データベースに格納・取り出しするためのインタフェースとなるスクリプト sqlar.sh を作成せよ。コマンドラインインタフェースは以下のようなものとする。

sqlar.sh サブコマンド DBファイル [ 対象ファイル... ]

サブコマンドの働きは以下のとおりである。

サブコマンド働き
a DBファイル Files... Files... で指定したファイル(群)をデータベースに追加
d DBファイル Files... データベースから指定したファイル群を削除
l DBファイルデータベース内に格納されているファイル一覧を出力
x DBファイル Files...データベースから指定したファイル群を取り出す(既存ファイルは上書きする)

格納するファイルはファイル名のみで識別するものとする。

練習問題: 解答例

サブコマンドに応じたシェル関数を作成し、 第1引数に応じて処理を切り替えるように作成した。 エラー処理などはほとんど省略したものを示す。

sqlar.sh

#!/bin/sh
PATH=/usr/local/sqlite3/bin:$PATH

if [ -z "$2" ]; then	# $2=DBファイル がなければUsageを出して終了
  cat<<-EOF >&2
	Usage: $0 SUBcommand SQLarchive [ Files... ]
	SUBcommands are as follows:
	  a	Add Files... to SQLarchive.
	  d	Delete Files... from SQLarchive.
	  l	List files in SQLarchive.
	  x	Extract files from SQLarchive.
	EOF
  exit 1
fi
cmd=$1; shift	# 第1引数はサブコマンド
db=$1;  shift	# 第2引数はDBファイル
query() {
  sqlite3 $db "$@"
}
mtime() {	# ファイルの修正時刻を touch -t の書式に変換する
  perl<<-EOF
	use POSIX "strftime";
	use File::stat;
	print strftime "%Y%m%d%H%M.%S", localtime(stat("$1")->mtime);
	EOF
}
hexize() {
  perl -ne 'print unpack("H*", $_)'
}
unhexize() {
  perl -n -e 's/([0-9a-f]{2})/print chr hex $1/gie'
}
query<<EOF	# アーカイブ用テーブルがなければ作成する
CREATE TABLE IF NOT EXISTS files(
  filename text PRIMARY KEY, mtime, data blob
);
EOF

addfiles() {	# ファイル追加
  for f; do	# 引数すべてをファイルとみなして繰り返す
    if [ ! -s $f ]; then	# ファイルがなければスキップ
      echo Empty or nonexistent file $f skipped >&2
      continue
    fi
    mt=`mtime $f`
    fn=`echo "$f" | sed "s/'/''/g"` # ファイル名のクォートをクォート
    query<<-EOF
	REPLACE INTO files VALUES(
	 '$fn',
	 '$mt',
	 X'`hexize < $f`');
	EOF
  done
}
delfiles() {	# ファイル削除
  for f; do
    fn=`echo "$f" | sed "s/'/''/g"` # ファイル名のクォートをクォート
    echo "DELETE FROM files WHERE filename='$fn';"
  done | query
}
listfiles() {	# 格納ファイル一覧
  query<<-EOF
	.mode column
	.head on
	SELECT filename, mtime, length(data) size FROM files
	ORDER BY filename;
	EOF
}
extractfiles() { # ファイル抽出(絶対パスは相対パスに変換)
  for f; do
    fn=`echo "$f" | sed "s/'/''/g"`	 # クォートをクォート
    cond="WHERE filename='$fn'"
    outfile=`echo "$f" | sed 's,^//*,,'` # 先頭からの / を除去
    dir=`dirname $outfile`
    test -d $dir || mkdir -p $dir
    query "SELECT hex(data) FROM files $cond;" \
	| unhexize > $outfile
    touch -t `query "SELECT mtime FROM files $cond;"` $outfile
  done
}
case $cmd in
  a)	addfiles "$@" ;;
  d)	delfiles "$@" ;;
  l)	listfiles ;;
  x)	extractfiles "$@" ;;
esac
yuuji@koeki-u.ac.jp