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