1 / 30

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

randydixon
Download Presentation

Database Unit Testing Team Edition for Database Professionals

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

  2. 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

  3. 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 Collaborationwith Work Item and Process Integration with Team Foundation Server Automated Change Support 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 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 EitherCreatea new Database at the target location orUpdatean Existing Schema Incorporate the Database Professional into the software lifecycle and provide them with a foundation for change management and process integration. 3

  4. Visual Studio Team System New! Visual Studio Team Suite SoftwareArchitects SoftwareDevelopers DatabaseProfessionals SoftwareTesters VisualStudioTeamExplorer VisualStudioIndustryPartners Application Modeling Code Analysis Performance Testing Database Change Management Infrastructure and Deployment Modeling Performance Tuning Manual Testing Database Testing Test Case Management Database Build & Deployment Security Analysis Unit Testing Code Coverage MSF Process and Guidance 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

  5. Database Projects Overview Team Data 5

  6. 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 Data GenerationOverview 6

  7. Data GenerationDesign 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

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

  9. Data GenerationDistributions @ Work

  10. Data GenerationDesign Time • Understand domain constraints • Check constraints (min/max) • Table cardinality • Enforce table ratios • Ex) Orders \ OrderDetails

  11. Data GenerationExecuting 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

  12. Data GenerationCustomization & Extensibility • Customization of value generation • Regular Expression Generator • Data Bound Generator • Extensibility • Custom Generator • Custom Distribution

  13. Creating & Configuring a Generation Plan Data Generation 13

  14. Database Unit TestingOverview • 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.NET\C# • Builds on existing Team Test unit testing functionality • Integrate your database tests along side your application unit tests

  15. Database Unit TestingDesigner 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 • Pre\Post Test Scripts • SQL executed prior to and after test script to setup & cleanup test • Test Initialize\Cleanup Scripts • Common SQL executed prior to and after every test

  16. Database Unit TestingTest 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 ResultSet • Not Empty ResultSet • Row Count • Scalar Value • Execution Time • Inconclusive

  17. Database Unit TestingTest 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

  18. Database Unit TestingTest Execution • Ways to execute database tests • From Visual Studio • Test Menu \ Test View \ Test Manager \ Test Results • F5 Experience • Command-line • Mstest.exe • Team Build • Execute tests post Team Build

  19. Creating a database unit test Database Unit Testing 19

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

  21. Database Unit TestingData-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.NET\C# code

  22. Database Unit TestingCustom Verification Logic • Add custom verification logic in VB.NET\C# • Exposed ExecutionResult object • Exposes resultset data returned from test script • Exposes execution time & rows affected info • Take advantage of Classic Team Test Assertions

  23. Database Unit TestingTransaction 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.NET\C#

  24. Database Unit TestingNegative Testing • Expected error cases are as important to test as main success scenarios • 3 methods • SQL – Try\Catch • VB.NET\C# - Try\Catch • VB.NET\C# - Expected Exception Attribute

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

  26. Database Unit TestingIntegration 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

  27. Database Unit TestingWrap 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

  28. 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

  29. Resources Product Website & CTP7 Download http://msdn.microsoft.com/vstudio/teamsystem/products/dbpro/default.aspx Product MSDN Forums http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=725&SiteID=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. 30

More Related