1: <?php
   2: /**
   3:  * Oracle layer for DBO.
   4:  *
   5:  * PHP versions 4 and 5
   6:  *
   7:  * CakePHP(tm) : Rapid Development Framework (http://cakephp.org)
   8:  * Copyright 2005-2012, Cake Software Foundation, Inc. (http://cakefoundation.org)
   9:  *
  10:  * Licensed under The MIT License
  11:  * Redistributions of files must retain the above copyright notice.
  12:  *
  13:  * @copyright     Copyright 2005-2012, Cake Software Foundation, Inc. (http://cakefoundation.org)
  14:  * @link          http://cakephp.org CakePHP(tm) Project
  15:  * @package       cake
  16:  * @subpackage    cake.cake.libs.model.datasources.dbo
  17:  * @since         CakePHP v 1.2.0.4041
  18:  * @license       MIT License (http://www.opensource.org/licenses/mit-license.php)
  19:  */
  20: 
  21: /**
  22:  * Oracle layer for DBO.
  23:  *
  24:  * Long description for class
  25:  *
  26:  * @package       cake
  27:  * @subpackage    cake.cake.libs.model.datasources.dbo
  28:  */
  29: class DboOracle extends DboSource {
  30: 
  31: /**
  32:  * Configuration options
  33:  *
  34:  * @var array
  35:  * @access public
  36:  */
  37:     var $config = array();
  38: 
  39: /**
  40:  * Alias
  41:  *
  42:  * @var string
  43:  */
  44:     var $alias = '';
  45: 
  46: /**
  47:  * Sequence names as introspected from the database
  48:  */
  49:     var $_sequences = array();
  50: 
  51: /**
  52:  * Transaction in progress flag
  53:  *
  54:  * @var boolean
  55:  */
  56:     var $__transactionStarted = false;
  57: 
  58: /**
  59:  * Column definitions
  60:  *
  61:  * @var array
  62:  * @access public
  63:  */
  64:     var $columns = array(
  65:         'primary_key' => array('name' => ''),
  66:         'string' => array('name' => 'varchar2', 'limit' => '255'),
  67:         'text' => array('name' => 'varchar2'),
  68:         'integer' => array('name' => 'number'),
  69:         'float' => array('name' => 'float'),
  70:         'datetime' => array('name' => 'date', 'format' => 'Y-m-d H:i:s'),
  71:         'timestamp' => array('name' => 'date', 'format' => 'Y-m-d H:i:s'),
  72:         'time' => array('name' => 'date', 'format' => 'Y-m-d H:i:s'),
  73:         'date' => array('name' => 'date', 'format' => 'Y-m-d H:i:s'),
  74:         'binary' => array('name' => 'bytea'),
  75:         'boolean' => array('name' => 'boolean'),
  76:         'number' => array('name' => 'number'),
  77:         'inet' => array('name' => 'inet'));
  78: 
  79: /**
  80:  * Connection object
  81:  *
  82:  * @var mixed
  83:  * @access protected
  84:  */
  85:     var $connection;
  86: 
  87: /**
  88:  * Query limit
  89:  *
  90:  * @var int
  91:  * @access protected
  92:  */
  93:     var $_limit = -1;
  94: 
  95: /**
  96:  * Query offset
  97:  *
  98:  * @var int
  99:  * @access protected
 100:  */
 101:     var $_offset = 0;
 102: 
 103: /**
 104:  * Enter description here...
 105:  *
 106:  * @var unknown_type
 107:  * @access protected
 108:  */
 109:     var $_map;
 110: 
 111: /**
 112:  * Current Row
 113:  *
 114:  * @var mixed
 115:  * @access protected
 116:  */
 117:     var $_currentRow;
 118: 
 119: /**
 120:  * Number of rows
 121:  *
 122:  * @var int
 123:  * @access protected
 124:  */
 125:     var $_numRows;
 126: 
 127: /**
 128:  * Query results
 129:  *
 130:  * @var mixed
 131:  * @access protected
 132:  */
 133:     var $_results;
 134: 
 135: /**
 136:  * Last error issued by oci extension
 137:  *
 138:  * @var unknown_type
 139:  */
 140:     var $_error;
 141: 
 142: /**
 143:  * Base configuration settings for MySQL driver
 144:  *
 145:  * @var array
 146:  */
 147:     var $_baseConfig = array(
 148:         'persistent' => true,
 149:         'host' => 'localhost',
 150:         'login' => 'system',
 151:         'password' => '',
 152:         'database' => 'cake',
 153:         'nls_sort' => '',
 154:         'nls_sort' => ''
 155:     );
 156: 
 157: /**
 158:  * Table-sequence map
 159:  *
 160:  * @var unknown_type
 161:  */
 162:     var $_sequenceMap = array();
 163: 
 164: /**
 165:  * Connects to the database using options in the given configuration array.
 166:  *
 167:  * @return boolean True if the database could be connected, else false
 168:  * @access public
 169:  */
 170:     function connect() {
 171:         $config = $this->config;
 172:         $this->connected = false;
 173:         $config['charset'] = !empty($config['charset']) ? $config['charset'] : null;
 174: 
 175:         if (!$config['persistent']) {
 176:             $this->connection = ocilogon($config['login'], $config['password'], $config['database'], $config['charset']);
 177:         } else {
 178:             $this->connection = ociplogon($config['login'], $config['password'], $config['database'], $config['charset']);
 179:         }
 180: 
 181:         if ($this->connection) {
 182:             $this->connected = true;
 183:             if (!empty($config['nls_sort'])) {
 184:                 $this->execute('ALTER SESSION SET NLS_SORT='.$config['nls_sort']);
 185:             }
 186: 
 187:             if (!empty($config['nls_comp'])) {
 188:                 $this->execute('ALTER SESSION SET NLS_COMP='.$config['nls_comp']);
 189:             }
 190:             $this->execute("ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'");
 191:         } else {
 192:             $this->connected = false;
 193:             $this->_setError();
 194:             return false;
 195:         }
 196:         return $this->connected;
 197:     }
 198: 
 199: /**
 200:  * Keeps track of the most recent Oracle error
 201:  *
 202:  */
 203:     function _setError($source = null, $clear = false) {
 204:         if ($source) {
 205:             $e = ocierror($source);
 206:         } else {
 207:             $e = ocierror();
 208:         }
 209:         $this->_error = $e['message'];
 210:         if ($clear) {
 211:             $this->_error = null;
 212:         }
 213:     }
 214: 
 215: /**
 216:  * Sets the encoding language of the session
 217:  *
 218:  * @param string $lang language constant
 219:  * @return bool
 220:  */
 221:     function setEncoding($lang) {
 222:         if (!$this->execute('ALTER SESSION SET NLS_LANGUAGE='.$lang)) {
 223:             return false;
 224:         }
 225:         return true;
 226:     }
 227: 
 228: /**
 229:  * Gets the current encoding language
 230:  *
 231:  * @return string language constant
 232:  */
 233:     function getEncoding() {
 234:         $sql = 'SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER=\'NLS_LANGUAGE\'';
 235:         if (!$this->execute($sql)) {
 236:             return false;
 237:         }
 238: 
 239:         if (!$row = $this->fetchRow()) {
 240:             return false;
 241:         }
 242:         return $row[0]['VALUE'];
 243:     }
 244: 
 245: /**
 246:  * Check whether the OCI8 extension is installed/loaded
 247:  *
 248:  * @return boolean
 249:  */
 250:     function enabled() {
 251:         return extension_loaded('oci8');
 252:     }
 253: 
 254: /**
 255:  * Disconnects from database.
 256:  *
 257:  * @return boolean True if the database could be disconnected, else false
 258:  * @access public
 259:  */
 260:     function disconnect() {
 261:         if ($this->connection) {
 262:             $this->connected = !ocilogoff($this->connection);
 263:             return !$this->connected;
 264:         }
 265:     }
 266: 
 267: /**
 268:  * Scrape the incoming SQL to create the association map. This is an extremely
 269:  * experimental method that creates the association maps since Oracle will not tell us.
 270:  *
 271:  * @param string $sql
 272:  * @return false if sql is nor a SELECT
 273:  * @access protected
 274:  */
 275:     function _scrapeSQL($sql) {
 276:         $sql = str_replace("\"", '', $sql);
 277:         $preFrom = preg_split('/\bFROM\b/', $sql);
 278:         $preFrom = $preFrom[0];
 279:         $find = array('SELECT');
 280:         $replace = array('');
 281:         $fieldList = trim(str_replace($find, $replace, $preFrom));
 282:         $fields = preg_split('/,\s+/', $fieldList);//explode(', ', $fieldList);
 283:         $lastTableName  = '';
 284: 
 285:         foreach($fields as $key => $value) {
 286:             if ($value != 'COUNT(*) AS count') {
 287:                 if (preg_match('/\s+(\w+(\.\w+)*)$/', $value, $matches)) {
 288:                     $fields[$key]   = $matches[1];
 289: 
 290:                     if (preg_match('/^(\w+\.)/', $value, $matches)) {
 291:                         $fields[$key]   = $matches[1] . $fields[$key];
 292:                         $lastTableName  = $matches[1];
 293:                     }
 294:                 }
 295:                 /*
 296:                 if (preg_match('/(([[:alnum:]_]+)\.[[:alnum:]_]+)(\s+AS\s+(\w+))?$/i', $value, $matches)) {
 297:                     $fields[$key]   = isset($matches[4]) ? $matches[2] . '.' . $matches[4] : $matches[1];
 298:                 }
 299:                 */
 300:             }
 301:         }
 302:         $this->_map = array();
 303: 
 304:         foreach($fields as $f) {
 305:             $e = explode('.', $f);
 306:             if (count($e) > 1) {
 307:                 $table = $e[0];
 308:                 $field = strtolower($e[1]);
 309:             } else {
 310:                 $table = 0;
 311:                 $field = $e[0];
 312:             }
 313:             $this->_map[] = array($table, $field);
 314:         }
 315:     }
 316: 
 317: /**
 318:  * Modify a SQL query to limit (and offset) the result set
 319:  *
 320:  * @param integer $limit Maximum number of rows to return
 321:  * @param integer $offset Row to begin returning
 322:  * @return modified SQL Query
 323:  * @access public
 324:  */
 325:     function limit($limit = -1, $offset = 0) {
 326:         $this->_limit = (int) $limit;
 327:         $this->_offset = (int) $offset;
 328:     }
 329: 
 330: /**
 331:  * Returns number of rows in previous resultset. If no previous resultset exists,
 332:  * this returns false.
 333:  *
 334:  * @return integer Number of rows in resultset
 335:  * @access public
 336:  */
 337:     function lastNumRows() {
 338:         return $this->_numRows;
 339:     }
 340: 
 341: /**
 342:  * Executes given SQL statement. This is an overloaded method.
 343:  *
 344:  * @param string $sql SQL statement
 345:  * @return resource Result resource identifier or null
 346:  * @access protected
 347:  */
 348:     function _execute($sql) {
 349:         $this->_statementId = @ociparse($this->connection, $sql);
 350:         if (!$this->_statementId) {
 351:             $this->_setError($this->connection);
 352:             return false;
 353:         }
 354: 
 355:         if ($this->__transactionStarted) {
 356:             $mode = OCI_DEFAULT;
 357:         } else {
 358:             $mode = OCI_COMMIT_ON_SUCCESS;
 359:         }
 360: 
 361:         if (!@ociexecute($this->_statementId, $mode)) {
 362:             $this->_setError($this->_statementId);
 363:             return false;
 364:         }
 365: 
 366:         $this->_setError(null, true);
 367: 
 368:         switch(ocistatementtype($this->_statementId)) {
 369:             case 'DESCRIBE':
 370:             case 'SELECT':
 371:                 $this->_scrapeSQL($sql);
 372:             break;
 373:             default:
 374:                 return $this->_statementId;
 375:             break;
 376:         }
 377: 
 378:         if ($this->_limit >= 1) {
 379:             ocisetprefetch($this->_statementId, $this->_limit);
 380:         } else {
 381:             ocisetprefetch($this->_statementId, 3000);
 382:         }
 383:         $this->_numRows = ocifetchstatement($this->_statementId, $this->_results, $this->_offset, $this->_limit, OCI_NUM | OCI_FETCHSTATEMENT_BY_ROW);
 384:         $this->_currentRow = 0;
 385:         $this->limit();
 386:         return $this->_statementId;
 387:     }
 388: 
 389: /**
 390:  * Fetch result row
 391:  *
 392:  * @return array
 393:  * @access public
 394:  */
 395:     function fetchRow() {
 396:         if ($this->_currentRow >= $this->_numRows) {
 397:             ocifreestatement($this->_statementId);
 398:             $this->_map = null;
 399:             $this->_results = null;
 400:             $this->_currentRow = null;
 401:             $this->_numRows = null;
 402:             return false;
 403:         }
 404:         $resultRow = array();
 405: 
 406:         foreach($this->_results[$this->_currentRow] as $index => $field) {
 407:             list($table, $column) = $this->_map[$index];
 408: 
 409:             if (strpos($column, ' count')) {
 410:                 $resultRow[0]['count'] = $field;
 411:             } else {
 412:                 $resultRow[$table][$column] = $this->_results[$this->_currentRow][$index];
 413:             }
 414:         }
 415:         $this->_currentRow++;
 416:         return $resultRow;
 417:     }
 418: 
 419: /**
 420:  * Fetches the next row from the current result set
 421:  *
 422:  * @return unknown
 423:  */
 424:     function fetchResult() {
 425:         return $this->fetchRow();
 426:     }
 427: 
 428: /**
 429:  * Checks to see if a named sequence exists
 430:  *
 431:  * @param string $sequence
 432:  * @return bool
 433:  * @access public
 434:  */
 435:     function sequenceExists($sequence) {
 436:         $sql = "SELECT SEQUENCE_NAME FROM USER_SEQUENCES WHERE SEQUENCE_NAME = '$sequence'";
 437:         if (!$this->execute($sql)) {
 438:             return false;
 439:         }
 440:         return $this->fetchRow();
 441:     }
 442: 
 443: /**
 444:  * Creates a database sequence
 445:  *
 446:  * @param string $sequence
 447:  * @return bool
 448:  * @access public
 449:  */
 450:     function createSequence($sequence) {
 451:         $sql = "CREATE SEQUENCE $sequence";
 452:         return $this->execute($sql);
 453:     }
 454: 
 455: /**
 456:  * Create trigger
 457:  *
 458:  * @param string $table
 459:  * @return mixed
 460:  * @access public
 461:  */
 462:     function createTrigger($table) {
 463:         $sql = "CREATE OR REPLACE TRIGGER pk_$table" . "_trigger BEFORE INSERT ON $table FOR EACH ROW BEGIN SELECT pk_$table.NEXTVAL INTO :NEW.ID FROM DUAL; END;";
 464:         return $this->execute($sql);
 465:     }
 466: 
 467: /**
 468:  * Returns an array of tables in the database. If there are no tables, an error is
 469:  * raised and the application exits.
 470:  *
 471:  * @return array tablenames in the database
 472:  * @access public
 473:  */
 474:     function listSources() {
 475:         $cache = parent::listSources();
 476:         if ($cache != null) {
 477:             return $cache;
 478:         }
 479:         $sql = 'SELECT view_name AS name FROM user_views UNION SELECT table_name AS name FROM user_tables';
 480: 
 481:         if (!$this->execute($sql)) {
 482:             return false;
 483:         }
 484:         $sources = array();
 485: 
 486:         while($r = $this->fetchRow()) {
 487:             $sources[] = strtolower($r[0]['name']);
 488:         }
 489:         parent::listSources($sources);
 490:         return $sources;
 491:     }
 492: 
 493: /**
 494:  * Returns an array of the fields in given table name.
 495:  *
 496:  * @param object instance of a model to inspect
 497:  * @return array Fields in table. Keys are name and type
 498:  * @access public
 499:  */
 500:     function describe(&$model) {
 501:         $table = $this->fullTableName($model, false);
 502: 
 503:         if (!empty($model->sequence)) {
 504:             $this->_sequenceMap[$table] = $model->sequence;
 505:         } elseif (!empty($model->table)) {
 506:             $this->_sequenceMap[$table] = $model->table . '_seq';
 507:         }
 508: 
 509:         $cache = parent::describe($model);
 510: 
 511:         if ($cache != null) {
 512:             return $cache;
 513:         }
 514: 
 515:         $sql = 'SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH FROM user_tab_columns WHERE table_name = \'';
 516:         $sql .= strtoupper($this->fullTableName($model)) . '\'';
 517: 
 518:         if (!$this->execute($sql)) {
 519:             return false;
 520:         }
 521: 
 522:         $fields = array();
 523: 
 524:         for ($i = 0; $row = $this->fetchRow(); $i++) {
 525:             $fields[strtolower($row[0]['COLUMN_NAME'])] = array(
 526:                 'type'=> $this->column($row[0]['DATA_TYPE']),
 527:                 'length'=> $row[0]['DATA_LENGTH']
 528:             );
 529:         }
 530:         $this->__cacheDescription($this->fullTableName($model, false), $fields);
 531: 
 532:         return $fields;
 533:     }
 534: 
 535: /**
 536:  * Deletes all the records in a table and drops all associated auto-increment sequences.
 537:  * Using DELETE instead of TRUNCATE because it causes locking problems.
 538:  *
 539:  * @param mixed $table A string or model class representing the table to be truncated
 540:  * @param integer $reset If -1, sequences are dropped, if 0 (default), sequences are reset,
 541:  *                      and if 1, sequences are not modified
 542:  * @return boolean  SQL TRUNCATE TABLE statement, false if not applicable.
 543:  * @access public
 544:  *
 545:  */
 546:     function truncate($table, $reset = 0) {
 547: 
 548:         if (empty($this->_sequences)) {
 549:             $sql = "SELECT sequence_name FROM all_sequences";
 550:             $this->execute($sql);
 551:             while ($row = $this->fetchRow()) {
 552:                 $this->_sequences[] = strtolower($row[0]['sequence_name']);
 553:             }
 554:         }
 555: 
 556:         $this->execute('DELETE FROM ' . $this->fullTableName($table));
 557:         if (!isset($this->_sequenceMap[$table]) || !in_array($this->_sequenceMap[$table], $this->_sequences)) {
 558:             return true;
 559:         }
 560:         if ($reset === 0) {
 561:             $this->execute("SELECT {$this->_sequenceMap[$table]}.nextval FROM dual");
 562:             $row = $this->fetchRow();
 563:             $currval = $row[$this->_sequenceMap[$table]]['nextval'];
 564: 
 565:             $this->execute("SELECT min_value FROM all_sequences WHERE sequence_name = '{$this->_sequenceMap[$table]}'");
 566:             $row = $this->fetchRow();
 567:             $min_value = $row[0]['min_value'];
 568: 
 569:             if ($min_value == 1) $min_value = 0;
 570:             $offset = -($currval - $min_value);
 571: 
 572:             $this->execute("ALTER SEQUENCE {$this->_sequenceMap[$table]} INCREMENT BY $offset MINVALUE $min_value");
 573:             $this->execute("SELECT {$this->_sequenceMap[$table]}.nextval FROM dual");
 574:             $this->execute("ALTER SEQUENCE {$this->_sequenceMap[$table]} INCREMENT BY 1");
 575:         } else {
 576:             //$this->execute("DROP SEQUENCE {$this->_sequenceMap[$table]}");
 577:         }
 578:         return true;
 579:     }
 580: 
 581: /**
 582:  * Enables, disables, and lists table constraints
 583:  *
 584:  * Note: This method could have been written using a subselect for each table,
 585:  * however the effort Oracle expends to run the constraint introspection is very high.
 586:  * Therefore, this method caches the result once and loops through the arrays to find
 587:  * what it needs. It reduced my query time by 50%. YMMV.
 588:  *
 589:  * @param string $action
 590:  * @param string $table
 591:  * @return mixed boolean true or array of constraints
 592:  */
 593:     function constraint($action, $table) {
 594:         if (empty($table)) {
 595:             trigger_error(__('Must specify table to operate on constraints', true));
 596:         }
 597: 
 598:         $table = strtoupper($table);
 599: 
 600:         if (empty($this->_keyConstraints)) {
 601:             $sql = "SELECT
 602:                       table_name,
 603:                       c.constraint_name
 604:                     FROM all_cons_columns cc
 605:                     LEFT JOIN all_indexes i ON (cc.constraint_name = i.index_name)
 606:                     LEFT JOIN all_constraints c ON(c.constraint_name = cc.constraint_name)";
 607:             $this->execute($sql);
 608:             while ($row = $this->fetchRow()) {
 609:                 $this->_keyConstraints[] = array($row[0]['table_name'], $row['c']['constraint_name']);
 610:             }
 611:         }
 612: 
 613:         $relatedKeys = array();
 614:         foreach ($this->_keyConstraints as $c) {
 615:             if ($c[0] == $table) {
 616:                 $relatedKeys[] = $c[1];
 617:             }
 618:         }
 619: 
 620:         if (empty($this->_constraints)) {
 621:             $sql = "SELECT
 622:                       table_name,
 623:                       constraint_name,
 624:                       r_constraint_name
 625:                     FROM
 626:                       all_constraints";
 627:             $this->execute($sql);
 628:             while ($row = $this->fetchRow()) {
 629:                 $this->_constraints[] = $row[0];
 630:             }
 631:         }
 632: 
 633:         $constraints = array();
 634:         foreach ($this->_constraints as $c) {
 635:             if (in_array($c['r_constraint_name'], $relatedKeys)) {
 636:                 $constraints[] = array($c['table_name'], $c['constraint_name']);
 637:             }
 638:         }
 639: 
 640:         foreach ($constraints as $c) {
 641:             list($table, $constraint) = $c;
 642:             switch ($action) {
 643:                 case 'enable':
 644:                     $this->execute("ALTER TABLE $table ENABLE CONSTRAINT $constraint");
 645:                     break;
 646:                 case 'disable':
 647:                     $this->execute("ALTER TABLE $table DISABLE CONSTRAINT $constraint");
 648:                     break;
 649:                 case 'list':
 650:                     return $constraints;
 651:                     break;
 652:                 default:
 653:                     trigger_error(__('DboOracle::constraint() accepts only enable, disable, or list', true));
 654:             }
 655:         }
 656:         return true;
 657:     }
 658: 
 659: /**
 660:  * Returns an array of the indexes in given table name.
 661:  *
 662:  * @param string $model Name of model to inspect
 663:  * @return array Fields in table. Keys are column and unique
 664:  */
 665:     function index($model) {
 666:         $index = array();
 667:         $table = $this->fullTableName($model, false);
 668:         if ($table) {
 669:             $indexes = $this->query('SELECT
 670:               cc.table_name,
 671:               cc.column_name,
 672:               cc.constraint_name,
 673:               c.constraint_type,
 674:               i.index_name,
 675:               i.uniqueness
 676:             FROM all_cons_columns cc
 677:             LEFT JOIN all_indexes i ON(cc.constraint_name = i.index_name)
 678:             LEFT JOIN all_constraints c ON(c.constraint_name = cc.constraint_name)
 679:             WHERE cc.table_name = \'' . strtoupper($table) .'\'');
 680:             foreach ($indexes as $i => $idx) {
 681:                 if ($idx['c']['constraint_type'] == 'P') {
 682:                     $key = 'PRIMARY';
 683:                 } else {
 684:                     continue;
 685:                 }
 686:                 if (!isset($index[$key])) {
 687:                     $index[$key]['column'] = strtolower($idx['cc']['column_name']);
 688:                     $index[$key]['unique'] = intval($idx['i']['uniqueness'] == 'UNIQUE');
 689:                 } else {
 690:                     if (!is_array($index[$key]['column'])) {
 691:                         $col[] = $index[$key]['column'];
 692:                     }
 693:                     $col[] = strtolower($idx['cc']['column_name']);
 694:                     $index[$key]['column'] = $col;
 695:                 }
 696:             }
 697:         }
 698:         return $index;
 699:     }
 700: 
 701: /**
 702:  * Generate a Oracle Alter Table syntax for the given Schema comparison
 703:  *
 704:  * @param unknown_type $schema
 705:  * @return unknown
 706:  */
 707:     function alterSchema($compare, $table = null) {
 708:         if (!is_array($compare)) {
 709:             return false;
 710:         }
 711:         $out = '';
 712:         $colList = array();
 713:         foreach($compare as $curTable => $types) {
 714:             if (!$table || $table == $curTable) {
 715:                 $out .= 'ALTER TABLE ' . $this->fullTableName($curTable) . " \n";
 716:                 foreach($types as $type => $column) {
 717:                     switch($type) {
 718:                         case 'add':
 719:                             foreach($column as $field => $col) {
 720:                                 $col['name'] = $field;
 721:                                 $alter = 'ADD '.$this->buildColumn($col);
 722:                                 if (isset($col['after'])) {
 723:                                     $alter .= ' AFTER '. $this->name($col['after']);
 724:                                 }
 725:                                 $colList[] = $alter;
 726:                             }
 727:                         break;
 728:                         case 'drop':
 729:                             foreach($column as $field => $col) {
 730:                                 $col['name'] = $field;
 731:                                 $colList[] = 'DROP '.$this->name($field);
 732:                             }
 733:                         break;
 734:                         case 'change':
 735:                             foreach($column as $field => $col) {
 736:                                 if (!isset($col['name'])) {
 737:                                     $col['name'] = $field;
 738:                                 }
 739:                                 $colList[] = 'CHANGE '. $this->name($field).' '.$this->buildColumn($col);
 740:                             }
 741:                         break;
 742:                     }
 743:                 }
 744:                 $out .= "\t" . implode(",\n\t", $colList) . ";\n\n";
 745:             }
 746:         }
 747:         return $out;
 748:     }
 749: 
 750: /**
 751:  * This method should quote Oracle identifiers. Well it doesn't.
 752:  * It would break all scaffolding and all of Cake's default assumptions.
 753:  *
 754:  * @param unknown_type $var
 755:  * @return unknown
 756:  * @access public
 757:  */
 758:     function name($name) {
 759:         if (strpos($name, '.') !== false && strpos($name, '"') === false) {
 760:             list($model, $field) = explode('.', $name);
 761:             if ($field[0] == "_") {
 762:                 $name = "$model.\"$field\"";
 763:             }
 764:         } else {
 765:             if ($name[0] == "_") {
 766:                 $name = "\"$name\"";
 767:             }
 768:         }
 769:         return $name;
 770:     }
 771: 
 772: /**
 773:  * Begin a transaction
 774:  *
 775:  * @param unknown_type $model
 776:  * @return boolean True on success, false on fail
 777:  * (i.e. if the database/model does not support transactions).
 778:  */
 779:     function begin() {
 780:         $this->__transactionStarted = true;
 781:         return true;
 782:     }
 783: 
 784: /**
 785:  * Rollback a transaction
 786:  *
 787:  * @param unknown_type $model
 788:  * @return boolean True on success, false on fail
 789:  * (i.e. if the database/model does not support transactions,
 790:  * or a transaction has not started).
 791:  */
 792:     function rollback() {
 793:         return ocirollback($this->connection);
 794:     }
 795: 
 796: /**
 797:  * Commit a transaction
 798:  *
 799:  * @param unknown_type $model
 800:  * @return boolean True on success, false on fail
 801:  * (i.e. if the database/model does not support transactions,
 802:  * or a transaction has not started).
 803:  */
 804:     function commit() {
 805:         $this->__transactionStarted = false;
 806:         return ocicommit($this->connection);
 807:     }
 808: 
 809: /**
 810:  * Converts database-layer column types to basic types
 811:  *
 812:  * @param string $real Real database-layer column type (i.e. "varchar(255)")
 813:  * @return string Abstract column type (i.e. "string")
 814:  * @access public
 815:  */
 816:     function column($real) {
 817:         if (is_array($real)) {
 818:             $col = $real['name'];
 819: 
 820:             if (isset($real['limit'])) {
 821:                 $col .= '('.$real['limit'].')';
 822:             }
 823:             return $col;
 824:         } else {
 825:             $real = strtolower($real);
 826:         }
 827:         $col = str_replace(')', '', $real);
 828:         $limit = null;
 829:         if (strpos($col, '(') !== false) {
 830:             list($col, $limit) = explode('(', $col);
 831:         }
 832: 
 833:         if (in_array($col, array('date', 'timestamp'))) {
 834:             return $col;
 835:         }
 836:         if (strpos($col, 'number') !== false) {
 837:             return 'integer';
 838:         }
 839:         if (strpos($col, 'integer') !== false) {
 840:             return 'integer';
 841:         }
 842:         if (strpos($col, 'char') !== false) {
 843:             return 'string';
 844:         }
 845:         if (strpos($col, 'text') !== false) {
 846:             return 'text';
 847:         }
 848:         if (strpos($col, 'blob') !== false) {
 849:             return 'binary';
 850:         }
 851:         if (in_array($col, array('float', 'double', 'decimal'))) {
 852:             return 'float';
 853:         }
 854:         if ($col == 'boolean') {
 855:             return $col;
 856:         }
 857:         return 'text';
 858:     }
 859: 
 860: /**
 861:  * Returns a quoted and escaped string of $data for use in an SQL statement.
 862:  *
 863:  * @param string $data String to be prepared for use in an SQL statement
 864:  * @return string Quoted and escaped
 865:  * @access public
 866:  */
 867:     function value($data, $column = null, $safe = false) {
 868:         $parent = parent::value($data, $column, $safe);
 869: 
 870:         if ($parent != null) {
 871:             return $parent;
 872:         }
 873: 
 874:         if ($data === null) {
 875:             return 'NULL';
 876:         }
 877: 
 878:         if ($data === '') {
 879:             return  "''";
 880:         }
 881: 
 882:         switch($column) {
 883:             case 'date':
 884:                 $data = date('Y-m-d H:i:s', strtotime($data));
 885:                 $data = "TO_DATE('$data', 'YYYY-MM-DD HH24:MI:SS')";
 886:             break;
 887:             case 'integer' :
 888:             case 'float' :
 889:             case null :
 890:                 if (is_numeric($data)) {
 891:                     break;
 892:                 }
 893:             default:
 894:                 $data = str_replace("'", "''", $data);
 895:                 $data = "'$data'";
 896:             break;
 897:         }
 898:         return $data;
 899:     }
 900: 
 901: /**
 902:  * Returns the ID generated from the previous INSERT operation.
 903:  *
 904:  * @param string
 905:  * @return integer
 906:  * @access public
 907:  */
 908:     function lastInsertId($source) {
 909:         $sequence = $this->_sequenceMap[$source];
 910:         $sql = "SELECT $sequence.currval FROM dual";
 911: 
 912:         if (!$this->execute($sql)) {
 913:             return false;
 914:         }
 915: 
 916:         while($row = $this->fetchRow()) {
 917:             return $row[$sequence]['currval'];
 918:         }
 919:         return false;
 920:     }
 921: 
 922: /**
 923:  * Returns a formatted error message from previous database operation.
 924:  *
 925:  * @return string Error message with error number
 926:  * @access public
 927:  */
 928:     function lastError() {
 929:         return $this->_error;
 930:     }
 931: 
 932: /**
 933:  * Returns number of affected rows in previous database operation. If no previous operation exists, this returns false.
 934:  *
 935:  * @return int Number of affected rows
 936:  * @access public
 937:  */
 938:     function lastAffected() {
 939:         return $this->_statementId ? ocirowcount($this->_statementId): false;
 940:     }
 941: 
 942: /**
 943:  * Renders a final SQL statement by putting together the component parts in the correct order
 944:  *
 945:  * @param string $type
 946:  * @param array $data
 947:  * @return string
 948:  */
 949:     function renderStatement($type, $data) {
 950:         extract($data);
 951:         $aliases = null;
 952: 
 953:         switch (strtolower($type)) {
 954:             case 'select':
 955:                 return "SELECT {$fields} FROM {$table} {$alias} {$joins} {$conditions} {$group} {$order} {$limit}";
 956:             break;
 957:             case 'create':
 958:                 return "INSERT INTO {$table} ({$fields}) VALUES ({$values})";
 959:             break;
 960:             case 'update':
 961:                 if (!empty($alias)) {
 962:                     $aliases = "{$this->alias}{$alias} ";
 963:                 }
 964:                 return "UPDATE {$table} {$aliases}SET {$fields} {$conditions}";
 965:             break;
 966:             case 'delete':
 967:                 if (!empty($alias)) {
 968:                     $aliases = "{$this->alias}{$alias} ";
 969:                 }
 970:                 return "DELETE FROM {$table} {$aliases}{$conditions}";
 971:             break;
 972:             case 'schema':
 973:                 foreach (array('columns', 'indexes') as $var) {
 974:                     if (is_array(${$var})) {
 975:                         ${$var} = "\t" . implode(",\n\t", array_filter(${$var}));
 976:                     }
 977:                 }
 978:                 if (trim($indexes) != '') {
 979:                     $columns .= ',';
 980:                 }
 981:                 return "CREATE TABLE {$table} (\n{$columns}{$indexes})";
 982:             break;
 983:             case 'alter':
 984:                 break;
 985:         }
 986:     }
 987: 
 988: /**
 989:  * Enter description here...
 990:  *
 991:  * @param Model $model
 992:  * @param unknown_type $linkModel
 993:  * @param string $type Association type
 994:  * @param unknown_type $association
 995:  * @param unknown_type $assocData
 996:  * @param unknown_type $queryData
 997:  * @param unknown_type $external
 998:  * @param unknown_type $resultSet
 999:  * @param integer $recursive Number of levels of association
1000:  * @param array $stack
1001:  */
1002:     function queryAssociation(&$model, &$linkModel, $type, $association, $assocData, &$queryData, $external = false, &$resultSet, $recursive, $stack) {
1003:         if ($query = $this->generateAssociationQuery($model, $linkModel, $type, $association, $assocData, $queryData, $external, $resultSet)) {
1004:             if (!isset($resultSet) || !is_array($resultSet)) {
1005:                 if (Configure::read() > 0) {
1006:                     echo '<div style = "font: Verdana bold 12px; color: #FF0000">' . sprintf(__('SQL Error in model %s:', true), $model->alias) . ' ';
1007:                     if (isset($this->error) && $this->error != null) {
1008:                         echo $this->error;
1009:                     }
1010:                     echo '</div>';
1011:                 }
1012:                 return null;
1013:             }
1014:             $count = count($resultSet);
1015: 
1016:             if ($type === 'hasMany' && (!isset($assocData['limit']) || empty($assocData['limit']))) {
1017:                 $ins = $fetch = array();
1018:                 for ($i = 0; $i < $count; $i++) {
1019:                     if ($in = $this->insertQueryData('{$__cakeID__$}', $resultSet[$i], $association, $assocData, $model, $linkModel, $stack)) {
1020:                         $ins[] = $in;
1021:                     }
1022:                 }
1023: 
1024:                 if (!empty($ins)) {
1025:                     $fetch = array();
1026:                     $ins = array_chunk($ins, 1000);
1027:                     foreach ($ins as $i) {
1028:                         $q = str_replace('{$__cakeID__$}', implode(', ', $i), $query);
1029:                         $q = str_replace('= (', 'IN (', $q);
1030:                         $res = $this->fetchAll($q, $model->cacheQueries, $model->alias);
1031:                         $fetch = array_merge($fetch, $res);
1032:                     }
1033:                 }
1034: 
1035:                 if (!empty($fetch) && is_array($fetch)) {
1036:                     if ($recursive > 0) {
1037: 
1038:                         foreach ($linkModel->__associations as $type1) {
1039:                             foreach ($linkModel->{$type1} as $assoc1 => $assocData1) {
1040:                                 $deepModel =& $linkModel->{$assoc1};
1041:                                 $tmpStack = $stack;
1042:                                 $tmpStack[] = $assoc1;
1043: 
1044:                                 if ($linkModel->useDbConfig === $deepModel->useDbConfig) {
1045:                                     $db =& $this;
1046:                                 } else {
1047:                                     $db =& ConnectionManager::getDataSource($deepModel->useDbConfig);
1048:                                 }
1049:                                 $db->queryAssociation($linkModel, $deepModel, $type1, $assoc1, $assocData1, $queryData, true, $fetch, $recursive - 1, $tmpStack);
1050:                             }
1051:                         }
1052:                     }
1053:                 }
1054:                 return $this->__mergeHasMany($resultSet, $fetch, $association, $model, $linkModel, $recursive);
1055:             } elseif ($type === 'hasAndBelongsToMany') {
1056:                 $ins = $fetch = array();
1057:                 for ($i = 0; $i < $count; $i++) {
1058:                     if ($in = $this->insertQueryData('{$__cakeID__$}', $resultSet[$i], $association, $assocData, $model, $linkModel, $stack)) {
1059:                         $ins[] = $in;
1060:                     }
1061:                 }
1062: 
1063:                 $foreignKey = $model->hasAndBelongsToMany[$association]['foreignKey'];
1064:                 $joinKeys = array($foreignKey, $model->hasAndBelongsToMany[$association]['associationForeignKey']);
1065:                 list($with, $habtmFields) = $model->joinModel($model->hasAndBelongsToMany[$association]['with'], $joinKeys);
1066:                 $habtmFieldsCount = count($habtmFields);
1067: 
1068:                 if (!empty($ins)) {
1069:                     $fetch = array();
1070:                     $ins = array_chunk($ins, 1000);
1071:                     foreach ($ins as $i) {
1072:                         $q = str_replace('{$__cakeID__$}', '(' .implode(', ', $i) .')', $query);
1073:                         $q = str_replace('= (', 'IN (', $q);
1074:                         $q = str_replace('  WHERE 1 = 1', '', $q);
1075: 
1076:                         $q = $this->insertQueryData($q, null, $association, $assocData, $model, $linkModel, $stack);
1077:                         if ($q != false) {
1078:                             $res = $this->fetchAll($q, $model->cacheQueries, $model->alias);
1079:                             $fetch = array_merge($fetch, $res);
1080:                         }
1081:                     }
1082:                 }
1083:             }
1084: 
1085:             for ($i = 0; $i < $count; $i++) {
1086:                 $row =& $resultSet[$i];
1087: 
1088:                 if ($type !== 'hasAndBelongsToMany') {
1089:                     $q = $this->insertQueryData($query, $resultSet[$i], $association, $assocData, $model, $linkModel, $stack);
1090:                     if ($q != false) {
1091:                         $fetch = $this->fetchAll($q, $model->cacheQueries, $model->alias);
1092:                     } else {
1093:                         $fetch = null;
1094:                     }
1095:                 }
1096: 
1097:                 if (!empty($fetch) && is_array($fetch)) {
1098:                     if ($recursive > 0) {
1099: 
1100:                         foreach ($linkModel->__associations as $type1) {
1101:                             foreach ($linkModel->{$type1} as $assoc1 => $assocData1) {
1102: 
1103:                                 $deepModel =& $linkModel->{$assoc1};
1104:                                 if (($type1 === 'belongsTo') || ($deepModel->alias === $model->alias && $type === 'belongsTo') || ($deepModel->alias != $model->alias)) {
1105:                                     $tmpStack = $stack;
1106:                                     $tmpStack[] = $assoc1;
1107:                                     if ($linkModel->useDbConfig == $deepModel->useDbConfig) {
1108:                                         $db =& $this;
1109:                                     } else {
1110:                                         $db =& ConnectionManager::getDataSource($deepModel->useDbConfig);
1111:                                     }
1112:                                     $db->queryAssociation($linkModel, $deepModel, $type1, $assoc1, $assocData1, $queryData, true, $fetch, $recursive - 1, $tmpStack);
1113:                                 }
1114:                             }
1115:                         }
1116:                     }
1117:                     if ($type == 'hasAndBelongsToMany') {
1118:                         $merge = array();
1119:                         foreach($fetch as $j => $data) {
1120:                             if (isset($data[$with]) && $data[$with][$foreignKey] === $row[$model->alias][$model->primaryKey]) {
1121:                                 if ($habtmFieldsCount > 2) {
1122:                                     $merge[] = $data;
1123:                                 } else {
1124:                                     $merge[] = Set::diff($data, array($with => $data[$with]));
1125:                                 }
1126:                             }
1127:                         }
1128:                         if (empty($merge) && !isset($row[$association])) {
1129:                             $row[$association] = $merge;
1130:                         } else {
1131:                             $this->__mergeAssociation($resultSet[$i], $merge, $association, $type);
1132:                         }
1133:                     } else {
1134:                         $this->__mergeAssociation($resultSet[$i], $fetch, $association, $type);
1135:                     }
1136:                     $resultSet[$i][$association] = $linkModel->afterfind($resultSet[$i][$association]);
1137: 
1138:                 } else {
1139:                     $tempArray[0][$association] = false;
1140:                     $this->__mergeAssociation($resultSet[$i], $tempArray, $association, $type);
1141:                 }
1142:             }
1143:         }
1144:     }
1145: 
1146: /**
1147:  * Generate a "drop table" statement for the given Schema object
1148:  *
1149:  * @param object $schema An instance of a subclass of CakeSchema
1150:  * @param string $table Optional.  If specified only the table name given will be generated.
1151:  *                      Otherwise, all tables defined in the schema are generated.
1152:  * @return string
1153:  */
1154:         function dropSchema($schema, $table = null) {
1155:             if (!is_a($schema, 'CakeSchema')) {
1156:                 trigger_error(__('Invalid schema object', true), E_USER_WARNING);
1157:                 return null;
1158:             }
1159:             $out = '';
1160: 
1161:             foreach ($schema->tables as $curTable => $columns) {
1162:                 if (!$table || $table == $curTable) {
1163:                     $out .= 'DROP TABLE ' . $this->fullTableName($curTable) . "\n";
1164:                 }
1165:             }
1166:             return $out;
1167:         }
1168: }
1169: