summaryrefslogtreecommitdiffstats
path: root/system/database/drivers/sqlsrv
diff options
context:
space:
mode:
Diffstat (limited to 'system/database/drivers/sqlsrv')
-rw-r--r--system/database/drivers/sqlsrv/sqlsrv_driver.php198
-rw-r--r--system/database/drivers/sqlsrv/sqlsrv_forge.php149
-rw-r--r--system/database/drivers/sqlsrv/sqlsrv_result.php24
-rw-r--r--system/database/drivers/sqlsrv/sqlsrv_utility.php49
4 files changed, 103 insertions, 317 deletions
diff --git a/system/database/drivers/sqlsrv/sqlsrv_driver.php b/system/database/drivers/sqlsrv/sqlsrv_driver.php
index 9f2f88699..655a9e90b 100644
--- a/system/database/drivers/sqlsrv/sqlsrv_driver.php
+++ b/system/database/drivers/sqlsrv/sqlsrv_driver.php
@@ -29,7 +29,7 @@
* SQLSRV Database Adapter Class
*
* Note: _DB is an extender class that the app controller
- * creates dynamically based on whether the active record
+ * creates dynamically based on whether the query builder
* class is being used or not.
*
* @package CodeIgniter
@@ -43,7 +43,7 @@ class CI_DB_sqlsrv_driver extends CI_DB {
public $dbdriver = 'sqlsrv';
// The character used for escaping
- protected $_escape_char = '';
+ protected $_escape_char = '"';
// clause and character used for LIKE escape sequences
protected $_like_escape_str = " ESCAPE '%s' ";
@@ -55,7 +55,10 @@ class CI_DB_sqlsrv_driver extends CI_DB {
* used for the count_all() and count_all_results() functions.
*/
protected $_count_string = 'SELECT COUNT(*) AS ';
- protected $_random_keyword = ' ASC'; // not currently supported
+ protected $_random_keyword = ' NEWID()';
+
+ // SQLSRV-specific properties
+ protected $_quoted_identifier = TRUE;
/**
* Non-persistent database connection
@@ -68,12 +71,12 @@ class CI_DB_sqlsrv_driver extends CI_DB {
$character_set = (0 === strcasecmp('utf8', $this->char_set)) ? 'UTF-8' : $this->char_set;
$connection = array(
- 'UID' => empty($this->username) ? '' : $this->username,
- 'PWD' => empty($this->password) ? '' : $this->password,
- 'Database' => $this->database,
- 'ConnectionPooling' => $pooling ? 1 : 0,
+ 'UID' => empty($this->username) ? '' : $this->username,
+ 'PWD' => empty($this->password) ? '' : $this->password,
+ 'Database' => $this->database,
+ 'ConnectionPooling' => $pooling ? 1 : 0,
'CharacterSet' => $character_set,
- 'ReturnDatesAsStrings' => 1
+ 'ReturnDatesAsStrings' => 1
);
// If the username and password are both empty, assume this is a
@@ -83,7 +86,15 @@ class CI_DB_sqlsrv_driver extends CI_DB {
unset($connection['UID'], $connection['PWD']);
}
- return sqlsrv_connect($this->hostname, $connection);
+ $conn_id = sqlsrv_connect($this->hostname, $connection);
+
+ // Determine how identifiers are escaped
+ $query = $this->query('SELECT CASE WHEN (@@OPTIONS | 256) = @@OPTIONS THEN 1 ELSE 0 END AS qi');
+ $query = $query->row_array();
+ $this->_quoted_identifier = empty($query) ? FALSE : (bool) $query->qi;
+ $this->_escape_char = ($this->_quoted_identifier) ? '"' : array('[', ']');
+
+ return $conn_id;
}
// --------------------------------------------------------------------
@@ -132,11 +143,9 @@ class CI_DB_sqlsrv_driver extends CI_DB {
*/
protected function _execute($sql)
{
- return sqlsrv_query($this->conn_id,
- $sql,
- NULL,
- array('Scrollable'=> SQLSRV_CURSOR_STATIC, 'SendStreamParamsAtExec' => TRUE)
- );
+ return (is_write_type($sql) && stripos($sql, 'INSERT') === FALSE)
+ ? sqlsrv_query($this->conn_id, $sql)
+ : sqlsrv_query($this->conn_id, $sql, NULL, array('Scrollable' => SQLSRV_CURSOR_STATIC));
}
// --------------------------------------------------------------------
@@ -148,13 +157,8 @@ class CI_DB_sqlsrv_driver extends CI_DB {
*/
public function trans_begin($test_mode = FALSE)
{
- if ( ! $this->trans_enabled)
- {
- return TRUE;
- }
-
// When transactions are nested we only begin/commit/rollback the outermost ones
- if ($this->_trans_depth > 0)
+ if ( ! $this->trans_enabled OR $this->_trans_depth > 0)
{
return TRUE;
}
@@ -162,7 +166,7 @@ class CI_DB_sqlsrv_driver extends CI_DB {
// Reset the transaction failure flag.
// If the $test_mode flag is set to TRUE transactions will be rolled back
// even if the queries produce a successful result.
- $this->_trans_failure = ($test_mode === TRUE) ? TRUE : FALSE;
+ $this->_trans_failure = ($test_mode === TRUE);
return sqlsrv_begin_transaction($this->conn_id);
}
@@ -176,13 +180,8 @@ class CI_DB_sqlsrv_driver extends CI_DB {
*/
public function trans_commit()
{
- if ( ! $this->trans_enabled)
- {
- return TRUE;
- }
-
// When transactions are nested we only begin/commit/rollback the outermost ones
- if ($this->_trans_depth > 0)
+ if ( ! $this->trans_enabled OR $this->_trans_depth > 0)
{
return TRUE;
}
@@ -199,13 +198,8 @@ class CI_DB_sqlsrv_driver extends CI_DB {
*/
public function trans_rollback()
{
- if ( ! $this->trans_enabled)
- {
- return TRUE;
- }
-
// When transactions are nested we only begin/commit/rollback the outermost ones
- if ($this->_trans_depth > 0)
+ if ( ! $this->trans_enabled OR $this->_trans_depth > 0)
{
return TRUE;
}
@@ -237,7 +231,7 @@ class CI_DB_sqlsrv_driver extends CI_DB {
*/
public function affected_rows()
{
- return @sqlrv_rows_affected($this->conn_id);
+ return sqlrv_rows_affected($this->result_id);
}
// --------------------------------------------------------------------
@@ -259,23 +253,6 @@ class CI_DB_sqlsrv_driver extends CI_DB {
// --------------------------------------------------------------------
/**
- * Parse major version
- *
- * Grabs the major version number from the
- * database server version string passed in.
- *
- * @param string $version
- * @return int major version number
- */
- protected function _parse_major_version($version)
- {
- preg_match('/([0-9]+)\.([0-9]+)\.([0-9]+)/', $version, $ver_info);
- return $ver_info[1]; // return the major version b/c that's all we're interested in.
- }
-
- // --------------------------------------------------------------------
-
- /**
* Database version number
*
* @return string
@@ -298,35 +275,6 @@ class CI_DB_sqlsrv_driver extends CI_DB {
// --------------------------------------------------------------------
/**
- * "Count All" query
- *
- * Generates a platform-specific query string that counts all records in
- * the specified database
- *
- * @param string
- * @return int
- */
- public function count_all($table = '')
- {
- if ($table == '')
- {
- return 0;
- }
-
- $query = $this->query("SELECT COUNT(*) AS numrows FROM " . $this->dbprefix . $table);
- if ($query->num_rows() == 0)
- {
- return 0;
- }
-
- $row = $query->row();
- $this->_reset_select();
- return (int) $row->numrows;
- }
-
- // --------------------------------------------------------------------
-
- /**
* List table query
*
* Generates a platform-specific query string so that the table names can be fetched
@@ -410,21 +358,6 @@ class CI_DB_sqlsrv_driver extends CI_DB {
// --------------------------------------------------------------------
/**
- * Escape the SQL Identifiers
- *
- * This function escapes column and table names
- *
- * @param string
- * @return string
- */
- public function _escape_identifiers($item)
- {
- return $item;
- }
-
- // --------------------------------------------------------------------
-
- /**
* From Tables
*
* This function implicitly groups FROM tables so there is no confusion
@@ -435,29 +368,7 @@ class CI_DB_sqlsrv_driver extends CI_DB {
*/
protected function _from_tables($tables)
{
- if ( ! is_array($tables))
- {
- $tables = array($tables);
- }
-
- return implode(', ', $tables);
- }
-
- // --------------------------------------------------------------------
-
- /**
- * Insert statement
- *
- * Generates a platform-specific insert string from the supplied data
- *
- * @param string the table name
- * @param array the insert keys
- * @param array the insert values
- * @return string
- */
- protected function _insert($table, $keys, $values)
- {
- return 'INSERT INTO '.$table.' ('.implode(', ', $keys).') VALUES ('.implode(', ', $values).')';
+ return is_array($tables) ? implode(', ', $tables) : $tables;
}
// --------------------------------------------------------------------
@@ -470,18 +381,26 @@ class CI_DB_sqlsrv_driver extends CI_DB {
* @param string the table name
* @param array the update data
* @param array the where clause
- * @param array the orderby clause
- * @param array the limit clause
+ * @param array the orderby clause (ignored)
+ * @param array the limit clause (ignored)
+ * @param array the like clause
* @return string
*/
- protected function _update($table, $values, $where)
+ protected function _update($table, $values, $where, $orderby = array(), $limit = FALSE, $like = array())
{
- foreach($values as $key => $val)
+ foreach ($values as $key => $val)
+ {
+ $valstr[] = $key.' = '.$val;
+ }
+
+ $where = empty($where) ? '' : ' WHERE '.implode(' ', $where);
+
+ if ( ! empty($like))
{
- $valstr[] = $key." = ".$val;
+ $where .= ($where === '' ? ' WHERE ' : ' AND ').implode(' ', $like);
}
- return 'UPDATE '.$table.' SET '.implode(', ', $valstr).' WHERE '.implode(' ', $where);
+ return 'UPDATE '.$table.' SET '.implode(', ', $valstr).$where;
}
// --------------------------------------------------------------------
@@ -490,15 +409,16 @@ class CI_DB_sqlsrv_driver extends CI_DB {
* Truncate statement
*
* Generates a platform-specific truncate string from the supplied data
- * If the database does not support the truncate() command
- * This function maps to "DELETE FROM table"
+ *
+ * If the database does not support the truncate() command,
+ * then this method maps to 'DELETE FROM table'
*
* @param string the table name
* @return string
*/
protected function _truncate($table)
{
- return "TRUNCATE ".$table;
+ return 'TRUNCATE TABLE '.$table;
}
// --------------------------------------------------------------------
@@ -510,12 +430,22 @@ class CI_DB_sqlsrv_driver extends CI_DB {
*
* @param string the table name
* @param array the where clause
+ * @param array the like clause
* @param string the limit clause
* @return string
*/
- protected function _delete($table, $where)
+ protected function _delete($table, $where = array(), $like = array(), $limit = FALSE)
{
- return 'DELETE FROM '.$table.' WHERE '.implode(' ', $where);
+ $conditions = array();
+
+ empty($where) OR $conditions[] = implode(' ', $where);
+ empty($like) OR $conditions[] = implode(' ', $like);
+
+ $conditions = (count($conditions) > 0) ? ' WHERE '.implode(' AND ', $conditions) : '';
+
+ return ($limit)
+ ? 'WITH ci_delete AS (SELECT TOP '.$limit.' * FROM '.$table.$conditions.') DELETE FROM ci_delete'
+ : 'DELETE FROM '.$table.$conditions;
}
// --------------------------------------------------------------------
@@ -532,7 +462,10 @@ class CI_DB_sqlsrv_driver extends CI_DB {
*/
protected function _limit($sql, $limit, $offset)
{
- return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.($limit + $offset).' ', $sql);
+ // As of SQL Server 2012 (11.0.*) OFFSET is supported
+ return version_compare($this->version(), '11', '>=')
+ ? $sql.' OFFSET '.(int) $offset.' ROWS FETCH NEXT '.(int) $limit.' ROWS ONLY'
+ : preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.($limit + $offset).' ', $sql);
}
// --------------------------------------------------------------------
@@ -540,12 +473,11 @@ class CI_DB_sqlsrv_driver extends CI_DB {
/**
* Close DB Connection
*
- * @param resource
* @return void
*/
- protected function _close($conn_id)
+ protected function _close()
{
- @sqlsrv_close($conn_id);
+ @sqlsrv_close($this->conn_id);
}
}
diff --git a/system/database/drivers/sqlsrv/sqlsrv_forge.php b/system/database/drivers/sqlsrv/sqlsrv_forge.php
index 0dc7b5242..e6f7e1ac1 100644
--- a/system/database/drivers/sqlsrv/sqlsrv_forge.php
+++ b/system/database/drivers/sqlsrv/sqlsrv_forge.php
@@ -34,43 +34,7 @@
*/
class CI_DB_sqlsrv_forge extends CI_DB_forge {
- /**
- * Create database
- *
- * @param string the database name
- * @return string
- */
- public function _create_database($name)
- {
- return "CREATE DATABASE ".$name;
- }
-
- // --------------------------------------------------------------------
-
- /**
- * Drop database
- *
- * @param string the database name
- * @return bool
- */
- public function _drop_database($name)
- {
- return "DROP DATABASE ".$name;
- }
-
- // --------------------------------------------------------------------
-
- /**
- * Drop Table
- *
- * @return bool
- */
- public function _drop_table($table)
- {
- return "DROP TABLE ".$this->db->_escape_identifiers($table);
- }
-
- // --------------------------------------------------------------------
+ protected $_drop_table = 'DROP TABLE %s';
/**
* Create Table
@@ -80,20 +44,17 @@ class CI_DB_sqlsrv_forge extends CI_DB_forge {
* @param mixed primary key(s)
* @param mixed key(s)
* @param bool should 'IF NOT EXISTS' be added to the SQL
- * @return bool
+ * @return string
*/
- public function _create_table($table, $fields, $primary_keys, $keys, $if_not_exists)
+ protected function _create_table($table, $fields, $primary_keys, $keys, $if_not_exists)
{
- $sql = 'CREATE TABLE ';
+ $sql = ($if_not_exists === TRUE)
+ ? "IF NOT EXISTS (SELECT * FROM sysobjects WHERE ID = object_id(N'".$table."') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)\n"
+ : '';
- if ($if_not_exists === TRUE)
- {
- $sql .= 'IF NOT EXISTS ';
- }
+ $sql .= 'CREATE TABLE '.$this->db->escape_identifiers($table).' (';
- $sql .= $this->db->_escape_identifiers($table)." (";
$current_field_count = 0;
-
foreach ($fields as $field => $attributes)
{
// Numeric field names aren't allowed in databases, so if the key is
@@ -101,41 +62,33 @@ class CI_DB_sqlsrv_forge extends CI_DB_forge {
// entered the field information, so we'll simply add it to the list
if (is_numeric($field))
{
- $sql .= "\n\t$attributes";
+ $sql .= "\n\t".$attributes;
}
else
{
$attributes = array_change_key_case($attributes, CASE_UPPER);
- $sql .= "\n\t".$this->db->protect_identifiers($field);
+ $sql .= "\n\t".$this->db->escape_identifiers($field).' '.$attributes['TYPE'];
- $sql .= ' '.$attributes['TYPE'];
-
- if (array_key_exists('CONSTRAINT', $attributes))
+ if (stripos($attributes['TYPE'], 'INT') === FALSE && ! empty($attributes['CONSTRAINT']))
{
$sql .= '('.$attributes['CONSTRAINT'].')';
}
- if (array_key_exists('UNSIGNED', $attributes) && $attributes['UNSIGNED'] === TRUE)
+ if ( ! empty($attributes['UNSIGNED']) && $attributes['UNSIGNED'] === TRUE)
{
$sql .= ' UNSIGNED';
}
- if (array_key_exists('DEFAULT', $attributes))
+ if (isset($attributes['DEFAULT']))
{
- $sql .= ' DEFAULT \''.$attributes['DEFAULT'].'\'';
+ $sql .= " DEFAULT '".$attributes['DEFAULT']."'";
}
- if (array_key_exists('NULL', $attributes) && $attributes['NULL'] === TRUE)
- {
- $sql .= ' NULL';
- }
- else
- {
- $sql .= ' NOT NULL';
- }
+ $sql .= ( ! empty($attributes['NULL']) && $attribues['NULL'] === TRUE)
+ ? ' NULL' : ' NOT NULL';
- if (array_key_exists('AUTO_INCREMENT', $attributes) && $attributes['AUTO_INCREMENT'] === TRUE)
+ if ( ! empty($attributes['AUTO_INCREMENT']) && $attributes['AUTO_INCREMENT'] === TRUE)
{
$sql .= ' AUTO_INCREMENT';
}
@@ -150,30 +103,22 @@ class CI_DB_sqlsrv_forge extends CI_DB_forge {
if (count($primary_keys) > 0)
{
- $primary_keys = $this->db->protect_identifiers($primary_keys);
- $sql .= ",\n\tPRIMARY KEY (" . implode(', ', $primary_keys) . ")";
+ $sql .= ",\n\tPRIMARY KEY (".implode(', ', $this->db->escape_identifiers($primary_keys)).')';
}
if (is_array($keys) && count($keys) > 0)
{
foreach ($keys as $key)
{
- if (is_array($key))
- {
- $key = $this->db->protect_identifiers($key);
- }
- else
- {
- $key = array($this->db->protect_identifiers($key));
- }
+ $key = is_array($key)
+ ? $this->db->escape_identifiers($key)
+ : array($this->escape_identifiers($key));
- $sql .= ",\n\tFOREIGN KEY (" . implode(', ', $key) . ")";
+ $sql .= ",\n\tFOREIGN KEY (".implode(', ', $key).')';
}
}
- $sql .= "\n)";
-
- return $sql;
+ return $sql."\n)";
}
// --------------------------------------------------------------------
@@ -193,56 +138,20 @@ class CI_DB_sqlsrv_forge extends CI_DB_forge {
* @param string the field after which we should add the new field
* @return string
*/
- public function _alter_table($alter_type, $table, $column_name, $column_definition = '', $default_value = '', $null = '', $after_field = '')
+ protected function _alter_table($alter_type, $table, $column_name, $column_definition = '', $default_value = '', $null = '', $after_field = '')
{
- $sql = 'ALTER TABLE '.$this->db->protect_identifiers($table).' '.$alter_type.' '.$this->db->protect_identifiers($column_name);
+ $sql = 'ALTER TABLE '.$this->db->escape_identifiers($table).' '.$alter_type.' '.$this->db->escape_identifiers($column_name);
// DROP has everything it needs now.
- if ($alter_type == 'DROP')
+ if ($alter_type === 'DROP')
{
return $sql;
}
- $sql .= " $column_definition";
-
- if ($default_value != '')
- {
- $sql .= " DEFAULT \"$default_value\"";
- }
-
- if ($null === NULL)
- {
- $sql .= ' NULL';
- }
- else
- {
- $sql .= ' NOT NULL';
- }
-
- if ($after_field != '')
- {
- return $sql.' AFTER '.$this->db->protect_identifiers($after_field);
- }
-
- return $sql;
-
- }
-
- // --------------------------------------------------------------------
-
- /**
- * Rename a table
- *
- * Generates a platform-specific query so that a table can be renamed
- *
- * @param string the old table name
- * @param string the new table name
- * @return string
- */
- public function _rename_table($table_name, $new_table_name)
- {
- // I think this syntax will work, but can find little documentation on renaming tables in MSSQL
- return 'ALTER TABLE '.$this->db->protect_identifiers($table_name).' RENAME TO '.$this->db->protect_identifiers($new_table_name);
+ return $sql.' '.$column_definition
+ .($default_value != '' ? ' DEFAULT "'.$default_value.'"' : '')
+ .($null === NULL ? ' NULL' : ' NOT NULL')
+ .($after_field != '' ? ' AFTER '.$this->db->escape_identifiers($after_field) : '');
}
}
diff --git a/system/database/drivers/sqlsrv/sqlsrv_result.php b/system/database/drivers/sqlsrv/sqlsrv_result.php
index 10d790e4b..f802383d2 100644
--- a/system/database/drivers/sqlsrv/sqlsrv_result.php
+++ b/system/database/drivers/sqlsrv/sqlsrv_result.php
@@ -92,12 +92,12 @@ class CI_DB_sqlsrv_result extends CI_DB_result {
$retval = array();
foreach (sqlsrv_field_metadata($this->result_id) as $offset => $field)
{
- $F = new stdClass();
- $F->name = $field['Name'];
- $F->type = $field['Type'];
+ $F = new stdClass();
+ $F->name = $field['Name'];
+ $F->type = $field['Type'];
$F->max_length = $field['Size'];
$F->primary_key = 0;
- $F->default = '';
+ $F->default = '';
$retval[] = $F;
}
@@ -124,22 +124,6 @@ class CI_DB_sqlsrv_result extends CI_DB_result {
// --------------------------------------------------------------------
/**
- * Data Seek
- *
- * Moves the internal pointer to the desired offset. We call
- * this internally before fetching results to make sure the
- * result set starts at zero
- *
- * @return void
- */
- protected function _data_seek($n = 0)
- {
- // Not implemented
- }
-
- // --------------------------------------------------------------------
-
- /**
* Result - associative array
*
* Returns the result set as an array
diff --git a/system/database/drivers/sqlsrv/sqlsrv_utility.php b/system/database/drivers/sqlsrv/sqlsrv_utility.php
index 5830c62de..5a71b1628 100644
--- a/system/database/drivers/sqlsrv/sqlsrv_utility.php
+++ b/system/database/drivers/sqlsrv/sqlsrv_utility.php
@@ -34,55 +34,16 @@
*/
class CI_DB_sqlsrv_utility extends CI_DB_utility {
- /**
- * List databases
- *
- * @return string
- */
- public function _list_databases()
- {
- return "EXEC sp_helpdb"; // Can also be: EXEC sp_databases
- }
-
- // --------------------------------------------------------------------
+ protected $_list_databases = 'EXEC sp_helpdb'; // Can also be: EXEC sp_databases
+ protected $_optimize_table = 'ALTER INDEX all ON %s REORGANIZE';
/**
- * Optimize table query
- *
- * Generates a platform-specific query so that a table can be optimized
- *
- * @param string the table name
- * @return bool
- */
- public function _optimize_table($table)
- {
- return FALSE; // Is this supported in MS SQL?
- }
-
- // --------------------------------------------------------------------
-
- /**
- * Repair table query
- *
- * Generates a platform-specific query so that a table can be repaired
- *
- * @param string the table name
- * @return bool
- */
- public function _repair_table($table)
- {
- return FALSE; // Is this supported in MS SQL?
- }
-
- // --------------------------------------------------------------------
-
- /**
- * MSSQL Export
+ * SQLSRV Export
*
* @param array Preferences
- * @return mixed
+ * @return bool
*/
- public function _backup($params = array())
+ protected function _backup($params = array())
{
// Currently unsupported
return $this->db->display_error('db_unsuported_feature');
generated by cgit v1.2.3-24-g4f1b (git 2.32.0) at 2024-11-26 22:19:49 +0100