Anatomy of SQL Server article 11th automated testing of multiple versions of SQL Server (translated)

Anatomy of SQL Server article 11th automated testing of multiple versions of SQL Server (translated)

http://improve.dk/automated-testing-of-orcamdf-against-multiple-sql-server-versions/

Since I released Orcamdf Studio, I have realized the differences between some system tables between SQL2005 and SQL2008.

These differences cause orcamdf parsing to fail because the code is for the format of the R2

When I needed to do SQL2005 compatibility, I gradually realized that I needed to expand the test coverage of multiple SQL Server versions, instead of just one version of the previous test.

Also, I need to perform specific tests on specific versions of features (for example: Sparse column tests can only run on SQLSERVER2008 and above)

NUnit Testcasesourceattribute Problem Solving

NUnit supports inline parameter testing with the TestCase property. Better yet, for dynamic test cases we can also provide parameter data, use the Testcasesource property, and use the Testcasesource attribute.

First, I implemented a simple enumeration to overwrite the version supported by my current work:

 Public enum databaseversion{    SqlServer2005,    SqlServer2008,    sqlserver2008r2,}

Then I create the Sqlservertestattribute class, directly inheriting from Testcasesourceattribute, just like this:

 Public classsqlservertestattribute:testcasesourceattribute{Private StaticIenumerable<testcasedata>versions {Get        {            foreach(varValueinchEnum.getvalues (typeof(databaseversion))) yield return NewTestcasedata (value). Setcategory (value.        ToString ()); }    }     PublicSqlservertestattribute ():Base(typeof(Sqlservertestattribute),"versions")    { }}

This Sqlservertestattribute class tells Testcasesourceattribute to find the source data for the test case in the private static version property (private statics).

The version attribute enumerates all the databaseversion values and returns them one after the other--ensuring that the test category is set to the name of the Databaseversion value

Next, I'll convert the current test to use the new Sqlservertest attribute, instead of the previous vanilla NUnit Test attribute:

[Sqlservertest]  Public void Heapforwardedrecord (databaseversion version) {    ...}

This will cause all of my tests to run once with each of the enumeration values in the Databaseversion enumeration, automatically getting each value in the input version parameter

Support for different development environments
Now, I don't want to force everyone to install all versions of SQL server--they may just want the software to support SQL Server 2005 & 2008r2 enough. In the OrcaMDF.Core.Tests project, I defined the connection string for each of the supported test databases, just like this

<connectionStrings>    <clear/>    <add name="SqlServer2005" connectionstring="Data source=. sql2005;integrated security=sspi"/>    <add name="sqlserver2008r2" connectionstring="Data source=.;i ntegrated security=sspi"/></connectionstrings>

If a database does not have a corresponding connection string (the name corresponds to the Databaseversion enumeration value) then the test for that version will not run. Because of this, I currently ignore SQL Server 2008 and only have SQL 2005 and SQL 2008R2 installed on my machine

In order to filter the currently available database, I modified my test case to let the base class run the actual test, using the lambda expression:

[Sqlservertest] Public voidHeapforwardedrecord (databaseversion version) {rundatabasetest (version, DB=    {        varScanner =NewDatascanner (DB); varrows = Scanner. Scantable ("Heapforwardedrecord").        ToList (); Assert.AreEqual ( -, rows[0]. field<int> ("A")); Assert.AreEqual ("". PadLeft ( the,'A'), rows[0]. field<string> ("B")); Assert.AreEqual ( -, rows[1]. field<int> ("A")); Assert.AreEqual ("". PadLeft (4000,'B'), rows[1]. field<string> ("B")); });}

This rundatabase method is declared inside the Sqlserversystemtestbase class.

