yuuji@734: #!/bin/sh
yuuji@734: 
yuuji@817: autoremovestop=2
yuuji@817: 
yuuji@791: # Setup variables for running time
yuuji@791: if $isCGI; then
yuuji@791:   case "$S4WORLDLIST" in
yuuji@791:     *:*:*)
yuuji@791:       worldlistfile=cache/worldlist
yuuji@797:       worldgrpfile=cache/worldgrps
yuuji@791:       worldoptionfile=cache/worldoption
yuuji@791:       worldnamefile=cache/worldname
yuuji@791:       if [ ! -e $worldlistfile -o $worldlistfile -ot s4-config.sh \
yuuji@791: 	     -o ! -e $worldoptionfile -o $worldoptionfile -ot s4-config.sh \
yuuji@794: 	     -o s4-world.sh -nt $worldoptionfile ]
yuuji@791:       then
yuuji@791: 	echo S4MASTERURL=$S4MASTERURL >> tmp/debug.out
yuuji@791: 	cat <<-EOF > $worldlistfile
yuuji@797: 	<div><table>
yuuji@791: 	 <tr><th>World List</th></tr>
yuuji@794: 	 <tr><td title="Base World$nl拠点となるWorldです">&rArr;
yuuji@794: 	  <a href="${S4MASTERURL:-$URL}">Base</a></td></tr>
yuuji@791: 	EOF
yuuji@791: 	true > $worldoptionfile
yuuji@791: 	for i in $S4WORLDLIST; do
yuuji@791: 	  echo $i | {
yuuji@791: 	    IFS=: read name short d
yuuji@791: 	    cgi="s4-world-$short$cgiext"
yuuji@791: 	    conf="s4-config-$short.sh"
yuuji@791: 	    cat<<-EOF >>$worldlistfile
yuuji@791: 	    <tr><td title="$d">&rArr; <a href="$cgi">$name</a></td></tr>
yuuji@791: 		EOF
yuuji@791: 	    cat<<-EOF >>$worldoptionfile
yuuji@791: 	    <option title="$d" value="$conf">$name</option>
yuuji@791: 		EOF
yuuji@791: 	    echo "$name" > $worldnamefile.$short
yuuji@791: 	  }
yuuji@791: 	done
yuuji@791: 	if [ -s "$worldoptionfile" ]; then
yuuji@791: 	  echo "<option value=\"s4-config.sh\">Base</option>" >> $worldoptionfile
yuuji@791: 	  echo "</table>$nl</div>" >> $worldlistfile
yuuji@797: 	  sed 's/href="\([^>]*\)"/href="\1?grps"/' $worldlistfile \
yuuji@797: 	      > $worldgrpfile
yuuji@791: 	else
yuuji@791: 	  true > $worldoptionfile; true > $worldlistfile	# Remove contents
yuuji@791: 	  rm -f ${worldnamefile}.*
yuuji@791: 	fi
yuuji@791:       fi
yuuji@791:       if [ -s "$worldlistfile" ]; then
yuuji@797: 	S4WORLDS="▼spaste(\`$worldlistfile')"
yuuji@791: 	S4WORLDNAME=${S4WORLD:+`cat $worldnamefile.$S4WORLD`}
yuuji@797: 	S4WORLDGRPS="▼spaste(\`$worldgrpfile')"
yuuji@791:       fi
yuuji@791:       ;;
yuuji@791:   esac
yuuji@791: fi
yuuji@791: 
yuuji@734: 
yuuji@817: syncaccount_1() {
yuuji@817: 
yuuji@817:   n_m=`query "SELECT printf('%d:%d',\
yuuji@817:   	      (SELECT count(*) FROM m.user), \
yuuji@817: 	      (SELECT count(*) FROM user));"`
yuuji@817:   n=${n_m%:*}; m=${n_m#*:}		# n:m
yuuji@817:   if [ -z "$n" -o "$n" -lt 2 ]; then
yuuji@817:     err "Skipping account sync because m.user cannot be reached[n=$n]."
yuuji@817:     return -3
yuuji@794:   fi
yuuji@817:   if [ -z "$forceusersync" -a ! -f db/forceusersync ]; then
yuuji@817:     if [ $((m-n)) -le $((autoremovestop+0)) ]; then
yuuji@817:       # If if-condition evaluation fails, fall through to else-caluse
yuuji@817:       rm -f db/forceusersync
yuuji@817:     else
yuuji@817:       err "More than $autoremovestop users vanished($((m-n)))."
yuuji@817:       err "Automatic removal canceled.  If you want to sync user accounts"
yuuji@817:       err "forcibly, touch db/forceusersync file each time of world update."
yuuji@817:       return -4
yuuji@817:     fi
yuuji@817:   fi
yuuji@885:   err "`gdate +%S.%3N` Starting account synchronization"
yuuji@887:   SYNC_M=1
yuuji@742: 
yuuji@835:   prevsync=`tail -1 $syncflag|colrm 20`	# 2020-06-21 12:30:00 = 19cols
yuuji@835:   syncall=${db%.*}.syncall
yuuji@835:   err syncallfile=$syncall
yuuji@835:   if [ -e $syncall ]; then
yuuji@835:     rm -f "$syncall"
yuuji@835:     err "Force update user_m for all users"
yuuji@835:   else
yuuji@835:     case "$prevsync" in
yuuji@835:       [2-9][0-9][0-9][0-9]-[01][0-9]-[0-3][0-9]\ [012][0-9]:??:??)
yuuji@881:       SYNCCOND="WHERE name in (SELECT name FROM m.user_s WHERE key='profupdate' AND val > '$prevsync')"
yuuji@880:       err "prevsync=$prevsync"
yuuji@880:       limited=`query "SELECT DISTINCT name FROM m.user_s $SYNCCOND;"`
yuuji@880:       if [ -n "$limited" ]; then
yuuji@880: 	err synccond limited to "$limited"
yuuji@880:       else
yuuji@880: 	err synccond canceled for user_m
yuuji@880: 	SYNCCOND="WHERE NULL"
yuuji@887: 	SYNC_M=
yuuji@880:       fi
yuuji@835:       err "Touch $syncall (owner=`id -un`) to update all user_m."
yuuji@835:       ;;
yuuji@835:       *)
yuuji@835: 	echo arere ;;
yuuji@835:     esac
yuuji@835:   fi
yuuji@817: ##  num=$(sqlite3 -bail -cmd 'PRAGMA FOREIGN_KEYS=on' $db <<EOF
yuuji@903:   result=$(query <<EOF
yuuji@734: BEGIN;
yuuji@734: DElETE FROM main.user WHERE rowid NOT IN (SELECT rowid FROM m.user);
yuuji@734: INSERT INTO main.user(rowid, name)
yuuji@734:        SELECT rowid, name FROM m.user
yuuji@734:        WHERE m.user.rowid NOT IN (SELECT rowid FROM user);
yuuji@904: /* PrimaryKey:name cannot be changed, but ensure the integrity of user */
yuuji@904: UPDATE user SET name = (SELECT name FROM m.user WHERE main.user.rowid=m.user.rowid) WHERE name != (SELECT name from m.user WHERE main.user.rowid=m.user.rowid);
yuuji@742: DELETE FROM main.user_s WHERE rowid NOT IN (SELECT rowid FROM m.user_s);
yuuji@742: REPLACE INTO main.user_s(rowid, name, key, type, val, bin)
yuuji@734: 	SELECT rowid,* FROM m.user_s;
yuuji@887: ${SYNC_M:+
yuuji@742: DELETE FROM main.user_m WHERE rowid NOT IN (SELECT rowid FROM m.user_m);
yuuji@742: REPLACE INTO main.user_m(rowid, name, key, type, val, bin)
yuuji@835: 	SELECT rowid,* FROM m.user_m    $SYNCCOND
yuuji@834: 	   EXCEPT
yuuji@835: 	SELECT rowid,* FROM main.user_m $SYNCCOND
yuuji@887: 	;} /* SYNC_M closed here */
yuuji@903: SELECT 'OK';
yuuji@734: END;
yuuji@903: EOF
yuuji@903: 	)
yuuji@903:   err "`gdate +%S.%3N` Update account tables finished with result=[$result]"
yuuji@903:   test x"$result" = x"OK" && num=$(query <<EOF
yuuji@734: /* Compare user tables */
yuuji@734: WITH master AS (
yuuji@734:   SELECT p.rowid,* FROM m.user p
yuuji@887:   			 NATURAL LEFT JOIN m.user_s
yuuji@887: 	 ${SYNC_M:+	 NATURAL LEFT JOIN m.user_m}
yuuji@903:   $SYNCCOND
yuuji@743: ), thisworld AS (
yuuji@734:   SELECT p.rowid,* FROM user p
yuuji@887: 		  	NATURAL LEFT JOIN user_s
yuuji@887:  	 ${SYNC_M:+	NATURAL LEFT JOIN user_m}
yuuji@903:   $SYNCCOND
yuuji@734: ), m_a AS (
yuuji@743:   SELECT * FROM master EXCEPT SELECT * FROM thisworld
yuuji@734: ), a_m AS (
yuuji@743:   SELECT * FROM thisworld EXCEPT SELECT * FROM master
yuuji@734: ) SELECT (SELECT count(*) FROM m_a) + (SELECT count(*) FROM a_m);
yuuji@734: EOF
yuuji@817:      )
yuuji@817:   if [ -n "$num" -a "$num" -eq 0 ]; then
yuuji@885:     err "`gdate +%S.%3N` Account synchronization done in difference $num"
yuuji@817:     echo "`date '+%F %T'`: Sync done by process $$" >> $syncflag
yuuji@817:   else
yuuji@885:     err "Account synch failed or bailed with num=[$num]"
yuuji@817:   fi
yuuji@817:   return $num
yuuji@817: }
yuuji@817: 
yuuji@817: syncaccount() {
yuuji@817:   forceusersync=$1
yuuji@859:   ## err "db=$db mas=$S4MASTERDB sessdb=$sessdb"
yuuji@817:   # If in parent world, no need to do rest of jobs
yuuji@817:   if [ -z "$S4MASTERDB" -o ! -s "$S4MASTERDB" ]; then
yuuji@817:     return
yuuji@817:   fi
yuuji@817:   # Confim child
yuuji@817:   if [ "$db" -ef "$S4MASTERDB" ]; then
yuuji@817:     return		# Points to the same file
yuuji@817:   fi
yuuji@817:   # File based sync check precedes to DB count check for performance issue
yuuji@817:   syncflag=${db%.*}.synctime
yuuji@817:   runflag=${db%.*}.run
yuuji@817:   userupdateflag=`dirname $S4MASTERDB`/`basename $userupdateflag`
yuuji@817:   test ! -e "$userupdateflag"			&& return
yuuji@817:   [ -z "$forceusersync" -a "$syncflag" -nt "$userupdateflag" ] && return
yuuji@817:   if [ -s "$runflag" ]; then
yuuji@817:     limit=`cat $runflag|tr -c -d 0-9`
yuuji@892:     err syncaccount: limit=$limit
yuuji@817:     if [ -n "$limit" -a "$limit" -gt `date +%s` ]; then
yuuji@817:       err "World $S4WORLD account sync withholded by process $$"
yuuji@817:       return		# Running sync by other process not leaching timeout
yuuji@817:     fi
yuuji@817:   fi
yuuji@817:   echo $((`date +%s` + 10)) > $runflag	# Setting running flag by timeout 10s
yuuji@892:   err syncaccount: setrunflag[$runflag]=`cat $runflag`
yuuji@817:   query<<-EOF
yuuji@817: 	.bail ON
yuuji@817: 	ATTACH DATABASE "$S4MASTERDB" AS m;
yuuji@817: 	EOF
yuuji@817:   syncaccount_1
yuuji@817:   rc=$?
yuuji@817:   test -e "$runflag" && rm -f "$runflag"
yuuji@817:   query<<-EOF
yuuji@817: 	DETACH DATABASE m;
yuuji@817: 	.bail OFF
yuuji@817: 	EOF
yuuji@817:   return $rc
yuuji@817: }
yuuji@825: 
yuuji@825: getworldDB() {	# $1=conf
yuuji@825:   (unset DB; . "$1"; echo ${DB:-$defaultdb})
yuuji@825: }
yuuji@825: 
yuuji@825: worldnameDBlist() {
yuuji@825:   echo Base:Base:`getworldDB ./s4-config.sh`
yuuji@825:   for w in $S4WORLDLIST; do
yuuji@825:     case "$w" in
yuuji@825:       *:*:*:*)	continue ;;
yuuji@825:       *:*:*)	w=${w%:*}; name=${w%:*}; world=${w#*:} ;;
yuuji@825:       *)	continue ;;
yuuji@825:     esac
yuuji@825:     d=`getworldDB "./s4-config-$world.sh"`
yuuji@825:     test -s "$d" && echo ${world}:${name}:$d
yuuji@825:   done
yuuji@825: }
yuuji@825: 
yuuji@828: grepgrpworld() {
yuuji@828:   world=$1; wname=$2; exp=$3; tdb=$4
yuuji@825:   case $world in
yuuji@827:     Base)	cgi=${S4MASTERURL:-$URL} ;;
yuuji@825:     *)		cgi=s4-world-$world$cgiext ;;
yuuji@825:   esac
yuuji@827:   case $exp in
yuuji@828:     mem:*)
yuuji@828:       arg=${exp#*:}
yuuji@828:       cond="user = '$arg'"
yuuji@828:       guide="「${S4WORLDNAME:-Base}」以外の世界の所属グループ"
yuuji@828:       s="(<a href=\"$cgi?stage=grps&kwd=mem:$arg\">所属絞</a>)" ;;
yuuji@828:     *)
yuuji@828:       arg=`sqlquote "%$3%"`
yuuji@828:       cond="gname LIKE $arg"
yuuji@828:       guide="$exp を含むグループは別世界にもあります"
yuuji@828:       s="(<a href=\"$cgi?stage=grps&kwd=$exp\">絞込</a>)" ;;
yuuji@827:   esac
yuuji@825:   query <<-EOF |
yuuji@828: 	ATTACH DATABASE "$tdb" AS td;
yuuji@825: 	SELECT DISTINCT td.grp.rowid,hex(gname)
yuuji@825: 	FROM td.grp NATURAL JOIN td.grp_mem
yuuji@825: 	WHERE $cond;
yuuji@825: 	DETACH DATABASE td;
yuuji@825: 	EOF
yuuji@825:   while IFS='|' read rowid hgname; do
yuuji@825:     # echo rowid=$rowid - `echo "$hgname"|unhexize|htmlescape`
yuuji@825:     htmlgn=`echo $hgname|unhexize|htmlescape`
yuuji@825:     printf '<a href="%s?grp+%d">%s</a> ' "$cgi" "$rowid" "$htmlgn"
yuuji@825:   done | {
yuuji@825:     read ans
yuuji@825:     if [ -n "$ans" ]; then
yuuji@825:       w=`echo $wname|htmlescape`
yuuji@826:       u="<a href=\"$cgi?grps\"><span class=\"pre\">$w</span></a>"
yuuji@825:       cat<<-EOF
yuuji@828: 	GUIDE:<h2>`echo "$guide"|htmlescape`</h2>
yuuji@825: 	<tr>
yuuji@826: 	 <tr><td>$u $s</td>
yuuji@825: 	 <td>$ans</td>
yuuji@825: 	</dl>
yuuji@825: 	EOF
yuuji@825:     fi
yuuji@825:   }
yuuji@828: }
yuuji@825: 
yuuji@825: peekgrpworlds() (
yuuji@828:   # $1=(Pattern|mem:User)
yuuji@828:   # err "pgw-1=[$1]"
yuuji@825:   for wd in `worldnameDBlist`; do
yuuji@825:     world=${wd%%:*}; wd=${wd#*:}
yuuji@825:     worldname=${wd%:*}
yuuji@825:     d=${wd#*:}
yuuji@825:     if [ ! $db -ef $d -a -s $d ]; then
yuuji@827:       grepgrpworld "$world" "$worldname" "$1" "$d"
yuuji@825:     fi
yuuji@825:   done | {
yuuji@825:     result=`cat`
yuuji@825:     if [ -n "$result" ]; then
yuuji@828:       cat<<-EOF
yuuji@828: 	`echo "$result"|sed 's/^GUIDE://;2q'`
yuuji@828: 	<table class="b">
yuuji@828: 	 `echo "$result"|grep -v '^GUIDE:'`
yuuji@828: 	</table>
yuuji@828: 	EOF
yuuji@825:     fi
yuuji@825:   }
yuuji@825: )