# HG changeset patch
# User HIROSE Yuuji <yuuji@gentei.org>
# Date 1477144716 -32374
# Node ID 48d0b6c4de655f92581e9e6394c055f0e5f7af37
# Parent  08165f6b7828629827e3bc1d590d39c65d472b70
Revise SQL for article search drastically for speed.

diff -r 08165f6b7828 -r 48d0b6c4de65 s4-blog.sh
--- a/s4-blog.sh	Sat Oct 22 00:39:53 2016 +0859
+++ b/s4-blog.sh	Sat Oct 22 22:58:10 2016 +0859
@@ -462,24 +462,25 @@
 searchart() {
   kwd=`getpar kwd|nkf -wZ1`		# Convert Zenkaku-SPC to ASCII-SPC
   kwdgrp=""
+  authcond=""
   if [ -z "$kwd" ]; then
     echo "検索語を指定してください" | html p; return
   fi
   if expr x"$kwd" : 'x#[1-9][0-9]*$' >/dev/null 1>&2; then
     # Like '#1234', assume as artID
     rowid=$((${kwd#\#} + 0))	# Force to be a number
-    kc="a.rowid = $rowid"
+    kc="ar.rowid = $rowid"
   else
     for k in `echo "$kwd" | sed "s/'/''/g"`; do
       if expr x"$k" : 'x@[1-9][][0-9]*-[][0-9:-]*$' >/dev/null 1>&2; then
 	# Like '@2016-10-10', expands to "s.ctime GLOB '@2016-10-10'"
 	ctime=${k#@}
 	# Not sure GROUP BY a.blogid is comfortable for searchers...?
-	kc=$kc${kc:+" AND "}"s.ctime GLOB '${ctime}*'"
+	kc=$kc${kc:+" AND "}"ctime GLOB '${ctime}*'"
 	##### kwdgrp=" GROUP BY a.blogid"   ## Add this to lessen results
       elif [ x"$k" = x"@today" -o x"$k" = x"@今日" ]; then
 	ctime=`date +%F`
-	kc=$kc${kc:+" AND "}"s.ctime GLOB '${ctime}*'"
+	kc=$kc${kc:+" AND "}"ctime GLOB '${ctime}*'"
       else
 	kc=$kc${kc:+" AND "}"content LIKE '%$k%'"
       fi
@@ -499,7 +500,8 @@
     fi
   elif { author=`getpar author`; test -n "$author"; }; then
     atptn=`sqlquotestr $author`
-    kc="$kc${kc:+ AND }author=$atptn"
+    #kc="$kc${kc:+ AND }author=$atptn"
+    authcond="WHERE author=$atptn"
     if isuser $author; then
       echo "(`linkhome $author` さんの書き込みからの検索)" | html p
     fi
@@ -509,41 +511,48 @@
   # blog:		id=blog-id, author=LeaderAuthor
   # blog_s:		id=blog-id, key='title', val='BLOG-TITLE'
   # WANT: blog-ROWid,article-id,val(TEXT)
-  sql="`sql4readableblogs`		-- Extract user-readable blogs
-       SELECT b.rid||'#'||x.id as '',
-		  b.title as TITLE,
-		  substr(x.ctime, 0, 11) as DATE,
-		  substr(x.content, 0, 78) as TEXT
-       FROM (SELECT blg.rid,blg.*,bs.val as title
-	     FROM readableblogs blg JOIN blog_s bs
-	          ON blg.id=bs.id AND bs.key='title') b
-	  JOIN
-	    (SELECT a.id,
-		    a.blogid,
-		    s.ctime,
-		    s.text /* || ' ' || m.filenames */ content
-	     FROM article a
-	       JOIN
-	          (SELECT id,  /* Create [id, ctime, text] from article_s */
-		          max(CASE key WHEN 'ctime' THEN val END) ctime,
-		     	  max(CASE key WHEN 'text' THEN val END) text
-	      	   FROM article_s GROUP BY id) s
-	       /*
-	       JOIN
-	          (SELECT id, group_concat(val) filenames
-		   FROM article_m
-		   WHERE type LIKE 'file:%'
-	      	   GROUP BY id) m */
-	       ON a.id=s.id /* AND a.id=m.id */ WHERE $kc$kwdgrp) x
-	   ON b.id=x.blogid
-       WHERE b.id IN (SELECT id FROM blog_s $cond)
-       ORDER by DATE DESC, TITLE, x.ctime;"
+  sql2="`sql4readableblogs`		-- Extract user-readable blogs
+-- 0.3sec
+WITH artsm AS (
+ SELECT a.id,ctime, text || ' ' || coalesce(files, '') content
+ FROM article a
+   LEFT JOIN
+     (SELECT ars.id, ctime, text, coalesce(files, '') files
+      FROM (SELECT id,
+		   max(CASE key WHEN 'ctime' THEN val END) ctime,
+		   max(CASE key WHEN 'text' THEN val END) text
+	    FROM   article_s
+	    GROUP BY id) ars
+	LEFT JOIN
+	  (SELECT id, group_concat(val) files
+	   FROM article_m
+	   WHERE type LIKE 'file:%'
+	   GROUP BY id) arm
+	ON ars.id=arm.id
+     ) ar
+   ON a.id=ar.id
+), ar AS (
+ SELECT article.rowid, article.blogid, article.id, ctime, content
+ FROM	article JOIN artsm ON article.id=artsm.id
+ $authcond
+), bl AS (
+ SELECT blg.rid, blg.*, blog_s.val TITLE
+ FROM	readableblogs blg JOIN blog_s ON blg.id=blog_s.id AND blog_s.key='title'
+)
+SELECT	bl.rid||'#'||ar.id '',
+	bl.title TITLE,
+	substr(ctime, 0, 11) DATE,
+	substr(content, 0, 78) TEXT
+FROM ar JOIN bl
+	ON ar.blogid=bl.id
+WHERE $kc AND bl.id IN (SELECT id FROM blog_s $cond)
+ORDER by DATE DESC, TITLE, ctime;"
   sedopt="s,<TR><TD>\([^<]*\)</TD>,<TR><TD><a\
  href=\"?replyblog+\1\">VIEW</a></TD>,"
-  
+  # echo "$sql2" > tmp/sql.out
   cat<<EOF
 <table class="b searchart">
-`sq -header -html $db "$sql"|sed "$sedopt"`
+`sq -header -html $db "$sql2"|sed "$sedopt"`
 </table>
 EOF
 }
diff -r 08165f6b7828 -r 48d0b6c4de65 s4-funcs.sh
--- a/s4-funcs.sh	Sat Oct 22 00:39:53 2016 +0859
+++ b/s4-funcs.sh	Sat Oct 22 22:58:10 2016 +0859
@@ -322,6 +322,7 @@
   rm $sqi
 }
 cleanup() {
+  trap '' INT HUP EXIT TERM PIPE
   echo .quit >&5
   kill $sq3pid
   kill $sq3pid