1: <?php
  2: /**
  3:  * COciCommandBuilder class file.
  4:  *
  5:  * @author Ricardo Grana <rickgrana@yahoo.com.br>
  6:  * @link http://www.yiiframework.com/
  7:  * @copyright 2008-2013 Yii Software LLC
  8:  * @license http://www.yiiframework.com/license/
  9:  */
 10: 
 11: /**
 12:  * COciCommandBuilder provides basic methods to create query commands for tables.
 13:  *
 14:  * @author Ricardo Grana <rickgrana@yahoo.com.br>
 15:  * @package system.db.schema.oci
 16:  */
 17: class COciCommandBuilder extends CDbCommandBuilder
 18: {
 19:     /**
 20:      * @var integer the last insertion ID
 21:      */
 22:     public $returnID;
 23: 
 24:     /**
 25:      * Returns the last insertion ID for the specified table.
 26:      * @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string).
 27:      * @return mixed last insertion id. Null is returned if no sequence name.
 28:      */
 29:     public function getLastInsertID($table)
 30:     {
 31:         return $this->returnID;
 32:     }
 33: 
 34:     /**
 35:      * Alters the SQL to apply LIMIT and OFFSET.
 36:      * @param string $sql SQL query string without LIMIT and OFFSET.
 37:      * @param integer $limit maximum number of rows, -1 to ignore limit.
 38:      * @param integer $offset row offset, -1 to ignore offset.
 39:      * @return string SQL with LIMIT and OFFSET
 40:      */
 41:     public function applyLimit($sql,$limit,$offset)
 42:     {
 43:         if (($limit < 0) and ($offset < 0)) return $sql;
 44: 
 45:         $filters = array();
 46:         if($offset>0){
 47:             $filters[] = 'rowNumId > '.(int)$offset;
 48:         }
 49: 
 50:         if($limit>=0){
 51:             $filters[]= 'rownum <= '.(int)$limit;
 52:         }
 53: 
 54:         if (count($filters) > 0){
 55:             $filter = implode(' and ', $filters);
 56:             $filter= " WHERE ".$filter;
 57:         }else{
 58:             $filter = '';
 59:         }
 60: 
 61: 
 62:         $sql = <<<EOD
 63: WITH USER_SQL AS ({$sql}),
 64:     PAGINATION AS (SELECT USER_SQL.*, rownum as rowNumId FROM USER_SQL)
 65: SELECT *
 66: FROM PAGINATION
 67: {$filter}
 68: EOD;
 69: 
 70:         return $sql;
 71:     }
 72: 
 73:     /**
 74:      * Creates an INSERT command.
 75:      * @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string).
 76:      * @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.
 77:      * @return CDbCommand insert command
 78:      */
 79:     public function createInsertCommand($table,$data)
 80:     {
 81:         $this->ensureTable($table);
 82:         $fields=array();
 83:         $values=array();
 84:         $placeholders=array();
 85:         $i=0;
 86:         foreach($data as $name=>$value)
 87:         {
 88:             if(($column=$table->getColumn($name))!==null && ($value!==null || $column->allowNull))
 89:             {
 90:                 $fields[]=$column->rawName;
 91:                 if($value instanceof CDbExpression)
 92:                 {
 93:                     $placeholders[]=$value->expression;
 94:                     foreach($value->params as $n=>$v)
 95:                         $values[$n]=$v;
 96:                 }
 97:                 else
 98:                 {
 99:                     $placeholders[]=self::PARAM_PREFIX.$i;
100:                     $values[self::PARAM_PREFIX.$i]=$column->typecast($value);
101:                     $i++;
102:                 }
103:             }
104:         }
105: 
106:         $sql="INSERT INTO {$table->rawName} (".implode(', ',$fields).') VALUES ('.implode(', ',$placeholders).')';
107: 
108:         if(is_string($table->primaryKey) && ($column=$table->getColumn($table->primaryKey))!==null && $column->type!=='string')
109:         {
110:             $sql.=' RETURNING '.$column->rawName.' INTO :RETURN_ID';
111:             $command=$this->getDbConnection()->createCommand($sql);
112:             $command->bindParam(':RETURN_ID', $this->returnID, PDO::PARAM_INT, 12);
113:             $table->sequenceName='RETURN_ID';
114:         }
115:         else
116:             $command=$this->getDbConnection()->createCommand($sql);
117: 
118:         foreach($values as $name=>$value)
119:             $command->bindValue($name,$value);
120: 
121:         return $command;
122:     }
123: 
124:     /**
125:      * Creates a multiple INSERT command.
126:      * This method could be used to achieve better performance during insertion of the large
127:      * amount of data into the database tables.
128:      * @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string).
129:      * @param array[] $data list data to be inserted, each value should be an array in format (column name=>column value).
130:      * If a key is not a valid column name, the corresponding value will be ignored.
131:      * @return CDbCommand multiple insert command
132:      * @since 1.1.14
133:      */
134:     public function createMultipleInsertCommand($table,array $data)
135:     {
136:         $templates=array(
137:             'main'=>'INSERT ALL {{rowInsertValues}} SELECT * FROM dual',
138:             'columnInsertValue'=>'{{value}}',
139:             'columnInsertValueGlue'=>', ',
140:             'rowInsertValue'=>'INTO {{tableName}} ({{columnInsertNames}}) VALUES ({{columnInsertValues}})',
141:             'rowInsertValueGlue'=>' ',
142:             'columnInsertNameGlue'=>', ',
143:         );
144:         return $this->composeMultipleInsertCommand($table,$data,$templates);
145:     }
146: }