Sandra Walters BTEK Software Inc Intro to Unit

Sandra Walters BTEK Software, Inc. Intro to Unit Testing with t. SQLt

Agenda §Why should I write unit tests? §…SQL unit tests? ! How? §Using t. SQLt in SQL Management Studio §The t. SQLt unit test adapter for Visual Studio §SSDT projects with t. SQLt §Ready. Roll projects with t. SQLt §CI with t. SQLt tests and VSTS §Code Coverage with SQL Cover

Bugs found by unit tests are the cheapest to fix Estimated Cost per Bug (US $) System Test $5, 000 Integration Test $500 Full Build $50 Unit Test $5 Source: . How Google Tests Software. Westford, MA: Addison-Wesley, 2012.

Why write unit tests? • • Helps team agree on “working as intended” Instant feedback to the developer Does refactored code behave well? Defines expectations (when used with TDD)

What is t. SQLt? • • Database unit testing framework MS SQL 2005 SP 2 and up, all editions Unit tests, and asserts, written in T-SQL Tests are run within transactions Tests are grouped within schemas Fake tables and views Stored procedure “spies”

http: //tsqlt. org Source: https: //github. com/t. SQLt-org/tsqlt

New in SQL 2017 Server-level option: (on by default) CLR strict security How to enable the t. SQLt CLR to be installed: EXEC sp_configure ‘show advanced options’, 1 RECONFIGURE; EXEC sp_configure ‘clr strict security’, 0 RECONFIGURE; NOT FOR SECURE / PRODUCTION ENVIRONMENTS… but then, you really shouldn’t be unit testing there anyway. Further reading: https: //github. com/t. SQLt-org/t. SQLt/issues/25

Assert methods available in the t. SQLt schema • • • Assert. Empty. Table Assert. Equals. String Assert. Equals. Table. Schema Assert. Like Assert. Not. Equals Assert. Object. Does. Not. Exist Assert. Object. Exists Assert. Result. Sets. Have. Same. Meta. Data Fail

How to define t. SQLt unit tests • Create a new test class (schema) t. SQLt. New. Test. Class ‘class name’ • Create unit tests (stored procedures) in the test class; must start with the word ‘test’ CREATE PROC [Class. Name]. [Test something]…. • Create a unit test setup (stored procedure) if necessary; must be named ‘Setup’ CREATE PROC [Class. Name]. [Set. Up]…

How to run t. SQLt unit tests • Run all tests t. SQLt. Run. All • Run a single unit test t. SQLt. Run ‘Test. Name’ • Run all tests in a test class t. SQLt. Run ‘Class. Name’

Using SQL Test

Sample Database: Wide World Importers Source: https: //github. com/Microsoft/sql-server-samples

Demo 1. t. SQLt added to an existing database 2. Running t. SQLt unit tests in SQL Management Studio 3. Creating new tests

SSDT Projects with t. SQLt “Composite” Projects: • Main database project • Subordinate projects (such as t. SQLt unit tests)

Unresolved references! Subordinate projects: set the referred project’s location as “Same Database”

Running t. SQLt Unit Tests in the VS IDE

Specify test SQL database connection in a runsettings file:

Test Explorer with t. SQLt unit tests found

Ready. Roll Core in VS 2017 Enterprise

Creating a Ready. Roll Core Project in VS 2017 ‘Wizard’-like walkthrough to create project from existing database:

Imported list of objects:

Continuous Integration with t. SQLt Test Adapter must be added to the solution (3 possible ways) • Place binaries directly on the build server in a folder accessible by the build agent • Add the binaries to a solution folder within the Visual Studio solution • Add the Nu. Get package for the test adapter to any project within the solution that can accept Nu. Get packages (no SSDT or Ready. Roll projects)

At a minimum, the build must: Push the revised unit tests to a SQL database Run the unit tests on the database

Pushing unit tests to a SQL database via a Command Line task with Sql. Package

Running the unit tests against a SQL server during the build At a minimum, set these items: • Test assemblies • Settings file • Path to custom test adapters

Results from the test task – Success!

Code Coverage with SQL Cover Source: https: //github. com/Go. Eddie/SQLCover

SQLCover uses the SQL Server API to determine what is “coverable”

Why use code coverage? Source: https: //martinfowler. com/bliki/Test. Coverage. html

t. SQLt is found at: http: //tsqlt. org/ Blog articles based on this talk: http: //blog. bteksoftware. com/ Article from the t. SQLt creators: https: //www. red-gate. com/simple-talk/sql/t-sql-programming/sql-serverunit-testing-with-tsqlt/ Author of the Visual Studio t. SQLt Test Adapter and SQLCover: https: //the. agilesql. club/blogs/Ed-Elliott/ Red. Gate – introduction to SQL Cover: https: //www. red-gate. com/blog/sql-cover Microsoft's Wide World Importers sample database (and others): https: //github. com/Microsoft/sql-server-samples/releases/tag/wide-worldimporters-v 1. 0

Thank you Sponsors