Cheatsheet of AnyDbTest-Part1

Content of cheat sheet

	Part 1
	AnyDbTest GuiRunner command line
	Perform performance test
	Setup and Teardown
	Ignore one test ad-hoc in test file
	How to run one uncompleted unit testing case
	Test query SQL statement
	Test stored function
	Test DML statement
	Compare two record sets with different field count
	Compare two record sets with different field name
	Show set-style and sequential-style comparison for unit test
	Part 2
	Sandbox test (aka. AutoRollBack)
	Use shared DB Connection file
	Export data set into XML
	Convert XML-fomat data file into Excel format
	How to use Excel as reference test data
	Cross database test
	Cross database test-Compare target result set with reference DB
	Cross database test-Pump data from reference DB
	Validate replication, ETL, DTS and SSIS tasks

 

AnyDbTest GuiRunner Command Line

Command line usage:

AnyDbTest   testCaseFile   [options]


options:

/runScope=<scopeType>   --Run automatically which test cases when GuiRunner loaded , by default scopeType is All.
           <scopeType>= All | PerfTest| UnitTest | None

/help   --Show this help message

The following line is a sample.

AnyDbTest   c:\AnyDbTest\TestCases\EtlValidation.xml   /runScope=All

<<Back to table of content   

 

Perform performance test

Performance testing gives you an approach to measure how long your stored procedures or SQL statements will take in your environment. What do you need to do is to tell AnyDbTest which statement will be tested and what values are for input-type arguments. The following XML is performance test case to diagnose how long the usp_GetStatesInCountry takes to run.

<perfTestCase testCaseName="PT_Fetch_USA_States" 
              statementAlias="proc_Get_state_in_country">
  <inputArguments>
    <argument name="@CountryCode" value="US"/>
  </inputArguments>
  <testCaseRemark>
    test how long it takes to execute usp_GetStatesInCountry
  </testCaseRemark>
</perfTestCase>

<<Back to table of content   

 

Setup and Teardown

In AnyDbTest, we have an opportunity to run some code just before a test method runs, and just after a test method finished. The former process often is called setup process, and the latter one is called teardown process.

In AnyDbTest, there are two kinds of action in setup and teardown section. They separately are normal SQL statement and data pump action. You can add more than one action in each setup/teardown section.

Normal statement action is to execute one SQL or stored procedure. Data pump action is to transfer data from one database into another database, or transfer data from one table into another table on the same database. We will illustrate data pump action in next chapters.

The following code is illustrating how to use setup and teardown in performance testing case. Similarly, it also is not hard to use them in unit testing case.

<perfTestCase testCaseName="PT_Demo_Setup"
              statementAlias="sql_GetCountryRegion">
  <testCaseRemark>
    to demo how to use setup and teardown
  </testCaseRemark>
  <inputArguments>
    <argument name="CountryCode" value="XX"/>
  </inputArguments>

  <setup>
    <statement statementAlias="sql_InsertRegion">
      <inputArguments>
        <argument name="@countryCode" value="XX"/>
        <argument name="@countryName" value="dummy country"/>
      </inputArguments>
      <remark>
        insert one country, its code called XX
      </remark>
    </statement>
  </setup>

  <teardown>
    <statement statementAlias="sql_DeleteRegion">
      <remark>
        to delete the country inserted in setup section
      </remark>
      <inputArguments>
        <argument name="@countryCode" value="XX"/>
      </inputArguments>
    </statement>
  </teardown>
</perfTestCase>

<<Back to table of content   

 

Ignore one test ad-hoc in test file

If you want to ignore one test temporarily, it is very easy. You need only add an extra ignoreThis attribute in test case section. And set the ignoreThis attribute as true (By default, the attribute value is false). This feature is applicable to both performance test and unit test. AnyDbTest will load such test cases no longer.

<perfTestCases>
  <perfTestCase testCaseName="PT_Fetch_USA_States" 
                statementAlias="proc_Get_state_in_country" 
                ignoreThis="true">
    <inputArguments>
      <argument name="@CountryCode" value="US"/>
    </inputArguments>
    <testCaseRemark>
      To test it take how long to execute usp_GetStatesInCountry
    </testCaseRemark>
  </perfTestCase>
