Prev Next

DbFit: Test-driven database development

DbFit is a set of FIT fixtures which enables FIT/FitNesse tests to execute directly against a database. This enables developers to manipulate database objects in a relational tabular form, making database testing and management much easier then with xUnit-style tools. The library is free to use, released under GNU GPL.

Features | Download | Reference | Source code | Related resources
Why DBFit | Project goals | Q & A


Features

  • Regression testing for queries and SQL statements
  • Functional testing for stored procedures and functions
  • Automatic transaction control and various short-cuts to make writing test scripts easier and more efficient
  • Support for DB2, Oracle, SQL Server (java and .net versions), MySql 5 and Derby (only java)

For more details, see DbFit pages on fitnesse.info.


Why DBFit

After introducing FitNesse into a big .NET project, I found that we were still lacking test-coverage for a lot of DB (legacy) code, and that DB developers have a long feedback loop, so they were not really benefiting from TDD. FitNesse was a good solution because it enabled DB developers to write functional tests much easier than in pl/sql based unit testing libraries, and without getting Java developers involved to write those as JUnit tests. Although FitNesse is typically used for acceptance tests, the combination of FitNesse+DbFit filled the testing gap for our DB code with excellent results. In fact, as the natural DB interface is relational, FitNesse tables allow writing database tests much more efficiently than using function-centered unit testing libraries. For a more detailed view into the background of DbFit, see Fighting the Monster.


Project goals

  • Enable managing database state from FitNesse as an utility for integration tests involving databases, by .Net and Java developers
  • Enable the use of FitNesse for effective database acceptance and unit testing by DB developers (with no .Net/Java knowledge)

The first group mainly needs reusable fixtures which they can use, either directly on the page or embedded in their project-specific fixtures, and will not mind digging into a bit of Java/.Net code. But the other group must not be made to wait for Java/.Net developers to do effective TDD. So the project is taking a dual approach – developing test fixtures which will be reusable and extendable, but also developing one arch-test fixture which can control the connection, transactions and test flow in a fitlibrary flow mode. So Java/.Net developers can utilise the fixtures directly, and DB developers will be able to utilise the test fixtures through the over-arching fixture control.

See other FitNesse resources on this site


Q&A

For support and to send questions, comments and ideas, join the DbFit discussion group on google groups. Comments are no longer enabled on this page.

