1: <?php
2: /**
3: * Zend Framework
4: *
5: * LICENSE
6: *
7: * This source file is subject to the new BSD license that is bundled
8: * with this package in the file LICENSE.txt.
9: * It is also available through the world-wide-web at this URL:
10: * http://framework.zend.com/license/new-bsd
11: * If you did not receive a copy of the license and are unable to
12: * obtain it through the world-wide-web, please send an email
13: * to license@zend.com so we can send you a copy immediately.
14: *
15: * @category Zend
16: * @package Zend_Db
17: * @subpackage Adapter
18: * @copyright Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com)
19: * @license http://framework.zend.com/license/new-bsd New BSD License
20: * @version $Id: Oracle.php 23573 2010-12-23 18:20:00Z mikaelkael $
21: */
22:
23: /**
24: * @see Zend_Db_Adapter_Abstract
25: */
26: require_once 'Zend/Db/Adapter/Abstract.php';
27:
28: /**
29: * @see Zend_Db_Statement_Oracle
30: */
31: require_once 'Zend/Db/Statement/Oracle.php';
32:
33: /**
34: * @category Zend
35: * @package Zend_Db
36: * @subpackage Adapter
37: * @copyright Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com)
38: * @license http://framework.zend.com/license/new-bsd New BSD License
39: */
40: class Zend_Db_Adapter_Oracle extends Zend_Db_Adapter_Abstract
41: {
42: /**
43: * User-provided configuration.
44: *
45: * Basic keys are:
46: *
47: * username => (string) Connect to the database as this username.
48: * password => (string) Password associated with the username.
49: * dbname => Either the name of the local Oracle instance, or the
50: * name of the entry in tnsnames.ora to which you want to connect.
51: * persistent => (boolean) Set TRUE to use a persistent connection
52: * @var array
53: */
54: protected $_config = array(
55: 'dbname' => null,
56: 'username' => null,
57: 'password' => null,
58: 'persistent' => false
59: );
60:
61: /**
62: * Keys are UPPERCASE SQL datatypes or the constants
63: * Zend_Db::INT_TYPE, Zend_Db::BIGINT_TYPE, or Zend_Db::FLOAT_TYPE.
64: *
65: * Values are:
66: * 0 = 32-bit integer
67: * 1 = 64-bit integer
68: * 2 = float or decimal
69: *
70: * @var array Associative array of datatypes to values 0, 1, or 2.
71: */
72: protected $_numericDataTypes = array(
73: Zend_Db::INT_TYPE => Zend_Db::INT_TYPE,
74: Zend_Db::BIGINT_TYPE => Zend_Db::BIGINT_TYPE,
75: Zend_Db::FLOAT_TYPE => Zend_Db::FLOAT_TYPE,
76: 'BINARY_DOUBLE' => Zend_Db::FLOAT_TYPE,
77: 'BINARY_FLOAT' => Zend_Db::FLOAT_TYPE,
78: 'NUMBER' => Zend_Db::FLOAT_TYPE,
79: );
80:
81: /**
82: * @var integer
83: */
84: protected $_execute_mode = null;
85:
86: /**
87: * Default class name for a DB statement.
88: *
89: * @var string
90: */
91: protected $_defaultStmtClass = 'Zend_Db_Statement_Oracle';
92:
93: /**
94: * Check if LOB field are returned as string
95: * instead of OCI-Lob object
96: *
97: * @var boolean
98: */
99: protected $_lobAsString = null;
100:
101: /**
102: * Creates a connection resource.
103: *
104: * @return void
105: * @throws Zend_Db_Adapter_Oracle_Exception
106: */
107: protected function _connect()
108: {
109: if (is_resource($this->_connection)) {
110: // connection already exists
111: return;
112: }
113:
114: if (!extension_loaded('oci8')) {
115: /**
116: * @see Zend_Db_Adapter_Oracle_Exception
117: */
118: require_once 'Zend/Db/Adapter/Oracle/Exception.php';
119: throw new Zend_Db_Adapter_Oracle_Exception('The OCI8 extension is required for this adapter but the extension is not loaded');
120: }
121:
122: $this->_setExecuteMode(OCI_COMMIT_ON_SUCCESS);
123:
124: $connectionFuncName = ($this->_config['persistent'] == true) ? 'oci_pconnect' : 'oci_connect';
125:
126: $this->_connection = @$connectionFuncName(
127: $this->_config['username'],
128: $this->_config['password'],
129: $this->_config['dbname'],
130: $this->_config['charset']);
131:
132: // check the connection
133: if (!$this->_connection) {
134: /**
135: * @see Zend_Db_Adapter_Oracle_Exception
136: */
137: require_once 'Zend/Db/Adapter/Oracle/Exception.php';
138: throw new Zend_Db_Adapter_Oracle_Exception(oci_error());
139: }
140: }
141:
142: /**
143: * Test if a connection is active
144: *
145: * @return boolean
146: */
147: public function isConnected()
148: {
149: return ((bool) (is_resource($this->_connection)
150: && (get_resource_type($this->_connection) == 'oci8 connection'
151: || get_resource_type($this->_connection) == 'oci8 persistent connection')));
152: }
153:
154: /**
155: * Force the connection to close.
156: *
157: * @return void
158: */
159: public function closeConnection()
160: {
161: if ($this->isConnected()) {
162: oci_close($this->_connection);
163: }
164: $this->_connection = null;
165: }
166:
167: /**
168: * Activate/deactivate return of LOB as string
169: *
170: * @param string $lob_as_string
171: * @return Zend_Db_Adapter_Oracle
172: */
173: public function setLobAsString($lobAsString)
174: {
175: $this->_lobAsString = (bool) $lobAsString;
176: return $this;
177: }
178:
179: /**
180: * Return whether or not LOB are returned as string
181: *
182: * @return boolean
183: */
184: public function getLobAsString()
185: {
186: if ($this->_lobAsString === null) {
187: // if never set by user, we use driver option if it exists otherwise false
188: if (isset($this->_config['driver_options']) &&
189: isset($this->_config['driver_options']['lob_as_string'])) {
190: $this->_lobAsString = (bool) $this->_config['driver_options']['lob_as_string'];
191: } else {
192: $this->_lobAsString = false;
193: }
194: }
195: return $this->_lobAsString;
196: }
197:
198: /**
199: * Returns an SQL statement for preparation.
200: *
201: * @param string $sql The SQL statement with placeholders.
202: * @return Zend_Db_Statement_Oracle
203: */
204: public function prepare($sql)
205: {
206: $this->_connect();
207: $stmtClass = $this->_defaultStmtClass;
208: if (!class_exists($stmtClass)) {
209: require_once 'Zend/Loader.php';
210: Zend_Loader::loadClass($stmtClass);
211: }
212: $stmt = new $stmtClass($this, $sql);
213: if ($stmt instanceof Zend_Db_Statement_Oracle) {
214: $stmt->setLobAsString($this->getLobAsString());
215: }
216: $stmt->setFetchMode($this->_fetchMode);
217: return $stmt;
218: }
219:
220: /**
221: * Quote a raw string.
222: *
223: * @param string $value Raw string
224: * @return string Quoted string
225: */
226: protected function _quote($value)
227: {
228: if (is_int($value) || is_float($value)) {
229: return $value;
230: }
231: $value = str_replace("'", "''", $value);
232: return "'" . addcslashes($value, "\000\n\r\\\032") . "'";
233: }
234:
235: /**
236: * Quote a table identifier and alias.
237: *
238: * @param string|array|Zend_Db_Expr $ident The identifier or expression.
239: * @param string $alias An alias for the table.
240: * @param boolean $auto If true, heed the AUTO_QUOTE_IDENTIFIERS config option.
241: * @return string The quoted identifier and alias.
242: */
243: public function quoteTableAs($ident, $alias = null, $auto = false)
244: {
245: // Oracle doesn't allow the 'AS' keyword between the table identifier/expression and alias.
246: return $this->_quoteIdentifierAs($ident, $alias, $auto, ' ');
247: }
248:
249: /**
250: * Return the most recent value from the specified sequence in the database.
251: * This is supported only on RDBMS brands that support sequences
252: * (e.g. Oracle, PostgreSQL, DB2). Other RDBMS brands return null.
253: *
254: * @param string $sequenceName
255: * @return string
256: */
257: public function lastSequenceId($sequenceName)
258: {
259: $this->_connect();
260: $sql = 'SELECT '.$this->quoteIdentifier($sequenceName, true).'.CURRVAL FROM dual';
261: $value = $this->fetchOne($sql);
262: return $value;
263: }
264:
265: /**
266: * Generate a new value from the specified sequence in the database, and return it.
267: * This is supported only on RDBMS brands that support sequences
268: * (e.g. Oracle, PostgreSQL, DB2). Other RDBMS brands return null.
269: *
270: * @param string $sequenceName
271: * @return string
272: */
273: public function nextSequenceId($sequenceName)
274: {
275: $this->_connect();
276: $sql = 'SELECT '.$this->quoteIdentifier($sequenceName, true).'.NEXTVAL FROM dual';
277: $value = $this->fetchOne($sql);
278: return $value;
279: }
280:
281: /**
282: * Gets the last ID generated automatically by an IDENTITY/AUTOINCREMENT column.
283: *
284: * As a convention, on RDBMS brands that support sequences
285: * (e.g. Oracle, PostgreSQL, DB2), this method forms the name of a sequence
286: * from the arguments and returns the last id generated by that sequence.
287: * On RDBMS brands that support IDENTITY/AUTOINCREMENT columns, this method
288: * returns the last value generated for such a column, and the table name
289: * argument is disregarded.
290: *
291: * Oracle does not support IDENTITY columns, so if the sequence is not
292: * specified, this method returns null.
293: *
294: * @param string $tableName OPTIONAL Name of table.
295: * @param string $primaryKey OPTIONAL Name of primary key column.
296: * @return string
297: */
298: public function lastInsertId($tableName = null, $primaryKey = null)
299: {
300: if ($tableName !== null) {
301: $sequenceName = $tableName;
302: if ($primaryKey) {
303: $sequenceName .= "_$primaryKey";
304: }
305: $sequenceName .= '_seq';
306: return $this->lastSequenceId($sequenceName);
307: }
308:
309: // No support for IDENTITY columns; return null
310: return null;
311: }
312:
313: /**
314: * Returns a list of the tables in the database.
315: *
316: * @return array
317: */
318: public function listTables()
319: {
320: $this->_connect();
321: $data = $this->fetchCol('SELECT table_name FROM all_tables');
322: return $data;
323: }
324:
325: /**
326: * Returns the column descriptions for a table.
327: *
328: * The return value is an associative array keyed by the column name,
329: * as returned by the RDBMS.
330: *
331: * The value of each array element is an associative array
332: * with the following keys:
333: *
334: * SCHEMA_NAME => string; name of schema
335: * TABLE_NAME => string;
336: * COLUMN_NAME => string; column name
337: * COLUMN_POSITION => number; ordinal position of column in table
338: * DATA_TYPE => string; SQL datatype name of column
339: * DEFAULT => string; default expression of column, null if none
340: * NULLABLE => boolean; true if column can have nulls
341: * LENGTH => number; length of CHAR/VARCHAR
342: * SCALE => number; scale of NUMERIC/DECIMAL
343: * PRECISION => number; precision of NUMERIC/DECIMAL
344: * UNSIGNED => boolean; unsigned property of an integer type
345: * PRIMARY => boolean; true if column is part of the primary key
346: * PRIMARY_POSITION => integer; position of column in primary key
347: * IDENTITY => integer; true if column is auto-generated with unique values
348: *
349: * @todo Discover integer unsigned property.
350: *
351: * @param string $tableName
352: * @param string $schemaName OPTIONAL
353: * @return array
354: */
355: public function describeTable($tableName, $schemaName = null)
356: {
357: $version = $this->getServerVersion();
358: if (($version === null) || version_compare($version, '9.0.0', '>=')) {
359: $sql = "SELECT TC.TABLE_NAME, TC.OWNER, TC.COLUMN_NAME, TC.DATA_TYPE,
360: TC.DATA_DEFAULT, TC.NULLABLE, TC.COLUMN_ID, TC.DATA_LENGTH,
361: TC.DATA_SCALE, TC.DATA_PRECISION, C.CONSTRAINT_TYPE, CC.POSITION
362: FROM ALL_TAB_COLUMNS TC
363: LEFT JOIN (ALL_CONS_COLUMNS CC JOIN ALL_CONSTRAINTS C
364: ON (CC.CONSTRAINT_NAME = C.CONSTRAINT_NAME AND CC.TABLE_NAME = C.TABLE_NAME AND CC.OWNER = C.OWNER AND C.CONSTRAINT_TYPE = 'P'))
365: ON TC.TABLE_NAME = CC.TABLE_NAME AND TC.COLUMN_NAME = CC.COLUMN_NAME
366: WHERE UPPER(TC.TABLE_NAME) = UPPER(:TBNAME)";
367: $bind[':TBNAME'] = $tableName;
368: if ($schemaName) {
369: $sql .= ' AND UPPER(TC.OWNER) = UPPER(:SCNAME)';
370: $bind[':SCNAME'] = $schemaName;
371: }
372: $sql .= ' ORDER BY TC.COLUMN_ID';
373: } else {
374: $subSql="SELECT AC.OWNER, AC.TABLE_NAME, ACC.COLUMN_NAME, AC.CONSTRAINT_TYPE, ACC.POSITION
375: from ALL_CONSTRAINTS AC, ALL_CONS_COLUMNS ACC
376: WHERE ACC.CONSTRAINT_NAME = AC.CONSTRAINT_NAME
377: AND ACC.TABLE_NAME = AC.TABLE_NAME
378: AND ACC.OWNER = AC.OWNER
379: AND AC.CONSTRAINT_TYPE = 'P'
380: AND UPPER(AC.TABLE_NAME) = UPPER(:TBNAME)";
381: $bind[':TBNAME'] = $tableName;
382: if ($schemaName) {
383: $subSql .= ' AND UPPER(ACC.OWNER) = UPPER(:SCNAME)';
384: $bind[':SCNAME'] = $schemaName;
385: }
386: $sql="SELECT TC.TABLE_NAME, TC.OWNER, TC.COLUMN_NAME, TC.DATA_TYPE,
387: TC.DATA_DEFAULT, TC.NULLABLE, TC.COLUMN_ID, TC.DATA_LENGTH,
388: TC.DATA_SCALE, TC.DATA_PRECISION, CC.CONSTRAINT_TYPE, CC.POSITION
389: FROM ALL_TAB_COLUMNS TC, ($subSql) CC
390: WHERE UPPER(TC.TABLE_NAME) = UPPER(:TBNAME)
391: AND TC.OWNER = CC.OWNER(+) AND TC.TABLE_NAME = CC.TABLE_NAME(+) AND TC.COLUMN_NAME = CC.COLUMN_NAME(+)";
392: if ($schemaName) {
393: $sql .= ' AND UPPER(TC.OWNER) = UPPER(:SCNAME)';
394: }
395: $sql .= ' ORDER BY TC.COLUMN_ID';
396: }
397:
398: $stmt = $this->query($sql, $bind);
399:
400: /**
401: * Use FETCH_NUM so we are not dependent on the CASE attribute of the PDO connection
402: */
403: $result = $stmt->fetchAll(Zend_Db::FETCH_NUM);
404:
405: $table_name = 0;
406: $owner = 1;
407: $column_name = 2;
408: $data_type = 3;
409: $data_default = 4;
410: $nullable = 5;
411: $column_id = 6;
412: $data_length = 7;
413: $data_scale = 8;
414: $data_precision = 9;
415: $constraint_type = 10;
416: $position = 11;
417:
418: $desc = array();
419: foreach ($result as $key => $row) {
420: list ($primary, $primaryPosition, $identity) = array(false, null, false);
421: if ($row[$constraint_type] == 'P') {
422: $primary = true;
423: $primaryPosition = $row[$position];
424: /**
425: * Oracle does not support auto-increment keys.
426: */
427: $identity = false;
428: }
429: $desc[$this->foldCase($row[$column_name])] = array(
430: 'SCHEMA_NAME' => $this->foldCase($row[$owner]),
431: 'TABLE_NAME' => $this->foldCase($row[$table_name]),
432: 'COLUMN_NAME' => $this->foldCase($row[$column_name]),
433: 'COLUMN_POSITION' => $row[$column_id],
434: 'DATA_TYPE' => $row[$data_type],
435: 'DEFAULT' => $row[$data_default],
436: 'NULLABLE' => (bool) ($row[$nullable] == 'Y'),
437: 'LENGTH' => $row[$data_length],
438: 'SCALE' => $row[$data_scale],
439: 'PRECISION' => $row[$data_precision],
440: 'UNSIGNED' => null, // @todo
441: 'PRIMARY' => $primary,
442: 'PRIMARY_POSITION' => $primaryPosition,
443: 'IDENTITY' => $identity
444: );
445: }
446: return $desc;
447: }
448:
449: /**
450: * Leave autocommit mode and begin a transaction.
451: *
452: * @return void
453: */
454: protected function _beginTransaction()
455: {
456: $this->_setExecuteMode(OCI_DEFAULT);
457: }
458:
459: /**
460: * Commit a transaction and return to autocommit mode.
461: *
462: * @return void
463: * @throws Zend_Db_Adapter_Oracle_Exception
464: */
465: protected function _commit()
466: {
467: if (!oci_commit($this->_connection)) {
468: /**
469: * @see Zend_Db_Adapter_Oracle_Exception
470: */
471: require_once 'Zend/Db/Adapter/Oracle/Exception.php';
472: throw new Zend_Db_Adapter_Oracle_Exception(oci_error($this->_connection));
473: }
474: $this->_setExecuteMode(OCI_COMMIT_ON_SUCCESS);
475: }
476:
477: /**
478: * Roll back a transaction and return to autocommit mode.
479: *
480: * @return void
481: * @throws Zend_Db_Adapter_Oracle_Exception
482: */
483: protected function _rollBack()
484: {
485: if (!oci_rollback($this->_connection)) {
486: /**
487: * @see Zend_Db_Adapter_Oracle_Exception
488: */
489: require_once 'Zend/Db/Adapter/Oracle/Exception.php';
490: throw new Zend_Db_Adapter_Oracle_Exception(oci_error($this->_connection));
491: }
492: $this->_setExecuteMode(OCI_COMMIT_ON_SUCCESS);
493: }
494:
495: /**
496: * Set the fetch mode.
497: *
498: * @todo Support FETCH_CLASS and FETCH_INTO.
499: *
500: * @param integer $mode A fetch mode.
501: * @return void
502: * @throws Zend_Db_Adapter_Oracle_Exception
503: */
504: public function setFetchMode($mode)
505: {
506: switch ($mode) {
507: case Zend_Db::FETCH_NUM: // seq array
508: case Zend_Db::FETCH_ASSOC: // assoc array
509: case Zend_Db::FETCH_BOTH: // seq+assoc array
510: case Zend_Db::FETCH_OBJ: // object
511: $this->_fetchMode = $mode;
512: break;
513: case Zend_Db::FETCH_BOUND: // bound to PHP variable
514: /**
515: * @see Zend_Db_Adapter_Oracle_Exception
516: */
517: require_once 'Zend/Db/Adapter/Oracle/Exception.php';
518: throw new Zend_Db_Adapter_Oracle_Exception('FETCH_BOUND is not supported yet');
519: break;
520: default:
521: /**
522: * @see Zend_Db_Adapter_Oracle_Exception
523: */
524: require_once 'Zend/Db/Adapter/Oracle/Exception.php';
525: throw new Zend_Db_Adapter_Oracle_Exception("Invalid fetch mode '$mode' specified");
526: break;
527: }
528: }
529:
530: /**
531: * Adds an adapter-specific LIMIT clause to the SELECT statement.
532: *
533: * @param string $sql
534: * @param integer $count
535: * @param integer $offset OPTIONAL
536: * @return string
537: * @throws Zend_Db_Adapter_Oracle_Exception
538: */
539: public function limit($sql, $count, $offset = 0)
540: {
541: $count = intval($count);
542: if ($count <= 0) {
543: /**
544: * @see Zend_Db_Adapter_Oracle_Exception
545: */
546: require_once 'Zend/Db/Adapter/Oracle/Exception.php';
547: throw new Zend_Db_Adapter_Oracle_Exception("LIMIT argument count=$count is not valid");
548: }
549:
550: $offset = intval($offset);
551: if ($offset < 0) {
552: /**
553: * @see Zend_Db_Adapter_Oracle_Exception
554: */
555: require_once 'Zend/Db/Adapter/Oracle/Exception.php';
556: throw new Zend_Db_Adapter_Oracle_Exception("LIMIT argument offset=$offset is not valid");
557: }
558:
559: /**
560: * Oracle does not implement the LIMIT clause as some RDBMS do.
561: * We have to simulate it with subqueries and ROWNUM.
562: * Unfortunately because we use the column wildcard "*",
563: * this puts an extra column into the query result set.
564: */
565: $limit_sql = "SELECT z2.*
566: FROM (
567: SELECT z1.*, ROWNUM AS \"zend_db_rownum\"
568: FROM (
569: " . $sql . "
570: ) z1
571: ) z2
572: WHERE z2.\"zend_db_rownum\" BETWEEN " . ($offset+1) . " AND " . ($offset+$count);
573: return $limit_sql;
574: }
575:
576: /**
577: * @param integer $mode
578: * @throws Zend_Db_Adapter_Oracle_Exception
579: */
580: private function _setExecuteMode($mode)
581: {
582: switch($mode) {
583: case OCI_COMMIT_ON_SUCCESS:
584: case OCI_DEFAULT:
585: case OCI_DESCRIBE_ONLY:
586: $this->_execute_mode = $mode;
587: break;
588: default:
589: /**
590: * @see Zend_Db_Adapter_Oracle_Exception
591: */
592: require_once 'Zend/Db/Adapter/Oracle/Exception.php';
593: throw new Zend_Db_Adapter_Oracle_Exception("Invalid execution mode '$mode' specified");
594: break;
595: }
596: }
597:
598: /**
599: * @return int
600: */
601: public function _getExecuteMode()
602: {
603: return $this->_execute_mode;
604: }
605:
606: /**
607: * Check if the adapter supports real SQL parameters.
608: *
609: * @param string $type 'positional' or 'named'
610: * @return bool
611: */
612: public function supportsParameters($type)
613: {
614: switch ($type) {
615: case 'named':
616: return true;
617: case 'positional':
618: default:
619: return false;
620: }
621: }
622:
623: /**
624: * Retrieve server version in PHP style
625: *
626: * @return string
627: */
628: public function getServerVersion()
629: {
630: $this->_connect();
631: $version = oci_server_version($this->_connection);
632: if ($version !== false) {
633: $matches = null;
634: if (preg_match('/((?:[0-9]{1,2}\.){1,3}[0-9]{1,2})/', $version, $matches)) {
635: return $matches[1];
636: } else {
637: return null;
638: }
639: } else {
640: return null;
641: }
642: }
643: }
644: