[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
01/02: Partition the package_derivations_by_guix_revision_range table
|
From: |
Christopher Baines |
|
Subject: |
01/02: Partition the package_derivations_by_guix_revision_range table |
|
Date: |
Mon, 23 May 2022 14:21:57 -0400 (EDT) |
cbaines pushed a commit to branch master
in repository data-service.
commit 64be52844e679d205d959ea06d50c44366c39097
Author: Christopher Baines <mail@cbaines.net>
AuthorDate: Mon May 23 19:10:25 2022 +0100
Partition the package_derivations_by_guix_revision_range table
And create a proper git_branches table in the process.
I'm hoping this will help with slow deletions from the
package_derivations_by_guix_revision_range table in the case where there are
lots of branches, since it'll separate the data for one branch from another.
These migrations will remove the existing data, so
rebuild-package-derivations-table will currently need manually running to
regenerate it.
---
Makefile.am | 2 +
guix-data-service/branch-updated-emails.scm | 13 +-
guix-data-service/data-deletion.scm | 37 ++++--
guix-data-service/model/git-branch.scm | 136 +++++++++++++--------
guix-data-service/model/git-commit.scm | 38 ++++++
guix-data-service/model/git-repository.scm | 2 +
guix-data-service/model/guix-revision.scm | 11 +-
.../package-derivation-by-guix-revision-range.scm | 124 ++++++++++++++-----
guix-data-service/model/package.scm | 76 ++++++------
guix-data-service/model/system-test.scm | 8 +-
sqitch/deploy/git_branch_id.sql | 32 +++++
..._package_derivations_by_guix_revision_range.sql | 18 +++
sqitch/revert/git_branch_id.sql | 7 ++
..._package_derivations_by_guix_revision_range.sql | 7 ++
sqitch/sqitch.plan | 2 +
sqitch/verify/git_branch_id.sql | 7 ++
..._package_derivations_by_guix_revision_range.sql | 7 ++
tests/model-git-branch.scm | 30 +----
tests/model-git-commit.scm | 52 ++++++++
19 files changed, 442 insertions(+), 167 deletions(-)
diff --git a/Makefile.am b/Makefile.am
index d72c437..9d97045 100644
--- a/Makefile.am
+++ b/Makefile.am
@@ -87,6 +87,7 @@ SOURCES =
\
guix-data-service/model/channel-news.scm
\
guix-data-service/model/derivation.scm
\
guix-data-service/model/git-branch.scm
\
+ guix-data-service/model/git-commit.scm
\
guix-data-service/model/git-repository.scm
\
guix-data-service/model/guix-revision-package-derivation.scm
\
guix-data-service/model/guix-revision.scm
\
@@ -140,6 +141,7 @@ TESTS = \
tests/jobs-load-new-guix-revision.scm \
tests/model-derivation.scm \
tests/model-git-branch.scm \
+ tests/model-git-commit.scm \
tests/model-git-repository.scm \
tests/model-license-set.scm \
tests/model-license.scm \
diff --git a/guix-data-service/branch-updated-emails.scm
b/guix-data-service/branch-updated-emails.scm
index 684c2e3..38432e6 100644
--- a/guix-data-service/branch-updated-emails.scm
+++ b/guix-data-service/branch-updated-emails.scm
@@ -22,6 +22,7 @@
#:use-module (email email)
#:use-module (guix-data-service model git-repository)
#:use-module (guix-data-service model git-branch)
+ #:use-module (guix-data-service model git-commit)
#:use-module (guix-data-service jobs load-new-guix-revision)
#:export (enqueue-job-for-email))
@@ -59,13 +60,19 @@
(when (and (not excluded-branch?)
(or (null? included-branches)
included-branch?))
- (insert-git-branch-entry conn
- branch-name
+ (insert-git-commit-entry conn
+ (or
(git-branch-for-repository-and-name
+ conn
+ git-repository-id
+ branch-name)
+ (insert-git-branch-entry
+ conn
+ git-repository-id
+ branch-name))
(if (string=? commit-all-zeros
x-git-newrev)
""
x-git-newrev)
- git-repository-id
date)
(unless (string=? commit-all-zeros x-git-newrev)
diff --git a/guix-data-service/data-deletion.scm
b/guix-data-service/data-deletion.scm
index 6480785..2e7af48 100644
--- a/guix-data-service/data-deletion.scm
+++ b/guix-data-service/data-deletion.scm
@@ -90,7 +90,7 @@ WHERE guix_revisions.git_repository_id = "
(number->string git-repository-id) " AND
commits.column1 NOT IN (
SELECT commit
- FROM git_branches
+ FROM git_commits
)")))))
(unless (null? guix-revision-ids)
@@ -130,8 +130,10 @@ WHERE id IN ("
AND id NOT IN (
SELECT id FROM guix_revisions
INNER JOIN git_branches ON
- git_branches.commit = guix_revisions.commit AND
git_branches.git_repository_id = guix_revisions.git_repository_id
+ INNER JOIN git_commits ON
+ git_commits.git_branch_id = git_branches.id AND
+ git_commits.commit = guix_revisions.commit
)"))
(delete-unreferenced-package-derivations)
@@ -176,16 +178,22 @@ WHERE git_repository_id = " (number->string
git-repository-id) " AND
", ")
")")))
- (define (delete-from-git-branches conn)
+ (define (delete-from-git-commits conn)
(exec-query
conn
(simple-format
#f
"
-DELETE FROM git_branches
-WHERE git_repository_id = ~A AND
- name = '~A' AND
- commit IN (~A)"
+DELETE FROM git_commits
+WHERE id IN (
+ SELECT id
+ FROM git_commits
+ INNER JOIN git_branches
+ ON git_branches.id = git_commits.git_branch_id
+ WHERE git_branches.git_repository_id = ~A
+ AND git_branches.name = '~A' AND
+ AND git_commits.commit IN (~A)
+)"
git-repository-id
branch-name
(string-join
@@ -197,7 +205,7 @@ WHERE git_repository_id = ~A AND
(with-postgresql-transaction
conn
(lambda (conn)
- (delete-from-git-branches conn)
+ (delete-from-git-commits conn)
(delete-jobs conn)
(exec-query
@@ -216,9 +224,12 @@ WHERE git_repository_id = $1 AND
(map car
(exec-query conn
"
-SELECT commit
+SELECT git_commits.commit
FROM git_branches
-WHERE git_repository_id = $1 AND name = $2"
+INNER JOIN git_commits
+ ON git_branches.id = git_commits.git_branch_id
+WHERE git_repository_id = $1
+ AND git_branches.name = $2"
(list (number->string git-repository-id)
branch-name))))
@@ -236,7 +247,9 @@ WHERE git_repository_id = $1 AND name = $2"
(exec-query conn
"
SELECT commit
-FROM git_branches
+FROM git_commits
+INNER JOIN git_branches
+ ON git_branches.id = git_commits.git_branch_id
WHERE git_repository_id = $1 AND name = $2
ORDER BY datetime DESC
OFFSET $3"
@@ -311,6 +324,8 @@ FROM (
SELECT DISTINCT ON (name, git_repository_id)
name, git_repository_id, commit
FROM git_branches
+ INNER JOIN git_commits
+ ON git_commits.git_branch_id = git_branches.id
ORDER BY git_repository_id, name, datetime DESC
) AS git_branches_latest_revision
WHERE commit = ''")))))
diff --git a/guix-data-service/model/git-branch.scm
b/guix-data-service/model/git-branch.scm
index a9b0202..78e149a 100644
--- a/guix-data-service/model/git-branch.scm
+++ b/guix-data-service/model/git-branch.scm
@@ -21,32 +21,52 @@
#:use-module (squee)
#:use-module (srfi srfi-19)
#:use-module (guix-data-service model utils)
- #:export (insert-git-branch-entry
+ #:export (git-branch-for-repository-and-name
+ insert-git-branch-entry
git-branches-for-commit
git-branches-with-repository-details-for-commit
most-recent-commits-for-branch
latest-processed-commit-for-branch
all-branches-with-most-recent-commit))
+(define (git-branch-for-repository-and-name conn
+ git-repository-id
+ name)
+ (match (exec-query
+ conn
+ "
+SELECT id
+FROM git_branches
+WHERE git_repository_id = $1
+ AND name = $2"
+ (list (number->string git-repository-id)
+ name))
+ (#f #f)
+ (((id)) (string->number id))))
+
(define (insert-git-branch-entry conn
- name commit
- git-repository-id datetime)
- (exec-query
- conn
- (string-append
- "INSERT INTO git_branches (name, commit, git_repository_id, datetime) "
- "VALUES ($1, $2, $3, to_timestamp($4)) "
- "ON CONFLICT DO NOTHING")
- (list name
- commit
- (number->string git-repository-id)
- (date->string datetime "~s"))))
+ git-repository-id
+ name)
+ (match (exec-query
+ conn
+ "
+INSERT INTO git_branches (git_repository_id, name)
+VALUES ($1, $2)
+RETURNING id"
+ (list (number->string git-repository-id)
+ name))
+ (((id))
+ (string->number id))))
(define (git-branches-for-commit conn commit)
(define query
"
-SELECT name, datetime FROM git_branches WHERE commit = $1
-ORDER BY datetime DESC")
+SELECT name, git_commits.datetime
+FROM git_commits
+INNER JOIN git_branches
+ ON git_commits.git_branch_id = git_branches.id
+WHERE commit = $1
+ORDER BY git_commits.datetime DESC")
(exec-query conn query (list commit)))
@@ -55,16 +75,49 @@ ORDER BY datetime DESC")
"
SELECT git_repositories.id, git_repositories.label,
git_repositories.url, git_repositories.cgit_url_base,
- git_branches.name, git_branches.datetime
-FROM git_branches
+ git_branches.name, git_commits.datetime
+FROM git_commits
+INNER JOIN git_branches
+ ON git_commits.git_branch_id = git_branches.id
INNER JOIN git_repositories
ON git_branches.git_repository_id = git_repositories.id
-WHERE git_branches.commit = $1")
+WHERE git_commits.commit = $1")
(group-list-by-first-n-fields
4
(exec-query conn query (list commit))))
+(define* (latest-processed-commit-for-branch conn repository-id branch-name)
+ (define query
+ (string-append
+ "
+SELECT git_commits.commit
+FROM git_branches
+INNER JOIN git_commits
+ ON git_branches.id = git_commits.git_branch_id
+INNER JOIN guix_revisions
+ ON git_commits.commit = guix_revisions.commit
+INNER JOIN load_new_guix_revision_jobs
+ ON load_new_guix_revision_jobs.commit = guix_revisions.commit
+INNER JOIN load_new_guix_revision_job_events
+ ON job_id = load_new_guix_revision_jobs.id
+WHERE guix_revisions.git_repository_id = $1
+ AND git_branches.git_repository_id = $1
+ AND git_branches.name = $2
+ AND load_new_guix_revision_job_events.event = 'success'
+ORDER BY datetime DESC
+LIMIT 1"))
+
+ (match (exec-query
+ conn
+ query
+ (list repository-id branch-name))
+ (((commit-hash))
+ commit-hash)
+ ('()
+ #f)))
+
+
(define* (most-recent-commits-for-branch conn git-repository-id
branch-name
#:key
@@ -74,7 +127,7 @@ WHERE git_branches.commit = $1")
(define query
(string-append
"
-SELECT git_branches.commit,
+SELECT git_commits.commit,
datetime,
(
load_new_guix_revision_job_events.event IS NOT NULL
@@ -84,12 +137,14 @@ SELECT git_branches.commit,
FROM load_new_guix_revision_job_events
INNER JOIN load_new_guix_revision_jobs ON
load_new_guix_revision_jobs.id =
load_new_guix_revision_job_events.job_id
- WHERE load_new_guix_revision_jobs.commit = git_branches.commit AND
+ WHERE load_new_guix_revision_jobs.commit = git_commits.commit AND
git_branches.git_repository_id =
load_new_guix_revision_jobs.git_repository_id
) AS job_events
FROM git_branches
+INNER JOIN git_commits
+ ON git_branches.id = git_commits.git_branch_id
LEFT OUTER JOIN guix_revisions
- ON git_branches.commit = guix_revisions.commit
+ ON git_commits.commit = guix_revisions.commit
LEFT JOIN load_new_guix_revision_jobs
ON load_new_guix_revision_jobs.commit = guix_revisions.commit
LEFT JOIN load_new_guix_revision_job_events
@@ -129,40 +184,12 @@ LIMIT " (number->string limit))
(list branch-name
(number->string git-repository-id)))))
-(define* (latest-processed-commit-for-branch conn repository-id branch-name)
- (define query
- (string-append
- "
-SELECT git_branches.commit
-FROM git_branches
-INNER JOIN guix_revisions
- ON git_branches.commit = guix_revisions.commit
-INNER JOIN load_new_guix_revision_jobs
- ON load_new_guix_revision_jobs.commit = guix_revisions.commit
-INNER JOIN load_new_guix_revision_job_events
- ON job_id = load_new_guix_revision_jobs.id
-WHERE guix_revisions.git_repository_id = $1
- AND git_branches.git_repository_id = $1
- AND git_branches.name = $2
- AND load_new_guix_revision_job_events.event = 'success'
-ORDER BY datetime DESC
-LIMIT 1"))
-
- (match (exec-query
- conn
- query
- (list repository-id branch-name))
- (((commit-hash))
- commit-hash)
- ('()
- #f)))
-
(define (all-branches-with-most-recent-commit conn git-repository-id)
(define query
(string-append
"
SELECT DISTINCT ON (name)
- name, git_branches.commit,
+ name, git_commits.commit,
datetime,
(
load_new_guix_revision_jobs.succeeded_at IS NOT NULL
@@ -172,14 +199,16 @@ SELECT DISTINCT ON (name)
FROM load_new_guix_revision_job_events
INNER JOIN load_new_guix_revision_jobs ON
load_new_guix_revision_jobs.id = load_new_guix_revision_job_events.job_id
- WHERE load_new_guix_revision_jobs.commit = git_branches.commit AND
+ WHERE load_new_guix_revision_jobs.commit = git_commits.commit AND
git_branches.git_repository_id =
load_new_guix_revision_jobs.git_repository_id
) AS job_events
FROM git_branches
+INNER JOIN git_commits
+ ON git_branches.id = git_commits.git_branch_id
LEFT OUTER JOIN guix_revisions
- ON git_branches.commit = guix_revisions.commit
+ ON git_commits.commit = guix_revisions.commit
LEFT JOIN load_new_guix_revision_jobs
- ON git_branches.commit = load_new_guix_revision_jobs.commit
+ ON git_commits.commit = load_new_guix_revision_jobs.commit
AND git_branches.git_repository_id =
load_new_guix_revision_jobs.git_repository_id
WHERE git_branches.git_repository_id = $1
ORDER BY name, datetime DESC"))
@@ -199,4 +228,3 @@ ORDER BY name, datetime DESC"))
conn
query
(list (number->string git-repository-id)))))
-
diff --git a/guix-data-service/model/git-commit.scm
b/guix-data-service/model/git-commit.scm
new file mode 100644
index 0000000..d017384
--- /dev/null
+++ b/guix-data-service/model/git-commit.scm
@@ -0,0 +1,38 @@
+;;; Guix Data Service -- Information about Guix over time
+;;; Copyright © 2019 Christopher Baines <mail@cbaines.net>
+;;;
+;;; This program is free software: you can redistribute it and/or
+;;; modify it under the terms of the GNU Affero General Public License
+;;; as published by the Free Software Foundation, either version 3 of
+;;; the License, or (at your option) any later version.
+;;;
+;;; This program is distributed in the hope that it will be useful,
+;;; but WITHOUT ANY WARRANTY; without even the implied warranty of
+;;; MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
+;;; Affero General Public License for more details.
+;;;
+;;; You should have received a copy of the GNU Affero General Public
+;;; License along with this program. If not, see
+;;; <http://www.gnu.org/licenses/>.
+
+(define-module (guix-data-service model git-commit)
+ #:use-module (ice-9 match)
+ #:use-module (json)
+ #:use-module (squee)
+ #:use-module (srfi srfi-19)
+ #:use-module (guix-data-service model utils)
+ #:export (insert-git-commit-entry))
+
+(define (insert-git-commit-entry conn
+ git-branch-id
+ commit
+ datetime)
+ (exec-query
+ conn
+ "
+INSERT INTO git_commits (commit, git_branch_id, datetime)
+VALUES ($1, $2, to_timestamp($3))
+ON CONFLICT DO NOTHING"
+ (list commit
+ (number->string git-branch-id)
+ (date->string datetime "~s"))))
diff --git a/guix-data-service/model/git-repository.scm
b/guix-data-service/model/git-repository.scm
index 9db682e..35342f6 100644
--- a/guix-data-service/model/git-repository.scm
+++ b/guix-data-service/model/git-repository.scm
@@ -163,6 +163,8 @@ SELECT id, label, url, cgit_url_base
FROM git_repositories WHERE id IN (
SELECT git_repository_id
FROM git_branches
+ INNER JOIN git_commits
+ ON git_branches.id = git_commits.git_branch_id
WHERE commit = $1
)")
diff --git a/guix-data-service/model/guix-revision.scm
b/guix-data-service/model/guix-revision.scm
index 2ebeacc..40bff02 100644
--- a/guix-data-service/model/guix-revision.scm
+++ b/guix-data-service/model/guix-revision.scm
@@ -95,17 +95,18 @@ INSERT INTO guix_revisions (git_repository_id, commit)
SELECT guix_revisions.id,
guix_revisions.commit,
guix_revisions.git_repository_id,
- git_branches.datetime
+ git_commits.datetime
FROM guix_revisions
INNER JOIN git_branches
- ON git_branches.commit = guix_revisions.commit
- AND git_branches.git_repository_id = guix_revisions.git_repository_id
+ ON git_branches.git_repository_id = guix_revisions.git_repository_id
+INNER JOIN git_commits
+ ON git_commits.commit = guix_revisions.commit
INNER JOIN load_new_guix_revision_jobs
ON load_new_guix_revision_jobs.commit = guix_revisions.commit
WHERE git_branches.name = $1
- AND git_branches.datetime <= $2
+ AND git_commits.datetime <= $2
AND load_new_guix_revision_jobs.succeeded_at IS NOT NULL
-ORDER BY git_branches.datetime DESC
+ORDER BY git_commits.datetime DESC
LIMIT 1")
(match (exec-query conn query
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 4bcba1b..671771e 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
@@ -19,9 +19,9 @@
#:use-module (ice-9 match)
#:use-module (squee)
#:use-module (guix-data-service database)
+ #:use-module (guix-data-service model git-branch)
#:use-module (guix-data-service utils)
- #:export (delete-guix-revision-package-derivation-entries
- insert-guix-revision-package-derivation-entries
+ #:export (insert-guix-revision-package-derivation-entries
update-package-derivations-table
vacuum-package-derivations-table
rebuild-package-derivations-table))
@@ -47,17 +47,83 @@ WHERE git_repository_id = $1 AND
branch-name
guix-revision-id)))
-(define* (insert-guix-revision-package-derivation-entries conn
- git-repository-id
- branch-name
- #:key
guix-revision-id)
+(define (insert-guix-revision-package-derivation-entries conn
+ git-repository-id
+ branch-name)
+ (define query
+ "
+INSERT INTO package_derivations_by_guix_revision_range
+SELECT DISTINCT
+ $1,
+ 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,
+ systems.system,
+ package_derivations.target,
+ guix_revision_package_derivations.revision_id
+ FROM package_derivations
+ INNER JOIN systems
+ ON package_derivations.system_id = systems.id
+ 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 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
+ 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
+ ORDER BY git_commits.datetime
+ RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
+)
+ORDER BY packages.name, packages.version")
+
+ (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 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)))))
+
+(define (insert-guix-revision-package-derivation-entries-for-guix-revision
+ conn
+ git-repository-id
+ branch-name
+ guix-revision-id)
+
(define query
- (string-append
- "
+ "
INSERT INTO package_derivations_by_guix_revision_range
SELECT DISTINCT
- git_branches.git_repository_id,
- git_branches.name AS branch_name,
+ git_branches.id
packages.name AS package_name,
packages.version AS package_version,
revision_packages.derivation_id AS derivation_id,
@@ -85,35 +151,30 @@ INNER JOIN guix_revisions
AND revision_packages.revision_id = guix_revisions.id
INNER JOIN git_branches
ON git_branches.name = $2
- AND guix_revisions.commit = git_branches.commit
-"
- (if guix-revision-id
- "WHERE
- revision_packages.derivation_id IN (
+INNER JOIN git_commits
+ ON git_branches.id = git_commits.git_branch_id
+ AND guix_revisions.commit = git_commits.commit
+WHERE revision_packages.derivation_id IN (
SELECT package_derivations.derivation_id
FROM package_derivations
INNER JOIN guix_revision_package_derivations
ON package_derivations.id =
guix_revision_package_derivations.package_derivation_id
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
- ORDER BY git_branches.datetime
+ ORDER BY git_commits.datetime
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
-ORDER BY packages.name, packages.version"))
+ORDER BY packages.name, packages.version")
(exec-query
conn
query
- `(,git-repository-id
- ,branch-name
- ,@(if guix-revision-id
- (list guix-revision-id)
- '()))))
+ (list git-repository-id
+ branch-name
+ guix-revision-id)))
(define (update-package-derivations-table conn
git-repository-id
@@ -140,14 +201,21 @@ LOCK TABLE ONLY package_derivations_by_guix_revision_range
(with-time-logging
(simple-format #f "inserting package derivation entries for ~A"
branch-name)
- (insert-guix-revision-package-derivation-entries
+ (insert-guix-revision-package-derivation-entries-for-guix-revision
conn
git-repository-id
branch-name
- #:guix-revision-id guix-revision-id))))
+ guix-revision-id))))
(exec-query
conn
- "SELECT name FROM git_branches WHERE commit = $1 AND git_repository_id =
$2"
+ "
+SELECT name
+FROM git_branches
+INNER JOIN git_commits
+ ON git_branches.id = git_commits.git_branch_id
+ AND git_commits.commit = $1
+WHERE git_repository_id = $2
+"
(list commit git-repository-id)))
#t)
diff --git a/guix-data-service/model/package.scm
b/guix-data-service/model/package.scm
index 97deefc..c0b1c40 100644
--- a/guix-data-service/model/package.scm
+++ b/guix-data-service/model/package.scm
@@ -306,26 +306,28 @@ FROM (
-- be parameters?
package_version,
first_guix_revisions.commit AS first_guix_revision_commit,
- first_git_branches.datetime AS first_datetime,
+ first_git_commits.datetime AS first_datetime,
last_guix_revisions.commit AS last_guix_revision_commit,
- last_git_branches.datetime AS last_datetime
+ last_git_commits.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 package_derivations_by_guix_revision_range.branch_name =
first_git_branches.name
- AND first_guix_revisions.git_repository_id =
first_git_branches.git_repository_id
- AND first_guix_revisions.commit = first_git_branches.commit
+ INNER JOIN git_branches
+ ON git_branches.git_repository_id = $2
+ AND git_branches.name = $3
+ INNER JOIN git_commits AS first_git_commits
+ ON first_git_commits.git_branch_id = git_branches.id
+ AND first_guix_revisions.git_repository_id = git_branches.git_repository_id
+ AND first_guix_revisions.commit = first_git_commits.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 package_derivations_by_guix_revision_range.branch_name =
last_git_branches.name
- AND last_guix_revisions.git_repository_id =
last_git_branches.git_repository_id
- AND last_guix_revisions.commit = last_git_branches.commit
+ AND last_guix_revisions.git_repository_id = git_branches.git_repository_id
+ INNER JOIN git_commits AS last_git_commits
+ ON last_git_commits.git_branch_id = git_branches.id
+ AND last_guix_revisions.commit = last_git_commits.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
- ORDER BY package_version DESC, first_git_branches.datetime ASC
+ AND package_derivations_by_guix_revision_range.git_branch_id =
git_branches.id
+ ORDER BY package_version DESC, first_git_commits.datetime ASC
) AS data1
WINDOW version_window AS (
PARTITION BY data1.package_version
@@ -348,9 +350,9 @@ ORDER BY package_version DESC, first_datetime ASC"
SELECT package_version,
derivations.file_name,
first_guix_revisions.commit AS first_guix_revision_commit,
- first_git_branches.datetime AS first_datetime,
+ first_git_commits.datetime AS first_datetime,
last_guix_revisions.commit AS last_guix_revision_commit,
- last_git_branches.datetime AS last_datetime,
+ last_git_commits.datetime AS last_datetime,
JSON_AGG(
json_build_object(
'build_server_id', builds.build_server_id,
@@ -374,19 +376,19 @@ LEFT OUTER JOIN latest_build_status
ON builds.id = latest_build_status.build_id
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 git_branches
+ ON git_branches.git_repository_id = $2
+ AND git_branches.name = $3
+INNER JOIN git_commits AS first_git_commits
+ ON first_guix_revisions.commit = first_git_commits.commit
+ AND first_git_commits.git_branch_id = git_branches.id
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
+INNER JOIN git_commits AS last_git_commits
+ ON last_guix_revisions.commit = last_git_commits.commit
+ AND last_git_commits.git_branch_id = git_branches.id
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
+AND package_derivations_by_guix_revision_range.git_branch_id = git_branches.id
AND package_derivations_by_guix_revision_range.system = $4
AND package_derivations_by_guix_revision_range.target = $5
GROUP BY 1, 2, 3, 4, 5, 6
@@ -459,9 +461,9 @@ FROM (
derivation_output_details.path,
derivations_by_output_details_set.derivation_output_details_set_id,
first_guix_revisions.commit AS first_guix_revision_commit,
- first_git_branches.datetime AS first_datetime,
+ first_git_commits.datetime AS first_datetime,
last_guix_revisions.commit AS last_guix_revision_commit,
- last_git_branches.datetime AS last_datetime
+ last_git_commits.datetime AS last_datetime
FROM package_derivations_by_guix_revision_range
INNER JOIN derivations
ON package_derivations_by_guix_revision_range.derivation_id =
derivations.id
@@ -469,24 +471,24 @@ FROM (
ON derivation_outputs.derivation_id = derivations.id
INNER JOIN derivation_output_details
ON derivation_outputs.derivation_output_details_id =
derivation_output_details.id
+ INNER JOIN git_branches
+ ON git_branches.git_repository_id = $2
+ AND git_branches.name = $3
INNER JOIN guix_revisions AS first_guix_revisions
ON first_guix_revision_id = first_guix_revisions.id
INNER JOIN derivations_by_output_details_set
ON derivations_by_output_details_set.derivation_id = derivations.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 git_commits AS first_git_commits
+ ON first_git_commits.git_branch_id = git_branches.id
+ AND first_guix_revisions.commit = first_git_commits.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
+ INNER JOIN git_commits AS last_git_commits
+ ON last_git_commits.git_branch_id = git_branches.id
+ AND last_guix_revisions.commit = last_git_commits.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 package_derivations_by_guix_revision_range.git_branch_id =
git_branches.id
AND derivation_outputs.name = $4
- AND first_git_branches.name = $3
- AND last_git_branches.name = $3
AND package_derivations_by_guix_revision_range.system = $5
AND package_derivations_by_guix_revision_range.target = $6
) AS data1
diff --git a/guix-data-service/model/system-test.scm
b/guix-data-service/model/system-test.scm
index 3a37cd4..fe2fb83 100644
--- a/guix-data-service/model/system-test.scm
+++ b/guix-data-service/model/system-test.scm
@@ -176,7 +176,7 @@ FROM (
OVER derivation_window AS last_datetime
FROM (
SELECT guix_revision_id,
- git_branches.datetime,
+ git_commits.datetime,
derivation_id
FROM guix_revision_system_test_derivations
INNER JOIN system_tests
@@ -185,10 +185,12 @@ FROM (
ON guix_revisions.id = guix_revision_id
INNER JOIN git_branches
ON guix_revisions.git_repository_id = git_branches.git_repository_id
- AND git_branches.commit = guix_revisions.commit
+ AND git_branches.name = $3
+ INNER JOIN git_commits
+ ON git_branches.id = git_commits.branch_id
+ AND guix_revisions.commit = git_commits.commit
WHERE system_tests.name = $1
AND guix_revisions.git_repository_id = $2
- AND git_branches.name = $3
AND system = $4
) AS data1
WINDOW derivation_window AS (
diff --git a/sqitch/deploy/git_branch_id.sql b/sqitch/deploy/git_branch_id.sql
new file mode 100644
index 0000000..9da071c
--- /dev/null
+++ b/sqitch/deploy/git_branch_id.sql
@@ -0,0 +1,32 @@
+-- Deploy guix-data-service:git_branch_id to pg
+
+BEGIN;
+
+ALTER TABLE git_branches RENAME TO git_branches_old;
+
+CREATE TABLE git_branches (
+ id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
+ name character varying NOT NULL,
+ git_repository_id integer NOT NULL REFERENCES git_repositories (id),
+ CONSTRAINT git_repository_id_name_unique UNIQUE (git_repository_id, name)
+);
+
+CREATE TABLE git_commits (
+ id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
+ commit character varying NOT NULL,
+ git_branch_id integer NOT NULL REFERENCES git_branches (id),
+ datetime timestamp without time zone NOT NULL
+);
+
+INSERT INTO git_branches (name, git_repository_id)
+ SELECT DISTINCT name, git_repository_id
+ FROM git_branches_old;
+
+INSERT INTO git_commits (commit, git_branch_id, datetime)
+ SELECT commit, git_branches.id, datetime
+ FROM git_branches_old
+ INNER JOIN git_branches
+ ON git_branches_old.name = git_branches.name
+ AND git_branches_old.git_repository_id = git_branches.git_repository_id;
+
+COMMIT;
diff --git
a/sqitch/deploy/partition_package_derivations_by_guix_revision_range.sql
b/sqitch/deploy/partition_package_derivations_by_guix_revision_range.sql
new file mode 100644
index 0000000..88611f6
--- /dev/null
+++ b/sqitch/deploy/partition_package_derivations_by_guix_revision_range.sql
@@ -0,0 +1,18 @@
+-- Deploy
guix-data-service:partition_package_derivations_by_guix_revision_range to pg
+
+BEGIN;
+
+DROP TABLE package_derivations_by_guix_revision_range;
+
+CREATE TABLE package_derivations_by_guix_revision_range (
+ git_branch_id integer NOT NULL REFERENCES git_branches (id),
+ package_name varchar NOT NULL,
+ package_version varchar NOT NULL,
+ derivation_id integer NOT NULL,
+ system varchar NOT NULL,
+ target 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)
+) PARTITION BY LIST (git_branch_id);
+
+COMMIT;
diff --git a/sqitch/revert/git_branch_id.sql b/sqitch/revert/git_branch_id.sql
new file mode 100644
index 0000000..d9a18da
--- /dev/null
+++ b/sqitch/revert/git_branch_id.sql
@@ -0,0 +1,7 @@
+-- Revert guix-data-service:git_branch_id from pg
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git
a/sqitch/revert/partition_package_derivations_by_guix_revision_range.sql
b/sqitch/revert/partition_package_derivations_by_guix_revision_range.sql
new file mode 100644
index 0000000..f3a1436
--- /dev/null
+++ b/sqitch/revert/partition_package_derivations_by_guix_revision_range.sql
@@ -0,0 +1,7 @@
+-- Revert
guix-data-service:partition_package_derivations_by_guix_revision_range from pg
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan
index 44138d6..1f329a5 100644
--- a/sqitch/sqitch.plan
+++ b/sqitch/sqitch.plan
@@ -83,3 +83,5 @@ some_indexes 2021-05-17T17:36:38Z Christopher Baines
<mail@cbaines.net> # Add so
package_metadata_location_id_index 2021-05-27T19:51:13Z Canan Talayhan
<canan.t.talayhan@gmail.com> # Add index for location id
packages_replacement 2021-04-24T04:52:57Z Christopher Baines
<mail@cbaines.net> # Add packages.replacement_package_id
package_derivations_not_null_hash_index 2021-07-11T14:19:32Z Christopher
Baines <mail@cbaines.net> # Add an index to package_derivations
+git_branch_id 2022-05-23T18:11:14Z Chris <chris@felis> # Add git_branch.id
+partition_package_derivations_by_guix_revision_range 2022-05-23T18:20:37Z
Chris <chris@felis> # Partition package_derivations_by_guix_revision_range
diff --git a/sqitch/verify/git_branch_id.sql b/sqitch/verify/git_branch_id.sql
new file mode 100644
index 0000000..aa5749b
--- /dev/null
+++ b/sqitch/verify/git_branch_id.sql
@@ -0,0 +1,7 @@
+-- Verify guix-data-service:git_branch_id on pg
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;
diff --git
a/sqitch/verify/partition_package_derivations_by_guix_revision_range.sql
b/sqitch/verify/partition_package_derivations_by_guix_revision_range.sql
new file mode 100644
index 0000000..8401cf4
--- /dev/null
+++ b/sqitch/verify/partition_package_derivations_by_guix_revision_range.sql
@@ -0,0 +1,7 @@
+-- Verify
guix-data-service:partition_package_derivations_by_guix_revision_range on pg
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;
diff --git a/tests/model-git-branch.scm b/tests/model-git-branch.scm
index 46412bc..1bcc1c3 100644
--- a/tests/model-git-branch.scm
+++ b/tests/model-git-branch.scm
@@ -17,32 +17,10 @@
conn
(lambda (conn)
(let* ((url "test-url")
- (id (git-repository-url->git-repository-id conn url)))
- (insert-git-branch-entry conn
- "master"
- "test-commit"
- id
- (current-date)))
- #t)
- #:always-rollback? #t))
-
- (test-assert "insert-git-branch-entry works twice"
- (with-postgresql-transaction
- conn
- (lambda (conn)
- (let* ((url "test-url")
- (id (git-repository-url->git-repository-id conn url)))
- (insert-git-branch-entry conn
- "master"
- "test-commit"
- id
- (current-date))
- (insert-git-branch-entry conn
- "master"
- "test-commit"
- id
- (current-date)))
- #t)
+ (git-repository-id
+ (git-repository-url->git-repository-id conn url)))
+ (insert-git-branch-entry conn git-repository-id "master")
+ #t))
#:always-rollback? #t))))
(test-end)
diff --git a/tests/model-git-commit.scm b/tests/model-git-commit.scm
new file mode 100644
index 0000000..b8bc3d8
--- /dev/null
+++ b/tests/model-git-commit.scm
@@ -0,0 +1,52 @@
+(define-module (test-model-git-commit)
+ #:use-module (srfi srfi-19)
+ #:use-module (srfi srfi-64)
+ #:use-module (guix-data-service database)
+ #:use-module (guix-data-service model git-repository)
+ #:use-module (guix-data-service model git-branch)
+ #:use-module (guix-data-service model git-commit))
+
+(test-begin "test-model-git-commit")
+
+(with-postgresql-connection
+ "test-module-git-commit"
+ (lambda (conn)
+ (check-test-database! conn)
+
+ (test-assert "insert-git-commit-entry works"
+ (with-postgresql-transaction
+ conn
+ (lambda (conn)
+ (let* ((url "test-url")
+ (git-repository-id
+ (git-repository-url->git-repository-id conn url))
+ (git-branch-id
+ (insert-git-branch-entry conn git-repository-id "master")))
+ (insert-git-commit-entry conn
+ git-branch-id
+ "test-commit"
+ (current-date)))
+ #t)
+ #:always-rollback? #t))
+
+ (test-assert "insert-git-commit-entry works twice"
+ (with-postgresql-transaction
+ conn
+ (lambda (conn)
+ (let* ((url "test-url")
+ (git-repository-id
+ (git-repository-url->git-repository-id conn url))
+ (git-branch-id
+ (insert-git-branch-entry conn git-repository-id "master")))
+ (insert-git-commit-entry conn
+ git-branch-id
+ "test-commit"
+ (current-date))
+ (insert-git-commit-entry conn
+ git-branch-id
+ "test-commit"
+ (current-date)))
+ #t)
+ #:always-rollback? #t))))
+
+(test-end)