guix-commits
[Top][All Lists]
Advanced

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

02/05: Implement version history using the derivations table


From: Christopher Baines
Subject: 02/05: Implement version history using the derivations table
Date: Wed, 25 Mar 2020 04:14:50 -0400 (EDT)

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

commit 102f7a0536b76f60c06aa42ef1677ca76a4b18a7
Author: Christopher Baines <address@hidden>
AuthorDate: Tue Mar 24 20:17:18 2020 +0000

    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)



reply via email to

[Prev in Thread] Current Thread [Next in Thread]