From 3ef99acc79d4ee8a853364a86bb6a26debcebdb1 Mon Sep 17 00:00:00 2001 From: Christopher Baines Date: Sat, 16 Nov 2019 08:55:54 +0000 Subject: Add a new channel-news module, along with tables the relevant data --- Makefile.am | 1 + guix-data-service/model/channel-news.scm | 134 +++++++++++++++++++++++++++++++ sqitch/deploy/channel_news_tables.sql | 39 +++++++++ sqitch/revert/channel_news_tables.sql | 11 +++ sqitch/sqitch.plan | 1 + sqitch/verify/channel_news_tables.sql | 7 ++ 6 files changed, 193 insertions(+) create mode 100644 guix-data-service/model/channel-news.scm create mode 100644 sqitch/deploy/channel_news_tables.sql create mode 100644 sqitch/revert/channel_news_tables.sql create mode 100644 sqitch/verify/channel_news_tables.sql 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 # Add retry value to job_event enum remove_guix_revision_duplicates 2019-10-05T08:00:14Z Christopher Baines # Remove duplicates in the guix_revisions table package_derivations_by_guix_revision_range 2019-11-09T19:09:48Z Christopher Baines # Add package_derivations_by_guix_revision_range +channel_news_tables 2019-11-15T07:32:07Z Christopher Baines # 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; -- cgit v1.2.3