Created
November 10, 2011 16:53
-
-
Save GromNaN/1355381 to your computer and use it in GitHub Desktop.
Doctrine_Connection_Mssql with optimized modifyLimitQuery()
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
/* | |
* $Id: Mssql.php 5804 2009-06-02 19:52:42Z jwage $ | |
* | |
* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS | |
* "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT | |
* LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR | |
* A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT | |
* OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, | |
* SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT | |
* LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, | |
* DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY | |
* THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT | |
* (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE | |
* OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. | |
* | |
* This software consists of voluntary contributions made by many individuals | |
* and is licensed under the LGPL. For more information, see | |
* <http://www.phpdoctrine.org>. | |
*/ | |
/** | |
* Doctrine_Connection_Mssql | |
* | |
* @package Doctrine | |
* @subpackage Connection | |
* @license http://www.opensource.org/licenses/lgpl-license.php LGPL | |
* @author Konsta Vesterinen <kvesteri@cc.hut.fi> | |
* @author Lukas Smith <smith@pooteeweet.org> (PEAR MDB2 library) | |
* @version $Revision: 5804 $ | |
* @link www.phpdoctrine.org | |
* @since 1.0 | |
*/ | |
class Doctrine_Connection_Mssql extends Doctrine_Connection | |
{ | |
/** | |
* @var string $driverName the name of this connection driver | |
*/ | |
protected $driverName = 'Mssql'; | |
/** | |
* the constructor | |
* | |
* @param Doctrine_Manager $manager | |
* @param PDO $pdo database handle | |
*/ | |
public function __construct(Doctrine_Manager $manager, $adapter) | |
{ | |
// initialize all driver options | |
$this->supported = array( | |
'sequences' => 'emulated', | |
'indexes' => true, | |
'affected_rows' => true, | |
'transactions' => true, | |
'summary_functions' => true, | |
'order_by_text' => true, | |
'current_id' => 'emulated', | |
'limit_queries' => 'emulated', | |
'LOBs' => true, | |
'replace' => 'emulated', | |
'sub_selects' => true, | |
'auto_increment' => true, | |
'primary_key' => true, | |
'result_introspection' => true, | |
'prepared_statements' => 'emulated', | |
); | |
parent::__construct($manager, $adapter); | |
} | |
/** | |
* quoteIdentifier | |
* Quote a string so it can be safely used as a table / column name | |
* | |
* Quoting style depends on which database driver is being used. | |
* | |
* @param string $identifier identifier name to be quoted | |
* @param bool $checkOption check the 'quote_identifier' option | |
* | |
* @return string quoted identifier string | |
*/ | |
public function quoteIdentifier($identifier, $checkOption = false) | |
{ | |
if ($checkOption && ! $this->getAttribute(Doctrine::ATTR_QUOTE_IDENTIFIER)) { | |
return $identifier; | |
} | |
if (strpos($identifier, '.') !== false) { | |
$parts = explode('.', $identifier); | |
$quotedParts = array(); | |
foreach ($parts as $p) { | |
$quotedParts[] = $this->quoteIdentifier($p); | |
} | |
return implode('.', $quotedParts); | |
} | |
return '[' . str_replace(']', ']]', $identifier) . ']'; | |
} | |
/** | |
* Adds an adapter-specific LIMIT clause to the SELECT statement. | |
* Inspired by Doctrine2 DBAL | |
* | |
* Known issues : | |
* - Remove the "DISTINCT" keyword | |
* - Not compatible with subqueries used like WHERE ... IN (SELECT ... LIMIT x OFFSET y) | |
* | |
* @param string $query | |
* @param mixed $limit | |
* @param mixed $offset | |
* @link https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Platforms/MsSqlPlatform.php#L607 | |
* @link http://www.toosweettobesour.com/2010/09/16/doctrine-1-2-mssql-alternative-limitpaging/ | |
* @return string | |
*/ | |
public function modifyLimitQuery($query, $limit = false, $offset = false, $isManip = false) | |
{ | |
$limit = intval($limit); | |
$offset = intval($offset); | |
if ($limit > 0) { | |
if ($offset < 0) { | |
throw new Doctrine_Connection_Exception("LIMIT argument offset=$offset is not valid"); | |
} | |
if ($offset == 0) { | |
$query = preg_replace('/^SELECT(\s+DISTINCT)?\s/i', "SELECT TOP $limit ", $query); | |
} else { | |
$over = stristr($query, 'ORDER BY'); | |
if (!$over) { | |
$over = 'ORDER BY (SELECT 0)'; | |
} else { | |
// Remove ORDER BY clause from $query | |
$query = stristr($query, 'ORDER BY', true); | |
} | |
// Remove the first SELECT from query | |
$query = preg_replace('/^SELECT(\s+DISTINCT)?\s/i', '', $query); | |
$start = $offset + 1; | |
$end = $offset + $limit; | |
$query = "SELECT * FROM (SELECT ROW_NUMBER() OVER ($over) AS [DOCTRINE_ROWNUM], $query) AS [doctrine_tbl] WHERE [DOCTRINE_ROWNUM] BETWEEN $start AND $end"; | |
} | |
} | |
return $query; | |
} | |
/** | |
* return version information about the server | |
* | |
* @param bool $native determines if the raw version string should be returned | |
* @return mixed array/string with version information or MDB2 error object | |
*/ | |
public function getServerVersion($native = false) | |
{ | |
if ($this->serverInfo) { | |
$serverInfo = $this->serverInfo; | |
} else { | |
$query = 'SELECT @@VERSION'; | |
$serverInfo = $this->fetchOne($query); | |
} | |
// cache server_info | |
$this->serverInfo = $serverInfo; | |
if ( ! $native) { | |
if (preg_match('/([0-9]+)\.([0-9]+)\.([0-9]+)/', $serverInfo, $tmp)) { | |
$serverInfo = array( | |
'major' => $tmp[1], | |
'minor' => $tmp[2], | |
'patch' => $tmp[3], | |
'extra' => null, | |
'native' => $serverInfo, | |
); | |
} else { | |
$serverInfo = array( | |
'major' => null, | |
'minor' => null, | |
'patch' => null, | |
'extra' => null, | |
'native' => $serverInfo, | |
); | |
} | |
} | |
return $serverInfo; | |
} | |
/** | |
* Checks if there's a sequence that exists. | |
* | |
* @param string $seq_name The sequence name to verify. | |
* @return boolean The value if the table exists or not | |
*/ | |
public function checkSequence($seqName) | |
{ | |
$query = 'SELECT * FROM ' . $seqName; | |
try { | |
$this->exec($query); | |
} catch(Doctrine_Connection_Exception $e) { | |
if ($e->getPortableCode() == Doctrine::ERR_NOSUCHTABLE) { | |
return false; | |
} | |
throw $e; | |
} | |
return true; | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment