Add support for checking the referencial integrity of a table when defined on one or more columns

[Seddryck]

It should be possible to define a query and check that a set of the returned columns contain values provided by another query.

<system-under-test>
  <result-set>
    <query>select field1, field2, field3 from myTable</select>
  </result-set>
</system-under-test>
<assert>
  <reference-exists>
    <column-mapping child="#0" parent="#0"/>
    <column-mapping child="#2" parent="#1" />
    <result-set>
       <query> select foo, bar from myOtherTable </query>
    </result-set>
  </reference-exists>
</assert>

[FuegoArtificial]

Hi @Seddryck,

the solution which I'm using for it is the following template, provided you have the test case data saved somewhere else.
Perhaps it helps.

<test name="All values of field '$SourceIdField$' of table '$SourceIdFieldTable$' are referentially integer with field '$ReferenceIdField$' of table '$ReferenceIdFieldTable$'.">   		
	$if(IgnoreReason)$<ignore>$IgnoreReason$</ignore>$endif$  
	<category>Referential Integrity</category>
	<system-under-test>
		<execution>
			<query connectionString="@myDB">
				<![CDATA[SELECT DISTINCT $SourceIdField$ FROM $SourceIdFieldTable$
				EXCEPT
				SELECT $ReferenceIdField$ FROM $ReferenceIdFieldTable$]]>
			</query>
		</execution>
	</system-under-test>
	<assert>
		<equalTo>
			<query connectionString="@myDB">
				<![CDATA[SELECT DISTINCT $SourceIdField$ FROM $SourceIdFieldTable$ WHERE 0 = 1]]>
			</query>
		</equalTo>
	</assert>
</test>

About the feature request #349: I have to refine the template first to provide a reasonable template to the public.

Cheers

[Seddryck]

Beta available at https://ci.appveyor.com/project/Seddryck/nbi/build/1.18.0-beta.109.build.346

[Seddryck]

Not really happy with the current naming.

It should be lookup-exist, parent should be renamed reference and child should candidate. column-mapping should also be embedded in a join after being renamed mapping. A shortcut should be added to mapping letting the user expressed that the two columns used during the equijoin have the same position or name (using).

And if several rows in the reference table validate the lookup … it should not be a problem.