#!/bin/sh
# Here's global variable table.  Do not use this names.
# $HGid$

[ -f s4-config.sh ] && . ./s4-config.sh

myname=`basename ${SCRIPT_NAME:-$0}`
mydir=`dirname ${SCRIPT_FILENAME:-$0}`
myargs="$@"
PATH=/usr/local/sqlite3/bin:/usr/local/vim7/bin:/usr/iekei/ImageMagick/bin:/usr/local/ImageMagick/bin:$PATH
tmpdir=${TMPDIR:-tmp}
dbdir=${DBDIR:-db}
tmpfiles=""
db=${DB:-$dbdir/cgi.sq3}
admin=${ADMIN:-hostmaster@example.org}
templ=${TEMPL:-templ}
layout=${LAYOUT:-$templ/default}
formdir=${FORMDIR:-$templ/form}
imgdir=${IMGDIR:-img}
url=${URL:-"${REQUEST_SCHEME:-http${HTTPS:+s}}://$HTTP_HOST$REQUEST_URI"}
urlbase=${url%%\?*}
msgdir=$templ/msg
timeout="+2 days"
memoplimitdays="7"
dumpcollen=22
#thumbxy=120x120
thumbxy=96x96
iconxy_S=80x80
iconxy_M=400x400
maximagexy=1600x1600
### maximagexy=400x400
file_accept='accept="image/*,text/*,audio/*,application/vnd.oasis.*,application/pdf,application/x-*"'
blogreadflagrowid=0
querylog=$tmpdir/query.log

tconfs=""
imgcached=cache/img.`date +%Y/%m`
conftbl=_tblconf
nl="
"
iconcachekey="profimgcache_S"
case "$HTTP_USER_AGENT" in
  *i[Pp]hone*|*[Aa]ndroid*)	touchpanel=1 ;;
  *)				touchpanel="" ;;
esac
. ./s4-cgi.sh

: <<EOF

    !! 検索等でblogテーブル参照時は sql4readableblogs() で定義される
    !! readableblogs テーブルを使うこと
    資料配布、グループ管理・ML、ファイル交換、クリッカー、アンケート
    レポート提出管理
ひとつのarticleをheadingにして新規ツリーを作成、あるといいかも。

[2016]
7/12	根本への反省
	* cgi自身の $1, $2 での切り替えでなく、CGI変数での受け渡しにすべき。
	  arg1/arg2/arg3 的に $1 に / 区切りでつけた方がよかったかな。

[以下2015]
8/4	○グループに承認加入モードを追加
	○グループに参加していない場合は grpaction できない
	Web
	締切設定

8/2	○s4.cgi生成系 → index.cgi生成
	○自分の提出物リスト

7/19	○設置
	○一斉送信
	○getparfilename の tmpd の扱い
	○やっぱりs4にしようかな
7/18	○書込著者からホームへのリンク
7/17	○個人blogに「レポート提出用」がついたときの挙動
	○添付ファイル回収
	○imgcacheは別ディレクトリにしないと + .htaccess
7/15	○レポート提出モードの表示を付ける
	管理者権限での削除? → まだいいか

7/13	○前回アクセス基準の新着数は欲しいなあ
	○レポート提出はどうしよう
	 → ○blogにモードを追加:
	       ○レポート提出モード
		添付ファイル (誰が見たかログ)
	クリッカーは別立てメニューにしないと(管理者がON/OFF)
	○添付ファイルの読み出し権(6/22から) ← モードで対処


7/9	○管理者の追加
	△グループメンバの操作 → 要不要を吟味
	○グループ情報編集の行先はそのグループがいい?
	○新規グループの作成はどこから入るか
	△グループホームとユーザホームを揃える

7/8	○グループ一覧をユーザ一覧と揃える。

7/6の次 ○グループのconf編集の入口
	○グループ検索

6/22の次 ○ホーム画面、○招待状、親記事追跡、○編集ボタン、削除ボタン、


6/7の次	○blogを作ってみる || userconfig || _mのまとめ編集(削除)
6/7の次の次 ○userconfigの画面だけ作ってみる。

○ 5/28の次 edittableに「削除」ボタンを足す
○6/1 par2tableを triplex 対応に
 select "yuuji@gentei.org",var,"text",NULL,val from par where var in (select col from _tblconf where tbl="/user" and keytype in ('p', 's'));
→とすると 一気に

##    form.def を考えなおそう:
##	userのように必須カラムを決まった位置に付ける?
##	必須カラム、owner(foreign key passwd(name)), update datetime
##    ユーザ管理とグループ管理はデフォルトで持たせてしまえ

##    縦持ちデータの入力/編集を供給する関数 single + multi
##    持てるテーブル構造はシステム標準5種 + ユーザ定義2種類
##	1. passwd
##	2. grp
##	3. grp_mem
##	4. topic	記事のIDとなる
##	5. topic_cont	特定IDの記事の内容物
##	6. list		繰り返し登場あり
##	7. hash		繰り返し登場なし

## ● listの定義:
##	create table list(id unique, parentID, type, value);
## ● hashの定義:
##	create table hash(parentID, type, value, primary key(parentID, type));

##    グループ属性: community, friend
## ○ blob使えるのかな。streamで行けるのか? xxdで行けた。ありがたい。
##    form-defとtableは1対1対応でいいか
##    csv2sq3 で .csv.sq3 の Makefile

##    書き込みオブジェクトとは何か?
##    topic : id, belongto, title, owner, mode
##			type := root | comment
##    topic_cont : id, topicid(F), ppath, contenttype, filename, content,
##				unique(id, filename)
##			type := body(single) | attachment(multi)

##    group := name(P), tag, gecos, owner(F), mode
##	tag := personal | friend | ... any string
##    group_member := gname(F), type, name(F), UNIQUE(gname, type, name)
##	type := "u" | "g"
##  できたー!
## with recursive allmem as (select * from grp_mem where gname='bar' union all select grp_mem.* from grp_mem,allmem where allmem.name=grp_mem.gname) select * from allmem where type='u';
	↓
	↓以下に変更
 with recursive allmem as
 (select gname,val from grp_m where gname='foo'
 union all select grp_m.gname,grp_m.val from
	 grp_m,allmem where allmem.val=grp_m.gname)
 select val from allmem where val in (select name from user);


  with recursive allmem as
 (select gname,val from grp_m where gname='foo'
 union all select grp_m.gname,grp_m.val from grp_m,allmem
 where allmem.val=grp_m.gname)
 select a.*, coalesce(b.val,a.val) from allmem a left join grp_mem_s b
 on a.gname=b.gname and a.val=b.user and b.key='email'
 where a.val in (select name from user);


##    triggerもできた。
##    5/22から:グループ作成画面
##    埋め込み画像 data:CONTENT-TYPE;base64,.....

##    考え得るノードタイプ
##	日報		- 個人所属かグループ所属か
##	課題提出	- 個人所属かグループ所属か
##	グループ管理
##	個人情報管理
##	

##    例: group:sip - topic:1:sip:Aperture:yuuji:rw
##		    - topic:2:sip:ISO:yuuji:rw
##	  topic_cont	1:1:/:body:text...Aperture
##			2:1:/1:body:text..Aperture
##			3:1:/1:attachment:binary..Aperture
##			4:1:/2:body:text..Aperture
##			5:1:/2:attachment:binary..Aperture
##			6:2:/:body:text..ISO
##			7:2:/6:body:text..ISO
##			8:2:/6:attachment:binary..

##    ログテーブル
##    time, who, action, tbl, id	idなんか取れるかな



■表設計
* 3つの表に分散管理
	id格納表 + hash表 + list表
	*          *_s    *_m



user, user_map, user_col

■抽象エントリタイプ
 * user
	idとして機能 → table中の owner に自動挿入(?)
 * group
	権限判定に利用
 * serial
	自動idとして機能
 * password
	入力	type=passwordで入力
	変更	oldpasswd, password×2 で確認後修正
 * session
	password認証後のセッションキーとして機能
 * text
	入力	type=text
 * textarea
	入力	textarea
 * image|document
	入力	type=fileで入力し、mime-typeを確認
 * owner
	入力時の $user で、外部キー制約が付く
 * gowner
	グループとしての所有者で、外部キー制約が付く
 * timestamp
	datetime()
 * parent
	木構造の場合の親の位置
 * path
	木構造の場合の自分の位置

格納タイプ
 * list
	表	parentID, key, val でUNIQUE(parentID, key, val)
 * hash
	表	parentID, key, val でUNIQUE(parentID, key)

オブジェクトタイプ
 * entry
	id, title, owner
 * textpart
	id, parentID, text
 * binarypart
	id, parentID, contenttype, filename, content
 * content
	hash(textpart), list(binarypart)
 * topic
	id, hash(content), list(reply)
 * reply
	id, parentID, content
 * blog
	list(entry)
blog = [topic, list(reply)]


blog = [ {"title" => "hoge", "owner" => "yuuji", "date" => "2015-04-27",
          "text"  => "hogehoge ..",
          "reply" => [ {"serial" => 1,
			"author" => "taro",
			"date"   => "2015-04-28",
			"parent" => "/",
			"path"   => "/1",
			"text"   => "blah, blah, ....",
			"image"  => ["a.jpg", "b.jpg"] },
		       {"serial" => 2,
			"author" => "hanako",
			"date"   => "2015-04-29",
			"parent" => "/",
			"path"   => "/2",
			"text"   => "blah, blah, ....",
			"image"  => [] }]},
	 {"title" => "buha", ...} ]


user:=
 ユーザ名(英数字):name:p:text:length="20" maxlength="40"
 パスワード:pswd:s:password:length="20" maxlength="40"
 説明(日本語OK):gecos:s:text:length="20" maxlength="40"
 セッションキー:skey:s:session
 メイルアドレス:email:m:text:length="20" maxlength="40"
 住所:address:m:textarea:maxlength="400"
 プロフィール画像:profimg:m:image:maxlength="400K"
 履歴書:profpdf:m:document:maxlength="4M"

変換表
/user/email=m

blog:=
 シリアル:id:p:serial
 タイトル:title:s:text:
 所有者:owner:s:owner:
 時刻:ctime:s:stamp:
 リード文:heading:s:textarea:
 リプライ:reply:m:*article:

article:=
 シリアル:id:p:serial
 筆者:author:s:owner
 時刻:ctime:s:stamp:
 参照元:parent:s:parent: 
 パス:path:s:path:
 本文:text:s:textarea:
 画像:image:m:image:

履歴書:profpdf:m:document:maxlength="4M"


EOF

sq() {
  # ./args.rb -cmd ".timeout 3000" "$@"
  sqlite3 -cmd 'PRAGMA foreign_keys=ON' -cmd ".timeout 3000" "$@"
}
dbsetup() {
  [ -d $tmpdir ] || mkdir -m 1777 $tmpdir
  [ -d $dbdir ] || mkdir -m 1775 $dbdir
  sqi=$tmpdir/sqi.$$
  sqo=$tmpdir/sqo.$$
  mkfifo $sqi $sqo
  #tail -f $sqi | sq $db &	# "tail -f" is too heavy. DO NOT USE!!
  sq  $db < $sqi &
  sq3pid="`jobs -p` $!"
  exec 2>> $tmpdir/error.out
  exec 3>> $tmpdir/debug.out
  exec 5> $sqi	# Turning $sqi access through fd5 for continuous open state
  rm $sqi
}
cleanup() {
  trap '' INT HUP EXIT TERM PIPE
  echo .quit >&5
  kill $sq3pid
  kill $sq3pid
  rm -f $sqo $sqi
  rm -rf $tmpfiles
}
# We want to use piped function to put querylog, but we use
# simple redirection for the sake of speed.
query() {
  echo ".once $sqo" >&5
  echo "`date '+%F %T'`:[${user:-NULL}] <<<" >> $querylog
  if [ -z "$1" ]; then
    tee -a $querylog
  else
    echo "$@" >> $querylog
    echo "$@"
  fi  >&5
  cat $sqo
  echo '>>>' >> $querylog
}
_m4() {
  #_S4NAME_=f,f,f
  m4 ${_S4NAME_:+"-D_S4NAME_=${_S4NAME_}"} "$@"
}
ismember() {
  # $1=user, $2=group
err ismem: "select user from grp_mem where gname=$(sqlquote $2) and user='$1';"
  test -n "`query \"select user from grp_mem where gname=$(sqlquote $2) and user='$1';\"`"
}
isuser() {			# Check if $1 is a valid user
  test -n "`query \"select name from user where name='$1';\"`"
}
isgroup() {			# Check if $1 is a valid group
  err isgroup: "select gname from grp where gname=$(sqlquote $1);"
  test -n "`query \"select gname from grp where gname=$(sqlquote $1);\"`"
}
isgrpowner() (
  # $1=user, $2=group
  gn=`sqlquote "$2"`
  sql="select user from grp_adm where gname=$gn and user='$1';"
  err isgrpowner: $sql
  test -n "`query $sql`"
)
getgroupadminmails() {
  # $1=group
  for i in $(getgroupadmins $1); do
    email4group "$1" "$i" ;
  done
}
getgroupadmins() {	# $1=group
  # This function is called in a backquote, so needn't to be subshellized
  qgrp=`sqlquote "$1"`
  query "select user from grp_adm where gname=$qgrp;"
}
getgroupattr() {	# $1=group $2=attr
  # This function is called in a backquote, so needn't to be subshellized
  getvalbyid grp $2 \
	     $(query "select rowid from grp where gname=`sqlquote $1`;")
}
getgroupbyid() {
  # $1=id|gname
  sql="select coalesce((select gname from grp where gname=$(sqlquote $1)),
	(select gname from grp where rowid=$(sqlquote $1)));"
  # err ggbyid: `echo $sql`
  query $sql
}
isfilereadable() { # $1=user $2=tbl $3=rowid
  # Return true if user($1) can read attachment files in tbl($2):rowid($3)
  [ -z "$1" -o -z "$2" -o -z "$3" ] && return 1 # invalid argument

  # Return true when anonymous mode
  [ "$anonymousmode" ] && return 0
  # case `getvalbyid blog mode $2` in
  #   normal|*open*|"")	return 0 ;;
  #   *closed*)
  #     owner=`getvalbyid blog owner $2`
  #      if isgrp $owner; then
  # 	 isgrpowner $1 $owner && return 0 || return 1
  #      elif isuser $owner; then
  # 	 [ x"$1" = x"$owner" ] && return 0 || return 1
  #      fi
  # esac
  # ↑ 要はこういう処理を↓で一気にやっている
  sql="with getblog as (\
	select key,val from blog_s where id=(\
	select blogid from article where id in\
        	(select id from $2 where rowid=$3))),\
      getowner as (select val from getblog where key='owner'),\
      getmode  as (select val from getblog where key='mode')\
    select case\
	when (select author from article where\
		id=(select id from $2 where rowid=$3))='$1' \
	then 'author'\
	when (select val from getmode) in ('report-open', 'normal')\
	then 'open'\
	when (select val from getmode) is null \
	then 'open'
	when (select val from getowner) in (select gname from grp)\
	then (select user from grp_adm where \
		gname=(select val from getowner) and \
		user='$1')\
	when (select author from article where\
		id=(select id from $2 where rowid=$3))='$1'
	then 'user+author'
	else '' end;"
  ## err isfilereadable: sql="`echo $sql`"
  # caseのネストで内側のcaseがスカラーtrueを返しても外側はtrue扱いにならない
  result=`query "$sql"`
  [ -n "$result" ] && return 0
  return 2
}
linkhome() {
  # $1=UserOrGroup
  echo -n '<a href="?'
  if isuser $1; then
    err "select 'home+'||rowid from user where name='$1';"
    query "select 'home+'||rowid from user where name='$1';"
  else
    echo -n "grp+$1"
  fi
  echo  "\">`gecos $1`</a>"
}
hreflink() {
  # s4 specific notation:
  #   ^href=URL
  #   ^iframe=URL
  # OSM umap Wikistyle Notation:
  #   [[URL]]		- Simle Link
  #   [[URL|Word]]	- Link with anchor word
  #   {{URL}}		- <img src="URL">
  #   {{URL|width}}	- <img src="URL" width="width">
  #   {{{URL}}	}	- <iframe src="URL"></iframe>
  #   {{{URL|height}}	- <iframe src="URL" height="height"></iframe>
  _hrefptn="[-A-Za-z0-9,.:;/~_%#&+?=@!]*"
  sed -e "s|\[\[\($_hrefptn\)\|\(.*\)\]\]|<a href=\"\1\">\2</a>|g" \
      -e "s|\[\[\($_hrefptn\)\]\]|<a href=\"\1\">\1</a>|" \
      -e "s|{{{\($_hrefptn\)\|\(.*\)}}}|<iframe src=\"\1\" height=\"\2\"></iframe>|g" \
      -e "s|{{{\($_hrefptn\)}}}|<iframe src=\"\1\"></iframe>|g" \
      -e "s|{{\($_hrefptn\)\|\(.*\)}}|<img src=\"\1\" width=\"\2\">|g" \
      -e "s|{{\($_hrefptn\)}}|<img src=\"\1\">|g"\
      -e "s|^href=\($_hrefptn\)|<a &>\1</a>|" \
      -e "s|^iframe=\($_hrefptn\)|<iframe src=\"\1\"></iframe>|"
}
minitbl() {
  sed -n '
	/^|.*|/ {;	# If the line begin with "|" and has 2 or more "|"
	 s,|$,,;				# Remove trailing "|" first
	 s,|\* *\([^|]*\) *,<th>\1</th>,g;	# "|*..." to "<th>...</th>"
	 s,| *\([^|]*\) *,<td>\1</td>,g;	# "|..."  to "<td>...</td>"
	 s,^,<tr>,; s,$,</tr>,;		# Enclose with "<tr>" and "</tr>"
	 H;				# Concat this line to HoldSpace
	 s/.*//;			# Delete PatternSpace for finalization
	 $ b cont
	 d;	# If in final line, output the rest, else jump to next turn
	}
	:cont
	x;				# For non-"|" lines, check HoldSpace
	/^./ {;				# If HoldSpace has "|" table elements
	  s|^|<table class="mini">|;	# Enclose whole elements like this:
	  # s|$|</table>|;		# <table class="mini">..\n..</table>
	  p;				# Print whole "table" element
	  s/.*//;			# Erase all when done.
	  x; s|^|</table>|; x;		# Preppend /table to the next line
	}
	x;				# Back to the newest line
	p;				# Print rest'
}
acclog() (
  # $1=table, $2=rowid
  n=${2%%[!0-9]*}	# Remove non-digit chars from $2(should be rowid)
  if [ -n "$n" ]; then
    now=`date +"%F %T"`
    #query "replace into acclog values('$user', '$1', '$n', '$now');"
    #query "replace into acclog values('$user', '$1', $n, '$now');"
    query "replace into tblaccesses values('$user', '$1', $n, '$now');"
  fi
)
gecos() (
  u=`sqlquote ${1:-$user}`
  query "select gecos from gecoses where name=$u;"
)
setpar() {
  query "replace into par values('$session', '$1', '$2', \"$3\");"
}
replpar() {
  query "update par set val=\"$3\" where sessid='$session' and var='$1' and type='$2';"
}
getpar() {
  val=`query "select val from par where var='$1' and sessid='$session' $2;"`
## err getpar/val1: "val=[$val]"
  if [ -z "$val" ]; then
    val=`query "select val from cookie where var='$1' and sessid='$session' $2;"`
  fi
## err getpar/val2: "val=[$val]"
  case "$var" in
    owner)
      if [ x"$user" = x"$val" ]; then
	echo $user; return
      elif ismember $user $val; then
	echo $val; return
      fi ;;
  esac
