From 78af2b4630ccde4b03659a259f739f83ae0de9ec Mon Sep 17 00:00:00 2001 From: rojaro <> Date: Mon, 13 Jul 2009 14:02:17 +0000 Subject: - ActiveRecord Port from Yii ... not perfect but works ^^ --- framework/Db/Schema/oci/TOciColumnSchema.php | 56 +++++ framework/Db/Schema/oci/TOciCommandBuilder.php | 122 +++++++++++ framework/Db/Schema/oci/TOciSchema.php | 278 +++++++++++++++++++++++++ framework/Db/Schema/oci/TOciTableSchema.php | 28 +++ 4 files changed, 484 insertions(+) create mode 100755 framework/Db/Schema/oci/TOciColumnSchema.php create mode 100755 framework/Db/Schema/oci/TOciCommandBuilder.php create mode 100755 framework/Db/Schema/oci/TOciSchema.php create mode 100755 framework/Db/Schema/oci/TOciTableSchema.php (limited to 'framework/Db/Schema/oci') diff --git a/framework/Db/Schema/oci/TOciColumnSchema.php b/framework/Db/Schema/oci/TOciColumnSchema.php new file mode 100755 index 00000000..d609d14d --- /dev/null +++ b/framework/Db/Schema/oci/TOciColumnSchema.php @@ -0,0 +1,56 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.Db.Schema.TDbColumnSchema'); + +/** + * TOciColumnSchema class describes the column meta data of a Oracle table. + * + * @author Ricardo Grana + * @version $Id: TOciColumnSchema.php + * @package System.Db.Schema.oci + * @since 1.0.5 + */ +class TOciColumnSchema extends TDbColumnSchema +{ + /** + * Extracts the PHP type from DB type. + * @param string DB type + */ + protected function extractOraType($dbType){ + if(strpos($dbType,'FLOAT')!==false) return 'double'; + + if ((strpos($dbType,'NUMBER')!==false) or + (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 'string'; + } + } + protected function extractType($dbType) + { + $this->type=$this->extractOraType($dbType); + } + + protected function extractDefault($defaultValue) + { + if(strpos($dbType,'timestamp')!==false) + $this->defaultValue=null; + else + parent::extractDefault($defaultValue); + } +} diff --git a/framework/Db/Schema/oci/TOciCommandBuilder.php b/framework/Db/Schema/oci/TOciCommandBuilder.php new file mode 100755 index 00000000..807b7271 --- /dev/null +++ b/framework/Db/Schema/oci/TOciCommandBuilder.php @@ -0,0 +1,122 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.Db.Schema.TDbCommandBuilder'); + +/** + * TOciCommandBuilder provides basic methods to create query commands for tables. + * + * @author Ricardo Grana + * @version $Id: TOciCommandBuilder.php 2679 2009-06-15 07:49:42Z Christophe.Boulain $ + * @package System.Db.Schema.oci + * @since 1.0.5 + */ +class TOciCommandBuilder extends TDbCommandBuilder +{ + /** + * @var integer the last insertion ID + */ + public $returnID; + + /** + * Returns the last insertion ID for the specified table. + * @param mixed the table schema ({@link TDbTableSchema}) 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 query string without LIMIT and OFFSET. + * @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 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 = <<column value). If a key is not a valid column name, the corresponding value will be ignored. + * @return TDbCommand insert command + */ + public function createInsertCommand($table,$data) + { + $this->ensureTable($table); + $fields=array(); + $values=array(); + $placeholders=array(); + foreach($data as $name=>$value) + { + if(($column=$table->getColumn($name))!==null && ($value!==null || $column->allowNull)) + { + $fields[]=$column->rawName; + if($value instanceof TDbExpression) + $placeholders[]=(string)$value; + else + { + $placeholders[]=':'.$name; + $values[':'.$name]=$column->typecast($value); + } + } + } + + $sql="INSERT INTO {$table->rawName} (".implode(', ',$fields).') VALUES ('.implode(', ',$placeholders).')'; + + if(is_string($table->primaryKey)) + { + $sql.=" RETURNING ".$table->primaryKey." 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; + } +} diff --git a/framework/Db/Schema/oci/TOciSchema.php b/framework/Db/Schema/oci/TOciSchema.php new file mode 100755 index 00000000..50de0626 --- /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: TOciSchema.php 2679 2009-06-15 07:49:42Z Christophe.Boulain $ + * @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; + } +} diff --git a/framework/Db/Schema/oci/TOciTableSchema.php b/framework/Db/Schema/oci/TOciTableSchema.php new file mode 100755 index 00000000..9fe73624 --- /dev/null +++ b/framework/Db/Schema/oci/TOciTableSchema.php @@ -0,0 +1,28 @@ + + * @link http://www.yiiframework.com/ + * @copyright Copyright © 2008-2009 Yii Software LLC + * @license http://www.yiiframework.com/license/ + */ + +prado::using('System.Db.Schema.TDbTableSchame'); + +/** + * TOciTableSchema represents the metadata for a Ora table. + * + * @author Ricardo Grana + * @version $Id: TOciTableSchema.php 2679 2009-06-15 07:49:42Z Christophe.Boulain $ + * @package System.Db.Schema.oci + * @since 1.0.5 + */ +class TOciTableSchema extends TDbTableSchema +{ + /** + * @var string name of the schema (database) that this table belongs to. + * Defaults to null, meaning no schema (or the current database). + */ + public $schemaName; +} -- cgit v1.2.3