Fix create small backup issue with latest_build_status

1 files changed, 19 insertions, 1 deletions

diff --git a/scripts/guix-data-service-create-small-backup b/scripts/guix-data-service-create-small-backup
index 2a84f42..40a4509 100755
--- a/scripts/guix-data-service-create-small-backup
+++ b/scripts/guix-data-service-create-small-backup
@@ -165,7 +165,8 @@ TRUNCATE derivations,
          guix_revision_package_derivations,
          package_derivations,
          builds,
-         build_status;
+         build_status,
+         latest_build_status;
 
 EOF
 
@@ -312,6 +313,23 @@ INSERT INTO derivation_sources
 
 INSERT INTO derivation_source_file_nars
   SELECT * FROM tmp_derivation_source_file_nars;
+
+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
+);
+
 EOF
 
 psql -v ON_ERROR_STOP=1 --echo-queries --no-psqlrc "$URI_FOR_DATABASE" -U guix_data_service <<EOF