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

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

00001 <?php
00027 require_once 'Zend/Db/Adapter/Pdo/Abstract.php';
00028 
00029 
00039 class Zend_Db_Adapter_Pdo_Mssql extends Zend_Db_Adapter_Pdo_Abstract
00040 {
00046     protected $_pdoType = 'mssql';
00047 
00059     protected $_numericDataTypes = array(
00060         Zend_Db::INT_TYPE    => Zend_Db::INT_TYPE,
00061         Zend_Db::BIGINT_TYPE => Zend_Db::BIGINT_TYPE,
00062         Zend_Db::FLOAT_TYPE  => Zend_Db::FLOAT_TYPE,
00063         'INT'                => Zend_Db::INT_TYPE,
00064         'SMALLINT'           => Zend_Db::INT_TYPE,
00065         'TINYINT'            => Zend_Db::INT_TYPE,
00066         'BIGINT'             => Zend_Db::BIGINT_TYPE,
00067         'DECIMAL'            => Zend_Db::FLOAT_TYPE,
00068         'FLOAT'              => Zend_Db::FLOAT_TYPE,
00069         'MONEY'              => Zend_Db::FLOAT_TYPE,
00070         'NUMERIC'            => Zend_Db::FLOAT_TYPE,
00071         'REAL'               => Zend_Db::FLOAT_TYPE,
00072         'SMALLMONEY'         => Zend_Db::FLOAT_TYPE
00073     );
00074 
00080     protected function _dsn()
00081     {
00082         // baseline of DSN parts
00083         $dsn = $this->_config;
00084 
00085         // don't pass the username and password in the DSN
00086         unset($dsn['username']);
00087         unset($dsn['password']);
00088         unset($dsn['options']);
00089         unset($dsn['persistent']);
00090         unset($dsn['driver_options']);
00091 
00092         if (isset($dsn['port'])) {
00093             $seperator = ':';
00094             if (strtoupper(substr(PHP_OS, 0, 3)) === 'WIN') {
00095                 $seperator = ',';
00096             }
00097             $dsn['host'] .= $seperator . $dsn['port'];
00098             unset($dsn['port']);
00099         }
00100 
00101         // this driver supports multiple DSN prefixes
00102         // @see http://www.php.net/manual/en/ref.pdo-dblib.connection.php
00103         if (isset($dsn['pdoType'])) {
00104             switch (strtolower($dsn['pdoType'])) {
00105                 case 'freetds':
00106                 case 'sybase':
00107                     $this->_pdoType = 'sybase';
00108                     break;
00109                 case 'mssql':
00110                     $this->_pdoType = 'mssql';
00111                     break;
00112                 case 'dblib':
00113                 default:
00114                     $this->_pdoType = 'dblib';
00115                     break;
00116             }
00117             unset($dsn['pdoType']);
00118         }
00119 
00120         // use all remaining parts in the DSN
00121         foreach ($dsn as $key => $val) {
00122             $dsn[$key] = "$key=$val";
00123         }
00124 
00125         $dsn = $this->_pdoType . ':' . implode(';', $dsn);
00126         return $dsn;
00127     }
00128 
00132     protected function _connect()
00133     {
00134         if ($this->_connection) {
00135             return;
00136         }
00137         parent::_connect();
00138         $this->_connection->exec('SET QUOTED_IDENTIFIER ON');
00139     }
00140 
00147     protected function _beginTransaction()
00148     {
00149         $this->_connect();
00150         $this->_connection->exec('BEGIN TRANSACTION');
00151         return true;
00152     }
00153 
00160     protected function _commit()
00161     {
00162         $this->_connect();
00163         $this->_connection->exec('COMMIT TRANSACTION');
00164         return true;
00165     }
00166 
00173     protected function _rollBack() {
00174         $this->_connect();
00175         $this->_connection->exec('ROLLBACK TRANSACTION');
00176         return true;
00177     }
00178 
00184     public function listTables()
00185     {
00186         $sql = "SELECT name FROM sysobjects WHERE type = 'U' ORDER BY name";
00187         return $this->fetchCol($sql);
00188     }
00189 
00221     public function describeTable($tableName, $schemaName = null)
00222     {
00223         if ($schemaName != null) {
00224             if (strpos($schemaName, '.') !== false) {
00225                 $result = explode('.', $schemaName);
00226                 $schemaName = $result[1];
00227             }
00228         }
00232         $sql = "exec sp_columns @table_name = " . $this->quoteIdentifier($tableName, true);
00233         if ($schemaName != null) {
00234             $sql .= ", @table_owner = " . $this->quoteIdentifier($schemaName, true);
00235         }
00236 
00237         $stmt = $this->query($sql);
00238         $result = $stmt->fetchAll(Zend_Db::FETCH_NUM);
00239 
00240         $table_name  = 2;
00241         $column_name = 3;
00242         $type_name   = 5;
00243         $precision   = 6;
00244         $length      = 7;
00245         $scale       = 8;
00246         $nullable    = 10;
00247         $column_def  = 12;
00248         $column_position = 16;
00249 
00253         $sql = "exec sp_pkeys @table_name = " . $this->quoteIdentifier($tableName, true);
00254         if ($schemaName != null) {
00255             $sql .= ", @table_owner = " . $this->quoteIdentifier($schemaName, true);
00256         }
00257 
00258         $stmt = $this->query($sql);
00259         $primaryKeysResult = $stmt->fetchAll(Zend_Db::FETCH_NUM);
00260         $primaryKeyColumn = array();
00261         $pkey_column_name = 3;
00262         $pkey_key_seq = 4;
00263         foreach ($primaryKeysResult as $pkeysRow) {
00264             $primaryKeyColumn[$pkeysRow[$pkey_column_name]] = $pkeysRow[$pkey_key_seq];
00265         }
00266 
00267         $desc = array();
00268         $p = 1;
00269         foreach ($result as $key => $row) {
00270             $identity = false;
00271             $words = explode(' ', $row[$type_name], 2);
00272             if (isset($words[0])) {
00273                 $type = $words[0];
00274                 if (isset($words[1])) {
00275                     $identity = (bool) preg_match('/identity/', $words[1]);
00276                 }
00277             }
00278 
00279             $isPrimary = array_key_exists($row[$column_name], $primaryKeyColumn);
00280             if ($isPrimary) {
00281                 $primaryPosition = $primaryKeyColumn[$row[$column_name]];
00282             } else {
00283                 $primaryPosition = null;
00284             }
00285 
00286             $desc[$this->foldCase($row[$column_name])] = array(
00287                 'SCHEMA_NAME'      => null, // @todo
00288                 'TABLE_NAME'       => $this->foldCase($row[$table_name]),
00289                 'COLUMN_NAME'      => $this->foldCase($row[$column_name]),
00290                 'COLUMN_POSITION'  => (int) $row[$column_position],
00291                 'DATA_TYPE'        => $type,
00292                 'DEFAULT'          => $row[$column_def],
00293                 'NULLABLE'         => (bool) $row[$nullable],
00294                 'LENGTH'           => $row[$length],
00295                 'SCALE'            => $row[$scale],
00296                 'PRECISION'        => $row[$precision],
00297                 'UNSIGNED'         => null, // @todo
00298                 'PRIMARY'          => $isPrimary,
00299                 'PRIMARY_POSITION' => $primaryPosition,
00300                 'IDENTITY'         => $identity
00301             );
00302         }
00303         return $desc;
00304     }
00305 
00317      public function limit($sql, $count, $offset = 0)
00318      {
00319         $count = intval($count);
00320         if ($count <= 0) {
00322             require_once 'Zend/Db/Adapter/Exception.php';
00323             throw new Zend_Db_Adapter_Exception("LIMIT argument count=$count is not valid");
00324         }
00325 
00326         $offset = intval($offset);
00327         if ($offset < 0) {
00329             require_once 'Zend/Db/Adapter/Exception.php';
00330             throw new Zend_Db_Adapter_Exception("LIMIT argument offset=$offset is not valid");
00331         }
00332 
00333         $sql = preg_replace(
00334             '/^SELECT\s+(DISTINCT\s)?/i',
00335             'SELECT $1TOP ' . ($count+$offset) . ' ',
00336             $sql
00337             );
00338 
00339         if ($offset > 0) {
00340             $orderby = stristr($sql, 'ORDER BY');
00341 
00342             if ($orderby !== false) {
00343                 $orderParts = explode(',', substr($orderby, 8));
00344                 $pregReplaceCount = null;
00345                 $orderbyInverseParts = array();
00346                 foreach ($orderParts as $orderPart) {
00347                     $orderPart = rtrim($orderPart);
00348                     $inv = preg_replace('/\s+desc$/i', ' ASC', $orderPart, 1, $pregReplaceCount);
00349                     if ($pregReplaceCount) {
00350                         $orderbyInverseParts[] = $inv;
00351                         continue;
00352                     }
00353                     $inv = preg_replace('/\s+asc$/i', ' DESC', $orderPart, 1, $pregReplaceCount);
00354                     if ($pregReplaceCount) {
00355                         $orderbyInverseParts[] = $inv;
00356                         continue;
00357                     } else {
00358                         $orderbyInverseParts[] = $orderPart . ' DESC';
00359                     }
00360                 }
00361 
00362                 $orderbyInverse = 'ORDER BY ' . implode(', ', $orderbyInverseParts);
00363             }
00364 
00365 
00366 
00367 
00368             $sql = 'SELECT * FROM (SELECT TOP ' . $count . ' * FROM (' . $sql . ') AS inner_tbl';
00369             if ($orderby !== false) {
00370                 $sql .= ' ' . $orderbyInverse . ' ';
00371             }
00372             $sql .= ') AS outer_tbl';
00373             if ($orderby !== false) {
00374                 $sql .= ' ' . $orderby;
00375             }
00376         }
00377 
00378         return $sql;
00379     }
00380 
00399     public function lastInsertId($tableName = null, $primaryKey = null)
00400     {
00401         $sql = 'SELECT SCOPE_IDENTITY()';
00402         return (int)$this->fetchOne($sql);
00403     }
00404 
00410     public function getServerVersion()
00411     {
00412         try {
00413             $stmt = $this->query("SELECT SERVERPROPERTY('productversion')");
00414             $result = $stmt->fetchAll(Zend_Db::FETCH_NUM);
00415             if (count($result)) {
00416                 return $result[0][0];
00417             }
00418             return null;
00419         } catch (PDOException $e) {
00420             return null;
00421         }
00422     }
00423 }

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