Generate a database-native schema for the given Schema object
| public createSchema ( CakeSchema $schema, string $tableName = null ) : string | ||
| $schema | CakeSchema | An instance of a subclass of CakeSchema |
| $tableName | string | Optional. If specified only the table name given will be generated. Otherwise, all tables defined in the schema are generated. |
| return | string | |
/**
* Create a new table.
*
* @param string $table
* @param array $fields
* @throws TableAlreadyExistsException
* @throws MigrationException if an sql error occured
* @return Migration
*/
public function createTable($table, $fields)
{
if (in_array($this->_db->fullTableName($table, false, false), $this->_db->listSources())) {
throw new TableAlreadyExistsException(__d('migration', 'Table "%s" already exists in database.', $this->_db->fullTableName($table, false, false)));
}
$this->_schema->tables = array($table => $fields);
try {
$this->_db->execute($this->_db->createSchema($this->_schema));
} catch (Exception $e) {
throw new MigrationException(__d('migration', 'SQL Error: %s', $e->getMessage()));
}
return $this;
}
/**
* Test that two columns with key => primary doesn't create invalid sql.
*
* @return void
*/
public function testTwoColumnsWithPrimaryKey()
{
$schema = new CakeSchema(array('connection' => 'test', 'roles_users' => array('role_id' => array('type' => 'integer', 'null' => false, 'default' => null, 'key' => 'primary'), 'user_id' => array('type' => 'integer', 'null' => false, 'default' => null, 'key' => 'primary'), 'indexes' => array('user_role_index' => array('column' => array('role_id', 'user_id'), 'unique' => 1), 'user_index' => array('column' => 'user_id', 'unique' => 0)))));
$result = $this->Dbo->createSchema($schema);
$this->assertContains('`role_id` int(11) NOT NULL,', $result);
$this->assertContains('`user_id` int(11) NOT NULL,', $result);
}
/**
* test that a describe() gets additional fieldParameters
*
* @return void
*/
public function testDescribeGettingFieldParameters() {
$schema = new CakeSchema(array(
'connection' => 'test',
'testdescribes' => array(
'id' => array('type' => 'integer', 'key' => 'primary'),
'stringy' => array(
'type' => 'string',
'null' => true,
'charset' => 'cp1250',
'collate' => 'cp1250_general_ci',
),
'other_col' => array(
'type' => 'string',
'null' => false,
'charset' => 'latin1',
'comment' => 'Test Comment'
)
)
));
$this->Dbo->execute($this->Dbo->createSchema($schema));
$model = new CakeTestModel(array('table' => 'testdescribes', 'name' => 'Testdescribes'));
$result = $model->getDataSource()->describe($model);
$this->Dbo->execute($this->Dbo->dropSchema($schema));
$this->assertEqual($result['stringy']['collate'], 'cp1250_general_ci');
$this->assertEqual($result['stringy']['charset'], 'cp1250');
$this->assertEqual($result['other_col']['comment'], 'Test Comment');
}
/**
* sql create statement
*
* @param $datasource
* @param $tablename
* @param $exclude_missing_tables
* @return string
*/
function getCreateSql($datasource, $tablename = null, $exclude_missing_tables = false)
{
if (!$this->_checkCurrentDatasource($datasource)) {
$this->_setupDataSource();
}
$this->Schema->tables = $this->_getProcessTables($tablename, $exclude_missing_tables);
$sql = $this->DataSource->createSchema($this->Schema);
return $this->out($sql);
}
/**
* Test that the primary flag is handled correctly.
*
* @return void
*/
public function testCreateSchemaAutoPrimaryKey()
{
$schema = new CakeSchema();
$schema->tables = array('no_indexes' => array('id' => array('type' => 'integer', 'null' => false, 'key' => 'primary'), 'data' => array('type' => 'integer', 'null' => false), 'indexes' => array()));
$result = $this->Dbo->createSchema($schema, 'no_indexes');
$this->assertContains('PRIMARY KEY (`id`)', $result);
$this->assertNotContains('UNIQUE KEY', $result);
$schema->tables = array('primary_index' => array('id' => array('type' => 'integer', 'null' => false), 'data' => array('type' => 'integer', 'null' => false), 'indexes' => array('PRIMARY' => array('column' => 'id', 'unique' => 1), 'some_index' => array('column' => 'data', 'unique' => 1))));
$result = $this->Dbo->createSchema($schema, 'primary_index');
$this->assertContains('PRIMARY KEY (`id`)', $result);
$this->assertContains('UNIQUE KEY `some_index` (`data`)', $result);
$schema->tables = array('primary_flag_has_index' => array('id' => array('type' => 'integer', 'null' => false, 'key' => 'primary'), 'data' => array('type' => 'integer', 'null' => false), 'indexes' => array('some_index' => array('column' => 'data', 'unique' => 1))));
$result = $this->Dbo->createSchema($schema, 'primary_flag_has_index');
$this->assertContains('PRIMARY KEY (`id`)', $result);
$this->assertContains('UNIQUE KEY `some_index` (`data`)', $result);
}
/**
* Create Table method
*
* @param string $type Type of operation to be done, in this case 'create_table'
* @param array $tables List of tables to be created
* @return boolean Return true in case of success, otherwise false
* @throws MigrationException
*/
protected function _createTable($type, $tables)
{
foreach ($tables as $table => $fields) {
if ($this->_invokePrecheck('beforeAction', 'create_table', array('table' => $table))) {
$this->Schema->tables = array($table => $fields);
$this->_invokeCallbacks('beforeAction', 'create_table', array('table' => $table));
try {
$this->db->execute($this->db->createSchema($this->Schema));
} catch (Exception $exception) {
throw new MigrationException($this, __d('migrations', 'SQL Error: %s', $exception->getMessage()));
}
}
$this->_invokeCallbacks('afterAction', 'create_table', array('table' => $table));
}
return true;
}
/**
* Test that describe ignores `default current_timestamp` in timestamp columns.
*
* @return void
*/
public function testDescribeHandleCurrentTimestamp()
{
$name = $this->Dbo->fullTableName('timestamp_default_values');
$sql = <<<SQL
CREATE TABLE {$name} (
\tid INT NOT NULL,
\tphone VARCHAR(10),
\tlimit_date TIMESTAMP 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" timestamp 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));
}
/**
* Run before all tests execute, should return SQL statement to create table for this fixture could be executed successfully.
*
* @param DboSource $db An instance of the database object used to create the fixture table
* @return bool True on success, false on failure
*/
public function create($db)
{
if (!isset($this->fields) || empty($this->fields)) {
return false;
}
if (empty($this->fields['tableParameters']['engine'])) {
$canUseMemory = $this->canUseMemory;
foreach ($this->fields as $args) {
if (is_string($args)) {
$type = $args;
} elseif (!empty($args['type'])) {
$type = $args['type'];
} else {
continue;
}
if (in_array($type, array('blob', 'text', 'binary'))) {
$canUseMemory = false;
break;
}
}
if ($canUseMemory) {
$this->fields['tableParameters']['engine'] = 'MEMORY';
}
}
$this->Schema->build(array($this->table => $this->fields));
try {
$db->execute($db->createSchema($this->Schema), array('log' => false));
$this->created[] = $db->configKeyName;
} catch (Exception $e) {
$msg = __d('cake_dev', 'Fixture creation for "%s" failed "%s"', $this->table, $e->getMessage());
CakeLog::error($msg);
trigger_error($msg, E_USER_WARNING);
return false;
}
return true;
}
/**
* Generate a database-native schema for the given Schema object
*
* @param CakeSchema $schema An instance of a subclass of CakeSchema
* @param string $tableName Optional. If specified only the table name given will be generated.
* Otherwise, all tables defined in the schema are generated.
* @return string
*/
public function createSchema($schema, $tableName = null)
{
$out = parent::createSchema($schema, $tableName);
foreach ($schema->tables as $curTable => $columns) {
if (!$tableName || $tableName === $curTable) {
foreach ($columns as $name => $col) {
if (!isset($col['name'])) {
$col['name'] = $name;
}
$out .= $this->_buildCommentExtProperty($curTable, $col, 'add');
}
}
}
return $out;
}
