aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--guix-data-service/model/build.scm7
-rw-r--r--guix-data-service/model/derivation.scm45
-rw-r--r--guix-data-service/model/nar.scm16
-rw-r--r--guix-data-service/model/package-derivation-by-guix-revision-range.scm4
-rw-r--r--guix-data-service/model/package-derivation.scm12
-rw-r--r--guix-data-service/model/package.scm4
-rw-r--r--sqitch/deploy/systems_table.sql38
-rw-r--r--sqitch/revert/systems_table.sql7
-rw-r--r--sqitch/sqitch.plan1
-rw-r--r--sqitch/verify/systems_table.sql7
10 files changed, 112 insertions, 29 deletions
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
(($ <derivation> 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
diff --git a/sqitch/deploy/systems_table.sql b/sqitch/deploy/systems_table.sql
new file mode 100644
index 0000000..f7259f0
--- /dev/null
+++ b/sqitch/deploy/systems_table.sql
@@ -0,0 +1,38 @@
+-- Deploy guix-data-service:systems_table to pg
+
+BEGIN;
+
+CREATE TABLE systems (
+ id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
+ system character varying UNIQUE NOT NULL
+);
+
+INSERT INTO systems (system) SELECT DISTINCT system FROM derivations;
+
+ALTER TABLE derivations
+ ADD COLUMN system_id integer REFERENCES systems (id);
+
+UPDATE derivations
+ SET system_id = (
+ SELECT id FROM systems WHERE systems.system = derivations.system
+ );
+
+ALTER TABLE derivations
+ ALTER COLUMN system_id SET NOT NULL;
+
+ALTER TABLE derivations DROP COLUMN system;
+
+ALTER TABLE package_derivations
+ ADD COLUMN system_id integer REFERENCES systems (id);
+
+UPDATE package_derivations
+ SET system_id = (
+ SELECT id FROM systems WHERE systems.system = package_derivations.system
+ );
+
+ALTER TABLE package_derivations
+ ALTER COLUMN system_id SET NOT NULL;
+
+ALTER TABLE package_derivations DROP COLUMN system;
+
+COMMIT;
diff --git a/sqitch/revert/systems_table.sql b/sqitch/revert/systems_table.sql
new file mode 100644
index 0000000..63fb42a
--- /dev/null
+++ b/sqitch/revert/systems_table.sql
@@ -0,0 +1,7 @@
+-- Revert guix-data-service:systems_table from pg
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan
index b5af91d..76d574b 100644
--- a/sqitch/sqitch.plan
+++ b/sqitch/sqitch.plan
@@ -78,3 +78,4 @@ regenerate_latest_build_status 2020-10-21T18:39:03Z Christopher Baines <mail@cba
guix_revision_package_derivations_add_package_derivation_index 2020-10-27T16:58:08Z Christopher Baines <mail@cbaines.net> # Add index for guix_revision_package_derivations.package_derivation_id
increase_derivation_inputs_statistics_targets 2020-12-27T10:34:58Z Christopher Baines <mail@cbaines.net> # Increase stats targets on derivation_inputs fields
remove_guix_revisions_store_path 2021-02-02T20:06:18Z Christopher Baines <mail@cbaines.net> # Drop guix_revisions.store_path
+systems_table 2021-04-22T08:12:10Z Christopher Baines <mail@cbaines.net> # Add a systems table
diff --git a/sqitch/verify/systems_table.sql b/sqitch/verify/systems_table.sql
new file mode 100644
index 0000000..c734717
--- /dev/null
+++ b/sqitch/verify/systems_table.sql
@@ -0,0 +1,7 @@
+-- Verify guix-data-service:systems_table on pg
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;
generated by cgit v1.2.3 (git 2.45.0) at 2024-11-21 14:25:23 +0000