A situation has come up at work where I want to use DbFit to test some business logic that is stored in stored procedures. This is part of our company’s overall effort to reduce technical debt and to get all parties (Product, QA, Dev) talking the same language about our product’s features.
DbFit provides a lightweight and quick way to test this logic. One of the many benefits of DbFit is that, if you connect to the database in flow mode, DBFit will do transaction management for you. The DbFit documentation states that when you are in flow mode, “the current transaction is automatically rolled back at the end of the page”. The alternative to flow mode is standalone mode. In standalone mode, you need to specify when you want to commit or rollback the transactions (using the DatabaseEnvironment fixture). In both flow mode and standalone mode, the transaction automatically starts when you connect to the database.
As I was building my DbFit tests, I realized I needed to set up some test data beforehand in the database so that the stored procedure could operate on the test data. In addition, I would need to delete this data when my test completed so that the test did not leave a footprint. In my case, I needed to set up a full customer account. To do this, about 16 tables need data inserted into them. While I could add all the necessary sql statements to the DbFit test in order to add this data, my group and I had previously created Fit sequence fixtures that we use in other areas that take care of setting up and tearing down a test customer account. These Setup/Teardown fixtures were already in use in the more “traditional” parts of our FitNesse test suite where the bulk of test pages have been created.
At this point, I am feeling pretty good. I have a Fit fixture library that can be used to set up a full account and I have DbFit tests that can operate on the test data via the respective stored procedures under test.
However, using database aware Fit fixtures and DbFit fixtures on the same page opens up a slightly thorny issue. The DbFit tests were originally created to connect in flow mode in order to take advantage of the built in transaction management. The Fit sequence fixtures I used also created transactions. As a result, it became very important for me to know what DbFit considers the “end of the page”.
Consider the following flow of my test. In this example, each line also has a reference to what transaction the statements will run in.
MyAwesomeDbFitTest.SetUp
DBFit: connect to database in flow mode (TRANSACTION 1)
DBFit: set up a small piece of test data (TRANSACTION 1)
Fit Fixture: set up full test customer (TRANSACTION 2)
MyAwesomeDbFitTest
DbFit: run queries, execute stored procs, other testing (TRANSACTION 1)
MyAwesomeDbFitTest.TearDown
Fit Fixture: teardown the full test customer (TRANSACTION 3)
If DbFit considers the test page “the end”, I am ok. The DBFit transaction will be rolled back and will not collide with the statements running in Transaction 3 when the Fit fixture is looking to teardown the test account in the database.
However, what I found is that DbFit considers the TearDown page (whether it is a TearDown page for just that page or a TearDown page at the Suite level that is used for each test) as part of the test and thus my overall test started to create blocks in the DB. Specifically, TRANSACTION 1 was blocking TRANSACTION 3.
As I wondered how long it would be before the DBA police came to my door and revoked my access rights due to the blocks my tests were creating (it was all in a DEV environment, I swear!), I started to think about what I could do to solve this issue. I thought of two options:
1. Port the Fit Fixture setup/teardown code to sql statements that could be executed as DbFit fixtures. This was quickly discarded due the fact that I am not keen on maintaining two copies of code that do essentially the same thing.
2. Drop into standalone mode for the DbFit tests so I can maintain a tighter level of control as to when the transactions rollback/commit.
After experimenting with option 2, I found that the following test flow will allow for the transactions to commit/rollback as needed and not create any blocks.
MyMoreAwesomeDbFitTest.SetUp
DBFit: connect to database in standalone mode (TRANSACTION 1)
DBFit: set up a small piece of test data (TRANSACTION 1)
Fit Sequence Fixture: set up full test customer (TRANSACTION 2)
MyMoreAwesomeDbFitTest
DbFit: run queries, execute stored procs, other testing (TRANSACTION 1)
MyMoreAwesomeDbFitTest.TearDown
DbFit: issue rollback statment via the DatabaseEnvironment fixture (TRANSACTION 1)
Fit Sequence Fixture: teardown the full test customer (TRANSACTION 3)
After making the necessary tweaks, the tests ran fine. However, I still have concerns about the maintainability of this approach. In order for other developers to leverage this pattern, they need to understand when the transactions are being created and they need to explicitly manage the transactions. This is not optimal. Ideally, a FitNesse page will contain only one type of database aware fixtures, DbFit or homegrown Fit fixtures. By doing this, transaction management can be pushed to the background and the developer can focus on test writing.
In an ideal world of my own making, another DbFit fixture would be available called QueryUsingFile (this is in addition to the free beer that would be present in this ideal world as well). It would allow me to specify a path to a file and would execute the SQL statements within the file. By doing this, I could organize a set of scripts to be used by any DbFit test and I could keep the test page code to a minimum. DbFit already has a ConnectUsingFile option so it seems a QueryUsingFile fixture would not be a big leap.
If I get a chance, I’ll post a sanitized version of the DbFit test that illustrates the pattern above.