Contents

• Database Access
• Inserting Records
  • Updating Records
  • Deleting Records
  • Connecting to a Database
• Notes on the Attached Fixture Library
• That’s a Wrap!

FitNesse is a wiki-based framework for writing acceptance tests for software systems. If you are not familiar with FitNesse, Part 1 of this series walks through a complete .NET example from writing the test in your browser to writing the C# code-behind. While FitNesse provides a rather nifty and user-friendly way to write acceptance tests in general, in practice there are plenty of quirks and glitches to watch out for. This article provides “tips from the trenches”, i.e. an accumulation of tips collected from intensive use of FitNesse on a daily basis to alleviate or avoid many of those pain points.

Here is your roadmap to the series, showing where you are right now:

	Part 1: FitNesse Introduction and Walkthrough
	Part 2: Documentation and Infrastructure
	Part 3: Naming and Layout
	Part 4: Debugging, Control Flow, and Tracing
	Part 5: Symbols, Variables, and Code-Behind Style
	Part 6: Multiplicities and Comparisons
	Part 7: Database Fixtures, Project Overview

Database Access

Inserting Records

Dbfit includes a general purpose Execute fixture that lets you execute most any SQL statement you like. However, you should use that only when there is not a more specific fixture available for the task at hand. Thus, to insert data into a table you can pass a SQL insert statement to the Execute fixture (left) but it is better to use the Insert fixture (right).

	Using General Purpose “Execute”	Using Specialized “Insert”
	!|Execute|insert into tablename values (...)|	!|Insert|tablename|

Here is a complete test table using the Insert fixture. A powerful feature of this fixture is that in an atomic operation you can insert records into the table and retrieve IDs for those inserted records! Here you can see that the first three IDs are returned and also stored into symbols.

	!|Insert |dbo.${MyTable} | |WidgetID|Price|DateValidFrom |RecordId?| |25 |1010 |1/1/2013 10:21:03|>>record1| |12 |1011 |5/1/2013 8:15 |>>record2| |212343 |200 |2/2/2014 |>>record3| |1234123 |500 |4/4/2012 20:00 | |
	Insert dbo.MyWidgets_tmp WidgetID Price DateValidFrom RecordId? 25 1010 1/1/2013 10:21:03 >>record1 12 1011 5/1/2013 8:15 >>record2 212343 200 2/2/2014 >>record3 1234123 500 4/4/2012 20:00
	Insert dbo.MyWidgets_tmp WidgetID Price DateValidFrom RecordId? 25 1010 1/1/2013 10:21:03 >>record1 12 1011 5/1/2013 8:15 >>record2 212343 200 2/2/2014 >>record3 1234123 500 4/4/2012 20:00

Reference: CleanCode.DataBaseNotes.CrudOperations

Updating Records

For updating records Dbfit also provides a more specific fixture available for the task at hand, the Update fixture. Thus, to update data in a table you can pass a SQL update statement to the Execute fixture (left) but it is better to use the Update fixture (right) when conditions allow (explained below).

	Using General Purpose “Execute”	Using Specialized “Update”
	!|Execute|update tablename set… where…|	!|Update|tablename | |field_to_change=|field_to_select| |new value |matching value |

With the Update fixture you can convert a standard SQL (left) into the test table (right):

	SQL statement	Using Specialized “Update”
	UPDATE dbo.MyWidgets SET Active=1 WHERE RecordId in (@record1, @record3) AND Enabled=True	!|Update|dbo.MyWidgets2 | |Active=|RecordId |Enabled| |1 |<<record1|true | |1 |<<record3|true |

You may include multiple columns in the selector; the example selects on both the RecordId field and the Enabled field. You may also include multiple fields to update though only one is shown in the example (Active).

The main advantage of the Update fixture over a standard SQL update statement is that you can use different selection criteria and different update values for every record. The example shows the same values because it is purposely matching the SQL statement. But clearly you are free to use different values in each cell in the test table.

The main disadvantage of the Update fixture is that you cannot provide an expression or even a simple field name to SQL-you may use only variables or constants known in the scope of the FitNesse test page. That is, within a SQL update statement you could use an expression (e.g. SET Price=Price+1) or a field name (e.g. SET BillDate=InvoiceDate). Those cannot be used with the Update fixture.

Reference: CleanCode.DataBaseNotes.CrudOperations

Deleting Records

For deleting records Dbfit provides a more specific fixture available-sort of(!). The Clean fixture is available but is undocumented. So just as SQL Server has well-known yet officially unsupported stored procs and such that you should not depend on, nor should you rely on undocumented features in DbFit. But that is not a great loss; I found DbFit’s Clean fixture unsatisfactory anyway. Rather, I have included a DbClean fixture in my project library that provides more capability.

	Using General Purpose “Execute” or “Clean”	Using Specialized “DbClean”
	!|Execute|delete from tablename where...| -- or -- |Clean | |Table |Where |Clean |	!|DbClean | |Table |Where |Clean?|ResultDetails?| |name |condition|rows |null |

