public function SchemaTestExtended::testTransactionDoomed in Drupal driver for SQL Server and SQL Azure 8.2

@ee https://github.com/Azure/msphpsql/issues/50

Some transactions will get DOOMED if an exception is thrown and the PDO driver will internally rollback and issue a new transaction. That is a BIG bug.

One of the most usual cases is when trying to query with a string against an integer column.

File

tests/src/Kernel/SchemaTestExtended.php, line 495

Class

SchemaTestExtended

Tests table creation and modification via the schema API.

Namespace

Drupal\Tests\sqlsrv\Kernel

Code

public function testTransactionDoomed() {
  $table_spec = array(
    'fields' => array(
      'id' => array(
        'type' => 'serial',
        'not null' => TRUE,
      ),
      'name' => array(
        'type' => 'varchar',
        'length' => 255,
        'binary' => false,
      ),
    ),
    'primary key' => array(
      'id',
    ),
  );
  $schema = $this->connection
    ->schema();
  $schema
    ->createTable('test_table', $table_spec);

  // Let's do it!
  $query = $this->connection
    ->insert('test_table');
  $query
    ->fields(array(
    'name' => 'JUAN',
  ));
  $id = $query
    ->execute();

  // Change the name
  $transaction = $this->connection
    ->startTransaction();
  $this->connection
    ->query('UPDATE {test_table} SET name = :p0 WHERE id = :p1', array(
    ':p0' => 'DAVID',
    ':p1' => $id,
  ));
  $name = $this->connection
    ->query('SELECT TOP(1) NAME from {test_table}')
    ->fetchField();
  $this
    ->assertEqual($name, 'DAVID');

  // Let's throw an exception that DOES NOT doom the transaction
  try {
    $name = $this->connection
      ->query('SELECT COUNT(*) FROM THIS_TABLE_DOES_NOT_EXIST')
      ->fetchField();
  } catch (\Exception $e) {
  }
  $name = $this->connection
    ->query('SELECT TOP(1) NAME from {test_table}')
    ->fetchField();
  $this
    ->assertEqual($name, 'DAVID');

  // Lets doom this transaction.
  try {
    $this->connection
      ->query('UPDATE {test_table} SET name = :p0 WHERE id = :p1', array(
      ':p0' => 'DAVID',
      ':p1' => 'THIS IS NOT AND WILL NEVER BE A NUMBER',
    ));
  } catch (\Exception $e) {
  }

  // What should happen here is that
  // any further attempt to do something inside the
  // scope of this transaction MUST throw an exception.
  $failed = FALSE;
  try {
    $name = $this->connection
      ->query('SELECT TOP(1) NAME from {test_table}')
      ->fetchField();
    $this
      ->assertEqual($name, 'DAVID');
  } catch (\Exception $e) {
    if (!$e instanceof DoomedTransactionException) {
      $this
        ->fail('Wrong exception when testing doomed transactions.');
    }
    $failed = TRUE;
  }
  $this
    ->assertTrue($failed, 'Operating on the database after the transaction is doomed throws an exception.');

  // Trying to unset the transaction without an explicit rollback should trigger
  // an exception.
  $failed = FALSE;
  try {
    unset($transaction);
  } catch (\Exception $e) {
    if (!$e instanceof DoomedTransactionException) {
      $this
        ->fail('Wrong exception when testing doomed transactions.');
    }
    $failed = TRUE;
  }
  $this
    ->assertTrue($failed, 'Trying to commit a doomed transaction throws an Exception.');

  //$query = db_select('test_table', 't');

  //$query->addField('t', 'name');

  //$name = $query->execute()->fetchField();

  //$this->assertEqual($name, 'DAVID');

  //unset($transaction);
}