PHP Query::sql方法代码示例

 /**
  * Prepares a sql statement to be executed
  *
  * @param string|\Cake\Database\Query $query The query to prepare.
  * @return \Cake\Database\StatementInterface
  */
 public function prepare($query)
 {
     $this->connect();
     $options = [PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL];
     $isObject = $query instanceof Query;
     if ($isObject && $query->bufferResults() === false) {
         $options = [];
     }
     $statement = $this->_connection->prepare($isObject ? $query->sql() : $query, $options);
     return new SqlserverStatement($statement, $this);
 }

 /**
  * Prepares a sql statement to be executed
  *
  * @param string|\Cake\Database\Query $query The query to turn into a prepared statement.
  * @return \Cake\Database\StatementInterface
  */
 public function prepare($query)
 {
     $this->connect();
     $isObject = $query instanceof Query;
     $statement = $this->_connection->prepare($isObject ? $query->sql() : $query);
     return new PDOStatement($statement, $this);
 }

 /**
  * Prepares a sql statement to be executed
  *
  * @param string|\Cake\Database\Query $query The query to prepare.
  * @return \Cake\Database\StatementInterface
  */
 public function prepare($query)
 {
     $this->connect();
     $isObject = $query instanceof Query;
     $statement = $this->_connection->prepare($isObject ? $query->sql() : $query);
     $result = new SqliteStatement(new PDOStatement($statement, $this), $this);
     if ($isObject && $query->bufferResults() === false) {
         $result->bufferResults(false);
     }
     return $result;
 }

 /**
  * Prepares a sql statement to be executed
  *
  * @param string|\Cake\Database\Query $query The query to prepare.
  * @return \Cake\Database\StatementInterface
  */
 public function prepare($query)
 {
     $this->connect();
     $options = [PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY];
     $isObject = $query instanceof Query;
     if ($isObject && $query->bufferResults() === false) {
         $options = [];
     }
     $statement = $this->_connection->prepare($isObject ? $query->sql() : $query, $options);
     $result = new CustomSqlserverStatement(new SqlserverStatement($statement, $this), $this);
     if ($isObject && $query->bufferResults() === false) {
         $result->bufferResults(false);
     }
     return $result;
 }

 /**
  * {@inheritDoc}
  */
 public function sql(ValueBinder $binder = null)
 {
     $this->triggerBeforeFind();
     $this->_transformQuery();
     $sql = parent::sql($binder);
     return $sql;
 }

 /**
  * Tests that strings passed to isNull and isNotNull will be treated as identifiers
  * when using autoQuoting
  *
  * @return void
  */
 public function testIsNullAutoQuoting()
 {
     $this->connection->driver()->autoQuoting(true);
     $query = new Query($this->connection);
     $query->select('*')->from('things')->where(function ($exp) {
         return $exp->isNull('field');
     });
     $this->assertQuotedQuery('WHERE \\(<field>\\) IS NULL', $query->sql());
     $query = new Query($this->connection);
     $query->select('*')->from('things')->where(function ($exp) {
         return $exp->isNotNull('field');
     });
     $this->assertQuotedQuery('WHERE \\(<field>\\) IS NOT NULL', $query->sql());
 }

 /**
  * Test that insert queries have results available to them.
  *
  * @return void
  */
 public function testInsertUsesOutput()
 {
     $driver = $this->getMock('Cake\\Database\\Driver\\Sqlserver', ['_connect', 'connection'], [[]]);
     $connection = $this->getMock('\\Cake\\Database\\Connection', ['connect', 'driver'], [['log' => false]]);
     $connection->expects($this->any())->method('driver')->will($this->returnValue($driver));
     $query = new \Cake\Database\Query($connection);
     $query->insert(['title'])->into('articles')->values(['title' => 'A new article']);
     $expected = 'INSERT INTO articles (title) OUTPUT INSERTED.* VALUES (:c0)';
     $this->assertEquals($expected, $query->sql());
 }

 /**
  * Test select with limit on lte SQLServer2008
  *
  * @return void
  */
 public function testSelectLimitOldServer()
 {
     $driver = $this->getMock('Cake\\Database\\Driver\\Sqlserver', ['_connect', 'connection', '_version'], [['dsn' => 'foo']]);
     $driver->expects($this->any())->method('_version')->will($this->returnValue(8));
     $connection = $this->getMock('\\Cake\\Database\\Connection', ['connect', 'driver'], [['log' => false]]);
     $connection->expects($this->any())->method('driver')->will($this->returnValue($driver));
     $query = new \Cake\Database\Query($connection);
     $query->select(['id', 'title'])->from('articles')->limit(10);
     $expected = 'SELECT TOP 10 id, title FROM articles';
     $this->assertEquals($expected, $query->sql());
     $query = new \Cake\Database\Query($connection);
     $query->select(['id', 'title'])->from('articles')->offset(10);
     $expected = 'SELECT * FROM (SELECT id, title, (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) AS [_cake_page_rownum_] ' . 'FROM articles) AS _cake_paging_ ' . 'WHERE _cake_paging_._cake_page_rownum_ > :c0';
     $this->assertEquals($expected, $query->sql());
     $query = new \Cake\Database\Query($connection);
     $query->select(['id', 'title'])->from('articles')->order(['id'])->offset(10);
     $expected = 'SELECT * FROM (SELECT id, title, (ROW_NUMBER() OVER (ORDER BY id)) AS [_cake_page_rownum_] ' . 'FROM articles) AS _cake_paging_ ' . 'WHERE _cake_paging_._cake_page_rownum_ > :c0';
     $this->assertEquals($expected, $query->sql());
     $query = new \Cake\Database\Query($connection);
     $query->select(['id', 'title'])->from('articles')->order(['id'])->where(['title' => 'Something'])->limit(10)->offset(50);
     $expected = 'SELECT * FROM (SELECT id, title, (ROW_NUMBER() OVER (ORDER BY id)) AS [_cake_page_rownum_] ' . 'FROM articles WHERE title = :c0) AS _cake_paging_ ' . 'WHERE (_cake_paging_._cake_page_rownum_ > :c1 AND _cake_paging_._cake_page_rownum_ <= :c2)';
     $this->assertEquals($expected, $query->sql());
 }

 /**
  * {@inheritDoc}
  */
 public function sql(ValueBinder $binder = null)
 {
     $this->_transformQuery();
     return parent::sql($binder);
 }

 /**
  * Create a NodeStatement from query and returns it
  * 
  * @param Query $query
  * @return NodeStatement
  */
 public function prepare($query)
 {
     $this->connect();
     $isObject = $query instanceof Query;
     return new NodeStatement($isObject ? $query->sql() : $query, $this);
 }

 /**
  * Test that INSERT INTO ... SELECT works.
  *
  * @return void
  */
 public function testInsertFromSelect()
 {
     $select = (new Query($this->connection))->select(['name', "'some text'", 99])->from('authors')->where(['id' => 1]);
     $query = new Query($this->connection);
     $query->insert(['title', 'body', 'author_id'], ['title' => 'string', 'body' => 'string', 'author_id' => 'integer'])->into('articles')->values($select);
     $result = $query->sql();
     $this->assertQuotedQuery('INSERT INTO <articles> \\(<title>, <body>, <author_id>\\) SELECT', $result, true);
     $this->assertQuotedQuery('SELECT <name>, \'some text\', 99 FROM <authors>', $result, true);
     $result = $query->execute();
     $this->assertCount(1, $result);
     $result = (new Query($this->connection))->select('*')->from('articles')->where(['author_id' => 99])->execute();
     $this->assertCount(1, $result);
     $expected = ['id' => 4, 'title' => 'mariano', 'body' => 'some text', 'author_id' => 99, 'published' => 'N'];
     $this->assertEquals($expected, $result->fetch('assoc'));
 }

 /**
  * Prepares a sql statement to be executed
  *
  * @param string|\Cake\Database\Query $query The query to convert into a statement.
  * @return \Cake\Database\StatementInterface
  */
 public function prepare($query)
 {
     $this->connect();
     $isObject = $query instanceof \Cake\ORM\Query || $query instanceof \Cake\Database\Query;
     $queryStringRaw = $isObject ? $query->sql() : $query;
     Log::write('debug', $queryStringRaw);
     // debug($queryStringRaw);
     $queryString = $this->_fromDualIfy($queryStringRaw);
     list($queryString, $paramMap) = self::convertPositionalToNamedPlaceholders($queryString);
     $innerStatement = $this->_connection->prepare($queryString);
     $statement = $this->_wrapStatement($innerStatement);
     $statement->queryString = $queryStringRaw;
     $statement->paramMap = $paramMap;
     $disableBuffer = false;
     $normalizedQuery = substr(strtolower(trim($queryString, " \t\n\r\v(")), 0, 6);
     if ($normalizedQuery !== 'select') {
         $disableBuffer = true;
     }
     if ($isObject && $query->bufferResults() === false || $disableBuffer) {
         $statement->bufferResults(false);
     }
     return $statement;
 }