Fixtures cleaning and foreign keys

I have two tables, supposely user and post.

Post has a foreign key user_id. I have fixtures for both tables.

My trouble is: the first time I run the testes, it inserts the fixtures and it is OK.

The second time, it will try to truncate the database tables, but it starts with "user" table, exploding a FK violation with "post.user_id".

How should I handle this?

First empty the post table… then the user table…

How can I tell Yii to do that way? :) He’s the guy who cleans database tables and inserts fixtures.

how do you tell him now?

I have a basic envinronment:




/protected/tests/

  fixtures/

    post.php

    user.php

  unit/

    PostTest.php

    UserTest.php



Yii seems to load files in a strange order, so:

  • The first time I run, it cleans the already empty tables

  • The second, third,… times it tries to clean user table first, and the FK explodes.

How could I define the fixture loading order?

[s]You could define .init.php files (user.init.php and post.init.php) for doing your custome initializations of your fixtures (last two paragraphs in the guide Testing: Defining Fixtures).

Create these two .init.php files so the default initialization process is not running.

Leave the user.init.php empty and do all the initialization work (in the correct order ;)) in post.init.php[/s]

[size=“3”]Edit: There should be a simpler solution by creating only one file init.php. There you can do all the initialization at once. Sorry for any confusion [/size]:unsure:

It’s not a clear solution cause code is put into a file where it doesn’t belong to but at least it should work.

Can you tell me/us if this works and/or if you or someone else have found a better and cleaner solution.

Found the following post here in the forum about .init.php files which could be helpful.

[b]Unti tests - fixtures

[/b]

I have figured out the problem: Yii is not disabling constraint checks (PostgreSQL). The "SET CONSTRAINTS ALL DEFERRED" workaround is not working too.

To solve the problem, I have extended CDbFixtureManager and I have overrided the methods prepare and getFixtures.

"prepare" now have 2 loops: first cleaning, then inserting.

"getFixtures" now orders the fixtures files accordingly to a static "$orderedFixtures" attribute (defined in the bootstrap.php file).

It is ugly, but I will hold it until I figure out how to do Yii really disable PostgreSQL FKs.

Nice to hear that it’s working :)

Have you also tried the following code in this post which looks to me like a very clean solution.

Yes, I already have done that, but it doesn’t work on my case. PostgreSQL documentation says:

If I have time, I will try to disable autocommit on inserting fixtures and see if it works.

Thanks for your help!

Hi, sorry for reopening old subject, but I had kinda hard time doing this exact thing so I thougt I’ll share my solution.

Previously linked clean solution didn’t work for me either.

Unfortunately, the “ugly” solution didn’t work either, but with slight modification it did in the end.

First, I created init.php in my fixtures folder, with instructions from https://blogs.law.harvard.edu/acts/2012/05/09/yii-fixtures-with-foreign-keys/ (basically the same idea as mentioned here, you set order of the tables to do resets and cleanups in two separate cycles).

However, my tests still weren’t working since there are some cleanups going on before every single test method, and those are indepedent on the init script (and they always tried to do something like “delete posts, fill posts, delete users” -> error since there exists posts referencing the users table). This is apparently going on in a load() method of CDbFixtureManager, which was the important method for me to override (basically in the same way - sort the fixtures in a usable way, do one cycle deleting, and another one inserting).

When overriding the CDbFixtureManager, I ran into problems with missing getRows() method in my extended class, which returns private value of _rows (which I also had to redeclare, together with _records). I simply copied getRows() from CDbFixtureManager into my new class. Pretty ugly, but only way I managed it to work.

Think overriding core classes of the framework like this isn’t really the good way, and I’m not even sure I didn’t screw some other functionality (due to the private properties, I guess), but for now it seems it’s working. I’d definitely be glad if someone would give me a hint how to settle this in a better way though.

Here’s my CDbFixtureManager extension:




<?php

Yii::import('system.test.CDbFixtureManager');


/**

 * Overriding default fixture manager in order to reset & load fixtures in specified order

 */

