--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/s4-annex.sh	Wed Jun 03 21:32:10 2020 +0900
@@ -0,0 +1,71 @@
+#!/bin/sh
+
+if [ -z "$S4MASTERDB" -o ! -s "$S4MASTERDB" ]; then
+  return
+fi
+if [ "$db" -ef "$S4MASTERDB" ]; then
+  return		# Points to the same file
+fi
+
+skey="skey-`basename $mydir`"
+syncflag=$dbdir/usersynced
+userupdateflag=`dirname $S4MASTERDB`/`basename $userupdateflag`
+test ! -e "$userupdateflag"		&& return
+test "$syncflag" -nt "$userupdateflag"	&& return
+    
+#  for sub.sq3
+# 
+#  main: user: 'taro', 'hanako', 'shige'
+#  sub:  user: 'taro', 'hanako', 'shige'
+#  sub2: user_s: ('taro', 't'), ('hanako', 'h'), ('shige', 's')
+#  then update
+# 
+
+judgeequal() {
+  read num
+  test $num -eq 0 && touch $syncflag
+  return $num
+}
+
+## sqlite3 -cmd '.timer 1' -cmd '.echo 1' $db <<EOF
+err "Starting account synchronization"
+err "db=$db mas=$S4MASTERDB sess=$sessdb"
+sqlite3 -bail -cmd 'PRAGMA FOREIGN_KEYS=on' $db <<EOF | judgeequal
+ATTACH DATABASE "$S4MASTERDB" AS m;
+CREATE TABLE IF NOT EXISTS user(name, primary key(name));
+BEGIN;
+DElETE FROM main.user WHERE rowid NOT IN (SELECT rowid FROM m.user);
+INSERT INTO main.user(rowid, name)
+       SELECT rowid, name FROM m.user
+       WHERE m.user.rowid NOT IN (SELECT rowid FROM user);
+UPDATE user SET name = (SELECT name FROM m.user WHERE main.user.rowid=m.user.rowid);
+DELETE FROM main.user_s;
+INSERT INTO main.user_s(rowid, name, key, type, val, bin)
+	SELECT rowid,* FROM m.user_s;
+DELETE FROM main.user_m;
+INSERT INTO main.user_m(rowid, name, key, type, val, bin)
+	SELECT rowid,* FROM m.user_m
+	WHERE key NOT LIKE '%cache%';
+END;
+
+/* Compare user tables */
+WITH master AS (
+  SELECT p.rowid,* FROM m.user p
+  	 NATURAL LEFT JOIN m.user_s
+	 NATURAL LEFT JOIN m.user_m
+), annex AS (
+  SELECT p.rowid,* FROM user p
+  	 NATURAL LEFT JOIN user_s
+ 	 NATURAL LEFT JOIN user_m
+), m_a AS (
+  SELECT * FROM master EXCEPT SELECT * FROM annex
+), a_m AS (
+  SELECT * FROM annex EXCEPT SELECT * FROM master
+) SELECT (SELECT count(*) FROM m_a) + (SELECT count(*) FROM a_m);
+
+-- SELECT * FROM annex;
+-- SELECT rowid,* FROM m.user LIMIT 10;
+-- SELECT rowid,* FROM user WHERE rowid NOT IN (SELECT rowid FROM m.user);
+DETACH DATABASE m;
+EOF
+err Done

--- a/s4-funcs.sh	Tue Jun 02 08:35:16 2020 +0900
+++ b/s4-funcs.sh	Wed Jun 03 21:32:10 2020 +0900
@@ -12,12 +12,14 @@
 tmpdir=${TMPDIR:-tmp}
 dbdir=${DBDIR:-db}
 tmpfiles=""
-db=${DB:-$dbdir/cgi.sq3}
 querylog=${QUERYLOG:-$tmpdir/query.log}
 searchlog=${SEARCHLOG:-$tmpdir/search.log}
-workdb=$dbdir/tmpdata.sq3
+db=${DB:-$dbdir/cgi.sq3}
 sessdb=$dbdir/sess.sq3
+skey='skey'			# Changed in s4-annex, if ANNEX mode
+userupdateflag=$dbdir/userupdate
 sesstb=tmp.sess
