• Main Page
  • Related Pages
  • Namespaces
  • Data Structures
  • Files
  • Examples
  • File List

E:/E/GEAMP/www/openbiz/openbiz/others/Zend/Db/Adapter/Oracle.php

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             // connection already exists
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         // check the connection
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             // if never set by user, we use driver option if it exists otherwise false
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         // Oracle doesn't allow the 'AS' keyword between the table identifier/expression and alias.
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         // No support for IDENTITY columns; return null
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, // @todo
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:   // seq array
00507             case Zend_Db::FETCH_ASSOC: // assoc array
00508             case Zend_Db::FETCH_BOTH:  // seq+assoc array
00509             case Zend_Db::FETCH_OBJ:   // object
00510                 $this->_fetchMode = $mode;
00511                 break;
00512             case Zend_Db::FETCH_BOUND: // bound to PHP variable
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         // extract and quote col names from the array keys
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         // build the statement
00634         $sql = "INSERT INTO "
00635              . $this->quoteIdentifier($table, true)
00636              . ' (' . implode(', ', $cols) . ') '
00637              . 'VALUES (' . implode(', ', $vals) . ')';
00638 
00639         // execute the statement and return the number of affected rows
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 }

Generated on Thu Apr 19 2012 17:01:17 for openbiz by  doxygen 1.7.2