BI Future Blog: Devops series: Structuring a tSQLt test environment

Introduction

Imagine that you have worked hard on a database project in a data warehouse environment and you plan to move your code to a production environment. Sometimes tables are often 'touched' by data and there are sometimes tables rarely used in a production environment. When tables are used very often during production usage, errors will occur immediately and users start complaining there is something wrong. But what if there are objects that aren't used very often, it may not very easily detected if something is wrong. Therefore it is a good practice to implement (database) unit testing. In tSQLt test framework there is a procedure (AssertObjectExists) which just checks whether an object exists (or not). This could be a good starting point to implement unit testing with tSQLt. When code is deployed to a test environment you can run this procedure checking if the object exist.

How to structure your unittesting

As said in the introduction, one of the simple tests is checking whether the installation of the code is correctly executed. You can do this by just checking whether an object exists in a database (or not). But, simply generating a tSQLt test procedure is too easy (for me). You want to organize these testprocedures and easily adding unittesting procedures in the future is useful. Therefore, I've decided to organize tSQLt test procedures in the following manner: test procedures are organized in databases, schemas and database objects, like Tables and views. This is depicted in the diagram, below.

In the example above, there are two test procedures for checking the existence of an object and one for testing whether if the Primary key is functioning properly.

A list of objects you want scripts could be :

Tables
Views
Synonyms
Stored procedures
Triggers
Functions
Sequences
etc

How to structure a tSQLt Framework installation process

One of the disadvantages is that tSQLt Framework is installed in the database in which you are working. And, although there is a de-installation script, I still have found tSQLt code in the database. I know that there are administrators who are not very happy with this.

Anyway, my current approach is as follows:

Installation of the framework :

Installation of the tSQLt Framework.
Installation of extra helper code.
(Check whether the installation works by just executing an empty framework)
Installation of the test procedures.

Execution of the test procedures :

Execute the testprocedures (these have their own schema).

And when I'm done I will execute the following steps:

De-installation of the test procedures
De-installation of the tSQLt Framework

Powershell script

I've created a Powershell script and I can execute this script repeatably (that won't happen) because it will create a new folder (with $path = "D:\tmp\SQL\"+"$date_"). I've done this in order to test the script and check differences with previous versions. This not feasible in a real world environment in my opinion. In a real world scenario, new manually created test procedures are added to the test environment. These are very difficult to create automatically. So for now, My advice is to use the script and try it a couple times until you are satisfied. Fix the structure and proceed on and add new manual created test procedures. But perhaps, you can setup a compare and change script to add new test procedures for new objects. For me it is enough to set up the environment once and proceed manually.

This script has the following characteristics :

It supports multiple databases.
It supports multiple schemas.
It supports all kind of database objects.
It's is an initial setup script and it's not execute it twice and save the scripts to the same location.

# Datetime used for creating the folder for generated scripts
$date_ = (Get-Date -f yyyyMMddHHmmss)

# Name of the SQL Server name
$ServerName = "." 

# Location where the scripts are stored
$path = "D:\tSQLt\AdventureWorksLT\unittesting\"

# Used this for testing purposes
# $path = "D:\tmp\SQL\"+"$date_"

# The databases that you want to script (-or $_.Name -eq '<database>')
$dbs = $serverInstance.Databases | Where-Object {($_.Name -eq 'AdventureWorksLT2017') }

# The database objects you want to script
$IncludeTypes = @("Tables","StoredProcedures","Views","UserDefinedFunctions", "Triggers") #$db.

# The schemas that you want to script.
$IncludeSchemas = @("SalesLT")

# The name of the generated tSQLt test procedures
$TestSchema = "advtests"

# Connect to a SQL Server instance
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
$serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName
$so = new-object ('Microsoft.SqlServer.Management.Smo.ScriptingOptions')

# For every database in the variable $dbs
foreach ($db in $dbs)
{
    $dbname = "$db".replace("[","").replace("]","")
    $dbpath = "$path"+ "\"+"$dbname"+"\"

    # Create a folder for every database
    if (!(Test-Path $dbpath))
        {$null=new-item -type directory -name "$dbname" -path "$path"}

    # For every schema in the Database
    foreach ($sch in $db.Schemas)
    {
        $schema = "$sch".replace("[","").replace("]","")

        # Is the schema present in the list of desired schemas
        If ($schema -in $IncludeSchemas) 
        {
           $schemapath = "$dbpath"+ "$schema"+"\"
           
           # Create a folder for every schema
           if (!(Test-Path $schemapath))
                {$null=new-item -type directory -name "$schema" -path "$dbpath"}
           
           $SchemaInstallScript = 
            "SET ANSI_PADDING ON -- needed to prevent errors`r`n" + 
            "`r`n" + 
            "--:setvar scriptpath `"$path`"`r`n" + 
            "`r`n"

            # For every type in the list of object types (eg. Stored procedures)
            foreach ($Type in $IncludeTypes)
            {
                # Create a folder for every objecttype
                $objpath = "$schemapath" + "$Type" + "\"
                if (!(Test-Path $objpath))
                    {$null=new-item -type directory -name "$Type" -path "$schemapath"}

                # This for installation SQL file (install.sql) for Object Types (Tables, SP, etc 
                $ObjTypeInstallScript = 
                "SET ANSI_PADDING ON -- needed to prevent errors`r`n" + 
                "`r`n" + 
                "--:setvar scriptpath `"$path`"`r`n" + 
                "`r`n"
                
                # Adding items to the Schema install script.
                $SchemaInstallScript += 
                "print('$Type')`r`n" + 
                "GO`r`n" + 
                ":r `$(scriptpath)`"\$dbname\$schema\$Type\install.sql`"`r`n" + 
                "GO`r`n"

                # For every ObjectType in the list  
                foreach ($ObjType in $db.$Type)
                {

                    # Only the included schemas are scripted                      
                    If ($IncludeSchemas -contains $ObjType.Schema ) 
                    {
                        $ObjName = $ObjType.Name.replace("[","").replace("]","") 
                        $objectpath = "$objpath" + "$ObjName" + "\"
                       
                        # Create a new folder for the object
                        if (!(Test-Path $objectpath))
                            {$null=new-item -type directory -name "$ObjName" -path "$objpath"}   
                        
                        $OutObjectFile = "$objectpath" + "test_exists_" + $schema + "_" + $ObjName + ".sql"   
                        
                        # Adding items to the ObjType install script.
                        $ObjTypeInstallScript += 
                            "print('$ObjName')`r`n" + 
                            "GO`r`n" + 
                            ":r `$(scriptpath)`"\$dbname\$schema\$Type\$ObjName\install.sql`"`r`n" + 
                            "GO`r`n"

                        # Generating the actual test exists procedure
                        $ContentObjectFile = 
                            "USE $dbname`r`n" + 
                            "GO`r`n" + 
                            "`r`n" + 
                            "IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'$TestSchema.test_exists_$schema`_$ObjName') AND type in (N'P', N'PC'))`r`n" + 
                            "EXEC ('`r`n" + 
                         "    CREATE PROCEDURE $TestSchema.test_exists_$schema`_$ObjName AS`r`n" + 
                      "        BEGIN`r`n" + 
                   "            THROW 50001, ''tSQL generate_from_tpl error!'', 1;`r`n" + 
                      "        END`r`n" + 
                            "')`r`n" + 
                            "GO`r`n" + 
                            "ALTER PROCEDURE $TestSchema.test_exists_$schema`_$ObjName AS`r`n" + 
                            "/*`r`n" + 
                            "Author : `r`n" + 
                            "`r`n" + 
                            "Description: `r`n" + 
                         "    This stored is automatically generated`r`n" + 
                            "`r`n" + 
                            "History `r`n" + 
                            "    $date_ : Generated`r`n" + 
                            " `r`n" + 
                            "*/`r`n" + 
                            "BEGIN`r`n" + 
                            "SET NOCOUNT ON;`r`n" + 
                            "`r`n" + 
                            "----- ASSERT -------------------------------------------------`r`n" + 
                            "EXEC tSQLt.AssertObjectExists @ObjectName = N'$schema.$ObjName';`r`n" + 
                            "`r`n" +  
                            "END;" | out-File $OutObjectFile -Encoding ASCII
                        
                        # Generating the local install file in the folder
                        $OutInstallFile = "$objectpath" + "install.sql"
                        $ContentInstallFile = 
                            "SET ANSI_PADDING ON -- needed to prevent errors `r`n" + 
                            "`r`n" + 
                            "USE $dbname`r`n" + 
                            "GO`r`n" + 
                            "`r`n" + 
                            "--:setvar scriptpath `"$path`"`r`n" + 
                            "`r`n" + 
                            "DECLARE @TestSchema as varchar(30) = '$TestSchema' `r`n" + 
                            "IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = @TestSchema)`r`n" + 
                         "    EXEC tSQLt.NewTestClass @TestSchema`r`n" + 
                         "`r`n" + 
                            "print('test_exists_$ObjName')`r`n" + 
                            "GO`r`n" + 
                            ":r  `$(scriptpath)`"\$dbname\$schema\$Type\$ObjName\test_exists_$schema`_$ObjName.sql`"`r`n " + 
                            "GO" | out-File $OutInstallFile -Encoding ASCII

                        # OutCMDFile
                        $OutCMDFile = "$objectpath" + "install.cmd"
                        $ContentCMDFile = 
                            "REM Object CMD file`r`n" + 
                            "SET curpath=`"$path\`"`r`n" + 
                            "SQLCMD -S localhost -E -i `"install.sql`" -v scriptpath=%curpath%`r`n"+ 
                            "PAUSE" | out-File $OutCMDFile -Encoding ASCII
                    } # if
                } #object
                # Save the ObjType install.sql file
                $OutObjTypeInstallFile = "$objpath" + "install.sql"
                $ObjTypeInstallScript | out-File $OutObjTypeInstallFile -Encoding ASCII

                # creating the ObjType cmd file
                $OutObjTypeCMDFile = "$objpath" + "install.cmd"
                $ContentObjTypeCMDFile = 
                    "REM ObjectType CMD file`r`n" + 
                    "SET curpath=$path\`r`n" + 
                    "SQLCMD -S localhost -E -i `"install.sql`" -v scriptpath=`"%curpath%`"`r`n"+ 
                    "PAUSE" | out-File $OutObjTypeCMDFile -Encoding ASCII
            } # object type
           
            # Save the Schema install.sql file
             $OutSchemaInstallScript = "$schemapath" + "install.sql"
             $SchemaInstallScript | out-File $OutSchemaInstallScript -Encoding ASCII

            # creating the schema cmd file
             $OutschemaCMDFile = "$schemapath" + "install.cmd"
             $ContentSchemaCMDFile = 
                "REM Schema CMD file`r`n" + 
                "SET curpath=$path\`r`n" + 
                "SQLCMD -S localhost -E -i `"install.sql`" -v scriptpath=`"%curpath%`"`r`n"+ 
                "PAUSE" | out-File $OutschemaCMDFile -Encoding ASCII 

        } #if included in schema
    } #schema
} #db

