Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

[petern]

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

Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

[Keith Medcalf]

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 <k.n...@zonnet.nl> wrote:
>
>> On Wed, 21 Mar 2018 14:05:07 -0700, Peter Michaux
>> <petermich...@gmail.com> 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 ... ;

Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

[Graham Holden]

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

[Peter Michaux]

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

[Kees Nuyt]

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

[Peter Michaux]

​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 <kmedc...@dessus.com> 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 <kmedc...@dessus.com>
> >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, 'as

Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

[Keith Medcalf]

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 <kmedc...@dessus.com>
>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;
>

Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

[David Raymond]

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 <kmedc...@dessus.com> 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 equivalen

Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

[Peter Michaux]

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 <kmedc...@dessus.com> 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.
> >
> >sqlite> DELETE FROM 

Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

[Keith Medcalf]

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

[Keith Medcalf]

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

[sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

[Peter Michaux]

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