[Sommaire] [Imprimer]

   1  <?php
   2  /*
   3   *  $Id: PgSQLTableInfo.php,v 1.31 2006/01/17 19:44:40 hlellelid Exp $
   4   *
   5   * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
   6   * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
   7   * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
   8   * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
   9   * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
  10   * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
  11   * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
  12   * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
  13   * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
  14   * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
  15   * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  16   *
  17   * This software consists of voluntary contributions made by many individuals
  18   * and is licensed under the LGPL. For more information please see
  19   * <http://creole.phpdb.org>.
  20   */
  21  
  22  require_once 'creole/metadata/TableInfo.php';
  23  
  24  /**
  25   * PgSQL implementation of TableInfo.
  26   *
  27   * See this Python code by David M. Cook for some good reference on Pgsql metadata
  28   * functions:
  29   * @link http://www.sandpyt.org/pipermail/sandpyt/2003-March/000008.html
  30   *
  31   * Here's some more information from postgresql:
  32   * @link http://developer.postgresql.org/docs/pgsql/src/backend/catalog/information_schema.sql
  33   *
  34   * @todo -c Eventually move to supporting only Postgres >= 7.4, which has the information_schema
  35   *
  36   * @author    Hans Lellelid <hans@xmpl.org>
  37   * @version   $Revision: 1.31 $
  38   * @package   creole.drivers.pgsql.metadata
  39   */
  40  class PgSQLTableInfo extends TableInfo {
  41      
  42      /**
  43       * Database Version.
  44       * @var String
  45       */
  46      private $version;
  47      
  48      /**
  49       * Table OID
  50       * @var Integer
  51       */
  52      private $oid;
  53  
  54      /**
  55       * @param string $table The table name.
  56       * @param string $database The database name.
  57       * @param resource $dblink The db connection resource.
  58       */
  59      function __construct(DatabaseInfo $database, $name, $version, $intOID) {
  60          parent::__construct ($database, $name);
  61          $this->version = $version;
  62          $this->oid = $intOID;
  63      } // function __construct(DatabaseInfo $database, $name) {
  64  
  65      /** Load the columns for this table */
  66      protected function initColumns () {
  67          // Include dependencies
  68          include_once ('creole/metadata/ColumnInfo.php');
  69          include_once ('creole/drivers/pgsql/PgSQLTypes.php');
  70  
  71          // Get the columns, types, etc.
  72          // Based on code from pgAdmin3 (http://www.pgadmin.org/)
  73          $result = pg_query ($this->conn->getResource(), sprintf ("SELECT 
  74                                      att.attname,
  75                                      att.atttypmod,
  76                                      att.atthasdef,
  77                                      att.attnotnull,
  78                                      def.adsrc, 
  79                                      CASE WHEN att.attndims > 0 THEN 1 ELSE 0 END AS isarray, 
  80                                      CASE 
  81                                          WHEN ty.typname = 'bpchar' 
  82                                              THEN 'char' 
  83                                          WHEN ty.typname = '_bpchar' 
  84                                              THEN '_char' 
  85                                          ELSE 
  86                                              ty.typname 
  87                                      END AS typname,
  88                                      ty.typtype
  89                                  FROM pg_attribute att
  90                                      JOIN pg_type ty ON ty.oid=att.atttypid
  91                                      LEFT OUTER JOIN pg_attrdef def ON adrelid=att.attrelid AND adnum=att.attnum
  92                                  WHERE att.attrelid = %d AND att.attnum > 0
  93                                      AND att.attisdropped IS FALSE
  94                                  ORDER BY att.attnum", $this->oid));
  95  
  96          if (!$result) {
  97              throw new SQLException("Could not list fields for table: " . $this->name, pg_last_error($this->conn->getResource()));
  98          }
  99          while($row = pg_fetch_assoc($result)) {
 100              
 101              $size = null;
 102              $precision = null;
 103              $scale = null;
 104              
 105              // Check to ensure that this column isn't an array data type
 106              if (((int) $row['isarray']) === 1)
 107              {
 108                  throw new SQLException (sprintf ("Array datatypes are not currently supported [%s.%s]", $this->name, $row['attname']));
 109              } // if (((int) $row['isarray']) === 1)
 110              $name = $row['attname'];
 111              // If they type is a domain, Process it
 112              if (strtolower ($row['typtype']) == 'd')
 113              {
 114                  $arrDomain = $this->processDomain ($row['typname']);
 115                  $type = $arrDomain['type'];
 116                  $size = $arrDomain['length'];
 117                  $precision = $size;
 118                  $scale = $arrDomain['scale'];
 119                  $boolHasDefault = (strlen (trim ($row['atthasdef'])) > 0) ? $row['atthasdef'] : $arrDomain['hasdefault'];
 120                  $default = (strlen (trim ($row['adsrc'])) > 0) ? $row['adsrc'] : $arrDomain['default'];
 121                  $is_nullable = (strlen (trim ($row['attnotnull'])) > 0) ? $row['attnotnull'] : $arrDomain['notnull'];
 122                  $is_nullable = (($is_nullable == 't') ? false : true);
 123              } // if (strtolower ($row['typtype']) == 'd')
 124              else
 125              {
 126                  $type = $row['typname'];
 127                  $arrLengthPrecision = $this->processLengthScale ($row['atttypmod'], $type);
 128                  $size = $arrLengthPrecision['length'];
 129                  $precision = $size;
 130                  $scale = $arrLengthPrecision['scale'];
 131                  $boolHasDefault = $row['atthasdef'];
 132                  $default = $row['adsrc'];
 133                  $is_nullable = (($row['attnotnull'] == 't') ? false : true);
 134              } // else (strtolower ($row['typtype']) == 'd')
 135  
 136              $autoincrement = null;
 137                         
 138              // if column has a default
 139              if (($boolHasDefault == 't') && (strlen (trim ($default)) > 0))
 140              {
 141                  if (!preg_match('/^nextval\(/', $default))
 142                  {
 143                      $strDefault= preg_replace ('/::[\W\D]*/', '', $default);
 144                      $default = str_replace ("'", '', $strDefault);
 145                  } // if (!preg_match('/^nextval\(/', $row['atthasdef']))
 146                  else
 147                  {
 148                      $autoincrement = true;
 149                      $default = null;
 150                  } // else
 151              } // if (($boolHasDefault == 't') && (strlen (trim ($default)) > 0))
 152              else
 153              {
 154                  $default = null;
 155              } // else (($boolHasDefault == 't') && (strlen (trim ($default)) > 0))
 156  
 157              $this->columns[$name] = new ColumnInfo($this, $name, PgSQLTypes::getType($type), $type, $size, $precision, $scale, $is_nullable, $default, $autoincrement);
 158          }
 159  
 160          $this->colsLoaded = true;
 161      } // protected function initColumns ()
 162  
 163      private function processLengthScale ($intTypmod, $strName)
 164      {
 165          // Define the return array
 166          $arrRetVal = array ('length'=>null, 'scale'=>null);
 167  
 168          // Some datatypes don't have a Typmod
 169          if ($intTypmod == -1)
 170          {
 171              return $arrRetVal;
 172          } // if ($intTypmod == -1)
 173  
 174          // Numeric Datatype?
 175          if ($strName == PgSQLTypes::getNativeType (CreoleTypes::NUMERIC))
 176          {
 177              $intLen = ($intTypmod - 4) >> 16;
 178              $intPrec = ($intTypmod - 4) & 0xffff;
 179              $intLen = sprintf ("%ld", $intLen);
 180              if ($intPrec)
 181              {
 182                  $intPrec = sprintf ("%ld", $intPrec);
 183              } // if ($intPrec)
 184              $arrRetVal['length'] = $intLen;
 185              $arrRetVal['scale'] = $intPrec;
 186          } // if ($strName == PgSQLTypes::getNativeType (CreoleTypes::NUMERIC))
 187          elseif ($strName == PgSQLTypes::getNativeType (CreoleTypes::TIME) || $strName == 'timetz'
 188              || $strName == PgSQLTypes::getNativeType (CreoleTypes::TIMESTAMP) || $strName == 'timestamptz'
 189              || $strName == 'interval' || $strName == 'bit')
 190          {
 191              $arrRetVal['length'] = sprintf ("%ld", $intTypmod);
 192          } // elseif (TIME, TIMESTAMP, INTERVAL, BIT)
 193          else
 194          {
 195              $arrRetVal['length'] = sprintf ("%ld", ($intTypmod - 4));
 196          } // else
 197          return $arrRetVal;
 198      } // private function processLengthScale ($intTypmod, $strName)
 199  
 200      private function processDomain ($strDomain)
 201      {
 202          if (strlen (trim ($strDomain)) < 1)
 203          {
 204              throw new SQLException ("Invalid domain name [" . $strDomain . "]");
 205          } // if (strlen (trim ($strDomain)) < 1)
 206          $result = pg_query ($this->conn->getResource(), sprintf ("SELECT
 207                                                          d.typname as domname,
 208                                                          b.typname as basetype,
 209                                                          d.typlen,
 210                                                          d.typtypmod,
 211                                                          d.typnotnull,
 212                                                          d.typdefault
 213                                                      FROM pg_type d
 214                                                          INNER JOIN pg_type b ON b.oid = CASE WHEN d.typndims > 0 then d.typelem ELSE d.typbasetype END
 215                                                      WHERE
 216                                                          d.typtype = 'd'
 217                                                          AND d.typname = '%s'
 218                                                      ORDER BY d.typname", $strDomain));
 219  
 220          if (!$result) {
 221              throw new SQLException("Query for domain [" . $strDomain . "] failed.", pg_last_error($this->conn->getResource()));
 222          }
 223  
 224          $row = pg_fetch_assoc ($result);
 225          if (!$row)
 226          {
 227              throw new SQLException ("Domain [" . $strDomain . "] not found.");
 228          } // if (!$row)
 229          $arrDomain = array ();
 230          $arrDomain['type'] = $row['basetype'];
 231          $arrLengthPrecision = $this->processLengthScale ($row['typtypmod'], $row['basetype']);
 232          $arrDomain['length'] = $arrLengthPrecision['length'];
 233          $arrDomain['scale'] = $arrLengthPrecision['scale'];
 234          $arrDomain['notnull'] = $row['typnotnull'];
 235          $arrDomain['default'] = $row['typdefault'];
 236          $arrDomain['hasdefault'] = (strlen (trim ($row['typdefault'])) > 0) ? 't' : 'f';
 237  
 238          pg_free_result ($result);
 239          return $arrDomain;
 240      } // private function processDomain ($strDomain)
 241  
 242      /** Load foreign keys for this table. */
 243      protected function initForeignKeys()
 244      {
 245          include_once 'creole/metadata/ForeignKeyInfo.php';
 246  
 247          $result = pg_query ($this->conn->getResource(), sprintf ("SELECT
 248                                conname,
 249                                confupdtype,
 250                                confdeltype,
 251                                cl.relname as fktab,
 252                                a2.attname as fkcol,
 253                                cr.relname as reftab,
 254                                a1.attname as refcol
 255                          FROM pg_constraint ct
 256                               JOIN pg_class cl ON cl.oid=conrelid
 257                               JOIN pg_class cr ON cr.oid=confrelid
 258                               LEFT JOIN pg_catalog.pg_attribute a1 ON a1.attrelid = ct.confrelid
 259                               LEFT JOIN pg_catalog.pg_attribute a2 ON a2.attrelid = ct.conrelid
 260                          WHERE
 261                               contype='f'
 262                               AND conrelid = %d
 263                               AND a2.attnum = ct.conkey[1]
 264                               AND a1.attnum = ct.confkey[1]
 265                          ORDER BY conname", $this->oid));
 266          if (!$result) {
 267              throw new SQLException("Could not list foreign keys for table: " . $this->name, pg_last_error($this->conn->getResource()));
 268          }
 269  
 270          while($row = pg_fetch_assoc($result)) {
 271              $name = $row['conname'];
 272              $local_table = $row['fktab'];
 273              $local_column = $row['fkcol'];
 274              $foreign_table = $row['reftab'];
 275              $foreign_column = $row['refcol'];
 276  
 277              // On Update
 278              switch ($row['confupdtype']) {
 279                case 'c':
 280                  $onupdate = ForeignKeyInfo::CASCADE; break;
 281                case 'd':
 282                  $onupdate = ForeignKeyInfo::SETDEFAULT; break;
 283                case 'n':
 284                  $onupdate = ForeignKeyInfo::SETNULL; break;
 285                case 'r':
 286                  $onupdate = ForeignKeyInfo::RESTRICT; break;
 287                default:
 288                case 'a':
 289                  //NOACTION is the postgresql default
 290                  $onupdate = ForeignKeyInfo::NONE; break;
 291              }
 292              // On Delete
 293              switch ($row['confdeltype']) {
 294                case 'c':
 295                  $ondelete = ForeignKeyInfo::CASCADE; break;
 296                case 'd':
 297                  $ondelete = ForeignKeyInfo::SETDEFAULT; break;
 298                case 'n':
 299                  $ondelete = ForeignKeyInfo::SETNULL; break;
 300                case 'r':
 301                  $ondelete = ForeignKeyInfo::RESTRICT; break;
 302                default:
 303                case 'a':
 304                  //NOACTION is the postgresql default
 305                  $ondelete = ForeignKeyInfo::NONE; break;
 306              }
 307  
 308  
 309              $foreignTable = $this->database->getTable($foreign_table);
 310              $foreignColumn = $foreignTable->getColumn($foreign_column);
 311  
 312              $localTable   = $this->database->getTable($local_table);
 313              $localColumn   = $localTable->getColumn($local_column);
 314  
 315              if (!isset($this->foreignKeys[$name])) {
 316                  $this->foreignKeys[$name] = new ForeignKeyInfo($name);
 317              }
 318              $this->foreignKeys[$name]->addReference($localColumn, $foreignColumn, $ondelete, $onupdate);
 319          }
 320  
 321          $this->fksLoaded = true;
 322      }
 323  
 324      /** Load indexes for this table */
 325      protected function initIndexes()
 326      {
 327          include_once 'creole/metadata/IndexInfo.php';
 328  
 329          // columns have to be loaded first
 330          if (!$this->colsLoaded) $this->initColumns();
 331  
 332          $result = pg_query ($this->conn->getResource(), sprintf ("SELECT
 333                                                            DISTINCT ON(cls.relname)
 334                                                            cls.relname as idxname,
 335                                                            indkey,
 336                                                            indisunique
 337                                                      FROM pg_index idx
 338                                                           JOIN pg_class cls ON cls.oid=indexrelid
 339                                                      WHERE indrelid = %d AND NOT indisprimary
 340                                                      ORDER BY cls.relname", $this->oid));
 341  
 342  
 343          if (!$result) {
 344              throw new SQLException("Could not list indexes keys for table: " . $this->name, pg_last_error($this->conn->getResource()));
 345          }
 346  
 347          while($row = pg_fetch_assoc($result)) {
 348              $name = $row["idxname"];
 349              $unique = ($row["indisunique"] == 't') ? true : false;
 350              if (!isset($this->indexes[$name])) {
 351                  $this->indexes[$name] = new IndexInfo($name, $unique);
 352              }
 353              $arrColumns = explode (' ', $row['indkey']);
 354              foreach ($arrColumns as $intColNum)
 355              {
 356                  $result2 = pg_query ($this->conn->getResource(), sprintf ("SELECT a.attname
 357                                                              FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid
 358                                                              WHERE c.oid = '%s' AND a.attnum = %d AND NOT a.attisdropped
 359                                                              ORDER BY a.attnum", $this->oid, $intColNum));
 360                  if (!$result2)
 361                  {
 362                      throw new SQLException("Could not list indexes keys for table: " . $this->name, pg_last_error($this->conn->getResource()));
 363                  }
 364                  $row2 = pg_fetch_assoc($result2);
 365                  $this->indexes[$name]->addColumn($this->columns[ $row2['attname'] ]);
 366              } // foreach ($arrColumns as $intColNum)
 367          }
 368  
 369          $this->indexesLoaded = true;
 370      }
 371  
 372      /** Loads the primary keys for this table. */
 373      protected function initPrimaryKey() {
 374  
 375          include_once 'creole/metadata/PrimaryKeyInfo.php';
 376  
 377  
 378          // columns have to be loaded first
 379          if (!$this->colsLoaded) $this->initColumns();
 380  
 381          // Primary Keys
 382          
 383          $result = pg_query($this->conn->getResource(), sprintf ("SELECT
 384                                                            DISTINCT ON(cls.relname)
 385                                                            cls.relname as idxname,
 386                                                            indkey,
 387                                                            indisunique
 388                                                      FROM pg_index idx
 389                                                           JOIN pg_class cls ON cls.oid=indexrelid
 390                                                      WHERE indrelid = %s AND indisprimary
 391                                                      ORDER BY cls.relname", $this->oid));
 392          if (!$result) {
 393              throw new SQLException("Could not list primary keys for table: " . $this->name, pg_last_error($this->conn->getResource()));
 394          }
 395  
 396          // Loop through the returned results, grouping the same key_name together
 397          // adding each column for that key.
 398  
 399          while($row = pg_fetch_assoc($result)) {
 400              $arrColumns = explode (' ', $row['indkey']);
 401              foreach ($arrColumns as $intColNum)
 402              {
 403                  $result2 = pg_query ($this->conn->getResource(), sprintf ("SELECT a.attname
 404                                                              FROM pg_catalog.pg_class c JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid
 405                                                              WHERE c.oid = '%s' AND a.attnum = %d AND NOT a.attisdropped
 406                                                              ORDER BY a.attnum", $this->oid, $intColNum));
 407                  if (!$result2)
 408                  {
 409                      throw new SQLException("Could not list indexes keys for table: " . $this->name, pg_last_error($this->conn->getResource()));
 410                  }
 411                  $row2 = pg_fetch_assoc($result2);
 412                  if (!isset($this->primaryKey)) {
 413                      $this->primaryKey = new PrimaryKeyInfo($row2['attname']);
 414                  }
 415                  $this->primaryKey->addColumn($this->columns[ $row2['attname'] ]);
 416              } // foreach ($arrColumns as $intColNum)
 417          }
 418          $this->pkLoaded = true;
 419      }
 420  
 421      
 422  
 423  }