Gets full table name including prefix
| public fullTableName ( Model | string $model, boolean $quote = true, boolean $schema = true ) : string | ||
| $model | Model | string | Either a Model object or a string table name. |
| $quote | boolean | Whether you want the table name quoted. |
| $schema | boolean | Whether you want the schema name included. |
| return | string | Full quoted table name |
/**
* 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);
}
/**
* testRealQueries method
*
* @return void
*/
public function testRealQueries()
{
$this->loadFixtures('Apple', 'Article', 'User', 'Comment', 'Tag', 'Sample', 'ArticlesTag');
$Apple = ClassRegistry::init('Apple');
$Article = ClassRegistry::init('Article');
$result = $this->Dbo->rawQuery('SELECT color, name FROM ' . $this->Dbo->fullTableName('apples'));
$this->assertTrue(!empty($result));
$result = $this->Dbo->fetchRow($result);
$expected = array($this->Dbo->fullTableName('apples', false, false) => array('color' => 'Red 1', 'name' => 'Red Apple 1'));
$this->assertEquals($expected, $result);
$result = $this->Dbo->fetchAll('SELECT name FROM ' . $this->Dbo->fullTableName('apples') . ' ORDER BY id');
$expected = array(array($this->Dbo->fullTableName('apples', false, false) => array('name' => 'Red Apple 1')), array($this->Dbo->fullTableName('apples', false, false) => array('name' => 'Bright Red Apple')), array($this->Dbo->fullTableName('apples', false, false) => array('name' => 'green blue')), array($this->Dbo->fullTableName('apples', false, false) => array('name' => 'Test Name')), array($this->Dbo->fullTableName('apples', false, false) => array('name' => 'Blue Green')), array($this->Dbo->fullTableName('apples', false, false) => array('name' => 'My new apple')), array($this->Dbo->fullTableName('apples', false, false) => array('name' => 'Some odd color')));
$this->assertEquals($expected, $result);
$result = $this->Dbo->field($this->Dbo->fullTableName('apples', false, false), 'SELECT color, name FROM ' . $this->Dbo->fullTableName('apples') . ' ORDER BY id');
$expected = array('color' => 'Red 1', 'name' => 'Red Apple 1');
$this->assertEquals($expected, $result);
$Apple->unbindModel(array(), false);
$result = $this->Dbo->read($Apple, array('fields' => array($Apple->escapeField('name')), 'conditions' => null, 'recursive' => -1));
$expected = array(array('Apple' => array('name' => 'Red Apple 1')), array('Apple' => array('name' => 'Bright Red Apple')), array('Apple' => array('name' => 'green blue')), array('Apple' => array('name' => 'Test Name')), array('Apple' => array('name' => 'Blue Green')), array('Apple' => array('name' => 'My new apple')), array('Apple' => array('name' => 'Some odd color')));
$this->assertEquals($expected, $result);
$result = $this->Dbo->read($Article, array('fields' => array('id', 'user_id', 'title'), 'conditions' => null, 'recursive' => 1));
$this->assertTrue(Set::matches('/Article[id=1]', $result));
$this->assertTrue(Set::matches('/Comment[id=1]', $result));
$this->assertTrue(Set::matches('/Comment[id=2]', $result));
$this->assertFalse(Set::matches('/Comment[id=10]', $result));
}
/**
* TestRenameField method
*
* @return void
*/
public function testRenameField()
{
$this->loadFixtures('User', 'Post');
$Model = new Model(array('table' => 'posts', 'ds' => 'test'));
$Migration = new TestPrecheckCakeMigration(array('up' => array('rename_field' => array('posts' => array('updated' => 'renamed_updated'))), 'down' => array('rename_field' => array('posts' => array('renamed_updated' => 'updated'))), 'precheck' => 'Migrations.PrecheckCondition'));
$Migration->initDb();
$fields = $this->db->describe($Model);
$this->assertTrue(isset($fields['updated']));
$this->assertFalse(isset($fields['renamed_updated']));
$this->assertTrue($Migration->Precheck->beforeAction($Migration, 'rename_field', array('table' => $this->db->fullTableName('posts', false, false), 'old_name' => 'updated', 'new_name' => 'renamed_updated')));
$this->assertTrue($Migration->run('up'));
$fields = $this->db->describe($Model);
$this->assertFalse(isset($fields['updated']));
$this->assertTrue(isset($fields['renamed_updated']));
$this->assertFalse($Migration->Precheck->beforeAction($Migration, 'rename_field', array('table' => $this->db->fullTableName('posts', false, false), 'old_name' => 'updated', 'new_name' => 'renamed_updated')));
$this->assertTrue($Migration->Precheck->beforeAction($Migration, 'rename_field', array('table' => $this->db->fullTableName('posts', false, false), 'old_name' => 'renamed_updated', 'new_name' => 'updated')));
try {
$Migration->run('up');
} catch (MigrationException $e) {
$this->fail('Exception triggered ' . $e->getMessage());
}
$this->assertTrue($Migration->run('down'));
$fields = $this->db->describe($Model);
$this->assertTrue(isset($fields['updated']));
$this->assertFalse(isset($fields['renamed_updated']));
$this->assertTrue($Migration->Precheck->beforeAction($Migration, 'rename_field', array('table' => $this->db->fullTableName('posts', false, false), 'old_name' => 'updated', 'new_name' => 'renamed_updated')));
$this->assertFalse($Migration->Precheck->beforeAction($Migration, 'rename_field', array('table' => $this->db->fullTableName('posts', false, false), 'old_name' => 'renamed_updated', 'new_name' => 'updated')));
try {
$Migration->run('down');
} catch (MigrationException $e) {
$this->fail('Exception triggered ' . $e->getMessage());
}
}
/**
* sql insert statement
*
* @param $datasource
* @param $tablename
* @param $exclude_missing_tables
* @param $return if want return sql string, set true.
* @return string
*/
function getInsertSql($datasource, $tablename, $exclude_missing_tables = false, $return = false)
{
if (!$this->_checkCurrentDatasource($datasource)) {
$this->_setupDataSource();
}
if (!$return && (empty($this->File) || !$this->File->writable())) {
return false;
}
$tables = $this->_getProcessTables($tablename, $exclude_missing_tables);
$insert_sql = '';
foreach ($tables as $table => $fields) {
/* @var $model AppModel */
$model = ClassRegistry::init(array('class' => Inflector::classify($table), 'table' => $table));
$field_names = array_keys($this->DataSource->describe($model));
$full_tablename = $this->DataSource->fullTableName($model);
$all_fields = implode(', ', array_map(array($this->DataSource, 'name'), $field_names));
$count_query = array('table' => $full_tablename, 'fields' => 'count(*) ' . $this->DataSource->alias . 'count', 'alias' => $this->DataSource->alias . $this->DataSource->name($model->alias), 'joins' => '', 'conditions' => 'WHERE 1=1', 'group' => '', 'order' => '', 'limit' => '');
$count_sql = $this->DataSource->renderStatement('select', $count_query);
$total = $this->DataSource->fetchRow($count_sql);
if (is_array($total)) {
$total = $total[0]['count'];
}
$query = array('table' => $full_tablename, 'fields' => implode(', ', $this->DataSource->fields($model)), 'alias' => $this->DataSource->alias . $this->DataSource->name($model->alias), 'joins' => '', 'conditions' => '', 'group' => '', 'order' => '', 'limit' => '');
$limit = 100;
$record = array();
for ($offset = 0; $offset < $total; $offset += $limit) {
$query['limit'] = $this->DataSource->limit($limit, $offset);
$select_sql = $this->DataSource->renderStatement('select', $query);
$datas = $this->DataSource->fetchAll($select_sql, false);
foreach ($datas as $record) {
$insert_query = array('table' => $full_tablename, 'fields' => $all_fields, 'values' => implode(', ', array_map(array($this->DataSource, 'value'), array_values($record[$model->alias]))));
$_sql = $this->out($this->DataSource->renderStatement('create', $insert_query) . ';');
if ($return) {
$insert_sql .= $_sql;
}
}
}
// -- sequence update section for postgres
// NOTE: only primary key sequence..
if (method_exists($this->DataSource, 'getSequence')) {
foreach ($fields as $field => $column) {
if ($field == 'indexes' || empty($record)) {
continue;
}
if ($column['type'] == 'integer' && isset($column['key']) && $column['key'] == 'primary') {
// only primary key
$sequence_name = $this->DataSource->getSequence($this->DataSource->fullTableName($model, false), $field);
$_sql = $this->out(sprintf('SELECT setval(%s, %s);', $this->DataSource->value($sequence_name), $record[$model->alias][$field]));
if ($return) {
$insert_sql .= $_sql;
}
}
}
}
}
return $insert_sql;
}
/**
* Rename Table method
*
* @param string $type Type of operation to be done, this case 'rename_table'
* @param array $tables List of tables to be renamed
* @return boolean Return true in case of success, otherwise false
* @throws MigrationException
*/
protected function _renameTable($type, $tables)
{
foreach ($tables as $oldName => $newName) {
if ($this->_invokePrecheck('beforeAction', 'rename_table', array('old_name' => $oldName, 'new_name' => $newName))) {
$sql = 'ALTER TABLE ' . $this->db->fullTableName($oldName) . ' RENAME TO ' . $this->db->fullTableName($newName) . ';';
$this->_invokeCallbacks('beforeAction', 'rename_table', array('old_name' => $oldName, 'new_name' => $newName));
if (@$this->db->execute($sql) === false) {
throw new MigrationException($this, __d('migrations', 'SQL Error: %s', $this->db->error));
}
$this->_invokeCallbacks('afterAction', 'rename_table', array('old_name' => $oldName, 'new_name' => $newName));
}
}
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);
}
public function testResetSequence()
{
$model = new Article();
$table = $this->Dbo->fullTableName($model, false);
$fields = array('id', 'user_id', 'title', 'body', 'published');
$values = array(array(1, 1, 'test', 'first post', false), array(2, 1, 'test 2', 'second post post', false));
$this->Dbo->insertMulti($table, $fields, $values);
$sequence = $this->Dbo->getSequence($table);
$result = $this->Dbo->rawQuery("SELECT nextval('{$sequence}')");
$original = $result->fetch(PDO::FETCH_ASSOC);
$this->assertTrue($this->Dbo->resetSequence($table, 'id'));
$result = $this->Dbo->rawQuery("SELECT currval('{$sequence}')");
$new = $result->fetch(PDO::FETCH_ASSOC);
$this->assertTrue($new['currval'] > $original['nextval'], 'Sequence did not update');
}
/**
* Test index generation from table info.
*
* @return void
*/
public function testIndexGeneration()
{
$name = $this->Dbo->fullTableName('index_test', false, false);
$this->Dbo->query('CREATE TABLE ' . $name . ' ("id" serial NOT NULL PRIMARY KEY, "bool" integer, "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('unique' => true, 'column' => 'id'), 'pointless_bool' => array('unique' => false, 'column' => 'bool'), 'char_index' => array('unique' => true, 'column' => 'small_char'));
$result = $this->Dbo->index($name);
$this->Dbo->query('DROP TABLE ' . $name);
$this->assertEquals($expected, $result);
$name = $this->Dbo->fullTableName('index_test_2', false, false);
$this->Dbo->query('CREATE TABLE ' . $name . ' ("id" serial NOT NULL PRIMARY KEY, "bool" integer, "small_char" varchar(50), "description" varchar(40) )');
$this->Dbo->query('CREATE UNIQUE INDEX multi_col ON ' . $name . '("small_char", "bool")');
$expected = array('PRIMARY' => array('unique' => true, 'column' => 'id'), 'multi_col' => array('unique' => true, 'column' => array('small_char', 'bool')));
$result = $this->Dbo->index($name);
$this->Dbo->query('DROP TABLE ' . $name);
$this->assertEquals($expected, $result);
}
/**
* 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;
}
public function testTruncateLongIndexKey()
{
$migration = new TestCakeMigration(array('up' => array('create_table' => array('migration_categories' => array('id' => array('type' => 'string', 'length ' => 36, 'null' => false, 'key' => 'primary'), 'description' => array('type' => 'string', 'null' => false, 'length' => 256, 'default' => null), 'info' => array('type' => 'string', 'length' => 256, 'null' => false, 'default' => null), 'indexes' => array('TESTING_INDEX' => array('column' => array('description', 'info'), 'unique' => 1))))), 'down' => array('drop_table' => array('migration_categories'))));
$sources = $this->db->listSources();
$this->assertFalse(in_array($this->db->fullTableName('migration_categories', false, false), $sources));
try {
$migration->run('up');
$this->fail('No exception triggered');
} catch (MigrationException $e) {
$this->assertPattern('/SQL Error/', $e->getMessage());
}
$this->assertFalse(in_array($this->db->fullTableName('migration_categories', false, false), $sources));
}
/**
* Check if the provided $table exists.
*
* @param string $table
* @return bool
*/
public function tableExists($table)
{
return in_array($this->_db->fullTableName($table, false, false), $this->_db->listSources());
}
