From fb494d1e299e256721cba77105a01b7480c48c08 Mon Sep 17 00:00:00 2001
From: "GODZilla0480@gmail.com" <>
Date: Sun, 28 Aug 2011 06:24:03 +0000
Subject: Add methods quoteTableName, quoteColumnName, quoteColumnAlias to
 TDbMetaData & TDbConnection and add TDbConnection:getDbMetaData [TODO:
 customize TOracleMetaData]

---
 framework/Data/Common/Mssql/TMssqlMetaData.php   | 80 ++++++++++++++++--------
 framework/Data/Common/Mysql/TMysqlMetaData.php   | 38 +++++++++--
 framework/Data/Common/Pgsql/TPgsqlMetaData.php   | 38 +++++++++--
 framework/Data/Common/Sqlite/TSqliteMetaData.php | 32 +++++++++-
 framework/Data/Common/TDbMetaData.php            | 48 ++++++++++++++
 5 files changed, 202 insertions(+), 34 deletions(-)

(limited to 'framework/Data/Common')

diff --git a/framework/Data/Common/Mssql/TMssqlMetaData.php b/framework/Data/Common/Mssql/TMssqlMetaData.php
index 334a297e..08d005ea 100644
--- a/framework/Data/Common/Mssql/TMssqlMetaData.php
+++ b/framework/Data/Common/Mssql/TMssqlMetaData.php
@@ -7,7 +7,7 @@
  * @copyright Copyright &copy; 2005-2011 PradoSoft
  * @license http://www.pradosoft.com/license/
  * @version $Id: TPgsqlMetaData.php 1866 2007-04-14 05:02:29Z wei $
