00001 <?php
00026 require_once 'Zend/Db/Adapter/Abstract.php';
00027
00031 require_once 'Zend/Db/Statement/Oracle.php';
00032
00040 class Zend_Db_Adapter_Oracle extends Zend_Db_Adapter_Abstract
00041 {
00054 protected $_config = array(
00055 'dbname' => null,
00056 'username' => null,
00057 'password' => null,
00058 'persistent' => false
00059 );
00060
00072 protected $_numericDataTypes = array(
00073 Zend_Db::INT_TYPE => Zend_Db::INT_TYPE,
00074 Zend_Db::BIGINT_TYPE => Zend_Db::BIGINT_TYPE,
00075 Zend_Db::FLOAT_TYPE => Zend_Db::FLOAT_TYPE,
00076 'BINARY_DOUBLE' => Zend_Db::FLOAT_TYPE,
00077 'BINARY_FLOAT' => Zend_Db::FLOAT_TYPE,
00078 'NUMBER' => Zend_Db::FLOAT_TYPE,
00079 );
00080
00084 protected $_execute_mode = null;
00085
00091 protected $_defaultStmtClass = 'Zend_Db_Statement_Oracle';
00092
00099 protected $_lobAsString = null;
00100
00107 protected function _connect()
00108 {
00109 if (is_resource($this->_connection)) {
00110
00111 return;
00112 }
00113
00114 if (!extension_loaded('oci8')) {
00118 require_once 'Zend/Db/Adapter/Oracle/Exception.php';
00119 throw new Zend_Db_Adapter_Oracle_Exception('The OCI8 extension is required for this adapter but the extension is not loaded');
00120 }
00121
00122 $this->_setExecuteMode(OCI_COMMIT_ON_SUCCESS);
00123
00124 $connectionFuncName = ($this->_config['persistent'] == true) ? 'oci_pconnect' : 'oci_connect';
00125
00126 $this->_connection = @$connectionFuncName(
00127 $this->_config['username'],
00128 $this->_config['password'],
00129 $this->_config['dbname'],
00130 $this->_config['charset']);
00131
00132
00133 if (!$this->_connection) {
00137 require_once 'Zend/Db/Adapter/Oracle/Exception.php';
00138 throw new Zend_Db_Adapter_Oracle_Exception(oci_error());
00139 }
00140 }
00141
00147 public function isConnected()
00148 {
00149 return ((bool) (is_resource($this->_connection)
00150 && get_resource_type($this->_connection) == 'oci8 connection'));
00151 }
00152
00158 public function closeConnection()
00159 {
00160 if ($this->isConnected()) {
00161 oci_close($this->_connection);
00162 }
00163 $this->_connection = null;
00164 }
00165
00172 public function setLobAsString($lobAsString)
00173 {
00174 $this->_lobAsString = (bool) $lobAsString;
00175 return $this;
00176 }
00177
00183 public function getLobAsString()
00184 {
00185 if ($this->_lobAsString === null) {
00186
00187 if (isset($this->_config['driver_options']) &&
00188 isset($this->_config['driver_options']['lob_as_string'])) {
00189 $this->_lobAsString = (bool) $this->_config['driver_options']['lob_as_string'];
00190 } else {
00191 $this->_lobAsString = false;
00192 }
00193 }
00194 return $this->_lobAsString;
00195 }
00196
00203 public function prepare($sql)
00204 {
00205 $this->_connect();
00206 $stmtClass = $this->_defaultStmtClass;
00207 if (!class_exists($stmtClass)) {
00208 require_once 'Zend/Loader.php';
00209 Zend_Loader::loadClass($stmtClass);
00210 }
00211 $stmt = new $stmtClass($this, $sql);
00212 if ($stmt instanceof Zend_Db_Statement_Oracle) {
00213 $stmt->setLobAsString($this->getLobAsString());
00214 }
00215 $stmt->setFetchMode($this->_fetchMode);
00216 return $stmt;
00217 }
00218
00225 protected function _quote($value)
00226 {
00227 if (is_int($value) || is_float($value)) {
00228 return $value;
00229 }
00230 $value = str_replace("'", "''", $value);
00231 return "'" . addcslashes($value, "\000\n\r\\\032") . "'";
00232 }
00233
00242 public function quoteTableAs($ident, $alias = null, $auto = false)
00243 {
00244
00245 return $this->_quoteIdentifierAs($ident, $alias, $auto, ' ');
00246 }
00247
00256 public function lastSequenceId($sequenceName)
00257 {
00258 $this->_connect();
00259 $sql = 'SELECT '.$this->quoteIdentifier($sequenceName, true).'.CURRVAL FROM dual';
00260 $value = $this->fetchOne($sql);
00261 return $value;
00262 }
00263
00272 public function nextSequenceId($sequenceName)
00273 {
00274 $this->_connect();
00275 $sql = 'SELECT '.$this->quoteIdentifier($sequenceName, true).'.NEXTVAL FROM dual';
00276 $value = $this->fetchOne($sql);
00277 return $value;
00278 }
00279
00297 public function lastInsertId($tableName = null, $primaryKey = null)
00298 {
00299 if ($tableName !== null) {
00300 $sequenceName = $tableName;
00301 if ($primaryKey) {
00302 $sequenceName .= "_$primaryKey";
00303 }
00304 $sequenceName .= '_seq';
00305 return $this->lastSequenceId($sequenceName);
00306 }
00307
00308
00309 return null;
00310 }
00311
00317 public function listTables()
00318 {
00319 $this->_connect();
00320 $data = $this->fetchCol('SELECT table_name FROM all_tables');
00321 return $data;
00322 }
00323
00354 public function describeTable($tableName, $schemaName = null)
00355 {
00356 $version = $this->getServerVersion();
00357 if (($version === null) || version_compare($version, '9.0.0', '>=')) {
00358 $sql = "SELECT TC.TABLE_NAME, TC.OWNER, TC.COLUMN_NAME, TC.DATA_TYPE,
00359 TC.DATA_DEFAULT, TC.NULLABLE, TC.COLUMN_ID, TC.DATA_LENGTH,
00360 TC.DATA_SCALE, TC.DATA_PRECISION, C.CONSTRAINT_TYPE, CC.POSITION
00361 FROM ALL_TAB_COLUMNS TC
00362 LEFT JOIN (ALL_CONS_COLUMNS CC JOIN ALL_CONSTRAINTS C
00363 ON (CC.CONSTRAINT_NAME = C.CONSTRAINT_NAME AND CC.TABLE_NAME = C.TABLE_NAME AND CC.OWNER = C.OWNER AND C.CONSTRAINT_TYPE = 'P'))
00364 ON TC.TABLE_NAME = CC.TABLE_NAME AND TC.COLUMN_NAME = CC.COLUMN_NAME
00365 WHERE UPPER(TC.TABLE_NAME) = UPPER(:TBNAME)";
00366 $bind[':TBNAME'] = $tableName;
00367 if ($schemaName) {
00368 $sql .= ' AND UPPER(TC.OWNER) = UPPER(:SCNAME)';
00369 $bind[':SCNAME'] = $schemaName;
00370 }
00371 $sql .= ' ORDER BY TC.COLUMN_ID';
00372 } else {
00373 $subSql="SELECT AC.OWNER, AC.TABLE_NAME, ACC.COLUMN_NAME, AC.CONSTRAINT_TYPE, ACC.POSITION
00374 from ALL_CONSTRAINTS AC, ALL_CONS_COLUMNS ACC
00375 WHERE ACC.CONSTRAINT_NAME = AC.CONSTRAINT_NAME
00376 AND ACC.TABLE_NAME = AC.TABLE_NAME
00377 AND ACC.OWNER = AC.OWNER
00378 AND AC.CONSTRAINT_TYPE = 'P'
00379 AND UPPER(AC.TABLE_NAME) = UPPER(:TBNAME)";
00380 $bind[':TBNAME'] = $tableName;
00381 if ($schemaName) {
00382 $subSql .= ' AND UPPER(ACC.OWNER) = UPPER(:SCNAME)';
00383 $bind[':SCNAME'] = $schemaName;
00384 }
00385 $sql="SELECT TC.TABLE_NAME, TC.OWNER, TC.COLUMN_NAME, TC.DATA_TYPE,
00386 TC.DATA_DEFAULT, TC.NULLABLE, TC.COLUMN_ID, TC.DATA_LENGTH,
00387 TC.DATA_SCALE, TC.DATA_PRECISION, CC.CONSTRAINT_TYPE, CC.POSITION
00388 FROM ALL_TAB_COLUMNS TC, ($subSql) CC
00389 WHERE UPPER(TC.TABLE_NAME) = UPPER(:TBNAME)
00390 AND TC.OWNER = CC.OWNER(+) AND TC.TABLE_NAME = CC.TABLE_NAME(+) AND TC.COLUMN_NAME = CC.COLUMN_NAME(+)";
00391 if ($schemaName) {
00392 $sql .= ' AND UPPER(TC.OWNER) = UPPER(:SCNAME)';
00393 }
00394 $sql .= ' ORDER BY TC.COLUMN_ID';
00395 }
00396
00397 $stmt = $this->query($sql, $bind);
00398
00402 $result = $stmt->fetchAll(Zend_Db::FETCH_NUM);
00403
00404 $table_name = 0;
00405 $owner = 1;
00406 $column_name = 2;
00407 $data_type = 3;
00408 $data_default = 4;
00409 $nullable = 5;
00410 $column_id = 6;
00411 $data_length = 7;
00412 $data_scale = 8;
00413 $data_precision = 9;
00414 $constraint_type = 10;
00415 $position = 11;
00416
00417 $desc = array();
00418 foreach ($result as $key => $row) {
00419 list ($primary, $primaryPosition, $identity) = array(false, null, false);
00420 if ($row[$constraint_type] == 'P') {
00421 $primary = true;
00422 $primaryPosition = $row[$position];
00426 $identity = false;
00427 }
00428 $desc[$this->foldCase($row[$column_name])] = array(
00429 'SCHEMA_NAME' => $this->foldCase($row[$owner]),
00430 'TABLE_NAME' => $this->foldCase($row[$table_name]),
00431 'COLUMN_NAME' => $this->foldCase($row[$column_name]),
00432 'COLUMN_POSITION' => $row[$column_id],
00433 'DATA_TYPE' => $row[$data_type],
00434 'DEFAULT' => $row[$data_default],
00435 'NULLABLE' => (bool) ($row[$nullable] == 'Y'),
00436 'LENGTH' => $row[$data_length],
00437 'SCALE' => $row[$data_scale],
00438 'PRECISION' => $row[$data_precision],
00439 'UNSIGNED' => null,
00440 'PRIMARY' => $primary,
00441 'PRIMARY_POSITION' => $primaryPosition,
00442 'IDENTITY' => $identity
00443 );
00444 }
00445 return $desc;
00446 }
00447
00453 protected function _beginTransaction()
00454 {
00455 $this->_setExecuteMode(OCI_DEFAULT);
00456 }
00457
00464 protected function _commit()
00465 {
00466 if (!oci_commit($this->_connection)) {
00470 require_once 'Zend/Db/Adapter/Oracle/Exception.php';
00471 throw new Zend_Db_Adapter_Oracle_Exception(oci_error($this->_connection));
00472 }
00473 $this->_setExecuteMode(OCI_COMMIT_ON_SUCCESS);
00474 }
00475
00482 protected function _rollBack()
00483 {
00484 if (!oci_rollback($this->_connection)) {
00488 require_once 'Zend/Db/Adapter/Oracle/Exception.php';
00489 throw new Zend_Db_Adapter_Oracle_Exception(oci_error($this->_connection));
00490 }
00491 $this->_setExecuteMode(OCI_COMMIT_ON_SUCCESS);
00492 }
00493
00503 public function setFetchMode($mode)
00504 {
00505 switch ($mode) {
00506 case Zend_Db::FETCH_NUM:
00507 case Zend_Db::FETCH_ASSOC:
00508 case Zend_Db::FETCH_BOTH:
00509 case Zend_Db::FETCH_OBJ:
00510 $this->_fetchMode = $mode;
00511 break;
00512 case Zend_Db::FETCH_BOUND:
00516 require_once 'Zend/Db/Adapter/Oracle/Exception.php';
00517 throw new Zend_Db_Adapter_Oracle_Exception('FETCH_BOUND is not supported yet');
00518 break;
00519 default:
00523 require_once 'Zend/Db/Adapter/Oracle/Exception.php';
00524 throw new Zend_Db_Adapter_Oracle_Exception("Invalid fetch mode '$mode' specified");
00525 break;
00526 }
00527 }
00528
00538 public function limit($sql, $count, $offset = 0)
00539 {
00540 $count = intval($count);
00541 if ($count <= 0) {
00545 require_once 'Zend/Db/Adapter/Oracle/Exception.php';
00546 throw new Zend_Db_Adapter_Oracle_Exception("LIMIT argument count=$count is not valid");
00547 }
00548
00549 $offset = intval($offset);
00550 if ($offset < 0) {
00554 require_once 'Zend/Db/Adapter/Oracle/Exception.php';
00555 throw new Zend_Db_Adapter_Oracle_Exception("LIMIT argument offset=$offset is not valid");
00556 }
00557
00564 $limit_sql = "SELECT z2.*
00565 FROM (
00566 SELECT z1.*, ROWNUM AS \"zend_db_rownum\"
00567 FROM (
00568 " . $sql . "
00569 ) z1
00570 ) z2
00571 WHERE z2.\"zend_db_rownum\" BETWEEN " . ($offset+1) . " AND " . ($offset+$count);
00572 return $limit_sql;
00573 }
00574
00579 private function _setExecuteMode($mode)
00580 {
00581 switch($mode) {
00582 case OCI_COMMIT_ON_SUCCESS:
00583 case OCI_DEFAULT:
00584 case OCI_DESCRIBE_ONLY:
00585 $this->_execute_mode = $mode;
00586 break;
00587 default:
00591 require_once 'Zend/Db/Adapter/Oracle/Exception.php';
00592 throw new Zend_Db_Adapter_Oracle_Exception("Invalid execution mode '$mode' specified");
00593 break;
00594 }
00595 }
00596
00600 public function _getExecuteMode()
00601 {
00602 return $this->_execute_mode;
00603 }
00604
00614 public function insert($table, array $bind)
00615 {
00616 $i = 0;
00617
00618 $cols = array();
00619 $vals = array();
00620 foreach ($bind as $col => $val) {
00621 $cols[] = $this->quoteIdentifier($col, true);
00622 if ($val instanceof Zend_Db_Expr) {
00623 $vals[] = $val->__toString();
00624 unset($bind[$col]);
00625 } else {
00626 $vals[] = ':'.$col.$i;
00627 unset($bind[$col]);
00628 $bind[':'.$col.$i] = $val;
00629 }
00630 $i++;
00631 }
00632
00633
00634 $sql = "INSERT INTO "
00635 . $this->quoteIdentifier($table, true)
00636 . ' (' . implode(', ', $cols) . ') '
00637 . 'VALUES (' . implode(', ', $vals) . ')';
00638
00639
00640 $stmt = $this->query($sql, $bind);
00641 $result = $stmt->rowCount();
00642 return $result;
00643 }
00644
00651 public function supportsParameters($type)
00652 {
00653 switch ($type) {
00654 case 'named':
00655 return true;
00656 case 'positional':
00657 default:
00658 return false;
00659 }
00660 }
00661
00667 public function getServerVersion()
00668 {
00669 $this->_connect();
00670 $version = oci_server_version($this->_connection);
00671 if ($version !== false) {
00672 $matches = null;
00673 if (preg_match('/((?:[0-9]{1,2}\.){1,3}[0-9]{1,2})/', $version, $matches)) {
00674 return $matches[1];
00675 } else {
00676 return null;
00677 }
00678 } else {
00679 return null;
00680 }
00681 }
00682 }