/library/Zend/Db/Adapter/Sqlsrv.php
PHP | 643 lines | 494 code | 30 blank | 119 comment | 28 complexity | abd0e096e4afeca09fcfff3ef8d740f8 MD5 | raw file
Possible License(s): BSD-3-Clause
- <?php
- /**
- * Zend Framework
- *
- * LICENSE
- *
- * This source file is subject to the new BSD license that is bundled
- * with this package in the file LICENSE.txt.
- * It is also available through the world-wide-web at this URL:
- * http://framework.zend.com/license/new-bsd
- * If you did not receive a copy of the license and are unable to
- * obtain it through the world-wide-web, please send an email
- * to license@zend.com so we can send you a copy immediately.
- *
- * @category Zend
- * @package Zend_Db
- * @subpackage Adapter
- * @copyright Copyright (c) 2005-2012 Zend Technologies USA Inc. (http://www.zend.com)
- * @license http://framework.zend.com/license/new-bsd New BSD License
- */
- /**
- * @namespace
- */
- namespace Zend\Db\Adapter;
- use Zend\Db;
- /**
- * @uses \Zend\Db\Db
- * @uses \Zend\Db\Adapter\AbstractAdapter
- * @uses \Zend\Db\Adapter\Exception
- * @uses \Zend\Db\Adapter\SqlsrvException
- * @uses \Zend\Db\Statement\Sqlsrv
- * @uses \Zend\Loader
- * @category Zend
- * @package Zend_Db
- * @subpackage Adapter
- * @copyright Copyright (c) 2005-2012 Zend Technologies USA Inc. (http://www.zend.com)
- * @license http://framework.zend.com/license/new-bsd New BSD License
- */
- class Sqlsrv extends AbstractAdapter
- {
- /**
- * User-provided configuration.
- *
- * Basic keys are:
- *
- * username => (string) Connect to the database as this username.
- * password => (string) Password associated with the username.
- * dbname => The name of the local SQL Server instance
- *
- * @var array
- */
- protected $_config = array(
- 'dbname' => null,
- 'username' => null,
- 'password' => null,
- );
- /**
- * Last insert id from INSERT query
- *
- * @var int
- */
- protected $_lastInsertId;
- /**
- * Query used to fetch last insert id
- *
- * @var string
- */
- protected $_lastInsertSQL = 'SELECT SCOPE_IDENTITY() as Current_Identity';
- /**
- * Keys are UPPERCASE SQL datatypes or the constants
- * Zend_Db::INT_TYPE, Zend_Db::BIGINT_TYPE, or Zend_Db::FLOAT_TYPE.
- *
- * Values are:
- * 0 = 32-bit integer
- * 1 = 64-bit integer
- * 2 = float or decimal
- *
- * @var array Associative array of datatypes to values 0, 1, or 2.
- */
- protected $_numericDataTypes = array(
- Db\Db::INT_TYPE => Db\Db::INT_TYPE,
- Db\Db::BIGINT_TYPE => Db\Db::BIGINT_TYPE,
- Db\Db::FLOAT_TYPE => Db\Db::FLOAT_TYPE,
- 'INT' => Db\Db::INT_TYPE,
- 'SMALLINT' => Db\Db::INT_TYPE,
- 'TINYINT' => Db\Db::INT_TYPE,
- 'BIGINT' => Db\Db::BIGINT_TYPE,
- 'DECIMAL' => Db\Db::FLOAT_TYPE,
- 'FLOAT' => Db\Db::FLOAT_TYPE,
- 'MONEY' => Db\Db::FLOAT_TYPE,
- 'NUMERIC' => Db\Db::FLOAT_TYPE,
- 'REAL' => Db\Db::FLOAT_TYPE,
- 'SMALLMONEY' => Db\Db::FLOAT_TYPE,
- );
- /**
- * Default class name for a DB statement.
- *
- * @var string
- */
- protected $_defaultStmtClass = 'Zend\Db\Statement\Sqlsrv';
- /**
- * Creates a connection resource.
- *
- * @return void
- * @throws \Zend\Db\Adapter\SqlsrvException
- */
- protected function _connect()
- {
- if (is_resource($this->_connection)) {
- // connection already exists
- return;
- }
- if (!extension_loaded('sqlsrv')) {
- throw new SqlsrvException('The Sqlsrv extension is required for this adapter but the extension is not loaded');
- }
- $serverName = $this->_config['host'];
- if (isset($this->_config['port'])) {
- $port = (integer) $this->_config['port'];
- $serverName .= ', ' . $port;
- }
- $connectionInfo = array(
- 'Database' => $this->_config['dbname'],
- );
- if (isset($this->_config['username']) && isset($this->_config['password']))
- {
- $connectionInfo += array(
- 'UID' => $this->_config['username'],
- 'PWD' => $this->_config['password'],
- );
- }
- // else - windows authentication
- if (!empty($this->_config['driver_options'])) {
- foreach ($this->_config['driver_options'] as $option => $value) {
- // A value may be a constant.
- if (is_string($value)) {
- $constantName = strtoupper($value);
- if (defined($constantName)) {
- $connectionInfo[$option] = constant($constantName);
- } else {
- $connectionInfo[$option] = $value;
- }
- }
- }
- }
- $this->_connection = sqlsrv_connect($serverName, $connectionInfo);
- if (!$this->_connection) {
- throw new SqlsrvException(sqlsrv_errors());
- }
- }
- /**
- * Check for config options that are mandatory.
- * Throw exceptions if any are missing.
- *
- * @param array $config
- * @throws \Zend\Db\Adapter\Exception
- */
- protected function _checkRequiredOptions(array $config)
- {
- // we need at least a dbname
- if (! array_key_exists('dbname', $config)) {
- throw new Exception("Configuration array must have a key for 'dbname' that names the database instance");
- }
- if (! array_key_exists('password', $config) && array_key_exists('username', $config)) {
- throw new Exception("Configuration array must have a key for 'password' for login credentials.
- If Windows Authentication is desired, both keys 'username' and 'password' should be ommited from config.");
- }
- if (array_key_exists('password', $config) && !array_key_exists('username', $config)) {
- throw new Exception("Configuration array must have a key for 'username' for login credentials.
- If Windows Authentication is desired, both keys 'username' and 'password' should be ommited from config.");
- }
- }
- /**
- * Set the transaction isoltion level.
- *
- * @param integer|null $level A fetch mode from Sqlsrv_TXN_*.
- * @return true
- * @throws \Zend\Db\Adapter\SqlsrvException
- */
- public function setTransactionIsolationLevel($level = null)
- {
- $this->_connect();
- $sql = null;
- // Default transaction level in sql server
- if ($level === null)
- {
- $level = Sqlsrv_TXN_READ_COMMITTED;
- }
- switch ($level) {
- case Sqlsrv_TXN_READ_UNCOMMITTED:
- $sql = "READ UNCOMMITTED";
- break;
- case Sqlsrv_TXN_READ_COMMITTED:
- $sql = "READ COMMITTED";
- break;
- case Sqlsrv_TXN_REPEATABLE_READ:
- $sql = "REPEATABLE READ";
- break;
- case Sqlsrv_TXN_SNAPSHOT:
- $sql = "SNAPSHOT";
- break;
- case Sqlsrv_TXN_SERIALIZABLE:
- $sql = "SERIALIZABLE";
- break;
- default:
- throw new SqlsrvException("Invalid transaction isolation level mode '$level' specified");
- }
- if (!sqlsrv_query($this->_connection, "SET TRANSACTION ISOLATION LEVEL $sql;")) {
- throw new SqlsrvException("Transaction cannot be changed to '$level'");
- }
- return true;
- }
- /**
- * Test if a connection is active
- *
- * @return boolean
- */
- public function isConnected()
- {
- return (is_resource($this->_connection)
- && (get_resource_type($this->_connection) == 'SQL Server Connection')
- );
- }
- /**
- * Force the connection to close.
- *
- * @return void
- */
- public function closeConnection()
- {
- if ($this->isConnected()) {
- sqlsrv_close($this->_connection);
- }
- $this->_connection = null;
- }
- /**
- * Returns an SQL statement for preparation.
- *
- * @param string $sql The SQL statement with placeholders.
- * @return \Zend\Db\Statement\Sqlsrv
- */
- public function prepare($sql)
- {
- $this->_connect();
- $stmtClass = $this->_defaultStmtClass;
- if (!class_exists($stmtClass)) {
- \Zend\Loader::loadClass($stmtClass);
- }
- $stmt = new $stmtClass($this, $sql);
- $stmt->setFetchMode($this->_fetchMode);
- return $stmt;
- }
- /**
- * Quote a raw string.
- *
- * @param string $value Raw string
- * @return string Quoted string
- */
- protected function _quote($value)
- {
- if (is_int($value)) {
- return $value;
- } elseif (is_float($value)) {
- return sprintf('%F', $value);
- }
- return "'" . str_replace("'", "''", $value) . "'";
- }
- /**
- * Gets the last ID generated automatically by an IDENTITY/AUTOINCREMENT column.
- *
- * As a convention, on RDBMS brands that support sequences
- * (e.g. Oracle, PostgreSQL, Db2), this method forms the name of a sequence
- * from the arguments and returns the last id generated by that sequence.
- * On RDBMS brands that support IDENTITY/AUTOINCREMENT columns, this method
- * returns the last value generated for such a column, and the table name
- * argument is disregarded.
- *
- * @param string $tableName OPTIONAL Name of table.
- * @param string $primaryKey OPTIONAL Name of primary key column.
- * @return string
- */
- public function lastInsertId($tableName = null, $primaryKey = null)
- {
- if ($tableName) {
- $tableName = $this->quote($tableName);
- $sql = 'SELECT IDENT_CURRENT (' . $tableName . ') as Current_Identity';
- return (string) $this->fetchOne($sql);
- }
- if ($this->_lastInsertId > 0) {
- return (string) $this->_lastInsertId;
- }
- $sql = $this->_lastInsertSQL;
- return (string) $this->fetchOne($sql);
- }
- /**
- * Inserts a table row with specified data.
- *
- * @param mixed $table The table to insert data into.
- * @param array $bind Column-value pairs.
- * @return int The number of affected rows.
- */
- public function insert($table, array $bind)
- {
- // extract and quote col names from the array keys
- $cols = array();
- $vals = array();
- foreach ($bind as $col => $val) {
- $cols[] = $this->quoteIdentifier($col, true);
- if ($val instanceof Db\Expr) {
- $vals[] = $val->__toString();
- unset($bind[$col]);
- } else {
- $vals[] = '?';
- }
- }
- // build the statement
- $sql = "INSERT INTO "
- . $this->quoteIdentifier($table, true)
- . ' (' . implode(', ', $cols) . ') '
- . 'VALUES (' . implode(', ', $vals) . ')'
- . ' ' . $this->_lastInsertSQL;
- // execute the statement and return the number of affected rows
- $stmt = $this->query($sql, array_values($bind));
- $result = $stmt->rowCount();
- $stmt->nextRowset();
- $this->_lastInsertId = $stmt->fetchColumn();
- return $result;
- }
- /**
- * Returns a list of the tables in the database.
- *
- * @return array
- */
- public function listTables()
- {
- $this->_connect();
- $sql = "SELECT name FROM sysobjects WHERE type = 'U' ORDER BY name";
- return $this->fetchCol($sql);
- }
- /**
- * Returns the column descriptions for a table.
- *
- * The return value is an associative array keyed by the column name,
- * as returned by the RDBMS.
- *
- * The value of each array element is an associative array
- * with the following keys:
- *
- * SCHEMA_NAME => string; name of schema
- * TABLE_NAME => string;
- * COLUMN_NAME => string; column name
- * COLUMN_POSITION => number; ordinal position of column in table
- * DATA_TYPE => string; SQL datatype name of column
- * DEFAULT => string; default expression of column, null if none
- * NULLABLE => boolean; true if column can have nulls
- * LENGTH => number; length of CHAR/VARCHAR
- * SCALE => number; scale of NUMERIC/DECIMAL
- * PRECISION => number; precision of NUMERIC/DECIMAL
- * UNSIGNED => boolean; unsigned property of an integer type
- * PRIMARY => boolean; true if column is part of the primary key
- * PRIMARY_POSITION => integer; position of column in primary key
- * IDENTITY => integer; true if column is auto-generated with unique values
- *
- * @todo Discover integer unsigned property.
- *
- * @param string $tableName
- * @param string $schemaName OPTIONAL
- * @return array
- */
- public function describeTable($tableName, $schemaName = null)
- {
- /**
- * Discover metadata information about this table.
- */
- $sql = "exec sp_columns @table_name = " . $this->quoteIdentifier($tableName, true);
- $stmt = $this->query($sql);
- $result = $stmt->fetchAll(Db\Db::FETCH_NUM);
- // ZF-7698
- $stmt->closeCursor();
-
- if (count($result) == 0) {
- return array();
- }
- $owner = 1;
- $table_name = 2;
- $column_name = 3;
- $type_name = 5;
- $precision = 6;
- $length = 7;
- $scale = 8;
- $nullable = 10;
- $column_def = 12;
- $column_position = 16;
- /**
- * Discover primary key column(s) for this table.
- */
- $tableOwner = $result[0][$owner];
- $sql = "exec sp_pkeys @table_owner = " . $tableOwner
- . ", @table_name = " . $this->quoteIdentifier($tableName, true);
- $stmt = $this->query($sql);
- $primaryKeysResult = $stmt->fetchAll(Db\Db::FETCH_NUM);
- $primaryKeyColumn = array();
- // Per http://msdn.microsoft.com/en-us/library/ms189813.aspx,
- // results from sp_keys stored procedure are:
- // 0=TABLE_QUALIFIER 1=TABLE_OWNER 2=TABLE_NAME 3=COLUMN_NAME 4=KEY_SEQ 5=PK_NAME
- $pkey_column_name = 3;
- $pkey_key_seq = 4;
- foreach ($primaryKeysResult as $pkeysRow) {
- $primaryKeyColumn[$pkeysRow[$pkey_column_name]] = $pkeysRow[$pkey_key_seq];
- }
- $desc = array();
- $p = 1;
- foreach ($result as $key => $row) {
- $identity = false;
- $words = explode(' ', $row[$type_name], 2);
- if (isset($words[0])) {
- $type = $words[0];
- if (isset($words[1])) {
- $identity = (bool) preg_match('/identity/', $words[1]);
- }
- }
- $isPrimary = array_key_exists($row[$column_name], $primaryKeyColumn);
- if ($isPrimary) {
- $primaryPosition = $primaryKeyColumn[$row[$column_name]];
- } else {
- $primaryPosition = null;
- }
- $desc[$this->foldCase($row[$column_name])] = array(
- 'SCHEMA_NAME' => null, // @todo
- 'TABLE_NAME' => $this->foldCase($row[$table_name]),
- 'COLUMN_NAME' => $this->foldCase($row[$column_name]),
- 'COLUMN_POSITION' => (int) $row[$column_position],
- 'DATA_TYPE' => $type,
- 'DEFAULT' => $row[$column_def],
- 'NULLABLE' => (bool) $row[$nullable],
- 'LENGTH' => $row[$length],
- 'SCALE' => $row[$scale],
- 'PRECISION' => $row[$precision],
- 'UNSIGNED' => null, // @todo
- 'PRIMARY' => $isPrimary,
- 'PRIMARY_POSITION' => $primaryPosition,
- 'IDENTITY' => $identity,
- );
- }
- return $desc;
- }
- /**
- * Leave autocommit mode and begin a transaction.
- *
- * @return void
- * @throws \Zend\Db\Adapter\SqlsrvException
- */
- protected function _beginTransaction()
- {
- if (!sqlsrv_begin_transaction($this->_connection)) {
- throw new SqlsrvException(sqlsrv_errors());
- }
- }
- /**
- * Commit a transaction and return to autocommit mode.
- *
- * @return void
- * @throws \Zend\Db\Adapter\SqlsrvException
- */
- protected function _commit()
- {
- if (!sqlsrv_commit($this->_connection)) {
- throw new SqlsrvException(sqlsrv_errors());
- }
- }
- /**
- * Roll back a transaction and return to autocommit mode.
- *
- * @return void
- * @throws \Zend\Db\Adapter\SqlsrvException
- */
- protected function _rollBack()
- {
- if (!sqlsrv_rollback($this->_connection)) {
- throw new SqlsrvException(sqlsrv_errors());
- }
- }
- /**
- * Set the fetch mode.
- *
- * @todo Support FETCH_CLASS and FETCH_INTO.
- *
- * @param integer $mode A fetch mode.
- * @return void
- * @throws \Zend\Db\Adapter\SqlsrvException
- */
- public function setFetchMode($mode)
- {
- switch ($mode) {
- case Db\Db::FETCH_NUM: // seq array
- case Db\Db::FETCH_ASSOC: // assoc array
- case Db\Db::FETCH_BOTH: // seq+assoc array
- case Db\Db::FETCH_OBJ: // object
- $this->_fetchMode = $mode;
- break;
- case Db\Db::FETCH_BOUND: // bound to PHP variable
- throw new SqlsrvException('FETCH_BOUND is not supported yet');
- break;
- default:
- throw new SqlsrvException("Invalid fetch mode '$mode' specified");
- break;
- }
- }
- /**
- * Adds an adapter-specific LIMIT clause to the SELECT statement.
- *
- * @param string $sql
- * @param integer $count
- * @param integer $offset OPTIONAL
- * @return string
- * @throws \Zend\Db\Adapter\SqlsrvException
- */
- public function limit($sql, $count, $offset = 0)
- {
- $count = intval($count);
- if ($count <= 0) {
- throw new Exception("LIMIT argument count=$count is not valid");
- }
- $offset = intval($offset);
- if ($offset < 0) {
- throw new Exception("LIMIT argument offset=$offset is not valid");
- }
- if ($offset == 0) {
- $sql = preg_replace('/^SELECT\s/i', 'SELECT TOP ' . $count . ' ', $sql);
- } else {
- $orderby = stristr($sql, 'ORDER BY');
- if (!$orderby) {
- $over = 'ORDER BY (SELECT 0)';
- } else {
- $over = preg_replace('/\"[^,]*\".\"([^,]*)\"/i', '"inner_tbl"."$1"', $orderby);
- }
-
- // Remove ORDER BY clause from $sql
- $sql = preg_replace('/\s+ORDER BY(.*)/', '', $sql);
-
- // Add ORDER BY clause as an argument for ROW_NUMBER()
- $sql = "SELECT ROW_NUMBER() OVER ($over) AS \"ZEND_DB_ROWNUM\", * FROM ($sql) AS inner_tbl";
-
- $start = $offset + 1;
- $end = $offset + $count;
- $sql = "WITH outer_tbl AS ($sql) SELECT * FROM outer_tbl WHERE \"ZEND_DB_ROWNUM\" BETWEEN $start AND $end";
- }
- return $sql;
- }
- /**
- * Check if the adapter supports real SQL parameters.
- *
- * @param string $type 'positional' or 'named'
- * @return bool
- */
- public function supportsParameters($type)
- {
- if ($type == 'positional') {
- return true;
- }
- // if its 'named' or anything else
- return false;
- }
- /**
- * Retrieve server version in PHP style
- *
- * @return string
- */
- public function getServerVersion()
- {
- $this->_connect();
- $serverInfo = sqlsrv_server_info($this->_connection);
- if ($serverInfo !== false) {
- return $serverInfo['SQLServerVersion'];
- }
- return null;
- }
- }