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.

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;