/**
* 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);
}
/**
* 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;
}
/**
* Test describe() on a fixture.
*
* @return void
*/
public function testDescribe()
{
$this->loadFixtures('Apple');
$model = new Apple();
$result = $this->Dbo->describe($model);
$this->assertTrue(isset($result['id']));
$this->assertTrue(isset($result['color']));
$result = $this->Dbo->describe($model->useTable);
$this->assertTrue(isset($result['id']));
$this->assertTrue(isset($result['color']));
}
/**
* test that a describe() gets additional fieldParameters
*
* @return void
*/
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 = $this->Dbo->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');
}
/**
* 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(11) NOT NULL AUTO_INCREMENT,
\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->assertEquals('', $result['limit_date']['default']);
$this->Dbo->execute('DROP TABLE ' . $name);
}
/**
* 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} (
id INT(11) NOT NULL AUTO_INCREMENT,
phone VARCHAR(10),
limit_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY 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 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);
}
/**
* Returns an array of the fields in given table name.
*
* @param string $tableName Name of database table to inspect
* @return array Fields in table. Keys are name and type
*/
function describe(&$model)
{
$cache = parent::describe($model);
if ($cache != null) {
return $cache;
}
$fields = false;
$cols = $this->query('DESCRIBE ' . $this->fullTableName($model));
foreach ($cols as $column) {
$colKey = array_keys($column);
if (isset($column[$colKey[0]]) && !isset($column[0])) {
$column[0] = $column[$colKey[0]];
}
if (isset($column[0])) {
$fields[$column[0]['Field']] = array('type' => $this->column($column[0]['Type']), 'null' => $column[0]['Null'] == 'YES' ? true : false, 'default' => $column[0]['Default'], 'length' => $this->length($column[0]['Type']));
if (!empty($column[0]['Key']) && isset($this->index[$column[0]['Key']])) {
$fields[$column[0]['Field']]['key'] = $this->index[$column[0]['Key']];
}
}
}
$this->__cacheDescription($this->fullTableName($model, false), $fields);
return $fields;
}
/**
* Change an existing column of a table.
*
* @param string $table
* @param string $name
* @param array $options
* @throws MissingTableException if table does not exist in database
* @throws MissingColumnException if column does not exist in the table
* @throws MigrationException if an sql error occurred
* @return Migration
*/
public function changeColumn($table, $name, $options)
{
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)));
}
$existingColumns = $this->_db->describe($table);
if (!array_key_exists($name, $existingColumns)) {
throw new MissingColumnException(__d('migration', 'Column "%s" does not exist in table "%s".', array($name, $table)));
}
$options = array_merge($existingColumns[$name], $options);
if (isset($options['length']) && $options['length'] !== null && isset($options['type']) && preg_match("/^(date|time|text)/", $options['type']) === 1) {
$options['length'] = null;
}
if (isset($options['type']) && preg_match("/^(date|time|integer|boolean)/", $options['type'])) {
if (isset($options['collate'])) {
unset($options['collate']);
}
if (isset($options['charset'])) {
unset($options['charset']);
}
}
if (isset($options['type']) && preg_match("/^(boolean)/", $options['type'])) {
$options['length'] = 1;
if (isset($options['default']) && !is_numeric($options['default']) && $options['default'] !== null) {
$options['default'] = null;
}
}
try {
$this->_db->execute($this->_db->alterSchema(array($table => array('change' => array($name => $options)))));
} catch (Exception $e) {
if (get_class($this->_db) === 'Postgres' && $existingColumns[$name]['type'] !== $options['type'] && preg_match("/Datatype\\smismatch/", $e->getMessage()) === 1) {
throw new MigrationException(__d('migration', 'Typecasting from "%s" to "%s" is not supported natively by CakePHP using PostgreSQL. You have to execute a custom sql query instead.', array($existingColumns[$name]['type'], $options['type'])));
}
throw new MigrationException(__d('migration', 'SQL Error: %s', $e->getMessage()));
}
return $this;
}
/**
* Returns an array of the fields in given table name.
*
* @param string $tableName Name of database table to inspect
* @return array Fields in table. Keys are name and type
*/
function &describe(&$model)
{
$fields = parent::describe($model);
$table = $this->fullTableName($model, false);
$this->_sequenceMap[$table] = array();
if ($fields === null) {
$cols = $this->fetchAll("SELECT DISTINCT column_name AS name, data_type AS type, is_nullable AS null,\n\t\t\t\t\tcolumn_default AS default, ordinal_position AS position, character_maximum_length AS char_length,\n\t\t\t\t\tcharacter_octet_length AS oct_length FROM information_schema.columns\n\t\t\t\tWHERE table_name = " . $this->value($table) . " AND table_schema = " . $this->value($this->config['schema']) . " ORDER BY position", false);
foreach ($cols as $column) {
$colKey = array_keys($column);
if (isset($column[$colKey[0]]) && !isset($column[0])) {
$column[0] = $column[$colKey[0]];
}
if (isset($column[0])) {
$c = $column[0];
if (!empty($c['char_length'])) {
$length = intval($c['char_length']);
} elseif (!empty($c['oct_length'])) {
$length = intval($c['oct_length']);
} else {
$length = $this->length($c['type']);
}
$fields[$c['name']] = array('type' => $this->column($c['type']), 'null' => $c['null'] == 'NO' ? false : true, 'default' => preg_replace("/^'(.*)'\$/", "\$1", preg_replace('/::.*/', '', $c['default'])), 'length' => $length);
if ($c['name'] == $model->primaryKey) {
$fields[$c['name']]['key'] = 'primary';
if ($fields[$c['name']]['type'] !== 'string') {
$fields[$c['name']]['length'] = 11;
}
}
if ($fields[$c['name']]['default'] == 'NULL' || preg_match('/nextval\\([\'"]?([\\w.]+)/', $c['default'], $seq)) {
$fields[$c['name']]['default'] = null;
if (!empty($seq) && isset($seq[1])) {
$this->_sequenceMap[$table][$c['name']] = $seq[1];
}
}
}
}
$this->__cacheDescription($table, $fields);
}
if (isset($model->sequence)) {
$this->_sequenceMap[$table][$model->primaryKey] = $model->sequence;
}
return $fields;
}
/**
* Returns an array of the fields in given table name.
*
* @param Model $model Model object to describe
* @return array Fields in table. Keys are name and type
*/
function describe(&$model)
{
$cache = parent::describe($model);
if ($cache != null) {
return $cache;
}
$table = $this->fullTableName($model, false);
$cols = $this->fetchAll("SELECT COLUMN_NAME as Field, DATA_TYPE as Type, COL_LENGTH('" . $table . "', COLUMN_NAME) as Length, IS_NULLABLE As [Null], COLUMN_DEFAULT as [Default], COLUMNPROPERTY(OBJECT_ID('" . $table . "'), COLUMN_NAME, 'IsIdentity') as [Key], NUMERIC_SCALE as Size FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" . $table . "'", false);
$fields = false;
foreach ($cols as $column) {
$field = $column[0]['Field'];
$fields[$field] = array('type' => $this->column($column[0]['Type']), 'null' => strtoupper($column[0]['Null']) == 'YES', 'default' => preg_replace("/^[(]{1,2}'?([^')]*)?'?[)]{1,2}\$/", "\$1", $column[0]['Default']), 'length' => intval($column[0]['Length']), 'key' => $column[0]['Key'] == '1' ? 'primary' : false);
if ($fields[$field]['default'] === 'null') {
$fields[$field]['default'] = null;
} else {
$this->value($fields[$field]['default'], $fields[$field]['type']);
}
if ($fields[$field]['key'] && $fields[$field]['type'] == 'integer') {
$fields[$field]['length'] = 11;
} elseif (!$fields[$field]['key']) {
unset($fields[$field]['key']);
}
if (in_array($fields[$field]['type'], array('date', 'time', 'datetime', 'timestamp'))) {
$fields[$field]['length'] = null;
}
}
$this->__cacheDescription($this->fullTableName($model, false), $fields);
return $fields;
}
/**
* testDescribe method
*
* @return void
*/
public function testDescribe()
{
$SqlserverTableDescription = new SqlserverTestResultIterator(array((object) array('Default' => '((0))', 'Field' => 'count', 'Key' => 0, 'Length' => '4', 'Null' => 'NO', 'Type' => 'integer'), (object) array('Default' => '', 'Field' => 'body', 'Key' => 0, 'Length' => '-1', 'Null' => 'YES', 'Type' => 'nvarchar'), (object) array('Default' => '', 'Field' => 'published', 'Key' => 0, 'Type' => 'datetime2', 'Length' => 8, 'Null' => 'YES', 'Size' => ''), (object) array('Default' => '', 'Field' => 'id', 'Key' => 1, 'Type' => 'nchar', 'Length' => 72, 'Null' => 'NO', 'Size' => '')));
$this->db->executeResultsStack = array($SqlserverTableDescription);
$dummyModel = $this->model;
$result = $this->db->describe($dummyModel);
$expected = array('count' => array('type' => 'integer', 'null' => false, 'default' => '0', 'length' => 4), 'body' => array('type' => 'text', 'null' => true, 'default' => null, 'length' => null), 'published' => array('type' => 'datetime', 'null' => true, 'default' => '', 'length' => null), 'id' => array('type' => 'string', 'null' => false, 'default' => '', 'length' => 36, 'key' => 'primary'));
$this->assertEquals($expected, $result);
}
/**
* Returns an array of the fields in given table name.
*
* @param string $tableName Name of database table to inspect
* @return array Fields in table. Keys are name and type
* @access public
*/
function describe(&$model)
{
$cache = parent::describe($model);
if ($cache != null) {
return $cache;
}
$fields = array();
$result = $this->fetchAll('PRAGMA table_info(' . $model->tablePrefix . $model->table . ')');
foreach ($result as $column) {
$fields[$column[0]['name']] = array('type' => $this->column($column[0]['type']), 'null' => !$column[0]['notnull'], 'default' => $column[0]['dflt_value'] == 'NULL' ? NULL : $column[0]['dflt_value'], 'length' => $this->length($column[0]['type']));
if ($column[0]['pk'] == 1) {
$colLength = $this->length($column[0]['type']);
$fields[$column[0]['name']] = array('type' => $fields[$column[0]['name']]['type'], 'null' => false, 'default' => $column[0]['dflt_value'] == 'NULL' ? NULL : $column[0]['dflt_value'], 'key' => $this->index['PRI'], 'length' => $colLength != null ? $colLength : 11);
}
}
$this->__cacheDescription($model->tablePrefix . $model->table, $fields);
return $fields;
}
/**
* Returns an array of the fields in given table name.
*
* @param object instance of a model to inspect
* @return array Fields in table. Keys are name and type
* @access public
*/
function describe(&$model)
{
if (!empty($model->sequence)) {
$this->_sequenceMap[$model->table] = $model->sequence;
} elseif (!empty($model->table)) {
$this->_sequenceMap[$model->table] = $model->table . '_seq';
}
$cache = parent::describe($model);
if ($cache != null) {
return $cache;
}
$sql = 'SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH FROM all_tab_columns WHERE table_name = \'';
$sql .= strtoupper($this->fullTableName($model)) . '\'';
if (!$this->execute($sql)) {
return false;
}
$fields = array();
for ($i = 0; $row = $this->fetchRow(); $i++) {
$fields[strtolower($row[0]['COLUMN_NAME'])] = array('type' => $this->column($row[0]['DATA_TYPE']), 'length' => $row[0]['DATA_LENGTH']);
}
$this->__cacheDescription($this->fullTableName($model, false), $fields);
return $fields;
}
/**
* Returns an array of the fields in given table name.
*
* @param Model|string $model Model object to describe, or a string table name.
* @return array Fields in table. Keys are name and type
* @throws CakeException
*/
public function describe($model)
{
$table = $this->fullTableName($model, false);
$cache = parent::describe($table);
if ($cache) {
return $cache;
}
$fields = array();
$cols = $this->_execute('SELECT
COLUMN_NAME as "Field",
DATA_TYPE as "Type",
DATA_LENGTH as "Length",
NULLABLE as "Null",
DATA_DEFAULT as "Default",
DATA_PRECISION as "Precision",
DATA_SCALE as "Size"
FROM user_tab_columns
WHERE table_name = \'' . strtoupper($table) . '\'');
if (!$cols) {
throw new CakeException(__d('cake_dev', 'Could not describe table for %s', $table));
}
$primaryKey = $this->_execute('SELECT column_name
FROM user_constraints
NATURAL JOIN user_cons_columns
WHERE constraint_type=\'P\'
AND status=\'ENABLED\'
AND validated=\'VALIDATED\'
AND table_name=\'' . strtoupper($table) . '\'');
if ($key = $primaryKey->fetch(PDO::FETCH_NUM)) {
$key = $key[0];
}
while ($column = $cols->fetch(PDO::FETCH_OBJ)) {
$field = strtolower($column->Field);
$fields[$field] = array('type' => $this->column($column), 'null' => $column->Null === 'Y' ? true : false, 'default' => $column->Default, 'length' => $this->length($column), 'key' => $key == $column->Field ? 'primary' : false);
if ($fields[$field]['default'] === 'null' || $fields[$field]['default'] === 'NULL') {
$fields[$field]['default'] = null;
}
if ($fields[$field]['default'] !== null) {
$fields[$field]['default'] = preg_replace("/^'?(.*)'/", "\$1", $fields[$field]['default']);
$this->value($fields[$field]['default'], $fields[$field]['type']);
}
if ($fields[$field]['key'] === false) {
unset($fields[$field]['key']);
}
}
$this->_cacheDescription($table, $fields);
$cols->closeCursor();
return $fields;
}
/**
* Returns an array of the fields in given table name.
*
* @param Model|string $model Name of database table to inspect
* @return array Fields in table. Keys are name and type
*/
public function describe($model)
{
$fields = parent::describe($model);
$table = $this->fullTableName($model, false, false);
$this->_sequenceMap[$table] = array();
$cols = null;
if ($fields === null) {
$cols = $this->_execute("SELECT DISTINCT table_schema AS schema, column_name AS name, data_type AS type, is_nullable AS null,\n\t\t\t\t\tcolumn_default AS default, ordinal_position AS position, character_maximum_length AS char_length,\n\t\t\t\t\tcharacter_octet_length AS oct_length FROM information_schema.columns\n\t\t\t\tWHERE table_name = ? AND table_schema = ? ORDER BY position", array($table, $this->config['schema']));
foreach ($cols as $c) {
$type = $c->type;
if (!empty($c->oct_length) && $c->char_length === null) {
if ($c->type == 'character varying') {
$length = null;
$type = 'text';
} elseif ($c->type == 'uuid') {
$length = 36;
} else {
$length = intval($c->oct_length);
}
} elseif (!empty($c->char_length)) {
$length = intval($c->char_length);
} else {
$length = $this->length($c->type);
}
if (empty($length)) {
$length = null;
}
$fields[$c->name] = array('type' => $this->column($type), 'null' => $c->null == 'NO' ? false : true, 'default' => preg_replace("/^'(.*)'\$/", "\$1", preg_replace('/::.*/', '', $c->default)), 'length' => $length);
if ($model instanceof Model) {
if ($c->name == $model->primaryKey) {
$fields[$c->name]['key'] = 'primary';
if ($fields[$c->name]['type'] !== 'string') {
$fields[$c->name]['length'] = 11;
}
}
}
if ($fields[$c->name]['default'] == 'NULL' || preg_match('/nextval\\([\'"]?([\\w.]+)/', $c->default, $seq)) {
$fields[$c->name]['default'] = null;
if (!empty($seq) && isset($seq[1])) {
if (strpos($seq[1], '.') === false) {
$sequenceName = $c->schema . '.' . $seq[1];
} else {
$sequenceName = $seq[1];
}
$this->_sequenceMap[$table][$c->name] = $sequenceName;
}
}
if ($fields[$c->name]['type'] == 'boolean' && !empty($fields[$c->name]['default'])) {
$fields[$c->name]['default'] = constant($fields[$c->name]['default']);
}
}
$this->_cacheDescription($table, $fields);
}
if (isset($model->sequence)) {
$this->_sequenceMap[$table][$model->primaryKey] = $model->sequence;
}
if ($cols) {
$cols->closeCursor();
}
return $fields;
}
/**
* Test describe() behavior for timestamp columns.
*
* @return void
*/
public function testDescribeTimestamp()
{
$this->loadFixtures('User');
$model = ClassRegistry::init('User');
$result = $this->Dbo->describe($model);
$expected = array('id' => array('type' => 'integer', 'null' => false, 'default' => null, 'length' => 11, 'key' => 'primary'), 'user' => array('type' => 'string', 'null' => true, 'default' => null, 'length' => 255), 'password' => array('type' => 'string', 'null' => true, 'default' => null, 'length' => 255), 'created' => array('type' => 'datetime', 'null' => true, 'default' => null, 'length' => null), 'updated' => array('type' => 'datetime', 'null' => true, 'default' => null, 'length' => null));
$this->assertEquals($expected, $result);
}
/**
* Returns an array of the fields in given table name.
*
* @param string $tableName Name of database table to inspect
* @return array Fields in table. Keys are name and type
*/
function describe(&$model)
{
$cache = parent::describe($model);
if ($cache != null) {
return $cache;
}
$fields = false;
$cols = $this->query('SHOW FULL COLUMNS FROM ' . $this->fullTableName($model));
foreach ($cols as $column) {
$colKey = array_keys($column);
if (isset($column[$colKey[0]]) && !isset($column[0])) {
$column[0] = $column[$colKey[0]];
}
if (isset($column[0])) {
$fields[$column[0]['Field']] = array('type' => $this->column($column[0]['Type']), 'null' => $column[0]['Null'] == 'YES' ? true : false, 'default' => $column[0]['Default'], 'length' => $this->length($column[0]['Type']));
if (!empty($column[0]['Key']) && isset($this->index[$column[0]['Key']])) {
$fields[$column[0]['Field']]['key'] = $this->index[$column[0]['Key']];
}
foreach ($this->fieldParameters as $name => $value) {
if (!empty($column[0][$value['column']])) {
$fields[$column[0]['Field']][$name] = $column[0][$value['column']];
}
}
if (isset($fields[$column[0]['Field']]['collate'])) {
$charset = $this->getCharsetName($fields[$column[0]['Field']]['collate']);
if ($charset) {
$fields[$column[0]['Field']]['charset'] = $charset;
}
}
}
}
$this->__cacheDescription($this->fullTableName($model, false), $fields);
return $fields;
}
/**
* Returns an array of the fields in given table name.
*
* @param Model|string $model Either the model or table name you want described.
* @return array Fields in table. Keys are name and type
*/
public function describe($model)
{
$table = $this->fullTableName($model, false, false);
$cache = parent::describe($table);
if ($cache) {
return $cache;
}
$fields = array();
$result = $this->_execute('PRAGMA table_info(' . $this->value($table, 'string') . ')');
foreach ($result as $column) {
$column = (array) $column;
$default = $column['dflt_value'] === 'NULL' ? null : trim($column['dflt_value'], "'");
$fields[$column['name']] = array('type' => $this->column($column['type']), 'null' => !$column['notnull'], 'default' => $default, 'length' => $this->length($column['type']));
if ($column['pk'] == 1) {
$fields[$column['name']]['key'] = $this->index['PRI'];
$fields[$column['name']]['null'] = false;
if (empty($fields[$column['name']]['length'])) {
$fields[$column['name']]['length'] = 11;
}
}
}
$result->closeCursor();
$this->_cacheDescription($table, $fields);
return $fields;
}
/**
316: * Returns an array of the fields in given table name.
317: *
318: * @param Model|string $model Name of database table to inspect or model instance
319: * @return array Fields in table. Keys are name and type
320: * @throws CakeException
321: */
public function describe($model)
{
$key = $this->fullTableName($model, false);
$cache = parent::describe($key);
if ($cache) {
return $cache;
}
$table = $this->fullTableName($model);
$fields = false;
$cols = $this->_execute('SHOW FULL COLUMNS FROM ' . $table);
if (!$cols) {
throw new CakeException(__d('cake_dev', 'Could not describe table for %s', $table));
}
while ($column = $cols->fetch(PDO::FETCH_OBJ)) {
$fields[$column->Field] = array('type' => $this->column($column->Type), 'null' => $column->Null === 'YES' ? true : false, 'default' => $column->Default, 'length' => $this->length($column->Type));
if (!empty($column->Key) && isset($this->index[$column->Key])) {
$fields[$column->Field]['key'] = $this->index[$column->Key];
}
foreach ($this->fieldParameters as $name => $value) {
if (!empty($column->{$value['column']})) {
$fields[$column->Field][$name] = $column->{$value['column']};
}
}
if (isset($fields[$column->Field]['collate'])) {
$charset = $this->getCharsetName($fields[$column->Field]['collate']);
if ($charset) {
$fields[$column->Field]['charset'] = $charset;
}
}
}
$this->_cacheDescription($key, $fields);
$cols->closeCursor();
return $fields;
}
/**
* testDescribe method
*
* @access public
* @return void
*/
function testDescribe()
{
$MssqlTableDescription = array(0 => array(0 => array('Default' => '((0))', 'Field' => 'count', 'Key' => 0, 'Length' => '4', 'Null' => 'NO', 'Type' => 'integer')));
$this->db->fetchAllResultsStack = array($MssqlTableDescription);
$dummyModel = $this->model;
$result = $this->db->describe($dummyModel);
$expected = array('count' => array('type' => 'integer', 'null' => false, 'default' => '0', 'length' => 4));
$this->assertEqual($result, $expected);
}
/**
* Returns an array of the fields in given table name.
*
* @param Model $model Model object to describe
* @return array Fields in table. Keys are name and type
*/
function &describe(&$model)
{
$cache = parent::describe($model);
if ($cache != null) {
return $cache;
}
$fields = array();
$result = db2_columns($this->connection, '', '', strtoupper($this->fullTableName($model)));
while (db2_fetch_row($result)) {
$fields[strtolower(db2_result($result, 'COLUMN_NAME'))] = array('type' => $this->column(strtolower(db2_result($result, 'TYPE_NAME'))), 'null' => db2_result($result, 'NULLABLE'), 'default' => db2_result($result, 'COLUMN_DEF'), 'length' => db2_result($result, 'COLUMN_SIZE'));
}
$this->__cacheDescription($model->tablePrefix . $model->table, $fields);
return $fields;
}
/**
* Returns an array of the fields in given table name.
*
* @param string $tableName Name of database table to inspect
* @return array Fields in table. Keys are name and type
*/
function describe(&$model)
{
$cache = parent::describe($model);
if ($cache != null) {
return $cache;
}
$fields = array();
$result = $this->fetchAll('PRAGMA table_info(' . $model->tablePrefix . $model->table . ')');
foreach ($result as $column) {
$fields[] = array('name' => $column[0]['name'], 'type' => $this->column($column[0]['type']), 'null' => !$column[0]['notnull'], 'default' => $column[0]['dflt_value']);
}
$this->__cacheDescription($model->tablePrefix . $model->table, $fields);
return $fields;
}
/**
* Returns an array of the fields in given table name.
*
* @param Model|string $model Name of database table to inspect or model instance
*
* @return array Fields in table. Keys are name and type
* @throws CakeException
*/
public function describe($model)
{
$key = $this->fullTableName($model, FALSE);
$cache = parent::describe($key);
if ($cache) {
return $cache;
}
$table = $this->fullTableName($model);
$fields = FALSE;
$cols = $this->_execute('SHOW FULL COLUMNS FROM ' . $table);
if (!$cols) {
throw new CakeException(__d('cake_dev', 'Could not describe table for %s', $table));
}
while ($column = $cols->fetch(PDO::FETCH_OBJ)) {
$fields[$column->Field] = array('type' => $this->column($column->Type), 'null' => $column->Null === 'YES' ? TRUE : FALSE, 'default' => $column->Default, 'length' => $this->length($column->Type));
if (in_array($fields[$column->Field]['type'], $this->fieldParameters['unsigned']['types'], TRUE)) {
$fields[$column->Field]['unsigned'] = $this->_unsigned($column->Type);
}
if (in_array($fields[$column->Field]['type'], array('timestamp', 'datetime')) && strtoupper($column->Default) === 'CURRENT_TIMESTAMP') {
$fields[$column->Field]['default'] = NULL;
}
if (!empty($column->Key) && isset($this->index[$column->Key])) {
$fields[$column->Field]['key'] = $this->index[$column->Key];
}
foreach ($this->fieldParameters as $name => $value) {
if (!empty($column->{$value['column']})) {
$fields[$column->Field][$name] = $column->{$value['column']};
}
}
if (isset($fields[$column->Field]['collate'])) {
$charset = $this->getCharsetName($fields[$column->Field]['collate']);
if ($charset) {
$fields[$column->Field]['charset'] = $charset;
}
}
}
$this->_cacheDescription($key, $fields);
$cols->closeCursor();
return $fields;
}
/**
* フィールド情報を取得する
*
* @param Model モデル
* @return array フィールド情報のリスト
* @access public
*/
function describe(&$model)
{
$cache = parent::describe($model);
if ($cache != null) {
return $cache;
}
if (!file_exists($this->config['database'] . DS . $this->config['prefix'] . $model->useTable . '.csv')) {
return null;
}
$fields = false;
// 接続されていない場合は、一時的に接続してヘッダーを取得
// （モデルの初期化時など）
if (empty($this->connected[$model->tablePrefix . $model->table])) {
$this->connect($model, false);
if (empty($this->connection[$model->tablePrefix . $model->table])) {
die(__("DboCsv::describe : Can't find Connection"));
}
$cols = fgetcsv($this->connection[$model->tablePrefix . $model->table], 10240);
$this->disconnect($model->tablePrefix . $model->table);
} else {
$cols = fgetcsv($this->connection[$model->tablePrefix . $model->table], 10240);
if (!$cols) {
// TODO 処理を見直す
// ファイルリソースがあるにも関わらずデータの取得ができない場合がある。（インストール時に再現）
// 取り急ぎの対応として一旦接続を切って再接続している。
// ファイルのロック処理？かもしれない。接続を一旦解除しているので他の部分に影響している可能性もある。
// 追記：接続したままだとロックがかかりっぱなしになるので再度接続を解除する事にした。
$this->disconnect($model->tablePrefix . $model->table);
$this->connect($model, false);
$cols = fgetcsv($this->connection[$model->tablePrefix . $model->table], 10240);
$this->disconnect($model->tablePrefix . $model->table);
}
}
$cols = str_replace("\"", "", $cols);
// ダブルコーテーションを削除
if (!$cols) {
return null;
}
foreach ($cols as $column) {
if ($column) {
if ($column == 'created' || $column == 'modified' || substr($column, strlen($column) - 5, 5) == "_date") {
$fields[$column] = array('type' => $this->column("datetime"), 'null' => true, 'default' => "", 'length' => $this->length("datetime"));
} elseif ($column == 'id') {
// CSVの場合、フィールド名 id は主キーで int(4) 固定とする
$type = 'int(4)';
$fields[$column] = array('type' => $this->column($type), 'null' => false, 'default' => $this->index['PRI'], 'length' => $this->length($type));
} else {
$fields[$column] = array('type' => $this->column("text"), 'null' => true, 'default' => "", 'length' => $this->length("text"));
}
if ($column == 'id' && isset($this->index[$column])) {
$fields[$column]['key'] = $this->index[$column];
}
}
}
$this->__cacheDescription($this->fullTableName($model, false), $fields);
return $fields;
}
/**
* Returns an array of the fields in given table name.
*
* @param string $tableName Name of database table to inspect
* @return array Fields in table. Keys are name and type
*/
function describe(&$model)
{
$cache = parent::describe($model);
if ($cache != null) {
return $cache;
}
$fields = false;
$cols = $this->query('DESC ' . $this->fullTableName($model));
foreach ($cols as $column) {
$colKey = array_keys($column);
if (isset($column[$colKey[0]]) && !isset($column[0])) {
$column[0] = $column[$colKey[0]];
}
if (isset($column[0])) {
$fields[$column[0]['Field']] = array('type' => $this->column($column[0]['Type']), 'null' => $column[0]['Null'], 'length' => $this->length($column[0]['Type']));
}
}
$this->__cacheDescription($model->tablePrefix . $model->table, $fields);
return $fields;
}
/**
* Returns an array of the fields in the table used by the given model.
*
* @param AppModel $model Model object
* @return array Fields in table. Keys are name and type
*/
function describe(&$model)
{
$cache = parent::describe($model);
if ($cache != null) {
return $cache;
}
$fields = false;
$cols = $this->_adodb->MetaColumns($this->fullTableName($model, false));
foreach ($cols as $column) {
$fields[$column->name] = array('type' => $this->column($column->type), 'null' => !$column->not_null, 'length' => $column->max_length);
if ($column->has_default) {
$fields[$column->name]['default'] = $column->default_value;
}
if ($column->primary_key == 1) {
$fields[$column->name]['key'] = 'primary';
}
}
$this->__cacheDescription($this->fullTableName($model, false), $fields);
return $fields;
}
/**
* Returns an array of the fields in given table name.
*
* @param Model $model Model object to describe
* @return array Fields in table. Keys are name and type
*/
function describe(&$model)
{
$this->modeltmp[$model->table] = $model->name;
$cache = parent::describe($model);
if ($cache != null) {
return $cache;
}
$fields = false;
$sql = "SELECT * FROM " . $this->fullTableName($model, false);
$rs = ibase_query($sql);
$coln = ibase_num_fields($rs);
$fields = false;
for ($i = 0; $i < $coln; $i++) {
$col_info = ibase_field_info($rs, $i);
$col_info['type'] = $this->column($col_info['type']);
$fields[strtolower($col_info['name'])] = array('type' => $col_info['type'], 'null' => '', 'length' => $col_info['length']);
}
$this->__cacheDescription($this->fullTableName($model, false), $fields);
return $fields;
}
/**
* Alter Table method
*
* @param string $type Type of operation to be done
* @param array $tables List of tables and fields
* @return boolean 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);
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;
}
$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;
}
