All Testers Should Have A Database

SQLite Manager and testing data files.

I recently was given a task to test some data files, created by one system, processed by another then sent back to the system of origin in a transformed state (data munged with a few extra columns and data, standard ETL stuff).

In my experience, when data leaves its system of origin it pretty much goes into the unknown, it passes a boundary into a foreign system, and sometimes a foreign culture. It may come from a company culture where testing is of the utmost importance and enter into a culture where testing in itself is a foreign concept, needless to say it will not get treated in the manner in which it is accustom too.

We must be careful of making too many assumptions, a 'csv' file that contains data means different things to different people and more importantly different systems.For example files created on a Windows server may not be encoded correctly for a UNIX machine, some developers insist that csv values are contained in "double quotes", others prefer to omit the same, and some systems just will not handle special characters, no matter how much encoding and escape characters you include....Any ways back to the task.

After being ask to undertake this testing task alarm bells rang,I  thought I needed to dust of my old awk skills, but then I remembered my data-warehouse days, and how easy it was to test data once it was actually in a database. I had a SQLite instance running on my machine from some RoR stuff that I was messing around with, and decided to use it to test the data files, after all it was just gathering (binary) dust on my Lenovo.

At this point I could of imported the file using the SQLite shell, but I wanted to search for a quicker way so I did some digging and found a cool Firefox plug-in called SQLite Manager.

In a nut shell, you install it, open it up in Firefox, point it to your csv files and hit import, field names (if you have a header line) and table name (based on file name) get created automatically, great for beginners and experts alike, we all like to save some time. Here is a quick guide on how to get it up and running, please note you need a running instance of SQLite before you can use this plugin, here are some very good install instructions (you dont need to install the rubygems, but you should use ruby...it increases your street cred).

Here is what my csv file looks like:

 1. Open up SQLite Manager, for the sake of this example Ill create a new database.

2. Once the database is created hit Database option on the top left, and go all the way down to 'Import' this will bring up the import wizard, you should be presented with some options, below I am importing a postcodes.csv and set the options to import the file, as you can see you can even import data via SQL and XML.

3.  Hit 'OK' and you should get an option to modify the import before it begins, as you can see SQLite Manager has already figured out the column names for you, here you can change the table attributes

4. After a while, depending how many records are in your file, the import will complete and you will be ready to start running your sql queries against the file, just hit the 'Execute SQL' tab and test away.

I hope this gives you an idea of how easy it is to set up your own database, and how useful it can be for testing, here ive given you an example in how you can use it to test data files. Maybe you are doing performance testing, you could perhaps use it to store results and compare metrics between runs and builds, and with the power of SQL you could get some really good reporting out of it.

I advise all testers (especially consultants) to keep your own database on your Laptop/PC, you never know when it will come in handy, and something like SQLite uses less resource than your browser! dont believe me...check your process status's!.