<?php

/**
 * @file
 * Support tests for SQL Server.
 */
class SqlServerSchemaTest extends DrupalWebTestCase {
  public static function getInfo() {
    return array(
      'name' => 'Schema tests',
      'description' => 'Generic tests for SQL Server Schema.',
      'group' => 'Database (SQL Server)'
    );
  }

  /**
   * Test adding / removing / readding a primary key to a table.
   */
  public function testPrimaryKeyHandling() {
    $table_spec = array(
      'fields' => array(
        'id'  => array(
          'type' => 'int',
          'not null' => TRUE,
        ),
      ),
    );

    db_create_table('test_table', $table_spec);
    $this->assertTrue(db_table_exists('test_table'), t('Creating a table without a primary key works.'));

    db_add_primary_key('test_table', array('id'));
    $this->pass(t('Adding a primary key should work when the table has no data.'));

    // Try adding a row.
    db_insert('test_table')->fields(array('id' => 1))->execute();
    // The second row with the same value should conflict.
    try {
      db_insert('test_table')->fields(array('id' => 1))->execute();
      $this->fail(t('Duplicate values in the table should not be allowed when the primary key is there.'));
    }
    catch (Exception $e) {
    }

    // Drop the primary key and retry.
    db_drop_primary_key('test_table');
    $this->pass(t('Removing a primary key should work.'));

    db_insert('test_table')->fields(array('id' => 1))->execute();
    $this->pass(t('Adding a duplicate row should work without the primary key.'));

    try {
      db_add_primary_key('test_table', array('id'));
      $this->fail(t('Trying to add a primary key should fail with duplicate rows in the table.'));
    }
    catch (Exception $e) {
    }
  }

  /**
   * Test altering a primary key.
   */
  public function testPrimaryKeyAlter() {
    $table_spec = array(
      'fields' => array(
        'id'  => array(
          'type' => 'int',
          'not null' => TRUE,
        ),
      ),
      'primary key' => array('id'),
    );

    db_create_table('test_table', $table_spec);

    // Add a default value.
    db_change_field('test_table', 'id', 'id', array(
      'type' => 'int',
      'not null' => TRUE,
      'default' => 1,
    ));
  }

  /**
   * Test adding / modifying an unsigned column.
   */
  public function testUnsignedField() {
    $table_spec = array(
      'fields' => array(
        'id'  => array(
          'type' => 'int',
          'not null' => TRUE,
          'unsigned' => TRUE,
        ),
      ),
    );

    db_create_table('test_table', $table_spec);

    try {
      db_insert('test_table')->fields(array('id' => -1))->execute();
      $failed = FALSE;
    }
    catch (Exception $e) {
      $failed = TRUE;
    }
    
    $this->assertTrue($failed, t('Inserting a negative value in an unsigned field failed.'));

    $this->assertUnsignedField('test_table', 'id');

    try {
      db_insert('test_table')->fields(array('id' => 1))->execute();
      $failed = FALSE;
    }
    catch (Exception $e) {
      $failed = TRUE;
    }
    $this->assertFalse($failed, t('Inserting a positive value in an unsigned field succeeded.'));

    // Change the field to signed.
    db_change_field('test_table', 'id', 'id', array(
      'type' => 'int',
      'not null' => TRUE,
    ));

    $this->assertSignedField('test_table', 'id');

    // Change the field back to unsigned.
    db_change_field('test_table', 'id', 'id', array(
      'type' => 'int',
      'not null' => TRUE,
      'unsigned' => TRUE,
    ));

    $this->assertUnsignedField('test_table', 'id');
  }

  /**
   * Test insert data in unsigned field.
   */
  protected function assertUnsignedField($table, $field_name) {
    try {
      db_insert('test_table')->fields(array('id' => -1))->execute();
      $success = TRUE;
    }
    catch (Exception $e) {
      $success = FALSE;
    }
    $this->assertFalse($success, t('Inserting a negative value in an unsigned field failed.'));

    try {
      db_insert('test_table')->fields(array('id' => 1))->execute();
      $success = TRUE;
    }
    catch (Exception $e) {
      $success = FALSE;
    }
    $this->assertTrue($success, t('Inserting a positive value in an unsigned field succeeded.'));

    db_delete('test_table')->execute();
  }

  /**
   * Test inserting data in signed field.
   */
  protected function assertSignedField($table, $field_name) {
    try {
      db_insert('test_table')->fields(array('id' => -1))->execute();
      $success = TRUE;
    }
    catch (Exception $e) {
      $success = FALSE;
    }
    $this->assertTrue($success, t('Inserting a negative value in a signed field succeeded.'));

    try {
      db_insert('test_table')->fields(array('id' => 1))->execute();
      $success = TRUE;
    }
    catch (Exception $e) {
      $success = FALSE;
    }
    $this->assertTrue($success, t('Inserting a positive value in a signed field succeeded.'));

    db_delete('test_table')->execute();
  }

  /**
   * Test db_add_field() and db_change_field() with indexes.
   */
  public function testAddChangeWithIndex() {
    $table_spec = array(
      'fields' => array(
        'id'  => array(
          'type' => 'int',
          'not null' => TRUE,
        ),
      ),
      'primary key' => array('id'),
    );

    db_create_table('test_table', $table_spec);

    // Add a default value.
    db_add_field('test_table', 'test', array(
      'type' => 'int',
      'not null' => TRUE,
      'default' => 1,
    ), array(
      'indexes' => array(
        'id_test' => array('id, test'),
      ),
    ));

    $this->assertTrue(db_index_exists('test_table', 'id_test'), t('The index has been created by db_add_field().'));

    // Change the definition, we have by contract to remove the indexes before.
    db_drop_index('test_table', 'id_test');
    $this->assertFalse(db_index_exists('test_table', 'id_test'), t('The index has been dropped.'));

    db_change_field('test_table', 'test', 'test', array(
      'type' => 'int',
      'not null' => TRUE,
      'default' => 1,
    ), array(
      'indexes' => array(
        'id_test' => array('id, test'),
      ),
    ));

    $this->assertTrue(db_index_exists('test_table', 'id_test'), t('The index has been recreated by db_change_field().'));
  }

  /**
   * Test db_add_field() and db_change_field() with binary spec.
   */
  public function testAddChangeWithBinary() {
    $table_spec = array(
      'fields' => array(
        'id'  => array(
          'type' => 'serial',
          'not null' => TRUE,
        ),
        'name' => array(
          'type' => 'varchar',
          'length' => 255,
          'binary' => false
        ),
      ),
      'primary key' => array('id'),
    );

    db_create_table('test_table_binary', $table_spec);
    
    // Insert a value in name
    db_insert('test_table_binary')
      ->fields(array(
        'name' => 'Sandra',
      ))->execute();
    
    // Insert a value in name
    db_insert('test_table_binary')
      ->fields(array(
        'name' => 'sandra',
      ))->execute();
    
    // By default, datase collation
    // should be case insensitive, returning both rows.
    $result = db_query('SELECT COUNT(*) FROM {test_table_binary} WHERE name = :name', array(':name' => 'SANDRA'))->fetchField();
    $this->assertEqual($result, 2, 'Returned the correct number of total rows.');
    
    // Now let's change the field
    // to case sensistive
    db_change_field('test_table_binary', 'name', 'name', array(
          'type' => 'varchar',
          'length' => 255,
          'binary' => true
        ));
    
    // With case sensitivity, no results.
    $result = db_query('SELECT COUNT(*) FROM {test_table_binary} WHERE name = :name', array(':name' => 'SANDRA'))->fetchField();
    $this->assertEqual($result, 0, 'Returned the correct number of total rows.');
    
    // Now one result.
    $result = db_query('SELECT COUNT(*) FROM {test_table_binary} WHERE name = :name', array(':name' => 'sandra'))->fetchField();
    $this->assertEqual($result, 1, 'Returned the correct number of total rows.');
  }

  /**
   * Test numeric field precision.
   */
  public function testNumericFieldPrecision() {
    $table_spec = array(
      'fields' => array(
        'id'  => array(
          'type' => 'serial',
          'not null' => TRUE,
        ),
        'name' => array(
          'type' => 'numeric',
          'precision' => 400,
          'scale' => 2
        ),
      ),
      'primary key' => array('id'),
    );

    $success = FALSE;
    try {
      db_create_table('test_table_binary', $table_spec);
      $success = TRUE;
    }
    catch (Exception $error) {
      $success = FALSE;
    }

    $this->assertTrue($success, t('Able to create a numeric field with an out of bounds precision.'));
  }
  
  /**
   * Test native XML storage.
   */
  public function textXMLStorage() {

    $table_spec = array(
      'fields' => array(
        'id'  => array(
          'type' => 'serial',
          'not null' => TRUE,
        ),
        'extend' => array(
          'type' => 'text',
          'sqlsrv_type' => 'xml',
        ),
      ),
      'primary key' => array('id'),
    );

    $success = FALSE;
    try {
      db_create_table('test_table_xml', $table_spec);
      $success = TRUE;
    }
    catch (Exception $error) {
      $success = FALSE;
    }

    $this->assertTrue($success, t('Able to create a table with an XML field.'));

    // Insert something and retrieve.
    $data = '<xml><a>data</a></xml>';
    try {
      db_insert('test_table_xml')->fields(array('extend' => $data))->execute();
      $success = TRUE;
    }
    catch (\Exception $e) {
      $success = FALSE;
    }

    $this->assertTrue($success, t('Able to insert XML data in an XML field.'));

    $query = db_select('test_table_xml', 't');
    $query->addField('t', 'extend');
    $retrieved =  $query->execute()->fetchAssoc();

    $this->assertEqual($data, $retrieved['extend'], t('XML was retrieved as the original string.'));

    // From now on there are little to no asserts, at least not having
    // Exceptions thrown is a good indication.
    
    // Add new field and convert it into the primary key, but make sure it is bigger than 900 bytes.
    db_add_field('test_table_xml', 'newid', 
            array(
              'type' => 'text',
              'sqlsrv_type' => 'nvarchar(4000)'
            ));

    db_drop_primary_key('test_table_xml');

    // The driver should detect this is > 900 bytes and create
    // a computed Primary Key + Indexes to compensate.
    // Usually you would not be able to PK on a nullable column
    // but because it's hashed this will work.
    db_add_primary_key('test_table_xml', array('newid'));

    // Now change the primary key column to a size that fits in 900 bytes
    // this should re-expand the PK to it's natural version. But drop PK
    // because newid has no values.
    db_drop_primary_key('test_table_xml');
    db_drop_field('test_table_xml', 'newid');
    db_add_field('test_table_xml', 'newid', 
            array(
              'type' => 'text',
              'sqlsrv_type' => 'nvarchar(300)',
              'not null' => TRUE,
              'default' => 'default',
            ));
    db_add_primary_key('test_table_xml', array('id', 'newid'));

    // Now add an XML key, because the current key is > 128 bytes
    // the driver should automatically recompress the PK into a computed
    // column to make space for this key.
    db_add_index('test_table_xml', 'xml_main', array('extend'));

    // Make sure the SCHEMA helper function confirms this.
    $xml_index_name = Database::getConnection()->schema()->tableHasXmlIndex('test_table_xml');
    $this->assertEqual($xml_index_name, 'xml_main_idx', t('XML index creation confirmed.'));

    // Now drop the index, the PK should be re-expanded.
    db_drop_index('test_table_xml', 'xml_main');
    
  }
}