## err getpar/ret: "val=[$val]"
  echo "$val"
}

getpartype() {
  query "select type from par where var='$1' and sessid='$session' $2;"
}
getparcount() {
  query "select count(*) from par where var='$1' and sessid='$session' $2;"
}
getparfilename() {
  # null if type of $1 is not file
  (f=`query "select val from par where var='$1' and sessid='$session' and type='file' $2;"`
   [ -n "$f" ] && echo $f)
}
sqlquote() {
  (v="$1"
   case "$v" in
     "") return ;;		# null
     "X'"*)			# quoted hex string
       echo $1 ;;
     *\"*)			# string including dbl-quote"
       v=`echo "$v"|sed -e 's/\"/\"\"/g'`
       echo "\"$v\""
       return ;;
     *.*.*|*-*-*|*[Ee]*[Ee]*|[Ee]*|*[\ -,:-df-~]*) # string
       echo "\"$v\""
       return ;;
     *)
       if expr "$v" : '[-0-9.Ee][-0-9.Ee]*$' >/dev/null 2>&1; then
	 echo $v		# MAYBE numeric, maybe...
       else
	 echo "\"$v\""
       fi ;;
   esac)
}
sqlquotestr() (
  case "$1" in
    *\'*)	v=`echo "$1"| sed "s/'/''/g"`
		echo "'$v'" ;;
    *)		echo "'$1'" ;;
  esac
)
mktempd() {
  TMPDIR=$tmpd mktemp -d -t $session
}
getcachedir() {  # $1=maintable
  if [ -n "$imgcached" ]; then
    echo $imgcached/$(echo ${1:-hoge}|md5)/$thumbxy
  else
    echo $tmpd/$thumbxy
  fi
}
getval() {
  # $1=table $2=col $3(optional)=condition
  case `gettbl_coltype "/$1/$2"` in
    user|author)		# author added 2015-06-18 for article(author)
      echo "$user" ;;
    stamp|datetime)
      date "+%F %T" ;;
    serial)
      (s=`getpar $2`
       if [ -n "$s" ]; then echo $s; else echo "`date +%s`x$$"; fi) ;;
    *)
      getpar "$2" "$3";;
  esac
}

getvalquote() {
  # $1=table $2=col $3(optional)=condition
  (v=`getval "$@"`
   case "$v" in
     "")	echo NULL ;;
     *)  	sqlquote "$v" ;;
   esac)
}
getparquote() {
  sqlquote `getpar $1`
}
getbinbyid() {
  # $1=tbl $2=col $3=rowid $4=tmpdirForBinary
  
}
getvalbyid() {
  # $1=tbl $2=col $3=rowid $4=tmpdirForBinary
  # If two or more values found, save them to $tmpd/${column}.$N and
  # store the number of files into $tmpd/${column}.count and
  # their each rowid stored into $tmpd/${column}.$N.rowid.
  ##  err gtb-$1=`gettblcols $1`, tbl=$1, col=$2, '$3'=$3

  (for c in `gettblcols $1`; do
     if [ x"$2" = x"$c" ]; then
       ###sq $db "select $2 from $1 where rowid=$3"
       query "select $2 from $1 where rowid=$3;"
       return
     fi
   done
   rowid=$3
   pk=`gettblpkey $1`
   key=`query "select $pk from $1 where rowid=$3;"`
   getkey="(select $pk from $1 where rowid=$3)"
   td=${4:-$tmpd}
   [ -d $td ] || mkdir -p $td
   ### err "select $pk from $1 where rowid=$3" - key=$key '$4(tmp)'=$4
   for kt in s m; do
     t=${1}_$kt
     for c in `gettbl_${kt}_cols $1`; do
       vcount=1			# count(val)
       if [ x"$2" = x"$c" ]; then
	 #### cond="$t where $pk=\"$key\" and key=\"$c\""  #2015-07-22
	 cond="$t where $pk=$getkey and key=\"$c\""
	 val=`query "select val from $cond limit 1;"`
	 type=`query "select type from $cond limit 1;"`
	 if [ $kt = m ]; then
	   ###vcount=`sq $db "select count(val) from $cond"`
	   # Reset val to store filenames if type is string
	   val=`query "select val from $cond and type like 'file:%' order by rowid;"`
err gvb1-sql: "select count(val) from $cond;"
	   vcount=`query "select count(val) from $cond;"`
	   echo $vcount > $td/$c.count
	   i=0
## err gvbid: i=$i vcount=$vcount
	   while [ $i -lt $vcount ]; do
	     slice="order by rowid limit 1 offset $i"
	     i=$((i+1))
	     fn=$c.$i
err td=$td, fn=$fn, type=$type, val="[$val]"
	     case $type in
	       file:*)
		 #file=$td/$val
		 r_f=`query "select rowid||'//'||val from $cond $slice;"`
		 f_rid=${r_f%%//*}
		 file=$td/${r_f##*//}
		 # FOR SPEED: Skip file generation if imgcache exists
		 [ -s "$file" -a -s "$td/$fn.rowid" -a -s "$file.rowid" ] \
		     && [ x"$f_rid" = x"`cat $td/$fn.rowid`" ] \
		     && continue
		 # err gvbid-get="select quote(bin) from $cond $slice;"
## err output: "fn=[$fn] file=[$file]"
		 sq $db<<EOF | unhexize > "$file"
.output '$td/$fn.rowid'
select rowid from $cond $slice;
.output '$td/$fn'
select val from $cond $slice;
.output '$td/${fn}.content-type'
select substr(type, 6) from $cond $slice;
.output stdout
select quote(bin) from $cond $slice;
EOF
		 ## err gvbid-get2: "`ls -lF $file`"
		 ## err i=$i - file=$file rowid=`cat $td/$fn.rowid`
		 cp "$td/$fn.rowid" "$file.rowid" 2>&3 # for convenience
		 cp "$file" "$file.orig" 2>&3
		 ls -lh "$file" |
		     awk '{print $5"B"}'|sed 's/BB/B/' > "$file.size"
		 case "$type" in
		   *:[Ii]mage*) mogrify -geometry $thumbxy "$file" ;;
		   ### ここのアイコンを増やしたい
		   *|*:[Aa]pplication*)
		     convert -geometry $thumbxy $imgdir/file-icon.png \
			     png:- > "$file"
		     ;;
		 esac
		 ;;
	       *)
		 sq $db<<EOF
