これまでの流れを総合してカタログ型データベースを組み立てよう。
実用を意識したデータベースシステムとするため、 前節での解説と実装例から追加する仕様について簡単に説明しつつ 完成版を示す。
テーブル初期化スクリプトでは、カタログに格納する属性定義を cards.def から読むようにしているが、将来的に登録属性が増えた場合に自動的にそれをテーブルに反映させるようにしたい。 そのため、テーブルを作成したときの日付を記録し、cards.def がそれより新しいタイムスタンプを持つ場合にテーブルを再構築するようにした。 また、個別のレコードごとにも日付けを持たせるために、cards_s、cards_m 両テーブルに登録日時を記録するための mtime カラムを追加した。 そのように変更したスクリプトを以下に示す。
[ -n "$myname" ] || { echo Use this as library; exit 1; }
_ct=_columns
_tb=cards
cards_stamp=db/_col.stamp # タイムスタンプファイル
. $mydir/cards-cgi.sh
logfile=tmp/init.log # 初期化SQLのログファイル
cards_initdb() {
echo "---------- init-db at `date` ----------" >> $logfile
grep -v '^#' "$1" | {
cat<<-EOF
DROP TABLE IF EXISTS $_ct;
CREATE TABLE $_ct(
attrname text,
attr text PRIMARY KEY,
attrmode text,
vtype text,
option text
);
CREATE TABLE IF NOT EXISTS $_tb(id text PRIMARY KEY);
CREATE TABLE IF NOT EXISTS ${_tb}_s (
id, key text, val, bin blob, mtime text,
FOREIGN KEY(id) REFERENCES $_tb(id)
ON DELETE CASCADE ON UPDATE CASCADE,
UNIQUE(id, key)
);
CREATE TABLE IF NOT EXISTS ${_tb}_m(
id, key text, val, bin blob, mtime text,
FOREIGN KEY(id) REFERENCES $_tb(id)
ON DELETE CASCADE ON UPDATE CASCADE,
UNIQUE(id, key, val)
);
EOF
while IFS=: read aname attr atmode vtype opt; do
cat<<-EOF
REPLACE INTO $_ct VALUES(
'$aname', '$attr', '$atmode', '$vtype', '$opt'
);
EOF
done
} | tee -a $logfile | query && echo "$1" > $cards_stamp # ログにも記録
exit # テーブル初期化と同時にタイムスタンプファイルを作成
}
cards_update() (
if [ -s "$cards_stamp" ]; then
def=`cat "$cards_stamp"` # 定義ファイルはスタンプファイルに書いてある
if [ -s "$def" -a "$def" -nt "$cards_stamp" ]; then
echo Updating $_ct using $def... 1>&2
cards_initdb "$def" # 定義ファイルが新しければ自動的に再構築
fi
fi
)
利用者が最初に開くWebページとなる CGI スクリプトを以下の仕様で作成する。
本体スクリプトを以下に示す。
#!/bin/sh
mydir=`dirname $0`
myname=`basename $0`
. $mydir/cgilib2-sh
. $mydir/cards-init.sh
. $mydir/cards-cgi.sh
cards_update
shift $((OPTIND-1))
case "$1" in
view/*) view=${1#view/} ;;
cat/*) cards_cat ${1#cat/} ;;
esac
cards_entry
cards_putform $view
cards_list
cards_footer
上記プログラム中で読み込んでいる、実行部本体である cards-cgi.sh を以下に示す。
[ -n "$myname" ] || { echo Use this as library; exit 1; }
_m4dir=$mydir/templ
percent() sed 's/\(..\)/%\1/g'
imgsrcdata_icon() { #hex(画像データ)を複合して縮小して img src にする関数
printf "<img src=\"data:image/jpeg,"
unhexize | convert -resize '150x150>' - jpeg:- | hexize | percent
echo '">'
}
cards_cat() { # $1={s,m}/rowid
# sまたはm のテーブルの rowid にあるバイナリデータをHTTPで書き出す
tbl=${_tb}_${1%/*} rowid=${1##*[!0-9]} of=$tmpd/bin
query "SELECT hex(bin) FROM $tbl WHERE rowid=$rowid" | unhexize > $of
fn=`query "SELECT val FROM $tbl WHERE rowid=$rowid"`
if [ -s "$of" ]; then
contenttype $(file --mime-type $of | cut -d' ' -f2 | tr -d '\n')
echo "Content-Disposition: filename=\"$fn\""
echo "Content-Length: `wc -c < $of`"; echo
cat $of
else
contenttype text/plain; echo
echo "File specification error"
fi
exit # データをcatしたらすぐ終了
}
action_button() { # $1=name, $2=jobvalue, $3=label, $4=label-title
# 温存/変工/新規/削除 の切り替えボタンのどれかを生成する
cat <<-EOF
<input class="action" type="radio" name="action.$1" value="$2"
id="$2.$1"><label for="$2.$1"${4:+ title="$4"}>`escape "$3"`</label>
EOF
[ x"$2" = x"rm" ] && cat<<-EOF
<span class="confirm">本当に消しますか:<label>
<input class="confirm" type="checkbox" name="cfm.$1" value="yes"
>はい</label></span>
EOF
}
cards_input() { # $1=attr, $2=type, $3=option, $4=value(optional), $5=rowid
# typeに応じたinput要素を出力
nm="name=\"$1\"" option="$3" at="${ATTR:+ $ATTR}" escv=`escape "$4"`
case "$2" in
image)
echo "<input$at $nm type=\"file\">" ;;
textarea)
echo "<textarea$at $nm${option:+ }$option>$escv</textarea>" ;;
text|hidden|password)
echo "<input$at type=\"$2\" $nm value=\"$escv\">" ;;
radio)
for v in $option; do
[ x"$v" = x"$4" ] && ch=" checked" || ch=""
echo "<input$at type=\"$2\" $nm value=\"`escape $v`\"$ch>"
done ;;
[Ss]elect)
echo "<select$at $nm>"
for v in $option; do
[ x"$v" = x"$4" ] && s=" selected" || s=""
echo "<option$s>`escape $v`</option>"
done
echo "</select>" ;;
esac
printf "${5:+%s\\n}" "<input type=\"hidden\" name=\"rowid.$1\" value=\"$5\">"
}
cards_view() { # $1=id のデータを表形式にして出力
submit='<span class="heresub"><input type="submit" value="確定">
<input type="reset" value="リセット"></span>'
echo '<table class="form">'
cond="WHERE id=(SELECT id FROM cards WHERE rowid=$1)"
cat<<-EOF |
WITH getall AS (
SELECT rowid rid, * FROM cards_s $cond
UNION ALL
SELECT rowid rid, * FROM cards_m $cond
)
SELECT lower(c.attrmode), a.rid, c.attr, c.vtype, c.option, c.attrname, a.seq,
hex(val) hval, hex(bin) hbin, mtime
FROM _columns c
LEFT JOIN
(SELECT l.rid, l.key, count(l.rid) seq, l.val, l.bin, l.mtime
FROM getall l LEFT JOIN getall r
ON l.key=r.key AND l.rid>=r.rid
GROUP BY l.key, l.rid) a
ON c.attr=a.key
ORDER BY c.rowid;
EOF
query | { # 上記SQL文をパイプで受ける
while IFS="|" read tb rid attr vtype opt kname seq hval hbin mtime; do
val0=`echo "$hval"|unhexize`
var="$attr.${seq:-0}.$tb"
newvar="$attr.0.$tb"
rmbtn=`action_button "$var" rm 削除`
newinpt=$(ATTR='placeholder="新規追加項目"' \
cards_input "$newvar" "$vtype" "$opt")
spannew="<span class=\"new\">$newinpt</span>"
case "$tb" in # 格納先テーブルによって切り替える
"p") # 主キーなら編集不要 削除ボタンのみ出す
id=$(escape `query "SELECT id FROM cards WHERE rowid=$1"`)
rid=$(query "SELECT rowid FROM cards WHERE rowid=$1")
cat<<-EOF
<tr><th>$kname</th><td>$id$rmbtn<span class="confirm c0"><br>
(レコード全体を消します!!)</span>$submit
<input name="id.0.p" type="hidden" value="$id">
<input name="id" type="hidden" value="$id">
<input name="rowid.id.0.p" type="hidden" value="$rid">
<input name="update" type="hidden" value="$id"></td></tr>
EOF
continue ;;
"m") th='th class="multi"'
multi=`action_button "$var" new 新規 この行の項目とは別に作成`
kname="$kname$seq" # 連番を振る
;;
"s") th='th class="single"' multi=""
[ -n "$val0" ] && spannew="" ;;
esac
case "$hbin" in
"") img=""; val=$val0 ;; # 文字列値のみ
*) val="" # バイナリ値がある場合
case "$val0" in # jぺgJPEGとPNGのみ考慮
*[Jj][Pp][Gg]|*[Pp][Nn][Gg])
href="<a href=\"$myname?cat/$tb/$rid\">"
img="$href`echo \"$hbin\"|imgsrcdata_icon`</a>" ;;
esac
esac
if [ -n "$val0" ]; then # 値があるなら表示編集切り替えボタン出力
bt=$(action_button $var keep 温存
action_button $var edit 変更 この行の項目の修正
)"$multi${val0:+$rmbtn}<br>"
inpt='<span class="edit">'$(
cards_input "$var" "$vtype" "$opt" "$val" "$rid")'</span>'
else # 値がないなら新規入力フォームのみ出力
bt='' #bt="<input type=\"hidden\" name=\"$ivar\" value=\"edit\">"
spannew="$newinpt"
inpt=""
fi
inpt="$inpt$spannew$submit"
mt="<br><span class=\"mtime\">${mtime%:*}更新</span>"
echo "<tr><$th>$kname${mtime:+$mt}</th>
<td>$bt<span class=\"value\">`escape \"$val\"`$img</span>$inpt</td></tr>"
done }
echo '</table>'
}
cards_m4() m4 -I $_m4dir "$@"
cards_newinputs() {
echo "<h2>新しく作成するID</h2>"
echo '<table class="form">'
query "SELECT * FROM $_ct WHERE attrmode LIKE 'p';" |
while IFS='|' read attrname attr attrmode vtype option; do
printf '<tr><td>%s</td>\n<td>' "`escape \"$attrname\"`"
cards_input "$attr" "$vtype" "$option"
echo "</td></tr>"
done
echo '</table>'
}
cards_putform() {
contenttype; echo
cards_m4 header.m4.html
if test -n "$1" && # [ cond1 -a cond2 ] だと必ずcond2が評価されるので
test -n "`query \"SELECT id FROM $_tb WHERE rowid=$1;\"`"; then
cards_view "$1" \
| cards_m4 -D_ACTION_="$myname?view/$1" \
-D_FORMS_="syscmd(cat)" form.m4.html
else
cards_newinputs \
| cards_m4 -D_ACTION_="$myname" \
-D_FORMS_="syscmd(cat)" form.m4.html
fi
}
cards_footer() cards_m4 footer.m4.html
cards_update_byid() {
pkey=`query "SELECT attr FROM $_ct WHERE attrmode LIKE 'p';"`
id=$(escape `getpar $pkey`)
[ -z "$id" ] && return
if [ x"`getpar action.id.0.p``getpar cfm.id.0.p`" = x"rmyes" ]; then
query "DELETE FROM cards WHERE id='$id';"
return # PRIMARY KEYの削除指定なら削除して即終了
elif [ x"$(query "SELECT id FROM cards WHERE id='$id';")" = x"" ]; then
view=$(query "INSERT INTO cards VALUES('$id');
SELECT last_insert_rowid();")
return # id指定がない場合も即終了
fi
[ -z "$view" ] && view=`query "SELECT rowid FROM cards WHERE id='$id';"`
for t in s m; do # cgiparsテーブルに投稿された値のコピーを
tb=${_tb}_$t # cards_s, cards_m 各々に対して繰り返す
cat<<-EOF
WITH pars AS (SELECT rowid,* FROM cgipars WHERE tag='$_tag')
REPLACE INTO $tb(rowid, id, key, val, bin, mtime)
SELECT (SELECT val FROM pars WHERE name='rowid.'||c.name) rid,
'$id',
substr(name, 1, instr(name,'.')-1) attrname,
CASE (SELECT vtype FROM $_ct WHERE name LIKE attr||'.%')
IN ('image', 'binary')
WHEN 1 THEN filename
ELSE val
END val,
CASE (SELECT vtype FROM $_ct WHERE name LIKE attr||'.%')
IN ('image', 'binary')
WHEN 1 THEN val
ELSE NULL
END bin,
datetime('now', 'localtime')
FROM pars c
WHERE name NOT LIKE 'rowid.%'
AND name LIKE '%.$t'
AND (EXISTS (SELECT * FROM pars WHERE
name='action.'||c.name AND val='edit')
OR name LIKE '%.0.$t' AND val>'');
WITH pars AS ( /* 削除指定と削除確認ボタンyesなら削除 */
SELECT rowid,substr(name, instr(name, '.')) nm,name,val
FROM cgipars WHERE tag='$_tag'
) DELETE FROM $tb
WHERE rowid in (SELECT val FROM pars c
WHERE name LIKE 'rowid.%'
AND EXISTS (SELECT * FROM pars WHERE
name='action'||c.nm AND val='rm')
AND EXISTS (SELECT * FROM pars WHERE
name LIKE '%.$t'
AND name='cfm'||c.nm AND val='yes'));
EOF
done | query
}
cards_entry() {
upid=`getpar id` # id指定があればそのレコードを更新
[ -n "$upid" ] && cards_update_byid "$upid"
}
cards_gensql() ( # 横持ちを縦持ちに変えるSQL文を生成する
s=""
for i; do
s="$s${s:+, }'x:'||max(CASE key WHEN '$i' THEN val END) '$i'"
done
cat<<-EOF
SELECT cards.rowid || ':' || id ID, $s
FROM cards LEFT JOIN cards_s USING(id)
GROUP BY id ORDER BY cards.id;
EOF
)
cards_list() { # 単一値の値を2つ選んで表形式出力する
href1="<a href=\"$myname?view/"
cols=`query "SELECT attr FROM $_ct WHERE attrmode LIKE 's' AND \
vtype='text' ORDER BY rowid LIMIT 2;"`
echo '<table class="list">'
cards_gensql $cols | sqlite3 -html -header $_db \
| sed -e "s|>\([0-9]*\):\(.*\)<|>${href1}\1\">\2</a><|" -e 's/>x:/>/'
echo '</table>'
}
上記スクリプトで m4 経由で出力しているテンプレートファイル、 header.m4.html、form.m4.html、footer.m4.html を順に示す。
define(`__T__', ifdef(`_TITLE_', _TITLE_, `Cards DB'))dnl
<!DOCTYPE html>
<html lang="ja">
<head><title>__T__</title>
<link rel="stylesheet" type="text/css" href="cards.css">
<meta name="viewport" content="width=device-width, initial-scale=1" />
</head>
<body`'ifdef(`_BODYCLASS_', ` 'class="_BODYCLASS_")>
<h1>__T__</h1>
<form action="_ACTION_" method="POST" enctype="multipart/form-data">
_FORMS_
<input type="submit" value="送信">
<input type="reset" value="リセット">
</form>
</body></html>
擬似的な動的フォームをCSS定義している cards.css は以下のとおりである。
table.list, table.list td, table.list th, table.form,
table.form td, table.form th{
border: 1px solid black; padding: 0.2ex 1ex;
}
table.form, table.list {border-collapse: collapse;}
table th {background: #fcf;} /* 単一値カラムと複数値カラムは */
th.multi {background: #fef;} /* 別の背景色にしておく */
/* 「温存/編集/新規/削除」ボタンとラベルは完全透明にしておくが、 */
input.action, input.action + * {opacity: 0.0;} /* 該当行をマウス hover */
tr:hover input.action, tr:hover input.action + *, /* またはタップ */
tr:active input.action, tr:active input.action + * { /* したら見せる */
opacity: 1.0;}
/* 修正用input、新規入力input、「新規」チェック時の値表示、
「修正」チェック時の値表示 は、すべて非表示にする */
span.edit, span.new, input[value="new"]:checked ~ span.value,
input[value="edit"]:checked ~ span.value {/* 「修正」チェックで */
display: none;} /* 値表示部分を非表示に */
input[value="edit"]:checked ~ span.edit { /* 「修正」チェックで */
display: inline;} /* 修正用inputを表示する */
span.confirm {
opacity: 1.0; visibility: hidden;} /* 削除確認は透明不可視にしておくが、*/
input[value="rm"]:checked ~ span.confirm {/* 「削除」チェックで */
visibility: visible; /* 可視化したうえで */
opacity: 1.0; transition: 3s; } /* 削除確認をじわじわ表示する */
input[value="rm"]:checked ~ span.value { /* 「削除」チェックで */
background: red;} /* 値表示部分の背景を赤に */
input[value="new"]:checked ~ span.new { /* 「新規」チェックで */
display: inline;} /* 新規入力inputを表示する */
span.heresub {display: none;} /* 入力窓近くの送信/リセットボタン非表示 */
input:not([value="keep"]):checked ~ span.heresub {
display: inline;} /* 「温存」以外がチェックされたら表示 */
span.mtime {font-size: 70%;}
span.value {white-space: pre;}