Evaluation of default field values in pgsql

On my system, the "Compare" feature of the schema module reports this error for the {system} table:

column schema_version:
declared: array('type' => 'int', 'size' => 'small', 'not null' => TRUE, 'default' => -1)
actual: array('type' => 'int', 'size' => 'small', 'not null' => TRUE, 'default' => 0)

But the reported "actual" default value doesn't reflect what I have in my pgsql database.

The schema definition has not recently changed, it is :

...
      'schema_version' => array(
        'description' => "The module's database schema version number. -1 if the module is not installed (its tables do not exist); 0 or the largest N of the module's hook_update_N() function that has either been run or existed when the module was first installed.",
        'type' => 'int',
        'not null' => TRUE,
        'default' => -1,
        'size' => 'small'),
...

So the default value is actually declared as "-1", we agree for this side.

If I check the database structure via pg_dump: pg_dump -s -t drupal.system mich, here's part of the output:

CREATE TABLE system (
    filename character varying(255) DEFAULT ''::character varying NOT NULL,
    name character varying(255) DEFAULT ''::character varying NOT NULL,
    type character varying(255) DEFAULT ''::character varying NOT NULL,
    owner character varying(255) DEFAULT ''::character varying NOT NULL,
    status integer DEFAULT 0 NOT NULL,
    throttle smallint DEFAULT 0 NOT NULL,
    bootstrap integer DEFAULT 0 NOT NULL,
    schema_version smallint DEFAULT (-1) NOT NULL,
    weight integer DEFAULT 0 NOT NULL,
    info text
);

The default value is "(-1)" which is incorrectly understood by schema module as "0".

The origin of this error is in schema_pgsql_inspect($table) (schema_pgsql.inc line 21) .

Actual schema is retrieved via the sql call, line 35. Narrowing to our specific case:

SELECT column_name,column_default,data_type FROM information_schema.columns
WHERE table_catalog='mich' AND table_schema=current_schema()
AND table_name='system' AND column_name='schema_version';

  column_name   | column_default | data_type 
----------------+----------------+-----------
 schema_version | (-1)           | smallint
(1 row)

The problem is likely in the block starting at line 90. Specifically, at line 104

          $col['default'] = intval($col['default']);

because intval('(-1)') evaluates to 0.

So how should we interpret the value ? The pg manual says:

The DEFAULT clause assigns a default data value for the column whose column definition it appears within. The value is any variable-free expression (subqueries and cross-references to other columns in the current table are not allowed). The data type of the default expression must match the data type of the column.

Here we probably don't need full evaluation, so removing the extra parentheses would probably do in most cases.
schema-pgdefault-value.patch

--- schema_pgsql.inc.orig	2009-02-28 18:11:53.000000000 +0100
+++ schema_pgsql.inc	2009-02-28 18:16:19.000000000 +0100
@@ -92,6 +92,9 @@ function schema_pgsql_inspect($tbl_name 
         // numeric, use intval() or floatval() to extract the value as a
         // numeric type.
 
+        // The value is actually an expression, and may be stored with parentheses
+        $col['default'] = preg_replace('/^\((.*)\)$/', '\1', $col['default']);
+
         // more pgsql-specific stuff
         if (strpos($col['default'], 'nextval(\'') !== FALSE &&
           $def_type == 'regclass)') {

But it's not difficult to get closer to definition, approximating SQL expression evaluation with php evaluation . Let's try a little interactive php:

php > echo intval('(-1)');
0
php > eval('$val = (-1 + 3 * 6);');
php > echo $val;
17
php > $col['default'] = '(-1)';
php > eval("\$val = {$col['default']};");
php > echo $val;
-1
php > $col['default'] = '(-1 + 3 * 6)';
php > eval("\$col['default'] = {$col['default']};");
php > echo $col['default'];
17

So here's an alternative patch (schema-pgdefault-eval.patch), which is running fine for me:

--- schema_pgsql.inc.orig	2009-02-28 18:11:53.000000000 +0100
+++ schema_pgsql.inc	2009-02-28 18:43:22.000000000 +0100
@@ -97,11 +97,9 @@ function schema_pgsql_inspect($tbl_name 
           $def_type == 'regclass)') {
           $col['type'] = 'serial';
           unset($col['default']);
-        } 
-        else if ($col['type'] == 'float') {
-          $col['default'] = floatval($col['default']);
         } else {
-          $col['default'] = intval($col['default']);
+        // The value is actually an expression, which may be stored in parentheses
+          eval("\$col['default'] = {$col['default']};");
         }
       } else {
         // The column is not numeric, so $col['default'] should remain

That's the last post of this modest series. HTH.

Thanks Barry for this excellent module.

Best regards,
----
Michelle Baert