.output $td/$fn.rowid
select rowid from $cond $slice;
.output $td/$fn
select val from $cond $slice; 
EOF
		 val=$val${val:+$nl}"`echo $fn`" # should be delimited by newline
		 ;;
	     esac
	   done
	 else
	   rm -f $td/$c.count
	   case $type in
	     file:*)
	       echo "$val" \
		   | while read fn; do
		       file=$td/$fn
		       if [ ! -s "$file" ]; then
			## sq $db "select quote(bin) from $cond and val=\"$fn\"" \
			 query "select quote(bin) from $cond and val=\"$fn\";" \
			     | unhexize > "$file"
			 ##@@## -- echo ${type#file:} > "$file.content-type"
			 case $type in
			   *:[Ii]mage*) mogrify -geometry $thumbxy "$file" ;;
			   *:[Aa]pplication*)
			     convert -geometry $thumbxy $imgdir/file-icon.png \
				     png:- > $file ;;
			 esac
		       fi
		     done
	       ;;
	   esac
	 fi
	 echo "$val"		# Keep newlines by ""
	 return
       fi
     done
   done)
}
getvalbypkey() (
  # $1=tbl $2=col $3=pkey $4=tmpdirForBinary
  pk=`gettblpkey $1`
  rowid=`query "select rowid from $1 where $pk='$3';"`
  getvalbyid "$1" "$2" $rowid $4
)
getvalbycond() {
  # $1=tbl $2=col $3=SQL-Condition
  ###rowid=`sq $db "select rowid from $1 where $3"`
  rowid=`query "select rowid from $1 where $3;"`
  if [ -n "$rowid" ]; then
    getvalbyid "$1" "$2" $rowid "$4"
  fi
}
getpwfield() {
  # getpwfield user column
#  val=`sqlite3 $db "select $2 from passwd where name='$1' $3"`
  val=`getvalbycond user $2 "name='$1'"`
  if [ -n "$val" ]; then
    echo "$val"
    return 0
  else
    return 1
  fi
}
encode() {
  if [ -z "$sha1" ]; then
    if type sha1 >/dev/null 2>&1; then
      sha1=sha1
    elif type sha1sum >/dev/null 2>&1; then
      sha1=sha1sum
    elif type gsha1sum >/dev/null 2>&1; then
      sha1=gsha1sum
    fi
  fi
  $sha1 "$@" | cut -d' ' -f1
}
enjpeg() {
  if [ -z "$cjpeg" ]; then
    if type cjpeg >/dev/null 2>&1; then
      cjpeg="cjpeg"
    else
      cjpeg="convert - jpeg:-"
    fi
  fi
  $cjpeg "$@"
}
mycrypt() (
  key=$1 salt=$2
err \$2=$2
  case $2 in
    '$'*'$'*)	salt=${salt#\$4\$}
		salt=${salt%\$*} ;;
  esac
  echo -n '$4$'"$salt"'$'
  echo "$salt$key" | encode || exit 1 # Abort if fail to call encode
)
hexize() {
  if [ -z "$hexize" ]; then
    if type xxd >/dev/null 2>&1; then
      hexize="xxd -p"
    else
      hexize_hd() {
	hexdump -ve '1/1 "%.2x"'
      }
      hexize="hexize_hd"
    fi
  fi
  cat "$@" | $hexize | tr -d '\n'
}
unhexize() {
  if [ -z "$unhex" ]; then
    if type xxd >/dev/null 2>&1; then
      unhex="xxd -p -r"
    elif type perl >/dev/null 2>&1; then
      cat >$tmpd/unhex.pl<<EOF
s/([0-9a-f]{2})/print chr hex \$1/gie
EOF
      # Perl refuses -e in setuid circumstances, which can be absurdly
      # avoided by creating scripts in a file where its parent directory is
      # world writable...:)
      unhex="perl -n $tmpd/unhex.pl"
    fi
  fi
  cat "$@" | $unhex
#  cat $1 | tee /tmp/uh.in| $unhex | tee /tmp/uh.out
}
percenthex() {
  hexize "$@" | sed 's/\(..\)/%\1/g'
}
htmlescape() {
  sed -e 's/\&/\&amp;/g' -e 's/"/\&quot;/g' -e "s/'/\&apos;/g" \
      -e "s/</\&lt;/g; s/>/\&gt;/g"
}
enascii() {
  if [ -z "$enascii" ]; then
    if type kakasi >/dev/null 2>&1; then
      enascii="kakasi -Ha -Ka -Ja -Ea -ka"
    else
      enascii_now=`date +%FT%T`
      enascii_sed() {
	nkf -Z0Z1Z2 \
	    | sed -e "s/^/$enascii_now/" -e "s|[^-0-9.A-z/,()_=]|x|g"
      }
      enascii="enascii_sed"
    fi
  fi
  cat "$@" | $enascii
}
size_h() {
  i="$1" oi=$1
  set -- B B KB MB GB TB
  while [ $((i)) -gt 9 -a -n "$1" ]; do	# -gt 9 means $oi > 1024
    oi=$i
    i=$((i/1024))
    shift
  done
  echo ${oi}$1
}
gettblconf() {
  if [ -z "$tconfs" ]; then
##    tconfs=`sq $db \
    tconfs=`query \
	"select tbl||'/'||col||'='||keytype||'/'||objtype from $conftbl;"`
  fi
  # /tb1/col1=p/text /tb1/col2=s/text /tb1/col3=m/image /tb2/col1=p/text ...
}
gettblkeys() {
  # $1=tbl
  gettblconf
  echo "$tconfs" | fgrep "/$1/" | \
      (type="" keys="" fks="" cols="" scols="" mcols="" hcols=""
       while IFS='=' read tc conf; do # tc=/tb1/col1 conf=s/text
	 col=${tc##*/} type=${conf%%/*}
	 case $type in
	   *p*)
	     cols=$cols"${cols:+:}$col"
	     keys=$keys"${keys:+:}$col" ;;
	   *f*)	cols=$cols"${cols:+:}$col"
		fks=$fks"${fks:+:}$col" ;;
	   *m*)	mcols=$mcols"${mcols:+:}$col" ;;
	   *s*)	scols=$scols"${scols:+:}$col" ;;
	 esac
	 case $type in
	   *h*)	hcols=$hcols"${hcols:+:}$col" ;;
	 esac
       done
       echo "_keys=$keys _fks=$fks _cols=$cols _scols=$scols _mcols=$mcols _hcols=$hcols")
}
gettblpkey() {
  # $1=tbl
  gettblkeys $1 | cut -d ' ' -f 1 | sed -e 's/.*=//' -e 's/:/ /g'
}
gettblfkey() {
  (x=`gettblkeys $1`
   x=${x#*_fks=}		# cut before "_fks=" including
   echo ${x%% *} | tr ':' ' ')
}
gettblcols() {
  (x=`gettblkeys $1`
   x=${x#*_cols=}		# cut before "_cols=" including
   echo ${x%% *} | tr ':' ' ')
}
gettbl_s_cols() {
  (x=`gettblkeys $1`
   x=${x#*_scols=}		# cut before "_scols=" including
   echo ${x%% *} | tr ':' ' ')
}
gettbl_m_cols() {
  (x=`gettblkeys $1`
   x=${x#*_mcols=}		# cut before "_mcols=" including
   echo ${x%% *} | tr ':' ' ')
}
gettbl_h_cols() {
  (x=`gettblkeys $1`
   x=${x#*_hcols=}		# cut before "_hcols=" including
   echo ${x%% *} | tr ':' ' ')
}
gettbl_coltype() (
  gettblconf
  x=`echo "$tconfs"|fgrep $1=`
  x=${x#*=}			# cut before =
  echo ${x#*/}			# cut before p/ including
)
is_hidden() {
  # $1=Tbl $2=col
  gettblconf
  x=`echo "$tconfs"|fgrep /$1/$2=`
  x=${x#*=}			# cut before =
  x=${x%%/*}			# cut after /
  case $x in
    *h*)	return 0 ;;
    *)		return 1 ;;
  esac
}

dbsetbyid() {
  # $1=tbl $2=id $3=col $4=val/filename - &optional - $5=content-type
  (t0=$1 t=$1 p=$2 c=$3
   tsc=$t/$c val=$4
   quotedp=$(sqlquotestr "$p")
   unset primary update
   gettblconf
#err tsc=$tsc, tconfs="$tconfs"
   conf=`echo "$tconfs"|fgrep "$tsc"=`
#err conf=$conf
   case ${conf#*=} in
     p*)	primary=1 ;;
     f*)	update=1  ;;
     u*)	;;
     m*)	t=${t}_m;;
     s*)	t=${t}_s;;
   esac
#err t=$t
   type=string fn=""
   case $conf in
     */password)
       type=encoded ### val=`echo $val|encode`
       ;;
     */image*|*/document*)
       type=`file --mime-type - < "$val" | cut -d' ' -f2`
       bin="X'`hexize "$val"`'"
       ;;
   esac
   pkey=`echo "$tconfs"|grep "${t0}/.*=p"|sed 1q`
   pkey=${pkey#/*/}		# cut $tbl/
   pkey=${pkey%=p/*}		# cut =p/... -> primary key
   if [ "$primary" ]; then
     nulls=`echo "$tconfs"|grep "$t/.*=[fu]/"|sed 's/^.*/, NULL/'|tr -d '\n'`
     ###sq $db "replace into $t values(\"$val\"$nulls)"
     query "replace into $t values(\"$val\"$nulls);"
   elif [ "$update" ]; then
     query "update $1 set $c=\"$val\" where $pkey=$quotedp;"
   else
     query "replace into $t values($quotedp, \"$c\", \"$type\", \"$val\", \"$bin\");"
   fi
  )
}
expire() (
  at="${1:-$timeout}"
  FMT="${2:-%F %T}"
  TZ=GMT gdate -d "$at" +"$FMT"
)
addsession() {
  # expireをセット
  # loginの先にどの画面に行くかの状態遷移表書式を決める
  expire=`expire ${2:-"+1min"}`
  query "replace into session values('$1', '$expire');"
  # Remove old session parameters
  now=`expire now`
  query "delete from session where expire < '$now';"
}
gencookie() (
  for kv; do
    expire="`expire '' '%a, %d-%b-%Y %H:%M:%S GMT'`"
    echo "Set-Cookie: $kv; expires=$expire"
  done
)
contenttype() {
  echo "Content-type: ${1:-text/html; charset=utf-8}"
  contenttype() {}	# Only need to work once
}
putheader() {
  
}
putfooter() {
  _m4 -D_TITLE_="${TITLE:-$myname}" $layout/footer.m4.html
}
getcookie() (
  for kv in `echo $HTTP_COOKIE|sed 's/[;, ]/ /g'`; do
    k="${kv%%=*}"
    v="`echo ${kv#*=}|nkf -Ww -mQ|sed -e 's/\"/\"\"/g'`"
    query "replace into cookie values('$session', '$k', 'string', \"$v\");"
  done
)
genrandom() {
  # $1=columns (default: 10)
  dd if=/dev/urandom count=1 2>/dev/null|nkf -MB|fold -w${1:-10}|sed -n 10p
}
genserial() {
  echo $((($(date +%s)-1433084400)/10))c$$
}
smail() {
  # smail rcpts subj (file)
  # $SMAIL_TO  <- Recipient value of To: header
  # $MAIL_FROM <- From: header value
  from=`echo "${MAIL_FROM:-$admin}"|nkf -jM|tr -d '\n'`
  rcpt=`echo $1`		# strip newlines
  subj=`echo $2|nkf -jM|tr -d '\n'`
  (_m4 -D_RCPT_="${SMAIL_TO:-$rcpt}" -D_SUBJ_="\`$subj'" -D_FROM_="$from" $msgdir/mail-header.m4
   cat $3 | nkf -jd ) | sendmail -f $admin $rcpt
}
setviastring() {
  table=$1
  oifs="$IFS"
  IFS="&" 
  for us in $2; do
    k=${us%%=*}
    v="`echo ${us#*=}|tr '%+' '= '|nkf -Ww -mQ|sed -e 's/\"/\"\"/g'`"
    query "replace into $table values('$session', '$k', 'string', \"$v\");"
    #echo $k=$v
  done
  IFS="$oifs"
}
checkdomain() (
  # Check the validity of domain by referring DNS
  item=$1
err checkdomain $1
host ${item#*@} 1>&3 2>&3
  host ${item#*@} >/dev/null 2>&1
)
pwcheck() {
  # $1=passwd
  dbpswd=`getpwfield $user pswd`
  encpswd=`mycrypt "$1" "$dbpswd"`
  ## err user=$user, pswd=$1, db=$dbpswd, enc=$encpswd
  [ x"$dbpswd" = x"$encpswd" ]
}
mypwhash() {
  mycrypt `cat` `genrandom 5`
}
wasureta() {
  user=$1
  if ! checkdomain $user; then
    contenttype; echo
    _m4 -D_TITLE_='Invalid email' $layout/title-only.html
    echo "ユーザ名($user)には正しいメイルアドレスが必要です。" | html p
    putfooter
    exit 0
  fi
  newpswd=`genrandom` # newsalt=`genrandom 5`
  #encpswd=`mycrypt "$newpswd" "$newsalt"`
  encpswd=`echo $newpswd|mypwhash`
  dbsetbyid user $user pswd "$encpswd"
  # Avoid $user substitution with m4, because $url comes from user input.
  _m4 -D_PSWD_="$newpswd" -D_URL_="$url" -D_ADMIN_="$admin" \
     $msgdir/mail-newaccount.m4 \
      | sed "s/_USER_/$user/g" \
      | smail $user "New Account"
}
checkauth() {
  user=`getpar user`
  skc=`getpar skey`		# from cookie
  [ -z "$user" ] && return 3
  skey="`getpwfield $user skey`"
  if [ -n "$skey" ]; then
    if [ x"$skey" = x"$skc" ]; then
      return 0
    fi
  fi
  pswd=`getpar pswd`
  quser=`sqlquotestr "$user"`
  dbuser=`query "SELECT name FROM user WHERE name=$quser;"`
  if [ -z "$dbuser" ]; then
    return 1
  elif [ x"$pswd" = x"wasureta" ]; then
    wasureta $user
    return 1		# wasureta error
  fi
 # dbpswd="`sq $db \"select pswd from passwd where name='$user'\"`"
  # putheader; echo; echo user=$user, db=$dbpswd, enc=$encpswd
  if pwcheck "$pswd"; then
    newsession=`genrandom 50`
    dbsetbyid user $user skey "$newsession"
    gencookie "user=$user" "skey=$newsession"
    return 0
  fi
  return 2		# Password mismatch
}
showlogin() {
  args=`echo $myargs|tr ' ' '+'`
  _m4 -D_SYSNAME_="Welcome" -D_MYNAME_="$myname${args+?}$args" \
     $layout/login.m4.html
  exit 0
}
dologin() {
  checkauth
  st=$?
  if [ $st != 0 ]; then
    contenttype; echo
    _m4 -D_USER_="$user" -D_URL_="$url" -D_ADMIN_="$admin" \
       $msgdir/login-fail-$st.m4.html
    showlogin			# and EXIT
  fi
}

# Do instant jobs here
dbsetup
trap cleanup INT HUP EXIT TERM PIPE
# trap cleanup INT HUP

err() {
  echo "$@" 1>&3
}

cgiinit() {
  session=`date +%F-$$`
  tmpf=tmp/stream
  tmpd=`tmpd=$tmpdir mktempd`
  tmpfiles=$tmpfiles" $tmpd"
  addsession $session
  getcookie
  case "$REQUEST_METHOD" in
    get|GET)	s="$QUERY_STRING" ;;
    post|POST)	## dd count=$CONTENT_LENGTH bs=1 of=$tmpf 2>/dev/null #slow
      		## dd bs=$CONTENT_LENGTH count=1 of=$tmpf # NOT working
		# cat > $tmpf		# too much?
		head -c $CONTENT_LENGTH > $tmpf	# safe?
      		(echo CL=$CONTENT_LENGTH; ls -lF $tmpf) 1>&3
		s="`cat tmp/stream`"
		tmpfiles=$tmpfiles"${tmpfiles+ }$tmpf"
		;;
  esac
  case "$CONTENT_TYPE" in
    *boundary*)
      bndry=${CONTENT_TYPE#*boundary=}
      #for us in `LC_CTYPE=C ./mpsplit.rb "$bndry" $tmpd < $tmpf`
      for us in `LC_CTYPE=C ./mpsplit.pl "$bndry" $tmpd < $tmpf`
      do
	k=${us%%\=*}
	#echo u=$us
	#v="`echo ${us#*=}|nkf -Ww -mQ|sed -e 's/\"/\"\"/g'`"
	v="`echo ${us#*=}|unhexize|sed -e 's/\"/\"\"/g'`"
 # err k=$k v=$v
	case "$k" in
	  *:filename)
	    type='file'; k=${k%:filename}
	    # DO NOT ALLOW Space and '|' in file names
	    newv=`echo "$v"|sed 's/[ \|]/X/g'`
	    if [ x"$v" != x"$newv" ]; then
	      :
	    fi
# (echo k=$k v="[$v]"; ls -lF "$tmpd/$v"; file --mime-type "$tmpd/$v") 1>&3
	    case `file --mime-type - < "$tmpd/$v"|cut -d' ' -f2` in
	      [Ii]mage/x-xcf)
		bzip2 "$tmpd/$v"
		v=${v}.bz2
		;;
	      [Ii]mage/x-*|*/vnd.*)	;;
	      [Ii]mage/*)
		mogrify -resize $maximagexy'>' "$tmpd/$v"
		;;
	    esac
	    ;;
	  *)
	    type='string'
	    ;;
	esac
	#sq $db "replace into par values('$session', '$k', '$type', \"$v\")"
	setpar "$k" "$type" "$v"
      done
      ;;
    *)
      setviastring par "$s"
      ;;
  esac
}
email4group() {
  # Get for-$1=group email address(es) for $2...=users
  qgrp=`sqlquote "$1"`; shift
  users=`for i; do sqlquote "$i"; done`
  users=`echo $users|tr ' ' ','`
  sql="select coalesce(s.val, g.user) from grp_mem g
	left join grp_mem_s s on g.gname=s.gname and g.user=s.user
	and s.key='email'
	where g.gname=$qgrp and g.user in ($users);"
  query "$sql"
}
email4groupbyuid() {
  # Get for-$1=group email address(es) for $2...=user-ids
  qgrp=`sqlquote "$1"`; shift
  uids=`echo "$@"`
  uids=`echo $uids|tr ' ' ','`
  sql="WITH
	grpemails AS (
	  SELECT gname, user, val email
	  FROM grp_mem NATURAL JOIN grp_mem_s
	  WHERE key='email' AND gname=$qgrp),
	useremails AS (
	  SELECT user.rowid rid, user.name, val email
	  FROM user
	       LEFT JOIN user_s
	       ON user.name=user_s.name AND user_s.key='email')
	SELECT DISTINCT coalesce(g.email, u.name)
	FROM  useremails u LEFT JOIN grpemails g
	      ON u.name=g.user
	WHERE u.rid in ($uids);"
  ## err email4gByid `echo $sql`
  query "$sql"
}
collectemail() (
  # Collect email addresses for group $1
  # If $TEAM is set, filter by team name
  # If $EXCEPT is set as username(s) delimited by comma,
  # remove $EXCEPT from list: ....NOT IN ($EXCEPT)
  for e; do
    if isuser "$e"; then
      em=`query "select val from user_m where name='$e' and key='email';"`
      [ -n "$em" ] && echo "$em" || echo "$e"
    else
      qgrp=`sqlquote "$e"`
      if [ -z "$TEAM" ]; then
	gmem="grp_mem"
      else
	tm=`sqlquote "$TEAM"`
	gmem="(SELECT gname, user FROM grp_mem_m WHERE gname='$e' AND key='team' AND val=$tm)"
      fi
      ex=${EXCEPT:+"AND g.user NOT IN ($EXCEPT)"}
      sql="select coalesce(s.val,um.val,g.user) from
	 $gmem g left join grp_mem_s s
	 on g.gname=s.gname and g.user=s.user and s.key='email'
	 left join user_m um on g.user=um.name and um.key='email'
	 where g.gname=$qgrp $ex;"
      ## err CollectEmail: `echo "$sql"`
      query "$sql"
    fi
  done
)
sendinvitation() (
  # $1=email
  iss="invite-`date +%s`-$user"
  addsession $iss +${memoplimitdays}days # 1 week due date
  query "replace into par values('$iss', 'invite', 'string', \"$1\");"
  gecos=`gecos`
  name=$user"${gecos:+($gecos)}"
  regist="$urlbase?reg+$iss"
  _m4 -D_URL_="$urlbase" \
      -D_USER_="$name" \
      -D_EMAIL_="$1" \
      -D_REGIST_="$regist" \
      -D_ADMIN_="$admin" \
      $msgdir/mail-invite.m4 \
      | smail $1 "BBSへの御招待"
  return 0
)
emaildomaincheck() {
  case "$1" in
    *@*@*) echo "無効なアドレスです"; return 1 ;;
    *@*)
      local=${1%@*} domain=${1#*@}
      if ! host $domain >/dev/null 2>&1; then
	echo "ドメイン($domain)が見付かりません。"
	return 2
      fi
      return 0
      ;;
    *)	echo "正しいメイルアドレスをいれてください"; return 3 ;;
  esac
}
invite() {
  email=`getpar email`
  case $email in
    *@*@*)  repo="無効なアドレスです" ;;
    *@*)
      local=${email%@*} domain=${email#*@}
      if ! repo=`emaildomaincheck $email`; then
	repo="招待アドレスのエラー: $repo"
      elif [ -n "`query \"select * from user where name='$email';\"`"  ]; then
	repo="$email さんは既に加入しています。"
      elif sendinvitation $email; then
	repo="アドレス($email)宛に案内を送信しました。"
      fi ;;
    "") repo="招待したい人のメイルアドレスを入力してください。" ;;
    *)  repo="無効なアドレスです" ;;
  esac
  addr=`query "select val from par where sessid like 'invite-%-$user';"`
  if [ -n "$addr" ]; then
    susp="<h2>招待済みで加入待ちのアドレス</h2><pre>$addr</pre>"
  fi
  _m4 -D_TITLE_="招待" -D_REPORT_="\`$repo'" -D_ACTION_="?invite" \
      -D_BODYCLASS_="default" -D_SUSPENDED_="$susp" \
      $layout/html.m4.html $layout/invite.m4.html
}
regist() {
  # $1=session-id-for-invitation
  _m4 -D_TITLE_="Invitation" $layout/html.m4.html
  if [ -z "$1" ]; then
    echo "bye bye" | html p
    reutrn
  fi
  email=`session=$1 getpar invite`
  if [ -z "$email" ];then
    cat<<EOF
<p>無効な招待状チケットです。</p>
<p>招待状の有効期限(1週間)が切れているか、チケット番号が異なっています。
加入している人に、再度招待してもらいましょう。</p>
EOF
    return
  fi
  echo "$email さんようこそ" | html h2
  query "replace into user values('$email');"
  # Fake login password to wasureta
  query "replace into par values('$session', 'pswd', 'string', 'wasureta'),
('$session', 'user', 'string', '$email');"
  wasureta $email
  echo "このアドレスに初期パスワードを送信しました。"	|html p
  echo "新着メイルを確認してログインしてください。"	|html p
  addsession $1			# for removal after 1 minute
  _m4 -D_SYSNAME_="Initial Login" -D_MYNAME_="$myname?userconf" \
      $layout/login.m4.html
  return
}
group_safename() {
  # Convert $1 to safe group name
  echo "$1" | tr -d '"'"'",
}
groupupdate() {
  gname=`getpar gname`
  qgname=`sqlquote $gname`
  if [ -n "$gname" ]; then
    # See ALSO same job in showgroup()
    newgname=`group_safename "$gname"`
    err newgname=$newgname
    if [ x"$newgname" != x"$gname" ]; then
      err NewGNAME: gname=$newgname
      gname=$newgname
      echo "使用禁止文字を除去し $gname としました。" | html p
      replpar gname string "$gname"
    fi
    # Name confliction check
    parow=`getpar rowid`
## err parow=$parow
    qgname=`sqlquote $gname`	# Set again in case gname modified
    query "BEGIN EXCLUSIVE;"
    ## err "select count(gname) from grp where rowid != ${parow:-0} and gname = $qgname;"
    count=$(query "select count(gname) from grp where rowid != ${parow:-0} and gname = $qgname;")
    if [ $count -gt 0 ]; then
      echo "そのグループ名は既にあります。" | html p
      query "END;"
      return
    fi
    par2table $formdir/grp.def
    query "END TRANSACTION;"
    # Remove orphan
    : <<EOF
	select a.id,b.val from (select * from blog where id in
	 (select id from blog_s where key='owner'
	 and val not in (select name from user union select gname from grp)))
	a left join blog_s b on a.id=b.id and b.key='owner';
EOF
    rm=`getpar rm` cfm=`getpar confirm`
    ## err groupupdate:::: after par2tbl rmcfm=$rm$cfm
    if [ x"$rm$cfm" = x"yesyes" ]; then
      if [ -z "`query \"select gname from grp where gname=$qgname;\"`" ]; then
	sql="delete from blog where id in
	 (select id from blog_s where key='owner' and val=$qgname);"
	err rm-grp cleaning sql=`echo $sql`
	query "$sql";
	grps			# When removing a group, switch to grp-list
	return			# and return
      fi
    fi
    [ -z "$parow" ] && joingrp "$gname" "$user" yes "" as-admin
  fi
  sql="select rowid from grp where gname=$qgname;"
  grid=$(query $sql)
  ## err grpupdate:new-grid=$grid, sql=$sql
  grp $grid
}
groupclone() {
  # $1=grp-rowid of clone-base group
  qgrp=`query "SELECT quote(gname) FROM grp WHERE rowid=$1;"`
  if [ -z "$qgrp" ]; then
    echo "無効なグループIDです($1)" | html p
    return
  fi
  i=0
  while true; do
    copy="-copy$i"
    newqname=`query "SELECT quote($qgrp || '$copy');"`
    # err Trying new grp=$newqname with copy=$copy
    test=`query "SELECT gname FROM grp WHERE gname=$newqname;"`
    if [ -n "$test" ]; then
      i=$((i++))
      continue
    fi
    break
  done
  # Creating New group "$newqname" with members of old group
  # err Creating new grp=$newqname with copy=$copy
  query<<-EOF
	BEGIN;
	INSERT INTO grp VALUES($newqname);	-- Create NEW one
	REPLACE INTO grp_s(gname, key, val)	-- Copy tag
	       	     SELECT $newqname, key, val
		     FROM grp_s WHERE gname=$qgrp AND key IN ('tag', 'mode');
	REPLACE INTO grp_s(gname, key, type, val) -- Copy gecos with "copy$n"
	       	     SELECT $newqname, key, type, val || '$copy'
		     FROM grp_s WHERE gname=$qgrp AND key='gecos';
	-- Copy members and their configuration --
	REPLACE INTO grp_mem SELECT $newqname, user
		     	     FROM grp_mem WHERE gname=$qgrp;
	REPLACE INTO grp_mem_s SELECT $newqname, user, key, type, val, bin
		     	       FROM grp_mem_s WHERE gname=$qgrp;
	REPLACE INTO grp_mem_m SELECT $newqname, user, key, type, val, bin
		     	       FROM grp_mem_m WHERE gname=$qgrp;
	-- Copy administrators --
	REPLACE INTO grp_adm SELECT $newqname, user
		     	     FROM grp_adm WHERE gname=$qgrp;
	COMMIT;
	EOF
  newrowid=`query "SELECT rowid FROM grp WHERE gname=$newqname;"`
  STOPCLONEMSG=1 groupconf "$newrowid"
}
groupman() {
  note="<p>グループ名に使用できない文字は自動的に削除されます。</p>"
  
  GF_STAGE="grpconf"
  GF_STAGE=groupupdate
  DT_VIEW=grp dumptable html grp 'gname gecos:DESC mtime:TIME' 'order by b.TIME desc' \
  |_m4 -D_TITLE_="グループ作成" \
       -D_FORM_="$note`genform $formdir/grp.def`" \
       -D_DUMPTABLE_="syscmd(cat)" \
       $layout/html.m4.html $layout/form+dump.m4.html
}
userconf() {
  [ -n "`getpar rowid`" ] && par2table $formdir/user.def
  _m4 -D_BODYCLASS_=userconf -D_TITLE_="ユーザ情報編集" $layout/html.m4.html
  GF_ACTION="?home" edittable "$formdir/user.def" "user" "$user"
}
groupconf() {
  # $1=rowid in grp (2015-07-21 changed from gname)
  [ -n "`getpar rowid`" ] && par2table $formdir/grp.def
  _m4 -D_BODYCLASS_=groupconf -D_TITLE_="グループ情報編集" $layout/html.m4.html
  #rowid=`query "select rowid from grp where gname='$1';"`
  rowid=${1%%[!A-Z0-9a-z_]*}
  # GF_ACTION="?grp+$1" edittable "$formdir/grp.def" "grp" "$rowid" #2015-0804
  GF_STAGE="groupupdate" edittable "$formdir/grp.def" "grp" "$rowid"
  if [ -z "$STOPCLONEMSG" ]; then
    echo "同じ構成員で新規グループ<a href=\"?groupclone+$rowid\">作成</a>" \
	| html p
  fi
}
mems() {
  _m4 -D_TITLE_="参加者一覧" -D_BODYCLASS_=listmember $layout/html.m4.html
  kwd=`getpar kwd`
  listmember $kwd
}
grps() {
  _m4 -D_TITLE_="グループ一覧" -D_BODYCLASS_=listgroup $layout/html.m4.html
  kwd=`getpar kwd`
  listgroup $kwd \
      | _m4 -D_DUMPTABLE_="syscmd(cat)" \
	    -D_TITLE_="グループ関連操作" \
	    -D_FORM_="<a href=\"?groupman\">新規グループ作成</a>" \
	    $layout/form+dump.m4.html
}
grp() {	# $1=group-rowid
  gpg=`getpar grp`
  grid=${1:-$gpg}
  grp=`getgroupbyid "$grid"`
  ## . ./s4-blog.sh
  jg=`getpar joingrp`
  if [ -n "$jg" ]; then
    [ -n "$jg" -a -n "$grp" ] &&
	joingrp "$grp" "$user" "$jg" "`getpar email`"
  fi
  htmlheader=$layout/html.m4.html
  showgroup "$grid"
}
sql4interestblogs() {
  cat<<EOF
CREATE TEMPORARY VIEW interestblogs AS
 SELECT blog.rowid rid, id, author
 FROM blog
      NATURAL JOIN
      (SELECT id, val owner FROM blog_s WHERE key='owner') bs
 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL
	     THEN 1		-- blog owner is an user, READABLE
	    WHEN (SELECT user FROM grp_mem
		  WHERE gname=bs.owner AND user='$user') IS NULL
	    THEN 0
	    ELSE 1
       END;
EOF
}
listnewblogsql() { # $1=user
  deftime=`query "SELECT coalesce((SELECT time FROM acclog
		  	 	   WHERE user='$user'
				    AND tblrowid=$blogreadflagrowid),
				  "0");"`
  cat<<EOF
`sql4interestblogs`
WITH article_ctime as (
 SELECT id,blogid,author,val ctime
 FROM   article join article_s s using(id)
 WHERE  s.key='ctime'
), blog_title_owner as (
 SELECT blg.rid brid, id,
        max(case key when 'title' then val end) title,
        max(case key when 'owner' then val end) owner
 FROM interestblogs blg, blog_s using(id) group by id
), visited AS (
 SELECT b.id id, brid, b.owner owner, b.title title, ctime, ac.author author
 FROM blog_title_owner b, article_ctime ac
      JOIN acclog al
      ON b.id=ac.blogid
                AND al.tbl='blog'
                AND al.tblrowid=brid
                AND al.user='$user'
		AND al.time < ctime
		AND '$deftime' < ctime
), unvisited as (
 SELECT b.id id, brid, b.owner owner, b.title title, ctime, ac.author author
 FROM blog_title_owner b, article_ctime ac
 ON b.id=ac.blogid
 WHERE brid NOT IN (SELECT tblrowid FROM acclog
                   WHERE tbl='blog' AND user='$user')
       AND ctime > '$deftime'
), blogall as (
 /* ---------------------------------------
	Collect new articles with dividing them into visited and unvisited
	separately, because constructing joined table of artice X acclog
	tends to become HUGE combinations.
    --------------------------------------- */
 SELECT * FROM visited UNION SELECT * FROM unvisited
), news as (
 select bl.brid brid, bl.title, bl.id blid, ctime,
        count(bl.id) "新着", bl.owner, bl.author
 from blogall bl /* left join a_u l
 on bl.brid=l.tblrowid */
 group by bl.id order by ctime desc,"新着" desc, bl.id
 LIMIT 10
) SELECT brid LINK, "新着",
       (SELECT count(*) FROM article WHERE blogid=blid) "総数",
       ctime, title,
       (SELECT gecos FROM gecoses WHERE name=author) gecos
  FROM news;
EOF
}

search_form() {
  help="(1)空白区切りの単語で本文検索
(2)@YYYY-MM-DD 日付け(シェルパターン可)で日付け検索
   @2016-0[1-6]  → 2016年1月から6月
   @>2016-01 @<2016-02-15  → 2016年1月から2月14日までの期間
   @week  → 最近一週間
(3)#番号 で記事ID検索
(1)と(2)は組み合わせOK
  例: @2016-10-0[1-9] 芋煮
  → 2016年10月上旬でキーワード「芋煮」を含む記事検索"
  auth=""
  placeholder="全記事からの検索"
  case "$1" in
    author=*)
      a=`echo "${1#author=}"|htmlescape`
      g=`gecos ${1#author=}`
      auth="<input type=\"hidden\" name=\"author\" value=\"$a\">"
      placeholder="このユーザの書込検索"
      help="★★ $g さんの書き込みから検索します$nl$help"
      ;;
  esac
  cat<<-EOF
	<div class="fr">
	<form action="$myname">$auth
	<input type="text" name="kwd" value="" title="$help"
	 placeholder=" $placeholder " width="10" accesskey="k">
	 ${touchpanel:+<p class="help">$help</p>}
	<input type="hidden" name="stage" value="searchart">
	</form>
	</div>
	EOF
}

imgsrc_cache() (
  # $1 = directory for cache'ing
  # $2 = table (user_m or grp_m)
  # $3 = keycond (was: condition for choosingowner)
  # $4 = size : S = Small, M = Medium, O = Original
  dir="$1" tbl="$2"
  keycond="$3"
  whos="$keycond AND key='profimg' AND type LIKE 'file:image%'
	 ORDER BY rowid DESC LIMIT 1"
  [ -d "$dir" ] || mkdir -p "$dir"
  tmpf=$tmpd/imgsrc_cache.$$
  case "$4" in
    [Ss]) size=S ;;
    [Oo]) size=O ;;
    *)    size=M ;;
  esac
  # ImageCache filename storing schema:
  # <table_s>.{key, val}={"profimgcache_S", "$cacheimg_S"}
  sql0="SELECT val || '//' || type FROM $tbl WHERE $whos;"
  sql1="SELECT hex(bin) FROM $tbl WHERE $whos;"
  valtype=`query "$sql0"`
  filename=${valtype%%//*}
  filetype=${valtype##*//file:}
  if [ x"$filename" = x"${filename%.*}" ]; then
    # If nor filename extension found, set it to image type
    case "$filetype" in
      image/*) filename=$filename.${filetype#image/} ;;
    esac
  fi
  cacheimg_S=$dir/S_$filename
  cacheimg_M=$dir/M_$filename
  cacheimg_O=$dir/$filename
  cacheimg=$dir/${size}_$filename
  sumfile="$dir/$filename.sum"
  sum=`query "$sql1" | tee $tmpf | encode`	# encode() is maybe sha1
  if test -s "$sumfile" && [ x"`cat \"$sumfile\"`" = x"$sum" ] \
     && test -s "$cacheimg_S" && test -s "$cacheimg_M" ; then
    # if cache is fresh and has the same checksum,
    echo "<img src=\"$cacheimg\">"
  else
    fifo=`mktemp "$tmpf.fifo.XXXXXXX"`
    rm -f $fifo			# Safe, because $tmpf is in mktemp dir.
    fifo2=$fifo.2
    mkfifo $fifo $fifo2
    fmt=${filename##*.}
    ## [[ NOTE ]]
    ## a. convert oldimage newimage
    ## b. convert oldimage fmt:- | convert - newimage
    ## b is much smaller than a
    cat $tmpf | unhexize \
	| tee $fifo \
	| convert -define ${fmt}:size=${iconxy_M} \
		  -resize ${iconxy_M}'>' - ${fmt}:- \
	| tee $fifo2 \
	| convert - "$cacheimg_M" &
    cat $fifo | convert -define ${fmt}:size=${iconxy_S} \
			-resize ${iconxy_S}'>' - ${fmt}:- \
	| convert - "$cacheimg_S" &
    printf '%s' "<img src=\"data:${filetype},"
    hexize "$fifo2" |sed 's/\(..\)/%\1/g' # Use medium as pre-cached image
    echo '">'
    echo "$sum" > $sumfile
  fi
  ## Now preparing cache image, done.
  ## Store this information to DB
  stbl=${tbl%_m}_s		# user_s or grp_s
  pkey=${keycond%%=*}		# Primary Key name
  pval=${keycond#*=}		# Primary Key value
  query <<-EOF
	REPLACE INTO $stbl($pkey, key, type, val)
	VALUES($pval, '$iconcachekey', 'string', `sqlquote "$cacheimg_S"`);
	EOF
)

showhome() {
  # $1=userRowIdToShow
  err showhome \$1=$1
  case "$1" in
    *@*) uname=`getvalbypkey user name "$1"` ;;
    *)   uname=`getvalbyid user name $1` ;;
  esac
  ## err ShowHome: uname=$uname
  td=`getcachedir home/"$1"`
  gecos=`gecos "$uname"`
  ## err SH:gecos=$gecos
  GF_VIEWONLY=1
  cond="gname in (select gname from grp_mem where user='$uname')"
  search_form_args=""
  if [ x"$user" = x"$uname" ]; then
    conflink="<a href=\"?userconf\" accesskey=\"e\"
	 title=\"E\">プロフィールの編集</a> /
	<a href=\"?blog\" accesskey=\"n\" title=\"N\">新規話題の作成</a>"
    # Display folders
    sql="select count(id) from article_m where id
		 in (select id from article where author='$user')
		and type like 'file:%';"
    ## err nfile-sql=`echo "$sql"`
    nfile=`query "$sql"`
    # err nfile=$nfile
    if [ $nfile -gt 0 ]; then
      conflink="$conflink / <a href=\"?lsmyfile\" accesskey=\"l\"
       title=\"L\">過去の提出ファイル</a>"
    fi
  else
    search_form_args="author=$uname"
  fi
  . ./s4-blog.sh

  tf=$tmpd/title.$$ pf=$tmpd/profile.$$ bf=$tmpd/blogs.$$ sf=$tmpd/search.$$
  search_form $search_form_args		> $sf
  echo "$gecos さん"			> $tf
  { echo "<div class=\"noprofimg\">"
    viewtable $formdir/user.def user $1
    echo "</div>"
  } > $pf

  sqcond="WHERE name='$uname' AND key='profimg' AND type LIKE 'file:image%'"
  img=`query "SELECT type FROM user_m $sqcond LIMIT 1;"`
  imf=$tmpd/profimg.$$; touch $imf
  if [ -n "$img" ]; then
    if true; then
      tbl=user_m
      enticond="name='$uname'"
      imgsrc_cache "$td/main" user_m "$enticond" M
    else
    { printf '%s' "<IMG src=\"data:${img#file:},"
      query "SELECT hex(bin) FROM user_m $sqcond ORDER BY rowid LIMIT 1;" \
    	  | sed 's/\(..\)/%\1/g'
      echo '">'
    }
    fi    > $imf
  fi
  nblog=`query "SELECT count(id) FROM blog_s WHERE key='owner' AND \
		val='$uname';"`
  listblog $uname			> $bf

  hometail=$tmpd/tail.$$
  mkfifo $hometail

  #Calling listgroupbytable, originally here
  
  (
  # Display Most Recent Entry
  shortval=${dumpcollen:+"substr(val, 0, $dumpcollen)"}
  shortval=${shortval:-val}

  # The m.aid in the next line is suspicious.  But works fine in SQLite3...
  DT_SQL="SELECT b.rowid || '#' || m.aid LINK,
       ctime,
       (SELECT $shortval FROM blog_s WHERE key='title' AND id=b.id) title,
       (SELECT gecos FROM gecoses
        WHERE name=(SELECT val FROM blog_s
                   WHERE key='owner' AND id=b.id)) owner,
       (SELECT $shortval val FROM article_s WHERE id=m.aid AND key='text') text
    FROM blog b
     JOIN
     (SELECT distinct blogid, a.id aid, max(val) ctime
      FROM article a, article_s s
      ON a.id=s.id AND a.author='$uname' AND s.key='ctime'
      GROUP BY blogid ORDER BY val DESC LIMIT 50
     ) m
     ON b.id=m.blogid;"
  # This should be as follows
  : <<EOF
WITH arts AS(
  SELECT (SELECT rowid FROM blog WHERE id=a.blogid) brid,
         a.blogid, a.id id, s.val ctime
  FROM article a NATURAL JOIN article_s s
  WHERE s.key = 'ctime' AND a.author='$user'
  GROUP by s.id
)
SELECT a0.brid,a0.blogid,a0.id,a0.ctime
FROM arts a0
     JOIN
     (SELECT blogid,max(ctime) mct FROM arts a1 GROUP BY blogid) a1
     ON a0.blogid=a1.blogid AND a0.ctime=a1.mct
ORDER BY ctime DESC LIMIT 50;
EOF

  cat<<-EOF
	`cgi_radio foldtabs yes 'id="mre" accesskey="d"'`<label
	 for="mre" title="D">最近の書き込み先</label>
	<div class="lcto">
	`DT_VIEW=replyblog dumptable html blog`
	</div>
	EOF
  unset DT_SQL
  if [ x"$user" = x"$uname" ]; then
    # Display NEWS
    # 2016-06-26
    if [ x"`getpar readchk``getpar read`" = x"yesyes" ]; then
      acclog blog $blogreadflagrowid
      # echo "全部既読にしました" | html p
    fi
    # 2016-02-19 Counting NEWS without using dumptable.
    sql=`listnewblogsql "$user"`
    new10=`DT_SQL="$sql" DT_VIEW=replyblog dumptable html blog`
    cont=`echo "$new10"|grep "^<TR>"|wc -l`
    cont=$((cont-1))
    err newcount=$cont
    if [ $cont -gt 0 ]; then
      #echo "全体の新着記事${cont}傑" | html h2
      cgi_radio foldtabs yes 'id="new10" accesskey="f"'
      echo "<label for=\"new10\" title=\"X\">新着${cont}傑</label><div>"
      cat<<-EOF | html form 'action="?home"'
	`cgi_checkbox readchk yes 'id="read"'`<label
	 for="read">新着ふくめて全部読んだことにする</label>
	 `cgi_submit '確定'`
	 `cgi_hidden read yes`
	EOF
      echo "$new10 <!-- new10 -->"
      echo "</div>"
    fi
  else				# Not My Home ($user != $uname)
    : # DT_SQL=
  fi
  ) > $hometail &		# Is background call safe to m4??
  #
  listgroupbytable $formdir/grp.def $cond |
      _m4 -D_BODYCLASS_=home -D_TITLE_="spaste(\`$tf')" \
	  -D_PROFILE_="spaste(\`$pf')$conflink" \
	  -D_PROFIMG_="spaste(\`$imf')" \
	  -D_BLOGS_="spaste(\`$bf')" \
	  -D_SEARCH_="spaste(\`$sf')" \
	  -D_NBLOG_="$nblog" \
	  -D_GROUPS_="syscmd(\`cat')" \
	  -D_HOMETAIL_="syscmd(\`cat $hometail')" \
	  $layout/html.m4.html $layout/home.m4.html

  # Record access log
  [ -n "$1" ] && [ x"$1" != x"$user" ] && acclog user $1
}
commission() {  # $1=grp-rowid $2=user-rowid
  contenttype; echo
  ## err commission: "$@"
  gname=`getgroupbyid $1`
  echo "グループ $gname 管理者委任" \
      | _m4 -D_TITLE_="syscmd(\`cat')" $layout/html.m4.html
  if [ -n "$2" ]; then
    grp_reg_adm "$@"
  else
    echo "無効な指定です。普通のアクセスならここに来ないはず。"|html p
  fi
}
listgroupbytable() {
  # $1=deffile $2...=condition
  tagline=`grep :tag: $1`; shift
  and="${1:+and }" where=${1:+where }
  href="<a href=\"$myname?grp+"
  echo '<div class="listgroup">'
  NGsql="select distinct tag from\
	(select gname, max(case key when 'tag' then val end) as tag, \
	  max(case key when 'ctime' then val end) as ctime\
	 from grp_s group by gname order by ctime);"
  sql="select val from grp_s where key='tag' $and$* group by val;"
## err ListGRP: query sql="$sql"
  for tag in `query "$sql"`
  do
## err ListGrp: tag=$tag
    tn=${tagline%%=${tag}*}
    tn=${tn##*[ :]}
    sql="select rowid||':'||gname as 'グループ名',説明 from
	 (select (select rowid from grp g where g.gname=grp_s.gname)
		 as rowid,
	 gname,
	 max(case key when 'gecos' then val end) as '説明',
	 max(case key when 'tag' then val end) as 'tag',
	 max(case key when 'mtime' then val end) as mtime from grp_s
	 $where$* group by gname having tag='$tag' order by mtime desc);"
## err PersonalGroupList= `echo $sql`
    echo "<h2>$tn</h2>"
    echo '<table class="b listgroup">'
    sq -header -html $db "$sql" \
       | sed "s,\(<TR><TD>\)\([0-9]*\):\([^ ]*\)</TD>,\1$href\2\">\3</a>,"
    echo '</table>'
  done
  echo '</div>'
}
iconhref() (
  # $1=icon-file, $2=Href $3=title $4...=anchor
  data=`percenthex "$1"`
  ct=`file --mime-type - < "$1"|cut -d' ' -f2`
## err iconhref: \$1=$1 \$2=$2 \$3="$@"
  href=$2; title=$3; shift 3
  echo "<a href=\"$href\"><img title=\"$title\" src=\"data:$ct,$data\">$@</a>"
)
iconhref2() (
  # $1=icon-file, $2=Href $3=title $4...=anchor
  src=$1
  href=$2; title=$3; shift 3
  echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$@</a>"
)
listentry() (
  # $1=user/group $2=SearchKeyword $3=condition(if any) $4=grprowid(if in grp)
  # Referring variable $iamowner=$grp to attach owner-request links
## err listentry: \$1=$1 \$2=$2 \$3=$3
  cond='' hiddens=''
  offset=`getpar offset`
  offset=${offset%%[!0-9]*}
  offset=$((offset + 0))	# change to numeric forcibly
  [ $offset -lt 0 ] && offset=0
  limit=30
  dir=`getcachedir "$1"`
  if [ x"$1" = x"user" ]; then
    hrb="$myname?home"
    deficon=person-default.png
    entity="ユーザ" tbl=user link=rowid nm=name # stage=mems
    [ -n "$4" ] && hiddens=`cgi_hidden grid $4`
    gcs=gecos
  else				# if group
    hrb="$myname?grp"
    deficon=group-default.png
    entity="グループ" tbl=grp link=rowid nm=gname stage=grps
    gcs=name
    tagline=`grep :tag: $formdir/grp.def|cut -d: -f5-`
    if [ -n "$tagline" ]; then
      tagconv=`echo $tagline|sed 's/\([^= :]*\)=\([^= :]*\)/-D\2=\1/g'`
## err tagconv=$tagconv
    fi
  fi
  if [ ! -d $dir ]; then
    mkdir -p $dir
  fi
  if [ ! -s $dir/$deficon ]; then
    convert -geometry $thumbxy $imgdir/$deficon $dir/$deficon
  fi
  if [ -n "$2" ]; then
    cond="where nick like '%$2%' or b.name like '%$2%'"
  fi

  # XX: これ複雑すぎるかな。もっとシンプルにしたい。$3条件も。2015-07-08
  # grpは呼出し元の動的スコープ変数でよくないな...
  ##qgrp=`sqlquote $grp`
  getgrp="(select gname from grp where rowid=${rowid:--1})"
  sql="select a.rowid, a.$link,
	coalesce(b.$gcs, a.$nm) as nick,
	quote(a.$nm) as qname,
	(SELECT val FROM ${tbl}_s
	 WHERE $nm=a.$nm AND key='$iconcachekey') icon,
	coalesce(b.gecos, a.$nm)  /* If group, concat (Nusers) */
	  || case when a.$nm in (select gname from grp)
		  then printf('(%d名)',
			(select count(user) from grp_mem where gname=a.$nm))
	          else ' <'||a.$nm||'>'
	     end
 	  as name,
	 b.tag,
	case when a.$nm in (select user from grp_adm
		where gname=$getgrp) then '(管理者)'
	     when '$user' in (select user from grp_adm where gname=a.$nm)
		then '(ADMIN)'
	     when '$user' in (select user from grp_mem where gname=a.$nm)
		then '(Member)'
	     when '$iamowner' = '' then ''
	     else ',not='||a.rowid end as ownerlink,
	CASE '$entity'
	  WHEN 'グループ'
	  THEN coalesce(
		(SELECT val FROM grp_s WHERE gname=a.$nm AND key='regmode'),
		'open')
	       ||
	       CASE WHEN '$user'
	     	      IN (SELECT user FROM grp_mem WHERE gname=a.$nm)
		    THEN ' ismember'
		    ELSE ''
	       END
	  ELSE 'user'
	END regmode
	from $tbl a left join
		(select $nm as name,
			max(case key when 'gecos' then val end) as gecos,
			max(case key when 'tag' then val end) as tag,
			max(case key when 'mtime' then val end) as mtime,
			max(case key when 'wtime' then val end) as wtime
			from ${tbl}_s group by $nm)
		b on a.$nm=b.name $cond $3
	order by b.tag desc, b.wtime desc, b.mtime desc, a.rowid asc"
  # Give precedence to newer maintained groups (2016-09-24)
  # Note that mtime is stored only in grp_s.
## err LE:sql.1="$sql"
  total=`query "with x as ($sql) select count(*) from x;"`
  echo "${entity} 一覧" | html h2
  if [ $total -gt $limit ]; then
    echo '<div class="right">'
    cgi_form $stage <<EOF
<label>次の語を含む${entity}で検索:
`cgi_text kwd $kwd`</label>
EOF
    echo '</div>'
  fi
  hiddens="$hiddens
	`cgi_hidden kwd \"$kwd\"`
	`cgi_hidden stage \"$stage\"`"
  cat<<EOF
  <p>${total}件中の$((offset+1))件めから${kwd:+" - 検索語: $kwd"}</p>
EOF
  if [ $((offset+limit)) -lt $total ]; then
    nextbtn=$(
    cat<<EOF
<div class="right clear"><form action="$myname" method="POST">
`cgi_submit 次の${limit}件`
$hiddens
`cgi_hidden offset $((offset + limit))`</form></div>
EOF
	   )
  fi
  if [ $offset -gt 0 ]; then
    prevbtn=$(
    cat<<EOF
<form action="$myname" method="POST">
`cgi_submit 前の${limit}件`
$hiddens
`cgi_hidden offset $((offset - limit))`</form>
EOF
    )
  fi
  pnbtn="$nextbtn$prevbtn"
  echo $pnbtn

##  err ListEntry: `echo "$sql"\;`
# sq $db here??? 2016-11-28
  query "$sql limit $limit ${offset:+offset $offset};" \
      | while IFS='|' read id lnk name qname icon gecos tag ownerp type; do
err name=$name owner=$ownerp lnk=$lnk
err newlnk=$lnk regmode=$regmode
    icondir=$dir/$id
    # Pick up only last icon
    echo "<div class=\"iconlist xy$thumbxy $type\">
	<p class=\"tag _$tag\">$tag</p>" \
	| _m4 $tagconv
    if [ -n "$NOSPEEDUP" ]; then
      files=`getvalbyid $tbl profimg $id $icondir`
      if [ -n "$files" ]; then
	icon=`echo "$files"|tail -1`
	iconhref2 "$icondir/$icon" "$hrb+$lnk" "$gecos"
      else
	iconhref "$dir/$deficon" "$hrb+$lnk" "$gecos"
      fi
    elif [ -n "$icon" -a -s "$icon" ]; then
      iconhref2 "$icon" "$hrb+$lnk" "$gecos"
    else
      cond="$nm=$qname"
      # err imgsrc_cache "$dir/list" ${tbl}_m "$cond" S
      # err query "SELECT type FROM ${tbl}_m $cond LIMIT 1;"
      img=`query "SELECT type FROM ${tbl}_m WHERE $cond AND key='profimg' LIMIT 1;"`
      # err "img=[$img]"
      if [ -n "$img" ]; then
	echo "<a href=\"$hrb+$lnk\">"
	imgsrc_cache "$icondir" ${tbl}_m "$nm=$qname" S
	echo "</a>"
      else
	iconhref2 "$dir/$deficon" "$hrb+$lnk" "$gecos"
      fi
    fi
    echo "<br>$name${ownerp:+<br>$ownerp}"
    echo "</div>"
  done
  echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}
)
listmember() {
  listentry user "$@"
}
listgroup() {
  listentry group "$@"
}
hexteams() {	# $1=gname, $2(optional)=user
  cond=${2:+" AND user='$2'"}
  query "SELECT DISTINCT hex(val) FROM grp_mem_m
	 WHERE gname='$1' AND key='team'$cond;"
}
showgroup() { # $1=group-rowid
  if [ -z "$1" ]; then
    grid=`getpar grid`
    grid=${grid%%[!0-9]*}
    [ -n "$grid" ] && grp=`getgroupbyid $grid`
  else
    grid=$1
  fi
  grp=`getgroupbyid $grid`
  qgrp=`sqlquote $grp`
## err showgroup2: grp=$grp qgrp="[$(sqlquote $grp)]"
  if isgroup "$grp"; then
    tf=$tmpd/title.$$
    bodyclass=`query "SELECT val FROM grp_s
	       WHERE gname=$qgrp AND key='regmode';"`
    if ismember "$user" "$grp"; then
      ismember="ismember"
      qgrp=`sqlquote $grp`
      bodyclass="$bodyclass${bodyclass:+ }ismember"
    else
      ismember="" # bodyclass="group"
    fi
    bodyclass="$bodyclass grouphome"
    echo "グループ $grp" > $tf
    showgroupsub $formdir/grp.def "$grid" | \
	_m4 -D_TITLE_="spaste(\`$tf')" \
	    -D_FORM_="syscmd(\`cat')" \
	    -D_BODYCLASS_="$bodyclass" \
	    -D_DUMPTABLE_="" \
	    $htmlheader $layout/form+dump.m4.html
    	# $htmlheader is defined in grp()
  else				# if $grp is removed at par2table
    listgroup
  fi
}
showgroupsub() {
  # $1=def-file $2=group-rowid
  # Using $ismember
  rowid=$2
  grp=`getgroupbyid $2`
  qgrp=`sqlquote $grp`
  td=`getcachedir grp/"$2"`
  #rowid=`sq $db "select rowid from grp where gname=$qgrp"`
  if [ -z "$rowid" ]; then
    #rowid=`sq $db "select rowid from grp where rowid=$grp"`
    #grp=`sq $db "select gname from grp where rowid=$grp"`
    echo "showgroupsub: invalid argument($1 $2)" | html p
    return
  fi
  val=`getvalbyid grp profimg $rowid $tmpd`
  enticond="gname=$qgrp"
  img=`query "SELECT type FROM grp_m WHERE $enticond LIMIT 1;"`
  if [ -n "$img" ]; then
    cat<<-EOF
	<p class="groupimg">
	`imgsrc_cache $td/main grp_m "$enticond" M`</p>
	EOF
  fi
  echo "<div class=\"noprofimg\">"
  viewtable $1 grp $rowid
  echo "</div>"
  if isgrpowner "$user" "$grp"; then
    echo "<p><a href=\"?groupconf+$rowid\">グループ情報の編集</a>"
    iamowner=$rowid
    colmd=" mode"
  fi
  if [ -n "$ismember" ]; then
    echo "${iamowner:+ / }<a href=\"?blog+$rowid\">グループの新規話題作成</a>"
    echo "/ <a href=\"?grpaction+$rowid\">メンバーを個別選択しての操作</a></p>"
# div.fold input[type="checkbox"]:checked ~ div {display: block;}
    cat<<EOF
<form action="?send2mem" method="POST" enctype="multipart/form-data">
<div class="fold clear">
`cgi_checkbox send yes id="send"`<label
 for="send">グループ全員にメッセージ送信</label>
<div>
`cgi_textarea message "" "cols=60"`
`cgi_submit 送信`
`cgi_reset リセット`
</div>
`cgi_hidden grp $rowid`
</div></form>
EOF
  fi
  # 加入ボタン + 加入者リスト
  if [ -n "$ismember" ]; then
    ismem='checked' state="(参加中)"
  else
    nomem='checked' state="(現在非加入)"
  fi
  # このグループでの加入アドレス
  eml=`query "select val from grp_mem_s where gname=$qgrp and user='$user' \
	and key='email';"`
##err EML: "select val from grp_mem_s where gname='$2' and user='$user' \
##	and key='email';"
##err email=$eml
  cat <<EOF
<div class="fold clear">
`cgi_checkbox reg yes id="reg"`<label
 for="reg">自身の加入状態を操作する</label>$state
<div>
EOF
  cgi_form grp <<EOF
<p>このグループに</p>
<table class="b">
<tr><th>メンバーとして</th><td>
<label>`cgi_radio joingrp "yes" $ismem`参加</label> / 
<label>`cgi_radio joingrp "no" $nomem`参加しない</label></td></tr>
<tr><th>参加する場合のメイルアドレス<br>
<small>(メインのアドレスとは違うものにする場合に記入<br>
同じでよい場合は空欄に)</small></th>
<td>`cgi_text email $eml`</td></tr>
</table>
`cgi_hidden grp $rowid`
EOF
  if [ x`getgroupattr $grp regmode` = x'moderated' -a -z "$ismem" ]; then
    echo "moderated (承認加入の)グループなので実際に参加できるのは
グループ管理者が承認操作をした後になります。" | html p 'class="warn"'
  fi
  echo '</div></div>'
  echo '<h2>話題一覧</h2>'
  cgi_form searchart<<EOF
<label>`cgi_text kwd`という語を含むコメントを検索</label>
`cgi_hidden owner $grp`
EOF
  cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"
  DT_CHLD=article:blogid \
	 DT_VIEW=replyblog dumptable html blog \
	 "ctime team title heading$colmd" "$cond"

  getgname="(select gname from grp where rowid=$rowid)"
  c="group by a.name having a.name in (select user from grp_mem where gname=$getgname)"
  cm="?commission+$rowid"
  thumbxy=50x50 listmember "" "$c" "$rowid" \
      |sed -e "s|\(<br>\),not=\(.*\)|\1|"	# 間違って押しやすい
  # team list
  hexteams=`hexteams "$grp"`
  if [ -n "$hexteams" ]; then
    echo "チーム一覧" | html h2
    echo '<div class="dumptable"><table class="b">'
    sq $db -html -header<<-EOF
	SELECT val TEAM,
       	       group_concat((SELECT gecos FROM gecoses WHERE name=user), ',')
		MEMBERS
	       FROM grp_mem_m WHERE gname=$qgrp AND key='team' GROUP BY val;
	EOF
    echo '</table></div>'
  fi
}
grp_getbodyclass() {
  # Get css class name for document.
  # `moderated' for moderated groups
  # `ismember'  for groups where user belongs
  # $1=GroupName (w/o quote)
  # $user=userNameCurrentlyLogin
  ## err grp_getbodyclass: 1="$1"
  qgrp=`sqlquote "$1"`
  query<<-EOF
	SELECT coalesce(
	   (SELECT val FROM grp_s WHERE gname=$qgrp AND key='regmode'),
	   'open')
	 ||
	   CASE WHEN '$user'
	     	      IN (SELECT user FROM grp_mem WHERE gname=$qgrp)
	        THEN ' ismember'
	        ELSE ''
	   END;
	EOF
}
grpaction() {			# $1=group-rowid
  err GRP_ACTION:IN
  grid=${1:-`getpar grp`}
  grp=`getgroupbyid "$grid"`
  if [ -z "$grp" ]; then
    echo "無効な指定です。" | html p; return
  fi
  if ! ismember $user $grp; then
    echo "加入者のみに許可された操作です。" | html p; return
  fi
  echo "グループ $grp 個別選択操作" \
      | _m4 -D_TITLE_="syscmd(\`cat')" \
	    -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \
	    $layout/html.m4.html

  isowner=""
  isgrpowner "$user" "$grp" && isowner="yes"
  usel=`getpar usel`
  if [ -n "$usel" ]; then
    uids=$(echo `echo $usel`|tr ' ' ',')
    ## err grpaction-1: grp=$grp, `echo $sql`
    text=`getpar text`
    
    rm=`getpar rm` cfm=`getpar confirm`
## err rm=$rm cfm=$cfm
    if [ x"$rm" = x"yes" ]; then
      if [ "$isowner" ]; then
	if [ x"$rm$cfm" = x"yesyes" ]; then
	  # Eliminate
	  cond="where gname=(select gname from grp where rowid=$grid) and user in (select name from user where rowid in ($uids))"
	  for tbl in grp_mem grp_mem_s grp_mem_m; do
	    sql="delete from $tbl $cond;"
	    # echo "sql=$sql"
	    query "$sql"
	    err rmGRPuser "$sql"
	  done
	  num=`query "select count(*) from user where rowid in ($uids);"`
	  #err num=$num
	  if [ 0$num -gt 0 ]; then
	    sql="select coalesce(b.val,a.name) from user a left join \
	user_s b on a.name=b.name and key='gecos' where a.rowid in ($uids);"
	    # err `echo "$sql"`
	    html pre<<EOF
以下の${num}名のグループ $grp 登録を解除しました。
`query "$sql"`
EOF
	  fi
	else
	  echo "確認のチェックがないのでやめておきます。" | html p
	  return
	fi
      else			# not Group Owner
	echo "グループ管理者でないのでメンバー操作はできません。" | html p
	return
      fi
      cat<<EOF

EOF
    elif [ x"$rm" = x"send" ]; then	# if sendmsg mode
      if [ -z "$text" ]; then	# if msg is empty
	echo "なにかメッセージを..." | html p
	return 0
      fi
      gecos=`gecos $user`
      mkfrom=`getpar mkfrom`
      if [ x"$mkfrom" = x"yes" ]; then
	safegc=`echo "$gecos" | tr -d '<>@'`
	myuid=`query "SELECT rowid FROM user WHERE name='$user';"`
	fromad=`email4groupbyuid "$grp" "$myuid"`
	mail_from="$safegc <$fromad>"
      else
	mail_from="$admin"
      fi
      MAIL_FROM=$mail_from \
	       smail "`email4groupbyuid "$grp" $usel` $user" \
	       "$gecos さんからのメッセージ" <<EOF
$url
のグループ「$grp」のメンバーである $gecos さんから、
あなた宛へのメッセージです。
----------------------------------------------------------
$text
EOF
      if [ $? = 0 ]; then
	echo "Note: 以下のメンバーにメッセージを送信しました。" | html p
	sql="select coalesce(b.val, a.name) from
		(select name from user where rowid in ($uids)) a
		left join user_s b on a.name=b.name and b.key='gecos';"
	html pre<<EOF
`query "$sql"`
(送信者である $gecos さんも含まれます)
EOF
	err SendDone: `echo $sql`
      fi
    elif [ x"$rm" = x"commission" ]; then
      grp_reg_adm $grid $usel
    elif [ x"$rm" = x"addteam" ]; then
      team=`getpar team|sed "s/'/''/g"`	# for single quotation
      newteam=`echo "$team"|tr -d ,`
      if [ x"$team" != x"$newteam" ]; then
	echo "チーム名に使えない文字を除去しました" | html p
	team=newteam
      fi
      if [ -z "$team" -o x"$team" = x"なし" ]; then
	cat<<-EOF | html p
	有効なチーム名を入力してください。
	カンマだけ、「なし」という名前は使えません。
	EOF
	echo "有効なチーム名を入力してください。" | html p
      else
	grp_add_team $grid "$team" $usel
      fi
    elif [ x"$rm" = x"rmteam" ]; then
      if [ x"yes" = x"`getpar teamconfirm`" ]; then
	rmteam=`getpar rmteam|sed "s/'/''/g"`
	if [ -n "`query \"SELECT val FROM grp_mem_m WHERE\
		gname='$grp' AND user='$user' AND key='team'\
		AND val='$rmteam';\"`" ]; then
	  grp_rm_team $grid "$rmteam" $usel
	else
	  echo "所属していないチームの除去操作はできません。"|html p
	fi
      else
	echo "確認チェックなしなのでチーム除去しませんでした。"|html p
      fi
    fi
  fi
  # New entry
  sql="select 	/* Ahh, ugly SQL, I wanna fix... */
	 case
	 when (select user from grp_adm where
		 gname=(select gname from grp where rowid=$grid)
		 and user=a.name) is not null
	      then 'k'
	 else ''
	 end || a.rowid||
	 ','||a.gecos as NAME,
	 (SELECT count(author)  /* Put post count for scoring 2016-08-01 */
		 FROM article NATURAL JOIN article_s
		 WHERE blogid IN
		      (SELECT id FROM blog_s
		       WHERE key='owner'
			 AND val=(SELECT gname FROM grp where rowid=$grid))
		 AND author=a.name AND key='text')
	    as POST,
	 (SELECT group_concat(val, ',')
	  FROM	 grp_mem_m
	  WHERE	 gname='$grp' AND user=a.name AND key='team') as TEAM
	 FROM
	 gecoses a
	 WHERE name in (select user from grp_mem where
		 gname=(select gname from grp where rowid=$grid))
	 ORDER by a.gecos;"
  ## err grpaction: "`echo \"$sql\"`"
  tf=$tmpd/title.$$
  echo "グループ[<a href=\"?grp+$grid\">$grp</a>]参加メンバーに対する操作" > $tf
  cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label
 for=\"cmadmin\">グループ管理者委任</label>
<div><p>このグループでの全権を付与します。信頼できる人に託してください。
</p></div>"
  excmsg="`cgi_radio rm yes id=\"conf\"`<label
 for=\"conf\">グループ登録解除</label>
<div>本当に消します! `cgi_checkbox confirm yes` 確認
<p>この操作による通知は本人に行きません。
あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>
</div>"
  # Get team list to which current user belongs into $hexteams
  myhexteams=$(hexteams "$grp" "$user")
  allhexteams=$(hexteams "$grp")
  if [ -n "$myhexteams" ]; then
    rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label
for=\"cmrmteam\">チーム属性除去</label>
<div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`
を除去します: `cgi_checkbox teamconfirm yes` 確認
<p>この操作による通知は本人に行きません。
あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>
</div><!-- end of $rmteammsg -->
"
  fi
  b1='<label> <input type="checkbox" name="usel" value="'
  ba='<label class="admin"><input type="checkbox" name="usel" value="'
  #b2='"> <span>' b3='</span></label>'
  #    	| sed  -e "s|^\(<TR><TD>\)k\([0-9]*\),\([^<]*\)|\1$ba\2$b2\3$b3|" \
  #	       -e "s|^\(<TR><TD>\)\([0-9]*\),\([^<]*\)|\1$b1\2$b2\3$b3|" \
  lnk='"> <span>\3</span></label> [<a href="?home+\2">HOME</a>]'
  cgi_form grpaction<<EOF \
      	| sed  -e "s|^\(<TR><TD>\)k\([0-9]*\),\([^<]*\)|\1$ba\2$lnk|" \
	       -e "s|^\(<TR><TD>\)\([0-9]*\),\([^<]*\)|\1$b1\2$lnk|" \
	| _m4 -D_TITLE_="spaste(\`$tf')" \
	      -D_SUBTITLE_="チェック後操作ボタン" \
	      -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \
	      $layout/form+dump.m4.html
<p>下でチェックした人を対象として:</p>
<div class="foldtabs">
`cgi_radio rm addteam 'id="cmteam"'`<label
for="cmteam">同じチーム属性を付与</label>
<div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`
`cgi_datalist_h teams $allhexteams`
</div>
${rmteammsg}
`cgi_radio rm send id="sendmsg"`<label
 for="sendmsg">メッセージ送信</label>
<div>
`cgi_checkbox mkfrom yes 'id="mkfrom" checked'`<label for="mkfrom"
>差出人を自分に(チェックを外すと相手が返事できない)</label><br>
`cgi_textarea text "" cols=40`
</div>
${isowner:+$cmmsg$excmsg}
`cgi_radio rm close id="x"`<label for="x">×</label>
</div>
<h4>$grp 参加者一覧</h4>
<table class="td2r">
`sq $db -header -html "$sql"`
</table>
`cgi_hidden grp $grid`
EOF
}
crview4article() { # $1=rowid of blog, $2(optional)=extra SQL
  # Create TEMPORARY VIEW
  query<<EOF
CREATE TEMPORARY VIEW writeusers AS
 SELECT DISTINCT author FROM article
 WHERE id in (
  select id from article where blogid=(select id from blog where rowid=$1)
 );
CREATE TEMPORARY VIEW movablegroups AS
SELECT g.rowid growid , g.gname
       FROM (SELECT grp.rowid, grp.gname FROM grp JOIN grp_mem gm
             ON grp.gname=gm.gname		-- そのユーザが属している
                AND user='$user') g		-- グループに絞る
       WHERE (SELECT author FROM writeusers
              EXCEPT
              SELECT user FROM grp_mem gm WHERE gm.gname = g.gname)
             IS NULL;
$2
EOF
}
sql4readableblogs() {
  # Create view of blogs that can be readable to $user
  # Blog is readable when:
  #  1: blog owner is an user
  #  2: else, 2.1: owner-group where the $user belongs
  #           2.2: else, owner-group is not moderated
  # blog(id, author), blog_s(id, key='owner', val= ->owner)
  cat<<EOF ## | tee tmp/sql.out
CREATE TEMPORARY VIEW readableblogs AS
 SELECT blog.rowid rid, id, author
 FROM blog
      NATURAL JOIN
      (SELECT id, val owner FROM blog_s WHERE key='owner') bs
 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL
	     THEN 1		-- blog owner is an user, READABLE
	    WHEN (SELECT val FROM grp_s
		  WHERE gname=bs.owner AND key='regmode') = 'moderated'
	      AND
		 (SELECT user FROM grp_mem
		  WHERE gname=bs.owner AND user='$user') IS NULL
	    THEN 0
	    ELSE 1
       END;
EOF
}
editheading() {	# $1=rowid-of-heading
  rowid=${1%%[!A-Z0-9a-z_]*}
  if [ -z "$rowid" ]; then
    echo "話題番号が未指定です。" | html p
    return
  fi
  owner=`getvalbyid blog owner $rowid`
  title=`getvalbyid blog title $rowid`
  GF_ACTION="?blog" edittable $formdir/blog.def blog $rowid \
      | _m4 -D_TITLE_="修正" \
	    -D_SUBTITLE_="[$title]@$owner" -D_DIARY_="" \
	    -D_BLOGS_="" -D_DUMPTABLE_="" \
	    -D_FORM_="syscmd(\`cat')" \
	    $layout/html.m4.html $layout/form+dump.m4.html
  # Move to group
  if isuser "$owner"; then
    crview4article $rowid
    n=`query "SELECT count(*) FROM writeusers;"`
    ## err N=$n
    if [ $((n)) -gt 0 ]; then
      ## err ROWID=$rowid
      sql="SELECT growid || ':' || gname FROM movablegroups;"
      cat<<-EOF
	<div class="fold">
	`cgi_checkbox mv send id="mv"`<label
	 for="mv">この話題をグループ所有に移動する</label>
	<div>
	<form action="?mvart" method="POST" enctype="multipart/form-data">
	移動先グループ:
	<select name="mv2grp">
	EOF
      query ".mode html"
      query<<-EOF |
	$sql
	.mode list
	EOF
      sed -e '/<\/TR>/d' -e 's,<TR>,,' -e 's,TD>,option>,g' \
	  -e 's,n>\([0-9]*\):\(.*\)<,n value="\1">\2<,'
      cat<<-EOF
	</select>
	<p>(移動できるグループは、この「話題」に書き込んでいる人全てが
	そのグループに加入しているものに限られます)</p>
	<p>`cgi_checkbox cfm yes`<label>確認
	(この操作は元に戻すことができません)</label></p>
	`cgi_hidden blogrowid $rowid`
	`cgi_submit 移動`
	`cgi_reset Reset`
	</form>
	</div>
	</div>
	EOF
    fi
    # end of isuser "$owner"
  elif { hexteams=$(hexteams "$owner" )	# blog is of GROUP 
	 [ -n "$hexteams" ];}; then
    none="`echo なし|hexize`"
    cat<<-EOF
	<div class="fold">
	`cgi_checkbox mv2team send id="mv2team"`<label
	 for="mv2team">この話題を以下のチームのものにする</label>
	<div><p>現在の所属チーム設定:
	`query "SELECT
           coalesce((SELECT val FROM blog_s
            WHERE id=(SELECT id FROM blog WHERE rowid=$rowid)
            AND key='team'),
	   ':なし');"`</p>
	<form action="?mvart" method="POST" enctype="multipart/form-data">
	移動先チーム: `cgi_select_h mv2team $none $hexteams`
	<p>`cgi_checkbox cfm yes`<label>確認</label></p>
	`cgi_hidden blogrowid $rowid`<br>
	`cgi_submit 移動`
	`cgi_reset Reset`
	</form></div></div>
	EOF
  fi
}
mvart() {	# move diary to some group or team
		# or move blog of group to team which belong to the group
  blogrowid=`getpar blogrowid`
  cfm=`getpar cfm`
  ##### echo move blog:$blogrowid to $mv2grp | html p
  blogrowid=${blogrowid%%[!A-Z0-9a-z_]*}	# Purify
  . ./s4-blog.sh
  if [ -z "$blogrowid" ]; then
    echo "無効な指定です(mvart)。" | html p
    return
  elif [ x"$cfm" != x"yes" ]; then
    echo "記事移動の確認にチェックがないので通常表示に戻ります。" | html p
  elif { mv2grp=`getpar mv2grp`
         mv2grp=${mv2grp%%[!A-Z0-9a-z_]*}		# Purify
	 [ -n "$mv2grp" ]; }; then
    crview4article $blogrowid
    ########## TRANSACTION BEGIN
    query "BEGIN;"
    n=`query "SELECT count(*) FROM writeusers;"`
    ## err Nwriteuser=$n
    if [ $((n)) -gt 0 ]; then
      query<<-EOF
	UPDATE blog_s SET val=(SELECT gname FROM grp WHERE rowid=$mv2grp)
	WHERE key='owner'
	      AND id=(SELECT id FROM blog WHERE rowid=$blogrowid)
	      AND $mv2grp IN (SELECT growid FROM movablegroups);
	EOF
    fi
    query "END;"
    ########## TRANSACTION END
  elif { mv2team=`getpar mv2team|sed "s/'/''/g"`
	 [ -n "$mv2team" ];}; then
    # blog owner can move it to ANY team
    case "$mv2team" in
      'なし')
	cat<<-EOF
	  DELETE FROM blog_s
	  WHERE  id=(SELECT id FROM blog WHERE rowid=$blogrowid)
		 AND key='team';
	EOF
	;;
      "") ;;
      *)cat<<-EOF
	  BEGIN;
	  REPLACE INTO blog_s(id, key, val)
	  VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),
		 'team', '$mv2team');
	  REPLACE INTO blog_s(id, key, val)
	  VALUES((SELECT id FROM blog WHERE rowid=$blogrowid),
		 'notify', 'all');	-- Change notify to all
	  END;
	EOF
    esac | query
  fi
  blog_reply $blogrowid
  echo yes | html p
}
editart() {	# $1=article-rowid $2=blogrowid
  rowid=${1%%[!A-Z0-9a-z_]*}
  blogrowid=${2%%[!A-Z0-9a-z_]*}
  if [ -z "$rowid" -o -z "$blogrowid" ]; then
    echo "表示する記事番号が未指定です。" | html p
    return
  fi
  owner=`getvalbyid blog owner $blogrowid`
  title=`getvalbyid blog title $blogrowid`
  author=`getvalbyid article author $rowid`
  ## err EDITart: owner=$owner, author=$author
  if isgrpowner $user $owner; then
    : EDIT OK
  elif [ x"$owner" != x"$user" -a x"$author" != x"$user" ]; then
    echo "本人か所有者しか編集できません." | html p
    return
  fi
  aid=`query "select id from article where rowid=$rowid;"`
  tmpout=$tmpd/editart.$$.out
  GF_ACTION="?replyblog+$blogrowid#$aid" \
	   edittable $formdir/article.def article $rowid \
	   > $tmpout
  rm -f /tmp/editart.out
  # Cannot use pipelining to m4 with genform() because of stdin stack
  _m4 -D_TITLE_="コメントの修正" -D_DIARY_="" \
      -D_FORM_="syscmd(cat $tmpout)" \
      -D_SUBTITLE_="`gecos $owner`の「$title」" \
      -D_BLOGS_= -D_DUMPTABLE_= \
      $layout/html.m4.html $layout/form+dump.m4.html
}
send2mem() {
  rowid=`getpar grp`
  if [ -z "$rowid" ]; then
    echo "グループが未指定です。" | html p
    return
  fi
  message=`getpar message`
  if [ -z "$message" ]; then
    echo "文章を入れてください。" | html p
    return
  fi
  grp=`getgroupbyid $rowid`
  members=`collectemail $grp`
  # smail rcpt subj (file)
  smail "$members" "グループ $grp 宛メッセージ(from `gecos $user`)" <<EOF
$urlbase?grp+$rowid
グループ $grp に所属する
`gecos $user` さんよりメッセージ:

$message
EOF
  cat<<EOF
<p>以下の宛先に送信しました。</p>
<pre>
$members
</pre>
<p><a href="?grp+$rowid">グループ $grp</a>に戻る。</p>
EOF
}
joingrpadmit() {
  # $1=yes/no $2=session-key
  if [ -z "$2" ]; then
    echo "bye bye" | html p; return
  fi
  t_usr=`session=$2 getpar user`
  t_grp=`session=$2 getpar group`
  ## err joingrpadmit: t_usr=$t_usr, t_grp=$t_grp
  _m4 -D_TITLE_="joingrp" $layout/html.m4.html
  if [ -z "$t_usr" -o -z "$t_grp" ]; then
    echo "無効な加入依頼です。" | html p
    echo "有効期限が切れたか、
他の管理者がいる場合は処理済みの可能性があります。" | html p
    return
  fi
  if ! isgrpowner "$user" $t_grp; then
    echo "グループ管理者のみの機能です。" | html p; return
  fi
  case $1 in
    yes)	joingrp "$t_grp" "$t_usr" yes ;;
    no)		joingrp "$t_grp" "$t_usr" no ;;
    *)
      echo "無効な指定です($1)。" | html p
      return ;;
  esac
  gid=$(query "select rowid from grp where gname=`sqlquote $t_grp`;")
  rcpts="`getgroupadminmails $t_grp` $user"
  ## err admit: msgdir=$msgdir, rcpts="["$rcpts"]"
  body="グループ <a href=\"?grp+$gid\">$t_grp</a>
