PageRenderTime 58ms CodeModel.GetById 26ms RepoModel.GetById 0ms app.codeStats 0ms

/library/Zend/Db/Adapter/Sqlsrv.php

http://github.com/zendframework/zf2
PHP | 643 lines | 494 code | 30 blank | 119 comment | 28 complexity | abd0e096e4afeca09fcfff3ef8d740f8 MD5 | raw file
Possible License(s): BSD-3-Clause
  1. <?php
  2. /**
  3. * Zend Framework
  4. *
  5. * LICENSE
  6. *
  7. * This source file is subject to the new BSD license that is bundled
  8. * with this package in the file LICENSE.txt.
  9. * It is also available through the world-wide-web at this URL:
  10. * http://framework.zend.com/license/new-bsd
  11. * If you did not receive a copy of the license and are unable to
  12. * obtain it through the world-wide-web, please send an email
  13. * to license@zend.com so we can send you a copy immediately.
  14. *
  15. * @category Zend
  16. * @package Zend_Db
  17. * @subpackage Adapter
  18. * @copyright Copyright (c) 2005-2012 Zend Technologies USA Inc. (http://www.zend.com)
  19. * @license http://framework.zend.com/license/new-bsd New BSD License
  20. */
  21. /**
  22. * @namespace
  23. */
  24. namespace Zend\Db\Adapter;
  25. use Zend\Db;
  26. /**
  27. * @uses \Zend\Db\Db
  28. * @uses \Zend\Db\Adapter\AbstractAdapter
  29. * @uses \Zend\Db\Adapter\Exception
  30. * @uses \Zend\Db\Adapter\SqlsrvException
  31. * @uses \Zend\Db\Statement\Sqlsrv
  32. * @uses \Zend\Loader
  33. * @category Zend
  34. * @package Zend_Db
  35. * @subpackage Adapter
  36. * @copyright Copyright (c) 2005-2012 Zend Technologies USA Inc. (http://www.zend.com)
  37. * @license http://framework.zend.com/license/new-bsd New BSD License
  38. */
  39. class Sqlsrv extends AbstractAdapter
  40. {
  41. /**
  42. * User-provided configuration.
  43. *
  44. * Basic keys are:
  45. *
  46. * username => (string) Connect to the database as this username.
  47. * password => (string) Password associated with the username.
  48. * dbname => The name of the local SQL Server instance
  49. *
  50. * @var array
  51. */
  52. protected $_config = array(
  53. 'dbname' => null,
  54. 'username' => null,
  55. 'password' => null,
  56. );
  57. /**
  58. * Last insert id from INSERT query
  59. *
  60. * @var int
  61. */
  62. protected $_lastInsertId;
  63. /**
  64. * Query used to fetch last insert id
  65. *
  66. * @var string
  67. */
  68. protected $_lastInsertSQL = 'SELECT SCOPE_IDENTITY() as Current_Identity';
  69. /**
  70. * Keys are UPPERCASE SQL datatypes or the constants
  71. * Zend_Db::INT_TYPE, Zend_Db::BIGINT_TYPE, or Zend_Db::FLOAT_TYPE.
  72. *
  73. * Values are:
  74. * 0 = 32-bit integer
  75. * 1 = 64-bit integer
  76. * 2 = float or decimal
  77. *
  78. * @var array Associative array of datatypes to values 0, 1, or 2.
  79. */
  80. protected $_numericDataTypes = array(
  81. Db\Db::INT_TYPE => Db\Db::INT_TYPE,
  82. Db\Db::BIGINT_TYPE => Db\Db::BIGINT_TYPE,
  83. Db\Db::FLOAT_TYPE => Db\Db::FLOAT_TYPE,
  84. 'INT' => Db\Db::INT_TYPE,
  85. 'SMALLINT' => Db\Db::INT_TYPE,
  86. 'TINYINT' => Db\Db::INT_TYPE,
  87. 'BIGINT' => Db\Db::BIGINT_TYPE,
  88. 'DECIMAL' => Db\Db::FLOAT_TYPE,
  89. 'FLOAT' => Db\Db::FLOAT_TYPE,
  90. 'MONEY' => Db\Db::FLOAT_TYPE,
  91. 'NUMERIC' => Db\Db::FLOAT_TYPE,
  92. 'REAL' => Db\Db::FLOAT_TYPE,
  93. 'SMALLMONEY' => Db\Db::FLOAT_TYPE,
  94. );
  95. /**
  96. * Default class name for a DB statement.
  97. *
  98. * @var string
  99. */
  100. protected $_defaultStmtClass = 'Zend\Db\Statement\Sqlsrv';
  101. /**
  102. * Creates a connection resource.
  103. *
  104. * @return void
  105. * @throws \Zend\Db\Adapter\SqlsrvException
  106. */
  107. protected function _connect()
  108. {
  109. if (is_resource($this->_connection)) {
  110. // connection already exists
  111. return;
  112. }
  113. if (!extension_loaded('sqlsrv')) {
  114. throw new SqlsrvException('The Sqlsrv extension is required for this adapter but the extension is not loaded');
  115. }
  116. $serverName = $this->_config['host'];
  117. if (isset($this->_config['port'])) {
  118. $port = (integer) $this->_config['port'];
  119. $serverName .= ', ' . $port;
  120. }
  121. $connectionInfo = array(
  122. 'Database' => $this->_config['dbname'],
  123. );
  124. if (isset($this->_config['username']) && isset($this->_config['password']))
  125. {
  126. $connectionInfo += array(
  127. 'UID' => $this->_config['username'],
  128. 'PWD' => $this->_config['password'],
  129. );
  130. }
  131. // else - windows authentication
  132. if (!empty($this->_config['driver_options'])) {
  133. foreach ($this->_config['driver_options'] as $option => $value) {
  134. // A value may be a constant.
  135. if (is_string($value)) {
  136. $constantName = strtoupper($value);
  137. if (defined($constantName)) {
  138. $connectionInfo[$option] = constant($constantName);
  139. } else {
  140. $connectionInfo[$option] = $value;
  141. }
  142. }
  143. }
  144. }
  145. $this->_connection = sqlsrv_connect($serverName, $connectionInfo);
  146. if (!$this->_connection) {
  147. throw new SqlsrvException(sqlsrv_errors());
  148. }
  149. }
  150. /**
  151. * Check for config options that are mandatory.
  152. * Throw exceptions if any are missing.
  153. *
  154. * @param array $config
  155. * @throws \Zend\Db\Adapter\Exception
  156. */
  157. protected function _checkRequiredOptions(array $config)
  158. {
  159. // we need at least a dbname
  160. if (! array_key_exists('dbname', $config)) {
  161. throw new Exception("Configuration array must have a key for 'dbname' that names the database instance");
  162. }
  163. if (! array_key_exists('password', $config) && array_key_exists('username', $config)) {
  164. throw new Exception("Configuration array must have a key for 'password' for login credentials.
  165. If Windows Authentication is desired, both keys 'username' and 'password' should be ommited from config.");
  166. }
  167. if (array_key_exists('password', $config) && !array_key_exists('username', $config)) {
  168. throw new Exception("Configuration array must have a key for 'username' for login credentials.
  169. If Windows Authentication is desired, both keys 'username' and 'password' should be ommited from config.");
  170. }
  171. }
  172. /**
  173. * Set the transaction isoltion level.
  174. *
  175. * @param integer|null $level A fetch mode from Sqlsrv_TXN_*.
  176. * @return true
  177. * @throws \Zend\Db\Adapter\SqlsrvException
  178. */
  179. public function setTransactionIsolationLevel($level = null)
  180. {
  181. $this->_connect();
  182. $sql = null;
  183. // Default transaction level in sql server
  184. if ($level === null)
  185. {
  186. $level = Sqlsrv_TXN_READ_COMMITTED;
  187. }
  188. switch ($level) {
  189. case Sqlsrv_TXN_READ_UNCOMMITTED:
  190. $sql = "READ UNCOMMITTED";
  191. break;
  192. case Sqlsrv_TXN_READ_COMMITTED:
  193. $sql = "READ COMMITTED";
  194. break;
  195. case Sqlsrv_TXN_REPEATABLE_READ:
  196. $sql = "REPEATABLE READ";
  197. break;
  198. case Sqlsrv_TXN_SNAPSHOT:
  199. $sql = "SNAPSHOT";
  200. break;
  201. case Sqlsrv_TXN_SERIALIZABLE:
  202. $sql = "SERIALIZABLE";
  203. break;
  204. default:
  205. throw new SqlsrvException("Invalid transaction isolation level mode '$level' specified");
  206. }
  207. if (!sqlsrv_query($this->_connection, "SET TRANSACTION ISOLATION LEVEL $sql;")) {
  208. throw new SqlsrvException("Transaction cannot be changed to '$level'");
  209. }
  210. return true;
  211. }
  212. /**
  213. * Test if a connection is active
  214. *
  215. * @return boolean
  216. */
  217. public function isConnected()
  218. {
  219. return (is_resource($this->_connection)
  220. && (get_resource_type($this->_connection) == 'SQL Server Connection')
  221. );
  222. }
  223. /**
  224. * Force the connection to close.
  225. *
  226. * @return void
  227. */
  228. public function closeConnection()
  229. {
  230. if ($this->isConnected()) {
  231. sqlsrv_close($this->_connection);
  232. }
  233. $this->_connection = null;
  234. }
  235. /**
  236. * Returns an SQL statement for preparation.
  237. *
  238. * @param string $sql The SQL statement with placeholders.
  239. * @return \Zend\Db\Statement\Sqlsrv
  240. */
  241. public function prepare($sql)
  242. {
  243. $this->_connect();
  244. $stmtClass = $this->_defaultStmtClass;
  245. if (!class_exists($stmtClass)) {
  246. \Zend\Loader::loadClass($stmtClass);
  247. }
  248. $stmt = new $stmtClass($this, $sql);
  249. $stmt->setFetchMode($this->_fetchMode);
  250. return $stmt;
  251. }
  252. /**
  253. * Quote a raw string.
  254. *
  255. * @param string $value Raw string
  256. * @return string Quoted string
  257. */
  258. protected function _quote($value)
  259. {
  260. if (is_int($value)) {
  261. return $value;
  262. } elseif (is_float($value)) {
  263. return sprintf('%F', $value);
  264. }
  265. return "'" . str_replace("'", "''", $value) . "'";
  266. }
  267. /**
  268. * Gets the last ID generated automatically by an IDENTITY/AUTOINCREMENT column.
  269. *
  270. * As a convention, on RDBMS brands that support sequences
  271. * (e.g. Oracle, PostgreSQL, Db2), this method forms the name of a sequence
  272. * from the arguments and returns the last id generated by that sequence.
  273. * On RDBMS brands that support IDENTITY/AUTOINCREMENT columns, this method
  274. * returns the last value generated for such a column, and the table name
  275. * argument is disregarded.
  276. *
  277. * @param string $tableName OPTIONAL Name of table.
  278. * @param string $primaryKey OPTIONAL Name of primary key column.
  279. * @return string
  280. */
  281. public function lastInsertId($tableName = null, $primaryKey = null)
  282. {
  283. if ($tableName) {
  284. $tableName = $this->quote($tableName);
  285. $sql = 'SELECT IDENT_CURRENT (' . $tableName . ') as Current_Identity';
  286. return (string) $this->fetchOne($sql);
  287. }
  288. if ($this->_lastInsertId > 0) {
  289. return (string) $this->_lastInsertId;
  290. }
  291. $sql = $this->_lastInsertSQL;
  292. return (string) $this->fetchOne($sql);
  293. }
  294. /**
  295. * Inserts a table row with specified data.
  296. *
  297. * @param mixed $table The table to insert data into.
  298. * @param array $bind Column-value pairs.
  299. * @return int The number of affected rows.
  300. */
  301. public function insert($table, array $bind)
  302. {
  303. // extract and quote col names from the array keys
  304. $cols = array();
  305. $vals = array();
  306. foreach ($bind as $col => $val) {
  307. $cols[] = $this->quoteIdentifier($col, true);
  308. if ($val instanceof Db\Expr) {
  309. $vals[] = $val->__toString();
  310. unset($bind[$col]);
  311. } else {
  312. $vals[] = '?';
  313. }
  314. }
  315. // build the statement
  316. $sql = "INSERT INTO "
  317. . $this->quoteIdentifier($table, true)
  318. . ' (' . implode(', ', $cols) . ') '
  319. . 'VALUES (' . implode(', ', $vals) . ')'
  320. . ' ' . $this->_lastInsertSQL;
  321. // execute the statement and return the number of affected rows
  322. $stmt = $this->query($sql, array_values($bind));
  323. $result = $stmt->rowCount();
  324. $stmt->nextRowset();
  325. $this->_lastInsertId = $stmt->fetchColumn();
  326. return $result;
  327. }
  328. /**
  329. * Returns a list of the tables in the database.
  330. *
  331. * @return array
  332. */
  333. public function listTables()
  334. {
  335. $this->_connect();
  336. $sql = "SELECT name FROM sysobjects WHERE type = 'U' ORDER BY name";
  337. return $this->fetchCol($sql);
  338. }
  339. /**
  340. * Returns the column descriptions for a table.
  341. *
  342. * The return value is an associative array keyed by the column name,
  343. * as returned by the RDBMS.
  344. *
  345. * The value of each array element is an associative array
  346. * with the following keys:
  347. *
  348. * SCHEMA_NAME => string; name of schema
  349. * TABLE_NAME => string;
  350. * COLUMN_NAME => string; column name
  351. * COLUMN_POSITION => number; ordinal position of column in table
  352. * DATA_TYPE => string; SQL datatype name of column
  353. * DEFAULT => string; default expression of column, null if none
  354. * NULLABLE => boolean; true if column can have nulls
  355. * LENGTH => number; length of CHAR/VARCHAR
  356. * SCALE => number; scale of NUMERIC/DECIMAL
  357. * PRECISION => number; precision of NUMERIC/DECIMAL
  358. * UNSIGNED => boolean; unsigned property of an integer type
  359. * PRIMARY => boolean; true if column is part of the primary key
  360. * PRIMARY_POSITION => integer; position of column in primary key
  361. * IDENTITY => integer; true if column is auto-generated with unique values
  362. *
  363. * @todo Discover integer unsigned property.
  364. *
  365. * @param string $tableName
  366. * @param string $schemaName OPTIONAL
  367. * @return array
  368. */
  369. public function describeTable($tableName, $schemaName = null)
  370. {
  371. /**
  372. * Discover metadata information about this table.
  373. */
  374. $sql = "exec sp_columns @table_name = " . $this->quoteIdentifier($tableName, true);
  375. $stmt = $this->query($sql);
  376. $result = $stmt->fetchAll(Db\Db::FETCH_NUM);
  377. // ZF-7698
  378. $stmt->closeCursor();
  379. if (count($result) == 0) {
  380. return array();
  381. }
  382. $owner = 1;
  383. $table_name = 2;
  384. $column_name = 3;
  385. $type_name = 5;
  386. $precision = 6;
  387. $length = 7;
  388. $scale = 8;
  389. $nullable = 10;
  390. $column_def = 12;
  391. $column_position = 16;
  392. /**
  393. * Discover primary key column(s) for this table.
  394. */
  395. $tableOwner = $result[0][$owner];
  396. $sql = "exec sp_pkeys @table_owner = " . $tableOwner
  397. . ", @table_name = " . $this->quoteIdentifier($tableName, true);
  398. $stmt = $this->query($sql);
  399. $primaryKeysResult = $stmt->fetchAll(Db\Db::FETCH_NUM);
  400. $primaryKeyColumn = array();
  401. // Per http://msdn.microsoft.com/en-us/library/ms189813.aspx,
  402. // results from sp_keys stored procedure are:
  403. // 0=TABLE_QUALIFIER 1=TABLE_OWNER 2=TABLE_NAME 3=COLUMN_NAME 4=KEY_SEQ 5=PK_NAME
  404. $pkey_column_name = 3;
  405. $pkey_key_seq = 4;
  406. foreach ($primaryKeysResult as $pkeysRow) {
  407. $primaryKeyColumn[$pkeysRow[$pkey_column_name]] = $pkeysRow[$pkey_key_seq];
  408. }
  409. $desc = array();
  410. $p = 1;
  411. foreach ($result as $key => $row) {
  412. $identity = false;
  413. $words = explode(' ', $row[$type_name], 2);
  414. if (isset($words[0])) {
  415. $type = $words[0];
  416. if (isset($words[1])) {
  417. $identity = (bool) preg_match('/identity/', $words[1]);
  418. }
  419. }
  420. $isPrimary = array_key_exists($row[$column_name], $primaryKeyColumn);
  421. if ($isPrimary) {
  422. $primaryPosition = $primaryKeyColumn[$row[$column_name]];
  423. } else {
  424. $primaryPosition = null;
  425. }
  426. $desc[$this->foldCase($row[$column_name])] = array(
  427. 'SCHEMA_NAME' => null, // @todo
  428. 'TABLE_NAME' => $this->foldCase($row[$table_name]),
  429. 'COLUMN_NAME' => $this->foldCase($row[$column_name]),
  430. 'COLUMN_POSITION' => (int) $row[$column_position],
  431. 'DATA_TYPE' => $type,
  432. 'DEFAULT' => $row[$column_def],
  433. 'NULLABLE' => (bool) $row[$nullable],
  434. 'LENGTH' => $row[$length],
  435. 'SCALE' => $row[$scale],
  436. 'PRECISION' => $row[$precision],
  437. 'UNSIGNED' => null, // @todo
  438. 'PRIMARY' => $isPrimary,
  439. 'PRIMARY_POSITION' => $primaryPosition,
  440. 'IDENTITY' => $identity,
  441. );
  442. }
  443. return $desc;
  444. }
  445. /**
  446. * Leave autocommit mode and begin a transaction.
  447. *
  448. * @return void
  449. * @throws \Zend\Db\Adapter\SqlsrvException
  450. */
  451. protected function _beginTransaction()
  452. {
  453. if (!sqlsrv_begin_transaction($this->_connection)) {
  454. throw new SqlsrvException(sqlsrv_errors());
  455. }
  456. }
  457. /**
  458. * Commit a transaction and return to autocommit mode.
  459. *
  460. * @return void
  461. * @throws \Zend\Db\Adapter\SqlsrvException
  462. */
  463. protected function _commit()
  464. {
  465. if (!sqlsrv_commit($this->_connection)) {
  466. throw new SqlsrvException(sqlsrv_errors());
  467. }
  468. }
  469. /**
  470. * Roll back a transaction and return to autocommit mode.
  471. *
  472. * @return void
  473. * @throws \Zend\Db\Adapter\SqlsrvException
  474. */
  475. protected function _rollBack()
  476. {
  477. if (!sqlsrv_rollback($this->_connection)) {
  478. throw new SqlsrvException(sqlsrv_errors());
  479. }
  480. }
  481. /**
  482. * Set the fetch mode.
  483. *
  484. * @todo Support FETCH_CLASS and FETCH_INTO.
  485. *
  486. * @param integer $mode A fetch mode.
  487. * @return void
  488. * @throws \Zend\Db\Adapter\SqlsrvException
  489. */
  490. public function setFetchMode($mode)
  491. {
  492. switch ($mode) {
  493. case Db\Db::FETCH_NUM: // seq array
  494. case Db\Db::FETCH_ASSOC: // assoc array
  495. case Db\Db::FETCH_BOTH: // seq+assoc array
  496. case Db\Db::FETCH_OBJ: // object
  497. $this->_fetchMode = $mode;
  498. break;
  499. case Db\Db::FETCH_BOUND: // bound to PHP variable
  500. throw new SqlsrvException('FETCH_BOUND is not supported yet');
  501. break;
  502. default:
  503. throw new SqlsrvException("Invalid fetch mode '$mode' specified");
  504. break;
  505. }
  506. }
  507. /**
  508. * Adds an adapter-specific LIMIT clause to the SELECT statement.
  509. *
  510. * @param string $sql
  511. * @param integer $count
  512. * @param integer $offset OPTIONAL
  513. * @return string
  514. * @throws \Zend\Db\Adapter\SqlsrvException
  515. */
  516. public function limit($sql, $count, $offset = 0)
  517. {
  518. $count = intval($count);
  519. if ($count <= 0) {
  520. throw new Exception("LIMIT argument count=$count is not valid");
  521. }
  522. $offset = intval($offset);
  523. if ($offset < 0) {
  524. throw new Exception("LIMIT argument offset=$offset is not valid");
  525. }
  526. if ($offset == 0) {
  527. $sql = preg_replace('/^SELECT\s/i', 'SELECT TOP ' . $count . ' ', $sql);
  528. } else {
  529. $orderby = stristr($sql, 'ORDER BY');
  530. if (!$orderby) {
  531. $over = 'ORDER BY (SELECT 0)';
  532. } else {
  533. $over = preg_replace('/\"[^,]*\".\"([^,]*)\"/i', '"inner_tbl"."$1"', $orderby);
  534. }
  535. // Remove ORDER BY clause from $sql
  536. $sql = preg_replace('/\s+ORDER BY(.*)/', '', $sql);
  537. // Add ORDER BY clause as an argument for ROW_NUMBER()
  538. $sql = "SELECT ROW_NUMBER() OVER ($over) AS \"ZEND_DB_ROWNUM\", * FROM ($sql) AS inner_tbl";
  539. $start = $offset + 1;
  540. $end = $offset + $count;
  541. $sql = "WITH outer_tbl AS ($sql) SELECT * FROM outer_tbl WHERE \"ZEND_DB_ROWNUM\" BETWEEN $start AND $end";
  542. }
  543. return $sql;
  544. }
  545. /**
  546. * Check if the adapter supports real SQL parameters.
  547. *
  548. * @param string $type 'positional' or 'named'
  549. * @return bool
  550. */
  551. public function supportsParameters($type)
  552. {
  553. if ($type == 'positional') {
  554. return true;
  555. }
  556. // if its 'named' or anything else
  557. return false;
  558. }
  559. /**
  560. * Retrieve server version in PHP style
  561. *
  562. * @return string
  563. */
  564. public function getServerVersion()
  565. {
  566. $this->_connect();
  567. $serverInfo = sqlsrv_server_info($this->_connection);
  568. if ($serverInfo !== false) {
  569. return $serverInfo['SQLServerVersion'];
  570. }
  571. return null;
  572. }
  573. }