[OSSTEST PATCH 68/82] host lifecycle: Prevent referential integrity violation

[Ian Jackson <iwj@xxxxxxxxxxxxxx>]

From: Ian Jackson <ian.jackson@xxxxxxxxxxxxx>

We can't use normal constraints for either of these, sadly.

We can make the constraints into a single query which says "OK".

Signed-off-by: Ian Jackson <ian.jackson@xxxxxxxxxxxxx>
---
 Osstest/JobDB/Executive.pm | 25 +++++++++++++++++++++++++
 1 file changed, 25 insertions(+)

diff --git a/Osstest/JobDB/Executive.pm b/Osstest/JobDB/Executive.pm
index 3a8308e9..f69ce277 100644
--- a/Osstest/JobDB/Executive.pm
+++ b/Osstest/JobDB/Executive.pm
@@ -553,6 +553,28 @@ END
                ON h.taskid = t.taskid
             WHERE h.hostname = ?
          ORDER BY h.lcseq;
+END
+    # We simulate two foreign key constraints which can't be in the
+    # db schema, by checking the values we are going to insert.
+    #
+    # For "resources" we would need a foreign key constraint
+    # with a literal value as part of the foreign key, which is
+    # not supported until PostgreSQL 13.
+    #
+    # For "tasks" we only want to apply the constraint on inserts into
+    # "host_lifecycle" - in particular, we want to allow delet6ions
+    # from "tasks" to render the taskid foreign key unresolvable.
+    # This could be done with a trigger, but since here is the only
+    # place we do insertions into host_lifecycle, this seems easier.
+    my $constraintsq = $dbh_tests->prepare(<<END);
+           SELECT * FROM
+            (SELECT 1 AS ok
+               FROM resources where restype='host' and resname=?) 
+              hostname_ok
+             NATURAL JOIN
+             (SELECT 1 AS ok
+                FROM tasks where taskid=? AND live)
+              taskid_ok;
 END
     my $insertq = $dbh_tests->prepare(<<END);
         INSERT INTO host_lifecycle
@@ -632,6 +654,9 @@ END
                push @lifecycle, "$omarks$otj:$o->{stepno}$osuffix";
            }
        }
+       $constraintsq->execute($hostname, $ttaskid);
+       $constraintsq->fetchrow_array() or confess "$hostname ?";
+
        if (defined $flight) {
            $insertq->execute($hostname, $ttaskid,
                              $flight, $job,
-- 
2.20.1