This results in the following folderstructure :

On mostly every level I've created install scripts that can execute certain areas of testprocedures or even one unittest procedure. Below an example of executing all testprocedures on a database.

Below an example of the content of testprocedure file :

USE AdventureWorksLT2017
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'advtests.test_exists_SalesLT_Customer') AND type in (N'P', N'PC'))
EXEC ('
    CREATE PROCEDURE advtests.test_exists_SalesLT_Customer AS
        BEGIN
            THROW 50001, ''tSQL generate_from_tpl error!'', 1;
        END
')
GO
ALTER PROCEDURE advtests.test_exists_SalesLT_Customer AS
/*
Author : 

Description: 
    This stored procedure is automatically generated

History 
    20190605180830 : Generated
 
*/
BEGIN
SET NOCOUNT ON;

----- ASSERT -------------------------------------------------
EXEC tSQLt.AssertObjectExists @ObjectName = N'SalesLT.Customer';

END;

Final thoughts

I was building the folder structure and scripts manually, but thought that it would be better to use Powershell script to create a test environment with one type of testing procedure: does the object exist. Now, I can script every database/project and setup very fast a test environment.

Hennie

BI Future Blog

woensdag 5 juni 2019

Devops series: Structuring a tSQLt test environment

Introduction

How to structure your unittesting

How to structure a tSQLt Framework installation process

Powershell script

Final thoughts

Geen opmerkingen:

Een reactie posten

Zoeken in deze blog