#349671 by Damien Tournoud and Josh Waihi: Make the PostgreSQL driver independent of schema to fix D6 => D7 update.

3 files changed, 91 insertions, 32 deletions

diff --git a/includes/database/pgsql/query.inc b/includes/database/pgsql/query.inc
index 726e59011..98d412ae6 100644
--- a/includes/database/pgsql/query.inc
+++ b/includes/database/pgsql/query.inc
@@ -1,7 +1,6 @@
 <?php
 // $Id$
 
-
 /**
  * @ingroup database
  * @{
@@ -26,29 +25,28 @@ class InsertQuery_pgsql extends InsertQuery {
       return NULL;
     }
 
-    $schema = drupal_get_schema($this->table);
-
     $stmt = $this->connection->prepareQuery((string)$this);
 
+    // Fetch the list of blobs and sequences used on that table.
+    $table_information = $this->connection->schema()->queryTableInformation($this->table);
+
     $max_placeholder = 0;
     $blobs = array();
-    $blob_cnt = 0;
+    $blob_count = 0;
     foreach ($this->insertValues as &$insert_values) {
       foreach ($this->insertFields as $idx => $field) {
-        switch ($schema['fields'][$field]['type']) {
-          case 'blob':
-            $blobs[$blob_cnt] = fopen('php://memory', 'a');
-            fwrite($blobs[$blob_cnt], $insert_values[$idx]);
-            rewind($blobs[$blob_cnt]);
-
-            $stmt->bindParam(':db_insert_placeholder_' . $max_placeholder++, $blobs[$blob_cnt], PDO::PARAM_LOB);
+        if (isset($table_information->blob_fields[$field])) {
+          $blobs[$blob_count] = fopen('php://memory', 'a');
+          fwrite($blobs[$blob_count], $insert_values[$idx]);
+          rewind($blobs[$blob_count]);
 
-            ++$blob_cnt;
+          $stmt->bindParam(':db_insert_placeholder_' . $max_placeholder++, $blobs[$blob_count], PDO::PARAM_LOB);
 
-            break;
-          default:
-            $stmt->bindParam(':db_insert_placeholder_'. $max_placeholder++, $insert_values[$idx]);
-            break;
+          // Pre-increment is faster in PHP than increment.
+          ++$blob_count;
+        }
+        else {
+          $stmt->bindParam(':db_insert_placeholder_'. $max_placeholder++, $insert_values[$idx]);
         }
       }
     }
@@ -58,8 +56,8 @@ class InsertQuery_pgsql extends InsertQuery {
     // the options array.
     $options = $this->queryOptions;
 
-    if ($schema['fields'][$schema['primary key'][0]]['type'] == 'serial') {
-      $options['sequence_name'] = $this->connection->makeSequenceName($this->table, $schema['primary key'][0]);
+    if (!empty($table_information->sequences)) {
+      $options['sequence_name'] = $table_information->sequences[0];
       $options['return'] = Database::RETURN_INSERT_ID;
     }
     $last_insert_id = $this->connection->query($stmt, array(), $options);
@@ -113,12 +111,13 @@ class UpdateQuery_pgsql extends UpdateQuery {
     $blobs = array();
     $blob_count = 0;
 
-    $schema = drupal_get_schema($this->table);
-
     // Because we filter $fields the same way here and in __toString(), the
     // placeholders will all match up properly.
     $stmt = $this->connection->prepareQuery((string)$this);
 
+    // Fetch the list of blobs and sequences used on that table.
+    $table_information = $this->connection->schema()->queryTableInformation($this->table);
+
     // Expressions take priority over literal fields, so we process those first
     // and remove any literal fields that conflict.
     $fields = $this->fields;
@@ -138,17 +137,15 @@ class UpdateQuery_pgsql extends UpdateQuery {
     foreach ($fields as $field => &$value) {
       $placeholder = ':db_update_placeholder_' . ($max_placeholder++);
 
-      switch ($schema['fields'][$field]['type']) {
-        case 'blob':
-          $blobs[$blob_count] = fopen('php://memory', 'a');
-          fwrite($blobs[$blob_count], $value);
-          rewind($blobs[$blob_count]);
-          $stmt->bindParam($placeholder, $blobs[$blob_count], PDO::PARAM_LOB);
-          ++$blob_count;
-          break;
-        default:
-          $stmt->bindParam($placeholder, $value);
-          break;
+      if (isset($table_information->blob_fields[$field])) {
+        $blobs[$blob_count] = fopen('php://memory', 'a');
+        fwrite($blobs[$blob_count], $value);
+        rewind($blobs[$blob_count]);
+        $stmt->bindParam($placeholder, $blobs[$blob_count], PDO::PARAM_LOB);
+        ++$blob_count;
+      }
+      else {
+        $stmt->bindParam($placeholder, $value);
       }
     }
 
@@ -165,7 +162,6 @@ class UpdateQuery_pgsql extends UpdateQuery {
     $options['already_prepared'] = TRUE;
     $this->connection->query($stmt, $options);
 
-    //$stmt->execute(NULL, $this->queryOptions);
     return $stmt->rowCount();
   }
 }
diff --git a/includes/database/pgsql/schema.inc b/includes/database/pgsql/schema.inc
index f467f0af1..c8af719e8 100644
--- a/includes/database/pgsql/schema.inc
+++ b/includes/database/pgsql/schema.inc
@@ -14,6 +14,61 @@
 class DatabaseSchema_pgsql extends DatabaseSchema {
 
   /**
+   * A cache of information about blob columns and sequences of tables.
+   *
+   * This is collected by DatabaseConnection_pgsql->queryTableInformation(),
+   * by introspecting the database.
+   *
+   * @see DatabaseConnection_pgsql->queryTableInformation().
+   * @var array
+   */
+  protected $tableInformation = array();
+
+  /**
+   * Fetch the list of blobs and sequences used on a table.
+   *
+   * We introspect the database to collect the information required by insert
+   * and update queries.
+   *
+   * @param $table_name
+   *   The non-prefixed name of the table.
+   * @return
+   *   An object with two member variables:
+   *     - 'blob_fields' that lists all the blob fields in the table.
+   *     - 'sequences' that lists the sequences used in that table.
+   */
+  public function queryTableInformation($table) {
+    // Generate a key to reference this table's information on.
+    $key = $this->connection->prefixTables('{' . $table . '}');
+    if (!strpos($key, '.')) {
+      $key = 'public.' . $key;
+    }
+
+    if (!isset($this->tableInformation[$key])) {
+      // Split the key into schema and table for querying.
+      list($schema,$table_name) = explode('.', $key);
+      $table_information = (object) array(
+        'blob_fields' => array(),
+        'sequences' => array(),
+      );
+      $result = db_query("SELECT column_name, data_type, column_default FROM information_schema.columns WHERE table_schema = :schema AND table_name = :table AND (data_type = 'bytea' OR (numeric_precision IS NOT NULL AND column_default LIKE :default))", array(':schema' => $schema, ':table' => $table_name, ':default' => '%nextval%'));
+      foreach ($result as $column) {
+        if ($column->data_type == 'bytea') {
+          $table_information->blob_fields[$column->column_name] = TRUE;
+        }
+        else if (preg_match("/nextval\('([^']+)'/", $column->column_default, $matches)) {
+          // We must know of any sequences in the table structure to help us
+          // return the last insert id. If there is more than 1 sequences the
+          // first one (index 0 of the sequences array) will be used.
+          $table_information->sequences[] = $matches[1];
+        }
+      }
+      $this->tableInformation[$key] = $table_information;
+    }
+    return $this->tableInformation[$key];
+  }
+
+  /**
    * Generate SQL to create a new table from a Drupal schema definition.
    *
    * @param $name
diff --git a/includes/database/schema.inc b/includes/database/schema.inc
index 70b902b0e..2a858a73c 100644
--- a/includes/database/schema.inc
+++ b/includes/database/schema.inc
@@ -78,6 +78,10 @@
  *  - 'unique keys': An associative array of unique keys ('keyname' =>
  *    specification). Each specification is an array of one or more
  *    key column specifiers (see below) that form a unique key on the table.
+ *  - 'foreign keys': An associative array, each key references a column
+ *    of the local table, each value is an array with a single key pair as
+ *    'tablename' => 'column' where 'column' is the foreign column to
+ *    reference.
  *  - 'indexes':  An associative array of indexes ('indexame' =>
  *    specification). Each specification is an array of one or more
  *    key column specifiers (see below) that form an index on the
@@ -98,6 +102,7 @@
  * $schema['node'] = array(
  *   'fields' => array(
  *     'nid'      => array('type' => 'serial', 'unsigned' => TRUE, 'not null' => TRUE),
+ *     'uid'      => array('type' => 'int', 'unsigned' => TRUE, 'not null' => TRUE),
  *     'vid'      => array('type' => 'int', 'unsigned' => TRUE, 'not null' => TRUE, 'default' => 0),
  *     'type'     => array('type' => 'varchar', 'length' => 32, 'not null' => TRUE, 'default' => ''),
  *     'title'    => array('type' => 'varchar', 'length' => 128, 'not null' => TRUE, 'default' => ''),
@@ -106,6 +111,9 @@
  *   'unique keys' => array(
  *     'vid'     => array('vid')
  *   ),
+ *   'foreign keys' => array(
+ *     'uid'  => array('users' => 'uid'),
+ *   )
  *   'indexes' => array(
  *     'nid'                 => array('nid'),
  *     'node_title_type'     => array('title', array('type', 4)),