[Sommaire] [Imprimer]

   1  <?php # $Id: postgres.inc.php 1178 2006-05-01 13:53:40Z garvinhicking $
   2  # Copyright (c) 2003-2005, Jannis Hermanns (on behalf the Serendipity Developer Team)
   3  # All rights reserved.  See LICENSE file for licensing details
   4  
   5  /**
   6   * Tells the DB Layer to start a DB transaction.
   7   *
   8   * @access public
   9   */
  10  function serendipity_db_begin_transaction(){
  11      serendipity_db_query('begin work');
  12  }
  13  
  14  /**
  15   * Tells the DB Layer to end a DB transaction.
  16   *
  17   * @access public
  18   * @param  boolean  If true, perform the query. If false, rollback.
  19   */
  20  function serendipity_db_end_transaction($commit){
  21      if ($commit){
  22          serendipity_db_query('commit');
  23      }else{
  24          serendipity_db_query('rollback');
  25      }
  26  }
  27  
  28  /**
  29   * Assemble and return SQL condition for a "IN (...)" clause
  30   *
  31   * @access public
  32   * @param  string   table column name
  33   * @param  array    referenced array of values to search for in the "IN (...)" clause
  34   * @param  string   condition of how to associate the different input values of the $search_ids parameter
  35   * @return string   resulting SQL string
  36   */
  37  function serendipity_db_in_sql($col, &$search_ids, $type = ' OR ') {
  38      return $col . " IN (" . implode(', ', $search_ids) . ")";
  39  }
  40  
  41  /**
  42   * Connect to the configured Database
  43   *
  44   * @access public
  45   * @return  ressource   connection handle
  46   */
  47  function serendipity_db_connect() {
  48      global $serendipity;
  49  
  50      if (isset($serendipity['dbPersistent']) && $serendipity['dbPersistent']) {
  51          $function = 'pg_pconnect';
  52      } else {
  53          $function = 'pg_connect';
  54      }
  55  
  56      $serendipity['dbConn'] = $function(
  57                                 sprintf(
  58                                   '%sdbname=%s user=%s password=%s',
  59                                   strlen($serendipity['dbHost']) ? ('host=' . $serendipity['dbHost'] . ' ') : '',
  60                                   $serendipity['dbName'],
  61                                   $serendipity['dbUser'],
  62                                   $serendipity['dbPass']
  63                                 )
  64                               );
  65  
  66      return $serendipity['dbConn'];
  67  }
  68  
  69  function serendipity_db_reconnect() {
  70  }
  71  
  72  /**
  73   * Returns a escaped string, so that it can be safely included in a SQL string encapsulated within quotes, without allowing SQL injection.
  74   *
  75   * @access  public
  76   * @param   string   input string
  77   * @return  string   output string
  78   */
  79  function serendipity_db_escape_string($string) {
  80      return pg_escape_string($string);
  81  }
  82  
  83  /**
  84   * Returns the option to a LIMIT SQL statement, because it varies accross DB systems
  85   *
  86   * @access public
  87   * @param  int      Number of the first row to return data from
  88   * @param  int      Number of rows to return
  89   * @return string   SQL string to pass to a LIMIT statement
  90   */
  91  function serendipity_db_limit($start, $offset) {
  92      return $offset . ', ' . $start;
  93  }
  94  
  95  /**
  96   * Return a LIMIT SQL option to the DB Layer as a full LIMIT statement
  97   *
  98   * @access public
  99   * @param   SQL string of a LIMIT option
 100   * @return  SQL string containing a full LIMIT statement
 101   */
 102  function serendipity_db_limit_sql($limitstring) {
 103      $limit_split = split(',', $limitstring);
 104      if (count($limit_split) > 1) {
 105          $limit = ' LIMIT ' . $limit_split[0] . ' OFFSET ' . $limit_split[1];
 106      } else {
 107          $limit = ' LIMIT ' . $limit_split[0];
 108      }
 109      return $limit;
 110  }
 111  
 112  /**
 113   * Returns the number of affected rows of a SQL query
 114   *
 115   * @access public
 116   * @return int      Number of affected rows
 117   */
 118  function serendipity_db_affected_rows() {
 119      global $serendipity;
 120      return pg_affected_rows($serendipity['dbLastResult']);
 121  }
 122  
 123  /**
 124   * Returns the number of updated rows in a SQL query
 125   *
 126   * @access public
 127   * @return int  Number of updated rows
 128   */
 129  function serendipity_db_updated_rows() {
 130      global $serendipity;
 131      // it is unknown whether pg_affected_rows returns number of rows
 132      //  UPDATED or MATCHED on an UPDATE statement.
 133      return pg_affected_rows($serendipity['dbLastResult']);
 134  }
 135  
 136  /**
 137   * Returns the number of matched rows in a SQL query
 138   *
 139   * @access public
 140   * @return int  Number of matched rows
 141   */
 142  function serendipity_db_matched_rows() {
 143      global $serendipity;
 144      // it is unknown whether pg_affected_rows returns number of rows
 145      //  UPDATED or MATCHED on an UPDATE statement.
 146      return pg_affected_rows($serendipity['dbLastResult']);
 147  }
 148  
 149  /**
 150   * Returns the latest INSERT_ID of an SQL INSERT INTO command, for auto-increment columns
 151   *
 152   * @access public
 153   * @param  string   Name of the table to get a INSERT ID for
 154   * @param  string   Name of the column to get a INSERT ID for
 155   * @return int      Value of the auto-increment column
 156   */
 157  function serendipity_db_insert_id($table = '', $id = '') {
 158      global $serendipity;
 159      if (empty($table) || empty($id)) {
 160          // BC - will/should never be called with empty parameters!
 161          return pg_last_oid($serendipity['dbLastResult']);
 162      } else {
 163          $query = "SELECT currval('{$serendipity['dbPrefix']}{$table}_{$id}_seq'::text) AS {$id}";
 164          $res   = pg_query($serendipity['dbConn'], $query);
 165          if (pg_num_rows($res)) {
 166              $insert_id = pg_fetch_array($res, 0, PGSQL_ASSOC);
 167              return $insert_id[$id];
 168          } else {
 169              return pg_last_oid($serendipity['dbLastResult']); // BC - should not happen!
 170          }
 171      }
 172  }
 173  
 174  /**
 175   * Perform a DB Layer SQL query.
 176   *
 177   * This function returns values dependin on the input parameters and the result of the query.
 178   * It can return:
 179   *   false if there was an error,
 180   *   true if the query succeeded but did not generate any rows
 181   *   array of field values if it returned a single row and $single is true
 182   *   array of array of field values if it returned row(s) [stacked array]
 183   *
 184   * @access public
 185   * @param   string      SQL query to execute
 186   * @param   boolean     Toggle whether the expected result is a single row (TRUE) or multiple rows (FALSE). This affects whether the returned array is 1 or 2 dimensional!
 187   * @param   string      Result type of the array indexing. Can be one of "assoc" (associative), "num" (numerical), "both" (numerical and associative, default)
 188   * @param   boolean     If true, errors will be reported. If false, errors will be ignored.
 189   * @param   string      A possible array key name, so that you can control the multi-dimensional mapping of an array by the key column
 190   * @param   string      A possible array field name, so that you can control the multi-dimensional mapping of an array by the key column and the field value.
 191   * @param   boolean     If true, the executed SQL error is known to fail, and should be disregarded (errors can be ignroed on DUPLICATE INDEX queries and the likes)
 192   * @return  mixed       Returns the result of the SQL query, depending on the input parameters
 193   */
 194  function &serendipity_db_query($sql, $single = false, $result_type = "both", $reportErr = false, $assocKey = false, $assocVal = false, $expectError = false) {
 195      global $serendipity;
 196      static $type_map = array(
 197                           'assoc' => PGSQL_ASSOC,
 198                           'num'   => PGSQL_NUM,
 199                           'both'  => PGSQL_BOTH,
 200                           'true'  => true,
 201                           'false' => false
 202      );
 203  
 204      if (!isset($serendipity['dbPgsqlOIDS'])) {
 205          $serendipity['dbPgsqlOIDS'] = true;
 206          @serendipity_db_query('SET default_with_oids = true', true, 'both', false, false, false, true);
 207      }
 208  
 209      if (!$expectError && ($reportErr || !$serendipity['production'])) {
 210          $serendipity['dbLastResult'] = pg_query($serendipity['dbConn'], $sql);
 211      } else {
 212          $serendipity['dbLastResult'] = @pg_query($serendipity['dbConn'], $sql);
 213      }
 214  
 215      if (!$serendipity['dbLastResult']) {
 216          if (!$expectError && !$serendipity['production']) {
 217              print "Error in $sql<br/>\n";
 218              print pg_last_error($serendipity['dbConn']) . "<BR/>\n";
 219              if (function_exists('debug_backtrace')) {
 220                  highlight_string(var_export(debug_backtrace(), 1));
 221              }
 222              print "<br><code>$sql</code>\n";
 223          }
 224          return $type_map['false'];
 225      }
 226  
 227      if ($serendipity['dbLastResult'] === true) {
 228          return $type_map['true'];
 229      }
 230  
 231      $result_type = $type_map[$result_type];
 232  
 233      $n = pg_num_rows($serendipity['dbLastResult']);
 234  
 235      switch ($n) {
 236          case 0:
 237              if ($single) {
 238                  return $type_map['false'];
 239              }
 240              return $type_map['true'];
 241          case 1:
 242              if ($single) {
 243                  return pg_fetch_array($serendipity['dbLastResult'], 0, $result_type);
 244              }
 245          default:
 246              $rows = array();
 247              for ($i = 0; $i < $n; $i++) {
 248                  if (!empty($assocKey)) {
 249                      // You can fetch a key-associated array via the two function parameters assocKey and assocVal
 250                      $row = pg_fetch_array($serendipity['dbLastResult'], $i, $result_type);
 251                      if (empty($assocVal)) {
 252                          $rows[$row[$assocKey]] = $row;
 253                      } else {
 254                          $rows[$row[$assocKey]] = $row[$assocVal];
 255                      }
 256                  } else {
 257                      $rows[] = pg_fetch_array($serendipity['dbLastResult'], $i, $result_type);
 258                  }
 259              }
 260              return $rows;
 261      }
 262  }
 263  
 264  /**
 265   * Prepares a Serendipty query input to fully valid SQL. Replaces certain "template" variables.
 266   *
 267   * @access public
 268   * @param  string   SQL query with template variables to convert
 269   * @return ressource    SQL ressource handle of the executed query
 270   */
 271  function serendipity_db_schema_import($query) {
 272      static $search  = array('{AUTOINCREMENT}', '{PRIMARY}', '{UNSIGNED}',
 273          '{FULLTEXT}', '{BOOLEAN}', 'int(1)', 'int(10)', 'int(11)', 'int(4)', '{UTF_8}');
 274      static $replace = array('SERIAL', 'primary key', '', '', 'BOOLEAN NOT NULL', 'int2',
 275          'int4', 'int4', 'int4', '');
 276  
 277      if (stristr($query, '{FULLTEXT_MYSQL}')) {
 278          return true;
 279      }
 280  
 281      $query = trim(str_replace($search, $replace, $query));
 282      if ($query{0} == '@') {
 283          // Errors are expected to happen (like duplicate index creation)
 284          return serendipity_db_query(substr($query, 1), false, 'both', false, false, false, true);
 285      } else {
 286          return serendipity_db_query($query);
 287      }
 288  }
 289  
 290  /**
 291   * Try to connect to the configured Database (during installation)
 292   *
 293   * @access public
 294   * @param  array     input configuration array, holding the connection info
 295   * @param  array     referenced array which holds the errors that might be encountered
 296   * @return boolean   return true on success, false on error
 297   */
 298  function serendipity_db_probe($hash, &$errs) {
 299      global $serendipity;
 300  
 301      if (!function_exists('pg_connect')) {
 302          $errs[] = 'No PostgreSQL extension found. Please check your webserver installation or contact your systems administrator regarding this problem.';
 303          return false;
 304      }
 305  
 306      $serendipity['dbConn'] = pg_connect(
 307                                 sprintf(
 308                                   '%sdbname=%s user=%s password=%s',
 309  
 310                                   strlen($hash['dbHost']) ? ('host=' . $hash['dbHost'] . ' ') : '',
 311                                   $hash['dbName'],
 312                                   $hash['dbUser'],
 313                                   $hash['dbPass']
 314                                 )
 315                               );
 316  
 317      if (!$serendipity['dbConn']) {
 318          $errs[] = 'Could not connect to database; check your settings.';
 319          return false;
 320      }
 321  
 322      return true;
 323  }
 324  
 325  /**
 326   * Returns the SQL code used for concatenating strings
 327   *
 328   * @access public
 329   * @param  string   Input string/column to concatenate
 330   * @return string   SQL parameter
 331   */
 332  function serendipity_db_concat($string) {
 333      return '(' . str_replace(', ', '||', $string) . ')';
 334  }
 335  
 336  /* vim: set sts=4 ts=4 expandtab : */