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); } }