class FDbFixtureManager extends CDbFixtureManager {


    /**

     * @var array   fixture name, row alias => row

     */

    private $_rows;


    /**

     * @var array   fixture name, row alias => record (or class name)

     */

    private $_records;   // 


    /**

     * Loads fixtures in a specific order

     * it's important to reset all tables first, and then fill them again in a specified order

     * 

     * @param   array   array of fixtures to run

     */


    public function load($fixtures) {

        $fixtures = array_merge(array_flip(ORDERED_ARRAY), $fixtures);

        $load_order = array_reverse($fixtures);


        $schema = $this->getDbConnection()->getSchema();

        $schema->checkIntegrity(false);


        $this->_rows = array();

        $this->_records = array();


        foreach ($fixtures as $fixtureName => $tableName) {

            if ($tableName[0] === ':') {

                $tableName = substr($tableName, 1);

                unset($modelClass);

            } else {

                $modelClass = Yii::import($tableName, true);

                $tableName = CActiveRecord::model($modelClass)->tableName();

                if (($prefix = $this->getDbConnection()->tablePrefix) !== null)

                    $tableName = preg_replace('/{{(.*?)}}/', $prefix . '\1', $tableName);

            }

            $this->resetTable($tableName);

        }


        foreach ($load_order as $fixtureName => $tableName) {

            if ($tableName[0] === ':') {

                $tableName = substr($tableName, 1);

                unset($modelClass);

            } else {

                $modelClass = Yii::import($tableName, true);

                $tableName = CActiveRecord::model($modelClass)->tableName();

                if (($prefix = $this->getDbConnection()->tablePrefix) !== null)

                    $tableName = preg_replace('/{{(.*?)}}/', $prefix . '\1', $tableName);

            }

            $rows = $this->loadFixture($tableName);

            if (is_array($rows) && is_string($fixtureName)) {

                $this->_rows[$fixtureName] = $rows;

                if (isset($modelClass)) {

                    foreach (array_keys($rows) as $alias)

                        $this->_records[$fixtureName][$alias] = $modelClass;

                }

            }

        }


        $schema->checkIntegrity(true);

    }


    /**

     * Returns the fixture data rows.

     * The rows will have updated primary key values if the primary key is auto-incremental.

     * 

     * @param string $name the fixture name

     * @return array the fixture data rows. False is returned if there is no such fixture data.

     */

    public function getRows($name) {

        if (isset($this->_rows[$name]))

            return $this->_rows[$name];

        else

            return false;

    }


}


?>



Now I ended up with the following "solution", enclosing each call of parent::method() within a transaction so that $schema->checkIntegrity(false) works:




<?php


Yii::import('system.test.CDbFixtureManager'); 


class MyDbFixtureManager extends CDbFixtureManager

{

    public function prepare()

    {

        $connection = $this->getDbConnection();


        $schema = $connection->getSchema();

        $schema->checkIntegrity(false);

        $transaction = $connection->beginTransaction();


        try {

            parent::prepare();

            $transaction->commit();

        }

        catch (Exception $e) {

            $transaction->rollBack();

            throw $e;

        }


        $schema->checkIntegrity(true);

    }

    

    public function getFixtures()

    {

        $connection = $this->getDbConnection();


        $schema = $connection->getSchema();

        $schema->checkIntegrity(false);

        $transaction = $connection->beginTransaction();


        $fixtures = null;


        try {

            $fixtures = parent::getFixtures();

            $transaction->commit();

        }

        catch (Exception $e) {

            $transaction->rollBack();

            throw $e;

        }


        $schema->checkIntegrity(true);


        return $fixtures;

    }


    public function load($fixtures)

    {

        $connection = $this->getDbConnection();


        $schema = $connection->getSchema();

        $schema->checkIntegrity(false);

        $transaction = $connection->beginTransaction();


        try {

            parent::load($fixtures);

            $transaction->commit();

        }

        catch (Exception $e) {

            $transaction->rollBack();

            throw $e;

        }


        $schema->checkIntegrity(true);

    }

}