How do I put a MySQL fixture in a PHPUnit driving a Selenium2?

Question

I have a Symfony2 web project using PHPUnit for the unit tests. Recently I added functional tests with the package phpunit/phpunit-selenium.

I have added tests to do a login, then to create an item and the tests successfully checks the item has been created.

It is time now to create a functional test that logs into the web and deletes an item. This pre-assumes that the item exists. So I need to setup a fixture at some place to ensure the "known state" pre-test.

Note 1: I already do have a 'test' environment different from the 'production' one, so installing fixtures is what I need, no matter if the DB remains cluttered after the test.

Note 2: I don't think it matters, but just in case: The php and its tests runs in one server, the mysql runs in another one and the selenium in a third server.

I wonder what are the best practices in devops-oriented companies for this situations.

This is what I am thinking to do:

1. Have a full-structure, few-data SQL text file that contains the happy-path data and edge cases.
2. Have the setup call a test-helper class like MySQLFixtureLoader
3. Have the MySqlFixtureLoader to clean the test DB and execute the SQL file "as is".
4. The tests now may run as the state is known.
5. Cleanup is not needed in teardown.

While this is like "clean", this has a handicap, and it is that if you run the tests accidentally in production, you reduce production to smoke.

Alternatively I am thinkg to do:

1. Off-test, from time to time (ie: once per month, once per week, once per day) clone the production-db into the test-db.
2. Have a no-structure, few-data SQL test file that ONLY contains INSERTs and patches the production data with newer data ontop.
3. Exercise the tests.
4. Cleaup in teardown so the resulting DB does not contain the tests cases anymore (so it equals the original prduction data). Probably executing another SQL file that is control-versioned.

Is is like less clean, because kills repetaibility. But ensures that run in production the risk of destroying drastically drops to near zero.

Less clean because using production data could pass the tests today, and fail them tomorrow, while neither the product code neither the test code has been changed.

Instead it also tests the software in "that particular MySQL server" that for example can be overloaded if cluttered with real data that would not exists in a clean fixture (for example in a search among 1 billion lines instead of the same search in just 1 thousand lines).

But... anyway, questions that arise:

• Should the fixture loaded in the setUp()? Maybe in the setUpBeforeClass()?
• What is better? To have SQL code in the test suite? Or some helping file also committed to the git and run an external mysql import it?
• I could consider to have like a fixture database and clone it to the test database in the setup(), but it is like less versionable (or you would need to pre-seed the fixture DB each time you clone or pull from the repo, just in case someone else added new test cases.
• Should the test DB be based the production data and add the test cases on it? Or should it be clean and small so it is "repeatible"?
• Should I just "add whatever needed for that test" and then teardown by deleting that so I could safely run the test suite on the production site without risk of deleting all?
• Should I rely on a pre-filled system that does not load automatically?

I already know things that I "could" do. What I'm asking is what have you done in the real-field, ie: in your real-life companies, that solves this issues and works clean an nice.

Thanks.

Answer 1

From my experience the "best" way is to have a database created that has all the test data you need including whatever real world extra data to properly test. Back that up and reload it before each test run. This ensures the test data state always matches with the test cases. This will make life much simpler than trying to add/delete things with your tests as you go. Keep the database export separate but a pre-requisite to your tests.

You can connect test cases together by adding data, modifying, and then going and deleting the data after wards to show the full CRUD functionality. That is a perfectly viable and useful approach as well depending on your application, however, I still recommend a known database starting point from a trusted backup. If you need to modify the data, load the backup, modify, snap a new export/backup.