PHP DboSource::fields方法代碼示例

 /**
  * 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 a full example of using virtual fields
  *
  * @return void
  */
 public function testVirtualFieldsFetch()
 {
     $this->loadFixtures('Article', 'Comment');
     $Article = ClassRegistry::init('Article');
     $Article->virtualFields = array('comment_count' => 'SELECT COUNT(*) FROM ' . $this->Dbo->fullTableName('comments') . ' WHERE Article.id = ' . $this->Dbo->fullTableName('comments') . '.article_id');
     $conditions = array('comment_count >' => 2);
     $query = 'SELECT ' . join(',', $this->Dbo->fields($Article, null, array('id', 'comment_count'))) . ' FROM ' . $this->Dbo->fullTableName($Article) . ' Article ' . $this->Dbo->conditions($conditions, true, true, $Article);
     $result = $this->Dbo->fetchAll($query);
     $expected = array(array('Article' => array('id' => 1, 'comment_count' => 4)));
     $this->assertEquals($expected, $result);
 }

 /**
  * Test it is possible to do a SELECT COUNT(DISTINCT Model.field)
  * query in postgres and it gets correctly quoted
  *
  * @return void
  */
 public function testQuoteDistinctInFunction()
 {
     $this->loadFixtures('Article');
     $Article = new Article();
     $result = $this->Dbo->fields($Article, null, array('COUNT(DISTINCT Article.id)'));
     $expected = array('COUNT(DISTINCT "Article"."id")');
     $this->assertEquals($expected, $result);
     $result = $this->Dbo->fields($Article, null, array('COUNT(DISTINCT id)'));
     $expected = array('COUNT(DISTINCT "id")');
     $this->assertEquals($expected, $result);
     $result = $this->Dbo->fields($Article, null, array('COUNT(DISTINCT FUNC(id))'));
     $expected = array('COUNT(DISTINCT FUNC("id"))');
     $this->assertEquals($expected, $result);
 }

 /**
  * Test Dbo value method
  *
  * @access public
  */
 function testQuoting()
 {
     $result = $this->db->fields($this->model);
     $expected = array('`AdodbTestModel`.`id` AS `AdodbTestModel__id`', '`AdodbTestModel`.`client_id` AS `AdodbTestModel__client_id`', '`AdodbTestModel`.`name` AS `AdodbTestModel__name`', '`AdodbTestModel`.`login` AS `AdodbTestModel__login`', '`AdodbTestModel`.`passwd` AS `AdodbTestModel__passwd`', '`AdodbTestModel`.`addr_1` AS `AdodbTestModel__addr_1`', '`AdodbTestModel`.`addr_2` AS `AdodbTestModel__addr_2`', '`AdodbTestModel`.`zip_code` AS `AdodbTestModel__zip_code`', '`AdodbTestModel`.`city` AS `AdodbTestModel__city`', '`AdodbTestModel`.`country` AS `AdodbTestModel__country`', '`AdodbTestModel`.`phone` AS `AdodbTestModel__phone`', '`AdodbTestModel`.`fax` AS `AdodbTestModel__fax`', '`AdodbTestModel`.`url` AS `AdodbTestModel__url`', '`AdodbTestModel`.`email` AS `AdodbTestModel__email`', '`AdodbTestModel`.`comments` AS `AdodbTestModel__comments`', '`AdodbTestModel`.`last_login` AS `AdodbTestModel__last_login`', '`AdodbTestModel`.`created` AS `AdodbTestModel__created`', '`AdodbTestModel`.`updated` AS `AdodbTestModel__updated`');
     $this->assertEqual($result, $expected);
     $expected = "'1.2'";
     $result = $this->db->value(1.2, 'float');
     $this->assertEqual($expected, $result);
     $expected = "'1,2'";
     $result = $this->db->value('1,2', 'float');
     $this->assertEqual($expected, $result);
     $expected = "'4713e29446'";
     $result = $this->db->value('4713e29446');
     $this->assertEqual($expected, $result);
     $expected = "'10010001'";
     $result = $this->db->value('10010001');
     $this->assertEqual($expected, $result);
     $expected = "'00010010001'";
     $result = $this->db->value('00010010001');
     $this->assertEqual($expected, $result);
 }

 /**
  * Test field and value quoting method
  *
  * @access public
  */
 function testQuoting()
 {
     $result = $this->db2->fields($this->model);
     $expected = array('"PostgresTestModel"."id" AS "PostgresTestModel__id"', '"PostgresTestModel"."client_id" AS "PostgresTestModel__client_id"', '"PostgresTestModel"."name" AS "PostgresTestModel__name"', '"PostgresTestModel"."login" AS "PostgresTestModel__login"', '"PostgresTestModel"."passwd" AS "PostgresTestModel__passwd"', '"PostgresTestModel"."addr_1" AS "PostgresTestModel__addr_1"', '"PostgresTestModel"."addr_2" AS "PostgresTestModel__addr_2"', '"PostgresTestModel"."zip_code" AS "PostgresTestModel__zip_code"', '"PostgresTestModel"."city" AS "PostgresTestModel__city"', '"PostgresTestModel"."country" AS "PostgresTestModel__country"', '"PostgresTestModel"."phone" AS "PostgresTestModel__phone"', '"PostgresTestModel"."fax" AS "PostgresTestModel__fax"', '"PostgresTestModel"."url" AS "PostgresTestModel__url"', '"PostgresTestModel"."email" AS "PostgresTestModel__email"', '"PostgresTestModel"."comments" AS "PostgresTestModel__comments"', '"PostgresTestModel"."last_login" AS "PostgresTestModel__last_login"', '"PostgresTestModel"."created" AS "PostgresTestModel__created"', '"PostgresTestModel"."updated" AS "PostgresTestModel__updated"');
     $this->assertEqual($result, $expected);
     $expected = "'1.2'";
     $result = $this->db2->value(1.2, 'float');
     $this->assertIdentical($expected, $result);
     $expected = "'1,2'";
     $result = $this->db2->value('1,2', 'float');
     $this->assertIdentical($expected, $result);
 }

 /**
  * Generates the fields list of an SQL query.
  *
  * @param Model $model
  * @param string $alias Alias tablename
  * @param mixed $fields
  * @return array
  */
 function fields(&$model, $alias = null, $fields = array(), $quote = true)
 {
     if (empty($alias)) {
         $alias = $model->alias;
     }
     $fields = parent::fields($model, $alias, $fields, false);
     if (!$quote) {
         return $fields;
     }
     $count = count($fields);
     if ($count >= 1 && $fields[0] != '*' && strpos($fields[0], 'COUNT(*)') === false) {
         for ($i = 0; $i < $count; $i++) {
             if (!preg_match('/^.+\\(.*\\)/', $fields[$i]) && !preg_match('/\\s+AS\\s+/', $fields[$i])) {
                 $prepend = '';
                 if (strpos($fields[$i], 'DISTINCT') !== false) {
                     $prepend = 'DISTINCT ';
                     $fields[$i] = trim(str_replace('DISTINCT', '', $fields[$i]));
                 }
                 if (strrpos($fields[$i], '.') === false) {
                     $fields[$i] = $prepend . $this->name($alias) . '.' . $this->name($fields[$i]) . ' AS ' . $this->name($alias . '__' . $fields[$i]);
                 } else {
                     $build = explode('.', $fields[$i]);
                     $fields[$i] = $prepend . $this->name($build[0]) . '.' . $this->name($build[1]) . ' AS ' . $this->name($build[0] . '__' . $build[1]);
                 }
             }
         }
     }
     return $fields;
 }

 /**
  * testDistinctFields method
  *
  * @return void
  */
 public function testDistinctFields()
 {
     $result = $this->db->fields($this->model, null, array('DISTINCT Car.country_code'));
     $expected = array('DISTINCT [Car].[country_code] AS [Car__country_code]');
     $this->assertEquals($expected, $result);
     $result = $this->db->fields($this->model, null, 'DISTINCT Car.country_code');
     $expected = array('DISTINCT [Car].[country_code] AS [Car__country_code]');
     $this->assertEquals($expected, $result);
 }

 /**
  * Generates the fields list of an SQL query.
  *
  * @param Model $model
  * @param string $alias Alias tablename
  * @param mixed $fields
  * @return array
  */
 function fields(&$model, $alias = null, $fields = array(), $quote = true)
 {
     if (empty($alias)) {
         $alias = $model->alias;
     }
     $fields = parent::fields($model, $alias, $fields, false);
     $count = count($fields);
     if ($count >= 1 && strpos($fields[0], 'COUNT(*)') === false) {
         $result = array();
         for ($i = 0; $i < $count; $i++) {
             $prepend = '';
             if (strpos($fields[$i], 'DISTINCT') !== false) {
                 $prepend = 'DISTINCT ';
                 $fields[$i] = trim(str_replace('DISTINCT', '', $fields[$i]));
             }
             $fieldAlias = count($this->__fieldMappings);
             if (!preg_match('/\\s+AS\\s+/i', $fields[$i])) {
                 if (substr($fields[$i], -1) == '*') {
                     if (strpos($fields[$i], '.') !== false && $fields[$i] != $alias . '.*') {
                         $build = explode('.', $fields[$i]);
                         $AssociatedModel = $model->{$build[0]};
                     } else {
                         $AssociatedModel = $model;
                     }
                     $_fields = $this->fields($AssociatedModel, $AssociatedModel->alias, array_keys($AssociatedModel->schema()));
                     $result = array_merge($result, $_fields);
                     continue;
                 }
                 if (strpos($fields[$i], '.') === false) {
                     $this->__fieldMappings[$alias . '__' . $fieldAlias] = $alias . '.' . $fields[$i];
                     $fieldName = $this->name($alias . '.' . $fields[$i]);
                     $fieldAlias = $this->name($alias . '__' . $fieldAlias);
                 } else {
                     $build = explode('.', $fields[$i]);
                     $this->__fieldMappings[$build[0] . '__' . $fieldAlias] = $fields[$i];
                     $fieldName = $this->name($build[0] . '.' . $build[1]);
                     $fieldAlias = $this->name(preg_replace("/^\\[(.+)\\]\$/", "\$1", $build[0]) . '__' . $fieldAlias);
                 }
                 if ($model->getColumnType($fields[$i]) == 'datetime') {
                     $fieldName = "CONVERT(VARCHAR(20), {$fieldName}, 20)";
                 }
                 $fields[$i] = "{$fieldName} AS {$fieldAlias}";
             }
             $result[] = $prepend . $fields[$i];
         }
         return $result;
     } else {
         return $fields;
     }
 }

 /**
  * Generates the fields list of an SQL query.
  *
  * @param Model $model
  * @param string $alias Alias table name
  * @param mixed $fields
  * @param boolean $quote
  * @return array
  */
 public function fields(Model $model, $alias = null, $fields = array(), $quote = true)
 {
     if (empty($alias)) {
         $alias = $model->alias;
     }
     $fields = parent::fields($model, $alias, $fields, false);
     if (!$quote) {
         return $fields;
     }
     $count = count($fields);
     if ($count >= 1 && !preg_match('/^\\s*COUNT\\(\\*/', $fields[0])) {
         $result = array();
         for ($i = 0; $i < $count; $i++) {
             if (!preg_match('/^.+\\(.*\\)/', $fields[$i]) && !preg_match('/\\s+AS\\s+/', $fields[$i])) {
                 if (substr($fields[$i], -1) == '*') {
                     if (strpos($fields[$i], '.') !== false && $fields[$i] != $alias . '.*') {
                         $build = explode('.', $fields[$i]);
                         $AssociatedModel = $model->{$build[0]};
                     } else {
                         $AssociatedModel = $model;
                     }
                     $_fields = $this->fields($AssociatedModel, $AssociatedModel->alias, array_keys($AssociatedModel->schema()));
                     $result = array_merge($result, $_fields);
                     continue;
                 }
                 $prepend = '';
                 if (strpos($fields[$i], 'DISTINCT') !== false) {
                     $prepend = 'DISTINCT ';
                     $fields[$i] = trim(str_replace('DISTINCT', '', $fields[$i]));
                 }
                 if (strrpos($fields[$i], '.') === false) {
                     $fields[$i] = $prepend . $this->name($alias) . '.' . $this->name($fields[$i]) . ' AS ' . $this->name($alias . '__' . $fields[$i]);
                 } else {
                     $build = explode('.', $fields[$i]);
                     $fields[$i] = $prepend . $this->name($build[0]) . '.' . $this->name($build[1]) . ' AS ' . $this->name($build[0] . '__' . $build[1]);
                 }
             } else {
                 $fields[$i] = preg_replace_callback('/\\(([\\s\\.\\w]+)\\)/', array(&$this, '_quoteFunctionField'), $fields[$i]);
             }
             $result[] = $fields[$i];
         }
         return $result;
     }
     return $fields;
 }

 /**
 * Test it is possible to do a SELECT COUNT(DISTINCT Model.field) query in postgres and it gets correctly quoted
 */
 function testQuoteDistinctInFunction()
 {
     $this->loadFixtures('Article');
     $Article = new Article();
     $result = $this->db->fields($Article, null, array('COUNT(DISTINCT Article.id)'));
     $expected = array('COUNT(DISTINCT "Article"."id")');
     $this->assertEqual($result, $expected);
 }

 /**
  * Generates the fields list of an SQL query.
  *
  * @param Model $model
  * @param string $alias Alias tablename
  * @param mixed $fields
  * @return array
  */
 function fields(&$model, $alias = null, $fields = array(), $quote = true)
 {
     if (empty($alias)) {
         $alias = $model->name;
     }
     $fields = parent::fields($model, $alias, $fields, false);
     $count = count($fields);
     if ($count >= 1 && $fields[0] != '*' && strpos($fields[0], 'COUNT(*)') === false) {
         for ($i = 0; $i < $count; $i++) {
             $dot = strrpos($fields[$i], '.');
             $fieldAlias = count($this->__fieldMappings);
             if ($dot === false && !preg_match('/\\s+AS\\s+/i', $fields[$i])) {
                 $this->__fieldMappings[$alias . '__' . $fieldAlias] = $alias . '.' . $fields[$i];
                 $fields[$i] = $this->name($alias) . '.' . $this->name($fields[$i]) . ' AS ' . $this->name($alias . '__' . $fieldAlias);
             } elseif (!preg_match('/\\s+AS\\s+/i', $fields[$i])) {
                 $build = explode('.', $fields[$i]);
                 $this->__fieldMappings[$build[0] . '__' . $fieldAlias] = $build[0] . '.' . $build[1];
                 $fields[$i] = $this->name($build[0]) . '.' . $this->name($build[1]) . ' AS ' . $this->name($build[0] . '__' . $fieldAlias);
             }
         }
     }
     return $fields;
 }

 /**
  * Generates the fields list of an SQL query.
  *
  * @param Model $Model The model to get fields for.
  * @param string $alias Alias table name
  * @param mixed $fields The provided list of fields.
  * @param bool $quote If false, returns fields array unquoted
  * @return array
  */
 public function fields(Model $model, $alias = null, $fields = array(), $quote = true)
 {
     $_fields = array();
     if (empty($alias)) {
         $alias = $model->alias;
     }
     if (is_array($fields)) {
         $fields = array_map('trim', $fields);
         foreach ($fields as $field) {
             if ($field != '*') {
                 if (substr($field, -2) == '.*') {
                     $AssociatedModel = $model;
                     $AssociatedAlias = substr($field, 0, -2);
                     if ($AssociatedAlias != $alias) {
                         $AssociatedModel = $model->{$AssociatedAlias};
                     }
                     foreach (parent::fields($AssociatedModel, null, array(), $quote) as $field) {
                         $_fields[] = $field;
                     }
                     $field = null;
                 }
                 if (!empty($field)) {
                     $_fields[] = $field;
                 }
             }
         }
     }
     $fields = parent::fields($model, $alias, $_fields, $quote);
     return $fields;
 }

 /**
  * Test it is possible to do a SELECT COUNT(DISTINCT Model.field)
  * query in postgres and it gets correctly quoted
  *
  * @return void
  */
 public function testQuoteDistinctInFunction()
 {
     $this->loadFixtures('Boat');
     $Boat = new Boat();
     $result = $this->Dbo->fields($Boat, null, array('COUNT(DISTINCT Boat.id)'));
     $expected = array('COUNT(DISTINCT "Boat"."id")');
     $this->assertEquals($expected, $result);
     $result = $this->Dbo->fields($Boat, null, array('COUNT(DISTINCT id)'));
     $expected = array('COUNT(DISTINCT "id")');
     $this->assertEquals($expected, $result);
     $result = $this->Dbo->fields($Boat, null, array('COUNT(DISTINCT FUNC(id))'));
     $expected = array('COUNT(DISTINCT FUNC("id"))');
     $this->assertEquals($expected, $result);
 }