fixed #676

4 files changed, 254 insertions, 92 deletions

diff --git a/framework/Data/Common/Oracle/TOracleCommandBuilder.php b/framework/Data/Common/Oracle/TOracleCommandBuilder.php
index 732f5a09..dc9b91db 100644
--- a/framework/Data/Common/Oracle/TOracleCommandBuilder.php
+++ b/framework/Data/Common/Oracle/TOracleCommandBuilder.php
@@ -41,7 +41,6 @@ class TOracleCommandBuilder extends TDbCommandBuilder
 		}
 		return parent::getSearchExpression($columns, $keywords);
 	}
-
 	/**
 	 *
 	 * @return boolean true if column can be used for LIKE searching.
@@ -54,6 +53,26 @@ class TOracleCommandBuilder extends TDbCommandBuilder
 	}
 
 	/**
+	 * 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.
+	 */
+	 /*
+	 *
+	 *	how Oracle don't implements ILIKE, this method won't be overrided
+	 *
+	protected function getSearchCondition($column, $words)
+	{
+		$conditions=array();
+		foreach($words as $word)
+			$conditions[] = $column.' LIKE '.$this->getDbConnection()->quoteString('%'.$word.'%');
+		return '('.implode(' AND ', $conditions).')';
+	}
+	*/
+
+
+	/**
 	 * Overrides parent implementation to use Oracle way of get paginated RecordSet instead of using LIMIT sql clause.
 	 * @param string SQL query string.
 	 * @param integer maximum number of rows, -1 to ignore limit.
@@ -64,7 +83,7 @@ class TOracleCommandBuilder extends TDbCommandBuilder
 	{
 		if( (int)$limit <= 0 && (int)$offset <= 0 )
 			return $sql;
-
+	
 		$pradoNUMLIN = 'pradoNUMLIN';
 		$fieldsALIAS = 'xyz';
 
@@ -73,19 +92,6 @@ class TOracleCommandBuilder extends TDbCommandBuilder
 		$niniDoSelect= strpos($sql,'SELECT')+6;
 		$nfimDoSelect= ( strpos($sql,'FROM') !== false ? strpos($sql,'FROM') : $nfimDaSQL );
 
-
-		$niniDoWhere= strpos($sql,'WHERE')+5;
-
-		$WhereConstraint=substr( $sql, $niniDoWhere, $nfimDoWhere-$niniDoWhere );
-
-		$WhereInSubSelect="";
-		$WhereMainSelect="";
-		if(trim($WhereConstraint)!=="")
-		{
-			$WhereInSubSelect="WHERE ".$WhereConstraint;
-			$WhereMainSelect="AND ".$WhereConstraint;
-		}
-
 		$sORDERBY	 = '';
 		if( stripos($sql,'ORDER') !== false ) {
 			$p = stripos($sql,'ORDER');
@@ -110,7 +116,6 @@ class TOracleCommandBuilder extends TDbCommandBuilder
 		} else {
 			if( strpos( $fields, ',' ) !== false )
 			{
-				$arr= $this->getTableInfo()->getColumns();
 				foreach( $arr as $field )
 				{
 					$field = strtolower( $field );
@@ -133,7 +138,7 @@ class TOracleCommandBuilder extends TDbCommandBuilder
 				  ") WHERE {$pradoNUMLIN} >= {$offset} ";
 
 		************************* */
-		$toReg = $offset + $limit-1;
+		$toReg = $offset + $limit;
 		$fullTableName = $this->getTableInfo()->getTableFullName();
 		if( empty($sORDERBY) )
 		{
@@ -147,11 +152,10 @@ class TOracleCommandBuilder extends TDbCommandBuilder
 			$newSql = " SELECT $fields FROM " .
 					  "(					" .
 					  "		SELECT ROW_NUMBER() OVER ( ORDER BY {$sORDERBY} ) as {$pradoNUMLIN} {$aliasedFields} " .
-					  "		FROM {$fullTableName} {$fieldsALIAS} $WhereInSubSelect" .
+					  "		FROM {$fullTableName} {$fieldsALIAS}" .
 					  ") nn					" .
-					  " WHERE nn.{$pradoNUMLIN} >= {$offset} AND nn.{$pradoNUMLIN} <= {$toReg} $WhereMainSelect" ;
+					  " WHERE nn.{$pradoNUMLIN} >= {$offset} AND nn.{$pradoNUMLIN} <= {$toReg} " ;
 		}
