Rebuild the package derivation ranges table for the small backup

This is better than just deleting the entries that don't match up with the
remaining revisions, but also not very useful for local development (due to
the lack of data).

1 files changed, 37 insertions, 7 deletions

diff --git a/scripts/guix-data-service-create-small-backup b/scripts/guix-data-service-create-small-backup
index b7e5c25..9020f3a 100755
--- a/scripts/guix-data-service-create-small-backup
+++ b/scripts/guix-data-service-create-small-backup
@@ -212,14 +212,44 @@ 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
-  )
+TRUNCATE package_derivations_by_guix_revision_range;
+
+INSERT INTO package_derivations_by_guix_revision_range
+SELECT DISTINCT
+       git_branches.git_repository_id,
+       git_branches.name 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 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
+WINDOW package_version AS (
+  PARTITION BY git_branches.git_repository_id, git_branches.name,
+               packages.name, packages.version, revision_packages.derivation_id
+  ORDER BY git_branches.datetime
+  RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 )
-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);
+ORDER BY packages.name, packages.version;
+
 EOF
 
 wait