Overview

Packages

  • Auth
  • Config
  • Controller
  • Date
  • Db
  • Feed
    • Abstract
    • Writers
  • File
    • Decorator
  • Form
    • Element
  • Image
  • Log
    • Writer
  • Net
    • Exception
    • REST
  • None
  • PHP
  • PHPMailer
  • Session
  • Util
  • Validate
    • Validator
  • Zend
    • Db
      • Adapter
      • Expr
      • Profiler
      • Select
      • Statement
      • Table
    • Loader
      • Autoloader
      • PluginLoader
    • Registry

Classes

  • Zend_Db_Adapter_Abstract
  • Zend_Db_Adapter_Db2
  • Zend_Db_Adapter_Mysqli
  • Zend_Db_Adapter_Oracle
  • Zend_Db_Adapter_Pdo_Abstract
  • Zend_Db_Adapter_Pdo_Ibm
  • Zend_Db_Adapter_Pdo_Ibm_Db2
  • Zend_Db_Adapter_Pdo_Ibm_Ids
  • Zend_Db_Adapter_Pdo_Mssql
  • Zend_Db_Adapter_Pdo_Mysql
  • Zend_Db_Adapter_Pdo_Oci
  • Zend_Db_Adapter_Pdo_Pgsql
  • Zend_Db_Adapter_Pdo_Sqlite
  • Zend_Db_Adapter_Sqlsrv

Exceptions

  • Zend_Db_Adapter_Db2_Exception
  • Zend_Db_Adapter_Exception
  • Zend_Db_Adapter_Mysqli_Exception
  • Zend_Db_Adapter_Oracle_Exception
  • Zend_Db_Adapter_Sqlsrv_Exception
  • Overview
  • Package
  • Class
  • Tree
  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: 
Pry Framework API documentation generated by ApiGen 2.6.1