Generates the fields list of an SQL query.
/**
* 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);
}
