1.1 --- a/s4-funcs.sh	Wed Apr 17 13:50:07 2019 +0900
     1.2 +++ b/s4-funcs.sh	Thu Apr 18 07:24:19 2019 +0900
     1.3 @@ -328,6 +328,15 @@
     1.4  logend() {
     1.5    echo ">>>" >> ${1:-$querylog}
     1.6  }
     1.7 +sqlog() {
     1.8 +  logstart
     1.9 +  if [ -z "$1" ]; then
    1.10 +    cat >> $querylog
    1.11 +  else
    1.12 +    echo "$*" >> $querylog
    1.13 +  fi
    1.14 +  logend
    1.15 +}
    1.16  sq() {
    1.17    # ./args.rb -cmd ".timeout 3000" "$@"
    1.18    logstart
    1.19 @@ -2352,6 +2361,7 @@
    1.20    cond="where a.id in (select id from blog_s where key='owner' and val=$qgrp) order by ctime desc"
    1.21    colstate="state:稼動状態:frozen=rowclass=凍結"
    1.22    DT_CHLD=article:blogid \
    1.23 +	 DT_QOWNER="$qgrp" \
    1.24  	 DT_VIEW=replyblog dumptable html blog \
    1.25  	 "ctime title heading team notify:通知$colmd $colstate" "$cond"
    1.26  
    1.27 @@ -3119,18 +3129,57 @@
    1.28    if [ -n "$VIEW" ]; then
    1.29      dvlink=" <a href=\"$myname?$VIEW+\\2\\3\">VI</a><a href=\"$myname?$VIEW+\\2#bottom\">EW</a>"
    1.30    fi
    1.31 +  sqlfile=$tmpd/dump.sql
    1.32 +  : > $sqlfile			# ensure to be empty
    1.33    # $DT_CHLD=ChildTable:BindColumn
    1.34    if [ -n "$DT_CHLD" ]; then
    1.35      _t=${DT_CHLD%:*} _i=${DT_CHLD#*:}
    1.36 +    cat<<-EOF >> $sqlfile
    1.37 +	-- presql
    1.38 +	CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS
    1.39 +	SELECT * FROM acclog WHERE user='$user' and tbl='$2';
    1.40 +	EOF
    1.41 +    # Speed up counting of new articles
    1.42 +    cat<<-EOF >> $sqlfile
    1.43 +	-- presql2
    1.44 +	CREATE TEMPORARY TABLE _counts AS
    1.45 +	  SELECT $_i, count($_i) cnt
    1.46 +	  FROM $_t GROUP BY $_i;
    1.47 +	/* Prepare NEW count table */
    1.48 +	CREATE TEMPORARY TABLE _target AS
    1.49 +	  SELECT b.rowid trowid, b.id
    1.50 +	    FROM "$2" b JOIN "$2_s" s
    1.51 +	      ON b.id=s.id AND s.key='owner' AND s.val=$DT_QOWNER;
    1.52 +
    1.53 +	CREATE TEMPORARY TABLE _children AS
    1.54 +	  SELECT a.trowid trowid, $_i, a.id, s.val ctime
    1.55 +	  FROM   (SELECT t.trowid, t.id $_i, a.id
    1.56 +          	  FROM _target t LEFT JOIN "$_t" a ON t.id=a.$_i) a
    1.57 +          LEFT JOIN ${_t}_s s ON a.id=s.id AND s.key='ctime';
    1.58 +
    1.59 +	CREATE TEMPORARY TABLE _news($_i, newcnt);
    1.60 +	  INSERT INTO _news
    1.61 +    	  SELECT a.id, coalesce(newcnt, 0) newcnt
    1.62 +    	  FROM   (SELECT DISTINCT id FROM _target)
    1.63 +	      a LEFT JOIN
    1.64 +	      	 (SELECT $_i, count(ctime) newcnt
    1.65 +		  FROM  _children x
    1.66 +		  WHERE ctime > coalesce((SELECT time from myacclog
    1.67 +                                          WHERE tblrowid=x.trowid),
    1.68 +                               	         '1970-01-01')
    1.69 +                  GROUP BY $_i) b
    1.70 +          ON a.id=b.$_i;
    1.71 +	EOF
    1.72 +    # REMOVE next line until 2019/5/1
    1.73      cntall="(select count($_i) from $_t where $_i=a.id)"
    1.74 -    # XXX: Dirty workaround for slow subquery of acclog
    1.75 -    presql="CREATE TEMPORARY TABLE IF NOT EXISTS myacclog AS
    1.76 -            SELECT * FROM acclog WHERE user='$user' and tbl='$2';"
    1.77 +    cntall="(coalesce((select cnt from _counts where $_i=a.id), 0))"
    1.78 +    # REMOVE next assignment until 2019/5/1
    1.79      cntnew="(select count(val) from ${_t}_s where key='ctime' \
    1.80          and id in (select id from $_t where $_i=a.id) \
    1.81          and val > coalesce((select time from myacclog where \
    1.82                              tblrowid=a.rowid),\
    1.83                      '1970-01-01'))"
    1.84 +    cntnew="(SELECT newcnt FROM _news where $_i=a.id)"
    1.85      cnt="$cntnew as '新着', $cntall as '総数',"
    1.86      dt_class=" td2r td3r dumpblogs"
    1.87    fi
    1.88 @@ -3162,20 +3211,31 @@
    1.89      scols="$scols${scols:+, }b.$as"
    1.90    done
    1.91  #case author when '$user' then a.rowid else '---' end as ID,
    1.92 -  sql=${DT_SQL:-"select \
    1.93 -a.rowid as LINK,\
    1.94 -$cnt\
    1.95 -$scols from $2 a left join\
    1.96 - (select $pk,$eav,
    1.97 -	max(case key when 'owner'
    1.98 -	 then (SELECT gecos FROM gecoses WHERE name=val) END) as gecos
    1.99 - from ${2}_s c group by $pk) b on a.$pk=b.$pk $4;"}
   1.100 -  ## err dt:SQL="`echo \"$presql$sql\"|tr -d '\n'`"
   1.101 +  if [ -z "DT_SQL" ]; then
   1.102 +    echo "$DT_SQL"
   1.103 +  else
   1.104 +    cat<<-EOF
   1.105 +	SELECT a.rowid as LINK,  $cnt $scols
   1.106 +	FROM $2 a LEFT JOIN
   1.107 +	 (SELECT $pk,$eav,
   1.108 +	  	 max(CASE key
   1.109 +		     WHEN 'owner'
   1.110 +	 	     THEN (SELECT gecos FROM gecoses WHERE name=val) END)
   1.111 +		 as gecos
   1.112 +	  FROM ${2}_s c GROUP BY $pk)
   1.113 +	 b ON a.$pk=b.$pk $4;
   1.114 +	EOF
   1.115 +  fi >> $sqlfile
   1.116 +  ## err dt:SQL="`echo \"$presql$presql2$sql\"|tr -d '\n'`"
   1.117 +  sqlog<<-EOF
   1.118 +	*** SQL-file: $sqlfile ***
   1.119 +	`cat $sqlfile`
   1.120 +	EOF
   1.121    cat<<EOF | sed "s,\(<TR><TD>\)\([1-9][0-9]*\)\(#[0-9a-fxs]*\)*</TD>,\1$elink$dvlink</TD>," | dt_rowhack
   1.122  <div> <!-- for folding by check button (s4-funcs.sh:dumptable()) -->
   1.123  <div class="dumptable">
   1.124  <table class="b$dt_class">
   1.125 -`sq -header -cmd ".mode $1" $db "$presql$sql"`
   1.126 +`sq -header -cmd ".mode $1" $db ".read $sqlfile"`
   1.127  </table>
   1.128  </div> <!-- dumptable -->
   1.129  </div> <!-- for folding by check button (s4-funcs.sh:dumptable()) -->