289 Comments

  1. spacer gojko
    Posted October 20, 2008 at 10:43 AM

    Hi Matthew,

    for that, you should be using the standalone mode. DatabaseTest is designed to control the page. See fitnesse.info/dbfit:modes for more information

  2. spacer Matthew
    Posted October 20, 2008 at 11:50 AM

    Fantastic. Thanks.

  3. spacer Matthew
    Posted October 20, 2008 at 12:29 PM

    Hello Gojko,

    I have a little problem when running dbfit in standalone mode. I receive this exception in the table that initialises the connection in the DatabaseEnvironment fixture.

    “Missing method: public TypeOfResult dbfitDotFixtureDotDatabaseEnvironment(Type1 arg1) { } in class dbfit.fixture.DatabaseEnvironment”

    I dont quite know what this relates to, but I don’t really want to have to modify third party code.

    Matthew

  4. spacer Matthew
    Posted October 20, 2008 at 1:14 PM

    PS: In addition, if those method sigs are added, the code in the doTables method overidden by DatabaseEnvironment is never executed, do I have an incompatable version of dbfit with my current version of Fitnesse?

    I am using dbfit 1.1 and fitnesse release 20080812.

  5. spacer gojko
    Posted October 20, 2008 at 1:41 PM

    Matthew,

    the problem is caused by packaging an incompatible version of fitlibrary with fitnesse. I complained about this on the mailing list but nobody from ObjectMentor seems to be interested in fixing it. You should not be required to add those methods to plain fit fixtures, only to fitlibrary stuff. DatabaseEnvironment doTable should be executed, not doTables.

  6. spacer Matthew
    Posted October 20, 2008 at 1:55 PM

    Apologies, I did mean to say “doTable” not “doTables”. Having added instrumentation code to DatabaseEnvironment it seems that method is never run and the default environment never instantiated.

    !|dbfit.fixture.DatabaseEnvironment|informix|
    |connect using file|infrmxCon.txt|

    This is the first line of my fitnesse test page.

  7. spacer Matthew
    Posted October 20, 2008 at 2:22 PM

    I seem to have fixed the problem by adding an import statement to the page.

    !|Import|
    |dbfit.fixture|

    Have you any idea what might have been causing the problem?

  8. spacer gojko
    Posted October 20, 2008 at 7:27 PM

    Hi Matthew,

    when import is the first table on the page, then the normal fit page processing is used. when DatabaseEnvironment is the first table on the page, fitlibrary kicks in and since an incompatible version of FitLibrary is packaged with the latest fitnesse release, we run into problems.

  9. spacer Choipd
    Posted October 28, 2008 at 9:41 AM

    I am trying to use dbfit and fitserver(c++).
    each pages with dbfit or fitserver is testing well, but i couldn’t run the suite. Gojko, do you have any idea to run with these two difference architectures mixed?

    Thank you for listen to me.

  10. spacer gojko
    Posted October 28, 2008 at 1:25 PM

    Hi,
    I’m really not sure how c++ fitserver would support dbfit. .NET and Java are supported, C++ is not out of the box. I guess you would need to rewrite dbfit to run under c++.

  11. spacer Tim
    Posted November 5, 2008 at 3:48 PM

    Gojko,

    Is it possible to pass the table name of a query as a parameter?
    Example: SELECT 1 AS X FROM @tablename

  12. spacer gojko
    Posted November 5, 2008 at 3:50 PM

    if you can do that directly in the database, you can do it with dbfit as well. dbfit just passes queries to the db. if this is not possible in the db, you might use markup variables to parameterise queries (but this is then not dynamic in run-time), or create your own fixture that creates the query string dynamically.

  13. spacer Pete Johnson
    Posted November 10, 2008 at 4:16 PM

    Hi Gojko
    We’ve been having a great time at Halcrow Rail MIS with Fitnesse and DbFit and have come up with some productive ways to test our ETL code and SQL Server stored procedures. We’ve become huge fans.

    One question: we’ve been having an issue with SQL timeouts while running stored procedures on SQL Server 2005 using the Query fixture. Is there a way we can amend the SQL command timeout of DBFit? We’ve tried adding a “Connection Timeout” to our connect strings but this has no effect.

    Thanks for any help.

  14. spacer gojko
    Posted November 11, 2008 at 12:10 PM

    Hi Pete,

    not sure, how would you do it if it was not going through DbFit? would you use the connection string or change the way that the call is executed?

  15. spacer Anton
    Posted November 11, 2008 at 1:32 PM

    I can’t connect to my Derby db from Fitnesse.

    I use the following connection string
    !|dbfit.DerbyTest|
    !|Connect|jdbc:derby://localhost:1527/testdbfit;|

    I’ve started the derby networkServer.

    When I run my Fitnesse test I get the error below
    java.lang.Error: Cannot register SQL driver org.apache.derby.jdbc.ClientDriver
    ……………..

  16. spacer gojko
    Posted November 11, 2008 at 3:29 PM

    Hi,

    is the derby java driver jar in your test run class path?

  17. spacer Anton
    Posted November 12, 2008 at 9:38 AM

    Thanks Gojko, I copied the Derby driver jar to my Fitnesse installation and it solved the problem.

  18. spacer Tim
    Posted November 13, 2008 at 4:43 PM

    Hi, gojko,

    In SQL Server stored procedures, you indicated that you cannot validate the results of a RETURN call because the type is not known. In SQL Server, the return type of a stored procedure call is always an integer. Does this “rule” make it possible to treat the stored procedure call like a function so we can validate the return value?

    Great work on a great tool, btw!

    Tim

  19. spacer Tim
    Posted November 13, 2008 at 8:35 PM

    Is there a possibility of a config item that would define the special text to denote a check for a zero length string (or all spaces)? We often want to validate that an OUTPUT parameter has been set to a blank string and Fitnesse itself doesn’t allow this since a blank validation implies “no check.” I’m thinking something along the same lines as the special handling of “null.” It doesn’t necessarily have to be configurable, though this might address a future complaint that someone’s stored proc is actually returning the special text used to denote an empty string.

    Thanks again for a very cool tool.

  20. spacer Stephen
    Posted November 13, 2008 at 9:07 PM

    Hello,

    I just got a new machine. Loaded java (it has 5 and 6) and made sure the Path is correct (c:\Program Files\Java\jre1.5.0_12;c:\Program Files\Java\jre1.6.0_7). But I keep getting the following error:

    C:\Fitnesse\DbFit>java -cp lib\fitnesse.jar fitnesse.FitNesse -p 8085 -e 0 -o

    Exception in thread “main” java.lang.UnsupportedClassVersionError: fitnesse/FitN
    esse (Unsupported major.minor version 49.0)
    at java.lang.ClassLoader.defineClass0(Native Method)

    My old machine still works just fine. I did download the newest release.

    Thanks for the help.

  21. spacer gojko
    Posted November 14, 2008 at 11:34 AM

    Stephen, try running it with java6 instead of java 5

  22. spacer gojko
    Posted November 14, 2008 at 11:44 AM

    Tim,

    see fixed length string parsing on the query page, this might help check empty strings. regarding sql server stored procedures, I’m still unsure about how this might work. Do you suggest assigning a “return” stored procedure argument to every stored procedure?

  23. spacer Tim
    Posted November 14, 2008 at 1:38 PM

    gojko, I did try out the fixed length query capability earlier (and retried it today just to be sure):

    |set option|fixed length string parsing|true|
    !|Execute Procedure|usp_ValidateCartonLoadingDestinationForStart|
    |Build Lane|One Carton At A Time|Is On|ReturnLog?|
    |null|Y|N|100,@BuildLane,NULL|
    |BadDivert|Y|N|Invalid Divert. Refer Store Map Table for valid diverts|
    |BadDivert|Y|Y|’ ‘|

    @ReturnLog is declared as varchar(200) and is returning a zero-length string. I’ve tried ”, ‘ ‘, ‘(200 spaces)’ and all produce a failure when comparing. I don’t know if this is relevant, but the error message shows the quoted string (including the quotes) in the expected field, and just the word “actual” in the other field. Also, once you go to one space between the quotes, the error message shows one space between the quotes, no matter how many spaces you put between the quotes.

    I messed around with the stored proc, returning an explicit ‘ ‘, ‘Hi ‘, ‘Hi’ with corresponding quoted validation values in dbfit. None of them matched, so, I’m thinking that the “fixed length string parsing” setting isn’t actually affecting the comparison logic for stored procs (?). I pasted the line for “Set option” so I don’t think it is horked over. Is there anything obviously wrong with my approach?

  24. spacer Tim
    Posted November 14, 2008 at 1:43 PM

    gojko, I think I now understand the issue with a return value for a stored proc. You’re implying that a change like this would break every existing check since the line would now *require* a reference to |?|, right? Is it at all reasonable to think that you could make the presence of the |?| optional? If it is there, it compares against the return value and if not, treat the stored proc as if it had no return value.

    Sorry for just asking the question. I really should do a little code spelunking…

  25. spacer Linh
    Posted November 14, 2008 at 7:28 PM

    gojko, is there a way to escape the ‘|’ character in DbFit. I’m have a need to use this character in my query.
    Thanks.

  26. spacer gojko
    Posted November 15, 2008 at 2:30 PM

    Linh,

    use !- and -! to escape characters in fitnesse (and dbfit). you can find more about that in the project docs

  27. spacer ekkis
    Posted November 16, 2008 at 2:26 AM

    hei gojko,

    I found your software recently through a SQL Server Central article:

    www.sqlservercentral.com/articles/Testing/64636/

    and in attempting to follow it I’m running into trouble. I’m running against a SQL Server 2000 box and have the following code:

    !|Insert|DBFitTest|
    |EmpName|FavoriteColor|
    |Ian Curtis|NULL|
    |Peter Hook|Grey|

    to which I get:

    System.Data.SqlClient.SqlException: Incorrect syntax near the keyword ‘precision’.
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

    I do have connectivity to the database because I have queries that return values (including one that indicates the presence of the table)… and I also have the most recent (as of a couple of days ago) version of the software… I’ve looked at docs and the syntax seems right… what am I running into?

    1k thx – e

  28. spacer gojko
    Posted November 16, 2008 at 12:00 PM

    Ekkis,
    use SqlServer2000Test instead of SQLServerTest when running on 2000. that will give you most of the functionality with legacy meta-data extraction. SQLServerTest only works with 2005 onwards

  29. spacer David
    Posted November 17, 2008 at 1:56 PM

    Does DBFit support SQL 2008? I am getting the following error when I run the following query SELECT CONVERT(BIT, ISNUMERIC(’12′)) AS ‘result’:
    fitlibrary.exception.InvalidMethodException: Missing method ‘query’ with 5 argument(s).
    at fitlibrary.Method.FindFirst(Object theTarget, IdentifierName theIdentifierName, Int32 theParameterCount)
    at fitlibrary.FlowFixtureBase.FindMethod(CellRange theCells)
    at fitlibrary.FlowFixtureBase.FindRowMethod(Parse theRow)
    at fitlibrary.FlowFixtureBase.ProcessFlowRow(Parse theCurrentRow)

  30. spacer gojko
    Posted November 17, 2008 at 2:41 PM

    David,

    the error suggests that your table format is wrong (5 arguments for the query method). Can you send me the fitnesse page that you are trying to use?

  31. spacer ekkis
    Posted November 18, 2008 at 11:30 PM

    thank you (sorry for cluttering the board)! your product is AWESOME.

  32. spacer Bill
    Posted November 24, 2008 at 7:10 PM

    Gojko,

    I am having the same sort of timeouts as Pete Johnson above (gojko.net/fitnesse/dbfit/#comment-36523). In your reply to him (gojko.net/fitnesse/dbfit/#comment-36534), you asked “How would you do it if it was not going through DbFit? would you use the connection string or change the way that the call is executed?”

    In C#, I would do it by setting the CommandTimeout property of the Command object. As far as I know, CommandTimeout cannot be set through the connection string. (ConnectionTimeo

gipoco.com is neither affiliated with the authors of this page nor responsible for its contents. This is a safe-cache copy of the original web site.