You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
CREATETABLEusers (
id TEXTNOT NULLPRIMARY KEY,
username TEXTNOT NULL UNIQUE,
updated_at integer default (unixepoch()) not null
) WITHOUT ROWID;
CREATETRIGGERupdate_users_updated_at AFTER UPDATE OF username ON users FOR EACH ROW
BEGINUPDATE users SET updated_at = unixepoch() WHERE id =NEW.id;
END;
migrate diff generates the following migration:
-- Create "users" tableCREATETABLE `users` (`id`textNOT NULL, `username`textNOT NULL, `updated_at`integerNOT NULL DEFAULT (unixepoch()), PRIMARY KEY (`id`)) WITHOUT ROWID;
-- Create index "users_username" to table: "users"CREATEUNIQUE INDEX `users_username` ON`users` (`username`);
-- Create trigger "update_users_updated_at"CREATETRIGGER `update_users_updated_at` AFTER UPDATE OF `username`ON`users` FOR EACH ROW BEGINUPDATE users SET updated_at = unixepoch() WHERE id =NEW.id;
END;
Next, we rename a column on the users table:
CREATETABLEusers (
user_id TEXTNOT NULLPRIMARY KEY,
username TEXTNOT NULL UNIQUE,
updated_at integer default (unixepoch()) not null
) WITHOUT ROWID;
CREATETRIGGERupdate_users_updated_at AFTER UPDATE OF username ON users FOR EACH ROW
BEGINUPDATE users SET updated_at = unixepoch() WHERE id =NEW.id;
END;
migrate diff generates the following incorrect migration:
-- Disable the enforcement of foreign-keys constraints
PRAGMA foreign_keys = off;
-- Create "new_users" tableCREATETABLE `new_users` (`user_id`textNOT NULL, `username`textNOT NULL, `updated_at`integerNOT NULL DEFAULT (unixepoch()), PRIMARY KEY (`user_id`)) WITHOUT ROWID;
-- Copy rows from old table "users" to new temporary table "new_users"INSERT INTO`new_users` (`username`, `updated_at`) SELECT`username`, `updated_at`FROM`users`;
-- Drop "users" table after copying rowsDROPTABLE`users`;
-- Rename temporary table "new_users" to "users"ALTERTABLE`new_users` RENAME TO `users`;
-- Create index "users_username" to table: "users"CREATEUNIQUE INDEX `users_username` ON`users` (`username`);
-- Enable back the enforcement of foreign-keys constraints
PRAGMA foreign_keys =on;
The SQLite manual says:
Triggers are automatically dropped when the table that they are associated with (the table-name table) is dropped. However if the trigger actions reference other tables, the trigger is not dropped or modified if those other tables are dropped or modified.
So, in this particular situation, the trigger should be re-created after the table has been renamed. Also, I noticed that if I rename username to uname in both the table definition and trigger, it works as expected.
The text was updated successfully, but these errors were encountered:
We start with this schema:
migrate diff
generates the following migration:Next, we rename a column on the users table:
migrate diff
generates the following incorrect migration:The SQLite manual says:
https://www.sqlite.org/lang_createtrigger.html#description
So, in this particular situation, the trigger should be re-created after the table has been renamed. Also, I noticed that if I rename
username
touname
in both the table definition and trigger, it works as expected.The text was updated successfully, but these errors were encountered: