Skip to content

KEY

Jump to bottom
ostry202 edited this page Feb 12, 2021 · 22 revisions

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

Example with transformers

<cmpSqlResultsTest>
    <compare mode="KEY" diffTableSize="5" chunk="0" fileOutputOn="true" keyColumns="2">
        <sql datasourceName="SQL_SERVER" filename="ContactTypeActualTable.sql"
             keyTableName="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>

Example with transformers, aggregators and beforeSqls

<cmpSqlResultsTest>
    <compare mode="KEY" diffTableSize="5" chunk="0" fileOutputOn="true" keyColumns="1,2">
        <sql datasourceName="SQL_SERVER" filename="product.sql" keyTableName="dbo.TemplateKeyComparatorCheckAggregators" keyTableColumns="StoreNumber INTEGER not NULL, ProductID INTEGER not NULL" />
		<beforeSqls>
			<sql datasourceName="SQL_SERVER" filename="drop.sql"/>
			<sql datasourceName="SQL_SERVER" filename="create.sql"/>
			<sql datasourceName="SQL_SERVER" filename="insert.sql"/>
		</beforeSqls>
        <file filename="product.csv" separator=";" startAtRow="2" ignoredColumns="1" threadColumn="1">
            <transformers>
                <transformer column="2">REMOVE_LEADING_ZEROS</transformer>
            </transformers>
            <aggregators>
                <condition column="4" conditionValue="0">NOT_EQUALS</condition>
                <aggregator column="3" params="yyyy-MM-dd;gt">DATE</aggregator>
                <aggregator column="4">SUM_INTEGERS</aggregator>
            </aggregators>
        </file>
    </compare>
</cmpSqlResultsTest>
  • diffTableSize - number of differences which will be displayed in logs. Even records from first DB/SQL and odd from the second (so the table will have two times more rows than defined here). Default is 5.
  • chunk - number of differences after which further comparison is stopped. 0 (or if not set) means never stop -> go to the end of all records. Number of differences visible in test result may be smaller than real one if FETCH comparator stopped his job because of chunk. So if the number of differences is equal to the chunk, it may mean that there are more differences. Default value is 0.
  • fileOutputOn - boolean value - if true all results will be saved in CSV files (same name as XML test configuration file but with different prefix and extension). File may be incomplete (if chunk > 0 - see above). In case of FETCH comparator 3 files will be generated: test_name_diff.csv - CSV with differences (all or chunked) test_name_sql1.csv - all (or chunked) data returned by first SQL (from config below) test_name_sql2.csv - all (or chunked) data returned by second SQL (from config below) Default is false.
  • 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).
  • datasourceName - names of connection to database engine defined in cmpSqlResults-config.xml file
  <datasource maxPollSize="6">
            <name>SQL_SERVER</name>
            <driver>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver>
			<!--<url>jdbc:sqlserver://${SQL_SERVER}\${SQL_SERVERINSTANCE};databaseName=${SQL_SERVERDBNAME}</url>-->
            <url>jdbc:sqlserver://${SQL_SERVER};databaseName=${SQL_SERVERDBNAME}</url>
            <user>${USERNAME}</user>
            <password>${PASSWORD}</password>
        </datasource>
  • 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.

  • 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)

  • 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="SQL_SERVER" filename="drop.sql"/>
<sql datasourceName="SQL_SERVER" filename="create.sql"/>
<sql datasourceName="SQL_SERVER" 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"). You can not configure both: duplicatesArbitratorColumn ( attribute) and aggregators ( element) 
<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>
  • ignoredColumns: columns in CSV file which will be ignored. If [testSetColumn] is set, you must add it to [ignoredColumns], in [transformers] or [aggregators] you have to use the remaining columns (without ignoredColumns). The column order must match (CSV file and SQL results)

Excel test report with transformers:

Excel test report with transformers, aggregators and beforeSqls

Clone this wiki locally