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 21108 2010-02-19 22:36:08Z 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     }

   152 

   153     /**

   154      * Force the connection to close.

   155      *

   156      * @return void

   157      */

   158     public function closeConnection()

   159     {

   160         if ($this->isConnected()) {

   161             oci_close($this->_connection);

   162         }

   163         $this->_connection = null;

   164     }

   165 

   166     /**

   167      * Activate/deactivate return of LOB as string

   168      *

   169      * @param string $lob_as_string

   170      * @return Zend_Db_Adapter_Oracle

   171      */

   172     public function setLobAsString($lobAsString)

   173     {

   174         $this->_lobAsString = (bool) $lobAsString;

   175         return $this;

   176     }

   177 

   178     /**

   179      * Return whether or not LOB are returned as string

   180      *

   181      * @return boolean

   182      */

   183     public function getLobAsString()

   184     {

   185         if ($this->_lobAsString === null) {

   186             // if never set by user, we use driver option if it exists otherwise false

   187             if (isset($this->_config['driver_options']) &&

   188                 isset($this->_config['driver_options']['lob_as_string'])) {

   189                 $this->_lobAsString = (bool) $this->_config['driver_options']['lob_as_string'];

   190             } else {

   191                 $this->_lobAsString = false;

   192             }

   193         }

   194         return $this->_lobAsString;

   195     }

   196 

   197     /**

   198      * Returns an SQL statement for preparation.

   199      *

   200      * @param string $sql The SQL statement with placeholders.

   201      * @return Zend_Db_Statement_Oracle

   202      */

   203     public function prepare($sql)

   204     {

   205         $this->_connect();

   206         $stmtClass = $this->_defaultStmtClass;

   207         if (!class_exists($stmtClass)) {

   208             require_once 'Zend/Loader.php';

   209             Zend_Loader::loadClass($stmtClass);

   210         }

   211         $stmt = new $stmtClass($this, $sql);

   212         if ($stmt instanceof Zend_Db_Statement_Oracle) {

   213             $stmt->setLobAsString($this->getLobAsString());

   214         }

   215         $stmt->setFetchMode($this->_fetchMode);

   216         return $stmt;

   217     }

   218 

   219     /**

   220      * Quote a raw string.

   221      *

   222      * @param string $value     Raw string

   223      * @return string           Quoted string

   224      */

   225     protected function _quote($value)

   226     {

   227         if (is_int($value) || is_float($value)) {

   228             return $value;

   229         }

   230         $value = str_replace("'", "''", $value);

   231         return "'" . addcslashes($value, "\000\n\r\\\032") . "'";

   232     }

   233 

   234     /**

   235      * Quote a table identifier and alias.

   236      *

   237      * @param string|array|Zend_Db_Expr $ident The identifier or expression.

   238      * @param string $alias An alias for the table.

   239      * @param boolean $auto If true, heed the AUTO_QUOTE_IDENTIFIERS config option.

   240      * @return string The quoted identifier and alias.

   241      */

   242     public function quoteTableAs($ident, $alias = null, $auto = false)

   243     {

   244         // Oracle doesn't allow the 'AS' keyword between the table identifier/expression and alias.

   245         return $this->_quoteIdentifierAs($ident, $alias, $auto, ' ');

   246     }

   247 

   248     /**

   249      * Return the most recent value from the specified sequence in the database.

   250      * This is supported only on RDBMS brands that support sequences

   251      * (e.g. Oracle, PostgreSQL, DB2).  Other RDBMS brands return null.

   252      *

   253      * @param string $sequenceName

   254      * @return string

   255      */

   256     public function lastSequenceId($sequenceName)

   257     {

   258         $this->_connect();

   259         $sql = 'SELECT '.$this->quoteIdentifier($sequenceName, true).'.CURRVAL FROM dual';

   260         $value = $this->fetchOne($sql);

   261         return $value;

   262     }

   263 

   264     /**

   265      * Generate a new value from the specified sequence in the database, and return it.

   266      * This is supported only on RDBMS brands that support sequences

   267      * (e.g. Oracle, PostgreSQL, DB2).  Other RDBMS brands return null.

   268      *

   269      * @param string $sequenceName

   270      * @return string

   271      */

   272     public function nextSequenceId($sequenceName)

   273     {

   274         $this->_connect();

   275         $sql = 'SELECT '.$this->quoteIdentifier($sequenceName, true).'.NEXTVAL FROM dual';

   276         $value = $this->fetchOne($sql);

   277         return $value;

   278     }

   279 

   280     /**

   281      * Gets the last ID generated automatically by an IDENTITY/AUTOINCREMENT column.

   282      *

   283      * As a convention, on RDBMS brands that support sequences

   284      * (e.g. Oracle, PostgreSQL, DB2), this method forms the name of a sequence

   285      * from the arguments and returns the last id generated by that sequence.

   286      * On RDBMS brands that support IDENTITY/AUTOINCREMENT columns, this method

   287      * returns the last value generated for such a column, and the table name

   288      * argument is disregarded.

   289      *

   290      * Oracle does not support IDENTITY columns, so if the sequence is not

   291      * specified, this method returns null.

   292      *

   293      * @param string $tableName   OPTIONAL Name of table.

   294      * @param string $primaryKey  OPTIONAL Name of primary key column.

   295      * @return string

   296      */

   297     public function lastInsertId($tableName = null, $primaryKey = null)

   298     {

   299         if ($tableName !== null) {

   300             $sequenceName = $tableName;

   301             if ($primaryKey) {

   302                 $sequenceName .= "_$primaryKey";

   303             }

   304             $sequenceName .= '_seq';

   305             return $this->lastSequenceId($sequenceName);

   306         }

   307 

   308         // No support for IDENTITY columns; return null

   309         return null;

   310     }

   311 

   312     /**

   313      * Returns a list of the tables in the database.

   314      *

   315      * @return array

   316      */

   317     public function listTables()

   318     {

   319         $this->_connect();

   320         $data = $this->fetchCol('SELECT table_name FROM all_tables');

   321         return $data;

   322     }

   323 

   324     /**

   325      * Returns the column descriptions for a table.

   326      *

   327      * The return value is an associative array keyed by the column name,

   328      * as returned by the RDBMS.

   329      *

   330      * The value of each array element is an associative array

   331      * with the following keys:

   332      *

   333      * SCHEMA_NAME      => string; name of schema

   334      * TABLE_NAME       => string;

   335      * COLUMN_NAME      => string; column name

   336      * COLUMN_POSITION  => number; ordinal position of column in table

   337      * DATA_TYPE        => string; SQL datatype name of column

   338      * DEFAULT          => string; default expression of column, null if none

   339      * NULLABLE         => boolean; true if column can have nulls

   340      * LENGTH           => number; length of CHAR/VARCHAR

   341      * SCALE            => number; scale of NUMERIC/DECIMAL

   342      * PRECISION        => number; precision of NUMERIC/DECIMAL

   343      * UNSIGNED         => boolean; unsigned property of an integer type

   344      * PRIMARY          => boolean; true if column is part of the primary key

   345      * PRIMARY_POSITION => integer; position of column in primary key

   346      * IDENTITY         => integer; true if column is auto-generated with unique values

   347      *

   348      * @todo Discover integer unsigned property.

   349      *

   350      * @param string $tableName

   351      * @param string $schemaName OPTIONAL

   352      * @return array

   353      */

   354     public function describeTable($tableName, $schemaName = null)

   355     {

   356         $version = $this->getServerVersion();

   357         if (($version === null) || version_compare($version, '9.0.0', '>=')) {

   358             $sql = "SELECT TC.TABLE_NAME, TC.OWNER, TC.COLUMN_NAME, TC.DATA_TYPE,

   359                     TC.DATA_DEFAULT, TC.NULLABLE, TC.COLUMN_ID, TC.DATA_LENGTH,

   360                     TC.DATA_SCALE, TC.DATA_PRECISION, C.CONSTRAINT_TYPE, CC.POSITION

   361                 FROM ALL_TAB_COLUMNS TC

   362                 LEFT JOIN (ALL_CONS_COLUMNS CC JOIN ALL_CONSTRAINTS C

   363                     ON (CC.CONSTRAINT_NAME = C.CONSTRAINT_NAME AND CC.TABLE_NAME = C.TABLE_NAME AND CC.OWNER = C.OWNER AND C.CONSTRAINT_TYPE = 'P'))

   364                   ON TC.TABLE_NAME = CC.TABLE_NAME AND TC.COLUMN_NAME = CC.COLUMN_NAME

   365                 WHERE UPPER(TC.TABLE_NAME) = UPPER(:TBNAME)";

   366             $bind[':TBNAME'] = $tableName;

   367             if ($schemaName) {

   368                 $sql .= ' AND UPPER(TC.OWNER) = UPPER(:SCNAME)';

   369                 $bind[':SCNAME'] = $schemaName;

   370             }

   371             $sql .= ' ORDER BY TC.COLUMN_ID';

   372         } else {

   373             $subSql="SELECT AC.OWNER, AC.TABLE_NAME, ACC.COLUMN_NAME, AC.CONSTRAINT_TYPE, ACC.POSITION

   374                 from ALL_CONSTRAINTS AC, ALL_CONS_COLUMNS ACC

   375                   WHERE ACC.CONSTRAINT_NAME = AC.CONSTRAINT_NAME

   376                     AND ACC.TABLE_NAME = AC.TABLE_NAME

   377                     AND ACC.OWNER = AC.OWNER

   378                     AND AC.CONSTRAINT_TYPE = 'P'

   379                     AND UPPER(AC.TABLE_NAME) = UPPER(:TBNAME)";

   380             $bind[':TBNAME'] = $tableName;

   381             if ($schemaName) {

   382                 $subSql .= ' AND UPPER(ACC.OWNER) = UPPER(:SCNAME)';

   383                 $bind[':SCNAME'] = $schemaName;

   384             }

   385             $sql="SELECT TC.TABLE_NAME, TC.OWNER, TC.COLUMN_NAME, TC.DATA_TYPE,

   386                     TC.DATA_DEFAULT, TC.NULLABLE, TC.COLUMN_ID, TC.DATA_LENGTH,

   387                     TC.DATA_SCALE, TC.DATA_PRECISION, CC.CONSTRAINT_TYPE, CC.POSITION

   388                 FROM ALL_TAB_COLUMNS TC, ($subSql) CC

   389                 WHERE UPPER(TC.TABLE_NAME) = UPPER(:TBNAME)

   390                   AND TC.OWNER = CC.OWNER(+) AND TC.TABLE_NAME = CC.TABLE_NAME(+) AND TC.COLUMN_NAME = CC.COLUMN_NAME(+)";

   391             if ($schemaName) {

   392                 $sql .= ' AND UPPER(TC.OWNER) = UPPER(:SCNAME)';

   393             }

   394             $sql .= ' ORDER BY TC.COLUMN_ID';

   395         }

   396 

   397         $stmt = $this->query($sql, $bind);

   398 

   399         /**

   400          * Use FETCH_NUM so we are not dependent on the CASE attribute of the PDO connection

   401          */

   402         $result = $stmt->fetchAll(Zend_Db::FETCH_NUM);

   403 

   404         $table_name      = 0;

   405         $owner           = 1;

   406         $column_name     = 2;

   407         $data_type       = 3;

   408         $data_default    = 4;

   409         $nullable        = 5;

   410         $column_id       = 6;

   411         $data_length     = 7;

   412         $data_scale      = 8;

   413         $data_precision  = 9;

   414         $constraint_type = 10;

   415         $position        = 11;

   416 

   417         $desc = array();

   418         foreach ($result as $key => $row) {

   419             list ($primary, $primaryPosition, $identity) = array(false, null, false);

   420             if ($row[$constraint_type] == 'P') {

   421                 $primary = true;

   422                 $primaryPosition = $row[$position];

   423                 /**

   424                  * Oracle does not support auto-increment keys.

   425                  */

   426                 $identity = false;

   427             }

   428             $desc[$this->foldCase($row[$column_name])] = array(

   429                 'SCHEMA_NAME'      => $this->foldCase($row[$owner]),

   430                 'TABLE_NAME'       => $this->foldCase($row[$table_name]),

   431                 'COLUMN_NAME'      => $this->foldCase($row[$column_name]),

   432                 'COLUMN_POSITION'  => $row[$column_id],

   433                 'DATA_TYPE'        => $row[$data_type],

   434                 'DEFAULT'          => $row[$data_default],

   435                 'NULLABLE'         => (bool) ($row[$nullable] == 'Y'),

   436                 'LENGTH'           => $row[$data_length],

   437                 'SCALE'            => $row[$data_scale],

   438                 'PRECISION'        => $row[$data_precision],

   439                 'UNSIGNED'         => null, // @todo

   440                 'PRIMARY'          => $primary,

   441                 'PRIMARY_POSITION' => $primaryPosition,

   442                 'IDENTITY'         => $identity

   443             );

   444         }

   445         return $desc;

   446     }

   447 

   448     /**

   449      * Leave autocommit mode and begin a transaction.

   450      *

   451      * @return void

   452      */

   453     protected function _beginTransaction()

   454     {

   455         $this->_setExecuteMode(OCI_DEFAULT);

   456     }

   457 

   458     /**

   459      * Commit a transaction and return to autocommit mode.

   460      *

   461      * @return void

   462      * @throws Zend_Db_Adapter_Oracle_Exception

   463      */

   464     protected function _commit()

   465     {

   466         if (!oci_commit($this->_connection)) {

   467             /**

   468              * @see Zend_Db_Adapter_Oracle_Exception

   469              */

   470             require_once 'Zend/Db/Adapter/Oracle/Exception.php';

   471             throw new Zend_Db_Adapter_Oracle_Exception(oci_error($this->_connection));

   472         }

   473         $this->_setExecuteMode(OCI_COMMIT_ON_SUCCESS);

   474     }

   475 

   476     /**

   477      * Roll back a transaction and return to autocommit mode.

   478      *

   479      * @return void

   480      * @throws Zend_Db_Adapter_Oracle_Exception

   481      */

   482     protected function _rollBack()

   483     {

   484         if (!oci_rollback($this->_connection)) {

   485             /**

   486              * @see Zend_Db_Adapter_Oracle_Exception

   487              */

   488             require_once 'Zend/Db/Adapter/Oracle/Exception.php';

   489             throw new Zend_Db_Adapter_Oracle_Exception(oci_error($this->_connection));

   490         }

   491         $this->_setExecuteMode(OCI_COMMIT_ON_SUCCESS);

   492     }

   493 

   494     /**

   495      * Set the fetch mode.

   496      *

   497      * @todo Support FETCH_CLASS and FETCH_INTO.

   498      *

   499      * @param integer $mode A fetch mode.

   500      * @return void

   501      * @throws Zend_Db_Adapter_Oracle_Exception

   502      */

   503     public function setFetchMode($mode)

   504     {

   505         switch ($mode) {

   506             case Zend_Db::FETCH_NUM:   // seq array

   507             case Zend_Db::FETCH_ASSOC: // assoc array

   508             case Zend_Db::FETCH_BOTH:  // seq+assoc array

   509             case Zend_Db::FETCH_OBJ:   // object

   510                 $this->_fetchMode = $mode;

   511                 break;

   512             case Zend_Db::FETCH_BOUND: // bound to PHP variable

   513                 /**

   514                  * @see Zend_Db_Adapter_Oracle_Exception

   515                  */

   516                 require_once 'Zend/Db/Adapter/Oracle/Exception.php';

   517                 throw new Zend_Db_Adapter_Oracle_Exception('FETCH_BOUND is not supported yet');

   518                 break;

   519             default:

   520                 /**

   521                  * @see Zend_Db_Adapter_Oracle_Exception

   522                  */

   523                 require_once 'Zend/Db/Adapter/Oracle/Exception.php';

   524                 throw new Zend_Db_Adapter_Oracle_Exception("Invalid fetch mode '$mode' specified");

   525                 break;

   526         }

   527     }

   528 

   529     /**

   530      * Adds an adapter-specific LIMIT clause to the SELECT statement.

   531      *

   532      * @param string $sql

   533      * @param integer $count

   534      * @param integer $offset OPTIONAL

   535      * @return string

   536      * @throws Zend_Db_Adapter_Oracle_Exception

   537      */

   538     public function limit($sql, $count, $offset = 0)

   539     {

   540         $count = intval($count);

   541         if ($count <= 0) {

   542             /**

   543              * @see Zend_Db_Adapter_Oracle_Exception

   544              */

   545             require_once 'Zend/Db/Adapter/Oracle/Exception.php';

   546             throw new Zend_Db_Adapter_Oracle_Exception("LIMIT argument count=$count is not valid");

   547         }

   548 

   549         $offset = intval($offset);

   550         if ($offset < 0) {

   551             /**

   552              * @see Zend_Db_Adapter_Oracle_Exception

   553              */

   554             require_once 'Zend/Db/Adapter/Oracle/Exception.php';

   555             throw new Zend_Db_Adapter_Oracle_Exception("LIMIT argument offset=$offset is not valid");

   556         }

   557 

   558         /**

   559          * Oracle does not implement the LIMIT clause as some RDBMS do.

   560          * We have to simulate it with subqueries and ROWNUM.

   561          * Unfortunately because we use the column wildcard "*",

   562          * this puts an extra column into the query result set.

   563          */

   564         $limit_sql = "SELECT z2.*

   565             FROM (

   566                 SELECT z1.*, ROWNUM AS \"zend_db_rownum\"

   567                 FROM (

   568                     " . $sql . "

   569                 ) z1

   570             ) z2

   571             WHERE z2.\"zend_db_rownum\" BETWEEN " . ($offset+1) . " AND " . ($offset+$count);

   572         return $limit_sql;

   573     }

   574 

   575     /**

   576      * @param integer $mode

   577      * @throws Zend_Db_Adapter_Oracle_Exception

   578      */

   579     private function _setExecuteMode($mode)

   580     {

   581         switch($mode) {

   582             case OCI_COMMIT_ON_SUCCESS:

   583             case OCI_DEFAULT:

   584             case OCI_DESCRIBE_ONLY:

   585                 $this->_execute_mode = $mode;

   586                 break;

   587             default:

   588                 /**

   589                  * @see Zend_Db_Adapter_Oracle_Exception

   590                  */

   591                 require_once 'Zend/Db/Adapter/Oracle/Exception.php';

   592                 throw new Zend_Db_Adapter_Oracle_Exception("Invalid execution mode '$mode' specified");

   593                 break;

   594         }

   595     }

   596 

   597     /**

   598      * @return int

   599      */

   600     public function _getExecuteMode()

   601     {

   602         return $this->_execute_mode;

   603     }

   604 

   605     /**

   606      * Check if the adapter supports real SQL parameters.

   607      *

   608      * @param string $type 'positional' or 'named'

   609      * @return bool

   610      */

   611     public function supportsParameters($type)

   612     {

   613         switch ($type) {

   614             case 'named':

   615                 return true;

   616             case 'positional':

   617             default:

   618                 return false;

   619         }

   620     }

   621 

   622     /**

   623      * Retrieve server version in PHP style

   624      *

   625      * @return string

   626      */

   627     public function getServerVersion()

   628     {

   629         $this->_connect();

   630         $version = oci_server_version($this->_connection);

   631         if ($version !== false) {

   632             $matches = null;

   633             if (preg_match('/((?:[0-9]{1,2}\.){1,3}[0-9]{1,2})/', $version, $matches)) {

   634                 return $matches[1];

   635             } else {

   636                 return null;

   637             }

   638         } else {

   639             return null;

   640         }

   641     }

   642 }