sqlsrv.schema.test in Drupal driver for SQL Server and SQL Azure 7.2
Same filename and directory in other branches
Support tests for SQL Server.
File
tests/sqlsrv.schema.testView source
<?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');
}
}
Classes
Name | Description |
---|---|
SqlServerSchemaTest | @file Support tests for SQL Server. |