On Friday, April 6, 2012 7:31:09 AM UTC-7, Simon Arnaud wrote: > > Hi > > In Sequel 3.32 notes, there is : > Alter table emulation now works correctly on SQLite when foreign keys > reference the table being altered. The emulation requires a > renaming/deleting the existing table and creating a new table, which > can break foreign key references. Sequel now disables the foreign key > PRAGMA when altering tables, so SQLite won't track the table renames > and break the foreign key relationships. > > The problem is that the pragma is called inside the transaction created > by Migrator::checked_transaction. And SQLite doc clearly says this > pragma does nothing inside a transaction. > > === > PRAGMA foreign_keys; > PRAGMA foreign_keys = boolean; > > Query, set, or clear the enforcement of foreign key constraints. > > This pragma is a no-op within a transaction; foreign key constraint > enforcement may only be enabled or disabled when there is no pending > BEGIN or SAVEPOINT. > === > > I added 'no_transaction' after 'Sequel.migration do', and the pragma > then works, however, the migration is no longer atomic. > > Would 'DB.foreign_keys = false' before 'Sequel.migration do' be a > better workaround ? > > Do you think it would be possible to have both correct behaviour and > atomic migration automatically ? >
It's possible, but then you can end up in situations where your foreign key references are invalid (e.g. migrations that delete referenced rows). A post 3.34 change was made so that the migrator does not use transactions by default on SQLite. Correct behavior is better than atomic behavior, IMO. Disabling foreign key references all together (instead of just when emulating alter_table), can lead to incorrect behavior. Really, someone should get SQLite to support more ALTER TABLE commands. Emulating ALTER TABLE when it isn't supported will always have corner cases. Thanks, Jeremy -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To view this discussion on the web visit https://groups.google.com/d/msg/sequel-talk/-/nREwJGA4x_kJ. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sequel-talk?hl=en.
