ApexSQL Unit Test – Demo kit

Applies to
ApexSQL Unit Test

Summary
This article explains the new feature – Demo kit. It provides a way for the user to evaluate ApexSQL Unit Test without affecting the current databases and without the need to set the environment and install necessary pre-requisites

Description

What does this demo kit do, consist of?

This demo kit contains scripts to build a new PUBS_QA database with pre-installed tSQLt framework and 15 SQL unit tests created specifically for PUBS_QA database. These 15 unit tests are SQL procedures that analyze the PUBS_QA database in order to find design, performance and data problems

Why we implemented demo kit in ApexSQL Unit Test?

1. It provides quick and simple evaluation of ApexSQL Unit Test, without the need for previous knowledge of tSQLt framework and SQL database unit testing
2. By using the demo kit, everything that the user will need to learn basics of SQL unit testing and tSQLt framework will be in one place, and available within a few clicks. This means that the user does not have to create a sample database, to install tSQLt framework after that, and to create unit tests
3. It can be installed right from ApexSQL Unit Test itself, allowing evaluators an easy way to get started

How to install and use the demo kit?

The option to create a demo kit is located in the Add database dialog, under the Unit Test explorer tab:

It is necessary for the SQL Server instance to be selected (highlighted), in order for the demo kit option to become available. By clicking the Create PUBS_QA demo kit option, the following steps will be automatically performed:

• PUBS_QA database is created, containing the following tables: authors, discounts, employee, jobs, pub_info, publishers, roysched, sales, stores, titleauthor and titles
• tSQLt framework is installed in the PUBS_QA database
• a set of 15 SQL database unit tests will be created under the PUBS_QA_Test class

After the demo kit installation is finished, PUBS_QA database can be found in the Object Explorer pane, and it will be added to the Unit test explorer tab, as tSQLt will be pre-installed:

The following is a table that contains all unit tests that will be created:

The next step after the installation

The next step is to run unit tests against PUBS_QA database from the Unit Test explorer tab:

Test results shows that 2 out of 15 available tests failed:

• if all books have price checks the titles table in the PUBS_QA database, specifically if any book has the NULL value in the price column. The titles table has two rows, where the price value is NULL. This is inserted on purpose, in order to simulate the failed test
• if database contains any table with columns of type image – checks if any table in the PUBS_QA database has at least one column which type is image. For the same reason, as in the previously explained test, this is designed for the purpose of showing the failed test. The type of the logo column in the pub_info table is image, so the test fails

The information about failed tests is available in the Test result messages section:

Test results provide the user with the information about how to fix the failed test:

• The if all books have price test will pass when the expected number of NULL fields is 2, instead of 0 as it is originally specified
• The if database contains any table with columns of type image test will pass when the data type of the logo column is changed

Fixing failed tests

To fix the failed test, either test itself should be modified, or an object that is causing the failure. Let’s edit the first failing test, using the Edit command from the context menu:

By clicking the Edit command from the context menu, SQL script of the stored procedure that represents a unit test will be opened in a new query. A portion of the SQL script that is by default commented is:

CREATE PROCEDURE PUBS_QA_Tests.[test if all books have price]
AS
BEGIN
	-- Initially 'titles' table has 2 books without defined prices.
	-- We need to correct that by defining them a price - 
	-- uncomment 2 following lines for fixing this issue
	-- UPDATE dbo.titles
	-- SET price=200 WHERE title_id='MC3026' OR title_id='PC9999';

The cause of the failing test is described, as well as how to fix it. Under the commented part, the update statement is specified that will add missing prices. Let’s executing the suggested update statement against the PUBS_QA database:

UPDATE dbo.titles
SET price = 200
WHERE title_id = 'MC3026'
	OR title_id = 'PC9999';

This will update remaining NULL values in the price column with the appropriate values, so the next time the test run, it will pass:

Test result messages shows the following output:

‘PUBS_QA_Tests’ tests started
  ’test if all books have price’ test SUCCESS

In addition, the icon in the Unit test explorer will be changed, showing that the previously failed test now passed:

Let’s edit the second failing test using the Edit command from the context menu:

A portion of the SQL script that represents the selected unit test is as follows:

CREATE PROCEDURE PUBS_QA_Tests.[test if database contains any table with columns of type image]
AS
BEGIN
	-- When storing images in SQL Server do not use the 'image' datatype, 
	-- according to MS it is being phased out in new versions of SQL server. Use always varbinary(max) instead
	-- If you want to pass this test aka change image type with varbinary(max) type
	-- in the table 'pub_info', just uncomment following two lines.
	-- ALTER TABLE dbo.pub_info 
	-- ALTER COLUMN logo varbinary(max) null

The last two lines shows simple ALTER statement that can be used to change the type of the logo column from image to varbinary. Let’s execute the suggested statement:

ALTER TABLE dbo.pub_info

ALTER COLUMN logo VARBINARY(max) NULL

Now, let’s run the previously failed test using the Run command from the context menu:

As a result, test will pass, which is shown in the Test result messages section:

‘PUBS_QA_Tests’ tests started
  ’test if database contains any table with columns of type image’ test SUCCESS

In addition, the icon for the selected test will be changed, showing that test passed:

The user can explore the rest of unit tests, change them, and check the test execution results. The advantage of the demo kit feature is that ApexSQL Unit Test can be fully explored without the need for any previous preparation in the user environment, and without affecting the existing databases. PUBS_QA database can be deleted at any point, and created again with a default set of unit tests.