alter table, other alter category, fails in presence of trigger on 3.25.2

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

alter table, other alter category, fails in presence of trigger on 3.25.2

Thierry Henrio
Hello,

I want to report a bug, I checked https://www.sqlite.org/src/rptview?rn=8
and did not find the same?

Let schema be:

sqlite> .schema
CREATE TABLE albums (id int primary key, title text, score int);
CREATE TABLE rates (album_id references albums(id) on delete cascade,
comment text, score int);
CREATE TRIGGER test after insert on rates begin update albums set
score=new.score where id=new.album_id; end;

And I want to make albums.title not null.
This falls into https://www.sqlite.org/lang_altertable.html#otheralter.

sqlite> BEGIN;
sqlite> CREATE TABLE new_albums (id int primary key, title text not null,
score int);
sqlite> INSERT INTO new_albums (id, title, score) SELECT id, title, score
FROM albums;
sqlite> DROP TABLE albums;
sqlite> ALTER TABLE new_albums RENAME TO albums;
Error: error in trigger test: no such table: main.albums

Expected behavior is last alter is ok.

This is in 3.25.2

sqlite> select sqlite_version();
3.25.2

Cheers, Thierry
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: alter table, other alter category, fails in presence of trigger on 3.25.2

Keith Medcalf

Unrelated, but are you sure that you want the albums "id int primary key" and did not happen to misspell "integer" so that the declaration should be "id integer primary key".  In the former case, id is an integer that just happens to be unique (ie, "id int primary key" is the same as "id integer unique") and not an explicitly named alias for the rowid (which requires correct spelling of the phrase "integer primary key").

Second unrelated, do you not want an affinity for the album_id column in rates?  Should not you have declared it as "album_id integer references albums(id) on delete cascade"?

Third unrelated, do not forget to create an index on the foreign key (as in "CREATE INDEX idxRates_album_id on rates (album_id)" for example).

Fourth unrelated, do you want the title and comment_text to be case sensitive or should they have COLLATE NOCASE?

As to the issue with the updated table rename, you can either use a version of sqlite3 that does not have the alter table rename updates, or for version 3.25.2 use the pragma "PRAGMA legacy_alter_table=ON" to avoid using the new "change the table names in triggers etc" features added in 3.25.0 so that you can continue to use the old method of just "substituting tables".

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Thierry Henrio
>Sent: Wednesday, 3 October, 2018 16:43
>To: [hidden email]
>Subject: [sqlite] alter table, other alter category, fails in
>presence of trigger on 3.25.2
>
>Hello,
>
>I want to report a bug, I checked
>https://www.sqlite.org/src/rptview?rn=8
>and did not find the same?
>
>Let schema be:
>
>sqlite> .schema
>CREATE TABLE albums (id int primary key, title text, score int);
>CREATE TABLE rates (album_id references albums(id) on delete cascade,
>comment text, score int);
>CREATE TRIGGER test after insert on rates begin update albums set
>score=new.score where id=new.album_id; end;
>
>And I want to make albums.title not null.
>This falls into
>https://www.sqlite.org/lang_altertable.html#otheralter.
>
>sqlite> BEGIN;
>sqlite> CREATE TABLE new_albums (id int primary key, title text not
>null,
>score int);
>sqlite> INSERT INTO new_albums (id, title, score) SELECT id, title,
>score
>FROM albums;
>sqlite> DROP TABLE albums;
>sqlite> ALTER TABLE new_albums RENAME TO albums;
>Error: error in trigger test: no such table: main.albums
>
>Expected behavior is last alter is ok.
>
>This is in 3.25.2
>
>sqlite> select sqlite_version();
>3.25.2
>
>Cheers, Thierry
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: alter table, other alter category, fails in presence of trigger on 3.25.2

Thierry Henrio
Hello Keith,

On Thu, Oct 4, 2018 at 1:16 AM Keith Medcalf <[hidden email]> wrote:

>
> Unrelated, but are you sure that you want the albums "id int primary key"
> and did not happen to misspell "integer" so that the declaration should be
> "id integer primary key".  In the former case, id is an integer that just
> happens to be unique (ie, "id int primary key" is the same as "id integer
> unique") and not an explicitly named alias for the rowid (which requires
> correct spelling of the phrase "integer primary key").
>

