Part 2 - CashBook

テキストファイルのみで構築した投票集計システムを RDB で作成してみよう。ただ単に投票集計するだけであれば、テキストファイルへのデータ記録でも事足りるが、スクリプト管理を進める目標は、多重同時更新などにも耐えうるものへと発展させるところにある。テキストファイルのみでの管理と比較しつつ構築を進めよう。

テーブルの作成

投票管理は、投票者(voter)と候補(cand)の組み合わせを多数保存できればよい。この2カラムのテーブルを作成しよう。1人1票の制約は UNIQUE制約 で実現できる。 sqlite3 コマンドを利用して、以下のようにテーブル作成する。

作成したテーブルは、.schema コマンドで確認できる(".sch" まで省略可能)。

レコードの挿入

これにデータを入れていく。SQL の INSERT 文にて1件分の新しいレコードを挿入するには以下のようにする。

投票箱のテーブル ballotbox
カラム	型	用途
voter	TEXT	投票者
cand	TEXT	投票対象(候補のうちの1つ)

INSERT INTO テーブル(カラム₁, カラム₂, ...) 
       VALUES(カラム1の値, カラム2の値, ...);

INSERT INTO テーブル VALUES(カラム1の値, カラム2の値, ...);

前者は値挿入するカラムを決めて値を指定する方法、後者はそのテーブルのすべてのカラムの値を指定する方法である。さほど複雑ではないが、のちのち多重同時実行での更新実験をすることをふまえてこれを補助するデータ挿入スクリプトを作成する。また、実際には Web やメイルなどのネットワークプロトコル経由で投票されることになるだろうが、最終的な処理は単純なスクリプトに落とし込むことができる。

sqlite3 コマンドではデータベースファイルの次の引数に SQL 文を与えることができるので、そこにINSERT文を指定する。次のスクリプト例ではシェル関数 query() を定義して、自身の引数に与えられたものをSQL文としてsqlite3に渡す手順としている。

vote0.sh

#!/bin/sh

query() {				# 関数化しておく
  sqlite3 vote.sq3 "$@"
}
if [ -z "$1" -o -z "$2" ]; then	# 第1、第2引数ともにNULLでないことを確認
  cat<<-USAGE
	Usage: $0 "投票用ID" "候補"
	例: $0 id000 黒
	USAGE
  exit 1			# 引数不備時にはUsageを出して終了
fi
query "REPLACE INTO ballotbox values('$1', '$2');"

最後の部分で、「INSERT INTO」を「REPLACE INTO」に変更した。これは、重複を許さない制約を持つテーブルにおいて、挿入する値が既に存在する場合は新しい値に置き換えるものであり、今後しばしば利用する。

query 関数で受けた引数を sqlite3 コマンドに渡すときに "$*" ではなく "$@" を利用している。sqlite3 はデータベースファイルの指定の次の引数があればそれを SQL 文として解釈し、それ以上引数を指定するとエラーとなる。であれば、SQL 文受け渡し部分に、位置パラメータと同一個数の引数に展開する "$@" より、単一文字列に展開する "$*" の方がよさそうだが、"$*" はそこに該当する引数がなかったときに "" に展開される。すると、「echo SQL文 | query」としても、sqlite3 コマンドには "" が渡り、標準入力のSQL文を無視される。標準入力からの SQL 文も処理させたいことから、引数処理は "$@" を使用する。

実際にレコードを登録してみる。以下のような条件で投票を行なうことを想定する。

id001 が2回投票しているが、あとから投票した「黄」だけになっていることを確認する。

集計の出力

入力されたレコードをもとに、得票集計をしてみる。集約関数 count() と GROUP BY 句を用いれば得られる。

得票数で降順に並べるには ORDER BY を付加する。以下では得票の数え上げである count(cand) に cn という別名を付けている。

集計機能も包含するように管理スクリプトも以下のように変更してみる。

シェルスクリプトに与えられた引数(オプション)によって処理を切り替えるように vote0.sh を修正したものを示す。

vote1.sh

#!/bin/sh

query() {
  sqlite3 vote.sq3 "$@"
}

if [ -n "$1" -a -n "$2" ]; then		# $1 と $2 ともに空でなければ
  query "REPLACE INTO ballotbox values('$1', '$2');"
