Ulti Ulti - 6 months ago 257
SQL Question

Doctrine2 - Type timestamp - Default value

For some reasons, I need to have tables with

TIMESTAMP
fields.

I created my own Timestamp Type (
Doctrine\DBAL\Types\Type
), it works fine.

But when I try to update my database structure, I got this.

Command line

ede80:~>php app/console doctrine:schema:update --force --complete
Updating database schema...

[Doctrine\DBAL\Exception\DriverException]
An exception occurred while executing 'ALTER TABLE MY_TABLE CHANGE DATE_CREATION DATE_CREATION timestamp DEFAULT NULL'

SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'DATE_CREATION'


SQL Query

If I execute the following query on the database, I works :

ALTER TABLE MY_TABLE CHANGE DATE_CREATION DATE_CREATION timestamp DEFAULT CURRENT_TIMESTAMP


ORM.YML

But when I try to change it in
MyTable.orm.yml
, like this :

dateCreation:
type: timestamp
nullable: true
column: DATE_CREATION
options:
default: CURRENT_TIMESTAMP


The executed query is

ALTER TABLE MY_TABLE CHANGE DATE_CREATION DATE_CREATION timestamp DEFAULT 'CURRENT_TIMESTAMP'


And it fails.

How can I set a working default value, so my database's structure can be up to date ?
I already tried to set
options: default
to
0
,
NULL
,
CURRENT_TIMESTAMP
,
00:00:01
...

PHP 5.3.3
MySQL 5.1.63

Here is the Timestamp Type

I think that convert*() aren't that well.

<?php
namespace CNAMTS\PHPK\CoreBundle\Doctrine\Type;

use Doctrine\DBAL\Types\Type;
use Doctrine\DBAL\Platforms\AbstractPlatform;

/**
*/
class Timestamp extends Type
{
const TIMESTAMP = 'timestamp';

public function getSQLDeclaration(array $fieldDeclaration, AbstractPlatform $platform)
{
return $platform->getDoctrineTypeMapping('TIMESTAMP');
}

public function convertToPHPValue($value, AbstractPlatform $platform)
{
return ($value === null) ? 0 : new \DateTime($value);
}

public function convertToDatabaseValue($value, AbstractPlatform $platform)
{
return ($value === null) ? 0 : $value->format(\DateTime::W3C);
}

public function getName()
{
return self::TIMESTAMP;
}
}

Answer

Here is the solution to have more than one timestamp field in the table.

You need to have your own Timestamp type defined and added in AppKernel.php :

public function boot() {
    parent::boot();

    /**
     * SQL type TIMESTAMP
     */
    $em = $this->container->get('doctrine.orm.default_entity_manager');
    Doctrine\DBAL\Types\Type::addType('timestamp', 'My\CoreBundle\Doctrine\Type\Timestamp');
    $em->getConnection()->getDatabasePlatform()->registerDoctrineTypeMapping('TIMESTAMP', 'timestamp');
}

In your entity definition, MyEntity.orm.yml :

 dateCreation:
        type: timestamp
        nullable: false
        column: DATE_CREATION
        columnDefinition: TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'
    dateModification:
        type: timestamp
        nullable: false
        column: DATE_MODIFICATION
        columnDefinition: TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

And in the entity MyEntity.php, have a prePersist :

public function doPrePersist()
{
    $this->dateCreation = new \DateTime();
}

That's how I made it work :-) Thx all for your help !

Comments