- * @package System.Data.Common.Pgsql
+ * @package System.Data.Common.Mssql
  */
 
 /**
@@ -34,6 +34,36 @@ class TMssqlMetaData extends TDbMetaData
 		return 'TMssqlTableInfo';
 	}
 
+	/**
+	 * Quotes a table name for use in a query.
+	 * @param string $name table name
+	 * @return string the properly quoted table name
+	 */
+	public function quoteTableName($name)
+	{
+		return parent::quoteTableName($name, '[', ']');
+	}
+
+	/**
+	 * Quotes a column name for use in a query.
+	 * @param string $name column name
+	 * @return string the properly quoted column name
+	 */
+	public function quoteColumnName($name)
+	{
+		return parent::quoteColumnName($name, '[', ']');
+	}
+
+	/**
+	 * Quotes a column alias for use in a query.
+	 * @param string $name column alias
+	 * @return string the properly quoted column alias
+	 */
+	public function quoteColumnAlias($name)
+	{
+		return parent::quoteColumnAlias($name, '"', '"');
+	}
+
 	/**
 	 * Get the column definitions for given table.
 	 * @param string table name.
@@ -45,12 +75,12 @@ class TMssqlMetaData extends TDbMetaData
 		$this->getDbConnection()->setActive(true);
 		$sql = <<<EOD
 				SELECT t.*,
-                         c.*,
+												c.*,
 					columnproperty(object_id(c.table_schema + '.' + c.table_name), c.column_name,'IsIdentity') as IsIdentity
-                    FROM INFORMATION_SCHEMA.TABLES t,
-                         INFORMATION_SCHEMA.COLUMNS c
-                   WHERE t.table_name = c.table_name
-                     AND t.table_name = :table
+										FROM INFORMATION_SCHEMA.TABLES t,
+												INFORMATION_SCHEMA.COLUMNS c
+									WHERE t.table_name = c.table_name
+										AND t.table_name = :table
 EOD;
 		if($schemaName!==null)
 			$sql .= ' AND t.table_schema = :schema';
@@ -152,14 +182,14 @@ EOD;
 	{
 		$sql = <<<EOD
 		SELECT k.column_name field_name
-		    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
-		    LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
-		      ON k.table_name = c.table_name
-		     AND k.constraint_name = c.constraint_name
-		   WHERE k.constraint_catalog = DB_NAME()
+				FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
+				LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
+					ON k.table_name = c.table_name
+				AND k.constraint_name = c.constraint_name
+			WHERE k.constraint_catalog = DB_NAME()
 		AND
 			c.constraint_type ='PRIMARY KEY'
-		    AND k.table_name = :table
+				AND k.table_name = :table
 EOD;
 		$command = $this->getDbConnection()->createCommand($sql);
 		$command->bindValue(':table', $col['TABLE_NAME']);
@@ -181,27 +211,27 @@ EOD;
 		//From http://msdn2.microsoft.com/en-us/library/aa175805(SQL.80).aspx
 		$sql = <<<EOD
 		SELECT
-		     KCU1.CONSTRAINT_NAME AS 'FK_CONSTRAINT_NAME'
-		   , KCU1.TABLE_NAME AS 'FK_TABLE_NAME'
-		   , KCU1.COLUMN_NAME AS 'FK_COLUMN_NAME'
-		   , KCU1.ORDINAL_POSITION AS 'FK_ORDINAL_POSITION'
-		   , KCU2.CONSTRAINT_NAME AS 'UQ_CONSTRAINT_NAME'
-		   , KCU2.TABLE_NAME AS 'UQ_TABLE_NAME'
-		   , KCU2.COLUMN_NAME AS 'UQ_COLUMN_NAME'
-		   , KCU2.ORDINAL_POSITION AS 'UQ_ORDINAL_POSITION'
+				KCU1.CONSTRAINT_NAME AS 'FK_CONSTRAINT_NAME'
+			, KCU1.TABLE_NAME AS 'FK_TABLE_NAME'
+			, KCU1.COLUMN_NAME AS 'FK_COLUMN_NAME'
+			, KCU1.ORDINAL_POSITION AS 'FK_ORDINAL_POSITION'
+			, KCU2.CONSTRAINT_NAME AS 'UQ_CONSTRAINT_NAME'
+			, KCU2.TABLE_NAME AS 'UQ_TABLE_NAME'
+			, KCU2.COLUMN_NAME AS 'UQ_COLUMN_NAME'
+			, KCU2.ORDINAL_POSITION AS 'UQ_ORDINAL_POSITION'
 		FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
 		JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1
 		ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
-		   AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
-		   AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
+			AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
+			AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
 		JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2
 		ON KCU2.CONSTRAINT_CATALOG =
 		RC.UNIQUE_CONSTRAINT_CATALOG
-		   AND KCU2.CONSTRAINT_SCHEMA =
+			AND KCU2.CONSTRAINT_SCHEMA =
 		RC.UNIQUE_CONSTRAINT_SCHEMA
-		   AND KCU2.CONSTRAINT_NAME =
+			AND KCU2.CONSTRAINT_NAME =
 		RC.UNIQUE_CONSTRAINT_NAME
-		   AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION
+			AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION
 		WHERE KCU1.TABLE_NAME = :table
 EOD;
 		$command = $this->getDbConnection()->createCommand($sql);
diff --git a/framework/Data/Common/Mysql/TMysqlMetaData.php b/framework/Data/Common/Mysql/TMysqlMetaData.php
index d3de3ff6..5eba540d 100644
--- a/framework/Data/Common/Mysql/TMysqlMetaData.php
+++ b/framework/Data/Common/Mysql/TMysqlMetaData.php
@@ -24,7 +24,7 @@ Prado::using('System.Data.Common.Mysql.TMysqlTableInfo');
  *
  * @author Wei Zhuo <weizho[at]gmail[dot]com>
  * @version $Id$
- * @package System.Data.Commom.Sqlite
+ * @package System.Data.Common.Mysql
  * @since 3.1
  */
 class TMysqlMetaData extends TDbMetaData
@@ -39,6 +39,36 @@ class TMysqlMetaData extends TDbMetaData
 		return 'TMysqlTableInfo';
 	}
 
