aboutsummaryrefslogtreecommitdiff
path: root/scripts/guix-data-service-create-small-backup
diff options
context:
space:
mode:
authorChristopher Baines <mail@cbaines.net>2020-03-31 20:46:18 +0100
committerChristopher Baines <mail@cbaines.net>2020-03-31 20:46:18 +0100
commit5081a64c1fec802d8e3f8503a4e97d1501b689a5 (patch)
tree4f1b780ac12ad4bb14e84a86f2f97c25e0e6946f /scripts/guix-data-service-create-small-backup
parent94030b736c0733dffe4c67275d14429ea0730f07 (diff)
downloaddata-service-5081a64c1fec802d8e3f8503a4e97d1501b689a5.tar
data-service-5081a64c1fec802d8e3f8503a4e97d1501b689a5.tar.gz
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).
Diffstat (limited to 'scripts/guix-data-service-create-small-backup')
-rwxr-xr-xscripts/guix-data-service-create-small-backup44
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