I though "int" was the same as "integer" (
https://www.sqlite.org/datatype3.html).
Thanks.


> Second unrelated, do you not want an affinity for the album_id column in
> rates?  Should not you have declared it as "album_id integer references
> albums(id) on delete cascade"?
>

Correct.

Third unrelated, do not forget to create an index on the foreign key (as in
> "CREATE INDEX idxRates_album_id on rates (album_id)" for example).
>

Sure.

Fourth unrelated, do you want the title and comment_text to be case
> sensitive or should they have COLLATE NOCASE?
>

No.


> As to the issue with the updated table rename, you can either use a
> version of sqlite3 that does not have the alter table rename updates, or
> for version 3.25.2 use the pragma "PRAGMA legacy_alter_table=ON" to avoid
> using the new "change the table names in triggers etc" features added in
> 3.25.0 so that you can continue to use the old method of just "substituting
> tables".
>

Indeed, use the pragma, thanks! https://www.sqlite.org/pragma.html#toc

using pragma, the following script output 5, which is expected result.

drop table if exists albums;
drop table if exists rates;
--
create table albums (id integer primary key, title text, score int);
create table rates (album_id integer references albums(id) on delete
cascade, score int);
create trigger test after insert on rates begin update albums set
score=new.score where id=new.album_id; end;
--
insert into albums (id, title) values (1, 'Cheap Thrills');
--
begin;
pragma legacy_alter_table=ON;
drop table if exists new_albums;
create table new_albums (id int primary key, title text not null, score
int);
insert into new_albums (id, title, score) select id, title, score from
albums;
drop table albums;
alter table new_albums rename to albums;
pragma legacy_alter_table=OFF;
end;
--
insert into rates values (1, 5);
select score from albums;

When I comment the pragma, I have

Error: near line 16: error in trigger test: no such table: main.albums
Error: near line 20: no such table: main.albums
Error: near line 21: no such table: albums

It is a resolution for the problem I faced : add a constraint to colum of a
table referenced in a trigger.

Do you believe ?

a) https://www.sqlite.org/lang_altertable.html#otheralter could be updated.

b) rename A to B should not fail in the face of a trigger referencing B.

?
, Thierry
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: alter table, other alter category, fails in presence of trigger on 3.25.2

Hick Gunter
The phrase "integer primary key" is the "wingardium leviosa" of the sqlite world. It must be pronouced correctly, lest you end up with a buffalo on your chest.

-----Urspr√ľngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Thierry Henrio
Gesendet: Donnerstag, 04. Oktober 2018 11:14
An: [hidden email]
Betreff: [EXTERNAL] Re: [sqlite] alter table, other alter category, fails in presence of trigger on 3.25.2

Hello Keith,

On Thu, Oct 4, 2018 at 1:16 AM Keith Medcalf <[hidden email]> wrote:

>
> Unrelated, but are you sure that you want the albums "id int primary key"
> and did not happen to misspell "integer" so that the declaration
> should be "id integer primary key".  In the former case, id is an
> integer that just happens to be unique (ie, "id int primary key" is
> the same as "id integer
> unique") and not an explicitly named alias for the rowid (which
> requires correct spelling of the phrase "integer primary key").
>

I though "int" was the same as "integer" ( https://www.sqlite.org/datatype3.html).
Thanks.


> Second unrelated, do you not want an affinity for the album_id column
> in rates?  Should not you have declared it as "album_id integer
> references
> albums(id) on delete cascade"?
>

Correct.

Third unrelated, do not forget to create an index on the foreign key (as in
> "CREATE INDEX idxRates_album_id on rates (album_id)" for example).
>

Sure.

Fourth unrelated, do you want the title and comment_text to be case
> sensitive or should they have COLLATE NOCASE?
>

No.


> As to the issue with the updated table rename, you can either use a
> version of sqlite3 that does not have the alter table rename updates,
> or for version 3.25.2 use the pragma "PRAGMA legacy_alter_table=ON" to
> avoid using the new "change the table names in triggers etc" features
> added in
> 3.25.0 so that you can continue to use the old method of just
> "substituting tables".
>

Indeed, use the pragma, thanks! https://www.sqlite.org/pragma.html#toc

using pragma, the following script output 5, which is expected result.

drop table if exists albums;
drop table if exists rates;
--
create table albums (id integer primary key, title text, score int); create table rates (album_id integer references albums(id) on delete cascade, score int); create trigger test after insert on rates begin update albums set score=new.score where id=new.album_id; end;
--
insert into albums (id, title) values (1, 'Cheap Thrills');
--
begin;
pragma legacy_alter_table=ON;
drop table if exists new_albums;
create table new_albums (id int primary key, title text not null, score int); insert into new_albums (id, title, score) select id, title, score from albums; drop table albums; alter table new_albums rename to albums; pragma legacy_alter_table=OFF; end;
--
insert into rates values (1, 5);
select score from albums;

When I comment the pragma, I have

Error: near line 16: error in trigger test: no such table: main.albums
Error: near line 20: no such table: main.albums
Error: near line 21: no such table: albums

It is a resolution for the problem I faced : add a constraint to colum of a table referenced in a trigger.

Do you believe ?

a) https://www.sqlite.org/lang_altertable.html#otheralter could be updated.

b) rename A to B should not fail in the face of a trigger referencing B.

?
, Thierry
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users