diff options
Diffstat (limited to 'framework')
-rw-r--r-- | framework/Data/Common/Mssql/TMssqlMetaData.php | 80 | ||||
-rw-r--r-- | framework/Data/Common/Mysql/TMysqlMetaData.php | 38 | ||||
-rw-r--r-- | framework/Data/Common/Pgsql/TPgsqlMetaData.php | 38 | ||||
-rw-r--r-- | framework/Data/Common/Sqlite/TSqliteMetaData.php | 32 | ||||
-rw-r--r-- | framework/Data/Common/TDbMetaData.php | 48 | ||||
-rw-r--r-- | framework/Data/TDbConnection.php | 48 |
6 files changed, 250 insertions, 34 deletions
diff --git a/framework/Data/Common/Mssql/TMssqlMetaData.php b/framework/Data/Common/Mssql/TMssqlMetaData.php index 334a297e7..08d005ea0 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 d3de3ff6a..5eba540dd 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 5ac572391..e57f1b37e 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 © 2005-2011 PradoSoft + * @copyright Copyright © 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 1ae872c2d..d4fbd4350 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 © 2005-2011 PradoSoft + * @copyright Copyright © 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 13f2eeb9b..bc29927f7 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 69420ea1c..77b15a30e 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()
|