PageRenderTime 407ms CodeModel.GetById 201ms app.highlight 17ms RepoModel.GetById 184ms app.codeStats 0ms

/halogy/database/drivers/postgre/postgre_driver.php

https://bitbucket.org/haloweb/halogy-1.0/
PHP | 684 lines | 274 code | 102 blank | 308 comment | 37 complexity | 8039b1ac13a83683474b2ff98fc25a24 MD5 | raw file
  1<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');
  2/**
  3 * CodeIgniter
  4 *
  5 * An open source application development framework for PHP 4.3.2 or newer
  6 *
  7 * @package		CodeIgniter
  8 * @author		ExpressionEngine Dev Team
  9 * @copyright	Copyright (c) 2008 - 2009, EllisLab, Inc.
 10 * @license		http://codeigniter.com/user_guide/license.html
 11 * @link		http://codeigniter.com
 12 * @since		Version 1.0
 13 * @filesource
 14 */
 15
 16// ------------------------------------------------------------------------
 17
 18/**
 19 * Postgre Database Adapter Class
 20 *
 21 * Note: _DB is an extender class that the app controller
 22 * creates dynamically based on whether the active record
 23 * class is being used or not.
 24 *
 25 * @package		CodeIgniter
 26 * @subpackage	Drivers
 27 * @category	Database
 28 * @author		ExpressionEngine Dev Team
 29 * @link		http://codeigniter.com/user_guide/database/
 30 */
 31class CI_DB_postgre_driver extends CI_DB {
 32
 33	var $dbdriver = 'postgre';
 34	
 35	var $_escape_char = '"';
 36
 37	// clause and character used for LIKE escape sequences
 38	var $_like_escape_str = " ESCAPE '%s' ";
 39	var $_like_escape_chr = '!';
 40
 41	/**
 42	 * The syntax to count rows is slightly different across different
 43	 * database engines, so this string appears in each driver and is
 44	 * used for the count_all() and count_all_results() functions.
 45	 */
 46	var $_count_string = "SELECT COUNT(*) AS ";
 47	var $_random_keyword = ' RANDOM()'; // database specific random keyword
 48
 49	/**
 50	 * Connection String
 51	 *
 52	 * @access	private
 53	 * @return	string
 54	 */	
 55	function _connect_string()
 56	{
 57		$components = array(
 58								'hostname'	=> 'host',
 59								'port'		=> 'port',
 60								'database'	=> 'dbname',
 61								'username'	=> 'user',
 62								'password'	=> 'password'
 63							);
 64		
 65		$connect_string = "";
 66		foreach ($components as $key => $val)
 67		{
 68			if (isset($this->$key) && $this->$key != '')
 69			{
 70				$connect_string .= " $val=".$this->$key;
 71			}
 72		}
 73		return trim($connect_string);
 74	}
 75
 76	// --------------------------------------------------------------------
 77
 78	/**
 79	 * Non-persistent database connection
 80	 *
 81	 * @access	private called by the base class
 82	 * @return	resource
 83	 */	
 84	function db_connect()
 85	{		
 86		return @pg_connect($this->_connect_string());
 87	}
 88
 89	// --------------------------------------------------------------------
 90
 91	/**
 92	 * Persistent database connection
 93	 *
 94	 * @access	private called by the base class
 95	 * @return	resource
 96	 */	
 97	function db_pconnect()
 98	{
 99		return @pg_pconnect($this->_connect_string());
100	}
101	
102	// --------------------------------------------------------------------
103
104	/**
105	 * Reconnect
106	 *
107	 * Keep / reestablish the db connection if no queries have been
108	 * sent for a length of time exceeding the server's idle timeout
109	 *
110	 * @access	public
111	 * @return	void
112	 */
113	function reconnect()
114	{
115		if (pg_ping($this->conn_id) === FALSE)
116		{
117			$this->conn_id = FALSE;
118		}
119	}
120
121	// --------------------------------------------------------------------
122
123	/**
124	 * Select the database
125	 *
126	 * @access	private called by the base class
127	 * @return	resource
128	 */	
129	function db_select()
130	{
131		// Not needed for Postgre so we'll return TRUE
132		return TRUE;
133	}
134
135	// --------------------------------------------------------------------
136
137	/**
138	 * Set client character set
139	 *
140	 * @access	public
141	 * @param	string
142	 * @param	string
143	 * @return	resource
144	 */
145	function db_set_charset($charset, $collation)
146	{
147		// @todo - add support if needed
148		return TRUE;
149	}
150
151	// --------------------------------------------------------------------
152	
153	/**
154	 * Version number query string
155	 *
156	 * @access	public
157	 * @return	string
158	 */
159	function _version()
160	{
161		return "SELECT version() AS ver";
162	}
163
164	// --------------------------------------------------------------------
165
166	/**
167	 * Execute the query
168	 *
169	 * @access	private called by the base class
170	 * @param	string	an SQL query
171	 * @return	resource
172	 */	
173	function _execute($sql)
174	{
175		$sql = $this->_prep_query($sql);
176		return @pg_query($this->conn_id, $sql);
177	}
178	
179	// --------------------------------------------------------------------
180
181	/**
182	 * Prep the query
183	 *
184	 * If needed, each database adapter can prep the query string
185	 *
186	 * @access	private called by execute()
187	 * @param	string	an SQL query
188	 * @return	string
189	 */	
190	function _prep_query($sql)
191	{
192		return $sql;
193	}
194
195	// --------------------------------------------------------------------
196
197	/**
198	 * Begin Transaction
199	 *
200	 * @access	public
201	 * @return	bool		
202	 */	
203	function trans_begin($test_mode = FALSE)
204	{
205		if ( ! $this->trans_enabled)
206		{
207			return TRUE;
208		}
209		
210		// When transactions are nested we only begin/commit/rollback the outermost ones
211		if ($this->_trans_depth > 0)
212		{
213			return TRUE;
214		}
215
216		// Reset the transaction failure flag.
217		// If the $test_mode flag is set to TRUE transactions will be rolled back
218		// even if the queries produce a successful result.
219		$this->_trans_failure = ($test_mode === TRUE) ? TRUE : FALSE;
220
221		return @pg_exec($this->conn_id, "begin");
222	}
223
224	// --------------------------------------------------------------------
225
226	/**
227	 * Commit Transaction
228	 *
229	 * @access	public
230	 * @return	bool		
231	 */	
232	function trans_commit()
233	{
234		if ( ! $this->trans_enabled)
235		{
236			return TRUE;
237		}
238
239		// When transactions are nested we only begin/commit/rollback the outermost ones
240		if ($this->_trans_depth > 0)
241		{
242			return TRUE;
243		}
244
245		return @pg_exec($this->conn_id, "commit");
246	}
247
248	// --------------------------------------------------------------------
249
250	/**
251	 * Rollback Transaction
252	 *
253	 * @access	public
254	 * @return	bool		
255	 */	
256	function trans_rollback()
257	{
258		if ( ! $this->trans_enabled)
259		{
260			return TRUE;
261		}
262
263		// When transactions are nested we only begin/commit/rollback the outermost ones
264		if ($this->_trans_depth > 0)
265		{
266			return TRUE;
267		}
268
269		return @pg_exec($this->conn_id, "rollback");
270	}
271
272	// --------------------------------------------------------------------
273
274	/**
275	 * Escape String
276	 *
277	 * @access	public
278	 * @param	string
279	 * @param	bool	whether or not the string will be used in a LIKE condition
280	 * @return	string
281	 */
282	function escape_str($str, $like = FALSE)
283	{
284		if (is_array($str))
285		{
286			foreach($str as $key => $val)
287	   		{
288				$str[$key] = $this->escape_str($val, $like);
289	   		}
290   		
291	   		return $str;
292	   	}
293
294		$str = pg_escape_string($str);
295		
296		// escape LIKE condition wildcards
297		if ($like === TRUE)
298		{
299			$str = str_replace(	array('%', '_', $this->_like_escape_chr),
300								array($this->_like_escape_chr.'%', $this->_like_escape_chr.'_', $this->_like_escape_chr.$this->_like_escape_chr),
301								$str);
302		}
303		
304		return $str;
305	}
306		
307	// --------------------------------------------------------------------
308
309	/**
310	 * Affected Rows
311	 *
312	 * @access	public
313	 * @return	integer
314	 */
315	function affected_rows()
316	{
317		return @pg_affected_rows($this->result_id);
318	}
319	
320	// --------------------------------------------------------------------
321
322	/**
323	 * Insert ID
324	 *
325	 * @access	public
326	 * @return	integer
327	 */
328	function insert_id()
329	{
330		$v = $this->_version();
331		$v = $v['server'];
332		
333		$table	= func_num_args() > 0 ? func_get_arg(0) : null;
334		$column	= func_num_args() > 1 ? func_get_arg(1) : null;
335		
336		if ($table == null && $v >= '8.1')
337		{
338			$sql='SELECT LASTVAL() as ins_id';
339		}
340		elseif ($table != null && $column != null && $v >= '8.0')
341		{
342			$sql = sprintf("SELECT pg_get_serial_sequence('%s','%s') as seq", $table, $column);
343			$query = $this->query($sql);
344			$row = $query->row();
345			$sql = sprintf("SELECT CURRVAL('%s') as ins_id", $row->seq);
346		}
347		elseif ($table != null)
348		{
349			// seq_name passed in table parameter
350			$sql = sprintf("SELECT CURRVAL('%s') as ins_id", $table);
351		}
352		else
353		{
354			return pg_last_oid($this->result_id);
355		}
356		$query = $this->query($sql);
357		$row = $query->row();
358		return $row->ins_id;
359	}
360
361	// --------------------------------------------------------------------
362
363	/**
364	 * "Count All" query
365	 *
366	 * Generates a platform-specific query string that counts all records in
367	 * the specified database
368	 *
369	 * @access	public
370	 * @param	string
371	 * @return	string
372	 */
373	function count_all($table = '')
374	{
375		if ($table == '')
376		{
377			return 0;
378		}
379
380		$query = $this->query($this->_count_string . $this->_protect_identifiers('numrows') . " FROM " . $this->_protect_identifiers($table, TRUE, NULL, FALSE));
381
382		if ($query->num_rows() == 0)
383		{
384			return 0;
385		}
386
387		$row = $query->row();
388		return (int) $row->numrows;
389	}
390
391	// --------------------------------------------------------------------
392
393	/**
394	 * Show table query
395	 *
396	 * Generates a platform-specific query string so that the table names can be fetched
397	 *
398	 * @access	private
399	 * @param	boolean
400	 * @return	string
401	 */
402	function _list_tables($prefix_limit = FALSE)
403	{	
404		$sql = "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'";	
405		
406		if ($prefix_limit !== FALSE AND $this->dbprefix != '')
407		{
408			$sql .= " AND table_name LIKE '".$this->escape_like_str($this->dbprefix)."%' ".sprintf($this->_like_escape_str, $this->_like_escape_char);
409		}
410		
411		return $sql;
412	}
413	
414	// --------------------------------------------------------------------
415
416	/**
417	 * Show column query
418	 *
419	 * Generates a platform-specific query string so that the column names can be fetched
420	 *
421	 * @access	public
422	 * @param	string	the table name
423	 * @return	string
424	 */
425	function _list_columns($table = '')
426	{
427		return "SELECT column_name FROM information_schema.columns WHERE table_name ='".$table."'";
428	}
429
430	// --------------------------------------------------------------------
431
432	/**
433	 * Field data query
434	 *
435	 * Generates a platform-specific query so that the column data can be retrieved
436	 *
437	 * @access	public
438	 * @param	string	the table name
439	 * @return	object
440	 */
441	function _field_data($table)
442	{
443		return "SELECT * FROM ".$table." LIMIT 1";
444	}
445
446	// --------------------------------------------------------------------
447
448	/**
449	 * The error message string
450	 *
451	 * @access	private
452	 * @return	string
453	 */
454	function _error_message()
455	{
456		return pg_last_error($this->conn_id);
457	}
458	
459	// --------------------------------------------------------------------
460
461	/**
462	 * The error message number
463	 *
464	 * @access	private
465	 * @return	integer
466	 */
467	function _error_number()
468	{
469		return '';
470	}
471
472	// --------------------------------------------------------------------
473
474	/**
475	 * Escape the SQL Identifiers
476	 *
477	 * This function escapes column and table names
478	 *
479	 * @access	private
480	 * @param	string
481	 * @return	string
482	 */
483	function _escape_identifiers($item)
484	{
485		if ($this->_escape_char == '')
486		{
487			return $item;
488		}
489
490		foreach ($this->_reserved_identifiers as $id)
491		{
492			if (strpos($item, '.'.$id) !== FALSE)
493			{
494				$str = $this->_escape_char. str_replace('.', $this->_escape_char.'.', $item);  
495				
496				// remove duplicates if the user already included the escape
497				return preg_replace('/['.$this->_escape_char.']+/', $this->_escape_char, $str);
498			}		
499		}
500	
501		if (strpos($item, '.') !== FALSE)
502		{
503			$str = $this->_escape_char.str_replace('.', $this->_escape_char.'.'.$this->_escape_char, $item).$this->_escape_char;			
504		}
505		else
506		{
507			$str = $this->_escape_char.$item.$this->_escape_char;
508		}
509		
510		// remove duplicates if the user already included the escape
511		return preg_replace('/['.$this->_escape_char.']+/', $this->_escape_char, $str);
512	}
513			
514	// --------------------------------------------------------------------
515
516	/**
517	 * From Tables
518	 *
519	 * This function implicitly groups FROM tables so there is no confusion
520	 * about operator precedence in harmony with SQL standards
521	 *
522	 * @access	public
523	 * @param	type
524	 * @return	type
525	 */
526	function _from_tables($tables)
527	{
528		if ( ! is_array($tables))
529		{
530			$tables = array($tables);
531		}
532		
533		return implode(', ', $tables);
534	}
535
536	// --------------------------------------------------------------------
537	
538	/**
539	 * Insert statement
540	 *
541	 * Generates a platform-specific insert string from the supplied data
542	 *
543	 * @access	public
544	 * @param	string	the table name
545	 * @param	array	the insert keys
546	 * @param	array	the insert values
547	 * @return	string
548	 */
549	function _insert($table, $keys, $values)
550	{	
551		return "INSERT INTO ".$table." (".implode(', ', $keys).") VALUES (".implode(', ', $values).")";
552	}
553	
554	// --------------------------------------------------------------------
555
556	/**
557	 * Update statement
558	 *
559	 * Generates a platform-specific update string from the supplied data
560	 *
561	 * @access	public
562	 * @param	string	the table name
563	 * @param	array	the update data
564	 * @param	array	the where clause
565	 * @param	array	the orderby clause
566	 * @param	array	the limit clause
567	 * @return	string
568	 */
569	function _update($table, $values, $where, $orderby = array(), $limit = FALSE)
570	{
571		foreach($values as $key => $val)
572		{
573			$valstr[] = $key." = ".$val;
574		}
575		
576		$limit = ( ! $limit) ? '' : ' LIMIT '.$limit;
577		
578		$orderby = (count($orderby) >= 1)?' ORDER BY '.implode(", ", $orderby):'';
579	
580		$sql = "UPDATE ".$table." SET ".implode(', ', $valstr);
581
582		$sql .= ($where != '' AND count($where) >=1) ? " WHERE ".implode(" ", $where) : '';
583
584		$sql .= $orderby.$limit;
585		
586		return $sql;
587	}
588
589	// --------------------------------------------------------------------
590
591	/**
592	 * Truncate statement
593	 *
594	 * Generates a platform-specific truncate string from the supplied data
595	 * If the database does not support the truncate() command
596	 * This function maps to "DELETE FROM table"
597	 *
598	 * @access	public
599	 * @param	string	the table name
600	 * @return	string
601	 */	
602	function _truncate($table)
603	{
604		return "TRUNCATE ".$table;
605	}
606	
607	// --------------------------------------------------------------------
608
609	/**
610	 * Delete statement
611	 *
612	 * Generates a platform-specific delete string from the supplied data
613	 *
614	 * @access	public
615	 * @param	string	the table name
616	 * @param	array	the where clause
617	 * @param	string	the limit clause
618	 * @return	string
619	 */	
620	function _delete($table, $where = array(), $like = array(), $limit = FALSE)
621	{
622		$conditions = '';
623
624		if (count($where) > 0 OR count($like) > 0)
625		{
626			$conditions = "\nWHERE ";
627			$conditions .= implode("\n", $this->ar_where);
628
629			if (count($where) > 0 && count($like) > 0)
630			{
631				$conditions .= " AND ";
632			}
633			$conditions .= implode("\n", $like);
634		}
635
636		$limit = ( ! $limit) ? '' : ' LIMIT '.$limit;
637	
638		return "DELETE FROM ".$table.$conditions.$limit;
639	}
640
641	// --------------------------------------------------------------------
642	/**
643	 * Limit string
644	 *
645	 * Generates a platform-specific LIMIT clause
646	 *
647	 * @access	public
648	 * @param	string	the sql query string
649	 * @param	integer	the number of rows to limit the query to
650	 * @param	integer	the offset value
651	 * @return	string
652	 */
653	function _limit($sql, $limit, $offset)
654	{	
655		$sql .= "LIMIT ".$limit;
656	
657		if ($offset > 0)
658		{
659			$sql .= " OFFSET ".$offset;
660		}
661		
662		return $sql;
663	}
664
665	// --------------------------------------------------------------------
666
667	/**
668	 * Close DB Connection
669	 *
670	 * @access	public
671	 * @param	resource
672	 * @return	void
673	 */
674	function _close($conn_id)
675	{
676		@pg_close($conn_id);
677	}
678
679
680}
681
682
683/* End of file postgre_driver.php */
684/* Location: ./system/database/drivers/postgre/postgre_driver.php */