Database Unit Testing Team Edition for Database Professionals

Database Unit Testing Team Edition for Database Professionals Sachin Rekhi Program Manager Microsoft Corporation http: //blogs. msdn. com/sachinre

Agenda • Team Data Overview • Data Generation • Database Unit Testing ● ● ● Overview Designer Experience Test Validation Test Execution Exploring the Generated Code Data-driven Testing Custom Verification Logic Transaction Support Negative Testing Custom Test Conditions Integration with Application Unit Tests

Incorporate the Database Professional into the software lifecycle and provide them with a foundation for change management and process integration. • Change Management ● Project Based Development • • ● Project Model that represents schema as objects providing a “personal sandbox” for offline development that lives within a Visual Studio Solution Team Collaboration with Work Item and Process Integration with Team Foundation Server Automated Change Support • • • ● Database Unit Testing • • • Rename Refactoring with the ability to preview pending changes prior to execution Comparison Tools (Schema & Data Compare) allow comparisons & synchronization of schema and data with design/test/production databases Version Control of all database objects with the ability to reverse engineer a database to bring it under Source Control Database Unit Testing ensures iterative changes don’t break existing functionality Data Generator provides Repetitive Dataset Generation for tests based on saved settings Build / Deployment ● ● MSBuild Integration for Database Deployments/Builds based on Projects Either Create a new Database at the target location or Update an Existing Schema 3

Visual Studio Team System Visual Studio Team Suite Software Architects Software Developers Software Testers Database Professionals Application Modeling Code Analysis Performance Testing Database Change Management Infrastructure and Deployment Modeling Visual Studio Team Explorer MSF Process and Guidance New! Performance Tuning Manual Testing Database Testing Test Case Management Database Build & Deployment Security Analysis Visual Studio Industry Partners Unit Testing Code Coverage Class Modeling Visio and UML Modeling Visual Studio Professional Edition Load Test Agent Visual Studio Team Foundation Server Change Management Reporting Integration Services Work Item Tracking Project Site Project Management 4

Team Database Projects Overview 5

Data Generation Overview To create a solid foundation for testing we support data generation Deterministic – always generate the same layout Matched to your schema and very customizable Easily update generation plan to reflect schema changes Bottom Line: Realistic values with representative distributions 6

Data Generation Design Time • Setting up Data Generation implies defining: ● ● ● Which generator to use Which distribution to attach to the generator Changing setting on the generator & distribution The numbers of rows to generate Optionally defining ratios between related tables • By default: ● Each column is bound to the generator matching the column data type • FK columns are mapped to the Foreign Key generator • Uniqueness is inferred from PK, UC constraints and indexes • Using the Uniform distribution when not unique

Data Generation Design Time Value generators • Simple generators for each data type ● ● ● Strings: ASCII and Unicode ((var)char, n(var)char, (n)text) Numbers: tinyint, smallint, bigint, real, float, decimal, numeric, money Binary ((var)binary, image) Date and Time Unique. Identifier (GUID) Bit • Complex generators ● Foreign Key, Regular Expression, Data Bound • Distributions ● Uniform, Normal, Inverse Normal, Exponential, Inverse Exponential

Data Generation Distributions @ Work

Data Generation Design Time • Understand domain constraints ● Check constraints (min/max) • Table cardinality ● ● Enforce table ratios Ex) Orders Order. Details

Data Generation Executing a Data Generation Plan • Validation of ● Security requirements • Fails when security requirements are not met! ● Target schema against DGEN definitions • Fails the generation when bindings do not match! • Optionally purge tables ● Required to guarantee repeatable data generation • Spin up parallel streams of INSERT statements ● ● Based on relationships between tables Number of connections used is currently gated by the schema relationships • Configurable Error Thresholds

Data Generation Customization & Extensibility • Customization of value generation ● ● Regular Expression Generator Data Bound Generator • Extensibility ● ● Custom Generator Custom Distribution

Data Generation Creating & Configuring a Generation Plan 13

