PageRenderTime 362ms CodeModel.GetById 201ms app.highlight 18ms RepoModel.GetById 123ms app.codeStats 0ms

/halogy/database/drivers/oci8/oci8_driver.php

https://bitbucket.org/haloweb/halogy-1.0/
PHP | 780 lines | 299 code | 119 blank | 362 comment | 36 complexity | c0d1b430d4d30b2be066824acd04982f 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 * oci8 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 */
 31
 32/**
 33 * oci8 Database Adapter Class
 34 *
 35 * This is a modification of the DB_driver class to
 36 * permit access to oracle databases
 37 *
 38 * NOTE: this uses the PHP 4 oci methods
 39 *
 40 * @author	  Kelly McArdle
 41 *
 42 */
 43
 44class CI_DB_oci8_driver extends CI_DB {
 45
 46	var $dbdriver = 'oci8';
 47	
 48	// The character used for excaping
 49	var $_escape_char = '"';
 50	
 51	// clause and character used for LIKE escape sequences
 52	var $_like_escape_str = " escape '%s' ";
 53	var $_like_escape_chr = '!';
 54	
 55	/**
 56	 * The syntax to count rows is slightly different across different
 57	 * database engines, so this string appears in each driver and is
 58	 * used for the count_all() and count_all_results() functions.
 59	 */
 60	var $_count_string = "SELECT COUNT(1) AS ";
 61	var $_random_keyword = ' ASC'; // not currently supported
 62
 63	// Set "auto commit" by default
 64	var $_commit = OCI_COMMIT_ON_SUCCESS;
 65
 66	// need to track statement id and cursor id
 67	var $stmt_id;
 68	var $curs_id;
 69
 70	// if we use a limit, we will add a field that will
 71	// throw off num_fields later
 72	var $limit_used;
 73
 74	/**
 75	 * Non-persistent database connection
 76	 *
 77	 * @access  private called by the base class
 78	 * @return  resource
 79	 */
 80	function db_connect()
 81	{
 82		return @ocilogon($this->username, $this->password, $this->hostname);
 83	}
 84
 85	// --------------------------------------------------------------------
 86
 87	/**
 88	 * Persistent database connection
 89	 *
 90	 * @access  private called by the base class
 91	 * @return  resource
 92	 */
 93	function db_pconnect()
 94	{
 95		return @ociplogon($this->username, $this->password, $this->hostname);
 96	}
 97
 98	// --------------------------------------------------------------------
 99
100	/**
101	 * Reconnect
102	 *
103	 * Keep / reestablish the db connection if no queries have been
104	 * sent for a length of time exceeding the server's idle timeout
105	 *
106	 * @access	public
107	 * @return	void
108	 */
109	function reconnect()
110	{
111		// not implemented in oracle
112	}
113
114	// --------------------------------------------------------------------
115
116	/**
117	 * Select the database
118	 *
119	 * @access  private called by the base class
120	 * @return  resource
121	 */
122	function db_select()
123	{
124		return TRUE;
125	}
126
127	// --------------------------------------------------------------------
128
129	/**
130	 * Set client character set
131	 *
132	 * @access	public
133	 * @param	string
134	 * @param	string
135	 * @return	resource
136	 */
137	function db_set_charset($charset, $collation)
138	{
139		// @todo - add support if needed
140		return TRUE;
141	}
142
143	// --------------------------------------------------------------------
144	
145	/**
146	 * Version number query string
147	 *
148	 * @access  public
149	 * @return  string
150	 */
151	function _version()
152	{
153		return ociserverversion($this->conn_id);
154	}
155
156	// --------------------------------------------------------------------
157
158	/**
159	 * Execute the query
160	 *
161	 * @access  private called by the base class
162	 * @param   string  an SQL query
163	 * @return  resource
164	 */
165	function _execute($sql)
166	{
167		// oracle must parse the query before it is run. All of the actions with
168		// the query are based on the statement id returned by ociparse
169		$this->stmt_id = FALSE;
170		$this->_set_stmt_id($sql);
171		ocisetprefetch($this->stmt_id, 1000);
172		return @ociexecute($this->stmt_id, $this->_commit);
173	}
174
175	/**
176	 * Generate a statement ID
177	 *
178	 * @access  private
179	 * @param   string  an SQL query
180	 * @return  none
181	 */
182	function _set_stmt_id($sql)
183	{
184		if ( ! is_resource($this->stmt_id))
185		{
186			$this->stmt_id = ociparse($this->conn_id, $this->_prep_query($sql));
187		}
188	}
189
190	// --------------------------------------------------------------------
191
192	/**
193	 * Prep the query
194	 *
195	 * If needed, each database adapter can prep the query string
196	 *
197	 * @access  private called by execute()
198	 * @param   string  an SQL query
199	 * @return  string
200	 */
201	function _prep_query($sql)
202	{
203		return $sql;
204	}
205
206	// --------------------------------------------------------------------
207
208	/**
209	 * getCursor.  Returns a cursor from the datbase
210	 *
211	 * @access  public
212	 * @return  cursor id
213	 */
214	function get_cursor()
215	{
216		$this->curs_id = ocinewcursor($this->conn_id);
217		return $this->curs_id;
218	}
219
220	// --------------------------------------------------------------------
221
222	/**
223	 * Stored Procedure.  Executes a stored procedure
224	 *
225	 * @access  public
226	 * @param   package	 package stored procedure is in
227	 * @param   procedure   stored procedure to execute
228	 * @param   params	  array of parameters
229	 * @return  array
230	 *
231	 * params array keys
232	 *
233	 * KEY	  OPTIONAL	NOTES
234	 * name		no		the name of the parameter should be in :<param_name> format
235	 * value	no		the value of the parameter.  If this is an OUT or IN OUT parameter,
236	 *					this should be a reference to a variable
237	 * type		yes		the type of the parameter
238	 * length	yes		the max size of the parameter
239	 */
240	function stored_procedure($package, $procedure, $params)
241	{
242		if ($package == '' OR $procedure == '' OR ! is_array($params))
243		{
244			if ($this->db_debug)
245			{
246				log_message('error', 'Invalid query: '.$package.'.'.$procedure);
247				return $this->display_error('db_invalid_query');
248			}
249			return FALSE;
250		}
251		
252		// build the query string
253		$sql = "begin $package.$procedure(";
254
255		$have_cursor = FALSE;
256		foreach($params as $param)
257		{
258			$sql .= $param['name'] . ",";
259			
260			if (array_key_exists('type', $param) && ($param['type'] == OCI_B_CURSOR))
261			{
262				$have_cursor = TRUE;
263			}
264		}
265		$sql = trim($sql, ",") . "); end;";
266				
267		$this->stmt_id = FALSE;
268		$this->_set_stmt_id($sql);
269		$this->_bind_params($params);
270		$this->query($sql, FALSE, $have_cursor);
271	}
272	
273	// --------------------------------------------------------------------
274
275	/**
276	 * Bind parameters
277	 *
278	 * @access  private
279	 * @return  none
280	 */
281	function _bind_params($params)
282	{
283		if ( ! is_array($params) OR ! is_resource($this->stmt_id))
284		{
285			return;
286		}
287		
288		foreach ($params as $param)
289		{
290 			foreach (array('name', 'value', 'type', 'length') as $val)
291			{
292				if ( ! isset($param[$val]))
293				{
294					$param[$val] = '';
295				}
296			}
297
298			ocibindbyname($this->stmt_id, $param['name'], $param['value'], $param['length'], $param['type']);
299		}
300	}
301
302	// --------------------------------------------------------------------
303
304	/**
305	 * Begin Transaction
306	 *
307	 * @access	public
308	 * @return	bool		
309	 */	
310	function trans_begin($test_mode = FALSE)
311	{
312		if ( ! $this->trans_enabled)
313		{
314			return TRUE;
315		}
316		
317		// When transactions are nested we only begin/commit/rollback the outermost ones
318		if ($this->_trans_depth > 0)
319		{
320			return TRUE;
321		}
322		
323		// Reset the transaction failure flag.
324		// If the $test_mode flag is set to TRUE transactions will be rolled back
325		// even if the queries produce a successful result.
326		$this->_trans_failure = ($test_mode === TRUE) ? TRUE : FALSE;
327		
328		$this->_commit = OCI_DEFAULT;
329		return TRUE;
330	}
331
332	// --------------------------------------------------------------------
333
334	/**
335	 * Commit Transaction
336	 *
337	 * @access	public
338	 * @return	bool		
339	 */	
340	function trans_commit()
341	{
342		if ( ! $this->trans_enabled)
343		{
344			return TRUE;
345		}
346
347		// When transactions are nested we only begin/commit/rollback the outermost ones
348		if ($this->_trans_depth > 0)
349		{
350			return TRUE;
351		}
352
353		$ret = OCIcommit($this->conn_id);
354		$this->_commit = OCI_COMMIT_ON_SUCCESS;
355		return $ret;
356	}
357
358	// --------------------------------------------------------------------
359
360	/**
361	 * Rollback Transaction
362	 *
363	 * @access	public
364	 * @return	bool		
365	 */	
366	function trans_rollback()
367	{
368		if ( ! $this->trans_enabled)
369		{
370			return TRUE;
371		}
372
373		// When transactions are nested we only begin/commit/rollback the outermost ones
374		if ($this->_trans_depth > 0)
375		{
376			return TRUE;
377		}
378
379		$ret = OCIrollback($this->conn_id);
380		$this->_commit = OCI_COMMIT_ON_SUCCESS;
381		return $ret;
382	}
383
384	// --------------------------------------------------------------------
385
386	/**
387	 * Escape String
388	 *
389	 * @access  public
390	 * @param   string
391	 * @param	bool	whether or not the string will be used in a LIKE condition
392	 * @return  string
393	 */
394	function escape_str($str, $like = FALSE)
395	{
396		if (is_array($str))
397		{
398			foreach($str as $key => $val)
399	   		{
400				$str[$key] = $this->escape_str($val, $like);
401	   		}
402   		
403	   		return $str;
404	   	}
405
406		// Access the CI object
407		$CI =& get_instance();
408
409		$str = $CI->input->_remove_invisible_characters($str);
410		
411		// escape LIKE condition wildcards
412		if ($like === TRUE)
413		{
414			$str = str_replace(	array('%', '_', $this->_like_escape_chr),
415								array($this->_like_escape_chr.'%', $this->_like_escape_chr.'_', $this->_like_escape_chr.$this->_like_escape_chr),
416								$str);
417		}
418		
419		return $str;
420	}
421
422	// --------------------------------------------------------------------
423
424	/**
425	 * Affected Rows
426	 *
427	 * @access  public
428	 * @return  integer
429	 */
430	function affected_rows()
431	{
432		return @ocirowcount($this->stmt_id);
433	}
434
435	// --------------------------------------------------------------------
436
437	/**
438	 * Insert ID
439	 *
440	 * @access  public
441	 * @return  integer
442	 */
443	function insert_id()
444	{
445		// not supported in oracle
446		return $this->display_error('db_unsupported_function');
447	}
448
449	// --------------------------------------------------------------------
450
451	/**
452	 * "Count All" query
453	 *
454	 * Generates a platform-specific query string that counts all records in
455	 * the specified database
456	 *
457	 * @access  public
458	 * @param   string
459	 * @return  string
460	 */
461	function count_all($table = '')
462	{
463		if ($table == '')
464		{
465			return 0;
466		}
467
468		$query = $this->query($this->_count_string . $this->_protect_identifiers('numrows') . " FROM " . $this->_protect_identifiers($table, TRUE, NULL, FALSE));
469
470		if ($query == FALSE)
471		{
472			return 0;
473		}
474
475		$row = $query->row();
476		return (int) $row->numrows;
477	}
478
479	// --------------------------------------------------------------------
480
481	/**
482	 * Show table query
483	 *
484	 * Generates a platform-specific query string so that the table names can be fetched
485	 *
486	 * @access  private
487	 * @param	boolean
488	 * @return  string
489	 */
490	function _list_tables($prefix_limit = FALSE)
491	{
492		$sql = "SELECT TABLE_NAME FROM ALL_TABLES";
493
494		if ($prefix_limit !== FALSE AND $this->dbprefix != '')
495		{
496			$sql .= " WHERE TABLE_NAME LIKE '".$this->escape_like_str($this->dbprefix)."%' ".sprintf($this->_like_escape_str, $this->_like_escape_char);
497		}
498		
499		return $sql;
500	}
501
502	// --------------------------------------------------------------------
503
504	/**
505	 * Show column query
506	 *
507	 * Generates a platform-specific query string so that the column names can be fetched
508	 *
509	 * @access  public
510	 * @param   string  the table name
511	 * @return  string
512	 */
513	function _list_columns($table = '')
514	{
515		return "SELECT COLUMN_NAME FROM all_tab_columns WHERE table_name = '$table'";
516	}
517
518	// --------------------------------------------------------------------
519
520	/**
521	 * Field data query
522	 *
523	 * Generates a platform-specific query so that the column data can be retrieved
524	 *
525	 * @access  public
526	 * @param   string  the table name
527	 * @return  object
528	 */
529	function _field_data($table)
530	{
531		return "SELECT * FROM ".$table." where rownum = 1";
532	}
533
534	// --------------------------------------------------------------------
535
536	/**
537	 * The error message string
538	 *
539	 * @access  private
540	 * @return  string
541	 */
542	function _error_message()
543	{
544		$error = ocierror($this->conn_id);
545		return $error['message'];
546	}
547
548	// --------------------------------------------------------------------
549
550	/**
551	 * The error message number
552	 *
553	 * @access  private
554	 * @return  integer
555	 */
556	function _error_number()
557	{
558		$error = ocierror($this->conn_id);
559		return $error['code'];
560	}
561	
562	// --------------------------------------------------------------------
563
564	/**
565	 * Escape the SQL Identifiers
566	 *
567	 * This function escapes column and table names
568	 *
569	 * @access	private
570	 * @param	string
571	 * @return	string
572	 */
573	function _escape_identifiers($item)
574	{
575		if ($this->_escape_char == '')
576		{
577			return $item;
578		}
579
580		foreach ($this->_reserved_identifiers as $id)
581		{
582			if (strpos($item, '.'.$id) !== FALSE)
583			{
584				$str = $this->_escape_char. str_replace('.', $this->_escape_char.'.', $item);  
585				
586				// remove duplicates if the user already included the escape
587				return preg_replace('/['.$this->_escape_char.']+/', $this->_escape_char, $str);
588			}		
589		}
590	
591		if (strpos($item, '.') !== FALSE)
592		{
593			$str = $this->_escape_char.str_replace('.', $this->_escape_char.'.'.$this->_escape_char, $item).$this->_escape_char;			
594		}
595		else
596		{
597			$str = $this->_escape_char.$item.$this->_escape_char;
598		}
599		
600		// remove duplicates if the user already included the escape
601		return preg_replace('/['.$this->_escape_char.']+/', $this->_escape_char, $str);
602	}
603	
604	// --------------------------------------------------------------------
605
606	/**
607	 * From Tables
608	 *
609	 * This function implicitly groups FROM tables so there is no confusion
610	 * about operator precedence in harmony with SQL standards
611	 *
612	 * @access	public
613	 * @param	type
614	 * @return	type
615	 */
616	function _from_tables($tables)
617	{
618		if ( ! is_array($tables))
619		{
620			$tables = array($tables);
621		}
622		
623		return implode(', ', $tables);
624	}
625
626	// --------------------------------------------------------------------
627	
628	/**
629	 * Insert statement
630	 *
631	 * Generates a platform-specific insert string from the supplied data
632	 *
633	 * @access  public
634	 * @param   string  the table name
635	 * @param   array   the insert keys
636	 * @param   array   the insert values
637	 * @return  string
638	 */
639	function _insert($table, $keys, $values)
640	{
641	return "INSERT INTO ".$table." (".implode(', ', $keys).") VALUES (".implode(', ', $values).")";
642	}
643
644	// --------------------------------------------------------------------
645
646	/**
647	 * Update statement
648	 *
649	 * Generates a platform-specific update string from the supplied data
650	 *
651	 * @access	public
652	 * @param	string	the table name
653	 * @param	array	the update data
654	 * @param	array	the where clause
655	 * @param	array	the orderby clause
656	 * @param	array	the limit clause
657	 * @return	string
658	 */
659	function _update($table, $values, $where, $orderby = array(), $limit = FALSE)
660	{
661		foreach($values as $key => $val)
662		{
663			$valstr[] = $key." = ".$val;
664		}
665		
666		$limit = ( ! $limit) ? '' : ' LIMIT '.$limit;
667		
668		$orderby = (count($orderby) >= 1)?' ORDER BY '.implode(", ", $orderby):'';
669	
670		$sql = "UPDATE ".$table." SET ".implode(', ', $valstr);
671
672		$sql .= ($where != '' AND count($where) >=1) ? " WHERE ".implode(" ", $where) : '';
673
674		$sql .= $orderby.$limit;
675		
676		return $sql;
677	}
678
679	// --------------------------------------------------------------------
680
681	/**
682	 * Truncate statement
683	 *
684	 * Generates a platform-specific truncate string from the supplied data
685	 * If the database does not support the truncate() command
686	 * This function maps to "DELETE FROM table"
687	 *
688	 * @access	public
689	 * @param	string	the table name
690	 * @return	string
691	 */	
692	function _truncate($table)
693	{
694		return "TRUNCATE TABLE ".$table;
695	}
696	
697	// --------------------------------------------------------------------
698
699	/**
700	 * Delete statement
701	 *
702	 * Generates a platform-specific delete string from the supplied data
703	 *
704	 * @access	public
705	 * @param	string	the table name
706	 * @param	array	the where clause
707	 * @param	string	the limit clause
708	 * @return	string
709	 */	
710	function _delete($table, $where = array(), $like = array(), $limit = FALSE)
711	{
712		$conditions = '';
713
714		if (count($where) > 0 OR count($like) > 0)
715		{
716			$conditions = "\nWHERE ";
717			$conditions .= implode("\n", $this->ar_where);
718
719			if (count($where) > 0 && count($like) > 0)
720			{
721				$conditions .= " AND ";
722			}
723			$conditions .= implode("\n", $like);
724		}
725
726		$limit = ( ! $limit) ? '' : ' LIMIT '.$limit;
727	
728		return "DELETE FROM ".$table.$conditions.$limit;
729	}
730
731	// --------------------------------------------------------------------
732
733	/**
734	 * Limit string
735	 *
736	 * Generates a platform-specific LIMIT clause
737	 *
738	 * @access  public
739	 * @param   string  the sql query string
740	 * @param   integer the number of rows to limit the query to
741	 * @param   integer the offset value
742	 * @return  string
743	 */
744	function _limit($sql, $limit, $offset)
745	{
746		$limit = $offset + $limit;
747		$newsql = "SELECT * FROM (select inner_query.*, rownum rnum FROM ($sql) inner_query WHERE rownum < $limit)";
748
749		if ($offset != 0)
750		{
751			$newsql .= " WHERE rnum >= $offset";
752		}
753
754		// remember that we used limits
755		$this->limit_used = TRUE;
756
757		return $newsql;
758	}	
759
760	// --------------------------------------------------------------------
761
762	/**
763	 * Close DB Connection
764	 *
765	 * @access  public
766	 * @param   resource
767	 * @return  void
768	 */
769	function _close($conn_id)
770	{
771		@ocilogoff($conn_id);
772	}
773
774
775}
776
777
778
779/* End of file oci8_driver.php */
780/* Location: ./system/database/drivers/oci8/oci8_driver.php */