From 102f7a0536b76f60c06aa42ef1677ca76a4b18a7 Mon Sep 17 00:00:00 2001 From: Christopher Baines Date: Tue, 24 Mar 2020 20:17:18 +0000 Subject: Implement version history using the derivations table Rather than having two big tables looking at the history, just use the derivations table as it has all the information. This will allow deleting the package_versions_by_guix_revision_range table which should help save time when importing revisions, and reduce the size of the database. --- guix-data-service/model/package.scm | 52 ++++++++++++++++++++++--------------- 1 file changed, 31 insertions(+), 21 deletions(-) diff --git a/guix-data-service/model/package.scm b/guix-data-service/model/package.scm index f484fab..dc5cc4c 100644 --- a/guix-data-service/model/package.scm +++ b/guix-data-service/model/package.scm @@ -230,27 +230,37 @@ ORDER BY version") (exec-query conn " -SELECT package_version, - first_guix_revisions.commit AS first_guix_revision_commit, - first_git_branches.datetime AS first_datetime, - last_guix_revisions.commit AS last_guix_revision_commit, - last_git_branches.datetime AS last_datetime -FROM package_versions_by_guix_revision_range -INNER JOIN guix_revisions AS first_guix_revisions - ON first_guix_revision_id = first_guix_revisions.id -INNER JOIN git_branches AS first_git_branches - ON first_guix_revisions.git_repository_id = first_git_branches.git_repository_id - AND first_guix_revisions.commit = first_git_branches.commit -INNER JOIN guix_revisions AS last_guix_revisions - ON last_guix_revision_id = last_guix_revisions.id -INNER JOIN git_branches AS last_git_branches - ON last_guix_revisions.git_repository_id = last_git_branches.git_repository_id - AND last_guix_revisions.commit = last_git_branches.commit -WHERE package_name = $1 -AND package_versions_by_guix_revision_range.git_repository_id = $2 -AND package_versions_by_guix_revision_range.branch_name = $3 -AND first_git_branches.name = $3 -AND last_git_branches.name = $3 +SELECT DISTINCT + data1.package_version, + first_value(first_guix_revision_commit) OVER version_window AS first_guix_revision_commit, + first_value(first_datetime) OVER version_window AS first_datetime, + last_value(last_guix_revision_commit) OVER version_window AS last_guix_revision_commit, + last_value(last_datetime) OVER version_window AS last_datetime +FROM ( + SELECT package_version, + first_guix_revisions.commit AS first_guix_revision_commit, + first_git_branches.datetime AS first_datetime, + last_guix_revisions.commit AS last_guix_revision_commit, + last_git_branches.datetime AS last_datetime + FROM package_derivations_by_guix_revision_range + INNER JOIN guix_revisions AS first_guix_revisions + ON first_guix_revision_id = first_guix_revisions.id + INNER JOIN git_branches AS first_git_branches + ON first_guix_revisions.git_repository_id = first_git_branches.git_repository_id + AND first_guix_revisions.commit = first_git_branches.commit + INNER JOIN guix_revisions AS last_guix_revisions + ON last_guix_revision_id = last_guix_revisions.id + INNER JOIN git_branches AS last_git_branches + ON last_guix_revisions.git_repository_id = last_git_branches.git_repository_id + AND last_guix_revisions.commit = last_git_branches.commit + WHERE package_name = $1 + AND package_derivations_by_guix_revision_range.git_repository_id = $2 + AND package_derivations_by_guix_revision_range.branch_name = $3 + AND first_git_branches.name = $3 + AND last_git_branches.name = $3 + ORDER BY first_datetime ASC, package_version DESC +) AS data1 +WINDOW version_window AS (PARTITION BY package_version) ORDER BY first_datetime DESC, package_version DESC" (list package-name (number->string git-repository-id) -- cgit v1.2.3