に
$t_usr
`[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`
しました。"
  (echo "$body"; echo; echo "$url?grp+$gid") | smail "$rcpts" "joingrp $1"
  query "delete from session where id='$2';"
  echo "$body" | html p
}

joingrprequest() {
  # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin) 
  jss="joingrp-`date +%s`-`genrandom 12`"
  addsession $jss +${memoplimitdays}days
  query "replace into par values('$jss', 'group', 'string', `sqlquote $1`),
('$jss', 'user', 'string', `sqlquote $user`);"
  smail "$(collectemail `getgroupadmins $1`)" "Join request to $1"<<EOF
$url
$user さんから
グループ $1
に加入依頼がありました。

承認する:
$urlbase?joingrpadmit+yes+$jss

白紙に戻す:
$urlbase?joingrpadmit+no+$jss
EOF
  echo "管理者に加入依頼を出しました。
${memoplimitdays}日以内に加入承認操作がされれば加入できますが、
グループ運用方針に懸かることですので直接の問い合わせが重要です。" | html p
}
joingrp() {
  # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin)
  ## err joingrp: \$1=$1 \$2=$2 \$3=$3 \$4=$4
  if isgrpowner "$user" "$1"; then
    isowner="yes"
  elif [ -n "$5" ]; then
    isowner="yes"
  else
    isowner=""
  fi
  ## err jg:isgrpowner: isowner="$isowner"
  if [ -n "$isowner" ]; then
    : # GROUP OWNER CAN DO EVERYTHING ABOUT REGISTRATION/RETIREMENT
  elif [ x"$2" != x"$user" ]; then # if user is not login user
    echo "本人か、グループ管理者しか加入操作はできません。" | html p
    return
  elif [ x"$3" = x"no" ]; then
    : # Do not pursue those who leave
  elif [ x"$3" = x"yes" ] && ismember "$user" "$grp"; then
    : # Member can change own email address for the joining moderated group
  else				# adding user is $user itself
    case `getgroupattr $1 regmode` in
      moderated)
	joingrprequest "$@"	# Request only
	return
	;;
      *)
	;;
    esac
  fi
  qgname=`sqlquote $1`
  cond="where gname=$qgname and user='$2'"
  if [ x"$3" = x"yes" ]; then
    query "replace into grp_mem values($qgname, '$2');"
    if [ -n "$4" ]; then
      if msg=`emaildomaincheck "$4"`; then
	query "replace into grp_mem_s values($qgname, '$user', 'email', \
		'string', '$4', NULL);"
      else
	echo $msg
      fi
    else
      query "delete from grp_mem_s $cond and key='email';"
    fi
    if [ -n "$5" ]; then	# as ADMIN
      # Coming here means newly created group
      sql="select case\
      	when (select count(*) from grp_mem where gname=$qgname)=1\
	then (select user from grp_mem\
	where gname=$qgname and user='$user')\
	else '' end;	   "
      err NewGrpChk: $sql
      if [ -n "`query \"$sql\"`" ]; then
	## err ADMIN: "replace into grp_adm values($qgname, '$user');"
	query "replace into grp_adm values($qgname, '$user');"
      fi
    fi
  else
    query "delete from grp_mem $cond;
