"Truncation" of SQLite database table does not reset auto-increment counters

[jlem]

Since SQLite does not have a TRUNCATE command, and you must use DELETE FROM, you must also call DELETE FROM sqlite_sequence where name='name_of_table';

It appears as though DBUnit does not do this, which means you cannot do something simple like this:

$actual = $this->getConnection()->createDataSet(['my_table']);

You must either filter the dataset...

$filtered = new \PHPUnit_Extensions_Database_DataSet_DataSetFilter($actual);
$filtered->setExcludeColumnsForTable('my_table', ['id']);

or write out a query with createQueryTable and exclude the auto incrementing field....

$queryTable = $this->getConnection()->createQueryTable(
    'my_table', 'SELECT col1, col2, col3, ...colN FROM my_table';
);

Both of which are quite verbose and complicate otherwise simple tests.

Moreover, the expected behavior of truncating a SQLite database on teardown should be the same as truncating a MySQL database - auto-increment counters should be reset.

[rbalik]

This is also broken on Postgres. You have to pass some extra arguments in the TRUNCATE to reset the sequences so the id column will start at 1 again.

[JimBacon]

Duplicate of https://github.com/sebastianbergmann/dbunit/issues/12 See also https://github.com/sebastianbergmann/dbunit/issues/58

[JimBacon]

My resolution to this was to add a new sequence restart operation with database-specific commands to restart sequences. I had to extend a number of classes to achieve this as below. While my example just covers PostgreSQL, I think it would suit SQLite.

/**
 * Extends PHPUnit_Extensions_Database_DB_MetaData_PgSQL in order to create
 * a database specific command to restart sequences.
 */
class My_DB_MetaData_PgSQL extends PHPUnit_Extensions_Database_DB_MetaData_PgSQL {
    public function getRestartCommand($table) {
      // Assumes sequence naming convention has been followed.
      return "SELECT setval('{$table}_id_seq', 1, false);";
  }
}

/**
 * Extends PHPUnit_Extensions_Database_DB_MetaData in order to replace the 
 * default postgres driver with mine.
 */
abstract class My_DB_MetaData extends PHPUnit_Extensions_Database_DB_MetaData {
  public static function createMetaData(PDO $pdo, $schema = '') {
    self::$metaDataClassMap['pgsql'] = 'My_DB_MetaData_PgSQL';
    return parent::createMetaData($pdo, $schema);
  }    
}

/**
 * Extends PHPUnit_Extensions_Database_DB_DefaultDatabaseConnection in order to
 * a. override the constructor so that it uses My_DB_MetaData.
 * b. give access to the command that will restart sequences.
 */
class My_DB_DefaultDatabaseConnection extends PHPUnit_Extensions_Database_DB_DefaultDatabaseConnection {
  public function __construct(PDO $connection, $schema = '') {
    $this->connection = $connection;
    $this->metaData   = My_DB_MetaData::createMetaData($connection, $schema);
    $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  }

  public function getRestartCommand($table) {
    return $this->getMetaData()->getRestartCommand($table);
  }
}

/**
 * New class to add a Restart operation.
 */
Class My_Operation_Restart implements PHPUnit_Extensions_Database_Operation_IDatabaseOperation {
  public function execute(PHPUnit_Extensions_Database_DB_IDatabaseConnection $connection, PHPUnit_Extensions_Database_DataSet_IDataSet $dataSet) {
    foreach ($dataSet->getReverseIterator() as $table) {
      $tableName = $table->getTableMetaData()->getTableName();
      $query = $connection->getRestartCommand($tableName);
      try {
          $connection->getConnection()->query($query);
      } catch (\Exception $e) {
        if ($e instanceof PDOException) {
          throw new PHPUnit_Extensions_Database_Operation_Exception('RESTART', $query, [], $table, $e->getMessage());
        }
        throw $e;
      }
    }
  }
}

/**
 * Extends PHPUnit_Extensions_Database_Operation_Factory in order to add 
 * functions that call the Restart operation.
 */
Class My_Operation_Factory extends PHPUnit_Extensions_Database_Operation_Factory {
  public static function RESTART_INSERT($cascadeTruncates = FALSE) {
    return new PHPUnit_Extensions_Database_Operation_Composite([
      self::TRUNCATE($cascadeTruncates),
      self::RESTART(),
      self::INSERT()
    ]);
  }

  public static function RESTART() {
    return new My_Operation_Restart();
  }
}

To enable my test class to make use of the changes, I had to override two functions it inherited from PHPUnit_Extensions_Database_TestCase as follows.

  // Override the function to determine the setup operation so that it uses.
  // My_Operation_Factory::RESTART_INSERT($cascadeTruncates = FALSE)
  protected function getSetUpOperation() {
     return My_Operation_Factory::RESTART_INSERT();
  }

  // Override the function to create the database connection so that is uses 
  // My_DB_DefaultDatabaseConnection.
  protected function createDefaultDBConnection(PDO $connection, $schema = ''){
    return new My_DB_DefaultDatabaseConnection($connection, $schema);
  }  

[sebastianbergmann]

Duplicate of #12 and #58