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

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

test -n "$HTTP_HOST" && isCGI=true || isCGI=false
if $isCGI; then
  case "$SCRIPT_NAME" in
    *-world-*)
      S4WORLD=${SCRIPT_NAME#*world-}
      S4WORLD=${S4WORLD%.*}
      echo S4WORLD=$S4WORLD >&2
      worldconf=s4-config-${S4WORLD}.sh
      ;;
    *)
      worldconf=s4-config.sh
      ;;
  esac
  echo worldconf=$worldconf >&2
  [ -n "$worldconf" -a -e "$worldconf" ] && . ./$worldconf
  echo DB=$DB >&2
fi
myname=`basename ${SCRIPT_NAME:-$0}`
mydir=`dirname ${SCRIPT_FILENAME:-$0}`
cgiext=${CGIEXT:-.cgi}
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}
logdir=${LOGDIR:-tmp}
tmpfiles=""
querylog=${QUERYLOG:-$logdir/query.log}
searchlog=${SEARCHLOG:-$logdir/search.log}
defaultdb=$dbdir/cgi.sq3
db=${S4INITDB:-${DB:-$defaultdb}}
sessdb=${SESSDB:-$dbdir/sess.sq3}
userupdateflag=$dbdir/userupdate
sesstb=tmp.sess
workdb=$dbdir/tmpdata.sq3
listentlimit=${LISTENTLIMIT:-30}
listartlimit=${LISTARTLIMIT:-50}
admin=${ADMIN:-hostmaster@example.org}
noreply=${NOREPLY:-noreply@example.org}
noreply_from="${S4NAME:-s4} message notification <$noreply>"
invite_policy=${INVITE_POLICY:-"このコミュニティに関りのあるあなたの信頼できる人を招きます。"}
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
filesize_max=${FILESIZE_MAX:-$((5*1024*1024))}
filesize_max_MB="$((filesize_max/1024/1024))MB"
file_accept='accept="image/*,text/*,audio/*,application/vnd.oasis.*,application/pdf,application/x-*,application/sqlite*,application/csv"'
file_accept='accept=".jpg,.jpeg,.gif,.png,.tiff,.pdf,.odt,.ods,.odp,.odg,.mp3,.mp4,.m4a,.m4v,.mkv,.obj,.avi,.ogg,.mov,.webm,.gpx,.json,.geojson,.umap,.kml,.kmz,.html,.css,.js,.java,.el,.go,.cc,.rb,.rs,.py,.pl,lua,.awk,.sh,.c,.h,.log,.txt,.tex,.sty,.zip,.xcf,.bz2,.gz,.xz,.7z,.csv,.dat,.db,.sq3,.blend,.gltf,.glb"'
file_accept_egrep='^(text/|message/|image/|audio/|video/|application/(vnd.(oasis|sqlite)|pdf|epub|xml|.*zip|[xz]-|json|csv|javascript|octet-stream)|model/)'
file_accept_help="
添付可能ファイル: テキスト、画像、音声、動画、ODF、PDF、
圧縮ファイル、データベースファイル
(いずれも ${filesize_max_MB} 以内)
"
file_warn="$file_accept_help
[編集]リンクから修正してください。"
hidden_mode="('quiz', 'enquete')"
blogreadflagrowid=0
blogcutoffflagrowid=-1
nonewgroupworld=${NONEWGROUPWORLD:-*archive*}
whatsnewdays=${WHATS_NEW_DAYS:-14}
main_session=`date +%F-$$`
session=$main_session
mathjax=${MATHJAX:-'<script>MathJax = {
  tex: {tags: "ams",
        macros: {warn: ["\\fcolorbox{red}{white}{#1}", 1],
		 Warn: ["{\\Large \\warn{#1}}", 1]}}};</script>
<script src="https://polyfill.io/v3/polyfill.min.js?features=es6"></script><script id="MathJax-script" async src="https://cdn.jsdelivr.net/npm/mathjax@3/es5/tex-mml-chtml.js"></script>'}

tconfs=""
imgcached=cache/${S4WORLD:+$S4WORLD/}img.`date +%Y/%m`
conftbl=_tblconf
nl="
"
likeesc=`printf '\037'`		# ESCAPE char of LIKE operator
iconcachekey="profimgcache_S"
asdelim=":::"			# delimiter of dumptable td-class specifier
sqlite3 --help 2>&1 | grep -q -- '-json' && usejson=true || usejson=false

# Start debug logging
logtag="($$)${S4WORLD:+{$S4WORLD\}}"
exec 3>> $logdir/debug.out
err() {
  # echo "[`date +%F-%T%z`]$logtag $@" 1>&3
  # Avoid backslash escape sequences
  cat<<EOF 1>&3
[`date +%F-%T%z`]$logtag $@
EOF
}
case "$HTTP_USER_AGENT" in
  *i[Pp]hone*|*[Aa]ndroid*)	touchpanel=1 ;;
  *)				touchpanel="" ;;
esac

# If S4MASTERDB is set, behave in another world
### if [ -n "$S4MASTERDB" -a -s "$S4MASTERDB" ]; then
# If S4WORLDLIST is set, this s4 have world!
if [ -n "$S4WORLDLIST" ]; then
  . ./s4-world.sh 2>> $logdir/debug.out
  # Variables set in s4-world.sh
  #	$S4WORLDS, $S4WROLDNAME, $S4WORLDGRPS
  # Files created in s4-world.sh
  #	$worldlistfile, $worldoptionfile, $worldnamefile, $worldgrpfile
fi


[ -f ./s4-cgi.sh ] && . ./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

logstart() {
  echo "`date '+%F %T'`:[${user:-NULL}]$logtag <<<" >> ${1:-$querylog}
}
logend() {
  echo ">>>$logtag" >> ${1:-$querylog}
}
sqlog() {
  logstart
  if [ -z "$1" ]; then
    cat >> $querylog
  else
    echo "$*" >> $querylog
  fi
  logend
}
sq() {
  # ./args.rb -cmd ".timeout 3000" "$@"
  logstart
  if [ -z "$1" ]; then
    tee -a $querylog|sqlite3 -cmd 'PRAGMA foreign_keys=ON' -cmd ".timeout 3000"
  else
    echo "$@" >> $querylog
    sqlite3 -cmd 'PRAGMA foreign_keys=ON' -cmd ".timeout 3000" "$@"
    ###sqlite3 -bail -cmd 'PRAGMA foreign_keys=ON' -cmd ".timeout 3000" "$@"
  fi
  logend
}
dbsetup() {
  pipedir=$tmpdir/pipedir
  [ -d $pipedir ] || mkdir -p -m 1777 $pipedir
  [ -d $dbdir ] || mkdir -m 1775 $dbdir
  suf=`date +%s`
  sqi=$pipedir/sqi-$suf.$$
  sqo=$pipedir/sqo-$suf.$$
  mkfifo $sqi $sqo
  #tail -f $sqi | sq $db &	# "tail -f" is too heavy. DO NOT USE!!
  sq  $db < $sqi &
  sq3pid="`jobs -p` $!"
  if $isCGI; then
    exec 2>> $logdir/error.out
    chmod o-r $logdir/error.out
  fi
  exec 5> $sqi # Turning $sqi access through fd5 for continuous open state
  chmod o-r $logdir/debug.out
  rm $sqi
  # Attach supplemental DB
  cat >&5 <<-EOF
	.output /dev/null
	ATTACH DATABASE "$sessdb" AS tmp;
	CREATE TABLE IF NOT EXISTS $sesstb(user, skey, expire, UNIQUE(user, skey));
	DELETE FROM $sesstb WHERE expire < datetime('now', 'localtime');
	DELETE FROM $sesstb WHERE expire is NULL or expire = '';
	.output stdout
	EOF
}
cleanup2() {	# Dirty workaround for produced zombie processes
  if [ -n "$HTTP_USER_AGENT" ]; then	# When called from httpd
    pkill -9 -u `id -u` -P 1
  fi
  chmod o-r $querylog $db $sessdb	# make sure
}
cleanup() {
  trap '' INT HUP EXIT TERM PIPE
  echo .quit >&5
  kill $sq3pid
  kill $sq3pid
  rm -f $sqo $sqi
  rm -rf $tmpfiles
  cleanup2
}
# 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 ".output $sqo" >&5
  logstart
  if [ -z "$1" ]; then
    tee -a $querylog
  else
    printf '%s\n' "$@" >> $querylog
    printf '%s\n' "$@"
  fi  >&5
  echo ".output stdout" >&5
  cat $sqo
  rc=$?
  logend
  return $rc
}
_m4() {
  #S4NAME=f,f,f
  if m4 --version | grep -q GNU; then
    if type om4; then		# https://github.com/ibara/m4
      m4() {
	om4 "$@"
      }
    fi
  fi >/dev/null 2>&1
  m4 ${S4NAME:+"-D_S4NAME_=${S4NAME}"} ${S4CSS:+-D_S4CSS_="$S4CSS"} \
     ${S4WORLD:+-D_S4WORLD_="$S4WORLD"} \
     ${S4WORLDNAME:+-D_S4WORLDNAME_="$S4WORLDNAME"} \
     ${S4WORLDGRPS:+-D_S4WORLDGRPS_="$S4WORLDGRPS"} \
     ${S4WORLDS:+-D_S4WORLDS_="$S4WORLDS"} "$@"
}
if ! type gdate && date --version | grep -q GNU; then
  gdate() date "$@"
fi >/dev/null 2>&1
if ! type md5 && type md5sum && md5sum --version | grep -q GNU; then
  md5() {
    if [ -z "$1" ]; then
      md5sum | cut -d' ' -f 1
    else
      for f; do
	printf "MD5 (%s) = %s\n" "$f" "$(md5 < $f)"
      done
    fi
  }
