Skip to content

KEY

Jump to bottom
Jakub Raczek edited this page Feb 9, 2021 · 22 revisions

We can compare query to expected data defined in csv file and generate Excel test raport

<cmpSqlResultsTest>
    <compare mode="KEY" diffTableSize="5" chunk="0" fileOutputOn="true" keyColumns="2">
        <sql datasourceName="SQL_SERVER" filename="ContactTypeActualTable.sql"
             keyTableName="AdventureWorks2008R2.dbo.TemplateKeyComparatorContactType"
             keyTableColumns="Name varchar(50) NOT NULL" />
        <!-- separator -> CSV file separtor. Default is "," -->
        <file filename="ContactType.csv" separator=";" startAtRow="2" ignoredColumns="1,5" testSetColumn="1">
		     <transformers>
                <transformer column="1">REMOVE_LEADING_ZEROS</transformer>
             </transformers>
		</file>
    </compare>
</cmpSqlResultsTest>
  • keyColumns: columns which will be treated as a key. In case of the CSV file, these are the remaining columns (without ignoredColumns). The column order must match (CSV file and SQL results).
  • testSetColumn: If a CSV file has a test set number you can define its column number. If defined, you will see additional column with TesSet Number in test result (console, differences and matches sheets). Default is empty (no additional column in the results)
  • keyTableName: name of the table which will store keys (based on [keyColumns] from the CSV file). Default is empty (table will not be created). You can use this table in SQL JOIN (to limit DB results). No default value -> if not defined, table is not created.
[INFO ] 2021-02-09 20:58:01,291 DBTestCompare:key_comparator.ContactType.KeyContactType - 
DROP TABLE AdventureWorks2008R2.dbo.TemplateKeyComparatorContactType
CREATE TABLE AdventureWorks2008R2.dbo.TemplateKeyComparatorContactType (Name varchar(50) NOT NULL);
INSERT INTO AdventureWorks2008R2.dbo.TemplateKeyComparatorContactType VALUES ('Accounting Manager')
INSERT INTO AdventureWorks2008R2.dbo.TemplateKeyComparatorContactType VALUES ('Assistant Sales Representative')
  • keyTableColumns: when [keyTableName] is defined you should also define table columns (names and types). DB engine specific. No default value.
  • beforeSqls: You can run multiple sql's before the test starts (for example prepare some of the DB tables before main SELECT execution). Those sql's can be stored as a separate files - on the same level as the main sql file.
<sql datasourceName="Terdata" filename="prepare.sql"/>
(...)
<beforeSqls>
<sql datasourceName="DB_oracle" filename="drop.sql"/>
<sql datasourceName="DB_oracle" filename="create.sql"/>
<sql datasourceName="DB_oracle" filename="insert.sql"/>
</beforeSqls>
(...)
<file filename="abc.csv" separator=";" startAtRow="2" ignoredColumns="1,2">
(...)
  • transformers: This option should not be over-use (as it modifies the test input). Each of the values ​​in the columns can be transformed before comparing with the results from the database. Transformation takes places always before aggregation. The key columns can be also transformed. You can use multiple transformers for the same column. They will be executed in the configured order. As for now there is only one transformer: REMOVE_LEADING_ZEROS (which removes all leading zeros from the string - leaving 0 if the String is only one character long) however it's very easy to extend DBTestCompare by adding new Transformers. The column property is the column number AFTER applying ignoredColumns (number of column which "stays").
<file filename="abc.csv" separator=";" startAtRow="2" ignoredColumns="1,2">
(...)
<transformers>
<transformer column="1">REMOVE_LEADING_ZEROS</transformer>
<transformer column="2">REMOVE_LEADING_ZEROS</transformer>
<transformer column="2">SOME_OTHER</transformer>
</transformers>
<aggregators>
(...)
</file>
  • aggregators: List of aggregators (defined for each column in the CSV file). The default is 'Override' (if there will be two rows with the same key - [keyColumns] - the newest one will be used in comparison). Available aggregators: SUM_INTEGERS, DATE (with pattern and lt/gt as aggregator a parameter). The result of aggregation is in the Excel '(3) FILE_AGGREGATED' sheet. Rows which were aggregated are green colored). Green row means that the Aggregator was used (because the key was duplicated in the source file - please double check this is what you expected). The column property is the column number AFTER applying ignoredColumns (number of column which "stays").
<file filename="abc.csv" separator=";" startAtRow="2" ignoredColumns="1,2">
<transformers>
(...)
<aggregators>
<aggregator column="3" params="yyyy-MM-dd;lt">DATE</aggregator>
<aggregator column="4">SUM_INTEGERS</aggregator>
</aggregators>
(...)
</file>

You can not configure both: duplicatesArbitratorColumn ( attribute) and aggregators ( element)

Excel test report:

Clone this wiki locally