system/database/drivers/mysql/mysql_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 5.1.6 or newer
   6  *
   7  * @package             CodeIgniter
   8  * @author              ExpressionEngine Dev Team
   9  * @copyright   Copyright (c) 2008 - 2011, 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  * 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
  28  * @author              ExpressionEngine Dev Team
  29  * @link                http://codeigniter.com/user_guide/database/
  30  */
  31 class CI_DB_mysql_driver extends CI_DB {
  32
  33         var $dbdriver = 'mysql';
  34
  35         // The character used for escaping
  36         var     $_escape_char = '`';
  37
  38         // clause and character used for LIKE escape sequences - not used in MySQL
  39         var $_like_escape_str = '';
  40         var $_like_escape_chr = '';
  41
  42         /**
  43          * Whether to use the MySQL "delete hack" which allows the number
  44          * of affected rows to be shown. Uses a preg_replace when enabled,
  45          * adding a bit more processing to all queries.
  46          */
  47         var $delete_hack = TRUE;
  48
  49         /**
  50          * The syntax to count rows is slightly different across different
  51          * database engines, so this string appears in each driver and is
  52          * used for the count_all() and count_all_results() functions.
  53          */
  54         var $_count_string = 'SELECT COUNT(*) AS ';
  55         var $_random_keyword = ' RAND()'; // database specific random keyword
  56
  57         // whether SET NAMES must be used to set the character set
  58         var $use_set_names;
  59
  60         /**
  61          * Non-persistent database connection
  62          *
  63          * @access      private called by the base class
  64          * @return      resource
  65          */
  66         function db_connect()
  67         {
  68                 if ($this->port != '')
  69                 {
  70                         $this->hostname .= ':'.$this->port;
  71                 }
  72
  73                 return @mysql_connect($this->hostname, $this->username, $this->password, TRUE);
  74         }
  75
  76         // --------------------------------------------------------------------
  77
  78         /**
  79          * Persistent database connection
  80          *
  81          * @access      private called by the base class
  82          * @return      resource
  83          */
  84         function db_pconnect()
  85         {
  86                 if ($this->port != '')
  87                 {
  88                         $this->hostname .= ':'.$this->port;
  89                 }
  90
  91                 return @mysql_pconnect($this->hostname, $this->username, $this->password);
  92         }
  93
  94         // --------------------------------------------------------------------
  95
  96         /**
  97          * Reconnect
  98          *
  99          * Keep / reestablish the db connection if no queries have been
 100          * sent for a length of time exceeding the server's idle timeout
 101          *
 102          * @access      public
 103          * @return      void
 104          */
 105         function reconnect()
 106         {
 107                 if (mysql_ping($this->conn_id) === FALSE)
 108                 {
 109                         $this->conn_id = FALSE;
 110                 }
 111         }
 112
 113         // --------------------------------------------------------------------
 114
 115         /**
 116          * Select the database
 117          *
 118          * @access      private called by the base class
 119          * @return      resource
 120          */
 121         function db_select()
 122         {
 123                 return @mysql_select_db($this->database, $this->conn_id);
 124         }
 125
 126         // --------------------------------------------------------------------
 127
 128         /**
 129          * Set client character set
 130          *
 131          * @access      public
 132          * @param       string
 133          * @param       string
 134          * @return      resource
 135          */
 136         function db_set_charset($charset, $collation)
 137         {
 138                 if ( ! isset($this->use_set_names))
 139                 {
 140                         // mysql_set_charset() requires PHP >= 5.2.3 and MySQL >= 5.0.7, use SET NAMES as fallback
 141                         $this->use_set_names = (version_compare(PHP_VERSION, '5.2.3', '>=') && version_compare(mysql_get_server_info(), '5.0.7', '>=')) ? FALSE : TRUE;
 142                 }
 143
 144                 if ($this->use_set_names === TRUE)
 145                 {
 146                         return @mysql_query("SET NAMES '".$this->escape_str($charset)."' COLLATE '".$this->escape_str($collation)."'", $this->conn_id);
 147                 }
 148                 else
 149                 {
 150                         return @mysql_set_charset($charset, $this->conn_id);
 151                 }
 152         }
 153
 154         // --------------------------------------------------------------------
 155
 156         /**
 157          * Version number query string
 158          *
 159          * @access      public
 160          * @return      string
 161          */
 162         function _version()
 163         {
 164                 return "SELECT version() AS ver";
 165         }
 166
 167         // --------------------------------------------------------------------
 168
 169         /**
 170          * Execute the query
 171          *
 172          * @access      private called by the base class
 173          * @param       string  an SQL query
 174          * @return      resource
 175          */
 176         function _execute($sql)
 177         {
 178                 $sql = $this->_prep_query($sql);
 179                 return @mysql_query($sql, $this->conn_id);
 180         }
 181
 182         // --------------------------------------------------------------------
 183
 184         /**
 185          * Prep the query
 186          *
 187          * If needed, each database adapter can prep the query string
 188          *
 189          * @access      private called by execute()
 190          * @param       string  an SQL query
 191          * @return      string
 192          */
 193         function _prep_query($sql)
 194         {
 195                 // "DELETE FROM TABLE" returns 0 affected rows This hack modifies
 196                 // the query so that it returns the number of affected rows
 197                 if ($this->delete_hack === TRUE)
 198                 {
 199                         if (preg_match('/^\s*DELETE\s+FROM\s+(\S+)\s*$/i', $sql))
 200                         {
 201                                 $sql = preg_replace("/^\s*DELETE\s+FROM\s+(\S+)\s*$/", "DELETE FROM \\1 WHERE 1=1", $sql);
 202                         }
 203                 }
 204
 205                 return $sql;
 206         }
 207
 208         // --------------------------------------------------------------------
 209
 210         /**
 211          * Begin Transaction
 212          *
 213          * @access      public
 214          * @return      bool
 215          */
 216         function trans_begin($test_mode = FALSE)
 217         {
 218                 if ( ! $this->trans_enabled)
 219                 {
 220                         return TRUE;
 221                 }
 222
 223                 // When transactions are nested we only begin/commit/rollback the outermost ones
 224                 if ($this->_trans_depth > 0)
 225                 {
 226                         return TRUE;
 227                 }
 228
 229                 // Reset the transaction failure flag.
 230                 // If the $test_mode flag is set to TRUE transactions will be rolled back
 231                 // even if the queries produce a successful result.
 232                 $this->_trans_failure = ($test_mode === TRUE) ? TRUE : FALSE;
 233
 234                 $this->simple_query('SET AUTOCOMMIT=0');
 235                 $this->simple_query('START TRANSACTION'); // can also be BEGIN or BEGIN WORK
 236                 return TRUE;
 237         }
 238
 239         // --------------------------------------------------------------------
 240
 241         /**
 242          * Commit Transaction
 243          *
 244          * @access      public
 245          * @return      bool
 246          */
 247         function trans_commit()
 248         {
 249                 if ( ! $this->trans_enabled)
 250                 {
 251                         return TRUE;
 252                 }
 253
 254                 // When transactions are nested we only begin/commit/rollback the outermost ones
 255                 if ($this->_trans_depth > 0)
 256                 {
 257                         return TRUE;
 258                 }
 259
 260                 $this->simple_query('COMMIT');
 261                 $this->simple_query('SET AUTOCOMMIT=1');
 262                 return TRUE;
 263         }
 264
 265         // --------------------------------------------------------------------
 266
 267         /**
 268          * Rollback Transaction
 269          *
 270          * @access      public
 271          * @return      bool
 272          */
 273         function trans_rollback()
 274         {
 275                 if ( ! $this->trans_enabled)
 276                 {
 277                         return TRUE;
 278                 }
 279
 280                 // When transactions are nested we only begin/commit/rollback the outermost ones
 281                 if ($this->_trans_depth > 0)
 282                 {
 283                         return TRUE;
 284                 }
 285
 286                 $this->simple_query('ROLLBACK');
 287                 $this->simple_query('SET AUTOCOMMIT=1');
 288                 return TRUE;
 289         }
 290
 291         // --------------------------------------------------------------------
 292
 293         /**
 294          * Escape String
 295          *
 296          * @access      public
 297          * @param       string
 298          * @param       bool    whether or not the string will be used in a LIKE condition
 299          * @return      string
 300          */
 301         function escape_str($str, $like = FALSE)
 302         {
 303                 if (is_array($str))
 304                 {
 305                         foreach ($str as $key => $val)
 306                         {
 307                                 $str[$key] = $this->escape_str($val, $like);
 308                         }
 309
 310                         return $str;
 311                 }
 312
 313                 if (function_exists('mysql_real_escape_string') AND is_resource($this->conn_id))
 314                 {
 315                         $str = mysql_real_escape_string($str, $this->conn_id);
 316                 }
 317                 elseif (function_exists('mysql_escape_string'))
 318                 {
 319                         $str = mysql_escape_string($str);
 320                 }
 321                 else
 322                 {
 323                         $str = addslashes($str);
 324                 }
 325
 326                 // escape LIKE condition wildcards
 327                 if ($like === TRUE)
 328                 {
 329                         $str = str_replace(array('%', '_'), array('\\%', '\\_'), $str);
 330                 }
 331
 332                 return $str;
 333         }
 334
 335         // --------------------------------------------------------------------
 336
 337         /**
 338          * Affected Rows
 339          *
 340          * @access      public
 341          * @return      integer
 342          */
 343         function affected_rows()
 344         {
 345                 return @mysql_affected_rows($this->conn_id);
 346         }
 347
 348         // --------------------------------------------------------------------
 349
 350         /**
 351          * Insert ID
 352          *
 353          * @access      public
 354          * @return      integer
 355          */
 356         function insert_id()
 357         {
 358                 return @mysql_insert_id($this->conn_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                 $this->_reset_select();
 389                 return (int) $row->numrows;
 390         }
 391
 392         // --------------------------------------------------------------------
 393
 394         /**
 395          * List table query
 396          *
 397          * Generates a platform-specific query string so that the table names can be fetched
 398          *
 399          * @access      private
 400          * @param       boolean
 401          * @return      string
 402          */
 403         function _list_tables($prefix_limit = FALSE)
 404         {
 405                 $sql = "SHOW TABLES FROM ".$this->_escape_char.$this->database.$this->_escape_char;
 406
 407                 if ($prefix_limit !== FALSE AND $this->dbprefix != '')
 408                 {
 409                         $sql .= " LIKE '".$this->escape_like_str($this->dbprefix)."%'";
 410                 }
 411
 412                 return $sql;
 413         }
 414
 415         // --------------------------------------------------------------------
 416
 417         /**
 418          * Show column query
 419          *
 420          * Generates a platform-specific query string so that the column names can be fetched
 421          *
 422          * @access      public
 423          * @param       string  the table name
 424          * @return      string
 425          */
 426         function _list_columns($table = '')
 427         {
 428                 return "SHOW COLUMNS FROM ".$this->_protect_identifiers($table, TRUE, NULL, FALSE);
 429         }
 430
 431         // --------------------------------------------------------------------
 432
 433         /**
 434          * Field data query
 435          *
 436          * Generates a platform-specific query so that the column data can be retrieved
 437          *
 438          * @access      public
 439          * @param       string  the table name
 440          * @return      object
 441          */
 442         function _field_data($table)
 443         {
 444                 return "DESCRIBE ".$table;
 445         }
 446
 447         // --------------------------------------------------------------------
 448
 449         /**
 450          * The error message string
 451          *
 452          * @access      private
 453          * @return      string
 454          */
 455         function _error_message()
 456         {
 457                 return mysql_error($this->conn_id);
 458         }
 459
 460         // --------------------------------------------------------------------
 461
 462         /**
 463          * The error message number
 464          *
 465          * @access      private
 466          * @return      integer
 467          */
 468         function _error_number()
 469         {
 470                 return mysql_errno($this->conn_id);
 471         }
 472
 473         // --------------------------------------------------------------------
 474
 475         /**
 476          * Escape the SQL Identifiers
 477          *
 478          * This function escapes column and table names
 479          *
 480          * @access      private
 481          * @param       string
 482          * @return      string
 483          */
 484         function _escape_identifiers($item)
 485         {
 486                 if ($this->_escape_char == '')
 487                 {
 488                         return $item;
 489                 }
 490
 491                 foreach ($this->_reserved_identifiers as $id)
 492                 {
 493                         if (strpos($item, '.'.$id) !== FALSE)
 494                         {
 495                                 $str = $this->_escape_char. str_replace('.', $this->_escape_char.'.', $item);
 496
 497                                 // remove duplicates if the user already included the escape
 498                                 return preg_replace('/['.$this->_escape_char.']+/', $this->_escape_char, $str);
 499                         }
 500                 }
 501
 502                 if (strpos($item, '.') !== FALSE)
 503                 {
 504                         $str = $this->_escape_char.str_replace('.', $this->_escape_char.'.'.$this->_escape_char, $item).$this->_escape_char;
 505                 }
 506                 else
 507                 {
 508                         $str = $this->_escape_char.$item.$this->_escape_char;
 509                 }
 510
 511                 // remove duplicates if the user already included the escape
 512                 return preg_replace('/['.$this->_escape_char.']+/', $this->_escape_char, $str);
 513         }
 514
 515         // --------------------------------------------------------------------
 516
 517         /**
 518          * From Tables
 519          *
 520          * This function implicitly groups FROM tables so there is no confusion
 521          * about operator precedence in harmony with SQL standards
 522          *
 523          * @access      public
 524          * @param       type
 525          * @return      type
 526          */
 527         function _from_tables($tables)
 528         {
 529                 if ( ! is_array($tables))
 530                 {
 531                         $tables = array($tables);
 532                 }
 533
 534                 return '('.implode(', ', $tables).')';
 535         }
 536
 537         // --------------------------------------------------------------------
 538
 539         /**
 540          * Insert statement
 541          *
 542          * Generates a platform-specific insert string from the supplied data
 543          *
 544          * @access      public
 545          * @param       string  the table name
 546          * @param       array   the insert keys
 547          * @param       array   the insert values
 548          * @return      string
 549          */
 550         function _insert($table, $keys, $values)
 551         {
 552                 return "INSERT INTO ".$table." (".implode(', ', $keys).") VALUES (".implode(', ', $values).")";
 553         }
 554
 555         // --------------------------------------------------------------------
 556
 557
 558         /**
 559          * Replace statement
 560          *
 561          * Generates a platform-specific replace string from the supplied data
 562          *
 563          * @access      public
 564          * @param       string  the table name
 565          * @param       array   the insert keys
 566          * @param       array   the insert values
 567          * @return      string
 568          */
 569         function _replace($table, $keys, $values)
 570         {
 571                 return "REPLACE INTO ".$table." (".implode(', ', $keys).") VALUES (".implode(', ', $values).")";
 572         }
 573
 574         // --------------------------------------------------------------------
 575
 576         /**
 577          * Insert_batch statement
 578          *
 579          * Generates a platform-specific insert string from the supplied data
 580          *
 581          * @access      public
 582          * @param       string  the table name
 583          * @param       array   the insert keys
 584          * @param       array   the insert values
 585          * @return      string
 586          */
 587         function _insert_batch($table, $keys, $values)
 588         {
 589                 return "INSERT INTO ".$table." (".implode(', ', $keys).") VALUES ".implode(', ', $values);
 590         }
 591
 592         // --------------------------------------------------------------------
 593
 594
 595         /**
 596          * Update statement
 597          *
 598          * Generates a platform-specific update string from the supplied data
 599          *
 600          * @access      public
 601          * @param       string  the table name
 602          * @param       array   the update data
 603          * @param       array   the where clause
 604          * @param       array   the orderby clause
 605          * @param       array   the limit clause
 606          * @return      string
 607          */
 608         function _update($table, $values, $where, $orderby = array(), $limit = FALSE)
 609         {
 610                 foreach ($values as $key => $val)
 611                 {
 612                         $valstr[] = $key . ' = ' . $val;
 613                 }
 614
 615                 $limit = ( ! $limit) ? '' : ' LIMIT '.$limit;
 616
 617                 $orderby = (count($orderby) >= 1)?' ORDER BY '.implode(", ", $orderby):'';
 618
 619                 $sql = "UPDATE ".$table." SET ".implode(', ', $valstr);
 620
 621                 $sql .= ($where != '' AND count($where) >=1) ? " WHERE ".implode(" ", $where) : '';
 622
 623                 $sql .= $orderby.$limit;
 624
 625                 return $sql;
 626         }
 627
 628         // --------------------------------------------------------------------
 629
 630
 631         /**
 632          * Update_Batch statement
 633          *
 634          * Generates a platform-specific batch update string from the supplied data
 635          *
 636          * @access      public
 637          * @param       string  the table name
 638          * @param       array   the update data
 639          * @param       array   the where clause
 640          * @return      string
 641          */
 642         function _update_batch($table, $values, $index, $where = NULL)
 643         {
 644                 $ids = array();
 645                 $where = ($where != '' AND count($where) >=1) ? implode(" ", $where).' AND ' : '';
 646
 647                 foreach ($values as $key => $val)
 648                 {
 649                         $ids[] = $val[$index];
 650
 651                         foreach (array_keys($val) as $field)
 652                         {
 653                                 if ($field != $index)
 654                                 {
 655                                         $final[$field][] =  'WHEN '.$index.' = '.$val[$index].' THEN '.$val[$field];
 656                                 }
 657                         }
 658                 }
 659
 660                 $sql = "UPDATE ".$table." SET ";
 661                 $cases = '';
 662
 663                 foreach ($final as $k => $v)
 664                 {
 665                         $cases .= $k.' = CASE '."\n";
 666                         foreach ($v as $row)
 667                         {
 668                                 $cases .= $row."\n";
 669                         }
 670
 671                         $cases .= 'ELSE '.$k.' END, ';
 672                 }
 673
 674                 $sql .= substr($cases, 0, -2);
 675
 676                 $sql .= ' WHERE '.$where.$index.' IN ('.implode(',', $ids).')';
 677
 678                 return $sql;
 679         }
 680
 681         // --------------------------------------------------------------------
 682
 683
 684         /**
 685          * Truncate statement
 686          *
 687          * Generates a platform-specific truncate string from the supplied data
 688          * If the database does not support the truncate() command
 689          * This function maps to "DELETE FROM table"
 690          *
 691          * @access      public
 692          * @param       string  the table name
 693          * @return      string
 694          */
 695         function _truncate($table)
 696         {
 697                 return "TRUNCATE ".$table;
 698         }
 699
 700         // --------------------------------------------------------------------
 701
 702         /**
 703          * Delete statement
 704          *
 705          * Generates a platform-specific delete string from the supplied data
 706          *
 707          * @access      public
 708          * @param       string  the table name
 709          * @param       array   the where clause
 710          * @param       string  the limit clause
 711          * @return      string
 712          */
 713         function _delete($table, $where = array(), $like = array(), $limit = FALSE)
 714         {
 715                 $conditions = '';
 716
 717                 if (count($where) > 0 OR count($like) > 0)
 718                 {
 719                         $conditions = "\nWHERE ";
 720                         $conditions .= implode("\n", $this->ar_where);
 721
 722                         if (count($where) > 0 && count($like) > 0)
 723                         {
 724                                 $conditions .= " AND ";
 725                         }
 726                         $conditions .= implode("\n", $like);
 727                 }
 728
 729                 $limit = ( ! $limit) ? '' : ' LIMIT '.$limit;
 730
 731                 return "DELETE FROM ".$table.$conditions.$limit;
 732         }
 733
 734         // --------------------------------------------------------------------
 735
 736         /**
 737          * Limit string
 738          *
 739          * Generates a platform-specific LIMIT clause
 740          *
 741          * @access      public
 742          * @param       string  the sql query string
 743          * @param       integer the number of rows to limit the query to
 744          * @param       integer the offset value
 745          * @return      string
 746          */
 747         function _limit($sql, $limit, $offset)
 748         {
 749                 if ($offset == 0)
 750                 {
 751                         $offset = '';
 752                 }
 753                 else
 754                 {
 755                         $offset .= ", ";
 756                 }
 757
 758                 return $sql."LIMIT ".$offset.$limit;
 759         }
 760
 761         // --------------------------------------------------------------------
 762
 763         /**
 764          * Close DB Connection
 765          *
 766          * @access      public
 767          * @param       resource
 768          * @return      void
 769          */
 770         function _close($conn_id)
 771         {
 772                 @mysql_close($conn_id);
 773         }
 774
 775 }
 776
 777
 778 /* End of file mysql_driver.php */
 779 /* Location: ./system/database/drivers/mysql/mysql_driver.php */