[Sommaire] [Imprimer]

   1  <?php
   2  /* SVN FILE: $Id: dbo_postgres.php 4409 2007-02-02 13:20:59Z phpnut $ */
   3  
   4  /**
   5   * PostgreSQL layer for DBO.
   6   *
   7   * Long description for file
   8   *
   9   * PHP versions 4 and 5
  10   *
  11   * CakePHP(tm) :  Rapid Development Framework <http://www.cakephp.org/>
  12   * Copyright 2005-2007, Cake Software Foundation, Inc.
  13   *                                1785 E. Sahara Avenue, Suite 490-204
  14   *                                Las Vegas, Nevada 89104
  15   *
  16   * Licensed under The MIT License
  17   * Redistributions of files must retain the above copyright notice.
  18   *
  19   * @filesource
  20   * @copyright        Copyright 2005-2007, Cake Software Foundation, Inc.
  21   * @link                http://www.cakefoundation.org/projects/info/cakephp CakePHP(tm) Project
  22   * @package            cake
  23   * @subpackage        cake.cake.libs.model.dbo
  24   * @since            CakePHP(tm) v 0.9.1.114
  25   * @version            $Revision: 4409 $
  26   * @modifiedby        $LastChangedBy: phpnut $
  27   * @lastmodified    $Date: 2007-02-02 07:20:59 -0600 (Fri, 02 Feb 2007) $
  28   * @license            http://www.opensource.org/licenses/mit-license.php The MIT License
  29   */
  30  
  31  /**
  32   * Include DBO.
  33   */
  34  uses ('model' . DS . 'datasources' . DS . 'dbo_source');
  35  
  36  /**
  37   * PostgreSQL layer for DBO.
  38   *
  39   * Long description for class
  40   *
  41   * @package        cake
  42   * @subpackage    cake.cake.libs.model.dbo
  43   */
  44  class DboPostgres extends DboSource{
  45      var $description = "PostgreSQL DBO Driver";
  46  
  47      var $_baseConfig = array(
  48          'connect'    => 'pg_pconnect',
  49          'persistent' => true,
  50          'host' => 'localhost',
  51          'login' => 'root',
  52          'password' => '',
  53          'database' => 'cake',
  54          'port' => 5432
  55      );
  56  
  57      var $columns = array('primary_key' => array('name' => 'serial NOT NULL'),
  58                  'string' => array('name'  => 'varchar', 'limit' => '255'),
  59                  'text' => array('name' => 'text'),
  60                  'integer' => array('name' => 'integer'),
  61                  'float' => array('name' => 'float'),
  62                  'datetime' => array('name' => 'timestamp'),
  63                  'timestamp' => array('name' => 'timestamp'),
  64                  'time' => array('name' => 'time'),
  65                  'date' => array('name' => 'date'),
  66                  'binary' => array('name' => 'bytea'),
  67                  'boolean' => array('name' => 'boolean'),
  68                  'number' => array('name' => 'numeric'),
  69                  'inet' => array('name'  => 'inet'));
  70  
  71      var $startQuote = '"';
  72  
  73      var $endQuote = '"';
  74  
  75  /**
  76   * Connects to the database using options in the given configuration array.
  77   *
  78   * @return True if successfully connected.
  79   */
  80  	function connect() {
  81  
  82          $config = $this->config;
  83          $connect = $config['connect'];
  84          $this->connection = $connect("host='{$config['host']}' port='{$config['port']}' dbname='{$config['database']}' user='{$config['login']}' password='{$config['password']}'");
  85  
  86          if ($this->connection) {
  87              $this->connected = true;
  88          } else {
  89              $this->connected = false;
  90          }
  91          return $this->connected;
  92      }
  93  
  94  /**
  95   * Disconnects from database.
  96   *
  97   * @return boolean True if the database could be disconnected, else false
  98   */
  99  	function disconnect() {
 100          $this->connected=!@pg_close($this->connection);
 101          return !$this->connected;
 102      }
 103  
 104  /**
 105   * Executes given SQL statement.
 106   *
 107   * @param string $sql SQL statement
 108   * @return resource Result resource identifier
 109   */
 110  	function _execute($sql) {
 111          return pg_query($this->connection, $sql);
 112      }
 113  
 114  /**
 115   * Returns a row from given resultset as an array .
 116   *
 117   * @return array The fetched row as an array
 118   */
 119  	function fetchRow($assoc = false) {
 120          if (is_resource($this->_result)) {
 121              $this->resultSet($this->_result);
 122              $resultRow=$this->fetchResult();
 123              return $resultRow;
 124          } else {
 125              return null;
 126          }
 127      }
 128  
 129  /**
 130   * Returns an array of tables in the database. If there are no tables, an error is raised and the application exits.
 131   *
 132   * @return array Array of tablenames in the database
 133   */
 134  	function listSources() {
 135          $cache = parent::listSources();
 136  
 137          if ($cache != null) {
 138              return $cache;
 139          }
 140  
 141          $sql = "SELECT table_name as name FROM INFORMATION_SCHEMA.tables WHERE table_schema = 'public';";
 142          $result = $this->fetchAll($sql);
 143  
 144          if (!$result) {
 145              return array();
 146          } else {
 147              $tables = array();
 148  
 149              foreach($result as $item) {
 150                  $tables[] = $item[0]['name'];
 151              }
 152  
 153              parent::listSources($tables);
 154              return $tables;
 155          }
 156      }
 157  
 158  /**
 159   * Returns an array of the fields in given table name.
 160   *
 161   * @param string $tableName Name of database table to inspect
 162   * @return array Fields in table. Keys are name and type
 163   */
 164      function &describe(&$model) {
 165  
 166          $cache = parent::describe($model);
 167          if ($cache != null) {
 168              return $cache;
 169          }
 170  
 171          $fields = false;
 172          $cols = $this->fetchAll("SELECT DISTINCT column_name AS name, data_type AS type, is_nullable AS null, column_default AS default, ordinal_position FROM information_schema.columns WHERE table_name =" . $this->value($model->tablePrefix . $model->table) . " ORDER BY ordinal_position");
 173  
 174          foreach($cols as $column) {
 175              $colKey = array_keys($column);
 176  
 177              if (isset($column[$colKey[0]]) && !isset($column[0])) {
 178                  $column[0] = $column[$colKey[0]];
 179              }
 180  
 181              if (isset($column[0])) {
 182                  $fields[] = array('name' => $column[0]['name'],
 183                              'type' => $this->column($column[0]['type']),
 184                              'null' => $column[0]['null'],
 185                              'default' => $column[0]['default']
 186                  );
 187              }
 188          }
 189          $this->__cacheDescription($model->tablePrefix . $model->table, $fields);
 190          return $fields;
 191      }
 192  
 193  /**
 194   * Returns a quoted and escaped string of $data for use in an SQL statement.
 195   *
 196   * @param string $data String to be prepared for use in an SQL statement
 197   * @return string Quoted and escaped
 198   */
 199  	function name($data) {
 200          if ($data == '*') {
 201              return '*';
 202          }
 203  
 204          $pos = strpos($data, '"');
 205  
 206          if ($pos === false) {
 207              $data = '"' . str_replace('.', '"."', $data) . '"';
 208          }
 209          return $data;
 210      }
 211  
 212  /**
 213   * Returns a quoted and escaped string of $data for use in an SQL statement.
 214   *
 215   * @param string $data String to be prepared for use in an SQL statement
 216   * @param string $column The column into which this data will be inserted
 217   * @return string Quoted and escaped
 218   * @todo Add logic that formats/escapes data based on column type
 219   */
 220  	function value($data, $column = null) {
 221  
 222          $parent = parent::value($data, $column);
 223          if ($parent != null) {
 224              return $parent;
 225          }
 226  
 227          if ($data === null) {
 228              return 'NULL';
 229          }
 230  
 231          switch($column) {
 232              case 'inet':
 233                  if (!strlen($data)){
 234                      return 'DEFAULT';
 235                  } else {
 236                      $data = pg_escape_string($data);
 237                  }
 238              break;
 239              case 'integer':
 240                  if ($data === '') {
 241                      return 'DEFAULT';
 242                  } else {
 243                      $data = pg_escape_string($data);
 244                  }
 245              break;
 246              case 'binary':
 247                  $data = pg_escape_bytea($data);
 248  
 249              break;
 250              case 'boolean':
 251                  $data = $this->boolean((bool)$data);
 252                  if ($data === true) {
 253                      $data = '1';
 254                  } elseif ($data === false) {
 255                      $data = '0';
 256                  }
 257              break;
 258              default:
 259                  $data = pg_escape_string($data);
 260              break;
 261          }
 262          return "'" . $data . "'";
 263      }
 264  
 265  /**
 266   * Begin a transaction
 267   *
 268   * @param unknown_type $model
 269   * @return boolean True on success, false on fail
 270   * (i.e. if the database/model does not support transactions).
 271   */
 272  	function begin(&$model) {
 273          if (parent::begin($model)) {
 274              if ($this->execute('BEGIN')) {
 275                  $this->__transactionStarted = true;
 276                  return true;
 277              }
 278          }
 279          return false;
 280      }
 281  
 282  /**
 283   * Commit a transaction
 284   *
 285   * @param unknown_type $model
 286   * @return boolean True on success, false on fail
 287   * (i.e. if the database/model does not support transactions,
 288   * or a transaction has not started).
 289   */
 290  	function commit(&$model) {
 291          if (parent::commit($model)) {
 292              $this->__transactionStarted = false;
 293              return $this->execute('COMMIT');
 294          }
 295          return false;
 296      }
 297  
 298  /**
 299   * Rollback a transaction
 300   *
 301   * @param unknown_type $model
 302   * @return boolean True on success, false on fail
 303   * (i.e. if the database/model does not support transactions,
 304   * or a transaction has not started).
 305   */
 306  	function rollback(&$model) {
 307          if (parent::rollback($model)) {
 308              return $this->execute('ROLLBACK');
 309          }
 310          return false;
 311      }
 312  
 313  /**
 314   * Returns a formatted error message from previous database operation.
 315   *
 316   * @return string Error message
 317   */
 318  	function lastError() {
 319          $last_error = pg_last_error($this->connection);
 320          if ($last_error) {
 321              return $last_error;
 322          }
 323          return null;
 324      }
 325  
 326  /**
 327   * Returns number of affected rows in previous database operation. If no previous operation exists, this returns false.
 328   *
 329   * @return int Number of affected rows
 330   */
 331  	function lastAffected() {
 332          if ($this->_result) {
 333              $return = pg_affected_rows($this->_result);
 334              return $return;
 335          }
 336          return false;
 337      }
 338  /**
 339   * Returns number of rows in previous resultset. If no previous resultset exists,
 340   * this returns false.
 341   *
 342   * @return int Number of rows in resultset
 343   */
 344  	function lastNumRows() {
 345          if ($this->_result) {
 346              $return = pg_num_rows($this->_result);
 347              return $return;
 348          }
 349          return false;
 350      }
 351  /**
 352   * Returns the ID generated from the previous INSERT operation.
 353   *
 354   * @param string $source Name of the database table
 355   * @param string $field Name of the ID database field. Defaults to "id"
 356   * @return int
 357   */
 358  	function lastInsertId($source, $field = 'id') {
 359          foreach ($this->__descriptions[$source] as $sourceinfo) {
 360              if (strcasecmp($sourceinfo['name'], $field) == 0) {
 361                  break;
 362              }
 363          }
 364  
 365          if(strpos($sourceinfo['default'], 'nextval') === false) {
 366              return null;
 367          }
 368  
 369          $sql = "SELECT last_value AS max FROM \"{$source}_{$field}_seq\"";
 370          $res = $this->rawQuery($sql);
 371          $data = $this->fetchRow($res);
 372          return $data[0]['max'];
 373      }
 374  /**
 375   * Generates the fields list of an SQL query.
 376   *
 377   * @param Model $model
 378   * @param string $alias Alias tablename
 379   * @param mixed $fields
 380   * @return array
 381   */
 382  	function fields(&$model, $alias, $fields) {
 383          if (is_array($fields)) {
 384                  $fields = $fields;
 385          } else {
 386              if ($fields != null) {
 387                  if (strpos($fields, ',')) {
 388                      $fields = explode(',', $fields);
 389                  } else {
 390                      $fields = array($fields);
 391                  }
 392                  $fields = array_map('trim', $fields);
 393              } else {
 394                  foreach($model->_tableInfo->value as $field) {
 395                      $fields[] = $field['name'];
 396                  }
 397              }
 398          }
 399  
 400          $count = count($fields);
 401  
 402          if ($count >= 1 && $fields[0] != '*' && strpos($fields[0], 'COUNT(*)') === false) {
 403              for($i = 0; $i < $count; $i++) {
 404                  if (!preg_match('/^.+\\(.*\\)/', $fields[$i])) {
 405                      $prepend = '';
 406                      if (strpos($fields[$i], 'DISTINCT') !== false) {
 407                          $prepend = 'DISTINCT ';
 408                          $fields[$i] = trim(r('DISTINCT', '', $fields[$i]));
 409                      }
 410  
 411                      $dot = strrpos($fields[$i], '.');
 412                      if ($dot === false) {
 413                          $fields[$i] = $prepend . $this->name($alias) . '.' . $this->name($fields[$i]) . ' AS ' . $this->name($alias . '__' . $fields[$i]);
 414                      } else {
 415                          $build = explode('.', $fields[$i]);
 416                          $fields[$i] = $prepend . $this->name($build[0]) . '.' . $this->name($build[1]) . ' AS ' . $this->name($build[0] . '__' . $build[1]);
 417                      }
 418                  }
 419              }
 420          }
 421          return $fields;
 422      }
 423  /**
 424   * Returns a limit statement in the correct format for the particular database.
 425   *
 426   * @param int $limit Limit of results returned
 427   * @param int $offset Offset from which to start results
 428   * @return string SQL limit/offset statement
 429   */
 430  	function limit($limit, $offset = null) {
 431          if ($limit) {
 432              $rt = '';
 433              if (!strpos(strtolower($limit), 'limit') || strpos(strtolower($limit), 'limit') === 0) {
 434                  $rt = ' LIMIT';
 435              }
 436  
 437              $rt .= ' ' . $limit;
 438              if ($offset) {
 439                  $rt .= ' OFFSET ' . $offset;
 440              }
 441  
 442              return $rt;
 443          }
 444          return null;
 445      }
 446  /**
 447   * Converts database-layer column types to basic types
 448   *
 449   * @param string $real Real database-layer column type (i.e. "varchar(255)")
 450   * @return string Abstract column type (i.e. "string")
 451   */
 452  	function column($real) {
 453          if (is_array($real)) {
 454              $col = $real['name'];
 455              if (isset($real['limit'])) {
 456                  $col .= '(' . $real['limit'] . ')';
 457              }
 458              return $col;
 459          }
 460  
 461          $col = r(')', '', $real);
 462          $limit = null;
 463          @list($col, $limit) = explode('(', $col);
 464  
 465          if (in_array($col, array('date', 'time'))) {
 466              return $col;
 467          }
 468          if (strpos($col, 'timestamp') !== false) {
 469              return 'datetime';
 470          }
 471          if ($col == 'inet') {
 472              return('inet');
 473          }
 474          if ($col == 'boolean') {
 475              return 'boolean';
 476          }
 477          if (strpos($col, 'int') !== false && $col != 'interval') {
 478              return 'integer';
 479          }
 480          if (strpos($col, 'char') !== false) {
 481                  return 'string';
 482          }
 483          if (strpos($col, 'text') !== false) {
 484                  return 'text';
 485          }
 486          if (strpos($col, 'bytea') !== false) {
 487                  return 'binary';
 488          }
 489          if (in_array($col, array('float', 'float4', 'float8', 'double', 'double precision', 'decimal', 'real', 'numeric'))) {
 490              return 'float';
 491          }
 492          return 'text';
 493      }
 494  
 495  /**
 496   * Enter description here...
 497   *
 498   * @param unknown_type $results
 499   */
 500  	function resultSet(&$results) {
 501          $this->results =& $results;
 502          $this->map = array();
 503          $num_fields = pg_num_fields($results);
 504          $index = 0;
 505          $j = 0;
 506  
 507          while($j < $num_fields) {
 508              $columnName = pg_field_name($results, $j);
 509  
 510              if (strpos($columnName, '__')) {
 511                  $parts = explode('__', $columnName);
 512                  $this->map[$index++]=array($parts[0], $parts[1]);
 513              } else {
 514                  $this->map[$index++] = array(0, $columnName);
 515              }
 516              $j++;
 517          }
 518      }
 519  /**
 520   * Fetches the next row from the current result set
 521   *
 522   * @return unknown
 523   */
 524  	function fetchResult() {
 525          if ($row = pg_fetch_row($this->results)) {
 526              $resultRow = array();
 527              $i = 0;
 528  
 529              foreach($row as $index => $field) {
 530                  list($table, $column) = $this->map[$index];
 531                  $resultRow[$table][$column] = $row[$index];
 532                  $i++;
 533              }
 534              return $resultRow;
 535          } else {
 536              return false;
 537          }
 538      }
 539  /**
 540   * Translates between PHP boolean values and PostgreSQL boolean values
 541   *
 542   * @param mixed $data Value to be translated
 543   * @return mixed Converted boolean value
 544   */
 545  	function boolean($data) {
 546          if ($data === true || $data === false) {
 547              return $data;
 548          } elseif (is_string($data) && !is_numeric($data)) {
 549              if (strpos($data, 't') !== false) {
 550                  return true;
 551              }
 552  
 553              return false;
 554          } else {
 555              return (bool)$data;
 556          }
 557      }
 558  }
 559  
 560  ?>