[sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT
Hi, I have read that INSERT OR REPLACE is equivalent to a DELETE followed by an INSERT. I came across a case where that is not true. Set up a test case $ rm -f asdf.sqlite && sqlite3 asdf.sqlite sqlite> .mode columns sqlite> .headers on sqlite> PRAGMA foreign_keys=OFF; sqlite> CREATE TABLE users ( ...> id INT UNSIGNED NOT NULL PRIMARY KEY, ...> username TEXT NOT NULL ...> ); sqlite> CREATE TABLE user_extras ( ...> user_id INT UNSIGNED NOT NULL PRIMARY KEY, ...> other INT NULL DEFAULT NULL, ...> FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE ...> ); sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf'); sqlite> INSERT INTO user_extras (user_id, other) VALUES (1, 33); sqlite> SELECT * FROM users; id username -- -- 1 asdf sqlite> SELECT * FROM user_extras; user_id other -- -- 1 33 Let's try a delete followed by an insert. sqlite> DELETE FROM users WHERE id = 1; sqlite> SELECT * FROM users; sqlite> SELECT * FROM user_extras; user_id other -- -- 1 33 sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf'); sqlite> SELECT * FROM users; id username -- -- 1 asdf sqlite> SELECT * FROM user_extras; user_id other -- -- 1 33 Notice that the value `user_extras.other` is still 33. That's good. sqlite> INSERT OR REPLACE INTO users (id, username) VALUES (1, 'asdf'); sqlite> SELECT * FROM users; id username -- -- 1 asdf sqlite> SELECT * FROM user_extras; user_id other -- -- 1 33 Still 33. That's good. Based on the above, it looks like `INSERT OR REPLACE` is equivalent to a `DELETE` followed by an `INSERT`. If we add a trigger, things change... sqlite> CREATE TRIGGER users_after_insert AFTER INSERT ON users ...> BEGIN ...> INSERT OR IGNORE INTO user_extras (user_id) VALUES (new.id); ...> END; A `DELETE` followed by an `INSERT` still works as before. sqlite> DELETE FROM users WHERE id = 1; sqlite> SELECT * FROM users; sqlite> SELECT * FROM user_extras; user_id other -- -- 1 33 sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf'); sqlite> SELECT * FROM users; id username -- -- 1 asdf sqlite> SELECT * FROM user_extras; user_id other -- -- 1 33 See above that the value of `user_extras.other` survived the `DELETE` followed by the `INSERT`. When we use `INSERT OR REPLACE` the `user_extras.other` value is cleared out. sqlite> INSERT OR REPLACE INTO users (id, username) VALUES (1, 'asdf'); sqlite> SELECT * FROM users; id username -- -- 1 asdf sqlite> SELECT * FROM user_extras; user_id other -- -- 1 Because of the trigger, `INSERT OR REPLACE` is no longer equivalent to `DELETE` followed by `INSERT`. Peter ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT
The parent is still being deleted and inserted, however, the trigger now uses the REPLACE conflict resolution method rather than the IGNORE resolution method and that resolution method causes the deletion and insertion of a new child record. --- 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- >boun...@mailinglists.sqlite.org] On Behalf Of Peter Michaux >Sent: Monday, 12 March, 2018 21:09 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE >followed by INSERT > >Hi, > >I have read that INSERT OR REPLACE is equivalent to a DELETE followed >by an >INSERT. I came across a case where that is not true. > >Set up a test case > >$ rm -f asdf.sqlite && sqlite3 asdf.sqlite > >sqlite> .mode columns >sqlite> .headers on >sqlite> PRAGMA foreign_keys=OFF; > >sqlite> CREATE TABLE users ( > ...> id INT UNSIGNED NOT NULL PRIMARY KEY, > ...> username TEXT NOT NULL > ...> ); > >sqlite> CREATE TABLE user_extras ( > ...> user_id INT UNSIGNED NOT NULL PRIMARY KEY, > ...> other INT NULL DEFAULT NULL, > ...> FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE >CASCADE ON DELETE CASCADE > ...> ); > >sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf'); > >sqlite> INSERT INTO user_extras (user_id, other) VALUES (1, 33); > >sqlite> SELECT * FROM users; >id username >-- -- >1 asdf > >sqlite> SELECT * FROM user_extras; >user_id other >-- -- >1 33 > > >Let's try a delete followed by an insert. > >sqlite> DELETE FROM users WHERE id = 1; > >sqlite> SELECT * FROM users; > >sqlite> SELECT * FROM user_extras; >user_id other >-- -- >1 33 > >sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf'); > >sqlite> SELECT * FROM users; >id username >-- -- >1 asdf > >sqlite> SELECT * FROM user_extras; >user_id other >-- -- >1 33 > >Notice that the value `user_extras.other` is still 33. That's good. > >sqlite> INSERT OR REPLACE INTO users (id, username) VALUES (1, >'asdf'); > >sqlite> SELECT * FROM users; >id username >-- -- >1 asdf > >sqlite> SELECT * FROM user_extras; >user_id other >-- -- >1 33 > >Still 33. That's good. > >Based on the above, it looks like `INSERT OR REPLACE` is equivalent >to a >`DELETE` followed by an `INSERT`. > >If we add a trigger, things change... > >sqlite> CREATE TRIGGER users_after_insert AFTER INSERT ON users > ...> BEGIN > ...> INSERT OR IGNORE INTO user_extras (user_id) VALUES >(new.id); > ...> END; > >A `DELETE` followed by an `INSERT` still works as before. > >sqlite> DELETE FROM users WHERE id = 1; > >sqlite> SELECT * FROM users; > >sqlite> SELECT * FROM user_extras; >user_id other >-- -- >1 33 > >sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf'); > >sqlite> SELECT * FROM users; >id username >-- -- >1 asdf > >sqlite> SELECT * FROM user_extras; >user_id other >-- -- >1 33 > >See above that the value of `user_extras.other` survived the `DELETE` >followed by the `INSERT`. > >When we use `INSERT OR REPLACE` the `user_extras.other` value is >cleared >out. > >sqlite> INSERT OR REPLACE INTO users (id, username) VALUES (1, >'asdf'); > >sqlite> SELECT * FROM users; >id username >-- -- >1 asdf > >sqlite> SELECT * FROM user_extras; >user_id other >-- -- >1 > >Because of the trigger, `INSERT OR REPLACE` is no longer equivalent >to >`DELETE` followed by `INSERT`. > > >Peter >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT
On this page, 7th paragrph: https://www.sqlite.org/lang_createtrigger.html See that: An ON CONFLICT clause may be specified as part of an UPDATE or INSERT action within the body of the trigger. However if an ON CONFLICT clause is specified as part of the statement causing the trigger to fire, then conflict handling policy of the outer statement is used instead. --- 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- >boun...@mailinglists.sqlite.org] On Behalf Of Peter Michaux >Sent: Monday, 12 March, 2018 21:09 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE >followed by INSERT > >Hi, > >I have read that INSERT OR REPLACE is equivalent to a DELETE followed >by an >INSERT. I came across a case where that is not true. > >Set up a test case > >$ rm -f asdf.sqlite && sqlite3 asdf.sqlite > >sqlite> .mode columns >sqlite> .headers on >sqlite> PRAGMA foreign_keys=OFF; > >sqlite> CREATE TABLE users ( > ...> id INT UNSIGNED NOT NULL PRIMARY KEY, > ...> username TEXT NOT NULL > ...> ); > >sqlite> CREATE TABLE user_extras ( > ...> user_id INT UNSIGNED NOT NULL PRIMARY KEY, > ...> other INT NULL DEFAULT NULL, > ...> FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE >CASCADE ON DELETE CASCADE > ...> ); > >sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf'); > >sqlite> INSERT INTO user_extras (user_id, other) VALUES (1, 33); > >sqlite> SELECT * FROM users; >id username >-- -- >1 asdf > >sqlite> SELECT * FROM user_extras; >user_id other >-- -- >1 33 > > >Let's try a delete followed by an insert. > >sqlite> DELETE FROM users WHERE id = 1; > >sqlite> SELECT * FROM users; > >sqlite> SELECT * FROM user_extras; >user_id other >-- -- >1 33 > >sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf'); > >sqlite> SELECT * FROM users; >id username >-- -- >1 asdf > >sqlite> SELECT * FROM user_extras; >user_id other >-- -- >1 33 > >Notice that the value `user_extras.other` is still 33. That's good. > >sqlite> INSERT OR REPLACE INTO users (id, username) VALUES (1, >'asdf'); > >sqlite> SELECT * FROM users; >id username >-- -- >1 asdf > >sqlite> SELECT * FROM user_extras; >user_id other >-- -- >1 33 > >Still 33. That's good. > >Based on the above, it looks like `INSERT OR REPLACE` is equivalent >to a >`DELETE` followed by an `INSERT`. > >If we add a trigger, things change... > >sqlite> CREATE TRIGGER users_after_insert AFTER INSERT ON users > ...> BEGIN > ...> INSERT OR IGNORE INTO user_extras (user_id) VALUES >(new.id); > ...> END; > >A `DELETE` followed by an `INSERT` still works as before. > >sqlite> DELETE FROM users WHERE id = 1; > >sqlite> SELECT * FROM users; > >sqlite> SELECT * FROM user_extras; >user_id other >-- -- >1 33 > >sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf'); > >sqlite> SELECT * FROM users; >id username >-- -- >1 asdf > >sqlite> SELECT * FROM user_extras; >user_id other >-- -- >1 33 > >See above that the value of `user_extras.other` survived the `DELETE` >followed by the `INSERT`. > >When we use `INSERT OR REPLACE` the `user_extras.other` value is >cleared >out. > >sqlite> INSERT OR REPLACE INTO users (id, username) VALUES (1, >'asdf'); > >sqlite> SELECT * FROM users; >id username >-- -- >1 asdf > >sqlite> SELECT * FROM user_extras; >user_id other >-- -- >1 > >Because of the trigger, `INSERT OR REPLACE` is no longer equivalent >to >`DELETE` followed by `INSERT`. > > >Peter >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT
Thank you for the information, Keith. It comes as a surprise to me that the conflict resolution clause of the statement that causes a trigger to fire can override an explicit conflict resolution clause in the body of a trigger. But, as you pointed out, it is documented. How can I write the trigger to not use a conflict resolution clause? I'm trying a bunch of different things but with no luck. Something like the following is possible? CREATE TRIGGER users_after_insert AFTER INSERT ON users BEGIN CASE WHEN (SELECT count(*) FROM user_extras WHERE user_id = new.id)) = 0 THEN INSERT INTO user_extras (user_id) VALUES (new.id) END; END; Thanks. Peter On Mon, Mar 12, 2018 at 8:50 PM, Keith Medcalf wrote: > > On this page, 7th paragrph: > > https://www.sqlite.org/lang_createtrigger.html > > See that: > > An ON CONFLICT clause may be specified as part of an UPDATE or INSERT > action within the body of the trigger. However if an ON CONFLICT clause is > specified as part of the statement causing the trigger to fire, then > conflict handling policy of the outer statement is used instead. > > --- > 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- > >boun...@mailinglists.sqlite.org] On Behalf Of Peter Michaux > >Sent: Monday, 12 March, 2018 21:09 > >To: sqlite-users@mailinglists.sqlite.org > >Subject: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE > >followed by INSERT > > > >Hi, > > > >I have read that INSERT OR REPLACE is equivalent to a DELETE followed > >by an > >INSERT. I came across a case where that is not true. > > > >Set up a test case > > > >$ rm -f asdf.sqlite && sqlite3 asdf.sqlite > > > >sqlite> .mode columns > >sqlite> .headers on > >sqlite> PRAGMA foreign_keys=OFF; > > > >sqlite> CREATE TABLE users ( > > ...> id INT UNSIGNED NOT NULL PRIMARY KEY, > > ...> username TEXT NOT NULL > > ...> ); > > > >sqlite> CREATE TABLE user_extras ( > > ...> user_id INT UNSIGNED NOT NULL PRIMARY KEY, > > ...> other INT NULL DEFAULT NULL, > > ...> FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE > >CASCADE ON DELETE CASCADE > > ...> ); > > > >sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf'); > > > >sqlite> INSERT INTO user_extras (user_id, other) VALUES (1, 33); > > > >sqlite> SELECT * FROM users; > >id username > >-- -- > >1 asdf > > > >sqlite> SELECT * FROM user_extras; > >user_id other > >-- -- > >1 33 > > > > > >Let's try a delete followed by an insert. > > > >sqlite> DELETE FROM users WHERE id = 1; > > > >sqlite> SELECT * FROM users; > > > >sqlite> SELECT * FROM user_extras; > >user_id other > >-- -- > >1 33 > > > >sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf'); > > > >sqlite> SELECT * FROM users; > >id username > >-- -- > >1 asdf > > > >sqlite> SELECT * FROM user_extras; > >user_id other > >-- -- > >1 33 > > > >Notice that the value `user_extras.other` is still 33. That's good. > > > >sqlite> INSERT OR REPLACE INTO users (id, username) VALUES (1, > >'asdf'); > > > >sqlite> SELECT * FROM users; > >id username > >-- -- > >1 asdf > > > >sqlite> SELECT * FROM user_extras; > >user_id other > >-- -- > >1 33 > > > >Still 33. That's good. > > > >Based on the above, it looks like `INSERT OR REPLACE` is equivalent > >to a > >`DELETE` followed by an `INSERT`. > > > >If we add a trigger, things change... > > > >sqlite> CREATE TRIGGER users_after_insert AFTER INSERT ON users > > ...> BEGIN > > ...> INSERT OR IGNORE INTO user_extras (user_id) VALUES > >(new.id); > > ...> END; > > > >A `DELETE` followed by an `INSERT` still works as before. > > > >
Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT
create trigger users_after_insert after insert on users when not exists ( select 1 from user_extras where user_id = new.id ) begin insert into user_extras (user_id) values (new.id); end; -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Peter Michaux Sent: Friday, March 16, 2018 2:42 PM To: SQLite mailing list Subject: Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT Thank you for the information, Keith. It comes as a surprise to me that the conflict resolution clause of the statement that causes a trigger to fire can override an explicit conflict resolution clause in the body of a trigger. But, as you pointed out, it is documented. How can I write the trigger to not use a conflict resolution clause? I'm trying a bunch of different things but with no luck. Something like the following is possible? CREATE TRIGGER users_after_insert AFTER INSERT ON users BEGIN CASE WHEN (SELECT count(*) FROM user_extras WHERE user_id = new.id)) = 0 THEN INSERT INTO user_extras (user_id) VALUES (new.id) END; END; Thanks. Peter On Mon, Mar 12, 2018 at 8:50 PM, Keith Medcalf wrote: > > On this page, 7th paragrph: > > https://www.sqlite.org/lang_createtrigger.html > > See that: > > An ON CONFLICT clause may be specified as part of an UPDATE or INSERT > action within the body of the trigger. However if an ON CONFLICT clause is > specified as part of the statement causing the trigger to fire, then > conflict handling policy of the outer statement is used instead. > > --- > 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- > >boun...@mailinglists.sqlite.org] On Behalf Of Peter Michaux > >Sent: Monday, 12 March, 2018 21:09 > >To: sqlite-users@mailinglists.sqlite.org > >Subject: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE > >followed by INSERT > > > >Hi, > > > >I have read that INSERT OR REPLACE is equivalent to a DELETE followed > >by an > >INSERT. I came across a case where that is not true. > > > >Set up a test case > > > >$ rm -f asdf.sqlite && sqlite3 asdf.sqlite > > > >sqlite> .mode columns > >sqlite> .headers on > >sqlite> PRAGMA foreign_keys=OFF; > > > >sqlite> CREATE TABLE users ( > > ...> id INT UNSIGNED NOT NULL PRIMARY KEY, > > ...> username TEXT NOT NULL > > ...> ); > > > >sqlite> CREATE TABLE user_extras ( > > ...> user_id INT UNSIGNED NOT NULL PRIMARY KEY, > > ...> other INT NULL DEFAULT NULL, > > ...> FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE > >CASCADE ON DELETE CASCADE > > ...> ); > > > >sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf'); > > > >sqlite> INSERT INTO user_extras (user_id, other) VALUES (1, 33); > > > >sqlite> SELECT * FROM users; > >id username > >-- -- > >1 asdf > > > >sqlite> SELECT * FROM user_extras; > >user_id other > >-- -- > >1 33 > > > > > >Let's try a delete followed by an insert. > > > >sqlite> DELETE FROM users WHERE id = 1; > > > >sqlite> SELECT * FROM users; > > > >sqlite> SELECT * FROM user_extras; > >user_id other > >-- -- > >1 33 > > > >sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf'); > > > >sqlite> SELECT * FROM users; > >id username > >-- -- > >1 asdf > > > >sqlite> SELECT * FROM user_extras; > >user_id other > >-- -- > >1 33 > > > >Notice that the value `user_extras.other` is still 33. That's good. > > > >sqlite> INSERT OR REPLACE INTO users (id, username) VALUES (1, > >'asdf'); > > > >sqlite> SELECT * FROM users; > >id username > >-- -- > >1 asdf > > > >sqlite> SELECT * FROM user_extras; > >user_id other > >-- -- > >1 33 > > > >Still 33. That's good. > > > >Based on the above, it looks like `INSERT OR R
Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT
It is kind of hard to write a specific trigger since your "purpose" is confusing. You have defined some referential integrity and are then ignoring it. If your goal is to update the parent, then why not use the statement designed to do that (UPDATE) ... ? --- 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- >boun...@mailinglists.sqlite.org] On Behalf Of Peter Michaux >Sent: Friday, 16 March, 2018 12:42 >To: SQLite mailing list >Subject: Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE >followed by INSERT > >Thank you for the information, Keith. > >It comes as a surprise to me that the conflict resolution clause of >the >statement that causes a trigger to fire can override an explicit >conflict >resolution clause in the body of a trigger. But, as you pointed out, >it is >documented. > >How can I write the trigger to not use a conflict resolution clause? >I'm >trying a bunch of different things but with no luck. Something like >the >following is possible? > >CREATE TRIGGER users_after_insert AFTER INSERT ON users >BEGIN >CASE WHEN (SELECT count(*) FROM user_extras WHERE user_id = >new.id)) = >0 THEN >INSERT INTO user_extras (user_id) VALUES (new.id) >END; >END; > >Thanks. > >Peter > > > >On Mon, Mar 12, 2018 at 8:50 PM, Keith Medcalf >wrote: > >> >> On this page, 7th paragrph: >> >> https://www.sqlite.org/lang_createtrigger.html >> >> See that: >> >> An ON CONFLICT clause may be specified as part of an UPDATE or >INSERT >> action within the body of the trigger. However if an ON CONFLICT >clause is >> specified as part of the statement causing the trigger to fire, >then >> conflict handling policy of the outer statement is used instead. >> >> --- >> 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- >> >boun...@mailinglists.sqlite.org] On Behalf Of Peter Michaux >> >Sent: Monday, 12 March, 2018 21:09 >> >To: sqlite-users@mailinglists.sqlite.org >> >Subject: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE >> >followed by INSERT >> > >> >Hi, >> > >> >I have read that INSERT OR REPLACE is equivalent to a DELETE >followed >> >by an >> >INSERT. I came across a case where that is not true. >> > >> >Set up a test case >> > >> >$ rm -f asdf.sqlite && sqlite3 asdf.sqlite >> > >> >sqlite> .mode columns >> >sqlite> .headers on >> >sqlite> PRAGMA foreign_keys=OFF; >> > >> >sqlite> CREATE TABLE users ( >> > ...> id INT UNSIGNED NOT NULL PRIMARY KEY, >> > ...> username TEXT NOT NULL >> > ...> ); >> > >> >sqlite> CREATE TABLE user_extras ( >> > ...> user_id INT UNSIGNED NOT NULL PRIMARY KEY, >> > ...> other INT NULL DEFAULT NULL, >> > ...> FOREIGN KEY (user_id) REFERENCES users(id) ON >UPDATE >> >CASCADE ON DELETE CASCADE >> > ...> ); >> > >> >sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf'); >> > >> >sqlite> INSERT INTO user_extras (user_id, other) VALUES (1, >33); >> > >> >sqlite> SELECT * FROM users; >> >id username >> >-- -- >> >1 asdf >> > >> >sqlite> SELECT * FROM user_extras; >> >user_id other >> >-- -- >> >1 33 >> > >> > >> >Let's try a delete followed by an insert. >> > >> >sqlite> DELETE FROM users WHERE id = 1; >> > >> >sqlite> SELECT * FROM users; >> > >> >sqlite> SELECT * FROM user_extras; >> >user_id other >> >-- -- >> >1 33 >> > >> >sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf'); >> > >> >sqlite> SELECT * FROM users; >> >id username >> >-- -- >> >1 asdf >> > >> >sqlite> SELECT * FROM user_
Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT
You are right that my purpose does seem kind of confusing. What I really want is UPSERT to avoid doing an UPDATE, checking if the number of rows affected is zero, then doing an INSERT. The lack of UPSERT leads to a lot more application code using the UPDATE/INSERT combination. UPSERT doesn't exist in SQLite so I was trying to work around that by using INSERT OR REPLACE which is not the same thing. I can see from another recent thread that some others also think that UPSERT would be a valuable addition to SQLite. Peter On Fri, Mar 16, 2018 at 2:07 PM, Keith Medcalf wrote: > > It is kind of hard to write a specific trigger since your "purpose" is > confusing. You have defined some referential integrity and are then > ignoring it. If your goal is to update the parent, then why not use the > statement designed to do that (UPDATE) ... ? > > --- > 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- > >boun...@mailinglists.sqlite.org] On Behalf Of Peter Michaux > >Sent: Friday, 16 March, 2018 12:42 > >To: SQLite mailing list > >Subject: Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE > >followed by INSERT > > > >Thank you for the information, Keith. > > > >It comes as a surprise to me that the conflict resolution clause of > >the > >statement that causes a trigger to fire can override an explicit > >conflict > >resolution clause in the body of a trigger. But, as you pointed out, > >it is > >documented. > > > >How can I write the trigger to not use a conflict resolution clause? > >I'm > >trying a bunch of different things but with no luck. Something like > >the > >following is possible? > > > >CREATE TRIGGER users_after_insert AFTER INSERT ON users > >BEGIN > >CASE WHEN (SELECT count(*) FROM user_extras WHERE user_id = > >new.id)) = > >0 THEN > >INSERT INTO user_extras (user_id) VALUES (new.id) > >END; > >END; > > > >Thanks. > > > >Peter > > > > > > > >On Mon, Mar 12, 2018 at 8:50 PM, Keith Medcalf > >wrote: > > > >> > >> On this page, 7th paragrph: > >> > >> https://www.sqlite.org/lang_createtrigger.html > >> > >> See that: > >> > >> An ON CONFLICT clause may be specified as part of an UPDATE or > >INSERT > >> action within the body of the trigger. However if an ON CONFLICT > >clause is > >> specified as part of the statement causing the trigger to fire, > >then > >> conflict handling policy of the outer statement is used instead. > >> > >> --- > >> 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- > >> >boun...@mailinglists.sqlite.org] On Behalf Of Peter Michaux > >> >Sent: Monday, 12 March, 2018 21:09 > >> >To: sqlite-users@mailinglists.sqlite.org > >> >Subject: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE > >> >followed by INSERT > >> > > >> >Hi, > >> > > >> >I have read that INSERT OR REPLACE is equivalent to a DELETE > >followed > >> >by an > >> >INSERT. I came across a case where that is not true. > >> > > >> >Set up a test case > >> > > >> >$ rm -f asdf.sqlite && sqlite3 asdf.sqlite > >> > > >> >sqlite> .mode columns > >> >sqlite> .headers on > >> >sqlite> PRAGMA foreign_keys=OFF; > >> > > >> >sqlite> CREATE TABLE users ( > >> > ...> id INT UNSIGNED NOT NULL PRIMARY KEY, > >> > ...> username TEXT NOT NULL > >> > ...> ); > >> > > >> >sqlite> CREATE TABLE user_extras ( > >> > ...> user_id INT UNSIGNED NOT NULL PRIMARY KEY, > >> > ...> other INT NULL DEFAULT NULL, > >> > ...> FOREIGN KEY (user_id) REFERENCES users(id) ON > >UPDATE > >> >CASCADE ON DELETE CASCADE > >> > ...> ); > >> > > >> >sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf'); > >> > > >> >sqlite> IN
Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT
On Wed, 21 Mar 2018 14:05:07 -0700, Peter Michaux wrote: > You are right that my purpose does seem kind of confusing. > > What I really want is UPSERT to avoid doing an UPDATE, checking if the > number of rows affected is zero, then doing an INSERT. The lack of UPSERT > leads to a lot more application code using the UPDATE/INSERT combination. > UPSERT doesn't exist in SQLite so I was trying to work around that by using > INSERT OR REPLACE which is not the same thing. I can see from another > recent thread that some others also think that UPSERT would be a valuable > addition to SQLite. I fail to see the problem in BEGIN; INSERT OR IGNORE ... ; UPDATE ; COMMIT; Simple code, no need to test number of affected rows, and pretty fast because the relevant pages will be in cache. Or use an updatable view with an INSTEAD OF INSERT trigger. I did notice that attempts to define a proper UPSERT syntax opened a can of worms by itself because it (also) has to provide two colum lists, one for a full INSERT if the row with that PK doesn't exist, and another one for the columns to be updated when the row already exists. So, I don't see a big advantage in UPSERT. My humble two cents, -- Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT
I think there are a couple main offenders with > BEGIN; > INSERT OR IGNORE ... ; > UPDATE ; > COMMIT; The first is that it is bulky. If this is in the application code then it has to be repeated for each desired UPSERT and it has to be repeated in the code of each application that uses the database. The second is that it seems so inefficient in the case of a new row being inserted. The row is inserted and then immediately updated. Why do both operations when only one is needed? Is it possible to write a stored procedure that checks a result of the INSERT OR IGNORE and only attempts the UPDATE if the row already existed? That would at least move the bulky code out of the application and into the database. Also it seems it would be more efficient. Thanks. Peter Peter On Wed, Mar 21, 2018 at 6:15 PM, Kees Nuyt wrote: > On Wed, 21 Mar 2018 14:05:07 -0700, Peter Michaux > wrote: > > > You are right that my purpose does seem kind of confusing. > > > > What I really want is UPSERT to avoid doing an UPDATE, checking if the > > number of rows affected is zero, then doing an INSERT. The lack of UPSERT > > leads to a lot more application code using the UPDATE/INSERT combination. > > UPSERT doesn't exist in SQLite so I was trying to work around that by > using > > INSERT OR REPLACE which is not the same thing. I can see from another > > recent thread that some others also think that UPSERT would be a valuable > > addition to SQLite. > > I fail to see the problem in > BEGIN; > INSERT OR IGNORE ... ; > UPDATE ; > COMMIT; > Simple code, no need to test number of affected rows, and pretty > fast because the relevant pages will be in cache. > > Or use an updatable view with an INSTEAD OF INSERT trigger. > > I did notice that attempts to define a proper UPSERT syntax > opened a can of worms by itself because it (also) has to provide > two colum lists, one for a full INSERT if the row with that PK > doesn't exist, and another one for the columns to be updated > when the row already exists. So, I don't see a big advantage in > UPSERT. > > My humble two cents, > > -- > Regards, > Kees Nuyt > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT
Thursday, March 22, 2018, 7:18:08 PM, Peter Michaux wrote: > I think there are a couple main offenders with >> BEGIN; >> INSERT OR IGNORE ... ; >> UPDATE ; >> COMMIT; > The first is that it is bulky. If this is in the application code then it > has to be repeated for each desired UPSERT and it has to be repeated in the > code of each application that uses the database. From what I can remember, most of the suggested formats for an UPSERT command (except perhaps R. Smith's "NOT"/"KEEP" idea) involve two lists of fields/values, so would be similarly "bulky" as separate INSERT and UPDATE commands, and need similar amounts of application code. > The second is that it seems so inefficient in the case of a new row being > inserted. The row is inserted and then immediately updated. Why do both > operations when only one is needed? There are at least two alternatives (hopefully I've given correct attribution): o "INSERT OR IGNORE" the "key" fields (ensures they now exist) followed by an UPDATE for the remaining fields (Simon Slavin). o "INSERT OR IGNORE" all values; if sqlite3_changes() indicates nothing changed (because the record is already present), perform the UPDATE (Olivier Mascia). > Is it possible to write a stored procedure that checks a result of the > INSERT OR IGNORE and only attempts the UPDATE if the row already existed? > That would at least move the bulky code out of the application and into the > database. Also it seems it would be more efficient. In some cases, e.g. where the data to be UPSERTed is in a (possibly transient) table you can use a TRIGGER to perform the UPDATE part. (At the risk of blowing my own trumpet, see an answer of mine on StackOverflow: https://stackoverflow.com/a/22481731/2096401). Regards, Graham ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT
Yes. Here is a stored procedure written in Python that implements a "stored procedure" (that is, it is a procedure and it is indeed stored) that does an "upsert" operation. You pass it the db connection object, the name of the table, a dictionary of the PrimaryKey fields, and a dictionary of the data fields. It does the "upsert" and returns the associated rowid of the record ... and it only needs to be written once. Of course, some source languages are not so nice and require far more complicated "stored procedures", however, you only have to design and write them once. from __future__ import print_function def upsert(db=None, table=None, pk=None, data=None, debug=False): if not (db and table and pk): raise ValueError('must specify db, table, pk') if not all(pk.values()): raise ValueError('PrimaryKeys must not be null') alldata = dict() alldata.update(pk) sqlUpdate = '' if data: alldata.update(data) sqlUpdate = ''.join(['UPDATE ', table, ' SET (', ', '.join(data.keys()), ') = (:', ', :'.join(data.keys()), ') WHERE (', ', '.join(pk.keys()), ') == (:', ', :'.join(pk.keys()), ');' ]) sqlInsert = ''.join(['INSERT OR IGNORE INTO ', table, ' (', ', '.join(alldata.keys()), ') values (:', ', :'.join(alldata.keys()), ');' ]) sqlSelect = ''.join(['SELECT id ', 'FROM ', table, ' WHERE (', ', '.join(pk.keys()), ') == (:', ', :'.join(pk.keys()), ');' ]) if debug: if sqlUpdate: print(sqlUpdate) print(sqlInsert) print(sqlSelect) cr = db.cursor() cr.execute('SAVEPOINT %s%s' % ('UpSert', table)) if data: cr.execute(sqlUpdate, alldata) cr.execute(sqlInsert, alldata) id = None try: for row in cr.execute(sqlSelect): id = row[0] except: pass cr.execute('RELEASE %s%s;' % ('UpSert', table)) cr.close() return id --- 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- >boun...@mailinglists.sqlite.org] On Behalf Of Peter Michaux >Sent: Thursday, 22 March, 2018 13:18 >To: SQLite mailing list >Subject: Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE >followed by INSERT > >I think there are a couple main offenders with > >> BEGIN; >> INSERT OR IGNORE ... ; >> UPDATE ; >> COMMIT; > >The first is that it is bulky. If this is in the application code >then it >has to be repeated for each desired UPSERT and it has to be repeated >in the >code of each application that uses the database. > >The second is that it seems so inefficient in the case of a new row >being >inserted. The row is inserted and then immediately updated. Why do >both >operations when only one is needed? > >Is it possible to write a stored procedure that checks a result of >the >INSERT OR IGNORE and only attempts the UPDATE if the row already >existed? >That would at least move the bulky code out of the application and >into the >database. Also it seems it would be more efficient. > >Thanks. > >Peter > > >Peter > > > >On Wed, Mar 21, 2018 at 6:15 PM, Kees Nuyt wrote: > >> On Wed, 21 Mar 2018 14:05:07 -0700, Peter Michaux >> wrote: >> >> > You are right that my purpose does seem kind of confusing. >> > >> > What I really want is UPSERT to avoid doing an UPDATE, checking >if the >> > number of rows affected is zero, then doing an INSERT. The lack >of UPSERT >> > leads to a lot more application code using the UPDATE/INSERT >combination. >> > UPSERT doesn't exist in SQLite so I was trying to work around >that by >> using >> > INSERT O
Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT
Peter, is "INSTEAD OF" trigger not available on your version of SQLite? https://sqlite.org/lang_createtrigger.html#instead_of_trigger CREATE VIEW mytable_UPSERT AS SELECT * FROM mytable; CREATE TRIGGER mytable_UPSERT INSTEAD OF INSERT ON mytable_UPSERT BEGIN -->INSERT OR IGNORE ... ; -->UPDATE ; END; INSERT INTO mytable_UPSERT Peter On Thu, Mar 22, 2018 at 12:18 PM, Peter Michaux wrote: > I think there are a couple main offenders with > > > BEGIN; > > INSERT OR IGNORE ... ; > > UPDATE ; > > COMMIT; > > The first is that it is bulky. If this is in the application code then it > has to be repeated for each desired UPSERT and it has to be repeated in the > code of each application that uses the database. > > The second is that it seems so inefficient in the case of a new row being > inserted. The row is inserted and then immediately updated. Why do both > operations when only one is needed? > > Is it possible to write a stored procedure that checks a result of the > INSERT OR IGNORE and only attempts the UPDATE if the row already existed? > That would at least move the bulky code out of the application and into the > database. Also it seems it would be more efficient. > > Thanks. > > Peter > > > Peter > > > > On Wed, Mar 21, 2018 at 6:15 PM, Kees Nuyt wrote: > > > On Wed, 21 Mar 2018 14:05:07 -0700, Peter Michaux > > wrote: > > > > > You are right that my purpose does seem kind of confusing. > > > > > > What I really want is UPSERT to avoid doing an UPDATE, checking if the > > > number of rows affected is zero, then doing an INSERT. The lack of > UPSERT > > > leads to a lot more application code using the UPDATE/INSERT > combination. > > > UPSERT doesn't exist in SQLite so I was trying to work around that by > > using > > > INSERT OR REPLACE which is not the same thing. I can see from another > > > recent thread that some others also think that UPSERT would be a > valuable > > > addition to SQLite. > > > > I fail to see the problem in > > BEGIN; > > INSERT OR IGNORE ... ; > > UPDATE ; > > COMMIT; > > Simple code, no need to test number of affected rows, and pretty > > fast because the relevant pages will be in cache. > > > > Or use an updatable view with an INSTEAD OF INSERT trigger. > > > > I did notice that attempts to define a proper UPSERT syntax > > opened a can of worms by itself because it (also) has to provide > > two colum lists, one for a full INSERT if the row with that PK > > doesn't exist, and another one for the columns to be updated > > when the row already exists. So, I don't see a big advantage in > > UPSERT. > > > > My humble two cents, > > > > -- > > Regards, > > Kees Nuyt > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users