04/10: Regenerate the latest_build_status table

[Christopher Baines]

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

commit 6a04d474c06b9bcfb91ee07d3639755024d1ff1c
Author: Christopher Baines <mail@cbaines.net>
AuthorDate: Wed Oct 21 19:46:27 2020 +0100

    Regenerate the latest_build_status table
    
    As it doesn't handle wierdness for statuses, like builds being canceled 
with a
    timestamp of 0.
---
 sqitch/deploy/regenerate_latest_build_status.sql | 23 +++++++++++++++++++++++
 sqitch/revert/regenerate_latest_build_status.sql |  7 +++++++
 sqitch/sqitch.plan                               |  1 +
 sqitch/verify/regenerate_latest_build_status.sql |  7 +++++++
 4 files changed, 38 insertions(+)

diff --git a/sqitch/deploy/regenerate_latest_build_status.sql 
b/sqitch/deploy/regenerate_latest_build_status.sql
new file mode 100644
index 0000000..a686deb
--- /dev/null
+++ b/sqitch/deploy/regenerate_latest_build_status.sql
@@ -0,0 +1,23 @@
+-- Deploy guix-data-service:regenerate_latest_build_status to pg
+
+BEGIN;
+
+DELETE FROM latest_build_status;
+
+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
+    CASE WHEN status = 'scheduled' THEN -2
+         WHEN status = 'started' THEN -1
+         ELSE 0
+    END DESC,
+    timestamp DESC
+    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
+);
+
+COMMIT;
diff --git a/sqitch/revert/regenerate_latest_build_status.sql 
b/sqitch/revert/regenerate_latest_build_status.sql
new file mode 100644
index 0000000..6ad0bff
--- /dev/null
+++ b/sqitch/revert/regenerate_latest_build_status.sql
@@ -0,0 +1,7 @@
+-- Revert guix-data-service:regenerate_latest_build_status from pg
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan
index f5b2c33..5df6d90 100644
--- a/sqitch/sqitch.plan
+++ b/sqitch/sqitch.plan
@@ -74,3 +74,4 @@ change_derivation_source_file_nars_constraint 
2020-10-02T17:12:58Z Christopher B
 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
+regenerate_latest_build_status 2020-10-21T18:39:03Z Christopher Baines 
<mail@cbaines.net> # Regenerate the latest_build_status table
diff --git a/sqitch/verify/regenerate_latest_build_status.sql 
b/sqitch/verify/regenerate_latest_build_status.sql
new file mode 100644
index 0000000..6026906
--- /dev/null
+++ b/sqitch/verify/regenerate_latest_build_status.sql
@@ -0,0 +1,7 @@
+-- Verify guix-data-service:regenerate_latest_build_status on pg
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;