Raise PHP requirement to 5.2.12 for PostgreSQL only

Seems it is already mentioned in bugs.php.net (http://bugs.php.net/bug.php?id=41135), isn't it? But I am not sure if it can solve the case of PostgreSQL, too :S

Also test with php5.3-200810020430.tar.gz and still fail :S

This bug should belongs to pdo_pgsql but not PostgreSQL itself. I simplify and clone the programming logic of our TNG DB abstraction and database_test.test as below.

Here are some code for reproduce the bug.

Prepare testing database and table

createdb -O postgres DRUPAL_7
su - postgres
psql DRUPAL_7

DRUPAL_7=# CREATE TABLE "test_one_blob" (
id SERIAL NOT NULL,
blob1 BYTEA
);

Testing with psql directly (CORRECT)

DRUPAL_7=# INSERT INTO "test_one_blob" ("blob1") VALUES (E'This is\\000a test.');
DRUPAL_7=# INSERT INTO "test_one_blob" ("blob1") VALUES ('');
DRUPAL_7=# INSERT INTO "test_one_blob" ("blob1") VALUES (NULL);

DRUPAL_7=# SELECT * FROM "test_one_blob";

Testing with PHP5.2 and pdo_pgsql (BUGGY!)

function db_insert($sql, $data) {
  global $active_db;
  $stmt = $active_db->prepare($sql);
  $blob = fopen('php://memory', 'a');
  fwrite($blob, $data);
  rewind($blob);
  $stmt->bindParam(':blob1', $blob, PDO::PARAM_LOB);
  $stmt->execute();
  $id = $active_db->lastInsertId('test_one_blob_id_seq');
  return $id;
}

function db_select($sql, $id) {
  global $active_db;
  $sql = 'SELECT * FROM "test_one_blob" WHERE "id" = :id';
  $stmt = $active_db->prepare($sql);
  $stmt->bindParam(':id', $id);
  $stmt->execute();
  return $stmt->fetch(PDO::FETCH_ASSOC);
}

$active_db = new PDO('pgsql:host=localhost dbname=DRUPAL_7', 'root', 'CHANGE', array(PDO::ATTR_STRINGIFY_FETCHES => TRUE));

// Test normal BLOB insert
$id = db_insert('INSERT INTO "test_one_blob" ("blob1") VALUES (:blob1)', "This is\000a test.");
$return = db_select('SELECT * FROM "test_one_blob" WHERE "id" = :id', $id);
var_dump($return['blob1']);

// Test NULL BLOB insert
$id = db_insert('INSERT INTO "test_one_blob" ("blob1") VALUES (:blob1)', NULL);
$return = db_select('SELECT * FROM "test_one_blob" WHERE "id" = :id', $id);
var_dump($return['blob1']);

// Test empty BLOB insert (BUGGY!!)
$id = db_insert('INSERT INTO "test_one_blob" ("blob1") VALUES (:blob1)', "");
$return = db_select('SELECT * FROM "test_one_blob" WHERE "id" = :id', $id);
var_dump($return['blob1']);

Also report to bugs.php.net:
http://bugs.php.net/46249

Add INSERT/UPDATE BLOB test for both NULL and empty string.

Tested with MySQL and PostgreSQL, PHP5.2.6 and PHP5.3-dev. MySQL is safe as expected, where PostgreSQL also BUGGY with empty string input as expected.

P.S. After asking help from IRC #postgresql, people suggest we may set existing BLOB fields as "nullable" in order to escape from the trap. BTW, some programming logic may also need to update IF we are using BLOB and compare its content with empty string :S

Because some tables are now coming with NOT NULL BLOB fields. When we INSERT/UPDATE empty string (which should be correct in programming logic) in these fields, pdo_pgsql will translate it as NULL and so report error.

I mention the above concern in #postgresql. aglio2 suggest that setting fields as "nullable" can be a quick solution, or else we may need to wait for months before pdo_pgsql get fixed.

P.S. I am also giving a try for http://drupal.org/node/147947#comment-1049609 related issues and most are functioning with "nullable" BLOB fields. Only face difficulty with node related changes and may need help soon :-)

We can handle nullable within *.install. Example: http://drupal.org/node/300802#comment-1049737