delete from grp_mem_s $cond;
delete from grp_mem_m $cond;"
  fi
}
grp_add_team() (
  # $1=grp-rowid $2=team $3...=user-rowid(s)
  grp=`getgroupbyid $1`
  team=$2; shift; shift
  [ -z "$grid" -o -z "$team" -o -z "$1" ] && return
  { echo "BEGIN;"
    for user; do
      echo "REPLACE INTO grp_mem_m(gname, user, key, type, val) VALUES(\
		'$grp',\
		(SELECT name FROM user WHERE rowid=$user),\
		'team', 'string', '$team');"
    done
    echo "END;"
  } | query
)
grp_rm_team() (
  # $1=grp-rowid $2=team $3...=user-rowid(s)
  grid=$1
  qgrp=$(sqlquote "`getgroupbyid $grid`")
  team=$2; shift; shift
  [ -z "$grid" -o -z "$team" ] && return
  { echo "BEGIN;"
    for user; do
      echo "DELETE FROM grp_mem_m\
	    WHERE gname=$qgrp \
	    AND user=(SELECT name FROM user WHERE rowid=$user)\
	    AND key='team' AND val='$team';"
    done
    cat<<-EOF
	DELETE FROM blog_s
	WHERE rowid=(
          SELECT rowid
	  FROM blog_s a
	  WHERE key='team'
	    AND id IN (SELECT id FROM blog_s WHERE key='owner' AND val=$qgrp)
	    AND NOT EXISTS (SELECT * FROM grp_mem_m
	                    WHERE key='team' AND val=a.val -- a.val=team
	                      AND gname = (SELECT val FROM blog_s b
	                                   WHERE a.id=b.id AND key='owner')
	                    ));
	EOF

    echo "END;"
  } | query
)
grp_reg_adm() {
  # $1=grp-rowid $2...=user-rowid
  grid=$1
  grp=`getgroupbyid "$1"`
  if [ -z "$grp" ]; then
    echo "無効なグループIDです" | html p; return
  fi
  if ! isgrpowner $user "$grp"; then
    echo "$grp グループの管理者しかこの操作はできません。" | html p; return
  fi
  shift
  for urid; do
    newadm=`query "select name from user where rowid=$urid;"`
    if [ -z "$newadm" ]; then
      echo "指定ユーザIDがおかしいようです。" | html p; return
    fi
    err GRP_reg_adm: "replace into grp_adm values(`sqlquote $grp`, '$newadm');"
    err ismember $newadm $grp
    if ismember $newadm $grp; then
      # OK, go ahead
      getgname="(select gname from grp where rowid=$grid)"
      query "replace into grp_adm values($getgname, '$newadm');"
      # confirm insertion
      sql="select * from grp_adm where gname=$getgname and user='$newadm'"
      if [ -n "`query \"$sql;\"`" ]; then
	echo "追加完了: $newadm" | html p
      else
	echo "追加失敗($1 $urid)" | html p
      fi
    fi
    showgroup $grid
  done
}
dumptable() {
  # $1=mode $2=Table $3=column-list-of-*_s(defaults to *) $4=conditions(if any)
  # textのフィールドだけ全てダンプにしたほうがいいか
  # $DT_VIEW sets link
  # 6/17の次: editリンクじゃなくてスレッドVIEWリンクでいいんちゃう?
  ### elink="<a href=\"$myname?edittable+$2+\\2\">EDIT</a>"
  VIEW=${DT_VIEW-replyblog}
  if [ -n "$VIEW" ]; then
    dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"
  fi
  # $DT_CHLD=ChildTable:BindColumn
  if [ -n "$DT_CHLD" ]; then
    _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}
    cntall="(select count($_i) from $_t where $_i=a.id)"
    # XXX: Dirty workaround for slow subquery of acclog
    presql="CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS
            SELECT * FROM acclog WHERE user='$user' and tbl='$2';"
    cntnew="(select count(val) from ${_t}_s where key='ctime' \
        and id in (select id from $_t where $_i=a.id) \
        and val > coalesce((select time from myacclog where \
                            tblrowid=a.rowid),\
                    '1970-01-01'))"
    cnt="$cntnew as '新着', $cntall as '総数',"
    dt_class=" td2r td3r dumpblogs"
  fi
  # Construct join expression
  eav="" scols=""
  pk=`gettblpkey $2`
  substr=${dumpcollen:+"substr(val, 0, $dumpcollen)"}
  substr=${substr:-val}
  for col in ${3:-`gettbl_s_cols $2`}; do
    case $col in
      gecos)	scols="$scols${scols:+, }${col#}"
		continue ;;	# built-in column name
      *:*)	col=${col%:*} as=${col#*:} ;;
      *)	as=${col} ;;
    esac
    eav=$eav${eav:+,}" max(case key when '$col' then $substr end) as $as"
    scols="$scols${scols:+, }b.$as"
  done
