Add a new table to describe the history of derivations

There's already the package_versions_by_guix_revision_range table, but I think
it would be also useful to be able to see how derivations change over time.

5 files changed, 103 insertions, 1 deletions

diff --git a/guix-data-service/jobs/load-new-guix-revision.scm b/guix-data-service/jobs/load-new-guix-revision.scm
index 86c3a78..643df0a 100644
--- a/guix-data-service/jobs/load-new-guix-revision.scm
+++ b/guix-data-service/jobs/load-new-guix-revision.scm
@@ -975,6 +975,75 @@ ORDER BY packages.name, packages.version"
 
   #t)
 
+(define (update-package-derivations-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_derivations_by_guix_revision_range
+  IN SHARE ROW EXCLUSIVE MODE")
+
+  (for-each
+   (match-lambda
+     ((branch-name)
+      (log-time
+       (simple-format #f "deleting package derivation entries for ~A" branch-name)
+       (lambda ()
+         (exec-query
+          conn
+          "
+DELETE FROM package_derivations_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 derivation entries for ~A" branch-name)
+       (lambda ()
+         (exec-query
+          conn
+          "
+INSERT INTO package_derivations_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,
+       revision_packages.derivation_id AS derivation_id,
+       revision_packages.system AS system,
+       revision_packages.target AS target,
+       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,
+                  package_derivations.derivation_id,
+                  package_derivations.system,
+                  package_derivations.target,
+                  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, revision_packages.derivation_id
+  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
@@ -983,7 +1052,8 @@ ORDER BY packages.name, packages.version"
         (and
          (extract-information-from conn git-repository-id
                                    commit store-item)
-         (update-package-versions-table conn git-repository-id commit))
+         (update-package-versions-table conn git-repository-id commit)
+         (update-package-derivations-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_derivations_by_guix_revision_range.sql b/sqitch/deploy/package_derivations_by_guix_revision_range.sql
new file mode 100644
index 0000000..e84c641
--- /dev/null
+++ b/sqitch/deploy/package_derivations_by_guix_revision_range.sql
@@ -0,0 +1,17 @@
+-- Deploy guix-data-service:package_derivations_by_guix_revision_range to pg
+
+BEGIN;
+
+CREATE TABLE package_derivations_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,
+  derivation_id integer NOT NULL,
+  system varchar NOT NULL,
+  target 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_derivations_by_guix_revision_range.sql b/sqitch/revert/package_derivations_by_guix_revision_range.sql
new file mode 100644
index 0000000..1cc648d
--- /dev/null
+++ b/sqitch/revert/package_derivations_by_guix_revision_range.sql
@@ -0,0 +1,7 @@
+-- Revert guix-data-service:package_derivations_by_guix_revision_range from pg
+
+BEGIN;
+
+DROP TABLE package_derivations_by_guix_revision_range;
+
+COMMIT;
diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan
index 14a5478..ec87d46 100644
--- a/sqitch/sqitch.plan
+++ b/sqitch/sqitch.plan
@@ -25,3 +25,4 @@ index_on_load_new_guix_revision_job_events_job_id 2019-09-29T10:39:04Z Christoph
 fix_null_values_in_git_branches 2019-09-29T11:06:12Z Christopher Baines <mail@cbaines.net> # Fix the NULL values in git_branches
 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
diff --git a/sqitch/verify/package_derivations_by_guix_revision_range.sql b/sqitch/verify/package_derivations_by_guix_revision_range.sql
new file mode 100644
index 0000000..149abf6
--- /dev/null
+++ b/sqitch/verify/package_derivations_by_guix_revision_range.sql
@@ -0,0 +1,7 @@
+-- Verify guix-data-service:package_derivations_by_guix_revision_range on pg
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;