-
 		return $newSql;
 	}
 
diff --git a/framework/Data/Common/Oracle/TOracleMetaData.php b/framework/Data/Common/Oracle/TOracleMetaData.php
index a09ec7ee..e5ad3fe5 100644
--- a/framework/Data/Common/Oracle/TOracleMetaData.php
+++ b/framework/Data/Common/Oracle/TOracleMetaData.php
@@ -10,11 +10,9 @@
  * @package System.Data.Common.Oracle
  */
 
-/**
- * Load the base TDbMetaData class.
- */
-Prado::using('System.Data.Common.TDbMetaData');
+
 Prado::using('System.Data.Common.Oracle.TOracleTableInfo');
+Prado::using('System.Data.Common.Oracle.TOracleTableColumn');
 
 /**
  * TOracleMetaData loads Oracle database table and column information.
@@ -22,11 +20,29 @@ Prado::using('System.Data.Common.Oracle.TOracleTableInfo');
  * @author Marcos Nobre <marconobre[at]gmail[dot]com>
  * @version $Id$
  * @package System.Data.Common.Oracle
- * @since 3.1.1
+ * @since 3.1
  */
-class TOracleMetaData extends TDbMetaData
+class TOracleMetaData extends TComponent
 {
-	private $_defaultSchema = 'public';
+	private $_tableInfoCache=array();
+	private $_connection;
+	private $_defaultSchema = 'system';
+
+	/**
+	 * @param TDbConnection database connection.
+	 */
+	public function __construct($conn)
+	{
+		$this->_connection=$conn;
+	}
+
+	/**
+	 * @return TDbConnection database connection.
+	 */
+	public function getDbConnection()
+	{
+		return $this->_connection;
+	}
 
 	/**
 	 * @return string TDbTableInfo class name.
@@ -53,15 +69,32 @@ class TOracleMetaData extends TDbMetaData
 	}
 
 	/**
+	 * 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];
+	}
+
+	/**
 	 * @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]);
+			return array($parts[0], $parts[1]);
 		else
-		return array($this->getDefaultSchema(),$parts[0]);
+			return array($this->getDefaultSchema(),$parts[0]);
 	}
 
 	/**
@@ -77,24 +110,25 @@ class TOracleMetaData extends TDbMetaData
 		// The subquery to get that field checks to see if there is an internally dependent
 		// sequence on the field.
 		$sql =
-		<<<EOD
+<<<EOD
 		SELECT
 			a.COLUMN_ID,
-			a.COLUMN_NAME as attname,
+			LOWER(a.COLUMN_NAME) as attname,
 			a.DATA_TYPE || DECODE( a.DATA_TYPE, 'NUMBER', '('||a.DATA_PRECISION||','||DATA_SCALE||')' , '') as type,
 			a.DATA_LENGTH as atttypmod,
-			DECODE(a.NULLABLE, 'Y', '1', '0') as attnotnull, 
-			DECODE(a.DEFAULT_LENGTH, NULL, '0', '1') as atthasdef, 
+			DECODE(a.NULLABLE, 'Y', '1', '0') as attnotnull,
+			DECODE(a.DEFAULT_LENGTH, NULL, '0', '1') as atthasdef,
 			DATA_DEFAULT as adsrc,
 			'0' AS attisserial
 		FROM
-			ALL_TAB_COLUMNS a 
+			ALL_TAB_COLUMNS a
 		WHERE
-			TABLE_NAME = '{$tableName}' 
+			TABLE_NAME = '{$tableName}'
 			AND OWNER = '{$schemaName}'
 		ORDER BY a.COLUMN_ID
 EOD;
 		$this->getDbConnection()->setActive(true);
+		$this->getDbConnection()->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER);
 		$command = $this->getDbConnection()->createCommand($sql);
 		//$command->bindValue(':table', $tableName);
 		//$command->bindValue(':schema', $schemaName);
@@ -106,7 +140,7 @@ EOD;
 			$this->processColumn($tableInfo, $col);
 		}
 		if($index===0)
-		throw new TDbException('dbmetadata_invalid_table_view', $table);
+			throw new TDbException('dbmetadata_invalid_table_view', $table);
 		return $tableInfo;
 	}
 
@@ -118,7 +152,8 @@ EOD;
 	protected function createNewTableInfo($schemaName,$tableName)
 	{
 		$info['SchemaName'] = $this->assertIdentifier($schemaName);
-		$info['TableName'] = $this->assertIdentifier($tableName);
+		$info['TableName']	= $this->assertIdentifier($tableName);
+		$info['IsView'] 	= false;
 		if($this->getIsView($schemaName,$tableName)) $info['IsView'] = true;
 		list($primary, $foreign) = $this->getConstraintKeys($schemaName, $tableName);
 		$class = $this->getTableInfoClass();
@@ -134,7 +169,7 @@ EOD;
 	{
 		if(strpos($name, '"')!==false)
 		{
-			$ref = 'http://www.postgresql.org/docs/7.4/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS';
+			$ref = 'http://www.oracle.com';
 			throw new TDbException('dbcommon_invalid_identifier_name', $name, $ref);
 		}
 		return $name;
@@ -147,9 +182,10 @@ EOD;
 	 */
 	protected function getIsView($schemaName,$tableName)
 	{
+		$this->getDbConnection()->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER);
 		$sql =
-		<<<EOD
-		select	OBJECT_TYPE 
+<<<EOD
+		select	OBJECT_TYPE
 		from 	ALL_OBJECTS
 		where	OBJECT_NAME = '{$tableName}'
 		and 	OWNER = '{$schemaName}'
@@ -167,9 +203,10 @@ EOD;
 	 */
 	protected function processColumn($tableInfo, $col)
 	{
-		$columnId = $col['attname']; //use column name as column Id
+		$columnId = strtolower($col['attname']); //use column name as column Id
 
-		$info['ColumnName'] 	= '"'.$columnId.'"'; //quote the column names!
+		//$info['ColumnName'] 	= '"'.$columnId.'"'; //quote the column names!
+		$info['ColumnName'] 	= $columnId; //NOT quote the column names!
 		$info['ColumnId'] 		= $columnId;
 		$info['ColumnIndex'] 	= $col['index'];
 		if(! (bool)$col['attnotnull'] ) $info['AllowNull'] = true;
@@ -177,7 +214,19 @@ EOD;
 		if($this->isForeignKeyColumn($columnId, $tableInfo)) $info['IsForeignKey'] = true;
 		if( (int)$col['atttypmod'] > 0 ) $info['ColumnSize'] =  $col['atttypmod']; // - 4;
 		if( (bool)$col['atthasdef'] ) $info['DefaultValue'] = $col['adsrc'];
-		
+		//
+		// For a while Oracle Tables has no  associated AutoIncrement Triggers
+		//
+		/*
+		if( $col['attisserial'] )
+		{
+			if(($sequence = $this->getSequenceName($tableInfo, $col['adsrc']))!==null)
+			{
+				$info['SequenceName'] = $sequence;
+				unset($info['DefaultValue']);
+			}
+		}
+		*/
 		$matches = array();
 		if(preg_match('/\((\d+)(?:,(\d+))?+\)/', $col['type'], $matches))
 		{
@@ -186,14 +235,13 @@ EOD;
 			{
 				$info['NumericPrecision'] = intval($matches[1]);
 				if(count($matches) > 2)
-				$info['NumericScale'] = intval($matches[2]);
+					$info['NumericScale'] = intval($matches[2]);
 			}
 			else
-			$info['ColumnSize'] = intval($matches[1]);
+				$info['ColumnSize'] = intval($matches[1]);
 		}
 		else
-		$info['DbType'] = $col['type'];
-
+			$info['DbType'] = $col['type'];
 		$tableInfo->Columns[$columnId] = new TOracleTableColumn($info);
 	}
 
@@ -206,9 +254,9 @@ EOD;
 		if(preg_match('/nextval\([^\']*\'([^\']+)\'[^\)]*\)/i',$src,$matches))
 		{
 			if(is_int(strpos($matches[1], '.')))
-			return $matches[1];
+				return $matches[1];
 			else
-			return $tableInfo->getSchemaName().'.'.$matches[1];
+				return $tableInfo->getSchemaName().'.'.$matches[1];
 		}
 	}
 
