From 0f0d3c62e608287cdf77f1a3239371b521ecb40b Mon Sep 17 00:00:00 2001 From: wei <> Date: Sat, 14 Apr 2007 05:02:29 +0000 Subject: Refactor ActiveRecordGateway to use TDataGatewayCommand --- .../Data/Common/IbmDb2/TIbmColumnMetaData.php | 157 ++++++++++++++++ framework/Data/Common/IbmDb2/TIbmMetaData.php | 113 ++++++++++++ .../Data/Common/IbmDb2/TIbmMetaDataInspector.php | 113 ++++++++++++ .../Data/Common/Mssql/TMssqlCommandBuilder.php | 173 ++++++++++++++++++ framework/Data/Common/Mssql/TMssqlMetaData.php | 202 +++++++++++++++++++++ framework/Data/Common/Mssql/TMssqlTableColumn.php | 56 ++++++ framework/Data/Common/Mssql/TMssqlTableInfo.php | 62 +++++++ framework/Data/Common/Mysql/TMysqlMetaData.php | 14 +- framework/Data/Common/Mysql/TMysqlTableColumn.php | 2 +- framework/Data/Common/Mysql/TMysqlTableInfo.php | 5 +- framework/Data/Common/Pgsql/TPgsqlMetaData.php | 3 +- framework/Data/Common/Pgsql/TPgsqlTableInfo.php | 5 +- framework/Data/Common/Sqlite/TSqliteMetaData.php | 22 ++- .../Data/Common/Sqlite/TSqliteTableColumn.php | 2 +- framework/Data/Common/TDbCommandBuilder.php | 18 ++ framework/Data/Common/TDbMetaData.php | 9 +- framework/Data/Common/TDbTableColumn.php | 27 ++- 17 files changed, 968 insertions(+), 15 deletions(-) create mode 100644 framework/Data/Common/IbmDb2/TIbmColumnMetaData.php create mode 100644 framework/Data/Common/IbmDb2/TIbmMetaData.php create mode 100644 framework/Data/Common/IbmDb2/TIbmMetaDataInspector.php create mode 100644 framework/Data/Common/Mssql/TMssqlCommandBuilder.php create mode 100644 framework/Data/Common/Mssql/TMssqlMetaData.php create mode 100644 framework/Data/Common/Mssql/TMssqlTableColumn.php create mode 100644 framework/Data/Common/Mssql/TMssqlTableInfo.php (limited to 'framework/Data/Common') diff --git a/framework/Data/Common/IbmDb2/TIbmColumnMetaData.php b/framework/Data/Common/IbmDb2/TIbmColumnMetaData.php new file mode 100644 index 00000000..284a5fd2 --- /dev/null +++ b/framework/Data/Common/IbmDb2/TIbmColumnMetaData.php @@ -0,0 +1,157 @@ + + * @link http://www.pradosoft.com/ + * @copyright Copyright © 2005-2007 PradoSoft + * @license http://www.pradosoft.com/license/ + * @version $Id: TIbmColumnMetaData.php 1807 2007-03-31 06:42:15Z wei $ + * @package System.Data.ActiveRecord.Vendor + */ + +/** + * TIbmColumnMetaData class. + * + * Column details for IBM DB2 database. Using php_pdo_ibm.dll extension. + * + * @author Cesar Ramos + * @version $Id: TIbmColumnMetaData.php 1807 2007-03-31 06:42:15Z wei $ + * @package System.Data.ActiveRecord.Vendor + * @since 3.1 + */ +class TIbmColumnMetaData extends TComponent +{ + private $_name; + private $_type; + private $_length; + private $_autoIncrement; + private $_default; + private $_notNull=true; + + private $_isPrimary=null; + + private $_property; + + /** + * Initialize column meta data. + * + * @param string column name. + * @param string column data type. + * @param string column data length. + * @param boolean column can not be null. + * @param string serial name. + * @param string default value. + */ + public function __construct($name,$type,$length,$notNull,$autoIncrement,$default,$primary) + { + $this->_property=$name; + $this->_name=$name; + $this->_type=$type; + $this->_length=$length; + $this->_notNull=$notNull; + $this->_autoIncrement=$autoIncrement; + $this->_default=$default; + $this->_isPrimary=$primary; + } + + /** + * @return string quoted column name. + */ + public function getName() + { + return $this->_name; + } + + /** + * @return integer length. + */ + public function getLength() + { + return $this->_length; + } + + /** + * @return string active record property name + */ + public function getProperty() + { + return $this->_property; + } + + /** + * @return boolean true if column is a sequence, false otherwise. + */ + public function hasSequence() + { + return $this->_autoIncrement; + } + + /** + * @return null no sequence name. + */ + public function getSequenceName() + { + return null; + } + + /** + * @return boolean true if the column is a primary key, or part of a composite primary key. + */ + public function getIsPrimaryKey() + { + return $this->_isPrimary; + } + + /** + * @return string column type + */ + public function getType() + { + return $this->_type; + } + + + /** + * @return boolean false if column can be null, true otherwise. + */ + public function getNotNull() + { + return $this->_notNull; + } + + /** + * @return boolean true if column has default value, false otherwise. + */ + public function hasDefault() + { + return $this->_default !== null; + } + + /** + * @return string default column value. + */ + public function getDefaultValue() + { + return $this->_default; + } + + /** + * @return string PHP primative type derived from the column type. + */ + public function getPHPType() + { + switch(strtolower($this->_type)) + { + case 'smallint': case 'integer': + return 'integer'; + case 'real': case 'float': case 'double': case 'decimal': case 'bigint': + return 'float'; + default: + return 'string'; + } + } + +} + +?> \ No newline at end of file diff --git a/framework/Data/Common/IbmDb2/TIbmMetaData.php b/framework/Data/Common/IbmDb2/TIbmMetaData.php new file mode 100644 index 00000000..99df5465 --- /dev/null +++ b/framework/Data/Common/IbmDb2/TIbmMetaData.php @@ -0,0 +1,113 @@ + + * @link http://www.pradosoft.com/ + * @copyright Copyright © 2005-2007 PradoSoft + * @license http://www.pradosoft.com/license/ + * @version $Id: TIbmMetaData.php 1807 2007-03-31 06:42:15Z wei $ + * @package System.Data.ActiveRecord.Vendor + */ +Prado::using('System.Data.ActiveRecord.Vendor.TDbMetaDataCommon'); + +/** + * TIbmMetaData class. + * + * Column details for IBM DB2 database. Using php_pdo_ibm.dll extension. + * + * Does not support LIMIT and OFFSET criterias. + * + * @author Cesar Ramos + * @version $Id: TIbmMetaData.php 1807 2007-03-31 06:42:15Z wei $ + * @package System.Data.ActiveRecord.Vendor + * @since 3.1 + */ +class TIbmMetaData extends TDbMetaDataCommon +{ + /** + * Build the SQL search string from the criteria object for IBM DB2 database. + * @param TDbConnection database connection. + * @param TActiveRecordCriteria search criteria. + * @return string SQL search. + */ + protected function getSqlFromCriteria($conn, $criteria) + { + if($criteria===null) return ''; + $sql = ''; + if(($condition = $criteria->getCondition())!==null) + $sql .= ' WHERE '.$condition; + $orders=array(); + foreach($criteria->getOrdersBy() as $by=>$ordering) + $orders[] = $this->getOrdering($by, $ordering); + if(count($orders) > 0) + $sql .= ' ORDER BY '.implode(', ', $orders); + //if(($limit = $criteria->getLimit())!==null) + //{ + // $sql .= ' FETCH FIRST '.intval($limit).' ROWS ONLY'; + //} + return strlen($sql) > 0 ? $sql : ''; + } + + /** + * Lowercase the data keys, IBM DB2 returns uppercase column names + * @param mixed record row + * @return array record row + */ + public function postQueryRow($row) + { + if(!is_array($row)) return $row; + $result=array(); + foreach($row as $k=>$v) + $result[strtolower($k)]=$v; + return $result; + } + + /** + * Lowercase the data keys, IBM DB2 returns uppercase column names + * @param mixed record row + * @return array record row + */ + public function postQuery($rows) + { + $data = array(); + foreach($rows as $k=>$v) + $data[$k] = $this->postQueryRow($v); + return $data; + } + + public function getSearchRegExpCriteria($fields, $keywords) + { + if(strlen(trim($keywords)) == 0) return ''; + $words = array(); + preg_match_all('/([a-zA-Z0-9-+]+)/', $keywords, $words); + $result = array(); + foreach($fields as $field) + { + $column = $this->getColumn($field); + if($this->isSearchableColumn($column)) + $result[] = $this->getLikeCriteriaStr($column->getName(), $words[0]); + } + $a = '('.implode(' OR ', $result).')'; + error_log($a); + return '('.implode(' OR ', $result).')'; + } + + protected function isSearchableColumn($column) + { + $type = strtolower($column->getType()); + return $type === 'char' || $type === 'varchar'; + } + + protected function getLikeCriteriaStr($column, $words) + { + $result=array(); + foreach($words as $word) + $result[] = "{$column} LIKE '%{$word}%'"; + return '('.implode(' AND ', $result).')'; + } + + +} +?> \ No newline at end of file diff --git a/framework/Data/Common/IbmDb2/TIbmMetaDataInspector.php b/framework/Data/Common/IbmDb2/TIbmMetaDataInspector.php new file mode 100644 index 00000000..1cd8af95 --- /dev/null +++ b/framework/Data/Common/IbmDb2/TIbmMetaDataInspector.php @@ -0,0 +1,113 @@ + + * @link http://www.pradosoft.com/ + * @copyright Copyright © 2005-2007 PradoSoft + * @license http://www.pradosoft.com/license/ + * @version $Id: TIbmMetaDataInspector.php 1807 2007-03-31 06:42:15Z wei $ + * @package System.Data.ActiveRecord.Vendor + */ +Prado::using('System.Data.ActiveRecord.Vendor.TDbMetaDataInspector'); +Prado::using('System.Data.ActiveRecord.Vendor.TIbmColumnMetaData'); +Prado::using('System.Data.ActiveRecord.Vendor.TIbmMetaData'); + +/** + * TIbmMetaDataInspector class. + * + * Column details for IBM DB2 database. Using php_pdo_ibm.dll extension. + * + * @author Cesar Ramos + * @version $Id: TIbmMetaDataInspector.php 1807 2007-03-31 06:42:15Z wei $ + * @package System.Data.ActiveRecord.Vendor + * @since 3.1 + */ +class TIbmMetaDataInspector extends TDbMetaDataInspector +{ + private $_schema; + + /** + * @param string default schema. + */ + public function setSchema($schema) + { + $this->_schema=$schema; + } + + /** + * @return string default schema. + */ + public function getSchema() + { + return $this->_schema; + } + /** + * Get the column definitions for given table. + * @param string table name. + * @return array column name value pairs of column meta data. + */ + protected function getColumnDefinitions($table) + { + if(count($parts= explode('.', $table)) > 1) + { + $tablename = $parts[1]; + $schema = $parts[0]; + } + else + { + $tablename = $parts[0]; + $schema = $this->getSchema(); + } + $sql="SELECT * FROM SYSCAT.COLUMNS WHERE TABNAME='".strtoupper($tablename)."'"; + if ($schema) + $sql=$sql." AND TABSCHEMA='".strtoupper($schema)."'"; + + $conn = $this->getDbConnection(); + $conn->setActive(true); + $command = $conn->createCommand($sql); + $command->prepare(); + $result=$command->query($sql); + foreach ($result as $col) + $cols[strtolower($col['COLNAME'])] = $this->getColumnMetaData($col); + return $cols; + } + + protected function getColumnMetaData($col) + { + $name = strtolower($col['COLNAME']); + $type = $col['TYPENAME']; + $length = $col['LENGTH']; + $notNull = $col['NULLS']==='N'?1:0; + $autoIncrement=$col['IDENTITY']==='Y'?1:0; + $default = $col['DEFAULT']; + $primaryKey = $col['KEYSEQ']?1:0; + return new TIbmColumnMetaData($name,$type,$length,$notNull,$autoIncrement,$default,$primaryKey); + } + + /** + * Not implemented, IBM does not always have foreign key constraints. + */ + protected function getConstraintKeys($table) + { + return array('primary'=>array(), 'foreign'=>array()); + } + + /** + * Create a new instance of meta data. + * @param string table name + * @param array column meta data + * @param array primary key meta data + * @param array foreign key meta data. + * @return TDbMetaData table meta data. + */ + protected function createMetaData($table, $columns, $primary, $foreign) + { + $pks = array(); + foreach($columns as $name=>$column) + if($column->getIsPrimaryKey()) + $pks[] = $name; + return new TIbmMetaData($table,$columns,$pks); + } +} +?> \ No newline at end of file diff --git a/framework/Data/Common/Mssql/TMssqlCommandBuilder.php b/framework/Data/Common/Mssql/TMssqlCommandBuilder.php new file mode 100644 index 00000000..b22c08a5 --- /dev/null +++ b/framework/Data/Common/Mssql/TMssqlCommandBuilder.php @@ -0,0 +1,173 @@ + + * @link http://www.pradosoft.com/ + * @copyright Copyright © 2005-2007 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'); + +/** + * TDbCommandBuilder provides basic methods to create query commands for tables + * giving by {@link setTableInfo TableInfo} the property. + * + * @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 + * + *
    + *
  • + * In particular, commas should NOT + * be used as part of the ordering expression or identifier. Commas must only be + * used for separating the ordering clauses. + *
    • + *
  • + * In the ORDER BY clause, the column name should NOT be be qualified + * with a table name or view name. Alias the column names or use column index. + *
    • + *
  • + * No clauses should follow the ORDER BY clause, e.g. no COMPUTE or FOR clauses. + *
    • + * + * @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; + } +} + +?> \ No newline at end of file diff --git a/framework/Data/Common/Mssql/TMssqlMetaData.php b/framework/Data/Common/Mssql/TMssqlMetaData.php new file mode 100644 index 00000000..c70bc349 --- /dev/null +++ b/framework/Data/Common/Mssql/TMssqlMetaData.php @@ -0,0 +1,202 @@ +getCatalogSchemaTableName($table); + $this->getDbConnection()->setActive(true); + $sql = <<getDbConnection()->createCommand($sql); + $command->bindValue(':table', $tableName); + if($schemaName!==null) + $command->bindValue(':schema', $schemaName); + if($catalogName!==null) + $command->bindValue(':catalog', $catalogName); + + $tableInfo=null; + foreach($command->query() as $col) + { + if($tableInfo===null) + $tableInfo = $this->createNewTableInfo($col); + $this->processColumn($tableInfo,$col); + } + return $tableInfo; + } + + protected function getCatalogSchemaTableName($table) + { + //remove possible delimiters + $result = explode('.', preg_replace('/\[|\]|"/', '', $table)); + if(count($result)===1) + return array(null,null,$result[0]); + if(count($result)===2) + return array(null,$result[0],$result[1]); + if(count($result)>2) + return array($result[0],$result[1],$result[2]); + } + + /** + * @param TMssqlTableInfo table information. + * @param array column information. + */ + protected function processColumn($tableInfo, $col) + { + $columnId = $col['COLUMN_NAME']; + + $info['ColumnName'] = "[$columnId]"; //quote the column names! + $info['ColumnId'] = $columnId; + $info['ColumnIndex'] = intval($col['ORDINAL_POSITION'])-1; //zero-based index + if($col['IS_NULLABLE']!=='NO') + $info['AllowNull'] = true; + if($col['COLUMN_DEFAULT']!==null) + $info['DefaultValue'] = $col['COLUMN_DEFAULT']; + + if(in_array($columnId, $tableInfo->getPrimaryKeys())) + $info['IsPrimaryKey'] = true; + if($this->isForeignKeyColumn($columnId, $tableInfo)) + $info['IsForeignKey'] = true; + + if($col['IsIdentity']==='1') + $info['AutoIncrement'] = true; + $info['DbType'] = $col['DATA_TYPE']; + if($col['CHARACTER_MAXIMUM_LENGTH']!==null) + $info['ColumnSize'] = intval($col['CHARACTER_MAXIMUM_LENGTH']); + if($col['NUMERIC_PRECISION'] !== null) + $info['NumericPrecision'] = intval($col['NUMERIC_PRECISION']); + if($col['NUMERIC_SCALE']!==null) + $info['NumericScale'] = intval($col['NUMERIC_SCALE']); + $tableInfo->Columns[$columnId] = new TMssqlTableColumn($info); + } + + /** + * @param string table schema name + * @param string table name. + * @return TMssqlTableInfo + */ + protected function createNewTableInfo($col) + { + $info['CatalogName'] = $col['TABLE_CATALOG']; + $info['SchemaName'] = $col['TABLE_SCHEMA']; + $info['TableName'] = $col['TABLE_NAME']; + if($col['TABLE_TYPE']==='VIEW') + $info['IsView'] = true; + list($primary, $foreign) = $this->getConstraintKeys($col); + return new TMssqlTableInfo($info,$primary,$foreign); + } + + /** + * 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($col) + { + $sql = <<getDbConnection()->createCommand($sql); + $command->bindValue(':table', $col['TABLE_NAME']); + $primary = array(); + foreach($command->query()->readAll() as $field) + $primary[] = $field['field_name']; + $foreign = $this->getForeignConstraints($col); + 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($col) + { + //From http://msdn2.microsoft.com/en-us/library/aa175805(SQL.80).aspx + $sql = <<getDbConnection()->createCommand($sql); + $command->bindValue(':table', $col['TABLE_NAME']); + $fkeys=array(); + $catalogSchema = "[{$col['TABLE_CATALOG']}].[{$col['TABLE_SCHEMA']}]"; + foreach($command->query() as $info) + { + $fkeys[$info['FK_CONSTRAINT_NAME']]['keys'][$info['FK_COLUMN_NAME']] = $info['UQ_COLUMN_NAME']; + $fkeys[$info['FK_CONSTRAINT_NAME']]['table'] = "{$catalogSchema}.[{$info['UQ_TABLE_NAME']}]"; + } + return count($fkeys) > 0 ? array_values($fkeys) : $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; + } +} + +?> \ No newline at end of file diff --git a/framework/Data/Common/Mssql/TMssqlTableColumn.php b/framework/Data/Common/Mssql/TMssqlTableColumn.php new file mode 100644 index 00000000..77c115e5 --- /dev/null +++ b/framework/Data/Common/Mssql/TMssqlTableColumn.php @@ -0,0 +1,56 @@ + + * @link http://www.pradosoft.com/ + * @copyright Copyright © 2005-2007 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'; + } + + public function getAutoIncrement() + { + return $this->getInfo('AutoIncrement',false); + } + + public function hasSequence() + { + return $this->getAutoIncrement(); + } + + public function getIsExcluded() + { + return strtolower($this->getDbType())==='timestamp'; + } +} + +?> \ No newline at end of file diff --git a/framework/Data/Common/Mssql/TMssqlTableInfo.php b/framework/Data/Common/Mssql/TMssqlTableInfo.php new file mode 100644 index 00000000..b407ad44 --- /dev/null +++ b/framework/Data/Common/Mssql/TMssqlTableInfo.php @@ -0,0 +1,62 @@ + + * @link http://www.pradosoft.com/ + * @copyright Copyright © 2005-2007 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'); + } + + public function getCatalogName() + { + return $this->getInfo('CatalogName'); + } + + /** + * @return string full name of the table, database dependent. + */ + public function getTableFullName() + { + 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); + } + +} + +?> \ No newline at end of file diff --git a/framework/Data/Common/Mysql/TMysqlMetaData.php b/framework/Data/Common/Mysql/TMysqlMetaData.php index 2a6d80bd..9e947298 100644 --- a/framework/Data/Common/Mysql/TMysqlMetaData.php +++ b/framework/Data/Common/Mysql/TMysqlMetaData.php @@ -1,5 +1,4 @@ -getSchemaTableName($table); + $find = $schemaName===null ? "`{$tableName}`" : "`{$schemaName}`.`{$tableName}`"; $this->getDbConnection()->setActive(true); - $sql = "SHOW FULL FIELDS FROM {$table}"; + $sql = "SHOW FULL FIELDS FROM {$find}"; $command = $this->getDbConnection()->createCommand($sql); $tableInfo = $this->createNewTableInfo($table); $index=0; @@ -37,6 +38,7 @@ class TMysqlMetaData extends TDbMetaData $columnId = $col['Field']; $info['ColumnName'] = "`$columnId`"; //quote the column names! + $info['ColumnId'] = $columnId; $info['ColumnIndex'] = $col['index']; if($col['Null']!=='NO') $info['AllowNull'] = true; @@ -122,7 +124,8 @@ class TMysqlMetaData extends TDbMetaData /** * http://dev.mysql.com/doc/refman/5.0/en/identifiers.html - * @param unknown_type $name + * @param string identifier name + * @param boolean true if valid identifier. */ protected function isValidIdentifier($name) { @@ -139,7 +142,8 @@ class TMysqlMetaData extends TDbMetaData list($schemaName,$tableName) = $this->getSchemaTableName($table); $info['SchemaName'] = $schemaName; $info['TableName'] = $tableName; - $info['IsView'] = $this->getIsView($schemaName,$tableName); + if($this->getIsView($schemaName,$tableName)) + $info['IsView'] = true; list($primary, $foreign) = $this->getConstraintKeys($schemaName, $tableName); return new TMysqlTableInfo($info,$primary,$foreign); } diff --git a/framework/Data/Common/Mysql/TMysqlTableColumn.php b/framework/Data/Common/Mysql/TMysqlTableColumn.php index 4f0f7da2..0e991d6b 100644 --- a/framework/Data/Common/Mysql/TMysqlTableColumn.php +++ b/framework/Data/Common/Mysql/TMysqlTableColumn.php @@ -59,7 +59,7 @@ class TMysqlTableColumn extends TDbTableColumn /** * @return boolean true if auto increment is true. */ - public function getHasSequence() + public function hasSequence() { return $this->getAutoIncrement(); } diff --git a/framework/Data/Common/Mysql/TMysqlTableInfo.php b/framework/Data/Common/Mysql/TMysqlTableInfo.php index e8585730..9bc01717 100644 --- a/framework/Data/Common/Mysql/TMysqlTableInfo.php +++ b/framework/Data/Common/Mysql/TMysqlTableInfo.php @@ -39,7 +39,10 @@ class TMysqlTableInfo extends TDbTableInfo */ public function getTableFullName() { - return '`'.$this->getSchemaName().'`.`'.$this->getTableName().'`'; + if(($schema=$this->getSchemaName())!==null) + return '`'.$schema.'`.`'.$this->getTableName(); + else + return '`'.$this->getTableName().'`'; } } diff --git a/framework/Data/Common/Pgsql/TPgsqlMetaData.php b/framework/Data/Common/Pgsql/TPgsqlMetaData.php index e964cfe7..ac7e6b7a 100644 --- a/framework/Data/Common/Pgsql/TPgsqlMetaData.php +++ b/framework/Data/Common/Pgsql/TPgsqlMetaData.php @@ -50,7 +50,7 @@ class TPgsqlMetaData extends TDbMetaData */ protected function getSchemaTableName($table) { - if(count($parts= explode('.', $table)) > 1) + if(count($parts= explode('.', str_replace('"','',$table))) > 1) return array($parts[0], $parts[1]); else return array($this->getDefaultSchema(),$parts[0]); @@ -171,6 +171,7 @@ EOD; $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; diff --git a/framework/Data/Common/Pgsql/TPgsqlTableInfo.php b/framework/Data/Common/Pgsql/TPgsqlTableInfo.php index 88a56635..a2670fe0 100644 --- a/framework/Data/Common/Pgsql/TPgsqlTableInfo.php +++ b/framework/Data/Common/Pgsql/TPgsqlTableInfo.php @@ -39,7 +39,10 @@ class TPgsqlTableInfo extends TDbTableInfo */ public function getTableFullName() { - return $this->getSchemaName().'.'.$this->getTableName(); + if(($schema=$this->getSchemaName())!==null) + return $schema.'.'.$this->getTableName(); + else + $this->getTableName(); } } diff --git a/framework/Data/Common/Sqlite/TSqliteMetaData.php b/framework/Data/Common/Sqlite/TSqliteMetaData.php index 68734046..2ce46fb7 100644 --- a/framework/Data/Common/Sqlite/TSqliteMetaData.php +++ b/framework/Data/Common/Sqlite/TSqliteMetaData.php @@ -80,6 +80,7 @@ class TSqliteMetaData extends TDbMetaData $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') @@ -115,6 +116,8 @@ class TSqliteMetaData extends TDbMetaData } /** + * + * * @param string quoted table name. * @return array foreign key details. */ @@ -145,6 +148,23 @@ class TSqliteMetaData extends TDbMetaData } 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 NOT NULL CONSTRAINT fk_foo_id REFERENCES foo(id) ON DELETE CASCADE, + foo_id2 CHAR(2) CONSTRAINT fk_foo_id REFERENCES foo(id2) ON DELETE CASCADE, + bar_id INTEGER NOT NULL CONSTRAINT fk_bar_id REFERENCES bar(id) +); +*/ + ?> \ No newline at end of file diff --git a/framework/Data/Common/Sqlite/TSqliteTableColumn.php b/framework/Data/Common/Sqlite/TSqliteTableColumn.php index cb379bfd..b8287218 100644 --- a/framework/Data/Common/Sqlite/TSqliteTableColumn.php +++ b/framework/Data/Common/Sqlite/TSqliteTableColumn.php @@ -53,7 +53,7 @@ class TSqliteTableColumn extends TDbTableColumn /** * @return boolean true if auto increment is true. */ - public function getHasSequence() + public function hasSequence() { return $this->getAutoIncrement(); } diff --git a/framework/Data/Common/TDbCommandBuilder.php b/framework/Data/Common/TDbCommandBuilder.php index 2a8f93ee..2c3578f0 100644 --- a/framework/Data/Common/TDbCommandBuilder.php +++ b/framework/Data/Common/TDbCommandBuilder.php @@ -66,6 +66,20 @@ class TDbCommandBuilder extends TComponent 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. @@ -122,6 +136,8 @@ class TDbCommandBuilder extends TComponent */ public function createFindCommand($where='1=1', $parameters=array(), $ordering=array(), $limit=-1, $offset=-1) { + if($where===null) + $where='1=1'; $table = $this->getTableInfo()->getTableFullName(); $sql = "SELECT * FROM {$table} WHERE {$where}"; if(count($ordering) > 0) @@ -141,6 +157,8 @@ class TDbCommandBuilder extends TComponent */ public function createCountCommand($where='1=1', $parameters=array(),$ordering=array(), $limit=-1, $offset=-1) { + if($where===null) + $where='1=1'; $table = $this->getTableInfo()->getTableFullName(); $sql = "SELECT COUNT(*) FROM {$table} WHERE {$where}"; if(count($ordering) > 0) diff --git a/framework/Data/Common/TDbMetaData.php b/framework/Data/Common/TDbMetaData.php index 477e2805..6838d7dc 100644 --- a/framework/Data/Common/TDbMetaData.php +++ b/framework/Data/Common/TDbMetaData.php @@ -64,9 +64,12 @@ abstract class TDbMetaData extends TComponent case 'sqlite2': //sqlite 2 Prado::using('System.Data.Common.Sqlite.TSqliteMetaData'); return new TSqliteMetaData($conn); - case 'ibm': - Prado::using('System.Data.Common.IbmDb2.TIbmDb2MetaData'); - return new TIbmDb2MetaData($conn); + case 'mssql': + Prado::using('System.Data.Common.Mssql.TMssqlMetaData'); + return new TMssqlMetaData($conn); +// case 'ibm': +// Prado::using('System.Data.Common.IbmDb2.TIbmDb2MetaData'); +// return new TIbmDb2MetaData($conn); default: throw new TDbException('ar_invalid_database_driver',$driver); } diff --git a/framework/Data/Common/TDbTableColumn.php b/framework/Data/Common/TDbTableColumn.php index c5c9ac80..6ff2ff46 100644 --- a/framework/Data/Common/TDbTableColumn.php +++ b/framework/Data/Common/TDbTableColumn.php @@ -83,6 +83,14 @@ class TDbTableColumn extends TComponent 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. */ @@ -92,7 +100,7 @@ class TDbTableColumn extends TComponent } /** - * @return integer zero-based ordinal position of the column in the table. + * @return integer one-based ordinal position of the column in the table. */ public function getColumnIndex() { @@ -139,6 +147,15 @@ class TDbTableColumn extends TComponent 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. */ @@ -170,6 +187,14 @@ class TDbTableColumn extends TComponent { return $this->getSequenceName()!==null; } + + /** + * @return boolean whether this column is excluded from insert and update. + */ + public function getIsExcluded() + { + return false; + } } ?> \ No newline at end of file -- cgit v1.2.3