/**
* testIndexDetection method
*
* @group indices
* @return void
*/
public function testIndexDetection()
{
$this->Dbo->cacheSources = false;
$name = $this->Dbo->fullTableName('simple');
$this->Dbo->rawQuery('CREATE TABLE ' . $name . ' (id int(11) AUTO_INCREMENT, bool tinyint(1), small_int tinyint(2), primary key(id));');
$expected = array('PRIMARY' => array('column' => 'id', 'unique' => 1));
$result = $this->Dbo->index('simple', false);
$this->Dbo->rawQuery('DROP TABLE ' . $name);
$this->assertEquals($expected, $result);
$name = $this->Dbo->fullTableName('with_a_key');
$this->Dbo->rawQuery('CREATE TABLE ' . $name . ' (id int(11) AUTO_INCREMENT, bool tinyint(1), small_int tinyint(2), primary key(id), KEY `pointless_bool` ( `bool` ));');
$expected = array('PRIMARY' => array('column' => 'id', 'unique' => 1), 'pointless_bool' => array('column' => 'bool', 'unique' => 0));
$result = $this->Dbo->index('with_a_key', false);
$this->Dbo->rawQuery('DROP TABLE ' . $name);
$this->assertEquals($expected, $result);
$name = $this->Dbo->fullTableName('with_two_keys');
$this->Dbo->rawQuery('CREATE TABLE ' . $name . ' (id int(11) AUTO_INCREMENT, bool tinyint(1), small_int tinyint(2), primary key(id), KEY `pointless_bool` ( `bool` ), KEY `pointless_small_int` ( `small_int` ));');
$expected = array('PRIMARY' => array('column' => 'id', 'unique' => 1), 'pointless_bool' => array('column' => 'bool', 'unique' => 0), 'pointless_small_int' => array('column' => 'small_int', 'unique' => 0));
$result = $this->Dbo->index('with_two_keys', false);
$this->Dbo->rawQuery('DROP TABLE ' . $name);
$this->assertEquals($expected, $result);
$name = $this->Dbo->fullTableName('with_compound_keys');
$this->Dbo->rawQuery('CREATE TABLE ' . $name . ' (id int(11) AUTO_INCREMENT, bool tinyint(1), small_int tinyint(2), primary key(id), KEY `pointless_bool` ( `bool` ), KEY `pointless_small_int` ( `small_int` ), KEY `one_way` ( `bool`, `small_int` ));');
$expected = array('PRIMARY' => array('column' => 'id', 'unique' => 1), 'pointless_bool' => array('column' => 'bool', 'unique' => 0), 'pointless_small_int' => array('column' => 'small_int', 'unique' => 0), 'one_way' => array('column' => array('bool', 'small_int'), 'unique' => 0));
$result = $this->Dbo->index('with_compound_keys', false);
$this->Dbo->rawQuery('DROP TABLE ' . $name);
$this->assertEquals($expected, $result);
$name = $this->Dbo->fullTableName('with_multiple_compound_keys');
$this->Dbo->rawQuery('CREATE TABLE ' . $name . ' (id int(11) AUTO_INCREMENT, bool tinyint(1), small_int tinyint(2), primary key(id), KEY `pointless_bool` ( `bool` ), KEY `pointless_small_int` ( `small_int` ), KEY `one_way` ( `bool`, `small_int` ), KEY `other_way` ( `small_int`, `bool` ));');
$expected = array('PRIMARY' => array('column' => 'id', 'unique' => 1), 'pointless_bool' => array('column' => 'bool', 'unique' => 0), 'pointless_small_int' => array('column' => 'small_int', 'unique' => 0), 'one_way' => array('column' => array('bool', 'small_int'), 'unique' => 0), 'other_way' => array('column' => array('small_int', 'bool'), 'unique' => 0));
$result = $this->Dbo->index('with_multiple_compound_keys', false);
$this->Dbo->rawQuery('DROP TABLE ' . $name);
$this->assertEquals($expected, $result);
}
/**
* test Index introspection.
*
* @return void
*/
public function testIndex() {
$name = $this->Dbo->fullTableName('with_a_key');
$this->Dbo->query('CREATE TABLE ' . $name . ' ("id" int(11) PRIMARY KEY, "bool" int(1), "small_char" varchar(50), "description" varchar(40) );');
$this->Dbo->query('CREATE INDEX pointless_bool ON ' . $name . '("bool")');
$this->Dbo->query('CREATE UNIQUE INDEX char_index ON ' . $name . '("small_char")');
$expected = array(
'PRIMARY' => array('column' => 'id', 'unique' => 1),
'pointless_bool' => array('column' => 'bool', 'unique' => 0),
'char_index' => array('column' => 'small_char', 'unique' => 1),
);
$result = $this->Dbo->index($name);
$this->assertEqual($expected, $result);
$this->Dbo->query('DROP TABLE ' . $name);
$this->Dbo->query('CREATE TABLE ' . $name . ' ("id" int(11) PRIMARY KEY, "bool" int(1), "small_char" varchar(50), "description" varchar(40) );');
$this->Dbo->query('CREATE UNIQUE INDEX multi_col ON ' . $name . '("small_char", "bool")');
$expected = array(
'PRIMARY' => array('column' => 'id', 'unique' => 1),
'multi_col' => array('column' => array('small_char', 'bool'), 'unique' => 1),
);
$result = $this->Dbo->index($name);
$this->assertEqual($expected, $result);
$this->Dbo->query('DROP TABLE ' . $name);
}
/**
* testIndexDetection method
*
* @group indices
* @return void
*/
public function testIndexDetection()
{
$this->Dbo->cacheSources = false;
$name = $this->Dbo->fullTableName('simple');
$this->Dbo->rawQuery('CREATE TABLE ' . $name . ' (id int(11) AUTO_INCREMENT, bool tinyint(1), small_int tinyint(2), primary key(id));');
$expected = array('PRIMARY' => array('column' => 'id', 'unique' => 1));
$result = $this->Dbo->index('simple', false);
$this->Dbo->rawQuery('DROP TABLE ' . $name);
$this->assertEquals($expected, $result);
$name = $this->Dbo->fullTableName('bigint');
$this->Dbo->rawQuery('CREATE TABLE ' . $name . ' (id bigint(20) AUTO_INCREMENT, bool tinyint(1), small_int tinyint(2), primary key(id));');
$expected = array('PRIMARY' => array('column' => 'id', 'unique' => 1));
$result = $this->Dbo->index('bigint', false);
$this->Dbo->rawQuery('DROP TABLE ' . $name);
$this->assertEquals($expected, $result);
$name = $this->Dbo->fullTableName('with_a_key');
$this->Dbo->rawQuery('CREATE TABLE ' . $name . ' (id int(11) AUTO_INCREMENT, bool tinyint(1), small_int tinyint(2), primary key(id), KEY `pointless_bool` ( `bool` ));');
$expected = array('PRIMARY' => array('column' => 'id', 'unique' => 1), 'pointless_bool' => array('column' => 'bool', 'unique' => 0));
$result = $this->Dbo->index('with_a_key', false);
$this->Dbo->rawQuery('DROP TABLE ' . $name);
$this->assertEquals($expected, $result);
$name = $this->Dbo->fullTableName('with_two_keys');
$this->Dbo->rawQuery('CREATE TABLE ' . $name . ' (id int(11) AUTO_INCREMENT, bool tinyint(1), small_int tinyint(2), primary key(id), KEY `pointless_bool` ( `bool` ), KEY `pointless_small_int` ( `small_int` ));');
$expected = array('PRIMARY' => array('column' => 'id', 'unique' => 1), 'pointless_bool' => array('column' => 'bool', 'unique' => 0), 'pointless_small_int' => array('column' => 'small_int', 'unique' => 0));
$result = $this->Dbo->index('with_two_keys', false);
$this->Dbo->rawQuery('DROP TABLE ' . $name);
$this->assertEquals($expected, $result);
$name = $this->Dbo->fullTableName('with_compound_keys');
$this->Dbo->rawQuery('CREATE TABLE ' . $name . ' (id int(11) AUTO_INCREMENT, bool tinyint(1), small_int tinyint(2), primary key(id), KEY `pointless_bool` ( `bool` ), KEY `pointless_small_int` ( `small_int` ), KEY `one_way` ( `bool`, `small_int` ));');
$expected = array('PRIMARY' => array('column' => 'id', 'unique' => 1), 'pointless_bool' => array('column' => 'bool', 'unique' => 0), 'pointless_small_int' => array('column' => 'small_int', 'unique' => 0), 'one_way' => array('column' => array('bool', 'small_int'), 'unique' => 0));
$result = $this->Dbo->index('with_compound_keys', false);
$this->Dbo->rawQuery('DROP TABLE ' . $name);
$this->assertEquals($expected, $result);
$name = $this->Dbo->fullTableName('with_multiple_compound_keys');
$this->Dbo->rawQuery('CREATE TABLE ' . $name . ' (id int(11) AUTO_INCREMENT, bool tinyint(1), small_int tinyint(2), primary key(id), KEY `pointless_bool` ( `bool` ), KEY `pointless_small_int` ( `small_int` ), KEY `one_way` ( `bool`, `small_int` ), KEY `other_way` ( `small_int`, `bool` ));');
$expected = array('PRIMARY' => array('column' => 'id', 'unique' => 1), 'pointless_bool' => array('column' => 'bool', 'unique' => 0), 'pointless_small_int' => array('column' => 'small_int', 'unique' => 0), 'one_way' => array('column' => array('bool', 'small_int'), 'unique' => 0), 'other_way' => array('column' => array('small_int', 'bool'), 'unique' => 0));
$result = $this->Dbo->index('with_multiple_compound_keys', false);
$this->Dbo->rawQuery('DROP TABLE ' . $name);
$this->assertEquals($expected, $result);
$name = $this->Dbo->fullTableName('with_fulltext');
$this->Dbo->rawQuery('CREATE TABLE ' . $name . ' (id int(11) AUTO_INCREMENT, name varchar(255), description text, primary key(id), FULLTEXT KEY `MyFtIndex` ( `name`, `description` )) ENGINE=MyISAM;');
$expected = array('PRIMARY' => array('column' => 'id', 'unique' => 1), 'MyFtIndex' => array('column' => array('name', 'description'), 'type' => 'fulltext'));
$result = $this->Dbo->index('with_fulltext', false);
$this->Dbo->rawQuery('DROP TABLE ' . $name);
$this->assertEquals($expected, $result);
$name = $this->Dbo->fullTableName('with_text_index');
$this->Dbo->rawQuery('CREATE TABLE ' . $name . ' (id int(11) AUTO_INCREMENT, text_field text, primary key(id), KEY `text_index` ( `text_field`(20) ));');
$expected = array('PRIMARY' => array('column' => 'id', 'unique' => 1), 'text_index' => array('column' => 'text_field', 'unique' => 0, 'length' => array('text_field' => 20)));
$result = $this->Dbo->index('with_text_index', false);
$this->Dbo->rawQuery('DROP TABLE ' . $name);
$this->assertEquals($expected, $result);
$name = $this->Dbo->fullTableName('with_compound_text_index');
$this->Dbo->rawQuery('CREATE TABLE ' . $name . ' (id int(11) AUTO_INCREMENT, text_field1 text, text_field2 text, primary key(id), KEY `text_index` ( `text_field1`(20), `text_field2`(20) ));');
$expected = array('PRIMARY' => array('column' => 'id', 'unique' => 1), 'text_index' => array('column' => array('text_field1', 'text_field2'), 'unique' => 0, 'length' => array('text_field1' => 20, 'text_field2' => 20)));
$result = $this->Dbo->index('with_compound_text_index', false);
$this->Dbo->rawQuery('DROP TABLE ' . $name);
$this->assertEquals($expected, $result);
}
/**
* Rename an existing index.
*
* @param string $table
* @param string $oldName
* @param string $newName
* @throws MissingTableException if table does not exist in database
* @throws MissingIndexException if the $oldName index does not exist on the table
* @throws IndexAlreadyExistsException if the $newName index already exists on the table
* @throws MigrationException if an sql error occurred
* @return Migration
*/
public function renameIndex($table, $oldName, $newName)
{
if (!in_array($this->_db->fullTableName($table, false, false), $this->_db->listSources())) {
throw new MissingTableException(__d('migration', 'Table "%s" does not exist in database.', $this->_db->fullTableName($table, false, false)));
}
$existingIndexes = $this->_db->index($table);
if (get_class($this->_db) === 'Postgres') {
$oldName = strtolower($oldName);
$newName = strtolower($newName);
}
if (!array_key_exists($oldName, $existingIndexes)) {
throw new MissingIndexException(__d('migration', 'Index "%s" does not exist on table "%s".', array($oldName, $table)));
}
if (array_key_exists($newName, $existingIndexes)) {
throw new IndexAlreadyExistsException(__d('migration', 'Index "%s" already exists on table "%s".', array($newName, $table)));
}
try {
$this->_db->execute($this->_db->alterSchema(array($table => array('drop' => array('indexes' => array($oldName => array())), 'add' => array('indexes' => array($newName => $existingIndexes[$oldName]))))));
} catch (Exception $e) {
throw new MigrationException(__d('migration', 'SQL Error: %s', $e->getMessage()));
}
return $this;
}
/**
* Test the alter index capabilities of postgres
*
* @return void
*/
public function testAlterIndexes()
{
$this->Dbo->cacheSources = false;
$schema1 = new CakeSchema(array('name' => 'AlterTest1', 'connection' => 'test', 'altertest' => array('id' => array('type' => 'integer', 'null' => false, 'default' => 0), 'name' => array('type' => 'string', 'null' => false, 'length' => 50), 'group1' => array('type' => 'integer', 'null' => true), 'group2' => array('type' => 'integer', 'null' => true))));
$this->Dbo->rawQuery($this->Dbo->createSchema($schema1));
$schema2 = new CakeSchema(array('name' => 'AlterTest2', 'connection' => 'test', 'altertest' => array('id' => array('type' => 'integer', 'null' => false, 'default' => 0), 'name' => array('type' => 'string', 'null' => false, 'length' => 50), 'group1' => array('type' => 'integer', 'null' => true), 'group2' => array('type' => 'integer', 'null' => true), 'indexes' => array('name_idx' => array('unique' => false, 'column' => 'name'), 'group_idx' => array('unique' => false, 'column' => 'group1'), 'compound_idx' => array('unique' => false, 'column' => array('group1', 'group2')), 'PRIMARY' => array('unique' => true, 'column' => 'id')))));
$this->Dbo->query($this->Dbo->alterSchema($schema2->compare($schema1)));
$indexes = $this->Dbo->index('altertest');
$this->assertEquals($schema2->tables['altertest']['indexes'], $indexes);
// Change three indexes, delete one and add another one
$schema3 = new CakeSchema(array('name' => 'AlterTest3', 'connection' => 'test', 'altertest' => array('id' => array('type' => 'integer', 'null' => false, 'default' => 0), 'name' => array('type' => 'string', 'null' => false, 'length' => 50), 'group1' => array('type' => 'integer', 'null' => true), 'group2' => array('type' => 'integer', 'null' => true), 'indexes' => array('name_idx' => array('unique' => true, 'column' => 'name'), 'group_idx' => array('unique' => false, 'column' => 'group2'), 'compound_idx' => array('unique' => false, 'column' => array('group2', 'group1')), 'another_idx' => array('unique' => false, 'column' => array('group1', 'name'))))));
$this->Dbo->query($this->Dbo->alterSchema($schema3->compare($schema2)));
$indexes = $this->Dbo->index('altertest');
$this->assertEquals($schema3->tables['altertest']['indexes'], $indexes);
// Compare us to ourself.
$this->assertEquals(array(), $schema3->compare($schema3));
// Drop the indexes
$this->Dbo->query($this->Dbo->alterSchema($schema1->compare($schema3)));
$indexes = $this->Dbo->index('altertest');
$this->assertEquals(array(), $indexes);
$this->Dbo->query($this->Dbo->dropSchema($schema1));
}
/**
* Alter Table method
*
* @param string $type Type of operation to be done
* @param array $tables List of tables and fields
* @return bool Return true in case of success, otherwise false
* @throws MigrationException
*/
protected function _alterTable($type, $tables)
{
foreach ($tables as $table => $fields) {
$indexes = array();
if (isset($fields['indexes'])) {
$indexes = $fields['indexes'];
unset($fields['indexes']);
}
if ($type === 'drop') {
$this->_alterIndexes($indexes, $type, $table);
}
foreach ($fields as $field => $col) {
$model = new Model(array('table' => $table, 'ds' => $this->connection));
$tableFields = $this->db->describe($model);
$tableFields['indexes'] = $this->db->index($model);
$tableFields['tableParameters'] = $this->db->readTableParameters($this->db->fullTableName($model, false, false));
if ($type === 'drop') {
$field = $col;
}
if ($type === 'rename') {
$data = array('table' => $table, 'old_name' => $field, 'new_name' => $col);
} else {
$data = array('table' => $table, 'field' => $field);
}
$callbackData = $data;
if ($this->_invokePrecheck('beforeAction', $type . '_field', $data)) {
switch ($type) {
case 'add':
$sql = $this->db->alterSchema(array($table => array('add' => array($field => $col))));
break;
case 'drop':
$sql = $this->db->alterSchema(array($table => array('drop' => array($field => array()))));
break;
case 'change':
if (!isset($col['type']) || $col['type'] == $tableFields[$field]['type']) {
$def = array_merge($tableFields[$field], $col);
} else {
$def = $col;
}
if (!empty($def['length']) && !empty($col['type']) && (substr($col['type'], 0, 4) === 'date' || substr($col['type'], 0, 4) === 'time')) {
$def['length'] = null;
}
$sql = $this->db->alterSchema(array($table => array('change' => array($field => $def))));
break;
case 'rename':
$data = array();
if (array_key_exists($field, $tableFields)) {
$data = $tableFields[$field];
}
$sql = $this->db->alterSchema(array($table => array('change' => array($field => array_merge($data, array('name' => $col))))));
break;
}
if ($this->dry) {
$this->logQuery($sql);
continue;
}
$this->_invokeCallbacks('beforeAction', $type . '_field', $callbackData);
if (@$this->db->execute($sql) === false) {
throw new MigrationException($this, sprintf(__d('migrations', 'SQL Error: %s'), $this->db->error));
}
$this->_invokeCallbacks('afterAction', $type . '_field', $callbackData);
}
}
if ($type !== 'drop') {
$this->_alterIndexes($indexes, $type, $table);
}
}
return true;
}
/**
* Test alter index (changing column of an index). Issue #26
* @return void
*/
public function testAlterIndex()
{
$this->loadFixtures('Post');
$model = new Model(array('table' => 'posts', 'ds' => 'test'));
$fields = $this->db->describe($model);
$indexes = $this->db->index($model);
$this->assertTrue(!empty($indexes) && is_array($indexes));
$this->assertEqual($indexes['PRIMARY']['column'], 'id');
$this->assertFalse(array_key_exists('key', $fields['published']));
$this->assertFalse(array_key_exists('key', $fields['title']));
$migration = new TestCakeMigration(array('up' => array('drop_field' => array('posts' => array('title')), 'create_field' => array('posts' => array('title' => array('type' => 'string', 'null' => false, 'length' => 255), 'indexes' => array('NEW_INDEX' => array('column' => 'title', 'unique' => false))))), 'down' => array('drop_field' => array('posts' => array('title', 'indexes' => array('NEW_INDEX'))), 'create_field' => array('posts' => array('title' => array('type' => 'string', 'null' => false, 'length' => 255, 'after' => 'author_id'))))));
$this->assertTrue($migration->run('up'));
$indexes = $this->db->index($model);
$this->assertArrayHasKey('NEW_INDEX', $indexes);
$this->assertEqual($indexes['NEW_INDEX']['column'], 'title');
$this->assertEqual($indexes['NEW_INDEX']['unique'], 0);
$migration2 = new TestCakeMigration(array('up' => array('drop_field' => array('posts' => array('published', 'title', 'indexes' => array('NEW_INDEX'))), 'create_field' => array('posts' => array('published' => array('type' => 'string', 'null' => true, 'length' => 1, 'default' => 'N'), 'title' => array('type' => 'string', 'null' => false, 'length' => 255), 'indexes' => array('NEW_INDEX' => array('column' => 'published', 'unique' => false))))), 'down' => array('drop_field' => array('posts' => array('published', 'title', 'indexes' => array('NEW_INDEX'))), 'create_field' => array('posts' => array('title' => array('type' => 'string', 'null' => false, 'length' => 255), 'published' => array('type' => 'string', 'null' => true, 'length' => 1, 'default' => 'N', 'after' => 'body'), 'indexes' => array('NEW_INDEX' => array('column' => 'title', 'unique' => false)))))));
$this->assertTrue($migration2->run('up'));
$indexes = $this->db->index($model);
$fields = $this->db->describe($model);
$this->assertFalse(array_key_exists('key', $fields['title']));
$this->assertTrue(array_key_exists('key', $fields['published']));
$this->assertEquals($indexes['NEW_INDEX']['column'], 'published');
$this->assertEqual($indexes['NEW_INDEX']['unique'], 0);
$this->assertTrue($migration2->run('down'));
$indexes = $this->db->index($model);
$fields = $this->db->describe($model);
$this->assertTrue(array_key_exists('key', $fields['title']));
$this->assertFalse(array_key_exists('key', $fields['published']));
$this->assertEquals($indexes['NEW_INDEX']['column'], 'title');
$this->assertEqual($indexes['NEW_INDEX']['unique'], 0);
$this->assertTrue($migration->run('down'));
$fields = $this->db->describe($model);
$indexes = $this->db->index($model);
$this->assertTrue(!empty($indexes) && is_array($indexes));
$this->assertEqual($indexes['PRIMARY']['column'], 'id');
$this->assertFalse(array_key_exists('key', $fields['published']));
$this->assertFalse(array_key_exists('key', $fields['title']));
}