Followup: This issue is now figure out as PHP pdo and pdo_pgsql bugs (http://bugs.php.net/bug.php?id=46249). I am now actively communicate with felipe@php.net and hopefully it will soon get fixed within PHP CVS HEAD. It should coming with PHP5.2.7 (it is now 5.2.7RC2).

I will suggest for the commit of patch in #8, and let PostgreSQL's simpletest come with a bit warning at this moment. Soon after the pdo and pdo_pgsql get fixed, we can document the minimal PHP version requirement as 5.2.7. Any idea?

This bug is now get fixed in PHP 5.2 and 5.3 CVS HEAD. All packages newer than snapshot php5.2-200810130030.tar.gz. is safe from this issue. Please check http://bugs.php.net/bug.php?id=46249 for more detail.

Patch files detail:

• tngdb-pdo_pgsql-blob-*.patch: BLOB NULL is a bit different from normal case: we can't use StreamAPI, but bind NULL to target field direcly. Because writing NULL into file handler means write nothing. The file handler will contain an empty string but not NULL. Therefore NULL will be translated as empty string when save into database, and so buggy. This patch fix the above issue.
• testEmptyBlob-*.patch: Similar as case before, add simpletest test case for both NULL and empty BLOB INSERT/UPDATE. Also update comment message and external links as bug reference.
• DRUPAL_MINIMUM_PHP-*.patch: Since it is just get fixed in PHP CVS HEAD, we will need to push our minimal PHP support version to coming 5.2.7 (as it is now 5.2.7RC2-dev). This can postpone till our D7 public release, or the official release of PHP 5.2.7.

All patches tested with MySQL and PostgreSQL, and all passed. It is a critical issue for PostgreSQL support and so may someone give a hand for it?

P.S. I wrote some note for install Apache2.2 + PHP5.2 + PostgreSQL manually, and you may find this as useful for setting up your own testbed: Apache2.2 + PHP5.2 + pgsql/pdo_pgsql from sketch on Debian sid HOWTO

@Crell: Totally agree with you, and so the DRUPAL_MINIMUM_PHP-*.patch should postpone until suitable timing.

BTW, the tngdb-pdo_pgsql-blob-*.patch is the correct handling of BLOB field if database required for variable binding. I use similar code snippet when develop Oracle and DB2 drivers. This patch should able to handle independently.

For the testEmptyBlob-*.patch, it is already well documented that it won't pass until we are using PHP version newer than snapshot php5.2-200810130030.tar.gz, so there shouldn't be any confuse. Maybe just let the test case result with warning, when we are using PHP with old version?

Latest complete version of bug reproduction code snippet:

function db_insert($sql, $data) {
  global $active_db;
  $stmt = $active_db->prepare($sql);
  if (is_null($data)) {
    $stmt->bindParam(':blob1', $data, PDO::PARAM_LOB);
  }
  else {
    $blob = fopen('php://memory', 'a');
    fwrite($blob, $data);
    rewind($blob);
    $stmt->bindParam(':blob1', $blob, PDO::PARAM_LOB);
  }
  $stmt->execute();
  $id = $active_db->lastInsertId('test_one_blob_id_seq');
  return $id;
}

function db_select($sql, $id) {
  global $active_db;
  $sql = 'SELECT * FROM "test_one_blob" WHERE "id" = :id';
  $stmt = $active_db->prepare($sql);
  $stmt->bindParam(':id', $id);
  $stmt->execute();
  return $stmt->fetch(PDO::FETCH_ASSOC);
}

$active_db = new PDO('pgsql:host=localhost dbname=DRUPAL_7', 'root',
'CHANGE', array(PDO::ATTR_STRINGIFY_FETCHES => TRUE));

// Test normal BLOB insert
$data = "This is\000a test.";
$id = db_insert('INSERT INTO "test_one_blob" ("blob1") VALUES (:blob1)',
$data);
$return = db_select('SELECT * FROM "test_one_blob" WHERE "id" = :id',
$id);
var_dump("Test normal BLOB insert");
var_dump($data);
var_dump($return['blob1']);
var_dump($data === $return['blob1']);

// Test NULL BLOB insert
$data = NULL;
$id = db_insert('INSERT INTO "test_one_blob" ("blob1") VALUES (:blob1)',
$data);
$return = db_select('SELECT * FROM "test_one_blob" WHERE "id" = :id',
$id);
var_dump("Test NULL BLOB insert");
var_dump($data);
var_dump($return['blob1']);
var_dump($data === $return['blob1']);

// Test empty BLOB insert
$data = "";
$id = db_insert('INSERT INTO "test_one_blob" ("blob1") VALUES (:blob1)',
$data);
$return = db_select('SELECT * FROM "test_one_blob" WHERE "id" = :id',
$id);
var_dump("Test empty BLOB insert");
var_dump($data);
var_dump($return['blob1']);
var_dump($data === $return['blob1']);