</perfTestCases>


 

<<Back to table of content   

 

How to run one uncompleted unit testing case

AnyDbTest has one special assertion namely NotCareResultset. Those cases with this assertion will pass when the targetResultset, referenceResultset, setup and teardown can execute without error. It will not compare the results held by targetResultset and referenceResultset.

It is useful to have this so that when you run the test, you know that you have not yet completed working on the test. It is a marker for work remaining to be done.

<<Back to table of content   

 

Test query SQL statement

Query SQL statements are those SQL statements which will return scalar value or record set. Let us illustrate by two examples.

In the first example, we will test one SQL which returns one scalar value. The statement is,

Select count(*) from Person.CountryRegion;

Its declaration section is,

<sqlDeclaration alias="sql_GetCountOfAllRegion" dbConnection="db1">
  <sql>
    <![CDATA[select count(*) from [Person].[CountryRegion]]]>
  </sql>
  <arguments>
    <argument name="@returnValue" direction="Return" 
              type="MSSQL_INT"/>
  </arguments>
</sqlDeclaration>

We can use the following unit testing to assert the query result is constant of 238.

<unitTestCase testCaseName="UT_TestScalarQuery" 
              assertion="StrictEqual">
  <targetResultset statementAlias="sql_GetCountOfAllRegion" 
                   outputArgument="@returnValue">
  </targetResultset>
  <referenceResultset statementAlias="sql_get_const_number" 
                      outputArgument="result">
    <inputArguments>
      <argument name="@constant" value="238"/>
    </inputArguments>
  </referenceResultset>
</unitTestCase>


 

In the second example, we need to test one SQL which returns one record set. The statement is,

Select * from Person.CountryRegion;

Its declaration section is,

<sqlDeclaration alias="sql_RetrieveAllRegion" dbConnection="db1">
  <sql>
    <![CDATA[select * from [Person].[CountryRegion]]]>
  </sql>
  <arguments>
    <argument name="@returnTable" direction="Return" 
              type="MSSQL_RECORDSET"/>
  </arguments>
</sqlDeclaration>


 

We can use the following unit testing to assert record count of the query is constant of 238.

<unitTestCase testCaseName="UT_TestRecordsetQuery" 
              assertion="RecordCountEqual">
  <targetResultset statementAlias="sql_RetrieveAllRegion" 
                   outputArgument="@returnTable">
  </targetResultset>
  <referenceResultset statementAlias="sql_get_const_number" 
                      outputArgument="result">
    <inputArguments>
      <argument name="@constant" value="238"/>
    </inputArguments>
  </referenceResultset>
</unitTestCase>

<<Back to table of content   

 

Test stored function

For Oracle, the method to test stored procedure is also applicable to test stored function. First all, we need add a procDeclaration section for the stored functions. Then we can use it in performance testing case or unit testing case.

For SQL Server or MySQL, it is recommend using one query SQL statement to test the stored function. Take two examples, we have two functions, one namely fun_get_scalar() returns scalar value, another namely fun_get_recordset() returns recordset.

For fun_get_scalar(), we can use the following query SQL statement,

Select fun_get_scalar();

For fun_get_recordset(), we can use the following query SQL statement to test.

Select * from fun_get_recordset() ;

Now, we can test the original stored function by testing the corresponding query SQL statement. Please look in the Test query SQL statement chapter.

<<Back to table of content   

 

Test DML statement

How to verify one DML statement (include simple DML statement and DML-style stored procedure) in one unit test case?

Because such a statement will not return any result set, we should to construct one corresponding query statement to reflect effect of the DML statement. Then choose one suitable assertion and validate the query statement result in unit testing case.

For example, we want to test the following stored procedure.

/*
Purpose: To update one country region  
*/ 
drop PROCEDURE [Person].[usp_UpdateCountryRegion]
go 
create PROCEDURE [Person].[usp_UpdateCountryRegion]
    @CountryRegionCode nchar(3), @Name nchar(50) 
