A computer application that uses information stored in a database is a Database Application. Most applications nowadays require a database, either flat-file, relational databases, built-in or stand-alone programs. Some examples are the Contacts Manager in your e-mail client, web stores, online telephone directory, etc.
A Database Application consists of a database (and may have Stored Procedures), a layer of code (in a container application) and a graphical front end. Testing such an application as a whole is a very difficult task. Locating the source of a problem on identifying one can be time consuming with so many components in the picture. Many database fields are never displayed in a visual interface and in some cases derived values are displayed. This makes manual testing more difficult. Also, there is just no way to visually inspect all the database fields and do it frequently to identify problems before they bubble up. Hence, as always, we Unit Test the database operations in an automated fashion!
As an example, let us look at the user registration piece of a web application. A new user creates an account by entering a username, password, some personal information and creates an account. On the database side, one or more records are inserted to register this new user. In addition to information entered by the user, a few other fields are also entered by the application like dates, type of user, etc. These fields may not be displayed in any interface, so visual inspection of this piece is not possible. Also, a simple feature like this may not always figure in the radar for regression testing during every release and yet a minor change could result is some vital field, used in marketing reports and analytics, not getting set in the database. To make things worse, these reports may only be generated every quarter, so it hits you pretty hard to find out that your database component was not working correctly for three months. To avoid this we implement automated tests.
How do we Test a Database Application?
The basic idea is to use the application feature to write stuff to the database and then verify the values written to the database by querying it. Visual Inspection is a good first step, but it is hardly sufficient. In an application, the values entered in the database and the queries may be created dynamically, it is very difficult to manually test this every time. Testing on finishing development or very late in the development life cycle is of little use, finding and fixing problems at this stage can be very expensive. A good strategy is to test the database mapping, incoming and outgoing data, stored procedures, functions, default column values, etc and to unit test these components. So a process which involves ‘Create Test – Test – Fix – Test – Stop on Pass’ is the right approach.
Database tests should be performed in an isolated environment – the tests have their very own databases. A process (other than the test) should not be able to alter the database. This way we know the state of the database and can rely on the results of the tests. Each individual test case should perform a clean up after itself, reset the database state, so the next test is not influenced by the results of the previous test.
A Database Test consists of the following main steps:
1. Data Setup – basically, here the test gets to know the database and so can infer a Pass/Fail status from the test result. The setup could be as elaborate as creating the database, populating it with seed data and loading test data or we could simply, during minor test cycles, delete (reset) values in a table (or all data in a specific context). This step sets up and establishes the database state.
2. Test Execution – here we execute our tests using a test framework like DbUnit. There are other database testing tools too, but we’ll look at DbUnit in this article.
3. Pass/Fail Verdict – the test decides the outcome by looking at the values in the database tables. Testing frameworks provide easy programmatic ways to do this which work much better than visual inspection.
Now let’s go through a quick test using DbUnit. A simple test extends the abstract base class DatabaseTestCase and implements methods getConnection() and getDataSet(). getConnection connects to the database and getDataSet defines the data we expect to see on test complete. Check out this blog entry for the sample code and steps. DbUnit also provides neat ways to define your data in xml and an ‘equals()’ method to compare tables.
And finally, a short paragraph on using DbUnit and iBatis to build a flexible and scalable database testing framework. I developed this framework to test an application that read data from files and wrote them to different databases. There were no unit tests and we were way past the unit testing phase. So we needed end-to-end functional tests. The real challenge was to build a framework where the tests could be modified easily with the (frequent) changes to business rules, file formats and destination tables. The tests consisted of 1 Test Case class file and were data driven. A single Test Case used a set of input data files to load the database and check the results using another set of expected result sets files. The piece that tied the Test Case and the data sets was iBatis. The connection parameters and the queries to set up the database for the test and check the results of the test were defined in XML files. A test used two or more XML files during execution. It created an iBatis SQLMapClient using the SQLMapConfig XML file and performed database operations based on the queries in the other XML files. iBatis let’s you use plain SQL in the XML files. So the database guys could also easily update or create test cases! This DbUnit – iBatis combination kept the framework very flexible and changes to business rules, application did not mean dumping entire test cases, just changes to the XML configuration files.
Recent Comments