There are several disadvantages of the generic Execute fixture and the DbFit Clean fixture.

First, neither the generic Execute fixture nor the Clean fixture supports FitNesse symbols. (Recall that you could, for example, define a FitNesse symbol TargetPrice, then use that in the Query fixture as in: SELECT a,b,c WHERE Price > @TargetPrice.) Second, the Clean output field is a Boolean, reporting success or failure but not how many records were processed. Finally, I have not been able to characterize what causes it, but both of these fixtures seem to sporadically fail.

DbClean overcomes all of those disadvantages. The Clean output field indicates how many records matched the WHERE clause and were deleted. The ResultDetails output field will be null for a successful operation; if a problem occurred, it will report an error message. DbClean also supports symbols just like Dbfit’s Query fixture so you can build a parameterized WHERE clause when necessary. My recommendation is to use DbClean twice in any database test, once at the start of the test where you do a sanity check to confirm that there are no matching records (thus checking for 0 returned in the Clean? Column)…

… and also at the end of the test where you check for the proper record counts inserted during this test and delete them…

Any errors at the start of the test indicate some test (possibly the same test) left detritus behind. Errors at the end of the test indicate the current test likely has an error (that may or may not be revealed in other test tables on the page).

Alas, DbClean has one important caveat. Because it is external to DbFit, you have to explicitly perform a commit on your DbFit operations before you use DbClean. If you do not, the transaction is left open, blocking DbClean from completing and it will eventually time out and fail. See the reference page to see an example.

Reference: CleanCode.DataBaseNotes.CrudOperations

Connecting to a Database

To connect to a database with DbFit, you must choose either flow mode or standalone mode. The DbFit manual recommends flow mode over standalone mode, in that flow mode provides automatic transaction management “… and some other shortcuts” though it is not clear what that entails.

You invoke flow mode with the appropriate database fixture-this one is for SQL Server:

But flow mode has one serious restriction; the above connection table must be the very first table on the page-that includes any tables in SetUp or SuiteSetUp pages! And that also precludes having an import fixture to define your namespaces, hence the use of the fully qualified name dbfit.SqlServerTest above. One common scenario that causes mysterious problems is if you choose to use flow mode, putting the above connection table on your test page, while at the same time having an inherited SetUp page that brings in some other test tables before it, violating the absolutely-first-table rule. You must override the inherited SetUp page in such a situation.

If you need to interact in one test with more than one database you must use standalone mode because of the above rule. In standalone mode use the DatabaseEnvironment fixture; you can sprinkle multiple DatabaseEnvironments in one test, though only one database is active at a time.

Particularly when using multiple databases I recommend doing a sanity check immediately after connecting to each database, like this one for SQL Server:

As the first assertion in a test this lets you know immediately whether you are have connected to the database you think you did and whether you can in fact converse with it.

Reference: Flow vs Standalone

– see Chapter 12

Notes on the Attached Fixture Library

Included with this series of articles is an archive containing both a library of custom FitNesse fixtures (CleanCodeFixtures.dll) and a FitNesse suite of tests that illustrate the fixture library as well as document many of the issues discussed in this series.

The test suite consists of these subsuites:

SubSuite	Description
Concept Notes	Discusses a variety of issues documented in this series
Control Flow Notes	Illustrates debugging, aborting, pausing, and tracing tests.
Data Base Notes	Examines standard CRUD operations in a FitNesse context.
General Utility Fixture Notes	Shows example usages of the fixtures in CleanCodeFixtures.dll.

 

Refer to the GeneralUtilityFixtureNotes for documentation and examples on how to use the fixtures. (You could also review the source code, of course; that is included as well.)

Here is a snapshot of the execution of the entire suite. FitNesse results begin with a one-line summary at the top, which expands into a one-line-per-test summary. Both of those are shown here. (Note that there are deliberately 6 errors injected into this suite for reasons explained on those particular test pages.) Below that is a complete transcript of the entire test run (not shown:-). You will see three of the four sub-suites mentioned above in the figure; the fourth one, Control Flow Notes, could not be included in an automated test run because it fiddles with the control flow.

If you want to execute any of the control flow tests (or any others not marked as executable by default) you can either edit the properties on the particular page, or just add this suffix “?test” to the URL and reload the particular page. Example (put this all on one line of course):

Reference: CleanCode (Remember, non-hyperlinked references point to http://yourserver:yourport/reference.)

That’s a Wrap!

I did not expect this series to turn into seven parts when I began writing but I suppose, in retrospect, having some 35 pages of notes should have given me a clue! But there you have it. Colleagues in my day job have often said they were glad I took copious notes on issues I encountered when I would venture into exploring a new technology that we were going to be adopting. It saved them a lot of time and effort not having to resolve many of the same issues. I hope that you can similarly use these “tips from the trenches” and save some effort so you have more time to get on with your main focus. Let me know!