[almighty] Database test patterns

[Thomas Mäder]

Hi folks,

in PR https://github.com/almighty/almighty-core/pull/294 I have used the 
pattern to roll back to execute tests in a transaction which is rolled 
back at the end of the test. Thus, the test is guaranteed to not change 
the database. There were couple of objections to this patterns, which 
leads me to seek some convergence on the topic of db tests.

Every test has certain preconditions. For example, when I test creation 
of an object with a given ID, the precondition is that this ID does not 
yet exist in the DB. If the preconditions are not met, the test cannot 
properly run and thus does not yield any information about the code it 
is supposed to test.

To solve this problem, there are two approaches. The first one is to 
program the test without having preconditions. For example, instead of 
creating an object with the ID "42" in the above case, the test could 
try to use a UUID as the ID for the object it creates. Since there will 
be no collision with previous runs, there is no precondition that can be 
violated.

The second one is get the system into a well know state before the test 
runs. One of the patterns to do this is to always start with an empty 
database. Alternatively, the test setup could contain code to establish 
the precondition. In our example, the setup would delete the object with 
the ID "42" (if it exists) in order to make sure that the create call 
can be tested.

One of the problems we have is that we run unit often against a local 
database while developing. Even if the tests are written to work 
repeatably, we don't want the test database to be trashed every time nor 
filled up with useless test data. So ideally, tests should clean up 
after themselves.

The rollback pattern keeps the db from filling up, but can't prevent 
failed preconditions. Another pattern is to setup objects used in the 
test before the test and to remove them after the test. However, this 
doesn't really work either: tear down may fail and leave objects in the 
database. The next run of the test may then fail.

I think the only reliable way to write db tests is this:

setup

     delete all objects the test expects NOT to exist

     delete all objects the test expects to exist (because they may not 
be in the correct state)

     create all objects the test expects to exist.

Test

     use the objects from above

tearDown

     remove all objects created in setup or the test

What are your proposals on how to structure db tests?

/Thomas