From 0ec9f9203e09ccf35a4e38b0db1fed56234bc20a Mon Sep 17 00:00:00 2001 From: Ian Jackson Date: Mon, 3 Jul 2017 17:54:19 +0100 Subject: [PATCH] db schema: Make the testid column NOT NLLL We have to fix some old data. We insist that the old data is indeed old (more than 5 years old) and not part of proper flights (ie, blessed "play" or "crashed" or "unknown"). Signed-off-by: Ian Jackson --- schema/testid-constraint.sql | 28 ++++++++++++++++++++++++++++ 1 file changed, 28 insertions(+) create mode 100644 schema/testid-constraint.sql diff --git a/schema/testid-constraint.sql b/schema/testid-constraint.sql new file mode 100644 index 00000000..541f26e6 --- /dev/null +++ b/schema/testid-constraint.sql @@ -0,0 +1,28 @@ +-- ##OSSTEST## 005 Harmless +-- +-- All steps should have a testid. +-- Some very old data in our existing instances lacks this. +-- +-- The time_t value is Tue Jul 3 17:09:12 BST 2012, 5 years ago. + +WITH cutoff AS + (SELECT 1341331754) +UPDATE steps + SET testid = 'xxx-dumy-testid.' || stepno + WHERE testid IS NULL + AND ( + WITH f AS + (SELECT * FROM flights WHERE flights.flight = steps.flight) + SELECT + ( ((SELECT blessing FROM f) = 'unknown' + OR (SELECT blessing FROM f) = 'play' + OR (SELECT blessing FROM f) = 'crashed') + ) + AND (SELECT started FROM f) < (SELECT * FROM cutoff) + AND COALESCE( steps.flight < + (SELECT min(flight) FROM flights + WHERE started >= (SELECT * FROM cutoff)) + , 1=1 ) + ); + +ALTER TABLE steps ALTER COLUMN testid SET NOT NULL; -- 2.39.5