Downloaded 38 times
Uploaded bySanil Mhatre
Database Unit Testing Made Easy with VSTS
This document discusses database unit testing using Visual Studio Team System (VSTS). It begins with an overview of software testing basics and unit testing principles. It then covers database unit testing terminology, principles of isolation and independence, and testable interfaces of stored procedures. The document outlines different levels of unit testing and factors to consider. It demonstrates implementing database unit testing in VSTS 2010 and new features in VSTS 2012. The goal is to show how VSTS can be used to test database code and improve quality.
More Related Content
Similar to Database Unit Testing Made Easy with VSTS
![UiPath Automation Suite Installation (Hands-On) [2/3]](https://cdn.slidesharecdn.com/ss_thumbnails/automationsuitecommunitysession2-251015095633-a6d862f1-thumbnail.jpg?width=600ounds&width=560&fit=bounds)
Database Unit Testing Made Easy with VSTS
- 1.
- 2. DATABASE UNIT TESTING MADEEASY WITH VISUAL STUDIO Sanil Mhatre Database Developer, Scottrade
- 3. About Me Database Developer –Scottrade (@scottradejobs) MCTS – MS SQL Server 2008 Database Development http://SQLWithSanil.com @SQLSuperGuru MSDN Forums & SQLServerCentral.com SQL Saturday #154 | St.Louis | Sept 15
- 4. Pre-requisites MS Visual Studio Team system framework & tools MS Visual Studio Team System (VSTS) 2010 & 2012 Ultimate & Premium Editions – create, modify & run database unit tests Professional Edition – run database unit tests but cannot create or modify them MS Visual Studio Team System 2008 Database
- 5. Agenda Software Testing – Basics Unit Testing – Basics Database Unit Testing – Principles, Terminology & Levels Demo – Implement Database Unit Testing with VSTS 2010 & MS SQL Server 2008 R2 New features in VSTS 2012
- 6. Software Testing Meet Requirements & Expectations Deterministic & Predictable outcome
- 7. Types of SoftwareTesting Criteria Black Box Testing White Box Testing Method in which the Method in which the internal design/ internal design/ implementation of the Definition implementation of the item being tested is item being tested is NOT known to the known to the tester. tester Mainly applicable to Mainly applicable to higher levels of lower levels of Levels Applicable To testing:Acceptance testing:Unit Testing Testing Integration Testing System Testing Generally, independent Generally, Software Responsibility Software Testers Developers Programming Not Required Required Knowledge
- 8. Levels of SoftwareTesting Unit Testing Integration Testing System Testing System Integration Testing
- 9. What is UnitTesting ? Validation & Verification Methodology Belongs to the White Box testing category Test individual units of Source Code Unit – smallest testable part of application Method, Function, Class Tests created before or during development Tests conducted during development
- 10. Goals of DatabaseUnit Testing Ensure quality & accuracy of database deliverables Establish a baseline state of your database Verify Subsequent changes made to database objects Identify regression issues throughout the application lifecycle
- 11. Database Unit Testing Terminology • Isolated Environment (database) • Local development, testing & SandBox validation • Created by Script, VSTS Project or Restoring backup • Exercises a targeted code path TestCase • Multiple assertions
- 12. Database Unit Testing Terminology • Simple Condition to indicate success or failure • Returns a True or False • Verifies Expected Assertion Interface • Verifies extraneous Results • Verifies data modification behavior
- 13. Database Unit Testing Terminology • Collection of all test cases Unit for a given stored procedure • Tests interface, code & Test behavior • TSQL Scripts that executes a test case Test • May include assertions like; • Return values Script • Output parameters • Expected Errors
- 14. Database Unit Testing Terminology • Logical & named grouping of related unit tests • Multiple test lists can be Test created • Can be run as a unit List • A test suite • Builds & Check in Policy • Deprecated in VSTS 2012
- 15. Database Unit Testing Terminology • TSQL Scripts • Prepare database for unit Setup testing & • Returns database back to pre test state after testing Cleanu • Framework also provides p common Initialize & Cleanup Scripts Scripts • Pre Test and Post Test Scripts • Data Generation Plans
- 16. Database Unit Testing Principles Isolation • Outcome of one test must not affect the outcome of any other test(s) • Each test must return the database back to its previous state after execution Independence • One test case must not depend on any other test case • Each test case must be run independently of any other test case • Test case assertion must be independent of the order of it’s execution
- 17. Database Unit Testing Principles Characteristics of Ideal Unit Test • Exercise all possible code paths • Evaluate all possible conditions • Validate correct behavior for each path/condition Considerable efforts for exhaustive testing • Non Trivial stored procedures • Number of input parameters • Permutations of optional parameters • Error code paths
- 18. Testable Interfaces ofa Stored Procedure Input & • Name Output • Data Type (Length) Parameters • Optionality Return Code • Success/failure Row Count • SET NOCOUNT ON/OFF
- 19. Testable Interfaces ofa Stored Procedure • One or more SELECT statements • Number of Columns Result Set • Column Ordinal position • Column Name & Data Type Informational • PRINT Messages Messages • RaiseError – Severity 10 & below Error • RaiseError – Severity 10 & above Messages
- 20. Unit Test Conditions PredefinedTest conditions in VSTS 2010 Data Checksum Empty ResultSet/Non Empty Result Set Execution Time Expected Schema Row Count Scalar Value Inconclusive
- 21. Levels of UnitTesting Factors Affecting the choice of Appropriate Level of Unit testing Costs of Unit test development & maintenance Added Value of Quality Application Criticality & Frequency of use Project Deadlines
- 22. Levels of UnitTesting • Smallest amount of testing • Ensures error free compiles • No code is exercised • Consists of single test case Minimal with SET FMTONLY ON • Executes with NULL inputs • Intrinsic Assertion – No Errors
- 23. Levels of UnitTesting • Exercises commonly used code paths & conditions • One step above Minimal • Tests Empty/Non-Empty Margina Result sets l • Some assertion of typical behavior & expected interface • Not a thorough & complete test
- 24. Levels of UnitTesting • Exercises all code paths & conditions • Test cases for all permutations of optional parameters Complet • Multiple Assertions to completely verify interface & behavior e • Could also verify column names of result set • It’s an exhaustive unit test to verify all but unexpected errors
- 25. Levels of UnitTesting Possible to develop and un-testable stored procedure ? A search stored procedure with 8 optional parameters will need over 2000 test cases Numerous found/not found conditions Error validation Possibly an indicator of flawed design Consider refactoring to facilitate testability
- 26. Demo Prerequisite : Database Project Exists in VSTS Create a Unit Test Project in VSTS Configure SandBox Create Tests Run Tests
- 27. New in VSTS2012 Redesigned Unit Test Explorer Support for 3rd party testing frameworks Code Analysis improvements Continuous test runner Deprecated Features Test lists Generate Unit Test Wizards
- 28. Conclusion Software Testing - Basics Unit Testing – Basics Database Unit Testing – Detailed discussion on Principles, Goals & Levels Demo – Implementation of Database Unit Testing with VSTS 2010 & SQL Server 2008 R2
- 29. Resources MSDN http://msdn.microsoft.com/en- us/library/aa833283.aspx http://msdn.microsoft.com/en- us/library/aa833423.aspx Wikipedia http://en.wikipedia.org/wiki/Software_testing Image - http://i.swebee.com/content/error-404-
- 30. THANK YOU ! Questions& Feedback
Editor's Notes
- #4 http://msdn.microsoft.com/en-us/library/dd172118.aspxhttp://www.microsoft.com/download/en/details.aspx?displaylang=en&id=14258
- #5 http://msdn.microsoft.com/en-us/library/dd172118.aspxhttp://www.microsoft.com/download/en/details.aspx?displaylang=en&id=14258
- #7 Goals of Software Testing ;Assure the Quality of a Software DeliverableEnsure the software meets requirement specsEnsure the software meets user expectations as well as stakeholder expectationsEnsure that the behaviour of the system is predictable and deterministichttp://en.wikipedia.org/wiki/Software_testingImage - http://i.swebee.com/content/error-404-road.jpg
- #8 White-box testing is when the tester has access to the internal data structures and algorithms including the code that implements these.Black-box testing treats the software as a "black box"—without any knowledge of internal implementation. The black-box tester has no "bonds" with the code, and a tester's perception is very simple: a code must have bugs. Using the principle, "Ask and you shall receive," black-box testers find bugs where programmers do not. On the other hand, black-box testing has been said to be "like a walk in a dark labyrinth without a flashlight," because the tester doesn't know how the software being tested was actually constructed. As a result, there are situations when (1) a tester writes many test cases to check something that could have been tested by only one test case, and/or (2) some parts of the back-end are not tested at all.Grey-box testinginvolves having knowledge of internal data structures and algorithms for purposes of designing tests, while executing those tests at the user, or black-box level. By knowing the underlying concepts of how the software works, the tester makes better-informed testing choices while testing the software from outside. Grey-box testing implements intelligent test scenarios, based on limited information. http://en.wikipedia.org/wiki/Software_testinghttp://softwaretestingfundamentals.com/differences-between-black-box-testing-and-white-box-testing/
- #9 Unit testing - also known as component testing, refers to tests that verify the functionality of a specific section of code, usually at the function levelIntegration testing - Integration testing is any type of software testing that seeks to verify the interfaces between components against a software designSystem testing - tests a completely integrated system to verify that it meets its requirementsSystem integration testing - verifies that a system is integrated to any external or third-party systems defined in the system requirements
- #12 SandboxA database sandbox is a private environment used for local unit testing and development. The sandbox allows development and testing to be done in isolation from other development activity and ensures repeatable results. Database developers use sandboxes to perform unit testing and validate rollout script behavior. Application developers may use local database sandboxes to test application code without affecting, or being affected by, other development activity. A database sandbox may be created from script, by deploying a VSTSDE database project locally, or by restoring a database backup at a known state.Test CaseA test cast exercises a targeted code path. Success or failure is ascertained after test case execution by asserting that the expected outcome was realized. Multiple assertions are commonly needed to validate the desired outcome of a specific test case.
- #13 AssertionAn assertion is a simple condition that returns "true" to indicate test success or "false" to indicate failure of a test case. A test case assertion verifies:expected interface output values (e.g. output parameter values, number of resultsets, number of rows, resultset values, etc.)no extraneous results (e.g. errors, extra resultsets, unexpected informational messages, etc.)data modification behavior (e.g. data correctly inserted/updated/deleted)
- #14 Unit TestA unit test is the collection of all of the test cases that exercise a stored procedure interface and code along with the assertions need to verify correct behavior.Test ScriptA test script is a T-SQL script that executes a test case. A test script may also include assertions implemented in T-SQL, such as checking return code values, output parameter values or a TRY/CATCH to assert an expected RAISERROR of a user-defined error occurred.
- #15 Multiple related unit tests may be grouped together in a test list within VSTSDE so all can be run as a unit and for logical organizational purposes (a.k.a. test suite). http://msdn.microsoft.com/en-us/library/ms182461.aspxhttp://msdn.microsoft.com/en-us/library/ms182463.aspx
- #16 Setup and Cleanup ScriptsSetup and cleanup scripts (a.k.a. fixture and teardown scripts) are T-SQL scripts used to prepare the database for unit testing and return the database back to the initial state afterward. Setup scripts typically insert data needed by a test case while cleanup scripts remove testing artifacts after test case execution. The VSTSDE unit test framework provides common Test Initialize and Test Cleanup scripts that are run before/after each test case within a unit test. This allows the same scripts to be shared by all test cases within a unit test. VSTSDE also includes the ability to run Pre-Test and Post-Test scripts before/after an individual test case. Pre/Post scripts are especially appropriate for data tailored for a specific test case. VSTSDE Data Generation Plans provide an alternative to unit test setup scripts. However, data values must be customized so that test cases can be developed against known data values. Care must be used to ensure DGP changes do not adversely affect existing unit tests and that cleanup scripts restore modified data back to the original state after test case execution.
- #28 Unit Test explorer replaces the old test view and test results windowUsing simple plugin adapters, third party test frameworks can plug into the test platform layer and get the full experience of running inside of Visual Studio