fi >/dev/null 2>&1
curtimestamp() {
  date "${*:-+%F %T}"
}
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\"`"
)
isgrpownerbygid() (
  # $1=user, $2=group-rowid
  sql="select user from grp_adm where gname=(select gname from grp where rowid=$2) and user='$1';"
  err isgrpownerbygid: $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
  # ↑ 要はこういう処理を↓で一気にやっている
  case "$2" in
    grp_*)
      sql="SELECT 'owner'
	FROM grp_adm
	WHERE gname=(SELECT gname FROM $2 WHERE rowid=$3)
	      AND
	      user = '$user';"
      ;;
    *)
      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'),
     getauthor as (select author from article where id=(select id from $2 where rowid=$3)),
     isgrp as (SELECT val from getowner WHERE val IN (select gname from grp)),
     isgrpadm as (select user from grp_adm where
		gname=(select val from getowner) and
		user='$1'),
      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) in ('quiz', 'enquete')
	then CASE
	     WHEN (SELECT val FROM isgrp) IS NULL
	     THEN
		CASE WHEN (SELECT val from getowner)
		     	   IN ('$user', (SELECT author FROM getauthor))
		     THEN 'owner-or-user-article-is-readable'
		     ELSE ''
		END
	     WHEN (select user from isgrpadm) IS NOT NULL
	     THEN 'i-am-admin'
	     ELSE (SELECT author from getauthor WHERE author IN (SELECT user FROM grp_adm WHERE gname=(SELECT val FROM getowner)))
	     END
	when (select val from getmode) is null
	then 'open'
	when (select val from getowner) in (select gname from grp)
	then (SELECT user FROM isgrpadm)
	when (select author from article where
		id=(select id from $2 where rowid=$3))='$1'
	then 'user+author'
	else '' end;"
      ;;
  esac
  ## err isfilereadable: sql="`echo $sql`"
  # caseのネストで内側のcaseがスカラーtrueを返しても外側はtrue扱いにならない
  # result=`query "$sql"`
  # err FileAccessibility=$result
  [ -n "`query $sql`" ] || return 2
}
linkhome() {
  # $1=UserOrGroupRowid
  echo -n "<a href=\"$myname?"
  if isuser $1; then
    err "select 'home+'||rowid from user where name='$1';"
    query "select 'home+'||rowid from user where name='$1';"
    name=`gecos $1|htmlescape`
  else
    _grid=`numericalize "$1"`
    echo -n "grp+$1"
    name=`query "SELECT gname FROM grp WHERE rowid=$_grid;"|htmlescape`
  fi
  echo  "\">$name</a>"
}
hreflink() {
  # s4 specific notation:
  #   ^href=URL
  #   ^iframe=URL
  #   ^video=URL
  #   [[#NUM]]		- Jump to article ID NUM
  #   [[#Keyword]	- Jump to keywrod search for "Keyword"
  # 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>
  # Other Style
  #   ----		- <hr> (In the beginning of line)
  #   *Word*		- <em>Word</em>
  #   _Word_		- <em>Word</em>
  #   **Word**		- <strong>Word</strong>
  #   __Word__		- <strong>Word</strong>
  #   SPC+SPC+$		- <br>
  #   ^{x}		- <sup>x</sup>    ({} requisite)
  #   _{x}		- <sub>x</sub>    ({} requisite)
  cb='<input type="checkbox" class="s4-checkbox" disabled'
  checkboxON="${cb} checked>"
  checkboxOFF="${cb}>"
  _hrefptn="[-A-Za-z0-9,.:;/~_%#&+?=@!]*"
  _hrefptn="[A-Za-z0-9/~%+?=@!.][^][()<> ]*"	# URL should start with ASCII
  sed -e "s|\[\[\#\([0-9][0-9]*\)\]\]|<a href=\"?aid\1\">#\1</a>|g" \
      -e "s|\[\[#\([^]&]*\)\]\]|<a href=\"?kwd=\1\&stage=searchart\">\#\1</a>|g" \
      -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>|" \
      -e "s|^video=\($_hrefptn\)|<video controls><source height=\"320\" src=\"\1\"></video>|" \
      -e "s,^#### *\(.*\),<h4>\1</h4>," \
      -e "s,^### *\(.*\),<h3>\1</h3>," \
      -e "s,^## *\(.*\),<h2>\1</h2>," \
      -e 's,^----*$,<hr>,' \
      -e 's, \*\*\([^* |][^*|]*[^ |]\)\*\* ,<strong>\1</strong>,g' \
      -e 's, __\([^_ |][^_]*[^ ]\)__ ,<strong>\1</strong>,g' \
      -e 's, \*\([^* |][^*|]*[^ |]\)\* ,<em>\1</em>,g' \
      -e 's, _\([^_ ][^_]*[^ ]\)_ ,<em>\1</em>,g' \
      -e 's,~~\([^~][^~]*\)~~,<s>\1</s>,g' \
      -e 's,\([^\\]\);;;,\1<br>,g;s,\\;;;,;;;,g' \
      -e 's,  $,<br>,' \
      -e '/\\[()]/b' \
      -e 's,\^{\([^\}]*\)},<sup>\1</sup>,g' \
      -e 's,_{\([^\}]*\)},<sub>\1</sub>,g' \
      -e "s,- \[  *\]\([^|-]*\),${checkboxOFF}<label>\\1</label>,g" \
      -e "s,- \[[^ ]\]\([^|-]*\),${checkboxON}<label>\\1</label>,g" \
      
}
minitbl() {
  sed -n '
	/^|.*|/ {;	# If the line begin with "|" and has 2 or more "|"
	 s,|$,,;				# Remove trailing "|" first
	 s,|\* *&gt;&gt;&gt;|\([^|]*\) *,<th colspan="4">\1</th>,g;
	 s,|\* *&gt;&gt;|\([^|]*\) *,<th colspan="3">\1</th>,g;
	 s,|\* *\([0-9][0-9]*\)&gt;|\([^|]*\) *,<th colspan="\1">\2</th>,g;
	 s,|\* *&gt;|\([^|]*\) *,<th colspan="2">\1</th>,g;
	 s,|\* *^^^\([^|]*\) *,<th rowspan="4">\1</th>,g;
	 s,|\* *^^\([^|]*\) *,<th rowspan="3">\1</th>,g;
	 s,|\* *\([0-9][0-9]*\)^\([^|]*\) *,<th rowspan="\1">\2</th>,g;
	 s,|\* *^\([^|]*\) *,<th rowspan="2">\1</th>,g;
	 s,|\* *\([^|]*\) *,<th>\1</th>,g;	# "|*..." to "<th>...</th>"
	 s,| *&gt;&gt;&gt;|\([^|]*\) *,<td colspan="4">\1</td>,g;
	 s,| *&gt;&gt;|\([^|]*\) *,<td colspan="3">\1</td>,g;
	 s,| *\([0-9][0-9]*\)&gt;|\([^|]*\) *,<td colspan="\1">\2</td>,g;
	 s,| *&gt;|\([^|]*\) *,<td colspan="2">\1</td>,g;
	 s,| *^^^\([^|]*\) *,<td rowspan="4">\1</td>,g;
	 s,| *^^\([^|]*\) *,<td rowspan="3">\1</td>,g;
	 s,| *\([0-9][0-9]*\)^\([^|]*\) *,<td rowspan="\1">\2</td>,g;
	 s,| *^\([^|]*\) *,<td rowspan="2">\1</td>,g;
	 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:
			# . of ^. is workaround for FreeBSD sed
	  # 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' | miniul
}
miniul() {
  sed -e '
	/^\* / {;			# 行頭 "* "
	 x; s,^,<ul>,; x;		# 1周目: ホールドスペース先頭に <ul> を
	:top
	 s/\n//;
	 s/^  *//;			# 2周目以降: 行頭空白削除
	 s,\* ,,;			# まず行頭の "* " を消しておく
	 H;				# 置き換え結果をホールドスペースに追加
	 s/.*//;			# パターンスペースは消しておく
	 # ↓最終行なら残ったホールドスペース処理のため :cont へ
	 $ b cont
	 N;				# 次の行を読む
	 s/\n//;			# 空白始まりは継続行
	 /^ /b top
	 x; s/\n/<li>/; s,$,</li>,;	# 継続行でなければ <li></li> で囲む
	 p; s/.*//;
	 x;				# 次も "* " ならループを抜けない
	 /^\* /b top
	 s,^,</ul>,;			# 次が一般行なら箇条書終わり
	}

	:cont
	x;				# 行頭| 以外の行:
	/./ {;				# ホールドスペースに文字列があれば
	  s/^\n/<li>/; s,$,</li></ul>,;	# 箇条書を書き切って終わり
	  H; x
	}
	x' | miniol
}
miniol() {
  sed -e '
	/^[1-9]\. / {;			# 行頭 "N. "
	 h;x;				# 1周目: ホールドスペース先頭に <ol> を
	 s,^\([1-9][0-9]*\)\. .*,<ol start="\1">,;     # 初期番号付きで追加
	 x;
	:top
	 s/\n//;
	 s/^  *//;			# 2周目以降: 行頭空白削除
	 H;				# 置き換え結果をホールドスペースに追加
	 x;
	 s,[1-9][0-9]*\. ,,;		# まず行頭の "N. " を消しておく
	 x;
	 s/.*//;			# パターンスペースは消しておく
	 # ↓最終行なら残ったホールドスペース処理のため :cont へ
	 $ b cont
	 N;				# 次の行を読む
	 s/\n//;			# 空白始まりは継続行
	 /^ /b top
	 x; s/\n/<li>/; s,$,</li>,;	# 継続行でなければ <li></li> で囲む
	 p; s/.*//;
	 x;				# 次も "* " ならループを抜けない
	 /^[1-9][0-9]*\. /b top
	 s,^,</ol>,;			# 次が一般行なら箇条書終わり
	}

	:cont
	x;				# 行頭| 以外の行:
	/./ {;				# ホールドスペースに文字列があれば
	  s/^\n/<li>/; s,$,</li></ol>,;	# 箇条書を書き切って終わり
	  H; x
	}
	x' | tr -d '\r' | summary_detail
  # Posted text ends with \r, so remove it before block-level replacement
}
summary_detail() {
  # <<< One-line Summary
  # ..collapsed blocks...
  # <<<
  foldprefix="<<<"
  f=`echo "$foldprefix"|htmlescape`
  sed -E \
      -e "/(^|>)$f *(.+)/ {
      	   s,,\\1<details><summary>\\2</summary><div class=\"detail-content\">,
	   \$ q
	   N
	   s/\\n//
	}
	/(^|\\/([uo]l|table)>)$f\$/ {
	   s//\\1<\\/div><\\/details>/
	   \$ p
	   N
	   s/\\n//
	}" | divbox
}
divbox() {
#!/bin/sh
  sed -E \
      -e '
	:top
      	/(^|>)!!!i(nfo)?$/ {
		s,,\1<div class="info">,;
		$ p; N; s/\n//; b top
	}
	/(^|>)!!!w(arn)?$/ {
		s,,\1<div class="warn">,
		$ p; N; s/\n//; b top
	}
	/(^|>)!!!a(lert)?$/ {
		s,,\1<div class="alert">,
		$ p; N; s/\n//; b top
	}
	/(^|>)!!!m(m|ermaid)?$/ {
		s,,\1<div class="s4_mermaid">,
		$ p; N; s/\n//; b top
	}
	/(^|\/([uo]l|table|div)>)!!!$/ {
		s//\1<\/div>/
		$ p
		N; s/\n//; b top
	}' | listing
}
listing() {
#!/bin/sh
  listprefix='```'
  f=`echo "$listprefix"|htmlescape`
  sed -E \
      -e  "
      :top
      	/(^|>)$f([-A-Z0-9_a-z]+)?\$/ {
		s,,\\1<button class=\"copy\">Copy</button><pre class=\"listing\"><code class=\"\\2\">,;
		N; s,\n,<span></span>,
		:loop
		\$ q;
	        n
		/(^|span>)$f\$/ {
			s//\\1<\\/code><\\/pre>/
			\$ q;
			N; s/\n//; \$ q; b top
		}
		s,^,<span></span>,
		b loop
	}"
}
acclog() (
  # $1=table, $2=rowid
  n=${2%%[!-0-9]*}	# Remove non-digit chars from $2(should be rowid)
  if [ -n "$n" ]; then
    now=`curtimestamp`
    #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() {
  # 2020/5/14 Add dirty code to cache essential params
  if [ x"$session" = x"$main_session" ]; then
    case "$1" in
      user)	_user="$v" ;;
      skey)	_skey="$v" ;;
    esac
  fi
  query "replace into par values('$session', '$1', '$2', \"$3\");"
}
unsetpar() {
  for i; do
    if [ x"$session" = x"$main_session" ]; then
      case "$i" in
	user|skey) unset _$i ;;
      esac
    fi
    query "DELETE FROM par WHERE var='$i' AND sessid='$session';"
  done
}
replpar() {
  query "update par set val=\"$3\" where sessid='$session' and var='$1' and type='$2';"
}
getpar() {
  # err GETPAR=$1, _user=$_user
  val=""
  if [ x"$session" = x"$main_session" ]; then
    case "$1" in	# Dirty cache mechanism for high-load average
      user)	val=$_user ;;
      skey)	val=$_skey ;;
    esac
  fi
  val=${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
	printf '%s' "$val"; return
      fi ;;
  esac
## err getpar/ret: "val=[$val]"
  printf '%s' "$val"
}
setskey() {
  # For quick response...(?)
  query "REPLACE INTO $sesstb VALUES('$1', '$2', datetime('now', 'localtime', '$timeout'));"
}
chkskey() {
  # $1=sesskey, $user=LoginUserName
  test -z "$1" && return 1
  repl=`query "SELECT rowid,user FROM $sesstb WHERE user='$user' AND skey = '$1';"` || return 2
  rowid=${repl%%\|*}; repuser=${repl#*\|}
  if [ -n "$rowid" -a x"$user" = x"$repuser" ]; then
     query "UPDATE $sesstb SET expire=datetime('now', 'localtime', '$timeout') WHERE rowid=$rowid;"	# Errors can be ignored
     return 0
  fi
  return 1
}
resetskey() {
  if [ -n "$_user" ]; then
    query "DELETE FROM $sesstb WHERE user='$_user';"
  fi
}
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=`printf '%s' "$v"|sed -e 's/\"/\"\"/g'`
       printf '%s' "\"$v\""
       return ;;
     *.*.*|*-*-*|*[Ee]*[Ee]*|[Ee]*|*[\ -,:-df-~]*) # string
       printf '%s' "\"$v\""
       return ;;
     *)
       if expr "$v" : '[-0-9.Ee][-0-9.Ee]*$' >/dev/null 2>&1; then
	 printf '%s' $v		# MAYBE numeric, maybe...
       else
	 printf '%s' "\"$v\""
       fi ;;
   esac)
}
sqlquotestr() (
  case "$1" in
    *\'*)	v=`printf '%s' "$1"| sed "s/'/''/g"`
		printf '%s' "'$v'" ;;
    *)		printf '%s' "'$1'" ;;
  esac
)
mktempd() {
  TMPDIR=$tmpd mktemp -d -t $session.XXXX
}
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)
      curtimestamp ;;
    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 ;;
     *)  	sqlquotestr "$v" ;;
   esac)
}
getparquote() {
  sqlquote "`getpar $1`"
}
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:*)
	       printf '%s\n' "$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
	 printf '%s' "$val"
	 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
}
numericalize() {
  echo "${1%%[!0-9]*}"
}
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
}
if type gs >/dev/null 2>&1; then
  gs_pdfwrite() {
    gs -sDEVICE=pdfwrite -dPDFSETTINGS=/default \
       -dNOPAUSE -dQUIET -dBATCH -o "$2" "$1" >/dev/null 2>&1
  }
