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, 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         /**
  38          * The syntax to count rows is slightly different across different
  39          * database engines, so this string appears in each driver and is
  40          * used for the count_all() and count_all_results() functions.
  41          */
  42         var $_count_string = "SELECT COUNT(*) AS ";
  43         var $_random_keyword = ' RANDOM()'; // database specific random keyword
  44
  45         /**
  46          * Connection String
  47          *
  48          * @access      private
  49          * @return      string
  50          */
  51         function _connect_string()
  52         {
  53                 $components = array(
  54                                                                 'hostname'      => 'host',
  55                                                                 'port'          => 'port',
  56                                                                 'database'      => 'dbname',
  57                                                                 'username'      => 'user',
  58                                                                 'password'      => 'password'
  59                                                         );
  60
  61                 $connect_string = "";
  62                 foreach ($components as $key => $val)
  63                 {
  64                         if (isset($this->$key) && $this->$key != '')
  65                         {
  66                                 $connect_string .= " $val=".$this->$key;
  67                         }
  68                 }
  69                 return trim($connect_string);
  70         }
  71
  72         // --------------------------------------------------------------------
  73
  74         /**
  75          * Non-persistent database connection
  76          *
  77          * @access      private called by the base class
  78          * @return      resource
  79          */
  80         function db_connect()
  81         {
  82                 return @pg_connect($this->_connect_string());
  83         }
  84
  85         // --------------------------------------------------------------------
  86
  87         /**
  88          * Persistent database connection
  89          *
  90          * @access      private called by the base class
  91          * @return      resource
  92          */
  93         function db_pconnect()
  94         {
  95                 return @pg_pconnect($this->_connect_string());
  96         }
  97
  98         // --------------------------------------------------------------------
  99
 100         /**
 101          * Select the database
 102          *
 103          * @access      private called by the base class
 104          * @return      resource
 105          */
 106         function db_select()
 107         {
 108                 // Not needed for Postgre so we'll return TRUE
 109                 return TRUE;
 110         }
 111
 112         // --------------------------------------------------------------------
 113
 114         /**
 115          * Set client character set
 116          *
 117          * @access      public
 118          * @param       string
 119          * @param       string
 120          * @return      resource
 121          */
 122         function db_set_charset($charset, $collation)
 123         {
 124                 // @todo - add support if needed
 125                 return TRUE;
 126         }
 127
 128         // --------------------------------------------------------------------
 129
 130         /**
 131          * Version number query string
 132          *
 133          * @access      public
 134          * @return      string
 135          */
 136         function _version()
 137         {
 138                 return "SELECT version() AS ver";
 139         }
 140
 141         // --------------------------------------------------------------------
 142
 143         /**
 144          * Execute the query
 145          *
 146          * @access      private called by the base class
 147          * @param       string  an SQL query
 148          * @return      resource
 149          */
 150         function _execute($sql)
 151         {
 152                 $sql = $this->_prep_query($sql);
 153                 return @pg_query($this->conn_id, $sql);
 154         }
 155
 156         // --------------------------------------------------------------------
 157
 158         /**
 159          * Prep the query
 160          *
 161          * If needed, each database adapter can prep the query string
 162          *
 163          * @access      private called by execute()
 164          * @param       string  an SQL query
 165          * @return      string
 166          */
 167         function _prep_query($sql)
 168         {
 169                 return $sql;
 170         }
 171
 172         // --------------------------------------------------------------------
 173
 174         /**
 175          * Begin Transaction
 176          *
 177          * @access      public
 178          * @return      bool
 179          */
 180         function trans_begin($test_mode = FALSE)
 181         {
 182                 if ( ! $this->trans_enabled)
 183                 {
 184                         return TRUE;
 185                 }
 186
 187                 // When transactions are nested we only begin/commit/rollback the outermost ones
 188                 if ($this->_trans_depth > 0)
 189                 {
 190                         return TRUE;
 191                 }
 192
 193                 // Reset the transaction failure flag.
 194                 // If the $test_mode flag is set to TRUE transactions will be rolled back
 195                 // even if the queries produce a successful result.
 196                 $this->_trans_failure = ($test_mode === TRUE) ? TRUE : FALSE;
 197
 198                 return @pg_exec($this->conn_id, "begin");
 199         }
 200
 201         // --------------------------------------------------------------------
 202
 203         /**
 204          * Commit Transaction
 205          *
 206          * @access      public
 207          * @return      bool
 208          */
 209         function trans_commit()
 210         {
 211                 if ( ! $this->trans_enabled)
 212                 {
 213                         return TRUE;
 214                 }
 215
 216                 // When transactions are nested we only begin/commit/rollback the outermost ones
 217                 if ($this->_trans_depth > 0)
 218                 {
 219                         return TRUE;
 220                 }
 221
 222                 return @pg_exec($this->conn_id, "commit");
 223         }
 224
 225         // --------------------------------------------------------------------
 226
 227         /**
 228          * Rollback Transaction
 229          *
 230          * @access      public
 231          * @return      bool
 232          */
 233         function trans_rollback()
 234         {
 235                 if ( ! $this->trans_enabled)
 236                 {
 237                         return TRUE;
 238                 }
 239
 240                 // When transactions are nested we only begin/commit/rollback the outermost ones
 241                 if ($this->_trans_depth > 0)
 242                 {
 243                         return TRUE;
 244                 }
 245
 246                 return @pg_exec($this->conn_id, "rollback");
 247         }
 248
 249         // --------------------------------------------------------------------
 250
 251         /**
 252          * Escape String
 253          *
 254          * @access      public
 255          * @param       string
 256          * @return      string
 257          */
 258         function escape_str($str)
 259         {
 260                 return pg_escape_string($str);
 261         }
 262
 263         // --------------------------------------------------------------------
 264
 265         /**
 266          * Affected Rows
 267          *
 268          * @access      public
 269          * @return      integer
 270          */
 271         function affected_rows()
 272         {
 273                 return @pg_affected_rows($this->result_id);
 274         }
 275
 276         // --------------------------------------------------------------------
 277
 278         /**
 279          * Insert ID
 280          *
 281          * @access      public
 282          * @return      integer
 283          */
 284         function insert_id()
 285         {
 286                 $v = $this->_version();
 287                 $v = $v['server'];
 288
 289                 $table  = func_num_args() > 0 ? func_get_arg(0) : null;
 290                 $column = func_num_args() > 1 ? func_get_arg(1) : null;
 291
 292                 if ($table == null && $v >= '8.1')
 293                 {
 294                         $sql='SELECT LASTVAL() as ins_id';
 295                 }
 296                 elseif ($table != null && $column != null && $v >= '8.0')
 297                 {
 298                         $sql = sprintf("SELECT pg_get_serial_sequence('%s','%s') as seq", $table, $column);
 299                         $query = $this->query($sql);
 300                         $row = $query->row();
 301                         $sql = sprintf("SELECT CURRVAL('%s') as ins_id", $row->seq);
 302                 }
 303                 elseif ($table != null)
 304                 {
 305                         // seq_name passed in table parameter
 306                         $sql = sprintf("SELECT CURRVAL('%s') as ins_id", $table);
 307                 }
 308                 else
 309                 {
 310                         return pg_last_oid($this->result_id);
 311                 }
 312                 $query = $this->query($sql);
 313                 $row = $query->row();
 314                 return $row->ins_id;
 315         }
 316
 317         // --------------------------------------------------------------------
 318
 319         /**
 320          * "Count All" query
 321          *
 322          * Generates a platform-specific query string that counts all records in
 323          * the specified database
 324          *
 325          * @access      public
 326          * @param       string
 327          * @return      string
 328          */
 329         function count_all($table = '')
 330         {
 331                 if ($table == '')
 332                         return '0';
 333
 334                 $query = $this->query($this->_count_string . $this->_protect_identifiers('numrows'). " FROM " . $this->_protect_identifiers($table, TRUE, NULL, FALSE));
 335
 336                 if ($query->num_rows() == 0)
 337                         return '0';
 338
 339                 $row = $query->row();
 340                 return $row->numrows;
 341         }
 342
 343         // --------------------------------------------------------------------
 344
 345         /**
 346          * Show table query
 347          *
 348          * Generates a platform-specific query string so that the table names can be fetched
 349          *
 350          * @access      private
 351          * @param       boolean
 352          * @return      string
 353          */
 354         function _list_tables($prefix_limit = FALSE)
 355         {
 356                 $sql = "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'";
 357
 358                 if ($prefix_limit !== FALSE AND $this->dbprefix != '')
 359                 {
 360                         $sql .= " AND table_name LIKE '".$this->dbprefix."%'";
 361                 }
 362
 363                 return $sql;
 364         }
 365
 366         // --------------------------------------------------------------------
 367
 368         /**
 369          * Show column query
 370          *
 371          * Generates a platform-specific query string so that the column names can be fetched
 372          *
 373          * @access      public
 374          * @param       string  the table name
 375          * @return      string
 376          */
 377         function _list_columns($table = '')
 378         {
 379                 return "SELECT column_name FROM information_schema.columns WHERE table_name ='".$table."'";
 380         }
 381
 382         // --------------------------------------------------------------------
 383
 384         /**
 385          * Field data query
 386          *
 387          * Generates a platform-specific query so that the column data can be retrieved
 388          *
 389          * @access      public
 390          * @param       string  the table name
 391          * @return      object
 392          */
 393         function _field_data($table)
 394         {
 395                 return "SELECT * FROM ".$table." LIMIT 1";
 396         }
 397
 398         // --------------------------------------------------------------------
 399
 400         /**
 401          * The error message string
 402          *
 403          * @access      private
 404          * @return      string
 405          */
 406         function _error_message()
 407         {
 408                 return pg_last_error($this->conn_id);
 409         }
 410
 411         // --------------------------------------------------------------------
 412
 413         /**
 414          * The error message number
 415          *
 416          * @access      private
 417          * @return      integer
 418          */
 419         function _error_number()
 420         {
 421                 return '';
 422         }
 423
 424         // --------------------------------------------------------------------
 425
 426         /**
 427          * Escape the SQL Identifiers
 428          *
 429          * This function escapes column and table names
 430          *
 431          * @access      private
 432          * @param       string
 433          * @return      string
 434          */
 435         function _escape_identifiers($item)
 436         {
 437                 if ($this->_escape_char == '')
 438                 {
 439                         return $item;
 440                 }
 441
 442                 if (strpos($item, '.') !== FALSE)
 443                 {
 444                         $str = $this->_escape_char.str_replace('.', $this->_escape_char.'.'.$this->_escape_char, $item).$this->_escape_char;
 445                 }
 446                 else
 447                 {
 448                         $str = $this->_escape_char.$item.$this->_escape_char;
 449                 }
 450
 451                 // remove duplicates if the user already included the escape
 452                 return preg_replace('/['.$this->_escape_char.']+/', $this->_escape_char, $str);
 453         }
 454
 455         // --------------------------------------------------------------------
 456
 457         /**
 458          * From Tables
 459          *
 460          * This function implicitly groups FROM tables so there is no confusion
 461          * about operator precedence in harmony with SQL standards
 462          *
 463          * @access      public
 464          * @param       type
 465          * @return      type
 466          */
 467         function _from_tables($tables)
 468         {
 469                 if ( ! is_array($tables))
 470                 {
 471                         $tables = array($tables);
 472                 }
 473
 474                 return implode(', ', $tables);
 475         }
 476
 477         // --------------------------------------------------------------------
 478
 479         /**
 480          * Insert statement
 481          *
 482          * Generates a platform-specific insert string from the supplied data
 483          *
 484          * @access      public
 485          * @param       string  the table name
 486          * @param       array   the insert keys
 487          * @param       array   the insert values
 488          * @return      string
 489          */
 490         function _insert($table, $keys, $values)
 491         {
 492                 return "INSERT INTO ".$table." (".implode(', ', $keys).") VALUES (".implode(', ', $values).")";
 493         }
 494
 495         // --------------------------------------------------------------------
 496
 497         /**
 498          * Update statement
 499          *
 500          * Generates a platform-specific update string from the supplied data
 501          *
 502          * @access      public
 503          * @param       string  the table name
 504          * @param       array   the update data
 505          * @param       array   the where clause
 506          * @param       array   the orderby clause
 507          * @param       array   the limit clause
 508          * @return      string
 509          */
 510         function _update($table, $values, $where, $orderby = array(), $limit = FALSE)
 511         {
 512                 foreach($values as $key => $val)
 513                 {
 514                         $valstr[] = $key." = ".$val;
 515                 }
 516
 517                 $limit = ( ! $limit) ? '' : ' LIMIT '.$limit;
 518
 519                 $orderby = (count($orderby) >= 1)?' ORDER BY '.implode(", ", $orderby):'';
 520
 521                 $sql = "UPDATE ".$table." SET ".implode(', ', $valstr);
 522
 523                 $sql .= ($where != '' AND count($where) >=1) ? " WHERE ".implode(" ", $where) : '';
 524
 525                 $sql .= $orderby.$limit;
 526
 527                 return $sql;
 528         }
 529
 530         // --------------------------------------------------------------------
 531
 532         /**
 533          * Truncate statement
 534          *
 535          * Generates a platform-specific truncate string from the supplied data
 536          * If the database does not support the truncate() command
 537          * This function maps to "DELETE FROM table"
 538          *
 539          * @access      public
 540          * @param       string  the table name
 541          * @return      string
 542          */
 543         function _truncate($table)
 544         {
 545                 return "TRUNCATE ".$table;
 546         }
 547
 548         // --------------------------------------------------------------------
 549
 550         /**
 551          * Delete statement
 552          *
 553          * Generates a platform-specific delete string from the supplied data
 554          *
 555          * @access      public
 556          * @param       string  the table name
 557          * @param       array   the where clause
 558          * @param       string  the limit clause
 559          * @return      string
 560          */
 561         function _delete($table, $where = array(), $like = array(), $limit = FALSE)
 562         {
 563                 $conditions = '';
 564
 565                 if (count($where) > 0 OR count($like) > 0)
 566                 {
 567                         $conditions = "\nWHERE ";
 568                         $conditions .= implode("\n", $this->ar_where);
 569
 570                         if (count($where) > 0 && count($like) > 0)
 571                         {
 572                                 $conditions .= " AND ";
 573                         }
 574                         $conditions .= implode("\n", $like);
 575                 }
 576
 577                 $limit = ( ! $limit) ? '' : ' LIMIT '.$limit;
 578
 579                 return "DELETE FROM ".$table.$conditions.$limit;
 580         }
 581
 582         // --------------------------------------------------------------------
 583         /**
 584          * Limit string
 585          *
 586          * Generates a platform-specific LIMIT clause
 587          *
 588          * @access      public
 589          * @param       string  the sql query string
 590          * @param       integer the number of rows to limit the query to
 591          * @param       integer the offset value
 592          * @return      string
 593          */
 594         function _limit($sql, $limit, $offset)
 595         {
 596                 $sql .= "LIMIT ".$limit;
 597
 598                 if ($offset > 0)
 599                 {
 600                         $sql .= " OFFSET ".$offset;
 601                 }
 602
 603                 return $sql;
 604         }
 605
 606         // --------------------------------------------------------------------
 607
 608         /**
 609          * Close DB Connection
 610          *
 611          * @access      public
 612          * @param       resource
 613          * @return      void
 614          */
 615         function _close($conn_id)
 616         {
 617                 @pg_close($conn_id);
 618         }
 619
 620
 621 }
 622
 623
 624 /* End of file postgre_driver.php */
 625 /* Location: ./system/database/drivers/postgre/postgre_driver.php */