Testing Database – Small Reply

On Ovid’s blog I found lately this entry.

First of all: this is a quite nice writing of something called “modern perl”. Everybody who shouts that perl is a piece of shit should read it. YES, you can write nice looking code in perl. Code that is maintainable and is easy to read. And is objective (a kind of somewhat different langauge syntax but who cares. Each language is different and has different class definitions – consider Ada, Perl, C++ and Python). Perl is not so bad.

Some ideas about the testing solution. Of course this is PostgreSQL .

Recreating database (by TRUNCATE).

Running tests that use databases is a problem. Recreating a database for each test is a must, each test should start with at the same starting position, with the same starting database. Or at least there should be the possibility of easily finding out what the initial database state really was (but explicit starting database is much better and easier to implement).

Instead of recreating database for each test, Ovid uses Truncate to delete data from all tables, so there won’t be the need of dropping them and recreating. That is nice unless there is a trigger ON TRUNCATE that is fired at executing the TRUNCATE query. This trigger could do anything, including inserts into currently truncated tables. Of course you can assume that there are no truncate triggers but such assumptions are usually dangerous (premature assumption is the root of almost all evil – the rest evil is caused by premature optimisation) Very soon you will have the ON TRUNCATE trigger in some database and of course you will forget to change the testing engine so everything will be messed up. No such mess with recreating database. More on the ON TRUNCATE trigger can be found here http://www.postgresql.org/docs/8.4/interactive/sql-createtrigger.html. In the earlier versions there isn’t the ON TRUNCATE trigger, but only ON DELETE, UPDATE and INSERT.

Recreating database (by TRANSACTION).

Another idea is to do everything in transaction and perform rollback at the end of test. This is great in PostgreSQL as all queries can be done transactional (YES!!! DDL queries too – thing that I suffer from at my daily work with Oracle). So creating an index or normal table or dropping a column in a table is transactional too.

This solution is quite nice but there are some things that cannot be done transactionally (but that depends on the software that you write). Usually you cannot create a transaction inside other transaction normally. So if you have to test a function from a program that starts and end a transaction, you cannot perform another begin at the beginnign and the rollback at the end because there already has been commit from the function. After this test the database can be quite different so before another test you have to delete all those changes. Dropping the database and creating from scratch seems to be the best solution. And of course you cannot assume the ordernig of the tests, so normally you cannot ensure that after the test A there will be run test B.

Recreating database (from scratch).

Is this the only solution? Well… in my opinion this is the only reliable and easy to prepare solution for tests.

Testing PostgreSQL internally.

For PostgreSQL there is another nice way of testing databases. Not very useful for testing application that uses the database, but you can easily create tests for testing whole database structure including database functions written in PL/pgSQL or PL/Perl or PL/whatever. This is created by Dave Wheeler (BTW thanx Dave). Thanks to pgTap you can create tests in pure SQL and run that inside your database, the output is compatible with TAP (stands for Test Anything Protocol) so you can use some tools for analysing the output.

Bookmark It

Related posts:

1. Unit Test Your Database. I observe the endless war about TDD. On one side...
2. The Importance of Database Testing Basic Mistakes in Database Testing There is a huge number...
3. How to Allow For Max 4 Rows in Update (PostgreSQL) The Problem Question found on the net: how to restrict...
4. Trying to Use Database in Python Why the f**** there is nothing like DBI in Python?...
5. How to Ensure There is One Row With a Value The Problem Let’s imagine a business requirement that there should...
6. Why Use ORMs? Why Use ORMs? ORM aka Object Relational Mapping is a...