# HG changeset patch
# User HIROSE Yuuji <yuuji@gentei.org>
# Date 1555902196 -32400
# Node ID 3cb5c360e1bf8c9719f71c2c881deb85f7a0c245
# Parent  5b911fee7e1b11730418466143e4c804812967e4
Try to speed up user-home summary construction.

diff -r 5b911fee7e1b -r 3cb5c360e1bf s4-funcs.sh
--- a/s4-funcs.sh	Mon Apr 22 12:02:33 2019 +0900
+++ b/s4-funcs.sh	Mon Apr 22 12:03:16 2019 +0900
@@ -381,7 +381,8 @@
 # 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 ".once $sqo" >&5
+  echo ".output $sqo" >&5
   logstart
   if [ -z "$1" ]; then
     tee -a $querylog
@@ -389,6 +390,7 @@
     echo "$@" >> $querylog
     echo "$@"
   fi  >&5
+  echo ".output stdout" >&5
   cat $sqo
   logend
 }
@@ -1876,7 +1878,12 @@
   fi
   nblog=`query "SELECT count(id) FROM blog_s WHERE key='owner' AND \
 		val='$uname';"`
+  ## REMOVE This comment block until 2019/7/1
+  ## err "----- `gdate +%FT%T.%3N` ------------C"
+  ## [ x"$user" = x'yuuji@gentei.org' ] && ddd=1
   listblog $uname			> $bf
+  ## unset ddd
+  ## err "----- `gdate +%FT%T.%3N` ------------D"
 
   hometail=$tmpd/tail.$$
   mkfifo $hometail
@@ -3131,6 +3138,7 @@
   fi
   sqlfile=$tmpd/dump.sql
   : > $sqlfile			# ensure to be empty
+  printf '.mode html\n.header 1\n' > $sqlfile
   # $DT_CHLD=ChildTable:BindColumn
   if [ -n "$DT_CHLD" ]; then
     _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}
@@ -3142,6 +3150,7 @@
     # Speed up counting of new articles
     cat<<-EOF >> $sqlfile
 	-- presql2
+	DROP TABLE IF EXISTS _counts;
 	CREATE TEMPORARY TABLE _counts AS
 	  SELECT $_i, count($_i) cnt
 	  FROM $_t GROUP BY $_i;
@@ -3152,14 +3161,20 @@
 	      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';
 
-	CREATE TEMPORARY TABLE _news($_i, newcnt);
-	  INSERT INTO _news
+	-- ${ddd:+.system gdate +%T.%3N >> tmp/d1}
+	DROP TABLE IF EXISTS _news;
+
+	-- CREATE TEMPORARY TABLE _news($_i, newcnt);
+	--  INSERT INTO _news
+	/* **COMPARE** the efficiency of TEMP-TABLE and VIEW !!! */
+	CREATE VIEW _news AS (
     	  SELECT a.id, coalesce(newcnt, 0) newcnt
     	  FROM   (SELECT DISTINCT id FROM _target)
 	      a LEFT JOIN
@@ -3169,7 +3184,8 @@
                                           WHERE tblrowid=x.trowid),
                                	         '1970-01-01')
                   GROUP BY $_i) b
-          ON a.id=b.$_i;
+          ON a.id=b.$_i);
+	-- ${ddd:+.system gdate +%T.%3N >> tmp/d1}
 	EOF
     # REMOVE next line until 2019/5/1
     cntall="(select count($_i) from $_t where $_i=a.id)"
@@ -3232,15 +3248,30 @@
 	*** SQL-file: $sqlfile ***
 	`cat $sqlfile`
 	EOF
+  if [ "$ddd" ]; then	# REMOVE this block until 2019/7/1
+    rm -f tmp/sql; cat $sqlfile >> tmp/sql
+    err "----- `gdate +%FT%T.%3N` ------------555555aaaaa"
+    # query ".read $sqlfile" > $tmpd/foo
+    sqlite3 -header -cmd 'pragma foreign_keys=ON' $db ".read $sqlfile" > $tmpd/foo
+    cp $tmpd/foo tmp/
+    err "----- `gdate +%FT%T.%3N` ------------555555"
+    ## $ddd LINE exists at the end of this function
+  fi
+  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_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 ".read $sqlfile"`
+`query ".read $sqlfile"`
 </table>
 </div> <!-- dumptable -->
 </div> <!-- for folding by check button (s4-funcs.sh:dumptable()) -->
 EOF
+### `query ".read $sqlfile"`
+### `sq -header -cmd ".mode $1" $db ".read $sqlfile"`
+
+  ## REMOVE THIS!
+  [ "$ddd" ] && err "----- `gdate +%FT%T.%3N` ------------666666"
 }
 
 par2table() (