[Summary view] [Print] [Text view]

   1  <?php
   2  // $Id: schema_pgsql.inc,v 1.24 2009/07/13 22:12:22 mikeryan Exp $
   3  
   4  function schema_pgsql_engine_type_map() {
   5    $map = db_type_map(); // sigh
   6    return $map;
   7  }
   8  
   9  function schema_pgsql_schema_type_map() {
  10    static $map;
  11    if (!isset($map)) {
  12      $map = array_flip(schema_pgsql_engine_type_map());
  13      $map['character varying'] = 'varchar:normal';
  14      $map['integer'] = 'int:normal';
  15    }
  16    return $map;
  17  }
  18  
  19  function schema_pgsql_inspect($tbl_name = NULL) {
  20    global $db_url;
  21  
  22    // Switch to the active database connection.
  23    // The only way to get the active connection's name is as a return value from
  24    // db_set_active(). However, calling this function will automatically switch
  25    // the active connection to 'default', which might not be what we want.
  26    // Therefore, we must immediately call db_set_active() again with the desired
  27    // connection name in order to proceed.
  28    $active_db_connection = db_set_active();
  29    db_set_active($active_db_connection);
  30  
  31    $tables = array();
  32    $url = parse_url(is_array($db_url) ? $db_url[$active_db_connection] : $db_url);
  33    $database = substr($url['path'], 1);
  34  
  35    //
  36    // Retrieve information about all columns in the database from the
  37    // 'columns' table of 'information_schema'.  In pgsql, TABLE_CATALOG
  38    // is the database name and we provide $database to db_query below.
  39    // We sort the columns by table_name and ordinal_position so the get
  40    // added to our array in the same order.
  41    //
  42    $sql = ('SELECT * FROM information_schema.COLUMNS '.
  43        'WHERE table_catalog=\'%s\' AND table_schema=current_schema()');
  44    if (isset($tbl_name)) {
  45      $sql .= 'AND table_name = \'%s\' ';
  46    }
  47    $sql .= 'ORDER BY table_name, ordinal_position';
  48    
  49    $res = db_query($sql, $database, $tbl_name);
  50  
  51    //
  52    // Add an entry to $tables[<tablename>]['fields'] for each column.  $r
  53    // is a row from information_schema.columns.  $col is the Schema
  54    // column structure we build up from it.
  55    //
  56    while ($r = db_fetch_array($res)) {
  57      $col = array();
  58  
  59      $r['new_table_name'] = schema_unprefix_table($r['table_name']);
  60  
  61      // We treat numeric columns slightly differently and identify them
  62      // because they have a 'numeric_precision_radix' property.
  63      $numeric = !is_null($r['numeric_precision_radix']);
  64  
  65      // Determine the Schema type and size from the database data_type.
  66      list($col['type'], $col['size']) = schema_schema_type($r['data_type'], $r['table_name'], $r['column_name'], 'pgsql');
  67  
  68      // Non-numeric columns (e.g. varchar) can have a 'length'.
  69      if (! $numeric && $r['character_maximum_length']) {
  70        $col['length'] = $r['character_maximum_length'];
  71      }
  72  
  73      // Type 'numeric' columns have precision and scale
  74      if ($col['type'] == 'numeric') {
  75        $col['precision'] = (int) $r['numeric_precision'];
  76        $col['scale'] = (int) $r['numeric_scale'];
  77      }
  78  
  79      // Any column can have NOT NULL.
  80      $col['not null'] = ($r['is_nullable'] == 'YES' ? FALSE : TRUE);
  81  
  82      // Any column might have a default value.  We have to set
  83      // $col['default'] to the correct type of data.  Remember that '',
  84      // 0, and '0' are all different types.
  85      if (! is_null($r['column_default'])) {
  86  
  87        // pgsql's column_default can have ::typename appended,
  88        // nextval('<sequence_name>') if it is serial, etc.  Here, we're
  89        // just splitting out the actual default value.
  90        if (strpos($r['column_default'], '::') !== FALSE) {
  91          list($col['default'], $def_type) = explode('::', $r['column_default']);
  92        } else {
  93          $col['default'] = $r['column_default'];
  94          $def_type = '';
  95        }
  96        
  97        if ($numeric) {
  98          // $col['default'] is currently a string.  If the column is
  99          // numeric, use intval() or floatval() to extract the value as a
 100          // numeric type.
 101  
 102          // The value is actually an expression, and may be stored with parentheses
 103          $col['default'] = preg_replace('/^\((.*)\)$/', '\1', $col['default']);
 104  
 105          // more pgsql-specific stuff
 106          if (strpos($col['default'], 'nextval(\'') !== FALSE &&
 107            $def_type == 'regclass)') {
 108            $col['type'] = 'serial';
 109            unset($col['default']);
 110          } 
 111          else if ($col['type'] == 'float') {
 112            $col['default'] = floatval($col['default']);
 113          } else {
 114            $col['default'] = intval($col['default']);
 115          }
 116        } else {
 117          // The column is not numeric, so $col['default'] should remain
 118          // a string.  However, pgsql returns $r['column_default']
 119          // wrapped in single-quotes.  We just want the string value,
 120          // so strip off the quotes.
 121          $col['default'] = substr($col['default'], 1, -1);
 122        }
 123  
 124      }
 125  
 126      // Set $col['unsigned'] if the column is unsigned.  These
 127      // domains are currently defined in system.install (they should
 128      // probably eventually be moved into database.pgsql.inc).
 129      switch ($r['domain_name']) {
 130        case 'int_unsigned':
 131        case 'smallint_unsigned':
 132        case 'bigint_unsigned':
 133          $col['unsigned'] = 1;
 134          break;
 135      }
 136      if (isset($r['check_clause']) && $r['check_clause'] == '(('. $r['column_name'] .' => 0))') {
 137        $col['unsigned'] = 1;
 138      }
 139  
 140      // Save the column definition we just derived from $r.
 141      $tables[$r['table_name']]['fields'][$r['column_name']] = $col;
 142      // At this point, $tables is indexed by the raw db table name - save the unprefixed
 143      // name for later use
 144      $tables[$r['table_name']]['name'] = $r['new_table_name'];
 145    }
 146  
 147    //
 148    // Make sur we caught all the unsigned columns.  I could not get
 149    // this to work as a left join on the previous query.
 150    //
 151    $res = db_query('SELECT ccu.*, cc.check_clause 
 152                     FROM information_schema.constraint_column_usage ccu 
 153                     INNER JOIN information_schema.check_constraints cc ON ccu.constraint_name=cc.constraint_name 
 154                     WHERE table_schema=current_schema()');
 155    while ($r = db_fetch_array($res)) {
 156      $r['table_name'] = schema_unprefix_table($r['table_name']);
 157  
 158      if ($r['check_clause'] == '(('. $r['column_name'] .' >= 0))' ||
 159        $r['check_clause'] == '(('. $r['column_name'] .' >= (0)::numeric))') {
 160        $tables[$r['table_name']]['fields'][$r['column_name']]['unsigned'] =TRUE;
 161      }
 162    }
 163  
 164    //
 165    // Retrieve information about all keys in the database from the pg
 166    // system catalogs.  This query is derived from phpPgAdmin's
 167    // getIndexes() function.  The pg_get_indexdef() function returns
 168    // the CREATE INDEX statement to create the index; we parse it to
 169    // produce our data structure.  Ick.
 170    //
 171    $res = db_query('SELECT n.nspname, c.relname AS tblname, '.
 172      '   c2.relname AS indname, i.indisprimary, i.indisunique, '.
 173      '   pg_get_indexdef(i.indexrelid) AS inddef '.
 174      'FROM pg_class c, pg_class c2, pg_index i, pg_namespace n '.
 175      'WHERE c.oid = i.indrelid AND i.indexrelid = c2.oid AND '.
 176      '      c.relnamespace=n.oid AND n.nspname=current_schema() '.
 177      'ORDER BY c2.relname');
 178    while ($r = db_fetch_array($res)) {
 179      $r['tblname'] = schema_unprefix_table($r['tblname']);
 180      $r['indname'] = schema_unprefix_table($r['indname']);
 181  
 182      if (preg_match('@CREATE(?: UNIQUE)? INDEX \w+ ON "?(\w+)"?(?: USING \w+)? \((.*)\)@', $r['inddef'], $m)) {
 183        list($all, $table, $keys) = $m;
 184  
 185        $name = $r['indname'];
 186        if (preg_match('@^'.$r['tblname'].'_(.*)_(?:idx|key)$@', $name, $m)) {
 187          $name = $m[1];
 188        }
 189  
 190        preg_match_all('@((?:"?\w+"?)|(?:substr\(\(?"?(\w+)\"?.*?, 1, (\d+)\)))(?:, |$)@', $keys, $m);
 191        foreach ($m[1] as $idx => $colname) {
 192          if ($m[2][$idx]) {
 193            $key = array($m[2][$idx], intval($m[3][$idx]));
 194          } else {
 195            $key = str_replace('"', '', $colname);
 196          }
 197          if ($r['indisprimary'] == 't') {
 198            $tables[$r['tblname']]['primary key'][] = $key;
 199          } else if ($r['indisunique'] == 't') {
 200            $tables[$r['tblname']]['unique keys'][$name][] = $key;
 201          } else {
 202            $tables[$r['tblname']]['indexes'][$name][] = $key;
 203          }
 204        }
 205      } 
 206      else {
 207        watchdog('schema', 'unrecognized pgsql index definition: @stmt',
 208          array('@stmt' => $r['inddef']));
 209      }
 210    }
 211    
 212    // Now, for tables which we have unprefixed, index $tables by the unprefixed name
 213    foreach ($tables as $tablename => $table) {
 214      $newname = $tables[$tablename]['name'];
 215      if ($tablename != $newname) {
 216        $tables[$newname] = $table;
 217        unset($tables[$tablename]);
 218      }
 219    }
 220  
 221    // All done!  Visit admin/build/schema/inspect to see the
 222    // pretty-printed version of what gets returned here and verify if
 223    // it is correct.
 224    return $tables;
 225  }