1<?php
2/**
3*
4* @package dbal
5* @version $Id$
6* @copyright (c) 2005 phpBB Group
7* @license http://opensource.org/licenses/gpl-license.php GNU Public License
8*
9*/
10
11/**
12* @ignore
13*/
14if (!defined('IN_PHPBB'))
15{
16	exit;
17}
18
19include_once($phpbb_root_path . 'includes/db/dbal.' . $phpEx);
20
21if (!class_exists('phpbb_error_collector'))
22{
23	include($phpbb_root_path . 'includes/error_collector.' . $phpEx);
24}
25
26/**
27* PostgreSQL Database Abstraction Layer
28* Minimum Requirement is Version 7.3+
29* @package dbal
30*/
31class dbal_postgres extends dbal
32{
33	var $last_query_text = '';
34	var $connect_error = '';
35
36	/**
37	* Connect to server
38	*/
39	function sql_connect($sqlserver, $sqluser, $sqlpassword, $database, $port = false, $persistency = false, $new_link = false)
40	{
41		$connect_string = '';
42
43		if ($sqluser)
44		{
45			$connect_string .= "user=$sqluser ";
46		}
47
48		if ($sqlpassword)
49		{
50			$connect_string .= "password=$sqlpassword ";
51		}
52
53		if ($sqlserver)
54		{
55			// $sqlserver can carry a port separated by : for compatibility reasons
56			// If $sqlserver has more than one : it's probably an IPv6 address.
57			// In this case we only allow passing a port via the $port variable.
58			if (substr_count($sqlserver, ':') === 1)
59			{
60				list($sqlserver, $port) = explode(':', $sqlserver);
61			}
62
63			if ($sqlserver !== 'localhost')
64			{
65				$connect_string .= "host=$sqlserver ";
66			}
67
68			if ($port)
69			{
70				$connect_string .= "port=$port ";
71			}
72		}
73
74		$schema = '';
75
76		if ($database)
77		{
78			$this->dbname = $database;
79			if (strpos($database, '.') !== false)
80			{
81				list($database, $schema) = explode('.', $database);
82			}
83			$connect_string .= "dbname=$database";
84		}
85
86		$this->persistency = $persistency;
87
88		if ($this->persistency)
89		{
90			if (!function_exists('pg_pconnect'))
91			{
92				$this->connect_error = 'pg_pconnect function does not exist, is pgsql extension installed?';
93				return $this->sql_error('');
94			}
95			$collector = new phpbb_error_collector;
96			$collector->install();
97			$this->db_connect_id = (!$new_link) ? @pg_pconnect($connect_string) : @pg_pconnect($connect_string, PGSQL_CONNECT_FORCE_NEW);
98		}
99		else
100		{
101			if (!function_exists('pg_connect'))
102			{
103				$this->connect_error = 'pg_connect function does not exist, is pgsql extension installed?';
104				return $this->sql_error('');
105			}
106			$collector = new phpbb_error_collector;
107			$collector->install();
108			$this->db_connect_id = (!$new_link) ? @pg_connect($connect_string) : @pg_connect($connect_string, PGSQL_CONNECT_FORCE_NEW);
109		}
110
111		$collector->uninstall();
112
113		if ($this->db_connect_id)
114		{
115			if (version_compare($this->sql_server_info(true), '8.2', '>='))
116			{
117				$this->multi_insert = true;
118			}
119
120			if ($schema !== '')
121			{
122				@pg_query($this->db_connect_id, 'SET search_path TO ' . $schema);
123			}
124			return $this->db_connect_id;
125		}
126
127		$this->connect_error = $collector->format_errors();
128		return $this->sql_error('');
129	}
130
131	/**
132	* Version information about used database
133	* @param bool $raw if true, only return the fetched sql_server_version
134	* @param bool $use_cache If true, it is safe to retrieve the value from the cache
135	* @return string sql server version
136	*/
137	function sql_server_info($raw = false, $use_cache = true)
138	{
139		global $cache;
140
141		if (!$use_cache || empty($cache) || ($this->sql_server_version = $cache->get('pgsql_version')) === false)
142		{
143			$query_id = @pg_query($this->db_connect_id, 'SELECT VERSION() AS version');
144			$row = @pg_fetch_assoc($query_id, null);
145			@pg_free_result($query_id);
146
147			$this->sql_server_version = (!empty($row['version'])) ? trim(substr($row['version'], 10)) : 0;
148
149			if (!empty($cache) && $use_cache)
150			{
151				$cache->put('pgsql_version', $this->sql_server_version);
152			}
153		}
154
155		return ($raw) ? $this->sql_server_version : 'PostgreSQL ' . $this->sql_server_version;
156	}
157
158	/**
159	* SQL Transaction
160	* @access private
161	*/
162	function _sql_transaction($status = 'begin')
163	{
164		switch ($status)
165		{
166			case 'begin':
167				return @pg_query($this->db_connect_id, 'BEGIN');
168			break;
169
170			case 'commit':
171				return @pg_query($this->db_connect_id, 'COMMIT');
172			break;
173
174			case 'rollback':
175				return @pg_query($this->db_connect_id, 'ROLLBACK');
176			break;
177		}
178
179		return true;
180	}
181
182	/**
183	* Base query method
184	*
185	* @param	string	$query		Contains the SQL query which shall be executed
186	* @param	int		$cache_ttl	Either 0 to avoid caching or the time in seconds which the result shall be kept in cache
187	* @return	mixed				When casted to bool the returned value returns true on success and false on failure
188	*
189	* @access	public
190	*/
191	function sql_query($query = '', $cache_ttl = 0)
192	{
193		if ($query != '')
194		{
195			global $cache;
196
197			// EXPLAIN only in extra debug mode
198			if (defined('DEBUG_EXTRA'))
199			{
200				$this->sql_report('start', $query);
201			}
202
203			$this->last_query_text = $query;
204			$this->query_result = ($cache_ttl && method_exists($cache, 'sql_load')) ? $cache->sql_load($query) : false;
205			$this->sql_add_num_queries($this->query_result);
206
207			if ($this->query_result === false)
208			{
209				if (($this->query_result = @pg_query($this->db_connect_id, $query)) === false)
210				{
211					$this->sql_error($query);
212				}
213
214				if (defined('DEBUG_EXTRA'))
215				{
216					$this->sql_report('stop', $query);
217				}
218
219				if ($cache_ttl && method_exists($cache, 'sql_save'))
220				{
221					$this->open_queries[(int) $this->query_result] = $this->query_result;
222					$cache->sql_save($query, $this->query_result, $cache_ttl);
223				}
224				else if (strpos($query, 'SELECT') === 0 && $this->query_result)
225				{
226					$this->open_queries[(int) $this->query_result] = $this->query_result;
227				}
228			}
229			else if (defined('DEBUG_EXTRA'))
230			{
231				$this->sql_report('fromcache', $query);
232			}
233		}
234		else
235		{
236			return false;
237		}
238
239		return $this->query_result;
240	}
241
242	/**
243	* Build db-specific query data
244	* @access private
245	*/
246	function _sql_custom_build($stage, $data)
247	{
248		return $data;
249	}
250
251	/**
252	* Build LIMIT query
253	*/
254	function _sql_query_limit($query, $total, $offset = 0, $cache_ttl = 0)
255	{
256		$this->query_result = false;
257
258		// if $total is set to 0 we do not want to limit the number of rows
259		if ($total == 0)
260		{
261			$total = 'ALL';
262		}
263
264		$query .= "\n LIMIT $total OFFSET $offset";
265
266		return $this->sql_query($query, $cache_ttl);
267	}
268
269	/**
270	* Return number of affected rows
271	*/
272	function sql_affectedrows()
273	{
274		return ($this->query_result) ? @pg_affected_rows($this->query_result) : false;
275	}
276
277	/**
278	* Fetch current row
279	*/
280	function sql_fetchrow($query_id = false)
281	{
282		global $cache;
283
284		if ($query_id === false)
285		{
286			$query_id = $this->query_result;
287		}
288
289		if (isset($cache->sql_rowset[$query_id]))
290		{
291			return $cache->sql_fetchrow($query_id);
292		}
293
294		return ($query_id !== false) ? @pg_fetch_assoc($query_id, null) : false;
295	}
296
297	/**
298	* Seek to given row number
299	* rownum is zero-based
300	*/
301	function sql_rowseek($rownum, &$query_id)
302	{
303		global $cache;
304
305		if ($query_id === false)
306		{
307			$query_id = $this->query_result;
308		}
309
310		if (isset($cache->sql_rowset[$query_id]))
311		{
312			return $cache->sql_rowseek($rownum, $query_id);
313		}
314
315		return ($query_id !== false) ? @pg_result_seek($query_id, $rownum) : false;
316	}
317
318	/**
319	* Get last inserted id after insert statement
320	*/
321	function sql_nextid()
322	{
323		$query_id = $this->query_result;
324
325		if ($query_id !== false && $this->last_query_text != '')
326		{
327			if (preg_match("/^INSERT[\t\n ]+INTO[\t\n ]+([a-z0-9\_\-]+)/is", $this->last_query_text, $tablename))
328			{
329				$query = "SELECT currval('" . $tablename[1] . "_seq') AS last_value";
330				$temp_q_id = @pg_query($this->db_connect_id, $query);
331
332				if (!$temp_q_id)
333				{
334					return false;
335				}
336
337				$temp_result = @pg_fetch_assoc($temp_q_id, NULL);
338				@pg_free_result($query_id);
339
340				return ($temp_result) ? $temp_result['last_value'] : false;
341			}
342		}
343
344		return false;
345	}
346
347	/**
348	* Free sql result
349	*/
350	function sql_freeresult($query_id = false)
351	{
352		global $cache;
353
354		if ($query_id === false)
355		{
356			$query_id = $this->query_result;
357		}
358
359		if (isset($cache->sql_rowset[$query_id]))
360		{
361			return $cache->sql_freeresult($query_id);
362		}
363
364		if (isset($this->open_queries[(int) $query_id]))
365		{
366			unset($this->open_queries[(int) $query_id]);
367			return @pg_free_result($query_id);
368		}
369
370		return false;
371	}
372
373	/**
374	* Escape string used in sql query
375	* Note: Do not use for bytea values if we may use them at a later stage
376	*/
377	function sql_escape($msg)
378	{
379		return @pg_escape_string($msg);
380	}
381
382	/**
383	* Build LIKE expression
384	* @access private
385	*/
386	function _sql_like_expression($expression)
387	{
388		return $expression;
389	}
390
391	/**
392	* return sql error array
393	* @access private
394	*/
395	function _sql_error()
396	{
397		// pg_last_error only works when there is an established connection.
398		// Connection errors have to be tracked by us manually.
399		if ($this->db_connect_id)
400		{
401			$message = @pg_last_error($this->db_connect_id);
402		}
403		else
404		{
405			$message = $this->connect_error;
406		}
407
408		return array(
409			'message'	=> $message,
410			'code'		=> ''
411		);
412	}
413
414	/**
415	* Close sql connection
416	* @access private
417	*/
418	function _sql_close()
419	{
420		return @pg_close($this->db_connect_id);
421	}
422
423	/**
424	* Build db-specific report
425	* @access private
426	*/
427	function _sql_report($mode, $query = '')
428	{
429		switch ($mode)
430		{
431			case 'start':
432
433				$explain_query = $query;
434				if (preg_match('/UPDATE ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
435				{
436					$explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
437				}
438				else if (preg_match('/DELETE FROM ([a-z0-9_]+).*?WHERE(.*)/s', $query, $m))
439				{
440					$explain_query = 'SELECT * FROM ' . $m[1] . ' WHERE ' . $m[2];
441				}
442
443				if (preg_match('/^SELECT/', $explain_query))
444				{
445					$html_table = false;
446
447					if ($result = @pg_query($this->db_connect_id, "EXPLAIN $explain_query"))
448					{
449						while ($row = @pg_fetch_assoc($result, NULL))
450						{
451							$html_table = $this->sql_report('add_select_row', $query, $html_table, $row);
452						}
453					}
454					@pg_free_result($result);
455
456					if ($html_table)
457					{
458						$this->html_hold .= '</table>';
459					}
460				}
461
462			break;
463
464			case 'fromcache':
465				$endtime = explode(' ', microtime());
466				$endtime = $endtime[0] + $endtime[1];
467
468				$result = @pg_query($this->db_connect_id, $query);
469				while ($void = @pg_fetch_assoc($result, NULL))
470				{
471					// Take the time spent on parsing rows into account
472				}
473				@pg_free_result($result);
474
475				$splittime = explode(' ', microtime());
476				$splittime = $splittime[0] + $splittime[1];
477
478				$this->sql_report('record_fromcache', $query, $endtime, $splittime);
479
480			break;
481		}
482	}
483}
484
485?>