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
00083 $dsn = $this->_config;
00084
00085
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
00102
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
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,
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,
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 }