From efce515bf8d5e44b23cb047165912ce817500533 Mon Sep 17 00:00:00 2001
From: Matthias Andreas Benkard <code@mail.matthias.benkard.de>
Date: Sun, 13 Mar 2011 12:57:36 +0100
Subject: Reduce the number of roundtrips needed for archive page creation.

---
 mulkcms.lisp | 76 +++++++++++++++++++++++++++---------------------------------
 schema.sql   | 22 ++++++++++++++++++
 2 files changed, 56 insertions(+), 42 deletions(-)

diff --git a/mulkcms.lisp b/mulkcms.lisp
index d835e2a..d68446d 100644
--- a/mulkcms.lisp
+++ b/mulkcms.lisp
@@ -87,9 +87,9 @@
   (let ((article-template (template "article")))
     (expand-template article-template article-params)))
 
-(defun paramify-article-data (revision-data comment-num &optional (comments nil commentary-p))
+(defun paramify-article-data (revision-data &optional (comments nil commentary-p))
   (destructuring-bind (rid article date title content author format status
-                       global-id publishing-date &rest args)
+                       global-id publishing-date comment-num &rest args)
       revision-data
     (declare (ignore args rid format author))
     (list :publishing-date publishing-date
@@ -160,20 +160,20 @@
 #-portable-mulkcms
 (defun find-all-revisions (characteristics &optional constraints)
   (query
-   (format nil
-           "SELECT (most_recent_revision(r)).*, (oldest_revision(r2)).date
-              FROM (SELECT article_revisions_for_characteristics(a.id, ~A)
-                           AS revision
-                      FROM articles a)
-                   AS mr
-              JOIN article_revisions r ON r.id = mr.revision
-              JOIN article_revisions r2 ON r.article = r2.article
-             WHERE r2.status IN ('published', 'syndicated')
-             GROUP BY r.article
-            HAVING ~A
-             ORDER BY (oldest_revision(r)).date DESC"
-           (make-characteristic-lists characteristics)
-           (or constraints "true"))))
+    (format nil
+            "SELECT (most_recent_revision(r)).*, d.publishing_date, c.comment_count
+               FROM (SELECT article_revisions_for_characteristics(a.id, ~A)
+                            AS revision
+                       FROM articles a)
+                    AS mr
+               JOIN article_revisions        r ON r.id = mr.revision
+               JOIN article_publishing_dates d ON d.article = r.article
+               JOIN article_comment_counts   c ON c.article = r.article
+              GROUP BY r.article, d.publishing_date, c.comment_count
+             HAVING ~A
+              ORDER BY d.publishing_date DESC"
+            (make-characteristic-lists characteristics)
+            (or constraints "true"))))
 
 
 #+portable-mulkcms
@@ -331,9 +331,18 @@
                                                :head head
                                                :body body)))))))
 
-(defun paramify-article (revision-data &optional commentary-p)
-  (let* ((article (second revision-data))
-         (comment-data (if commentary-p
+(defun paramify-article (revision-data &optional commentary-p comments)
+  (let* (
+         )
+    (cond ((null revision-data)
+           nil)
+          (commentary-p
+           (paramify-article-data revision-data comments))
+          (t
+           (paramify-article-data revision-data)))))
+
+(defun find-article-params (article characteristics &optional commentary-p)
+  (let* ((comment-data (if commentary-p
                            (query "SELECT id FROM comments WHERE article = $1"
                                   article
                                   :column)
@@ -351,27 +360,8 @@
                                        :lists)))
                              comment-data)))
          (comments (mapcar #'paramify-comment comment-revision-data))
-         (comment-num
-          (if commentary-p
-              (length comment-revision-data)
-              (query "SELECT count(*)
-                        FROM comments
-                       WHERE article = $1
-                         AND EXISTS (SELECT *
-                                       FROM comment_revisions
-                                      WHERE comment = comments.id
-                                        AND status IN ('approved', 'trusted'))"
-                     article
-                     :single))))
-    (cond ((null revision-data)
-           nil)
-          (commentary-p
-           (paramify-article-data revision-data comment-num comments))
-          (t
-           (paramify-article-data revision-data comment-num)))))
-
-(defun find-article-params (article characteristics &optional commentary-p)
-  (let* ((revisions (find-article-revisions article characteristics))
+         (comment-num (length comments))  ;FIXME
+         (revisions (find-article-revisions article characteristics))
          (revision (first revisions))
          (revision-data (query "SELECT *
                                   FROM article_revisions
@@ -387,8 +377,10 @@
     (cond ((null revision-data)
            nil)
           (t
-           (paramify-article (append revision-data (list publishing-date))
-                             commentary-p)))))
+           (paramify-article (append revision-data (list publishing-date
+                                                         comment-num))
+                             commentary-p
+                             comments)))))
 
 (defun find-article-request-handler (path &optional action characteristics)
   (with-db
diff --git a/schema.sql b/schema.sql
index 87adc26..5f636fe 100644
--- a/schema.sql
+++ b/schema.sql
@@ -269,4 +269,26 @@ CREATE AGGREGATE most_recent_revision (article_revisions) (
   STYPE = article_revisions
 );
 
+
+CREATE VIEW article_comment_counts AS
+  SELECT a.id       AS article,
+         count(c.*) AS comment_count
+    FROM articles a
+    LEFT JOIN comments c
+      ON c.article = a.id
+     AND EXISTS (SELECT *
+                   FROM comment_revisions
+                  WHERE comment = c.id
+                    AND status IN ('approved', 'trusted'))
+   GROUP BY a.id;
+
+
+CREATE VIEW article_publishing_dates AS
+  SELECT article   AS article,
+         min(date) AS publishing_date
+    FROM article_revisions
+   WHERE status IN ('published', 'syndicated')
+   GROUP BY article;
+
+
 COMMIT;
-- 
cgit v1.2.3