SQLite database tables list in tests for ORMPurger

[BonBonSlick]

Q	A
Version	"^2.5.11",

Support Question

Trying to optimize tests. http://lzakrzewski.com/2016/02/tricks-to-speed-up-database-tests/

But cant purge database because ORMPurger throws exception that tables does not exist.

 dump($conn->exec('SELECT * FROM sqlite_master WHERE type = "table"')); // 0
// SQLite does not support that feature,tables  list 
 dump($this->getEntityManager()->getConnection()->getSchemaManager()->listDatabases()); 

settings

doctrine:
  dbal:
    driver: pdo_sqlite
    path: /tmp/test-test.db
    memory: true
    charset: UTF8

Dropping and creating schema works, but this is taking too much, 10-15 secs with fixtures loading. 1 test takes about 30 secs.

How to get tables or do purge without recreation of schema? This way I can also do backup as described

Trick no 4: Restore cached copy of database in link

[Ocramius]

Doesn't seem like an issue from this repository: this is already what the ORMPurger does.

[BonBonSlick]

I know, $classes = []; to all required metadata for mapped classes. $commitOrder = $this->getCommitOrder($this->em, $classes); has all what should be, but next

        $connection = $this->em->getConnection();
        $filterExpr = $connection->getConfiguration()->getFilterSchemaAssetsExpression();
        $emptyFilterExpression = empty($filterExpr);
        dump($orderedTables);
        die;
// dump
array:35 [
  0 => "postcode_street"
  1 => "ThreadMetadata"
  2 => "message_metadata"
  3 => "message"
  4 => "user_images"
.....

here

   if ($this->purgeMode === self::PURGE_MODE_DELETE) {
                    $connection->executeUpdate("DELETE FROM " . $tbl);
                } else {
                    $connection->executeUpdate($platform->getTruncateTableSQL($tbl, true));
                }

will throw

Caused by
PDOException: SQLSTATE[HY000]: General error: 1 no such table: postcode_street

/var/www/advire/test/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:41
/var/www/advire/test/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:1060
/var/www/advire/test/vendor/doctrine/data-fixtures/lib/Doctrine/Common/DataFixtures/Purger/ORMPurger.php:142
/var/www/advire/test/tests/Functional/FunctionalTestCase.php:115
/var/www/advire/test/tests/Functional/FunctionalTestCase.php:75

[Ocramius]

@BonBonSlick please reproduce this within a test scenario first: what I'm getting from over here is just a glimpse of what is going on, and it is extremely hard to follow what your local problem is, without an idea of what your schema definition looks like.

I'd suggest writing a test case, maybe added to https://github.com/doctrine/data-fixtures/blob/a11ceca670dad73d55f27f96277f53cd037865d2/tests/Doctrine/Tests/Common/DataFixtures/Purger/ORMPurgerTest.php

[BonBonSlick]

    $connection = $this->em->getConnection();
        $filterExpr = $connection->getConfiguration()->getFilterSchemaAssetsExpression();
        $emptyFilterExpression = empty($filterExpr);
        dump($connection->getDatabase());
        dump($connection->getSchemaManager()->listTableNames());
        die;

"/tmp/oppasland-test.db"
[]

Seems no tables in memory sqllite DB file. Possibly because of that it throws exception , but why no tables?

Tried to migrate like this

$ php bin/console doctrine:schema:update --env=test --force

 Updating database schema...

     110 queries were executed

 [OK] Database schema updated successfully!   

And still empty tables list, running tests in test env

Maybe somewhere here should be check if table exist in database tables array. If not, migrate?

[BonBonSlick]

Yes, I found solution, create schema before purge is required in test mode

        // create database
        $metadatas = $this->entityManager->getMetadataFactory()->getAllMetadata();
        if (!empty($metadatas)) {
            try {
                $schemaTool->createSchema($metadatas);
            } catch (\Exception $e) {
                dump($e);
                die;
            }
        }

        $purger = new ORMPurger($this->getEntityManager());
        $purger->purge();

        // load test fixtures
        $this->loadWholeFixturesFromCache();

[Ocramius]

should be check if table exist in database tables array

Not really: if a table is defined in the metadata, it should exist in the DB as well. Otherwise, you have a very problematic asymmetry between DDL and metadata structures.

[BonBonSlick]

@Ocramius what about caching of sql query for purge? It may speed up all tests around the world :) adding something to ORMPurger

    if (null === self::$purgeQueryInMemory) {
            self::$purgeQueryInMemory = $this->generatePurgeDBQuery();
        }

Query might be very long, 100+ tables. Because

Creating the query which purges the database needs to read the whole metadata of entities which takes time. It could be done only once before the first test and then it could be restored from cache:

[Ocramius]

@BonBonSlick if you want to go on and implement it (and test it carefully), sure. Consider using a separate class though, not inlining it within the ORMPurger

[BonBonSlick]

I have implemented cached ORMPurger, but is appears other doctrine parts rely on on interface but on implementation , example is ORMExecutor public function __construct(EntityManagerInterface $em, ORMPurger $purger = null) When fixtures executed, executor call every time purge from purger.

[Ocramius]

That's where unit testing helps :-)

[BonBonSlick]

Maybe ORMExecutor ORMPurger dependency might be replaced with PurgerInterface? :)