03/05: Stop using package_versions_by_guix_revision_range

[Christopher Baines]

cbaines pushed a commit to branch master
in repository data-service.

commit ca0d3ee7543b64eca32422a8e29f5f853923750f
Author: Christopher Baines <address@hidden>
AuthorDate: Tue Mar 24 20:44:57 2020 +0000

    Stop using package_versions_by_guix_revision_range
    
    It's been replaced by the package_derivations_by_guix_revision_range table.
---
 guix-data-service/data-deletion.scm                |  3 +-
 guix-data-service/jobs/load-new-guix-revision.scm  | 63 ----------------------
 scripts/guix-data-service-create-small-backup      | 12 -----
 ...rop_package_versions_by_guix_revision_range.sql |  7 +++
 ...rop_package_versions_by_guix_revision_range.sql |  7 +++
 sqitch/sqitch.plan                                 |  1 +
 ...rop_package_versions_by_guix_revision_range.sql |  7 +++
 7 files changed, 23 insertions(+), 77 deletions(-)

diff --git a/guix-data-service/data-deletion.scm 
b/guix-data-service/data-deletion.scm
index 1a20124..296e000 100644
--- a/guix-data-service/data-deletion.scm
+++ b/guix-data-service/data-deletion.scm
@@ -66,8 +66,7 @@ WHERE branch_name = $1 AND git_repository_id = $2"
           table)
          (list branch-name
                (number->string git-repository-id))))
-      '("package_versions_by_guix_revision_range"
-        "package_derivations_by_guix_revision_range"))
+      '("package_derivations_by_guix_revision_range"))
 
      (for-each
       (lambda (table)
diff --git a/guix-data-service/jobs/load-new-guix-revision.scm 
b/guix-data-service/jobs/load-new-guix-revision.scm
index d5a54f9..7781c76 100644
--- a/guix-data-service/jobs/load-new-guix-revision.scm
+++ b/guix-data-service/jobs/load-new-guix-revision.scm
@@ -1154,68 +1154,6 @@ WHERE job_id = $1"
 
 (prevent-inlining-for-tests extract-information-from)
 
-(define (update-package-versions-table conn git-repository-id commit)
-  (with-time-logging "lock table: package_versions_by_guix_revision_range"
-    ;; 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)
-      (with-time-logging
-          (simple-format #f "deleting package version entries for ~A" 
branch-name)
-        (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)))
-      (with-time-logging
-          (simple-format #f "inserting package version entries for ~A" 
branch-name)
-        (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 store git-repository-id commit)
   (let* ((channel-for-commit
           (channel (name 'guix)
@@ -1262,7 +1200,6 @@ ORDER BY packages.name, packages.version"
                  (simple-format #t "debug: importing channel news not 
supported\n")
                  #t))
 
-           (update-package-versions-table conn git-repository-id commit)
            (update-package-derivations-table conn
                                              git-repository-id
                                              guix-revision-id
diff --git a/scripts/guix-data-service-create-small-backup 
b/scripts/guix-data-service-create-small-backup
index 6616c52..e5f5774 100755
--- a/scripts/guix-data-service-create-small-backup
+++ b/scripts/guix-data-service-create-small-backup
@@ -180,18 +180,6 @@ WITH guix_revision_ids AS (
     SELECT commit FROM git_branches
   )
 )
-DELETE FROM package_versions_by_guix_revision_range
-WHERE first_guix_revision_id NOT IN (SELECT id FROM guix_revision_ids) OR
-      last_guix_revision_id NOT IN (SELECT id FROM guix_revision_ids);
-EOF
-
-psql -v ON_ERROR_STOP=1 --echo-queries --no-psqlrc "$URI_FOR_DATABASE" -U 
guix_data_service <<EOF &
-
-WITH guix_revision_ids AS (
-  SELECT id FROM guix_revisions WHERE commit IN (
-    SELECT commit FROM git_branches
-  )
-)
 DELETE FROM package_derivations_by_guix_revision_range
 WHERE first_guix_revision_id NOT IN (SELECT id FROM guix_revision_ids) OR
       last_guix_revision_id NOT IN (SELECT id FROM guix_revision_ids);
diff --git a/sqitch/deploy/drop_package_versions_by_guix_revision_range.sql 
b/sqitch/deploy/drop_package_versions_by_guix_revision_range.sql
new file mode 100644
index 0000000..f954349
--- /dev/null
+++ b/sqitch/deploy/drop_package_versions_by_guix_revision_range.sql
@@ -0,0 +1,7 @@
+-- Deploy guix-data-service:drop_package_versions_by_guix_revision_range to pg
+
+BEGIN;
+
+DROP TABLE package_versions_by_guix_revision_range;
+
+COMMIT;
diff --git a/sqitch/revert/drop_package_versions_by_guix_revision_range.sql 
b/sqitch/revert/drop_package_versions_by_guix_revision_range.sql
new file mode 100644
index 0000000..31a0169
--- /dev/null
+++ b/sqitch/revert/drop_package_versions_by_guix_revision_range.sql
@@ -0,0 +1,7 @@
+-- Revert guix-data-service:drop_package_versions_by_guix_revision_range from 
pg
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan
index 975d7ee..d770077 100644
--- a/sqitch/sqitch.plan
+++ b/sqitch/sqitch.plan
@@ -53,3 +53,4 @@ channel_instance_derivations 2020-02-10T07:59:03Z Christopher 
Baines <mail@cbain
 update_build_servers_build_config 2020-02-13T20:07:19Z Christopher Baines 
<address@hidden> # Update build_servers_build_config values
 make_some_constraints_deferrable 2020-02-16T10:54:22Z Christopher Baines 
<address@hidden> # Make some constraints deferrable
 guix_revision_system_test_derivations_add_system 2020-03-19T21:30:33Z 
Christopher Baines <address@hidden> # Add a system column to the 
guix_revision_system_test_derivations table
+drop_package_versions_by_guix_revision_range 2020-03-24T20:40:38Z Christopher 
Baines <address@hidden> # Drop package_versions_by_guix_revision_range
diff --git a/sqitch/verify/drop_package_versions_by_guix_revision_range.sql 
b/sqitch/verify/drop_package_versions_by_guix_revision_range.sql
new file mode 100644
index 0000000..489f443
--- /dev/null
+++ b/sqitch/verify/drop_package_versions_by_guix_revision_range.sql
@@ -0,0 +1,7 @@
+-- Verify guix-data-service:drop_package_versions_by_guix_revision_range on pg
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;