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()); }