AS
BEGIN     
    Delete from [Person].[CountryRegion] 
      where CountryRegionCode=@CountryRegionCode;
    Insert into [Person].[CountryRegion] 
    (CountryRegionCode,Name) values (@CountryRegionCode,@Name); 
END;

We can use the following SQL as the validating statement,

select * from [Person].[CountryRegion] 
  where CountryRegionCode=@CountryCode;

The whole steps are very easy. We can call the DML statement in setup process of test case, and then use the validating query as targetResultset statement.

<unitTestCase testCaseName="UT_Test_UpdateCountryRegion" 
              assertion="RecordCountEqual">
  <setup>
    <statement statementAlias="proc_UpdateCountryRegion">
      <inputArguments>
        <argument name="@CountryRegionCode" value="ABC"/>
        <argument name="@Name" value="new country"/>
      </inputArguments>
    </statement>
  </setup>
  <targetResultset statementAlias="sql_GetCountryRegion" 
                   outputArgument="@Return_Table">
    <inputArguments>
      <argument name="@CountryCode" value="ABC"/>
    </inputArguments>
  </targetResultset>
  <referenceResultset statementAlias="sql_get_const_number" 
                      outputArgument="@result">
    <inputArguments>
      <argument name="@constant" value="1"/>
    </inputArguments>
  </referenceResultset>
</unitTestCase>


 

<<Back to table of content   

 

Compare two record sets with different field count

Maybe you have noticed, In Quickstart we compared the record set of usp_GetAllStates and result set of usp_GetStatesInCountry. The result sets have the same field count and the same field names. You will ask question whether AnyDbTest can compare two record sets with different field count or field name. Both of the answers are YES.

Suppose that we have three stored procedures. usp_ GetStatesInCountry will output all fields of Person.StateProvince table; usp_GetStateBriefsInCountry will not output all fields of Person.StateProvince table; usp_GetStateBriefsInCountry_2 just is like usp_GetStateBriefsInCountry, except it rename the field name to StateProvinceName.

In fact, such scenario is very common. Sometimes this is desirable to ignore some columns to perform the comparison, particularly primary keys and time stamp columns.

/*
Purpose: To get all state in specific country
*/
drop PROCEDURE [Person].[usp_GetStatesInCountry]
go
create PROCEDURE [Person].[usp_GetStatesInCountry]
@CountryCode nchar(3)
AS
BEGIN
select * from Person.StateProvince
where  CountryRegionCode=@CountryCode;
END;

Go

/*
Purpose: To get all state in specific country,
but only output brief info of one state,
such as StateProvinceID, StateProvinceCode, Name
*/
drop PROCEDURE [Person].[usp_GetStateBriefsInCountry]
go
create PROCEDURE [Person].[usp_GetStateBriefsInCountry]
@CountryCode nchar(3)
AS
BEGIN
select StateProvinceID, StateProvinceCode,
CountryRegionCode, Name
from Person.StateProvince
where CountryRegionCode=@CountryCode;
END;

Go

/*
Purpose: To get all state in specific country,
but only output brief info of one state,
such as StateProvinceID, StateProvinceCode, StateProvinceName
*/
drop PROCEDURE [Person].[usp_GetStateBriefsInCountry_2]
go
create PROCEDURE [Person].[usp_GetStateBriefsInCountry_2]
@CountryCode nchar(3)
AS
BEGIN
select StateProvinceID, StateProvinceCode,
CountryRegionCode, Name as StateProvinceName
from Person.StateProvince
where  CountryRegionCode=@CountryCode;
END;

We need pay attention to one rule. If we omit outputSetting section or no field listed in outputSetting section, AnyDbTest will compare all fields. Otherwise, AnyDbTest will only compare those fields appears in outputSetting section of each resultset.

Now, we want to compare two record sets with different field count, and to judge they are set-style equal each other or not. We should make sure they have the same amount of fields. The following XML is to compare two recordset with different field count.

