0

When running our suite of acceptance tests we would like to execute every single test on a defined database state. If one of the tests would write to the database (like create users or something else), it must of course not affect later tests.

We have thought about several options to achieve that, but copying the whole database before every single tests does not seem like the best solution (thinking of possible performance issues).

One more idea was to use MySQL transactions, but some of our tests cause many HTTP requests, so different PHP processes are spawned and they would lose the transaction too early for a clean rollback after the full test is done.

Are there better ways to guarantee a defined database state for every of our acceptance tests? We would like to keep it simpler than solutions like aufs or btrfs tackling it on system level.

Johannes
  • 11
  • 1
  • 2
    shut down mysql, physically copy the db files, restart mysql. later, rinse, repeat. – Marc B Jun 16 '15 at 13:56
  • Not the most attractive solution technically since we would have to do this after every test method in every test case, but perhaps it is necessary!? Thanks for your input! – Johannes Jun 16 '15 at 14:10

1 Answers1

0

You could approach this problem using PhpUnit.

It is used for automated testing with PHP. Is not the only library, but one of the most extended ones.

You could use it with database testing as well ( https://phpunit.de/manual/current/en/database.html ). Basically, it lets you accomplish exactly what you are looking for. Import initially the whole database, and then in each test suite, load what you need and then restore to the previous state. For example, you could save temporarily the current status of the table A and after you are done with all tests of the suite, simply restore it. Instead of reloading the whole database.

By the way, having a minimal Database with only the required information for testing will help a lot as well. In that case you don't have to deal with big performance issues, and you can simply restore it after each test suite.

xarlymg89
  • 2,552
  • 2
  • 27
  • 41
  • I see your point and we are actually already using PHPUnit for our unit testing. For the acceptance tests fixtures and a minimal database don't seem to be as useful. We would prefer acting on (almost) real data, just without editing them by writing to the database permanently while testing. Reproducability is our key need here… – Johannes Jun 16 '15 at 14:09
  • Then if you can't use a minimal DB (although you should be able to reproduce all the possible cases in your tables), then check out what I wrote about restoring partially the DB to a previous state. Restoring one or two tables, depending on the case, could be doable. Obviously, if you have a huge table, you'll have to wait. I cannot see another solution right now. – xarlymg89 Jun 17 '15 at 07:38
  • How would you best approach restoring the previous DB state in MySQL? – Johannes Jun 17 '15 at 09:41
  • I'm not able to get an example of a partial restore, although it looks like Doctrine has it's own way to do that https://phpunit.de/manual/current/en/database.html#database.will-phpunit-re-create-the-database-schema-for-each-test Could you explain why you cannot have a minimal DB? I have managed to get a minimal DB of 7MB extracted from a production DB of 11GB. – xarlymg89 Jun 18 '15 at 15:11