[ -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>'
}