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?
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:
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:
Test name |
If all books have price |
If all ID numbers of the authors have the correct pattern |
If database contains any table with columns of type image |
If database contains decimal type of columns without specified precision and scale |
If employee ID is unique |
If job ID auto increments |
If result sets have the same metadata |
If royalty percentages wrong scope |
If some publisher has no name specified |
If the sales table has the valid values of order dates |
If titleauthor table has the valid ID for author and title |
If titles columns insert in correct order |
If wrong job lvl trigger |
Whether tables have expected schema |
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:
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:
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.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center