fi
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" -e 's/`/\&#096;/g' \
      -e 's/`/\&#96/'
  # -e 's/(/\&#040;/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() (
  path=${URL#*:/}
  path=${URL%/*}
  expire="`expire '' '%a, %d-%b-%Y %H:%M:%S GMT'`"
  for kv; do
    # echo "Set-Cookie: $kv; expires=$expire; Path=$path"
    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'`"
    ## err "GetCookie: $k=[$v]"
    case "$k" in
      user)	_user="$v" ;;
      skey)	_skey="$v" ;;
    esac
    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 \
      | tr -d '+=\n'|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 : /|tr -d '\n'`
  rcpt=`echo $1|tr ' ' '\n'|sort -u|tr '\n' ' '` # uniq and strip newlines
  ## Gmail rejects below(Duplicated headers)
  ##rcptheader=`echo $1|tr ' ' '\n'|sort -u|sed '2,$s/^/To: /g'`
  rcptheader=`echo $1|tr ' ' '\n'|sort -u|tr '\n' ','|sed 's/,$//'`
  subj=`echo $2|nkf -jM|tr -d '\n'`
  sender=${SENDER:-$admin}
  # Do not call m4 with directly passing text
  _r=$tmpd/rcpt
  echo -n "${SMAIL_TO:-$rcptheader}" > $_r
  replyto=${REPLYTO:+"Reply-to: $REPLYTO$LF"}
  (_m4 -D_RCPT_="spaste(\`$_r')" -D_REPLYTO_="$replyto" -D_SUBJ_="\`$subj'" -D_FROM_="$from" $msgdir/mail-header.m4
   cat $3 | nkf -jd ) | sendmail -f $sender $rcpt
}
smail_queue_flush() {
  # $1=timelimit
  timelimit=`query "SELECT datetime('now', 'localtime', '-6 hours');"`
  rowids=$(sq $workdb <<-EOF
	SELECT rowid FROM smailq
	       GROUP BY rcpts, subj
	       HAVING min(time) < '$timelimit';
	EOF
  )
  for rid in $rowids; do
    sq $workdb \
       "SELECT hex(rcpts),hex(subj) FROM smailq WHERE rowid in ($rid)" \
      | if IFS='|' read hexr hexs; then
      # err "hexrcpt=[$hexr] hexsubj=[$hexs]"
      rcpt=`echo "$hexr"|unhexize`
      subj=`echo "$hexs"|unhexize`
      
      # err ROWID==$rowid "sql=<<SELECT hex(rcpts),hex(subj) FROM smailq WHERE rowid=$rowid;>>"
      # err "rcpt=[$rcpt] subj=[$subj]"
      if sq $workdb <<-EOF | smail "$rcpt" "$subj"
	.separator "\n" "------------------\n\n"
	SELECT time, text FROM smailq
	       WHERE rcpts=(SELECT rcpts FROM smailq WHERE rowid=$rid)
	         AND subj=(SELECT subj FROM smailq WHERE rowid=$rid)
	       ORDER by time;
	EOF
      then
	cat <<-EOF | sq $workdb 
	DELETE FROM smailq
	       WHERE rcpts=(SELECT rcpts FROM smailq WHERE rowid=$rid)
	         AND subj=(SELECT subj FROM smailq WHERE rowid=$rid);
	EOF
      fi
    fi
  done
}
smail_queue() {
  # $1=Rcpts, $@=subj
  now=`query "SELECT datetime('now', 'localtime');"`
  if [ $? -eq 0 ]; then
    rcpts="X'"`echo "$1"|hexize`"'"
    subj="X'"`echo "$2"|hexize`"'"
    text="X'"`cat | hexize`"'"
    err "smail_queue: rcpts=[$1] s=[$2] t:hex=[$text]"
    mintime=$(cat  <<-EOF | sq $workdb
	CREATE TABLE IF NOT EXISTS smailq(rcpts, subj, text, time);
	INSERT INTO smailq VALUES($rcpts, $subj, $text, '$now');
	SELECT min(time) FROM smailq WHERE rcpts=$rcpts AND subj=$subj;
	EOF
    )
    ## XXX: Adhoc load mitigation
    case "$now" in
      *[01])
	err flush_queue=$mintime
	smail_queue_flush ;;
    esac
  fi
}
setviastring() {
  table=par
  oifs="$IFS"
  IFS="&" 
  for us in $1; do
    k=${us%%=*}
    v="`echo ${us#*=}|tr '%+' '= '|nkf -Ww -mQ|sed -e 's/\"/\"\"/g'`"
    setpar "$k" "string" "$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`
}
flag_profupdate() {
  # XXX:	Sorry to use undeclared column in user.def
  #		This is useful to mitigate account sync load
  query <<-EOF
	REPLACE INTO user_s(name, key, type, val)
	VALUES('$user', 'profupdate', 'string', datetime('now', 'localtime'));
	EOF
  touch $userupdateflag
}
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" && touch $userupdateflag
  # 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 "`collectemail $user`" "New Account"
}
checkauth() {
  user=`getpar user`
  skc=`getpar skey`		# from cookie
  [ -z "$user" ] && return 2
  ##skey="`getpwfield $user skey`"
  if [ -n "$skc" ]; then
    if chkskey "$skc"; then
      gencookie "user=$user" "skey=$skc"	# 2021-12-24: Update expire
      return 0
    fi
  fi
  pswd=`getpar pswd`
  quser=`sqlquotestr "$user"`
  dbuser=`query "SELECT name FROM user WHERE name=$quser;"`
  if [ $? != 0 ]; then		# Maybe DB locked
    return 4			# 4=server too heavy
  elif [ -z "$dbuser" ]; then
    err "Login USER failed: [$user]"
    return 2			# 2=login fail
  elif [ x"$pswd" = x"wasureta" ]; then
    if [ -n "$S4MASTERDB" ]; then
      contenttype; echo
      echo "Reset password is valid in BASE world." | html p
      echo "パスワードリセットはベースワールドでおこなってください。" | html p
      cat <<-EOF | html p
	<a href="$S4MASTERURL">BASE World</a>
	EOF
      exit 0			# XXXXX more smart...
    fi
    wasureta "$user"
    err wasureta issued for "$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 34`
    if setskey "$user" "$newsession" &&
	dbsetbyid user "$user" login "`curtimestamp`"; then
      gencookie "user=$user" "skey=$newsession"
      return 0
    else
      return 4		# Heavy load??
    fi
  fi
  err "Login failed: [$user]"
  return 2		# Password mismatch
}
showlogin() {
  args=`echo $myargs|tr ' ' '+'`
  test -z "$args" && resetskey
  s4name=${S4NAME:-s4}
  ( sed '/^<body/q' $layout/html.m4.html
    cat $layout/login.m4.html
    echo '</body></html>'
  ) | _m4 \
	-D_BODYCLASS_="login" \
	-D_TITLE_="$s4name" \
	-D_SYSNAME_="Welcome to $s4name" \
	-D_MYNAME_="$myname${args+?}$args" ${S4CSS:+-D_S4CSS_="$S4CSS"}
  exit 0
}
dologin() {
  test -n "$S4WORLD" && syncaccount
  checkauth
  st=$?
  if [ $st != 0 ]; then
    contenttype; echo
    _m4 -D_USER_="$user" -D_URL_="$url" -D_ADMIN_="$admin" \
	-D _LOADAVG_="`uptime|awk '{print $(NF-2)}'|tr -d ,`" \
       $msgdir/login-fail-$st.m4.html
    showlogin			# and EXIT
  fi
  err "Auth OK: [$user]"
}

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


cgiinit() {
  tmpd=`tmpd=$tmpdir mktempd`
  tmpf=$tmpd/stream.$$
  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?
      		err "CONTENT_LENGTH=$CONTENT_LENGTH$nl`ls -lF $tmpf`"
		s="`cat $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'`"
	case "$k" in
	  *:filename)
	    mimetype=`file --mime-type - < "$tmpd/$v"|cut -d' ' -f2`
	    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
	    err "k=[$k] v=[$v]"	# debug@2020-05-31
	    err "`ls -lF $tmpd/$v` MimeType=$mimetype"
	    case "$mimetype" in
	      [Ii]mage/x-xcf)
		bzip2 "$tmpd/$v"
		v=${v}.bz2
		;;
	      [Ii]mage/x-*|*/vnd.*)	;;
	      [Ii]mage/[Hh][Ee][Ii][Ff])
		if type heif-convert >/dev/null 2>&1; then
		  vjpg="${v%.heic}.jpg"
		  err "Conv $v to $vjpg in $tmpd"
		  convert -quality 75 -resize $maximagexy'>' \
			  "$tmpd/$v" "$tmpd/$vjpg" >/dev/null 2>&1
		  v=$vjpg
		else
		  mimetype="Not supported"
		fi
		;;
	      [Ii]mage/*)
		mogrify -quality 75 -resize $maximagexy'>' "$tmpd/$v"
		err "Mogrified: `ls -lF $tmpd/$v`"	# 2020-05-31
		;;
	      [Aa]pplication/[Pp][Dd][Ff])
		if [ x"`getpar comppdf`" = x"yes" ]; then
		  if type gs_pdfwrite >/dev/null 2>&1; then
		    nv=${v%.pdf}-compressed.pdf
		    err Calling gs from $v to $nv
		    if gs_pdfwrite "$tmpd/$v" "$tmpd/$nv"; then
		      err "PDF compressed: `ls -lF $tmpd/*.pdf`"
		      v=$nv
		    fi
		  fi
		fi
	    esac
	    if ! echo "$mimetype" | egrep "$file_accept_egrep" >/dev/null 2>&1
	    then
	      replpar text string " *添付できない形式です($v)* $file_warn"
	      continue
	    elif [ `wc -c < "$tmpd/$v"` -gt "$filesize_max" ]; then
	      replpar text string \
		  " *添付ファイル($v)は${filesize_max}バイト以下にしてください* $file_warn"
	      continue
	    fi
	    ;;
	  image|document|binary)
	    # When no file name supplied to input[type="file"]
	    continue ;;
	  *)
	    type='string'
	    ;;
	esac
	#sq $db "replace into par values('$session', '$k', '$type', \"$v\")"
	setpar "$k" "$type" "$v"
      done
      ;;
    *)
      setviastring "$s"
      ;;
  esac
}
email4group() {
  # Get for-$1=group email address(es) for $2...=users
  qgrp=`sqlquote "$1"`; shift
  users=`for i; do sqlquote "$i"; echo; done`
  users=`echo $users|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.name, val email
	  FROM user
	       LEFT JOIN user_m
	       ON user.name=user_m.name AND user_m.key='email')
	SELECT DISTINCT coalesce(g.email, u.email, u.name)
	FROM  useremails u LEFT JOIN grpemails g
	      ON u.name=g.user
	WHERE u.name in ($users);"
  query "$sql"
}
email4groupbyuid() {
  # Get for-$1=group email address(es) for $2...=user-ids
  g=$1; shift
  uids=`echo "$@"`
  uids=`echo $uids|tr ' ' ','`
  sql="SELECT DISTINCT name FROM user WHERE rowid IN ($uids);"
  email4group "$g" `query "$sql"`
}
myemail4group() {
  # Get my email address for $1-specified group
  email4group "$1" "$user" | sed -e 1q -e 's/[ ,].*//'
}
collectmembersbyid() {
  # Collect user names of group specified by grid
  rid=${1%%[!0-9]*}		# Cleaning
  query "SELECT user FROM grp_mem \
	WHERE gname=(SELECT gname FROM grp WHERE rowid=$rid);"
}
collectmembersbyid() {
  # Collect user names of group name
  qgrp=`sqlquote "$1"`
  query "SELECT user FROM grp_mem WHERE gname=$qgrp;"
}
collectgecosesbyid() {
  # Collect user gecoses of group
  rid=${1%%[!0-9]*}		# Cleaning
  query<<-EOF
	SELECT gecos
	FROM gecoses
	WHERE name IN (SELECT user FROM grp_mem
	      	       WHERE gname=(SELECT gname FROM grp WHERE rowid=$rid));
	EOF
}
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 "DELETE FROM par WHERE var='invite' AND val='$1';"
  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 "SNSへの御招待"
  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 | tr '[A-Z]' '[a-z]'`
  if [ -n "$INVITE_ONLYFROM" ]; then
    if ! echo "$user" | grep -E -e "$INVITE_ONLYFROM" >/dev/null 2>&1; then
      echo "招待可能ユーザに登録されていません" | html p
      return
    fi
  fi
  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)宛に案内を送信しました。"
      else	# Cannot be reached here
	repo="自動登録できない状況です。管理者に依頼してください。"
      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
  if [ -f $invite_policy ]; then
    pol="spaste(\`$invite_policy')"
  else
    pol="$invite_policy"
  fi
  _m4 -D_TITLE_="招待" -D_REPORT_="\`$repo'" -D_ACTION_="?invite" \
      -D_BODYCLASS_="default" -D_SUSPENDED_="$susp" \
      -D_INVITE_POLICY_="$pol" \
      $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 | tr '[A-Z]' '[a-z]'`	# Ensure lower case
  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";
	unsetpar tag kwd
	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
  rid=${1%%[!0-9]*}		# Cleaning
  case "$1" in
    */noteam)
      noteam="AND key != 'team'" ;;
  esac
  qgrp=`query "SELECT quote(gname) FROM grp WHERE rowid=$rid;"`
  if [ -z "$qgrp" ]; then
    echo "無効なグループIDです($1)" | html p
    return
  fi
  if ! isgrpownerbygid "$user" "$rid"; then
     echo "グループ管理者のみがクローン可能です" | 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 $noteam;
	-- 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_]*}

  ### If user is not admin, lead to group home
  grp=`getgroupbyid $rowid`
  if ! isgrpowner "$user" "$grp"; then
    echo "<p><a href=\"?grp+$rowid\">`echo "$grp"|htmlescape`</a></p>"
    return
  fi

  # 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
    ## Setup migration menu
    height="10em"		## Ugly!!
    if [ -n "$S4WORLDLIST" ]; then
      v=`fgrep -v "value=\"$worldconf\"" $worldoptionfile`
      err v=$v
      if [ -n "$v" ]; then
	migrate=$(cat<<-EOF
	`cgi_radio grpaction migrate id="migrate"`<label
	 for="migrate">別Worldへ移住</label>
	<div style="height: $height;">
	<form action="?migrategrp">
	<p>移住先:<select name="migrateto">$nl$v$nl</select></p>
	<p>グループや掲示板のURLが変わります。
	外部からリンクしている場合は飛べなくなります。
	すでにリンクされた掲示板を多数含む場合は既存グループを温存し、
	「グループのクローン」で
	メンバーを引き継いだ上でそのクローンを移住するのがお勧めです。</p>
	<p><label>`cgi_checkbox emichk yes`確認</label></p>
	`cgi_hidden stage migrategrp`
	`cgi_hidden rowid $rowid`
	`cgi_submit OK`
	`cgi_reset Reset`
	</form>
	</div>
	EOF
	       )
      fi
    fi
    html div 'class="foldtabs"' <<-EOF
	`cgi_radio grpaction clone id="clone"`<label
	 for="clone">グループのクローン作成</label>
	<div style="height: $height;">
	 <p>構成メンバーが同じ新規グループを作成します。</p>
	 <table>
	  <tr><td><a href="?groupclone+$rowid">
	   <button>クローン作成(チームも複製)</button></a></td>
	   <td><p>(チームなどもそのままで掲示板なしの状態から)</p></td></tr>
	  <tr><td><a href="?groupclone+$rowid/noteam">
	   <button>作成(チームなし)</button></a></td>
	   <td>(チームは引き継がずメンバーのみ同じグループを作成)</td></tr>
	 </table>
	 <p>ボタンを押すと即作成します。不要な場合はグループ編集で
	 削除してください。</p>
	</div>
	$migrate
	 `cgi_radio grpaction close id="x"`<label for="x" accesskey="x">×</label>
	<div style="height: $height; background: transparent;"></div>
	EOF
  fi
}
migrategrp() {
  rowid=`getpar rowid`
  rowid=${rowid%%[!0-9]*}
  grp=`getgroupbyid $rowid`
  if ! isgrpowner "$user" "$grp"; then
    echo "<p><a href=\"?grp+$rowid\">`echo "$grp"|htmlescape`</a></p>"
    return
  fi
  if [ x`getpar emichk` != x"yes" ]; then
    echo "移住確認未チェックなので中止します。" | html p
    grp "$rowid"
    return
  fi
  destconf=`getpar migrateto`
  err destconf=$destconf
  if [ ! -e $destconf ]; then
    echo "移住先Worldが認識できないので中止します($destconf)。" | html p
    grp "$rowid"
    return
  fi
  if [ -n "$worldconf" ]; then
    srcconf=$worldconf
  else
    srcconf=s4-config.sh
  fi
  _m4 -D_TITLE_="移住操作" -D_BODYCLASS_="" $layout/html.m4.html
  echo "移住操作" | html h1
  echo '<pre>'
  set -- "$srcconf" "$destconf" "$rowid"
  err  ./s4-migrate.sh "$srcconf" "$destconf" "$rowid"
  . ./s4-migrate.sh		# Dot(.) sourcing might not pass arguments
  rc=$?
  echo "</pre>"
  if [ $rc -eq 0 ]; then
    echo "World [$world] への移住完了。" | html p
    echo "<p><a href=\"$dsturl?grp+$destrowid\">移住先</a></p>"
  elif [ -n "$faillist" ]; then
    echo "移住後削除失敗" | html p
    echo "空いている時間帯に再度試して下さい。" | html p
  else
    echo "移住失敗" | html p
    echo "移動先に重複がないか確認して下さい。" | html p
  fi
  return
}
mems() {
  _m4 -D_TITLE_="参加者一覧" -D_BODYCLASS_=listmember $layout/html.m4.html
  kwd=`getpar kwd`
  listmember $kwd
}
grps() {
  case "$S4WORLD" in
    $nonewgroupworld) ;;
    *)	LINK_NEWGRP="<a href=\"?groupman\">新規グループ作成</a>" ;;
  esac
  _m4 -D_TITLE_="グループ一覧" -D_BODYCLASS_=listgroup $layout/html.m4.html
  kwd=`getpar kwd`
  listgroup "$kwd" \
      | _m4 -D_DUMPTABLE_="syscmd(cat)" \
	    -D_TITLE_="グループ関連操作" \
	    -D_FORM_="${LINK_NEWGRP}${NEWGRP_GUIDE}" \
	    $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
	    WHEN (SELECT user FROM grp_mem
		  WHERE gname=bs.owner AND user='$user') IS NULL
	    THEN 0
	    ELSE 1
       END;
