Merge pull request #972 from narfbg/develop-db-mssql

Improve the MSSQL database driver

5 files changed, 49 insertions, 89 deletions

diff --git a/system/database/drivers/mssql/mssql_driver.php b/system/database/drivers/mssql/mssql_driver.php
index 697b2fd28..fff9f92e3 100644
--- a/system/database/drivers/mssql/mssql_driver.php
+++ b/system/database/drivers/mssql/mssql_driver.php
@@ -57,6 +57,16 @@ class CI_DB_mssql_driver extends CI_DB {
 	protected $_count_string = 'SELECT COUNT(*) AS ';
 	protected $_random_keyword = ' NEWID()';
 
+	public function __construct($params)
+	{
+		parent::__construct($params);
+
+		if ( ! empty($this->port))
+		{
+			$this->hostname .= (DIRECTORY_SEPARATOR === '\\' ? ',' : ':').$this->port;
+		}
+	}
+
 	/**
 	 * Non-persistent database connection
 	 *
@@ -64,11 +74,6 @@ class CI_DB_mssql_driver extends CI_DB {
 	 */
 	public function db_connect()
 	{
-		if ( ! empty($this->port))
-		{
-			$this->hostname .= ','.$this->port;
-		}
-
 		return @mssql_connect($this->hostname, $this->username, $this->password);
 	}
 
@@ -81,11 +86,6 @@ class CI_DB_mssql_driver extends CI_DB {
 	 */
 	public function db_pconnect()
 	{
-		if ( ! empty($this->port))
-		{
-			$this->hostname .= ','.$this->port;
-		}
-
 		return @mssql_pconnect($this->hostname, $this->username, $this->password);
 	}
 
@@ -121,7 +121,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 +137,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 +146,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 +160,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 +178,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 +214,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 +247,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 +336,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;
 	}
 
 	// --------------------------------------------------------------------
@@ -484,9 +468,7 @@ class CI_DB_mssql_driver extends CI_DB {
 	 */
 	protected function _limit($sql, $limit, $offset)
 	{
-		$i = $limit + $offset;
-
-		return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$i.' ', $sql);
+		return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.($limit + $offset).' ', $sql);
 	}
 
 	// --------------------------------------------------------------------
diff --git a/system/database/drivers/mssql/mssql_forge.php b/system/database/drivers/mssql/mssql_forge.php
index e6227e189..d2a30b20f 100644
--- a/system/database/drivers/mssql/mssql_forge.php
+++ b/system/database/drivers/mssql/mssql_forge.php
@@ -75,26 +75,20 @@ class CI_DB_mssql_forge extends CI_DB_forge {
 					$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->protect_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())
 	{
diff --git a/user_guide_src/source/changelog.rst b/user_guide_src/source/changelog.rst
index 801f0e481..66da20c40 100644
--- a/user_guide_src/source/changelog.rst
+++ b/user_guide_src/source/changelog.rst
@@ -106,6 +106,7 @@ Release Date: Not Released
    -  Added MSSQL, SQLSRV support for optimize_table() in :doc:`Database Utility <database/utilities>`.
    -  Improved CUBRID support for list_databases() in :doc:`Database Utility <database/utilities>` (until now only the currently used database was returned).
    -  Added unbuffered_row() method for getting a row without prefetching whole result (consume less memory).
+   -  Added port handling support for MSSQL on UNIX-based systems.
 
 -  Libraries