Renders a final SQL statement by putting together the component parts in the correct order
/**
* 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;
}
/**
* testRenderStatement method
*
* @return void
*/
public function testRenderStatement()
{
$result = $this->Dbo->renderStatement('select', array('fields' => 'id', 'table' => 'table', 'conditions' => 'WHERE 1=1', 'alias' => '', 'joins' => '', 'order' => '', 'limit' => '', 'group' => ''));
$this->assertRegExp('/^\\s*SELECT\\s+id\\s+FROM\\s+table\\s+WHERE\\s+1=1\\s*$/', $result);
$result = $this->Dbo->renderStatement('update', array('fields' => 'value=2', 'table' => 'table', 'conditions' => 'WHERE 1=1', 'alias' => ''));
$this->assertRegExp('/^\\s*UPDATE\\s+table\\s+SET\\s+value=2\\s+WHERE\\s+1=1\\s*$/', $result);
$result = $this->Dbo->renderStatement('update', array('fields' => 'value=2', 'table' => 'table', 'conditions' => 'WHERE 1=1', 'alias' => 'alias', 'joins' => ''));
$this->assertRegExp('/^\\s*UPDATE\\s+table\\s+AS\\s+alias\\s+SET\\s+value=2\\s+WHERE\\s+1=1\\s*$/', $result);
$result = $this->Dbo->renderStatement('delete', array('fields' => 'value=2', 'table' => 'table', 'conditions' => 'WHERE 1=1', 'alias' => ''));
$this->assertRegExp('/^\\s*DELETE\\s+FROM\\s+table\\s+WHERE\\s+1=1\\s*$/', $result);
$result = $this->Dbo->renderStatement('delete', array('fields' => 'value=2', 'table' => 'table', 'conditions' => 'WHERE 1=1', 'alias' => 'alias', 'joins' => ''));
$this->assertRegExp('/^\\s*DELETE\\s+alias\\s+FROM\\s+table\\s+AS\\s+alias\\s+WHERE\\s+1=1\\s*$/', $result);
}
/**
* Overrides DboSource::renderStatement to handle schema generation with SQLite-style indexes
*
* @param string $type The type of statement being rendered.
* @param array $data The data to convert to SQL.
* @return string
*/
public function renderStatement($type, $data)
{
switch (strtolower($type)) {
case 'schema':
extract($data);
if (is_array($columns)) {
$columns = "\t" . implode(",\n\t", array_filter($columns));
}
if (is_array($indexes)) {
$indexes = "\t" . implode("\n\t", array_filter($indexes));
}
return "CREATE TABLE {$table} (\n{$columns});\n{$indexes}";
default:
return parent::renderStatement($type, $data);
}
}
/**
* Overrides DboSource::renderStatement to handle schema generation with Postgres-style indexes
*
* @param string $type
* @param array $data
* @return string
*/
function renderStatement($type, $data)
{
switch (strtolower($type)) {
case 'schema':
extract($data);
foreach ($indexes as $i => $index) {
if (preg_match('/PRIMARY KEY/', $index)) {
unset($indexes[$i]);
$columns[] = $index;
break;
}
}
$join = array('columns' => ",\n\t", 'indexes' => "\n");
foreach (array('columns', 'indexes') as $var) {
if (is_array(${$var})) {
${$var} = implode($join[$var], array_filter(${$var}));
}
}
return "CREATE TABLE {$table} (\n\t{$columns}\n);\n{$indexes}";
break;
default:
return parent::renderStatement($type, $data);
break;
}
}
/**
* Builds final SQL statement
*
* @param string $type Query type
* @param array $data Query data
* @return string
*/
function renderStatement($type, $data)
{
switch (strtolower($type)) {
case 'select':
extract($data);
$fields = trim($fields);
if (strpos($limit, 'TOP') !== false && strpos($fields, 'DISTINCT ') === 0) {
$limit = 'DISTINCT ' . trim($limit);
$fields = substr($fields, 9);
}
if (preg_match('/offset\\s+([0-9]+)/i', $limit, $offset)) {
$limit = preg_replace('/\\s*offset.*$/i', '', $limit);
preg_match('/top\\s+([0-9]+)/i', $limit, $limitVal);
$offset = intval($offset[1]) + intval($limitVal[1]);
$rOrder = $this->__switchSort($order);
list($order2, $rOrder) = array($this->__mapFields($order), $this->__mapFields($rOrder));
//return "SELECT * FROM (SELECT {$limit} * FROM (SELECT TOP {$offset} {$fields} FROM {$table} {$alias} {$joins} {$conditions} {$group} {$order}) AS Set1 {$rOrder}) AS Set2 {$order2}";
$limitint = (int) $offset + 1 - (int) trim(str_replace('TOP', '', $limit));
return "SELECT * FROM (SELECT row_number() OVER ({$order}) as resultNum, {$fields} FROM {$table} {$alias} {$joins} {$conditions} {$group}) as numberResults WHERE resultNum BETWEEN {$limitint} and {$offset} ";
} else {
return "SELECT {$limit} {$fields} FROM {$table} {$alias} {$joins} {$conditions} {$group} {$order}";
}
break;
case "schema":
extract($data);
foreach ($indexes as $i => $index) {
if (preg_match('/PRIMARY KEY/', $index)) {
unset($indexes[$i]);
break;
}
}
foreach (array('columns', 'indexes') as $var) {
if (is_array(${$var})) {
${$var} = "\t" . implode(",\n\t", array_filter(${$var}));
}
}
return "CREATE TABLE {$table} (\n{$columns});\n{$indexes}";
break;
default:
return parent::renderStatement($type, $data);
break;
}
}
/**
* Overrides DboSource::renderStatement to handle schema generation with SQLite-style indexes
*
* @param string $type
* @param array $data
* @return string
* @access public
*/
function renderStatement($type, $data)
{
switch (strtolower($type)) {
case 'schema':
extract($data);
foreach (array('columns', 'indexes') as $var) {
if (is_array(${$var})) {
${$var} = "\t" . join(",\n\t", array_filter(${$var}));
}
}
return "CREATE TABLE {$table} (\n{$columns});\n{$indexes}";
break;
default:
return parent::renderStatement($type, $data);
break;
}
}
/**
* Builds final SQL statement
*
* @param string $type Query type
* @param array $data Query data
* @return string
*/
public function renderStatement($type, $data)
{
switch (strtolower($type)) {
case 'select':
extract($data);
$fields = trim($fields);
if ($limit) {
if (!$order) {
$order = 'ORDER BY NULL';
}
preg_match('/\\s*LIMIT\\s+(\\d+)(,\\s*(\\d+)){0,1}$/', $limit, $limitOffset);
if (isset($limitOffset[3])) {
$limit = intval($limitOffset[3]);
$offset = intval($limitOffset[1]);
} else {
$limit = intval($limitOffset[1]);
$offset = 0;
}
$start = $offset + 1;
$end = $offset + $limit;
$rowCounter = self::ROW_COUNTER;
$sql = "SELECT * FROM (\n\t\t\t\t\t\t\tSELECT {$fields}, ROW_NUMBER() OVER ({$order}) AS {$rowCounter}\n\t\t\t\t\t\t\tFROM {$table} {$alias} {$joins} {$conditions} {$group}\n\t\t\t\t\t\t) cake_paging\n\t\t\t\t\t\tWHERE cake_paging.{$rowCounter} BETWEEN {$start} AND {$end}";
return trim($sql);
}
return trim("SELECT {$fields} FROM {$table} {$alias} {$joins} {$conditions} {$group} {$order}");
case "schema":
extract($data);
foreach ($indexes as $i => $index) {
if (preg_match('/PRIMARY KEY/', $index)) {
unset($indexes[$i]);
break;
}
}
foreach (array('columns', 'indexes') as $var) {
if (is_array(${$var})) {
${$var} = "\t" . implode(",\n\t", array_filter(${$var}));
}
}
return trim("CREATE TABLE {$table} (\n{$columns});\n{$indexes}");
default:
return parent::renderStatement($type, $data);
}
}
/**
* Builds final SQL statement
*
* @param string $type Query type
* @param array $data Query data
* @return string
*/
function renderStatement($type, $data)
{
extract($data);
if (strtolower($type) == 'select') {
if (preg_match('/offset\\s+([0-9]+)/i', $limit, $offset)) {
$limit = preg_replace('/\\s*offset.*$/i', '', $limit);
preg_match('/top\\s+([0-9]+)/i', $limit, $limitVal);
$offset = intval($offset[1]) + intval($limitVal[1]);
$rOrder = $this->__switchSort($order);
list($order2, $rOrder) = array($this->__mapFields($order), $this->__mapFields($rOrder));
return "SELECT * FROM (SELECT {$limit} * FROM (SELECT TOP {$offset} {$fields} FROM {$table} {$alias} {$joins} {$conditions} {$order}) AS Set1 {$rOrder}) AS Set2 {$order2}";
} else {
return "SELECT {$limit} {$fields} FROM {$table} {$alias} {$joins} {$conditions} {$order}";
}
} else {
return parent::renderStatement($type, $data);
}
}
/**
* Builds final SQL statement
*
* @param string $type Query type
* @param array $data Query data
* @return string
*/
public function renderStatement($type, $data)
{
switch (strtolower($type)) {
case 'select':
extract($data);
$fields = trim($fields);
if (strpos($limit, 'TOP') !== false && strpos($fields, 'DISTINCT ') === 0) {
$limit = 'DISTINCT ' . trim($limit);
$fields = substr($fields, 9);
}
// hack order as SQLServer requires an order if there is a limit.
if ($limit && !$order) {
$order = 'ORDER BY (SELECT NULL)';
}
// For older versions use the subquery version of pagination.
if (version_compare($this->getVersion(), '11', '<') && preg_match('/FETCH\\sFIRST\\s+([0-9]+)/i', $limit, $offset)) {
preg_match('/OFFSET\\s*(\\d+)\\s*.*?(\\d+)\\s*ROWS/', $limit, $limitOffset);
$limit = 'TOP ' . intval($limitOffset[2]);
$page = intval($limitOffset[1] / $limitOffset[2]);
$offset = intval($limitOffset[2] * $page);
$rowCounter = self::ROW_COUNTER;
return "\n\t\t\t\t\t\tSELECT {$limit} * FROM (\n\t\t\t\t\t\t\tSELECT {$fields}, ROW_NUMBER() OVER ({$order}) AS {$rowCounter}\n\t\t\t\t\t\t\tFROM {$table} {$alias} {$joins} {$conditions} {$group}\n\t\t\t\t\t\t) AS _cake_paging_\n\t\t\t\t\t\tWHERE _cake_paging_.{$rowCounter} > {$offset}\n\t\t\t\t\t\tORDER BY _cake_paging_.{$rowCounter}\n\t\t\t\t\t";
} elseif (strpos($limit, 'FETCH') !== false) {
return "SELECT {$fields} FROM {$table} {$alias} {$joins} {$conditions} {$group} {$order} {$limit}";
}
return "SELECT {$limit} {$fields} FROM {$table} {$alias} {$joins} {$conditions} {$group} {$order}";
case "schema":
extract($data);
foreach ($indexes as $i => $index) {
if (preg_match('/PRIMARY KEY/', $index)) {
unset($indexes[$i]);
break;
}
}
foreach (array('columns', 'indexes') as $var) {
if (is_array(${$var})) {
${$var} = "\t" . implode(",\n\t", array_filter(${$var}));
}
}
return "CREATE TABLE {$table} (\n{$columns});\n{$indexes}";
default:
return parent::renderStatement($type, $data);
}
}
