Testing Database - Small Reply

I found this on the Ovid’s blog.

First of all: this is quite a nice writing of something called the modern perl. Everybody who shouts that perl is a piece of shit should read it. YES, you can write a 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).

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

Recreating The Database (by TRUNCATE).

Running tests which use databases is a problem. Recreating a database for each test is a must, each test should start 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 the 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 which is fired at executing the TRUNCATE query. This trigger could do anything, including inserts into the 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). 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 in the PostgreSQL documentation. In the earlier versions there isn’t the ON TRUNCATE trigger, but only ON DELETE, ON UPDATE and ON INSERT.

Recreating The Database (by TRANSACTION).

Another idea is to do everything in a 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 a 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. That depends on the software that you write. Usually you cannot create a transaction inside another transaction normally. So if you have to test a function from a program, which starts and ends a transaction, you cannot perform another begin at the beginning and the rollback at the end, because there already has been commit from the function.

After such a 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 The 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 an application which uses the database. However you can easily create tests for checking the whole database structure including database functions written in PL/pgSQL, PL/Perl or PL/whatever. This is created by Dave Wheeler. Thanks to the pgTa you can create tests in pure SQL and run them inside your database, the output is compatible with the Test Anything Protocol, so you can use many different tools for analysing the output.