03/06: Add a latest_build_status table

[Christopher Baines]

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

commit 83884ed2ea4369d88b5a323333a6c78dbc471d24
Author: Christopher Baines <mail@cbaines.net>
AuthorDate: Tue Oct 13 19:31:43 2020 +0100

    Add a latest_build_status table
    
    This will avoid many queries trying to figure out what the latest build 
status
    is, which will hopefuly simplify queries as well as improving performance.
---
 guix-data-service/model/build-status.scm     | 49 +++++++++++++++++++++++++++-
 sqitch/deploy/create_latest_build_status.sql | 27 +++++++++++++++
 sqitch/revert/create_latest_build_status.sql |  7 ++++
 sqitch/sqitch.plan                           |  1 +
 sqitch/verify/create_latest_build_status.sql |  7 ++++
 5 files changed, 90 insertions(+), 1 deletion(-)

diff --git a/guix-data-service/model/build-status.scm 
b/guix-data-service/model/build-status.scm
index 199e8a4..5a079a2 100644
--- a/guix-data-service/model/build-status.scm
+++ b/guix-data-service/model/build-status.scm
@@ -18,6 +18,7 @@
 (define-module (guix-data-service model build-status)
   #:use-module (ice-9 match)
   #:use-module (squee)
+  #:use-module (guix-data-service database)
   #:use-module (guix-data-service model utils)
   #:export (build-statuses
             build-status-strings
@@ -89,4 +90,50 @@ VALUES "
      "
 ON CONFLICT DO NOTHING"))
 
-  (exec-query conn query '()))
+  (define (delete-old-latest-status-entries conn)
+    (define query
+      (string-append
+       "
+DELETE FROM latest_build_status
+WHERE build_id IN ("
+       (string-join
+        (map number->string build-ids)
+        ",")
+       ")"))
+
+    (exec-query conn query))
+
+  (define (insert-new-latest-status-entries conn)
+    (define query
+      (string-append
+       "
+INSERT INTO latest_build_status
+SELECT DISTINCT build_id,
+                first_value(timestamp) OVER rows_for_build AS timestamp,
+                first_value(status) OVER rows_for_build AS status
+FROM build_status
+WHERE build_id IN ("
+       (string-join
+        (map number->string build-ids)
+        ",")
+       ")
+WINDOW rows_for_build AS (
+  PARTITION BY build_id
+  ORDER BY
+    timestamp DESC,
+    CASE WHEN status = 'scheduled' THEN -2
+         WHEN status = 'started' THEN -1
+         ELSE 0
+    END DESC
+    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
+)"))
+
+    (exec-query conn query))
+
+  (with-postgresql-transaction
+   conn
+   (lambda (conn)
+     (exec-query conn query '())
+
+     (delete-old-latest-status-entries conn)
+     (insert-new-latest-status-entries conn))))
diff --git a/sqitch/deploy/create_latest_build_status.sql 
b/sqitch/deploy/create_latest_build_status.sql
new file mode 100644
index 0000000..982d729
--- /dev/null
+++ b/sqitch/deploy/create_latest_build_status.sql
@@ -0,0 +1,27 @@
+-- Deploy guix-data-service:create_latest_build_status to pg
+
+BEGIN;
+
+CREATE TABLE latest_build_status (
+    build_id integer PRIMARY KEY NOT NULL REFERENCES builds(id),
+    "timestamp" timestamp without time zone DEFAULT clock_timestamp(),
+    status guix_data_service.buildstatus NOT NULL
+);
+
+INSERT INTO latest_build_status
+SELECT DISTINCT build_id,
+                first_value(timestamp) OVER rows_for_build AS timestamp,
+                first_value(status) OVER rows_for_build AS status
+FROM build_status
+WINDOW rows_for_build AS (
+  PARTITION BY build_id
+  ORDER BY
+    timestamp DESC,
+    CASE WHEN status = 'scheduled' THEN -2
+         WHEN status = 'started' THEN -1
+         ELSE 0
+    END DESC
+    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
+);
+
+COMMIT;
diff --git a/sqitch/revert/create_latest_build_status.sql 
b/sqitch/revert/create_latest_build_status.sql
new file mode 100644
index 0000000..1afce28
--- /dev/null
+++ b/sqitch/revert/create_latest_build_status.sql
@@ -0,0 +1,7 @@
+-- Revert guix-data-service:create_latest_build_status from pg
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan
index 6055c5a..f5b2c33 100644
--- a/sqitch/sqitch.plan
+++ b/sqitch/sqitch.plan
@@ -73,3 +73,4 @@ change_autovacuum_config 2020-10-01T21:24:46Z Christopher 
Baines <mail@cbaines.n
 change_derivation_source_file_nars_constraint 2020-10-02T17:12:58Z Christopher 
Baines <mail@cbaines.net> # Change derivation source file nars constraint
 add_derivation_sources_derivation_source_file_id_index 2020-10-02T19:11:59Z 
Christopher Baines <mail@cbaines.net> # Add 
derivation_sources.derivation_source_file_id index
 git_repositories_add_fetch_with_authentication_field 2020-10-07T17:31:20Z 
Christopher Baines <mail@cbaines.net> # Add 
git_repositories.fetch_with_authentication
+create_latest_build_status 2020-10-13T17:22:39Z Christopher Baines 
<mail@cbaines.net> # Create the latest_build_status table
diff --git a/sqitch/verify/create_latest_build_status.sql 
b/sqitch/verify/create_latest_build_status.sql
new file mode 100644
index 0000000..a272972
--- /dev/null
+++ b/sqitch/verify/create_latest_build_status.sql
@@ -0,0 +1,7 @@
+-- Verify guix-data-service:create_latest_build_status on pg
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;