1<?php
2/**
3*
4* @package dbal
5* @version $Id$
6* @copyright (c) 2010 phpBB Group
7* @license http://opensource.org/licenses/gpl-license.php GNU Public License
8*
9* This is the MS SQL Server Native database abstraction layer.
10* PHP mssql native driver required.
11* @author Chris Pucci
12*
13*/
14
15/**
16* @ignore
17*/
18if (!defined('IN_PHPBB'))
19{
20	exit;
21}
22
23include_once($phpbb_root_path . 'includes/db/dbal.' . $phpEx);
24
25/**
26 * 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
27 * and maintain our own cursor index into that array.
28 */
29class result_mssqlnative
30{
31	public function result_mssqlnative($queryresult = false)
32	{
33		$this->m_cursor = 0;
34		$this->m_rows = array();
35		$this->m_num_fields = sqlsrv_num_fields($queryresult);
36		$this->m_field_meta = sqlsrv_field_metadata($queryresult);
37
38		while ($row = sqlsrv_fetch_array($queryresult, SQLSRV_FETCH_ASSOC))
39		{
40			if ($row !== null)
41			{
42				foreach($row as $k => $v)
43				{
44					if (is_object($v) && method_exists($v, 'format'))
45					{
46						$row[$k] = $v->format("Y-m-d\TH:i:s\Z");
47					}
48				}
49				$this->m_rows[] = $row;//read results into memory, cursors are not supported
50			}
51		}
52
53		$this->m_row_count = sizeof($this->m_rows);
54	}
55
56	private function array_to_obj($array, &$obj)
57	{
58		foreach ($array as $key => $value)
59		{
60			if (is_array($value))
61			{
62				$obj->$key = new stdClass();
63				array_to_obj($value, $obj->$key);
64			}
65			else
66			{
67				$obj->$key = $value;
68			}
69		}
70		return $obj;
71	}
72
73	public function fetch($mode = SQLSRV_FETCH_BOTH, $object_class = 'stdClass')
74	{
75		if ($this->m_cursor >= $this->m_row_count || $this->m_row_count == 0)
76		{
77			return false;
78		}
79
80		$ret = false;
81		$arr_num = array();
82
83		if ($mode == SQLSRV_FETCH_NUMERIC || $mode == SQLSRV_FETCH_BOTH)
84		{
85			foreach($this->m_rows[$this->m_cursor] as $key => $value)
86			{
87				$arr_num[] = $value;
88			}
89		}
90
91		switch ($mode)
92		{
93			case SQLSRV_FETCH_ASSOC:
94				$ret = $this->m_rows[$this->m_cursor];
95			break;
96			case SQLSRV_FETCH_NUMERIC:
97				$ret = $arr_num;
98			break;
99			case 'OBJECT':
100				$ret = $this->array_to_obj($this->m_rows[$this->m_cursor], $o = new $object_class);
101			break;
102			case SQLSRV_FETCH_BOTH:
103			default:
104				$ret = $this->m_rows[$this->m_cursor] + $arr_num;
105			break;
106		}
107		$this->m_cursor++;
108		return $ret;
109	}
110
111	public function get($pos, $fld)
112	{
113		return $this->m_rows[$pos][$fld];
114	}
115
116	public function num_rows()
117	{
118		return $this->m_row_count;
119	}
120
121	public function seek($iRow)
122	{
123		$this->m_cursor = min($iRow, $this->m_row_count);
124	}
125
126	public function num_fields()
127	{
128		return $this->m_num_fields;
129	}
130
131	public function field_name($nr)
132	{
133		$arr_keys = array_keys($this->m_rows[0]);
134		return $arr_keys[$nr];
135	}
136
137	public function field_type($nr)
138	{
139		$i = 0;
140		$int_type = -1;
141		$str_type = '';
142
143		foreach ($this->m_field_meta as $meta)
144		{
145			if ($nr == $i)
146			{
147				$int_type = $meta['Type'];
148				break;
149			}
150			$i++;
151		}
152
153		//http://msdn.microsoft.com/en-us/library/cc296183.aspx contains type table
154		switch ($int_type)
155		{
156			case SQLSRV_SQLTYPE_BIGINT: 		$str_type = 'bigint'; break;
157			case SQLSRV_SQLTYPE_BINARY: 		$str_type = 'binary'; break;
158			case SQLSRV_SQLTYPE_BIT: 			$str_type = 'bit'; break;
159			case SQLSRV_SQLTYPE_CHAR: 			$str_type = 'char'; break;
160			case SQLSRV_SQLTYPE_DATETIME: 		$str_type = 'datetime'; break;
161			case SQLSRV_SQLTYPE_DECIMAL/*($precision, $scale)*/: $str_type = 'decimal'; break;
162			case SQLSRV_SQLTYPE_FLOAT: 			$str_type = 'float'; break;
163			case SQLSRV_SQLTYPE_IMAGE: 			$str_type = 'image'; break;
164			case SQLSRV_SQLTYPE_INT: 			$str_type = 'int'; break;
165			case SQLSRV_SQLTYPE_MONEY: 			$str_type = 'money'; break;
166			case SQLSRV_SQLTYPE_NCHAR/*($charCount)*/: $str_type = 'nchar'; break;
167			case SQLSRV_SQLTYPE_NUMERIC/*($precision, $scale)*/: $str_type = 'numeric'; break;
168			case SQLSRV_SQLTYPE_NVARCHAR/*($charCount)*/: $str_type = 'nvarchar'; break;
169			case SQLSRV_SQLTYPE_NTEXT: 			$str_type = 'ntext'; break;
170			case SQLSRV_SQLTYPE_REAL: 			$str_type = 'real'; break;
171			case SQLSRV_SQLTYPE_SMALLDATETIME: 	$str_type = 'smalldatetime'; break;
172			case SQLSRV_SQLTYPE_SMALLINT: 		$str_type = 'smallint'; break;
173			case SQLSRV_SQLTYPE_SMALLMONEY: 	$str_type = 'smallmoney'; break;
174			case SQLSRV_SQLTYPE_TEXT: 			$str_type = 'text'; break;
175			case SQLSRV_SQLTYPE_TIMESTAMP: 		$str_type = 'timestamp'; break;
176			case SQLSRV_SQLTYPE_TINYINT: 		$str_type = 'tinyint'; break;
177			case SQLSRV_SQLTYPE_UNIQUEIDENTIFIER: $str_type = 'uniqueidentifier'; break;
178			case SQLSRV_SQLTYPE_UDT: 			$str_type = 'UDT'; break;
179			case SQLSRV_SQLTYPE_VARBINARY/*($byteCount)*/: $str_type = 'varbinary'; break;
180			case SQLSRV_SQLTYPE_VARCHAR/*($charCount)*/: $str_type = 'varchar'; break;
181			case SQLSRV_SQLTYPE_XML: 			$str_type = 'xml'; break;
182			default: $str_type = $int_type;
183		}
184		return $str_type;
185	}
186
187	public function free()
188	{
189		unset($this->m_rows);
190		return;
191	}
192}
193
194/**
195* @package dbal
196*/
197class dbal_mssqlnative extends dbal
198{
199	var $m_insert_id = NULL;
200	var $last_query_text = '';
201	var $query_options = array();
202	var $connect_error = '';
203
204	/**
205	* Connect to server
206	*/
207	function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false, $new_link = false)
208	{
209		// Test for driver support, to avoid suppressed fatal error
210		if (!function_exists('sqlsrv_connect'))
211		{
212			$this->connect_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';
213			return $this->sql_error('');
214		}
215
216		//set up connection variables
217		$this->persistency = $persistency;
218		$this->user = $sqluser;
219		$this->dbname = $database;
220		$port_delimiter = (defined('PHP_OS') && substr(PHP_OS, 0, 3) === 'WIN') ? ',' : ':';
221		$this->server = $sqlserver . (($port) ? $port_delimiter . $port : '');
222
223		//connect to database
224		$this->db_connect_id = sqlsrv_connect($this->server, array(
225			'Database' => $this->dbname,
226			'UID' => $this->user,
227			'PWD' => $sqlpassword
228		));
229
230		return ($this->db_connect_id) ? $this->db_connect_id : $this->sql_error('');
231	}
232
233	/**
234	* Version information about used database
235	* @param bool $raw if true, only return the fetched sql_server_version
236	* @param bool $use_cache If true, it is safe to retrieve the value from the cache
237	* @return string sql server version
238	*/
239	function sql_server_info($raw = false, $use_cache = true)
240	{
241		global $cache;
242
243		if (!$use_cache || empty($cache) || ($this->sql_server_version = $cache->get('mssql_version')) === false)
244		{
245			$arr_server_info = sqlsrv_server_info($this->db_connect_id);
246			$this->sql_server_version = $arr_server_info['SQLServerVersion'];
247
248			if (!empty($cache) && $use_cache)
249			{
250				$cache->put('mssql_version', $this->sql_server_version);
251			}
252		}
253
254		if ($raw)
255		{
256			return $this->sql_server_version;
257		}
258
259		return ($this->sql_server_version) ? 'MSSQL<br />' . $this->sql_server_version : 'MSSQL';
260	}
261
262	/**
263	* {@inheritDoc}
264	*/
265	function sql_buffer_nested_transactions()
266	{
267		return true;
268	}
269
270	/**
271	* SQL Transaction
272	* @access private
273	*/
274	function _sql_transaction($status = 'begin')
275	{
276		switch ($status)
277		{
278			case 'begin':
279				return sqlsrv_begin_transaction($this->db_connect_id);
280			break;
281
282			case 'commit':
283				return sqlsrv_commit($this->db_connect_id);
284			break;
285
286			case 'rollback':
287				return sqlsrv_rollback($this->db_connect_id);
288			break;
289		}
290		return true;
291	}
292
293	/**
294	* Base query method
295	*
296	* @param	string	$query		Contains the SQL query which shall be executed
297	* @param	int		$cache_ttl	Either 0 to avoid caching or the time in seconds which the result shall be kept in cache
298	* @return	mixed				When casted to bool the returned value returns true on success and false on failure
299	*
300	* @access	public
301	*/
302	function sql_query($query = '', $cache_ttl = 0)
303	{
304		if ($query != '')
305		{
306			global $cache;
307
308			// EXPLAIN only in extra debug mode
309			if (defined('DEBUG_EXTRA'))
310			{
311				$this->sql_report('start', $query);
312			}
313
314			$this->last_query_text = $query;
315			$this->query_result = ($cache_ttl && method_exists($cache, 'sql_load')) ? $cache->sql_load($query) : false;
316			$this->sql_add_num_queries($this->query_result);
317
318			if ($this->query_result === false)
319			{
320				if (($this->query_result = @sqlsrv_query($this->db_connect_id, $query, array(), $this->query_options)) === false)
321				{
322					$this->sql_error($query);
323				}
324				// reset options for next query
325				$this->query_options = array();
326
327				if (defined('DEBUG_EXTRA'))
328				{
329					$this->sql_report('stop', $query);
330				}
331
332				if ($cache_ttl && method_exists($cache, 'sql_save'))
333				{
334					$this->open_queries[(int) $this->query_result] = $this->query_result;
335					$cache->sql_save($query, $this->query_result, $cache_ttl);
336				}
337				else if (strpos($query, 'SELECT') === 0 && $this->query_result)
338				{
339					$this->open_queries[(int) $this->query_result] = $this->query_result;
340				}
341			}
342			else if (defined('DEBUG_EXTRA'))
343			{
344				$this->sql_report('fromcache', $query);
345			}
346		}
347		else
348		{
349			return false;
350		}
351		return $this->query_result;
352	}
353
354	/**
355	* Build LIMIT query
356	*/
357	function _sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0)
358	{
359		$this->query_result = false;
360
361		// total == 0 means all results - not zero results
362		if ($offset == 0 && $total !== 0)
363		{
364			if (strpos($query, "SELECT") === false)
365			{
366				$query = "TOP {$total} " . $query;
367			}
368			else
369			{
370				$query = preg_replace('/SELECT(\s*DISTINCT)?/Dsi', 'SELECT$1 TOP '.$total, $query);
371			}
372		}
373		else if ($offset > 0)
374		{
375			$query = preg_replace('/SELECT(\s*DISTINCT)?/Dsi', 'SELECT$1 TOP(10000000) ', $query);
376			$query = 'SELECT *
377					FROM (SELECT sub2.*, ROW_NUMBER() OVER(ORDER BY sub2.line2) AS line3
378					FROM (SELECT 1 AS line2, sub1.* FROM (' . $query . ') AS sub1) as sub2) AS sub3';
379
380			if ($total > 0)
381			{
382				$query .= ' WHERE line3 BETWEEN ' . ($offset+1) . ' AND ' . ($offset + $total);
383			}
384			else
385			{
386				$query .= ' WHERE line3 > ' . $offset;
387			}
388		}
389
390		$result = $this->sql_query($query, $cache_ttl);
391
392		return $result;
393	}
394
395	/**
396	* Return number of affected rows
397	*/
398	function sql_affectedrows()
399	{
400		return (!empty($this->query_result)) ? @sqlsrv_rows_affected($this->query_result) : false;
401	}
402
403	/**
404	* Fetch current row
405	*/
406	function sql_fetchrow($query_id = false)
407	{
408		global $cache;
409
410		if ($query_id === false)
411		{
412			$query_id = $this->query_result;
413		}
414
415		if (isset($cache->sql_rowset[$query_id]))
416		{
417			return $cache->sql_fetchrow($query_id);
418		}
419
420		if ($query_id === false)
421		{
422			return false;
423		}
424
425		$row = @sqlsrv_fetch_array($query_id, SQLSRV_FETCH_ASSOC);
426
427		if ($row)
428		{
429			foreach ($row as $key => $value)
430			{
431				$row[$key] = ($value === ' ' || $value === NULL) ? '' : $value;
432			}
433
434			// remove helper values from LIMIT queries
435			if (isset($row['line2']))
436			{
437				unset($row['line2'], $row['line3']);
438			}
439		}
440		return (sizeof($row)) ? $row : false;
441	}
442
443	/**
444	* Get last inserted id after insert statement
445	*/
446	function sql_nextid()
447	{
448		$result_id = @sqlsrv_query($this->db_connect_id, 'SELECT @@IDENTITY');
449
450		if ($result_id !== false)
451		{
452			$row = @sqlsrv_fetch_array($result_id);
453			$id = $row[0];
454			@sqlsrv_free_stmt($result_id);
455			return $id;
456		}
457		else
458		{
459			return false;
460		}
461	}
462
463	/**
464	* Free sql result
465	*/
466	function sql_freeresult($query_id = false)
467	{
468		global $cache;
469
470		if ($query_id === false)
471		{
472			$query_id = $this->query_result;
473		}
474
475		if (isset($cache->sql_rowset[$query_id]))
476		{
477			return $cache->sql_freeresult($query_id);
478		}
479
480		if (isset($this->open_queries[$query_id]))
481		{
482			unset($this->open_queries[$query_id]);
483			return @sqlsrv_free_stmt($query_id);
484		}
485		return false;
486	}
487
488	/**
489	* Escape string used in sql query
490	*/
491	function sql_escape($msg)
492	{
493		return str_replace(array("'", "\0"), array("''", ''), $msg);
494	}
495
496	/**
497	* {@inheritDoc}
498	*/
499	function sql_lower_text($column_name)
500	{
501		return "LOWER(SUBSTRING($column_name, 1, DATALENGTH($column_name)))";
502	}
503
504	/**
505	* Build LIKE expression
506	* @access private
507	*/
508	function _sql_like_expression($expression)
509	{
510		return $expression . " ESCAPE '\\'";
511	}
512
513	/**
514	* return sql error array
515	* @access private
516	*/
517	function _sql_error()
518	{
519		if (function_exists('sqlsrv_errors'))
520		{
521			$errors = @sqlsrv_errors(SQLSRV_ERR_ERRORS);
522			$error_message = '';
523			$code = 0;
524
525			if ($errors != null)
526			{
527				foreach ($errors as $error)
528				{
529					$error_message .= "SQLSTATE: " . $error[ 'SQLSTATE'] . "\n";
530					$error_message .= "code: " . $error[ 'code'] . "\n";
531					$code = $error['code'];
532					$error_message .= "message: " . $error[ 'message'] . "\n";
533				}
534				$this->last_error_result = $error_message;
535				$error = $this->last_error_result;
536			}
537			else
538			{
539				$error = (isset($this->last_error_result) && $this->last_error_result) ? $this->last_error_result : array();
540			}
541
542			$error = array(
543				'message'	=> $error,
544				'code'		=> $code,
545			);
546		}
547		else
548		{
549			$error = array(
550				'message'	=> $this->connect_error,
551				'code'		=> '',
552			);
553		}
554
555		return $error;
556	}
557
558	/**
559	* Build db-specific query data
560	* @access private
561	*/
562	function _sql_custom_build($stage, $data)
563	{
564		return $data;
565	}
566
567	/**
568	* Close sql connection
569	* @access private
570	*/
571	function _sql_close()
572	{
573		return @sqlsrv_close($this->db_connect_id);
574	}
575
576	/**
577	* Build db-specific report
578	* @access private
579	*/
580	function _sql_report($mode, $query = '')
581	{
582		switch ($mode)
583		{
584			case 'start':
585				$html_table = false;
586				@sqlsrv_query($this->db_connect_id, 'SET SHOWPLAN_TEXT ON;');
587				if ($result = @sqlsrv_query($this->db_connect_id, $query))
588				{
589					@sqlsrv_next_result($result);
590					while ($row = @sqlsrv_fetch_array($result))
591					{
592						$html_table = $this->sql_report('add_select_row', $query, $html_table, $row);
593					}
594				}
595				@sqlsrv_query($this->db_connect_id, 'SET SHOWPLAN_TEXT OFF;');
596				@sqlsrv_free_stmt($result);
597
598				if ($html_table)
599				{
600					$this->html_hold .= '</table>';
601				}
602			break;
603
604			case 'fromcache':
605				$endtime = explode(' ', microtime());
606				$endtime = $endtime[0] + $endtime[1];
607
608				$result = @sqlsrv_query($this->db_connect_id, $query);
609				while ($void = @sqlsrv_fetch_array($result))
610				{
611					// Take the time spent on parsing rows into account
612				}
613				@sqlsrv_free_stmt($result);
614
615				$splittime = explode(' ', microtime());
616				$splittime = $splittime[0] + $splittime[1];
617
618				$this->sql_report('record_fromcache', $query, $endtime, $splittime);
619
620			break;
621		}
622	}
623
624	/**
625	* Utility method used to retrieve number of rows
626	* Emulates mysql_num_rows
627	* Used in acp_database.php -> write_data_mssqlnative()
628	* Requires a static or keyset cursor to be definde via
629	* mssqlnative_set_query_options()
630	*/
631	function mssqlnative_num_rows($res)
632	{
633		if ($res !== false)
634		{
635			return sqlsrv_num_rows($res);
636		}
637		else
638		{
639			return false;
640		}
641	}
642
643	/**
644	* Allows setting mssqlnative specific query options passed to sqlsrv_query as 4th parameter.
645	*/
646	function mssqlnative_set_query_options($options)
647	{
648		$this->query_options = $options;
649	}
650}
651
652?>
653