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 4.3.2 or newer
   6  *
   7  * @package             CodeIgniter
   8  * @author              ExpressionEngine Dev Team
   9  * @copyright   Copyright (c) 2008 - 2009, EllisLab, Inc.
  10  * @license             http://codeigniter.com/user_guide/license.html
  11  * @link                http://codeigniter.com
  12  * @since               Version 1.0
  13  * @filesource
  14  */
  15
  16 // ------------------------------------------------------------------------
  17
  18 /**
  19  * 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                 return (int) $row->numrows;
 389         }
 390
 391         // --------------------------------------------------------------------
 392
 393         /**
 394          * Show table query
 395          *
 396          * Generates a platform-specific query string so that the table names can be fetched
 397          *
 398          * @access      private
 399          * @param       boolean
 400          * @return      string
 401          */
 402         function _list_tables($prefix_limit = FALSE)
 403         {
 404                 $sql = "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'";
 405
 406                 if ($prefix_limit !== FALSE AND $this->dbprefix != '')
 407                 {
 408                         $sql .= " AND table_name LIKE '".$this->escape_like_str($this->dbprefix)."%' ".sprintf($this->_like_escape_str, $this->_like_escape_char);
 409                 }
 410
 411                 return $sql;
 412         }
 413
 414         // --------------------------------------------------------------------
 415
 416         /**
 417          * Show column query
 418          *
 419          * Generates a platform-specific query string so that the column names can be fetched
 420          *
 421          * @access      public
 422          * @param       string  the table name
 423          * @return      string
 424          */
 425         function _list_columns($table = '')
 426         {
 427                 return "SELECT column_name FROM information_schema.columns WHERE table_name ='".$table."'";
 428         }
 429
 430         // --------------------------------------------------------------------
 431
 432         /**
 433          * Field data query
 434          *
 435          * Generates a platform-specific query so that the column data can be retrieved
 436          *
 437          * @access      public
 438          * @param       string  the table name
 439          * @return      object
 440          */
 441         function _field_data($table)
 442         {
 443                 return "SELECT * FROM ".$table." LIMIT 1";
 444         }
 445
 446         // --------------------------------------------------------------------
 447
 448         /**
 449          * The error message string
 450          *
 451          * @access      private
 452          * @return      string
 453          */
 454         function _error_message()
 455         {
 456                 return pg_last_error($this->conn_id);
 457         }
 458
 459         // --------------------------------------------------------------------
 460
 461         /**
 462          * The error message number
 463          *
 464          * @access      private
 465          * @return      integer
 466          */
 467         function _error_number()
 468         {
 469                 return '';
 470         }
 471
 472         // --------------------------------------------------------------------
 473
 474         /**
 475          * Escape the SQL Identifiers
 476          *
 477          * This function escapes column and table names
 478          *
 479          * @access      private
 480          * @param       string
 481          * @return      string
 482          */
 483         function _escape_identifiers($item)
 484         {
 485                 if ($this->_escape_char == '')
 486                 {
 487                         return $item;
 488                 }
 489
 490                 foreach ($this->_reserved_identifiers as $id)
 491                 {
 492                         if (strpos($item, '.'.$id) !== FALSE)
 493                         {
 494                                 $str = $this->_escape_char. str_replace('.', $this->_escape_char.'.', $item);
 495
 496                                 // remove duplicates if the user already included the escape
 497                                 return preg_replace('/['.$this->_escape_char.']+/', $this->_escape_char, $str);
 498                         }
 499                 }
 500
 501                 if (strpos($item, '.') !== FALSE)
 502                 {
 503                         $str = $this->_escape_char.str_replace('.', $this->_escape_char.'.'.$this->_escape_char, $item).$this->_escape_char;
 504                 }
 505                 else
 506                 {
 507                         $str = $this->_escape_char.$item.$this->_escape_char;
 508                 }
 509
 510                 // remove duplicates if the user already included the escape
 511                 return preg_replace('/['.$this->_escape_char.']+/', $this->_escape_char, $str);
 512         }
 513
 514         // --------------------------------------------------------------------
 515
 516         /**
 517          * From Tables
 518          *
 519          * This function implicitly groups FROM tables so there is no confusion
 520          * about operator precedence in harmony with SQL standards
 521          *
 522          * @access      public
 523          * @param       type
 524          * @return      type
 525          */
 526         function _from_tables($tables)
 527         {
 528                 if ( ! is_array($tables))
 529                 {
 530                         $tables = array($tables);
 531                 }
 532
 533                 return implode(', ', $tables);
 534         }
 535
 536         // --------------------------------------------------------------------
 537
 538         /**
 539          * Insert statement
 540          *
 541          * Generates a platform-specific insert string from the supplied data
 542          *
 543          * @access      public
 544          * @param       string  the table name
 545          * @param       array   the insert keys
 546          * @param       array   the insert values
 547          * @return      string
 548          */
 549         function _insert($table, $keys, $values)
 550         {
 551                 return "INSERT INTO ".$table." (".implode(', ', $keys).") VALUES (".implode(', ', $values).")";
 552         }
 553
 554         // --------------------------------------------------------------------
 555
 556         /**
 557          * Update statement
 558          *
 559          * Generates a platform-specific update string from the supplied data
 560          *
 561          * @access      public
 562          * @param       string  the table name
 563          * @param       array   the update data
 564          * @param       array   the where clause
 565          * @param       array   the orderby clause
 566          * @param       array   the limit clause
 567          * @return      string
 568          */
 569         function _update($table, $values, $where, $orderby = array(), $limit = FALSE)
 570         {
 571                 foreach($values as $key => $val)
 572                 {
 573                         $valstr[] = $key." = ".$val;
 574                 }
 575
 576                 $limit = ( ! $limit) ? '' : ' LIMIT '.$limit;
 577
 578                 $orderby = (count($orderby) >= 1)?' ORDER BY '.implode(", ", $orderby):'';
 579
 580                 $sql = "UPDATE ".$table." SET ".implode(', ', $valstr);
 581
 582                 $sql .= ($where != '' AND count($where) >=1) ? " WHERE ".implode(" ", $where) : '';
 583
 584                 $sql .= $orderby.$limit;
 585
 586                 return $sql;
 587         }
 588
 589         // --------------------------------------------------------------------
 590
 591         /**
 592          * Truncate statement
 593          *
 594          * Generates a platform-specific truncate string from the supplied data
 595          * If the database does not support the truncate() command
 596          * This function maps to "DELETE FROM table"
 597          *
 598          * @access      public
 599          * @param       string  the table name
 600          * @return      string
 601          */
 602         function _truncate($table)
 603         {
 604                 return "TRUNCATE ".$table;
 605         }
 606
 607         // --------------------------------------------------------------------
 608
 609         /**
 610          * Delete statement
 611          *
 612          * Generates a platform-specific delete string from the supplied data
 613          *
 614          * @access      public
 615          * @param       string  the table name
 616          * @param       array   the where clause
 617          * @param       string  the limit clause
 618          * @return      string
 619          */
 620         function _delete($table, $where = array(), $like = array(), $limit = FALSE)
 621         {
 622                 $conditions = '';
 623
 624                 if (count($where) > 0 OR count($like) > 0)
 625                 {
 626                         $conditions = "\nWHERE ";
 627                         $conditions .= implode("\n", $this->ar_where);
 628
 629                         if (count($where) > 0 && count($like) > 0)
 630                         {
 631                                 $conditions .= " AND ";
 632                         }
 633                         $conditions .= implode("\n", $like);
 634                 }
 635
 636                 $limit = ( ! $limit) ? '' : ' LIMIT '.$limit;
 637
 638                 return "DELETE FROM ".$table.$conditions.$limit;
 639         }
 640
 641         // --------------------------------------------------------------------
 642         /**
 643          * Limit string
 644          *
 645          * Generates a platform-specific LIMIT clause
 646          *
 647          * @access      public
 648          * @param       string  the sql query string
 649          * @param       integer the number of rows to limit the query to
 650          * @param       integer the offset value
 651          * @return      string
 652          */
 653         function _limit($sql, $limit, $offset)
 654         {
 655                 $sql .= "LIMIT ".$limit;
 656
 657                 if ($offset > 0)
 658                 {
 659                         $sql .= " OFFSET ".$offset;
 660                 }
 661
 662                 return $sql;
 663         }
 664
 665         // --------------------------------------------------------------------
 666
 667         /**
 668          * Close DB Connection
 669          *
 670          * @access      public
 671          * @param       resource
 672          * @return      void
 673          */
 674         function _close($conn_id)
 675         {
 676                 @pg_close($conn_id);
 677         }
 678
 679
 680 }
 681
 682
 683 /* End of file postgre_driver.php */
 684 /* Location: ./system/database/drivers/postgre/postgre_driver.php */