elif [ x"$1" = x"-s" ]; then		# -s オプションは集計
  query "SELECT cand,count(cand) cn FROM ballotbox \
	GROUP BY cand ORDER BY cn DESC;"
elif [ x"$1" = x"-l" ]; then		# -l オプションは一覧出力
  query "SELECT * from ballotbox ORDER BY voter;"
else
  cat<<-EOF
	Usage:
	  Add record:			$0 "Voter" OneOfCandidates
	  List all records:		$0 -l
	  Show summation of price:	$0 -s
	EOF
fi

このように、よく使う SQL 問い合わせをシェルスクリプトに埋め込むことで手間が軽減できる。

登録レコードの検査

今回の投票システムの条件では、候補は赤・青・黄のどれかだがそれに当てはまらないでたらめな投票をしてもレコードに入る。当てはまった場合のみ有効投票としたい。データの保存先をCSVなどのテキストファイルにする場合はスクリプト側で無効投票の検査を行わねばならないが、 RDBを用いる場合は、データベース側で検査してくれる。

テーブルの作成時にカラム制約を与える。まず、既存のテーブルを別の名前(bb0)に変更しておく。これはあとでレコードの再登録を省力化するためである。

続けて同じカラム構成でテーブルを作成するが、候補者カラム(cand)は候補者一覧に含まれるどれかでなければならないという制約を加える。このためにまず「候補者一覧」を登録するテーブル(candidates) を作成し、候補者を登録しておく。

投票箱となる ballotbox の再作成を行なう。この「候補者一覧」表の候補者(cand)に「candidatesテーブルのcandカラムの集合に含まれる」という制約を付加して CREATE TABLE する。

なお、SQLite3 (3.6.19以降)では制約つきのテーブルはつねに作れるが、標準状態では実際のレコード挿入では制約が無効化されている。明示的に有効化するには以下のようにする。

SQLite3 は起動直後はつねにOFFの状態なので、sqlite3 コマンド起動後に上記の文を発行するか、sqlite3 コマンドの -cmd オプションを利用して

さて、実験的に ballotbox テーブルに制約違反となるレコードを入れてみよう。実際には候補者にはない「紫」に投票してみる。

foreign_keys=ON になっている状態であればエラーが出てレコードの挿入には失敗する。もしエラーなしで挿入できてしまった場合は foreign_keys=OFF のままであるので状態を確認する。

ところで、外部キー制約を付ける前のテーブルを bb0 として残してある。ここに入っているレコードをそのまま新しい ballotbox テーブルに入れてみよう。

もともと bb0 にあったレコードは、候補者のみに正しく投票されているものであるため制約つきの ballotbox テーブルにも挿入できる。

以上の流れを統合し、以下の3つの処理すべてに対応したシェルスクリプトを作成する。

投票箱の制約つきテーブル ballotbox
カラム	型	用途	制約
voter	TEXT	投票者
cand	TEXT	投票対象(候補のうちの1つ)	candidatesテーブルのcandに含まれる

これらのうち後者2つは先述の vote1.sh で既に対応していた。これに -i オプションを追加して、初期化作業を行なうようにしてみよう。シェルスクリプトとしての構成を大幅に変え、シェル関数を用いて初期化・一覧表示・集計処理を分けるようにし、また、シェルスクリプト自身へのオプション解析に getopts を利用している。

vote2.sh

#!/bin/sh

dbfile=vote.sq3			# データベースファイル
query() {
  sqlite3 -cmd 'PRAGMA foreign_keys=ON' $dbfile "$@"
}

usage() {			# 使用法の説明出力
  cat<<-EOF
	Usage:
	  Add record:			$0 "Voter" OneOfCandidates
	  Initialize Database:		$0 -i
	  List all records:		$0 -l
	  Show summation of price:	$0 -s
	EOF
}

