Disclaimer: I haven't yet tried this with the pre-release version of
sqlite-3.25.1, but I wanted to report it before the 24 hours of that
release notice expired.
In sqlite-3.25.0, the release notes say the following:
"Fix table rename feature so that it also updates references to the
renamed table in triggers and views."
This is mentioned again on https://www.sqlite.org/lang_altertable.html:
"Beginning with release 3.25.0 (2018-09-15), references to the table
within trigger bodies and view definitions are also renamed."
However, because of this change, the "completely general" procedure on
the ALTER TABLE page no longer works.
For example, the following works in sqlite-3.24.0 but not in sqlite-3.25.0:
-- Create a pair of tables related by a trigger.
create table t1(a integer not null primary key, b text not null);
create table t2(c integer primary key, d text);
-- Create a simple trigger on t1 that uses t2.
create trigger t1_trigger
after insert on t1
for each row
begin
insert into t2 (c, d) values (new.a, new.b);
end;
-- Demonstrate that it works.
select 'Before modifications' as comment;
insert into t1 values (1, 'aaa');
select * from t1;
select * from t2;
-- Follow the general procedure from
-- https://www.sqlite.org/lang_altertable.html
pragma foreign_keys = off;
begin transaction;
-- Change c and d to be "not null".
create table new_t2(c integer not null primary key, d text not null);
-- Copy data.
insert into new_t2 (c, d) select c, d from t2;
-- Drop old table.
drop table t2;
-- Rename table.
select 'About to rename new_t2 to t2' as comment;
alter table new_t2 rename to t2;
-- Check FKs.
pragma foreign_key_check;
-- Finish.
commit transaction;
pragma foreign_keys = on;
-- Demonstrate that it still works.
select 'After modifications' as comment;
insert into t1 values (2, 'bbb');
select * from t1;
select * from t2;
Output in sqlite-3.24.0:
$ ./sqlite3 :memory: < ~/external/sqlite3/trigger.sql
Before modifications
1|aaa
1|aaa
About to rename new_t2 to t2
After modifications
1|aaa
2|bbb
1|aaa
2|bbb
Output in sqlite-3.25.0:
$ ./sqlite3 :memory: < ~/external/sqlite3/trigger.sql
Before modifications
1|aaa
1|aaa
About to rename new_t2 to t2
Error: near line 34: error in trigger t1_trigger: no such table: main.t2
After modifications
Error: near line 45: no such table: main.t2
1|aaa
Error: near line 47: no such table: t2
Note that the failure occurs before the ALTER TABLE page's procedure
says to re-create any triggers. But even so, in this case, the triggers
are "on" t1, not t2, so in previous SQLite versions they did not need to
be re-created anyway.
It seems like the new ALTER TABLE behaviour was intended to be an
enhancement that makes it easier to make schema changes and that this is
an unexpected consequence of the change. Personally, I found the older
behaviour easier to work with: You re-created triggers on the table you
were recreating, but left any (symbolic) references to the recreated
table from other tables' triggers alone. Now, it looks like I will have
to drop all triggers in the database that reference the recreated table
and recreate them all, which seems to be the opposite of the intended
effect. This also means going back and rewriting previous database
schema patches that no longer run under sqlite-3.25.0.
Perhaps at least there should be a pragma or configuration flag to turn
this new behaviour off?
--
Brad Spencer
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
