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
- 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.
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.
- 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
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.
|
|
fitnesse.org makes references to JdbcFixtures and provides the ability to execute an arbitrary block of PL/SQL with ScriptFixture. Unfortunately, Uncle Bob no longer provides any of that code.
Would it be possible for DbFit to add the ability to execute blocks of sql?
This would help when dealing with complex stored procedures (which, I admit, are a bad thing in general).
Hi Barrett,
Use |Execute|Begin …. End;| to execute any pl/sql code. You can use !- and -! to do a multi-line statement. the latest beta version also allows you to escape the :param mapping to bind variables so that you can use even that in your code.
Gojko,
I’m using the version of DbFit from 2008-03-08.
I guess I’m missing something fundamental, because I’ve tried to get standalone mode to work, and I can’t seem to get it to work.
I have a set of stored procedure tests organized in flow mode on five different pages, plus a SetUp page. The setup page has the “dbfit.SqlServer200Test” fixture on it, with the connection string to get to the DB I’m testing. This page inserts a bunch of test data that the subsequent tests use for verifying the stored procs work. When I execute it in flow mode, all of the tests work.
However, when I follow what I think are the instructions for changing it to standalone mode in the “dbfit:modes” page by changing teh SqlServer200Test fixture to something like the below,
|import|
|dbfit.fixture|
!|DatabaseEnvironment|sqlserver2000|
|Connect|Data Source=etc…;|
along with:
!|DatabaseEnvironment|
|rollback|
on the TearDown page, I get several exceptions, the first of which is one on the Set Parameter fixture which says that it “Couldn’t cast Set Parameter to Fixture”. I subsequently tried to do this:
!|dbfit.util.Export|
|dbfit.fixture|
right after using the DatabaseEnvironment to connect. When I do that, I get a bunch of exceptions about “Type ‘query’ could not be found in assemblies”.
Obviously, I’m doing something wrong here, but I don’t know what.
What I really want to do, though, is simply get the actual IDbConnection object initialized by the SetUp page, and pass that to, or get access to it in, my fixture. Is there a way to do that? The object under test takes an IDbConnection as a constructor parameter, and that would make it simple to test if I could just pass in the one used by the pages.
Brad
Hi Brad,
there was a bug with SetParameter in that release, it has been fixed since. Get the latest beta from dbfit-dotnet-20080813.zip.
If you want to mix your fixtures with DbFit, standalone mode is the way to go. Then read the current environment from dbfit.DbEnvironmentFactory.DefaultEnvironment.CurrentConnection
Great, I’ll give it a shot. I guess my changes are the right ones then, aside from the “export” thing?
Does that version of DbFit require any specific version of Fit or FitNesse?
Again, thanks for putting DbFit together, and for the help.
Brad
@Brad,
the latest beta build is a pre-release of 1.1, linked with the latest versions of Fit.NET and FitNesse. If you need the set parameter fixture but do not want to integrate with the latest releases, just create one in your own namespace and use it. The source is here.
Export effectively cancels the effects of import; so FIT will not automatically load fixtures from the dbfit.fixtures namespace after that table.
No, upgrading is OK. I just didn’t want to install the new DbFit into the old FitNesse wiki, and then wonder why it didn’t work.
That just means I have to go find them, which I recall wasn’t that straightforward a task the last time I did it…
Brad
Hi Brad,
Find what? Fitnesse (wiki) is on fitnesse.org. .NET fit runner is included with DbFit. See dbfit beta 1.1 for beta downloads.
When I originally set up FitNesse, it was somewhat less than easy to find all the pieces I needed, until I used your “Getting Fit with FitNesse” document. That probably says more about me than the stuff I was looking for, but that’s OK.
Hi Gojko,
I hope you can help. I have recently been looking at kicking off DbFit from the command line. I have located an article on a Fitnesse support site giving some instruction on how to acheive this, and so far so good. I can execute the test suites, however, none of the tests are successful, they only produce exceptions. After looking at the resulting HTML I found all tests to conclude to the same exception, ‘Could not find fixture: …’, this is the same for all fixtures, Query, Execute Procedure etc.. although this is not an issue when the tests are kicked off from a browser. So I must be missing something? Bellow is command I’m executing from the command line. Do I need to reference any other jar’s in the class definition?
java -cp fitnesse.jar fitnesse.runner.TestRunner localhost 8085 FrontPage -html TestResults.html
Thanks in advance
Will
Hi Will,
you need to add the dbfit and filtlibrary JARs to the classpath as well. You’ll find the appropriate versions in the dbfit-complete package of the latest release.
Thanks Gojko, I’ll give that a go, thanks for the speady response and a fantastic tool!
Hi Gojko,
I am still experienceing the same issue, even though I am including all of the appropriate JARs in the class path. Bellow is the line I am now executing.
java -cp lib\fitnesse.jar;lib\fitlibrary.jar;lib\dbfit-20080822.jar fitnesse.runner.TestRunner localhost 8085 FrontPage -debug
Thank you again for your help.
Will
Hi Will,
can you send me the source of your FrontPage, root page (/root) and the output that you get by running TestRunner. You are missing the db driver jar (don’t know which db you are using). Send me the files by e-mail
Hi Gojko,
I am a beginner.I recently only started using DBFit. Its a wonderful tool. Congrats for that. I am able to execute queries or procedures correctly when i include the Dot Net files. But when i try to include the java files, it shows exceptions. It happens for both normal queries and procedures. I couldn find the reason. I don know if its because of any crazy mistake of mine. But Please help me out. Thanks in advance.
Here is the result which i got :
classpath: lib/*.jar
dbfit.OracleTest
Connect
java.sql.SQLException: Listener refused the connection with the following error: ORA-12514, TNS:listener does not currently know of service requested in connect descriptor The Connection descriptor used by the client was: localhost at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at
I Gokul, your error suggest that the connection string is wrong. Use the following form for Oracle/Java
|Connect|host:port|username|password|database sid|
See acceptance test set-up for an example.
Hi Gojko,
What is database SID? And my database runs in 127.0.0.1:8080.
Pl help.
your dba has to tell you that.
hi,
Gojko, i am just using it in my laptop and my database is oracle express edition 10g version. I am using DBFit jus to learn the concepts and the tool. What i want to know is, what is the full form of SID? How can i find it for my Database?
In that case, it is most likely XE. Use this command to connect:
!|dbfit.OracleTest|
|Connect|localhost:1521|username|password|XE|
the web console is running on 8080; your db is probably running on 1521
Hi Gojko,
I’m using dbfit with SQL Server 2005 and I’m trying to execute a stored procedure that takes no parameters. This stored proc is just used as a getAll to fill a list. I am not having any success with “Execute Procedure” and can not find any information on how to run a stored proc without parameters. Our company would like to be able to test our stored procs and I have not been able to get this running.
Thank you
is your stored procedure returning data or not? if it is, then use something like this:
!|Query|getAll|
|col1|col2|col3|
…
if it does not return anything, you can do
!|Execute Procedure|getAll|
Hi Gojko,
The stored procedure is returning multiple rows of data. (in this case the proc returns 19 rows)
I used
!|Query|GetAllEmployers|
|groupid|name|
where I am just specifying the columns, and the results are as follows:
Assertions: 0 right, 19 wrong, 0 ignored, 0 exceptions
my stored proc is below:
__________________________________________
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[GetAllEmployers]
AS
SET NOCOUNT ON
SELECT groupid, [name]
FROM groups
ORDER BY [name]
SET NOCOUNT OFF
________________________________
Am I missing something?
Thank you
do you want to test the output or not? if you are not concerned with testing the output, just with executing the procedure, use Execute procedure. to test the output, put the expected output in the table below column headings.
Hi Gojko,
Thank you for your help, I saw the error in my ways…
I do have another question. Does dbfit handle tables that store data using “uniqueidentifier”? I tried to run another stored proc and received the following error:
“System.ApplicationException: Can’t parse system.Guid because it doesn’t have a static Parse() method…”
Will dbfit support system.Guid/uniqueidentifier?
thank you
Hi Elizabeth,
yes, dbfit supports guids. See DbFit FAQ and search for GUID. It would probably be useful to read the dbfit reference documentation if you plan to use it.
Everything is working for me now. Thank you so much for your help. I really appreciate it.
Hi Gojko,
DBFit has been really helpful but I’m having some difficulty managing transactions from my test pages. I don’t want some queries to be involved in a transaction so I thought I could run the tests in stand-alone mode to prevent this. Following the steps from the online documentation, here’s what I did in wiki markup to check if a test was involved in a transaction. I’m using version 1.0.2008.310 of the dbfit.dll
|import|
|dbfit.fixture|
!|DatabaseEnvironment|SQLSERVER|
|Connect|server=MyServer; database=master; Integrated Security=SSPI;|
The transaction count should be 0, but this test always fails and returns a tran count of 1.
!|Query|select @@TranCount x|
|x?|
|0|
Am I doing something wrong or is there a way from the Fitnesse test page, to explicitly prevent queries from being run inside a transaction?
Thanks,
Jon
Hi Jon,
dbfit will always run in a transaction, but in standalone mode you can include it in a wider transaction. You can commit and rollback from DbFit, though. To run a query without a transaction, you’ll have to implement a different dbenvironment instance that does not start transactions. what exactly are you trying to achieve with this, maybe there is a different solution?
Hi Gojko,
Thanks for the quick reply. The issue for me is one of DB resources. For example, let’s say on my test page that I want to compare the results of several procedures against each another. However, each procedure is expensive and runs for a long time (e.g. it contains multiple joins across very large tables). The concern I have is that if the entire Fit test page is executed in a single transaction, then all those procedures will be included in the transaction which will create memory and contention issues at the database level.
Since I’m new to DBFit, maybe I don’t understand how transactions work at the test page level. If each test must run in a transaction, is there a way to make them run in separate transactions? Does each table on the page belong to a separate transaction or does the whole page run in a single transaction?
Thanks,
Jon
Hi Jon,
you can use commit or rollback commands to end a transaction and start a new one. use store query to save results of a query and detach it from the current transaction, and compare stored queries to verify it later.
Hi Gojko
Do you have any experience in running tests using the dotnet testrunner? And more specifically, the output format? Whatever I do, the output (saved to a file) is always html formatted. And if I try to reformat it with the FormattingOption to xml, this output is just pretty much just placed within -tags which is about as useful as the html is in the first place.
So, I suspect that I’m not really getting the fitnesse “raw” output format from the testrunner… Any ideas?
(testrunner.exe -results TestResults.dat localhost 8085 SomeTest.SomeOtherTest)
Thanks//J
Hi Johan,
Sorry for the late reply. I used TestRunner a while ago, and then the .NET test Runner always used the same output format. You can reformat it using the java fitnesse runner. So something like this:
C:\services\FitNesse> dotnet2\TestRunner.exe -results c:\temp\fitnesseres.txt localhost 8888 TicketReviewTests.WinningsRecordedCorrectly
C:\services\FitNesse> java -cp fitnesse.jar fitnesse.runner.FormattingOption c:\temp\fitnesseres.txt xml c:\temp\fitnesseres.xml localhost 8888 TicketReviewTests.WinningsRecordedCorrectly
Maybe the functionality changed meanwhile, but I doubt it.
Hi Gojko,
I’m currently evaluating dbfit (version of 2008.08.22) for our testing purpose. I created few tests, bundled them into test suites and been able to run them successfully. All our FitNesse files reside on a network drive but Fitnesse is run localy.
When I use my computer, everything works fine. When I use my collegue computer and try to run any tests, I get the following error.
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.Exception: OCIEnvCreate failed with return code -1 but error message text was not available. at System.Data.OracleClient.OciHandle..ctor(OciHandle parentHandle, HTYPE handleType, MODE ocimode, HANDLEFLAG handleflags) at System.Data.OracleClient.OciEnvironmentHandle..ctor(MODE environmentMode, Boolean unicode) at System.Data.OracleClient.OracleInternalConnection.OpenOnLocalTransaction(String userName, String password, String serverName, Boolean integratedSecurity, Boolean unicode, Boolean omitOracleConnectionName) at System.Data.OracleClient.OracleInternalConnection..ctor(OracleConnectionString connectionOptions) at System.Data.OracleClient.OracleConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.OracleClient.OracleConnection.Open() at dbfit.AbstractDbEnvironment.Connect(String connectionString) in D:\work\dbfit\impl\dotnet\src\environment\AbstractDbEnvironment.cs:line 65 at dbfit.AbstractDbEnvironment.Connect(String dataSource, String username, String password) in D:\work\dbfit\impl\dotnet\src\environment\AbstractDbEnvironment.cs:line 59 at dbfit.DatabaseTest.Connect(String dataSource, String username, String password) in D:\work\dbfit\impl\dotnet\src\DatabaseTest.cs:line 29 — End of inner exception stack trace — at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams) at fitlibrary.Method.Invoke(Object[] theParameters) at fitlibrary.Method.Invoke(Fixture theFixture, IEnumerable theCells) at fitlibrary.FlowFixtureBase.ExecuteMethod(Method theMethod, CellRange theCells) at fitlibrary.FlowFixtureBase.ProcessFlowRow(Parse theCurrentRow)
Any ideas?
Patrick
Hi Patrick,
there seems to be an OCI (oracle client) problem in your colleague’s computer. check if the oracle client is properly installed and set up there. possibly try to connect to the target database from your colleague’s computer using sqlplus and ping it with tnsping to troubleshoot errors.
Gojko,
Thanks for the quick reply.
My collegue’s computer has the same Oracle client as my computer (and we are both able to connect to the target Oracle database using SQL*Plus, TOAD, Oracle Forms, SAS,…) So I guess the problem is something else.
This brings up another issue; testers might not necessairly have an Oracle client installed on their computers. If that’s the case, do I understand that our only option is to use the Java Test Runner? (I created a test that uses the java test runner and it works fine on both computers.)
Patrick
The problem might be with privileges/paths to the MS Oracle ADO.NET driver, so I would suggest checking that as well (or creating a simple example that uses System.Data.Oracle client to connect to the database from his machine). Regarding the requirement for the OCI client — MS Oracle ADO.NET driver requires that. You have two options to lift that: one is to use the java test runner if you can; another is to have a centralised computer for QA to run the tests, and then have testers access that fitnesse remotely using a browser.
Gojko,
I’ve had some problems creating expected results that number in the 500 to 1000 rows. When I try to save the results IE works for a while then it will give me an error and my results aren’t all saved. Then I can’t open the test anymore, unless I reduce the number of cases I’ve created. Is there a way around the limitations.
Thanks
Stephen
Hi Stephen,
You can increase the amount of memory available to FitNesse in the start.bat/sh. Google for -Xmx java option.
Hi Gojko,
How to include a new option in properties? Consider i want to create a new option called as “Report”, which should have the report of the test executed. The report can be in html format itself or pdf format. And it has to be stored in some destination every time this “Report” button is clicked. Please help me out with this. Thanks in advance.
Hi Gokul,
you’ll have to change fitnesse code to do that. not sure exactly how you should do that, but try to get some information from objectmentor or on the fitnesse mailing list.
Hey Gojko,
Thanks for the swift response. Oh what is that objectmentor and mailing list? where do i find those? Also i need info about test suites please.
ObjectMentor (http://www.objectmentor.com) are the guys who built fitnesse. Mailing list is fitnesse on yahoo groups. Test suites for DbFit are also managed by FitNesse.
My last reply vanished when I hit ‘Submit Comment’… 8^(
I am having problems getting dbFit to work with .Net ans SqlServer.
I figure it can’t be a major issue because select statements and tests work fine.
1) Create and drop statements APPEAR to work but the tables are never created or dropped.
2) Stored procedures, Inserts and updates generate an error message:
System.Data.SqlClient.SqlException: Incorrect syntax near the keyword ‘precision’.
!|Execute Procedure|CalcLength_P|
|name|str length?|
|mika|4|
|paradajz|8|
The stored procedure works fine thorugh SQL Sever Management Studio.
Any ideas?
Hi,
which version of SQL Server are you using? can you send me the whole page (with set-up and tear-down parts) to gojko -at- gojko.com
We are using SQL Server 2000 in the development environment. If it makes a difference, our QA and production environment use SQL Server 2005.
I sent the page code to you in an email. Thank you for the help!
Use SqlServer2000Test instead of SqlServerTest for 2000.
Hey Gojko,
I tried running “folderrunner” as per the syntax given.
I executed
“java -cp fitlibraryRunner.jar fitlibrary.runner.FolderRunnerUI c:\tool\dbfit-complete-20080310\FitNesseRoot\HelloIndia c:\tool\dbfit-complete-20080310\FitNesseRoot\results”
This is the error msg which i got:
java -cp fitlibraryRunner.jar fitlibrary.runner.FolderRunnerUI c:\tool\dbfit-complete-20080310\FitNesseRoot\HelloIndia c:\tool\dbfit-complete-20080310
\FitNesseRoot\results
Exception in thread “main” java.lang.NoClassDefFoundError: fitlibrary/runner/Fol
derRunnerUI
Caused by: java.lang.ClassNotFoundException: fitlibrary.runner.FolderRunnerUI
at java.net.URLClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClassInternal(Unknown Source)
Can you please let me know where i am going wrong?
Hi Gokul,
absolutely no idea. I’ve never used fitlibrary folder runner. try to ask on the fitnesse mailing list, maybe someone will have a better idea.
Hello Gojko,
I am having a little trouble using dbfit as part of a larger fixture structure.
I want to test a legacy system that uses a database by injecting some test data into said DB, running the system batch processes then comparing the data in the DB with what I expect it to be.
Now using Fit tables I was thinking the only possible way to model this test system was to use a workflow fixture, the doFixture, and have it return a DatabaseTest fixture whenever I want to processes SQL requests.
However when I expect say some SQL query to be coloured by fit/fitnesse the tables and all the cells in them are greyed out and counted as ignored. Is this because DatabaseTest is infact based on the doFixture itself and the original fixture code was never designed to allow workflows to create others?
Matthew