From dd0cece91f3b5b083ea910650d7b40d44d887921 Mon Sep 17 00:00:00 2001
From: Matthias Andreas Benkard <code@mail.matthias.benkard.de>
Date: Sun, 27 May 2018 08:46:29 +0200
Subject: Add support for explicit journal keeping in the database.

---
 ChangeLog                           |  8 +++++++
 mulkcms.lisp                        | 46 ++++++++++++++++++++++++++++---------
 schema.sql                          | 16 +++++++++++++
 templates/article_summary_page.html |  6 ++++-
 4 files changed, 64 insertions(+), 12 deletions(-)
 create mode 100644 ChangeLog

diff --git a/ChangeLog b/ChangeLog
new file mode 100644
index 0000000..cf96ced
--- /dev/null
+++ b/ChangeLog
@@ -0,0 +1,8 @@
+2018-05-27  Matthias Benkard  <usenet@mail.matthias.benkard.de>
+
+	* Journal entries are now kept in a separate table.  To move
+	existing journal entries over to the new schema, use the following
+	queries:
+
+	INSERT INTO journals VALUES (0, 'journal');
+	INSERT INTO journal_entries(journal, index, article) SELECT 0, trim(LEADING 'journal/%' FROM alias)::integer, article FROM article_aliases WHERE alias ~ 'journal/\d+';
diff --git a/mulkcms.lisp b/mulkcms.lisp
index 5adc7a7..062fe8f 100644
--- a/mulkcms.lisp
+++ b/mulkcms.lisp
@@ -573,13 +573,9 @@
 
 (defprepared find-journal-articles
   "SELECT article
-     FROM article_revisions
-    WHERE status IN ('published', 'syndicated')
-    GROUP BY article
-   HAVING EXISTS (SELECT 1 FROM article_aliases
-                   WHERE article = article_revisions.article
-                     AND alias LIKE 'journal/%')
-    ORDER BY min(date) DESC"
+     FROM journal_entries
+    WHERE journal = 0
+    ORDER BY index DESC"
   :column)
 
 (defun find-journal-archive-request-handler (path full-p
@@ -615,9 +611,9 @@
                   (mapcar #'paramify-article
                           (find-all-revisions characteristics
                                               "EXISTS (SELECT 1
-                                                       FROM article_aliases
-                                                      WHERE article = r.article
-                                                        AND alias LIKE 'journal/%')")))
+                                                         FROM journal_entries je
+                                                        WHERE journal = 0
+                                                          AND r.article = je.article)")))
                  (displayed-revisions (if full-p
                                           revisions
                                           (subseq revisions
@@ -934,6 +930,33 @@
                          article-id
                          user-id
                          (format nil "urn:uuid:~A" (make-uuid))))))
+            (when (assoc "create-journal-entry" params :test #'equal)
+              (with-transaction ()
+                (let* ((article-id
+                         (query "INSERT INTO articles(type) VALUES (1) RETURNING id"
+                                :single!))
+                       (article-revision-id
+                         (query "INSERT INTO article_revisions(article, title, content, author, format, status, global_id)
+                                      VALUES ($1, '', '', $2, 'html', 'draft', $3)
+                                   RETURNING id"
+                                article-id
+                                user-id
+                                (format nil "urn:uuid:~A" (make-uuid))
+                                :single!))
+                       (journal-index (query "INSERT INTO journal_entries(journal, index, article)
+                                                   SELECT 0, 1+MAX(index), $1
+                                                     FROM journal_entries
+                                                    WHERE journal = 0
+                                                RETURNING index"
+                                article-id
+                                :single!)))
+                  (query "INSERT INTO article_aliases(alias, article)
+                                 VALUES ('journal/' || ($1 :: TEXT), $2)"
+                         journal-index
+                         article-id)
+                  (query "INSERT INTO article_revision_characteristics(revision, characteristic, value)
+                               VALUES ($1, 'language', 'de')"
+                         article-revision-id))))
             (let* ((articles (query "SELECT a.id,
                                             array_agg(DISTINCT
                                                       ROW(r.id,
@@ -974,7 +997,8 @@
                                  :branch-title-label "Title"
                                  :characteristics-label "Characteristics"
                                  :date-label "Date"
-                                 :create-button-label "Add article"
+                                 :create-article-button-label "Add Article"
+                                 :create-journal-entry-button-label "Add Journal Entry"
                                  :add-alias-label "+"
                                  :articles article-data)))))))))
 
diff --git a/schema.sql b/schema.sql
index bb4a0db..6545da0 100644
--- a/schema.sql
+++ b/schema.sql
@@ -68,6 +68,22 @@ CREATE TABLE article_aliases(
   FOREIGN KEY (article) REFERENCES articles
 );
 
+CREATE TABLE journals(
+  id          INTEGER NOT NULL,
+  path_prefix VARCHAR,  --can be null to make the journal unreachable
+  PRIMARY KEY (id)
+);
+
+CREATE TABLE journal_entries(
+  journal INTEGER NOT NULL,
+  index   INTEGER NOT NULL,
+  article INTEGER NOT NULL,
+  PRIMARY KEY (journal, index),
+  FOREIGN KEY (article) REFERENCES articles,
+  FOREIGN KEY (journal) REFERENCES journals,
+  CHECK (index >= 0)
+);
+
 CREATE TABLE comments(
   id        SERIAL  NOT NULL,
   article   INTEGER NOT NULL,
diff --git a/templates/article_summary_page.html b/templates/article_summary_page.html
index 09b3cf5..8c7b0e3 100644
--- a/templates/article_summary_page.html
+++ b/templates/article_summary_page.html
@@ -6,7 +6,11 @@
 <p>
   <form method="POST">
     <input name="create-article" type="hidden" />
-    <input type="submit" value="{create-button-label|html-attr-value}" />
+    <input type="submit" value="{create-article-button-label|html-attr-value}" />
+  </form>
+  <form method="POST">
+    <input name="create-journal-entry" type="hidden" />
+    <input type="submit" value="{create-journal-entry-button-label|html-attr-value}" />
   </form>
 </p>
 
-- 
cgit v1.2.3