blob: 45bf77149491261779551d2f69ee50078896c28e [file] [log] [blame]
Derek Jones0b59f272008-05-13 04:22:33 +00001<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
Derek Allardd2df9bc2007-04-15 17:41:17 +00002/**
3 * CodeIgniter
4 *
5 * An open source application development framework for PHP 4.3.2 or newer
6 *
7 * @package CodeIgniter
Derek Allard3d879d52008-01-18 19:41:32 +00008 * @author ExpressionEngine Dev Team
Rick Ellis9ba2bf22008-09-12 23:34:39 +00009 * @copyright Copyright (c) 2008, EllisLab, Inc.
Derek Jones7a9193a2008-01-21 18:39:20 +000010 * @license http://codeigniter.com/user_guide/license.html
11 * @link http://codeigniter.com
Derek Allardd2df9bc2007-04-15 17:41:17 +000012 * @since Version 1.0
13 * @filesource
14 */
15
16// ------------------------------------------------------------------------
17
18/**
19 * MySQL 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
Derek Allard3d879d52008-01-18 19:41:32 +000028 * @author ExpressionEngine Dev Team
Derek Jones7a9193a2008-01-21 18:39:20 +000029 * @link http://codeigniter.com/user_guide/database/
Derek Allardd2df9bc2007-04-15 17:41:17 +000030 */
31class CI_DB_mysql_driver extends CI_DB {
32
Rick Ellis5aa8c602008-10-07 01:24:07 +000033 var $dbdriver = 'mysql';
34
Rick Ellis605a07a2008-10-17 04:07:54 +000035 // The character used for escaping
36 var $_escape_char = '`';
37
Derek Allardd2df9bc2007-04-15 17:41:17 +000038 /**
39 * Whether to use the MySQL "delete hack" which allows the number
40 * of affected rows to be shown. Uses a preg_replace when enabled,
41 * adding a bit more processing to all queries.
42 */
43 var $delete_hack = TRUE;
Derek Allard6ddb5a12007-12-18 17:22:50 +000044
45 /**
46 * The syntax to count rows is slightly different across different
47 * database engines, so this string appears in each driver and is
48 * used for the count_all() and count_all_results() functions.
49 */
Derek Allard39b622d2008-01-16 21:10:09 +000050 var $_count_string = 'SELECT COUNT(*) AS ';
Derek Allard6ddb5a12007-12-18 17:22:50 +000051 var $_random_keyword = ' RAND()'; // database specific random keyword
Derek Allardd2df9bc2007-04-15 17:41:17 +000052
53 /**
54 * Non-persistent database connection
55 *
56 * @access private called by the base class
57 * @return resource
58 */
59 function db_connect()
60 {
Rick Ellisd22e53b2008-10-17 22:30:31 +000061 if ($this->port != '')
62 {
63 $this->hostname .= ':'.$this->port;
64 }
65
Derek Allardd2df9bc2007-04-15 17:41:17 +000066 return @mysql_connect($this->hostname, $this->username, $this->password, TRUE);
67 }
68
69 // --------------------------------------------------------------------
70
71 /**
72 * Persistent database connection
73 *
74 * @access private called by the base class
75 * @return resource
76 */
77 function db_pconnect()
78 {
Rick Ellisd22e53b2008-10-17 22:30:31 +000079 if ($this->port != '')
80 {
81 $this->hostname .= ':'.$this->port;
82 }
83
Derek Allardd2df9bc2007-04-15 17:41:17 +000084 return @mysql_pconnect($this->hostname, $this->username, $this->password);
85 }
86
87 // --------------------------------------------------------------------
88
89 /**
90 * Select the database
91 *
92 * @access private called by the base class
93 * @return resource
94 */
95 function db_select()
96 {
97 return @mysql_select_db($this->database, $this->conn_id);
98 }
99
100 // --------------------------------------------------------------------
101
102 /**
Derek Allard39b622d2008-01-16 21:10:09 +0000103 * Set client character set
104 *
105 * @access public
106 * @param string
107 * @param string
108 * @return resource
109 */
110 function db_set_charset($charset, $collation)
111 {
112 return @mysql_query("SET NAMES '".$this->escape_str($charset)."' COLLATE '".$this->escape_str($collation)."'", $this->conn_id);
113 }
114
115 // --------------------------------------------------------------------
116
117 /**
Derek Allardd2df9bc2007-04-15 17:41:17 +0000118 * Version number query string
119 *
120 * @access public
121 * @return string
122 */
123 function _version()
124 {
125 return "SELECT version() AS ver";
126 }
127
128 // --------------------------------------------------------------------
129
130 /**
131 * Execute the query
132 *
133 * @access private called by the base class
134 * @param string an SQL query
135 * @return resource
136 */
137 function _execute($sql)
138 {
139 $sql = $this->_prep_query($sql);
140 return @mysql_query($sql, $this->conn_id);
141 }
142
143 // --------------------------------------------------------------------
144
145 /**
146 * Prep the query
147 *
148 * If needed, each database adapter can prep the query string
149 *
150 * @access private called by execute()
151 * @param string an SQL query
152 * @return string
153 */
154 function _prep_query($sql)
155 {
156 // "DELETE FROM TABLE" returns 0 affected rows This hack modifies
157 // the query so that it returns the number of affected rows
158 if ($this->delete_hack === TRUE)
159 {
160 if (preg_match('/^\s*DELETE\s+FROM\s+(\S+)\s*$/i', $sql))
161 {
162 $sql = preg_replace("/^\s*DELETE\s+FROM\s+(\S+)\s*$/", "DELETE FROM \\1 WHERE 1=1", $sql);
163 }
164 }
165
166 return $sql;
167 }
168
169 // --------------------------------------------------------------------
170
171 /**
172 * Begin Transaction
173 *
174 * @access public
175 * @return bool
176 */
177 function trans_begin($test_mode = FALSE)
178 {
Derek Jones0b59f272008-05-13 04:22:33 +0000179 if ( ! $this->trans_enabled)
Derek Allardd2df9bc2007-04-15 17:41:17 +0000180 {
181 return TRUE;
182 }
183
184 // When transactions are nested we only begin/commit/rollback the outermost ones
185 if ($this->_trans_depth > 0)
186 {
187 return TRUE;
188 }
189
190 // Reset the transaction failure flag.
191 // If the $test_mode flag is set to TRUE transactions will be rolled back
192 // even if the queries produce a successful result.
193 $this->_trans_failure = ($test_mode === TRUE) ? TRUE : FALSE;
194
195 $this->simple_query('SET AUTOCOMMIT=0');
196 $this->simple_query('START TRANSACTION'); // can also be BEGIN or BEGIN WORK
197 return TRUE;
198 }
199
200 // --------------------------------------------------------------------
201
202 /**
203 * Commit Transaction
204 *
205 * @access public
206 * @return bool
207 */
208 function trans_commit()
209 {
Derek Jones0b59f272008-05-13 04:22:33 +0000210 if ( ! $this->trans_enabled)
Derek Allardd2df9bc2007-04-15 17:41:17 +0000211 {
212 return TRUE;
213 }
214
215 // When transactions are nested we only begin/commit/rollback the outermost ones
216 if ($this->_trans_depth > 0)
217 {
218 return TRUE;
219 }
220
221 $this->simple_query('COMMIT');
222 $this->simple_query('SET AUTOCOMMIT=1');
223 return TRUE;
224 }
225
226 // --------------------------------------------------------------------
227
228 /**
229 * Rollback Transaction
230 *
231 * @access public
232 * @return bool
233 */
234 function trans_rollback()
235 {
Derek Jones0b59f272008-05-13 04:22:33 +0000236 if ( ! $this->trans_enabled)
Derek Allardd2df9bc2007-04-15 17:41:17 +0000237 {
238 return TRUE;
239 }
240
241 // When transactions are nested we only begin/commit/rollback the outermost ones
242 if ($this->_trans_depth > 0)
243 {
244 return TRUE;
245 }
246
247 $this->simple_query('ROLLBACK');
248 $this->simple_query('SET AUTOCOMMIT=1');
249 return TRUE;
250 }
251
252 // --------------------------------------------------------------------
253
254 /**
255 * Escape String
256 *
257 * @access public
258 * @param string
259 * @return string
260 */
261 function escape_str($str)
262 {
Derek Allard694b5b82007-12-18 15:58:03 +0000263 if (is_array($str))
Derek Allard993925b2008-08-21 12:43:31 +0000264 {
265 foreach($str as $key => $val)
266 {
267 $str[$key] = $this->escape_str($val);
268 }
269
270 return $str;
271 }
272
Derek Allard694b5b82007-12-18 15:58:03 +0000273 if (function_exists('mysql_real_escape_string') AND is_resource($this->conn_id))
Derek Allardd2df9bc2007-04-15 17:41:17 +0000274 {
275 return mysql_real_escape_string($str, $this->conn_id);
276 }
277 elseif (function_exists('mysql_escape_string'))
278 {
279 return mysql_escape_string($str);
280 }
281 else
282 {
283 return addslashes($str);
284 }
285 }
286
287 // --------------------------------------------------------------------
288
289 /**
290 * Affected Rows
291 *
292 * @access public
293 * @return integer
294 */
295 function affected_rows()
296 {
297 return @mysql_affected_rows($this->conn_id);
298 }
299
300 // --------------------------------------------------------------------
301
302 /**
303 * Insert ID
304 *
305 * @access public
306 * @return integer
307 */
308 function insert_id()
309 {
310 return @mysql_insert_id($this->conn_id);
311 }
312
313 // --------------------------------------------------------------------
314
315 /**
316 * "Count All" query
317 *
318 * Generates a platform-specific query string that counts all records in
319 * the specified database
320 *
321 * @access public
322 * @param string
323 * @return string
324 */
325 function count_all($table = '')
326 {
327 if ($table == '')
328 return '0';
329
Rick Ellis605a07a2008-10-17 04:07:54 +0000330 $query = $this->query($this->_count_string . $this->_protect_identifiers('numrows'). " FROM " . $this->_protect_identifiers($table, TRUE, NULL, FALSE));
Derek Allardd2df9bc2007-04-15 17:41:17 +0000331
Rick Ellisff734012008-09-30 20:38:12 +0000332 if ($query->num_rows() == 0)
333 return '0';
334
Derek Allardd2df9bc2007-04-15 17:41:17 +0000335 $row = $query->row();
Derek Allardb94b89c2008-04-28 23:18:00 +0000336 return (int)$row->numrows;
Derek Allardd2df9bc2007-04-15 17:41:17 +0000337 }
338
339 // --------------------------------------------------------------------
340
341 /**
342 * List table query
343 *
344 * Generates a platform-specific query string so that the table names can be fetched
345 *
346 * @access private
Derek Allard39b622d2008-01-16 21:10:09 +0000347 * @param boolean
Derek Allardd2df9bc2007-04-15 17:41:17 +0000348 * @return string
349 */
Derek Allard694b5b82007-12-18 15:58:03 +0000350 function _list_tables($prefix_limit = FALSE)
Derek Allardd2df9bc2007-04-15 17:41:17 +0000351 {
Rick Ellis605a07a2008-10-17 04:07:54 +0000352 $sql = "SHOW TABLES FROM ".$this->_escape_char.$this->database.$this->_escape_char;
Derek Allard39b622d2008-01-16 21:10:09 +0000353
354 if ($prefix_limit !== FALSE AND $this->dbprefix != '')
Derek Allard694b5b82007-12-18 15:58:03 +0000355 {
Derek Allard39b622d2008-01-16 21:10:09 +0000356 $sql .= " LIKE '".$this->dbprefix."%'";
Derek Allard694b5b82007-12-18 15:58:03 +0000357 }
Derek Allard39b622d2008-01-16 21:10:09 +0000358
Derek Allard694b5b82007-12-18 15:58:03 +0000359 return $sql;
Derek Allardd2df9bc2007-04-15 17:41:17 +0000360 }
361
362 // --------------------------------------------------------------------
363
364 /**
365 * Show column query
366 *
367 * Generates a platform-specific query string so that the column names can be fetched
368 *
369 * @access public
370 * @param string the table name
371 * @return string
372 */
373 function _list_columns($table = '')
374 {
Rick Ellis605a07a2008-10-17 04:07:54 +0000375 return "SHOW COLUMNS FROM ".$table;
Derek Allardd2df9bc2007-04-15 17:41:17 +0000376 }
377
378 // --------------------------------------------------------------------
379
380 /**
381 * Field data query
382 *
383 * Generates a platform-specific query so that the column data can be retrieved
384 *
385 * @access public
386 * @param string the table name
387 * @return object
388 */
389 function _field_data($table)
390 {
Rick Ellis605a07a2008-10-17 04:07:54 +0000391 return "SELECT * FROM ".$table." LIMIT 1";
Derek Allardd2df9bc2007-04-15 17:41:17 +0000392 }
393
394 // --------------------------------------------------------------------
395
396 /**
397 * The error message string
398 *
399 * @access private
400 * @return string
401 */
402 function _error_message()
403 {
404 return mysql_error($this->conn_id);
405 }
406
407 // --------------------------------------------------------------------
408
409 /**
410 * The error message number
411 *
412 * @access private
413 * @return integer
414 */
415 function _error_number()
416 {
417 return mysql_errno($this->conn_id);
418 }
Rick Ellis52dc8ca2008-09-30 19:53:52 +0000419
420 // --------------------------------------------------------------------
421
422 /**
Rick Ellis605a07a2008-10-17 04:07:54 +0000423 * Escape the SQL Identifiers
Rick Ellis52dc8ca2008-09-30 19:53:52 +0000424 *
Rick Ellis605a07a2008-10-17 04:07:54 +0000425 * This function escapes column and table names
Rick Ellis52dc8ca2008-09-30 19:53:52 +0000426 *
427 * @access private
Rick Ellis605a07a2008-10-17 04:07:54 +0000428 * @param string
Rick Ellis52dc8ca2008-09-30 19:53:52 +0000429 * @return string
430 */
Rick Ellis605a07a2008-10-17 04:07:54 +0000431 function _escape_identifiers($item)
Rick Ellis52dc8ca2008-09-30 19:53:52 +0000432 {
Rick Ellis605a07a2008-10-17 04:07:54 +0000433 if ($this->_escape_char == '')
Derek Allardd2df9bc2007-04-15 17:41:17 +0000434 {
Rick Ellis605a07a2008-10-17 04:07:54 +0000435 return $item;
Derek Allardd2df9bc2007-04-15 17:41:17 +0000436 }
Rick Ellisa0e86292008-10-26 22:46:55 +0000437
438 foreach ($this->_reserved_identifiers as $id)
439 {
440 if (strpos($item, '.'.$id) !== FALSE)
441 {
442 $str = $this->_escape_char. str_replace('.', $this->_escape_char.'.', $item);
443
444 // remove duplicates if the user already included the escape
445 return preg_replace('/['.$this->_escape_char.']+/', $this->_escape_char, $str);
446 }
447 }
448
Rick Ellis605a07a2008-10-17 04:07:54 +0000449 if (strpos($item, '.') !== FALSE)
Derek Allard39b622d2008-01-16 21:10:09 +0000450 {
Rick Ellis605a07a2008-10-17 04:07:54 +0000451 $str = $this->_escape_char.str_replace('.', $this->_escape_char.'.'.$this->_escape_char, $item).$this->_escape_char;
Derek Allard39b622d2008-01-16 21:10:09 +0000452 }
Derek Jones91f50b62008-01-16 21:48:18 +0000453 else
454 {
Rick Ellis605a07a2008-10-17 04:07:54 +0000455 $str = $this->_escape_char.$item.$this->_escape_char;
Derek Jones91f50b62008-01-16 21:48:18 +0000456 }
Rick Ellisa0e86292008-10-26 22:46:55 +0000457
Rick Ellis605a07a2008-10-17 04:07:54 +0000458 // remove duplicates if the user already included the escape
459 return preg_replace('/['.$this->_escape_char.']+/', $this->_escape_char, $str);
Derek Allard39b622d2008-01-16 21:10:09 +0000460 }
461
Derek Allardd2df9bc2007-04-15 17:41:17 +0000462 // --------------------------------------------------------------------
463
464 /**
Derek Jonesc6ad0232008-01-29 18:44:54 +0000465 * From Tables
466 *
467 * This function implicitly groups FROM tables so there is no confusion
468 * about operator precedence in harmony with SQL standards
469 *
470 * @access public
471 * @param type
472 * @return type
473 */
474 function _from_tables($tables)
475 {
Derek Jones0b59f272008-05-13 04:22:33 +0000476 if ( ! is_array($tables))
Derek Jonesc6ad0232008-01-29 18:44:54 +0000477 {
478 $tables = array($tables);
479 }
480
481 return '('.implode(', ', $tables).')';
482 }
483
484 // --------------------------------------------------------------------
485
486 /**
Derek Allardd2df9bc2007-04-15 17:41:17 +0000487 * Insert statement
488 *
489 * Generates a platform-specific insert string from the supplied data
490 *
491 * @access public
492 * @param string the table name
493 * @param array the insert keys
494 * @param array the insert values
495 * @return string
496 */
497 function _insert($table, $keys, $values)
498 {
Rick Ellis605a07a2008-10-17 04:07:54 +0000499 return "INSERT INTO ".$table." (".implode(', ', $keys).") VALUES (".implode(', ', $values).")";
Derek Allardd2df9bc2007-04-15 17:41:17 +0000500 }
501
502 // --------------------------------------------------------------------
503
504 /**
505 * Update statement
506 *
507 * Generates a platform-specific update string from the supplied data
508 *
509 * @access public
510 * @param string the table name
511 * @param array the update data
512 * @param array the where clause
Derek Allard39b622d2008-01-16 21:10:09 +0000513 * @param array the orderby clause
514 * @param array the limit clause
Derek Allardd2df9bc2007-04-15 17:41:17 +0000515 * @return string
516 */
Derek Allard39b622d2008-01-16 21:10:09 +0000517 function _update($table, $values, $where, $orderby = array(), $limit = FALSE)
Derek Allardd2df9bc2007-04-15 17:41:17 +0000518 {
519 foreach($values as $key => $val)
520 {
521 $valstr[] = $key." = ".$val;
522 }
Derek Allardda6d2402007-12-19 14:49:29 +0000523
Derek Jones0b59f272008-05-13 04:22:33 +0000524 $limit = ( ! $limit) ? '' : ' LIMIT '.$limit;
Derek Allard39b622d2008-01-16 21:10:09 +0000525
526 $orderby = (count($orderby) >= 1)?' ORDER BY '.implode(", ", $orderby):'';
Derek Allardd2df9bc2007-04-15 17:41:17 +0000527
Rick Ellis605a07a2008-10-17 04:07:54 +0000528 $sql = "UPDATE ".$table." SET ".implode(', ', $valstr);
529
Derek Allard32cf7eb2008-02-05 16:03:50 +0000530 $sql .= ($where != '' AND count($where) >=1) ? " WHERE ".implode(" ", $where) : '';
Rick Ellis605a07a2008-10-17 04:07:54 +0000531
Derek Allard32cf7eb2008-02-05 16:03:50 +0000532 $sql .= $orderby.$limit;
533
534 return $sql;
Derek Allard39b622d2008-01-16 21:10:09 +0000535 }
536
537 // --------------------------------------------------------------------
538
539 /**
540 * Truncate statement
541 *
542 * Generates a platform-specific truncate string from the supplied data
543 * If the database does not support the truncate() command
544 * This function maps to "DELETE FROM table"
545 *
546 * @access public
547 * @param string the table name
548 * @return string
549 */
550 function _truncate($table)
551 {
Rick Ellis605a07a2008-10-17 04:07:54 +0000552 return "TRUNCATE ".$table;
Derek Allardd2df9bc2007-04-15 17:41:17 +0000553 }
554
555 // --------------------------------------------------------------------
556
557 /**
558 * Delete statement
559 *
560 * Generates a platform-specific delete string from the supplied data
561 *
562 * @access public
563 * @param string the table name
564 * @param array the where clause
Derek Allard39b622d2008-01-16 21:10:09 +0000565 * @param string the limit clause
Derek Allardd2df9bc2007-04-15 17:41:17 +0000566 * @return string
567 */
Derek Allard39b622d2008-01-16 21:10:09 +0000568 function _delete($table, $where = array(), $like = array(), $limit = FALSE)
Derek Allardd2df9bc2007-04-15 17:41:17 +0000569 {
Derek Allard39b622d2008-01-16 21:10:09 +0000570 $conditions = '';
571
Derek Jones0b59f272008-05-13 04:22:33 +0000572 if (count($where) > 0 OR count($like) > 0)
Derek Allard39b622d2008-01-16 21:10:09 +0000573 {
574 $conditions = "\nWHERE ";
575 $conditions .= implode("\n", $this->ar_where);
576
577 if (count($where) > 0 && count($like) > 0)
578 {
579 $conditions .= " AND ";
580 }
581 $conditions .= implode("\n", $like);
582 }
583
Derek Jones0b59f272008-05-13 04:22:33 +0000584 $limit = ( ! $limit) ? '' : ' LIMIT '.$limit;
Derek Allarde77d77c2007-12-19 15:01:55 +0000585
Derek Allard39b622d2008-01-16 21:10:09 +0000586 return "DELETE FROM ".$table.$conditions.$limit;
Derek Allardd2df9bc2007-04-15 17:41:17 +0000587 }
588
589 // --------------------------------------------------------------------
590
591 /**
592 * Limit string
593 *
594 * Generates a platform-specific LIMIT clause
595 *
596 * @access public
597 * @param string the sql query string
598 * @param integer the number of rows to limit the query to
599 * @param integer the offset value
600 * @return string
601 */
602 function _limit($sql, $limit, $offset)
603 {
604 if ($offset == 0)
605 {
606 $offset = '';
607 }
608 else
609 {
610 $offset .= ", ";
611 }
612
613 return $sql."LIMIT ".$offset.$limit;
614 }
615
616 // --------------------------------------------------------------------
617
618 /**
619 * Close DB Connection
620 *
621 * @access public
622 * @param resource
623 * @return void
624 */
625 function _close($conn_id)
626 {
627 @mysql_close($conn_id);
628 }
629
630}
631
Derek Jones0b59f272008-05-13 04:22:33 +0000632
633/* End of file mysql_driver.php */
Derek Jonesa3ffbbb2008-05-11 18:18:29 +0000634/* Location: ./system/database/drivers/mysql/mysql_driver.php */