1.1 --- a/s4-funcs.sh	Mon Oct 24 09:47:24 2016 +0859
     1.2 +++ b/s4-funcs.sh	Tue Oct 25 11:29:32 2016 +0859
     1.3 @@ -1448,39 +1448,72 @@
     1.4    htmlheader=$layout/html.m4.html
     1.5    showgroup "$grid"
     1.6  }
     1.7 +sql4interestblogs() {
     1.8 +  cat<<EOF
     1.9 +CREATE TEMPORARY VIEW interestblogs AS
    1.10 + SELECT blog.rowid rid, id, author
    1.11 + FROM blog
    1.12 +      NATURAL JOIN
    1.13 +      (SELECT id, val owner FROM blog_s WHERE key='owner') bs
    1.14 + WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL
    1.15 +	     THEN 1		-- blog owner is an user, READABLE
    1.16 +	    WHEN (SELECT user FROM grp_mem
    1.17 +		  WHERE gname=bs.owner AND user='$user') IS NULL
    1.18 +	    THEN 0
    1.19 +	    ELSE 1
    1.20 +       END;
    1.21 +EOF
    1.22 +}
    1.23  listnewblogsql() { # $1=user
    1.24 -  cat<<EOF
    1.25 -`sql4readableblogs`
    1.26 -with article_ctime as (
    1.27 -  select id,blogid,author,val ctime
    1.28 -  from article join article_s s using(id)
    1.29 -  where s.key='ctime'
    1.30 +  deftime=`query "SELECT coalesce((SELECT time FROM acclog_user
    1.31 +		  WHERE tblrowid=$blogreadflagrowid), "0");"`
    1.32 +  cat<<EOF # | tee tmp/sql.out
    1.33 +`sql4interestblogs`
    1.34 +WITH article_ctime as (
    1.35 + SELECT id,blogid,author,val ctime
    1.36 + FROM   article join article_s s using(id)
    1.37 + WHERE  s.key='ctime'
    1.38  ), blog_title_owner as (
    1.39 -  select blg.rid brid, id,
    1.40 + SELECT blg.rid brid, id,
    1.41          max(case key when 'title' then val end) title,
    1.42          max(case key when 'owner' then val end) owner
    1.43 - from readableblogs blg, blog_s using(id) group by id
    1.44 -), acclog_user as (
    1.45 -  select * from acclog where user='$user' and tbl='blog'
    1.46 + FROM interestblogs blg, blog_s using(id) group by id
    1.47 +), visited AS (
    1.48 + SELECT b.id id, brid, b.owner owner, b.title title, ctime, ac.author author
    1.49 + FROM blog_title_owner b, article_ctime ac
    1.50 +      JOIN acclog al
    1.51 +      ON b.id=ac.blogid
    1.52 +                AND al.tbl='blog'
    1.53 +                AND al.tblrowid=brid
    1.54 +                AND al.user='$user'
    1.55 +		AND al.time < ctime
    1.56 +		AND '$deftime' < ctime
    1.57 +), unvisited as (
    1.58 + SELECT b.id id, brid, b.owner owner, b.title title, ctime, ac.author author
    1.59 + FROM blog_title_owner b, article_ctime ac
    1.60 + ON b.id=ac.blogid
    1.61 + WHERE brid NOT IN (SELECT tblrowid FROM acclog
    1.62 +                   WHERE tbl='blog' AND user='$user')
    1.63 +       AND ctime > '$deftime'
    1.64  ), blogall as (
    1.65 - select b.id, brid, b.owner, b.title, ctime, ac.author
    1.66 - from blog_title_owner b, article_ctime ac
    1.67 -      on b.id=ac.blogid
    1.68 -)
    1.69 - select brid LINK, 
    1.70 -        sum(max(coalesce(l.time, "0"),
    1.71 -		coalesce((SELECT time FROM acclog_user
    1.72 -		         WHERE tblrowid=$blogreadflagrowid), "0"))
    1.73 -	      < ctime
    1.74 -         ) "新着",
    1.75 - count(id) "総数", ctime, title,
    1.76 - /* owner gecos */
    1.77 - (select gecos from gecoses where name=bl.owner) gecos
    1.78 - from blogall bl left join acclog_user l
    1.79 -      on bl.brid=round(l.tblrowid)
    1.80 - group by bl.id
    1.81 - having "新着" > 0 order by ctime desc, "新着" desc, bl.id
    1.82 - limit 10;
    1.83 + /* ---------------------------------------
    1.84 +	Collect new articles with dividing them into visited and unvisited
    1.85 +	separately, because constructing joined table of artice X acclog
    1.86 +	tends to become HUGE combinations.
    1.87 +    --------------------------------------- */
    1.88 + SELECT * FROM visited UNION SELECT * FROM unvisited
    1.89 +), news as (
    1.90 + select bl.brid brid, bl.title, bl.id blid, ctime,
    1.91 +        count(bl.id) "新着", bl.owner, bl.author
    1.92 + from blogall bl /* left join a_u l
    1.93 + on bl.brid=l.tblrowid */
    1.94 + group by bl.id order by ctime desc,"新着" desc, bl.id
    1.95 + LIMIT 10
    1.96 +) SELECT brid LINK, "新着",
    1.97 +       (SELECT count(*) FROM article WHERE blogid=blid) "総数",
    1.98 +       ctime, title,
    1.99 +       (SELECT gecos FROM gecoses WHERE name=author) gecos
   1.100 +  FROM news;
   1.101  EOF
   1.102  }
   1.103