#case author when '$user' then a.rowid else '---' end as ID,
  sql=${DT_SQL:-"select \
a.rowid as LINK,\
$cnt\
$scols from $2 a left join\
 (select $pk,$eav,
	max(case key when 'owner'
	 then (SELECT gecos FROM gecoses WHERE name=val) END) as gecos
 from ${2}_s c group by $pk) b on a.$pk=b.$pk $4;"}
  ## err dt:SQL="`echo \"$presql$sql\"|tr -d '\n'`"
  cat<<EOF | sed "s,\(<TR><TD>\)\([1-9][0-9]*\)\(#[0-9a-fxs]*\)*</TD>,\1$elink$dvlink</TD>,"
<div> <!-- for folding by check button (s4-funcs.sh:dumptable()) -->
<div class="dumptable">
<table class="b$dt_class">
`sq -header -cmd ".mode $1" $db "$presql$sql"`
</table>
</div> <!-- dumptable -->
</div> <!-- for folding by check button (s4-funcs.sh:dumptable()) -->
EOF
}

par2table() (
  # copy current parameters of par into destination table
  # $1=definition-file
  # Using $user and $session
  # Return value:
  #	0: Stored successfully
  #	1: Insufficient fillings
  #	2: No permission to modify the record
  #	3: Invalid rowid
  #	4: SUCCESS to delete
  #	5: Stop deletion for lack of confirm check
  #	6: Password length too short
  #	7: Password mismatch
  #	8: Old password incorrect
  rowid=`getpar rowid`
  if [ ! -e $1 ]; then
    echo "テーブル定義ファイルが見付かりません" | html p
    exit 1
  fi
  tbl=${1%.def}
  tbl=${tbl##*/}
  if [ -n "$rowid" ]; then	# Modify existing entry
    if [ x"$tbl" = x"user" ]; then
      rowowner=`query "select name from $tbl where rowid=$rowid;"`
    elif [ x"$tbl" = x"grp" ]; then
      sql="select gname from $tbl where rowid=$rowid;"
      ##err p2t:grp:q $sql
      isgrpowner $user "`query $sql`" && rowowner=$user
    else
      rowowner=`getvalbyid $tbl owner "$rowid"`
      rowowner=${rowowner:-`query "select author from $tbl
	 where rowid=$rowid;"`}
    fi
    ### err rowowner=$rowowner
    if [ x"$user" != x"$rowowner" ]; then
      echo "他人のレコードはいじれないの" | html p
      return 2
    elif [ -z "$rowowner" ]; then
      echo "指定したレコードはないみたい" | html p
      return 3
    fi
    rm=`getpar rm` cfm=`getpar confirm`
    # Editing existent entry 
    if [ x"$rm" = x"yes" ]; then
      if [ x"$rm$cfm" = x"yesyes" ]; then
	query "delete from $tbl where rowid=$rowid;"
	return 4
      else
	echo "消去確認のチェックがないので消さなかったの..." | html p
	return 5
      fi
    fi
  fi

  ts=${tbl}_s tm=${tbl}_m val="" pval="" formaster=""
  if [ -n "$rowid" ]; then
    # Update of existing record
    for col in `gettblcols $tbl`; do
      val=`getparquote $col`
      [ -z "$val" ] && continue
      ## err  query "update $tbl set $col=$val where rowid=$rowid"
      ## XX: THIS IS DIRTY hack to ensure non-foreign key in blog_s
      sql="update $tbl set $col=$val where rowid=$rowid;"
      if [ x"$tbl" = x"grp" -a x"$col" = x"gname" \
	    -o x"tbl" = x"user" -a x"$col" = x"name" ]; then
	## User name cannot be changed with interface provided with this
	## script.  But we offer the trigger to change owner user
	## of blog_s table.
	#err "select quote($col) from $tbl where rowid=$rowid;"
	old=`query "select quote($col) from $tbl where rowid=$rowid;"`
	cat<<-EOF | query
		-- Here we cannot use BEGIN-COMMIT because groupupdate()
		-- should use EXCLUSIVE transaction outside of this.
		SAVEPOINT par2table;
		$sql
		update blog_s set val=$val
		where key='owner' and val=$old;
		RELEASE SAVEPOINT par2table;
		EOF
	## XX: DIRTY Hack Ends here
	## We should keep blog's owner as a single column which has
	## foreign key constraint with primary key of grp/user.
       else
	 query "$sql"
       fi
     done
     # Then, set up $pval for further insertion of tbl_s and tbl_m
     for col in `gettblpkey $tbl`; do
       val=`query "select $col from $tbl where rowid=$rowid;"|sed -e 's/\"/\"\"/g'`
       pval="$pval${pval:+, }\"$val\""
     done
  else
    # New entry
    # Generate values() for primary keys
    for col in `gettblpkey $tbl`; do
      # Genuine primary keys for _m and _s
      val=`getvalquote $tbl $col`
      [ -z "$val" ] && continue
      pval="$pval${pval:+, }$val"
    done
##err pval=$pval
    for col in `gettblfkey $tbl`; do
      # args for values() to insertion into master table
      val=`getvalquote $tbl $col`
      [ -z "$val" ] && continue
      formaster=$formaster"${formaster:+, }$val"
    done
    formaster="$pval${formaster:+, }$formaster"
## err formaster=$formaster
    if [ -z "$formaster" ]; then
      echo "項目を全て埋めてください" | html pre
      return 1
    fi
    ## err "replace into $tbl values($formaster);"
    query "replace into $tbl values($formaster);"
    ## Insertion to master table, done
  fi
     
  for kt in s m; do
    tb2=${tbl}_$kt
    for col in `gettbl_${kt}_cols $tbl`; do
      ptype=`getpartype $col "limit 1"`

      # First, check update of existing entries in _m
      if [ $kt = m ]; then
	# sessID|address.1.22|string|Somewhere-x.y.z
	sql=""
##err dots from query "select var from par where var like '$col.%';"
        for v in `query "select var from par where var like '$col.%';"`; do
	  # v=address.1.22
	  st_rowid=${v##*.}
	  origcol=${v%%.*}	# original column derived from
##err Updating for $v st_rowid=$st_rowid, partype=`getpartype $v`
	  ##case `getpartype $v` in
	  ## err CASE `gettbl_coltype $tbl/$origcol` in
	  ## err edit flag = `getpar action.$v`
	  case `getpar action.$v` in
	    rm)
	      if [ x`getpar confirm.$v` = x"yes" ]; then
		newsql="delete from $tb2"
	      else
		echo "削除確認未チェック" | html p
	      fi ;;
	    edit)
	      case `gettbl_coltype $tbl/$origcol` in
		image|document|binary)
		  file=$tmpd/`getparfilename $v`
		  ## err type=file=$file
		  [ -z "$file" ] && continue
		  bn=`sqlquotestr "${file##*/}"`
		  bin="X'"$(hexize "$file")"'"
		  ct=`file --mime-type - < "$file" |cut -d' ' -f2`
		  type=\"file:$ct\"
		  newsql="update $tb2 set val=$bn, type=$type, bin=$bin"
		  cachedir=`getcachedir "$tbl/$rowid"`
		  err getcache tbl/rowid=$tbl/$rowid, rm -r $cachedir
		  rm -rf $cachedir
		  ;;
		*)
		  newsql="update $tb2 set val=(select val from par where var \
like '$col.%.$st_rowid')"
		  ;;
	      esac
	      ;;
	    *)			# maybe "keep", do not modify value
	      continue
	      ;;
	  esac
	  # err newsql=$newsql
	  sql=$sql$nl"$newsql where rowid=$st_rowid;"
	done

	if [ x"$bin" = x"NULL" ]; then
	  ## err repl:normal sql=`echo $sql`
	  query "$sql
delete from $tb2 where type='string' and val='';"
	  ## err repl:normal done
	else
	  sqlfile="$tmpd/sqlf.$$"
	  echo "$sql" > $sqlfile
	  ## err repl:sqlfile=`ls -lF $sqlfile`
	  query ".read $sqlfile"
	  ## err repl:done
	fi
	# Rest of kt==m: set multiple mode
	nr=`getparcount $col`
      else
        nr=1			# for kt==s, number of records is 1
      fi

      i=0
      while [ $i -lt $nr ]; do
	limit="limit 1 offset $i"
	i=$((i+1))		# increase beforehand against continue
	val=`getvalquote $tbl $col "$limit"`
	[ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue
	## err $col=$val
	bin=NULL
	## err partype$col=`getpartype $col "$limit"`
        case $ptype in
	  file) file=$tmpd/`getparfilename $col "$limit"`
		## err parfile-$col=$file
	        [ -z "$file" ] && continue
	        bin="X'"$(hexize "$file")"'"
	        ct=`file --mime-type - < "$file"|cut -d' ' -f2`
	        type=\"file:$ct\" ;;
	  "*"*) continue ;;	# foreign table
	  *)    type=\"string\" ;;
	esac
	case `gettbl_coltype $tbl/$col` in
	  password)		# special care for password
	    # name={password,pswd1,pswd2}
	    p1=`getpar pswd1 "$limit"`
	    if [ -z "$p1" ]; then
	      continue		# SKIP password setting, if p1 is empty 
	    else
	      pswd=`getpar pswd "$limit"` p2=`getpar pswd2 "$limit"`