@@ -218,7 +266,7 @@ EOD;
 	protected function isPrecisionType($type)
 	{
 		$type = strtolower(trim($type));
-		return $type==='number'; 
+		return $type==='number'; // || $type==='interval' || strpos($type, 'time')===0;
 	}
 
 	/**
@@ -229,17 +277,18 @@ EOD;
 	 */
 	protected function getConstraintKeys($schemaName, $tableName)
 	{
+		$this->getDbConnection()->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER);
+//		select decode( a.CONSTRAINT_TYPE, 'P', 'PRIMARY KEY (', 'FOREIGN KEY (' )||b.COLUMN_NAME||')' as consrc,
 		$sql =
-		<<<EOD
-				select decode( a.CONSTRAINT_TYPE, 'P', 'PRIMARY KEY (', 'FOREIGN KEY (' )||b.COLUMN_NAME||')' as consrc, 
-					   a.CONSTRAINT_TYPE as contype
-				from ALL_CONSTRAINTS a, ALL_CONS_COLUMNS b
-				where ( a.constraint_name = b.constraint_name )
-				and	  a.TABLE_NAME = '{$tableName}' 
-				and   a.OWNER = '{$schemaName}'
-				and   a.CONSTRAINT_TYPE in ('P','R')
+<<<EOD
+		select b.COLUMN_NAME as consrc,
+			   a.CONSTRAINT_TYPE as contype
+		from ALL_CONSTRAINTS a, ALL_CONS_COLUMNS b
+ 		where (a.constraint_name = b.constraint_name AND a.table_name = b.table_name AND a.owner = b.owner)
+		and	  a.TABLE_NAME = '{$tableName}'
+		and   a.OWNER = '{$schemaName}'
+		and   a.CONSTRAINT_TYPE in ('P','R')
 EOD;
-		
 		$this->getDbConnection()->setActive(true);
 		$command = $this->getDbConnection()->createCommand($sql);
 		//$command->bindValue(':table', $tableName);
@@ -251,11 +300,19 @@ EOD;
 			switch( strtolower( $row['contype'] ) )
 			{
 				case 'p':
-					$primary = $this->getPrimaryKeys($row['consrc']);
+					$primary = array_merge( $primary, array(strtolower( $row['consrc'] )) );
+					/*
+					$arr = $this->getPrimaryKeys($row['consrc']);
+					$primary = array_merge( $primary, array(strtolower( $arr[0] )) );
+					*/
 					break;
-				case 'f':
-					if(($fkey = $this->getForeignKeys($row['consrc']))!==null)
-					$foreign[] = $fkey;
+				case 'r':
+					$foreign = array_merge( $foreign, array(strtolower( $row['consrc'] )) );
+					/*
+					// if(($fkey = $this->getForeignKeys($row['consrc']))!==null)
+					$fkey = $this->getForeignKeys( $row['consrc'] );
+					$foreign = array_merge( $foreign, array(strtolower( $fkey )) );
+					*/
 					break;
 			}
 		}
@@ -271,7 +328,7 @@ EOD;
 	{
 		$matches = array();
 		if(preg_match('/PRIMARY\s+KEY\s+\(([^\)]+)\)/i', $src, $matches))
-		return preg_split('/,\s+/',$matches[1]);
+			return preg_split('/,\s+/',$matches[1]);
 		return array();
 	}
 
@@ -290,7 +347,7 @@ EOD;
 			$keys = preg_split('/,\s+/', $matches[1]);
 			$fkeys = array();
 			foreach(preg_split('/,\s+/', $matches[3]) as $i => $fkey)
-			$fkeys[$keys[$i]] = $fkey;
+				$fkeys[$keys[$i]] = $fkey;
 			return array('table' => str_replace('"','',$matches[2]), 'keys' => $fkeys);
 		}
 	}
