Add methods quoteTableName, quoteColumnName, quoteColumnAlias to TDbMetaData & TDbConnection and add TDbConnection:getDbMetaData [TODO: customize TOracleMetaData]

5 files changed, 202 insertions, 34 deletions

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 © 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
  */
 
 /**
@@ -35,6 +35,36 @@ class TMssqlMetaData extends TDbMetaData
 	}
 
 	/**
+	 * 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.
 	 * @return TMssqlTableInfo table information.
@@ -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

@@ -40,6 +40,36 @@ class TMysqlMetaData extends TDbMetaData
 	}

 

 	/**

+	 * 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.

 	 * @return TMysqlTableInfo table information.

@@ -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

@@ -37,6 +37,36 @@ class TPgsqlMetaData extends TDbMetaData
 	}

 

 	/**

+	 * 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.

 	 */

 	public function setDefaultSchema($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

@@ -35,6 +35,36 @@ class TSqliteMetaData extends TDbMetaData
 	}

 

 	/**

+	 * 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.

 	 * @return TPgsqlTableInfo table information.

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
@@ -27,6 +27,11 @@ abstract class TDbMetaData extends TComponent
 	private $_connection;

 

 	/**

+	 * @var array

+	 */

+	protected static $delimiterIdentifier = array('[', ']', '"', '`', "'");

+

+	/**

 	 * @param TDbConnection database connection.

 	 */

 	public function __construct($conn)

@@ -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;

+	}

 }