EOF
}
listnewblogsql() { # $1=user
  newdays=${WHATS_NEW_DAYS##*[!0-9]}	# Shave non-digits
  newdays=${newdays%%[!0-9]*}
  newdays=${newdays:-14}
  basetime="datetime('now', 'localtime', '-${newdays} days')"
  deftime=`query "SELECT coalesce((SELECT max(time) FROM acclog
		  	 	   WHERE user='$user'
				    AND tblrowid IN
				      ($blogreadflagrowid,
				       $blogcutoffflagrowid)),
				  $basetime		-- "0"
				  );"`
  cat<<EOF
`sql4interestblogs`
WITH article_ctime as (
 SELECT id,blogid,author,max(val) ctime
 FROM   article join article_s s using(id)
 WHERE  s.key='ctime' AND s.val > '$deftime'
 GROUP BY id
), 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
), blogall as (
 SELECT * FROM blog_title_owner b JOIN article_ctime ac ON b.id=ac.blogid
), news as (
 SELECT brid, bl.id blid, bl.title, ctime,
 	coalesce(al.time, '$deftime') atime,
        count(bl.id) "新着", bl.author
 FROM blogall bl
      LEFT JOIN
      (SELECT * FROM acclog WHERE user='$user' AND tbl='blog') al
      ON bl.brid=al.tblrowid
 WHERE atime < bl.ctime
 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() {
  # $1		 = { author=<AUTHOR> | grid=<GroupRowid> }
  # $2(optional) = pre-input keywords
  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"
      ;;
    grid=*)
      a=`echo "${1#grid=}"`; a=$((0 + $a))
      auth="<input type=\"hidden\" name=\"grid\" value=\"$a\">"
      placeholder="このグループからの検索"
      ;;
  esac
  inikwd="$2"				# no need to htmlescape
  cat<<-EOF
	<div class="right">
	<form action="$myname">$auth
	<input type="text" name="kwd" value="$inikwd" title="$help"
	 placeholder=" $placeholder " width="10" accesskey="k">
	 <!-- POST SENTENCE -->
	 ${touchpanel:+<p class="help">$help</p>}
	<input type="hidden" name="stage" value="searchart">
	<!-- EOF -->
	</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 no 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
    if [ -z "$S4MASTERDB" ]; then
      usermenu="<a href=\"?userconf\" accesskey=\"e\"
	 title=\"Shortcut: E${nl}Edit Profile\">プロフィールの編集</a> / "
    elif [ -n "$S4MASTERURL" ]; then
      usermenu="<a href=\"$S4MASTERURL\" accesskey=\"e\"
	 title=\"Shortcut: E${nl}Main Site\">Base World</a> / "
    fi
    usermenu="$usermenu
	<a href=\"?blog\" accesskey=\"n\" title=\"Shortcut: N${nl}New blog\">新規話題の作成</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
      usermenu="$usermenu / <a href=\"?lsmyfile\" accesskey=\"l\"
       title=\"Shortcut: L${nl}List All Attachment files\">過去の提出ファイル</a>"
    fi
  else
    latestlog=`query "SELECT max(time) FROM acclog WHERE user='$uname' \
    		     GROUP BY user;"`
    usermenu="<p>Last seen on $latestlog</p>"
    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
  printf "%s さん%s" "$gecos" "${S4WORLDNAME:+@$S4WORLDNAME}" \
    | htmlescape	> $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...
  # $hidden_mode is defined in global section
  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
      AND
       ('$uname' = '$user'			-- user can read all own posts
          OR
	  -- exclude posts of others in quiz/enquete blogs
        NOT EXISTS (SELECT * FROM blog_s
       	   	   WHERE id=b.id AND key='mode' AND val IN $hidden_mode));"
  # 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="Shortcut: D${nl}Recent Post">最近の書き込み先</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"`
    # echo "$sql" > tmp/listnew
    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=\"Shortcut: F${nl}NEWS\">新着${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>"
    else			# If news is 0, set log cut off flag
      acclog blog $blogcutoffflagrowid	# for speed
    fi
  else				# Not My Home ($user != $uname)
    : # DT_SQL=
  fi
  ) > $hometail &		# Is background call safe to m4??
  #
  listgroupbytable $formdir/grp.def "$cond" $uname |
      _m4 -D_BODYCLASS_=home -D_TITLE_="spaste(\`$tf')" \
	  -D_PROFILE_="spaste(\`$pf')$usermenu" \
	  -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 $3(optional)=uname
  tagline=`grep :tag: $1`;
  and="${2:+and }" where=${2:+where }
  href="<a href=\"$myname?grp+"
  echo '<div class="listgroup">'
  sql="select val from grp_s where key='tag' $and$2 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$2 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
  if [ -n "$S4WORLDLIST" -a -n "$3" ]; then
    err "peekgrpworlds($user) BEGIN: `gdate +%S.%03N`"
    peekgrpworlds mem:"$3"
    err "peekgrpworlds($user)   END: `gdate +%S.%03N`"
  fi
  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
  anchor=`echo $@|htmlescape`
  echo "<a href=\"$href\"><img title=\"$title\" src=\"$src\">$anchor</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]*}
  if [ -z "$offset" ]; then
    offset=`getpar start`; offset=${offset%%[!0-9]*}
    offset=$((offset-1))
  fi
  offset=$((offset + 0))	# change to numeric forcibly
  [ $offset -lt 0 ] && offset=0
  limit=$listentlimit
  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
  kwd=${2:-`getpar kwd`}
  if [ -n "$kwd" ]; then
    kwd=`echo $kwd | tr -d '";\n' | tr -d "'"`
    case "$kwd" in
      mem:*@*)
	byuser=${kwd#*mem:}
	qusr=`sqlquote "$byuser"`
	cond1="(a.gname IN (SELECT gname FROM grp_mem WHERE user=$qusr))"
	;;
    esac
    if [ x"$1" = x"group" ]; then
      if [ -n "$cond1" ]; then
	enthead="`gecos "$byuser"|htmlescape` さんの所属"
      else
	cond1="(b.name like '%${kwd}%')"
      fi
    else
      cond1="(nick like '%${kwd}%' or b.name like '%${kwd}%')"
    fi
  fi
  tag=`getpar tag` tag2=`getpar tag2`
  if [ x"$tag" = x"NULL" ]; then
    tag="" tag2=""
  fi
  if [ -n "$tag$tag2" ]; then
    tag=${tag:-$tag2}
    qtag=`sqlquote "$tag"`
    cond2="tag=$qtag"
  fi
  if [ -n "$cond1$cond2" ]; then
    cond="$cond1${cond2:+ AND $cond2}"
    cond="WHERE ${cond# AND }"
  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,
			max(case key when 'login' then val end) as login
			from ${tbl}_s group by $nm)
		b on a.$nm=b.name $cond $3
	order by b.wtime desc, b.login desc,
	         b.mtime desc, b.tag 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 "$enthead${entity} 一覧" "${S4WORLDNAME:+@$S4WORLDNAME}" | html h2
  echo '<div class="listentry">'		# List-entry div
  # Show owner/member filter button
  METHOD=GET
  hiddens="$hiddens
	`cgi_hidden stage \"$stage\"`"
  if [ x"$tbl" = x"grp" ]; then
    args=`grep "^種別:"  $formdir/grp.def | cut -d: -f5`
    fh="<select name=\"tag\">$nl"
    fh="$fh<option value=\"NULL\"${tag:+ selected}>グループ種別...</option>"
    for l in $args; do
      val=${l#*=} tname=${l%=*}
      if [ x"$val" = x"$tag" ]; then
	s=" selected"
	selectedtags="(種別[${tname}]のみ)"
      else
	s=""
      fi
      form=$nl$form"<option value=\"$val\"$s>$tname</option>"
    done
    form="$fh$form</select><input type=\"submit\" value=\"で絞る\">"
    cat<<-EOF
	<form action="$myname" method="$METHOD">
	</form>
	以下一覧のうち: `cgi_checkbox onlymem no 'id="ismembtn"'`<label
	for="ismembtn">参加中以外隠す</label>
	`cgi_checkbox onlyadm no 'id="isadmbtn"'`<label
	for="isadmbtn">管理者参加以外隠す</label>
	EOF
    # limit=3
    hiddens=$hiddens" "`cgi_hidden tag2 "$tag"`
  fi
  if [ $total -gt $limit -o \( -n "$S4WORLDLIST" -a x"$tbl" = x"grp" \) ]; then
    echo '<div>'
    METHOD=GET cgi_form $stage <<EOF
$form
<label>次の語を含む${entity}で検索:
`cgi_text kwd "$kwd"`</label>
$hiddens
EOF
    echo '</div>'
  else
    echo $selectedtags | html p
  fi
  test -n "$kwd" && hiddens="$hiddens$nl`cgi_hidden kwd \"$kwd\"`"
  cat<<EOF
  <form action="$myname" method="$METHOD">
  <p>${total}件中の<input class="hidesub" type="text" name="start"
  value="$((offset+1))" size="3">件めから${kwd:+" - 検索語: $kwd"}$hiddens
  <input type="submit" value="確定"></p>
  </form>
EOF
  if [ $((offset+limit)) -lt $total ]; then
    nextbtn=$(
    cat<<EOF
<div class="right clear"><form action="$myname" method="$METHOD">
`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="$METHOD">
`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
    htmlname=`echo $name|htmlescape`
    echo "<div class=\"iconlist xy$thumbxy $type $ownerp\">
	<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>$htmlname${ownerp:+<br>($ownerp)}"
    echo "</div>"
  done
  echo "</div>"					# End of List-entry div
  echo ${pnbtn:+"<hr>$nextbtn$prevbtn"}
  if [ -n "$kwd" -a x"$tbl" = x"grp" -a -n "$S4WORLDS" ]; then
    peekgrpworlds "$kwd"
  fi
)
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 ORDER by val;"
}
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"`
  htmlgrp=`echo "$grp"|htmlescape`
  ## err showgroup2: grid=$grid grp=$grp qgrp="[$qgrp]"
  if isgroup "$grp"; then
    tf=$tmpd/title.$$
    sf=$tmpd/search.$$
    bodyclass=`query "SELECT val FROM grp_s
	       WHERE gname=$qgrp AND key='regmode';"`
    if ismember "$user" "$grp"; then
      ismember="ismember"
      bodyclass="$bodyclass${bodyclass:+ }ismember"
    else
      ismember="" # bodyclass="group"
    fi
    bodyclass="$bodyclass grouphome"
    echo "<div class=\"search\">`search_form grid=\"$grid\"`</div>"> $sf
    echo "グループ $htmlgrp" > $tf

    showgroupsub $formdir/grp.def "$grid" | \
	_m4 -D_TITLE_="syscmd(\`cat $tf')" \
	    -D_FORM_="syscmd(\`cat')" \
	    -D_BODYCLASS_="$bodyclass" \
	    -D_DUMPTABLE_="" \
	    $htmlheader $sf $layout/form+dump.m4.html
	    # $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\" accesskey=\"e\"
      title=\"Shortcut: e${nl}Edit Group\">グループ情報の編集</a>"
    iamowner=$rowid
    colmd=" mode"
    # We have to specify report-type blog to lshandoutall
    repblog=`query "
	WITH grpblogs AS (
	  SELECT id FROM blog_s
	  WHERE key='owner' AND val=(SELECT gname FROM grp WHERE rowid=$rowid)
	), ownerMode AS (
	  SELECT id,
	  	 max(CASE key WHEN 'owner' THEN val END) owner,
		 max(CASE key WHEN 'mode' THEN val END) mode
	  FROM blog_s
	  GROUP BY id
	), blogid AS (
	  SELECT id
	  FROM grpblogs NATURAL LEFT JOIN ownerMode
	  WHERE mode LIKE 'report%' LIMIT 1
	) SELECT blog.rowid FROM blogid NATURAL LEFT JOIN blog;"`
    if [ -n "$repblog" ]; then
       csvid="gethandoutcsv"
       replink="/ <a href=\"?lshandoutall+$repblog#$csvid\">レポート全集計</a>"
    fi
  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
	${iamowner:+ / }<a accesskey="n" title="Shortcut: n${nl}New blog"
	  href="?blog+$rowid">グループの新規話題作成</a>
	  / <a accesskey="m" title="Shortcut: m${nl}Operations on Members"
	  href="?grpaction+$rowid">メンバーを個別選択しての操作</a>
	  $replink</p>
	<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>'
  thelp="1ヶ月分のまとめには上部検索窓に @month と入れてください。"
  cat<<-EOF
	<div class="fold"> <!-- in showgroupsub -->
	 <h2>話題一覧</h2>
	 <form class="summary inline" action="$myname" title="$thelp">
	 `cgi_hidden owner "$grp"`
	 `cgi_hidden kwd "@week"`
	 `cgi_hidden stage searchart`
	 `cgi_submit "一週間のまとめ"`
	 </form>
	 <input type="checkbox" id="dtcheck" checked><label
	  for="dtcheck">話題一覧表示</label>
	EOF
  cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by \"稼動状態\", ctime desc"
  colstate="state:稼動状態:frozen=rowclass=凍結"
  frzbtn='<button class="toggle-frozen"></button>'
  if [ -n "$ismember" ]; then
    DT_COUNT=author DT_COUNT_HEADER="書" DT_COUNT_BY=$user
  fi
  DT_CHLD=article:blogid \
	 DT_QOWNER="$qgrp" \
	 DT_VIEW=replyblog dumptable html blog \
	 "ctime title heading team notify:通知$colmd $colstate" "$cond" \
    | if [ -n "$iamowner" ]
  then
    sed -Ee "s,(<TD class=\"稼動状態\">).*(</TD>),\1$frzbtn\2,"
  else
    cat
  fi
  ## DO not convert to frzbtn when not admin
  echo "</div>  <!-- in showgroupsub -->"

  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 "`getpar kwd`" "$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"`
  htmlgrp=`echo "$grp" | htmlescape`
  myuid=`query "SELECT rowid FROM user WHERE name='$user';"`
  if [ -z "$grp" ]; then
    echo "無効な指定です。" | html p; return
  fi
  if ! ismember $user "$grp"; then
    echo "加入者のみに許可された操作です。" | html p; return
  fi
  isowner=""
  isgrpowner "$user" "$grp" && isowner="yes"
  err 2=$2 3=$3
  case "$2" in
    get:teamcsv)
      teamcsv "$grid"
      return
      ;;
  esac
  echo "グループ $grp 個別選択操作"  \
      | _m4 -D_TITLE_="syscmd(\`cat')" \
	    -D_BODYCLASS_="`grp_getbodyclass \"$grp\"`" \
	    $layout/html.m4.html

  usel=`getpar usel`
  if [ -n "$usel" ]; then
    uids=$(echo `echo $usel`|tr ' ' ',')
    ## err grpaction-1: grp=$grp, `echo $sql`
    text=`getpar text|tr -d '\r'`
    
    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 grp_adm grp_adm_s grp_adm_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`
      safegc=`echo "$gecos" | tr -d '<>@,'`
      #fromad=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`
      fromad=`myemail4group "$grp"`
      ###mail_from="$safegc <$fromad>"
      mail_from="$safegc <$user>"	# TEST: 2020/5/13
      test -n `getpar sender` &&
	export SENDER=$user		# TEST: 2020/5/15
      replyto=$fromad
      
      ## Start parse of attachment files
      if [ -n "`getpar email`" ]; then
	ar=`getpar supprcpt`
	if [ -n "$ar" ]; then
	  for a in $ar; do
	    if checkdomain "$a"; then
	      supprcpt="$supprcpt $a"
	    else
	      err "SupprcptErr=[$a] by $user"
	      removercpt="$removercpt $a"
	    fi
	  done
	fi
	subj=`getpar subject`
	afiles=""
	for fn in `query "SELECT DISTINCT val FROM par WHERE var='image' AND sessid='$session';"`
	do
	  f=$tmpd/$fn
	  if [ -s $f ]; then
	    afiles=$afiles"${afiles:+ }$f"
	  fi
	done
      else
	preface=$(cat <<-EOF
	$url
	のグループ「$grp」のメンバーである $gecos さんから、
	あなた宛のメッセージです。
	----------------------------------------------------------
	EOF
	)
      fi
      rcpts="`email4groupbyuid "$grp" $usel` $fromad$supprcpt"
      rcpts=`echo $rcpts|tr ' ' '\n'|sort|uniq|tr '\n' ' '`
      subj="${subj:-$gecos さんからのメッセージ}"
      REPLYTO=$replyto
      MAIL_FROM=$mail_from
      export REPLYTO SMAIL_TO MAIL_FROM
      err "GrpActionSend: user=[$user], MAIL_FROM=[$mail_from], rcpts=[$rcpts], REPLYTO=[$replyto}"
      for r in $rcpts; do
	if [ x"$user" = x"$r" -o x"$fromad" = x"$r" ]; then
	  SMAIL_TO=$rcpts	# Show all rcpts to sender oneself
	else
	  # Show sender and rcpts address for guest
	  SMAIL_TO=`echo $r $user $fromad|tr ' ' '\n'|sort -u|tr '\n' ' '`
	fi
	if [ -n "$afiles" ];then
	  ./sendmultipart.sh -t "$r" -s "$subj" -f "$mail_from" $afiles
	else
	  smail "$r" "$subj"
	fi <<EOF
