Use a fixed test database name?

[Sam Minnee]

Hi everyone,

Currently the databases used for running automated tests are randomly
named and live only as long as the execution of a single test run.

This has the advantage that the same codebase can have tests running
multiple times in parallel, which may happen occasionally.

However, it has the following disadvantages:

* Test execution starts with about 20 seconds of database build time,
which if you're just re-executing a single test is a little painful.
* Your system gets gunked up with temp databases if tests end
prematurely.
* Your test runner needs database create/delete rights.

I'm beginning to wonder if the disadvantages outweigh the advantages.

Should we move to a system whereby the test database for a given
project has a fixed name? For example, could suffix "_unittests" on
the name of the regular database.

Under this model, we would still run DatabaseAdmin::doBuild() before
running the tests, but instead of generating a database from scratch,
it would merely alter the schema as required.

[Ingo Schommer]

Yeah, agreed - anything we can do to speed up the tests.
I've been running multiple tests in parallel mainly because they're
so slow, so reducing the startup time will make this less likely.
Can we relyably implement locking to avoid running
tests in parallel? We could run into the same problem
than "gunked up temp databases" with invalid locks, right?

-------
Ingo Schommer | Senior Developer
SilverStripe
http://silverstripe.com

Phone: +64 4 978 7330 ext 42
Skype: chillu23

[Sam Minnee]

> We could run into the same problem
> than "gunked up temp databases" with invalid locks, right?

Yes, we could; it'd be tricky to make reliable. Although we could
improving things by:
* using the filesystem for locking (which is less likely to flake out
than a database server)
* using register_shutdown_function to clear the lock, which will
still be called if a regular PHP error occurs.
* have a timeout on the locks, for example 2 hours, so that if a
stale lock was introduced we would clear it out eventually.

The database locks can be stored as individual files, TEMP_FOLDER/
testdb.lock.(sanitised dbname). The files could be be empty: the
expiry date can be determined by adding the lifetime to the filemtime
().

With that in place, we could then create a new test database if a lock
was found, except instead of using a random number as the database
name, increment them sequentially:

Main DB:
SS_mysite

Normal test DB:
SS_mysite_unittests

Failover test DBs:
SS_mysite_unittests2
SS_mysite_unittests3
SS_mysite_unittests4
SS_mysite_unittests5

We could delete failover test DBs on test completion, and garbage
collect ones whose locks are expired. In this way we could get the
best of both worlds.

This mechanism could be supported by the following set of
configuration options:

* Whether to allow creation of test databases (default=true, false
means that you need to create the test dbs yourself)
* Whether to allow the use of failover databases (default=true, false
means that the tests will instafail if you try to run them when
another site has lock)
* How many databases to leave persisent (default=1. 0=delete all
databases at test completion, 1=only leave the non-failover database,
N=let (N-1) failover databases persist)
* How old can locked databases be before we garbage collect them
(default=2 hours)

[Cam Spiers]

Your system gets gunked up with temp databases if tests end
prematurely.

I have experienced this and would appreciate something like this new proposal.

Cam