• Main Page
  • Related Pages
  • Namespaces
  • Data Structures
  • Files
  • Examples
  • File List

E:/E/GEAMP/www/openbiz/openbiz/others/Zend/Db/Adapter/Sqlsrv.php

00001 <?php
00025 require_once 'Zend/Db/Adapter/Abstract.php';
00026 
00030 require_once 'Zend/Db/Statement/Sqlsrv.php';
00031 
00039 class Zend_Db_Adapter_Sqlsrv extends Zend_Db_Adapter_Abstract
00040 {
00052     protected $_config = array(
00053         'dbname'       => null,
00054         'username'     => null,
00055         'password'     => null,
00056     );
00057 
00063     protected $_lastInsertId;
00064 
00070     protected $_lastInsertSQL = 'SELECT SCOPE_IDENTITY() as Current_Identity';
00071 
00083     protected $_numericDataTypes = array(
00084         Zend_Db::INT_TYPE    => Zend_Db::INT_TYPE,
00085         Zend_Db::BIGINT_TYPE => Zend_Db::BIGINT_TYPE,
00086         Zend_Db::FLOAT_TYPE  => Zend_Db::FLOAT_TYPE,
00087         'INT'                => Zend_Db::INT_TYPE,
00088         'SMALLINT'           => Zend_Db::INT_TYPE,
00089         'TINYINT'            => Zend_Db::INT_TYPE,
00090         'BIGINT'             => Zend_Db::BIGINT_TYPE,
00091         'DECIMAL'            => Zend_Db::FLOAT_TYPE,
00092         'FLOAT'              => Zend_Db::FLOAT_TYPE,
00093         'MONEY'              => Zend_Db::FLOAT_TYPE,
00094         'NUMERIC'            => Zend_Db::FLOAT_TYPE,
00095         'REAL'               => Zend_Db::FLOAT_TYPE,
00096         'SMALLMONEY'         => Zend_Db::FLOAT_TYPE,
00097     );
00098 
00104     protected $_defaultStmtClass = 'Zend_Db_Statement_Sqlsrv';
00105 
00112     protected function _connect()
00113     {
00114         if (is_resource($this->_connection)) {
00115             // connection already exists
00116             return;
00117         }
00118 
00119         if (!extension_loaded('sqlsrv')) {
00123             require_once 'Zend/Db/Adapter/Sqlsrv/Exception.php';
00124             throw new Zend_Db_Adapter_Sqlsrv_Exception('The Sqlsrv extension is required for this adapter but the extension is not loaded');
00125         }
00126 
00127         $serverName = $this->_config['host'];
00128         if (isset($this->_config['port'])) {
00129             $port        = (integer) $this->_config['port'];
00130             $serverName .= ', ' . $port;
00131         }
00132 
00133         $connectionInfo = array(
00134             'Database' => $this->_config['dbname'],
00135         );
00136 
00137         if (isset($this->_config['username']) && isset($this->_config['password']))
00138         {
00139             $connectionInfo += array(
00140                 'UID'      => $this->_config['username'],
00141                 'PWD'      => $this->_config['password'],
00142             );
00143         }
00144         // else - windows authentication
00145 
00146         if (!empty($this->_config['driver_options'])) {
00147             foreach ($this->_config['driver_options'] as $option => $value) {
00148                 // A value may be a constant.
00149                 if (is_string($value)) {
00150                     $constantValue = @constant(strtoupper($value));
00151                     if ($constantValue === null) {
00152                         $connectionInfo[$option] = $value;
00153                     } else {
00154                         $connectionInfo[$option] = $constantValue;
00155                     }
00156                 }
00157             }
00158         }
00159 
00160         $this->_connection = sqlsrv_connect($serverName, $connectionInfo);
00161 
00162         if (!$this->_connection) {
00166             require_once 'Zend/Db/Adapter/Sqlsrv/Exception.php';
00167             throw new Zend_Db_Adapter_Sqlsrv_Exception(sqlsrv_errors());
00168         }
00169     }
00170 
00178     protected function _checkRequiredOptions(array $config)
00179     {
00180         // we need at least a dbname
00181         if (! array_key_exists('dbname', $config)) {
00183             require_once 'Zend/Db/Adapter/Exception.php';
00184             throw new Zend_Db_Adapter_Exception("Configuration array must have a key for 'dbname' that names the database instance");
00185         }
00186 
00187         if (! array_key_exists('password', $config) && array_key_exists('username', $config)) {
00191             require_once 'Zend/Db/Adapter/Exception.php';
00192             throw new Zend_Db_Adapter_Exception("Configuration array must have a key for 'password' for login credentials.
00193                                                 If Windows Authentication is desired, both keys 'username' and 'password' should be ommited from config.");
00194         }
00195 
00196         if (array_key_exists('password', $config) && !array_key_exists('username', $config)) {
00200             require_once 'Zend/Db/Adapter/Exception.php';
00201             throw new Zend_Db_Adapter_Exception("Configuration array must have a key for 'username' for login credentials.
00202                                                 If Windows Authentication is desired, both keys 'username' and 'password' should be ommited from config.");
00203         }
00204     }
00205 
00213     public function setTransactionIsolationLevel($level = null)
00214     {
00215         $this->_connect();
00216         $sql = null;
00217 
00218         // Default transaction level in sql server
00219         if ($level === null)
00220         {
00221             $level = SQLSRV_TXN_READ_COMMITTED;
00222         }
00223 
00224         switch ($level) {
00225             case SQLSRV_TXN_READ_UNCOMMITTED:
00226                 $sql = "READ UNCOMMITTED";
00227                 break;
00228             case SQLSRV_TXN_READ_COMMITTED:
00229                 $sql = "READ COMMITTED";
00230                 break;
00231             case SQLSRV_TXN_REPEATABLE_READ:
00232                 $sql = "REPEATABLE READ";
00233                 break;
00234             case SQLSRV_TXN_SNAPSHOT:
00235                 $sql = "SNAPSHOT";
00236                 break;
00237             case SQLSRV_TXN_SERIALIZABLE:
00238                 $sql = "SERIALIZABLE";
00239                 break;
00240             default:
00241                 require_once 'Zend/Db/Adapter/Sqlsrv/Exception.php';
00242                 throw new Zend_Db_Adapter_Sqlsrv_Exception("Invalid transaction isolation level mode '$level' specified");
00243         }
00244 
00245         if (!sqlsrv_query($this->_connection, "SET TRANSACTION ISOLATION LEVEL $sql;")) {
00246             require_once 'Zend/Db/Adapter/Sqlsrv/Exception.php';
00247             throw new Zend_Db_Adapter_Sqlsrv_Exception("Transaction cannot be changed to '$level'");
00248         }
00249 
00250         return true;
00251     }
00252 
00258     public function isConnected()
00259     {
00260         return (is_resource($this->_connection)
00261                 && (get_resource_type($this->_connection) == 'SQL Server Connection')
00262         );
00263     }
00264 
00270     public function closeConnection()
00271     {
00272         if ($this->isConnected()) {
00273             sqlsrv_close($this->_connection);
00274         }
00275         $this->_connection = null;
00276     }
00277 
00284     public function prepare($sql)
00285     {
00286         $this->_connect();
00287         $stmtClass = $this->_defaultStmtClass;
00288 
00289         if (!class_exists($stmtClass)) {
00293             require_once 'Zend/Loader.php';
00294             Zend_Loader::loadClass($stmtClass);
00295         }
00296 
00297         $stmt = new $stmtClass($this, $sql);
00298         $stmt->setFetchMode($this->_fetchMode);
00299         return $stmt;
00300     }
00301 
00308     protected function _quote($value)
00309     {
00310         if (is_int($value)) {
00311             return $value;
00312         } elseif (is_float($value)) {
00313             return sprintf('%F', $value);
00314         }
00315 
00316         return "'" . str_replace("'", "''", $value) . "'";
00317     }
00318 
00333     public function lastInsertId($tableName = null, $primaryKey = null)
00334     {
00335         if ($tableName) {
00336             $tableName = $this->quote($tableName);
00337             $sql       = 'SELECT IDENT_CURRENT (' . $tableName . ') as Current_Identity';
00338             return (string) $this->fetchOne($sql);
00339         }
00340 
00341         if ($this->_lastInsertId > 0) {
00342             return (string) $this->_lastInsertId;
00343         }
00344 
00345         $sql = $this->_lastInsertSQL;
00346         return (string) $this->fetchOne($sql);
00347     }
00348 
00356     public function insert($table, array $bind)
00357     {
00358         // extract and quote col names from the array keys
00359         $cols = array();
00360         $vals = array();
00361         foreach ($bind as $col => $val) {
00362             $cols[] = $this->quoteIdentifier($col, true);
00363             if ($val instanceof Zend_Db_Expr) {
00364                 $vals[] = $val->__toString();
00365                 unset($bind[$col]);
00366             } else {
00367                 $vals[] = '?';
00368             }
00369         }
00370 
00371         // build the statement
00372         $sql = "INSERT INTO "
00373              . $this->quoteIdentifier($table, true)
00374              . ' (' . implode(', ', $cols) . ') '
00375              . 'VALUES (' . implode(', ', $vals) . ')'
00376              . ' ' . $this->_lastInsertSQL;
00377 
00378         // execute the statement and return the number of affected rows
00379         $stmt   = $this->query($sql, array_values($bind));
00380         $result = $stmt->rowCount();
00381 
00382         $stmt->nextRowset();
00383 
00384         $this->_lastInsertId = $stmt->fetchColumn();
00385 
00386         return $result;
00387     }
00388 
00394     public function listTables()
00395     {
00396         $this->_connect();
00397         $sql = "SELECT name FROM sysobjects WHERE type = 'U' ORDER BY name";
00398         return $this->fetchCol($sql);
00399     }
00400 
00431     public function describeTable($tableName, $schemaName = null)
00432     {
00436         $sql    = "exec sp_columns @table_name = " . $this->quoteIdentifier($tableName, true);
00437         $stmt   = $this->query($sql);
00438         $result = $stmt->fetchAll(Zend_Db::FETCH_NUM);
00439 
00440         $owner           = 1;
00441         $table_name      = 2;
00442         $column_name     = 3;
00443         $type_name       = 5;
00444         $precision       = 6;
00445         $length          = 7;
00446         $scale           = 8;
00447         $nullable        = 10;
00448         $column_def      = 12;
00449         $column_position = 16;
00450 
00454         $tableOwner = $result[0][$owner];
00455         $sql        = "exec sp_pkeys @table_owner = " . $tableOwner
00456                     . ", @table_name = " . $this->quoteIdentifier($tableName, true);
00457         $stmt       = $this->query($sql);
00458 
00459         $primaryKeysResult = $stmt->fetchAll(Zend_Db::FETCH_NUM);
00460         $primaryKeyColumn  = array();
00461 
00462         // Per http://msdn.microsoft.com/en-us/library/ms189813.aspx,
00463         // results from sp_keys stored procedure are:
00464         // 0=TABLE_QUALIFIER 1=TABLE_OWNER 2=TABLE_NAME 3=COLUMN_NAME 4=KEY_SEQ 5=PK_NAME
00465 
00466         $pkey_column_name = 3;
00467         $pkey_key_seq     = 4;
00468         foreach ($primaryKeysResult as $pkeysRow) {
00469             $primaryKeyColumn[$pkeysRow[$pkey_column_name]] = $pkeysRow[$pkey_key_seq];
00470         }
00471 
00472         $desc = array();
00473         $p    = 1;
00474         foreach ($result as $key => $row) {
00475             $identity = false;
00476             $words    = explode(' ', $row[$type_name], 2);
00477             if (isset($words[0])) {
00478                 $type = $words[0];
00479                 if (isset($words[1])) {
00480                     $identity = (bool) preg_match('/identity/', $words[1]);
00481                 }
00482             }
00483 
00484             $isPrimary = array_key_exists($row[$column_name], $primaryKeyColumn);
00485             if ($isPrimary) {
00486                 $primaryPosition = $primaryKeyColumn[$row[$column_name]];
00487             } else {
00488                 $primaryPosition = null;
00489             }
00490 
00491             $desc[$this->foldCase($row[$column_name])] = array(
00492                 'SCHEMA_NAME'      => null, // @todo
00493                 'TABLE_NAME'       => $this->foldCase($row[$table_name]),
00494                 'COLUMN_NAME'      => $this->foldCase($row[$column_name]),
00495                 'COLUMN_POSITION'  => (int) $row[$column_position],
00496                 'DATA_TYPE'        => $type,
00497                 'DEFAULT'          => $row[$column_def],
00498                 'NULLABLE'         => (bool) $row[$nullable],
00499                 'LENGTH'           => $row[$length],
00500                 'SCALE'            => $row[$scale],
00501                 'PRECISION'        => $row[$precision],
00502                 'UNSIGNED'         => null, // @todo
00503                 'PRIMARY'          => $isPrimary,
00504                 'PRIMARY_POSITION' => $primaryPosition,
00505                 'IDENTITY'         => $identity,
00506             );
00507         }
00508 
00509         return $desc;
00510     }
00511 
00518     protected function _beginTransaction()
00519     {
00520         if (!sqlsrv_begin_transaction($this->_connection)) {
00521             require_once 'Zend/Db/Adapter/Sqlsrv/Exception.php';
00522             throw new Zend_Db_Adapter_Sqlsrv_Exception(sqlsrv_errors());
00523         }
00524     }
00525 
00532     protected function _commit()
00533     {
00534         if (!sqlsrv_commit($this->_connection)) {
00535             require_once 'Zend/Db/Adapter/Sqlsrv/Exception.php';
00536             throw new Zend_Db_Adapter_Sqlsrv_Exception(sqlsrv_errors());
00537         }
00538     }
00539 
00546     protected function _rollBack()
00547     {
00548         if (!sqlsrv_rollback($this->_connection)) {
00549             require_once 'Zend/Db/Adapter/Sqlsrv/Exception.php';
00550             throw new Zend_Db_Adapter_Sqlsrv_Exception(sqlsrv_errors());
00551         }
00552     }
00553 
00563     public function setFetchMode($mode)
00564     {
00565         switch ($mode) {
00566             case Zend_Db::FETCH_NUM:   // seq array
00567             case Zend_Db::FETCH_ASSOC: // assoc array
00568             case Zend_Db::FETCH_BOTH:  // seq+assoc array
00569             case Zend_Db::FETCH_OBJ:   // object
00570                 $this->_fetchMode = $mode;
00571                 break;
00572             case Zend_Db::FETCH_BOUND: // bound to PHP variable
00573                 require_once 'Zend/Db/Adapter/Sqlsrv/Exception.php';
00574                 throw new Zend_Db_Adapter_Sqlsrv_Exception('FETCH_BOUND is not supported yet');
00575                 break;
00576             default:
00577                 require_once 'Zend/Db/Adapter/Sqlsrv/Exception.php';
00578                 throw new Zend_Db_Adapter_Sqlsrv_Exception("Invalid fetch mode '$mode' specified");
00579                 break;
00580         }
00581     }
00582 
00592      public function limit($sql, $count, $offset = 0)
00593      {
00594         $count = intval($count);
00595         if ($count <= 0) {
00596             require_once 'Zend/Db/Adapter/Exception.php';
00597             throw new Zend_Db_Adapter_Exception("LIMIT argument count=$count is not valid");
00598         }
00599 
00600         $offset = intval($offset);
00601         if ($offset < 0) {
00603             require_once 'Zend/Db/Adapter/Exception.php';
00604             throw new Zend_Db_Adapter_Exception("LIMIT argument offset=$offset is not valid");
00605         }
00606 
00607         $orderby = stristr($sql, 'ORDER BY');
00608         if ($orderby !== false) {
00609             $sort  = (stripos($orderby, ' desc') !== false) ? 'desc' : 'asc';
00610             $order = str_ireplace('ORDER BY', '', $orderby);
00611             $order = trim(preg_replace('/\bASC\b|\bDESC\b/i', '', $order));
00612         }
00613 
00614         $sql = preg_replace('/^SELECT\s/i', 'SELECT TOP ' . ($count+$offset) . ' ', $sql);
00615 
00616         $sql = 'SELECT * FROM (SELECT TOP ' . $count . ' * FROM (' . $sql . ') AS inner_tbl';
00617         if ($orderby !== false) {
00618             $sql .= ' ORDER BY ' . $order . ' ';
00619             $sql .= (stripos($sort, 'asc') !== false) ? 'DESC' : 'ASC';
00620         }
00621         $sql .= ') AS outer_tbl';
00622         if ($orderby !== false) {
00623             $sql .= ' ORDER BY ' . $order . ' ' . $sort;
00624         }
00625 
00626         return $sql;
00627     }
00628 
00635     public function supportsParameters($type)
00636     {
00637         if ($type == 'positional') {
00638             return true;
00639         }
00640 
00641         // if its 'named' or anything else
00642         return false;
00643     }
00644 
00650     public function getServerVersion()
00651     {
00652         $this->_connect();
00653         $version = sqlsrv_client_info($this->_connection);
00654 
00655         if ($version !== false) {
00656             return $version['DriverVer'];
00657         }
00658 
00659         return null;
00660     }
00661 }

Generated on Thu Apr 19 2012 17:01:17 for openbiz by  doxygen 1.7.2