From 903ae8a581fac1e6917fc3e31d2ad8fb91df80c3 Mon Sep 17 00:00:00 2001 From: ctrlaltca <> Date: Thu, 12 Jul 2012 11:21:01 +0000 Subject: standardize the use of unix eol; use svn properties to enforce native eol --- .../Data/Common/Mssql/TMssqlCommandBuilder.php | 346 +++---- framework/Data/Common/Mssql/TMssqlTableColumn.php | 126 +-- framework/Data/Common/Mssql/TMssqlTableInfo.php | 126 +-- .../Data/Common/Mysql/TMysqlCommandBuilder.php | 50 +- framework/Data/Common/Mysql/TMysqlMetaData.php | 772 +++++++-------- framework/Data/Common/Mysql/TMysqlTableColumn.php | 142 +-- framework/Data/Common/Mysql/TMysqlTableInfo.php | 116 +-- .../Data/Common/Pgsql/TPgsqlCommandBuilder.php | 136 +-- framework/Data/Common/Pgsql/TPgsqlMetaData.php | 844 ++++++++-------- framework/Data/Common/Pgsql/TPgsqlTableColumn.php | 92 +- framework/Data/Common/Pgsql/TPgsqlTableInfo.php | 110 +-- .../Data/Common/Sqlite/TSqliteCommandBuilder.php | 92 +- framework/Data/Common/Sqlite/TSqliteMetaData.php | 420 ++++---- .../Data/Common/Sqlite/TSqliteTableColumn.php | 126 +-- framework/Data/Common/Sqlite/TSqliteTableInfo.php | 92 +- framework/Data/Common/TDbCommandBuilder.php | 1014 ++++++++++---------- framework/Data/Common/TDbMetaData.php | 364 +++---- framework/Data/Common/TDbTableColumn.php | 396 ++++---- framework/Data/Common/TDbTableInfo.php | 310 +++--- 19 files changed, 2837 insertions(+), 2837 deletions(-) (limited to 'framework/Data/Common') 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 @@ - - * @link http://www.pradosoft.com/ - * @copyright Copyright © 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 - * @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 - * - * - * 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) - * - * - * Regular expressions are used to alter the SQL query. The resulting SQL query - * may be malformed for complex queries. The following restrictions apply - * - * - * - * @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; - } -} - + + * @link http://www.pradosoft.com/ + * @copyright Copyright © 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 + * @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 + * + * + * 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) + * + * + * Regular expressions are used to alter the SQL query. The resulting SQL query + * may be malformed for complex queries. The following restrictions apply + * + * + * + * @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 @@ - - * @link http://www.pradosoft.com/ + + * @link http://www.pradosoft.com/ * @copyright Copyright © 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 - * @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 + * @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 @@ - - * @link http://www.pradosoft.com/ + + * @link http://www.pradosoft.com/ * @copyright Copyright © 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 - * @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 + * @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); + } +} + diff --git a/framework/Data/Common/Mysql/TMysqlCommandBuilder.php b/framework/Data/Common/Mysql/TMysqlCommandBuilder.php index fa2b2c51..58854bbc 100644 --- a/framework/Data/Common/Mysql/TMysqlCommandBuilder.php +++ b/framework/Data/Common/Mysql/TMysqlCommandBuilder.php @@ -1,26 +1,26 @@ - - * @link http://www.pradosoft.com/ + + * @link http://www.pradosoft.com/ * @copyright Copyright © 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'); - -/** - * TMysqlCommandBuilder implements default TDbCommandBuilder - * - * @author Wei Zhuo - * @version $Id: TDbCommandBuilder.php 1863 2007-04-12 12:43:49Z wei $ - * @package System.Data.Common - * @since 3.1 - */ -class TMysqlCommandBuilder extends TDbCommandBuilder -{ -} - + * @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'); + +/** + * TMysqlCommandBuilder implements default TDbCommandBuilder + * + * @author Wei Zhuo + * @version $Id: TDbCommandBuilder.php 1863 2007-04-12 12:43:49Z wei $ + * @package System.Data.Common + * @since 3.1 + */ +class TMysqlCommandBuilder extends TDbCommandBuilder +{ +} + diff --git a/framework/Data/Common/Mysql/TMysqlMetaData.php b/framework/Data/Common/Mysql/TMysqlMetaData.php index f9824015..151111af 100644 --- a/framework/Data/Common/Mysql/TMysqlMetaData.php +++ b/framework/Data/Common/Mysql/TMysqlMetaData.php @@ -1,386 +1,386 @@ - - * @link http://www.pradosoft.com/ - * @copyright Copyright © 2005-2012 PradoSoft - * @license http://www.pradosoft.com/license/ - * @version $Id$ - * @package System.Data.Common.Mysql - */ - -/** - * Load the base TDbMetaData class. - */ -Prado::using('System.Data.Common.TDbMetaData'); -Prado::using('System.Data.Common.Mysql.TMysqlTableInfo'); - -/** - * TMysqlMetaData loads Mysql version 4.1.x and 5.x database table and column information. - * - * For Mysql version 4.1.x, PHP 5.1.3 or later is required. - * See http://netevil.org/node.php?nid=795&SC=1 - * - * @author Wei Zhuo - * @version $Id$ - * @package System.Data.Common.Mysql - * @since 3.1 - */ -class TMysqlMetaData extends TDbMetaData -{ - private $_serverVersion=0; - - /** - * @return string TDbTableInfo class name. - */ - protected function getTableInfoClass() - { - 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. - * @return TMysqlTableInfo table information. - */ - protected function createTableInfo($table) - { - list($schemaName,$tableName) = $this->getSchemaTableName($table); - $find = $schemaName===null ? "`{$tableName}`" : "`{$schemaName}`.`{$tableName}`"; - $this->getDbConnection()->setActive(true); - $sql = "SHOW FULL FIELDS FROM {$find}"; - $command = $this->getDbConnection()->createCommand($sql); - $tableInfo = $this->createNewTableInfo($table); - $index=0; - foreach($command->query() as $col) - { - $col['index'] = $index++; - $this->processColumn($tableInfo,$col); - } - if($index===0) - throw new TDbException('dbmetadata_invalid_table_view', $table); - return $tableInfo; - } - - /** - * @return float server version. - */ - protected function getServerVersion() - { - if(!$this->_serverVersion) - { - $version = $this->getDbConnection()->getAttribute(PDO::ATTR_SERVER_VERSION); - $digits=array(); - preg_match('/(\d+)\.(\d+)\.(\d+)/', $version, $digits); - $this->_serverVersion=floatval($digits[1].'.'.$digits[2].$digits[3]); - } - return $this->_serverVersion; - } - - /** - * @param TMysqlTableInfo table information. - * @param array column information. - */ - protected function processColumn($tableInfo, $col) - { - $columnId = $col['Field']; - - $info['ColumnName'] = "`$columnId`"; //quote the column names! - $info['ColumnId'] = $columnId; - $info['ColumnIndex'] = $col['index']; - if($col['Null']==='YES') - $info['AllowNull'] = true; - if(is_int(strpos(strtolower($col['Extra']), 'auto_increment'))) - $info['AutoIncrement']=true; - if($col['Default']!=="") - $info['DefaultValue'] = $col['Default']; - - if($col['Key']==='PRI' || in_array($columnId, $tableInfo->getPrimaryKeys())) - $info['IsPrimaryKey'] = true; - if($this->isForeignKeyColumn($columnId, $tableInfo)) - $info['IsForeignKey'] = true; - - $info['DbType'] = $col['Type']; - $match=array(); - //find SET/ENUM values, column size, precision, and scale - if(preg_match('/\((.*)\)/', $col['Type'], $match)) - { - $info['DbType']= preg_replace('/\(.*\)/', '', $col['Type']); - - //find SET/ENUM values - if($this->isEnumSetType($info['DbType'])) - $info['DbTypeValues'] = preg_split("/[',]/S", $match[1], -1, PREG_SPLIT_NO_EMPTY); - - //find column size, precision and scale - $pscale = array(); - if(preg_match('/(\d+)(?:,(\d+))?+/', $match[1], $pscale)) - { - if($this->isPrecisionType($info['DbType'])) - { - $info['NumericPrecision'] = intval($pscale[1]); - if(count($pscale) > 2) - $info['NumericScale'] = intval($pscale[2]); - } - else - $info['ColumnSize'] = intval($pscale[1]); - } - } - - $tableInfo->Columns[$columnId] = new TMysqlTableColumn($info); - } - - /** - * @return boolean true if column type if "numeric", "interval" or begins with "time". - */ - protected function isPrecisionType($type) - { - $type = strtolower(trim($type)); - return $type==='decimal' || $type==='dec' - || $type==='float' || $type==='double' - || $type==='double precision' || $type==='real'; - } - - /** - * @return boolean true if column type if "enum" or "set". - */ - protected function isEnumSetType($type) - { - $type = strtolower(trim($type)); - return $type==='set' || $type==='enum'; - } - - /** - * @param string table name, may be quoted with back-ticks and may contain database name. - * @return array tuple ($schema,$table), $schema may be null. - * @throws TDbException when table name contains invalid identifier bytes. - */ - protected function getSchemaTableName($table) - { - //remove the back ticks and separate out the "database.table" - $result = explode('.', str_replace('`', '', $table)); - foreach($result as $name) - { - if(!$this->isValidIdentifier($name)) - { - $ref = 'http://dev.mysql.com/doc/refman/5.0/en/identifiers.html'; - throw new TDbException('dbcommon_invalid_identifier_name', $table, $ref); - } - } - return count($result) > 1 ? $result : array(null, $result[0]); - } - - /** - * http://dev.mysql.com/doc/refman/5.0/en/identifiers.html - * @param string identifier name - * @param boolean true if valid identifier. - */ - protected function isValidIdentifier($name) - { - return !preg_match('#/|\\|.|\x00|\xFF#', $name); - } - - /** - * @param string table schema name - * @param string table name. - * @return TMysqlTableInfo - */ - protected function createNewTableInfo($table) - { - list($schemaName,$tableName) = $this->getSchemaTableName($table); - $info['SchemaName'] = $schemaName; - $info['TableName'] = $tableName; - if($this->getIsView($schemaName,$tableName)) - $info['IsView'] = true; - list($primary, $foreign) = $this->getConstraintKeys($schemaName, $tableName); - $class = $this->getTableInfoClass(); - return new $class($info,$primary,$foreign); - } - - /** - * For MySQL version 5.0.1 or later we can use SHOW FULL TABLES - * http://dev.mysql.com/doc/refman/5.0/en/show-tables.html - * - * For MySQL version 5.0.1 or ealier, this always return false. - * @param string database name, null to use default connection database. - * @param string table or view name. - * @return boolean true if is view, false otherwise. - * @throws TDbException if table or view does not exist. - */ - protected function getIsView($schemaName,$tableName) - { - if($this->getServerVersion()<5.01) - return false; - if($schemaName!==null) - $sql = "SHOW FULL TABLES FROM `{$schemaName}` LIKE :table"; - else - $sql = "SHOW FULL TABLES LIKE :table"; - - $command = $this->getDbConnection()->createCommand($sql); - $command->bindValue(':table', $tableName); - try - { - return count($result = $command->queryRow()) > 0 && $result['Table_type']==='VIEW'; - } - catch(TDbException $e) - { - $table = $schemaName===null?$tableName:$schemaName.'.'.$tableName; - throw new TDbException('dbcommon_invalid_table_name',$table,$e->getMessage()); - } - } - - /** - * Gets the primary and foreign key column details for the given table. - * @param string schema name - * @param string table name. - * @return array tuple ($primary, $foreign) - */ - protected function getConstraintKeys($schemaName, $tableName) - { - $table = $schemaName===null ? "`{$tableName}`" : "`{$schemaName}`.`{$tableName}`"; - $sql = "SHOW INDEX FROM {$table}"; - $command = $this->getDbConnection()->createCommand($sql); - $primary = array(); - foreach($command->query() as $row) - { - 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) - if($this->getServerVersion() >= 5.121) - $foreign = $this->getForeignConstraints($schemaName,$tableName); - else - $foreign = $this->findForeignConstraints($schemaName,$tableName); - return array($primary,$foreign); - } - - /** - * Gets foreign relationship constraint keys and table name - * @param string database name - * @param string table name - * @return array foreign relationship table name and keys. - */ - protected function getForeignConstraints($schemaName, $tableName) - { - $andSchema = $schemaName !== null ? 'AND TABLE_SCHEMA LIKE :schema' : 'AND TABLE_SCHEMA LIKE DATABASE()'; - $sql = <<getDbConnection()->createCommand($sql); - $command->bindValue(':table', $tableName); - if($schemaName!==null) - $command->bindValue(':schema', $schemaName); - $fkeys=array(); - foreach($command->query() as $col) - { - $fkeys[$col['con']]['keys'][$col['col']] = $col['fkcol']; - $fkeys[$col['con']]['table'] = $col['fktable']; - } - return count($fkeys) > 0 ? array_values($fkeys) : $fkeys; - } - - /** - * @param string database name - * @param string table name - * @return string SQL command to create the table. - * @throws TDbException if PHP version is less than 5.1.3 - */ - protected function getShowCreateTable($schemaName, $tableName) - { - if(version_compare(PHP_VERSION,'5.1.3','<')) - throw new TDbException('dbmetadata_requires_php_version', 'Mysql 4.1.x', '5.1.3'); - - //See http://netevil.org/node.php?nid=795&SC=1 - $this->getDbConnection()->setAttribute(PDO::ATTR_EMULATE_PREPARES, true); - if($schemaName!==null) - $sql = "SHOW CREATE TABLE `{$schemaName}`.`{$tableName}`"; - else - $sql = "SHOW CREATE TABLE `{$tableName}`"; - $command = $this->getDbConnection()->createCommand($sql); - $result = $command->queryRow(); - return isset($result['Create Table']) ? $result['Create Table'] : (isset($result['Create View']) ? $result['Create View'] : ''); - } - - /** - * Extract foreign key constraints by extracting the contraints from SHOW CREATE TABLE result. - * @param string database name - * @param string table name - * @return array foreign relationship table name and keys. - */ - protected function findForeignConstraints($schemaName, $tableName) - { - $sql = $this->getShowCreateTable($schemaName, $tableName); - $matches =array(); - $regexp = '/FOREIGN KEY\s+\(([^\)]+)\)\s+REFERENCES\s+`?([^`]+)`?\s\(([^\)]+)\)/mi'; - preg_match_all($regexp,$sql,$matches,PREG_SET_ORDER); - $foreign = array(); - foreach($matches as $match) - { - $fields = array_map('trim',explode(',',str_replace('`','',$match[1]))); - $fk_fields = array_map('trim',explode(',',str_replace('`','',$match[3]))); - $keys=array(); - foreach($fields as $k=>$v) - $keys[$v] = $fk_fields[$k]; - $foreign[] = array('keys' => $keys, 'table' => trim($match[2])); - } - return $foreign; - } - - /** - * @param string column name. - * @param TPgsqlTableInfo table information. - * @return boolean true if column is a foreign key. - */ - protected function isForeignKeyColumn($columnId, $tableInfo) - { - foreach($tableInfo->getForeignKeys() as $fk) - { - if(in_array($columnId, array_keys($fk['keys']))) - return true; - } - return false; - } -} - + + * @link http://www.pradosoft.com/ + * @copyright Copyright © 2005-2012 PradoSoft + * @license http://www.pradosoft.com/license/ + * @version $Id$ + * @package System.Data.Common.Mysql + */ + +/** + * Load the base TDbMetaData class. + */ +Prado::using('System.Data.Common.TDbMetaData'); +Prado::using('System.Data.Common.Mysql.TMysqlTableInfo'); + +/** + * TMysqlMetaData loads Mysql version 4.1.x and 5.x database table and column information. + * + * For Mysql version 4.1.x, PHP 5.1.3 or later is required. + * See http://netevil.org/node.php?nid=795&SC=1 + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.Common.Mysql + * @since 3.1 + */ +class TMysqlMetaData extends TDbMetaData +{ + private $_serverVersion=0; + + /** + * @return string TDbTableInfo class name. + */ + protected function getTableInfoClass() + { + 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. + * @return TMysqlTableInfo table information. + */ + protected function createTableInfo($table) + { + list($schemaName,$tableName) = $this->getSchemaTableName($table); + $find = $schemaName===null ? "`{$tableName}`" : "`{$schemaName}`.`{$tableName}`"; + $this->getDbConnection()->setActive(true); + $sql = "SHOW FULL FIELDS FROM {$find}"; + $command = $this->getDbConnection()->createCommand($sql); + $tableInfo = $this->createNewTableInfo($table); + $index=0; + foreach($command->query() as $col) + { + $col['index'] = $index++; + $this->processColumn($tableInfo,$col); + } + if($index===0) + throw new TDbException('dbmetadata_invalid_table_view', $table); + return $tableInfo; + } + + /** + * @return float server version. + */ + protected function getServerVersion() + { + if(!$this->_serverVersion) + { + $version = $this->getDbConnection()->getAttribute(PDO::ATTR_SERVER_VERSION); + $digits=array(); + preg_match('/(\d+)\.(\d+)\.(\d+)/', $version, $digits); + $this->_serverVersion=floatval($digits[1].'.'.$digits[2].$digits[3]); + } + return $this->_serverVersion; + } + + /** + * @param TMysqlTableInfo table information. + * @param array column information. + */ + protected function processColumn($tableInfo, $col) + { + $columnId = $col['Field']; + + $info['ColumnName'] = "`$columnId`"; //quote the column names! + $info['ColumnId'] = $columnId; + $info['ColumnIndex'] = $col['index']; + if($col['Null']==='YES') + $info['AllowNull'] = true; + if(is_int(strpos(strtolower($col['Extra']), 'auto_increment'))) + $info['AutoIncrement']=true; + if($col['Default']!=="") + $info['DefaultValue'] = $col['Default']; + + if($col['Key']==='PRI' || in_array($columnId, $tableInfo->getPrimaryKeys())) + $info['IsPrimaryKey'] = true; + if($this->isForeignKeyColumn($columnId, $tableInfo)) + $info['IsForeignKey'] = true; + + $info['DbType'] = $col['Type']; + $match=array(); + //find SET/ENUM values, column size, precision, and scale + if(preg_match('/\((.*)\)/', $col['Type'], $match)) + { + $info['DbType']= preg_replace('/\(.*\)/', '', $col['Type']); + + //find SET/ENUM values + if($this->isEnumSetType($info['DbType'])) + $info['DbTypeValues'] = preg_split("/[',]/S", $match[1], -1, PREG_SPLIT_NO_EMPTY); + + //find column size, precision and scale + $pscale = array(); + if(preg_match('/(\d+)(?:,(\d+))?+/', $match[1], $pscale)) + { + if($this->isPrecisionType($info['DbType'])) + { + $info['NumericPrecision'] = intval($pscale[1]); + if(count($pscale) > 2) + $info['NumericScale'] = intval($pscale[2]); + } + else + $info['ColumnSize'] = intval($pscale[1]); + } + } + + $tableInfo->Columns[$columnId] = new TMysqlTableColumn($info); + } + + /** + * @return boolean true if column type if "numeric", "interval" or begins with "time". + */ + protected function isPrecisionType($type) + { + $type = strtolower(trim($type)); + return $type==='decimal' || $type==='dec' + || $type==='float' || $type==='double' + || $type==='double precision' || $type==='real'; + } + + /** + * @return boolean true if column type if "enum" or "set". + */ + protected function isEnumSetType($type) + { + $type = strtolower(trim($type)); + return $type==='set' || $type==='enum'; + } + + /** + * @param string table name, may be quoted with back-ticks and may contain database name. + * @return array tuple ($schema,$table), $schema may be null. + * @throws TDbException when table name contains invalid identifier bytes. + */ + protected function getSchemaTableName($table) + { + //remove the back ticks and separate out the "database.table" + $result = explode('.', str_replace('`', '', $table)); + foreach($result as $name) + { + if(!$this->isValidIdentifier($name)) + { + $ref = 'http://dev.mysql.com/doc/refman/5.0/en/identifiers.html'; + throw new TDbException('dbcommon_invalid_identifier_name', $table, $ref); + } + } + return count($result) > 1 ? $result : array(null, $result[0]); + } + + /** + * http://dev.mysql.com/doc/refman/5.0/en/identifiers.html + * @param string identifier name + * @param boolean true if valid identifier. + */ + protected function isValidIdentifier($name) + { + return !preg_match('#/|\\|.|\x00|\xFF#', $name); + } + + /** + * @param string table schema name + * @param string table name. + * @return TMysqlTableInfo + */ + protected function createNewTableInfo($table) + { + list($schemaName,$tableName) = $this->getSchemaTableName($table); + $info['SchemaName'] = $schemaName; + $info['TableName'] = $tableName; + if($this->getIsView($schemaName,$tableName)) + $info['IsView'] = true; + list($primary, $foreign) = $this->getConstraintKeys($schemaName, $tableName); + $class = $this->getTableInfoClass(); + return new $class($info,$primary,$foreign); + } + + /** + * For MySQL version 5.0.1 or later we can use SHOW FULL TABLES + * http://dev.mysql.com/doc/refman/5.0/en/show-tables.html + * + * For MySQL version 5.0.1 or ealier, this always return false. + * @param string database name, null to use default connection database. + * @param string table or view name. + * @return boolean true if is view, false otherwise. + * @throws TDbException if table or view does not exist. + */ + protected function getIsView($schemaName,$tableName) + { + if($this->getServerVersion()<5.01) + return false; + if($schemaName!==null) + $sql = "SHOW FULL TABLES FROM `{$schemaName}` LIKE :table"; + else + $sql = "SHOW FULL TABLES LIKE :table"; + + $command = $this->getDbConnection()->createCommand($sql); + $command->bindValue(':table', $tableName); + try + { + return count($result = $command->queryRow()) > 0 && $result['Table_type']==='VIEW'; + } + catch(TDbException $e) + { + $table = $schemaName===null?$tableName:$schemaName.'.'.$tableName; + throw new TDbException('dbcommon_invalid_table_name',$table,$e->getMessage()); + } + } + + /** + * Gets the primary and foreign key column details for the given table. + * @param string schema name + * @param string table name. + * @return array tuple ($primary, $foreign) + */ + protected function getConstraintKeys($schemaName, $tableName) + { + $table = $schemaName===null ? "`{$tableName}`" : "`{$schemaName}`.`{$tableName}`"; + $sql = "SHOW INDEX FROM {$table}"; + $command = $this->getDbConnection()->createCommand($sql); + $primary = array(); + foreach($command->query() as $row) + { + 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) + if($this->getServerVersion() >= 5.121) + $foreign = $this->getForeignConstraints($schemaName,$tableName); + else + $foreign = $this->findForeignConstraints($schemaName,$tableName); + return array($primary,$foreign); + } + + /** + * Gets foreign relationship constraint keys and table name + * @param string database name + * @param string table name + * @return array foreign relationship table name and keys. + */ + protected function getForeignConstraints($schemaName, $tableName) + { + $andSchema = $schemaName !== null ? 'AND TABLE_SCHEMA LIKE :schema' : 'AND TABLE_SCHEMA LIKE DATABASE()'; + $sql = <<getDbConnection()->createCommand($sql); + $command->bindValue(':table', $tableName); + if($schemaName!==null) + $command->bindValue(':schema', $schemaName); + $fkeys=array(); + foreach($command->query() as $col) + { + $fkeys[$col['con']]['keys'][$col['col']] = $col['fkcol']; + $fkeys[$col['con']]['table'] = $col['fktable']; + } + return count($fkeys) > 0 ? array_values($fkeys) : $fkeys; + } + + /** + * @param string database name + * @param string table name + * @return string SQL command to create the table. + * @throws TDbException if PHP version is less than 5.1.3 + */ + protected function getShowCreateTable($schemaName, $tableName) + { + if(version_compare(PHP_VERSION,'5.1.3','<')) + throw new TDbException('dbmetadata_requires_php_version', 'Mysql 4.1.x', '5.1.3'); + + //See http://netevil.org/node.php?nid=795&SC=1 + $this->getDbConnection()->setAttribute(PDO::ATTR_EMULATE_PREPARES, true); + if($schemaName!==null) + $sql = "SHOW CREATE TABLE `{$schemaName}`.`{$tableName}`"; + else + $sql = "SHOW CREATE TABLE `{$tableName}`"; + $command = $this->getDbConnection()->createCommand($sql); + $result = $command->queryRow(); + return isset($result['Create Table']) ? $result['Create Table'] : (isset($result['Create View']) ? $result['Create View'] : ''); + } + + /** + * Extract foreign key constraints by extracting the contraints from SHOW CREATE TABLE result. + * @param string database name + * @param string table name + * @return array foreign relationship table name and keys. + */ + protected function findForeignConstraints($schemaName, $tableName) + { + $sql = $this->getShowCreateTable($schemaName, $tableName); + $matches =array(); + $regexp = '/FOREIGN KEY\s+\(([^\)]+)\)\s+REFERENCES\s+`?([^`]+)`?\s\(([^\)]+)\)/mi'; + preg_match_all($regexp,$sql,$matches,PREG_SET_ORDER); + $foreign = array(); + foreach($matches as $match) + { + $fields = array_map('trim',explode(',',str_replace('`','',$match[1]))); + $fk_fields = array_map('trim',explode(',',str_replace('`','',$match[3]))); + $keys=array(); + foreach($fields as $k=>$v) + $keys[$v] = $fk_fields[$k]; + $foreign[] = array('keys' => $keys, 'table' => trim($match[2])); + } + return $foreign; + } + + /** + * @param string column name. + * @param TPgsqlTableInfo table information. + * @return boolean true if column is a foreign key. + */ + protected function isForeignKeyColumn($columnId, $tableInfo) + { + foreach($tableInfo->getForeignKeys() as $fk) + { + if(in_array($columnId, array_keys($fk['keys']))) + return true; + } + return false; + } +} + diff --git a/framework/Data/Common/Mysql/TMysqlTableColumn.php b/framework/Data/Common/Mysql/TMysqlTableColumn.php index 34dceaad..901f4f54 100644 --- a/framework/Data/Common/Mysql/TMysqlTableColumn.php +++ b/framework/Data/Common/Mysql/TMysqlTableColumn.php @@ -1,72 +1,72 @@ - - * @link http://www.pradosoft.com/ + + * @link http://www.pradosoft.com/ * @copyright Copyright © 2005-2012 PradoSoft - * @license http://www.pradosoft.com/license/ - * @version $Id$ - * @package System.Data.Common.Mysql - */ - -/** - * Load common TDbTableCommon class. - */ -Prado::using('System.Data.Common.TDbTableColumn'); - -/** - * Describes the column metadata of the schema for a Mysql database table. - * - * @author Wei Zhuo - * @version $Id$ - * @package System.Data.Common.Mysql - * @since 3.1 - */ -class TMysqlTableColumn extends TDbTableColumn -{ - private static $types = array( - 'integer' => array('bit', 'tinyint', 'smallint', 'mediumint', 'int', 'integer', 'bigint'), - 'boolean' => array('boolean', 'bool'), - 'float' => array('float', 'double', 'double precision', 'decimal', 'dec', 'numeric', 'fixed') - ); - - /** - * Overrides parent implementation, returns PHP type from the db type. - * @return boolean derived PHP primitive type from the column db type. - */ - public function getPHPType() - { - $dbtype = trim(str_replace(array('unsigned', 'zerofill'),array('','',),strtolower($this->getDbType()))); - if($dbtype==='tinyint' && $this->getColumnSize()===1) - return 'boolean'; - foreach(self::$types as $type => $dbtypes) - { - if(in_array($dbtype, $dbtypes)) - return $type; - } - return 'string'; - } - - /** - * @return boolean true if column will auto-increment when the column value is inserted as null. - */ - public function getAutoIncrement() - { - return $this->getInfo('AutoIncrement', false); - } - - /** - * @return boolean true if auto increment is true. - */ - public function hasSequence() - { - return $this->getAutoIncrement(); - } - - public function getDbTypeValues() - { - return $this->getInfo('DbTypeValues'); - } -} - + * @license http://www.pradosoft.com/license/ + * @version $Id$ + * @package System.Data.Common.Mysql + */ + +/** + * Load common TDbTableCommon class. + */ +Prado::using('System.Data.Common.TDbTableColumn'); + +/** + * Describes the column metadata of the schema for a Mysql database table. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.Common.Mysql + * @since 3.1 + */ +class TMysqlTableColumn extends TDbTableColumn +{ + private static $types = array( + 'integer' => array('bit', 'tinyint', 'smallint', 'mediumint', 'int', 'integer', 'bigint'), + 'boolean' => array('boolean', 'bool'), + 'float' => array('float', 'double', 'double precision', 'decimal', 'dec', 'numeric', 'fixed') + ); + + /** + * Overrides parent implementation, returns PHP type from the db type. + * @return boolean derived PHP primitive type from the column db type. + */ + public function getPHPType() + { + $dbtype = trim(str_replace(array('unsigned', 'zerofill'),array('','',),strtolower($this->getDbType()))); + if($dbtype==='tinyint' && $this->getColumnSize()===1) + return 'boolean'; + foreach(self::$types as $type => $dbtypes) + { + if(in_array($dbtype, $dbtypes)) + return $type; + } + return 'string'; + } + + /** + * @return boolean true if column will auto-increment when the column value is inserted as null. + */ + public function getAutoIncrement() + { + return $this->getInfo('AutoIncrement', false); + } + + /** + * @return boolean true if auto increment is true. + */ + public function hasSequence() + { + return $this->getAutoIncrement(); + } + + public function getDbTypeValues() + { + return $this->getInfo('DbTypeValues'); + } +} + diff --git a/framework/Data/Common/Mysql/TMysqlTableInfo.php b/framework/Data/Common/Mysql/TMysqlTableInfo.php index 30a3d0a1..40b421db 100644 --- a/framework/Data/Common/Mysql/TMysqlTableInfo.php +++ b/framework/Data/Common/Mysql/TMysqlTableInfo.php @@ -1,59 +1,59 @@ - - * @link http://www.pradosoft.com/ + + * @link http://www.pradosoft.com/ * @copyright Copyright © 2005-2012 PradoSoft - * @license http://www.pradosoft.com/license/ - * @version $Id$ - * @package System.Data.Common.Mysql - */ - -/** - * Loads the base TDbTableInfo class and TMysqlTableColumn class. - */ -Prado::using('System.Data.Common.TDbTableInfo'); -Prado::using('System.Data.Common.Mysql.TMysqlTableColumn'); - -/** - * TMysqlTableInfo class provides additional table information for MySQL database. - * - * @author Wei Zhuo - * @version $Id$ - * @package System.Data.Common.Mysql - * @since 3.1 - */ -class TMysqlTableInfo extends TDbTableInfo -{ - /** - * @return string name of the schema this column belongs to. - */ - public function getSchemaName() - { - return $this->getInfo('SchemaName'); - } - - /** - * @return string full name of the table, database dependent. - */ - public function getTableFullName() - { - if(($schema=$this->getSchemaName())!==null) - return '`'.$schema.'`.`'.$this->getTableName().'`'; - else - return '`'.$this->getTableName().'`'; - } - - /** - * @param TDbConnection database connection. - * @return TDbCommandBuilder new command builder - */ - public function createCommandBuilder($connection) - { - Prado::using('System.Data.Common.Mysql.TMysqlCommandBuilder'); - return new TMysqlCommandBuilder($connection,$this); - } -} - -?> + * @license http://www.pradosoft.com/license/ + * @version $Id$ + * @package System.Data.Common.Mysql + */ + +/** + * Loads the base TDbTableInfo class and TMysqlTableColumn class. + */ +Prado::using('System.Data.Common.TDbTableInfo'); +Prado::using('System.Data.Common.Mysql.TMysqlTableColumn'); + +/** + * TMysqlTableInfo class provides additional table information for MySQL database. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.Common.Mysql + * @since 3.1 + */ +class TMysqlTableInfo extends TDbTableInfo +{ + /** + * @return string name of the schema this column belongs to. + */ + public function getSchemaName() + { + return $this->getInfo('SchemaName'); + } + + /** + * @return string full name of the table, database dependent. + */ + public function getTableFullName() + { + if(($schema=$this->getSchemaName())!==null) + return '`'.$schema.'`.`'.$this->getTableName().'`'; + else + return '`'.$this->getTableName().'`'; + } + + /** + * @param TDbConnection database connection. + * @return TDbCommandBuilder new command builder + */ + public function createCommandBuilder($connection) + { + Prado::using('System.Data.Common.Mysql.TMysqlCommandBuilder'); + return new TMysqlCommandBuilder($connection,$this); + } +} + +?> diff --git a/framework/Data/Common/Pgsql/TPgsqlCommandBuilder.php b/framework/Data/Common/Pgsql/TPgsqlCommandBuilder.php index 8f4147e5..f9938d61 100644 --- a/framework/Data/Common/Pgsql/TPgsqlCommandBuilder.php +++ b/framework/Data/Common/Pgsql/TPgsqlCommandBuilder.php @@ -1,69 +1,69 @@ - - * @link http://www.pradosoft.com/ + + * @link http://www.pradosoft.com/ * @copyright Copyright © 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'); - -/** - * TPgsqlCommandBuilder provides specifics methods to create limit/offset query commands - * for Pgsql database. - * - * @author Wei Zhuo - * @version $Id: TDbCommandBuilder.php 1863 2007-04-12 12:43:49Z wei $ - * @package System.Data.Common - * @since 3.1 - */ -class TPgsqlCommandBuilder extends TDbCommandBuilder -{ - /** - * Overrides parent implementation. Only column of type text or character (and its variants) - * accepts the LIKE criteria. - * @param array list of column id for potential search condition. - * @param string string of keywords - * @return string SQL search condition matching on a set of columns. - */ - public function getSearchExpression($fields, $keywords) - { - $columns = array(); - foreach($fields as $field) - { - if($this->isSearchableColumn($this->getTableInfo()->getColumn($field))) - $columns[] = $field; - } - return parent::getSearchExpression($columns, $keywords); - } - /** - * - * @return boolean true if column can be used for LIKE searching. - */ - protected function isSearchableColumn($column) - { - $type = strtolower($column->getDbType()); - return $type === 'character varying' || $type === 'varchar' || - $type === 'character' || $type === 'char' || $type === 'text'; - } - - /** - * Overrides parent implementation to use PostgreSQL's ILIKE instead of LIKE (case-sensitive). - * @param string column name. - * @param array keywords - * @return string search condition for all words in one column. - */ - protected function getSearchCondition($column, $words) - { - $conditions=array(); - foreach($words as $word) - $conditions[] = $column.' ILIKE '.$this->getDbConnection()->quoteString('%'.$word.'%'); - return '('.implode(' AND ', $conditions).')'; - } - -} - + * @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'); + +/** + * TPgsqlCommandBuilder provides specifics methods to create limit/offset query commands + * for Pgsql database. + * + * @author Wei Zhuo + * @version $Id: TDbCommandBuilder.php 1863 2007-04-12 12:43:49Z wei $ + * @package System.Data.Common + * @since 3.1 + */ +class TPgsqlCommandBuilder extends TDbCommandBuilder +{ + /** + * Overrides parent implementation. Only column of type text or character (and its variants) + * accepts the LIKE criteria. + * @param array list of column id for potential search condition. + * @param string string of keywords + * @return string SQL search condition matching on a set of columns. + */ + public function getSearchExpression($fields, $keywords) + { + $columns = array(); + foreach($fields as $field) + { + if($this->isSearchableColumn($this->getTableInfo()->getColumn($field))) + $columns[] = $field; + } + return parent::getSearchExpression($columns, $keywords); + } + /** + * + * @return boolean true if column can be used for LIKE searching. + */ + protected function isSearchableColumn($column) + { + $type = strtolower($column->getDbType()); + return $type === 'character varying' || $type === 'varchar' || + $type === 'character' || $type === 'char' || $type === 'text'; + } + + /** + * Overrides parent implementation to use PostgreSQL's ILIKE instead of LIKE (case-sensitive). + * @param string column name. + * @param array keywords + * @return string search condition for all words in one column. + */ + protected function getSearchCondition($column, $words) + { + $conditions=array(); + foreach($words as $word) + $conditions[] = $column.' ILIKE '.$this->getDbConnection()->quoteString('%'.$word.'%'); + return '('.implode(' AND ', $conditions).')'; + } + +} + diff --git a/framework/Data/Common/Pgsql/TPgsqlMetaData.php b/framework/Data/Common/Pgsql/TPgsqlMetaData.php index 85785606..86a9892e 100644 --- a/framework/Data/Common/Pgsql/TPgsqlMetaData.php +++ b/framework/Data/Common/Pgsql/TPgsqlMetaData.php @@ -1,422 +1,422 @@ - - * @link http://www.pradosoft.com/ - * @copyright Copyright © 2005-2012 PradoSoft - * @license http://www.pradosoft.com/license/ - * @version $Id$ - * @package System.Data.Common.Pgsql - */ - -/** - * Load the base TDbMetaData class. - */ -Prado::using('System.Data.Common.TDbMetaData'); -Prado::using('System.Data.Common.Pgsql.TPgsqlTableInfo'); - -/** - * TPgsqlMetaData loads PostgreSQL database table and column information. - * - * @author Wei Zhuo - * @version $Id$ - * @package System.Data.Common.Pgsql - * @since 3.1 - */ -class TPgsqlMetaData extends TDbMetaData -{ - private $_defaultSchema = 'public'; - - /** - * @return string TDbTableInfo class name. - */ - protected function getTableInfoClass() - { - 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. - */ - public function setDefaultSchema($schema) - { - $this->_defaultSchema=$schema; - } - - /** - * @return string default schema. - */ - public function getDefaultSchema() - { - return $this->_defaultSchema; - } - - /** - * @param string table name with optional schema name prefix, uses default schema name prefix is not provided. - * @return array tuple as ($schemaName,$tableName) - */ - protected function getSchemaTableName($table) - { - if(count($parts= explode('.', str_replace('"','',$table))) > 1) - return array($parts[0], $parts[1]); - else - return array($this->getDefaultSchema(),$parts[0]); - } - - /** - * Get the column definitions for given table. - * @param string table name. - * @return TPgsqlTableInfo table information. - */ - protected function createTableInfo($table) - { - list($schemaName,$tableName) = $this->getSchemaTableName($table); - - // This query is made much more complex by the addition of the 'attisserial' field. - // The subquery to get that field checks to see if there is an internally dependent - // sequence on the field. - $sql = -<< 0 AND NOT a.attisdropped - ORDER BY a.attnum -EOD; - $this->getDbConnection()->setActive(true); - $command = $this->getDbConnection()->createCommand($sql); - $command->bindValue(':table', $tableName); - $command->bindValue(':schema', $schemaName); - $tableInfo = $this->createNewTableInfo($schemaName, $tableName); - $index=0; - foreach($command->query() as $col) - { - $col['index'] = $index++; - $this->processColumn($tableInfo, $col); - } - if($index===0) - throw new TDbException('dbmetadata_invalid_table_view', $table); - return $tableInfo; - } - - /** - * @param string table schema name - * @param string table name. - * @return TPgsqlTableInfo - */ - protected function createNewTableInfo($schemaName,$tableName) - { - $info['SchemaName'] = $this->assertIdentifier($schemaName); - $info['TableName'] = $this->assertIdentifier($tableName); - if($this->getIsView($schemaName,$tableName)) - $info['IsView'] = true; - list($primary, $foreign) = $this->getConstraintKeys($schemaName, $tableName); - $class = $this->getTableInfoClass(); - return new $class($info,$primary,$foreign); - } - - /** - * @param string table name, schema name or column name. - * @return string a valid identifier. - * @throws TDbException when table name contains a double quote ("). - */ - protected function assertIdentifier($name) - { - if(strpos($name, '"')!==false) - { - $ref = 'http://www.postgresql.org/docs/7.4/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS'; - throw new TDbException('dbcommon_invalid_identifier_name', $name, $ref); - } - return $name; - } - - /** - * @param string table schema name - * @param string table name. - * @return boolean true if the table is a view. - */ - protected function getIsView($schemaName,$tableName) - { - $sql = -<<getDbConnection()->setActive(true); - $command = $this->getDbConnection()->createCommand($sql); - $command->bindValue(':schema',$schemaName); - $command->bindValue(':table', $tableName); - return intval($command->queryScalar()) === 1; - } - - /** - * @param TPgsqlTableInfo table information. - * @param array column information. - */ - protected function processColumn($tableInfo, $col) - { - $columnId = $col['attname']; //use column name as column Id - - $info['ColumnName'] = '"'.$columnId.'"'; //quote the column names! - $info['ColumnId'] = $columnId; - $info['ColumnIndex'] = $col['index']; - if(!$col['attnotnull']) - $info['AllowNull'] = true; - if(in_array($columnId, $tableInfo->getPrimaryKeys())) - $info['IsPrimaryKey'] = true; - if($this->isForeignKeyColumn($columnId, $tableInfo)) - $info['IsForeignKey'] = true; - - if($col['atttypmod'] > 0) - $info['ColumnSize'] = $col['atttypmod'] - 4; - if($col['atthasdef']) - $info['DefaultValue'] = $col['adsrc']; - if($col['attisserial'] || substr($col['adsrc'],0,8) === 'nextval(') - { - if(($sequence = $this->getSequenceName($tableInfo, $col['adsrc']))!==null) - { - $info['SequenceName'] = $sequence; - unset($info['DefaultValue']); - } - } - $matches = array(); - if(preg_match('/\((\d+)(?:,(\d+))?+\)/', $col['type'], $matches)) - { - $info['DbType'] = preg_replace('/\(\d+(?:,\d+)?\)/','',$col['type']); - if($this->isPrecisionType($info['DbType'])) - { - $info['NumericPrecision'] = intval($matches[1]); - if(count($matches) > 2) - $info['NumericScale'] = intval($matches[2]); - } - else - $info['ColumnSize'] = intval($matches[1]); - } - else - $info['DbType'] = $col['type']; - - $tableInfo->Columns[$columnId] = new TPgsqlTableColumn($info); - } - - /** - * @return string serial name if found, null otherwise. - */ - protected function getSequenceName($tableInfo,$src) - { - $matches = array(); - if(preg_match('/nextval\([^\']*\'([^\']+)\'[^\)]*\)/i',$src,$matches)) - { - if(is_int(strpos($matches[1], '.'))) - return $matches[1]; - else - return $tableInfo->getSchemaName().'.'.$matches[1]; - } - } - - /** - * @return boolean true if column type if "numeric", "interval" or begins with "time". - */ - protected function isPrecisionType($type) - { - $type = strtolower(trim($type)); - return $type==='numeric' || $type==='interval' || strpos($type, 'time')===0; - } - - /** - * Gets the primary and foreign key column details for the given table. - * @param string schema name - * @param string table name. - * @return array tuple ($primary, $foreign) - */ - protected function getConstraintKeys($schemaName, $tableName) - { - $sql = -<<getDbConnection()->setActive(true); - $command = $this->getDbConnection()->createCommand($sql); - $command->bindValue(':table', $tableName); - $command->bindValue(':schema', $schemaName); - $primary = array(); - $foreign = array(); - foreach($command->query() as $row) - { - switch($row['contype']) - { - case 'p': - $primary = $this->getPrimaryKeys($tableName, $schemaName, $row['indkey']); - break; - case 'f': - if(($fkey = $this->getForeignKeys($row['consrc']))!==null) - $foreign[] = $fkey; - break; - } - } - return array($primary,$foreign); - } - - /** - * Gets the primary key field names - * @param string pgsql primary key definition - * @return array primary key field names. - */ - protected function getPrimaryKeys($tableName, $schemaName, $columnIndex) - { - $index = join(', ', explode(' ', $columnIndex)); - $sql = -<<getDbConnection()->createCommand($sql); - $command->bindValue(':table', $tableName); - $command->bindValue(':schema', $schemaName); -// $command->bindValue(':columnIndex', join(', ', explode(' ', $columnIndex))); - $primary = array(); - foreach($command->query() as $row) - { - $primary[] = $row['attname']; - } - - return $primary; - } - - /** - * Gets foreign relationship constraint keys and table name - * @param string pgsql foreign key definition - * @return array foreign relationship table name and keys, null otherwise - */ - protected function getForeignKeys($src) - { - $matches = array(); - $brackets = '\(([^\)]+)\)'; - $find = "/FOREIGN\s+KEY\s+{$brackets}\s+REFERENCES\s+([^\(]+){$brackets}/i"; - if(preg_match($find, $src, $matches)) - { - $keys = preg_split('/,\s+/', $matches[1]); - $fkeys = array(); - foreach(preg_split('/,\s+/', $matches[3]) as $i => $fkey) - $fkeys[$keys[$i]] = $fkey; - return array('table' => str_replace('"','',$matches[2]), 'keys' => $fkeys); - } - } - - /** - * @param string column name. - * @param TPgsqlTableInfo table information. - * @return boolean true if column is a foreign key. - */ - protected function isForeignKeyColumn($columnId, $tableInfo) - { - foreach($tableInfo->getForeignKeys() as $fk) - { - if(in_array($columnId, array_keys($fk['keys']))) - return true; - } - return false; - } -} - + + * @link http://www.pradosoft.com/ + * @copyright Copyright © 2005-2012 PradoSoft + * @license http://www.pradosoft.com/license/ + * @version $Id$ + * @package System.Data.Common.Pgsql + */ + +/** + * Load the base TDbMetaData class. + */ +Prado::using('System.Data.Common.TDbMetaData'); +Prado::using('System.Data.Common.Pgsql.TPgsqlTableInfo'); + +/** + * TPgsqlMetaData loads PostgreSQL database table and column information. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.Common.Pgsql + * @since 3.1 + */ +class TPgsqlMetaData extends TDbMetaData +{ + private $_defaultSchema = 'public'; + + /** + * @return string TDbTableInfo class name. + */ + protected function getTableInfoClass() + { + 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. + */ + public function setDefaultSchema($schema) + { + $this->_defaultSchema=$schema; + } + + /** + * @return string default schema. + */ + public function getDefaultSchema() + { + return $this->_defaultSchema; + } + + /** + * @param string table name with optional schema name prefix, uses default schema name prefix is not provided. + * @return array tuple as ($schemaName,$tableName) + */ + protected function getSchemaTableName($table) + { + if(count($parts= explode('.', str_replace('"','',$table))) > 1) + return array($parts[0], $parts[1]); + else + return array($this->getDefaultSchema(),$parts[0]); + } + + /** + * Get the column definitions for given table. + * @param string table name. + * @return TPgsqlTableInfo table information. + */ + protected function createTableInfo($table) + { + list($schemaName,$tableName) = $this->getSchemaTableName($table); + + // This query is made much more complex by the addition of the 'attisserial' field. + // The subquery to get that field checks to see if there is an internally dependent + // sequence on the field. + $sql = +<< 0 AND NOT a.attisdropped + ORDER BY a.attnum +EOD; + $this->getDbConnection()->setActive(true); + $command = $this->getDbConnection()->createCommand($sql); + $command->bindValue(':table', $tableName); + $command->bindValue(':schema', $schemaName); + $tableInfo = $this->createNewTableInfo($schemaName, $tableName); + $index=0; + foreach($command->query() as $col) + { + $col['index'] = $index++; + $this->processColumn($tableInfo, $col); + } + if($index===0) + throw new TDbException('dbmetadata_invalid_table_view', $table); + return $tableInfo; + } + + /** + * @param string table schema name + * @param string table name. + * @return TPgsqlTableInfo + */ + protected function createNewTableInfo($schemaName,$tableName) + { + $info['SchemaName'] = $this->assertIdentifier($schemaName); + $info['TableName'] = $this->assertIdentifier($tableName); + if($this->getIsView($schemaName,$tableName)) + $info['IsView'] = true; + list($primary, $foreign) = $this->getConstraintKeys($schemaName, $tableName); + $class = $this->getTableInfoClass(); + return new $class($info,$primary,$foreign); + } + + /** + * @param string table name, schema name or column name. + * @return string a valid identifier. + * @throws TDbException when table name contains a double quote ("). + */ + protected function assertIdentifier($name) + { + if(strpos($name, '"')!==false) + { + $ref = 'http://www.postgresql.org/docs/7.4/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS'; + throw new TDbException('dbcommon_invalid_identifier_name', $name, $ref); + } + return $name; + } + + /** + * @param string table schema name + * @param string table name. + * @return boolean true if the table is a view. + */ + protected function getIsView($schemaName,$tableName) + { + $sql = +<<getDbConnection()->setActive(true); + $command = $this->getDbConnection()->createCommand($sql); + $command->bindValue(':schema',$schemaName); + $command->bindValue(':table', $tableName); + return intval($command->queryScalar()) === 1; + } + + /** + * @param TPgsqlTableInfo table information. + * @param array column information. + */ + protected function processColumn($tableInfo, $col) + { + $columnId = $col['attname']; //use column name as column Id + + $info['ColumnName'] = '"'.$columnId.'"'; //quote the column names! + $info['ColumnId'] = $columnId; + $info['ColumnIndex'] = $col['index']; + if(!$col['attnotnull']) + $info['AllowNull'] = true; + if(in_array($columnId, $tableInfo->getPrimaryKeys())) + $info['IsPrimaryKey'] = true; + if($this->isForeignKeyColumn($columnId, $tableInfo)) + $info['IsForeignKey'] = true; + + if($col['atttypmod'] > 0) + $info['ColumnSize'] = $col['atttypmod'] - 4; + if($col['atthasdef']) + $info['DefaultValue'] = $col['adsrc']; + if($col['attisserial'] || substr($col['adsrc'],0,8) === 'nextval(') + { + if(($sequence = $this->getSequenceName($tableInfo, $col['adsrc']))!==null) + { + $info['SequenceName'] = $sequence; + unset($info['DefaultValue']); + } + } + $matches = array(); + if(preg_match('/\((\d+)(?:,(\d+))?+\)/', $col['type'], $matches)) + { + $info['DbType'] = preg_replace('/\(\d+(?:,\d+)?\)/','',$col['type']); + if($this->isPrecisionType($info['DbType'])) + { + $info['NumericPrecision'] = intval($matches[1]); + if(count($matches) > 2) + $info['NumericScale'] = intval($matches[2]); + } + else + $info['ColumnSize'] = intval($matches[1]); + } + else + $info['DbType'] = $col['type']; + + $tableInfo->Columns[$columnId] = new TPgsqlTableColumn($info); + } + + /** + * @return string serial name if found, null otherwise. + */ + protected function getSequenceName($tableInfo,$src) + { + $matches = array(); + if(preg_match('/nextval\([^\']*\'([^\']+)\'[^\)]*\)/i',$src,$matches)) + { + if(is_int(strpos($matches[1], '.'))) + return $matches[1]; + else + return $tableInfo->getSchemaName().'.'.$matches[1]; + } + } + + /** + * @return boolean true if column type if "numeric", "interval" or begins with "time". + */ + protected function isPrecisionType($type) + { + $type = strtolower(trim($type)); + return $type==='numeric' || $type==='interval' || strpos($type, 'time')===0; + } + + /** + * Gets the primary and foreign key column details for the given table. + * @param string schema name + * @param string table name. + * @return array tuple ($primary, $foreign) + */ + protected function getConstraintKeys($schemaName, $tableName) + { + $sql = +<<getDbConnection()->setActive(true); + $command = $this->getDbConnection()->createCommand($sql); + $command->bindValue(':table', $tableName); + $command->bindValue(':schema', $schemaName); + $primary = array(); + $foreign = array(); + foreach($command->query() as $row) + { + switch($row['contype']) + { + case 'p': + $primary = $this->getPrimaryKeys($tableName, $schemaName, $row['indkey']); + break; + case 'f': + if(($fkey = $this->getForeignKeys($row['consrc']))!==null) + $foreign[] = $fkey; + break; + } + } + return array($primary,$foreign); + } + + /** + * Gets the primary key field names + * @param string pgsql primary key definition + * @return array primary key field names. + */ + protected function getPrimaryKeys($tableName, $schemaName, $columnIndex) + { + $index = join(', ', explode(' ', $columnIndex)); + $sql = +<<getDbConnection()->createCommand($sql); + $command->bindValue(':table', $tableName); + $command->bindValue(':schema', $schemaName); +// $command->bindValue(':columnIndex', join(', ', explode(' ', $columnIndex))); + $primary = array(); + foreach($command->query() as $row) + { + $primary[] = $row['attname']; + } + + return $primary; + } + + /** + * Gets foreign relationship constraint keys and table name + * @param string pgsql foreign key definition + * @return array foreign relationship table name and keys, null otherwise + */ + protected function getForeignKeys($src) + { + $matches = array(); + $brackets = '\(([^\)]+)\)'; + $find = "/FOREIGN\s+KEY\s+{$brackets}\s+REFERENCES\s+([^\(]+){$brackets}/i"; + if(preg_match($find, $src, $matches)) + { + $keys = preg_split('/,\s+/', $matches[1]); + $fkeys = array(); + foreach(preg_split('/,\s+/', $matches[3]) as $i => $fkey) + $fkeys[$keys[$i]] = $fkey; + return array('table' => str_replace('"','',$matches[2]), 'keys' => $fkeys); + } + } + + /** + * @param string column name. + * @param TPgsqlTableInfo table information. + * @return boolean true if column is a foreign key. + */ + protected function isForeignKeyColumn($columnId, $tableInfo) + { + foreach($tableInfo->getForeignKeys() as $fk) + { + if(in_array($columnId, array_keys($fk['keys']))) + return true; + } + return false; + } +} + diff --git a/framework/Data/Common/Pgsql/TPgsqlTableColumn.php b/framework/Data/Common/Pgsql/TPgsqlTableColumn.php index 46eab9c1..fe8ff499 100644 --- a/framework/Data/Common/Pgsql/TPgsqlTableColumn.php +++ b/framework/Data/Common/Pgsql/TPgsqlTableColumn.php @@ -1,49 +1,49 @@ - * @link http://www.pradosoft.com/ +/** + * TPgsqlTableColumn class file. + * + * @author Wei Zhuo + * @link http://www.pradosoft.com/ * @copyright Copyright © 2005-2012 PradoSoft - * @license http://www.pradosoft.com/license/ - * @version $Id$ - * @package System.Data.Common.Pgsql - */ - -/** - * Load common TDbTableCommon class. - */ -Prado::using('System.Data.Common.TDbTableColumn'); - -/** - * Describes the column metadata of the schema for a PostgreSQL database table. - * - * @author Wei Zhuo - * @version $Id$ - * @package System.Data.Common.Pgsql - * @since 3.1 - */ -class TPgsqlTableColumn extends TDbTableColumn -{ - private static $types=array( - 'integer' => array('bit', 'bit varying', 'real', 'serial', 'int', 'integer'), - 'boolean' => array('boolean'), - 'float' => array('bigint', 'bigserial', 'double precision', 'money', 'numeric') - ); - - /** - * Overrides parent implementation, returns PHP type from the db type. - * @return boolean derived PHP primitive type from the column db type. - */ - public function getPHPType() - { - $dbtype = strtolower($this->getDbType()); - foreach(self::$types as $type => $dbtypes) - { - if(in_array($dbtype, $dbtypes)) - return $type; - } - return 'string'; - } -} + * @license http://www.pradosoft.com/license/ + * @version $Id$ + * @package System.Data.Common.Pgsql + */ + +/** + * Load common TDbTableCommon class. + */ +Prado::using('System.Data.Common.TDbTableColumn'); + +/** + * Describes the column metadata of the schema for a PostgreSQL database table. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.Common.Pgsql + * @since 3.1 + */ +class TPgsqlTableColumn extends TDbTableColumn +{ + private static $types=array( + 'integer' => array('bit', 'bit varying', 'real', 'serial', 'int', 'integer'), + 'boolean' => array('boolean'), + 'float' => array('bigint', 'bigserial', 'double precision', 'money', 'numeric') + ); + + /** + * Overrides parent implementation, returns PHP type from the db type. + * @return boolean derived PHP primitive type from the column db type. + */ + public function getPHPType() + { + $dbtype = strtolower($this->getDbType()); + foreach(self::$types as $type => $dbtypes) + { + if(in_array($dbtype, $dbtypes)) + return $type; + } + return 'string'; + } +} diff --git a/framework/Data/Common/Pgsql/TPgsqlTableInfo.php b/framework/Data/Common/Pgsql/TPgsqlTableInfo.php index 11e5a613..6ad941d7 100644 --- a/framework/Data/Common/Pgsql/TPgsqlTableInfo.php +++ b/framework/Data/Common/Pgsql/TPgsqlTableInfo.php @@ -1,58 +1,58 @@ - * @link http://www.pradosoft.com/ +/** + * TPgsqlTableInfo class file. + * + * @author Wei Zhuo + * @link http://www.pradosoft.com/ * @copyright Copyright © 2005-2012 PradoSoft - * @license http://www.pradosoft.com/license/ - * @version $Id$ - * @package System.Data.Common.Pgsql - */ - -/** - * Loads the base TDbTableInfo class and TPgsqlTableColumn class. - */ -Prado::using('System.Data.Common.TDbTableInfo'); -Prado::using('System.Data.Common.Pgsql.TPgsqlTableColumn'); - -/** - * TPgsqlTableInfo class provides additional table information for PostgreSQL database. - * - * @author Wei Zhuo - * @version $Id$ - * @package System.Data.Common.Pgsql - * @since 3.1 - */ -class TPgsqlTableInfo extends TDbTableInfo -{ - /** - * @return string name of the schema this column belongs to. - */ - public function getSchemaName() - { - return $this->getInfo('SchemaName'); - } - - /** - * @return string full name of the table, database dependent. - */ - public function getTableFullName() - { - if(($schema=$this->getSchemaName())!==null) - return $schema.'.'.$this->getTableName(); - else - return $this->getTableName(); - } - - /** - * @param TDbConnection database connection. - * @return TDbCommandBuilder new command builder - */ - public function createCommandBuilder($connection) - { - Prado::using('System.Data.Common.Pgsql.TPgsqlCommandBuilder'); - return new TPgsqlCommandBuilder($connection,$this); - } -} + * @license http://www.pradosoft.com/license/ + * @version $Id$ + * @package System.Data.Common.Pgsql + */ + +/** + * Loads the base TDbTableInfo class and TPgsqlTableColumn class. + */ +Prado::using('System.Data.Common.TDbTableInfo'); +Prado::using('System.Data.Common.Pgsql.TPgsqlTableColumn'); + +/** + * TPgsqlTableInfo class provides additional table information for PostgreSQL database. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.Common.Pgsql + * @since 3.1 + */ +class TPgsqlTableInfo extends TDbTableInfo +{ + /** + * @return string name of the schema this column belongs to. + */ + public function getSchemaName() + { + return $this->getInfo('SchemaName'); + } + + /** + * @return string full name of the table, database dependent. + */ + public function getTableFullName() + { + if(($schema=$this->getSchemaName())!==null) + return $schema.'.'.$this->getTableName(); + else + return $this->getTableName(); + } + + /** + * @param TDbConnection database connection. + * @return TDbCommandBuilder new command builder + */ + public function createCommandBuilder($connection) + { + Prado::using('System.Data.Common.Pgsql.TPgsqlCommandBuilder'); + return new TPgsqlCommandBuilder($connection,$this); + } +} diff --git a/framework/Data/Common/Sqlite/TSqliteCommandBuilder.php b/framework/Data/Common/Sqlite/TSqliteCommandBuilder.php index d162ed60..d7d82812 100644 --- a/framework/Data/Common/Sqlite/TSqliteCommandBuilder.php +++ b/framework/Data/Common/Sqlite/TSqliteCommandBuilder.php @@ -1,47 +1,47 @@ - - * @link http://www.pradosoft.com/ + + * @link http://www.pradosoft.com/ * @copyright Copyright © 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'); - -/** - * TSqliteCommandBuilder provides specifics methods to create limit/offset query commands - * for Sqlite database. - * - * @author Wei Zhuo - * @version $Id: TDbCommandBuilder.php 1863 2007-04-12 12:43:49Z wei $ - * @package System.Data.Common - * @since 3.1 - */ -class TSqliteCommandBuilder extends TDbCommandBuilder -{ - /** - * Alters the sql to apply $limit and $offset. - * @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) - { - $limitStr = ' LIMIT '.$limit; - $offsetStr = $offset >= 0 ? ' OFFSET '.$offset : ''; - return $sql.$limitStr.$offsetStr; - } - else - return $sql; - } -} - + * @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'); + +/** + * TSqliteCommandBuilder provides specifics methods to create limit/offset query commands + * for Sqlite database. + * + * @author Wei Zhuo + * @version $Id: TDbCommandBuilder.php 1863 2007-04-12 12:43:49Z wei $ + * @package System.Data.Common + * @since 3.1 + */ +class TSqliteCommandBuilder extends TDbCommandBuilder +{ + /** + * Alters the sql to apply $limit and $offset. + * @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) + { + $limitStr = ' LIMIT '.$limit; + $offsetStr = $offset >= 0 ? ' OFFSET '.$offset : ''; + return $sql.$limitStr.$offsetStr; + } + else + return $sql; + } +} + diff --git a/framework/Data/Common/Sqlite/TSqliteMetaData.php b/framework/Data/Common/Sqlite/TSqliteMetaData.php index 5acfad9d..d101d179 100644 --- a/framework/Data/Common/Sqlite/TSqliteMetaData.php +++ b/framework/Data/Common/Sqlite/TSqliteMetaData.php @@ -1,210 +1,210 @@ - - * @link http://www.pradosoft.com/ - * @copyright Copyright © 2005-2012 PradoSoft - * @license http://www.pradosoft.com/license/ - * @version $Id: TSqliteMetaData.php 1861 2007-04-12 08:05:03Z wei $ - * @package System.Data.Common.Sqlite - */ - -/** - * Load the base TDbMetaData class. - */ -Prado::using('System.Data.Common.TDbMetaData'); -Prado::using('System.Data.Common.Sqlite.TSqliteTableInfo'); - -/** - * TSqliteMetaData loads SQLite database table and column information. - * - * @author Wei Zhuo - * @version $Id: TSqliteMetaData.php 1861 2007-04-12 08:05:03Z wei $ - * @package System.Data.Commom.Sqlite - * @since 3.1 - */ -class TSqliteMetaData extends TDbMetaData -{ - /** - * @return string TDbTableInfo class name. - */ - protected function getTableInfoClass() - { - 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. - * @return TPgsqlTableInfo table information. - */ - protected function createTableInfo($tableName) - { - $tableName = str_replace("'",'',$tableName); - $this->getDbConnection()->setActive(true); - $table = $this->getDbConnection()->quoteString($tableName); - $sql = "PRAGMA table_info({$table})"; - $command = $this->getDbConnection()->createCommand($sql); - $foreign = $this->getForeignKeys($table); - $index=0; - $columns=array(); - $primary=array(); - foreach($command->query() as $col) - { - $col['index'] = $index++; - $column = $this->processColumn($col, $foreign); - $columns[$col['name']] = $column; - if($column->getIsPrimaryKey()) - $primary[] = $col['name']; - } - $info['TableName'] = $tableName; - if($this->getIsView($tableName)) - $info['IsView'] = true; - if(count($columns)===0) - throw new TDbException('dbmetadata_invalid_table_view', $tableName); - $class = $this->getTableInfoClass(); - $tableInfo = new $class($info,$primary,$foreign); - $tableInfo->getColumns()->copyFrom($columns); - return $tableInfo; - } - - /** - * @param string table name. - * @return boolean true if the table is a view. - */ - protected function getIsView($tableName) - { - $sql = 'SELECT count(*) FROM sqlite_master WHERE type="view" AND name= :table'; - $this->getDbConnection()->setActive(true); - $command = $this->getDbConnection()->createCommand($sql); - $command->bindValue(':table', $tableName); - return intval($command->queryScalar()) === 1; - } - - /** - * @param array column information. - * @param array foreign key details. - * @return TSqliteTableColumn column details. - */ - protected function processColumn($col, $foreign) - { - $columnId = $col['name']; //use column name as column Id - - $info['ColumnName'] = '"'.$columnId.'"'; //quote the column names! - $info['ColumnId'] = $columnId; - $info['ColumnIndex'] = $col['index']; - - if($col['notnull']!=='99') - $info['AllowNull'] = true; - - if($col['pk']==='1') - $info['IsPrimaryKey'] = true; - if($this->isForeignKeyColumn($columnId, $foreign)) - $info['IsForeignKey'] = true; - - if($col['dflt_value']!==null) - $info['DefaultValue'] = $col['dflt_value']; - - $type = strtolower($col['type']); - $info['AutoIncrement'] = $type==='integer' && $col['pk']==='1'; - - $info['DbType'] = $type; - $match=array(); - if(is_int($pos=strpos($type, '(')) && preg_match('/\((.*)\)/', $type, $match)) - { - $ps = explode(',', $match[1]); - if(count($ps)===2) - { - $info['NumericPrecision'] = intval($ps[0]); - $info['NumericScale'] = intval($ps[1]); - } - else - $info['ColumnSize']=intval($match[1]); - $info['DbType'] = substr($type,0,$pos); - } - - return new TSqliteTableColumn($info); - } - - /** - * - * - * @param string quoted table name. - * @return array foreign key details. - */ - protected function getForeignKeys($table) - { - $sql = "PRAGMA foreign_key_list({$table})"; - $command = $this->getDbConnection()->createCommand($sql); - $fkeys = array(); - foreach($command->query() as $col) - { - $fkeys[$col['table']]['keys'][$col['from']] = $col['to']; - $fkeys[$col['table']]['table'] = $col['table']; - } - return count($fkeys) > 0 ? array_values($fkeys) : $fkeys; - } - - /** - * @param string column name. - * @param array foreign key column names. - * @return boolean true if column is a foreign key. - */ - protected function isForeignKeyColumn($columnId, $foreign) - { - foreach($foreign as $fk) - { - if(in_array($columnId, array_keys($fk['keys']))) - return true; - } - return false; - } -} - -/** - -CREATE TABLE foo -( - id INTEGER NOT NULL PRIMARY KEY, - id2 CHAR(2) -); - -CREATE TABLE bar -( - id INTEGER NOT NULL PRIMARY KEY, - foo_id INTEGER - CONSTRAINT fk_foo_id REFERENCES foo(id) ON DELETE CASCADE -); -*/ - + + * @link http://www.pradosoft.com/ + * @copyright Copyright © 2005-2012 PradoSoft + * @license http://www.pradosoft.com/license/ + * @version $Id: TSqliteMetaData.php 1861 2007-04-12 08:05:03Z wei $ + * @package System.Data.Common.Sqlite + */ + +/** + * Load the base TDbMetaData class. + */ +Prado::using('System.Data.Common.TDbMetaData'); +Prado::using('System.Data.Common.Sqlite.TSqliteTableInfo'); + +/** + * TSqliteMetaData loads SQLite database table and column information. + * + * @author Wei Zhuo + * @version $Id: TSqliteMetaData.php 1861 2007-04-12 08:05:03Z wei $ + * @package System.Data.Commom.Sqlite + * @since 3.1 + */ +class TSqliteMetaData extends TDbMetaData +{ + /** + * @return string TDbTableInfo class name. + */ + protected function getTableInfoClass() + { + 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. + * @return TPgsqlTableInfo table information. + */ + protected function createTableInfo($tableName) + { + $tableName = str_replace("'",'',$tableName); + $this->getDbConnection()->setActive(true); + $table = $this->getDbConnection()->quoteString($tableName); + $sql = "PRAGMA table_info({$table})"; + $command = $this->getDbConnection()->createCommand($sql); + $foreign = $this->getForeignKeys($table); + $index=0; + $columns=array(); + $primary=array(); + foreach($command->query() as $col) + { + $col['index'] = $index++; + $column = $this->processColumn($col, $foreign); + $columns[$col['name']] = $column; + if($column->getIsPrimaryKey()) + $primary[] = $col['name']; + } + $info['TableName'] = $tableName; + if($this->getIsView($tableName)) + $info['IsView'] = true; + if(count($columns)===0) + throw new TDbException('dbmetadata_invalid_table_view', $tableName); + $class = $this->getTableInfoClass(); + $tableInfo = new $class($info,$primary,$foreign); + $tableInfo->getColumns()->copyFrom($columns); + return $tableInfo; + } + + /** + * @param string table name. + * @return boolean true if the table is a view. + */ + protected function getIsView($tableName) + { + $sql = 'SELECT count(*) FROM sqlite_master WHERE type="view" AND name= :table'; + $this->getDbConnection()->setActive(true); + $command = $this->getDbConnection()->createCommand($sql); + $command->bindValue(':table', $tableName); + return intval($command->queryScalar()) === 1; + } + + /** + * @param array column information. + * @param array foreign key details. + * @return TSqliteTableColumn column details. + */ + protected function processColumn($col, $foreign) + { + $columnId = $col['name']; //use column name as column Id + + $info['ColumnName'] = '"'.$columnId.'"'; //quote the column names! + $info['ColumnId'] = $columnId; + $info['ColumnIndex'] = $col['index']; + + if($col['notnull']!=='99') + $info['AllowNull'] = true; + + if($col['pk']==='1') + $info['IsPrimaryKey'] = true; + if($this->isForeignKeyColumn($columnId, $foreign)) + $info['IsForeignKey'] = true; + + if($col['dflt_value']!==null) + $info['DefaultValue'] = $col['dflt_value']; + + $type = strtolower($col['type']); + $info['AutoIncrement'] = $type==='integer' && $col['pk']==='1'; + + $info['DbType'] = $type; + $match=array(); + if(is_int($pos=strpos($type, '(')) && preg_match('/\((.*)\)/', $type, $match)) + { + $ps = explode(',', $match[1]); + if(count($ps)===2) + { + $info['NumericPrecision'] = intval($ps[0]); + $info['NumericScale'] = intval($ps[1]); + } + else + $info['ColumnSize']=intval($match[1]); + $info['DbType'] = substr($type,0,$pos); + } + + return new TSqliteTableColumn($info); + } + + /** + * + * + * @param string quoted table name. + * @return array foreign key details. + */ + protected function getForeignKeys($table) + { + $sql = "PRAGMA foreign_key_list({$table})"; + $command = $this->getDbConnection()->createCommand($sql); + $fkeys = array(); + foreach($command->query() as $col) + { + $fkeys[$col['table']]['keys'][$col['from']] = $col['to']; + $fkeys[$col['table']]['table'] = $col['table']; + } + return count($fkeys) > 0 ? array_values($fkeys) : $fkeys; + } + + /** + * @param string column name. + * @param array foreign key column names. + * @return boolean true if column is a foreign key. + */ + protected function isForeignKeyColumn($columnId, $foreign) + { + foreach($foreign as $fk) + { + if(in_array($columnId, array_keys($fk['keys']))) + return true; + } + return false; + } +} + +/** + +CREATE TABLE foo +( + id INTEGER NOT NULL PRIMARY KEY, + id2 CHAR(2) +); + +CREATE TABLE bar +( + id INTEGER NOT NULL PRIMARY KEY, + foo_id INTEGER + CONSTRAINT fk_foo_id REFERENCES foo(id) ON DELETE CASCADE +); +*/ + diff --git a/framework/Data/Common/Sqlite/TSqliteTableColumn.php b/framework/Data/Common/Sqlite/TSqliteTableColumn.php index 915debbe..6783e1ad 100644 --- a/framework/Data/Common/Sqlite/TSqliteTableColumn.php +++ b/framework/Data/Common/Sqlite/TSqliteTableColumn.php @@ -1,64 +1,64 @@ - - * @link http://www.pradosoft.com/ + + * @link http://www.pradosoft.com/ * @copyright Copyright © 2005-2012 PradoSoft - * @license http://www.pradosoft.com/license/ - * @version $Id: TSqliteTableColumn.php 1861 2007-04-12 08:05:03Z wei $ - * @package System.Data.Common.Sqlite - */ - -/** - * Load common TDbTableCommon class. - */ -Prado::using('System.Data.Common.TDbTableColumn'); - -/** - * Describes the column metadata of the schema for a PostgreSQL database table. - * - * @author Wei Zhuo - * @version $Id: TSqliteTableColumn.php 1861 2007-04-12 08:05:03Z wei $ - * @package System.Data.Common.Sqlite - * @since 3.1 - */ -class TSqliteTableColumn extends TDbTableColumn -{ - /** - * @TODO add sqlite types. - */ - 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() - { - $dbtype = strtolower($this->getDbType()); - foreach(self::$types as $type => $dbtypes) - { - if(in_array($dbtype, $dbtypes)) - return $type; - } - return 'string'; - } - - /** - * @return boolean true if column will auto-increment when the column value is inserted as null. - */ - public function getAutoIncrement() - { - return $this->getInfo('AutoIncrement', false); - } - - /** - * @return boolean true if auto increment is true. - */ - public function hasSequence() - { - return $this->getAutoIncrement(); - } -} - + * @license http://www.pradosoft.com/license/ + * @version $Id: TSqliteTableColumn.php 1861 2007-04-12 08:05:03Z wei $ + * @package System.Data.Common.Sqlite + */ + +/** + * Load common TDbTableCommon class. + */ +Prado::using('System.Data.Common.TDbTableColumn'); + +/** + * Describes the column metadata of the schema for a PostgreSQL database table. + * + * @author Wei Zhuo + * @version $Id: TSqliteTableColumn.php 1861 2007-04-12 08:05:03Z wei $ + * @package System.Data.Common.Sqlite + * @since 3.1 + */ +class TSqliteTableColumn extends TDbTableColumn +{ + /** + * @TODO add sqlite types. + */ + 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() + { + $dbtype = strtolower($this->getDbType()); + foreach(self::$types as $type => $dbtypes) + { + if(in_array($dbtype, $dbtypes)) + return $type; + } + return 'string'; + } + + /** + * @return boolean true if column will auto-increment when the column value is inserted as null. + */ + public function getAutoIncrement() + { + return $this->getInfo('AutoIncrement', false); + } + + /** + * @return boolean true if auto increment is true. + */ + public function hasSequence() + { + return $this->getAutoIncrement(); + } +} + diff --git a/framework/Data/Common/Sqlite/TSqliteTableInfo.php b/framework/Data/Common/Sqlite/TSqliteTableInfo.php index fe9ab846..420dce85 100644 --- a/framework/Data/Common/Sqlite/TSqliteTableInfo.php +++ b/framework/Data/Common/Sqlite/TSqliteTableInfo.php @@ -1,47 +1,47 @@ - - * @link http://www.pradosoft.com/ + + * @link http://www.pradosoft.com/ * @copyright Copyright © 2005-2012 PradoSoft - * @license http://www.pradosoft.com/license/ - * @version $Id: TSqliteTableInfo.php 1861 2007-04-12 08:05:03Z wei $ - * @package System.Data.Common.Sqlite - */ - -/** - * Loads the base TDbTableInfo class and TSqliteTableColumn class. - */ -Prado::using('System.Data.Common.TDbTableInfo'); -Prado::using('System.Data.Common.Sqlite.TSqliteTableColumn'); - -/** - * TSqliteTableInfo class provides additional table information for PostgreSQL database. - * - * @author Wei Zhuo - * @version $Id: TSqliteTableInfo.php 1861 2007-04-12 08:05:03Z wei $ - * @package System.Data.Common.Sqlite - * @since 3.1 - */ -class TSqliteTableInfo extends TDbTableInfo -{ - /** - * @param TDbConnection database connection. - * @return TDbCommandBuilder new command builder - */ - public function createCommandBuilder($connection) - { - Prado::using('System.Data.Common.Sqlite.TSqliteCommandBuilder'); - return new TSqliteCommandBuilder($connection,$this); - } - - /** - * @return string full name of the table, database dependent. - */ - public function getTableFullName() - { - return "'".$this->getTableName()."'"; - } -} - + * @license http://www.pradosoft.com/license/ + * @version $Id: TSqliteTableInfo.php 1861 2007-04-12 08:05:03Z wei $ + * @package System.Data.Common.Sqlite + */ + +/** + * Loads the base TDbTableInfo class and TSqliteTableColumn class. + */ +Prado::using('System.Data.Common.TDbTableInfo'); +Prado::using('System.Data.Common.Sqlite.TSqliteTableColumn'); + +/** + * TSqliteTableInfo class provides additional table information for PostgreSQL database. + * + * @author Wei Zhuo + * @version $Id: TSqliteTableInfo.php 1861 2007-04-12 08:05:03Z wei $ + * @package System.Data.Common.Sqlite + * @since 3.1 + */ +class TSqliteTableInfo extends TDbTableInfo +{ + /** + * @param TDbConnection database connection. + * @return TDbCommandBuilder new command builder + */ + public function createCommandBuilder($connection) + { + Prado::using('System.Data.Common.Sqlite.TSqliteCommandBuilder'); + return new TSqliteCommandBuilder($connection,$this); + } + + /** + * @return string full name of the table, database dependent. + */ + public function getTableFullName() + { + return "'".$this->getTableName()."'"; + } +} + diff --git a/framework/Data/Common/TDbCommandBuilder.php b/framework/Data/Common/TDbCommandBuilder.php index 212e5f02..ebdede99 100644 --- a/framework/Data/Common/TDbCommandBuilder.php +++ b/framework/Data/Common/TDbCommandBuilder.php @@ -1,507 +1,507 @@ - - * @link http://www.pradosoft.com/ - * @copyright Copyright © 2005-2012 PradoSoft - * @license http://www.pradosoft.com/license/ - * @version $Id$ - * @package System.Data.Common - */ - -/** - * TDbCommandBuilder provides basic methods to create query commands for tables - * giving by {@link setTableInfo TableInfo} the property. - * - * @author Wei Zhuo - * @version $Id$ - * @package System.Data.Common - * @since 3.1 - */ -class TDbCommandBuilder extends TComponent -{ - private $_connection; - private $_tableInfo; - - /** - * @param TDbConnection database connection. - * @param TDbTableInfo table information. - */ - public function __construct($connection=null, $tableInfo=null) - { - $this->setDbConnection($connection); - $this->setTableInfo($tableInfo); - } - - /** - * @return TDbConnection database connection. - */ - public function getDbConnection() - { - return $this->_connection; - } - - /** - * @param TDbConnection database connection. - */ - public function setDbConnection($value) - { - $this->_connection=$value; - } - - /** - * @param TDbTableInfo table information. - */ - public function setTableInfo($value) - { - $this->_tableInfo=$value; - } - - /** - * @param TDbTableInfo table information. - */ - public function getTableInfo() - { - return $this->_tableInfo; - } - - /** - * Iterate through all the columns and returns the last insert id of the - * first column that has a sequence or serial. - * @return mixed last insert id, null if none is found. - */ - public function getLastInsertID() - { - foreach($this->getTableInfo()->getColumns() as $column) - { - if($column->hasSequence()) - return $this->getDbConnection()->getLastInsertID($column->getSequenceName()); - } - } - - /** - * Alters the sql to apply $limit and $offset. Default implementation is applicable - * for PostgreSQL, MySQL and SQLite. - * @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 ? (int)$limit : -1; - $offset = $offset!==null ? (int)$offset : -1; - $limitStr = $limit >= 0 ? ' LIMIT '.$limit : ''; - $offsetStr = $offset >= 0 ? ' OFFSET '.$offset : ''; - return $sql.$limitStr.$offsetStr; - } - - /** - * @param string SQL string without existing ordering. - * @param array pairs of column names as key and direction as value. - * @return string modified SQL applied with ORDER BY. - */ - public function applyOrdering($sql, $ordering) - { - $orders=array(); - foreach($ordering as $name => $direction) - { - $direction = strtolower($direction) == 'desc' ? 'DESC' : 'ASC'; - if(false !== strpos($name, '(') && false !== strpos($name, ')')) { - // key is a function (bad practice, but we need to handle it) - $key = $name; - } else { - // key is a column - $key = $this->getTableInfo()->getColumn($name)->getColumnName(); - } - $orders[] = $key.' '.$direction; - } - if(count($orders) > 0) - $sql .= ' ORDER BY '.implode(', ', $orders); - return $sql; - } - - /** - * Computes the SQL condition for search a set of column using regular expression - * (or LIKE, depending on database implementation) to match a string of - * keywords (default matches all keywords). - * @param array list of column id for potential search condition. - * @param string string of keywords - * @return string SQL search condition matching on a set of columns. - */ - public function getSearchExpression($fields, $keywords) - { - if(strlen(trim($keywords)) == 0) return ''; - $words = preg_split('/\s/u', $keywords); - $conditions = array(); - foreach($fields as $field) - { - $column = $this->getTableInfo()->getColumn($field)->getColumnName(); - $conditions[] = $this->getSearchCondition($column, $words); - } - return '('.implode(' OR ', $conditions).')'; - } - - /** - * @param string column name. - * @param array keywords - * @return string search condition for all words in one column. - */ - protected function getSearchCondition($column, $words) - { - $conditions=array(); - foreach($words as $word) - $conditions[] = $column.' LIKE '.$this->getDbConnection()->quoteString('%'.$word.'%'); - return '('.implode(' AND ', $conditions).')'; - } - - /** - * - * Different behavior depends on type of passed data - * string - * usage without modification - * - * null - * will be expanded to full list of quoted table column names (quoting depends on database) - * - * array - * - Column names will be quoted if used as key or value of array - * - * array('col1', 'col2', 'col2') - * // SELECT `col1`, `col2`, `col3` FROM... - * - * - * - Column aliasing - * - * array('mycol1' => 'col1', 'mycol2' => 'COUNT(*)') - * // SELECT `col1` AS mycol1, COUNT(*) AS mycol2 FROM... - * - * - * - NULL and scalar values (strings will be quoted depending on database) - * - * array('col1' => 'my custom string', 'col2' => 1.0, 'col3' => 'NULL') - * // SELECT "my custom string" AS `col1`, 1.0 AS `col2`, NULL AS `col3` FROM... - * - * - * - If the *-wildcard char is used as key or value, add the full list of quoted table column names - * - * array('col1' => 'NULL', '*') - * // SELECT `col1`, `col2`, `col3`, NULL AS `col1` FROM... - * - * @param mixed $value - * @return array of generated fields - use implode(', ', $selectfieldlist) to collapse field list for usage - * @since 3.1.7 - * @todo add support for table aliasing - * @todo add support for quoting of column aliasing - */ - public function getSelectFieldList($data='*') { - if(is_scalar($data)) { - $tmp = explode(',', $data); - $result = array(); - foreach($tmp as $v) - $result[] = trim($v); - return $result; - } - - $bHasWildcard = false; - $result = array(); - if(is_array($data) || $data instanceof Traversable) { - $columns = $this->getTableInfo()->getColumns(); - foreach($data as $key=>$value) { - if($key==='*' || $value==='*') { - $bHasWildcard = true; - continue; - } - - if(strToUpper($key)==='NULL') { - $result[] = 'NULL'; - continue; - } - - if(strpos($key, '(')!==false && strpos($key, ')')!==false) { - $result[] = $key; - continue; - } - - if(stripos($key, 'AS')!==false) { - $result[] = $key; - continue; - } - - if(stripos($value, 'AS')!==false) { - $result[] = $value; - continue; - } - - $v = isset($columns[$value]); - $k = isset($columns[$key]); - if(is_integer($key) && $v) { - $key = $value; - $k = $v; - } - - if(strToUpper($value)==='NULL') { - if($k) - $result[] = 'NULL AS ' . $columns[$key]->getColumnName(); - else - $result[] = 'NULL' . (is_string($key) ? (' AS ' . (string)$key) : ''); - continue; - } - - if(strpos($value, '(')!==false && strpos($value, ')')!==false) { - if($k) - $result[] = $value . ' AS ' . $columns[$key]->getColumnName(); - else - $result[] = $value . (is_string($key) ? (' AS ' . (string)$key) : ''); - continue; - } - - if($v && $key==$value) { - $result[] = $columns[$value]->getColumnName(); - continue; - } - - if($k && $value==null) { - $result[] = $columns[$key]->getColumnName(); - continue; - } - - if(is_string($key) && $v) { - $result[] = $columns[$value]->getColumnName() . ' AS ' . $key; - continue; - } - - if(is_numeric($value) && $k) { - $result[] = $value . ' AS ' . $columns[$key]->getColumnName(); - continue; - } - - if(is_string($value) && $k) { - $result[] = $this->getDbConnection()->quoteString($value) . ' AS ' . $columns[$key]->getColumnName(); - continue; - } - - if(!$v && !$k && is_integer($key)) { - $result[] = is_numeric($value) ? $value : $this->getDbConnection()->quoteString((string)$value); - continue; - } - - $result[] = (is_numeric($value) ? $value : $this->getDbConnection()->quoteString((string)$value)) . ' AS ' . $key; - } - } - - if($data===null || count($result) == 0 || $bHasWildcard) - $result = $result = array_merge($this->getTableInfo()->getColumnNames(), $result); - - return $result; - } - - /** - * Appends the $where condition to the string "SELECT * FROM tableName WHERE ". - * The tableName is obtained from the {@link setTableInfo TableInfo} property. - * @param string query condition - * @param array condition parameters. - * @return TDbCommand query command. - */ - public function createFindCommand($where='1=1', $parameters=array(), $ordering=array(), $limit=-1, $offset=-1, $select='*') - { - $table = $this->getTableInfo()->getTableFullName(); - $fields = implode(', ', $this -> getSelectFieldList($select)); - $sql = "SELECT {$fields} FROM {$table}"; - if(!empty($where)) - $sql .= " WHERE {$where}"; - return $this->applyCriterias($sql, $parameters, $ordering, $limit, $offset); - } - - public function applyCriterias($sql, $parameters=array(),$ordering=array(), $limit=-1, $offset=-1) - { - if(count($ordering) > 0) - $sql = $this->applyOrdering($sql, $ordering); - if($limit>=0 || $offset>=0) - $sql = $this->applyLimitOffset($sql, $limit, $offset); - $command = $this->createCommand($sql); - $this->bindArrayValues($command, $parameters); - return $command; - } - - /** - * Creates a count(*) command for the table described in {@link setTableInfo TableInfo}. - * @param string count condition. - * @param array binding parameters. - * @return TDbCommand count command. - */ - public function createCountCommand($where='1=1', $parameters=array(),$ordering=array(), $limit=-1, $offset=-1) - { - return $this->createFindCommand($where, $parameters, $ordering, $limit, $offset, 'COUNT(*)'); - } - - /** - * Creates a delete command for the table described in {@link setTableInfo TableInfo}. - * The conditions for delete is given by the $where argument and the parameters - * for the condition is given by $parameters. - * @param string delete condition. - * @param array delete parameters. - * @return TDbCommand delete command. - */ - public function createDeleteCommand($where,$parameters=array()) - { - $table = $this->getTableInfo()->getTableFullName(); - if (!empty($where)) - $where = ' WHERE '.$where; - $command = $this->createCommand("DELETE FROM {$table}".$where); - $this->bindArrayValues($command, $parameters); - return $command; - } - - /** - * Creates an insert command for the table described in {@link setTableInfo TableInfo} for the given data. - * Each array key in the $data array must correspond to the column name of the table - * (if a column allows to be null, it may be omitted) to be inserted with - * the corresponding array value. - * @param array name-value pairs of new data to be inserted. - * @return TDbCommand insert command - */ - public function createInsertCommand($data) - { - $table = $this->getTableInfo()->getTableFullName(); - list($fields, $bindings) = $this->getInsertFieldBindings($data); - $command = $this->createCommand("INSERT INTO {$table}({$fields}) VALUES ($bindings)"); - $this->bindColumnValues($command, $data); - return $command; - } - - /** - * Creates an update command for the table described in {@link setTableInfo TableInfo} for the given data. - * Each array key in the $data array must correspond to the column name to be updated with the corresponding array value. - * @param array name-value pairs of data to be updated. - * @param string update condition. - * @param array update parameters. - * @return TDbCommand update command. - */ - public function createUpdateCommand($data, $where, $parameters=array()) - { - $table = $this->getTableInfo()->getTableFullName(); - if($this->hasIntegerKey($parameters)) - $fields = implode(', ', $this->getColumnBindings($data, true)); - else - $fields = implode(', ', $this->getColumnBindings($data)); - - if (!empty($where)) - $where = ' WHERE '.$where; - $command = $this->createCommand("UPDATE {$table} SET {$fields}".$where); - $this->bindArrayValues($command, array_merge($data, $parameters)); - return $command; - } - - /** - * Returns a list of insert field name and a list of binding names. - * @param object array or object to be inserted. - * @return array tuple ($fields, $bindings) - */ - protected function getInsertFieldBindings($values) - { - $fields = array(); $bindings=array(); - foreach(array_keys($values) as $name) - { - $fields[] = $this->getTableInfo()->getColumn($name)->getColumnName(); - $bindings[] = ':'.$name; - } - return array(implode(', ',$fields), implode(', ', $bindings)); - } - - /** - * Create a name-value or position-value if $position=true binding strings. - * @param array data for binding. - * @param boolean true to bind as position values. - * @return string update column names with corresponding binding substrings. - */ - protected function getColumnBindings($values, $position=false) - { - $bindings=array(); - foreach(array_keys($values) as $name) - { - $column = $this->getTableInfo()->getColumn($name)->getColumnName(); - $bindings[] = $position ? $column.' = ?' : $column.' = :'.$name; - } - return $bindings; - } - - /** - * @param string SQL query string. - * @return TDbCommand corresponding database command. - */ - public function createCommand($sql) - { - $this->getDbConnection()->setActive(true); - return $this->getDbConnection()->createCommand($sql); - } - - /** - * Bind the name-value pairs of $values where the array keys correspond to column names. - * @param TDbCommand database command. - * @param array name-value pairs. - */ - public function bindColumnValues($command, $values) - { - foreach($values as $name=>$value) - { - $column = $this->getTableInfo()->getColumn($name); - if($value === null && $column->getAllowNull()) - $command->bindValue(':'.$name, null, PDO::PARAM_NULL); - else - $command->bindValue(':'.$name, $value, $column->getPdoType()); - } - } - - /** - * @param TDbCommand database command - * @param array values for binding. - */ - public function bindArrayValues($command, $values) - { - if($this->hasIntegerKey($values)) - { - $values = array_values($values); - for($i = 0, $max=count($values); $i<$max; $i++) - $command->bindValue($i+1, $values[$i], $this->getPdoType($values[$i])); - } - else - { - foreach($values as $name=>$value) - { - $prop = $name[0]===':' ? $name : ':'.$name; - $command->bindValue($prop, $value, $this->getPdoType($value)); - } - } - } - - /** - * @param mixed PHP value - * @return integer PDO parameter types. - */ - public static function getPdoType($value) - { - switch(gettype($value)) - { - case 'boolean': return PDO::PARAM_BOOL; - case 'integer': return PDO::PARAM_INT; - case 'string' : return PDO::PARAM_STR; - case 'NULL' : return PDO::PARAM_NULL; - } - } - - /** - * @param array - * @return boolean true if any array key is an integer. - */ - protected function hasIntegerKey($array) - { - foreach($array as $k=>$v) - { - if(gettype($k)==='integer') - return true; - } - return false; - } -} + + * @link http://www.pradosoft.com/ + * @copyright Copyright © 2005-2012 PradoSoft + * @license http://www.pradosoft.com/license/ + * @version $Id$ + * @package System.Data.Common + */ + +/** + * TDbCommandBuilder provides basic methods to create query commands for tables + * giving by {@link setTableInfo TableInfo} the property. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.Common + * @since 3.1 + */ +class TDbCommandBuilder extends TComponent +{ + private $_connection; + private $_tableInfo; + + /** + * @param TDbConnection database connection. + * @param TDbTableInfo table information. + */ + public function __construct($connection=null, $tableInfo=null) + { + $this->setDbConnection($connection); + $this->setTableInfo($tableInfo); + } + + /** + * @return TDbConnection database connection. + */ + public function getDbConnection() + { + return $this->_connection; + } + + /** + * @param TDbConnection database connection. + */ + public function setDbConnection($value) + { + $this->_connection=$value; + } + + /** + * @param TDbTableInfo table information. + */ + public function setTableInfo($value) + { + $this->_tableInfo=$value; + } + + /** + * @param TDbTableInfo table information. + */ + public function getTableInfo() + { + return $this->_tableInfo; + } + + /** + * Iterate through all the columns and returns the last insert id of the + * first column that has a sequence or serial. + * @return mixed last insert id, null if none is found. + */ + public function getLastInsertID() + { + foreach($this->getTableInfo()->getColumns() as $column) + { + if($column->hasSequence()) + return $this->getDbConnection()->getLastInsertID($column->getSequenceName()); + } + } + + /** + * Alters the sql to apply $limit and $offset. Default implementation is applicable + * for PostgreSQL, MySQL and SQLite. + * @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 ? (int)$limit : -1; + $offset = $offset!==null ? (int)$offset : -1; + $limitStr = $limit >= 0 ? ' LIMIT '.$limit : ''; + $offsetStr = $offset >= 0 ? ' OFFSET '.$offset : ''; + return $sql.$limitStr.$offsetStr; + } + + /** + * @param string SQL string without existing ordering. + * @param array pairs of column names as key and direction as value. + * @return string modified SQL applied with ORDER BY. + */ + public function applyOrdering($sql, $ordering) + { + $orders=array(); + foreach($ordering as $name => $direction) + { + $direction = strtolower($direction) == 'desc' ? 'DESC' : 'ASC'; + if(false !== strpos($name, '(') && false !== strpos($name, ')')) { + // key is a function (bad practice, but we need to handle it) + $key = $name; + } else { + // key is a column + $key = $this->getTableInfo()->getColumn($name)->getColumnName(); + } + $orders[] = $key.' '.$direction; + } + if(count($orders) > 0) + $sql .= ' ORDER BY '.implode(', ', $orders); + return $sql; + } + + /** + * Computes the SQL condition for search a set of column using regular expression + * (or LIKE, depending on database implementation) to match a string of + * keywords (default matches all keywords). + * @param array list of column id for potential search condition. + * @param string string of keywords + * @return string SQL search condition matching on a set of columns. + */ + public function getSearchExpression($fields, $keywords) + { + if(strlen(trim($keywords)) == 0) return ''; + $words = preg_split('/\s/u', $keywords); + $conditions = array(); + foreach($fields as $field) + { + $column = $this->getTableInfo()->getColumn($field)->getColumnName(); + $conditions[] = $this->getSearchCondition($column, $words); + } + return '('.implode(' OR ', $conditions).')'; + } + + /** + * @param string column name. + * @param array keywords + * @return string search condition for all words in one column. + */ + protected function getSearchCondition($column, $words) + { + $conditions=array(); + foreach($words as $word) + $conditions[] = $column.' LIKE '.$this->getDbConnection()->quoteString('%'.$word.'%'); + return '('.implode(' AND ', $conditions).')'; + } + + /** + * + * Different behavior depends on type of passed data + * string + * usage without modification + * + * null + * will be expanded to full list of quoted table column names (quoting depends on database) + * + * array + * - Column names will be quoted if used as key or value of array + * + * array('col1', 'col2', 'col2') + * // SELECT `col1`, `col2`, `col3` FROM... + * + * + * - Column aliasing + * + * array('mycol1' => 'col1', 'mycol2' => 'COUNT(*)') + * // SELECT `col1` AS mycol1, COUNT(*) AS mycol2 FROM... + * + * + * - NULL and scalar values (strings will be quoted depending on database) + * + * array('col1' => 'my custom string', 'col2' => 1.0, 'col3' => 'NULL') + * // SELECT "my custom string" AS `col1`, 1.0 AS `col2`, NULL AS `col3` FROM... + * + * + * - If the *-wildcard char is used as key or value, add the full list of quoted table column names + * + * array('col1' => 'NULL', '*') + * // SELECT `col1`, `col2`, `col3`, NULL AS `col1` FROM... + * + * @param mixed $value + * @return array of generated fields - use implode(', ', $selectfieldlist) to collapse field list for usage + * @since 3.1.7 + * @todo add support for table aliasing + * @todo add support for quoting of column aliasing + */ + public function getSelectFieldList($data='*') { + if(is_scalar($data)) { + $tmp = explode(',', $data); + $result = array(); + foreach($tmp as $v) + $result[] = trim($v); + return $result; + } + + $bHasWildcard = false; + $result = array(); + if(is_array($data) || $data instanceof Traversable) { + $columns = $this->getTableInfo()->getColumns(); + foreach($data as $key=>$value) { + if($key==='*' || $value==='*') { + $bHasWildcard = true; + continue; + } + + if(strToUpper($key)==='NULL') { + $result[] = 'NULL'; + continue; + } + + if(strpos($key, '(')!==false && strpos($key, ')')!==false) { + $result[] = $key; + continue; + } + + if(stripos($key, 'AS')!==false) { + $result[] = $key; + continue; + } + + if(stripos($value, 'AS')!==false) { + $result[] = $value; + continue; + } + + $v = isset($columns[$value]); + $k = isset($columns[$key]); + if(is_integer($key) && $v) { + $key = $value; + $k = $v; + } + + if(strToUpper($value)==='NULL') { + if($k) + $result[] = 'NULL AS ' . $columns[$key]->getColumnName(); + else + $result[] = 'NULL' . (is_string($key) ? (' AS ' . (string)$key) : ''); + continue; + } + + if(strpos($value, '(')!==false && strpos($value, ')')!==false) { + if($k) + $result[] = $value . ' AS ' . $columns[$key]->getColumnName(); + else + $result[] = $value . (is_string($key) ? (' AS ' . (string)$key) : ''); + continue; + } + + if($v && $key==$value) { + $result[] = $columns[$value]->getColumnName(); + continue; + } + + if($k && $value==null) { + $result[] = $columns[$key]->getColumnName(); + continue; + } + + if(is_string($key) && $v) { + $result[] = $columns[$value]->getColumnName() . ' AS ' . $key; + continue; + } + + if(is_numeric($value) && $k) { + $result[] = $value . ' AS ' . $columns[$key]->getColumnName(); + continue; + } + + if(is_string($value) && $k) { + $result[] = $this->getDbConnection()->quoteString($value) . ' AS ' . $columns[$key]->getColumnName(); + continue; + } + + if(!$v && !$k && is_integer($key)) { + $result[] = is_numeric($value) ? $value : $this->getDbConnection()->quoteString((string)$value); + continue; + } + + $result[] = (is_numeric($value) ? $value : $this->getDbConnection()->quoteString((string)$value)) . ' AS ' . $key; + } + } + + if($data===null || count($result) == 0 || $bHasWildcard) + $result = $result = array_merge($this->getTableInfo()->getColumnNames(), $result); + + return $result; + } + + /** + * Appends the $where condition to the string "SELECT * FROM tableName WHERE ". + * The tableName is obtained from the {@link setTableInfo TableInfo} property. + * @param string query condition + * @param array condition parameters. + * @return TDbCommand query command. + */ + public function createFindCommand($where='1=1', $parameters=array(), $ordering=array(), $limit=-1, $offset=-1, $select='*') + { + $table = $this->getTableInfo()->getTableFullName(); + $fields = implode(', ', $this -> getSelectFieldList($select)); + $sql = "SELECT {$fields} FROM {$table}"; + if(!empty($where)) + $sql .= " WHERE {$where}"; + return $this->applyCriterias($sql, $parameters, $ordering, $limit, $offset); + } + + public function applyCriterias($sql, $parameters=array(),$ordering=array(), $limit=-1, $offset=-1) + { + if(count($ordering) > 0) + $sql = $this->applyOrdering($sql, $ordering); + if($limit>=0 || $offset>=0) + $sql = $this->applyLimitOffset($sql, $limit, $offset); + $command = $this->createCommand($sql); + $this->bindArrayValues($command, $parameters); + return $command; + } + + /** + * Creates a count(*) command for the table described in {@link setTableInfo TableInfo}. + * @param string count condition. + * @param array binding parameters. + * @return TDbCommand count command. + */ + public function createCountCommand($where='1=1', $parameters=array(),$ordering=array(), $limit=-1, $offset=-1) + { + return $this->createFindCommand($where, $parameters, $ordering, $limit, $offset, 'COUNT(*)'); + } + + /** + * Creates a delete command for the table described in {@link setTableInfo TableInfo}. + * The conditions for delete is given by the $where argument and the parameters + * for the condition is given by $parameters. + * @param string delete condition. + * @param array delete parameters. + * @return TDbCommand delete command. + */ + public function createDeleteCommand($where,$parameters=array()) + { + $table = $this->getTableInfo()->getTableFullName(); + if (!empty($where)) + $where = ' WHERE '.$where; + $command = $this->createCommand("DELETE FROM {$table}".$where); + $this->bindArrayValues($command, $parameters); + return $command; + } + + /** + * Creates an insert command for the table described in {@link setTableInfo TableInfo} for the given data. + * Each array key in the $data array must correspond to the column name of the table + * (if a column allows to be null, it may be omitted) to be inserted with + * the corresponding array value. + * @param array name-value pairs of new data to be inserted. + * @return TDbCommand insert command + */ + public function createInsertCommand($data) + { + $table = $this->getTableInfo()->getTableFullName(); + list($fields, $bindings) = $this->getInsertFieldBindings($data); + $command = $this->createCommand("INSERT INTO {$table}({$fields}) VALUES ($bindings)"); + $this->bindColumnValues($command, $data); + return $command; + } + + /** + * Creates an update command for the table described in {@link setTableInfo TableInfo} for the given data. + * Each array key in the $data array must correspond to the column name to be updated with the corresponding array value. + * @param array name-value pairs of data to be updated. + * @param string update condition. + * @param array update parameters. + * @return TDbCommand update command. + */ + public function createUpdateCommand($data, $where, $parameters=array()) + { + $table = $this->getTableInfo()->getTableFullName(); + if($this->hasIntegerKey($parameters)) + $fields = implode(', ', $this->getColumnBindings($data, true)); + else + $fields = implode(', ', $this->getColumnBindings($data)); + + if (!empty($where)) + $where = ' WHERE '.$where; + $command = $this->createCommand("UPDATE {$table} SET {$fields}".$where); + $this->bindArrayValues($command, array_merge($data, $parameters)); + return $command; + } + + /** + * Returns a list of insert field name and a list of binding names. + * @param object array or object to be inserted. + * @return array tuple ($fields, $bindings) + */ + protected function getInsertFieldBindings($values) + { + $fields = array(); $bindings=array(); + foreach(array_keys($values) as $name) + { + $fields[] = $this->getTableInfo()->getColumn($name)->getColumnName(); + $bindings[] = ':'.$name; + } + return array(implode(', ',$fields), implode(', ', $bindings)); + } + + /** + * Create a name-value or position-value if $position=true binding strings. + * @param array data for binding. + * @param boolean true to bind as position values. + * @return string update column names with corresponding binding substrings. + */ + protected function getColumnBindings($values, $position=false) + { + $bindings=array(); + foreach(array_keys($values) as $name) + { + $column = $this->getTableInfo()->getColumn($name)->getColumnName(); + $bindings[] = $position ? $column.' = ?' : $column.' = :'.$name; + } + return $bindings; + } + + /** + * @param string SQL query string. + * @return TDbCommand corresponding database command. + */ + public function createCommand($sql) + { + $this->getDbConnection()->setActive(true); + return $this->getDbConnection()->createCommand($sql); + } + + /** + * Bind the name-value pairs of $values where the array keys correspond to column names. + * @param TDbCommand database command. + * @param array name-value pairs. + */ + public function bindColumnValues($command, $values) + { + foreach($values as $name=>$value) + { + $column = $this->getTableInfo()->getColumn($name); + if($value === null && $column->getAllowNull()) + $command->bindValue(':'.$name, null, PDO::PARAM_NULL); + else + $command->bindValue(':'.$name, $value, $column->getPdoType()); + } + } + + /** + * @param TDbCommand database command + * @param array values for binding. + */ + public function bindArrayValues($command, $values) + { + if($this->hasIntegerKey($values)) + { + $values = array_values($values); + for($i = 0, $max=count($values); $i<$max; $i++) + $command->bindValue($i+1, $values[$i], $this->getPdoType($values[$i])); + } + else + { + foreach($values as $name=>$value) + { + $prop = $name[0]===':' ? $name : ':'.$name; + $command->bindValue($prop, $value, $this->getPdoType($value)); + } + } + } + + /** + * @param mixed PHP value + * @return integer PDO parameter types. + */ + public static function getPdoType($value) + { + switch(gettype($value)) + { + case 'boolean': return PDO::PARAM_BOOL; + case 'integer': return PDO::PARAM_INT; + case 'string' : return PDO::PARAM_STR; + case 'NULL' : return PDO::PARAM_NULL; + } + } + + /** + * @param array + * @return boolean true if any array key is an integer. + */ + protected function hasIntegerKey($array) + { + foreach($array as $k=>$v) + { + if(gettype($k)==='integer') + return true; + } + return false; + } +} diff --git a/framework/Data/Common/TDbMetaData.php b/framework/Data/Common/TDbMetaData.php index fdf367c9..5fc4fec9 100644 --- a/framework/Data/Common/TDbMetaData.php +++ b/framework/Data/Common/TDbMetaData.php @@ -1,183 +1,183 @@ - - * @link http://www.pradosoft.com/ + + * @link http://www.pradosoft.com/ * @copyright Copyright © 2005-2012 PradoSoft - * @license http://www.pradosoft.com/license/ - * @version $Id$ - * @package System.Data.Common - */ - -/** - * TDbMetaData is the base class for retrieving metadata information, such as - * table and columns information, from a database connection. - * - * Use the {@link getTableInfo} method to retrieve a table information. - * - * @author Wei Zhuo - * @version $Id$ - * @package System.Data.Common - * @since 3.1 - */ -abstract class TDbMetaData extends TComponent -{ - private $_tableInfoCache=array(); - private $_connection; - - /** - * @var array - */ - protected static $delimiterIdentifier = array('[', ']', '"', '`', "'"); - - /** - * @param TDbConnection database connection. - */ - public function __construct($conn) - { - $this->_connection=$conn; - } - - /** - * @return TDbConnection database connection. - */ - public function getDbConnection() - { - return $this->_connection; - } - - /** - * Obtain database specific TDbMetaData class using the driver name of the database connection. - * @param TDbConnection database connection. - * @return TDbMetaData database specific TDbMetaData. - */ - public static function getInstance($conn) - { - $conn->setActive(true); //must be connected before retrieving driver name - $driver = $conn->getDriverName(); - switch(strtolower($driver)) - { - case 'pgsql': - Prado::using('System.Data.Common.Pgsql.TPgsqlMetaData'); - return new TPgsqlMetaData($conn); - case 'mysqli': - case 'mysql': - Prado::using('System.Data.Common.Mysql.TMysqlMetaData'); - return new TMysqlMetaData($conn); - case 'sqlite': //sqlite 3 - case 'sqlite2': //sqlite 2 - Prado::using('System.Data.Common.Sqlite.TSqliteMetaData'); - return new TSqliteMetaData($conn); - case 'mssql': // Mssql driver on windows hosts - case 'dblib': // dblib drivers on linux (and maybe others os) hosts - Prado::using('System.Data.Common.Mssql.TMssqlMetaData'); - return new TMssqlMetaData($conn); - case 'oci': - Prado::using('System.Data.Common.Oracle.TOracleMetaData'); - return new TOracleMetaData($conn); -// case 'ibm': -// Prado::using('System.Data.Common.IbmDb2.TIbmDb2MetaData'); -// return new TIbmDb2MetaData($conn); - default: - throw new TDbException('ar_invalid_database_driver',$driver); - } - } - - /** - * Obtains table meta data information for the current connection and given table name. - * @param string table or view name - * @return TDbTableInfo table information. - */ - public function getTableInfo($tableName=null) - { - $key = $tableName===null?$this->getDbConnection()->getConnectionString():$tableName; - if(!isset($this->_tableInfoCache[$key])) - { - $class = $this->getTableInfoClass(); - $tableInfo = $tableName===null ? new $class : $this->createTableInfo($tableName); - $this->_tableInfoCache[$key] = $tableInfo; - } - return $this->_tableInfoCache[$key]; - } - - /** - * Creates a command builder for a given table name. - * @param string table name. - * @return TDbCommandBuilder command builder instance for the given table. - */ - public function createCommandBuilder($tableName=null) - { - return $this->getTableInfo($tableName)->createCommandBuilder($this->getDbConnection()); - } - - /** - * This method should be implemented by decendent classes. - * @return TDbTableInfo driver dependent create builder. - */ - abstract protected function createTableInfo($tableName); - - /** - * @return string TDbTableInfo class name. - */ - protected function getTableInfoClass() - { - 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) - { - $name = str_replace(self::$delimiterIdentifier, '', $name); - - $args = func_get_args(); - $rgt = $lft = isset($args[1]) ? $args[1] : ''; - $rgt = isset($args[2]) ? $args[2] : $rgt; - - 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) - { - $args = func_get_args(); - $rgt = $lft = isset($args[1]) ? $args[1] : ''; - $rgt = isset($args[2]) ? $args[2] : $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) - { - $args = func_get_args(); - $rgt = $lft = isset($args[1]) ? $args[1] : ''; - $rgt = isset($args[2]) ? $args[2] : $rgt; - - return $lft . str_replace(self::$delimiterIdentifier, '', $name) . $rgt; - } -} - + * @license http://www.pradosoft.com/license/ + * @version $Id$ + * @package System.Data.Common + */ + +/** + * TDbMetaData is the base class for retrieving metadata information, such as + * table and columns information, from a database connection. + * + * Use the {@link getTableInfo} method to retrieve a table information. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.Common + * @since 3.1 + */ +abstract class TDbMetaData extends TComponent +{ + private $_tableInfoCache=array(); + private $_connection; + + /** + * @var array + */ + protected static $delimiterIdentifier = array('[', ']', '"', '`', "'"); + + /** + * @param TDbConnection database connection. + */ + public function __construct($conn) + { + $this->_connection=$conn; + } + + /** + * @return TDbConnection database connection. + */ + public function getDbConnection() + { + return $this->_connection; + } + + /** + * Obtain database specific TDbMetaData class using the driver name of the database connection. + * @param TDbConnection database connection. + * @return TDbMetaData database specific TDbMetaData. + */ + public static function getInstance($conn) + { + $conn->setActive(true); //must be connected before retrieving driver name + $driver = $conn->getDriverName(); + switch(strtolower($driver)) + { + case 'pgsql': + Prado::using('System.Data.Common.Pgsql.TPgsqlMetaData'); + return new TPgsqlMetaData($conn); + case 'mysqli': + case 'mysql': + Prado::using('System.Data.Common.Mysql.TMysqlMetaData'); + return new TMysqlMetaData($conn); + case 'sqlite': //sqlite 3 + case 'sqlite2': //sqlite 2 + Prado::using('System.Data.Common.Sqlite.TSqliteMetaData'); + return new TSqliteMetaData($conn); + case 'mssql': // Mssql driver on windows hosts + case 'dblib': // dblib drivers on linux (and maybe others os) hosts + Prado::using('System.Data.Common.Mssql.TMssqlMetaData'); + return new TMssqlMetaData($conn); + case 'oci': + Prado::using('System.Data.Common.Oracle.TOracleMetaData'); + return new TOracleMetaData($conn); +// case 'ibm': +// Prado::using('System.Data.Common.IbmDb2.TIbmDb2MetaData'); +// return new TIbmDb2MetaData($conn); + default: + throw new TDbException('ar_invalid_database_driver',$driver); + } + } + + /** + * Obtains table meta data information for the current connection and given table name. + * @param string table or view name + * @return TDbTableInfo table information. + */ + public function getTableInfo($tableName=null) + { + $key = $tableName===null?$this->getDbConnection()->getConnectionString():$tableName; + if(!isset($this->_tableInfoCache[$key])) + { + $class = $this->getTableInfoClass(); + $tableInfo = $tableName===null ? new $class : $this->createTableInfo($tableName); + $this->_tableInfoCache[$key] = $tableInfo; + } + return $this->_tableInfoCache[$key]; + } + + /** + * Creates a command builder for a given table name. + * @param string table name. + * @return TDbCommandBuilder command builder instance for the given table. + */ + public function createCommandBuilder($tableName=null) + { + return $this->getTableInfo($tableName)->createCommandBuilder($this->getDbConnection()); + } + + /** + * This method should be implemented by decendent classes. + * @return TDbTableInfo driver dependent create builder. + */ + abstract protected function createTableInfo($tableName); + + /** + * @return string TDbTableInfo class name. + */ + protected function getTableInfoClass() + { + 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) + { + $name = str_replace(self::$delimiterIdentifier, '', $name); + + $args = func_get_args(); + $rgt = $lft = isset($args[1]) ? $args[1] : ''; + $rgt = isset($args[2]) ? $args[2] : $rgt; + + 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) + { + $args = func_get_args(); + $rgt = $lft = isset($args[1]) ? $args[1] : ''; + $rgt = isset($args[2]) ? $args[2] : $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) + { + $args = func_get_args(); + $rgt = $lft = isset($args[1]) ? $args[1] : ''; + $rgt = isset($args[2]) ? $args[2] : $rgt; + + return $lft . str_replace(self::$delimiterIdentifier, '', $name) . $rgt; + } +} + diff --git a/framework/Data/Common/TDbTableColumn.php b/framework/Data/Common/TDbTableColumn.php index 8a0832d8..3967ec0c 100644 --- a/framework/Data/Common/TDbTableColumn.php +++ b/framework/Data/Common/TDbTableColumn.php @@ -1,199 +1,199 @@ - - * @link http://www.pradosoft.com/ + + * @link http://www.pradosoft.com/ * @copyright Copyright © 2005-2012 PradoSoft - * @license http://www.pradosoft.com/license/ - * @version $Id$ - * @package System.Data.Common - */ - -/** - * TDbTableColumn class describes the column meta data of the schema for a database table. - * - * @author Wei Zhuo - * @version $Id$ - * @package System.Data.Common - * @since 3.1 - */ -class TDbTableColumn extends TComponent -{ - const UNDEFINED_VALUE= INF; //use infinity for undefined value - - private $_info=array(); - - /** - * Sets the table column meta data. - * @param array table column information. - */ - public function __construct($columnInfo) - { - $this->_info=$columnInfo; - } - - /** - * @param string information array key name - * @param mixed default value if information array value is null - * @return mixed information array value. - */ - protected function getInfo($name,$default=null) - { - return isset($this->_info[$name]) ? $this->_info[$name] : $default; - } - - /** - * @param string information array key name - * @param mixed new information array value. - */ - protected function setInfo($name,$value) - { - $this->_info[$name]=$value; - } - - /** - * Returns the derived PHP primitive type from the db type. Default returns 'string'. - * @return string derived PHP primitive type from the column db type. - */ - public function getPHPType() - { - return 'string'; - } - - /** - * @param integer PDO bind param/value types, default returns string. - */ - public function getPdoType() - { - switch($this->getPHPType()) - { - case 'boolean': return PDO::PARAM_BOOL; - case 'integer': return PDO::PARAM_INT; - case 'string' : return PDO::PARAM_STR; - } - return PDO::PARAM_STR; - } - - /** - * @return string name of the column in the table (identifier quoted). - */ - public function getColumnName() - { - return $this->getInfo('ColumnName'); - } - - /** - * @return string name of the column with quoted identifier. - */ - public function getColumnId() - { - return $this->getInfo('ColumnId'); - } - - /** - * @return string size of the column. - */ - public function getColumnSize() - { - return $this->getInfo('ColumnSize'); - } - - /** - * @return integer zero-based ordinal position of the column in the table. - */ - public function getColumnIndex() - { - return $this->getInfo('ColumnIndex'); - } - - /** - * @return string column type. - */ - public function getDbType() - { - return $this->getInfo('DbType'); - } - - /** - * @return boolean specifies whether value Null is allowed, default is false. - */ - public function getAllowNull() - { - return $this->getInfo('AllowNull',false); - } - - /** - * @return mixed default column value if column value was null. - */ - public function getDefaultValue() - { - return $this->getInfo('DefaultValue', self::UNDEFINED_VALUE); - } - - /** - * @return string precision of the column data, if the data is numeric. - */ - public function getNumericPrecision() - { - return $this->getInfo('NumericPrecision'); - } - - /** - * @return string scale of the column data, if the data is numeric. - */ - public function getNumericScale() - { - return $this->getInfo('NumericScale'); - } - - public function getMaxiumNumericConstraint() - { - if(($precision=$this->getNumericPrecision())!==null) - { - $scale=$this->getNumericScale(); - return $scale===null ? pow(10,$precision) : pow(10,$precision-$scale); - } - } - - /** - * @return boolean whether this column is a primary key for the table, default is false. - */ - public function getIsPrimaryKey() - { - return $this->getInfo('IsPrimaryKey',false); - } - - /** - * @return boolean whether this column is a foreign key, default is false. - */ - public function getIsForeignKey() - { - return $this->getInfo('IsForeignKey',false); - } - - /** - * @param string sequence name, only applicable if column is a sequence - */ - public function getSequenceName() - { - return $this->getInfo('SequenceName'); - } - - /** - * @return boolean whether the column is a sequence. - */ - public function hasSequence() - { - return $this->getSequenceName()!==null; - } - - /** - * @return boolean whether this column is excluded from insert and update. - */ - public function getIsExcluded() - { - return false; - } -} - + * @license http://www.pradosoft.com/license/ + * @version $Id$ + * @package System.Data.Common + */ + +/** + * TDbTableColumn class describes the column meta data of the schema for a database table. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.Common + * @since 3.1 + */ +class TDbTableColumn extends TComponent +{ + const UNDEFINED_VALUE= INF; //use infinity for undefined value + + private $_info=array(); + + /** + * Sets the table column meta data. + * @param array table column information. + */ + public function __construct($columnInfo) + { + $this->_info=$columnInfo; + } + + /** + * @param string information array key name + * @param mixed default value if information array value is null + * @return mixed information array value. + */ + protected function getInfo($name,$default=null) + { + return isset($this->_info[$name]) ? $this->_info[$name] : $default; + } + + /** + * @param string information array key name + * @param mixed new information array value. + */ + protected function setInfo($name,$value) + { + $this->_info[$name]=$value; + } + + /** + * Returns the derived PHP primitive type from the db type. Default returns 'string'. + * @return string derived PHP primitive type from the column db type. + */ + public function getPHPType() + { + return 'string'; + } + + /** + * @param integer PDO bind param/value types, default returns string. + */ + public function getPdoType() + { + switch($this->getPHPType()) + { + case 'boolean': return PDO::PARAM_BOOL; + case 'integer': return PDO::PARAM_INT; + case 'string' : return PDO::PARAM_STR; + } + return PDO::PARAM_STR; + } + + /** + * @return string name of the column in the table (identifier quoted). + */ + public function getColumnName() + { + return $this->getInfo('ColumnName'); + } + + /** + * @return string name of the column with quoted identifier. + */ + public function getColumnId() + { + return $this->getInfo('ColumnId'); + } + + /** + * @return string size of the column. + */ + public function getColumnSize() + { + return $this->getInfo('ColumnSize'); + } + + /** + * @return integer zero-based ordinal position of the column in the table. + */ + public function getColumnIndex() + { + return $this->getInfo('ColumnIndex'); + } + + /** + * @return string column type. + */ + public function getDbType() + { + return $this->getInfo('DbType'); + } + + /** + * @return boolean specifies whether value Null is allowed, default is false. + */ + public function getAllowNull() + { + return $this->getInfo('AllowNull',false); + } + + /** + * @return mixed default column value if column value was null. + */ + public function getDefaultValue() + { + return $this->getInfo('DefaultValue', self::UNDEFINED_VALUE); + } + + /** + * @return string precision of the column data, if the data is numeric. + */ + public function getNumericPrecision() + { + return $this->getInfo('NumericPrecision'); + } + + /** + * @return string scale of the column data, if the data is numeric. + */ + public function getNumericScale() + { + return $this->getInfo('NumericScale'); + } + + public function getMaxiumNumericConstraint() + { + if(($precision=$this->getNumericPrecision())!==null) + { + $scale=$this->getNumericScale(); + return $scale===null ? pow(10,$precision) : pow(10,$precision-$scale); + } + } + + /** + * @return boolean whether this column is a primary key for the table, default is false. + */ + public function getIsPrimaryKey() + { + return $this->getInfo('IsPrimaryKey',false); + } + + /** + * @return boolean whether this column is a foreign key, default is false. + */ + public function getIsForeignKey() + { + return $this->getInfo('IsForeignKey',false); + } + + /** + * @param string sequence name, only applicable if column is a sequence + */ + public function getSequenceName() + { + return $this->getInfo('SequenceName'); + } + + /** + * @return boolean whether the column is a sequence. + */ + public function hasSequence() + { + return $this->getSequenceName()!==null; + } + + /** + * @return boolean whether this column is excluded from insert and update. + */ + public function getIsExcluded() + { + return false; + } +} + diff --git a/framework/Data/Common/TDbTableInfo.php b/framework/Data/Common/TDbTableInfo.php index 5342212e..c5c79e5b 100644 --- a/framework/Data/Common/TDbTableInfo.php +++ b/framework/Data/Common/TDbTableInfo.php @@ -1,166 +1,166 @@ - * @link http://www.pradosoft.com/ +/** + * TDbTableInfo class file. + * + * @author Wei Zhuo + * @link http://www.pradosoft.com/ * @copyright Copyright © 2005-2012 PradoSoft - * @license http://www.pradosoft.com/license/ - * @version $Id$ - * @package System.Data.Common - */ - -/** - * TDbTableInfo class describes the meta data of a database table. - * - * @author Wei Zhuo - * @version $Id$ - * @package System.Data.Common - * @since 3.1 - */ -class TDbTableInfo extends TComponent -{ - private $_info=array(); - - private $_primaryKeys; - private $_foreignKeys; - - private $_columns; - + * @license http://www.pradosoft.com/license/ + * @version $Id$ + * @package System.Data.Common + */ + +/** + * TDbTableInfo class describes the meta data of a database table. + * + * @author Wei Zhuo + * @version $Id$ + * @package System.Data.Common + * @since 3.1 + */ +class TDbTableInfo extends TComponent +{ + private $_info=array(); + + private $_primaryKeys; + private $_foreignKeys; + + private $_columns; + private $_lowercase; /** * @var null|array * @since 3.1.7 - */ - private $_names = null; - - /** - * Sets the database table meta data information. - * @param array table column information. - */ - public function __construct($tableInfo=array(),$primary=array(),$foreign=array()) - { - $this->_info=$tableInfo; - $this->_primaryKeys=$primary; - $this->_foreignKeys=$foreign; - $this->_columns=new TMap; - } - - /** - * @param TDbConnection database connection. - * @return TDbCommandBuilder new command builder - */ - public function createCommandBuilder($connection) - { - Prado::using('System.Data.Common.TDbCommandBuilder'); - return new TDbCommandBuilder($connection,$this); - } - - /** - * @param string information array key name - * @param mixed default value if information array value is null - * @return mixed information array value. - */ - protected function getInfo($name,$default=null) - { - return isset($this->_info[$name]) ? $this->_info[$name] : $default; - } - - /** - * @param string information array key name - * @param mixed new information array value. - */ - protected function setInfo($name,$value) - { - $this->_info[$name]=$value; - } - - /** - * @return string name of the table this column belongs to. - */ - public function getTableName() - { - return $this->getInfo('TableName'); - } - - /** - * @return string full name of the table, database dependent. - */ - public function getTableFullName() - { - return $this->getTableName(); - } - - /** - * @return boolean whether the table is a view, default is false. - */ - public function getIsView() - { - return $this->getInfo('IsView',false); - } - - /** - * @return TMap TDbTableColumn column meta data. - */ - public function getColumns() - { - return $this->_columns; - } - - /** - * @param string column id - * @return TDbTableColumn column information. - */ - public function getColumn($name) - { - if(($column = $this->_columns->itemAt($name))!==null) - return $column; - throw new TDbException('dbtableinfo_invalid_column_name', $name, $this->getTableFullName()); - } - - /** - * @param array list of column Id, empty to get all columns. - * @return array table column names (identifier quoted) - */ - public function getColumnNames() + */ + private $_names = null; + + /** + * Sets the database table meta data information. + * @param array table column information. + */ + public function __construct($tableInfo=array(),$primary=array(),$foreign=array()) + { + $this->_info=$tableInfo; + $this->_primaryKeys=$primary; + $this->_foreignKeys=$foreign; + $this->_columns=new TMap; + } + + /** + * @param TDbConnection database connection. + * @return TDbCommandBuilder new command builder + */ + public function createCommandBuilder($connection) + { + Prado::using('System.Data.Common.TDbCommandBuilder'); + return new TDbCommandBuilder($connection,$this); + } + + /** + * @param string information array key name + * @param mixed default value if information array value is null + * @return mixed information array value. + */ + protected function getInfo($name,$default=null) + { + return isset($this->_info[$name]) ? $this->_info[$name] : $default; + } + + /** + * @param string information array key name + * @param mixed new information array value. + */ + protected function setInfo($name,$value) + { + $this->_info[$name]=$value; + } + + /** + * @return string name of the table this column belongs to. + */ + public function getTableName() + { + return $this->getInfo('TableName'); + } + + /** + * @return string full name of the table, database dependent. + */ + public function getTableFullName() + { + return $this->getTableName(); + } + + /** + * @return boolean whether the table is a view, default is false. + */ + public function getIsView() + { + return $this->getInfo('IsView',false); + } + + /** + * @return TMap TDbTableColumn column meta data. + */ + public function getColumns() + { + return $this->_columns; + } + + /** + * @param string column id + * @return TDbTableColumn column information. + */ + public function getColumn($name) + { + if(($column = $this->_columns->itemAt($name))!==null) + return $column; + throw new TDbException('dbtableinfo_invalid_column_name', $name, $this->getTableFullName()); + } + + /** + * @param array list of column Id, empty to get all columns. + * @return array table column names (identifier quoted) + */ + public function getColumnNames() { if($this->_names===null) - { - $this->_names=array(); - foreach($this->getColumns() as $column) + { + $this->_names=array(); + foreach($this->getColumns() as $column) $this->_names[] = $column->getColumnName(); - } - return $this->_names; - } - - /** - * @return string[] names of primary key columns. - */ - public function getPrimaryKeys() - { - return $this->_primaryKeys; - } - - /** - * @return array tuples of foreign table and column name. - */ - public function getForeignKeys() - { - return $this->_foreignKeys; - } - - /** - * @return array lowercased column key names mapped to normal column ids. - */ - public function getLowerCaseColumnNames() - { - if($this->_lowercase===null) - { - $this->_lowercase=array(); - foreach($this->getColumns()->getKeys() as $key) - $this->_lowercase[strtolower($key)] = $key; - } - return $this->_lowercase; - } + } + return $this->_names; + } + + /** + * @return string[] names of primary key columns. + */ + public function getPrimaryKeys() + { + return $this->_primaryKeys; + } + + /** + * @return array tuples of foreign table and column name. + */ + public function getForeignKeys() + { + return $this->_foreignKeys; + } + + /** + * @return array lowercased column key names mapped to normal column ids. + */ + public function getLowerCaseColumnNames() + { + if($this->_lowercase===null) + { + $this->_lowercase=array(); + foreach($this->getColumns()->getKeys() as $key) + $this->_lowercase[strtolower($key)] = $key; + } + return $this->_lowercase; + } } \ No newline at end of file -- cgit v1.2.3