One of the biggest benefits to the tester from using database comparison tools is the ability to compare databases as part of a product upgrade test. Most, if not all, products with a database backend need to be upgraded at some point. When I say upgrade I mean that the end user will want to upgrade from an old version to the latest version of the product you are testing. Such an upgrade can involved updating the structure or data within the product’s database.
To this extent the software tester is going to be responsible for testing that product upgrade. Now you can approach this with some black box testing and completely ignore what is going on behind the user interface with the database. Or you can supplement this black box testing, get your hands dirty, and carry out some white box testing. For this you are going to need a little understanding of SQL and you may also need to know a little bit about the architecture of the database your application uses.
I can guarantee you that if you put some effort into this white box testing approach you’ll find more bugs. And with a failure during a database upgrade likely to cause some form of data corruption for the end users, finding more bugs has to be a good thing here. So as testers we’re faced with a difficult and technically demanding area of testing yet the consequences of getting this testing right are likely to prevent significant issues impacting the users (that is the potential for the corruption of their data that they store in your application).
The basic approach to running a comparison test like this consists of…
- Start with the old version of the product running with the old database structure and data
- Back up or copy the old database structure and data *
- Upgrade your application
- Back up or copy the upgraded database structure and data *
- Install a clean version of the application
- Back up or copy the clean database structure and data *
* – It’s usually easier to take two independent backups here. One backup with just the database structure and a second backup that covers the database structure and the database data.
Once you’ve completed this you should have the test artefacts listed below. If you’ve used a tool like mysqldump then these artefacts will essentially just be text files that can be compared with a standard text difference tool (like windiff):
- Old database structure
- Old database data
- Upgraded database structure
- Upgraded database data
- New database structure
- New database data
Once you’ve completed this there are a number of comparison tests you can employ now. If you’ve used a tool (like mysqldump) that creates the database backup/dump in text format then you can now compare these artefacts with a text difference tool, or you could employ specific database comparison tools (see below).
The easiest, and probably most useful, comparison test is the database structure comparison. However, all of the following will give you an important insight in to the success (or failure) of the upgrade process…
Upgraded database structure comparison
Take upgraded database structure and compare this structure against the new database structure. In 99% of cases these should be identical. If an upgraded database structure is different to the structure of a database from a cleanly installed new database then you’ve got questions to ask your development team
New database data comparison
With the data from the old database compare against the data from the upgraded database. You will expect to see differences here but you may find some that you wouldn’t expect to see. This can be a difficult comparison to make, depending on the complexity of the database, so it might be worth working with the development team whilst running this test.
New database structure comparison
This comparison should compare old database structure with the new database structure. This will show up the expected differences that have been implemented for the new database. This list of differences should be checked with the development team to ensure only expected changes are evident in the new database structure
The above tests can be executed with freely available text based comparison tools (like windiff). However, if your database structure has any degree of complexity to it, it is usually advisable to employ a dedicated SQL difference tool like SQL Delta, SQL Data Examiner, dbForge Data Compare, AdeptSQL Diff or SQL Data Compare . Products that have the capability to compare two different database (including both data and database structure) give the tester an easy way of checking changes between databases.
The other big advantage of employing SQL comparison tools is that they are very helpful when it comes to keeping test environment mirroring a live environment. That’s a discussion for another day though.