2

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;
    }
}
Ulti
  • 588
  • 5
  • 18
  • Why do you not want to use DATETIME type? http://stackoverflow.com/questions/409286/datetime-vs-timestamp – Michael Sivolobov May 05 '15 at 14:37
  • I have already read this page, but I can't, it's a standard in my job, unfortunately.. I know, this is stupid. – Ulti May 05 '15 at 14:41
  • You can try to define your property as Doctrine's `datetime` type and maybe it will work because `datetime` is interpreted as `TIMESTAMP` in PostgreSQL. – Michael Sivolobov May 05 '15 at 14:46
  • Thx for your tip, I effectively tried that, but they are stored as `Datetime` field, and I need `Timestamp` fields. I use MySQL actually. – Ulti May 05 '15 at 14:48

4 Answers4

2

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"
ediraphael
  • 21
  • 4
  • Thx for that tip, I didn't know it. It could work in my case, but I need at least 2 TIMESTAMP fields, and unfortunately, with this, it's impossible due to some DBMS reasons (I use MySQL 5.1.63, this limitation has been removed in MySQL 5.6.5). So I'll keep my first workaround :-/ But nice try ! – Ulti May 06 '15 at 08:16
2

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!

Michał Tomczuk
  • 531
  • 3
  • 10
0

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 !

Ulti
  • 588
  • 5
  • 18
-2

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
Undo
  • 25,519
  • 37
  • 106
  • 129
ediraphael
  • 21
  • 4