${preface:+$preface$nl}$text
EOF
      done
      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"`
${supprcpt:+追加宛先 $supprcpt$nl}(送信者である $gecos さんも含まれます)
${removercpt:+アドレスエラーによる削除(送られません): <em class="warn">$removercpt</em>}
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 ,`	# ..and strip spaces of both ends
      if [ x"$team" != x"$newteam" ]; then
	echo "チーム名に使えない文字を除去しました" | html p
	team=$newteam
      fi
      if [ -z "$team" -o x"$team" = x"なし" -o x"$team" = x"TEAM" ]; 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
  # POST count summary
  from=`getpar from`; to=`getpar to`
  from_input="<input type=\"date\" name=\"from\" placeholder=\"YYYY-MM-DD\" value=\"${from}\">"
  to_input="<input type=\"date\" name=\"to\" value=\"${to:-9999}\">"
  fromtonote="<p title=\"Count the Number of Posts from-to\">POST/ACCESS集計: $from_input - $to_input</p><!-- $from - $to -->"
  # New entry
  sql="WITH mems AS (
	  SELECT g.rowid, name, gecos FROM grp_mem gm LEFT JOIN gecoses g
	  ON gm.user=g.name
	  WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)
	), target_article AS (
	  SELECT id FROM article_s
	  WHERE key='ctime' AND val BETWEEN '${from:-0000}' AND '${to:-9999}'
	), posts AS (
  	  SELECT author, count(author) post
	  FROM article NATURAL JOIN article_s NATURAL JOIN target_article
  	  WHERE blogid IN (SELECT id FROM blog_s
                   WHERE key='owner'
                   AND val=(SELECT gname FROM grp WHERE rowid=$grid))
  		   AND key='text'
	  GROUP BY author
	), teams AS (
	  SELECT user, group_concat(val, ', ') team
	  FROM mems m LEFT JOIN grp_mem_m gm ON m.name=gm.user
	  WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)
	  AND key='team'
	  GROUP BY user
	), user_post AS (
	  SELECT m.rowid, name, m.gecos, coalesce(post, 0) as POST
	  FROM mems m LEFT JOIN posts
	  ON m.name=posts.author
	  GROUP by m.rowid
	), user_view AS (
	  SELECT user vuser,count(user) cnt
	  FROM tblaccesses
	  WHERE user IN (
		SELECT user FROM grp_mem
		WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid))
	  AND tbl='blog'
	  AND tblrowid IN (
		    SELECT rowid FROM blog
		    WHERE id IN (
		    	  SELECT id FROM blog_s
			  WHERE key='owner' AND val=(
			  	SELECT gname FROM grp WHERE rowid=$grid)))
	  AND time BETWEEN '${from:-0000}' AND '${to:-9999}'
	  GROUP BY user ORDER BY cnt
	)
	SELECT
	  CASE
	  WHEN (SELECT user FROM grp_adm
	        WHERE gname=(SELECT gname FROM grp WHERE rowid=$grid)
		AND user=up.name) IS NOT NULL
	      then 'k'
	  ELSE ''
	  END || rowid || ','
	  || rtrim(substr(name, 1, instr(name, '@')), '@') UID,
	  gecos NAME,
	  post POST,
	  (coalesce((SELECT cnt FROM user_view WHERE vuser=name), 0)) ACCESS,
 	  team _TEAM_
	FROM user_post up LEFT JOIN teams t
	ON up.name=t.user
	ORDER BY up.name;"
  ## Want to count ACCESS with JOIN, but failed to produce user-unique rows
  ## err grpaction: "`echo \"$sql\"`"
  tf=$tmpd/title.$$
  echo "グループ[<a href=\"?grp+$grid\">$htmlgrp</a>]参加メンバーに対する操作" > $tf
  cmmsg="`cgi_radio rm commission id=\"cmadmin\"`<label accesskey=\"f\"
 title=\"Shortcut: f${nl}Add to Administrator of the Group\"
 for=\"cmadmin\">管理者委任</label>