protected voidRundatabasetest (databaseversion version, action<database>test) {    stringVersionconnectionname =version.    ToString (); //Only run test for the version if a connection string has been provided    if(Configurationmanager.connectionstrings[versionconnectionname] = =NULL) assert.inconclusive (); //Setup database and store file paths, if we haven ' t do so alreadyEnsuredatabaseissetup (version); //Run actual Test    using(vardb =NewDatabase (databasefiles[version])) test (db);}

If the corresponding connection string is not declared in the configuration file, we will discard the test and mark him as indeterminate-we cannot run it at all according to the current configuration situation.

Next, Ensuredatabaseissetup () executes the usual configuration code (details can refer to the previous article)
Although this time each database version, each test firmware needs to be executed. The last Orcamdf instance will be created and passed to the actual test parameters

Features that support different SQL Server versions
As mentioned earlier, I need a test method for the execution of a specific SQL Server version.
The standard Sqlservertestattribute automatically enumerates all the values inside the Databaseversion enumeration, but I have no reason to create a separate sqlserver2005testattribute like this.

 Public classsqlserver2005testattribute:testcasesourceattribute{Private StaticIenumerable<testcasedata>versions {Get        {            yield return NewTestcasedata (databaseversion.sqlserver2005).        Setcategory (DatabaseVersion.SqlServer2005.ToString ()); }    }     PublicSqlserver2005testattribute ():Base(typeof(Sqlserver2005testattribute),"versions")    { }}

What if you need to run the test on SQL Server 2008?

 Public classsqlserver2008plustestattribute:testcasesourceattribute{Private StaticIenumerable<testcasedata>versions {Get        {            foreach(varValueinchEnum.getvalues (typeof(databaseversion))) if((databaseversion) value >=databaseversion.sqlserver2008)yield return NewTestcasedata (value). Setcategory (value.        ToString ()); }    }     PublicSqlserver2008plustestattribute ():Base(typeof(Sqlserver2008plustestattribute),"versions")    { }}

Once we have attributes, it will be easier to run separate tests for our version

[Sqlserver2008plustest] Public voidscanallnullsparse (databaseversion version) {rundatabasetest (version, DB=    {        varScanner =NewDatascanner (DB); varrows = Scanner. Scantable ("Scanallnullsparse").  ToList (); Sparse Column assert.areequal (NULL, rows[0]. field<int?> ("A")); Assert.AreEqual (NULL, rows[0]. field<int?> ("B")); });}

Support for the ReSharper test runner
In order to run the test, we need ReSharper 6.0 because ReSharper 5.1 does not support Testcasesource attribute.
Once you have performed the test, you will see the following test results (SQL Server 2005 & R2 Test already supported)

Each test case automatically tests multiple versions of Databaseversion (in addition to parsing the test because he does not implement sqlserversystemtestbase and therefore cannot run multi-version tests).

Most of the tests on SQL Server 2005 failed because I did not support SQL Server 2005. All SQL2008 tests are inconclusive when I run the test.

Finally, all the tests for SQL2008R2 have passed

Test filter
Obviously, we can't always test all versions of SQL Server, which is a waste of time. One way to disable testing for a specific version is to delete the connection string.

However, this still produces ambiguous output, and it can be cumbersome to always modify the configuration file

Unfortunately, ReSharper Test Runner does not support category filtering for parameterized tests created with Testcasesourceattribute.
I have a feature request on Youtrack, and I hope the ReSharper team will consider adding this feature to ReSharper6.1. If you also think this feature is great, consider voting support

Fortunately, NUnit Test Runner does not support this filtering. Open the OrcaMDF.Core.Tests assembly in NUnit test runner to give you the following results

Notice how NUnit knows the parameterized test parameters before we run the test! At the same time also need to pay attention to nunit how to let Differingrecordformats test only run at SQL2008 or above,

And the Fgspecificclusteredallocation test only lets run at SQL2005 or above

Fortunately, if we click on the Tab tab of categories, we can get a list of test categories

By explicitly selecting a specific version category, we can choose to run some versions, and once run, the small dots in the header of the category category that are not selected will turn gray

It can be noted that the elapsed time took 89 seconds, basically 1 seconds a test, and 98% of the time spent on the LOB type characteristics of the test.
Because of the category format, I was able to choose among the major test categories to easily filter out long-running test projects and focus only on those categories that were quickly completed.

LOB types need to be tested in particular because they involve a lot of disk activity before the database starts, configuration tables and configuration rows are created

Looking to the future
Adding a new version is as simple as installing SQL Server, adding a connection string to the configuration item, and finally adding the SQL Server version name into the Databaseversion enumeration. That's all.

Further, in a way I need to test multiple upgrade paths sequentially. Based on some of my own tests, a database that was upgraded from SQL Server 2005 to SQLSERVER2008 R2

may be different from the database created locally on SQLSERVER2008 R2, or from SQL2008 to SQL2008R2. Therefore, I need to test a number of different upgrade paths to ensure full compatibility.

However, the priority of the compatibility test is lower in my test priority list because these compatibility tests can take a lot of time

End of the 11th chapter

Anatomy of SQL Server article 11th automated testing of multiple versions of SQL Server (translated)