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

[Ian Jackson <iwj@xenproject.org>]

From: Ian Jackson <ian.jackson@eu.citrix.com>

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@eu.citrix.com>
---
 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