[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[no subject]
From: |
Mathieu Othacehe |
Date: |
Wed, 28 Apr 2021 15:19:44 -0400 (EDT) |
branch: master
commit 97cf5b56d57fb3fbf33068a5e39022133f797bf2
Author: Mathieu Othacehe <othacehe@gnu.org>
AuthorDate: Wed Apr 28 21:18:18 2021 +0200
Make sure that the cached Job build status is always synchronized.
* src/sql/upgrade-10.sql: New file.
* Makefile.am (dist_sql_DATA): Update it.
* src/schema.sql (update_job_status): New function,
(build_status): new trigger.
---
Makefile.am | 3 ++-
src/schema.sql | 14 ++++++++++++++
src/sql/upgrade-10.sql | 17 +++++++++++++++++
3 files changed, 33 insertions(+), 1 deletion(-)
diff --git a/Makefile.am b/Makefile.am
index 052fe7e..22a5051 100644
--- a/Makefile.am
+++ b/Makefile.am
@@ -96,7 +96,8 @@ dist_sql_DATA = \
src/sql/upgrade-6.sql \
src/sql/upgrade-7.sql \
src/sql/upgrade-8.sql \
- src/sql/upgrade-9.sql
+ src/sql/upgrade-9.sql \
+ src/sql/upgrade-10.sql
dist_css_DATA = \
src/static/css/choices.min.css \
diff --git a/src/schema.sql b/src/schema.sql
index 181d8dd..d1479a0 100644
--- a/src/schema.sql
+++ b/src/schema.sql
@@ -131,6 +131,20 @@ CREATE INDEX Builds_status_ts_id on Builds(status DESC,
timestamp DESC, id ASC);
CREATE INDEX Builds_priority_timestamp on Builds(priority ASC, timestamp DESC);
CREATE INDEX Builds_weather_evaluation ON Builds (weather, evaluation);
+-- Make sure that the cached Job build status is always synchronized with the
+-- matching build status.
+CREATE FUNCTION update_job_status()
+RETURNS TRIGGER AS $$
+BEGIN
+UPDATE Jobs SET status = NEW.status WHERE Jobs.build = NEW.id;
+RETURN null;
+END
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER build_status AFTER UPDATE ON Builds
+FOR EACH ROW
+EXECUTE PROCEDURE update_job_status();
+
CREATE INDEX Jobs_name ON Jobs (name);
CREATE INDEX Jobs_system_status ON Jobs (system, status);
CREATE INDEX Jobs_build ON Jobs (build); --speeds up delete cascade.
diff --git a/src/sql/upgrade-10.sql b/src/sql/upgrade-10.sql
new file mode 100644
index 0000000..5a68044
--- /dev/null
+++ b/src/sql/upgrade-10.sql
@@ -0,0 +1,17 @@
+BEGIN TRANSACTION;
+
+-- Make sure that the cached Job build status is always synchronized with the
+-- matching build status.
+CREATE FUNCTION update_job_status()
+RETURNS TRIGGER AS $$
+BEGIN
+UPDATE Jobs SET status = NEW.status WHERE Jobs.build = NEW.id;
+RETURN null;
+END
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER build_status AFTER UPDATE ON Builds
+FOR EACH ROW
+EXECUTE PROCEDURE update_job_status();
+
+COMMIT;