1.1 --- a/s4-funcs.sh	Thu Aug 24 07:44:38 2017 +0859
     1.2 +++ b/s4-funcs.sh	Mon Aug 28 22:51:45 2017 +0859
     1.3 @@ -31,6 +31,7 @@
     1.4  ### maximagexy=400x400
     1.5  file_accept='accept="image/*,text/*,audio/*,application/vnd.oasis.*,application/pdf,application/x-*"'
     1.6  blogreadflagrowid=0
     1.7 +blogcutoffflagrowid=-1
     1.8  querylog=$tmpdir/query.log
     1.9  
    1.10  tconfs=""
    1.11 @@ -506,7 +507,7 @@
    1.12  }
    1.13  acclog() (
    1.14    # $1=table, $2=rowid
    1.15 -  n=${2%%[!0-9]*}	# Remove non-digit chars from $2(should be rowid)
    1.16 +  n=${2%%[!-0-9]*}	# Remove non-digit chars from $2(should be rowid)
    1.17    if [ -n "$n" ]; then
    1.18      now=`date +"%F %T"`
    1.19      #query "replace into acclog values('$user', '$1', '$n', '$now');"
    1.20 @@ -1542,51 +1543,36 @@
    1.21  EOF
    1.22  }
    1.23  listnewblogsql() { # $1=user
    1.24 -  deftime=`query "SELECT coalesce((SELECT time FROM acclog
    1.25 +  deftime=`query "SELECT coalesce((SELECT max(time) FROM acclog
    1.26  		  	 	   WHERE user='$user'
    1.27 -				    AND tblrowid=$blogreadflagrowid),
    1.28 +				    AND tblrowid IN
    1.29 +				      ($blogreadflagrowid,
    1.30 +				       $blogcutoffflagrowid)),
    1.31  				  "0");"`
    1.32    cat<<EOF
    1.33  `sql4interestblogs`
    1.34  WITH article_ctime as (
    1.35 - SELECT id,blogid,author,val ctime
    1.36 + SELECT id,blogid,author,max(val) ctime
    1.37   FROM   article join article_s s using(id)
    1.38 - WHERE  s.key='ctime'
    1.39 + WHERE  s.key='ctime' AND s.val > '$deftime'
    1.40 + GROUP BY id
    1.41  ), blog_title_owner as (
    1.42   SELECT blg.rid brid, id,
    1.43          max(case key when 'title' then val end) title,
    1.44          max(case key when 'owner' then val end) owner
    1.45   FROM interestblogs blg, blog_s using(id) group by id
    1.46 -), visited AS (
    1.47 - SELECT b.id id, brid, b.owner owner, b.title title, ctime, ac.author author
    1.48 - FROM blog_title_owner b, article_ctime ac
    1.49 -      JOIN acclog al
    1.50 -      ON b.id=ac.blogid
    1.51 -                AND al.tbl='blog'
    1.52 -                AND al.tblrowid=brid
    1.53 -                AND al.user='$user'
    1.54 -		AND al.time < ctime
    1.55 -		AND '$deftime' < ctime
    1.56 -), unvisited as (
    1.57 - SELECT b.id id, brid, b.owner owner, b.title title, ctime, ac.author author
    1.58 - FROM blog_title_owner b, article_ctime ac
    1.59 - ON b.id=ac.blogid
    1.60 - WHERE brid NOT IN (SELECT tblrowid FROM acclog
    1.61 -                   WHERE tbl='blog' AND user='$user')
    1.62 -       AND ctime > '$deftime'
    1.63  ), blogall as (
    1.64 - /* ---------------------------------------
    1.65 -	Collect new articles with dividing them into visited and unvisited
    1.66 -	separately, because constructing joined table of artice X acclog
    1.67 -	tends to become HUGE combinations.
    1.68 -    --------------------------------------- */
    1.69 - SELECT * FROM visited UNION SELECT * FROM unvisited
    1.70 + SELECT * FROM blog_title_owner b JOIN article_ctime ac ON b.id=ac.blogid
    1.71  ), news as (
    1.72 - select bl.brid brid, bl.title, bl.id blid, ctime,
    1.73 -        count(bl.id) "新着", bl.owner, bl.author
    1.74 - from blogall bl /* left join a_u l
    1.75 - on bl.brid=l.tblrowid */
    1.76 - group by bl.id order by ctime desc,"新着" desc, bl.id
    1.77 + SELECT brid, bl.id blid, bl.title, ctime,
    1.78 + 	coalesce(al.time, '$deftime') atime,
    1.79 +        count(bl.id) "新着", bl.author
    1.80 + FROM blogall bl
    1.81 +      LEFT JOIN
    1.82 +      (SELECT * FROM acclog WHERE user='$user' AND tbl='blog') al
    1.83 +      ON bl.brid=al.tblrowid
    1.84 + WHERE atime < bl.ctime
    1.85 + GROUP by bl.id ORDER BY ctime desc,"新着" desc, bl.id
    1.86   LIMIT 10
    1.87  ) SELECT brid LINK, "新着",
    1.88         (SELECT count(*) FROM article WHERE blogid=blid) "総数",
    1.89 @@ -1835,6 +1821,7 @@
    1.90      fi
    1.91      # 2016-02-19 Counting NEWS without using dumptable.
    1.92      sql=`listnewblogsql "$user"`
    1.93 +    # echo "$sql" > tmp/listnew
    1.94      new10=`DT_SQL="$sql" DT_VIEW=replyblog dumptable html blog`
    1.95      cont=`echo "$new10"|grep "^<TR>"|wc -l`
    1.96      cont=$((cont-1))
    1.97 @@ -1851,6 +1838,8 @@
    1.98  	EOF
    1.99        echo "$new10 <!-- new10 -->"
   1.100        echo "</div>"
   1.101 +    else			# If news is 0, set log cut off flag
   1.102 +      acclog blog $blogcutoffflagrowid	# for speed
   1.103      fi
   1.104    else				# Not My Home ($user != $uname)
   1.105      : # DT_SQL=