From 773e5a9c3896dc022f913602ca8e1fdc09e4b592 Mon Sep 17 00:00:00 2001 From: Christopher Baines Date: Sat, 15 Feb 2020 11:36:31 +0000 Subject: Add a module to handle deleting data This, along with the way of specifying which branches are processed is a way to manage the data stored within the Guix Data Service. This only goes so far, it doesn't delete derivations, but it does delete some of the information related to a revision. --- Makefile.am | 1 + guix-data-service/data-deletion.scm | 165 ++++++++++++++++++++++++++++++++++++ 2 files changed, 166 insertions(+) create mode 100644 guix-data-service/data-deletion.scm diff --git a/Makefile.am b/Makefile.am index 1f05b9c..b10c251 100644 --- a/Makefile.am +++ b/Makefile.am @@ -70,6 +70,7 @@ SOURCES = \ guix-data-service/comparison.scm \ guix-data-service/config.scm \ guix-data-service/database.scm \ + guix-data-service/data-deletion.scm \ guix-data-service/jobs.scm \ guix-data-service/jobs/load-new-guix-revision.scm \ guix-data-service/model/build-server.scm \ diff --git a/guix-data-service/data-deletion.scm b/guix-data-service/data-deletion.scm new file mode 100644 index 0000000..18aeb85 --- /dev/null +++ b/guix-data-service/data-deletion.scm @@ -0,0 +1,165 @@ +;;; Guix Data Service -- Information about Guix over time +;;; Copyright © 2020 Christopher Baines +;;; +;;; 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 +;;; . + +(define-module (guix-data-service data-deletion) + #:use-module (squee) + #:use-module (guix-data-service database) + #:export (delete-data-for-branch)) + +(define (delete-data-for-branch conn git-repository-id branch-name) + (define commits + (map car + (exec-query conn + " +SELECT commit +FROM git_branches +WHERE git_repository_id = $1 AND name = $2" + (list (number->string git-repository-id) + branch-name)))) + + + (with-postgresql-transaction + conn + (lambda (conn) + (exec-query + conn + (simple-format + #f + " +DELETE FROM git_branches +WHERE git_repository_id = ~A AND + name = '~A' AND + commit IN (~A)" + git-repository-id + branch-name + (string-join + (map (lambda (commit) + (string-append "'" commit "'")) + commits) + ", "))) + + (for-each + (lambda (table) + (exec-query + conn + (simple-format + #f + " +DELETE FROM ~A +WHERE branch_name = $1 AND git_repository_id = $2" + table) + (list branch-name + (number->string git-repository-id)))) + '("package_versions_by_guix_revision_range" + "package_derivations_by_guix_revision_range")) + + (for-each + (lambda (table) + (exec-query + conn + (string-append + " +DELETE FROM " table " +WHERE job_id IN ( + SELECT id + FROM load_new_guix_revision_jobs + WHERE git_repository_id = " (number->string git-repository-id) " AND + commit IN (" + (string-join + (map (lambda (commit) + (string-append "'" commit "'")) + commits) + ", ") + ") +)"))) + '("load_new_guix_revision_job_events" + "load_new_guix_revision_job_logs")) + + (exec-query + conn + (string-append + " +DELETE FROM load_new_guix_revision_jobs +WHERE git_repository_id = " (number->string git-repository-id) " AND + commit IN (" + (string-join + (map (lambda (commit) + (string-append "'" commit "'")) + commits) + ", ") + ")")) + + (let ((guix-revision-ids + (map + car + (exec-query + conn + (string-append + " +SELECT guix_revisions.id +FROM (VALUES " + (string-join + (map (lambda (commit) + (string-append "('" commit "')")) + commits) + ", ") + ") AS commits +INNER JOIN guix_revisions + ON guix_revisions.commit = commits.column1 +WHERE guix_revisions.git_repository_id = " + (number->string git-repository-id) " AND + commits.column1 NOT IN ( + SELECT commit + FROM git_branches +)"))))) + + (unless (null? guix-revision-ids) + (for-each + (lambda (table) + (exec-query + conn + (simple-format + #f + " +DELETE FROM ~A WHERE ~A IN (VALUES ~A)" + table + (if (string=? table + "guix_revision_package_derivations") + "revision_id" + "guix_revision_id") + (string-join + (map (lambda (guix-revision-id) + (string-append "(" guix-revision-id ")")) + guix-revision-ids) + ", ")))) + '("channel_instances" + "guix_revision_channel_news_entries" + "guix_revision_lint_checkers" + "guix_revision_lint_warnings" + "guix_revision_package_derivations" + "guix_revision_system_test_derivations")) + + (exec-query + conn + (string-append + " +DELETE FROM guix_revisions +WHERE id IN (" + (string-join guix-revision-ids ", ") + ")"))))))) + +(with-postgresql-connection "foo" (lambda (conn) (for-each (lambda (branch-name) (delete-data-for-branch conn 1 branch-name)) (map car (exec-query conn "SELECT DISTINCT name FROM git_branches WHERE git_repository_id = 1 AND name != 'master'"))))) -- cgit v1.2.3