+workdb=$dbdir/tmpdata.sq3
 listentlimit=${LISTENTLIMIT:-30}
 admin=${ADMIN:-hostmaster@example.org}
 noreply=${NOREPLY:-noreply@example.org}
@@ -62,6 +64,18 @@
 "
 likeesc=`printf '\037'`		# ESCAPE char of LIKE operator
 iconcachekey="profimgcache_S"
+
+# Start debug logging
+exec 3>> $tmpdir/debug.out
+err() {
+  echo "[`date +%F-%T%z`] $@" 1>&3
+}
+# If S4MASTERDB is set, behave as ANNEX s4
+if [ -n "$S4MASTERDB" -a -s "$S4MASTERDB" ]; then
+  . ./s4-annex.sh 2>> tmp/debug.out
+fi
+
+
 case "$HTTP_USER_AGENT" in
   *i[Pp]hone*|*[Aa]ndroid*)	touchpanel=1 ;;
   *)				touchpanel="" ;;
@@ -372,7 +386,6 @@
   if [ -n "$isCGI" ]; then
      exec 2>> $tmpdir/error.out
   fi
-  exec 3>> $tmpdir/debug.out
   exec 5> $sqi # Turning $sqi access through fd5 for continuous open state
   chmod o-r $tmpdir/error.out $tmpdir/debug.out
   rm $sqi
@@ -1197,7 +1210,7 @@
     v="`echo ${kv#*=}|nkf -Ww -mQ|sed -e 's/\"/\"\"/g'`"
     case "$k" in
       user)	_user="$v" ;;
-      skey)	_skey="$v" ;;
+      $skey)	_skey="$v" ;;
     esac
     query "replace into cookie values('$session', '$k', 'string', \"$v\");"
   done
@@ -1214,7 +1227,7 @@
   # smail rcpts subj (file)
   # $SMAIL_TO  <- Recipient value of To: header
   # $MAIL_FROM <- From: header value
-  from=`echo "${MAIL_FROM:-$admin}"|nkf -jM|tr -d '\n'`
+  from=`echo "${MAIL_FROM:-$admin}"|nkf -jM|tr : /|tr -d '\n'`
   rcpt=`echo $1|tr ' ' '\n'|sort -u|tr '\n' ' '` # uniq and strip newlines
   rcptheader=`echo $1|tr ' ' '\n'|sort -u|sed '2,$s/^/To: /g'`
   subj=`echo $2|nkf -jM|tr -d '\n'`
@@ -1304,7 +1317,7 @@
   # $1=passwd
   dbpswd=`getpwfield $user pswd`
   encpswd=`mycrypt "$1" "$dbpswd"`
-  ## err user=$user, pswd=$1, db=$dbpswd, enc=$encpswd
+  # err user=$user, pswd=$1, db=$dbpswd, enc=$encpswd
   [ x"$dbpswd" = x"$encpswd" ]
 }
 mypwhash() {
@@ -1357,7 +1370,8 @@
     newsession=`genrandom 34`
     if setskey "$user" "$newsession" &&
 	dbsetbyid user "$user" login "`date '+%F %T'`"; then
-      gencookie "user=$user" "skey=$newsession"
+      err gencookie "user=$user" "$skey=$newsession"
+      gencookie "user=$user" "$skey=$newsession"
       return 0
     else
       return 4		# Heavy load??
@@ -1397,9 +1411,6 @@
 trap cleanup INT HUP EXIT TERM PIPE
 # trap cleanup INT HUP
 
-err() {
-  echo "[`date +%F-%T%z`] $@" 1>&3
-}
 
 cgiinit() {
   session=`date +%F-$$`
@@ -3936,8 +3947,10 @@
 	.read $transaction
 	RELEASE SAVEPOINT pa2table_insert;
 	EOF
-  return $?
-  ##err donee
+  rc=$?
+  [ $rc -eq 0 -a x"$tbl" = x"user" ] && touch $userupdateflag
+  ## err "Table:$tbl update done "
+  return $rc
 )
 genform() {
   # $1 = form definition file