Add a new channel-news module, along with tables the relevant data

6 files changed, 193 insertions, 0 deletions

diff --git a/Makefile.am b/Makefile.am
index afaf3d7..5964900 100644
--- a/Makefile.am
+++ b/Makefile.am
@@ -74,6 +74,7 @@ SOURCES =							\
   guix-data-service/model/build-server.scm			\
   guix-data-service/model/build-status.scm			\
   guix-data-service/model/build.scm				\
+  guix-data-service/model/channel-news.scm			\
   guix-data-service/model/derivation.scm			\
   guix-data-service/model/git-branch.scm			\
   guix-data-service/model/git-repository.scm			\
diff --git a/guix-data-service/model/channel-news.scm b/guix-data-service/model/channel-news.scm
new file mode 100644
index 0000000..350ac16
--- /dev/null
+++ b/guix-data-service/model/channel-news.scm
@@ -0,0 +1,134 @@
+(define-module (guix-data-service model channel-news)
+  #:use-module (srfi srfi-1)
+  #:use-module (ice-9 vlist)
+  #:use-module (ice-9 match)
+  #:use-module (squee)
+  #:use-module (guix channels)
+  #:use-module (guix-data-service model utils)
+  #:export (insert-channel-news-entries-for-guix-revision))
+
+(define (insert-channel-news-entry-text conn text)
+  (insert-missing-data-and-return-all-ids
+   conn
+   "channel_news_entry_text"
+   '(lang text)
+   (map (match-lambda
+          ((lang . text)
+           (list lang text)))
+        text)))
+
+(define (insert-channel-news-entry conn commit tag)
+  (match (exec-query
+          conn
+          (string-append
+           "INSERT INTO channel_news_entries (commit, tag) VALUES ("
+           (value->quoted-string-or-null commit)
+           ","
+           (value->quoted-string-or-null tag)
+           ") RETURNING id"))
+    (((id))
+     (string->number id))))
+
+(define (insert-channel-news-entries conn channel-news-entries)
+  (define select-channel-news-entries
+    "
+SELECT channel_news_entries.id,
+       channel_news_entries.commit,
+       channel_news_entries.tag,
+       (
+         SELECT ARRAY_AGG(
+           channel_news_entry_titles.channel_news_entry_text_id
+           ORDER BY channel_news_entry_titles.channel_news_entry_text_id
+         )
+         FROM channel_news_entry_titles
+         WHERE channel_news_entry_id = channel_news_entries.id
+       ) AS title_text,
+       (
+         SELECT ARRAY_AGG(
+           channel_news_entry_bodies.channel_news_entry_text_id
+           ORDER BY channel_news_entry_bodies.channel_news_entry_text_id
+         )
+         FROM channel_news_entry_bodies
+         WHERE channel_news_entry_id = channel_news_entries.id
+       ) AS body_text
+FROM channel_news_entries
+ORDER BY id")
+
+  (define existing
+    (exec-query->vhash conn
+                       select-channel-news-entries
+                       (match-lambda
+                         ((_ commit tag title-ids body-ids)
+                          (list commit
+                                tag
+                                (map string->number
+                                     (parse-postgresql-array-string title-ids))
+                                (map string->number
+                                     (parse-postgresql-array-string body-ids)))))
+                       (lambda (result)
+                         (string->number (first result)))))
+
+  (map
+   (lambda (entry)
+     (let ((commit (channel-news-entry-commit entry))
+           (tag (channel-news-entry-tag entry))
+           (title-ids
+            (sort (insert-channel-news-entry-text
+                   conn (channel-news-entry-title entry))
+                  <))
+           (body-ids
+            (sort (insert-channel-news-entry-text
+                   conn
+                   (channel-news-entry-body entry))
+                  <)))
+       (or (and=> (vhash-assoc (list (or commit '())
+                                     (or tag '())
+                                     title-ids
+                                     body-ids)
+                               existing)
+                  (match-lambda
+                    ((value . key)
+                     key)))
+           (let ((channel-news-entry-id
+                  (insert-channel-news-entry conn commit tag)))
+             (for-each
+              (lambda (table ids)
+                (exec-query
+                 conn
+                 (string-append
+                  "INSERT INTO " table
+                  " VALUES "
+                  (string-join
+                   (map (lambda (id)
+                          (simple-format #f "(~A, ~A)"
+                                         channel-news-entry-id
+                                         id))
+                        ids)
+                   ", "))))
+              '("channel_news_entry_titles"
+                "channel_news_entry_bodies")
+              (list title-ids
+                    body-ids))
+
+             channel-news-entry-id))))
+   channel-news-entries))
+
+(define (insert-channel-news-entries-for-guix-revision
+         conn
+         guix-revision-id
+         channel-news-entries)
+  (unless (null? channel-news-entries)
+    (let ((channel-news-entry-ids
+           (insert-channel-news-entries conn channel-news-entries)))
+      (exec-query
+       conn
+       (string-append
+        "INSERT INTO guix_revision_channel_news_entries "
+        "(guix_revision_id, channel_news_entry_id, index) VALUES "
+        (string-join
+         (map (lambda (id index)
+                (simple-format #f "(~A,~A,~A)" guix-revision-id id index))
+              channel-news-entry-ids
+              (iota (length channel-news-entries)))
+         ", ")))))
+  #t)
diff --git a/sqitch/deploy/channel_news_tables.sql b/sqitch/deploy/channel_news_tables.sql
new file mode 100644
index 0000000..fa1add5
--- /dev/null
+++ b/sqitch/deploy/channel_news_tables.sql
@@ -0,0 +1,39 @@
+-- Deploy guix-data-service:channel_news_tables to pg
+
+BEGIN;
+
+CREATE TABLE channel_news_entries (
+  id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
+  commit varchar,
+  tag varchar
+);
+
+CREATE TABLE guix_revision_channel_news_entries (
+  guix_revision_id integer NOT NULL REFERENCES guix_revisions (id),
+  channel_news_entry_id integer NOT NULL REFERENCES channel_news_entries (id),
+  index integer NOT NULL,
+  PRIMARY KEY (guix_revision_id, channel_news_entry_id)
+);
+
+CREATE TABLE channel_news_entry_text (
+  id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
+  lang varchar NOT NULL,
+  text varchar NOT NULL,
+  UNIQUE (lang, text)
+);
+
+CREATE TABLE channel_news_entry_titles (
+  channel_news_entry_id
+    integer NOT NULL REFERENCES channel_news_entries (id),
+  channel_news_entry_text_id
+    integer NOT NULL REFERENCES channel_news_entry_text (id)
+);
+
+CREATE TABLE channel_news_entry_bodies (
+  channel_news_entry_id
+    integer NOT NULL REFERENCES channel_news_entries (id),
+  channel_news_entry_text_id
+    integer NOT NULL REFERENCES channel_news_entry_text (id)
+);
+
+COMMIT;
diff --git a/sqitch/revert/channel_news_tables.sql b/sqitch/revert/channel_news_tables.sql
new file mode 100644
index 0000000..c623319
--- /dev/null
+++ b/sqitch/revert/channel_news_tables.sql
@@ -0,0 +1,11 @@
+-- Revert guix-data-service:channel_news_tables from pg
+
+BEGIN;
+
+DROP TABLE guix_revision_channel_news_entries;
+DROP TABLE channel_news_entry_titles;
+DROP TABLE channel_news_entry_bodies;
+DROP TABLE channel_news_entry_text;
+DROP TABLE channel_news_entries;
+
+COMMIT;
diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan
index ec87d46..027d337 100644
--- a/sqitch/sqitch.plan
+++ b/sqitch/sqitch.plan
@@ -26,3 +26,4 @@ fix_null_values_in_git_branches 2019-09-29T11:06:12Z Christopher Baines <mail@cb
 add_retry_value_to_job_event_enum 2019-10-02T19:13:52Z Christopher Baines <mail@cbaines.net> # Add retry value to job_event enum
 remove_guix_revision_duplicates 2019-10-05T08:00:14Z Christopher Baines <mail@cbaines.net> # Remove duplicates in the guix_revisions table
 package_derivations_by_guix_revision_range 2019-11-09T19:09:48Z Christopher Baines <mail@cbaines.net> # Add package_derivations_by_guix_revision_range
+channel_news_tables 2019-11-15T07:32:07Z Christopher Baines <mail@cbaines.net> # Add tables to store channel news
diff --git a/sqitch/verify/channel_news_tables.sql b/sqitch/verify/channel_news_tables.sql
new file mode 100644
index 0000000..3317b4d
--- /dev/null
+++ b/sqitch/verify/channel_news_tables.sql
@@ -0,0 +1,7 @@
+-- Verify guix-data-service:channel_news_tables on pg
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;