Database Unit Testing Overview • Ensures that database changes do not break existing code • Generate automated tests for programmability objects • Develop a battery of tests to run with every database build • Design & customize your tests in the language of your choice ● T-SQL, VB. NETC# • Builds on existing Team Test unit testing functionality • Integrate your database tests along side your application unit tests

Database Unit Testing Designer Experience • Develop your database unit test through an easy to use database test designer • Test Script Generation ● Automatically generate test script stub for • Stored Procedures, Functions, Triggers • Test Script ● SQL to exercise the object under test • PrePost Test Scripts ● SQL executed prior to and after test script to setup & cleanup test • Test InitializeCleanup Scripts ● Common SQL executed prior to and after every test

Database Unit Testing Test Validation • Two primary methods of validating database unit tests ● T-SQL Assertions • RAISERROR ● Test Conditions • Easily configured UI-based client side assertions • Supported types – – – Empty Result. Set Not Empty Result. Set Row Count Scalar Value Execution Time Inconclusive

Database Unit Testing Test Execution • Automatic Deployment Integration ● Automatically deploy database project prior to running tests • Data Generation Integration ● Automatically generate data based on generation plan prior to running tests • Execution & Validation connection strings ● Validation connection can be higher privileged account

Database Unit Testing Test Execution • Ways to execute database tests ● From Visual Studio • Test Menu Test View Test Manager Test Results • F 5 Experience ● Command-line • Mstest. exe ● Team Build • Execute tests post Team Build

Database Unit Testing Creating a database unit test 19

Database Unit Testing Exploring the Generated Code • Database unit test designer round-trips generated VB. NETC# code • ADO. NET code to ● ● Execute SQL against target Validate test conditions • Exposed code enables variety of customization scenarios

Database Unit Testing Data-driven Testing • Have the ability to parameterize inputs in test script • Feed inputs from a database table • Easily test a variety of inputs in a single test case • Requires customization of VB. NETC# code

Database Unit Testing Custom Verification Logic • Add custom verification logic in VB. NETC# • Exposed Execution. Result object ● ● Exposes resultset data returned from test script Exposes execution time & rows affected info • Take advantage of Classic Team Test Assertions

Database Unit Testing Transaction Support • Data Generation ● Used to ensure consistent database state prior to test run • Transaction Support ● ● Ensures consistent state during test run Two Methods • Transactions in T-SQL • System. Transactions in VB. NETC#

Database Unit Testing Negative Testing • Expected error cases are as important to test as main success scenarios • 3 methods ● ● ● SQL – TryCatch VB. NETC# - Expected Exception Attribute

Database Unit Testing Custom Test Conditions • Create re-usable advanced verification logic • Fully supports test condition extensibility • Author a new test condition that derives from Test. Condition base class in any. NET language • Complete integrated UI experience

Database Unit Testing Integration with Application Tests • Create VS solution containing both app & db projects • Support for heterogeneous test lists • Run application & database tests side-by-side • Ensure database changes don’t break data access through app tests

Database Unit Testing Wrap Up • Database Unit Testing ensures quality in the data tier • Data Generation to support repeatable data generation for testing • First class unit testing framework for database developers • Integrate your database tests along side your application tests

Pricing, Licensing and Availability • Included in Team Suite at No Extra Cost • Purchase as an individual Edition ● Same pricing as other Team System Editions • http: //msdn. microsoft. com/vstudio/howtobuy/default. aspx • Availability ● ● CTP 7 Available Today RTM by the end of 2006 28

Resources • Product Website & CTP 7 Download ● http: //msdn. microsoft. com/vstudio/teamsystem/products/dbpro/de fault. aspx • Product MSDN Forums ● http: //forums. microsoft. com/MSDN/Show. Forum. aspx? Forum. ID=7 25&Site. ID=1 • Blogs ● http: //blogs. msdn. com/sachinre • Wealth of blog posts, videos, and web casts on database unit testing tips, tricks, and best practices ● ● http: //blogs. msdn. com/camerons http: //blogs. msdn. com/gertd 29

30