Skip to content

Instantly share code, notes, and snippets.

@joubertredrat
Created December 20, 2016 19:21
Show Gist options
  • Save joubertredrat/cd6ee4ebcb59c22867a6fb93123c0d24 to your computer and use it in GitHub Desktop.
Save joubertredrat/cd6ee4ebcb59c22867a6fb93123c0d24 to your computer and use it in GitHub Desktop.
Single quotes on mysql reserved keywords in schema creation

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment