diff options
| -rw-r--r-- | guix-data-service/jobs/load-new-guix-revision.scm | 69 | ||||
| -rw-r--r-- | sqitch/deploy/package_versions_by_guix_revision_range.sql | 14 | ||||
| -rw-r--r-- | sqitch/revert/package_versions_by_guix_revision_range.sql | 7 | ||||
| -rw-r--r-- | sqitch/sqitch.plan | 1 | ||||
| -rw-r--r-- | sqitch/verify/package_versions_by_guix_revision_range.sql | 7 |
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; |