From 3f0149b5b74d4017ba7f4ca28aa8a3e2053db964 Mon Sep 17 00:00:00 2001 From: "Christophe.Boulain" <> Date: Mon, 15 Jun 2009 07:49:42 +0000 Subject: Primilary import of new db stuff --- framework/Db/schema/oci/TOciSchema.php | 278 +++++++++++++++++++++++++++++++++ 1 file changed, 278 insertions(+) create mode 100755 framework/Db/schema/oci/TOciSchema.php (limited to 'framework/Db/schema/oci/TOciSchema.php') diff --git a/framework/Db/schema/oci/TOciSchema.php b/framework/Db/schema/oci/TOciSchema.php new file mode 100755 index 00000000..5c4dd8e2 --- /dev/null +++ b/framework/Db/schema/oci/TOciSchema.php @@ -0,0 +1,278 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.db.schema.TDbSchema'); + +/** + * TOciSchema is the class for retrieving metadata information from a PostgreSQL database. + * + * @author Ricardo Grana + * @version $Id$ + * @package system.db.schema.oci + * @since 1.0.5 + */ +class TOciSchema extends TDbSchema +{ + private $_defaultSchema = ''; + private $_sequences=array(); + + /** + * Quotes a table name for use in a query. + * @param string table name + * @return string the properly quoted table name + */ + public function quoteTableName($name) + { + return $name; + } + + /** + * Quotes a column name for use in a query. + * @param string column name + * @return string the properly quoted column name + */ + public function quoteColumnName($name) + { + return $name; + } + + /** + * Creates a command builder for the database. + * This method may be overridden by child classes to create a DBMS-specific command builder. + * @return TDbCommandBuilder command builder instance + */ + protected function createCommandBuilder() + { + return new TOciCommandBuilder($this); + } + + /** + * @param string default schema. + */ + public function setDefaultSchema($schema) + { + $this->_defaultSchema=$schema; + } + + /** + * @return string default schema. + */ + public function getDefaultSchema() + { + if (!strlen($this->_defaultSchema)) + { + $this->setDefaultSchema(strtoupper($this->getDbConnection()->username)); + } + + 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) + { + $table = strtoupper($table); + if(count($parts= explode('.', str_replace('"','',$table))) > 1) + return array($parts[0], $parts[1]); + else + return array($this->getDefaultSchema(),$parts[0]); + } + + /** + * Creates a table instance representing the metadata for the named table. + * @return TDbTableSchema driver dependent table metadata. + */ + protected function createTable($name) + { + $table=new TOciTableSchema; + $this->resolveTableNames($table,$name); + + if(!$this->findColumns($table)) + return null; + $this->findConstraints($table); + + return $table; + } + + /** + * Generates various kinds of table names. + * @param TOciTableSchema the table instance + * @param string the unquoted table name + */ + protected function resolveTableNames($table,$name) + { + $parts=explode('.',str_replace('"','',$name)); + if(isset($parts[1])) + { + $schemaName=$parts[0]; + $tableName=$parts[1]; + } + else + { + $schemaName=$this->getDefaultSchema(); + $tableName=$parts[0]; + } + + $table->name=$tableName; + $table->schemaName=$schemaName; + if($schemaName===$this->getDefaultSchema()) + $table->rawName=$this->quoteTableName($tableName); + else + $table->rawName=$this->quoteTableName($schemaName).'.'.$this->quoteTableName($tableName); + } + + /** + * Collects the table column metadata. + * @param TOciTableSchema the table metadata + * @return boolean whether the table exists in the database + */ + protected function findColumns($table) + { + list($schemaName,$tableName) = $this->getSchemaTableName($table->name); + + $sql=<< 0 then ',' || a.data_scale else '' end + || ')' + when data_type = 'DATE' then '' + else '(' || to_char(a.data_length) || ')' + end as data_type, + a.nullable, a.data_default, + ( SELECT D.constraint_type + FROM ALL_CONS_COLUMNS C + inner join ALL_constraints D on D.OWNER = C.OWNER and D.constraint_name = C.constraint_name + WHERE C.OWNER = B.OWNER + and C.table_name = B.object_name + and C.column_name = A.column_name + and D.constraint_type = 'P') as Key +FROM ALL_TAB_COLUMNS A +inner join ALL_OBJECTS B ON b.owner = a.owner and ltrim(B.OBJECT_NAME) = ltrim(A.TABLE_NAME) +WHERE + a.owner = '{$schemaName}' + and b.object_type = 'TABLE' + and b.object_name = '{$tableName}' +ORDER by a.column_id +EOD; + + $command=$this->getDbConnection()->createCommand($sql); + + if(($columns=$command->queryAll())===array()){ + return false; + } + + foreach($columns as $column) + { + $c=$this->createColumn($column); + + $table->columns[$c->name]=$c; + if($c->isPrimaryKey) + { + if($table->primaryKey===null) + $table->primaryKey=$c->name; + else if(is_string($table->primaryKey)) + $table->primaryKey=array($table->primaryKey,$c->name); + else + $table->primaryKey[]=$c->name; + } + } + return true; + } + + /** + * Creates a table column. + * @param array column metadata + * @return TDbColumnSchema normalized column metadata + */ + protected function createColumn($column) + { + $c=new TOciColumnSchema; + $c->name=$column['COLUMN_NAME']; + $c->rawName=$this->quoteColumnName($c->name); + $c->allowNull=$column['NULLABLE']==='Y'; + $c->isPrimaryKey=strpos($column['KEY'],'P')!==false; + $c->isForeignKey=false; + $c->init($column['DATA_TYPE'],$column['DATA_DEFAULT']); + + return $c; + } + + /** + * Collects the primary and foreign key column details for the given table. + * @param TOciTableSchema the table metadata + */ + protected function findConstraints($table) + { + $sql=<< 'P' + order by d.constraint_name, c.position +EOD; + $command=$this->getDbConnection()->createCommand($sql); + foreach($command->queryAll() as $row) + { + if($row['constraint_type']==='R') // foreign key + { + $name = $row["COLUMN_NAME"]; + $table->foreignKeys[$name]=array($row["TABLE_REF"], array($row["COLUMN_REF"])); + if(isset($table->columns[$name])) + $table->columns[$name]->isForeignKey=true; + } + + } + } + + + /** + * Returns all table names in the database. + * @return array all table names in the database. + */ + protected function findTableNames($schema='') + { + if($schema==='') + { + $sql=<<getDbConnection()->createCommand($sql); + } + else + { + $sql=<<getDbConnection()->createCommand($sql); + $command->bindParam(':schema',$schema); + } + + $rows=$command->queryAll(); + $names=array(); + foreach($rows as $row) + { + if($schema===$this->getDefaultSchema()) + $names[]=$row['table_name']; + else + $names[]=$row['schema_name'].'.'.$row['table_name']; + } + return $names; + } +} -- cgit v1.2.3