/halogy/database/drivers/mssql/mssql_driver.php

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