Support for Microsoft's Native SQL Server Driver for PHP - Patch by Chris Pucci at Microsoft [Bug #57055]

If you are using SQL Server, please try to test this new dbal so we can safely include it in 3.0.8. If you want to try it on a current phpBB version you can apply the latest version of the patch to your board which you can find attached to the bug tracker ticket (look in the comments for the latest version, the one in the ticket itself is outdated): http://www.phpbb.com/bugs/phpbb3/ticket.php?ticket_id=57055 git-svn-id: file:///svn/phpbb/branches/phpBB-3_0_0@10489 89ea8834-ac86-4346-8a33-228a782c2dd0
author: Nils Adermann <naderman@naderman.de> 2010-02-11 00:02:51 +0000
committer: Nils Adermann <naderman@naderman.de> 2010-02-11 00:02:51 +0000
commit: 1802b9ff9286a7fc24493e71b3432816cbdbfcd8 (patch)
tree: 886dc576cb5a2be1d700372732446291479d483a /phpBB
parent: 6c321c53a1ebd495518738157dc3a2bf8c0701e1 (diff)
download: forums-1802b9ff9286a7fc24493e71b3432816cbdbfcd8.tar
forums-1802b9ff9286a7fc24493e71b3432816cbdbfcd8.tar.gz
forums-1802b9ff9286a7fc24493e71b3432816cbdbfcd8.tar.bz2
forums-1802b9ff9286a7fc24493e71b3432816cbdbfcd8.tar.xz
forums-1802b9ff9286a7fc24493e71b3432816cbdbfcd8.zip
14 files changed, 829 insertions, 15 deletions
diff --git a/phpBB/docs/CHANGELOG.html b/phpBB/docs/CHANGELOG.html
index 876b8b1a61..6121d8ad1a 100644
--- a/phpBB/docs/CHANGELOG.html
+++ b/phpBB/docs/CHANGELOG.html
@@ -96,6 +96,7 @@
 		<li>[Fix] Honor minimum and maximum password length in generated passwords as much as we can. (Bug #13181)</li>
 		<li>[Fix] No longer return the character O in generated random strings and passwords. (Bug #57345)</li>
 		<li>[Fix] Some XHTML corrections in subsilver2. (Bug #57505)</li>
+		<li>[Feature] Support for Microsoft's Native SQL Server Driver for PHP (Bug #57055 - Patch by Chris Pucci at Microsoft)</li>
 	</ul>
 
 	<a name="v306"></a><h3>1.ii. Changes since 3.0.6</h3>
diff --git a/phpBB/includes/acp/acp_database.php b/phpBB/includes/acp/acp_database.php
index 7b734da2af..abfad2b90b 100644
--- a/phpBB/includes/acp/acp_database.php
+++ b/phpBB/includes/acp/acp_database.php
@@ -109,6 +109,7 @@ class acp_database
 
 							case 'mssql':
 							case 'mssql_odbc':
+							case 'mssqlnative':
 								$extractor = new mssql_extractor($download, $store, $format, $filename, $time);
 							break;
 
@@ -138,6 +139,7 @@ class acp_database
 
 									case 'mssql':
 									case 'mssql_odbc':
+									case 'mssqlnative':
 										$extractor->flush('TRUNCATE TABLE ' . $table_name . "GO\n");
 									break;
 
@@ -1509,6 +1511,10 @@ class mssql_extractor extends base_extractor
 		{
 			$this->write_data_mssql($table_name);
 		}
+		else if($db->sql_layer === 'mssqlnative')
+		{
+			$this->write_data_mssqlnative($table_name);
+		}
 		else
 		{
 			$this->write_data_odbc($table_name);
@@ -1608,7 +1614,103 @@ class mssql_extractor extends base_extractor
 		}
 		$this->flush($sql_data);
 	}
+	
+	function write_data_mssqlnative($table_name)
+	{
+		global $db;
+		$ary_type = $ary_name = $meta_array = array();
+		$ident_set = false;
+		$sql_data = '';
+
+		// Grab all of the data from current table.
+		$sql = "SELECT * FROM $table_name";
+		$result = $db->sql_query($sql);
+
+		$retrieved_data = $db->mssqlnative_num_rows($result); 
+
+		$meta_array = sqlsrv_field_metadata($result);	
+		$i_num_fields = sqlsrv_num_fields($result);
+	
+		for ($i = 0; $i < $i_num_fields; $i++)
+		{
+			$info = $db->mssqlnative_fieldInfo($table_name, $meta_array[$i]['Name']);
+			$ary_type[$i] = $info->type();
+			$ary_name[$i] = $info->name();
+		}
+		
+		if ($retrieved_data)
+		{
+			$sql = "SELECT 1 as has_identity
+				FROM INFORMATION_SCHEMA.COLUMNS
+				WHERE COLUMNPROPERTY(object_id('$table_name'), COLUMN_NAME, 'IsIdentity') = 1";
+			$result2 = $db->sql_query($sql);
+			$row2 = $db->sql_fetchrow($result2);
+			
+			if (!empty($row2['has_identity']))
+			{
+				$sql_data .= "\nSET IDENTITY_INSERT $table_name ON\nGO\n";
+				$ident_set = true;
+			}
+			$db->sql_freeresult($result2);
+		}
+
+		while ($row = $db->sql_fetchrow($result))
+		{
+			$schema_vals = $schema_fields = array();
 
+			// Build the SQL statement to recreate the data.
+			for ($i = 0; $i < $i_num_fields; $i++)
+			{
+				$str_val = $row[$ary_name[$i]];
+
+				if (preg_match('#char|text|bool|varbinary#i', $ary_type[$i]))
+				{
+					$str_quote = '';
+					$str_empty = "''";
+					$str_val = sanitize_data_mssql(str_replace("'", "''", $str_val));
+				}
+				else if (preg_match('#date|timestamp#i', $ary_type[$i]))
+				{
+					if (empty($str_val))
+					{
+						$str_quote = '';
+					}
+					else
+					{
+						$str_quote = "'";
+					}
+				}
+				else
+				{
+					$str_quote = '';
+					$str_empty = 'NULL';
+				}
+
+				if (empty($str_val) && $str_val !== '0' && !(is_int($str_val) || is_float($str_val)))
+				{
+					$str_val = $str_empty;
+				}
+
+				$schema_vals[$i] = $str_quote . $str_val . $str_quote;
+				$schema_fields[$i] = $ary_name[$i];
+			}
+
+			// Take the ordered fields and their associated data and build it
+			// into a valid sql statement to recreate that field in the data.
+			$sql_data .= "INSERT INTO $table_name (" . implode(', ', $schema_fields) . ') VALUES (' . implode(', ', $schema_vals) . ");\nGO\n";
+
+			$this->flush($sql_data);
+			$sql_data = '';
+		}
+		$db->sql_freeresult($result);
+
+		if ($retrieved_data && $ident_set)
+		{
+			$sql_data .= "\nSET IDENTITY_INSERT $table_name OFF\nGO\n";
+		}
+		$this->flush($sql_data);
+	}	
+	
 	function write_data_odbc($table_name)
 	{
 		global $db;
diff --git a/phpBB/includes/cache.php b/phpBB/includes/cache.php
index c760c20d52..6b1e078ca4 100644
--- a/phpBB/includes/cache.php
+++ b/phpBB/includes/cache.php
@@ -299,6 +299,7 @@ class cache extends acm
 			{
 				case 'mssql':
 				case 'mssql_odbc':
+				case 'mssqlnative':
 					$sql = 'SELECT user_id, bot_agent, bot_ip
 						FROM ' . BOTS_TABLE . '
 						WHERE bot_active = 1
diff --git a/phpBB/includes/db/db_tools.php b/phpBB/includes/db/db_tools.php
index 7123c83e51..819ef69c96 100644
--- a/phpBB/includes/db/db_tools.php
+++ b/phpBB/includes/db/db_tools.php
@@ -160,6 +160,36 @@ class phpbb_db_tools
 			'VARBINARY'	=> '[varchar] (255)',
 		),
 
+		'mssqlnative'	=> array(
+			'INT:'		=> '[int]',
+			'BINT'		=> '[float]',
+			'UINT'		=> '[int]',
+			'UINT:'		=> '[int]',
+			'TINT:'		=> '[int]',
+			'USINT'		=> '[int]',
+			'BOOL'		=> '[int]',
+			'VCHAR'		=> '[varchar] (255)',
+			'VCHAR:'	=> '[varchar] (%d)',
+			'CHAR:'		=> '[char] (%d)',
+			'XSTEXT'	=> '[varchar] (1000)',
+			'STEXT'		=> '[varchar] (3000)',
+			'TEXT'		=> '[varchar] (8000)',
+			'MTEXT'		=> '[text]',
+			'XSTEXT_UNI'=> '[varchar] (100)',
+			'STEXT_UNI'	=> '[varchar] (255)',
+			'TEXT_UNI'	=> '[varchar] (4000)',
+			'MTEXT_UNI'	=> '[text]',
+			'TIMESTAMP'	=> '[int]',
+			'DECIMAL'	=> '[float]',
+			'DECIMAL:'	=> '[float]',
+			'PDECIMAL'	=> '[float]',
+			'PDECIMAL:'	=> '[float]',
+			'VCHAR_UNI'	=> '[varchar] (255)',
+			'VCHAR_UNI:'=> '[varchar] (%d)',
+			'VCHAR_CI'	=> '[varchar] (255)',
+			'VARBINARY'	=> '[varchar] (255)',
+		),
+
 		'oracle'	=> array(
 			'INT:'		=> 'number(%d)',
 			'BINT'		=> 'number(20)',
@@ -261,7 +291,7 @@ class phpbb_db_tools
 	* A list of supported DBMS. We change this class to support more DBMS, the DBMS itself only need to follow some rules.
 	* @var array
 	*/
-	var $supported_dbms = array('firebird', 'mssql', 'mysql_40', 'mysql_41', 'oracle', 'postgres', 'sqlite');
+	var $supported_dbms = array('firebird', 'mssql', 'mssqlnative', 'mysql_40', 'mysql_41', 'oracle', 'postgres', 'sqlite');
 
 	/**
 	* This is set to true if user only wants to return the 'to-be-executed' SQL statement(s) (as an array).
@@ -307,6 +337,10 @@ class phpbb_db_tools
 				$this->sql_layer = 'mssql';
 			break;
 
+			case 'mssqlnative':
+				$this->sql_layer = 'mssqlnative';
+			break;
+
 			default:
 				$this->sql_layer = $this->db->sql_layer;
 			break;
@@ -368,6 +402,7 @@ class phpbb_db_tools
 		switch ($this->sql_layer)
 		{
 			case 'mssql':
+			case 'mssqlnative':
 				$table_sql = 'CREATE TABLE [' . $table_name . '] (' . "\n";
 			break;
 
@@ -386,6 +421,7 @@ class phpbb_db_tools
 			switch ($this->sql_layer)
 			{
 				case 'mssql':
+				case 'mssqlnative':
 					$columns[] = "\t [{$column_name}] " . $prepared_column['column_type_sql_default'];
 				break;
 
@@ -425,6 +461,7 @@ class phpbb_db_tools
 			break;
 
 			case 'mssql':
+			case 'mssqlnative':
 				$table_sql .= "\n) ON [PRIMARY]" . (($create_textimage) ? ' TEXTIMAGE_ON [PRIMARY]' : '');
 				$statements[] = $table_sql;
 			break;
@@ -453,6 +490,7 @@ class phpbb_db_tools
 
 					case 'firebird':
 					case 'mssql':
+					case 'mssqlnative':
 						// We need the data here
 						$old_return_statements = $this->return_statements;
 						$this->return_statements = true;
@@ -970,6 +1008,7 @@ class phpbb_db_tools
 			// same deal with PostgreSQL, we must perform more complex operations than
 			// we technically could
 			case 'mssql':
+			case 'mssqlnative':
 				$sql = "SELECT c.name
 					FROM syscolumns c
 					LEFT JOIN sysobjects o ON c.id = o.id
@@ -1187,6 +1226,7 @@ class phpbb_db_tools
 			break;
 
 			case 'mssql':
+			case 'mssqlnative':
 				$sql .= " {$column_type} ";
 				$sql_default = " {$column_type} ";
 
@@ -1335,6 +1375,7 @@ class phpbb_db_tools
 			break;
 
 			case 'mssql':
+			case 'mssqlnative':
 				$statements[] = 'ALTER TABLE [' . $table_name . '] ADD [' . $column_name . '] ' . $column_data['column_type_sql_default'];
 			break;
 
@@ -1455,6 +1496,7 @@ class phpbb_db_tools
 			break;
 
 			case 'mssql':
+			case 'mssqlnative':
 				$statements[] = 'ALTER TABLE [' . $table_name . '] DROP COLUMN [' . $column_name . ']';
 			break;
 
@@ -1549,6 +1591,7 @@ class phpbb_db_tools
 		switch ($this->sql_layer)
 		{
 			case 'mssql':
+			case 'mssqlnative':
 				$statements[] = 'DROP INDEX ' . $table_name . '.' . $index_name;
 			break;
 
@@ -1652,6 +1695,7 @@ class phpbb_db_tools
 			break;
 
 			case 'mssql':
+			case 'mssqlnative':
 				$sql = "ALTER TABLE [{$table_name}] WITH NOCHECK ADD ";
 				$sql .= "CONSTRAINT [PK_{$table_name}] PRIMARY KEY  CLUSTERED (";
 				$sql .= '[' . implode("],\n\t\t[", $column) . ']';
@@ -1745,6 +1789,7 @@ class phpbb_db_tools
 			break;
 
 			case 'mssql':
+			case 'mssqlnative':
 				$statements[] = 'CREATE UNIQUE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ') ON [PRIMARY]';
 			break;
 		}
@@ -1774,6 +1819,7 @@ class phpbb_db_tools
 			break;
 
 			case 'mssql':
+			case 'mssqlnative':
 				$statements[] = 'CREATE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ') ON [PRIMARY]';
 			break;
 		}
@@ -1791,7 +1837,7 @@ class phpbb_db_tools
 	{
 		$index_array = array();
 
-		if ($this->sql_layer == 'mssql')
+		if ($this->sql_layer == 'mssql' || $this->sql_layer == 'mssqlnative')
 		{
 			$sql = "EXEC sp_statistics '$table_name'";
 			$result = $this->db->sql_query($sql);
@@ -1900,6 +1946,7 @@ class phpbb_db_tools
 			break;
 
 			case 'mssql':
+			case 'mssqlnative':
 				$statements[] = 'ALTER TABLE [' . $table_name . '] ALTER COLUMN [' . $column_name . '] ' . $column_data['column_type_sql'];
 
 				if (!empty($column_data['default']))
diff --git a/phpBB/includes/db/mssqlnative.php b/phpBB/includes/db/mssqlnative.php
new file mode 100644
index 0000000000..c3657d2708
--- /dev/null
+++ b/phpBB/includes/db/mssqlnative.php
@@ -0,0 +1,608 @@
+<?php
+/**
+*
+* @package dbal
+* This is the MS SQL Server Native database abstraction layer.
+* PHP mssql native driver required.
+* @copyright (c) 2005 phpBB Group
+* @license http://opensource.org/licenses/gpl-license.php GNU Public License
+* @author Chris Pucci
+*
+*/
+
+/**
+* @ignore
+*/
+if (!defined('IN_PHPBB'))
+{
+	exit;
+}
+
+include_once($phpbb_root_path . 'includes/db/dbal.' . $phpEx);
+
+/**
+ * Prior to version 1.1 the SQL Server Native PHP driver didn't support sqlsrv_num_rows, or cursor based seeking so we recall all rows into an array
+ * and maintain our own cursor index into that array.
+ */
+class result_mssqlnative
+{
+	public function result_mssqlnative($queryresult = false)
+	{
+		$this->m_cursor = 0;
+		$this->m_rows = array();
+		$this->m_num_fields = sqlsrv_num_fields($queryresult);
+		$this->m_field_meta = sqlsrv_field_metadata($queryresult);
+
+		while ($row = sqlsrv_fetch_array($queryresult, SQLSRV_FETCH_ASSOC))
+		{
+			if ($row !== null)
+			{
+				foreach($row as $k => $v)
+				{
+					if (is_object($v) && method_exists($v, 'format'))
+					{
+						$row[$k] = $v->format("Y-m-d\TH:i:s\Z");
+					}
+				}
+				$this->m_rows[] = $row;//read results into memory, cursors are not supported
+			}
+		}
+
+		$this->m_row_count = count($this->m_rows);
+		sqlsrv_free_stmt($queryresult);
+	}
+
+	private function array_to_obj($array, &$obj)
+	{
+		foreach ($array as $key => $value)
+		{
+			if (is_array($value))
+			{
+				$obj->$key = new stdClass();
+				array_to_obj($value, $obj->$key);
+			}
+			else
+			{
+				$obj->$key = $value;
+			}
+		}
+		return $obj;
+	}
+
+	public function fetch($mode = SQLSRV_FETCH_BOTH, $object_class = 'stdClass')
+	{
+		if ($this->m_cursor >= $this->m_row_count || $this->m_row_count == 0)
+		{
+			return false;
+		}
+
+		$ret = false;
+		$arr_num = array();
+
+		if ($mode == SQLSRV_FETCH_NUMERIC || $mode == SQLSRV_FETCH_BOTH)
+		{
+			foreach($this->m_rows[$this->m_cursor] as $key => $value)
+			{
+				$arr_num[] = $value;
+			}
+		}
+
+		switch ($mode)
+		{
+			case SQLSRV_FETCH_ASSOC:
+				$ret = $this->m_rows[$this->m_cursor];
+			break;
+			case SQLSRV_FETCH_NUMERIC:
+				$ret = $arr_num;
+			break;
+			case 'OBJECT':
+				$ret = $this->array_to_obj($this->m_rows[$this->m_cursor], $o = new $object_class);
+			break;
+			case SQLSRV_FETCH_BOTH:
+			default:
+				$ret = $this->m_rows[$this->m_cursor] + $arr_num;
+			break;
+		}
+		$this->m_cursor++;
+		return $ret;
+	}
+
+	public function get($pos, $fld)
+	{
+		return $this->m_rows[$pos][$fld];
+	}
+
+	public function num_rows()
+	{
+		return $this->m_row_count;
+	}
+
+	public function seek($iRow)
+	{
+		$this->m_cursor = min($iRow, $this->m_row_count);
+	}
+
+	public function num_fields()
+	{
+		return $this->m_num_fields;
+	}
+
+	public function field_name($nr)
+	{
+		$arr_keys = array_keys($this->m_rows[0]);
+		return $arr_keys[$nr];
+	}
+
+	public function field_type($nr)
+	{
+		$i = 0;
+		$int_type = -1;
+		$str_type = '';
+
+		foreach ($this->m_field_meta as $meta)
+		{
+			if ($nr == $i)
+			{
+				$int_type = $meta['Type'];
+				break;
+			}
+			$i++;
+		}
+
+		//http://msdn.microsoft.com/en-us/library/cc296183.aspx contains type table
+		switch ($int_type)
+		{
+			case SQLSRV_SQLTYPE_BIGINT: 		$str_type = 'bigint'; break;
+			case SQLSRV_SQLTYPE_BINARY: 		$str_type = 'binary'; break;
+			case SQLSRV_SQLTYPE_BIT: 			$str_type = 'bit'; break;
+			case SQLSRV_SQLTYPE_CHAR: 			$str_type = 'char'; break;
+			case SQLSRV_SQLTYPE_DATETIME: 		$str_type = 'datetime'; break;
+			case SQLSRV_SQLTYPE_DECIMAL/*($precision, $scale)*/: $str_type = 'decimal'; break;
+			case SQLSRV_SQLTYPE_FLOAT: 			$str_type = 'float'; break;
+			case SQLSRV_SQLTYPE_IMAGE: 			$str_type = 'image'; break;
+			case SQLSRV_SQLTYPE_INT: 			$str_type = 'int'; break;
+			case SQLSRV_SQLTYPE_MONEY: 			$str_type = 'money'; break;
+			case SQLSRV_SQLTYPE_NCHAR/*($charCount)*/: $str_type = 'nchar'; break;
+			case SQLSRV_SQLTYPE_NUMERIC/*($precision, $scale)*/: $str_type = 'numeric'; break;
+			case SQLSRV_SQLTYPE_NVARCHAR/*($charCount)*/: $str_type = 'nvarchar'; break;
+			case SQLSRV_SQLTYPE_NTEXT: 			$str_type = 'ntext'; break;
+			case SQLSRV_SQLTYPE_REAL: 			$str_type = 'real'; break;
+			case SQLSRV_SQLTYPE_SMALLDATETIME: 	$str_type = 'smalldatetime'; break;
+			case SQLSRV_SQLTYPE_SMALLINT: 		$str_type = 'smallint'; break;
+			case SQLSRV_SQLTYPE_SMALLMONEY: 	$str_type = 'smallmoney'; break;
+			case SQLSRV_SQLTYPE_TEXT: 			$str_type = 'text'; break;
+			case SQLSRV_SQLTYPE_TIMESTAMP: 		$str_type = 'timestamp'; break;
+			case SQLSRV_SQLTYPE_TINYINT: 		$str_type = 'tinyint'; break;
+			case SQLSRV_SQLTYPE_UNIQUEIDENTIFIER: $str_type = 'uniqueidentifier'; break;
+			case SQLSRV_SQLTYPE_UDT: 			$str_type = 'UDT'; break;
+			case SQLSRV_SQLTYPE_VARBINARY/*($byteCount)*/: $str_type = 'varbinary'; break;
+			case SQLSRV_SQLTYPE_VARCHAR/*($charCount)*/: $str_type = 'varchar'; break;
+			case SQLSRV_SQLTYPE_XML: 			$str_type = 'xml'; break;
+			default: $str_type = $int_type;
+		}
+		return $str_type;
+	}
+
+	public function free()
+	{
+		unset($this->m_rows);
+		return;
+	}
+}
+
+/**
+* @package dbal
+*/
+class dbal_mssqlnative extends dbal
+{
+	var $m_insert_id = NULL;
+	var $last_query_text = '';
+
+	/**
+	* Connect to server
+	*/
+	function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false, $new_link = false)
+	{
+		# Test for driver support, to avoid suppressed fatal error
+		if (!function_exists('sqlsrv_connect'))
+		{
+			trigger_error('Native MS SQL Server driver for PHP is missing or needs to be updated. Version 1.1 or later is required to install phpBB3. You can download the driver from: http://www.microsoft.com/sqlserver/2005/en/us/PHP-Driver.aspx\n', E_USER_ERROR);
+		}
+
+		//set up connection variables
+		$this->persistency = $persistency;
+		$this->user = $sqluser;
+		$this->dbname = $database;
+		$port_delimiter = (defined('PHP_OS') && substr(PHP_OS, 0, 3) === 'WIN') ? ',' : ':';
+		$this->server = $sqlserver . (($port) ? $port_delimiter . $port : '');
+
+		//connect to database
+		error_reporting(E_ALL);
+		$this->db_connect_id = sqlsrv_connect($this->server, array(
+			'Database' => $this->dbname,
+			'UID' => $this->user,
+			'PWD' => $sqlpassword
+		));
+
+		return ($this->db_connect_id) ? $this->db_connect_id : $this->sql_error('');
+	}
+
+	/**
+	* Version information about used database
+	* @param bool $raw if true, only return the fetched sql_server_version
+	* @return string sql server version
+	*/
+	function sql_server_info($raw = false)
+	{
+		global $cache;
+
+		if (empty($cache) || ($this->sql_server_version = $cache->get('mssql_version')) === false)
+		{
+			$arr_server_info = sqlsrv_server_info($this->db_connect_id);
+			$this->sql_server_version = $arr_server_info['SQLServerVersion'];
+
+			if (!empty($cache))
+			{
+				$cache->put('mssql_version', $this->sql_server_version);
+			}
+		}
+
+		if ($raw)
+		{
+			return $this->sql_server_version;
+		}
+
+		return ($this->sql_server_version) ? 'MSSQL<br />' . $this->sql_server_version : 'MSSQL';
+	}
+
+	/**
+	* SQL Transaction
+	* @access private
+	*/
+	function _sql_transaction($status = 'begin')
+	{
+		switch ($status)
+		{
+			case 'begin':
+				return sqlsrv_begin_transaction($this->db_connect_id);
+			break;
+
+			case 'commit':
+				return sqlsrv_commit($this->db_connect_id);
+			break;
+
+			case 'rollback':
+				return sqlsrv_rollback($this->db_connect_id);
+			break;
+		}
+		return true;
+	}
+
+	/**
+	* Base query method
+	*
+	* @param	string	$query		Contains the SQL query which shall be executed
+	* @param	int		$cache_ttl	Either 0 to avoid caching or the time in seconds which the result shall be kept in cache
+	* @return	mixed				When casted to bool the returned value returns true on success and false on failure
+	*
+	* @access	public
+	*/
+	function sql_query($query = '', $cache_ttl = 0)
+	{
+		if ($query != '')
+		{
+			global $cache;
+
+			// EXPLAIN only in extra debug mode
+			if (defined('DEBUG_EXTRA'))
+			{
+				$this->sql_report('start', $query);
+			}
+
+			$this->last_query_text = $query;
+			$this->query_result = ($cache_ttl && method_exists($cache, 'sql_load')) ? $cache->sql_load($query) : false;
+			$this->sql_add_num_queries($this->query_result);
+
+			if ($this->query_result === false)
+			{
+				if (($this->query_result = @sqlsrv_query($this->db_connect_id, $query)) === false)
+				{
+					$this->sql_error($query);
+				}
+
+				if (defined('DEBUG_EXTRA'))
+				{
+					$this->sql_report('stop', $query);
+				}
+
+				if ($cache_ttl && method_exists($cache, 'sql_save'))
+				{
+					$this->open_queries[(int) $this->query_result] = $this->query_result;
+					$cache->sql_save($query, $this->query_result, $cache_ttl);
+				}
+				else if (strpos($query, 'SELECT') === 0 && $this->query_result)
+				{
+					$this->open_queries[(int) $this->query_result] = $this->query_result;
+				}
+			}
+			else if (defined('DEBUG_EXTRA'))
+			{
+				$this->sql_report('fromcache', $query);
+			}
+		}
+		else
+		{
+			return false;
+		}
+		return $this->query_result;
+	}
+
+	/**
+	* Build LIMIT query
+	*/
+	function _sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0)
+	{
+		$this->query_result = false;
+
+		if ($offset === false || $offset == 0)
+		{
+			if (strpos($query, "SELECT") === false)
+			{
+				$query = "TOP {$total} " . $query;
+			}
+			else
+			{
+				$query = preg_replace('/SELECT(\s*DISTINCT)?/Dsi', 'SELECT$1 TOP '.$total, $query);
+			}
+		}
+		else
+		{
+			$query = preg_replace('/SELECT(\s*DISTINCT)?/Dsi', 'SELECT$1 TOP(10000000) ', $query);
+			$query = 'SELECT *
+					FROM (SELECT sub2.*, ROW_NUMBER() OVER(ORDER BY sub2.line2) AS line3
+					FROM (SELECT 1 AS line2, sub1.* FROM (' . $query . ') AS sub1) as sub2) AS sub3
+					WHERE line3 BETWEEN ' . ($offset+1) . ' AND ' . ($offset + $total);
+		}
+
+		$result = $this->sql_query($query, $cache_ttl);
+
+		return $result;
+	}
+
+	/**
+	* Return number of affected rows
+	*/
+	function sql_affectedrows()
+	{
+		return ($this->db_connect_id) ? @sqlsrv_rows_affected($this->db_connect_id) : false;
+	}
+
+	/**
+	* Fetch current row
+	*/
+	function sql_fetchrow($query_id = false)
+	{
+		global $cache;
+
+		if ($query_id === false)
+		{
+			$query_id = $this->query_result;
+		}
+
+		if (isset($cache->sql_rowset[$query_id]))
+		{
+			return $cache->sql_fetchrow($query_id);
+		}
+
+		if ($query_id === false)
+		{
+			return false;
+		}
+
+		$row = @sqlsrv_fetch_array($query_id, SQLSRV_FETCH_ASSOC);
+
+		// I hope i am able to remove this later... hopefully only a PHP or MSSQL bug
+		if ($row)
+		{
+			foreach ($row as $key => $value)
+			{
+				$row[$key] = ($value === ' ' || $value === NULL) ? '' : $value;
+			}
+		}
+		return $row;
+	}
+
+	/**
+	* Seek to given row number
+	* rownum is zero-based
+	*/
+	function sql_rowseek($rownum, &$query_id)
+	{
+		global $cache;
+
+		if (isset($cache->sql_rowset[$query_id]))
+		{
+			return $cache->sql_rowseek($rownum, $query_id);
+		}
+
+		$seek = new result_mssqlnative($query_id);
+		$row = $seek->seek($rownum);
+		return ($row = $seek->fetch()) ? $row : false;
+	}
+
+	/**
+	* Get last inserted id after insert statement
+	*/
+	function sql_nextid()
+	{
+		$result_id = @sqlsrv_query($this->db_connect_id, 'SELECT @@IDENTITY');
+
+		if ($result_id !== false)
+		{
+			$row = @sqlsrv_fetch_array($result_id);
+			$id = $row[0];
+			@sqlsrv_free_stmt($result_id);
+			return $id;
+		}
+		else
+		{
+			return false;
+		}
+	}
+
+	/**
+	* Free sql result
+	*/
+	function sql_freeresult($query_id = false)
+	{
+		global $cache;
+
+		if ($query_id === false)
+		{
+			$query_id = $this->query_result;
+		}
+
+		if (isset($cache->sql_rowset[$query_id]))
+		{
+			return $cache->sql_freeresult($query_id);
+		}
+
+		if (isset($this->open_queries[$query_id]))
+		{
+			unset($this->open_queries[$query_id]);
+			return @sqlsrv_free_stmt($query_id);
+		}
+		return false;
+	}
+
+	/**
+	* Escape string used in sql query
+	*/
+	function sql_escape($msg)
+	{
+		return str_replace(array("'", "\0"), array("''", ''), $msg);
+	}
+
+	/**
+	* Build LIKE expression
+	* @access private
+	*/
+	function _sql_like_expression($expression)
+	{
+		return $expression . " ESCAPE '\\'";
+	}
+
+	/**
+	* return sql error array
+	* @access private
+	*/
+	function _sql_error()
+	{
+		$errors = @sqlsrv_errors(SQLSRV_ERR_ERRORS);
+		$error_message = '';
+
+		if ($errors != null)
+		{
+			foreach ($errors as $error)
+			{
+				$error_message .= "SQLSTATE: ".$error[ 'SQLSTATE']."\n";
+				$error_message .= "code: ".$error[ 'code']."\n";
+				$error_message .= "message: ".$error[ 'message']."\n";
+			}
+			$this->last_error_result = $error_message;
+			$error = $this->last_error_result;
+		}
+		else
+		{
+			$error = (isset($this->last_error_result) && $this->last_error_result) ? $this->last_error_result : array();
+		}
+		return $error;
+	}
+
+	/**
+	* Build db-specific query data
+	* @access private
+	*/
+	function _sql_custom_build($stage, $data)
+	{
+		return $data;
+	}
+
+	/**
+	* Close sql connection
+	* @access private
+	*/
+	function _sql_close()
+	{
+		return @sqlsrv_close($this->db_connect_id);
+	}
+
+	/**
+	* Build db-specific report
+	* @access private
+	*/
+	function _sql_report($mode, $query = '')
+	{
+		switch ($mode)
+		{
+			case 'start':
+				$html_table = false;
+				@sqlsrv_query($this->db_connect_id, 'SET SHOWPLAN_TEXT ON;');
+				if ($result = @sqlsrv_query($this->db_connect_id, $query))
+				{
+					@sqlsrv_next_result($result);
+					while ($row = @sqlsrv_fetch_array($result))
+					{
+						$html_table = $this->sql_report('add_select_row', $query, $html_table, $row);
+					}
+				}
+				@sqlsrv_query($this->db_connect_id, 'SET SHOWPLAN_TEXT OFF;');
+				@sqlsrv_free_stmt($result);
+
+				if ($html_table)
+				{
+					$this->html_hold .= '</table>';
+				}
+			break;
+
+			case 'fromcache':
+				$endtime = explode(' ', microtime());
+				$endtime = $endtime[0] + $endtime[1];
+
+				$result = @sqlsrv_query($this->db_connect_id, $query);
+				while ($void = @sqlsrv_fetch_array($result))
+				{
+					// Take the time spent on parsing rows into account
+				}
+				@sqlsrv_free_stmt($result);
+
+				$splittime = explode(' ', microtime());
+				$splittime = $splittime[0] + $splittime[1];
+
+				$this->sql_report('record_fromcache', $query, $endtime, $splittime);
+
+			break;
+		}
+	}
+
+	/**
+	* Utility method used to retrieve number of rows
+	* Emulates mysql_num_rows
+	* Used in acp_database.php -> write_data_mssqlnative()
+	*/
+	function mssqlnative_num_rows($res)
+	{
+		if ($res !== false)
+		{
+			$row = new result_mssqlnative($res);
+			$num_rows = $row->num_rows();
+			return $num_rows;
+		}
+		else
+		{
+			return false;
+		}
+	}
+}
+
+?>
+\ No newline at end of file
diff --git a/phpBB/includes/functions_admin.php b/phpBB/includes/functions_admin.php
index ff291fbe8a..ea64bb3beb 100644
--- a/phpBB/includes/functions_admin.php
+++ b/phpBB/includes/functions_admin.php
@@ -3041,6 +3041,7 @@ function get_database_size()
 
 		case 'mssql':
 		case 'mssql_odbc':
+		case 'mssqlnative':
 			$sql = 'SELECT ((SUM(size) * 8.0) * 1024.0) as dbsize
 				FROM sysfiles';
 			$result = $db->sql_query($sql, 7200);
diff --git a/phpBB/includes/functions_convert.php b/phpBB/includes/functions_convert.php
index 8a1d07c8e5..8b3c6ea8db 100644
--- a/phpBB/includes/functions_convert.php
+++ b/phpBB/includes/functions_convert.php
@@ -1643,6 +1643,7 @@ function mass_auth($ug_type, $forum_id, $ug_id, $acl_list, $setting = ACL_NO)
 
 					case 'mssql':
 					case 'sqlite':
+					case 'mssqlnative':
 						$sql = implode(' UNION ALL ', preg_replace('#^(.*?)$#', 'SELECT \1', $sql_subary));
 					break;
 
diff --git a/phpBB/includes/functions_install.php b/phpBB/includes/functions_install.php
index 1b9c66fc84..992e8d6bb0 100644
--- a/phpBB/includes/functions_install.php
+++ b/phpBB/includes/functions_install.php
@@ -95,6 +95,16 @@ function get_available_dbms($dbms = false, $return_unavailable = false, $only_20
 			'AVAILABLE'		=> true,
 			'2.0.x'			=> true,
 		),
+		'mssqlnative'		=> array(
+			'LABEL'			=> 'MS SQL Server 2005+ [ Native ]',
+			'SCHEMA'		=> 'mssql',
+			'MODULE'		=> 'sqlsrv',
+			'DELIM'			=> 'GO',
+			'COMMENTS'		=> 'remove_comments',
+			'DRIVER'		=> 'mssqlnative',
+			'AVAILABLE'		=> true,
+			'2.0.x'			=> false,
+		),			
 		'oracle'	=>	array(
 			'LABEL'			=> 'Oracle',
 			'SCHEMA'		=> 'oracle',
@@ -220,6 +230,7 @@ function get_tables($db)
 
 		case 'mssql':
 		case 'mssql_odbc':
+		case 'mssqlnative':
 			$sql = "SELECT name
 				FROM sysobjects
 				WHERE type='U'";
@@ -313,6 +324,7 @@ function connect_check_db($error_connect, &$error, $dbms_details, $table_prefix,
 
 		case 'mssql':
 		case 'mssql_odbc':
+		case 'mssqlnative':
 			$prefix_length = 90;
 		break;
 
diff --git a/phpBB/includes/message_parser.php b/phpBB/includes/message_parser.php
index 8979511d9a..50aad8588a 100644
--- a/phpBB/includes/message_parser.php
+++ b/phpBB/includes/message_parser.php
@@ -1284,6 +1284,7 @@ class parse_message extends bbcode_firstpass
 			{
 				case 'mssql':
 				case 'mssql_odbc':
+				case 'mssqlnative':
 					$sql = 'SELECT *
 						FROM ' . SMILIES_TABLE . '
 						ORDER BY LEN(code) DESC';
diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php
index 4efc52088f..055fa78995 100644
--- a/phpBB/install/database_update.php
+++ b/phpBB/install/database_update.php
@@ -1743,7 +1743,37 @@ class updater_db_tools
 			'VCHAR_CI'	=> '[varchar] (255)',
 			'VARBINARY'	=> '[varchar] (255)',
 		),
-
+		
+		'mssqlnative'	=> array(
+			'INT:'		=> '[int]',
+			'BINT'		=> '[float]',
+			'UINT'		=> '[int]',
+			'UINT:'		=> '[int]',
+			'TINT:'		=> '[int]',
+			'USINT'		=> '[int]',
+			'BOOL'		=> '[int]',
+			'VCHAR'		=> '[varchar] (255)',
+			'VCHAR:'	=> '[varchar] (%d)',
+			'CHAR:'		=> '[char] (%d)',
+			'XSTEXT'	=> '[varchar] (1000)',
+			'STEXT'		=> '[varchar] (3000)',
+			'TEXT'		=> '[varchar] (8000)',
+			'MTEXT'		=> '[text]',
+			'XSTEXT_UNI'=> '[varchar] (100)',
+			'STEXT_UNI'	=> '[varchar] (255)',
+			'TEXT_UNI'	=> '[varchar] (4000)',
+			'MTEXT_UNI'	=> '[text]',
+			'TIMESTAMP'	=> '[int]',
+			'DECIMAL'	=> '[float]',
+			'DECIMAL:'	=> '[float]',
+			'PDECIMAL'	=> '[float]',
+			'PDECIMAL:'	=> '[float]',
+			'VCHAR_UNI'	=> '[varchar] (255)',
+			'VCHAR_UNI:'=> '[varchar] (%d)',
+			'VCHAR_CI'	=> '[varchar] (255)',
+			'VARBINARY'	=> '[varchar] (255)',
+		),
+		
 		'oracle'	=> array(
 			'INT:'		=> 'number(%d)',
 			'BINT'		=> 'number(20)',
@@ -1845,7 +1875,7 @@ class updater_db_tools
 	* A list of supported DBMS. We change this class to support more DBMS, the DBMS itself only need to follow some rules.
 	* @var array
 	*/
-	var $supported_dbms = array('firebird', 'mssql', 'mysql_40', 'mysql_41', 'oracle', 'postgres', 'sqlite');
+	var $supported_dbms = array('firebird', 'mssql', 'mssqlnative', 'mysql_40', 'mysql_41', 'oracle', 'postgres', 'sqlite');
 
 	/**
 	* This is set to true if user only wants to return the 'to-be-executed' SQL statement(s) (as an array).
@@ -1890,6 +1920,10 @@ class updater_db_tools
 			case 'mssql_odbc':
 				$this->sql_layer = 'mssql';
 			break;
+			
+			case 'mssqlnative':
+				$this->sql_layer = 'mssqlnative';
+			break;
 
 			default:
 				$this->sql_layer = $this->db->sql_layer;
@@ -2322,6 +2356,7 @@ class updater_db_tools
 			// same deal with PostgreSQL, we must perform more complex operations than
 			// we technically could
 			case 'mssql':
+			case 'mssqlnative':
 				$sql = "SELECT c.name
 					FROM syscolumns c
 					LEFT JOIN sysobjects o ON c.id = o.id
@@ -2425,7 +2460,7 @@ class updater_db_tools
 	*/
 	function sql_index_exists($table_name, $index_name)
 	{
-		if ($this->sql_layer == 'mssql')
+		if ($this->sql_layer == 'mssql' || $this->sql_layer == 'mssqlnative')
 		{
 			$sql = "EXEC sp_statistics '$table_name'";
 			$result = $this->db->sql_query($sql);
@@ -2530,7 +2565,7 @@ class updater_db_tools
 	*/
 	function sql_unique_index_exists($table_name, $index_name)
 	{
-		if ($this->sql_layer == 'mssql')
+		if ($this->sql_layer == 'mssql' || $this->sql_layer == 'mssqlnative')
 		{
 			$sql = "EXEC sp_statistics '$table_name'";
 			$result = $this->db->sql_query($sql);
@@ -2769,6 +2804,7 @@ class updater_db_tools
 			break;
 
 			case 'mssql':
+			case 'mssqlnative':
 				$sql .= " {$column_type} ";
 				$sql_default = " {$column_type} ";
 
@@ -2918,6 +2954,7 @@ class updater_db_tools
 			break;
 
 			case 'mssql':
+			case 'mssqlnative':
 				// Does not support AFTER, only through temporary table
 				$statements[] = 'ALTER TABLE [' . $table_name . '] ADD [' . $column_name . '] ' . $column_data['column_type_sql_default'];
 			break;
@@ -3042,6 +3079,7 @@ class updater_db_tools
 			break;
 
 			case 'mssql':
+			case 'mssqlnative':
 				$statements[] = 'ALTER TABLE [' . $table_name . '] DROP COLUMN [' . $column_name . ']';
 			break;
 
@@ -3136,6 +3174,7 @@ class updater_db_tools
 		switch ($this->sql_layer)
 		{
 			case 'mssql':
+			case 'mssqlnative':
 				$statements[] = 'DROP INDEX ' . $table_name . '.' . $index_name;
 			break;
 
@@ -3172,6 +3211,7 @@ class updater_db_tools
 			break;
 
 			case 'mssql':
+			case 'mssqlnative':
 				$sql = "ALTER TABLE [{$table_name}] WITH NOCHECK ADD ";
 				$sql .= "CONSTRAINT [PK_{$table_name}] PRIMARY KEY  CLUSTERED (";
 				$sql .= '[' . implode("],\n\t\t[", $column) . ']';
@@ -3265,6 +3305,7 @@ class updater_db_tools
 			break;
 
 			case 'mssql':
+			case 'mssqlnative':
 				$statements[] = 'CREATE UNIQUE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ') ON [PRIMARY]';
 			break;
 		}
@@ -3294,6 +3335,7 @@ class updater_db_tools
 			break;
 
 			case 'mssql':
+			case 'mssqlnative':
 				$statements[] = 'CREATE INDEX ' . $index_name . ' ON ' . $table_name . '(' . implode(', ', $column) . ') ON [PRIMARY]';
 			break;
 		}
@@ -3326,6 +3368,7 @@ class updater_db_tools
 			break;
 
 			case 'mssql':
+			case 'mssqlnative':
 				$statements[] = 'ALTER TABLE [' . $table_name . '] ALTER COLUMN [' . $column_name . '] ' . $column_data['column_type_sql'];
 
 				if (!empty($column_data['default']))
diff --git a/phpBB/install/install_convert.php b/phpBB/install/install_convert.php
index 7f1b9de5b6..06c3a8b4a6 100644
--- a/phpBB/install/install_convert.php
+++ b/phpBB/install/install_convert.php
@@ -1248,6 +1248,7 @@ class install_convert extends module
 					{
 						case 'mssql':
 						case 'mssql_odbc':
+						case 'mssqlnative':
 							$db->sql_query('SET IDENTITY_INSERT ' . $schema['target'] . ' ON');
 						break;
 					}
@@ -1375,6 +1376,7 @@ class install_convert extends module
 					{
 						case 'mssql':
 						case 'mssql_odbc':
+						case 'mssqlnative':
 							$db->sql_query('SET IDENTITY_INSERT ' . $schema['target'] . ' OFF');
 						break;
 
diff --git a/phpBB/install/install_install.php b/phpBB/install/install_install.php
index 1cc1365752..f4989b5bd7 100644
--- a/phpBB/install/install_install.php
+++ b/phpBB/install/install_install.php
@@ -1203,6 +1203,7 @@ class install_install extends module
 		{
 			case 'mssql':
 			case 'mssql_odbc':
+			case 'mssqlnative':
 				$sql_query = preg_replace('#\# MSSQL IDENTITY (phpbb_[a-z_]+) (ON|OFF) \##s', 'SET IDENTITY_INSERT \1 \2;', $sql_query);
 			break;
 
@@ -1375,7 +1376,7 @@ class install_install extends module
 			$sql_ary[] = 'UPDATE ' . $data['table_prefix'] . "config
 				SET config_value = 'phpbb_captcha_gd'
 				WHERE config_name = 'captcha_plugin'";
-			
+
 			$sql_ary[] = 'UPDATE ' . $data['table_prefix'] . "config
 				SET config_value = '1'
 				WHERE config_name = 'captcha_gd'";
diff --git a/phpBB/install/schemas/mssql_schema.sql b/phpBB/install/schemas/mssql_schema.sql
index 960c6eeaed..0827b14cc2 100644
--- a/phpBB/install/schemas/mssql_schema.sql
+++ b/phpBB/install/schemas/mssql_schema.sql
@@ -4,9 +4,6 @@
 
 */
 
-BEGIN TRANSACTION
-GO
-
 /*
 	Table: 'phpbb_attachments'
 */
@@ -1733,8 +1730,3 @@ ALTER TABLE [phpbb_zebra] WITH NOCHECK ADD
 	)  ON [PRIMARY] 
 GO
 
-
-
-COMMIT
-GO
-
diff --git a/phpBB/language/en/install.php b/phpBB/language/en/install.php
index 9543c712da..1c27e2f40d 100644
--- a/phpBB/language/en/install.php
+++ b/phpBB/language/en/install.php
@@ -147,6 +147,7 @@ $lang = array_merge($lang, array(
 	'DLL_MBSTRING'				=> 'Multi-byte character support',
 	'DLL_MSSQL'					=> 'MSSQL Server 2000+',
 	'DLL_MSSQL_ODBC'			=> 'MSSQL Server 2000+ via ODBC',
+	'DLL_MSSQLNATIVE'			=> 'MSSQL Server 2005+ [ Native ]',
 	'DLL_MYSQL'					=> 'MySQL',
 	'DLL_MYSQLI'				=> 'MySQL with MySQLi Extension',
 	'DLL_ORACLE'				=> 'Oracle',
@@ -214,6 +215,7 @@ $lang = array_merge($lang, array(
 		<li>SQLite 2.8.2+</li>
 		<li>Firebird 2.1+</li>
 		<li>MS SQL Server 2000 or above (directly or via ODBC)</li>
+		<li>MS SQL Server 2005 or above (native)</li>
 		<li>Oracle</li>
 	</ul>
author	Nils Adermann <naderman@naderman.de>	2010-02-11 00:02:51 +0000
committer	Nils Adermann <naderman@naderman.de>	2010-02-11 00:02:51 +0000
commit	1802b9ff9286a7fc24493e71b3432816cbdbfcd8 (patch)
tree	886dc576cb5a2be1d700372732446291479d483a /phpBB
parent	6c321c53a1ebd495518738157dc3a2bf8c0701e1 (diff)
download	forums-1802b9ff9286a7fc24493e71b3432816cbdbfcd8.tar forums-1802b9ff9286a7fc24493e71b3432816cbdbfcd8.tar.gz forums-1802b9ff9286a7fc24493e71b3432816cbdbfcd8.tar.bz2 forums-1802b9ff9286a7fc24493e71b3432816cbdbfcd8.tar.xz forums-1802b9ff9286a7fc24493e71b3432816cbdbfcd8.zip