One of the more enjoyable aspects of programming, right up there with commenting your code, is testing your code. Sarcasm aside, it is important to do both. While this should not be a revelation for anyone, testing has taken a back seat for many organizations due to feasibility and manageability. But as more companies adopt Agile development methodologies, more and more application code is being unit tested. Unit testing is a great way to find problems early and thus cut down on time spent debugging. But unit testing DB code isn't always that easy or prevalent since not as many tools are available for working with DB code. But there is one tool that does make testing very easy for database professionals to test database code: DbFit.

DbFit is an add-on to the FitNesse testing framework that is used for integration testing of application code (methods inside of DLLs), not unit testing like NUnit. DbFit (and FitNesse) are designed to provide an easy test mechanism that can be up and running in moments. FitNesse is a simple wiki that allows for a wiki page to be a test. It even allows for groups of wiki pages to be a test suite so that related code can be tested together. And DbFit does not add any code or structure to your database; it is completely independent.

One of the difficulties in testing DB code is taking into account that unlike unit testing application code, databases by their very nature retain state so that both functionality (i.e. the algorithm) and the state of the data are tested together whereas unit testing application code merely tests the functionality (since it doesn't test any external dependencies). To account for this, the default operation of DbFit is to wrap all tests (each wiki page that is a test) into its own transaction. In doing this, when each test is finished the state of the database is returned to what it was before the test started. And if more than one Stored Procedure needs to be tested within the scope of a single transaction, then multiple operations can be performed on each wiki page / test. There is a way to retain the state of DML operations by controlling the transaction BEGIN, COMMIT, and ROLLBACK, but I will focus on the majority / default case of having DbFit manage each test as its own transaction (flow mode).

THE BASICS

So let's go over the basics. DbFit has a variety of features that allow you to customize your testing:

• Short-cut for simple INSERT sourced by a wiki table for multiple INSERTs
• Short-cut for simple UPDATE
• Execute a custom query (SELECT or EXEC of a Proc) and test its output of 1 or more records against a wiki table of values - OUTPUT parameters are not testable here
• Execute a Stored Procedure any number of times against a wiki table of 1 or more sets of input parameters OUTPUT parameters can also be tested as part of this feature in which case you can validate the OUTPUT parameter(s) and even store that value for later use, but Result Sets are not testable here
• Execute any custom SQL in which no inputs are passed in or results tested - this can be used to set up the scenario for testing
• Variables can be declared that carry their value down to sub-pages
• Wiki pages can be included in any number of other pages - this helps avoid copying and pasting common setup tasks
• Test Suites can be set up to test and display the results of a set of sub-pages and even sub-Suites. If running a parent Suite, it can run all Tests within all SubSuites (ergo, if all Tests and Suites are contained within a single master Suite, then ALL tests can be executed by a single-click)
• Pages (regular wiki pages and test pages) are all Pascal Case--sometimes known as "upper camel case" (e.g. TestPage).
• Pages have "Properties" that you can tailor via the left-nav of each page that denote if the page is a regular wiki page, a test page, a test suite, or a variety of other options.
• Test pages within a Suite can be accessed via an optional Table Of Contents and each Test page within the Suite will have a link at the top of the page back to the main Suite page
• Create Test pages within the Suite by going to a non-existent URL that starts with the Suite name, followed by a period, following by a Test page name (e.g. TestSuite.TestPage)
• Test's can then be ran individually via the specific Test page or all at once by clicking on the "Suite" button (left-nav) while on the Suite main page
• A Test Suite can have a global "Set Up" page (named SetUp) that will run before each Test page in the Suite (e.g. TestSuite.SetUp); this is where the DB connection string would go so it doesn't have to be repeated per each Test

LET'S GET STARTED

First we need to get the DbFit program and get it running:

• Download "dbfit-complete-xxxxxxxxx.zip" from http://sourceforge.net/project/showfiles.php?group_id=191053&package_id=224326
• Create a local directory for it: C:\DbFit (note: the zip file does not contain a top-level folder so be sure to do this step!)
• Unzip files from dbfit-compelte-xxxxxxxx.zip to C:\DbFit
• Open a DOS window (cmd.exe)
• CD\DbFit
• startFitnesse.bat
• To stop, hit Control-C or close the DOS window
• It is also a good idea to get the PDF guide that goes through most of the features written by the author of DBFit: Test Driven Database Development with DbFit. You might want to save this to your C:\DbFit directory

Next we can start with a simple example. You can create a Test page by going to a non-existent URL with a PascalCased name (e.g. TestPage). This will put you in edit mode of the new page that will exist once you click the "Save" button at the bottom. Go to: http://localhost:8085/HelloWorld, paste in the following code, and then click the "Save" button.

!define COMMAND_PATTERN {%m %p}
!define TEST_RUNNER {dotnet2\FitServer.exe}
!define PATH_SEPARATOR {;}
!path dotnet2\*.dll
!|dbfit.SQLServerTest|
!|Connect|data source=localhost;Integrated Security=SSPI;database=tempdb;|
'''Note that Boolean results need to be matched to "true" and "false" and NOT 1 and 0'''
|Query| SELECT CONVERT(BIT, ISNUMERIC('12')) AS 'result' |
|result|
|True|

After creating the page (i.e. clicking the "Save" button), go to Properties (button in left-nav) and check "Test" under "Actions" and click on "Save Properties". The Test page can be executed by clicking on the "Test" button in the left-nav (the button will not appear if you did not complete the previous step of checking the "Test" check-box in "Properties").

When you click "Test" you will see a colored section titled "Assertions". It will be green this time since the test works (and any time that all of the tests are successful) but if there are any errors it will be red. To the far right at the top is a large letter "i" with the words "Tests Executed OK" below. If you click on the letter or the words you will go to a page that shows how long the test(s) took to complete. And to see what happens upon an error: click the "Edit" button in the left-nav of the HelloWorld page (not the ErrorLogs page that shows the Exection Time), change the word "True" at the bottom to "False", click the "Save" button, and then click the "Test" button. You should now see that what you were expecting--False--is not in the Result Set but there was a result of "True" and that is shown as "surplus" since it was not expected.

THE FUN STUFF

Now that we have just seen the basic framework of creating a test, let's go through the various features of DbFit. Since DbFit is a wiki, the rest of the demo will be a real working demo that will show you how it works with explanation in the wiki / test page as opposed to just reading about it here and looking at screen-shots. To that end, the rest of the demo is structured so that all you need to do is create each wiki page (there will be a link just like the above one for HelloWorld) and then you just copy-and-paste the contents of the code-block into the page. We won't go through every single feature, but you will see enough to get the idea and the document linked above has the full list of features. Each section contains a link that will open a new wiki page that you can then paste the contents of the code block following the link into. Once you see how these pages work you can edit them to better understand the behavior of the features.

Basic Wiki Formatting

This page shows some of the basic formatting options of the wiki.

http://localhost:8085/WikiFormatting

!1 !c Wiki Formatting
!3 Basic Formatting
{{{
- !1 Apply Heading 1 style to the rest of the line.
- !2 Apply Heading 2 style to the rest of the line.
- !3 Apply Heading 3 style to the rest of the line.
- !c Align to centre.
- ---- Horizontal line (4 or more dashes).
- !img url Display image from url.
- '''text''' Bold  --  three single quotes enclosing text on each side.
- ''text'' Italics  --  two single quotes enclosing text on each side.
- # Comment  --  ignore the rest of the line.
}}}
!3 Variables
!- Use: !define var_name { var_value } -!
!- or:  !define var_name ( var_value ) -!
!- !define bob { (Hello) } -!
!define bob { (Hello) }
!- !define bob2 ( {Hello} ) -!
!define bob2 ( {Hello} )
!- show variable by using: ${var_name} -!
!- show: ${bob} -!
show bob: ${bob}
show bob2: ${bob2}
!3 Preventing Formatting
 * Prevent formatting on a single line by starting with a ! (but will still replace variables)
! !1 this text is NOT displayed as Heading 1
! show bob: ${bob}
 * Enclose text in !- {{{ and  }}} -!
{{{
this is text
that is enclosed in triple curly brackets
   which keeps spaces and returns, but still replaces variables
show bob: ${bob}
}}}
 * Enclose text in !- !- and -! -!
!-
this block does NOT keep spaces or returns (as they are in this block in the source), 
   but does not replace variables
 or respect the {{{ ... }}} blocks:
show bob: ${bob}
-!
!3 Link Formatting
Internal links are just the page names, which are Pascal-Cased: HelloWorld
External links start with http:// : http://www.SQLSharp.com
External links can also have a label by using !- [[ label ][ url ]] -! : [[ SQLSharp.com ][ http://www.SQLSharp.com ]]
External links ending in either .jpg or .gif displays the image !-(http://www.SQLSharp.com/images/logo.gif)-!:  http://www.SQLSharp.com/images/logo.gif

!3 Complete Reference
http://fitnesse.org/FitNesse.MarkupLanguageReference

Basic Suite Page

This page shows how a Test Suite works. Test Suites allow for combining any number of sub-pages into a coordinated test. After creating the page, go to Properties (button in left-nav) and check "Suite" (but NOT "Test") under "Actions" and click on "Save Properties" (I have noticed that when creating a page with the name of Suite, the "Suite" property is automatically checked). Including the directive "!contents" (without the quotes) at the top of the Suite content and it will build a Table Of Contents of all Test pages within the Suite for easy navigation.

http://localhost:8085/DemoSuite

!c !1 Demo Suite
!contents -R -g -p -f -h
!define COMMAND_PATTERN {%m %p}
!define TEST_RUNNER {dotnet2\FitServer.exe}
!define PATH_SEPARATOR {;}
!path dotnet2\*.dll

'''this variable (and the above definitions) are available to all sub-pages'''
!define testvar { Orange }

Suite Setup Page

The SetUp page will be executed at the beginning of each test page without having to be specifically included. For this reason it is the ideal place to put the basic connection settings so they only need to be entered once. This page does not need any "Properties" set.

http://localhost:8085/DemoSuite.SetUp

!|dbfit.SQLServerTest|
# if you want to connect via login and password, consult the online documentation or the PDF file 
!|Connect|data source=localhost;Integrated Security=SSPI;database=tempdb;|
Note from the User Guide:
{{{
     We've already mentioned that it is a good practice to initialise the database
     connection on a suite's SetUp page. Another thing to consider is whether
     you need to clear values stored in symbols. FitNesse persists symbol values
     from one test to the next in a Suite run. If you are using symbols in DbFit
     tables to store primary keys or other values, you may need to clear the values
     from your symbols before each test run. The flow-mode of DbFit provides
     the ClearParameters method which is just called within its own table:
}}}
!|ClearParameters|

Queries, Ordered Queries, and Accepting Failures

This page will show the two free-form Query options. The first one will test the results in whatever order they are returned while the "Ordered Query" operation can test for the specific order of each record. You can test a negative condition using the "fail" modifier in a result set. Be sure to set the "Test" check-box in the "Properties" area.

http://localhost:8085/DemoSuite.AdHocQueries

!1 !c Ad Hoc Queries
!2 Regular Queries
'''When using the "Query" operation, the first wiki table row is the Query itself, the second row is just what you want to capture from the result set (it does not need to be all columns), and the remaining rows are the what you expect the results to be.'''
'''Note that the three possible outcomes: Match, Missing (row expected but not returned), and Surplus (row returned but not expected)'''
|Query| SELECT 100 AS 'ID', 'Bob' AS 'Name' UNION SELECT 102 AS 'ID', 'Sally' AS 'Name' |
|ID|Name|
|100|Bob|
|103|Jane|

!2 Ordered Queries
'''This query will pass since it returns the three rows in the order expected: 1, 2, and then 3'''
|Ordered Query| SELECT 1 AS 'val' UNION SELECT 2 UNION SELECT 3 |
|val|
|1|
|2|
|3|
'''This query will fail since it returns the three rows in the order expected: 1, 3, and then 2'''
|Ordered Query| SELECT 1 AS 'val' UNION SELECT 2 UNION SELECT 3 |
|val|
|1|
|3|
|2|
!2 Accepting Failures
'''This query will pass since it DOESN'T returns the three rows in the order expected: 1, 3, and then 2'''
!Note the use of the fail[value that should not be returned] syntax; this lets you test negative cases
|Ordered Query| SELECT 1 AS 'val' UNION SELECT 2 UNION SELECT 3 |
|val|
|1|
|fail[3]|
|fail[2]|
'''This query will also pass like the one directly above, but it is making sure a non-existant value is not in that spot'''
|Ordered Query| SELECT 1 AS 'val' UNION SELECT 2 UNION SELECT 3 |
|val|
|1|
|fail[3]|
|fail[21]|
'''You can also use this with the regular "Query" operation.  This test will ensure that we do not a particular value.  Another test can be created to check for the positive condition of getting the correct value.'''
|Query|SELECT SQRT(9) AS 'square_root'|
|square_root|
|fail[2]|

!2 User-Defined Functions and Stored Procedures
'''Later we will see how Query can also be a simple test of a UDF or a Stored Procedure'''

Including Pages and Executing Ad Hoc SQL

Another time-saving feature is the ability to include page content in another page. This will allow you to create something once and re-use it multiple times. And unlike the SetUp page, it is not automatically in all sub-pages so it can be used selectively. Please note that pages that are included in sub-pages do not need to have the "Test" check-box set in "Properties".

http://localhost:8085/DemoSuite.IncludePage

!1 !c Setting up re-usable code for several tests
'''A note from the User Guide:'''
{{{
     First, it is a good idea to create a main wiki in which to store your reusable
     pages. Create a new top-level page in FitNesse and name it UtilityPages or
     something similar. Continuing with the previous example, we could create
     a new page in the UtilityPages wiki named InsertContact.
}}}

!2 First create a Table to use for subsequent tests

'''NOTE 1: DbFit treats PascalCased words as special so use camelCase OR place an exclamation (!) to the left of the wiki table'''
'''NOTE 2: Use the "Execute" operation when there is no output or no need to test any output / results'''
''In most cases the DB table will already exist, so this step would not exist''
!|Execute|CREATE TABLE DBFitTest(TestID INT IDENTITY(1, 1) NOT NULL, EmpName VARCHAR(50) NOT NULL, FavoriteColor VARCHAR(20) NULL)|

'''NOTE: you cannot use inline (--) comments in block SQL code, but you can use block comments (/* ... */)'''

'''Create the INSERT proc'''
# the following command turns off the mapping of the special character "@" so we can create the proc
|set option|bind symbols|false|
!|Execute|!-
CREATE PROCEDURE DBFitTest_Insert(
@EmpName VARCHAR(50),
@FavoriteColor VARCHAR(20) = NULL,
@TestID INT OUTPUT
) AS
SET NOCOUNT ON
INSERT INTO DBFitTest (EmpName, FavoriteColor)
VALUES (@EmpName, @FavoriteColor)
SET @TestID = SCOPE_IDENTITY()
SELECT @TestID AS 'NewTestID'
-!|
# the following command turns back on the mapping of the special character "@"
|set option|bind symbols|true|

'''Create the UPDATE proc'''
# the following command turns off the mapping of the special character "@" so we can create the proc
|set option|bind symbols|false|
!|Execute|!-
CREATE PROCEDURE DBFitTest_Update(
@TestID INT,
@EmpName VARCHAR(50),
@FavoriteColor VARCHAR(20),
@OriginalEmpName VARCHAR(50) OUTPUT
) AS
SET NOCOUNT ON
/* first get the original Name */SELECT @OriginalEmpName = EmpName
FROM   DBFitTest
WHERE  TestID = @TestID
/* now change the data */UPDATE DBFitTest
SET    EmpName = @EmpName,
       FavoriteColor = @FavoriteColor
WHERE  TestID = @TestID
SELECT @OriginalEmpName AS 'OriginalEmpName'
-!|
# the following command turns back on the mapping of the special character "@"
|set option|bind symbols|true|

'''Create a simple Get proc'''
# the following command turns off the mapping of the special character "@" so we can create the proc
|set option|bind symbols|false|
!|Execute|!-
CREATE PROCEDURE DBFitTest_GetByTestID(
@TestID INT
) AS
SET NOCOUNT ON
SELECT EmpName, FavoriteColor
FROM   DBFitTest
WHERE  TestID = @TestID
ORDER BY EmpName
-!|
# the following command turns back on the mapping of the special character "@"
|set option|bind symbols|true|

'''Create a simple GetALL proc'''
# this time we do NOT need the "set option" function since there are no parameters and no "@" characters
!|Execute|!-
CREATE PROCEDURE DBFitTest_GetAll
AS
SET NOCOUNT ON
SELECT TestID, EmpName, FavoriteColor
FROM   DBFitTest
ORDER BY EmpName
-!|

Simple INSERT and UPDATE and Variables

We shall now see how to make use of our IncludePage when we look at the short-cut methods of doing INSERT and UPDATE. We will also see how to use variables to trap output and use in test results and operations. Be sure to set the "Test" check-box in the "Properties" area.

http://localhost:8085/DemoSuite.InsertAndUpdate

!1 !c INSERT, UPDATE, and Variables/Parameters
!2 First we include the standard setup using !include
By default it will display the included page in a box that can be collapsed (e.g. the Set Up page above).  If you want the box collapsed by default, use the -c flag.
If you want the included page to not be in a box at all but just part of the page, use the -seamless flag.
# NOTE: you need to prefix the wiki page to be included with a period (.)
!include -c .DemoSuite.IncludePage

!2 Simple INSERT (using table created by the IncludePage)
'''Note that we can pass in a true NULL value using NULL (not case-sensitive)'''
!|Insert|DBFitTest|
|EmpName|FavoriteColor|
|Ian Curtis|NULL|
|Peter Hook|Grey|
'''If we want to capture the auto-generated IDENTITY value, add the column title followed by a question mark (?) and in the data row place the returned value into a variable using two greater-than signs (>>) prefixed to the variable name.  When running the test it will show you the value returned in the wiki table.'''
'''Please note that DBFit and the DBFit documentation will call them "parameters", but I find that to be misleading so I call them "variables".'''
!|Insert|DBFitTest|
|EmpName|FavoriteColor|TestID?|
|Stephen Morris|Red|>>stephen|
|Bernard Albrecht|Grey|>>bernard|

'''Do a simple SELECT to make sure the data was inserted'''
!|Query|SELECT TestID, EmpName, FavoriteColor FROM DBFitTest WHERE FavoriteColor IS NULL|
|TestID|EmpName|FavoriteColor|
|1|Ian Curtis|NULL|

!2 Simple UPDATE (using table created by the IncludePage)
'''We can use the parameters that were created in the INSERT to store the IDENTITY value by prefixing the variable name with two less-than signs (<<).  When you run the test it will show you, in the wiki table, the value in the variable.'''
!|Update|DBFitTest|
|EmpName=|TestID|
|Bernard Sumner|<<bernard|
'''Let's verify our UPDATE.  We can even use the variable created above in the INSERT in the test to compare with by again prefixing the variable name with two less-than signs (<<).  As before, when you run the test it will show you, in the wiki table, the value in the variable.'''
!|Query|SELECT TestID, EmpName, FavoriteColor FROM DBFitTest WHERE FavoriteColor = 'Grey'|
|TestID|EmpName|FavoriteColor|
|2|Peter Hook|Grey|
|<<bernard|Bernard Sumner|Grey|
'''Do another SELECT.  This time notice how the variables created above in the INSERT can be used in the "Query" operation as regular SQL variables that are prefixed with an at-sign (@).'''
!|Query|SELECT TestID, EmpName, FavoriteColor FROM DBFitTest WHERE TestID = @stephen|
|TestID|EmpName|FavoriteColor|
|<<stephen|Stephen Morris|Red|

!2 Creating a variable outside of an INSERT
'''Again, please note that DbFit and the DbFit documentation will call them "parameters", but I find that to be misleading so I call them "variables".'''
'''A variable can be created by the "Set Parameter" operation'''
|Set Parameter|color|Grey|
'''As before, the variable can be used in the results to compare with prefixed by <<'''
!|Query|SELECT TestID, EmpName, FavoriteColor FROM DBFitTest WHERE TestID = @bernard|
|TestID|EmpName|FavoriteColor|
|<<bernard|Bernard Sumner|<<color|
'''And the parameter can be used in the "Query" or "Execute" (or any operation) prefixed with @'''
!|Execute|DELETE FROM DBFitTest WHERE FavoriteColor = @color|
!|Query|SELECT TestID, EmpName, FavoriteColor FROM DBFitTest|
|TestID|EmpName|FavoriteColor|
|1|Ian Curtis|NULL|
|<<stephen|Stephen Morris|Red|

Execute and Execute Procedure

Here we will see how to execute ad hoc SQL that will not be returning results, or at least results that you will be testing. By using the "Execute" operation you can process ad hoc queries without being required to capture the output for testing, unlike with the "Query" operation. The "Execute Procedure" operation only accepts a Stored Procedure name but allows for calling it multiple times based on how many rows in the wiki table and it can capture values from OUTPUT parameters for later use.

http://localhost:8085/ExecuteAndExecuteProcedure

!1 !c Execute and Execute Procedure
!2 First we include the standard setup (which uses "Execute") using !include and the -seamless option
By default it will display the included page in a box that can be collapsed (e.g. the Set Up page above).  If you want the box collapsed by default, use the -c flag.
If you want the included page to not be in a box at all but just part of the page, use the -seamless flag.
'''This time we are using the -seamless flag on the !include function.  Please notice that we are using the "Execute" operation to create the Table and Stored Procedures since there is no input to pass in and no results to test.'''
# NOTE: you need to prefix the wiki page to be included with a period (.)
!include -seamless .DemoSuite.IncludePage

!2 Test a more traditional scenario using "Query" and "Execute Procedure"
'''Now that we have created a more common situation that includes basic CRUD procs, we can see how "Query" and "Execute Procedure" work.'''
'''Please notice that we did ''not'' create a Delete Stored Procedure.  While a typical system will have one, we did not need to simulate that for this test since DBFit works inside of a Transaction and the records that will be added to the Table (and the Table and Stored Procedures as well) will be rolled back automatically at the end of the test, thus making these tests easily re-runnable.'''
'''First we will look at the "Query" operation that we have used before since it will capture the row of output for the newly created TestID.'''
'''In the following example we will make use of the variable we created in the main DemoSuite page: testvar.  You will notice how the value of the variable--Orange--is substituted whenever the page is rendered (i.e. even when not executing the test).'''
'''We will also have to pass in a 0 for the OUTPUT parameter of @TestID since the "Query" operation does not work with OUTPUT parameters.'''
|Query|EXEC DBFitTest_Insert 'Mark Robinson', '${testvar}', 0|
|NewTestID|
|1|

'''While the above test worked, and while many people use a SELECT to output the newly created ID, we won't typically know the next available value to test for.  And that does not allow us to capture the value for later use.  In DbFit, that can only be done when using OUTPUT parameters.  OUTPUT parameters ''can'' be captured when using the "Execute Procedure" operation.'''
'''The format of the "Execute Procedure" operation is that the first row is the Stored Procedure name, the second row is the input and output parameters, and the following rows are sets of input that will call the Stored Procedure per row.  OUTPUT parameters are denoted with the parameter name followed by a question mark (?).'''
'''In the following example we again use the variable created in the main Suite page: testvar.'''
!|Execute Procedure|DBFitTest_Insert|
|EmpName|FavoriteColor|TestID?|
|Phil Krauth|${testvar}|>>phil|
|Bridget|Green|>>bridget|
'''Let's make sure the record inserted correctly before we try to UPDATE it.  Here we see the use of a SELECT with a * instead of specific column names AND we see that not all of the fields are specified to test for in the second wiki table row (we are not capturing the TestID field).'''
!|Query|SELECT * FROM DBFitTest WHERE TestID = @bridget|
|EmpName|FavoriteColor|
|Bridget|Green|
'''Now that we have confirmed the data we can proceed with testing the UPDATE proc.'''
!|Execute Procedure|DBFitTest_Update|
|TestID|EmpName|FavoriteColor|OriginalEmpName?|
|<<bridget|Bridget Cross|Aquamarine|Bridget|
'''And we should now test the affect of the UPDATE proc to make sure that it worked.  This time we will use the GetByTestID proc that will naturally be in most systems that use CRUD procs.'''
!|Query|EXEC DBFitTest_GetByTestID @bridget|
|EmpName|FavoriteColor|
|Bridget Cross|Aquamarine|
'''Lastly let's test the generic GetALL proc.  Here we again see that we can test an entire result set.  Also, the order in which the records are returned is not important with regards to the order that the wiki rows are in.  If we did want to make the order important, then we could use "Ordered Query" as we saw in a previous example.'''
!|Query|EXEC DBFitTest_GetAll|
|TestID|EmpName|FavoriteColor|
|1|Mark Robinson|${testvar}|
|<<bridget|Bridget Cross|Aquamarine|
|<<phil|Phil Krauth|${testvar}|

'''As you can see from this page, there are many tools to make use of:
1) "Execute" to run any SQL that doesn't care about input/output parameters or result sets
2) "Execute Procedure" that will run a Stored Procedure for each row in the wiki table and can trap OUTPUT parameters but not result sets
3) "Query" that will run any SQL and is geared towards testing result sets and will match the results to the wiki table
4) variables starting with $ that can be passed from a page to any lower page (within a Suite or sub-Suites)
5) variables that can capture OUTPUT parameter values (when prefixed with >>) and then can be used in queries as a regular parameter prefixed with @ or in test results (Query) or input parameter mappings (Execute Procedure) when prefixed with <<
'''

Structure and Sub-Suites

The examples so far have shown many things on a single test page but the organization of the commands was designed for a brief demo to show the basic functionality. In a more realistic situation a test page can be set up to test various permutations of input parameters that would alter the behavior of a particular Stored Procedure. Simple procedures that relate to a particular functional area can be tested together on a single page.

More complex code can be thoroughly tested on a single page and a collection of pages of related code can be grouped together in a Suite. Within a Suite you can create a sub-Suite by creating another test page and marking it as a "Suite" in the "Properties" page rather than marking it as a "Test" page. From this new Suite page (for example: DemoSuite.UserManagement) you can create test pages with a period (.) separator just like we did above for the main Suite. Using the UserManagement example just noted, you could have a series of tests within that functional area with the pages looking like:

DemoSuite.UserManagement.AddingUsers
DemoSuite.UserManagement.UpdatingUsers
DemoSuite.UserManagement.DeletingUsers
DemoSuite.UserManagement.RetrievingUsers

Now go back to the main "DemoSuite" page (there is a link to this page at the top of each page within the Suite) and click the "Suite" button at the top of the left-nav. If this button is not there, click on "Properties" in the left-nav and check the "Suite" check-box and save the change. Clicking the "Suite" button will run all tests in the Suite, including any sub-Suites. This makes it very easy to run groups of tests. You could structure your setup to have a main Suite of sub-Suites that test functional areas. Then you could go to each sub-Suite to run a series of tests for that functional area or you can test the entire system by clicking the "Suite" button on the main Suite page.

Deleting, Renaming, and Moving Pages

If you want to delete, rename, or move any page in the wiki, go to that page and click on the "Refactor" button in the left-nav. Here you will find all of these options.

The Homepage

Lastly, if you want to edit the homepage, just go to:

http://localhost:8085/FrontPage?edit

The !contents function does not work there but you can put links to your pages and test suites simply by putting in the page name by itself and it will automatically link to the page. For example, if you enter just the following at the top of the page:

HelloWorld
WikiFormatting
DemoSuite

then it will create a links to those pages that we created earlier. This is because it is a wiki and recognizes the PascalCased words as a page names. And clicking on FitNesse logo in the upper-left will always take you back to the homepage.

Not Covered in this Tutorial

Obviously there is not enough time to cover all of what DbFit can do. To see the full extent you need to read the PDF (Test Driven Database Development with DbFit) that linked in the Resources section below. Some areas to check out are the "Inspect" operation and the section on "Working With Padded Characters". Another notable feature is being able to compare the results of two different pieces of code. For example, if you migrating from one Procedure to another (maybe you are making it more efficient) and do not have a consistent result set to put into a wiki table (so the "Query" operation would not work), then you can store the results from both and compare those results. See the "Compare Stored Queries" section for details.

MORE ABOUT TESTING

For those shops that are relying on ORM technology such nHibernate or LINQ and not so much on Stored Procedures, DbFit can still help by making the application level testing easier. DbFit used with regular FitNesse tests (for application code) can be used to set up the environment and/or test the affects of DML statements issued by the application, whether hard-coded or generated by something like LINQ. And regardless of how your application accesses the database, DbFit can also minimize having to follow a complex code-path / work-flow in the application logic just to get the DB into the necessary state required to test a single method. See the documentation regarding "stand-alone" mode for more information.

It should be noted, however, that this is not true Unit Testing since it is not possible to "mock" external dependencies. Proper Unit Testing does not allow the code being tested to make any external references, so Unit Testing at the application level will never truly hit the database. This is done by having all external dependencies "mocked" to return pre-defined results to simulate ideal conditions. At the database level, however, it is not possible to "mock" any external Stored Procedure or User-Defined Function calls within a Stored Procedure or User-Defined Function. Hence, if Proc_1 calls Proc_2 which in turn calls Proc_3, then by testing Proc_1 you are really tested how all three Stored Procedures work together. But regardless of the semantics, testing with DbFit is highly beneficial.

DbFit is also not a replacement for application level integration testing that can be done with something like FitNesse. However, what testing code at the DB level does is narrow down the location of a problem sooner. And DbFit is not designed to test performance or to trouble-shoot deadlocks. But there are enough back-end process Stored Procedures, and Stored Procedures and User-Defined Functions that are called by other Stored Procedures to warrant DB level testing, even if you have ample integration testing coverage.

CONCLUSION

Testing code has never been the most enjoyable aspect of programming but it is necessary to ensure quality and consistency. At ChannelAdvisor we have come a long way adding Unit Tests to our application code and have started testing code at the DB level with DbFit. With DbFit, there is a very easy and manageable way of testing DB code that removes many of the annoyances of testing. While some complex situations might not be as feasible to test, most should be able to fit into the DbFit framework. And having a set of saved tests will reduce time in creating tests each time a proc is changed (due to refactoring, etc.). At this point there should be no excuse for not testing code. And if you are curious as to "why not TSQLUnit or DBUnit", check out the PDF and other links in the Resources section that follows. Now if only the compiler could read our minds and auto-populate the comments.

RESOURCES

• DBFit: http://www.fitnesse.info/dbfit
• FitNesse: http://www.fitnesse.info/start
• Gojko Adzic (creator of DbFit): http://gojko.net/
• Test Driven Database Development with DbFit
• Standalone and flow mode comparison: http://fitnesse.info/dbfit:modes
• Fighting the monster: http://gojko.net/2007/11/20/fighting-the-monster/

Copyright © October 2008 by Solomon Rutzky

Any content taken from the user manual is copyright © 2006-2008 by Gojko Adzic