<unitTestCase testCaseName="UT_Compare_Recordset_Diff_FieldCount"
  assertion="SetEqual">
  <testCaseRemark>
    to demo how to compare two recordset with different field count
  </testCaseRemark>
  <targetResultset statementAlias="proc_Get_state_in_country"
    outputArgument="@Return_Table" >
    <inputArguments>
      <argument name="@CountryCode" value="US"/>
    </inputArguments>
    <outputSetting>
      <field name="StateProvinceID" />
      <field name="StateProvinceCode" />
      <field name="CountryRegionCode" />
      <field name="Name" />
    </outputSetting>
  </targetResultset>
  <referenceResultset statementAlias="proc_GetStateBriefsInCountry"
    outputArgument="@Return_Table">
    <inputArguments>
      <argument name="@CountryCode" value="US"/>
    </inputArguments>
    <outputSetting>
      <field name="StateProvinceID" />
      <field name="StateProvinceCode" />
      <field name="CountryRegionCode" />
      <field name="Name" />
    </outputSetting>
  </referenceResultset>
</unitTestCase>


 

<<Back to table of content   

 

Compare two record sets with different field name

If you want, you can give every field an outputName in outputSetting section. If we don not give ouputName for one field, AnyDbTest will use field name as outputName by default.

When comparing targetResultset and referenceResultset, AnyDbTest will compare all fields appeared in outputSetting of them. And every outputField of targetResultset will be compared with its counterpart outputField (owning the same outputField name) of referenceResultset.

The following XML is to compare two recordsets with different field name. Please pay attention to outputSetting section.

<unitTestCase testCaseName="UT_Compare_Recordset_Diff_FieldName"
  assertion="SetEqual">
  <testCaseRemark>
    to demo how to compare two recordset with different field name
  </testCaseRemark>
  <targetResultset statementAlias="proc_GetStateBriefsInCountry_2"
    outputArgument="@Return_Table" >
    <inputArguments>
      <argument name="@CountryCode" value="US"/>
    </inputArguments>
    <outputSetting>
      <field name="StateProvinceID" />
      <field name="StateProvinceCode" />
      <field name="CountryRegionCode" />
      <field name="StateProvinceName"  outputName="StateProvinceName"/>
    </outputSetting>
  </targetResultset>

  <referenceResultset statementAlias="proc_GetStateBriefsInCountry"
     outputArgument="@Return_Table">
    <inputArguments>
      <argument name="@CountryCode" value="US"/>
    </inputArguments>
    <outputSetting>
      <field name="StateProvinceID" />
      <field name="StateProvinceCode"/>
      <field name="CountryRegionCode"/>
      <field name="Name"  outputName="StateProvinceName"/>
    </outputSetting>
  </referenceResultset>
</unitTestCase>


 

<<Back to table of content   

 

Show set-style and sequential-style comparison for unit test

After one unit test case ran by GUI Runner, we can click the diff button if we want to see differences highlighted on the screen between targetResultset and referenceResultset. GUI Runner will prompt one diff dialog, like the following figure.

In the diff dialog dialog, there are 5 buttons on the toolbar. They are Create changed diff file, Create common diff file, Create surplus diff file, Create absent diff file and Create sequence diff file. All kinds of diff files are html format, so you can use any web browser to view them, and you can share the diff files to other team members.

Let us spend some time to know individual meaning of these buttons. The first 4 buttons can only be used the scenarios in where targetResultset and referenceResultset both are record sets.

Create changed diff button, Computes the difference of targetResultset with referenceResultset. The difference of these two resultsets is all items that appear in one resultset, but not in another resultset. If an item appears X times in one resultset, and Y times in the another resultset, the difference contains the item X - Y times (zero times if Y >= X).

Create common diff file, Computes the intersection of targetResultset with referenceResultset. The intersection of two resultsets is all items that appear in both of the resultsets. If an item appears X times in one resultset, and Y times in the other one resultset, the intersection contains the item Minimum(X,Y) times.

Create surplus diff file, Computes the added parts in targetResultset over referenceResultset.

Create absent diff file, Computes the added parts in referenceResultset over targetResultset.

Create sequence diff file, Computers the all difference of targetResultset with referenceResultset in the sequential comparison way. That is, the comparison will be done record by record.

<<Back to table of content  

file:///X:/root/sp2/Web/site/images/bullet.gif