summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorGODZilla0480@gmail.com <>2011-08-28 06:24:03 +0000
committerGODZilla0480@gmail.com <>2011-08-28 06:24:03 +0000
commitfb494d1e299e256721cba77105a01b7480c48c08 (patch)
tree60a126bd254916501f45f8034108a77c580bd0bc
parent5ad92b6fe39f04aad8d94f32d23c7e665725fe8c (diff)
Add methods quoteTableName, quoteColumnName, quoteColumnAlias to TDbMetaData & TDbConnection and add TDbConnection:getDbMetaData [TODO: customize TOracleMetaData]
-rw-r--r--HISTORY1
-rw-r--r--framework/Data/Common/Mssql/TMssqlMetaData.php80
-rw-r--r--framework/Data/Common/Mysql/TMysqlMetaData.php38
-rw-r--r--framework/Data/Common/Pgsql/TPgsqlMetaData.php38
-rw-r--r--framework/Data/Common/Sqlite/TSqliteMetaData.php32
-rw-r--r--framework/Data/Common/TDbMetaData.php48
-rw-r--r--framework/Data/TDbConnection.php48
7 files changed, 251 insertions, 34 deletions
diff --git a/HISTORY b/HISTORY
index 23e3ab83..6940e7d8 100644
--- a/HISTORY
+++ b/HISTORY
@@ -11,6 +11,7 @@ ENH: Add THttpSession::regenerate() to update the current session id with a newl
CHG: Remove TReflectionClass and all references since equals ReflectionClass (Yves)
CHG: Modifiy TTemplate::parseAttributes to allow definition of "HTML5 Custom Data Attributes (data-*)" e.g Attributes.data-custom-value="foobar" (Yves)
EHN: Modify TActiveRecordConfig & TActiveRecordManager to allow custom subclassing of System.Data.ActiveRecord.TActiveRecordManager & System.Data.ActiveRecord.TActiveRecordGateway (Yves)
+EHN: Add methods quoteTableName, quoteColumnName, quoteColumnAlias to TDbMetaData & TDbConnection and add TDbConnection:getDbMetaData [TODO: customize TOracleMetaData] (Yves)
Version 3.1.9 June 3, 2011
BUG: Issue#280 - Documentation has been updated
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
*/
/**
@@ -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;
+ }
}
diff --git a/framework/Data/TDbConnection.php b/framework/Data/TDbConnection.php
index 69420ea1..77b15a30 100644
--- a/framework/Data/TDbConnection.php
+++ b/framework/Data/TDbConnection.php
@@ -99,6 +99,11 @@ class TDbConnection extends TComponent
private $_transaction;
/**
+ * @var TDbMetaData
+ */
+ private $_dbMeta = null;
+
+ /**
* @var string
* @since 3.1.7
*/
@@ -393,6 +398,49 @@ class TDbConnection extends TComponent
}
/**
+ * 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 $this->getDbMetaData()->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 $this->getDbMetaData()->quoteColumnName($name);
+ }
+
+ /**
+ * Quotes a column alias for use in a query.
+ * @param string $name column name
+ * @return string the properly quoted column alias
+ */
+ public function quoteColumnAlias($name)
+ {
+ return $this->getDbMetaData()->quoteColumnAlias($name);
+ }
+
+ /**
+ * @return TDbMetaData
+ */
+ public function getDbMetaData()
+ {
+ if($this->_dbMeta===null)
+ {
+ Prado::using('System.Data.Common.TDbMetaData');
+ $this->_dbMeta = TDbMetaData::getInstance($this);
+ }
+ return $this->_dbMeta;
+ }
+
+ /**
* @return TDbColumnCaseMode the case of the column names
*/
public function getColumnCase()