summaryrefslogtreecommitdiffstats
path: root/system/database/drivers/mssql
diff options
context:
space:
mode:
Diffstat (limited to 'system/database/drivers/mssql')
-rw-r--r--system/database/drivers/mssql/mssql_driver.php145
-rw-r--r--system/database/drivers/mssql/mssql_forge.php55
-rw-r--r--system/database/drivers/mssql/mssql_result.php8
-rw-r--r--system/database/drivers/mssql/mssql_utility.php2
4 files changed, 114 insertions, 96 deletions
diff --git a/system/database/drivers/mssql/mssql_driver.php b/system/database/drivers/mssql/mssql_driver.php
index 697b2fd28..47dc55844 100644
--- a/system/database/drivers/mssql/mssql_driver.php
+++ b/system/database/drivers/mssql/mssql_driver.php
@@ -43,7 +43,7 @@ class CI_DB_mssql_driver extends CI_DB {
public $dbdriver = 'mssql';
// 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' ";
@@ -57,19 +57,37 @@ class CI_DB_mssql_driver extends CI_DB {
protected $_count_string = 'SELECT COUNT(*) AS ';
protected $_random_keyword = ' NEWID()';
- /**
- * Non-persistent database connection
+ // MSSQL-specific properties
+ protected $_quoted_identifier = TRUE;
+
+ /*
+ * Constructor
*
- * @return resource
+ * Appends the port number to the hostname, if needed.
+ *
+ * @param array
+ * @return void
*/
- public function db_connect()
+ public function __construct($params)
{
+ parent::__construct($params);
+
if ( ! empty($this->port))
{
- $this->hostname .= ','.$this->port;
+ $this->hostname .= (DIRECTORY_SEPARATOR === '\\' ? ',' : ':').$this->port;
}
+ }
+
+ // --------------------------------------------------------------------
- return @mssql_connect($this->hostname, $this->username, $this->password);
+ /**
+ * Non-persistent database connection
+ *
+ * @return resource
+ */
+ public function db_connect()
+ {
+ return $this->_mssql_connect();
}
// --------------------------------------------------------------------
@@ -81,12 +99,35 @@ class CI_DB_mssql_driver extends CI_DB {
*/
public function db_pconnect()
{
- if ( ! empty($this->port))
+ return $this->_mssql_connect(TRUE);
+ }
+
+ // --------------------------------------------------------------------
+
+ /*
+ * MSSQL Connect
+ *
+ * @param bool
+ * @return resource
+ */
+ protected function _mssql_connect($persistent = FALSE)
+ {
+ $conn_id = ($persistent)
+ ? @mssql_pconnect($this->hostname, $this->username, $this->password)
+ : @mssql_connect($this->hostname, $this->username, $this->password);
+
+ if ( ! $conn_id)
{
- $this->hostname .= ','.$this->port;
+ return FALSE;
}
- return @mssql_pconnect($this->hostname, $this->username, $this->password);
+ // 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;
}
// --------------------------------------------------------------------
@@ -106,7 +147,7 @@ class CI_DB_mssql_driver extends CI_DB {
// Note: The brackets are required in the event that the DB name
// contains reserved characters
- if (@mssql_select_db('['.$database.']', $this->conn_id))
+ if (@mssql_select_db($this->escape_identifiers($database), $this->conn_id))
{
$this->database = $database;
return TRUE;
@@ -121,7 +162,7 @@ class CI_DB_mssql_driver extends CI_DB {
* Execute the query
*
* @param string an SQL query
- * @return resource
+ * @return mixed resource if rows are returned, bool otherwise
*/
protected function _execute($sql)
{
@@ -137,13 +178,8 @@ class CI_DB_mssql_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;
}
@@ -151,10 +187,9 @@ class CI_DB_mssql_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);
- $this->simple_query('BEGIN TRAN');
- return TRUE;
+ return $this->simple_query('BEGIN TRAN');
}
// --------------------------------------------------------------------
@@ -166,19 +201,13 @@ class CI_DB_mssql_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;
}
- $this->simple_query('COMMIT TRAN');
- return TRUE;
+ return $this->simple_query('COMMIT TRAN');
}
// --------------------------------------------------------------------
@@ -190,19 +219,13 @@ class CI_DB_mssql_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;
}
- $this->simple_query('ROLLBACK TRAN');
- return TRUE;
+ return $this->simple_query('ROLLBACK TRAN');
}
// --------------------------------------------------------------------
@@ -232,7 +255,7 @@ class CI_DB_mssql_driver extends CI_DB {
// escape LIKE condition wildcards
if ($like === TRUE)
{
- $str = str_replace(
+ return str_replace(
array($this->_like_escape_chr, '%', '_'),
array($this->_like_escape_chr.$this->_like_escape_chr, $this->_like_escape_chr.'%', $this->_like_escape_chr.'_'),
$str
@@ -265,11 +288,13 @@ class CI_DB_mssql_driver extends CI_DB {
*/
public function insert_id()
{
- $ver = self::_parse_major_version($this->version());
- $sql = ($ver >= 8 ? "SELECT SCOPE_IDENTITY() AS last_id" : "SELECT @@IDENTITY AS last_id");
- $query = $this->query($sql);
- $row = $query->row();
- return $row->last_id;
+ $query = (self::_parse_major_version($this->version()) > 7)
+ ? 'SELECT SCOPE_IDENTITY() AS last_id'
+ : 'SELECT @@IDENTITY AS last_id';
+
+ $query = $this->query($query);
+ $query = $query->row();
+ return $query->last_id;
}
// --------------------------------------------------------------------
@@ -352,7 +377,7 @@ class CI_DB_mssql_driver extends CI_DB {
*/
protected function _field_data($table)
{
- return "SELECT TOP 1 * FROM ".$table;
+ return 'SELECT TOP 1 * FROM '.$table;
}
// --------------------------------------------------------------------
@@ -385,12 +410,7 @@ class CI_DB_mssql_driver extends CI_DB {
*/
protected function _from_tables($tables)
{
- if ( ! is_array($tables))
- {
- $tables = array($tables);
- }
-
- return implode(', ', $tables);
+ return is_array($tables) ? implode(', ', $tables) : $tables;
}
// --------------------------------------------------------------------
@@ -484,9 +504,30 @@ class CI_DB_mssql_driver extends CI_DB {
*/
protected function _limit($sql, $limit, $offset)
{
- $i = $limit + $offset;
+ // As of SQL Server 2012 (11.0.*) OFFSET is supported
+ if (version_compare($this->version(), '11', '>='))
+ {
+ return $sql.' OFFSET '.(int) $offset.' ROWS FETCH NEXT '.(int) $limit.' ROWS ONLY';
+ }
+
+ $limit = $offset + $limit;
+
+ // As of SQL Server 2005 (9.0.*) ROW_NUMBER() is supported,
+ // however an ORDER BY clause is required for it to work
+ if (version_compare($this->version(), '9', '>=') && $offset && ! empty($this->qb_orderby))
+ {
+ $orderby = 'ORDER BY '.implode(', ', $this->qb_orderby);
+
+ // We have to strip the ORDER BY clause
+ $sql = trim(substr($sql, 0, strrpos($sql, 'ORDER BY '.$orderby)));
+
+ return 'SELECT '.(count($this->qb_select) === 0 ? '*' : implode(', ', $this->qb_select))." FROM (\n"
+ .preg_replace('/^(SELECT( DISTINCT)?)/i', '\\1 ROW_NUMBER() OVER('.$orderby.') AS '.$this->escape_identifiers('CI_rownum').', ', $sql)
+ ."\n) ".$this->escape_identifiers('CI_subquery')
+ ."\nWHERE ".$this->escape_identifiers('CI_rownum').' BETWEEN '.((int) $offset + 1).' AND '.$limit;
+ }
- return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$i.' ', $sql);
+ return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$limit.' ', $sql);
}
// --------------------------------------------------------------------
diff --git a/system/database/drivers/mssql/mssql_forge.php b/system/database/drivers/mssql/mssql_forge.php
index 3708c2233..3a3528f7b 100644
--- a/system/database/drivers/mssql/mssql_forge.php
+++ b/system/database/drivers/mssql/mssql_forge.php
@@ -70,31 +70,25 @@ class CI_DB_mssql_forge extends CI_DB_forge {
$sql .= "\n\t".$this->db->escape_identifiers($field).' '.$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']) && $attributes['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';
}
@@ -109,22 +103,16 @@ class CI_DB_mssql_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->db->escape_identifiers($key));
$sql .= ",\n\tFOREIGN KEY (".implode(', ', $key).')';
}
@@ -152,7 +140,7 @@ class CI_DB_mssql_forge extends CI_DB_forge {
*/
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')
@@ -160,21 +148,10 @@ class CI_DB_mssql_forge extends CI_DB_forge {
return $sql;
}
- $sql .= " ".$column_definition;
-
- if ($default_value !== '')
- {
- $sql .= " DEFAULT '".$default_value."'";
- }
-
- $sql .= ($null === NULL) ? ' NULL' : ' NOT NULL';
-
- if ($after_field !== '')
- {
- return $sql.' AFTER '.$this->db->protect_identifiers($after_field);
- }
-
- return $sql;
+ 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/mssql/mssql_result.php b/system/database/drivers/mssql/mssql_result.php
index 4cc87f4cf..5929306af 100644
--- a/system/database/drivers/mssql/mssql_result.php
+++ b/system/database/drivers/mssql/mssql_result.php
@@ -92,12 +92,12 @@ class CI_DB_mssql_result extends CI_DB_result {
$retval = array();
while ($field = mssql_fetch_field($this->result_id))
{
- $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->max_length;
$F->primary_key = 0;
- $F->default = '';
+ $F->default = '';
$retval[] = $F;
}
diff --git a/system/database/drivers/mssql/mssql_utility.php b/system/database/drivers/mssql/mssql_utility.php
index 6d47618ce..69fcec5f6 100644
--- a/system/database/drivers/mssql/mssql_utility.php
+++ b/system/database/drivers/mssql/mssql_utility.php
@@ -41,7 +41,7 @@ class CI_DB_mssql_utility extends CI_DB_utility {
* MSSQL Export
*
* @param array Preferences
- * @return mixed
+ * @return bool
*/
protected function _backup($params = array())
{