1: <?php
2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21:
22:
23: 24: 25:
26: require_once 'Zend/Db/Adapter/Abstract.php';
27:
28: 29: 30:
31: require_once 'Zend/Db/Statement/Sqlsrv.php';
32:
33: 34: 35: 36: 37: 38: 39:
40: class Zend_Db_Adapter_Sqlsrv extends Zend_Db_Adapter_Abstract
41: {
42: 43: 44: 45: 46: 47: 48: 49: 50: 51: 52:
53: protected $_config = array(
54: 'dbname' => null,
55: 'username' => null,
56: 'password' => null,
57: );
58:
59: 60: 61: 62: 63:
64: protected $_lastInsertId;
65:
66: 67: 68: 69: 70:
71: protected $_lastInsertSQL = 'SELECT SCOPE_IDENTITY() as Current_Identity';
72:
73: 74: 75: 76: 77: 78: 79: 80: 81: 82: 83:
84: protected $_numericDataTypes = array(
85: Zend_Db::INT_TYPE => Zend_Db::INT_TYPE,
86: Zend_Db::BIGINT_TYPE => Zend_Db::BIGINT_TYPE,
87: Zend_Db::FLOAT_TYPE => Zend_Db::FLOAT_TYPE,
88: 'INT' => Zend_Db::INT_TYPE,
89: 'SMALLINT' => Zend_Db::INT_TYPE,
90: 'TINYINT' => Zend_Db::INT_TYPE,
91: 'BIGINT' => Zend_Db::BIGINT_TYPE,
92: 'DECIMAL' => Zend_Db::FLOAT_TYPE,
93: 'FLOAT' => Zend_Db::FLOAT_TYPE,
94: 'MONEY' => Zend_Db::FLOAT_TYPE,
95: 'NUMERIC' => Zend_Db::FLOAT_TYPE,
96: 'REAL' => Zend_Db::FLOAT_TYPE,
97: 'SMALLMONEY' => Zend_Db::FLOAT_TYPE,
98: );
99:
100: 101: 102: 103: 104:
105: protected $_defaultStmtClass = 'Zend_Db_Statement_Sqlsrv';
106:
107: 108: 109: 110: 111: 112:
113: protected function _connect()
114: {
115: if (is_resource($this->_connection)) {
116:
117: return;
118: }
119:
120: if (!extension_loaded('sqlsrv')) {
121: 122: 123:
124: require_once 'Zend/Db/Adapter/Sqlsrv/Exception.php';
125: throw new Zend_Db_Adapter_Sqlsrv_Exception('The Sqlsrv extension is required for this adapter but the extension is not loaded');
126: }
127:
128: $serverName = $this->_config['host'];
129: if (isset($this->_config['port'])) {
130: $port = (integer) $this->_config['port'];
131: $serverName .= ', ' . $port;
132: }
133:
134: $connectionInfo = array(
135: 'Database' => $this->_config['dbname'],
136: );
137:
138: if (isset($this->_config['username']) && isset($this->_config['password']))
139: {
140: $connectionInfo += array(
141: 'UID' => $this->_config['username'],
142: 'PWD' => $this->_config['password'],
143: );
144: }
145:
146:
147: if (!empty($this->_config['driver_options'])) {
148: foreach ($this->_config['driver_options'] as $option => $value) {
149:
150: if (is_string($value)) {
151: $constantName = strtoupper($value);
152: if (defined($constantName)) {
153: $connectionInfo[$option] = constant($constantName);
154: } else {
155: $connectionInfo[$option] = $value;
156: }
157: }
158: }
159: }
160:
161: $this->_connection = sqlsrv_connect($serverName, $connectionInfo);
162:
163: if (!$this->_connection) {
164: 165: 166:
167: require_once 'Zend/Db/Adapter/Sqlsrv/Exception.php';
168: throw new Zend_Db_Adapter_Sqlsrv_Exception(sqlsrv_errors());
169: }
170: }
171:
172: 173: 174: 175: 176: 177: 178:
179: protected function _checkRequiredOptions(array $config)
180: {
181:
182: if (! array_key_exists('dbname', $config)) {
183:
184: require_once 'Zend/Db/Adapter/Exception.php';
185: throw new Zend_Db_Adapter_Exception("Configuration array must have a key for 'dbname' that names the database instance");
186: }
187:
188: if (! array_key_exists('password', $config) && array_key_exists('username', $config)) {
189: 190: 191:
192: require_once 'Zend/Db/Adapter/Exception.php';
193: throw new Zend_Db_Adapter_Exception("Configuration array must have a key for 'password' for login credentials.
194: If Windows Authentication is desired, both keys 'username' and 'password' should be ommited from config.");
195: }
196:
197: if (array_key_exists('password', $config) && !array_key_exists('username', $config)) {
198: 199: 200:
201: require_once 'Zend/Db/Adapter/Exception.php';
202: throw new Zend_Db_Adapter_Exception("Configuration array must have a key for 'username' for login credentials.
203: If Windows Authentication is desired, both keys 'username' and 'password' should be ommited from config.");
204: }
205: }
206:
207: 208: 209: 210: 211: 212: 213:
214: public function setTransactionIsolationLevel($level = null)
215: {
216: $this->_connect();
217: $sql = null;
218:
219:
220: if ($level === null)
221: {
222: $level = SQLSRV_TXN_READ_COMMITTED;
223: }
224:
225: switch ($level) {
226: case SQLSRV_TXN_READ_UNCOMMITTED:
227: $sql = "READ UNCOMMITTED";
228: break;
229: case SQLSRV_TXN_READ_COMMITTED:
230: $sql = "READ COMMITTED";
231: break;
232: case SQLSRV_TXN_REPEATABLE_READ:
233: $sql = "REPEATABLE READ";
234: break;
235: case SQLSRV_TXN_SNAPSHOT:
236: $sql = "SNAPSHOT";
237: break;
238: case SQLSRV_TXN_SERIALIZABLE:
239: $sql = "SERIALIZABLE";
240: break;
241: default:
242: require_once 'Zend/Db/Adapter/Sqlsrv/Exception.php';
243: throw new Zend_Db_Adapter_Sqlsrv_Exception("Invalid transaction isolation level mode '$level' specified");
244: }
245:
246: if (!sqlsrv_query($this->_connection, "SET TRANSACTION ISOLATION LEVEL $sql;")) {
247: require_once 'Zend/Db/Adapter/Sqlsrv/Exception.php';
248: throw new Zend_Db_Adapter_Sqlsrv_Exception("Transaction cannot be changed to '$level'");
249: }
250:
251: return true;
252: }
253:
254: 255: 256: 257: 258:
259: public function isConnected()
260: {
261: return (is_resource($this->_connection)
262: && (get_resource_type($this->_connection) == 'SQL Server Connection')
263: );
264: }
265:
266: 267: 268: 269: 270:
271: public function closeConnection()
272: {
273: if ($this->isConnected()) {
274: sqlsrv_close($this->_connection);
275: }
276: $this->_connection = null;
277: }
278:
279: 280: 281: 282: 283: 284:
285: public function prepare($sql)
286: {
287: $this->_connect();
288: $stmtClass = $this->_defaultStmtClass;
289:
290: if (!class_exists($stmtClass)) {
291: 292: 293:
294: require_once 'Zend/Loader.php';
295: Zend_Loader::loadClass($stmtClass);
296: }
297:
298: $stmt = new $stmtClass($this, $sql);
299: $stmt->setFetchMode($this->_fetchMode);
300: return $stmt;
301: }
302:
303: 304: 305: 306: 307: 308:
309: protected function _quote($value)
310: {
311: if (is_int($value)) {
312: return $value;
313: } elseif (is_float($value)) {
314: return sprintf('%F', $value);
315: }
316:
317: return "'" . str_replace("'", "''", $value) . "'";
318: }
319:
320: 321: 322: 323: 324: 325: 326: 327: 328: 329: 330: 331: 332: 333:
334: public function lastInsertId($tableName = null, $primaryKey = null)
335: {
336: if ($tableName) {
337: $tableName = $this->quote($tableName);
338: $sql = 'SELECT IDENT_CURRENT (' . $tableName . ') as Current_Identity';
339: return (string) $this->fetchOne($sql);
340: }
341:
342: if ($this->_lastInsertId > 0) {
343: return (string) $this->_lastInsertId;
344: }
345:
346: $sql = $this->_lastInsertSQL;
347: return (string) $this->fetchOne($sql);
348: }
349:
350: 351: 352: 353: 354: 355: 356:
357: public function insert($table, array $bind)
358: {
359:
360: $cols = array();
361: $vals = array();
362: foreach ($bind as $col => $val) {
363: $cols[] = $this->quoteIdentifier($col, true);
364: if ($val instanceof Zend_Db_Expr) {
365: $vals[] = $val->__toString();
366: unset($bind[$col]);
367: } else {
368: $vals[] = '?';
369: }
370: }
371:
372:
373: $sql = "INSERT INTO "
374: . $this->quoteIdentifier($table, true)
375: . ' (' . implode(', ', $cols) . ') '
376: . 'VALUES (' . implode(', ', $vals) . ')'
377: . ' ' . $this->_lastInsertSQL;
378:
379:
380: $stmt = $this->query($sql, array_values($bind));
381: $result = $stmt->rowCount();
382:
383: $stmt->nextRowset();
384:
385: $this->_lastInsertId = $stmt->fetchColumn();
386:
387: return $result;
388: }
389:
390: 391: 392: 393: 394:
395: public function listTables()
396: {
397: $this->_connect();
398: $sql = "SELECT name FROM sysobjects WHERE type = 'U' ORDER BY name";
399: return $this->fetchCol($sql);
400: }
401:
402: 403: 404: 405: 406: 407: 408: 409: 410: 411: 412: 413: 414: 415: 416: 417: 418: 419: 420: 421: 422: 423: 424: 425: 426: 427: 428: 429: 430: 431:
432: public function describeTable($tableName, $schemaName = null)
433: {
434: 435: 436:
437: $sql = "exec sp_columns @table_name = " . $this->quoteIdentifier($tableName, true);
438: $stmt = $this->query($sql);
439: $result = $stmt->fetchAll(Zend_Db::FETCH_NUM);
440:
441:
442: $stmt->closeCursor();
443:
444: if (count($result) == 0) {
445: return array();
446: }
447:
448: $owner = 1;
449: $table_name = 2;
450: $column_name = 3;
451: $type_name = 5;
452: $precision = 6;
453: $length = 7;
454: $scale = 8;
455: $nullable = 10;
456: $column_def = 12;
457: $column_position = 16;
458:
459: 460: 461:
462: $tableOwner = $result[0][$owner];
463: $sql = "exec sp_pkeys @table_owner = " . $tableOwner
464: . ", @table_name = " . $this->quoteIdentifier($tableName, true);
465: $stmt = $this->query($sql);
466:
467: $primaryKeysResult = $stmt->fetchAll(Zend_Db::FETCH_NUM);
468: $primaryKeyColumn = array();
469:
470:
471:
472:
473:
474: $pkey_column_name = 3;
475: $pkey_key_seq = 4;
476: foreach ($primaryKeysResult as $pkeysRow) {
477: $primaryKeyColumn[$pkeysRow[$pkey_column_name]] = $pkeysRow[$pkey_key_seq];
478: }
479:
480: $desc = array();
481: $p = 1;
482: foreach ($result as $key => $row) {
483: $identity = false;
484: $words = explode(' ', $row[$type_name], 2);
485: if (isset($words[0])) {
486: $type = $words[0];
487: if (isset($words[1])) {
488: $identity = (bool) preg_match('/identity/', $words[1]);
489: }
490: }
491:
492: $isPrimary = array_key_exists($row[$column_name], $primaryKeyColumn);
493: if ($isPrimary) {
494: $primaryPosition = $primaryKeyColumn[$row[$column_name]];
495: } else {
496: $primaryPosition = null;
497: }
498:
499: $desc[$this->foldCase($row[$column_name])] = array(
500: 'SCHEMA_NAME' => null,
501: 'TABLE_NAME' => $this->foldCase($row[$table_name]),
502: 'COLUMN_NAME' => $this->foldCase($row[$column_name]),
503: 'COLUMN_POSITION' => (int) $row[$column_position],
504: 'DATA_TYPE' => $type,
505: 'DEFAULT' => $row[$column_def],
506: 'NULLABLE' => (bool) $row[$nullable],
507: 'LENGTH' => $row[$length],
508: 'SCALE' => $row[$scale],
509: 'PRECISION' => $row[$precision],
510: 'UNSIGNED' => null,
511: 'PRIMARY' => $isPrimary,
512: 'PRIMARY_POSITION' => $primaryPosition,
513: 'IDENTITY' => $identity,
514: );
515: }
516:
517: return $desc;
518: }
519:
520: 521: 522: 523: 524: 525:
526: protected function _beginTransaction()
527: {
528: if (!sqlsrv_begin_transaction($this->_connection)) {
529: require_once 'Zend/Db/Adapter/Sqlsrv/Exception.php';
530: throw new Zend_Db_Adapter_Sqlsrv_Exception(sqlsrv_errors());
531: }
532: }
533:
534: 535: 536: 537: 538: 539:
540: protected function _commit()
541: {
542: if (!sqlsrv_commit($this->_connection)) {
543: require_once 'Zend/Db/Adapter/Sqlsrv/Exception.php';
544: throw new Zend_Db_Adapter_Sqlsrv_Exception(sqlsrv_errors());
545: }
546: }
547:
548: 549: 550: 551: 552: 553:
554: protected function _rollBack()
555: {
556: if (!sqlsrv_rollback($this->_connection)) {
557: require_once 'Zend/Db/Adapter/Sqlsrv/Exception.php';
558: throw new Zend_Db_Adapter_Sqlsrv_Exception(sqlsrv_errors());
559: }
560: }
561:
562: 563: 564: 565: 566: 567: 568: 569: 570:
571: public function setFetchMode($mode)
572: {
573: switch ($mode) {
574: case Zend_Db::FETCH_NUM:
575: case Zend_Db::FETCH_ASSOC:
576: case Zend_Db::FETCH_BOTH:
577: case Zend_Db::FETCH_OBJ:
578: $this->_fetchMode = $mode;
579: break;
580: case Zend_Db::FETCH_BOUND:
581: require_once 'Zend/Db/Adapter/Sqlsrv/Exception.php';
582: throw new Zend_Db_Adapter_Sqlsrv_Exception('FETCH_BOUND is not supported yet');
583: break;
584: default:
585: require_once 'Zend/Db/Adapter/Sqlsrv/Exception.php';
586: throw new Zend_Db_Adapter_Sqlsrv_Exception("Invalid fetch mode '$mode' specified");
587: break;
588: }
589: }
590:
591: 592: 593: 594: 595: 596: 597: 598: 599:
600: public function limit($sql, $count, $offset = 0)
601: {
602: $count = intval($count);
603: if ($count <= 0) {
604: require_once 'Zend/Db/Adapter/Exception.php';
605: throw new Zend_Db_Adapter_Exception("LIMIT argument count=$count is not valid");
606: }
607:
608: $offset = intval($offset);
609: if ($offset < 0) {
610:
611: require_once 'Zend/Db/Adapter/Exception.php';
612: throw new Zend_Db_Adapter_Exception("LIMIT argument offset=$offset is not valid");
613: }
614:
615: if ($offset == 0) {
616: $sql = preg_replace('/^SELECT\s/i', 'SELECT TOP ' . $count . ' ', $sql);
617: } else {
618: $orderby = stristr($sql, 'ORDER BY');
619:
620: if (!$orderby) {
621: $over = 'ORDER BY (SELECT 0)';
622: } else {
623: $over = preg_replace('/\"[^,]*\".\"([^,]*)\"/i', '"inner_tbl"."$1"', $orderby);
624: }
625:
626:
627: $sql = preg_replace('/\s+ORDER BY(.*)/', '', $sql);
628:
629:
630: $sql = "SELECT ROW_NUMBER() OVER ($over) AS \"ZEND_DB_ROWNUM\", * FROM ($sql) AS inner_tbl";
631:
632: $start = $offset + 1;
633: $end = $offset + $count;
634:
635: $sql = "WITH outer_tbl AS ($sql) SELECT * FROM outer_tbl WHERE \"ZEND_DB_ROWNUM\" BETWEEN $start AND $end";
636: }
637:
638: return $sql;
639: }
640:
641: 642: 643: 644: 645: 646:
647: public function supportsParameters($type)
648: {
649: if ($type == 'positional') {
650: return true;
651: }
652:
653:
654: return false;
655: }
656:
657: 658: 659: 660: 661:
662: public function getServerVersion()
663: {
664: $this->_connect();
665: $serverInfo = sqlsrv_server_info($this->_connection);
666:
667: if ($serverInfo !== false) {
668: return $serverInfo['SQLServerVersion'];
669: }
670:
671: return null;
672: }
673: }
674: