[Sommaire] [Imprimer]

   1  <?php
   2  // +----------------------------------------------------------------------+
   3  // | PHP Version 4                                                        |
   4  // +----------------------------------------------------------------------+
   5  // | Copyright (c) 1998-2004 Manuel Lemos, Tomas V.V.Cox,                 |
   6  // | Stig. S. Bakken, Lukas Smith                                         |
   7  // | All rights reserved.                                                 |
   8  // +----------------------------------------------------------------------+
   9  // | MDB is a merge of PEAR DB and Metabases that provides a unified DB   |
  10  // | API as well as database abstraction for PHP applications.            |
  11  // | This LICENSE is in the BSD license style.                            |
  12  // |                                                                      |
  13  // | Redistribution and use in source and binary forms, with or without   |
  14  // | modification, are permitted provided that the following conditions   |
  15  // | are met:                                                             |
  16  // |                                                                      |
  17  // | Redistributions of source code must retain the above copyright       |
  18  // | notice, this list of conditions and the following disclaimer.        |
  19  // |                                                                      |
  20  // | Redistributions in binary form must reproduce the above copyright    |
  21  // | notice, this list of conditions and the following disclaimer in the  |
  22  // | documentation and/or other materials provided with the distribution. |
  23  // |                                                                      |
  24  // | Neither the name of Manuel Lemos, Tomas V.V.Cox, Stig. S. Bakken,    |
  25  // | Lukas Smith nor the names of his contributors may be used to endorse |
  26  // | or promote products derived from this software without specific prior|
  27  // | written permission.                                                  |
  28  // |                                                                      |
  29  // | THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS  |
  30  // | "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT    |
  31  // | LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS    |
  32  // | FOR A PARTICULAR PURPOSE ARE DISCLAIMED.  IN NO EVENT SHALL THE      |
  33  // | REGENTS OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,          |
  34  // | INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, |
  35  // | BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS|
  36  // |  OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED  |
  37  // | AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT          |
  38  // | LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY|
  39  // | WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE          |
  40  // | POSSIBILITY OF SUCH DAMAGE.                                          |
  41  // +----------------------------------------------------------------------+
  42  // | Author: Paul Cooper <pgc@ucecom.com>                                 |
  43  // +----------------------------------------------------------------------+
  44  //
  45  // $Id: pgsql.php,v 1.39.4.3 2004/03/09 15:43:40 lsmith Exp $
  46  
  47  if(!defined('MDB_MANAGER_PGSQL_INCLUDED'))
  48  {
  49      define('MDB_MANAGER_PGSQL_INCLUDED', 1);
  50  
  51  require_once ('MDB/Modules/Manager/Common.php');
  52  
  53  /**
  54   * MDB MySQL driver for the management modules
  55   *
  56   * @package MDB
  57   * @category Database
  58   * @access private
  59   * @author  Paul Cooper <pgc@ucecom.com>
  60   */
  61  class MDB_Manager_pgsql extends MDB_Manager_common
  62  {
  63      // {{{ createDatabase()
  64  
  65      /**
  66       * create a new database
  67       *
  68       * @param object    $db        database object that is extended by this class
  69       * @param string $name name of the database that should be created
  70       * @return mixed MDB_OK on success, a MDB error on failure
  71       * @access public
  72       **/
  73      function createDatabase(&$db, $name)
  74      {
  75          return($db->_standaloneQuery("CREATE DATABASE $name"));
  76      }
  77  
  78      // }}}
  79      // {{{ dropDatabase()
  80  
  81      /**
  82       * drop an existing database
  83       *
  84       * @param object    $db        database object that is extended by this class
  85       * @param string $name name of the database that should be dropped
  86       * @return mixed MDB_OK on success, a MDB error on failure
  87       * @access public
  88       **/
  89      function dropDatabase(&$db, $name)
  90      {
  91          return($db->_standaloneQuery("DROP DATABASE $name"));
  92      }
  93  
  94      // }}}
  95      // {{{ createTable()
  96  
  97      /**
  98       * create a new table
  99       *
 100       * @param object    $db        database object that is extended by this class
 101       * @param string $name Name of the database that should be created
 102       * @param array $fields Associative array that contains the definition of each field of the new table
 103       *                         The indexes of the array entries are the names of the fields of the table an
 104       *                         the array entry values are associative arrays like those that are meant to be
 105       *                          passed with the field definitions to get[Type]Declaration() functions.
 106       *
 107       *                         Example
 108       *                         array(
 109       *
 110       *                             'id' => array(
 111       *                                 'type' => 'integer',
 112       *                                 'unsigned' => 1
 113       *                                 'notnull' => 1
 114       *                                 'default' => 0
 115       *                             ),
 116       *                             'name' => array(
 117       *                                 'type' => 'text',
 118       *                                 'length' => 12
 119       *                             ),
 120       *                             'password' => array(
 121       *                                 'type' => 'text',
 122       *                                 'length' => 12
 123       *                             )
 124       *                         );
 125       * @return mixed MDB_OK on success, a MDB error on failure
 126       * @access public
 127       **/
 128      function createTable(&$db, $name, $fields)
 129      {
 130          if (!isset($name) || !strcmp($name, '')) {
 131              return($db->raiseError(MDB_ERROR_CANNOT_CREATE, NULL, NULL, 'no valid table name specified'));
 132          }
 133          if (count($fields) == 0) {
 134              return($db->raiseError(MDB_ERROR_CANNOT_CREATE, NULL, NULL, 'no fields specified for table "'.$name.'"'));
 135          }
 136          $query_fields = '';
 137          if (MDB::isError($query_fields = $db->getFieldDeclarationList($fields))) {
 138              return($db->raiseError(MDB_ERROR_CANNOT_CREATE, NULL, NULL, 'unkown error'));
 139          }
 140          return($db->query("CREATE TABLE $name ($query_fields)"));
 141      }
 142  
 143      // }}}
 144      // {{{ alterTable()
 145  
 146      /**
 147       * alter an existing table
 148       *
 149       * @param object    $db        database object that is extended by this class
 150       * @param string $name name of the table that is intended to be changed.
 151       * @param array $changes associative array that contains the details of each type
 152       *                              of change that is intended to be performed. The types of
 153       *                              changes that are currently supported are defined as follows:
 154       *
 155       *                              name
 156       *
 157       *                                 New name for the table.
 158       *
 159       *                             AddedFields
 160       *
 161       *                                 Associative array with the names of fields to be added as
 162       *                                  indexes of the array. The value of each entry of the array
 163       *                                  should be set to another associative array with the properties
 164       *                                  of the fields to be added. The properties of the fields should
 165       *                                  be the same as defined by the Metabase parser.
 166       *
 167       *                                 Additionally, there should be an entry named Declaration that
 168       *                                  is expected to contain the portion of the field declaration already
 169       *                                  in DBMS specific SQL code as it is used in the CREATE TABLE statement.
 170       *
 171       *                             RemovedFields
 172       *
 173       *                                 Associative array with the names of fields to be removed as indexes
 174       *                                  of the array. Currently the values assigned to each entry are ignored.
 175       *                                  An empty array should be used for future compatibility.
 176       *
 177       *                             RenamedFields
 178       *
 179       *                                 Associative array with the names of fields to be renamed as indexes
 180       *                                  of the array. The value of each entry of the array should be set to
 181       *                                  another associative array with the entry named name with the new
 182       *                                  field name and the entry named Declaration that is expected to contain
 183       *                                  the portion of the field declaration already in DBMS specific SQL code
 184       *                                  as it is used in the CREATE TABLE statement.
 185       *
 186       *                             ChangedFields
 187       *
 188       *                                 Associative array with the names of the fields to be changed as indexes
 189       *                                  of the array. Keep in mind that if it is intended to change either the
 190       *                                  name of a field and any other properties, the ChangedFields array entries
 191       *                                  should have the new names of the fields as array indexes.
 192       *
 193       *                                 The value of each entry of the array should be set to another associative
 194       *                                  array with the properties of the fields to that are meant to be changed as
 195       *                                  array entries. These entries should be assigned to the new values of the
 196       *                                  respective properties. The properties of the fields should be the same
 197       *                                  as defined by the Metabase parser.
 198       *
 199       *                                 If the default property is meant to be added, removed or changed, there
 200       *                                  should also be an entry with index ChangedDefault assigned to 1. Similarly,
 201       *                                  if the notnull constraint is to be added or removed, there should also be
 202       *                                  an entry with index ChangedNotNull assigned to 1.
 203       *
 204       *                                 Additionally, there should be an entry named Declaration that is expected
 205       *                                  to contain the portion of the field changed declaration already in DBMS
 206       *                                  specific SQL code as it is used in the CREATE TABLE statement.
 207       *                             Example
 208       *                                 array(
 209       *                                     'name' => 'userlist',
 210       *                                     'AddedFields' => array(
 211       *                                         'quota' => array(
 212       *                                             'type' => 'integer',
 213       *                                             'unsigned' => 1
 214       *                                             'Declaration' => 'quota INT'
 215       *                                         )
 216       *                                     ),
 217       *                                     'RemovedFields' => array(
 218       *                                         'file_limit' => array(),
 219       *                                         'time_limit' => array()
 220       *                                         ),
 221       *                                     'ChangedFields' => array(
 222       *                                         'gender' => array(
 223       *                                             'default' => 'M',
 224       *                                             'ChangeDefault' => 1,
 225       *                                             'Declaration' => "gender CHAR(1) DEFAULT 'M'"
 226       *                                         )
 227       *                                     ),
 228       *                                     'RenamedFields' => array(
 229       *                                         'sex' => array(
 230       *                                             'name' => 'gender',
 231       *                                             'Declaration' => "gender CHAR(1) DEFAULT 'M'"
 232       *                                         )
 233       *                                     )
 234       *                                 )
 235       * @param boolean $check indicates whether the function should just check if the DBMS driver
 236       *                              can perform the requested table alterations if the value is TRUE or
 237       *                              actually perform them otherwise.
 238       * @return mixed MDB_OK on success, a MDB error on failure
 239       * @access public
 240       **/
 241      function alterTable(&$db, $name, &$changes, $check)
 242      {
 243          if ($check) {
 244              for ($change = 0, reset($changes); $change < count($changes); next($changes), $change++) {
 245                  switch (key($changes)) {
 246                      case 'AddedFields':
 247                          break;
 248                      case 'RemovedFields':
 249                          return($db->raiseError(MDB_ERROR_UNSUPPORTED, NULL, NULL, 'database server does not support dropping table columns'));
 250                      case 'name':
 251                      case 'RenamedFields':
 252                      case 'ChangedFields':
 253                      default:
 254                          return($db->raiseError(MDB_ERROR_UNSUPPORTED, NULL, NULL, 'change type "'.key($changes).'\" not yet supported'));
 255                  }
 256              }
 257              return(MDB_OK);
 258          } else {
 259              if (isSet($changes[$change = 'name']) || isSet($changes[$change = 'RenamedFields']) || isSet($changes[$change = 'ChangedFields'])) {
 260                  return($db->raiseError(MDB_ERROR_UNSUPPORTED, NULL, NULL, 'change type "'.$change.'" not yet supported'));
 261              }
 262              $query = '';
 263              if (isSet($changes['AddedFields'])) {
 264                  $fields = $changes['AddedFields'];
 265                  for ($field = 0, reset($fields); $field < count($fields); next($fields), $field++) {
 266                      if (MDB::isError($result = $db->query("ALTER TABLE $name ADD ".$fields[key($fields)]['Declaration']))) {
 267                          return($result);
 268                      }
 269                  }
 270              }
 271              if (isSet($changes['RemovedFields'])) {
 272                  $fields = $changes['RemovedFields'];
 273                  for ($field = 0, reset($fields); $field < count($fields); next($fields), $field++) {
 274                      if (MDB::isError($result = $db->query("ALTER TABLE $name DROP ".key($fields)))) {
 275                          return($result);
 276                      }
 277                  }
 278              }
 279              return(MDB_OK);
 280          }
 281      }
 282  
 283      // }}}
 284      // {{{ listDatabases()
 285  
 286      /**
 287       * list all databases
 288       *
 289       * @param object    $db        database object that is extended by this class
 290       * @return mixed data array on success, a MDB error on failure
 291       * @access public
 292       **/
 293      function listDatabases(&$db)
 294      {
 295          return($db->queryCol('SELECT datname FROM pg_database'));
 296      }
 297  
 298      // }}}
 299      // {{{ listUsers()
 300  
 301      /**
 302       * list all users
 303       *
 304       * @param object    $db        database object that is extended by this class
 305       * @return mixed data array on success, a MDB error on failure
 306       * @access public
 307       **/
 308      function listUsers(&$db)
 309      {
 310          return($db->queryCol('SELECT usename FROM pg_user'));
 311      }
 312  
 313      // }}}
 314      // {{{ listTables()
 315  
 316      /**
 317       * list all tables in the current database
 318       *
 319       * @param object    $db        database object that is extended by this class
 320       * @return mixed data array on success, a MDB error on failure
 321       * @access public
 322       **/
 323      function listTables(&$db)
 324      {
 325          // gratuitously stolen from PEAR DB _getSpecialQuery in pgsql.php
 326          $sql = 'SELECT c.relname as "Name"
 327              FROM pg_class c, pg_user u
 328              WHERE c.relowner = u.usesysid AND c.relkind = \'r\'
 329              AND not exists (select 1 from pg_views where viewname = c.relname)
 330              AND c.relname !~ \'^pg_\'
 331              AND c.relname !~ \'^pga_\'
 332              UNION
 333              SELECT c.relname as "Name"
 334              FROM pg_class c
 335              WHERE c.relkind = \'r\'
 336              AND not exists (select 1 from pg_views where viewname = c.relname)
 337              AND not exists (select 1 from pg_user where usesysid = c.relowner)
 338              AND c.relname !~ \'^pg_\'
 339              AND c.relname !~ \'^pga_\'';
 340          return($db->queryCol($sql));
 341      }
 342  
 343      // }}}
 344      // {{{ listTableFields()
 345  
 346      /**
 347       * list all fields in a tables in the current database
 348       *
 349       * @param object    $db        database object that is extended by this class
 350       * @param string $table name of table that should be used in method
 351       * @return mixed data array on success, a MDB error on failure
 352       * @access public
 353       */
 354      function listTableFields(&$db, $table)
 355      {
 356          $result = $db->query("SELECT * FROM $table");
 357          if(MDB::isError($result)) {
 358              return($result);
 359          }
 360          $columns = $db->getColumnNames($result);
 361          if(MDB::isError($columns)) {
 362              $db->freeResult($columns);
 363          }
 364          return(array_flip($columns));
 365      }
 366  
 367      // }}} 
 368      // {{{ getTableFieldDefinition()
 369  
 370      /**
 371       * get the stucture of a field into an array
 372       *
 373       * @param object    $db        database object that is extended by this class
 374       * @param string    $table         name of table that should be used in method
 375       * @param string    $field_name     name of field that should be used in method
 376       * @return mixed data array on success, a MDB error on failure
 377       * @access public
 378       */
 379      function getTableFieldDefinition(&$db, $table, $field_name)
 380      {
 381          $result = $db->query("SELECT 
 382                      attnum,attname,typname,attlen,attnotnull,
 383                      atttypmod,usename,usesysid,pg_class.oid,relpages,
 384                      reltuples,relhaspkey,relhasrules,relacl,adsrc
 385                      FROM pg_class,pg_user,pg_type,
 386                           pg_attribute left outer join pg_attrdef on
 387                           pg_attribute.attrelid=pg_attrdef.adrelid 
 388                      WHERE (pg_class.relname='$table') 
 389                          and (pg_class.oid=pg_attribute.attrelid) 
 390                          and (pg_class.relowner=pg_user.usesysid) 
 391                          and (pg_attribute.atttypid=pg_type.oid)
 392                          and attnum > 0
 393                          and attname = '$field_name'
 394                          ORDER BY attnum
 395                          ");
 396          if(MDB::isError($result)) {
 397              return($result);
 398          }
 399          $columns = $db->fetchInto($result, MDB_FETCHMODE_ASSOC);
 400          $field_column = $columns['attname'];
 401          $type_column = $columns['typname'];
 402          $db_type = preg_replace('/\d/','', strtolower($type_column) );
 403          $length = $columns['attlen'];
 404          if ($length == -1) {
 405              $length = $columns['atttypmod']-4;
 406          }
 407          //$decimal = strtok('(), '); = eh?
 408          $type = array();
 409          switch($db_type) {
 410              case 'int':
 411                  $type[0] = 'integer';
 412                  if($length == '1') {
 413                      $type[1] = 'boolean';
 414                  }
 415                  break;
 416              case 'text':
 417              case 'char':
 418              case 'varchar':
 419              case 'bpchar':
 420                  $type[0] = 'text';
 421                  
 422                  if($length == '1') {
 423                      $type[1] = 'boolean';
 424                  } elseif(strstr($db_type, 'text'))
 425                      $type[1] = 'clob';
 426                  break;
 427  /*                        
 428              case 'enum':
 429                  preg_match_all('/\'.+\'/U',$row[$type_column], $matches);
 430                  $length = 0;
 431                  if(is_array($matches)) {
 432                      foreach($matches[0] as $value) {
 433                          $length = max($length, strlen($value)-2);
 434                      }
 435                  }
 436                  unset($decimal);
 437              case 'set':
 438                  $type[0] = 'text';
 439                  $type[1] = 'integer';
 440                  break;
 441  */
 442              case 'date':
 443                  $type[0] = 'date';
 444                  break;
 445              case 'datetime':
 446              case 'timestamp':
 447                  $type[0] = 'timestamp';
 448                  break;
 449              case 'time':
 450                  $type[0] = 'time';
 451                  break;
 452              case 'float':
 453              case 'double':
 454              case 'real':
 455              
 456                  $type[0] = 'float';
 457                  break;
 458              case 'decimal':
 459              case 'money':
 460              case 'numeric':
 461                  $type[0] = 'decimal';
 462                  break;
 463              case 'oid':
 464              case 'tinyblob':
 465              case 'mediumblob':
 466              case 'longblob':
 467              case 'blob':
 468                  $type[0] = 'blob';
 469                  $type[1] = 'text';
 470                  break;
 471              case 'year':
 472                  $type[0] = 'integer';
 473                  $type[1] = 'date';
 474                  break;
 475              default:
 476                  return($db->raiseError(MDB_ERROR_MANAGER, NULL, NULL,
 477                      'List table fields: unknown database attribute type'));
 478          }
 479           
 480          if ($columns['attnotnull'] == 'f') {
 481              $notnull = 1;
 482          }
 483          
 484          if (!preg_match("/nextval\('([^']+)'/",$columns['adsrc']))  {
 485              $default = substr($columns['adsrc'],1,-1);
 486          }
 487          $definition = array();
 488          for($field_choices = array(), $datatype = 0; $datatype < count($type); $datatype++) {
 489              $field_choices[$datatype] = array('type' => $type[$datatype]);
 490              if(isset($notnull)) {
 491                  $field_choices[$datatype]['notnull'] = 1;
 492              }
 493              if(isset($default)) {
 494                  $field_choices[$datatype]['default'] = $default;
 495              }
 496              if($type[$datatype] != 'boolean'
 497                  && $type[$datatype] != 'time'
 498                  && $type[$datatype] != 'date'
 499                  && $type[$datatype] != 'timestamp')
 500              {
 501                  if(strlen($length)) {
 502                      $field_choices[$datatype]['length'] = $length;
 503                  }
 504              }
 505          }
 506          $definition[0] = $field_choices;
 507          if (preg_match("/nextval\('([^']+)'/",$columns['adsrc'],$nextvals))  {
 508               
 509              $implicit_sequence = array();
 510              $implicit_sequence['on'] = array();
 511              $implicit_sequence['on']['table'] = $table;
 512              $implicit_sequence['on']['field'] = $field_name;
 513              $definition[1]['name'] = $nextvals[1];
 514              $definition[1]['definition'] = $implicit_sequence;
 515          }
 516   
 517          // check that its not just a unique field
 518          if(MDB::isError($indexes = $db->queryAll("SELECT 
 519                  oid,indexrelid,indrelid,indkey,indisunique,indisprimary 
 520                  FROm pg_index, pg_class 
 521                  WHERE (pg_class.relname='$table') 
 522                      AND (pg_class.oid=pg_index.indrelid)", NULL, MDB_FETCHMODE_ASSOC))) {
 523              return $indexes;
 524          }
 525          $indkeys = explode(' ',$indexes['indkey']);
 526          if (in_array($columns['attnum'],$indkeys)) {
 527              if (MDB::isError($indexname = $db->queryAll("SELECT 
 528                      relname FROM pg_class WHERE oid={$columns['indexrelid']}")) ) {
 529                  return $indexname;
 530              }
 531              
 532              $is_primary = ($indexes['isdisprimary'] == 't') ;
 533              $is_unique = ($indexes['isdisunique'] == 't') ;
 534          
 535              $implicit_index = array();
 536              $implicit_index['unique'] = 1;
 537              $implicit_index['FIELDS'][$field_name] = $indexname['relname'];
 538              $definition[2]['name'] = $field_name;
 539              $definition[2]['definition'] = $implicit_index;
 540          }
 541          
 542          $db->freeResult($result);
 543          return($definition);
 544      }
 545  
 546  
 547      // }}}
 548      // {{{ listViews()
 549  
 550      /**
 551       * list the views in the database
 552       *
 553       * @param object    $db        database object that is extended by this class
 554       * @return mixed MDB_OK on success, a MDB error on failure
 555       * @access public
 556       **/
 557      function listViews(&$db)
 558      {
 559          // gratuitously stolen from PEAR DB _getSpecialQuery in pgsql.php
 560          return($db->queryCol('SELECT viewname FROM pg_views'));
 561      }
 562  
 563      // }}}
 564      // {{{ listTableIndexes()
 565  
 566      /**
 567       * list all indexes in a table
 568       *
 569       * @param object    $db        database object that is extended by this class
 570       * @param string    $table      name of table that should be used in method
 571       * @return mixed data array on success, a MDB error on failure
 572       * @access public
 573       */
 574      function listTableIndexes(&$db, $table) {
 575          $result = $db->query("SELECT relname 
 576                                  FROM pg_class WHERE oid IN
 577                                    (SELECR indexrelid FROM pg_index, pg_class 
 578                                     WHERE (pg_class.relname='$table') 
 579                                     AND (pg_class.oid=pg_index.indrelid))");
 580          return $db->fetchCol($result);
 581      }
 582  
 583      // }}}
 584      // {{{ getTableIndexDefinition()
 585      /**
 586       * get the stucture of an index into an array
 587       *
 588       * @param object    $db        database object that is extended by this class
 589       * @param string    $table      name of table that should be used in method
 590       * @param string    $index_name name of index that should be used in method
 591       * @return mixed data array on success, a MDB error on failure
 592       * @access public
 593       */
 594      function getTableIndexDefinition(&$db, $table, $index_name)
 595      {
 596          $row = $db->queryAll("SELECT * from pg_index, pg_class 
 597                                  WHERE (pg_class.relname='$index_name') 
 598                                  AND (pg_class.oid=pg_index.indexrelid)", NULL, MDB_FETCHMODE_ASSOC);
 599          if ($row[0]['relname'] != $index_name) {
 600              return($db->raiseError(MDB_ERROR_MANAGER, NULL, NULL, 'Get table index definition: it was not specified an existing table index'));
 601          }
 602  
 603          $columns = $this->listTableFields($db, $table);
 604  
 605          $definition = array();
 606          if ($row[0]['indisunique'] == 't') {
 607              $definition[$index_name]['unique'] = 1;
 608          }
 609  
 610          $index_column_numbers = explode(' ', $row[0]['indkey']);
 611  
 612          foreach ($index_column_numbers as $number) {
 613              $definition['FIELDS'][$columns[($number - 1)]] = array('sorting' => 'ascending');
 614          }
 615          return $definition;
 616      }
 617  
 618  
 619      // }}}
 620      // {{{ createSequence()
 621  
 622      /**
 623       * create sequence
 624       *
 625       * @param object    $db        database object that is extended by this class
 626       * @param string $seq_name name of the sequence to be created
 627       * @param string $start start value of the sequence; default is 1
 628       * @return mixed MDB_OK on success, a MDB error on failure
 629       * @access public
 630       **/
 631      function createSequence(&$db, $seq_name, $start)
 632      {
 633          $seqname = $db->getSequenceName($seq_name);
 634          return($db->query("CREATE SEQUENCE $seqname INCREMENT 1".($start < 1 ? " MINVALUE $start" : '')." START $start"));
 635      }
 636  
 637      // }}}
 638      // {{{ dropSequence()
 639  
 640      /**
 641       * drop existing sequence
 642       *
 643       * @param object    $db        database object that is extended by this class
 644       * @param string $seq_name name of the sequence to be dropped
 645       * @return mixed MDB_OK on success, a MDB error on failure
 646       * @access public
 647       **/
 648      function dropSequence(&$db, $seq_name)
 649      {
 650          $seqname = $db->getSequenceName($seq_name);
 651          return($db->query("DROP SEQUENCE $seqname"));
 652      }
 653  
 654      // }}}
 655      // {{{ listSequences()
 656  
 657      /**
 658       * list all sequences in the current database
 659       *
 660       * @param object    $db        database object that is extended by this class
 661       * @return mixed data array on success, a MDB error on failure
 662       * @access public
 663       **/
 664      function listSequences(&$db)
 665      {
 666          // gratuitously stolen and adapted from PEAR DB _getSpecialQuery in pgsql.php
 667          $sql = 'SELECT c.relname as "Name"
 668              FROM pg_class c, pg_user u
 669              WHERE c.relowner = u.usesysid AND c.relkind = \'S\'
 670              AND not exists (select 1 from pg_views where viewname = c.relname)
 671              AND c.relname !~ \'^pg_\'
 672              UNION
 673              SELECT c.relname as "Name"
 674              FROM pg_class c
 675              WHERE c.relkind = \'S\'
 676              AND not exists (select 1 from pg_views where viewname = c.relname)
 677              AND not exists (select 1 from pg_user where usesysid = c.relowner)
 678              AND c.relname !~ \'^pg_\'';
 679          return($db->queryCol($sql));
 680      }
 681  }
 682  
 683  };
 684  ?>