1 / 28

UNIT TESTING for SQL

UNIT TESTING for SQL. Prepared for SUGSA CodeLabs Alain King Paul Johnson. Simply put, we want quality!. Why?. KRS is committed to Behaviour Driven Development (BDD), i.e. using stories and scenarios to drive development .

gratia
Télécharger la présentation

UNIT TESTING for SQL

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. UNIT TESTINGfor SQL Prepared for SUGSA CodeLabs Alain King Paul Johnson

  2. Unit Testing for SQL Simply put, we want quality! Why?

  3. Unit Testing for SQL KRS is committed to Behaviour Driven Development (BDD), i.e. using stories and scenarios to drive development. With most of the newer technologies, these scenarios turn into unit tests, either in MSTest, Nunit, Junit, and others How

  4. Unit Testing for SQL We also have big, complex legacy systems that have served us well for many years… …and these are mostly written using MS SQL Server stored procedures… …and yet we still demand quality, robust, elegant solutions… …But…

  5. Unit Testing for SQL Clients have invested in high-end servers to host the database so we want to harness that power for large processing tasks (like month end processes)… … and there are a number of functions that happen through SQL code in the BI environment (like the ETL process) … …And…

  6. Unit Testing for SQL How do you safely add new database functionality? How do you safely refactor existing stored procedures/functions? …without breaking the existing system… Now what?

  7. Unit Testing for SQL tSQLt is a database unit testing framework for Microsoft SQL Server. tSQLt is compatible with SQL Server 2005 (service pack 2 required) and above on all editions. tSQLt allows you to implement unit tests in T-SQL. This is important as you do not have to switch between various tools to create your code and your unit tests. Introducing tSQLt

  8. Unit Testing for SQL Tests are automatically run within transactions – this keeps tests independent and reduces any cleanup work you need Tests can be grouped together within a schema – allowing you to organize your tests and use common setup methods Output can be generated in plain text or XML – making it easier to integrate with a continuous integration tool Provides the ability to fake tables and views, and to create stored procedure spies – allowing you to isolate the code which you are testing tSQLt Features

  9. Unit Testing for SQL Lets look at some sample code Write a test for a new stored proc Examples

  10. Unit Testing for SQL begintran select*fromPerson.PersonwhereBusinessEntityID= 2 BusinessEntityIDPersonTypeNameStyleTitleFirstNameMiddleNameLastName 2 EM 0 NULLTerriLeeDuffy declare@BusinessEntityIDint, @MiddleNamevarchar(50) select@BusinessEntityID= 2, @MiddleName='Leigh' updatePerson.Person setMiddleName=@MiddleName whereBusinessEntityID=@BusinessEntityID select*fromPerson.PersonwhereBusinessEntityID= 2 BusinessEntityIDPersonTypeNameStyleTitleFirstNameMiddleNameLastName 2 EM 0 NULLTerriLeighDuffy rollbacktransaction select*fromPerson.PersonwhereBusinessEntityID= 2 BusinessEntityIDPersonTypeNameStyleTitleFirstNameMiddleNameLastName 2 EM 0 NULLTerriLeeDuffy Examples

  11. Unit Testing for SQL select*fromPerson.PersonwhereBusinessEntityID= 2 BusinessEntityIDPersonTypeNameStyleTitleFirstNameMiddleNameLastName 2 EM 0 NULLTerriLeeDuffy begintran declare@BusinessEntityIDint, @MiddleNamevarchar(50) select@BusinessEntityID= 2,@MiddleName='Leigh' execPerson.uspUpdatePersonInfo@BusinessEntityID=@BusinessEntityID, @MiddleName=@MiddleName select*fromPerson.PersonwhereBusinessEntityID= 2 BusinessEntityIDPersonTypeNameStyleTitleFirstNameMiddleNameLastName 2 EM 0 NULLTerriLeighDuffy rollbacktransaction -- check that data is back to original select*fromPerson.PersonwhereBusinessEntityID= 2 BusinessEntityIDPersonTypeNameStyleTitleFirstNameMiddleNameLastName 2 EM 0 NULLTerriLeeDuffy Examples

  12. Unit Testing for SQL exectSQLt.NewTestClass'TestPerson' go dropprocTestPerson.[test Person middle name updated correctly] go createprocTestPerson.[test Person middle name updated correctly] as begin declare@BusinessEntityIDint, @MiddleNamevarchar(50) select@BusinessEntityID= 2,@MiddleName='Leigh' -- When I update the MiddletName declare@retvalint exec@retval=Person.uspUpdatePersonInfo@BusinessEntityID=@BusinessEntityID,@MiddleName=@MiddleName declare@ActualMiddleNamevarchar(50) select@ActualMiddleName=middlename fromPerson.Person whereBusinessEntityID=@BusinessEntityID exectSQLt.AssertEqualsString@MiddleName,'broken' end exectSQLt.RunAll Examples

  13. Unit Testing for SQL exectSQLt.RunAll [TestPerson].[test Person middle name updated correctly] failed: Expected: <Leigh> but was: <broken> +----------------------+ |Test Execution Summary| +----------------------+ |No|Test Case Name |Result | +--+--------------------------------------------------------+-------+ |1 |[TestPerson].[test Person middle name updated correctly]|Failure| ----------------------------------------------------------------------------- Msg 50000, Level 16, State 10, Line 1 Test Case Summary: 1 test case(s) executed, 0 succeeded, 1 failed, 0 errored. ----------------------------------------------------------------------------- Examples

  14. Unit Testing for SQL Write a test for a new stored proc So what did I do wrong? Test First Examples

  15. Unit Testing for SQL Scenario Given Person with ID 2 and Middle Name of Lee When I update the Middle Name to be Leigh Then the Middle Name for Person with ID 2 should be Leigh Examples

  16. Unit Testing for SQL Write a test for a new stored proc Lets have a look at some code Examples

  17. Unit Testing for SQL Write a test to compare 2 result sets Upgrading legacy systems to be 2008 / 2012 compliant Replacing complex queries with CTE’s to be easier to maintain In both cases, you are making changes to code and the expect result should be same Examples

  18. Unit Testing for SQL Write a test to compare 2 result sets We can check this by writing a test to output the results from the original code into one table and the results of the modified code into another table and comparing them. But we don’t just want to compare for one set of inputs Examples

  19. Unit Testing for SQL Write a test to compare 2 result sets JUnit and NUnit have TestCase functionality that allows one to run the same test with just supplying inputs and expected results as parameters. Examples

  20. Unit Testing for SQL EXECtSQLt.NewTestClass'test_BillingReports'; GO /* ----------------------------------------------------------------------------- */ createproceduretest_BillingReports.[test periodbilling (@periodno 201101).] as begin exectest_BillingReports.periodbilling_sub201101 end; /* ----------------------------------------------------------------------------- */ createproceduretest_BillingReports.[test periodbilling (@periodno 201201).] as begin exectest_BillingReports.periodbilling_sub201201 end; /* ----------------------------------------------------------------------------- */ createproceduretest_BillingReports.[test periodbilling (@periodno 201202).] as begin exectest_BillingReports.periodbilling_sub201202 end; Examples

  21. Unit Testing for SQL createproceduretest_BillingReports.periodbilling_sub(@periodnoint) as begin ifobject_id('actual')isnotnull droptableactual; ifobject_id('expected')isnotnull droptableexpected; ------Assertion createtableactual (columns) createtableexpected (columns) --OtherDatabase has the original version of the sproc insertexpected(columns) execOtherDatabase..periodbilling@periodno=@periodno insertactual(columns) execperiodbilling@periodno=@periodno exectSQLt.assertEqualsTable'expected','actual'; end; go EXECtSQLt.run'test_BillingReports'; Examples

  22. Unit Testing for SQL Write a test to compare 2 result sets Lets take a look at some more code Examples

  23. Unit Testing for SQL Write a test for an existing stored procedure This can be used for DDT – Defect Driven Testing or for enhancing existing functionality. Lets look at a test for an existing procedure in AdventureWorks called uspGetEmployeeManagers Examples

  24. Unit Testing for SQL Download from tsqlt.org exectSQLt.NewTestClass'TestPerson' Go createprocTestPerson.[test UpdatePersonMiddleName] as exectSQLt.AssertEqualsString@MiddleName,@ActualMiddleName end exectSQLt.RunAll Exercises

  25. Unit Testing for SQL Write a test for a new stored procedure to check if an email address already exists in the database Exercises

  26. Unit Testing for SQL Write a test for a new stored procedure to update available leave hours for an employee Exercises

  27. Unit Testing for SQL We want to make changes to an existing stored procedure, write a test for uspGetBillOfMaterials to ensure that any changes you make do not have a negative impact on existing functionality. Exercises

  28. Unit Testing for SQL Questions? www.krs.co.za ( (021) 681 2900 Alain King: alain.king@krs.co.za Paul Johnson: paul.johnson@krs.co.za

More Related