[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
02/03: Create a table for systems
|
From: |
Christopher Baines |
|
Subject: |
02/03: Create a table for systems |
|
Date: |
Fri, 23 Apr 2021 06:21:37 -0400 (EDT) |
cbaines pushed a commit to branch master
in repository data-service.
commit b430c632b75e5e90e36a855599e3f91302720d54
Author: Christopher Baines <mail@cbaines.net>
AuthorDate: Fri Apr 23 11:14:51 2021 +0100
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 +-
sqitch/deploy/systems_table.sql | 38 ++++++++++++++++++
sqitch/revert/systems_table.sql | 7 ++++
sqitch/sqitch.plan | 1 +
sqitch/verify/systems_table.sql | 7 ++++
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;