PHP DboSource::renderStatement方法代碼示例

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