01/02: Improve the package derivation by guix revision range insert queries

[Christopher Baines]

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

commit 786a5fa0416bdebaf185d228a03791b92277d9e5
Author: Christopher Baines <mail@cbaines.net>
AuthorDate: Thu May 26 00:23:56 2022 +0100

    Improve the package derivation by guix revision range insert queries
---
 .../package-derivation-by-guix-revision-range.scm  | 43 +++++++++++++---------
 1 file changed, 25 insertions(+), 18 deletions(-)

diff --git 
a/guix-data-service/model/package-derivation-by-guix-revision-range.scm 
b/guix-data-service/model/package-derivation-by-guix-revision-range.scm
index da9df81..9ce527e 100644
--- a/guix-data-service/model/package-derivation-by-guix-revision-range.scm
+++ b/guix-data-service/model/package-derivation-by-guix-revision-range.scm
@@ -83,14 +83,11 @@ INNER JOIN (
 INNER JOIN guix_revisions
   ON guix_revisions.git_repository_id = $1
  AND revision_packages.revision_id = guix_revisions.id
-INNER JOIN git_branches
-  ON git_branches.id = $2
 INNER JOIN git_commits
-  ON git_branches.id = git_commits.git_branch_id
+  ON git_commits.git_branch_id = $2
  AND guix_revisions.commit = git_commits.commit
 WINDOW package_version AS (
-  PARTITION BY git_branches.git_repository_id, git_branches.name,
-               packages.name, packages.version, revision_packages.derivation_id
+  PARTITION BY packages.name, packages.version, revision_packages.derivation_id
   ORDER BY git_commits.datetime
   RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 )
@@ -126,7 +123,7 @@ PARTITION OF package_derivations_by_guix_revision_range FOR 
VALUES IN ("
     "
 INSERT INTO package_derivations_by_guix_revision_range
 SELECT DISTINCT
-       git_branches.id,
+       $2,
        packages.name AS package_name,
        packages.version AS package_version,
        revision_packages.derivation_id AS derivation_id,
@@ -152,11 +149,8 @@ INNER JOIN (
 INNER JOIN guix_revisions
   ON guix_revisions.git_repository_id = $1
  AND revision_packages.revision_id = guix_revisions.id
-INNER JOIN git_branches
-  ON git_branches.name = $2
- AND git_branches.git_repository_id = $1
 INNER JOIN git_commits
-  ON git_branches.id = git_commits.git_branch_id
+  ON git_commits.git_branch_id = $2
  AND guix_revisions.commit = git_commits.commit
 WHERE revision_packages.derivation_id IN (
         SELECT package_derivations.derivation_id
@@ -166,19 +160,32 @@ WHERE revision_packages.derivation_id IN (
         WHERE revision_id = $3
       )
 WINDOW package_version AS (
-  PARTITION BY git_branches.git_repository_id, git_branches.name,
-               packages.name, packages.version, revision_packages.derivation_id
+  PARTITION BY packages.name, packages.version, revision_packages.derivation_id
   ORDER BY git_commits.datetime
   RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
 )
 ORDER BY packages.name, packages.version")
 
-  (exec-query
-   conn
-   query
-   (list git-repository-id
-         branch-name
-         guix-revision-id)))
+  (let ((git-branch-id (git-branch-for-repository-and-name conn
+                                                           (string->number
+                                                            git-repository-id)
+                                                           branch-name)))
+    (exec-query
+     conn
+     (string-append
+      "
+CREATE TABLE IF NOT EXISTS 
package_derivations_by_guix_revision_range_git_branch_"
+      (number->string git-branch-id) "
+PARTITION OF package_derivations_by_guix_revision_range FOR VALUES IN ("
+      (number->string git-branch-id)
+      ")"))
+
+    (exec-query
+     conn
+     query
+     (list git-repository-id
+           (number->string git-branch-id)
+           guix-revision-id))))
 
 (define (update-package-derivations-table conn
                                           git-repository-id