Phinx: SQLite error with addTimestamps()

[Jeckerson]

With current implementation of addTimestamps() method, it is impossible to add timestamp columns for already created table in SQLite environment. As there must be default value for column...

Migration 1:

$this->table('users')
    ->addColumn('name', 'string', ['limit' => 255])
    ->addColumn('email', 'string', ['limit' => 255])
    ->addColumn('password', 'char', ['limit' => 60])
    ->save();

Migration 2:

$this->table('users')->addTimestamps()->update();

Which generates:

PDOException: SQLSTATE[HY000]: General error: 1 Cannot add a column with non-constant default in /srv/vendor/robmorgan/phinx/src/Phinx/Db/Adapter/PdoAdapter.php:168

[PetrenkoBohdan]

@dereuromark I'm tried to fix this bug, but when I fix it, I have the next problem with rollback. And it hard for me, to understand how did rollback works. There is a solution what I found for addTimestamps()

        $this->addColumn($createdAt, 'timestamp', [
            'default' => 'NULL',
            'update' => '',
            'timezone' => $withTimezone,
        ])->changeColumn($createdAt, 'timestamp', [
            'default' => 'CURRENT_TIMESTAMP',
        ])->addColumn($updatedAt, 'timestamp', [
            'null' => true,
            'default' => null,
            'timezone' => $withTimezone,
        ]);

Default value can be set only, when table is already exists. So first we need to create created_at, and then update default value

[MasterOdin]

I am not sure I see how your solution would work.

Default value can be set only, when table is already exists.
This is not true for sqlite. A default value using a non-constant value (e.g. the return of function CURRENT_TIMESTAMP) can only be applied at table creation time (this post explains technical details of why).

I think really only a note to the docs should be added just stating that addTimestamps() can only be used at table creation time for SQLite, given there's no way to make the $createdAt column work sensibly with alters.

[MasterOdin]

This is now fixed for the upcoming 0.12 release.

Thanks @PetrenkoBohdan for your code there, got me to realize that the SQLite adapter was already doing something funny under the hood for change column commands, just had to adopt it to the add column commands. That funny bit being that instead of using SQLite's very weak ALTER TABLE command, it's better to create a temp table with the new desired functionality, and then copy everything over to it, and then rename the table, rather than trying to use ALTER TABLE.

@dereuromark close this?