ikkez/f3-schema-builder

Bug: TableModifier->getCols(true) returns broken Timestamp value in MySQL

exodus4d opened this issue · 2 comments

Regarding to the docs for getCols(true) ;

Returns an array of existing table columns. If $types is set to TRUE, it will return an associative array with column name as key and the schema array as value.

This does not work for columns defined as 'default' => Schema::DF_CURRENT_TIMESTAMP.

What you get for that column:

array(5) {
  ["type"]=> "timestamp"
  ["pdo_type"]=>2
  ["default"]=> "current_timestamp()" <-- bad!  :(
  ["nullable"]=>true
  ["pkey"]=> false
}

What you expect:

array(5) {
  ["type"]=> "timestamp"
  ["pdo_type"]=>2
  ["default"]=> "CUR_STAMP" <-- good!
  ["nullable"]=>true
  ["pkey"]=> false
}

Hint: Schema::DF_CURRENT_TIMESTAMP == "CUR_STAMP" (https://github.com/ikkez/f3-schema-builder/blob/master/lib/db/sql/schema.php#L102)

Where does this happen?

The problem is the condition in line 768 (https://github.com/ikkez/f3-schema-builder/blob/master/lib/db/sql/schema.php#L768):

if (!is_null($default) && (
        (is_int(strpos($curdef=$this->findQuery($this->schema->defaultTypes['CUR_STAMP']),
                $default)) || is_int(strpos($default,$curdef)))
        || $default == "('now'::text)::timestamp(0) without time zone"))
{
    $default = 'CUR_STAMP';
 }

To be more precise, is_int(strpos($default,$curdef)) is supposed to be evaluated as true, then the complete if becomes true and $default becomes "CUR_STAMP".

Why does this happen:

  1. $default is "current_timestamp()" This is correct! (because it read from "information_schema.columns" database table)
  2. $curdef is "CURRENT_TIMESTAMP" This is correct! (is set one line above)

now we have ``is_int(strpos( "current_timestamp()" ,"CURRENT_TIMESTAMP"))=>false`.

How to fix:

Wrap both variables in strtolower() functions inside of strpos(). Then we get the expected true :)

ikkez commented

good catch 😉 and thanks for the detailed analysis :)
I think it didn't harm that much, as undetected values are passed through and handled raw, but it's much cleaner when this value actually is what one may expect ;)

Thanks for fix! I came across this issue when I implemented a "default column value change detection" to my DB-diff tool. I always get the result, that columns with default timestamp value have been changed :)