summaryrefslogtreecommitdiff
path: root/framework/Data/Common/Oracle
diff options
context:
space:
mode:
Diffstat (limited to 'framework/Data/Common/Oracle')
-rw-r--r--framework/Data/Common/Oracle/TOracleCommandBuilder.php44
-rw-r--r--framework/Data/Common/Oracle/TOracleMetaData.php152
-rw-r--r--framework/Data/Common/Oracle/TOracleTableColumn.php2
-rw-r--r--framework/Data/Common/Oracle/TOracleTableInfo.php148
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;
}
}
generated by cgit v1.2.3 (git 2.46.0) at 2024-11-01 13:26:51 +0000