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
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
00145
00146 if (!empty($this->_config['driver_options'])) {
00147 foreach ($this->_config['driver_options'] as $option => $value) {
00148
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
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
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
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
00372 $sql = "INSERT INTO "
00373 . $this->quoteIdentifier($table, true)
00374 . ' (' . implode(', ', $cols) . ') '
00375 . 'VALUES (' . implode(', ', $vals) . ')'
00376 . ' ' . $this->_lastInsertSQL;
00377
00378
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
00463
00464
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,
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,
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:
00567 case Zend_Db::FETCH_ASSOC:
00568 case Zend_Db::FETCH_BOTH:
00569 case Zend_Db::FETCH_OBJ:
00570 $this->_fetchMode = $mode;
00571 break;
00572 case Zend_Db::FETCH_BOUND:
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
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 }