1.1 --- a/s4-blog.sh	Sun Jul 10 20:40:33 2016 +0859
     1.2 +++ b/s4-blog.sh	Sun Jul 10 23:25:49 2016 +0859
     1.3 @@ -436,7 +436,7 @@
     1.4      echo "検索語を指定してください" | html p; return
     1.5    fi
     1.6    for k in `echo "$kwd" | sed "s/'/''/g"`; do
     1.7 -    kc=$kc${kc:+" AND "}"s.val LIKE '%$k%'"
     1.8 +    kc=$kc${kc:+" AND "}"s.text LIKE '%$k%'"
     1.9    done
    1.10    kwd=`echo "$kwd"|htmlescape`
    1.11    owner=`getpar owner`
    1.12 @@ -456,19 +456,32 @@
    1.13    # blog:		id=blog-id, author=LeaderAuthor
    1.14    # blog_s:		id=blog-id, key='title', val='BLOG-TITLE'
    1.15    # WANT: blog-ROWid,article-id,val(TEXT)
    1.16 -  sql="select b.rowid||'#'||x.id as '',
    1.17 +    sql="`sql4readableblogs`		-- Extract user-readable blogs
    1.18 +       SELECT b.rid||'#'||x.id as '',
    1.19  		  b.title as TITLE,
    1.20 -		  substr(x.val, 0, 80) as TEXT
    1.21 -	from (select blog.rowid,blog.*,bs.val as title from blog join blog_s bs
    1.22 -		on blog.id=bs.id and bs.key='title') b
    1.23 +		  substr(x.ctime, 0, 11) as DATE,
    1.24 +		  substr(x.text, 0, 78) as TEXT
    1.25 +       FROM (SELECT blg.rid,blg.*,bs.val as title
    1.26 +	     FROM readableblogs blg join blog_s bs
    1.27 +	          ON blg.id=bs.id and bs.key='title') b
    1.28  	  join
    1.29 -	    (select a.id,a.blogid,s.val from article a join article_s s
    1.30 -		 on a.id=s.id where $kc)
    1.31 +	    (SELECT a.id,
    1.32 +		    a.blogid,
    1.33 +		    s.ctime,
    1.34 +		    s.text
    1.35 +	     FROM article a
    1.36 +	       JOIN
    1.37 +		  (SELECT id,  /* Create [id, ctime, text] from article_s */
    1.38 +			  max(CASE key WHEN 'ctime' THEN val END) ctime,
    1.39 +			  max(CASE key WHEN 'text' THEN val END) text
    1.40 +		   FROM article_s GROUP BY id) s
    1.41 +	       ON a.id=s.id where $kc)
    1.42  	     x on b.id=x.blogid
    1.43 -	 where b.id in (select id from blog_s
    1.44 -	$cond);"
    1.45 +	 where b.id in (select id from blog_s $cond)
    1.46 +       ORDER by x.ctime DESC;"
    1.47    sedopt="s,<TR><TD>\([^<]*\)</TD>,<TR><TD><a\
    1.48   href=\"?replyblog+\1\">VIEW</a></TD>,"
    1.49 +  
    1.50    cat<<EOF
    1.51  <table class="b searchart">
    1.52  `sq -header -html $db "$sql"|sed "$sedopt"`

     2.1 --- a/s4-funcs.sh	Sun Jul 10 20:40:33 2016 +0859
     2.2 +++ b/s4-funcs.sh	Sun Jul 10 23:25:49 2016 +0859
     2.3 @@ -2095,6 +2095,31 @@
     2.4  $2
     2.5  EOF
     2.6  }
     2.7 +sql4readableblogs() {
     2.8 +  # Create view of blogs that can be readable to $user
     2.9 +  # Blog is readable when:
    2.10 +  #  1: blog owner is an user
    2.11 +  #  2: else, 2.1: owner-group where the $user belongs
    2.12 +  #           2.2: else, owner-group is not moderated
    2.13 +  # blog(id, author), blog_s(id, key='owner', val= ->owner)
    2.14 +  cat<<EOF | tee tmp/sql.out
    2.15 +CREATE TEMPORARY VIEW readableblogs AS
    2.16 + SELECT blog.rowid rid, id, author
    2.17 + FROM blog
    2.18 +      NATURAL JOIN
    2.19 +      (SELECT id, val owner FROM blog_s WHERE key='owner') bs
    2.20 + WHERE CASE WHEN (SELECT name FROM user where name=bs.owner) IS NOT NULL
    2.21 +	     THEN 1		-- blog owner is an user, READABLE
    2.22 +	    WHEN (SELECT val FROM grp_s
    2.23 +		  WHERE gname=bs.owner AND key='regmode') = 'moderated'
    2.24 +	      AND
    2.25 +		 (SELECT user FROM grp_mem
    2.26 +		  WHERE gname=bs.owner AND user='$user') IS NULL
    2.27 +	    THEN 0
    2.28 +	    ELSE 1
    2.29 +       END;
    2.30 +EOF
    2.31 +}
    2.32  editheading() {	# $1=rowid-of-heading
    2.33    rowid=${1%%[!A-Z0-9a-z_]*}
    2.34    if [ -z "$rowid" ]; then