diff options
author | Christopher Baines <mail@cbaines.net> | 2020-10-13 19:31:43 +0100 |
---|---|---|
committer | Christopher Baines <mail@cbaines.net> | 2020-10-13 19:31:43 +0100 |
commit | 83884ed2ea4369d88b5a323333a6c78dbc471d24 (patch) | |
tree | 4a085a97b263cb3bb7bab44c31f3901a2bdbaecf /sqitch/deploy | |
parent | 941d1af55659eeadf4155b29176e510fd740bc4d (diff) | |
download | data-service-83884ed2ea4369d88b5a323333a6c78dbc471d24.tar data-service-83884ed2ea4369d88b5a323333a6c78dbc471d24.tar.gz |
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.
Diffstat (limited to 'sqitch/deploy')
-rw-r--r-- | sqitch/deploy/create_latest_build_status.sql | 27 |
1 files changed, 27 insertions, 0 deletions
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; |