Add a new table to store package versions by revision ranges

This isn't new information, but derived from information already in the
database. It's collected here to make querying faster.

The table is updated when each new revision is entered.

5 files changed, 96 insertions, 2 deletions

diff --git a/guix-data-service/jobs/load-new-guix-revision.scm b/guix-data-service/jobs/load-new-guix-revision.scm
index dd0291d..349a86e 100644
--- a/guix-data-service/jobs/load-new-guix-revision.scm
+++ b/guix-data-service/jobs/load-new-guix-revision.scm
@@ -864,13 +864,78 @@ WHERE job_id = $1"
                     git-repository-id))
               (commit commit)))))
 
+(define (update-package-versions-table conn git-repository-id commit)
+  ;; Lock the table to wait for other transactions to commit before updating
+  ;; the table
+  (exec-query
+   conn
+   "
+LOCK TABLE ONLY package_versions_by_guix_revision_range
+  IN SHARE ROW EXCLUSIVE MODE")
+
+  (for-each
+   (match-lambda
+     ((branch-name)
+      (log-time
+       (simple-format #f "deleting package version entries for ~A" branch-name)
+       (lambda ()
+         (exec-query
+          conn
+          "
+DELETE FROM package_versions_by_guix_revision_range
+WHERE git_repository_id = $1 AND branch_name = $2"
+          (list git-repository-id
+                branch-name))))
+      (log-time
+       (simple-format #f "inserting package version entries for ~A" branch-name)
+       (lambda ()
+         (exec-query
+          conn
+          "
+INSERT INTO package_versions_by_guix_revision_range
+SELECT DISTINCT
+       $1::integer AS git_repository_id,
+       $2 AS branch_name,
+       packages.name AS package_name,
+       packages.version AS package_version,
+       first_value(guix_revisions.id)
+         OVER package_version AS first_guix_revision_id,
+       last_value(guix_revisions.id)
+         OVER package_version AS last_guix_revision_id
+FROM packages
+INNER JOIN (
+  SELECT DISTINCT package_derivations.package_id,
+                  guix_revision_package_derivations.revision_id
+  FROM package_derivations
+  INNER JOIN guix_revision_package_derivations
+    ON package_derivations.id = guix_revision_package_derivations.package_derivation_id
+) AS revision_packages ON packages.id = revision_packages.package_id
+INNER JOIN guix_revisions ON revision_packages.revision_id = guix_revisions.id
+INNER JOIN git_branches ON guix_revisions.commit = git_branches.commit
+WHERE git_branches.name = $2
+WINDOW package_version AS (
+  PARTITION BY packages.name, packages.version
+  ORDER BY git_branches.datetime
+  RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
+)
+ORDER BY packages.name, packages.version"
+          (list git-repository-id branch-name))))))
+   (exec-query
+    conn
+    "SELECT name FROM git_branches WHERE commit = $1 AND git_repository_id = $2"
+    (list commit git-repository-id)))
+
+  #t)
+
 (define (load-new-guix-revision conn git-repository-id commit)
   (let ((store-item
          (store-item-for-git-repository-id-and-commit
           conn git-repository-id commit)))
     (if store-item
-        (extract-information-from conn git-repository-id
-                                  commit store-item)
+        (and
+         (extract-information-from conn git-repository-id
+                                   commit store-item)
+         (update-package-versions-table conn git-repository-id commit))
         (begin
           (simple-format #t "Failed to generate store item for ~A\n"
                          commit)
diff --git a/sqitch/deploy/package_versions_by_guix_revision_range.sql b/sqitch/deploy/package_versions_by_guix_revision_range.sql
new file mode 100644
index 0000000..26fa10a
--- /dev/null
+++ b/sqitch/deploy/package_versions_by_guix_revision_range.sql
@@ -0,0 +1,14 @@
+-- Deploy guix-data-service:package_versions_by_guix_revision_range to pg
+
+BEGIN;
+
+CREATE TABLE package_versions_by_guix_revision_range (
+  git_repository_id integer NOT NULL REFERENCES git_repositories (id),
+  branch_name varchar NOT NULL,
+  package_name varchar NOT NULL,
+  package_version varchar NOT NULL,
+  first_guix_revision_id integer NOT NULL REFERENCES guix_revisions (id),
+  last_guix_revision_id integer NOT NULL REFERENCES guix_revisions (id)
+);
+
+COMMIT;
diff --git a/sqitch/revert/package_versions_by_guix_revision_range.sql b/sqitch/revert/package_versions_by_guix_revision_range.sql
new file mode 100644
index 0000000..5e270d2
--- /dev/null
+++ b/sqitch/revert/package_versions_by_guix_revision_range.sql
@@ -0,0 +1,7 @@
+-- Revert guix-data-service:package_versions_by_guix_revision_range from pg
+
+BEGIN;
+
+DROP TABLE package_versions_by_guix_revision_range;
+
+COMMIT;
diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan
index b0b145b..8c0beec 100644
--- a/sqitch/sqitch.plan
+++ b/sqitch/sqitch.plan
@@ -20,3 +20,4 @@ change_git_branches_primary_key 2019-08-05T18:57:41Z Christopher Baines <mail@cb
 remove_duplicate_load_new_guix_revision_jobs 2019-08-05T19:06:36Z Christopher Baines <mail@cbaines.net> # Remove duplicate load_new_guix_revision_jobs
 lint_warnings 2019-08-18T17:10:12Z Christopher Baines <mail@cbaines.net> # Store lint warnings
 guix_revision_lint_checkers 2019-09-01T12:17:38Z chris <chris@phact> # Associate lint_checkers to guix_revisions
+package_versions_by_guix_revision_range 2019-09-26T20:23:15Z Christopher Baines <mail@cbaines.net> # Add package_versions_by_guix_revision_range table
diff --git a/sqitch/verify/package_versions_by_guix_revision_range.sql b/sqitch/verify/package_versions_by_guix_revision_range.sql
new file mode 100644
index 0000000..e1f8e59
--- /dev/null
+++ b/sqitch/verify/package_versions_by_guix_revision_range.sql
@@ -0,0 +1,7 @@
+-- Verify guix-data-service:package_versions_by_guix_revision_range on pg
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;