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   1
                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   2
Database Testing in an RDBMS




          A testing framework for Microsoft SQL-Server, Jorge Almeida, Dezembro 2006   3
               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   4
    Database Manual Testing
• Combination of PRINT statements and ad-
  hoc 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   5
  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   6
       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   7
      Some database testing tools
Category             Description                                                       Examples

Unit testing tools   Tools which enable regression test                                •DBUnit
                                                                                       •NDbUnit
                                                                                       •OUnit for Oracle (being
                                                                                       replaced soon by Qute)
                                                                                       •SQLUnit
                                                                                       •TSQLUnit
                                                                                       •Visual Studio Team Edition
                                                                                       for Database Professionals
Testing tools for    Tools simulate high usage loads on database,                      •Empirix
load testing         enabling to determine whether the system‟s                        •Mercury Interactive
                     architecture will stand up to true production                     •RadView
                     needs
                                                                                       •Rational Suite Test Studio
                                                                                       •Web Performance
Test data            Developers need test data against which to                        •Data Factory
generator            validate their systems. Test data generators can                  •Datatect
                     be particularly useful when need large amounts                    •DTM Data Generator
                     of data, for stress and load testing
                                                                                       •Turbo Data


                                    A testing framework for Microsoft SQL-Server, Jorge Almeida, Dezembro 2006       8
                  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   9
             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   10
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   11
  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   12
                      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   13
                      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   14
                      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   15
Test Results




 A testing framework for Microsoft SQL-Server, Jorge Almeida, Dezembro 2006   16
           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   17
              Conclusions
• Good unit testing tool for Microsoft SQL-
  Server 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   18
                    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   19
Questions ?




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