system/database/drivers/postgre/postgre_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  * Postgre 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_postgre_driver extends CI_DB {
  32
  33         var $dbdriver = 'postgre';
  34
  35         var $_escape_char = '"';
  36
  37         // clause and character used for LIKE escape sequences
  38         var $_like_escape_str = " ESCAPE '%s' ";
  39         var $_like_escape_chr = '!';
  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 = ' RANDOM()'; // database specific random keyword
  48
  49         /**
  50          * Connection String
  51          *
  52          * @access      private
  53          * @return      string
  54          */
  55         function _connect_string()
  56         {
  57                 $components = array(
  58                                                                 'hostname'      => 'host',
  59                                                                 'port'          => 'port',
  60                                                                 'database'      => 'dbname',
  61                                                                 'username'      => 'user',
  62                                                                 'password'      => 'password'
  63                                                         );
  64
  65                 $connect_string = "";
  66                 foreach ($components as $key => $val)
  67                 {
  68                         if (isset($this->$key) && $this->$key != '')
  69                         {
  70                                 $connect_string .= " $val=".$this->$key;
  71                         }
  72                 }
  73                 return trim($connect_string);
  74         }
  75
  76         // --------------------------------------------------------------------
  77
  78         /**
  79          * Non-persistent database connection
  80          *
  81          * @access      private called by the base class
  82          * @return      resource
  83          */
  84         function db_connect()
  85         {
  86                 return @pg_connect($this->_connect_string());
  87         }
  88
  89         // --------------------------------------------------------------------
  90
  91         /**
  92          * Persistent database connection
  93          *
  94          * @access      private called by the base class
  95          * @return      resource
  96          */
  97         function db_pconnect()
  98         {
  99                 return @pg_pconnect($this->_connect_string());
 100         }
 101
 102         // --------------------------------------------------------------------
 103
 104         /**
 105          * Reconnect
 106          *
 107          * Keep / reestablish the db connection if no queries have been
 108          * sent for a length of time exceeding the server's idle timeout
 109          *
 110          * @access      public
 111          * @return      void
 112          */
 113         function reconnect()
 114         {
 115                 if (pg_ping($this->conn_id) === FALSE)
 116                 {
 117                         $this->conn_id = FALSE;
 118                 }
 119         }
 120
 121         // --------------------------------------------------------------------
 122
 123         /**
 124          * Select the database
 125          *
 126          * @access      private called by the base class
 127          * @return      resource
 128          */
 129         function db_select()
 130         {
 131                 // Not needed for Postgre so we'll return TRUE
 132                 return TRUE;
 133         }
 134
 135         // --------------------------------------------------------------------
 136
 137         /**
 138          * Set client character set
 139          *
 140          * @access      public
 141          * @param       string
 142          * @param       string
 143          * @return      resource
 144          */
 145         function db_set_charset($charset, $collation)
 146         {
 147                 // @todo - add support if needed
 148                 return TRUE;
 149         }
 150
 151         // --------------------------------------------------------------------
 152
 153         /**
 154          * Version number query string
 155          *
 156          * @access      public
 157          * @return      string
 158          */
 159         function _version()
 160         {
 161                 return "SELECT version() AS ver";
 162         }
 163
 164         // --------------------------------------------------------------------
 165
 166         /**
 167          * Execute the query
 168          *
 169          * @access      private called by the base class
 170          * @param       string  an SQL query
 171          * @return      resource
 172          */
 173         function _execute($sql)
 174         {
 175                 $sql = $this->_prep_query($sql);
 176                 return @pg_query($this->conn_id, $sql);
 177         }
 178
 179         // --------------------------------------------------------------------
 180
 181         /**
 182          * Prep the query
 183          *
 184          * If needed, each database adapter can prep the query string
 185          *
 186          * @access      private called by execute()
 187          * @param       string  an SQL query
 188          * @return      string
 189          */
 190         function _prep_query($sql)
 191         {
 192                 return $sql;
 193         }
 194
 195         // --------------------------------------------------------------------
 196
 197         /**
 198          * Begin Transaction
 199          *
 200          * @access      public
 201          * @return      bool
 202          */
 203         function trans_begin($test_mode = FALSE)
 204         {
 205                 if ( ! $this->trans_enabled)
 206                 {
 207                         return TRUE;
 208                 }
 209
 210                 // When transactions are nested we only begin/commit/rollback the outermost ones
 211                 if ($this->_trans_depth > 0)
 212                 {
 213                         return TRUE;
 214                 }
 215
 216                 // Reset the transaction failure flag.
 217                 // If the $test_mode flag is set to TRUE transactions will be rolled back
 218                 // even if the queries produce a successful result.
 219                 $this->_trans_failure = ($test_mode === TRUE) ? TRUE : FALSE;
 220
 221                 return @pg_exec($this->conn_id, "begin");
 222         }
 223
 224         // --------------------------------------------------------------------
 225
 226         /**
 227          * Commit Transaction
 228          *
 229          * @access      public
 230          * @return      bool
 231          */
 232         function trans_commit()
 233         {
 234                 if ( ! $this->trans_enabled)
 235                 {
 236                         return TRUE;
 237                 }
 238
 239                 // When transactions are nested we only begin/commit/rollback the outermost ones
 240                 if ($this->_trans_depth > 0)
 241                 {
 242                         return TRUE;
 243                 }
 244
 245                 return @pg_exec($this->conn_id, "commit");
 246         }
 247
 248         // --------------------------------------------------------------------
 249
 250         /**
 251          * Rollback Transaction
 252          *
 253          * @access      public
 254          * @return      bool
 255          */
 256         function trans_rollback()
 257         {
 258                 if ( ! $this->trans_enabled)
 259                 {
 260                         return TRUE;
 261                 }
 262
 263                 // When transactions are nested we only begin/commit/rollback the outermost ones
 264                 if ($this->_trans_depth > 0)
 265                 {
 266                         return TRUE;
 267                 }
 268
 269                 return @pg_exec($this->conn_id, "rollback");
 270         }
 271
 272         // --------------------------------------------------------------------
 273
 274         /**
 275          * Escape String
 276          *
 277          * @access      public
 278          * @param       string
 279          * @param       bool    whether or not the string will be used in a LIKE condition
 280          * @return      string
 281          */
 282         function escape_str($str, $like = FALSE)
 283         {
 284                 if (is_array($str))
 285                 {
 286                         foreach ($str as $key => $val)
 287                         {
 288                                 $str[$key] = $this->escape_str($val, $like);
 289                         }
 290
 291                         return $str;
 292                 }
 293
 294                 $str = pg_escape_string($str);
 295
 296                 // escape LIKE condition wildcards
 297                 if ($like === TRUE)
 298                 {
 299                         $str = str_replace(     array('%', '_', $this->_like_escape_chr),
 300                                                                 array($this->_like_escape_chr.'%', $this->_like_escape_chr.'_', $this->_like_escape_chr.$this->_like_escape_chr),
 301                                                                 $str);
 302                 }
 303
 304                 return $str;
 305         }
 306
 307         // --------------------------------------------------------------------
 308
 309         /**
 310          * Affected Rows
 311          *
 312          * @access      public
 313          * @return      integer
 314          */
 315         function affected_rows()
 316         {
 317                 return @pg_affected_rows($this->result_id);
 318         }
 319
 320         // --------------------------------------------------------------------
 321
 322         /**
 323          * Insert ID
 324          *
 325          * @access      public
 326          * @return      integer
 327          */
 328         function insert_id()
 329         {
 330                 $v = $this->_version();
 331                 $v = $v['server'];
 332
 333                 $table  = func_num_args() > 0 ? func_get_arg(0) : NULL;
 334                 $column = func_num_args() > 1 ? func_get_arg(1) : NULL;
 335
 336                 if ($table == NULL && $v >= '8.1')
 337                 {
 338                         $sql='SELECT LASTVAL() as ins_id';
 339                 }
 340                 elseif ($table != NULL && $column != NULL && $v >= '8.0')
 341                 {
 342                         $sql = sprintf("SELECT pg_get_serial_sequence('%s','%s') as seq", $table, $column);
 343                         $query = $this->query($sql);
 344                         $row = $query->row();
 345                         $sql = sprintf("SELECT CURRVAL('%s') as ins_id", $row->seq);
 346                 }
 347                 elseif ($table != NULL)
 348                 {
 349                         // seq_name passed in table parameter
 350                         $sql = sprintf("SELECT CURRVAL('%s') as ins_id", $table);
 351                 }
 352                 else
 353                 {
 354                         return pg_last_oid($this->result_id);
 355                 }
 356                 $query = $this->query($sql);
 357                 $row = $query->row();
 358                 return $row->ins_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          * Show 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 = "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'";
 406
 407                 if ($prefix_limit !== FALSE AND $this->dbprefix != '')
 408                 {
 409                         $sql .= " AND table_name LIKE '".$this->escape_like_str($this->dbprefix)."%' ".sprintf($this->_like_escape_str, $this->_like_escape_chr);
 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 "SELECT column_name FROM information_schema.columns WHERE table_name ='".$table."'";
 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 "SELECT * FROM ".$table." LIMIT 1";
 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 pg_last_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 '';
 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          * Insert_batch statement
 559          *
 560          * Generates a platform-specific insert string from the supplied data
 561          *
 562          * @access  public
 563          * @param   string  the table name
 564          * @param   array   the insert keys
 565          * @param   array   the insert values
 566          * @return  string
 567          */
 568         function _insert_batch($table, $keys, $values)
 569         {
 570                 return "INSERT INTO ".$table." (".implode(', ', $keys).") VALUES ".implode(', ', $values);
 571         }
 572
 573         // --------------------------------------------------------------------
 574
 575         /**
 576          * Update statement
 577          *
 578          * Generates a platform-specific update string from the supplied data
 579          *
 580          * @access      public
 581          * @param       string  the table name
 582          * @param       array   the update data
 583          * @param       array   the where clause
 584          * @param       array   the orderby clause
 585          * @param       array   the limit clause
 586          * @return      string
 587          */
 588         function _update($table, $values, $where, $orderby = array(), $limit = FALSE)
 589         {
 590                 foreach ($values as $key => $val)
 591                 {
 592                         $valstr[] = $key." = ".$val;
 593                 }
 594
 595                 $limit = ( ! $limit) ? '' : ' LIMIT '.$limit;
 596
 597                 $orderby = (count($orderby) >= 1)?' ORDER BY '.implode(", ", $orderby):'';
 598
 599                 $sql = "UPDATE ".$table." SET ".implode(', ', $valstr);
 600
 601                 $sql .= ($where != '' AND count($where) >=1) ? " WHERE ".implode(" ", $where) : '';
 602
 603                 $sql .= $orderby.$limit;
 604
 605                 return $sql;
 606         }
 607
 608         // --------------------------------------------------------------------
 609
 610         /**
 611          * Truncate statement
 612          *
 613          * Generates a platform-specific truncate string from the supplied data
 614          * If the database does not support the truncate() command
 615          * This function maps to "DELETE FROM table"
 616          *
 617          * @access      public
 618          * @param       string  the table name
 619          * @return      string
 620          */
 621         function _truncate($table)
 622         {
 623                 return "TRUNCATE ".$table;
 624         }
 625
 626         // --------------------------------------------------------------------
 627
 628         /**
 629          * Delete statement
 630          *
 631          * Generates a platform-specific delete string from the supplied data
 632          *
 633          * @access      public
 634          * @param       string  the table name
 635          * @param       array   the where clause
 636          * @param       string  the limit clause
 637          * @return      string
 638          */
 639         function _delete($table, $where = array(), $like = array(), $limit = FALSE)
 640         {
 641                 $conditions = '';
 642
 643                 if (count($where) > 0 OR count($like) > 0)
 644                 {
 645                         $conditions = "\nWHERE ";
 646                         $conditions .= implode("\n", $this->ar_where);
 647
 648                         if (count($where) > 0 && count($like) > 0)
 649                         {
 650                                 $conditions .= " AND ";
 651                         }
 652                         $conditions .= implode("\n", $like);
 653                 }
 654
 655                 $limit = ( ! $limit) ? '' : ' LIMIT '.$limit;
 656
 657                 return "DELETE FROM ".$table.$conditions.$limit;
 658         }
 659
 660         // --------------------------------------------------------------------
 661         /**
 662          * Limit string
 663          *
 664          * Generates a platform-specific LIMIT clause
 665          *
 666          * @access      public
 667          * @param       string  the sql query string
 668          * @param       integer the number of rows to limit the query to
 669          * @param       integer the offset value
 670          * @return      string
 671          */
 672         function _limit($sql, $limit, $offset)
 673         {
 674                 $sql .= "LIMIT ".$limit;
 675
 676                 if ($offset > 0)
 677                 {
 678                         $sql .= " OFFSET ".$offset;
 679                 }
 680
 681                 return $sql;
 682         }
 683
 684         // --------------------------------------------------------------------
 685
 686         /**
 687          * Close DB Connection
 688          *
 689          * @access      public
 690          * @param       resource
 691          * @return      void
 692          */
 693         function _close($conn_id)
 694         {
 695                 @pg_close($conn_id);
 696         }
 697
 698
 699 }
 700
 701
 702 /* End of file postgre_driver.php */
 703 /* Location: ./system/database/drivers/postgre/postgre_driver.php */