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: Oci.php 20096 2010-01-06 02:05:09Z bkarwin $
21: */
22:
23:
24: /**
25: * @see Zend_Db_Adapter_Pdo_Abstract
26: */
27: require_once 'Zend/Db/Adapter/Pdo/Abstract.php';
28:
29:
30: /**
31: * Class for connecting to Oracle databases and performing common operations.
32: *
33: * @category Zend
34: * @package Zend_Db
35: * @subpackage Adapter
36: * @copyright Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com)
37: * @license http://framework.zend.com/license/new-bsd New BSD License
38: */
39: class Zend_Db_Adapter_Pdo_Oci extends Zend_Db_Adapter_Pdo_Abstract
40: {
41:
42: /**
43: * PDO type.
44: *
45: * @var string
46: */
47: protected $_pdoType = 'oci';
48:
49: /**
50: * Default class name for a DB statement.
51: *
52: * @var string
53: */
54: protected $_defaultStmtClass = 'Zend_Db_Statement_Pdo_Oci';
55:
56: /**
57: * Keys are UPPERCASE SQL datatypes or the constants
58: * Zend_Db::INT_TYPE, Zend_Db::BIGINT_TYPE, or Zend_Db::FLOAT_TYPE.
59: *
60: * Values are:
61: * 0 = 32-bit integer
62: * 1 = 64-bit integer
63: * 2 = float or decimal
64: *
65: * @var array Associative array of datatypes to values 0, 1, or 2.
66: */
67: protected $_numericDataTypes = array(
68: Zend_Db::INT_TYPE => Zend_Db::INT_TYPE,
69: Zend_Db::BIGINT_TYPE => Zend_Db::BIGINT_TYPE,
70: Zend_Db::FLOAT_TYPE => Zend_Db::FLOAT_TYPE,
71: 'BINARY_DOUBLE' => Zend_Db::FLOAT_TYPE,
72: 'BINARY_FLOAT' => Zend_Db::FLOAT_TYPE,
73: 'NUMBER' => Zend_Db::FLOAT_TYPE
74: );
75:
76: /**
77: * Creates a PDO DSN for the adapter from $this->_config settings.
78: *
79: * @return string
80: */
81: protected function _dsn()
82: {
83: // baseline of DSN parts
84: $dsn = $this->_config;
85:
86: if (isset($dsn['host'])) {
87: $tns = 'dbname=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)' .
88: '(HOST=' . $dsn['host'] . ')';
89:
90: if (isset($dsn['port'])) {
91: $tns .= '(PORT=' . $dsn['port'] . ')';
92: } else {
93: $tns .= '(PORT=1521)';
94: }
95:
96: $tns .= '))(CONNECT_DATA=(SID=' . $dsn['dbname'] . ')))';
97: } else {
98: $tns = 'dbname=' . $dsn['dbname'];
99: }
100:
101: if (isset($dsn['charset'])) {
102: $tns .= ';charset=' . $dsn['charset'];
103: }
104:
105: return $this->_pdoType . ':' . $tns;
106: }
107:
108: /**
109: * Quote a raw string.
110: * Most PDO drivers have an implementation for the quote() method,
111: * but the Oracle OCI driver must use the same implementation as the
112: * Zend_Db_Adapter_Abstract class.
113: *
114: * @param string $value Raw string
115: * @return string Quoted string
116: */
117: protected function _quote($value)
118: {
119: if (is_int($value) || is_float($value)) {
120: return $value;
121: }
122: $value = str_replace("'", "''", $value);
123: return "'" . addcslashes($value, "\000\n\r\\\032") . "'";
124: }
125:
126: /**
127: * Quote a table identifier and alias.
128: *
129: * @param string|array|Zend_Db_Expr $ident The identifier or expression.
130: * @param string $alias An alias for the table.
131: * @return string The quoted identifier and alias.
132: */
133: public function quoteTableAs($ident, $alias = null, $auto = false)
134: {
135: // Oracle doesn't allow the 'AS' keyword between the table identifier/expression and alias.
136: return $this->_quoteIdentifierAs($ident, $alias, $auto, ' ');
137: }
138:
139: /**
140: * Returns a list of the tables in the database.
141: *
142: * @return array
143: */
144: public function listTables()
145: {
146: $data = $this->fetchCol('SELECT table_name FROM all_tables');
147: return $data;
148: }
149:
150: /**
151: * Returns the column descriptions for a table.
152: *
153: * The return value is an associative array keyed by the column name,
154: * as returned by the RDBMS.
155: *
156: * The value of each array element is an associative array
157: * with the following keys:
158: *
159: * SCHEMA_NAME => string; name of schema
160: * TABLE_NAME => string;
161: * COLUMN_NAME => string; column name
162: * COLUMN_POSITION => number; ordinal position of column in table
163: * DATA_TYPE => string; SQL datatype name of column
164: * DEFAULT => string; default expression of column, null if none
165: * NULLABLE => boolean; true if column can have nulls
166: * LENGTH => number; length of CHAR/VARCHAR
167: * SCALE => number; scale of NUMERIC/DECIMAL
168: * PRECISION => number; precision of NUMERIC/DECIMAL
169: * UNSIGNED => boolean; unsigned property of an integer type
170: * PRIMARY => boolean; true if column is part of the primary key
171: * PRIMARY_POSITION => integer; position of column in primary key
172: * IDENTITY => integer; true if column is auto-generated with unique values
173: *
174: * @todo Discover integer unsigned property.
175: *
176: * @param string $tableName
177: * @param string $schemaName OPTIONAL
178: * @return array
179: */
180: public function describeTable($tableName, $schemaName = null)
181: {
182: $version = $this->getServerVersion();
183: if (($version === null) || version_compare($version, '9.0.0', '>=')) {
184: $sql = "SELECT TC.TABLE_NAME, TC.OWNER, TC.COLUMN_NAME, TC.DATA_TYPE,
185: TC.DATA_DEFAULT, TC.NULLABLE, TC.COLUMN_ID, TC.DATA_LENGTH,
186: TC.DATA_SCALE, TC.DATA_PRECISION, C.CONSTRAINT_TYPE, CC.POSITION
187: FROM ALL_TAB_COLUMNS TC
188: LEFT JOIN (ALL_CONS_COLUMNS CC JOIN ALL_CONSTRAINTS C
189: ON (CC.CONSTRAINT_NAME = C.CONSTRAINT_NAME AND CC.TABLE_NAME = C.TABLE_NAME AND CC.OWNER = C.OWNER AND C.CONSTRAINT_TYPE = 'P'))
190: ON TC.TABLE_NAME = CC.TABLE_NAME AND TC.COLUMN_NAME = CC.COLUMN_NAME
191: WHERE UPPER(TC.TABLE_NAME) = UPPER(:TBNAME)";
192: $bind[':TBNAME'] = $tableName;
193: if ($schemaName) {
194: $sql .= ' AND UPPER(TC.OWNER) = UPPER(:SCNAME)';
195: $bind[':SCNAME'] = $schemaName;
196: }
197: $sql .= ' ORDER BY TC.COLUMN_ID';
198: } else {
199: $subSql="SELECT AC.OWNER, AC.TABLE_NAME, ACC.COLUMN_NAME, AC.CONSTRAINT_TYPE, ACC.POSITION
200: from ALL_CONSTRAINTS AC, ALL_CONS_COLUMNS ACC
201: WHERE ACC.CONSTRAINT_NAME = AC.CONSTRAINT_NAME
202: AND ACC.TABLE_NAME = AC.TABLE_NAME
203: AND ACC.OWNER = AC.OWNER
204: AND AC.CONSTRAINT_TYPE = 'P'
205: AND UPPER(AC.TABLE_NAME) = UPPER(:TBNAME)";
206: $bind[':TBNAME'] = $tableName;
207: if ($schemaName) {
208: $subSql .= ' AND UPPER(ACC.OWNER) = UPPER(:SCNAME)';
209: $bind[':SCNAME'] = $schemaName;
210: }
211: $sql="SELECT TC.TABLE_NAME, TC.OWNER, TC.COLUMN_NAME, TC.DATA_TYPE,
212: TC.DATA_DEFAULT, TC.NULLABLE, TC.COLUMN_ID, TC.DATA_LENGTH,
213: TC.DATA_SCALE, TC.DATA_PRECISION, CC.CONSTRAINT_TYPE, CC.POSITION
214: FROM ALL_TAB_COLUMNS TC, ($subSql) CC
215: WHERE UPPER(TC.TABLE_NAME) = UPPER(:TBNAME)
216: AND TC.OWNER = CC.OWNER(+) AND TC.TABLE_NAME = CC.TABLE_NAME(+) AND TC.COLUMN_NAME = CC.COLUMN_NAME(+)";
217: if ($schemaName) {
218: $sql .= ' AND UPPER(TC.OWNER) = UPPER(:SCNAME)';
219: }
220: $sql .= ' ORDER BY TC.COLUMN_ID';
221: }
222:
223: $stmt = $this->query($sql, $bind);
224:
225: /**
226: * Use FETCH_NUM so we are not dependent on the CASE attribute of the PDO connection
227: */
228: $result = $stmt->fetchAll(Zend_Db::FETCH_NUM);
229:
230: $table_name = 0;
231: $owner = 1;
232: $column_name = 2;
233: $data_type = 3;
234: $data_default = 4;
235: $nullable = 5;
236: $column_id = 6;
237: $data_length = 7;
238: $data_scale = 8;
239: $data_precision = 9;
240: $constraint_type = 10;
241: $position = 11;
242:
243: $desc = array();
244: foreach ($result as $key => $row) {
245: list ($primary, $primaryPosition, $identity) = array(false, null, false);
246: if ($row[$constraint_type] == 'P') {
247: $primary = true;
248: $primaryPosition = $row[$position];
249: /**
250: * Oracle does not support auto-increment keys.
251: */
252: $identity = false;
253: }
254: $desc[$this->foldCase($row[$column_name])] = array(
255: 'SCHEMA_NAME' => $this->foldCase($row[$owner]),
256: 'TABLE_NAME' => $this->foldCase($row[$table_name]),
257: 'COLUMN_NAME' => $this->foldCase($row[$column_name]),
258: 'COLUMN_POSITION' => $row[$column_id],
259: 'DATA_TYPE' => $row[$data_type],
260: 'DEFAULT' => $row[$data_default],
261: 'NULLABLE' => (bool) ($row[$nullable] == 'Y'),
262: 'LENGTH' => $row[$data_length],
263: 'SCALE' => $row[$data_scale],
264: 'PRECISION' => $row[$data_precision],
265: 'UNSIGNED' => null, // @todo
266: 'PRIMARY' => $primary,
267: 'PRIMARY_POSITION' => $primaryPosition,
268: 'IDENTITY' => $identity
269: );
270: }
271: return $desc;
272: }
273:
274: /**
275: * Return the most recent value from the specified sequence in the database.
276: * This is supported only on RDBMS brands that support sequences
277: * (e.g. Oracle, PostgreSQL, DB2). Other RDBMS brands return null.
278: *
279: * @param string $sequenceName
280: * @return integer
281: */
282: public function lastSequenceId($sequenceName)
283: {
284: $this->_connect();
285: $value = $this->fetchOne('SELECT '.$this->quoteIdentifier($sequenceName, true).'.CURRVAL FROM dual');
286: return $value;
287: }
288:
289: /**
290: * Generate a new value from the specified sequence in the database, and return it.
291: * This is supported only on RDBMS brands that support sequences
292: * (e.g. Oracle, PostgreSQL, DB2). Other RDBMS brands return null.
293: *
294: * @param string $sequenceName
295: * @return integer
296: */
297: public function nextSequenceId($sequenceName)
298: {
299: $this->_connect();
300: $value = $this->fetchOne('SELECT '.$this->quoteIdentifier($sequenceName, true).'.NEXTVAL FROM dual');
301: return $value;
302: }
303:
304: /**
305: * Gets the last ID generated automatically by an IDENTITY/AUTOINCREMENT column.
306: *
307: * As a convention, on RDBMS brands that support sequences
308: * (e.g. Oracle, PostgreSQL, DB2), this method forms the name of a sequence
309: * from the arguments and returns the last id generated by that sequence.
310: * On RDBMS brands that support IDENTITY/AUTOINCREMENT columns, this method
311: * returns the last value generated for such a column, and the table name
312: * argument is disregarded.
313: *
314: * Oracle does not support IDENTITY columns, so if the sequence is not
315: * specified, this method returns null.
316: *
317: * @param string $tableName OPTIONAL Name of table.
318: * @param string $primaryKey OPTIONAL Name of primary key column.
319: * @return string
320: * @throws Zend_Db_Adapter_Oracle_Exception
321: */
322: public function lastInsertId($tableName = null, $primaryKey = null)
323: {
324: if ($tableName !== null) {
325: $sequenceName = $tableName;
326: if ($primaryKey) {
327: $sequenceName .= $this->foldCase("_$primaryKey");
328: }
329: $sequenceName .= $this->foldCase('_seq');
330: return $this->lastSequenceId($sequenceName);
331: }
332: // No support for IDENTITY columns; return null
333: return null;
334: }
335:
336: /**
337: * Adds an adapter-specific LIMIT clause to the SELECT statement.
338: *
339: * @param string $sql
340: * @param integer $count
341: * @param integer $offset
342: * @throws Zend_Db_Adapter_Exception
343: * @return string
344: */
345: public function limit($sql, $count, $offset = 0)
346: {
347: $count = intval($count);
348: if ($count <= 0) {
349: /** @see Zend_Db_Adapter_Exception */
350: require_once 'Zend/Db/Adapter/Exception.php';
351: throw new Zend_Db_Adapter_Exception("LIMIT argument count=$count is not valid");
352: }
353:
354: $offset = intval($offset);
355: if ($offset < 0) {
356: /** @see Zend_Db_Adapter_Exception */
357: require_once 'Zend/Db/Adapter/Exception.php';
358: throw new Zend_Db_Adapter_Exception("LIMIT argument offset=$offset is not valid");
359: }
360:
361: /**
362: * Oracle does not implement the LIMIT clause as some RDBMS do.
363: * We have to simulate it with subqueries and ROWNUM.
364: * Unfortunately because we use the column wildcard "*",
365: * this puts an extra column into the query result set.
366: */
367: $limit_sql = "SELECT z2.*
368: FROM (
369: SELECT z1.*, ROWNUM AS \"zend_db_rownum\"
370: FROM (
371: " . $sql . "
372: ) z1
373: ) z2
374: WHERE z2.\"zend_db_rownum\" BETWEEN " . ($offset+1) . " AND " . ($offset+$count);
375: return $limit_sql;
376: }
377:
378: }
379: