00001 <?php
00027 require_once 'Zend/Db/Adapter/Pdo/Abstract.php';
00028
00029
00039 class Zend_Db_Adapter_Pdo_Oci extends Zend_Db_Adapter_Pdo_Abstract
00040 {
00041
00047 protected $_pdoType = 'oci';
00048
00054 protected $_defaultStmtClass = 'Zend_Db_Statement_Pdo_Oci';
00055
00067 protected $_numericDataTypes = array(
00068 Zend_Db::INT_TYPE => Zend_Db::INT_TYPE,
00069 Zend_Db::BIGINT_TYPE => Zend_Db::BIGINT_TYPE,
00070 Zend_Db::FLOAT_TYPE => Zend_Db::FLOAT_TYPE,
00071 'BINARY_DOUBLE' => Zend_Db::FLOAT_TYPE,
00072 'BINARY_FLOAT' => Zend_Db::FLOAT_TYPE,
00073 'NUMBER' => Zend_Db::FLOAT_TYPE
00074 );
00075
00081 protected function _dsn()
00082 {
00083
00084 $dsn = $this->_config;
00085
00086 if (isset($dsn['host'])) {
00087 $tns = 'dbname=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)' .
00088 '(HOST=' . $dsn['host'] . ')';
00089
00090 if (isset($dsn['port'])) {
00091 $tns .= '(PORT=' . $dsn['port'] . ')';
00092 } else {
00093 $tns .= '(PORT=1521)';
00094 }
00095
00096 $tns .= '))(CONNECT_DATA=(SID=' . $dsn['dbname'] . ')))';
00097 } else {
00098 $tns = 'dbname=' . $dsn['dbname'];
00099 }
00100
00101 if (isset($dsn['charset'])) {
00102 $tns .= ';charset=' . $dsn['charset'];
00103 }
00104
00105 return $this->_pdoType . ':' . $tns;
00106 }
00107
00117 protected function _quote($value)
00118 {
00119 if (is_int($value) || is_float($value)) {
00120 return $value;
00121 }
00122 $value = str_replace("'", "''", $value);
00123 return "'" . addcslashes($value, "\000\n\r\\\032") . "'";
00124 }
00125
00133 public function quoteTableAs($ident, $alias = null, $auto = false)
00134 {
00135
00136 return $this->_quoteIdentifierAs($ident, $alias, $auto, ' ');
00137 }
00138
00144 public function listTables()
00145 {
00146 $data = $this->fetchCol('SELECT table_name FROM all_tables');
00147 return $data;
00148 }
00149
00180 public function describeTable($tableName, $schemaName = null)
00181 {
00182 $version = $this->getServerVersion();
00183 if (($version === null) || version_compare($version, '9.0.0', '>=')) {
00184 $sql = "SELECT TC.TABLE_NAME, TC.OWNER, TC.COLUMN_NAME, TC.DATA_TYPE,
00185 TC.DATA_DEFAULT, TC.NULLABLE, TC.COLUMN_ID, TC.DATA_LENGTH,
00186 TC.DATA_SCALE, TC.DATA_PRECISION, C.CONSTRAINT_TYPE, CC.POSITION
00187 FROM ALL_TAB_COLUMNS TC
00188 LEFT JOIN (ALL_CONS_COLUMNS CC JOIN ALL_CONSTRAINTS C
00189 ON (CC.CONSTRAINT_NAME = C.CONSTRAINT_NAME AND CC.TABLE_NAME = C.TABLE_NAME AND CC.OWNER = C.OWNER AND C.CONSTRAINT_TYPE = 'P'))
00190 ON TC.TABLE_NAME = CC.TABLE_NAME AND TC.COLUMN_NAME = CC.COLUMN_NAME
00191 WHERE UPPER(TC.TABLE_NAME) = UPPER(:TBNAME)";
00192 $bind[':TBNAME'] = $tableName;
00193 if ($schemaName) {
00194 $sql .= ' AND UPPER(TC.OWNER) = UPPER(:SCNAME)';
00195 $bind[':SCNAME'] = $schemaName;
00196 }
00197 $sql .= ' ORDER BY TC.COLUMN_ID';
00198 } else {
00199 $subSql="SELECT AC.OWNER, AC.TABLE_NAME, ACC.COLUMN_NAME, AC.CONSTRAINT_TYPE, ACC.POSITION
00200 from ALL_CONSTRAINTS AC, ALL_CONS_COLUMNS ACC
00201 WHERE ACC.CONSTRAINT_NAME = AC.CONSTRAINT_NAME
00202 AND ACC.TABLE_NAME = AC.TABLE_NAME
00203 AND ACC.OWNER = AC.OWNER
00204 AND AC.CONSTRAINT_TYPE = 'P'
00205 AND UPPER(AC.TABLE_NAME) = UPPER(:TBNAME)";
00206 $bind[':TBNAME'] = $tableName;
00207 if ($schemaName) {
00208 $subSql .= ' AND UPPER(ACC.OWNER) = UPPER(:SCNAME)';
00209 $bind[':SCNAME'] = $schemaName;
00210 }
00211 $sql="SELECT TC.TABLE_NAME, TC.OWNER, TC.COLUMN_NAME, TC.DATA_TYPE,
00212 TC.DATA_DEFAULT, TC.NULLABLE, TC.COLUMN_ID, TC.DATA_LENGTH,
00213 TC.DATA_SCALE, TC.DATA_PRECISION, CC.CONSTRAINT_TYPE, CC.POSITION
00214 FROM ALL_TAB_COLUMNS TC, ($subSql) CC
00215 WHERE UPPER(TC.TABLE_NAME) = UPPER(:TBNAME)
00216 AND TC.OWNER = CC.OWNER(+) AND TC.TABLE_NAME = CC.TABLE_NAME(+) AND TC.COLUMN_NAME = CC.COLUMN_NAME(+)";
00217 if ($schemaName) {
00218 $sql .= ' AND UPPER(TC.OWNER) = UPPER(:SCNAME)';
00219 }
00220 $sql .= ' ORDER BY TC.COLUMN_ID';
00221 }
00222
00223 $stmt = $this->query($sql, $bind);
00224
00228 $result = $stmt->fetchAll(Zend_Db::FETCH_NUM);
00229
00230 $table_name = 0;
00231 $owner = 1;
00232 $column_name = 2;
00233 $data_type = 3;
00234 $data_default = 4;
00235 $nullable = 5;
00236 $column_id = 6;
00237 $data_length = 7;
00238 $data_scale = 8;
00239 $data_precision = 9;
00240 $constraint_type = 10;
00241 $position = 11;
00242
00243 $desc = array();
00244 foreach ($result as $key => $row) {
00245 list ($primary, $primaryPosition, $identity) = array(false, null, false);
00246 if ($row[$constraint_type] == 'P') {
00247 $primary = true;
00248 $primaryPosition = $row[$position];
00252 $identity = false;
00253 }
00254 $desc[$this->foldCase($row[$column_name])] = array(
00255 'SCHEMA_NAME' => $this->foldCase($row[$owner]),
00256 'TABLE_NAME' => $this->foldCase($row[$table_name]),
00257 'COLUMN_NAME' => $this->foldCase($row[$column_name]),
00258 'COLUMN_POSITION' => $row[$column_id],
00259 'DATA_TYPE' => $row[$data_type],
00260 'DEFAULT' => $row[$data_default],
00261 'NULLABLE' => (bool) ($row[$nullable] == 'Y'),
00262 'LENGTH' => $row[$data_length],
00263 'SCALE' => $row[$data_scale],
00264 'PRECISION' => $row[$data_precision],
00265 'UNSIGNED' => null,
00266 'PRIMARY' => $primary,
00267 'PRIMARY_POSITION' => $primaryPosition,
00268 'IDENTITY' => $identity
00269 );
00270 }
00271 return $desc;
00272 }
00273
00282 public function lastSequenceId($sequenceName)
00283 {
00284 $this->_connect();
00285 $value = $this->fetchOne('SELECT '.$this->quoteIdentifier($sequenceName, true).'.CURRVAL FROM dual');
00286 return $value;
00287 }
00288
00297 public function nextSequenceId($sequenceName)
00298 {
00299 $this->_connect();
00300 $value = $this->fetchOne('SELECT '.$this->quoteIdentifier($sequenceName, true).'.NEXTVAL FROM dual');
00301 return $value;
00302 }
00303
00322 public function lastInsertId($tableName = null, $primaryKey = null)
00323 {
00324 if ($tableName !== null) {
00325 $sequenceName = $tableName;
00326 if ($primaryKey) {
00327 $sequenceName .= $this->foldCase("_$primaryKey");
00328 }
00329 $sequenceName .= $this->foldCase('_seq');
00330 return $this->lastSequenceId($sequenceName);
00331 }
00332
00333 return null;
00334 }
00335
00345 public function limit($sql, $count, $offset = 0)
00346 {
00347 $count = intval($count);
00348 if ($count <= 0) {
00350 require_once 'Zend/Db/Adapter/Exception.php';
00351 throw new Zend_Db_Adapter_Exception("LIMIT argument count=$count is not valid");
00352 }
00353
00354 $offset = intval($offset);
00355 if ($offset < 0) {
00357 require_once 'Zend/Db/Adapter/Exception.php';
00358 throw new Zend_Db_Adapter_Exception("LIMIT argument offset=$offset is not valid");
00359 }
00360
00367 $limit_sql = "SELECT z2.*
00368 FROM (
00369 SELECT z1.*, ROWNUM AS \"zend_db_rownum\"
00370 FROM (
00371 " . $sql . "
00372 ) z1
00373 ) z2
00374 WHERE z2.\"zend_db_rownum\" BETWEEN " . ($offset+1) . " AND " . ($offset+$count);
00375 return $limit_sql;
00376 }
00377
00378 }