<div><p>このグループでの全権を付与します。信頼できる人に託してください。
</p></div>"
  excmsg="`cgi_radio rm yes id=\"conf\"`<label accesskey=\"g\"
 title=\"Shortcut: g${nl}Remove from the Group\"
 for=\"conf\">GRP登録解除</label>
<div>本当に消します! `cgi_checkbox confirm yes` 確認
<p>この操作による通知は本人に行きません。
あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>
</div>"
  submitBTN=`cgi_submit 送信`
  # Get team list to which current user belongs into $hexteams
  allhexteams=$(hexteams "$grp")
  if [ -n "$isowner" ]; then
    myhexteams="$allhexteams"		# admin can remove all teams' attr
  else
    myhexteams=$(hexteams "$grp" "$user")
  fi
  if [ -n "$isowner" -a -n "$allhexteams" ]; then
    gettingcsv="<p>Download: <a href=\"?getteamcsv+$grid\">Team.csv</a> (Zoom Breakout Room 事前割り当てに使えます), <a href=\"?getteamcsv+$grid+name\">Team-with-name.csv</a>(参照用名前付)</p>"
  fi
  if [ -n "$myhexteams" ]; then
    rmteammsg="`cgi_radio rm rmteam 'id=\"cmrmteam\"'`<label accesskey=\"s\"
title=\"Shortcut: s${nl}Strip a team tag from\"
for=\"cmrmteam\">チーム属性除去</label>
<div>チーム属性:`cgi_select_h rmteam \"2d2d2d\" $myhexteams`
を除去します: `cgi_checkbox teamconfirm yes` 確認 $submitBTN
<p>この操作による通知は本人に行きません。
あらかじめ通知するか、登録解除してよい状況かしっかり確認してください。</p>
</div><!-- end of $rmteammsg -->
"
  fi
  stf=$tmpd/selteam.$$
  cgi_select_h selteam "5445414d" $allhexteams > $stf
  b1='<label> <input type="checkbox" name="usel" value="'
  ba='<label class="admin"><input type="checkbox" name="usel" value="'
  br='<span id="reverse" title="Reverse Selection"></span>'
  # lnk='"> <span></span></label> [<a href="?home+\3">HOME</a>]'
  lnk='<a href="?home+\3">\5</a>'
  # (1) Join <TR> line and the next
  # (2) (<TR><TD)>(k?)(1234),(userid)</TD><TD>(GECOS)</TD>
  #     ↓
  # <TR><TD>\2<label><input ...value="\3">\4</label></TD> \
  #    <TD><a href="?home+\3">\5</a></TD>
  cgi_form grpaction<<EOF \
      	| sed  -e '/^<TR>/{; N; s/\n//; }' \
      	| sed  -E \
	       -e "s|^(<TR><TD>)(k?)([0-9]*),(.*)</TD><TD>(.*)</TD>|\1\2$b1\3\">\4</label></td><td>$lnk</TD>|" \
	       -e 's/^(<TR><TD[^>]*>)k(<label)/\1\2 class="admin"/' \
	       -e "s|^(<TR><TH>)(UID)|\1$br \2|" \
	| _m4 -D_TITLE_="spaste(\`$tf')" \
	      -D_SUBTITLE_="チェック後操作ボタン" \
	      -D_FORM_="syscmd(cat)" -D_DUMPTABLE_="" \
	      $layout/form+dump.m4.html \
	| _m4 -D_TEAM_="spaste(\`$stf')"
<p>下でチェックした人を対象として:</p>
<div class="foldtabs">
`cgi_radio rm addteam 'id="cmteam"'`<label accesskey="a"
title="Shortcut: a${nl}Add a team tag to"
for="cmteam">同じチーム属性を付与</label>
<div>チーム名:`cgi_text team "" 'id="inteam" list="teams"'`
`cgi_datalist_h teams $allhexteams`$submitBTN
</div>
${rmteammsg}
`cgi_radio rm send id="sendmsg"`<label accesskey="d"
 title="Shortcut: d${nl}DirectMail to"
 for="sendmsg" title="Direct Message">DM送信</label>
<div>
`cgi_checkbox email yes 'id="email" class="fold"'`<label for="email"
title="Using email format">email書式を使う</label>
<div class="folded">
<table>
<tr><td>From: </td><td>$user</td></tr>
<tr><td>このFrom:で送る</td>
<td>`cgi_checkbox sender yes 'checked'`<small></small>
</td></tr>
<tr><td>Subject: </td><td>`cgi_text subject`</td></tr>
<tr><td>追加宛先(通常空欄): </td><td>`cgi_text supprcpt ""`</td></tr>
<tr><td>ファイル添付: </td>
    <td>`cgi_file image "" "multiple $file_accept title=\"$file_accept_help\""`<br><small>文書ファイルはPDFに変換してから</small></td></tr>
</table>
<p>(下記一覧から1人以上選択していない場合は送れません$submitBTN)</p>
</div>
<div>本文:`cgi_textarea text "" cols=72`
</div>
</div>
${isowner:+$cmmsg$excmsg}
`cgi_radio rm close id="x"`<label for="x" accesskey="x">×</label>
</div>
<h4>$htmlgrp 参加者一覧</h4>$gettingcsv$fromtonote
<table class="td3r td4r thl">
`sq $db -header -html "$sql"`
</table>
`cgi_hidden grp $grid`
`cgi_hidden myuid $myuid id="myuid"`
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 by $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)

  # $hidden_mode is defined in global section at head

  cat<<EOF ## | tee tmp/sql.out
CREATE TEMPORARY VIEW readableblogs AS
 SELECT blog.rowid rid, id, author
 FROM blog
      NATURAL JOIN
      (SELECT id,
      	      max(CASE key WHEN 'owner' THEN val END) owner,
      	      max(CASE key WHEN 'mode' THEN val END) mode
       FROM blog_s GROUP by id) bs
 WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL
	     THEN 		-- blog owner is an user, READABLE
		NOT mode IN $hidden_mode
	    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
	    WHEN mode IN $hidden_mode
	    THEN 0		-- "quiz" mode blog cannot be searched
	    ELSE 1
       END;
EOF
}
mvteamform() {
  owner=$1
  hexteams=$(hexteams "$owner" "$user")
  test -z "$hexteams" && return
  none="`echo なし|hexize`"
  cat<<-EOF
	<!-- <div class="fold">
	`cgi_checkbox mv2team send id="mv2team"`<label
	 for="mv2team">この話題を以下のチームのものにする</label>
	<div> -->
	<p>この話題をチーム所有にする:
	チーム: `cgi_select_h mv2team $none $hexteams`</p>
	</form></div></div>
	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`
  math=`getvalbyid blog mathjax $blogrowid`
  ## 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
  printf '%s' "${math:+$mathjax}" >> $tmpout
  # 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`
  rowid=${rowid%%[!0-9]*}	# Cleaning
  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"`
  myuid=`query "SELECT rowid FROM user WHERE name='$user';"`
  mailfrom=`email4groupbyuid "$grp" "$myuid" | sed -e 1q -e 's/[ ,].*//'`
  mailfrom="`gecos "$user"` <$mailfrom>"
  sj="グループ「$grp」宛メッセージ(from `gecos $user`)"
  msg=$(cat<<-EOF
	$urlbase?grp+$rowid
	グループ $grp に所属する
	`gecos $user` さんよりメッセージ:

	$message
	EOF
	)
  # smail rcpt subj (file)
  for m in $members; do
    echo "$msg" |
      MAIL_FROM=$mailfrom \
	       SENDER=$noreply \
	       REPLYTO=$mailfrom \
	       smail "$m" "$sj"
  done
  cat<<EOF
<p>以下のユーザに送信しました。</p>
<pre>
`collectgecosesbyid "$rowid" | sed 's/$/ さん/'`
</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 adduser`
  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="に
$t_usr
`[ x$1 = xyes ] && echo 'を追加' || echo 'の解除操作を'`
しました。"
  echo "$t_grp$nl$body$nl$nl$url?grp+$gid" | smail "$rcpts" "joingrp $1"
  query "delete from session where id='$2';"
  echo "グループ <a href=\"?grp+$gid\">$t_grp</a>$nl$body" | html p
}

joingrprequest() {
  # $1=group $2=user $3=yes/no $4=email(if any $5=AsAdmin) 
  jss="joingrp-`date +%s`-`genrandom 12`"
  gecos=`gecos "$user"`
  addsession $jss +${memoplimitdays}days
  grpadmins=`getgroupadmins "$1"`
  query "replace into par values('$jss', 'group', 'string', `sqlquote \"$1\"`),
('$jss', 'adduser', 'string', `sqlquote \"$user\"`);"
  smail "$(collectemail $grpadmins)" "Join request to $1"<<EOF
$url
$user ${gecos:+($gecos)}さんから
グループ $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"`
  grid=`query "SELECT rowid FROM grp WHERE gname=$qgname;"`
  cond="where gname=$qgname and user='$2'"
  if [ x"$3" = x"yes" ]; then
    query "replace into grp_mem values($qgname, '$2');"
    # Notify joingrp to admin
    action="に加入しました。"
    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 "begin;
	delete from grp_mem $cond;
	delete from grp_mem_s $cond;
	delete from grp_mem_m $cond;
	delete from grp_adm $cond;
	delete from grp_adm_s $cond;
	delete from grp_adm_m $cond;
	end;"
    action="から脱退しました。"
  fi
  smail_queue "$(collectemail `getgroupadmins $1`)" "Member change of $1"<<-EOF
	$url?grp+$grid
    	$user (`gecos $user`)さんが
	グループ $1
	$action
	EOF
}
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
  done
  showgroup $grid
}
dt_colhack() {
  # FROM: <TD>xxx:::yyy</TD>
  # TO:   <TD class="xxx">yyy</TD>
  sed -Ee 's,<TD>([^:<"]+)'$asdelim'([^<]*)(</TD>|$),<TD class="\1">\2\3,g'
}
dt_rowhack() {
  # From: <TR>
  #        ....
  #        <TD>rowclass=foo</TD>
  #        </TR>
  # To:   <TR class="foo">....<TD>foo</TD></TR>
  sed -e '
	/^<TR>/ {
	  :loop
	  s/\n//
	  N
	  /<\/TR>/ {
	    s/\n//
	    s,^<TR>\(.*\)<TD\([^>]*\)>rowclass=\(.*\)\(</TD></TR>\),<TR class="\3">\1<TD\2>\3\4,
	    n
	  }
	  $q
	  b loop
	}'
}
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
  sqlfile=$tmpd/dump.sql
  : > $sqlfile			# ensure to be empty
  printf '.mode html\n.header 1\n' > $sqlfile
  # $DT_CHLD=ChildTable:BindColumn (eg. article:blogid)
  if [ -n "$DT_CHLD" ]; then
    _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}
    cat<<-EOF >> $sqlfile
	-- presql
	CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS
	SELECT * FROM acclog WHERE user='$user' and tbl='$2';
	EOF
    # Speed up counting of new articles
    dt_count=${DT_COUNT:+"CREATE TEMPORARY VIEW _dtcount AS
	  SELECT a.id $_i, $DT_COUNT, coalesce(cnt2, 0) cnt2
	  FROM (SELECT DISTINCT id FROM _target) a
	    LEFT JOIN
	       (SELECT blogid,$DT_COUNT,count($DT_COUNT) cnt2
	        FROM $_t
	        WHERE $DT_COUNT = '$DT_COUNT_BY'
	        GROUP BY $_i,$DT_COUNT) b
	    ON a.id=b.$_i
		;"}
    cat<<-EOF >> $sqlfile
	-- presql2
	DROP TABLE IF EXISTS _counts;
	CREATE TEMPORARY TABLE _counts AS
	  SELECT $_i, count($_i) cnt
	  FROM $_t GROUP BY $_i;
	/* Prepare NEW count table */
	CREATE TEMPORARY TABLE _target AS
	  SELECT b.rowid trowid, b.id
	    FROM "$2" b JOIN "$2_s" s
	      ON b.id=s.id AND s.key='owner'
	      ${DT_QOWNER:+ AND s.val=$DT_QOWNER};

	DROP TABLE IF EXISTS _children;
	CREATE TEMPORARY TABLE _children AS
	  SELECT a.trowid trowid, $_i, a.id, s.val ctime
	  FROM   (SELECT t.trowid, t.id $_i, a.id
          	  FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a
          LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';

	DROP TABLE IF EXISTS _news;
	DROP VIEW IF EXISTS _news;

	-- CREATE TEMPORARY TABLE _news($_i, newcnt);
	-- INSERT INTO _news
	/* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */
	CREATE TEMPORARY VIEW _news AS
    	  SELECT a.id $_i, coalesce(newcnt, 0) newcnt
    	  FROM   (SELECT DISTINCT id FROM _target)
	      a LEFT JOIN
	      	 (SELECT $_i, count(ctime) newcnt
		  FROM  _children x
		  WHERE ctime > coalesce((SELECT time from myacclog
                                          WHERE tblrowid=x.trowid),
                               	         '1970-01-01')
                  GROUP BY $_i) b
          ON a.id=b.$_i;
	$dt_count
	EOF
    cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"
    cntnew="(SELECT newcnt FROM _news where $_i=a.id)"
    cntmine=${DT_COUNT:+"(SELECT cnt2 FROM _dtcount WHERE $_i=a.id)"}
    cnt="$cntnew as '新着', $cntall as '総数', "
    cnt=$cnt${DT_COUNT:+"$cntmine as '$DT_COUNT_HEADER',"}
    dt_class=" td2r td3r td4r dumpblogs"
  fi
  # Construct join expression
  eav="" scols=""
  pk=`gettblpkey $2`
  substr=${dumpcollen:+"substr(%s, 0, $dumpcollen)"}
  substr=${substr:-%s}
  for col in ${3:-`gettbl_s_cols $2`}; do
    valvar=val
    fromtbl=b
    if gettblcols "$2" | grep -w "$col" >/dev/null 2>&1; then
      # If $col belongs to master table
      fromtbl=a; col=${col#a.}
    fi
    case $col in
      gecos)	scols="$scols${scols:+, }${col#}"
		continue ;;	# built-in column name
      *:*)	as=${col#*:}	# as can be 稼動状態:frozen=凍結中
		col=${col%%:*}	# stage:稼動状態:frozen=凍結中 -> stage
		case "$as" in
		  *:*=*) cnd=${as#*:}
			 h=${cnd%%=*} v=${cnd#*=}
			 h=`sqlquotestr "$h"`
			 v=`sqlquotestr "$v"`
			 if [ x"$fromtbl" = x"b" ]; then
			   valvar="CASE val WHEN $h THEN $v END"
			 else
			   valvar="$h"
			 fi
			 as=${as%%:*} ;;
		esac
		;;
      *)	as=${col} ;;
    esac
    ss=`printf "$substr" "$valvar"`
    if [ x"$fromtbl" = x"b" ]; then
      eav=$eav${eav:+,}" \"$as$asdelim\"||coalesce(max(case key when '$col' then $ss end), '') as $as"
    else
      eav=$eav${eav:+,}" \"$as$asdelim\"||$ss as $as"
    fi
    scols="$scols${scols:+, }${fromtbl}.$as"
  done
#case author when '$user' then a.rowid else '---' end as ID,
  if [ -n "$DT_SQL" ]; then
    echo "$DT_SQL"
  else
    cat<<-EOF
	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;
	EOF
  fi >> $sqlfile
  ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"
  sqlog<<-EOF
	*** SQL-file: $sqlfile ***
	`cat $sqlfile`
	EOF
  printf '.mode list\n.header 0\n' >> $sqlfile
  cat<<EOF | sed "s,\(<TR><TD>\)\([1-9][0-9]*\)\(#[0-9a-fxs]*\)*</TD>,\1$elink$dvlink</TD>," | dt_colhack | dt_rowhack
<div> <!-- for folding by check button (s4-funcs.sh:dumptable()) -->
<div class="dumptable">
<table class="b$dt_class">
`query ".read $sqlfile"`
</table>
</div> <!-- dumptable -->
</div> <!-- for folding by check button (s4-funcs.sh:dumptable()) -->
EOF
}

clean_orphaned() {
  # This shoud be done by foreign_key rules, but some db lack them
  query<<-EOF
	-- Find blogs that have no parent
	WITH orphanedblog AS (
	  SELECT blog.id,val FROM blog JOIN blog_s bs
	         ON blog.id=bs.id AND key='owner'
              WHERE val NOT IN (SELECT gname FROM grp)
                AND val NOT IN (SELECT name FROM user)
	) -- Remove them
	DELETE FROM blog WHERE id IN (SELECT id FROM orphanedblog);

	-- Find articles that have no parent blog
	WITH orphanedarticle AS (
	  SELECT id FROM article
	  WHERE  blogid NOT IN (SELECT id FROM blog)
	) -- Remove them
	DELETE FROM article WHERE id IN (SELECT id FROM orphanedarticle);
	EOF
}

clearcachedir() (
  td=`getcachedir "$1"`
  err td=$td: ls- `ls $td`
  if [ -w "$td/image.1" ]; then
    err Removing td=$td
    rm -fr $td/		# Clear icon-image cache!
  fi
)

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
  #	9: Duplicated post
  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
    elif [ x"$tbl" = x"blog" ]; then
      # Check if owner in blog_s
      blogowner=`getvalbyid blog owner "$rowid"`
      if isgrpowner "$user" "$blogowner"; then
	rowowner=$user
	origauthor=`getvalbyid blog author $rowid`
	if [ -n "$origauthor" -a x"$user" != x"$origauthor" ];
	then					# Keep original author
	  setpar author string "$origauthor"	# if differs from $user
	fi					# 2021-11-06 suggd.by Ruri
      else
	rowowner=`query "SELECT author FROM $tbl WHERE rowid=$rowid;"`
      fi
    else
      # 2016-12-05 There's no owner column in $tbl (need confirmation)
      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;"
	clearcachedir "$tbl/$rowid"
	if [ x"$tbl" = x"grp" -o x"$tbl" = x"blog" ]; then
	  clean_orphaned
	fi
	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
    # XXX: WORK-AROUND FOR SOME STUPID BROWSER
    #      Avoid empty repost of article.
    if [ x"$tbl" = x"article" ]; then
      # If rowid is empty and ID exists in article-table, that is REPOST!
      aid=`getpar id`
      xaid=`query "SELECT id FROM $tbl WHERE id='$aid';"`
      if [ -n "$xaid" ]; then
	# REPOST of article
	html p <<-EOF
	書き込み直後のリロードなので上書きを回避します。
	最新記事は末尾の「再読み込み」ボタンから見てください。
	EOF
	err "Repost aid=$aid Browser=[$HTTP_USER_AGENT] user=$user"
	return 9			# STOP Duplicated posting
      fi
    fi
    # 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
     
  transaction=$tmpd/sqlfile.sql; touch $transaction
  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.%' AND sessid='$session';"`; 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`
		  if [ ! -s "$file" ]; then	# Maybe stupid REPOST
		    err "Empty REPOST by [$HTTP_USER_AGENT] user=$user"
		    continue
		  fi
		  ## 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' AND sessid='$session')"
		  ;;
	      esac
	      ;;
	    mv)
	      # regularize filename and strip directory part
	      newname=`getpar mv.$v|tr -d '":;#<>?^%$!'|tr -d "'"|tr ' ' _`
	      newname=`basename $newname`
	      oldname=`query "SELECT val FROM $tb2 WHERE rowid=$st_rowid;"`
	      oldext=`expr "$oldname" : '.*\.\(.*\)'`
	      newext=`expr "$newname" : '.*\.\(.*\)'`
	      err "p2t(mv): oldname=$oldname $oldext -> newname($v)=$newname $newext"
	      if [ -n "$newname" -a x"$oldext" = x"$newext" ];
	      then
		newsql="UPDATE $tb2 SET val='$newname'"
	      else
		html p<<-EOF
		$newname は取り扱えないファイル名です。
		空白を含まない名前にして下さい。
		拡張子の変更もできません。
		EOF
		continue
	      fi
	      ;;
	    *)			# maybe "keep", do not modify value
	      continue
	      ;;
	  esac
	  # err newsql=$newsql
	  sql=$sql$nl"$newsql where rowid=$st_rowid;"
	  clearcachedir "$tbl/$rowid"
	done

	if [ x"$bin" = x"NULL" ]; then
	  ## err repl:normal sql=`echo $sql`
	  if [ -n "$transaction" ]; then
	    cat<<-EOF >> $transaction
		$sql
		DELETE FROM $tb2 WHERE type='string' AND val='';
		EOF
	  else
	    query "$sql
delete from $tb2   where type='string' and val='';"
	    ## err repl:normal done
	  fi
	else
	  # Binary update line is TOO LONG to pipelining
	  sqlfile="$tmpd/sqlf.$$"
	  if [ -n "$transaction" ]; then
	    cat<<-EOF >> $transaction
		$sql
		EOF
	  else
	    cat<<-EOF > $sqlfile
		$sql
		EOF
	    query ".read $sqlfile"
	  fi
	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"`
	##XXX [ -z "$val" -o x"$val" = x'""' -o x"$val" = x"NULL" ] && continue
	[ -z "$val" -o x"$val" = x'""' ] && continue
	## err $col=$val
	bin=NULL
	## err partype$col=`getpartype $col "$limit"`
	ptype=`getpartype $col "$limit"` # partype should be obtained each time
        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
	  [Cc][Hh][Ee][Cc][Kk][Bb][Oo][Xx]|[Tt][Ee][Xx][Tt])
	    test x"$val" = x"NULL" && val="''"
	    ;;
	  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);"
	if [ x"$val" = x"NULL" -a x"$bin" = x"NULL" ]; then
	  continue	# Do not insert completely NULL record 2021-11-08
	fi
	sql="REPLACE into $tb2 values($pval, \"$col\", $type, $val, $bin);"
	if [ x"$bin" = x"NULL" ]; then
	  ## err Normal-query: `echo $sql`
	  if [ -n "$transaction" ]; then
	    printf '%s' "$sql" >> $transaction
	  else
	    query "$sql"
	  fi
	else
	  sqlfile="$tmpd/query.$$"
	  ## err sqlfile=`ls -lF $sqlfile`
	  if [ -n "$transaction" ]; then
	    cat<<-EOF >> $transaction
		$sql
		EOF
	  else
	    cat<<-EOF >> $sqlfile
		$sql
		EOF
	    query ".read $sqlfile"
	  fi
	fi
	## err p2t done
      done
    done
  done
  [ -n "$transaction" -a -s "$transaction" ] && cat <<-EOF | query
	-- We cannot use transaction here, because groupupdate may use it.
	SAVEPOINT pa2table_insert;
	.read $transaction
	RELEASE SAVEPOINT pa2table_insert;
	EOF
  rc=$?
  [ $rc -eq 0 -a x"$tbl" = x"user" ] && flag_profupdate
  ## err "Table:$tbl update done "
  return $rc
)
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 -r 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=`printf '%s\n' "$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=$(printf '%s\n' "$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])
	checked=${val:+ checked}
	form="<label><input type=\"checkbox\" name=\"$name\" value=\"${args#*=}\"$checked>${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>"
			   iconhref2 "$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">
	EOF
  test -n "$rowid" && printf '%s\n' "$rm" # Workaround for utf8 buggy NetBSD sh
  cat<<EOF
 <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/*|application/csv|application/json)
      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"
	nkf -e $bin | htmlescape | nkf --oc="$charset" \
	    | 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}"
      ;;
    video/*)
      if [ -z "$3" ]; then
	_m4 -D_TITLE_="$fn" \
	    -D_SRC_="?showattc+$1+$2+raw" $layout/videoplay.m4.html
	exit $?
      fi
  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=\"?\""
}