init() {			# 初期化
  rm -f $dbfile			# ファイルを消せばゼロからの開始となる
  cat<<-EOF | query
	CREATE TABLE candidates(cand TEXT UNIQUE);
	INSERT INTO candidates VALUES('赤');
	INSERT INTO candidates VALUES('青');
	INSERT INTO candidates VALUES('黄');
	CREATE TABLE ballotbox
	 (voter TEXT UNIQUE, cand TEXT, 
	  FOREIGN KEY(cand) REFERENCES candidates(cand));
	EOF
}
list() {
  query "SELECT * from ballotbox;"
}
summary() {
  query "SELECT cand,count(cand) cn FROM ballotbox 
		GROUP BY cand ORDER BY cn DESC;"
}

while getopts hils i; do	# 有効なオプションは h i l s のどれか
  case "$i" in
    i)	init; exit	;;
    l)	list	;;
    s)	summary	;;
    *)	usage; exit	;;	# 上記以外のオプション(-h含む)なら用法出力
  esac
done
shift $((OPTIND-1))		# getoptsを抜けたら必ずこれで引数を調整する

if [ -n "$1" -a -n "$2" ]; then
  query "REPLACE INTO ballotbox values('$1', '$2');"
fi

また、init 関数中で query() を呼ぶときに引数を与えず、標準出力をパイプで送り込んでいる。関数内部で呼び出している sqlite3 の標準入力に渡っている。

練習問題: 制約の徹底

「投票」に対する制約をより厳格化させたものを作成する。一般的な投票では、候補だけでなく投票権を持った人も有限集合になっている。そうでなければ投票者名義をランダムに発生させて無限投票ができることになる。

たとえば今回の投票において投票者と候補が以下のような集合に含まれなければならないという制約を課してみよう。

vote2.sh を拡張し、「投票者と候補それぞれが決められた集合の要素である」という制約を課したデータベースを作成し、これにしたがった投票システム vote.sh を作成せよ。なお、投票者および候補の一覧はそれぞれ voters.csv、candidates.csv というテキストファイルに1行1レコードで列挙されているものとする。書式はそれぞれ「ID,氏名」(2カラム)、「候補」(1カラム)という形式でたとえば以下のような行の集まりである。

voters.csv の例:

id001,秋田杉三
id002,大館童夢
id003,大曲花子
id004,六郷湧子
id005,能代把助
id006,美郷羅弁太
id007,雄勝小町

candidates.csv の例:

赤
青
黄

練習問題: 解答例

外部キー制約つきのテーブル関係を構築する部分が完成すれば集計や一覧処理は vote2.sh までと全く同様である。投票者一覧を voters.csv から得る部分は、foreign_key の設定に注意しつつ、以下のように init() 関数を書き換える。下線を施した部分が vote2.sh からの変更部分である(リスト全体は実行例のあとに示す)。

init() {			# 初期化
  rm -f $dbfile			# ファイルを消せばゼロからの開始となる
  cat<<EOF | query
.mode csv
CREATE TABLE voters(id TEXT UNIQUE, name TEXT);
.import voters.csv voters
CREATE TABLE candidates(cand TEXT UNIQUE);
.import candidates.csv candidates
CREATE TABLE ballotbox
 (voter TEXT UNIQUE, cand TEXT, 
  -- voterカラムの値は、votersテーブルのidの値である必要がある
  FOREIGN KEY(voter) REFERENCES voters(id),
  -- candカラムの値は、candidatesテーブルのcandの値である必要がある
  FOREIGN KEY(cand) REFERENCES candidates(cand));
EOF
}

問題文に例示した voters.csv と candidates.csv を与えて実際に処理を進めた実行例を示す。

: データベース初期化
./vote.sh -i
: 有効票の投票と確認
./vote.sh id001 赤
./vote.sh id002 赤
./vote.sh id003 青
./vote.sh id001 黄
./vote.sh id004 青
./vote.sh -l
id002|赤
id003|青
id001|黄
id004|青
: 無効票の投票では票の内訳は変わらない
./vote.sh id004 黒		# 「黒」などない
Error: FOREIGN KEY constraint failed
./vote.sh -l
id002|赤
id003|青
id001|黄
id004|青
: 投票者一覧にないidでは投票できない
./vote.sh id999 白		# id999は一覧にない
Error: FOREIGN KEY constraint failed
./vote.sh -s
青|2
赤|1
黄|1

このように、データベースのテーブル設計を工夫することで、シェルスクリプト本体での条件判断が不要となる。

vote.sh