Docstoc

A testing framework for Microsoft SQL-Server

Document Sample
A testing framework for Microsoft SQL-Server Powered By Docstoc
					A testing framework for Microsoft SQL-Server
Jorge Almeida

A testing framework for Microsoft SQL-Server, Jorge Almeida, Dezembro 2006

‹#›

Summary
• Database Testing in an RDBMS Unit test framework for databases • Database manual testing • Database automatized tests • Database testing tools • TSQLUnit framework • How to write T-SQL unit tests • Conclusions • References

A testing framework for Microsoft SQL-Server, Jorge Almeida, Dezembro 2006

‹#›

Database Testing in an RDBMS

A testing framework for Microsoft SQL-Server, Jorge Almeida, Dezembro 2006

‹#›

Database Testing
• Categories of database tests
– Interface tests (validate what is going into, out of, and mapped) – Internal database tests (validate the internal structure, behavior and contents)

• Internal database tests should validate:
– Database methods (e.g. stored procedures, functions, and triggers) – Existence of database schema elements (tables, procedures, ...) – View definitions – Referential integrity rules – Default values for a column – Data invariants for a single column – Data invariants involving several columns

A testing framework for Microsoft SQL-Server, Jorge Almeida, Dezembro 2006

‹#›

Database Manual Testing
• Combination of PRINT statements and adhoc tests with the SQL Query Analyzer • Trace/debug tools like SQL Profiler • Human judgment is required to analyze the results • The tests can not easily be repeated again

A testing framework for Microsoft SQL-Server, Jorge Almeida, Dezembro 2006

‹#›

Database Automatized Tests
• Can be repeated over and over again • Improve the quality of the code (refactoring) • Serve the purpose of documentation • May improve the design

A testing framework for Microsoft SQL-Server, Jorge Almeida, Dezembro 2006

‹#›

Database testing tools
• Category of database testing (interface and internal) • Support the language of developing • Help putting the database into a known state

A testing framework for Microsoft SQL-Server, Jorge Almeida, Dezembro 2006

‹#›

Some database testing tools
Category Unit testing tools Description Tools which enable regression test Examples •DBUnit •NDbUnit •OUnit for Oracle (being replaced soon by Qute) •SQLUnit •TSQLUnit •Visual Studio Team Edition for Database Professionals •Empirix •Mercury Interactive •RadView •Rational Suite Test Studio •Web Performance •Data Factory •Datatect •DTM Data Generator •Turbo Data

Testing tools for load testing

Tools simulate high usage loads on database, enabling to determine whether the system‟s architecture will stand up to true production needs

Test data generator

Developers need test data against which to validate their systems. Test data generators can be particularly useful when need large amounts of data, for stress and load testing

A testing framework for Microsoft SQL-Server, Jorge Almeida, Dezembro 2006

‹#›

TSQLUnit
• • • • • A testing framework for Microsoft SQL-Server It follows the tradition of the "xUnit" framework TSQLUnit is open source Developed by Henrik Ekelund Unit testing for stored procedures, functions, triggers and views • Available from http://sourceforge.net/projects/tsqlunit • Version 0.9 (December 15, 2002)
A testing framework for Microsoft SQL-Server, Jorge Almeida, Dezembro 2006

‹#›

TSQLUnit Install
• Download file from
http://tsqlunit.sourceforge.net/tsqlunit_download.htm

• Unzip the file • Connect to database as dbo using the SQL Query Analyzer, execute the tsqlunit.sql file

A testing framework for Microsoft SQL-Server, Jorge Almeida, Dezembro 2006

‹#›

Basic principles of database testing
• Database with good test data • Shouldn‟t developing against a production database • Test one feature only per test procedure • Be careful of how T-SQL comparison operators work • When it is difficult to write tests change the design or refactor the system
A testing framework for Microsoft SQL-Server, Jorge Almeida, Dezembro 2006

‹#›

How to write T-SQL unit tests
• Create a stored procedure with a name that starts with ut and underscore (e.g. ut_testSomething) • Code a test, then call tsu_failure if test fails • Execute tsu_runTests

