CODEWITHSUNDEEP
phpmysqlsymfonydoctrine-ormtimestamp
Ulti
Ulti

Reputation: 608

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;
    }
}

Upvotes: 2

Views: 4976

Answers (4)

Michał Tomczuk
Michał Tomczuk

Reputation: 611

Not sure if my answer still helps, but maybe it will help someone in the future. The way we did it is only thru mappings, no extra types (timestamp is allowed by Doctrine). There's the solution:

dateAdd:
     type: datetime
     column: date_add
     columnDefinition: TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
     options:
         default: CURRENT_TIMESTAMP
editDate:
     type: datetime
     column: date_edit
     columnDefinition: TIMESTAMP on update CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
     options:
         default: CURRENT_TIMESTAMP

The column definition makes sure it will be created properly, and options settings make sure that when you run doctrine:schema:validate, you will see it is in sync (it is used for cross-checking the defaults with current definition).

Same, if you'd like to make those columns nullable, you have to change columnDefinition and add nullable: false.

Hope that helps!

Upvotes: 2

Ulti
Ulti

Reputation: 608

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 !

Upvotes: 0

ediraphael
ediraphael

Reputation: 21

I tried that and it worked for me :

dateCreation:
        type: datetime
        version: true
        column: DATE_CREATION

with 

    "doctrine/orm": "~2.2,>=2.2.3",
    "doctrine/doctrine-bundle": "~1.2",
    "doctrine/doctrine-fixtures-bundle": "~2.2"
    "doctrine/migrations": "dev-master",
    "doctrine/doctrine-migrations-bundle": "dev-master"

Upvotes: 2

ediraphael
ediraphael

Reputation: 21

You can try to remove the quote around CURRENT_TIMESTAMP. The sql query is executed well without the quotes .

ALTER TABLE MY_TABLE CHANGE DATE_CREATION DATE_CREATION timestamp [NULL|NOT NULL] DEFAULT CURRENT_TIMESTAMP

Upvotes: -2

Related Questions