From b430c632b75e5e90e36a855599e3f91302720d54 Mon Sep 17 00:00:00 2001 From: Christopher Baines Date: Fri, 23 Apr 2021 11:14:51 +0100 Subject: Create a table for systems And use it for the systems in the derivations and package derivations tables. The primary motivation here is to allow quickly working out what systems the database contains, and having a small table with just the right data seems a good way to do that. --- guix-data-service/model/build.scm | 7 +++- guix-data-service/model/derivation.scm | 45 ++++++++++++++-------- guix-data-service/model/nar.scm | 16 +++++--- .../package-derivation-by-guix-revision-range.scm | 4 +- guix-data-service/model/package-derivation.scm | 12 +++--- guix-data-service/model/package.scm | 4 +- 6 files changed, 59 insertions(+), 29 deletions(-) (limited to 'guix-data-service') diff --git a/guix-data-service/model/build.scm b/guix-data-service/model/build.scm index d0a75b1..3d38e67 100644 --- a/guix-data-service/model/build.scm +++ b/guix-data-service/model/build.scm @@ -23,6 +23,7 @@ #:use-module (json) #:use-module (guix-data-service database) #:use-module (guix-data-service model utils) + #:use-module (guix-data-service model system) #:export (select-build-stats select-builds-with-context select-builds-with-context-by-derivation-file-name @@ -51,7 +52,8 @@ `(("guix_revisions.commit = $" . ,revision-commit)) '()) ,@(if system - `(("package_derivations.system = $" . ,system)) + `(("package_derivations.system_id = $" . + ,(system->system-id conn system))) '()) ,@(if target `(("package_derivations.target = $" . ,target)) @@ -143,7 +145,8 @@ ORDER BY status")) `(("guix_revisions.commit = $" . ,revision-commit)) '()) ,@(if system - `(("package_derivations.system = $" . ,system)) + `(("package_derivations.system_id = $" . + ,(system->system-id conn system))) '()) ,@(if target `(("package_derivations.target = $" . ,target)) diff --git a/guix-data-service/model/derivation.scm b/guix-data-service/model/derivation.scm index 68e8923..099d4cb 100644 --- a/guix-data-service/model/derivation.scm +++ b/guix-data-service/model/derivation.scm @@ -33,6 +33,7 @@ #:use-module (guix derivations) #:use-module (guix-data-service database) #:use-module (guix-data-service model utils) + #:use-module (guix-data-service model system) #:export (valid-systems valid-targets count-derivations @@ -130,7 +131,7 @@ (define (select-derivations-by-revision-name-and-version conn revision-commit-hash name version) (define query " -SELECT derivations.system, +SELECT systems.system, package_derivations.target, derivations.file_name, JSON_AGG( @@ -145,6 +146,8 @@ SELECT derivations.system, ORDER BY latest_build_status.timestamp ) FROM derivations +INNER JOIN systems + ON derivations.system_id = systems.id INNER JOIN package_derivations ON derivations.id = package_derivations.derivation_id INNER JOIN packages @@ -165,10 +168,10 @@ LEFT OUTER JOIN latest_build_status WHERE guix_revisions.commit = $1 AND packages.name = $2 AND packages.version = $3 -GROUP BY derivations.system, +GROUP BY systems.system, package_derivations.target, derivations.file_name -ORDER BY derivations.system DESC, +ORDER BY systems.system DESC, NULLIF(package_derivations.target, '') DESC NULLS FIRST, derivations.file_name") @@ -213,7 +216,7 @@ ORDER BY derivations.system DESC, ",") ")") #f)) - '("derivations.system" + '("systems.system" "target") (list systems targets)) @@ -306,7 +309,7 @@ EXISTS ( (string-append " SELECT derivations.file_name, - derivations.system, + systems.system, package_derivations.target" (if include-builds? ", @@ -331,6 +334,8 @@ SELECT derivations.file_name, "") " FROM derivations +INNER JOIN systems + ON derivations.system_id = systems.id INNER JOIN derivations_by_output_details_set ON derivations.id = derivations_by_output_details_set.derivation_id INNER JOIN package_derivations @@ -402,7 +407,7 @@ ORDER BY derivations.file_name ",") ")") #f)) - '("derivations.system" + '("systems.system" "target") (list systems targets)) @@ -495,7 +500,7 @@ EXISTS ( (string-append " SELECT derivations.file_name, - derivations.system, + systems.system, package_derivations.target" (if include-builds? ", @@ -520,6 +525,8 @@ SELECT derivations.file_name, "") " FROM derivations +INNER JOIN systems + ON derivations.system_id = systems.id INNER JOIN derivations_by_output_details_set ON derivations.id = derivations_by_output_details_set.derivation_id INNER JOIN package_derivations @@ -585,13 +592,15 @@ ORDER BY derivations.file_name WITH RECURSIVE all_derivations(id) AS ( SELECT package_derivations.derivation_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 INNER JOIN guix_revisions ON guix_revision_package_derivations.revision_id = guix_revisions.id WHERE guix_revisions.commit = $1 - AND package_derivations.system = $2 + AND systems.system = $2 AND package_derivations.target = $3 UNION SELECT derivation_outputs.derivation_id @@ -715,6 +724,8 @@ INNER JOIN derivation_output_details ON derivation_outputs.derivation_output_details_id = derivation_output_details.id INNER JOIN package_derivations ON derivations.id = package_derivations.derivation_id +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 INNER JOIN guix_revisions @@ -728,7 +739,7 @@ WHERE guix_revisions.commit = $1 '(" AND derivation_output_details.path > ") '()) ,@(if system - '(" AND package_derivations.system = ") + '(" AND systems.system = ") '()) ,@(if target '(" AND package_derivations.target = ") @@ -1038,8 +1049,9 @@ VALUES ($1, $2)" (define (select-derivation-by-file-name-hash conn file-name-hash) (define query (string-append - "SELECT id, file_name, builder, args, to_json(env_vars), system " + "SELECT derivations.id, file_name, builder, args, to_json(env_vars), system " "FROM derivations " + "INNER JOIN systems ON derivations.system_id = systems.id " "WHERE substring(file_name from 12 for 32) = $1")) (match (exec-query conn query (list file-name-hash)) @@ -1060,8 +1072,9 @@ VALUES ($1, $2)" (define (select-derivation-by-file-name conn file-name) (define query (string-append - "SELECT id, file_name, builder, args, to_json(env_vars), system " + "SELECT derivations.id, file_name, builder, args, to_json(env_vars), system " "FROM derivations " + "INNER JOIN systems ON derivations.system_id = systems.id " "WHERE file_name = $1")) (match (exec-query conn query (list file-name)) @@ -1522,7 +1535,7 @@ LIMIT $1" (define (insert-into-derivations) (string-append "INSERT INTO derivations " - "(file_name, builder, args, env_vars, system) VALUES " + "(file_name, builder, args, env_vars, system_id) VALUES " (string-join (map (match-lambda (($ outputs inputs sources @@ -1539,7 +1552,7 @@ LIMIT $1" value "$$ ]"))) env-vars) ",") - system))) + (system->system-id conn system)))) derivations) ",") " RETURNING id" @@ -1641,7 +1654,7 @@ LIMIT $1" ")") #f)) '("derivations.file_name" - "derivations.system" + "systems.system" "target" "latest_build_status.status") (list (deduplicate-strings file-names) @@ -1655,10 +1668,12 @@ LIMIT $1" " SELECT derivations.file_name, - derivations.system, + systems.system, package_derivations.target, latest_build_status.status FROM derivations +INNER JOIN systems + ON derivations.system_id = systems.id INNER JOIN package_derivations ON derivations.id = package_derivations.derivation_id INNER JOIN derivations_by_output_details_set diff --git a/guix-data-service/model/nar.scm b/guix-data-service/model/nar.scm index e493abb..7cf1f31 100644 --- a/guix-data-service/model/nar.scm +++ b/guix-data-service/model/nar.scm @@ -247,7 +247,7 @@ SELECT build_server_id, system, target, substitute_known, COUNT(*) FROM ( SELECT build_servers.id AS build_server_id, derivation_output_details.path, - package_derivations.system, + systems.system, package_derivations.target, nar_data.build_server_id IS NOT NULL AS substitute_known FROM derivation_output_details @@ -256,6 +256,8 @@ FROM ( derivation_output_details.id INNER JOIN package_derivations ON derivation_outputs.derivation_id = package_derivations.derivation_id + 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 @@ -264,7 +266,7 @@ FROM ( CROSS JOIN build_servers INNER JOIN build_servers_build_config ON build_servers.id = build_servers_build_config.build_server_id - AND package_derivations.system = build_servers_build_config.system + AND systems.system = build_servers_build_config.system AND package_derivations.target = build_servers_build_config.target LEFT JOIN ( SELECT nars.store_path, narinfo_fetch_records.build_server_id @@ -318,7 +320,7 @@ ORDER BY build_server_id DESC, system, target, build_server_id, substitute_known SELECT system, target, reproducible, COUNT(*) FROM ( SELECT derivation_output_details.path, - package_derivations.system, + systems.system, package_derivations.target, CASE WHEN (COUNT(DISTINCT nar_data.build_server_id) <= 1) THEN NULL @@ -330,6 +332,8 @@ FROM ( derivation_output_details.id INNER JOIN package_derivations ON derivation_outputs.derivation_id = package_derivations.derivation_id + 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 @@ -351,7 +355,7 @@ FROM ( guix_revisions.commit = $1 AND package_derivations.target = '' -- Exclude cross builds GROUP BY derivation_output_details.path, - package_derivations.system, + systems.system, package_derivations.target ) data GROUP BY system, target, reproducible @@ -421,9 +425,11 @@ WHERE derivation_output_details.path NOT IN ( -- Select outputs that are in the relevant revisions SELECT derivation_id FROM package_derivations + INNER JOIN systems + ON package_derivations.system_id = systems.id INNER JOIN build_servers_build_config ON build_servers_build_config.build_server_id = $1 - AND build_servers_build_config.system = package_derivations.system + AND build_servers_build_config.system = systems.system AND build_servers_build_config.target = package_derivations.target INNER JOIN guix_revision_package_derivations ON guix_revision_package_derivations.package_derivation_id = package_derivations.id 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 c6832ac..4bcba1b 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 @@ -71,10 +71,12 @@ FROM packages INNER JOIN ( SELECT package_derivations.package_id, package_derivations.derivation_id, - package_derivations.system, + 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 diff --git a/guix-data-service/model/package-derivation.scm b/guix-data-service/model/package-derivation.scm index 31dab32..cd63daf 100644 --- a/guix-data-service/model/package-derivation.scm +++ b/guix-data-service/model/package-derivation.scm @@ -21,6 +21,7 @@ #:use-module (ice-9 match) #:use-module (squee) #:use-module (guix-data-service model utils) + #:use-module (guix-data-service model system) #:export (insert-package-derivations count-packages-derivations-in-revision)) @@ -32,7 +33,7 @@ (((package-id system target) derivation-id) (list package-id derivation-id - system + (system->system-id conn system) target))) package-ids-systems-and-targets derivation-ids)) @@ -42,15 +43,16 @@ (insert-missing-data-and-return-all-ids conn "package_derivations" - '(package_id derivation_id system target) + '(package_id derivation_id system_id target) data-4-tuples))) (define (count-packages-derivations-in-revision conn commit-hash) (define query " -SELECT package_derivations.system, package_derivations.target, +SELECT systems.system, package_derivations.target, COUNT(DISTINCT package_derivations.derivation_id) FROM package_derivations +INNER JOIN systems ON package_derivations.system_id = systems.id WHERE package_derivations.id IN ( SELECT guix_revision_package_derivations.package_derivation_id FROM guix_revision_package_derivations @@ -58,7 +60,7 @@ WHERE package_derivations.id IN ( ON guix_revision_package_derivations.revision_id = guix_revisions.id WHERE guix_revisions.commit = $1 ) -GROUP BY package_derivations.system, package_derivations.target -ORDER BY package_derivations.system DESC, package_derivations.target ASC") +GROUP BY systems.system, package_derivations.target +ORDER BY systems.system DESC, package_derivations.target ASC") (exec-query conn query (list commit-hash))) diff --git a/guix-data-service/model/package.scm b/guix-data-service/model/package.scm index 9583d43..2d8d21b 100644 --- a/guix-data-service/model/package.scm +++ b/guix-data-service/model/package.scm @@ -573,8 +573,10 @@ INNER JOIN guix_revision_package_derivations latest_processed_guix_revision.id INNER JOIN package_derivations ON package_derivations.id = guix_revision_package_derivations.package_derivation_id - AND package_derivations.system = $2 AND package_derivations.target = $3 +INNER JOIN systems + ON package_derivations.system_id = systems_id + AND systems.system = $2 INNER JOIN packages ON package_derivations.package_id = packages.id WHERE packages.name = $1 -- cgit v1.2.3