+	/**
+	 * Quotes a table name for use in a query.
+	 * @param string $name table name
+	 * @return string the properly quoted table name
+	 */
+	public function quoteTableName($name)
+	{
+		return parent::quoteTableName($name, '`', '`');
+	}
+
+	/**
+	 * Quotes a column name for use in a query.
+	 * @param string $name column name
+	 * @return string the properly quoted column name
+	 */
+	public function quoteColumnName($name)
+	{
+		return parent::quoteColumnName($name, '`', '`');
+	}
+
+	/**
+	 * Quotes a column alias for use in a query.
+	 * @param string $name column alias
+	 * @return string the properly quoted column alias
+	 */
+	public function quoteColumnAlias($name)
+	{
+		return parent::quoteColumnAlias($name, '`', '`');
+	}
+
 	/**
 	 * Get the column definitions for given table.
 	 * @param string table name.
@@ -246,8 +276,8 @@ class TMysqlMetaData extends TDbMetaData
 			if($row['Key_name']==='PRIMARY')
 				$primary[] = $row['Column_name'];
 		}
-        // MySQL version was increased to >=5.1.21 instead of 5.x
-        // due to a MySQL bug (http://bugs.mysql.com/bug.php?id=19588)
+				// MySQL version was increased to >=5.1.21 instead of 5.x
+				// due to a MySQL bug (http://bugs.mysql.com/bug.php?id=19588)
 		if($this->getServerVersion() >= 5.121)
 			$foreign = $this->getForeignConstraints($schemaName,$tableName);
 		else
@@ -272,7 +302,7 @@ class TMysqlMetaData extends TDbMetaData
 				REFERENCED_TABLE_NAME as fktable,
 				REFERENCED_COLUMN_NAME as fkcol
 			FROM
-				 `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE`
+				`INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE`
 			WHERE
 				REFERENCED_TABLE_NAME IS NOT NULL
 				AND TABLE_NAME LIKE :table
diff --git a/framework/Data/Common/Pgsql/TPgsqlMetaData.php b/framework/Data/Common/Pgsql/TPgsqlMetaData.php
index 5ac57239..e57f1b37 100644
--- a/framework/Data/Common/Pgsql/TPgsqlMetaData.php
+++ b/framework/Data/Common/Pgsql/TPgsqlMetaData.php
@@ -4,7 +4,7 @@
  *
  * @author Wei Zhuo <weizhuo[at]gmail[dot]com>
  * @link http://www.pradosoft.com/
- * @copyright Copyright &copy; 2005-2011 PradoSoft
+ * @copyright Copyright &copy; 2005-2011 PradoSoft
  * @license http://www.pradosoft.com/license/
  * @version $Id$
  * @package System.Data.Common.Pgsql
@@ -36,6 +36,36 @@ class TPgsqlMetaData extends TDbMetaData
 		return 'TPgsqlTableInfo';
 	}
 
+	/**
+	 * Quotes a table name for use in a query.
+	 * @param string $name table name
+	 * @return string the properly quoted table name
+	 */
+	public function quoteTableName($name)
+	{
+		return parent::quoteTableName($name, '"', '"');
+	}
+
+	/**
+	 * Quotes a column name for use in a query.
+	 * @param string $name column name
+	 * @return string the properly quoted column name
+	 */
+	public function quoteColumnName($name)
+	{
+		return parent::quoteColumnName($name, '"', '"');
+	}
+
+	/**
+	 * Quotes a column alias for use in a query.
+	 * @param string $name column alias
+	 * @return string the properly quoted column alias
+	 */
+	public function quoteColumnAlias($name)
+	{
+		return parent::quoteColumnAlias($name, '"', '"');
+	}
+
 	/**
 	 * @param string default schema.
 	 */
@@ -333,13 +363,13 @@ EOD;
 		$index = join(', ', explode(' ', $columnIndex));
 		$sql =
 <<<EOD
-    SELECT attnum, attname FROM pg_catalog.pg_attribute WHERE
+		SELECT attnum, attname FROM pg_catalog.pg_attribute WHERE
 		attrelid=(
 			SELECT oid FROM pg_catalog.pg_class WHERE relname=:table AND relnamespace=(
 				SELECT oid FROM pg_catalog.pg_namespace WHERE nspname=:schema
 			)
 		)
-        AND attnum IN ({$index})
+				AND attnum IN ({$index})
 EOD;
 		$command = $this->getDbConnection()->createCommand($sql);
 		$command->bindValue(':table', $tableName);
@@ -348,7 +378,7 @@ EOD;
 		$primary = array();
 		foreach($command->query() as $row)
 		{
-            $primary[] = $row['attname'];
+						$primary[] = $row['attname'];
 		}
 
 		return $primary;
