Regenerate the latest_build_status table

As it doesn't handle wierdness for statuses, like builds being canceled with a
timestamp of 0.

1 files changed, 23 insertions, 0 deletions

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;