[limb-svn] r6858 - 3.x/trunk/limb/dbal/src/drivers/pgsql
svn at limb-project.com
svn at limb-project.com
Wed Mar 26 11:32:54 MSK 2008
Author: svk
Date: 2008-03-26 11:32:53 +0300 (Wed, 26 Mar 2008)
New Revision: 6858
URL: http://fisheye.limb-project.com/changelog/limb/?cs=6858
Modified:
3.x/trunk/limb/dbal/src/drivers/pgsql/lmbPgsqlConnection.class.php
3.x/trunk/limb/dbal/src/drivers/pgsql/lmbPgsqlQueryStatement.class.php
3.x/trunk/limb/dbal/src/drivers/pgsql/lmbPgsqlRecordSet.class.php
3.x/trunk/limb/dbal/src/drivers/pgsql/lmbPgsqlStatement.class.php
Log:
-- postgresql driver rewritten to use query preparation by client library (experimental)
Modified: 3.x/trunk/limb/dbal/src/drivers/pgsql/lmbPgsqlConnection.class.php
===================================================================
--- 3.x/trunk/limb/dbal/src/drivers/pgsql/lmbPgsqlConnection.class.php 2008-03-25 15:31:51 UTC (rev 6857)
+++ 3.x/trunk/limb/dbal/src/drivers/pgsql/lmbPgsqlConnection.class.php 2008-03-26 08:32:53 UTC (rev 6858)
@@ -133,7 +133,8 @@
function executeStatement($stmt)
{
- return $this->execute($stmt->getSQL());
+ $stmt_name = $stmt->getStatementName();
+ return pg_execute($this->getConnectionId(), $stmt_name, $stmt->getPrepParams());
}
function beginTransaction()
Modified: 3.x/trunk/limb/dbal/src/drivers/pgsql/lmbPgsqlQueryStatement.class.php
===================================================================
--- 3.x/trunk/limb/dbal/src/drivers/pgsql/lmbPgsqlQueryStatement.class.php 2008-03-25 15:31:51 UTC (rev 6857)
+++ 3.x/trunk/limb/dbal/src/drivers/pgsql/lmbPgsqlQueryStatement.class.php 2008-03-26 08:32:53 UTC (rev 6858)
@@ -24,7 +24,7 @@
function getOneRecord()
{
$record = new lmbPgsqlRecord();
- $queryId = $this->connection->execute($this->getSQL());
+ $queryId = $this->connection->executeStatement($this);
$values = pg_fetch_assoc($queryId);
$record->import($values);
pg_free_result($queryId);
@@ -34,7 +34,7 @@
function getOneValue()
{
- $queryId = $this->connection->execute($this->getSQL());
+ $queryId = $this->connection->executeStatement($this);
$row = pg_fetch_row($queryId);
pg_free_result($queryId);
if(is_array($row))
@@ -44,7 +44,7 @@
function getOneColumnAsArray()
{
$column = array();
- $queryId = $this->connection->execute($this->getSQL());
+ $queryId = $this->connection->executeStatement($this);
while(is_array($row = pg_fetch_row($queryId)))
$column[] = $row[0];
pg_free_result($queryId);
@@ -53,8 +53,36 @@
function getRecordSet()
{
- return new lmbPgsqlRecordSet($this->connection, $this->getSQL());
+ return new lmbPgsqlRecordSet($this->connection, $this);
}
+
+ function count()
+ {
+ if(!(preg_match("/^\s*SELECT\s+DISTINCT/is", $this->sql) || preg_match('/\s+GROUP\s+BY\s+/is',$this->sql)) && preg_match("/^\s*SELECT\s+.+\s+FROM\s+/Uis", $this->sql))
+ {
+ $rewritesql = preg_replace('/^\s*SELECT\s.*\s+FROM\s/Uis','SELECT COUNT(*) FROM ', $this->sql);
+ $rewritesql = preg_replace('/(\sORDER\s+BY\s.*)/is','', $rewritesql);
+
+ $queryId = $this->execute($rewritesql);
+ $row = pg_fetch_row($queryId);
+ pg_free_result($queryId);
+ if (is_array($row))
+ {
+ return $row[0];
+ }
+ else
+ {
+ return 0;
+ }
+ }
+
+ // could not re-write the query, try a different method.
+ $queryId = $this->execute($this->sql);
+ $count = pg_num_rows($queryId);
+ pg_free_result($queryId);
+ return $count;
+ }
+
}
Modified: 3.x/trunk/limb/dbal/src/drivers/pgsql/lmbPgsqlRecordSet.class.php
===================================================================
--- 3.x/trunk/limb/dbal/src/drivers/pgsql/lmbPgsqlRecordSet.class.php 2008-03-25 15:31:51 UTC (rev 6857)
+++ 3.x/trunk/limb/dbal/src/drivers/pgsql/lmbPgsqlRecordSet.class.php 2008-03-26 08:32:53 UTC (rev 6858)
@@ -18,17 +18,17 @@
*/
class lmbPgsqlRecordSet extends lmbDbBaseRecordSet
{
- protected $query;
protected $connection;
+ protected $stmt;
protected $current;
protected $valid;
protected $key;
- function __construct($connection, $queryString)
+ function __construct($connection, $statement)
{
$this->connection = $connection;
- $this->query = $queryString;
+ $this->stmt = $statement;
}
function freeQuery()
@@ -37,6 +37,7 @@
{
pg_free_result($this->queryId);
$this->queryId = null;
+ $this->stmt->free();
}
}
@@ -45,30 +46,22 @@
if(isset($this->queryId) && is_resource($this->queryId) && pg_num_rows($this->queryId))
{
if(pg_result_seek($this->queryId, 0) === false)
- $this->connection->_raiseError();
+ $this->connection->_raiseError("");
}
elseif(!$this->queryId)
{
- $query = $this->query;
+ $this->stmt->free();
if(is_array($this->sort_params))
{
- if(preg_match('~(?<=FROM).+\s+ORDER\s+BY\s+~i', $query))
- $query .= ',';
- else
- $query .= ' ORDER BY ';
- foreach($this->sort_params as $field => $order)
- $query .= $this->connection->quoteIdentifier($field) . " $order,";
-
- $query = rtrim($query, ',');
+ $this->stmt->addOrder($this->sort_params);
}
if($this->limit)
{
- $query .= ' LIMIT ' . $this->limit;
- $query .= ' OFFSET ' . $this->offset;
+ $this->stmt->addLimit($this->offset, $this->limit);
}
- $this->queryId = $this->connection->execute($query);
+ $this->queryId = $this->stmt->execute();
}
$this->key = 0;
$this->next();
@@ -97,23 +90,19 @@
{
return $this->key;
}
-
+
function at($pos)
{
- $query = $this->query;
+ $stmt = clone $this->stmt;
+ $stmt->free();
+ if($this->sort_params)
+ $stmt->addOrder($this->sort_params);
+ $stmt->addLimit($pos, 1);
- if(is_array($this->sort_params))
- {
- $query .= ' ORDER BY ';
- foreach($this->sort_params as $field => $order)
- $query .= $this->connection->quoteIdentifier($field) . " $order,";
- $query = rtrim($query, ',');
- }
-
- $queryId = $this->connection->execute($query . " LIMIT 1 OFFSET $pos");
-
+ $queryId = $stmt->execute();
$res = pg_fetch_assoc($queryId);
pg_free_result($queryId);
+
if($res)
{
$record = new lmbPgsqlRecord();
@@ -121,6 +110,7 @@
return $record;
}
}
+
function countPaginated()
{
@@ -131,23 +121,7 @@
function count()
{
- if(!(preg_match("/^\s*SELECT\s+DISTINCT/is", $this->query) || preg_match('/\s+GROUP\s+BY\s+/is',$this->query)) && preg_match("/^\s*SELECT\s+.+\s+FROM\s+/Uis", $this->query))
- {
- $rewritesql = preg_replace('/^\s*SELECT\s.*\s+FROM\s/Uis','SELECT COUNT(*) FROM ', $this->query);
- $rewritesql = preg_replace('/(\sORDER\s+BY\s.*)/is', '', $rewritesql);
-
- $queryId = $this->connection->execute($rewritesql);
- $row = pg_fetch_row($queryId);
- pg_free_result($queryId);
- if(is_array($row))
- return $row[0];
- }
-
- // could not re-write the query, try a different method.
- $queryId = $this->connection->execute($this->query);
- $count = pg_num_rows($queryId);
- pg_free_result($queryId);
- return $count;
+ return $this->stmt->count();
}
}
Modified: 3.x/trunk/limb/dbal/src/drivers/pgsql/lmbPgsqlStatement.class.php
===================================================================
--- 3.x/trunk/limb/dbal/src/drivers/pgsql/lmbPgsqlStatement.class.php 2008-03-25 15:31:51 UTC (rev 6857)
+++ 3.x/trunk/limb/dbal/src/drivers/pgsql/lmbPgsqlStatement.class.php 2008-03-26 08:32:53 UTC (rev 6858)
@@ -17,12 +17,16 @@
class lmbPgsqlStatement implements lmbDbStatement
{
protected $statement;
+ protected $statement_name;
protected $connection;
+ protected $sql;
+ protected $queryId;
protected $parameters = array();
+ protected $prepParams = array();
function __construct($connection, $sql)
{
- $this->statement = $sql;
+ $this->sql = $sql;
$this->connection = $connection;
}
@@ -33,23 +37,23 @@
function setNull($name)
{
- $this->parameters[$name] = 'null';
+ $this->parameters[$name] = null;
}
function setSmallInt($name, $value)
{
- $this->parameters[$name] = is_null($value) ? 'null' : intval($value);
+ $this->parameters[$name] = is_null($value) ? null : intval($value);
}
function setInteger($name, $value)
{
- $this->parameters[$name] = is_null($value) ? 'null' : intval($value);
+ $this->parameters[$name] = is_null($value) ? null : intval($value);
}
function setFloat($name, $value)
{
$this->parameters[$name] = is_null($value) ?
- 'null' :
+ null :
floatval($value);
}
@@ -65,7 +69,7 @@
}
else
{
- $this->parameters[$name] = 'null';
+ $this->parameters[$name] = null;
}
}
@@ -81,53 +85,51 @@
}
else
{
- $this->parameters[$name] = 'null';
+ $this->parameters[$name] = null;
}
}
function setBoolean($name, $value)
{
$this->parameters[$name] = is_null($value) ?
- 'null' :(($value) ? "'1'" : "'0'");
+ null :(($value) ? "1" : "0");
}
function setChar($name, $value)
{
$this->parameters[$name] = is_null($value) ?
- 'null' :
- "'" . pg_escape_string((string) $value) . "'";
+ null : $value;
}
function setVarChar($name, $value)
{
$this->parameters[$name] = is_null($value) ?
- 'null' :
- "'" . pg_escape_string((string) $value) . "'";
+ null : $value;
}
-
+
function setClob($name, $value)
{
$this->parameters[$name] = is_null($value) ?
- 'null' :
- "'" . pg_escape_string((string) $value) . "'";
+ null : $value;
}
protected function _setDate($name, $value, $format)
{
if(is_int($value))
{
- $this->parameters[$name] = "'" . date($format, $value) . "'";
+ $this->parameters[$name] = date($format, $value);
}
else if(is_string($value))
{
- $this->parameters[$name] = "'" . pg_escape_string((string) $value) . "'";
+ $this->parameters[$name] = (string) $value;
}
else
{
- $this->parameters[$name] = 'null';
+ $this->parameters[$name] = null;
}
}
-
+
+
function setDate($name, $value)
{
$this->_setDate($name, $value, 'Y-m-d');
@@ -143,13 +145,14 @@
$this->_setDate($name, $value, 'Y-m-d H:i:s');
}
+
function setBlob($name, $value)
{
$this->parameters[$name] = is_null($value) ?
- 'null' :
- "'" . pg_escape_bytea((string) $value) . "'";
+ null :
+ (string) $value;
}
-
+
function set($name, $value)
{
if(is_string($value))
@@ -182,20 +185,149 @@
}
}
+ function getStatement()
+ {
+ $this->_prepareStatement();
+ return $this->statement;
+ }
+
+ protected function _prepareStatement()
+ {
+ $sql = $this->_handleBindVars($this->sql);
+ if (empty($this->statement_name) || !is_resource($this->statement))
+ {
+ $this->statement_name = uniqid(time());
+ $this->statement = pg_prepare($this->connection->getConnectionId(), $this->statement_name, $sql);
+ }
+ if(!$this->statement)
+ {
+ $this->connection->_raiseError("");
+ return;
+ }
+ }
+
+
+ protected function _handleBindVars($sql)
+ {
+ $this->prepParams = array();
+ $newsql = '';
+ $index = 1;
+ if (preg_match("#^select :(\w+):;?$#i", $sql, $m))
+ $cast_types = true;
+ else
+ $cast_types = false;
+ while(preg_match('/^(\'[^\']*?\')|(--[^(\n)]*?\n)|(:(?-U)\w+:(?U))|.+/Us', $sql, $matches))
+ {
+ if(isset($matches[3]))
+ {
+ $param = str_replace(':', '', $matches[0]);
+
+ if(!array_key_exists($param, $this->parameters))
+ $this->parameters[$param] = null;
+
+ $this->prepParams[] = $this->parameters[$param];
+ $newsql .= '$'.$index;
+ if ($cast_types)
+ {
+ $this->statement_name = null;
+ $newsql .= '::';
+ if (is_null($this->parameters[$param]))
+ {
+ $newsql .= 'int';
+ }
+ elseif (is_integer($this->parameters[$param]))
+ {
+ $newsql .= 'int';
+ }
+ elseif (is_float($this->parameters[$param]))
+ {
+ $newsql .= 'float';
+ }
+ else
+ {
+ $newsql .= 'varchar';
+ }
+ }
+ $index++;
+ }
+ else
+ $newsql .= $matches[0];
+
+ $sql = substr($sql, strlen($matches[0]));
+ }
+ return $newsql;
+ }
+
function getSQL()
{
- $sql = $this->statement;
- foreach($this->parameters as $key => $value)
+ return $this->sql;
+ }
+
+ function getPrepParams()
+ {
+ return $this->prepParams;
+ }
+
+ function getStatementName()
+ {
+ $this->getStatement();
+ return $this->statement_name;
+ }
+
+ function execute($sql = "")
+ {
+ if (!empty($sql))
{
- $sql = str_replace(':' . $key . ':', $value, $sql);
+ $stored_sql = $this->sql;
+ $this->sql = $sql;
+ $this->free();
}
- return $sql;
+ $queryId = $this->connection->executeStatement($this);
+ if (isset($stored_sql))
+ {
+ $this->sql = $stored_sql;
+ $this->free();
+ }
+ return $queryId;
}
+
+ function addOrder($sort_params)
+ {
+ if(preg_match('~(?<=FROM).+\s+ORDER\s+BY\s+~i', $this->sql))
+ $this->sql .= ',';
+ else
+ $this->sql .= ' ORDER BY ';
- function execute()
+ foreach($sort_params as $field => $order)
+ $this->sql .= $this->connection->quoteIdentifier($field) . " $order,";
+
+ $this->sql = rtrim($this->sql, ',');
+ }
+
+ function addLimit($offset, $limit)
{
- return $this->connection->executeStatement($this);
+ $this->sql .= ' LIMIT ' . $limit . ' OFFSET ' . $offset;
}
+
+ function count()
+ {
+ if (!$this->queryId)
+ $this->queryId = $this->execute();
+
+ return pg_num_rows($this->queryId);
+ }
+
+ function free()
+ {
+ if ($this->queryId && is_resource($this->queryId))
+ pg_free_result($this->queryId);
+
+ $this->queryId = null;
+ $this->statement = null;
+ $this->statement_name = null;
+ }
+
+
}
More information about the limb-svn
mailing list