PHP DboSource::describe方法代碼示例

/**
 * 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;
 }