[SQLite Adapter] Triggers are dropped on table/column modification

[nebez]

When modifying tables or columns in SQLite, a lot of operations require a new temporary table to be made. The structure and the data is then moved from one table to another. Currently this does not bring the table triggers along with it.

There is a closed PR that demonstrates one way of fixing the issue: https://github.com/cakephp/phinx/pull/1284 However it was deemed that an alteration of the SQLite adapter is the right way to go about fixing it so it was closed. Creating an issue to highlight the problem and maybe somebody will want to re-write the adapter.

[chinpei215]

I think the current implementation will drop not only triggers but also indexes. We would need to follow the way shown in the official documentation: https://sqlite.org/lang_altertable.html#otheralter

And, I thought we could use 'the simpler procedure' (shown the above link), in some situations. But according to the official documentation, it would be available only in the renameColumn(). Though it seems to also work when we change datatypes, it is not documented explicitly. So I am not sure whether we can also use 'the simpler procedure' in the changeColumn() or not.

[InvisibleSmiley]

I ran into this with one migration that creates a table including a unique index, then another migration altering the table, and finally some UPSERT that complains:

PDOException: SQLSTATE[HY000]: General error: 1 ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint

Took me a while to understand that the actual issue was that the second migration resulted in the unique index being dropped. The error only occurred when I switched from Phinx 0.11 to 0.12.

Please adjust the issue summary to include "index" (and possibly also "view") so people can find it more easily.

Also please note that the documentation referred to above refers to "sqlite_schema" which does not exist in my version of SQLite (3.31.3) - it is called "sqlite_master" there.

[MasterOdin]

Could you include the migrations that is causing the error for you?

[InvisibleSmiley]

Sure. Simple example:

First

$this->table('foo')
            ->addColumn('indexcol', 'integer')
            ->addIndex(
                'indexcol',
                ['unique' => true]
            )
            ->create();

Second

 $this->table('foo')
            ->changeColumn('indexcol', 'integer', ['null' => false])
            ->update();

Index is present after first, dropped by second migration.

[MasterOdin]

Thanks for including the migration. Should be fixed for the next release. 😃