summaryrefslogtreecommitdiff
path: root/framework/Data/Common/Mssql
diff options
context:
space:
mode:
Diffstat (limited to 'framework/Data/Common/Mssql')
-rw-r--r--framework/Data/Common/Mssql/TMssqlCommandBuilder.php346
-rw-r--r--framework/Data/Common/Mssql/TMssqlTableColumn.php126
-rw-r--r--framework/Data/Common/Mssql/TMssqlTableInfo.php126
3 files changed, 299 insertions, 299 deletions
diff --git a/framework/Data/Common/Mssql/TMssqlCommandBuilder.php b/framework/Data/Common/Mssql/TMssqlCommandBuilder.php
index 8a1e4dd6..3485adaa 100644
--- a/framework/Data/Common/Mssql/TMssqlCommandBuilder.php
+++ b/framework/Data/Common/Mssql/TMssqlCommandBuilder.php
@@ -1,173 +1,173 @@
-<?php
-/**
- * TMsssqlCommandBuilder class file.
- *
- * @author Wei Zhuo <weizhuo[at]gmail[dot]com>
- * @link http://www.pradosoft.com/
- * @copyright Copyright &copy; 2005-2012 PradoSoft
- * @license http://www.pradosoft.com/license/
- * @version $Id: TDbCommandBuilder.php 1863 2007-04-12 12:43:49Z wei $
- * @package System.Data.Common
- */
-
-Prado::using('System.Data.Common.TDbCommandBuilder');
-
-/**
- * TMssqlCommandBuilder provides specifics methods to create limit/offset query commands
- * for MSSQL servers.
- *
- * @author Wei Zhuo <weizho[at]gmail[dot]com>
- * @version $Id: TDbCommandBuilder.php 1863 2007-04-12 12:43:49Z wei $
- * @package System.Data.Common
- * @since 3.1
- */
-class TMssqlCommandBuilder extends TDbCommandBuilder
-{
- /**
- * Overrides parent implementation. Uses "SELECT @@Identity".
- * @return integer last insert id, null if none is found.
- */
- public function getLastInsertID()
- {
- foreach($this->getTableInfo()->getColumns() as $column)
- {
- if($column->hasSequence())
- {
- $command = $this->getDbConnection()->createCommand('SELECT @@Identity');
- return intval($command->queryScalar());
- }
- }
- }
-
- /**
- * Overrides parent implementation. Alters the sql to apply $limit and $offset.
- * The idea for limit with offset is done by modifying the sql on the fly
- * with numerous assumptions on the structure of the sql string.
- * The modification is done with reference to the notes from
- * http://troels.arvin.dk/db/rdbms/#select-limit-offset
- *
- * <code>
- * SELECT * FROM (
- * SELECT TOP n * FROM (
- * SELECT TOP z columns -- (z=n+skip)
- * FROM tablename
- * ORDER BY key ASC
- * ) AS FOO ORDER BY key DESC -- ('FOO' may be anything)
- * ) AS BAR ORDER BY key ASC -- ('BAR' may be anything)
- * </code>
- *
- * <b>Regular expressions are used to alter the SQL query. The resulting SQL query
- * may be malformed for complex queries.</b> The following restrictions apply
- *
- * <ul>
- * <li>
- * In particular, <b>commas</b> should <b>NOT</b>
- * be used as part of the ordering expression or identifier. Commas must only be
- * used for separating the ordering clauses.
- * </li>
- * <li>
- * In the ORDER BY clause, the column name should NOT be be qualified
- * with a table name or view name. Alias the column names or use column index.
- * </li>
- * <li>
- * No clauses should follow the ORDER BY clause, e.g. no COMPUTE or FOR clauses.
- * </li>
- * </ul>
- *
- * @param string SQL query string.
- * @param integer maximum number of rows, -1 to ignore limit.
- * @param integer row offset, -1 to ignore offset.
- * @return string SQL with limit and offset.
- */
- public function applyLimitOffset($sql, $limit=-1, $offset=-1)
- {
- $limit = $limit!==null ? intval($limit) : -1;
- $offset = $offset!==null ? intval($offset) : -1;
- if ($limit > 0 && $offset <= 0) //just limit
- $sql = preg_replace('/^([\s(])*SELECT( DISTINCT)?(?!\s*TOP\s*\()/i',"\\1SELECT\\2 TOP $limit", $sql);
- else if($limit > 0 && $offset > 0)
- $sql = $this->rewriteLimitOffsetSql($sql, $limit,$offset);
- return $sql;
- }
-
- /**
- * Rewrite sql to apply $limit > and $offset > 0 for MSSQL database.
- * See http://troels.arvin.dk/db/rdbms/#select-limit-offset
- * @param string sql query
- * @param integer $limit > 0
- * @param integer $offset > 0
- * @return sql modified sql query applied with limit and offset.
- */
- protected function rewriteLimitOffsetSql($sql, $limit, $offset)
- {
- $fetch = $limit+$offset;
- $sql = preg_replace('/^([\s(])*SELECT( DISTINCT)?(?!\s*TOP\s*\()/i',"\\1SELECT\\2 TOP $fetch", $sql);
- $ordering = $this->findOrdering($sql);
-
- $orginalOrdering = $this->joinOrdering($ordering);
- $reverseOrdering = $this->joinOrdering($this->reverseDirection($ordering));
- $sql = "SELECT * FROM (SELECT TOP {$limit} * FROM ($sql) as [__inner top table__] {$reverseOrdering}) as [__outer top table__] {$orginalOrdering}";
- return $sql;
- }
-
- /**
- * Base on simplified syntax http://msdn2.microsoft.com/en-us/library/aa259187(SQL.80).aspx
- *
- * @param string $sql
- * @return array ordering expression as key and ordering direction as value
- */
- protected function findOrdering($sql)
- {
- if(!preg_match('/ORDER BY/i', $sql))
- return array();
- $matches=array();
- $ordering=array();
- preg_match_all('/(ORDER BY)[\s"\[](.*)(ASC|DESC)?(?:[\s"\[]|$|COMPUTE|FOR)/i', $sql, $matches);
- if(count($matches)>1 && count($matches[2]) > 0)
- {
- $parts = explode(',', $matches[2][0]);
- foreach($parts as $part)
- {
- $subs=array();
- if(preg_match_all('/(.*)[\s"\]](ASC|DESC)$/i', trim($part), $subs))
- {
- if(count($subs) > 1 && count($subs[2]) > 0)
- {
- $ordering[$subs[1][0]] = $subs[2][0];
- }
- //else what?
- }
- else
- $ordering[trim($part)] = 'ASC';
- }
- }
- return $ordering;
- }
-
- /**
- * @param array ordering obtained from findOrdering()
- * @return string concat the orderings
- */
- protected function joinOrdering($orders)
- {
- if(count($orders)>0)
- {
- $str=array();
- foreach($orders as $column => $direction)
- $str[] = $column.' '.$direction;
- return 'ORDER BY '.implode(', ', $str);
- }
- }
-
- /**
- * @param array original ordering
- * @return array ordering with reversed direction.
- */
- protected function reverseDirection($orders)
- {
- foreach($orders as $column => $direction)
- $orders[$column] = strtolower(trim($direction))==='desc' ? 'ASC' : 'DESC';
- return $orders;
- }
-}
-
+<?php
+/**
+ * TMsssqlCommandBuilder class file.
+ *
+ * @author Wei Zhuo <weizhuo[at]gmail[dot]com>
+ * @link http://www.pradosoft.com/
+ * @copyright Copyright &copy; 2005-2012 PradoSoft
+ * @license http://www.pradosoft.com/license/
+ * @version $Id: TDbCommandBuilder.php 1863 2007-04-12 12:43:49Z wei $
+ * @package System.Data.Common
+ */
+
+Prado::using('System.Data.Common.TDbCommandBuilder');
+
+/**
+ * TMssqlCommandBuilder provides specifics methods to create limit/offset query commands
+ * for MSSQL servers.
+ *
+ * @author Wei Zhuo <weizho[at]gmail[dot]com>
+ * @version $Id: TDbCommandBuilder.php 1863 2007-04-12 12:43:49Z wei $
+ * @package System.Data.Common
+ * @since 3.1
+ */
+class TMssqlCommandBuilder extends TDbCommandBuilder
+{
+ /**
+ * Overrides parent implementation. Uses "SELECT @@Identity".
+ * @return integer last insert id, null if none is found.
+ */
+ public function getLastInsertID()
+ {
+ foreach($this->getTableInfo()->getColumns() as $column)
+ {
+ if($column->hasSequence())
+ {
+ $command = $this->getDbConnection()->createCommand('SELECT @@Identity');
+ return intval($command->queryScalar());
+ }
+ }
+ }
+
+ /**
+ * Overrides parent implementation. Alters the sql to apply $limit and $offset.
+ * The idea for limit with offset is done by modifying the sql on the fly
+ * with numerous assumptions on the structure of the sql string.
+ * The modification is done with reference to the notes from
+ * http://troels.arvin.dk/db/rdbms/#select-limit-offset
+ *
+ * <code>
+ * SELECT * FROM (
+ * SELECT TOP n * FROM (
+ * SELECT TOP z columns -- (z=n+skip)
+ * FROM tablename
+ * ORDER BY key ASC
+ * ) AS FOO ORDER BY key DESC -- ('FOO' may be anything)
+ * ) AS BAR ORDER BY key ASC -- ('BAR' may be anything)
+ * </code>
+ *
+ * <b>Regular expressions are used to alter the SQL query. The resulting SQL query
+ * may be malformed for complex queries.</b> The following restrictions apply
+ *
+ * <ul>
+ * <li>
+ * In particular, <b>commas</b> should <b>NOT</b>
+ * be used as part of the ordering expression or identifier. Commas must only be
+ * used for separating the ordering clauses.
+ * </li>
+ * <li>
+ * In the ORDER BY clause, the column name should NOT be be qualified
+ * with a table name or view name. Alias the column names or use column index.
+ * </li>
+ * <li>
+ * No clauses should follow the ORDER BY clause, e.g. no COMPUTE or FOR clauses.
+ * </li>
+ * </ul>
+ *
+ * @param string SQL query string.
+ * @param integer maximum number of rows, -1 to ignore limit.
+ * @param integer row offset, -1 to ignore offset.
+ * @return string SQL with limit and offset.
+ */
+ public function applyLimitOffset($sql, $limit=-1, $offset=-1)
+ {
+ $limit = $limit!==null ? intval($limit) : -1;
+ $offset = $offset!==null ? intval($offset) : -1;
+ if ($limit > 0 && $offset <= 0) //just limit
+ $sql = preg_replace('/^([\s(])*SELECT( DISTINCT)?(?!\s*TOP\s*\()/i',"\\1SELECT\\2 TOP $limit", $sql);
+ else if($limit > 0 && $offset > 0)
+ $sql = $this->rewriteLimitOffsetSql($sql, $limit,$offset);
+ return $sql;
+ }
+
+ /**
+ * Rewrite sql to apply $limit > and $offset > 0 for MSSQL database.
+ * See http://troels.arvin.dk/db/rdbms/#select-limit-offset
+ * @param string sql query
+ * @param integer $limit > 0
+ * @param integer $offset > 0
+ * @return sql modified sql query applied with limit and offset.
+ */
+ protected function rewriteLimitOffsetSql($sql, $limit, $offset)
+ {
+ $fetch = $limit+$offset;
+ $sql = preg_replace('/^([\s(])*SELECT( DISTINCT)?(?!\s*TOP\s*\()/i',"\\1SELECT\\2 TOP $fetch", $sql);
+ $ordering = $this->findOrdering($sql);
+
+ $orginalOrdering = $this->joinOrdering($ordering);
+ $reverseOrdering = $this->joinOrdering($this->reverseDirection($ordering));
+ $sql = "SELECT * FROM (SELECT TOP {$limit} * FROM ($sql) as [__inner top table__] {$reverseOrdering}) as [__outer top table__] {$orginalOrdering}";
+ return $sql;
+ }
+
+ /**
+ * Base on simplified syntax http://msdn2.microsoft.com/en-us/library/aa259187(SQL.80).aspx
+ *
+ * @param string $sql
+ * @return array ordering expression as key and ordering direction as value
+ */
+ protected function findOrdering($sql)
+ {
+ if(!preg_match('/ORDER BY/i', $sql))
+ return array();
+ $matches=array();
+ $ordering=array();
+ preg_match_all('/(ORDER BY)[\s"\[](.*)(ASC|DESC)?(?:[\s"\[]|$|COMPUTE|FOR)/i', $sql, $matches);
+ if(count($matches)>1 && count($matches[2]) > 0)
+ {
+ $parts = explode(',', $matches[2][0]);
+ foreach($parts as $part)
+ {
+ $subs=array();
+ if(preg_match_all('/(.*)[\s"\]](ASC|DESC)$/i', trim($part), $subs))
+ {
+ if(count($subs) > 1 && count($subs[2]) > 0)
+ {
+ $ordering[$subs[1][0]] = $subs[2][0];
+ }
+ //else what?
+ }
+ else
+ $ordering[trim($part)] = 'ASC';
+ }
+ }
+ return $ordering;
+ }
+
+ /**
+ * @param array ordering obtained from findOrdering()
+ * @return string concat the orderings
+ */
+ protected function joinOrdering($orders)
+ {
+ if(count($orders)>0)
+ {
+ $str=array();
+ foreach($orders as $column => $direction)
+ $str[] = $column.' '.$direction;
+ return 'ORDER BY '.implode(', ', $str);
+ }
+ }
+
+ /**
+ * @param array original ordering
+ * @return array ordering with reversed direction.
+ */
+ protected function reverseDirection($orders)
+ {
+ foreach($orders as $column => $direction)
+ $orders[$column] = strtolower(trim($direction))==='desc' ? 'ASC' : 'DESC';
+ return $orders;
+ }
+}
+
diff --git a/framework/Data/Common/Mssql/TMssqlTableColumn.php b/framework/Data/Common/Mssql/TMssqlTableColumn.php
index 5621bb6b..811d0f6e 100644
--- a/framework/Data/Common/Mssql/TMssqlTableColumn.php
+++ b/framework/Data/Common/Mssql/TMssqlTableColumn.php
@@ -1,64 +1,64 @@
-<?php
-/**
- * TMssqlTableColumn class file.
- *
- * @author Wei Zhuo <weizhuo[at]gmail[dot]com>
- * @link http://www.pradosoft.com/
+<?php
+/**
+ * TMssqlTableColumn class file.
+ *
+ * @author Wei Zhuo <weizhuo[at]gmail[dot]com>
+ * @link http://www.pradosoft.com/
* @copyright Copyright &copy; 2005-2012 PradoSoft
- * @license http://www.pradosoft.com/license/
- * @version $Id: TMssqlTableColumn.php 1863 2007-04-12 12:43:49Z wei $
- * @package System.Data.Common.Mssql
- */
-
-/**
- * Load common TDbTableCommon class.
- */
-Prado::using('System.Data.Common.TDbTableColumn');
-
-/**
- * Describes the column metadata of the schema for a Mssql database table.
- *
- * @author Wei Zhuo <weizho[at]gmail[dot]com>
- * @version $Id: TMssqlTableColumn.php 1863 2007-04-12 12:43:49Z wei $
- * @package System.Data.Common.Mssql
- * @since 3.1
- */
-class TMssqlTableColumn extends TDbTableColumn
-{
- private static $types = array();
-
- /**
- * Overrides parent implementation, returns PHP type from the db type.
- * @return boolean derived PHP primitive type from the column db type.
- */
- public function getPHPType()
- {
-
- return 'string';
- }
-
- /**
- * @return boolean true if the column has identity (auto-increment)
- */
- public function getAutoIncrement()
- {
- return $this->getInfo('AutoIncrement',false);
- }
-
- /**
- * @return boolean true if auto increments.
- */
- public function hasSequence()
- {
- return $this->getAutoIncrement();
- }
-
- /**
- * @return boolean true if db type is 'timestamp'.
- */
- public function getIsExcluded()
- {
- return strtolower($this->getDbType())==='timestamp';
- }
-}
-
+ * @license http://www.pradosoft.com/license/
+ * @version $Id: TMssqlTableColumn.php 1863 2007-04-12 12:43:49Z wei $
+ * @package System.Data.Common.Mssql
+ */
+
+/**
+ * Load common TDbTableCommon class.
+ */
+Prado::using('System.Data.Common.TDbTableColumn');
+
+/**
+ * Describes the column metadata of the schema for a Mssql database table.
+ *
+ * @author Wei Zhuo <weizho[at]gmail[dot]com>
+ * @version $Id: TMssqlTableColumn.php 1863 2007-04-12 12:43:49Z wei $
+ * @package System.Data.Common.Mssql
+ * @since 3.1
+ */
+class TMssqlTableColumn extends TDbTableColumn
+{
+ private static $types = array();
+
+ /**
+ * Overrides parent implementation, returns PHP type from the db type.
+ * @return boolean derived PHP primitive type from the column db type.
+ */
+ public function getPHPType()
+ {
+
+ return 'string';
+ }
+
+ /**
+ * @return boolean true if the column has identity (auto-increment)
+ */
+ public function getAutoIncrement()
+ {
+ return $this->getInfo('AutoIncrement',false);
+ }
+
+ /**
+ * @return boolean true if auto increments.
+ */
+ public function hasSequence()
+ {
+ return $this->getAutoIncrement();
+ }
+
+ /**
+ * @return boolean true if db type is 'timestamp'.
+ */
+ public function getIsExcluded()
+ {
+ return strtolower($this->getDbType())==='timestamp';
+ }
+}
+
diff --git a/framework/Data/Common/Mssql/TMssqlTableInfo.php b/framework/Data/Common/Mssql/TMssqlTableInfo.php
index c65e3eaa..3b48e42d 100644
--- a/framework/Data/Common/Mssql/TMssqlTableInfo.php
+++ b/framework/Data/Common/Mssql/TMssqlTableInfo.php
@@ -1,64 +1,64 @@
-<?php
-/**
- * TMssqlTableInfo class file.
- *
- * @author Wei Zhuo <weizhuo[at]gmail[dot]com>
- * @link http://www.pradosoft.com/
+<?php
+/**
+ * TMssqlTableInfo class file.
+ *
+ * @author Wei Zhuo <weizhuo[at]gmail[dot]com>
+ * @link http://www.pradosoft.com/
* @copyright Copyright &copy; 2005-2012 PradoSoft
- * @license http://www.pradosoft.com/license/
- * @version $Id: TMssqlTableInfo.php 1861 2007-04-12 08:05:03Z wei $
- * @package System.Data.Common.Mssql
- */
-
-/**
- * Loads the base TDbTableInfo class and TMssqlTableColumn class.
- */
-Prado::using('System.Data.Common.TDbTableInfo');
-Prado::using('System.Data.Common.Mssql.TMssqlTableColumn');
-
-/**
- * TMssqlTableInfo class provides additional table information for Mssql database.
- *
- * @author Wei Zhuo <weizho[at]gmail[dot]com>
- * @version $Id: TMssqlTableInfo.php 1861 2007-04-12 08:05:03Z wei $
- * @package System.Data.Common.Mssql
- * @since 3.1
- */
-class TMssqlTableInfo extends TDbTableInfo
-{
- /**
- * @return string name of the schema this column belongs to.
- */
- public function getSchemaName()
- {
- return $this->getInfo('SchemaName');
- }
-
- /**
- * @return string catalog name (database name)
- */
- public function getCatalogName()
- {
- return $this->getInfo('CatalogName');
- }
-
- /**
- * @return string full name of the table, database dependent.
- */
- public function getTableFullName()
- {
- //MSSQL alway returns the catalog, schem and table names.
- return '['.$this->getCatalogName().'].['.$this->getSchemaName().'].['.$this->getTableName().']';
- }
-
- /**
- * @param TDbConnection database connection.
- * @return TDbCommandBuilder new command builder
- */
- public function createCommandBuilder($connection)
- {
- Prado::using('System.Data.Common.Mssql.TMssqlCommandBuilder');
- return new TMssqlCommandBuilder($connection,$this);
- }
-}
-
+ * @license http://www.pradosoft.com/license/
+ * @version $Id: TMssqlTableInfo.php 1861 2007-04-12 08:05:03Z wei $
+ * @package System.Data.Common.Mssql
+ */
+
+/**
+ * Loads the base TDbTableInfo class and TMssqlTableColumn class.
+ */
+Prado::using('System.Data.Common.TDbTableInfo');
+Prado::using('System.Data.Common.Mssql.TMssqlTableColumn');
+
+/**
+ * TMssqlTableInfo class provides additional table information for Mssql database.
+ *
+ * @author Wei Zhuo <weizho[at]gmail[dot]com>
+ * @version $Id: TMssqlTableInfo.php 1861 2007-04-12 08:05:03Z wei $
+ * @package System.Data.Common.Mssql
+ * @since 3.1
+ */
+class TMssqlTableInfo extends TDbTableInfo
+{
+ /**
+ * @return string name of the schema this column belongs to.
+ */
+ public function getSchemaName()
+ {
+ return $this->getInfo('SchemaName');
+ }
+
+ /**
+ * @return string catalog name (database name)
+ */
+ public function getCatalogName()
+ {
+ return $this->getInfo('CatalogName');
+ }
+
+ /**
+ * @return string full name of the table, database dependent.
+ */
+ public function getTableFullName()
+ {
+ //MSSQL alway returns the catalog, schem and table names.
+ return '['.$this->getCatalogName().'].['.$this->getSchemaName().'].['.$this->getTableName().']';
+ }
+
+ /**
+ * @param TDbConnection database connection.
+ * @return TDbCommandBuilder new command builder
+ */
+ public function createCommandBuilder($connection)
+ {
+ Prado::using('System.Data.Common.Mssql.TMssqlCommandBuilder');
+ return new TMssqlCommandBuilder($connection,$this);
+ }
+}
+