diff options
Diffstat (limited to 'framework/db/schema/oci')
| -rw-r--r-- | framework/db/schema/oci/COciColumnSchema.php | 66 | ||||
| -rw-r--r-- | framework/db/schema/oci/COciCommandBuilder.php | 125 | ||||
| -rw-r--r-- | framework/db/schema/oci/COciSchema.php | 350 | ||||
| -rw-r--r-- | framework/db/schema/oci/COciTableSchema.php | 25 |
4 files changed, 566 insertions, 0 deletions
diff --git a/framework/db/schema/oci/COciColumnSchema.php b/framework/db/schema/oci/COciColumnSchema.php new file mode 100644 index 0000000..bad57c3 --- /dev/null +++ b/framework/db/schema/oci/COciColumnSchema.php @@ -0,0 +1,66 @@ +<?php +/** + * COciColumnSchema class file. + * + * @author Ricardo Grana <rickgrana@yahoo.com.br> + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2011 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * COciColumnSchema class describes the column meta data of a Oracle table. + * + * @author Ricardo Grana <rickgrana@yahoo.com.br> + * @version $Id: COciColumnSchema.php 3515 2011-12-28 12:29:24Z mdomba $ + * @package system.db.schema.oci + */ +class COciColumnSchema extends CDbColumnSchema +{ + /** + * Extracts the PHP type from DB type. + * @param string $dbType DB type + * @return string + */ + protected function extractOraType($dbType){ + if(strpos($dbType,'FLOAT')!==false) return 'double'; + + if (strpos($dbType,'NUMBER')!==false || strpos($dbType,'INTEGER')!==false) + { + if(strpos($dbType,'(') && preg_match('/\((.*)\)/',$dbType,$matches)) + { + $values=explode(',',$matches[1]); + if(isset($values[1]) and (((int)$values[1]) > 0)) + return 'double'; + else + return 'integer'; + } + else + return 'double'; + } + else + return 'string'; + } + + /** + * Extracts the PHP type from DB type. + * @param string $dbType DB type + */ + protected function extractType($dbType) + { + $this->type=$this->extractOraType($dbType); + } + + /** + * Extracts the default value for the column. + * The value is typecasted to correct PHP type. + * @param mixed $defaultValue the default value obtained from metadata + */ + protected function extractDefault($defaultValue) + { + if(stripos($defaultValue,'timestamp')!==false) + $this->defaultValue=null; + else + parent::extractDefault($defaultValue); + } +} diff --git a/framework/db/schema/oci/COciCommandBuilder.php b/framework/db/schema/oci/COciCommandBuilder.php new file mode 100644 index 0000000..4c041be --- /dev/null +++ b/framework/db/schema/oci/COciCommandBuilder.php @@ -0,0 +1,125 @@ +<?php +/** + * COciCommandBuilder class file. + * + * @author Ricardo Grana <rickgrana@yahoo.com.br> + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2011 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * COciCommandBuilder provides basic methods to create query commands for tables. + * + * @author Ricardo Grana <rickgrana@yahoo.com.br> + * @version $Id: COciCommandBuilder.php 3515 2011-12-28 12:29:24Z mdomba $ + * @package system.db.schema.oci + */ +class COciCommandBuilder extends CDbCommandBuilder +{ + /** + * @var integer the last insertion ID + */ + public $returnID; + + /** + * Returns the last insertion ID for the specified table. + * @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string). + * @return mixed last insertion id. Null is returned if no sequence name. + */ + public function getLastInsertID($table) + { + return $this->returnID; + } + + /** + * Alters the SQL to apply LIMIT and OFFSET. + * Default implementation is applicable for PostgreSQL, MySQL and SQLite. + * @param string $sql SQL query string without LIMIT and OFFSET. + * @param integer $limit maximum number of rows, -1 to ignore limit. + * @param integer $offset row offset, -1 to ignore offset. + * @return string SQL with LIMIT and OFFSET + */ + public function applyLimit($sql,$limit,$offset) + { + if (($limit < 0) and ($offset < 0)) return $sql; + + $filters = array(); + if($offset>0){ + $filters[] = 'rowNumId > '.(int)$offset; + } + + if($limit>=0){ + $filters[]= 'rownum <= '.(int)$limit; + } + + if (count($filters) > 0){ + $filter = implode(' and ', $filters); + $filter= " WHERE ".$filter; + }else{ + $filter = ''; + } + + + $sql = <<<EOD + WITH USER_SQL AS ({$sql}), + PAGINATION AS (SELECT USER_SQL.*, rownum as rowNumId FROM USER_SQL) + SELECT * + FROM PAGINATION + {$filter} +EOD; + + return $sql; + } + + /** + * Creates an INSERT command. + * @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string). + * @param array $data data to be inserted (column name=>column value). If a key is not a valid column name, the corresponding value will be ignored. + * @return CDbCommand insert command + */ + public function createInsertCommand($table,$data) + { + $this->ensureTable($table); + $fields=array(); + $values=array(); + $placeholders=array(); + $i=0; + foreach($data as $name=>$value) + { + if(($column=$table->getColumn($name))!==null && ($value!==null || $column->allowNull)) + { + $fields[]=$column->rawName; + if($value instanceof CDbExpression) + { + $placeholders[]=$value->expression; + foreach($value->params as $n=>$v) + $values[$n]=$v; + } + else + { + $placeholders[]=self::PARAM_PREFIX.$i; + $values[self::PARAM_PREFIX.$i]=$column->typecast($value); + $i++; + } + } + } + + $sql="INSERT INTO {$table->rawName} (".implode(', ',$fields).') VALUES ('.implode(', ',$placeholders).')'; + + if(is_string($table->primaryKey) && ($column=$table->getColumn($table->primaryKey))!==null && $column->type!=='string') + { + $sql.=' RETURNING '.$column->rawName.' INTO :RETURN_ID'; + $command=$this->getDbConnection()->createCommand($sql); + $command->bindParam(':RETURN_ID', $this->returnID, PDO::PARAM_INT, 12); + $table->sequenceName='RETURN_ID'; + } + else + $command=$this->getDbConnection()->createCommand($sql); + + foreach($values as $name=>$value) + $command->bindValue($name,$value); + + return $command; + } +}
\ No newline at end of file diff --git a/framework/db/schema/oci/COciSchema.php b/framework/db/schema/oci/COciSchema.php new file mode 100644 index 0000000..90ccaa9 --- /dev/null +++ b/framework/db/schema/oci/COciSchema.php @@ -0,0 +1,350 @@ +<?php +/** + * COciSchema class file. + * + * @author Ricardo Grana <rickgrana@yahoo.com.br> + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2011 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * COciSchema is the class for retrieving metadata information from an Oracle database. + * + * @property string $defaultSchema Default schema. + * + * @author Ricardo Grana <rickgrana@yahoo.com.br> + * @version $Id: COciSchema.php 3515 2011-12-28 12:29:24Z mdomba $ + * @package system.db.schema.oci + */ +class COciSchema extends CDbSchema +{ + private $_defaultSchema = ''; + + /** + * @var array the abstract column types mapped to physical column types. + * @since 1.1.6 + */ + public $columnTypes=array( + 'pk' => 'NUMBER(10) NOT NULL PRIMARY KEY', + 'string' => 'VARCHAR2(255)', + 'text' => 'CLOB', + 'integer' => 'NUMBER(10)', + 'float' => 'NUMBER', + 'decimal' => 'NUMBER', + 'datetime' => 'TIMESTAMP', + 'timestamp' => 'TIMESTAMP', + 'time' => 'TIMESTAMP', + 'date' => 'DATE', + 'binary' => 'BLOB', + 'boolean' => 'NUMBER(1)', + 'money' => 'NUMBER(19,4)', + ); + + /** + * Quotes a table name for use in a query. + * A simple table name does not schema prefix. + * @param string $name table name + * @return string the properly quoted table name + * @since 1.1.6 + */ + public function quoteSimpleTableName($name) + { + return '"'.$name.'"'; + } + + /** + * Quotes a column name for use in a query. + * A simple column name does not contain prefix. + * @param string $name column name + * @return string the properly quoted column name + * @since 1.1.6 + */ + public function quoteSimpleColumnName($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 CDbCommandBuilder command builder instance + */ + protected function createCommandBuilder() + { + return new COciCommandBuilder($this); + } + + /** + * @param string $schema 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 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]); + } + + /** + * Loads the metadata for the specified table. + * @param string $name table name + * @return CDbTableSchema driver dependent table metadata. + */ + protected function loadTable($name) + { + $table=new COciTableSchema; + $this->resolveTableNames($table,$name); + + if(!$this->findColumns($table)) + return null; + $this->findConstraints($table); + + return $table; + } + + /** + * Generates various kinds of table names. + * @param COciTableSchema $table the table instance + * @param string $name 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 COciTableSchema $table the table metadata + * @return boolean whether the table exists in the database + */ + protected function findColumns($table) + { + $schemaName=$table->schemaName; + $tableName=$table->name; + + $sql=<<<EOD +SELECT a.column_name, a.data_type || + case + when data_precision is not null + then '(' || a.data_precision || + case when a.data_scale > 0 then ',' || a.data_scale else '' end + || ')' + when data_type = 'DATE' then '' + when data_type = 'NUMBER' 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' or b.object_type = 'VIEW') + 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; + $table->sequenceName=''; + $c->autoIncrement=true; + } + } + return true; + } + + /** + * Creates a table column. + * @param array $column column metadata + * @return CDbColumnSchema normalized column metadata + */ + protected function createColumn($column) + { + $c=new COciColumnSchema; + $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 COciTableSchema $table the table metadata + */ + protected function findConstraints($table) + { + $sql=<<<EOD + SELECT D.constraint_type as CONSTRAINT_TYPE, C.COLUMN_NAME, C.position, D.r_constraint_name, + E.table_name as table_ref, f.column_name as column_ref, + C.table_name + FROM ALL_CONS_COLUMNS C + inner join ALL_constraints D on D.OWNER = C.OWNER and D.constraint_name = C.constraint_name + left join ALL_constraints E on E.OWNER = D.r_OWNER and E.constraint_name = D.r_constraint_name + left join ALL_cons_columns F on F.OWNER = E.OWNER and F.constraint_name = E.constraint_name and F.position = c.position + WHERE C.OWNER = '{$table->schemaName}' + and C.table_name = '{$table->name}' + and D.constraint_type <> '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"], $row["COLUMN_REF"]); + if(isset($table->columns[$name])) + $table->columns[$name]->isForeignKey=true; + } + + } + } + + /** + * Returns all table names in the database. + * @param string $schema the schema of the tables. Defaults to empty string, meaning the current or default schema. + * If not empty, the returned table names will be prefixed with the schema name. + * @return array all table names in the database. + */ + protected function findTableNames($schema='') + { + if($schema==='') + { + $sql=<<<EOD +SELECT table_name, '{$schema}' as table_schema FROM user_tables +EOD; + $command=$this->getDbConnection()->createCommand($sql); + } + else + { + $sql=<<<EOD +SELECT object_name as table_name, owner as table_schema FROM all_objects +WHERE object_type = 'TABLE' AND owner=:schema +EOD; + $command=$this->getDbConnection()->createCommand($sql); + $command->bindParam(':schema',$schema); + } + + $rows=$command->queryAll(); + $names=array(); + foreach($rows as $row) + { + if($schema===$this->getDefaultSchema() || $schema==='') + $names[]=$row['TABLE_NAME']; + else + $names[]=$row['TABLE_SCHEMA'].'.'.$row['TABLE_NAME']; + } + return $names; + } + + /** + * Builds a SQL statement for renaming a DB table. + * @param string $table the table to be renamed. The name will be properly quoted by the method. + * @param string $newName the new table name. The name will be properly quoted by the method. + * @return string the SQL statement for renaming a DB table. + * @since 1.1.6 + */ + public function renameTable($table, $newName) + { + return 'ALTER TABLE ' . $this->quoteTableName($table) . ' RENAME TO ' . $this->quoteTableName($newName); + } + + /** + * Builds a SQL statement for changing the definition of a column. + * @param string $table the table whose column is to be changed. The table name will be properly quoted by the method. + * @param string $column the name of the column to be changed. The name will be properly quoted by the method. + * @param string $type the new column type. The {@link getColumnType} method will be invoked to convert abstract column type (if any) + * into the physical one. Anything that is not recognized as abstract type will be kept in the generated SQL. + * For example, 'string' will be turned into 'varchar(255)', while 'string not null' will become 'varchar(255) not null'. + * @return string the SQL statement for changing the definition of a column. + * @since 1.1.6 + */ + public function alterColumn($table, $column, $type) + { + $type=$this->getColumnType($type); + $sql='ALTER TABLE ' . $this->quoteTableName($table) . ' MODIFY ' + . $this->quoteColumnName($column) . ' ' + . $this->getColumnType($type); + return $sql; + } + + /** + * Builds a SQL statement for dropping an index. + * @param string $name the name of the index to be dropped. The name will be properly quoted by the method. + * @param string $table the table whose index is to be dropped. The name will be properly quoted by the method. + * @return string the SQL statement for dropping an index. + * @since 1.1.6 + */ + public function dropIndex($name, $table) + { + return 'DROP INDEX '.$this->quoteTableName($name); + } +} diff --git a/framework/db/schema/oci/COciTableSchema.php b/framework/db/schema/oci/COciTableSchema.php new file mode 100644 index 0000000..8892745 --- /dev/null +++ b/framework/db/schema/oci/COciTableSchema.php @@ -0,0 +1,25 @@ +<?php +/** + * COciTableSchema class file. + * + * @author Ricardo Grana <rickgrana@yahoo.com.br> + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2011 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +/** + * COciTableSchema represents the metadata for a Oracle table. + * + * @author Ricardo Grana <rickgrana@yahoo.com.br> + * @version $Id: COciTableSchema.php 3515 2011-12-28 12:29:24Z mdomba $ + * @package system.db.schema.oci + */ +class COciTableSchema extends CDbTableSchema +{ + /** + * @var string name of the schema (database) that this table belongs to. + * Defaults to null, meaning no schema (or the current database). + */ + public $schemaName; +} |