@@ -304,8 +361,9 @@ EOD;
 	{
 		foreach($tableInfo->getForeignKeys() as $fk)
 		{
-			if(in_array($columnId, array_keys($fk['keys'])))
-			return true;
+			if( $fk==$columnId )
+			//if(in_array($columnId, array_keys($fk['keys'])))
+				return true;
 		}
 		return false;
 	}
diff --git a/framework/Data/Common/Oracle/TOracleTableColumn.php b/framework/Data/Common/Oracle/TOracleTableColumn.php
index 6e387cfb..4cfed5b6 100644
--- a/framework/Data/Common/Oracle/TOracleTableColumn.php
+++ b/framework/Data/Common/Oracle/TOracleTableColumn.php
@@ -21,7 +21,7 @@ Prado::using('System.Data.Common.TDbTableColumn');
  * @author Marcos Nobre <marconobre[at]gmail[dot]com>
  * @version $Id$
  * @package System.Data.Common.Oracle
- * @since 3.1.1
+ * @since 3.1
  */
 class TOracleTableColumn extends TDbTableColumn
 {
diff --git a/framework/Data/Common/Oracle/TOracleTableInfo.php b/framework/Data/Common/Oracle/TOracleTableInfo.php
index 0f163df3..b3c1ff0a 100644
--- a/framework/Data/Common/Oracle/TOracleTableInfo.php
+++ b/framework/Data/Common/Oracle/TOracleTableInfo.php
@@ -1,4 +1,5 @@
 <?php
+
 /**
  * TOracleTableInfo class file.
  *
@@ -7,31 +8,75 @@
  * @copyright Copyright &copy; 2005-2007 PradoSoft
  * @license http://www.pradosoft.com/license/
  * @version $Id$
- * @package System.Data.Common.Oracle
+ * @package System.Data.Common
  */
 
 /**
- * Loads the base TDbTableInfo class and TOracleTableColumn class.
- */
-Prado::using('System.Data.Common.TDbTableInfo');
-Prado::using('System.Data.Common.Oracle.TOracleTableColumn');
-
-/**
- * TOracleTableInfo class provides additional table information for ORACLE database.
+ * TDbTableInfo class describes the meta data of a database table.
  *
- * @author Marcos Nobre <marconobre[at]gmail[dot]com>
+ * @author Wei Zhuo <weizho[at]gmail[dot]com>
  * @version $Id$
- * @package System.Data.Common.Oracle
- * @since 3.1.1
+ * @package System.Data.Common
+ * @since 3.1
  */
-class TOracleTableInfo extends TDbTableInfo
+class TOracleTableInfo extends TComponent
 {
+	private $_info=array();
+
+	private $_primaryKeys;
+	private $_foreignKeys;
+
+	private $_columns;
+
+	private $_lowercase;
+
 	/**
-	 * @return string name of the schema this column belongs to.
+	 * Sets the database table meta data information.
+	 * @param array table column information.
 	 */
-	public function getSchemaName()
+	public function __construct($tableInfo=array(),$primary=array(),$foreign=array())
 	{
-		return $this->getInfo('SchemaName');
+		$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.Oracle.TOracleCommandBuilder');
+		return new TOracleCommandBuilder($connection,$this);
+	}
+
+	/**
+	 * @param string information array key name
+	 * @param mixed default value if information array value is null
+	 * @return mixed information array value.
+	 */
+	public 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');
 	}
 
 	/**
@@ -39,20 +84,75 @@ class TOracleTableInfo extends TDbTableInfo
 	 */
 	public function getTableFullName()
 	{
-		if(($schema=$this->getSchemaName())!==null)
-			return $schema.'.'.$this->getTableName();
-		else
-			$this->getTableName();
+		return $this->_info['SchemaName'].'.'.$this->getTableName();
 	}
 
 	/**
-	 * @param TDbConnection database connection.
-	 * @return TDbCommandBuilder new command builder
+	 * @return boolean whether the table is a view, default is false.
 	 */
-	public function createCommandBuilder($connection)
+	public function getIsView()
 	{
-		Prado::using('System.Data.Common.Oracle.TOracleCommandBuilder');
-		return new TOracleCommandBuilder($connection,$this);
+		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()
+	{
+		foreach($this->getColumns() as $column)
+			$names[] = $column->getColumnName();
+		return $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;
 	}
 }