database schema Archives | Symfony 2 Blog

John-Bercow

Back in the days of yore, sometime around Yesteryear, I became aware of Doctrine Fixtures.
I’m not going to go into any great depth as to what they are, what they do, or why you should use them, because frankly if you don’t use them you are a silly billy bean.

Anyway, one thing that makes them not so great is that in the default implementation given in the Symfony documentation, you end up with a db table with auto incremented records starting at some crazy number, incrementally higher and higher every time you run the fixtures.

I’m sure there are ways round this, ways I have never imagined. But as I haven’t imagined them, I can’t very well implement them.

So here is my one line wonder (well, one line after a few other lines have been typed once) to always get you back to zero.

Command and Conquer

Sadly this has nothing to do with that bald headed NOD goon Kane, or that commando guy who killed people with one shot. But programming is quite a dull subject when written down, so I have to spice up my posts with references to more fun times.

Anyway, what we actually need is a Symfony Console Command.

Wait, wot? This sounds hard and scary and I can’t really be bothered learning new things right now.

Actually it’s not – it’s really easy, and it saves time. Your time. Time that you can now invest in bettering yourself. Or on Reddit. Whatever.

Ok, I’m just going to paste my code, then walk through it.

namespace MCM\MyExampleBundle\Command;

use Symfony\Bundle\FrameworkBundle\Command\ContainerAwareCommand;
use Symfony\Component\Console\Input\InputArgument;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Input\InputOption;
use Symfony\Component\Console\Output\OutputInterface;

class PurgeEverythingCommand extends ContainerAwareCommand
{
    protected function configure()
    {
        $this
            ->setName('mcm:purge')
            ->setDescription('Purges the test database, reloads the fixtures, and triggers a test routine.')
            ->addArgument('confirm', InputArgument::REQUIRED, 'You must confirm you wish to erase everything in your test database!')
            ->addOption('notests', null, InputOption::VALUE_NONE, 'Run without the test suite')
        ;
    }

    protected function execute(InputInterface $input, OutputInterface $output)
    {
        // flatten all the tables
        exec('php /full/path/your/site/root/app/console doctrine:schema:drop --env=test --force');

        // recreate the schema
        exec('php /full/path/your/site/root/app/console doctrine:schema:create --env=test');

        // clear any doctrine caches
        exec('php /full/path/your/site/root/app/console doctrine:cache:clear-metadata --env=test');
        exec('php /full/path/your/site/root/app/console doctrine:cache:clear-query --env=test');
        exec('php /full/path/your/site/root/app/console doctrine:cache:clear-result --env=test');

        // re-create the fixtures
        exec('php /full/path/your/site/root/app/console doctrine:fixtures:load -n --env=test');

        if( !$input->getOption('notests') )
        {
            // re-run the tests
            system('phpunit -c app/');
        }
    }

}

Meep, the computer is now telling you exactly what you could do with a life time supply of chocolate.

Ok – you will have gathered from the first line that Commands go in a command sub folder off your Bundle directory. They don’t have too, but it’s a nice convention.

Make sure you suffix your file name with Command.

Then we get to the configuration:

    protected function configure()
    {
        $this
            ->setName('mcm:purge')
            ->setDescription('Purges the test database, reloads the fixtures, and triggers a test routine.')
            ->addArgument('confirm', InputArgument::REQUIRED, 'You must confirm you wish to erase everything in your test database!')
            ->addOption('notests', null, InputOption::VALUE_NONE, 'Run without the test suite')
        ;
    }

The setName method is saying what will we need to type in after app/console to get this command to run. You can put anything here.

The setDescription method is what you will see as a bit of helper text after your string from setName when you type in just app/console and hit return.

The addArgument method sets an argument on the command line that could allow the user to modify the outcome of your script depending on what arguments they choose. In my case, I hide this command initially so a user doesn’t accidentally type in the command and delete everything.

Lastly, the addOption method again allows more granular configuration of how parts of your command will run. In our case, whether or not to run the test suite after recreating the db.

Click here for a full breakdown of all the available ‘actions’ available when using a Command.

And now the actual guts of the how the command executes:

    protected function execute(InputInterface $input, OutputInterface $output)
    {
        // flatten all the tables
        exec('php /full/path/your/site/root/app/console doctrine:schema:drop --env=test --force');

        // recreate the schema
        exec('php /full/path/your/site/root/app/console doctrine:schema:create --env=test');

        // clear any doctrine caches
        exec('php /full/path/your/site/root/app/console doctrine:cache:clear-metadata --env=test');
        exec('php /full/path/your/site/root/app/console doctrine:cache:clear-query --env=test');
        exec('php /full/path/your/site/root/app/console doctrine:cache:clear-result --env=test');

        // re-create the fixtures
        exec('php /full/path/your/site/root/app/console doctrine:fixtures:load -n --env=test');

        if( !$input->getOption('notests') )
        {
            // re-run the tests
            system('phpunit -c app/');
        }
    }

All this is doing is dropping the database in the Test (--env=test) environment; recreating the database schema; clearing any Doctrine caches; and then importing the fixtures into a fresh, empty table structure.

This gets round any pesky foreign key relationship issues when trying to empty tables.

Optionally, the test suite will then be triggered.

The three Doctrine cache clear commands are probably not even needed – but it happens behind the scenes against a test database, so why not?

Downsides

Yeah there are downsides to this sadly.

The biggest downside is that if there are any errors, you will get the error but no context of where the error occurred.

In my case, there are only three possible outcomes.

It all worked so you are just dumped back to your command line
Your fixture import failed – a red box with an error shows up
Your fixture test failed – outputs some combination like …..!…EEEE..

Point one is good. So we move on happier in our lives, more time to post cat pictures to reddit.

Point two is not so good.

In this case you are best to run the schema:drop, schema:create, and fixtures:load commands from the above script again by hand, and see which fixture is bombing out.

Point three is also not so good.

In this case you need to run the test suite manually as it gives a more verbose output of the results.

Lights, Camera, Action-diddly-action-Jackson!

So to finally run this bad boy we just type in:

php app/console mcm:purge --notests confirm

You can omit the --notests option as it’s optional, but the confirm argument is required remember. This is just a fail safe.

So it might seem a little crazy and have a few downsides, but 99% of the time this is a real time saver, so get on it.

Tag: database schema

How I Use Doctrine Fixtures with Symfony 2 for an Easier Life

Command and Conquer

Downsides

Lights, Camera, Action-diddly-action-Jackson!