DataSource Query abstraction
| public query ( ) : resource | ||
| return | resource | Result resource identifier. |
/**
* test that describe does not corrupt UUID primary keys
*
* @return void
*/
public function testDescribeWithUuidPrimaryKey() {
$tableName = 'uuid_tests';
$this->Dbo->query("CREATE TABLE {$tableName} (id VARCHAR(36) PRIMARY KEY, name VARCHAR, created DATETIME, modified DATETIME)");
$Model = new Model(array('name' => 'UuidTest', 'ds' => 'test', 'table' => 'uuid_tests'));
$result = $this->Dbo->describe($Model);
$expected = array(
'type' => 'string',
'length' => 36,
'null' => false,
'default' => null,
'key' => 'primary',
);
$this->assertEqual($result['id'], $expected);
$this->Dbo->query('DROP TABLE ' . $tableName);
$tableName = 'uuid_tests';
$this->Dbo->query("CREATE TABLE {$tableName} (id CHAR(36) PRIMARY KEY, name VARCHAR, created DATETIME, modified DATETIME)");
$Model = new Model(array('name' => 'UuidTest', 'ds' => 'test', 'table' => 'uuid_tests'));
$result = $this->Dbo->describe($Model);
$expected = array(
'type' => 'string',
'length' => 36,
'null' => false,
'default' => null,
'key' => 'primary',
);
$this->assertEqual($result['id'], $expected);
$this->Dbo->query('DROP TABLE ' . $tableName);
}
/**
* Test that describe() ignores `default current_timestamp` in datetime columns.
* This is for MySQL >= 5.6.
*
* @return void
*/
public function testDescribeHandleCurrentTimestampDatetime()
{
$mysqlVersion = $this->Dbo->query('SELECT VERSION() as version', array('log' => FALSE));
$this->skipIf(version_compare($mysqlVersion[0][0]['version'], '5.6.0', '<'));
$name = $this->Dbo->fullTableName('timestamp_default_values');
$sql = <<<SQL
CREATE TABLE {$name} (
\tid INT(11) NOT NULL AUTO_INCREMENT,
\tphone VARCHAR(10),
\tlimit_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
\tPRIMARY KEY(id)
);
SQL;
$this->Dbo->execute($sql);
$model = new Model(array('table' => 'timestamp_default_values', 'ds' => 'test', 'alias' => 'TimestampDefaultValue'));
$result = $this->Dbo->describe($model);
$this->Dbo->execute('DROP TABLE ' . $name);
$this->assertNull($result['limit_date']['default']);
$schema = new CakeSchema(array('connection' => 'test', 'testdescribes' => $result));
$result = $this->Dbo->createSchema($schema);
$this->assertContains('`limit_date` datetime NOT NULL,', $result);
}
/**
* 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));
}
/**
* test Index introspection.
*
* @access public
* @return void
*/
function testIndex()
{
$name = $this->db->fullTableName('with_a_key');
$this->db->query('CREATE TABLE ' . $name . ' ("id" int(11) PRIMARY KEY, "bool" int(1), "small_char" varchar(50), "description" varchar(40) );');
$this->db->query('CREATE INDEX pointless_bool ON ' . $name . '("bool")');
$this->db->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->db->index($name);
$this->assertEqual($expected, $result);
$this->db->query('DROP TABLE ' . $name);
$this->db->query('CREATE TABLE ' . $name . ' ("id" int(11) PRIMARY KEY, "bool" int(1), "small_char" varchar(50), "description" varchar(40) );');
$this->db->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->db->index($name);
$this->assertEqual($expected, $result);
$this->db->query('DROP TABLE ' . $name);
}
/**
* @param $fileName
* @param $object
* @param null $search
*
* @return bool
*/
private function __executeSQL($fileName, DboSource $object, $search = null)
{
if (file_exists(SCHEMA . $fileName)) {
$sql = file_get_contents(SCHEMA . $fileName);
$contents = explode(';', $sql);
if ($search && count($search) > 0) {
foreach ($contents as $content) {
$statements[] = str_replace(array_keys($search), array_values($search), $content);
}
} else {
$statements = $contents;
}
/** @var $statements [] */
foreach ($statements as $statement) {
if (trim($statement) != '') {
$object->query($statement);
}
}
return true;
} else {
$this->Session->setFlash(__d('hurad', 'File "Config/Schema/%s" not exists.', $fileName), 'flash_message', array('class' => 'danger'));
return false;
}
}