A testing framework for Microsoft SQL-Server, Jorge Almeida, Dezembro 2006

‹#›

Test example (1)
CREATE PROCEDURE capitalize @inStr VARCHAR(500)=NULL, @outStr VARCHAR(500)=NULL OUTPUT AS BEGIN SET @outStr=NULL IF @inStr IS NOT NULL SET @outStr=UPPER(LEFT(@inStr,1))+RIGHT(@inStr,LEN(@inStr)-1) END

CREATE PROCEDURE ut_CapitalizeOneSentence AS BEGIN DECLARE @outStr VARCHAR(500) EXECUTE capitalize „a string‟, @outStr OUTPUT IF ASCII(LEFT(@outStr,1)) <> ASCII(„A‟) OR @outStr IS NULL EXECUTE tsu_failure „Capitalize should make the first character uppercase‟ END

A testing framework for Microsoft SQL-Server, Jorge Almeida, Dezembro 2006

‹#›

Test example (2)
CREATE PROCEDURE capitalize @inStr VARCHAR(500)=NULL, @outStr VARCHAR(500)=NULL OUTPUT AS BEGIN SET @outStr=NULL IF @inStr IS NOT NULL SET @outStr=UPPER(LEFT(@inStr,1))+RIGHT(@inStr,LEN(@inStr)-1) END

CREATE PROCEDURE ut_CapitalizeNullSentence AS BEGIN DECLARE @outStr VARCHAR(500) EXECUTE capitalize NULL, @outStr OUTPUT IF @outStr IS NOT NULL EXECUTE tsu_failure „Capitalize should not return any value‟ END

A testing framework for Microsoft SQL-Server, Jorge Almeida, Dezembro 2006

‹#›

Test example (3)
CREATE PROCEDURE capitalize @inStr VARCHAR(500)=NULL, @outStr VARCHAR(500)=NULL OUTPUT AS BEGIN SET @outStr=NULL IF @inStr IS NOT NULL SET @outStr=UPPER(LEFT(@inStr,1))+RIGHT(@inStr,LEN(@inStr)-1) END

CREATE PROCEDURE ut_CapitalizeEmptySentence AS BEGIN DECLARE @outStr VARCHAR(500) EXECUTE capitalize ‘’, @outStr OUTPUT IF @outStr<>‟‟ EXECUTE tsu_failure „Capitalize should return the same value‟ END

A testing framework for Microsoft SQL-Server, Jorge Almeida, Dezembro 2006

‹#›

Test Results

A testing framework for Microsoft SQL-Server, Jorge Almeida, Dezembro 2006

‹#›

Improving tests
• Organize tests in classes (suites) (e.g. rename ut_CapitalizeEmptySentence as ut_Capitalize_EmptySentence then run the store procedure tsu_runTest ‘Capitalize’) • Create a unique procedure for test setup (e.g. ut_Capitalize_SetUp) • Create a unique procedure for test “teardown” (e.g. ut_Capitalize_TearDown)
A testing framework for Microsoft SQL-Server, Jorge Almeida, Dezembro 2006

‹#›

Conclusions
• Good unit testing tool for Microsoft SQLServer stored procedures • Regression test suite • Some improvements needed (e.g. execution time of a single test) • Be careful with error treatment in test store procedures

A testing framework for Microsoft SQL-Server, Jorge Almeida, Dezembro 2006

‹#›

References
• • • • • http://sourceforge.net/projects/tsqlunit http://www.tassq.org http://www.agile.org/essays/databaseTesting.html http://www.ambysoft.com/books/refactoringDatabases.html http://www.ambysoft.com/books/agileDatabaseTechniques.html

A testing framework for Microsoft SQL-Server, Jorge Almeida, Dezembro 2006

‹#›

Questions ?

A testing framework for Microsoft SQL-Server, Jorge Almeida, Dezembro 2006

‹#›


				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:153
posted:11/19/2009
language:English
pages:20