Getting started

1. Get compiled jar file from releases page

Zip file includes:

• DBTestCompare-1.0-SNAPSHOT-jar-with-dependencies.jar

• "test_definition" folder with test examples in XML files and datasources definitions "cmpSqlResults-config.xml"

• "jdbc_drivers" folder with jdbc drivers (only opensource are included; MySQL, MariaDB, PostgreSQL), to deploy licensed drivers follow the instructions

2. Edit "cmpSqlResults-config.xml" file from "test_definition" folder to target your database server.

Edit corresponding datasource definition, the file includes examples of the connection string to Teradata, SQL Server, PostgreSQL, MariaDB and MySQL.

Set proper DBSERVER, DBNAME, dbuser and dbpassword to connect to you databases under tests.

Set number of threads for parallel test execution

<threads>5</threads>

Set maxPollSize for each of the datasource definitions, BE CAREFULL - If the number of threads is greater than maxPollSize, the thread will wait for connection. Don't forget it is somehow related!

Set teamcityLogsEnabled to "true" to enable real time test execution progress in TeamCity Continuous Integration tool. You can override that setting by command line parameter.

<logger teamcityLogsEnabled="true" logLevel="TRACE" />

Set logLevel for "log4j"

<logger teamcityLogsEnabled="false" logLevel="TRACE" />

Possible values:TRACE,DEBUG,WARN,ERROR

Set testFailureExitCode to choose exit code in case of test failure, by defualt is 0.

<testFailureExitCode>2</testFailureExitCode>

Set filter section to include or exclude some tests. You can override that setting by command line parameter.

    <filter>
...
    </filter>

Full content of "cmpSqlResults-config.xml" file

<cmpSqlResultsConfig>
    <logger teamcityLogsEnabled="false" logLevel="TRACE" />
    <filter>
        <!-- categories/directories or test files which you want to run. All tests will be run if
        you leave it empty. You can use many (or none) include elements. -->
		<!--<include>fetch_compare_test.inline_sql_MySQL_PostgreSQL</include>
		<include>compare_with_file</include>
        <include>delta</include>-->

        <!-- categories/directories or test files which you want to exclude (from those included). You can use many
        (or none) exclude elements. -->
        <exclude>cube</exclude>
    </filter>

    <!-- min:1 max:unchecked (be careful!) -->
    <threads>5</threads>

    <!-- if any test will fail you can decide what exit code you wish to use. Default is 0 -->
    <testFailureExitCode>2</testFailureExitCode>

    <datasources>
        <!-- If number of threads will be greater than maxPollSize, thread will wait for connection. Don't forget it is
        somehow related! -->
        <datasource maxPollSize="6">
            <name>TERADATA</name>
            <driver>com.teradata.jdbc.TeraDriver</driver>
            <url>jdbc:teradata://DBSERVER/DATABASE=DBNAME,charset=UTF8</url>
            <user>username</user>
            <password>password</password>
        </datasource>
        <datasource maxPollSize="6">
            <name>SQL_SERVER</name>
            <driver>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver>
            <url>jdbc:sqlserver://DBSERVER\SQLEXPRESS;databaseName=dbname</url>
            <user>dbuser</user>
            <password>dbpassword</password>
        </datasource>
        <datasource maxPollSize="6">
            <name>SQL_SERVER_WIN_AUTH</name>
            <driver>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver>
            <!-- to use WIN-AUTH (integratedSecurity=true) you must put sqljdbc_auth.dll file in windows/system32
            folder. Download it from here: https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774 -->
            <url>jdbc:sqlserver://DBSERVER;databaseName=master;integratedSecurity=true</url>
        </datasource>
		<datasource maxPollSize="6">
            <name>MySQL</name>
            <driver>com.mysql.jdbc.Driver</driver>
            <url>jdbc:mysql://DBSERVER:3306/dbname</url>
            <user>dbuser</user>
            <password>dbpassword</password>
		</datasource>
		<datasource maxPollSize="6">
            <name>PostgreSQL</name>
            <driver>org.postgresql.Driver</driver>
            <url>jdbc:postgresql://DBSERVER:5432/dbname</url>
            <user>dbuser</user>
            <password>dbpassword</password>
		</datasource>
    </datasources>
</cmpSqlResultsConfig>

3. Edit one of the test example in "test-definitions" folder

<cmpSqlResultsTest>
    <compare mode="FETCH" fetchSize="100" chunk="10" diffTableSize="5" fileOutputOn="false">
        <sql datasourceName="SQL_SERVER">
            <![CDATA[
SELECT [AddressID]
      ,[AddressLine1]
      ,[AddressLine2]
      ,[City]
      ,[StateProvinceID]
      ,[PostalCode]
      ,[ModifiedDate]
 FROM [AdventureWorks2008R2].[Person].[Address]
 order by AddressID
        </sql>
        <sql datasourceName="PostgreSQL">
            <![CDATA[
select addressid, addressline1, addressline2, city, stateprovinceid, postalcode, modifieddate
from public.personaddress
order by addressid;
        ]]>
        </sql>
    </compare>
</cmpSqlResultsTest>

4. To execute tests run program (Java 8 must be installed first (JDK or JRE)):

If you set JAVA_HOME variable:

java -jar DBTestCompare-1.0-SNAPSHOT-jar-with-dependencies.jar

5. Check the test results in command line or go to "test-output" folder to check html test report