Hi guys,
I'm testing Schema from DBAL and I discovered this problem below:
<?php
require(__DIR__.'/vendor/autoload.php');
$schema = new \Doctrine\DBAL\Schema\Schema();
$products = $schema->createTable('products');
$products->addColumn(
'date_insert',
'datetime',
[
'default' => 'CURRENT_TIMESTAMP',
'comment' => 'Insert date'
]
);
$products->addColumn(
'date_update',
'datetime',
[
'notnull' => false,
'default' => "NULL ON UPDATE CURRENT_TIMESTAMP",
'comment' => 'Last change date'
]
);
$queries = $schema->toSql(new Doctrine\DBAL\Platforms\MySQL57Platform());
echo $queries[0];
$queries = $schema->toSql(new Doctrine\DBAL\Platforms\MySqlPlatform());
echo $queries[0];
This code above, the expected result is
CREATE TABLE products (
date_insert DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT 'Insert date',
date_update DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last change date'
) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB
But schema is putting single quotes on NULL ON UPDATE CURRENT_TIMESTAMP
, causing a SQL syntax error.
CREATE TABLE products (
date_insert DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL COMMENT 'Insert date',
date_update DATETIME DEFAULT 'NULL ON UPDATE CURRENT_TIMESTAMP' COMMENT 'Last change date'
) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB
On debug here, we saw that single quotes is removed only if default is CURRENT_TIMESTAMP
and not if is user another keywords, as NOW()
, NULL
, CURRENT_TIME
, CURRENT_DATE
and others.
https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Platforms/AbstractPlatform.php#L2278
I was able to fix this in my test with this class
<?php
/**
* Class with default bugfix for tests
*
* @author Joubert <[email protected]>
*/
namespace Doctrine\DBAL\Platforms;
class MySQL57PlatformWithFix extends MySQL57Platform
{
/**
* Get reserved keywords for default
*
* @return array
*/
public static function getReservedDefaultKeywords()
{
return [
'NULL',
'CURRENT_DATE',
'CURRENT_TIME',
'CURRENT_TIMESTAMP',
'NULL ON UPDATE CURRENT_DATE',
'NULL ON UPDATE CURRENT_TIME',
'NULL ON UPDATE CURRENT_TIMESTAMP',
];
}
/**
* @see Doctrine\DBAL\Platforms\MySqlPlatform::getDefaultValueDeclarationSQL()
*/
public function getDefaultValueDeclarationSQL($field)
{
if (in_array($field['default'], self::getReservedDefaultKeywords())) {
$default = " DEFAULT ".$field['default'];
} else {
$default = parent::getDefaultValueDeclarationSQL($field);
}
return $default;
}
}
Now is time to talk guys, this is a good question.