PostgreSQL auto-converts column names into lowercase

drupal8.postgres-case.0.patch queued for re-testing.

Great! Patch passes! This will fix #1529268: 2.x series is incompatible with PostgreSQL due to use of uppercase in column names which is how I found my way here. Wrapping the identifiers in quotes maintains the the letter-casing of column names, which is exactly what we want (otherwise postgres queries die a painful PDO Exception death).

Does this need to be committed to 8.x before backporting to 7.x?

What this patch misses is the ability to wrap with quotes each field which contains upper case. Example: contentId needs to be wrapped in quotes.

drupal_db=# select * from testtable where contentId = '5';
ERROR:  column "contentid" does not exist
LINE 1: select * from testtable where contentId = '5';
                                      ^

drupal_db=# select * from testtable where "contentId" = '5';
 form_id | contentId | module
---------+-----------+--------
(0 rows)

In the following example, t.contentId needs to be t."contentId"

drupal_db=# SELECT t.contentId from testtable AS t where "contentId" = '5';
ERROR:  column t.contentid does not exist
LINE 1: SELECT t.contentId from testtable AS t where "contentId" = '5';
               ^

drupal_db=# SELECT t."contentId" from testtable AS t where "contentId" = '5';
 contentId
-----------
(0 rows)

In drupal 7, part of the solution is to implement the escapeField() function from the DatabaseConnection class, which works as follows:

<?php
  public function escapeField($field) {
    if(preg_match('/[A-Z]/', $field)) {
      $i = strpos($field, '.');
      if ($i) {
        // e.g. n.title -> n."Title"
        return substr($field, 0, $i) . '."' . substr($field, $i + 1) . '"';
      }
      return '"' . $field . '"';
    }
    return $field;
  }
?>

But there still needs to be something done on module installation, so that field comments can be added. For instance, with the previous code implemented (in d7), I get the following error upon installation of the Mollom module:

WD php: PDOException: SQLSTATE[42703]: Undefined column: 7 ERROR:  column "contentid" of relation "mollom" does not exist: COMMENT ON COLUMN {mollom}.contentId IS 'Content ID returned by     [error]
Mollom.';

As you can see, contentId is not wrapped in quotes (and I don't know enough about the db abstraction layer to know where that query gets built).

Sorry for the d7 example - I haven't started working with d8 yet. I thought it was useful to post here because the same problem needs to be solved in d8.

Aside: wouldn't it be simpler to to run strtolower(); on all fields, in the pgsql implementation, rather than wrapping all fields with quotes to preserve their uppercase-ness?

Here's the d7 correction in createTableSql() in includes/database/pgsql/schema.inc:

    // Add column comments.
    foreach ($table['fields'] as $field_name => $field) {
      if (!empty($field['description'])) {
        if (preg_match('/[A-Z]/', $field_name)) {
          $field_name = '"' . $field_name . '"';
        }
        $statements[] = 'COMMENT ON COLUMN {' . $name . '}.' . $field_name . ' IS ' . $this->prepareComment($field['description']);
      }
    }

The extra bit is the check:

        if (preg_match('/[A-Z]/', $field_name)) {
          $field_name = '"' . $field_name . '"';
        }

Then Mollom installs correctly.

With both the patch in #5 and the initial drupal8.postgres-case.0.patch at the beginning of this thread, Mollom installs correctly; however, in operation I get

PDOException: SQLSTATE[42703]: Undefined column: 7 ERROR: column "contentid" of relation "mollom" does not exist LINE 1: INSERT INTO mollom (entity, id, contentId, captchaId... INSERT INTO mollom (entity, id, contentId, captchaId, form_id, changed, moderate, spamScore, spamClassification, solved, qualityScore, profanityScore, reason, languages) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6, :db_insert_placeholder_7, :db_insert_placeholder_8, :db_insert_placeholder_9, :db_insert_placeholder_10, :db_insert_placeholder_11, :db_insert_placeholder_12, :db_insert_placeholder_13); Array ( ) in drupal_write_record() (line 7166 of /var/www/site.com/news/includes/common.inc).

because again PostgreSQL lower-cases unquoted field names, which yields to a mismatch with the schema.

Seems this is more or less a duplicate of #1600670: Cannot query Postgres database that has column names with capital letters.