04/06: Switch queries across to use latest_build

guix-commits

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

04/06: Switch queries across to use latest_build_status

From:	Christopher Baines
Subject:	04/06: Switch queries across to use latest_build_status
Date:	Tue, 13 Oct 2020 15:18:03 -0400 (EDT)

cbaines pushed a commit to branch master
in repository data-service.

commit e273e220420f7f0351824fefb32d0c6c423e5dc3
Author: Christopher Baines <mail@cbaines.net>
AuthorDate: Tue Oct 13 19:32:42 2020 +0100

    Switch queries across to use latest_build_status
---
 guix-data-service/builds.scm                 |  8 ++-----
 guix-data-service/model/build.scm            | 31 ++++++----------------------
 guix-data-service/model/channel-instance.scm |  6 +-----
 guix-data-service/model/derivation.scm       | 24 ++++-----------------
 guix-data-service/model/package.scm          | 12 ++---------
 guix-data-service/model/system-test.scm      |  6 +-----
 6 files changed, 16 insertions(+), 71 deletions(-)

diff --git a/guix-data-service/builds.scm b/guix-data-service/builds.scm
index 20cf094..b54d7b8 100644
--- a/guix-data-service/builds.scm
+++ b/guix-data-service/builds.scm
@@ -462,12 +462,8 @@ SELECT builds.id, derivations.file_name
 FROM derivations
 INNER JOIN builds
   ON derivations.file_name = builds.derivation_file_name
-LEFT JOIN (
-  SELECT DISTINCT ON (build_id) *
-  FROM build_status
-  ORDER BY build_id, id DESC
-) AS latest_build_status
-ON builds.id = latest_build_status.build_id
+LEFT JOIN latest_build_status
+  ON latest_build_status.build_id = builds.id
 WHERE builds.build_server_id = $1 AND
       latest_build_status.status IN (
         'scheduled', 'started'
diff --git a/guix-data-service/model/build.scm 
b/guix-data-service/model/build.scm
index 3a2d630..cd2eb54 100644
--- a/guix-data-service/model/build.scm
+++ b/guix-data-service/model/build.scm
@@ -79,13 +79,8 @@ LEFT JOIN builds
    ON builds.derivation_output_details_set_id =
         derivation_output_details_sets.id AND
       builds.build_server_id = build_servers.id
-LEFT JOIN
-(
-  SELECT DISTINCT ON (build_id) *
-  FROM build_status
-  ORDER BY build_id, id DESC
-) AS latest_build_status
-ON builds.id = latest_build_status.build_id
+LEFT JOIN latest_build_status
+  ON builds.id = latest_build_status.build_id
 "
      (if (null? criteria)
          ""
@@ -168,13 +163,8 @@ INNER JOIN guix_revisions
   ON guix_revision_package_derivations.revision_id = guix_revisions.id"
          "")
      "
-INNER JOIN
-(
-  SELECT DISTINCT ON (build_id) *
-  FROM build_status
-  ORDER BY build_id, id DESC
-) AS latest_build_status
-ON latest_build_status.build_id = builds.id
+INNER JOIN latest_build_status
+  ON latest_build_status.build_id = builds.id
 "
                    (if (null? where-conditions)
                        ""
@@ -237,12 +227,7 @@ SELECT build_servers.id,
        latest_build_status.status
 FROM builds
 INNER JOIN build_servers ON build_servers.id = builds.build_server_id
-INNER JOIN
-(
-  SELECT DISTINCT ON (build_id) *
-  FROM build_status
-  ORDER BY build_id, id DESC
-) AS latest_build_status
+INNER JOIN latest_build_status
   ON latest_build_status.build_id = builds.id
 INNER JOIN derivation_output_details_sets
   ON builds.derivation_output_details_set_id =
@@ -362,11 +347,7 @@ LEFT OUTER JOIN builds
   ON derivations_by_output_details_set.derivation_output_details_set_id =
      builds.derivation_output_details_set_id
  AND builds.build_server_id = $2
-LEFT OUTER JOIN (
-  SELECT DISTINCT ON (build_id) *
-  FROM build_status
-  ORDER BY build_id, id DESC
-) AS latest_build_status
+LEFT OUTER JOIN latest_build_status
   ON builds.id = latest_build_status.build_id
 WHERE latest_build_status.status = 'failed'
   AND NOT EXISTS (
diff --git a/guix-data-service/model/channel-instance.scm 
b/guix-data-service/model/channel-instance.scm
index 1985cd3..4452ece 100644
--- a/guix-data-service/model/channel-instance.scm
+++ b/guix-data-service/model/channel-instance.scm
@@ -76,11 +76,7 @@ INNER JOIN derivations_by_output_details_set
 LEFT OUTER JOIN builds
   ON derivations_by_output_details_set.derivation_output_details_set_id =
      builds.derivation_output_details_set_id
-LEFT OUTER JOIN (
-  SELECT DISTINCT ON (build_id) *
-  FROM build_status
-  ORDER BY build_id, id DESC
-) AS latest_build_status
+LEFT OUTER JOIN latest_build_status
   ON builds.id = latest_build_status.build_id
 INNER JOIN guix_revisions
   ON guix_revisions.id = channel_instances.guix_revision_id
diff --git a/guix-data-service/model/derivation.scm 
b/guix-data-service/model/derivation.scm
index 6ec0266..05b96c9 100644
--- a/guix-data-service/model/derivation.scm
+++ b/guix-data-service/model/derivation.scm
@@ -157,11 +157,7 @@ LEFT OUTER JOIN builds
       builds.derivation_output_details_set_id
 LEFT OUTER JOIN build_servers
   ON builds.build_server_id = build_servers.id
-LEFT OUTER JOIN (
-  SELECT DISTINCT ON (build_id) *
-  FROM build_status
-  ORDER BY build_id, id DESC
-) AS latest_build_status
+LEFT OUTER JOIN latest_build_status
   ON builds.id = latest_build_status.build_id
 WHERE guix_revisions.commit = $1
   AND packages.name = $2
@@ -261,11 +257,7 @@ SELECT derivations.file_name,
                   ORDER BY latest_build_status.timestamp
                 )
          FROM builds
-         INNER JOIN (
-           SELECT DISTINCT ON (build_id) *
-           FROM build_status
-           ORDER BY build_id, id DESC
-         ) AS latest_build_status
+         INNER JOIN latest_build_status
            ON builds.id = latest_build_status.build_id
          WHERE builds.derivation_output_details_set_id =
                
derivations_by_output_details_set.derivation_output_details_set_id
@@ -390,11 +382,7 @@ SELECT derivations.file_name,
                   ORDER BY latest_build_status.timestamp
                 )
          FROM builds
-         INNER JOIN (
-           SELECT DISTINCT ON (build_id) *
-           FROM build_status
-           ORDER BY build_id, id DESC
-         ) AS latest_build_status
+         INNER JOIN latest_build_status
            ON builds.id = latest_build_status.build_id
          WHERE builds.derivation_output_details_set_id =
                
derivations_by_output_details_set.derivation_output_details_set_id
@@ -1445,11 +1433,7 @@ INNER JOIN derivations_by_output_details_set
 LEFT OUTER JOIN builds
   ON derivations.derivation_output_details_set_id =
      builds.derivation_output_details_set_id
-LEFT OUTER JOIN (
-  SELECT DISTINCT ON (build_id) *
-  FROM build_status
-  ORDER BY build_id, id DESC
-) AS latest_build_status
+LEFT OUTER JOIN latest_build_status
   ON builds.id = latest_build_status.build_id
 WHERE " criteria ";"))
 
diff --git a/guix-data-service/model/package.scm 
b/guix-data-service/model/package.scm
index ef20253..113bd72 100644
--- a/guix-data-service/model/package.scm
+++ b/guix-data-service/model/package.scm
@@ -361,11 +361,7 @@ INNER JOIN derivations_by_output_details_set
 LEFT OUTER JOIN builds
   ON derivations_by_output_details_set.derivation_output_details_set_id =
      builds.derivation_output_details_set_id
-LEFT OUTER JOIN (
-  SELECT DISTINCT ON (build_id) *
-  FROM build_status
-  ORDER BY build_id, id DESC
-) AS latest_build_status
+LEFT OUTER JOIN latest_build_status
   ON builds.id = latest_build_status.build_id
 INNER JOIN guix_revisions AS first_guix_revisions
   ON first_guix_revision_id = first_guix_revisions.id
@@ -493,11 +489,7 @@ FROM (
 LEFT OUTER JOIN builds
   ON data2.derivation_output_details_set_id =
      builds.derivation_output_details_set_id
-LEFT OUTER JOIN (
-  SELECT DISTINCT ON (build_id) *
-  FROM build_status
-  ORDER BY build_id, id DESC
-) AS latest_build_status
+LEFT OUTER JOIN latest_build_status
   ON builds.id = latest_build_status.build_id
 GROUP BY 1, 2, 3, 4, 5, 6
 ORDER BY first_datetime DESC, package_version DESC")
diff --git a/guix-data-service/model/system-test.scm 
b/guix-data-service/model/system-test.scm
index 7ac55e8..c6c9204 100644
--- a/guix-data-service/model/system-test.scm
+++ b/guix-data-service/model/system-test.scm
@@ -108,11 +108,7 @@ INNER JOIN derivations_by_output_details_set
 LEFT OUTER JOIN builds
   ON derivations_by_output_details_set.derivation_output_details_set_id =
      builds.derivation_output_details_set_id
-LEFT OUTER JOIN (
-  SELECT DISTINCT ON (build_id) *
-  FROM build_status
-  ORDER BY build_id, id DESC
-) AS latest_build_status
+LEFT OUTER JOIN latest_build_status
   ON builds.id = latest_build_status.build_id
 INNER JOIN guix_revisions
   ON guix_revisions.id = guix_revision_system_test_derivations.guix_revision_id

[Prev in Thread]

Current Thread

[Next in Thread]

branch master updated (4231f11 -> 7936ca2), Christopher Baines, 2020/10/13
- 04/06: Switch queries across to use latest_build_status, Christopher Baines <=
- 01/06: Include package details for the package derivation outputs JSON, Christopher Baines, 2020/10/13
- 06/06: Remove unnecessary join in select-outputs-without-known-nar-entries, Christopher Baines, 2020/10/13
- 05/06: Display backtraces when exceptions occur for fetching builds, Christopher Baines, 2020/10/13
- 02/06: Include the revision in the package derivation outputs JSON, Christopher Baines, 2020/10/13
- 03/06: Add a latest_build_status table, Christopher Baines, 2020/10/13

Prev by Date: 11/14: gnu: dos2unix: Update to 7.4.2.
Next by Date: 01/06: Include package details for the package derivation outputs JSON
Previous by thread: branch master updated (4231f11 -> 7936ca2)
Next by thread: 01/06: Include package details for the package derivation outputs JSON
Index(es):
- Date
- Thread