diff --git a/framework/Data/Common/Sqlite/TSqliteMetaData.php b/framework/Data/Common/Sqlite/TSqliteMetaData.php
index 1ae872c2..d4fbd435 100644
--- a/framework/Data/Common/Sqlite/TSqliteMetaData.php
+++ b/framework/Data/Common/Sqlite/TSqliteMetaData.php
@@ -4,7 +4,7 @@
  *
  * @author Wei Zhuo <weizhuo[at]gmail[dot]com>
  * @link http://www.pradosoft.com/
- * @copyright Copyright &copy; 2005-2011 PradoSoft
+ * @copyright Copyright &copy; 2005-2011 PradoSoft
  * @license http://www.pradosoft.com/license/
  * @version $Id: TSqliteMetaData.php 1861 2007-04-12 08:05:03Z wei $
  * @package System.Data.Common.Sqlite
@@ -34,6 +34,36 @@ class TSqliteMetaData extends TDbMetaData
 		return 'TSqliteTableInfo';
 	}
 
+	/**
+	 * Quotes a table name for use in a query.
+	 * @param string $name table name
+	 * @return string the properly quoted table name
+	 */
+	public function quoteTableName($name)
+	{
+		return parent::quoteTableName($name, "'", "'");
+	}
+
+	/**
+	 * Quotes a column name for use in a query.
+	 * @param string $name column name
+	 * @return string the properly quoted column name
+	 */
+	public function quoteColumnName($name)
+	{
+		return parent::quoteColumnName($name, '"', '"');
+	}
+
+	/**
+	 * Quotes a column alias for use in a query.
+	 * @param string $name column alias
+	 * @return string the properly quoted column alias
+	 */
+	public function quoteColumnAlias($name)
+	{
+		return parent::quoteColumnAlias($name, '"', '"');
+	}
+
 	/**
 	 * Get the column definitions for given table.
 	 * @param string table name.
diff --git a/framework/Data/Common/TDbMetaData.php b/framework/Data/Common/TDbMetaData.php
index 13f2eeb9..bc29927f 100644
--- a/framework/Data/Common/TDbMetaData.php
+++ b/framework/Data/Common/TDbMetaData.php
@@ -26,6 +26,11 @@ abstract class TDbMetaData extends TComponent
 	private $_tableInfoCache=array();
 	private $_connection;
 
+	/**
+	 * @var array
+	 */
+	protected static $delimiterIdentifier = array('[', ']', '"', '`', "'");
+
 	/**
 	 * @param TDbConnection database connection.
 	 */
@@ -119,5 +124,48 @@ abstract class TDbMetaData extends TComponent
 	{
 		return 'TDbTableInfo';
 	}
+
+	/**
+	 * Quotes a table name for use in a query.
+	 * @param string $name table name
+	 * @param string $lft left delimiter
+	 * @param string $rgt right delimiter
+	 * @return string the properly quoted table name
+	 */
+	public function quoteTableName($name, $lft='', $rgt='')
+	{
+		$name = str_replace(self::$delimiterIdentifier, '', $name);
+
+		if(strpos($name, '.')===false)
+			return $lft . $name . $rgt;
+		$names=explode('.', $name);
+		foreach($names as &$n)
+			$n = $lft . $n . $rgt;
+		return implode('.', $names);
+	}
+
+	/**
+	 * Quotes a column name for use in a query.
+	 * @param string $name column name
+	 * @param string $lft left delimiter
+	 * @param string $rgt right delimiter
+	 * @return string the properly quoted column name
+	 */
+	public function quoteColumnName($name, $lft='', $rgt='')
+	{
+		return $lft . str_replace(self::$delimiterIdentifier, '', $name) . $rgt;
+	}
+
+	/**
+	 * Quotes a column alias for use in a query.
+	 * @param string $name column alias
+	 * @param string $lft left delimiter
+	 * @param string $rgt right delimiter
+	 * @return string the properly quoted column alias
+	 */
+	public function quoteColumnAlias($name, $lft='', $rgt='')
+	{
+		return $lft . str_replace(self::$delimiterIdentifier, '', $name) . $rgt;
+	}
 }
 
-- 
cgit v1.2.3