1: <?php
  2: /**
  3:  * COciSchema 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:  * COciSchema is the class for retrieving metadata information from an Oracle database.
 13:  *
 14:  * @property string $defaultSchema Default schema.
 15:  *
 16:  * @author Ricardo Grana <rickgrana@yahoo.com.br>
 17:  * @package system.db.schema.oci
 18:  */
 19: class COciSchema extends CDbSchema
 20: {
 21:     private $_defaultSchema = '';
 22: 
 23:     /**
 24:      * @var array the abstract column types mapped to physical column types.
 25:      * @since 1.1.6
 26:      */
 27:     public $columnTypes=array(
 28:         'pk' => 'NUMBER(10) NOT NULL PRIMARY KEY',
 29:         'bigpk' => 'NUMBER(20) NOT NULL PRIMARY KEY',
 30:         'string' => 'VARCHAR2(255)',
 31:         'text' => 'CLOB',
 32:         'integer' => 'NUMBER(10)',
 33:         'bigint' => 'NUMBER(20)',
 34:         'float' => 'NUMBER',
 35:         'decimal' => 'NUMBER',
 36:         'datetime' => 'TIMESTAMP',
 37:         'timestamp' => 'TIMESTAMP',
 38:         'time' => 'TIMESTAMP',
 39:         'date' => 'DATE',
 40:         'binary' => 'BLOB',
 41:         'boolean' => 'NUMBER(1)',
 42:         'money' => 'NUMBER(19,4)',
 43:     );
 44: 
 45:     /**
 46:      * Quotes a table name for use in a query.
 47:      * A simple table name does not schema prefix.
 48:      * @param string $name table name
 49:      * @return string the properly quoted table name
 50:      * @since 1.1.6
 51:      */
 52:     public function quoteSimpleTableName($name)
 53:     {
 54:         return '"'.$name.'"';
 55:     }
 56: 
 57:     /**
 58:      * Quotes a column name for use in a query.
 59:      * A simple column name does not contain prefix.
 60:      * @param string $name column name
 61:      * @return string the properly quoted column name
 62:      * @since 1.1.6
 63:      */
 64:     public function quoteSimpleColumnName($name)
 65:     {
 66:         return '"'.$name.'"';
 67:     }
 68: 
 69:     /**
 70:      * Creates a command builder for the database.
 71:      * This method may be overridden by child classes to create a DBMS-specific command builder.
 72:      * @return CDbCommandBuilder command builder instance
 73:      */
 74:     protected function createCommandBuilder()
 75:     {
 76:         return new COciCommandBuilder($this);
 77:     }
 78: 
 79:     /**
 80:      * @param string $schema default schema.
 81:      */
 82:     public function setDefaultSchema($schema)
 83:     {
 84:         $this->_defaultSchema=$schema;
 85:     }
 86: 
 87:     /**
 88:      * @return string default schema.
 89:      */
 90:     public function getDefaultSchema()
 91:     {
 92:         if (!strlen($this->_defaultSchema))
 93:         {
 94:             $this->setDefaultSchema(strtoupper($this->getDbConnection()->username));
 95:         }
 96: 
 97:         return $this->_defaultSchema;
 98:     }
 99: 
100:     /**
101:      * @param string $table table name with optional schema name prefix, uses default schema name prefix is not provided.
102:      * @return array tuple as ($schemaName,$tableName)
103:      */
104:     protected function getSchemaTableName($table)
105:     {
106:         $table = strtoupper($table);
107:         if(count($parts= explode('.', str_replace('"','',$table))) > 1)
108:             return array($parts[0], $parts[1]);
109:         else
110:             return array($this->getDefaultSchema(),$parts[0]);
111:     }
112: 
113:     /**
114:      * Loads the metadata for the specified table.
115:      * @param string $name table name
116:      * @return CDbTableSchema driver dependent table metadata.
117:      */
118:     protected function loadTable($name)
119:     {
120:         $table=new COciTableSchema;
121:         $this->resolveTableNames($table,$name);
122: 
123:         if(!$this->findColumns($table))
124:             return null;
125:         $this->findConstraints($table);
126: 
127:         return $table;
128:     }
129: 
130:     /**
131:      * Generates various kinds of table names.
132:      * @param COciTableSchema $table the table instance
133:      * @param string $name the unquoted table name
134:      */
135:     protected function resolveTableNames($table,$name)
136:     {
137:         $parts=explode('.',str_replace('"','',$name));
138:         if(isset($parts[1]))
139:         {
140:             $schemaName=$parts[0];
141:             $tableName=$parts[1];
142:         }
143:         else
144:         {
145:             $schemaName=$this->getDefaultSchema();
146:             $tableName=$parts[0];
147:         }
148: 
149:         $table->name=$tableName;
150:         $table->schemaName=$schemaName;
151:         if($schemaName===$this->getDefaultSchema())
152:             $table->rawName=$this->quoteTableName($tableName);
153:         else
154:             $table->rawName=$this->quoteTableName($schemaName).'.'.$this->quoteTableName($tableName);
155:     }
156: 
157:     /**
158:      * Collects the table column metadata.
159:      * @param COciTableSchema $table the table metadata
160:      * @return boolean whether the table exists in the database
161:      */
162:     protected function findColumns($table)
163:     {
164:         $schemaName=$table->schemaName;
165:         $tableName=$table->name;
166: 
167:         $sql=<<<EOD
168: SELECT a.column_name, a.data_type ||
169:     case
170:         when data_precision is not null
171:             then '(' || a.data_precision ||
172:                     case when a.data_scale > 0 then ',' || a.data_scale else '' end
173:                 || ')'
174:         when data_type = 'DATE' then ''
175:         when data_type = 'NUMBER' then ''
176:         else '(' || to_char(a.data_length) || ')'
177:     end as data_type,
178:     a.nullable, a.data_default,
179:     (   SELECT D.constraint_type
180:         FROM ALL_CONS_COLUMNS C
181:         inner join ALL_constraints D on D.OWNER = C.OWNER and D.constraint_name = C.constraint_name
182:         WHERE C.OWNER = B.OWNER
183:            and C.table_name = B.object_name
184:            and C.column_name = A.column_name
185:            and D.constraint_type = 'P') as Key,
186:     com.comments as column_comment
187: FROM ALL_TAB_COLUMNS A
188: inner join ALL_OBJECTS B ON b.owner = a.owner and ltrim(B.OBJECT_NAME) = ltrim(A.TABLE_NAME)
189: LEFT JOIN all_col_comments com ON (A.owner = com.owner AND A.table_name = com.table_name AND A.column_name = com.column_name)
190: WHERE
191:     a.owner = '{$schemaName}'
192:     and (b.object_type = 'TABLE' or b.object_type = 'VIEW')
193:     and b.object_name = '{$tableName}'
194: ORDER by a.column_id
195: EOD;
196: 
197:         $command=$this->getDbConnection()->createCommand($sql);
198: 
199:         if(($columns=$command->queryAll())===array()){
200:             return false;
201:         }
202: 
203:         foreach($columns as $column)
204:         {
205:             $c=$this->createColumn($column);
206: 
207:             $table->columns[$c->name]=$c;
208:             if($c->isPrimaryKey)
209:             {
210:                 if($table->primaryKey===null)
211:                     $table->primaryKey=$c->name;
212:                 elseif(is_string($table->primaryKey))
213:                     $table->primaryKey=array($table->primaryKey,$c->name);
214:                 else
215:                     $table->primaryKey[]=$c->name;
216:                 $table->sequenceName='';
217:                 $c->autoIncrement=true;
218:             }
219:         }
220:         return true;
221:     }
222: 
223:     /**
224:      * Creates a table column.
225:      * @param array $column column metadata
226:      * @return CDbColumnSchema normalized column metadata
227:      */
228:     protected function createColumn($column)
229:     {
230:         $c=new COciColumnSchema;
231:         $c->name=$column['COLUMN_NAME'];
232:         $c->rawName=$this->quoteColumnName($c->name);
233:         $c->allowNull=$column['NULLABLE']==='Y';
234:         $c->isPrimaryKey=strpos($column['KEY'],'P')!==false;
235:         $c->isForeignKey=false;
236:         $c->init($column['DATA_TYPE'],$column['DATA_DEFAULT']);
237:         $c->comment=$column['COLUMN_COMMENT']===null ? '' : $column['COLUMN_COMMENT'];
238: 
239:         return $c;
240:     }
241: 
242:     /**
243:      * Collects the primary and foreign key column details for the given table.
244:      * @param COciTableSchema $table the table metadata
245:      */
246:     protected function findConstraints($table)
247:     {
248:         $sql=<<<EOD
249:         SELECT D.constraint_type as CONSTRAINT_TYPE, C.COLUMN_NAME, C.position, D.r_constraint_name,
250:                 E.table_name as table_ref, f.column_name as column_ref,
251:                 C.table_name
252:         FROM ALL_CONS_COLUMNS C
253:         inner join ALL_constraints D on D.OWNER = C.OWNER and D.constraint_name = C.constraint_name
254:         left join ALL_constraints E on E.OWNER = D.r_OWNER and E.constraint_name = D.r_constraint_name
255:         left join ALL_cons_columns F on F.OWNER = E.OWNER and F.constraint_name = E.constraint_name and F.position = c.position
256:         WHERE C.OWNER = '{$table->schemaName}'
257:            and C.table_name = '{$table->name}'
258:            and D.constraint_type <> 'P'
259:         order by d.constraint_name, c.position
260: EOD;
261:         $command=$this->getDbConnection()->createCommand($sql);
262:         foreach($command->queryAll() as $row)
263:         {
264:             if($row['CONSTRAINT_TYPE']==='R')   // foreign key
265:             {
266:                 $name = $row["COLUMN_NAME"];
267:                 $table->foreignKeys[$name]=array($row["TABLE_REF"], $row["COLUMN_REF"]);
268:                 if(isset($table->columns[$name]))
269:                     $table->columns[$name]->isForeignKey=true;
270:             }
271: 
272:         }
273:     }
274: 
275:     /**
276:      * Returns all table names in the database.
277:      * @param string $schema the schema of the tables. Defaults to empty string, meaning the current or default schema.
278:      * If not empty, the returned table names will be prefixed with the schema name.
279:      * @return array all table names in the database.
280:      */
281:     protected function findTableNames($schema='')
282:     {
283:         if($schema==='')
284:         {
285:             $sql=<<<EOD
286: SELECT table_name, '{$schema}' as table_schema FROM user_tables
287: EOD;
288:             $command=$this->getDbConnection()->createCommand($sql);
289:         }
290:         else
291:         {
292:             $sql=<<<EOD
293: SELECT object_name as table_name, owner as table_schema FROM all_objects
294: WHERE object_type = 'TABLE' AND owner=:schema
295: EOD;
296:             $command=$this->getDbConnection()->createCommand($sql);
297:             $command->bindParam(':schema',$schema);
298:         }
299: 
300:         $rows=$command->queryAll();
301:         $names=array();
302:         foreach($rows as $row)
303:         {
304:             if($schema===$this->getDefaultSchema() || $schema==='')
305:                 $names[]=$row['TABLE_NAME'];
306:             else
307:                 $names[]=$row['TABLE_SCHEMA'].'.'.$row['TABLE_NAME'];
308:         }
309:         return $names;
310:     }
311: 
312:     /**
313:      * Builds a SQL statement for renaming a DB table.
314:      * @param string $table the table to be renamed. The name will be properly quoted by the method.
315:      * @param string $newName the new table name. The name will be properly quoted by the method.
316:      * @return string the SQL statement for renaming a DB table.
317:      * @since 1.1.6
318:      */
319:     public function renameTable($table, $newName)
320:     {
321:         return 'ALTER TABLE ' . $this->quoteTableName($table) . ' RENAME TO ' . $this->quoteTableName($newName);
322:     }
323: 
324:     /**
325:      * Builds a SQL statement for changing the definition of a column.
326:      * @param string $table the table whose column is to be changed. The table name will be properly quoted by the method.
327:      * @param string $column the name of the column to be changed. The name will be properly quoted by the method.
328:      * @param string $type the new column type. The {@link getColumnType} method will be invoked to convert abstract column type (if any)
329:      * into the physical one. Anything that is not recognized as abstract type will be kept in the generated SQL.
330:      * For example, 'string' will be turned into 'varchar(255)', while 'string not null' will become 'varchar(255) not null'.
331:      * @return string the SQL statement for changing the definition of a column.
332:      * @since 1.1.6
333:      */
334:     public function alterColumn($table, $column, $type)
335:     {
336:         $type=$this->getColumnType($type);
337:         $sql='ALTER TABLE ' . $this->quoteTableName($table) . ' MODIFY '
338:             . $this->quoteColumnName($column) . ' '
339:             . $this->getColumnType($type);
340:         return $sql;
341:     }
342: 
343:     /**
344:      * Builds a SQL statement for dropping an index.
345:      * @param string $name the name of the index to be dropped. The name will be properly quoted by the method.
346:      * @param string $table the table whose index is to be dropped. The name will be properly quoted by the method.
347:      * @return string the SQL statement for dropping an index.
348:      * @since 1.1.6
349:      */
350:     public function dropIndex($name, $table)
351:     {
352:         return 'DROP INDEX '.$this->quoteTableName($name);
353:     }
354: 
355:     /**
356:      * Resets the sequence value of a table's primary key.
357:      * The sequence will be reset such that the primary key of the next new row inserted
358:      * will have the specified value or max value of a primary key plus one (i.e. sequence trimming).
359:      *
360:      * Note, behavior of this method has changed since 1.1.14 release. Please refer to the following
361:      * issue for more details: {@link https://github.com/yiisoft/yii/issues/2241}
362:      *
363:      * @param CDbTableSchema $table the table schema whose primary key sequence will be reset
364:      * @param integer|null $value the value for the primary key of the next new row inserted.
365:      * If this is not set, the next new row's primary key will have the max value of a primary
366:      * key plus one (i.e. sequence trimming).
367:      * @since 1.1.13
368:      */
369:     public function resetSequence($table,$value=null)
370:     {
371:         if($table->sequenceName===null)
372:             return;
373: 
374:         if($value!==null)
375:             $value=(int)$value;
376:         else
377:         {
378:             $value=(int)$this->getDbConnection()
379:                 ->createCommand("SELECT MAX(\"{$table->primaryKey}\") FROM {$table->rawName}")
380:                 ->queryScalar();
381:             $value++;
382:         }
383:         $this->getDbConnection()
384:             ->createCommand("DROP SEQUENCE \"{$table->name}_SEQ\"")
385:             ->execute();
386:         $this->getDbConnection()
387:             ->createCommand("CREATE SEQUENCE \"{$table->name}_SEQ\" START WITH {$value} INCREMENT BY 1 NOMAXVALUE NOCACHE")
388:             ->execute();
389:     }
390: 
391:     /**
392:      * Enables or disables integrity check.
393:      * @param boolean $check whether to turn on or off the integrity check.
394:      * @param string $schema the schema of the tables. Defaults to empty string, meaning the current or default schema.
395:      * @since 1.1.14
396:      */
397:     public function checkIntegrity($check=true,$schema='')
398:     {
399:         if($schema==='')
400:             $schema=$this->getDefaultSchema();
401:         $mode=$check ? 'ENABLE' : 'DISABLE';
402:         foreach($this->getTableNames($schema) as $table)
403:         {
404:             $constraints=$this->getDbConnection()
405:                 ->createCommand("SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME=:t AND OWNER=:o")
406:                 ->queryColumn(array(':t'=>$table,':o'=>$schema));
407:             foreach($constraints as $constraint)
408:                 $this->getDbConnection()
409:                     ->createCommand("ALTER TABLE \"{$schema}\".\"{$table}\" {$mode} CONSTRAINT \"{$constraint}\"")
410:                     ->execute();
411:         }
412:     }
413: }
414: