# HG changeset patch
# User HIROSE Yuuji <yuuji@gentei.org>
# Date 1555539859 -32400
# Node ID b87e1cbf34916e20ccbc00c82b403d5f2edba062
# Parent  b86a82ffe40726cd494266644ade0037716a3359
Make it faster to count all/new articles in group home.

diff -r b86a82ffe407 -r b87e1cbf3491 s4-funcs.sh
--- a/s4-funcs.sh	Wed Apr 17 13:50:07 2019 +0900
+++ b/s4-funcs.sh	Thu Apr 18 07:24:19 2019 +0900
@@ -328,6 +328,15 @@
 logend() {
   echo ">>>" >> ${1:-$querylog}
 }
+sqlog() {
+  logstart
+  if [ -z "$1" ]; then
+    cat >> $querylog
+  else
+    echo "$*" >> $querylog
+  fi
+  logend
+}
 sq() {
   # ./args.rb -cmd ".timeout 3000" "$@"
   logstart
@@ -2352,6 +2361,7 @@
   cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"
   colstate="state:稼動状態:frozen=rowclass=凍結"
   DT_CHLD=article:blogid \
+	 DT_QOWNER="$qgrp" \
 	 DT_VIEW=replyblog dumptable html blog \
 	 "ctime title heading team notify:通知$colmd $colstate" "$cond"
 
@@ -3119,18 +3129,57 @@
   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
   # $DT_CHLD=ChildTable:BindColumn
   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
+    cat<<-EOF >> $sqlfile
+	-- presql2
+	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' AND s.val=$DT_QOWNER;
+
+	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';
+
+	CREATE TEMPORARY TABLE _news($_i, newcnt);
+	  INSERT INTO _news
+    	  SELECT a.id, 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;
+	EOF
+    # REMOVE next line until 2019/5/1
     cntall="(select count($_i) from $_t where $_i=a.id)"
-    # XXX: Dirty workaround for slow subquery of acclog
-    presql="CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS
-            SELECT * FROM acclog WHERE user='$user' and tbl='$2';"
+    cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"
+    # REMOVE next assignment until 2019/5/1
     cntnew="(select count(val) from ${_t}_s where key='ctime' \
         and id in (select id from $_t where $_i=a.id) \
         and val > coalesce((select time from myacclog where \
                             tblrowid=a.rowid),\
                     '1970-01-01'))"
+    cntnew="(SELECT newcnt FROM _news where $_i=a.id)"
     cnt="$cntnew as '新着', $cntall as '総数',"
     dt_class=" td2r td3r dumpblogs"
   fi
@@ -3162,20 +3211,31 @@
     scols="$scols${scols:+, }b.$as"
   done
 #case author when '$user' then a.rowid else '---' end as ID,
-  sql=${DT_SQL:-"select \
-a.rowid as LINK,\
-$cnt\
-$scols from $2 a left join\
- (select $pk,$eav,
-	max(case key when 'owner'
-	 then (SELECT gecos FROM gecoses WHERE name=val) END) as gecos
- from ${2}_s c group by $pk) b on a.$pk=b.$pk $4;"}
-  ## err dt:SQL="`echo \"$presql$sql\"|tr -d '\n'`"
+  if [ -z "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
   cat<<EOF | sed "s,\(<TR><TD>\)\([1-9][0-9]*\)\(#[0-9a-fxs]*\)*</TD>,\1$elink$dvlink</TD>," | dt_rowhack
 <div> <!-- for folding by check button (s4-funcs.sh:dumptable()) -->
 <div class="dumptable">
 <table class="b$dt_class">
-`sq -header -cmd ".mode $1" $db "$presql$sql"`
+`sq -header -cmd ".mode $1" $db ".read $sqlfile"`
 </table>
 </div> <!-- dumptable -->
 </div> <!-- for folding by check button (s4-funcs.sh:dumptable()) -->