[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['dbConn']->beginTransaction();
  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['dbConn']->commit();
  23      }else{
  24          $serendipity['dbConn']->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      $serendipity['dbConn'] = new PDO(
  51                                 sprintf(
  52                                   'pgsql:%sdbname=%s',
  53                                   strlen($serendipity['dbHost']) ? ('host=' . $serendipity['dbHost'] . ';') : '',
  54                                   $serendipity['dbName']
  55                                 ),
  56                                 $serendipity['dbUser'],
  57                                 $serendipity['dbPass']
  58                               );
  59  
  60      return $serendipity['dbConn'];
  61  }
  62  
  63  function serendipity_db_reconnect() {
  64  }
  65  
  66  /**
  67   * Returns a escaped string, so that it can be safely included in a SQL string encapsulated within quotes, without allowing SQL injection.
  68   *
  69   * @access  public
  70   * @param   string   input string
  71   * @return  string   output string
  72   */
  73  function serendipity_db_escape_string($string) {
  74      global $serendipity;
  75      return substr($serendipity['dbConn']->quote($string), 1, -1);
  76  }
  77  
  78  /**
  79   * Returns the option to a LIMIT SQL statement, because it varies accross DB systems
  80   *
  81   * @access public
  82   * @param  int      Number of the first row to return data from
  83   * @param  int      Number of rows to return
  84   * @return string   SQL string to pass to a LIMIT statement
  85   */
  86  function serendipity_db_limit($start, $offset) {
  87      return $offset . ', ' . $start;
  88  }
  89  
  90  /**
  91   * Return a LIMIT SQL option to the DB Layer as a full LIMIT statement
  92   *
  93   * @access public
  94   * @param   SQL string of a LIMIT option
  95   * @return  SQL string containing a full LIMIT statement
  96   */
  97  function serendipity_db_limit_sql($limitstring) {
  98      $limit_split = split(',', $limitstring);
  99      if (count($limit_split) > 1) {
 100          $limit = ' LIMIT ' . $limit_split[0] . ' OFFSET ' . $limit_split[1];
 101      } else {
 102          $limit = ' LIMIT ' . $limit_split[0];
 103      }
 104      return $limit;
 105  }
 106  
 107  /**
 108   * Returns the number of affected rows of a SQL query
 109   *
 110   * @access public
 111   * @return int      Number of affected rows
 112   */
 113  function serendipity_db_affected_rows() {
 114      global $serendipity;
 115      return $serendipity['dbSth']->rowCount();
 116  }
 117  
 118  /**
 119   * Returns the number of updated rows in a SQL query
 120   *
 121   * @access public
 122   * @return int  Number of updated rows
 123   */
 124  function serendipity_db_updated_rows() {
 125      global $serendipity;
 126      // it is unknown whether pg_affected_rows returns number of rows
 127      //  UPDATED or MATCHED on an UPDATE statement.
 128      return $serendipity['dbSth']->rowCount();
 129  }
 130  
 131  /**
 132   * Returns the number of matched rows in a SQL query
 133   *
 134   * @access public
 135   * @return int  Number of matched rows
 136   */
 137  function serendipity_db_matched_rows() {
 138      global $serendipity;
 139      // it is unknown whether pg_affected_rows returns number of rows
 140      //  UPDATED or MATCHED on an UPDATE statement.
 141      return $serendipity['dbSth']->rowCount();
 142  }
 143  
 144  /**
 145   * Returns the latest INSERT_ID of an SQL INSERT INTO command, for auto-increment columns
 146   *
 147   * @access public
 148   * @param  string   Name of the table to get a INSERT ID for
 149   * @param  string   Name of the column to get a INSERT ID for
 150   * @return int      Value of the auto-increment column
 151   */
 152  function serendipity_db_insert_id($table = '', $id = '') {
 153      global $serendipity;
 154      if (empty($table) || empty($id)) {
 155          // BC - will/should never be called with empty parameters!
 156          return $serendipity['dbConn']->lastInsertId();
 157      } else {
 158          $query = "SELECT currval('{$serendipity['dbPrefix']}{$table}_{$id}_seq'::text) AS {$id}";
 159          $res = $serendipity['dbConn']->prepare($query);
 160          $res->execute();
 161          foreach($res->fetchAll(PDO::FETCH_ASSOC) as $row) {
 162              return $row[$id];
 163          }
 164          return $serendipity['dbConn']->lastInsertId();
 165      }
 166  }
 167  
 168  /**
 169   * Perform a DB Layer SQL query.
 170   *
 171   * This function returns values dependin on the input parameters and the result of the query.
 172   * It can return:
 173   *   false if there was an error,
 174   *   true if the query succeeded but did not generate any rows
 175   *   array of field values if it returned a single row and $single is true
 176   *   array of array of field values if it returned row(s) [stacked array]
 177   *
 178   * @access public
 179   * @param   string      SQL query to execute
 180   * @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!
 181   * @param   string      Result type of the array indexing. Can be one of "assoc" (associative), "num" (numerical), "both" (numerical and associative, default)
 182   * @param   boolean     If true, errors will be reported. If false, errors will be ignored.
 183   * @param   string      A possible array key name, so that you can control the multi-dimensional mapping of an array by the key column
 184   * @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.
 185   * @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)
 186   * @return  mixed       Returns the result of the SQL query, depending on the input parameters
 187   */
 188  function &serendipity_db_query($sql, $single = false, $result_type = "both", $reportErr = false, $assocKey = false, $assocVal = false, $expectError = false) {
 189      global $serendipity;
 190      static $type_map = array(
 191                           'assoc' => PDO::FETCH_ASSOC,
 192                           'num'   => PDO::FETCH_NUM,
 193                           'both'  => PDO::FETCH_BOTH,
 194                           'true'  => true,
 195                           'false' => false
 196      );
 197  
 198      if (!$expectError && ($reportErr || !$serendipity['production'])) {
 199          $serendipity['dbSth'] = $serendipity['dbConn']->prepare($sql);
 200      } else {
 201          $serendipity['dbSth'] = $serendipity['dbConn']->prepare($sql);
 202      }
 203  
 204      if (!$serendipity['dbSth']) {
 205          if (!$expectError && !$serendipity['production']) {
 206              print "Error in $sql<br/>\n";
 207              print $serendipity['dbConn']->errorInfo() . "<BR/>\n";
 208              if (function_exists('debug_backtrace')) {
 209                  highlight_string(var_export(debug_backtrace(), 1));
 210              }
 211              print "<br><code>$sql</code>\n";
 212          }
 213          return $type_map['false'];
 214      }
 215  
 216      $serendipity['dbSth']->execute();
 217  
 218      if ($serendipity['dbSth'] === true) {
 219          return $type_map['true'];
 220      }
 221  
 222      $result_type = $type_map[$result_type];
 223  
 224      $n = 0;
 225  
 226      $rows = array();
 227      foreach($serendipity['dbSth']->fetchAll($result_type) as $row) {
 228          if (!empty($assocKey)) {
 229              // You can fetch a key-associated array via the two function parameters assocKey and assocVal
 230              if (empty($assocVal)) {
 231                  $rows[$row[$assocKey]] = $row;
 232              } else {
 233                  $rows[$row[$assocKey]] = $row[$assocVal];
 234              }
 235          } else {
 236              $rows[] = $row;
 237          }
 238      }
 239      if(count($rows) == 0) {
 240          if ($single) {
 241              return $type_map['false'];
 242          }
 243          return $type_map['true'];
 244      }
 245      if(count($rows) == 1 && $single) {
 246          return $rows[0];
 247      }
 248      return $rows;
 249  }
 250  
 251  /**
 252   * Prepares a Serendipty query input to fully valid SQL. Replaces certain "template" variables.
 253   *
 254   * @access public
 255   * @param  string   SQL query with template variables to convert
 256   * @return ressource    SQL ressource handle of the executed query
 257   */
 258  function serendipity_db_schema_import($query) {
 259      static $search  = array('{AUTOINCREMENT}', '{PRIMARY}', '{UNSIGNED}',
 260          '{FULLTEXT}', '{BOOLEAN}', 'int(1)', 'int(10)', 'int(11)', 'int(4)', '{UTF_8}');
 261      static $replace = array('SERIAL', 'primary key', '', '', 'BOOLEAN NOT NULL', 'int2',
 262          'int4', 'int4', 'int4', '');
 263  
 264      if (stristr($query, '{FULLTEXT_MYSQL}')) {
 265          return true;
 266      }
 267  
 268      $query = trim(str_replace($search, $replace, $query));
 269      if ($query{0} == '@') {
 270          // Errors are expected to happen (like duplicate index creation)
 271          return serendipity_db_query(substr($query, 1), false, 'both', false, false, false, true);
 272      } else {
 273          return serendipity_db_query($query);
 274      }
 275  }
 276  
 277  /**
 278   * Try to connect to the configured Database (during installation)
 279   *
 280   * @access public
 281   * @param  array     input configuration array, holding the connection info
 282   * @param  array     referenced array which holds the errors that might be encountered
 283   * @return boolean   return true on success, false on error
 284   */
 285  function serendipity_db_probe($hash, &$errs) {
 286      global $serendipity;
 287  
 288      if(!in_array('pgsql', PDO::getAvailableDrivers())) {
 289          $errs[] = 'PDO_PGSQL driver not avialable';
 290          return false;
 291      }
 292  
 293      $serendipity['dbConn'] = new PDO(
 294                                 sprintf(
 295                                   'pgsql:%sdbname=%s',
 296                                   strlen($hash['dbHost']) ? ('host=' . $hash['dbHost'] . ';') : '',
 297                                   $hash['dbName']
 298                                 ),
 299                                 $hash['dbUser'],
 300                                 $hash['dbPass']
 301                               );
 302  
 303      if (!$serendipity['dbConn']) {
 304          $errs[] = 'Could not connect to database; check your settings.';
 305          return false;
 306      }
 307  
 308      return true;
 309  }
 310  
 311  /**
 312   * Returns the SQL code used for concatenating strings
 313   *
 314   * @access public
 315   * @param  string   Input string/column to concatenate
 316   * @return string   SQL parameter
 317   */
 318  function serendipity_db_concat($string) {
 319      return '(' . str_replace(', ', '||', $string) . ')';
 320  }
 321  
 322  /* vim: set sts=4 ts=4 expandtab : */