##	       err pswd=$pswd
	      if pwcheck "$pswd"; then
		if [ x"$p1" = x"$p2" ]; then
		  case "$p1" in
		    ??????????*) ;;
		    *) echo "パスワードは10字以上にしてください。" | html p
		       return 6;;
		  esac
		  val="\"`echo $p1|mypwhash`\""
		else
		  echo "2つの新パスワード不一致" | html p
		  return 7
		fi
	      else
		echo "旧パスワード違います" | html p
		return 8
	      fi
	    fi
	    ;;
	esac
	## err p2t: "replace into $tb2 values($pval, \"$col\", $type, $val, bin...);"
	#query "replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"
	sql="replace into $tb2 values($pval, \"$col\", $type, $val, $bin);"
	if [ x"$bin" = x"NULL" ]; then
	  ## err Normal-query: `echo $sql`
	  query "$sql"
	else
	  sqlfile="$tmpd/query.$$"
	  echo "$sql" > $sqlfile
	  ## err sqlfile=`ls -lF $sqlfile`
	  query ".read $sqlfile"
	fi
	## err p2t done
      done
    done
  done
  return 0
  ##err donee
)
genform() {
  # $1 = form definition file
  # $2, $3 (optional)= table name and ROWID
  # If $GF_VIEWONLY set and nonNull, output values without form
  # If $GF_ARGS set, use it as content-strings in the form
  # If $GF_OWNER set, use it as value of name="owner"
  # If $GF_STAGE set, use it as value of name="stage"
  forms="" hiddens="" rowid=$3
  if [ ! -e "$1" ]; then
    echo "そのようなデータベースはないようです($2)。" | html p
    return
  elif [ -n "$2" ]; then
    rec=`query "select * from $2 where rowid='$rowid';"`
    if [ -z "$rec" ]; then
      pk=`gettblpkey $2`
      ###rec=`sq $db "select rowid from $2 where $pk='$rowid'"`
      rec=`query "select rowid from $2 where $pk='$rowid';"`
      rowid=$rec
      rec=$3
    fi
    if [ -z "$rec" ]; then
      echo "そんなレコードはないみたいね..." | html p
      return
    fi
  fi
  if [ -z "$GF_VIEWONLY" ]; then
    rm='<input id="rm" name="rm" type="checkbox"
 value="yes"><label for="rm">このエントリの削除</label>
<span>ほんとうに消しますよ(確認)!
<input name="confirm" type=checkbox value="yes">はい</span>'
  fi
  # Image Cache dir
  ## err genform: getcache=$2/$rowid
  td=`getcachedir "$2/$rowid"`
  while IFS=: read prompt name keytype type args; do
    [ -z "${prompt%%\#*}" ] && continue # skip comment line(#)
    sp="${args:+ }"
    form="" val=""
    if [ -n "$rowid" ]; then
      # err genform2a: Seeking for "$2.$name, type=$type"
      rawval=`getvalbyid $2 $name $rowid $td`
      val=`echo "$rawval"|htmlescape`
## err genform3a: getvalbyid $2 $name $rowid $td
## err genform3b: val="[$val]" type="$type"
    fi
    if [ -n "$GF_VIEWONLY" ]; then
      is_hidden "$2" "$name" && continue
    fi
    case "$type" in
      text*)
	cgiform=cgi_multi_$type
	if [ -s $td/$name.count -a -n "$val" ]; then
	  form=`$cgiform $name $td`
	  val=$(echo "$val"|
		     while read fn; do
		       echo "<tr><td>`cat $td/$fn|htmlescape|hreflink`
			</td></tr>$nl"
		     done)
	  val="<table>$nl$val$nl</table>"
	else
	  #form="<input name=\"$name\" value=\"$val\" type=\"$type\"$sp$args>$nl"
	  form=`cgi_$type $name "$rawval" "$args"`
	fi
	;;
      [Rr][Aa][Dd][Ii][Oo])
	fh="<label><input type=\"radio\" name=\"$name\""
	form="`echo $args|sed -e \
\"s,\([^ =][^=]*\)=\([^= ][^= ]*\),$fh value=\\"\2\\">\1</label>,g\"`"
	;;
      [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx])
	form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\">${args%=*}</label>"
	;;
      [Ss][Ee][Ll][Ee][Cc][Tt])
	fh="<select name=\"$name\">$nl"
	form=$(for l in $args; do
		 echo "<option value=\"${l#*=}\">${l%=*}</option>" 
	       done)
	if [ -n "$val" ]; then
	  form=`echo $form|sed -e "s,\(value=.$val.\),\\1 selected,"`
	fi
	form="$fh$form</select>"
	;;
      [Ii][Mm][Aa][Gg][Ee]|[Dd][Oo][Cc][Uu][Mm][Ee][Nn][Tt]|[Bb]inary)
	if [ -s $td/$name.count ]; then
	  form=`cgi_multi_file $name $td "$args"`
	  if [ -n "$val" ]; then
	    hrfb="$myname?showattc+$2_m"
	    val=$(echo "$rawval" \
		       | while read fn; do
			   data=`percenthex "$td/$fn"`
			   #ct=`cat $td/$fn.content-type`
			   ct=`file --mime-type - < "$td/$fn"|cut -d' ' -f2`
			   ri=`cat "$td/$fn.rowid"`
## err fn=$fn, name=$name, ri=$ri; ls -lF "$td/" 1>&3
			   #imgsrc="<img src=\"data:$ct,$data\">"
			   #echo "<a href=\"$hrfb+$ri\">$imgsrc</a><br>"
			   iconhref "$td/$fn" "$hrfb+$ri" ""
			 done)
	  fi
	else
	  form="<input type=\"file\" name=\"$name\" $args>"
	  if [ -n "$val" ]; then
	    imgs=$(echo "$rawval"\
			|while read fn;do
			   data=`percenthex "$td/$fn"`
			   echo "<img src=\"data:image/png,$data\">$fn<br>"
			 done)
	    form=$form"<br>$imgs"
	    val=$imgs	# 2015-06-15
	  else
	    form="<input type=\"file\" name=\"$name\" $args>"
	  fi
	fi
	;;
      [Hh][Ii][Dd][Dd][Ee][Nn])
	if [ -n "$GF_STAGE" -a x"$name" = x"stage" ]; then
	  args="value=\"$GF_STAGE\""
	fi
	form="<input type=\"hidden\" name=\"$name\" $args>"
	prompt=''		# Remove prompt
	;;
      [Aa][Uu][Tt][Hh][Oo][Rr])
	[ -n "$GF_VIEWONLY" ] && continue
	form="<input type=\"hidden\" name=\"author\" value=\"$user\">"
	prompt="" ;;
      [Oo][Ww][Nn][Ee][Rr])
	[ -n "$GF_VIEWONLY" ] && continue
	val=${GF_OWNER:-$val}
	val=${val:-$user}
	form="<input type=\"hidden\" name=\"owner\" value=\"$val\">"
	prompt="" ;;
      [Uu][Ss][Ee][Rr])
	# XXX: is null $user ok?
	#form="<input type=\"hidden\" name=\"user\" value=\"$user\">"
	[ -n "$GF_VIEWONLY" ] && continue
	form="$user"
	;;
      [Pp]assword)
	[ -n "$GF_VIEWONLY" ] && continue
	form="`cgi_passwd`"
	val=""
	;;
      [Ss][Ee][Rr][Ii][Aa][Ll]|[Ss][Tt][Aa][Mm][Pp])
	[ -n "$GF_VIEWONLY" ] && continue
	if [ -z "$rowid" ]; then
	  val=`genserial`
	fi
	form="<input type=\"hidden\" name=\"$name\" value=\"$val\">"
	prompt="" ;;
      [Ss][Ee][Ss][Ss][Ii][Oo][Nn])
	prompt=""
	;;
      parent|path|blog*)
	prompt=""
	;;
      "*"*)
	tail=$tail"``"
	continue ;;
    esac
    if [ -n "$prompt" ]; then
      if [ -n "${GF_VIEWONLY}" ]; then
	form=$val
      else
	:
      fi
      forms=$forms"  <tr class=\"$name\"><th>$prompt</th><td>$form</td></tr>$nl"
    else
      hiddens=$hiddens$nl"$form"
    fi
  done < $1
  # enctype="multipart/form-data"
  cat<<EOF
<form action="${GF_ACTION:-$myname}" method="POST" enctype="multipart/form-data">
${rowid:+$rm}
 <table class="b $2">
$forms
 </table>$hiddens
 ${GF_STAGE:+`cgi_hidden stage $GF_STAGE`}
 ${rowid:+<input type="hidden" name="rowid" value="$rowid">}
EOF
  if [ -z $GF_VIEWONLY ]; then
    cat<<EOF
 <input type="submit" name="sub" value="OK">
 <input type="reset" name="res" value="Reset">
EOF
  fi
  cat<<EOF
$GF_ARGS</form>
$tail
EOF
}
edittable() {
  # $1=form-def $2=table $3 rowid
  genform "$@"
}
viewtable() {
  GF_VIEWONLY=1 genform "$@"
}
showattc() {
  # $1=table_m $2=rowid &optional $3=RawFlag
  ## err \$1=$1 \$2=$2 \$3=$3
  if ! isfilereadable $user $1 $2; then
    contenttype; echo
    echo "このファイルは管理者のみしか見られません" | html p
    putfooter; exit
  fi
  idir=`umask 002; mktempd` || exit 1
  # tmpfiles=$tmpfiles"${tmpfiles+ }$idir"
  bin=$idir/$myname-$$.bin
  sql="select quote(bin) from $1 where rowid='$2';"
  ## err showattc: sql: $sql
  sq $db "$sql" | unhexize > $bin
  tv=`query "select type||'//'||val from $1 where rowid='$2';"`
  type=${tv%//*} fn=${tv#*//}
  ## err tv=$tv type=$type fn=$fn, tp2=${tv%\|*}
  ct=${type#file:}
  case $ct in			# all text/* changed to text/plain
    text/*)
      charset=`nkf -g $bin|cut -d' ' -f1`
      case $charset in
	ASCII*)	charset=""	;;
      esac
      if [ -z "$3" ]; then
	ct="text/html${charset:+; charset=$charset}"
	link="?showattc+$1+$2+raw"
	cat $bin | htmlescape \
	    | sed 's,^,<span></span>,' \
	    | _m4 -D_TITLE_="$fn" -D_CONTENT_TYPE_="$ct" \
		  -D_LINK_="$link" \
		  -D_BODY_="syscmd(\`cat')" $layout/pretty.m4.txt
	exit $?
      fi
      ct="text/plain${charset:+; charset=$charset}"
      ;;
  esac
  contenttype "$ct"
  echo "Content-Disposition: filename=\"$fn\""
  echo "Content-Length: " `cat $bin | wc -c`; echo
  #echo "Content-Type: " ${type#file:}; echo
  cat $bin
}
#
# Some default stupid handler on CGI values
#
default_storedb() {
  # ARG: $1=table-def-file
  # RET: $tbl=table-name, $col=mail-column, $cols=columns
  tbl=`basename $1`
  tbl=${tbl%.def}
  cols="`grep :text $1|cut -d: -f2`"
  col=`echo "$cols"|head -1`
  vcol=`getpar $col`
  err default0: \$1=$1 col=$col cols="[$cols]" vcol=$vcol
  if [ -n "$vcol" ]; then
    par2table $1
  else
    return 2			# No insertion occurred
  fi
}

default_view() {	# $1=def-file
  ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \
  ## DT_VIEW="edittable+$tbl" dumptable html $tbl "name memo file" \
  default_storedb "$@"
  query "select rowid from $tbl order by rowid desc;" \
      | while read rowid; do
	  viewtable $1 $tbl $rowid
	done | _m4 -D_TITLE_="$tbl" \
		   -D_FORM_="`genform $1`" \
		   -D_DUMPTABLE_="syscmd(cat)" \
		   $layout/html.m4.html $layout/form+dump.m4.html
}
default_viewtext() {	# $1=def-file
  ### DT_VIEW="edittable+$tbl" dumptable html $tbl "$cols" \
  default_storedb "$@"
  DT_VIEW="viewtable+$tbl" dumptable html $tbl "name memo file" \
      | _m4 -D_TITLE_="$tbl" \
	    -D_FORM_="`genform $1`" \
	    -D_DUMPTABLE_="syscmd(cat)" \
	    $layout/html.m4.html $layout/form+dump.m4.html
}
default_smail() {
  default_storedb "$@"
  if [ $? -eq 2 ]; then
    _m4 -D_TITLE_="入力" \
	-D_FORM_="`genform $1`" \
	-D_DUMPTABLE_="" \
	$layout/html.m4.html $layout/form+dump.m4.html
    return
  fi
  cond=""
  for pk in `gettblpkey $tbl`; do
    pv=$(sqlquote $(getpar $pk))
    cond="$cond${cond:+ and }$pk=$pv"
  done
  sql="select rowid from $tbl where $cond;"
  rowid=`query "$sql"`
  ## err smail1 - "$sql" "-> rowid=$rowid"
  
  while IFS=: read prompt name keytype type args; do # Read from $1
    val=`getpar $name`
    if [ -n "$val" ]; then
      text="$text
$prompt
$name=$val
---------------------------------------------------------"
    fi
    case "$type" in
      image|document|file)
	fn="`getvalbyid $tbl $name $rowid $tmpd`"
	fns=$(echo "$fn"|while read fn; do
			   err mv $tmpd/$fn.orig $tmpd/$fn
			   mv $tmpd/$fn.orig $tmpd/$fn
			   rm $tmpd/$fn.rowid # Remove cache flag
			   ## err "`ls $tmpd/$fn`"
			   echo $fn
			 done)
	files="$files $fns"
	;;
    esac
  done < $1
  ## err FILES=$files "`ls -lF $tmpd`"
  subj="from ${REMOTE_ADDR}"
  (echo "$url"
   echo "への書き込みがありました。"
   echo "------"
   echo "$text"
  ) | (cd $tmpd &&
	    err LS="`ls -lF`" &&
	    $mydir/sendmultipart.sh -t "$admin" -s "$subj" $files)
  _m4 -D_TITLE_="入力完了" $layout/html.m4.html
  echo "以下の内容で送信しました。" | html p
  viewtable $1 $tbl \
	    `query "select rowid from $tbl order by rowid desc limit 1;"`
  echo "戻る" | html a "href=\"?\""
}