Re: [sqlite] plans for completion of INSERT OR REPLACE INTO?

[Jean-Denis Muys]

On 7/7/09 8:47 , "Nicolas Williams"  wrote:

> On Tue, Jul 07, 2009 at 12:17:36AM +0100, Simon Slavin wrote:
[...] 
> This is way off-topic now, but, to be fair, RFC1855 is not a standard,
> it is an Informational status RFC -- it provides information.
> 

It possibly is never off-topic to educate and to tirelessly explain why
top-posting is evil.

Somebody on the this list has a nice signature going a bit like:

A> Because we read top to bottom
Q> Why is top posting evil?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] plans for completion of INSERT OR REPLACE INTO?

[Nicolas Williams]

On Tue, Jul 07, 2009 at 12:17:36AM +0100, Simon Slavin wrote:
> He didn't make up the rule.  Nor did I.  It's part of the standard for  
> mailing lists and usenet:
> 
> 

This is way off-topic now, but, to be fair, RFC1855 is not a standard,
it is an Informational status RFC -- it provides information.

That said, netiquette is a very good thing :)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] plans for completion of INSERT OR REPLACE INTO?

[Simon Slavin]

On 6 Jul 2009, at 11:59pm, James Gregurich wrote:

> How's this...
>
> you have a pretty low threshold for "obnoxious." Frankly, I lack the
> desire and energy needed to keep up with the list of rules people make
> up. read the email or don't.

He didn't make up the rule.  Nor did I.  It's part of the standard for  
mailing lists and usenet:



Section 3.1.1 (in part)


"- If you are sending a reply to a message or a posting be sure you  
summarize the original at the top of the message, or include just  
enough text of the original to give a context. This will make
sure readers understand when they start to read your response. Since  
NetNews, especially, is proliferated by distributing the postings from  
one host to another, it is possible to see a response to a message  
before seeing the original. Giving context helps everyone. But do not  
include the entire original!"

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] plans for completion of INSERT OR REPLACE INTO?

[James Gregurich]

On Jul 6, 2009, at 3:14 PM, Nicolas Williams wrote:

> On Mon, Jul 06, 2009 at 02:49:07PM -0700, James Gregurich wrote:
>> 1) Why on earth would you want to scroll all the way to the bottom of
>> a long email to get the response simply for the sake of "We read
>> English top to bottom."
>
> Any quoted context must be read before the reply or else is not
> necessary and should not have been quoted.  Thus top posting (as
> the practice is known) is obnoxious because it causes the reader to
> read the reply, read the quoted text, and then re-read the reply to
> understand it in its proper context, and if the quoted text was not
> necessary in order to understand your reply (as in this particular
> case), then it just wastes bandwidth and storage.
>
>> 2) This is going to be a challenge for me because I'm not writing a
>> fixed DB with a known schema.  I'm writing a higher-level data store
>> wrapper using SQLITE as the backing. The schema is configured at
>> runtime.
>
> I agree.  ISTM that SQLite3 ought to call either call DELETE triggers
> for all rows deleted by INSERT OR REPLACE, or UPDATE triggers for the
> row that had the same PRIMARY KEY and DELETE triggers for any other
> deleted rows.
>
> (To me INSERT OR REPLACE can equally be seen as deleting all  
> conflicting
> rows, then inserting a replacement row, or else as updating the row  
> that
> conflicts with the new row's PRIMARY KEY and deleting rows that  
> conflict
> on other unique indexes.  If the INSERT OR REPLACE has many input rows
> then this gets murkier in that which rows are updated or deleted may
> become non-deterministic, I think, so deleting all affected rows seems
> like the better solution.)
>
>> 3)  I found the following comment on the mysql dev site (relevant
>> since "replace into" is inspired by mysql). They apparently fire the
>> triggers as expected. It seems to me that your comments  
>> overcomplicate
>> the requirements. If you are going to delete a row, call the delete
>> triggersthat is all that is needed as best I can tell. However, I
>> do admit that I am not well educated on SQL db engines. I'm figuring
>> all this out as I go along. Feel free to tell me how absurdly wrong I
>> am.
>
> INSERT OR REPLACE is not a feature of the standard, so anything  
> goes...
>
> Nico
> -- 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



How's this...

you have a pretty low threshold for "obnoxious." Frankly, I lack the  
desire and energy needed to keep up with the list of rules people make  
up. read the email or don't. it doesn't matter to me. I don't make  
rules designed to get other people to adhere to my personal  
preferencesand it annoys me when I get lectured by others who  
expect me to follow some unwritten code of conduct just to suit their  
personal tastes. The adjective I'd use to counter "obnoxious" is  
"uptight."





___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] plans for completion of INSERT OR REPLACE INTO?

[Nicolas Williams]

On Mon, Jul 06, 2009 at 02:49:07PM -0700, James Gregurich wrote:
> 1) Why on earth would you want to scroll all the way to the bottom of  
> a long email to get the response simply for the sake of "We read  
> English top to bottom."

Any quoted context must be read before the reply or else is not
necessary and should not have been quoted.  Thus top posting (as
the practice is known) is obnoxious because it causes the reader to
read the reply, read the quoted text, and then re-read the reply to
understand it in its proper context, and if the quoted text was not
necessary in order to understand your reply (as in this particular
case), then it just wastes bandwidth and storage.

> 2) This is going to be a challenge for me because I'm not writing a  
> fixed DB with a known schema.  I'm writing a higher-level data store  
> wrapper using SQLITE as the backing. The schema is configured at  
> runtime.

I agree.  ISTM that SQLite3 ought to call either call DELETE triggers
for all rows deleted by INSERT OR REPLACE, or UPDATE triggers for the
row that had the same PRIMARY KEY and DELETE triggers for any other
deleted rows.

(To me INSERT OR REPLACE can equally be seen as deleting all conflicting
rows, then inserting a replacement row, or else as updating the row that
conflicts with the new row's PRIMARY KEY and deleting rows that conflict
on other unique indexes.  If the INSERT OR REPLACE has many input rows
then this gets murkier in that which rows are updated or deleted may
become non-deterministic, I think, so deleting all affected rows seems
like the better solution.)

> 3)  I found the following comment on the mysql dev site (relevant  
> since "replace into" is inspired by mysql). They apparently fire the  
> triggers as expected. It seems to me that your comments overcomplicate  
> the requirements. If you are going to delete a row, call the delete  
> triggersthat is all that is needed as best I can tell. However, I  
> do admit that I am not well educated on SQL db engines. I'm figuring  
> all this out as I go along. Feel free to tell me how absurdly wrong I  
> am.

INSERT OR REPLACE is not a feature of the standard, so anything goes...

Nico
-- 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] plans for completion of INSERT OR REPLACE INTO?

[James Gregurich]

1) Why on earth would you want to scroll all the way to the bottom of  
a long email to get the response simply for the sake of "We read  
English top to bottom."

2) This is going to be a challenge for me because I'm not writing a  
fixed DB with a known schema.  I'm writing a higher-level data store  
wrapper using SQLITE as the backing. The schema is configured at  
runtime.

3)  I found the following comment on the mysql dev site (relevant  
since "replace into" is inspired by mysql). They apparently fire the  
triggers as expected. It seems to me that your comments overcomplicate  
the requirements. If you are going to delete a row, call the delete  
triggersthat is all that is needed as best I can tell. However, I  
do admit that I am not well educated on SQL db engines. I'm figuring  
all this out as I go along. Feel free to tell me how absurdly wrong I  
am.


http://dev.mysql.com/doc/refman/5.0/en/replace.html

> MySQL uses the following algorithm for REPLACE (and LOAD DATA ...  
> REPLACE):
>
> Try to insert the new row into the table
>
> While the insertion fails because a duplicate-key error occurs for a  
> primary key or unique index:
>
> Delete from the table the conflicting row that has the duplicate key  
> value
>
> Try again to insert the new row into the table
>


and in the comments...


> If you are using REPLACE INTO... triggers are fired in this order  
> (if delete of duplcate key is used):
> - before insert
> - before delete
> - after delete
> - after insert





On Jul 6, 2009, at 1:15 PM, Simon Slavin wrote:

> Please quote previous text above your response to it.  We read English
> top to bottom.
>
> On 6 Jul 2009, at 8:22pm, James Gregurich wrote:
>
>> On Jul 6, 2009, at 3:53 AM, Simon Slavin wrote:
>>
>>> It should not call DELETE triggers since it never deletes.  It  
>>> should
>>> call either INSERT triggers or UPDATE triggers depending on which  
>>> one
>>> it decides to do.
>>>
>>> In the meantime, you can do the same thing yourself: instead of
>>> calling INSERT OR REPLACE, test to see which one would happen then
>>> call either INSERT or UPDATE.  The triggers on both of those
>>> combinations will work correctly.
>
>> so you are suggesting that I put an INSERT in a C loop checking for a
>> constraint violation failure.
>
> You have pointed out an error I made.  I was thinking that REPLACE
> meant that only one existing row could be replaced.  This is wrong:
> the new row can replace any number of existing rows.  Thank you for
> spotting my error.  The documentation points it out, not quite as
> clearly, in the REPLACE section of
>
> http://www.sqlite.org/lang_conflict.html
>
> So any proper trigger structure would have to call a combination of
> all three types of triggers: INSERT, DELETE and UPDATE.  I can't think
> of a good way to manage this properly.  And that may be why INSERT OR
> REPLACE itself doesn't use triggers correctly: it's too complicated to
> work out which of the existing rows is being REPLACED.  And you can't
> call DELETE triggers instead, because they're intended to stop
> something being deleted, and it might be okay to DELETE this thing if
> you're immediately going to INSERT another row that satisfies the
> requirement.
>
> I can only suggest that you handle the constraints testing yourself,
> in your code.  You will know, from the design of your database,
> whether your new row should be considered a replacement for an
> existing one.  You're going to have to replace your INSERT OR REPLACE
> with your own check for other constraints, and then deciding in your
> own code what commands to execute.  I don't think it will be possible
> to write code to do this properly for an arbitrary database.
>
>>>  Or do the INSERT, allow it to fail if
>>> it will, then do the UPDATE.
>
> This previous suggestion of mine won't work at all, given that there's
> no way to tell which of the existing records you think you're  
> updating.
>
> I conclude that INSERT OR REPLACE isn't compatible with triggers, or
> that triggers have to support FOR EACH TRANSACTION as well as ROW and
> STATEMENT, or that INSERT OR REPLACE has to treat a primary key
> conflict differently to some other kind of conflict.  There are
> drawbacks to all three of these.  I can't think of a way to do it that
> suits SQLite's small/neat/obvious design criteria.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] plans for completion of INSERT OR REPLACE INTO?

[Simon Slavin]

Please quote previous text above your response to it.  We read English  
top to bottom.

On 6 Jul 2009, at 8:22pm, James Gregurich wrote:

> On Jul 6, 2009, at 3:53 AM, Simon Slavin wrote:
>
>> It should not call DELETE triggers since it never deletes.  It should
>> call either INSERT triggers or UPDATE triggers depending on which one
>> it decides to do.
>>
>> In the meantime, you can do the same thing yourself: instead of
>> calling INSERT OR REPLACE, test to see which one would happen then
>> call either INSERT or UPDATE.  The triggers on both of those
>> combinations will work correctly.

> so you are suggesting that I put an INSERT in a C loop checking for a
> constraint violation failure.

You have pointed out an error I made.  I was thinking that REPLACE  
meant that only one existing row could be replaced.  This is wrong:  
the new row can replace any number of existing rows.  Thank you for  
spotting my error.  The documentation points it out, not quite as  
clearly, in the REPLACE section of

http://www.sqlite.org/lang_conflict.html

So any proper trigger structure would have to call a combination of  
all three types of triggers: INSERT, DELETE and UPDATE.  I can't think  
of a good way to manage this properly.  And that may be why INSERT OR  
REPLACE itself doesn't use triggers correctly: it's too complicated to  
work out which of the existing rows is being REPLACED.  And you can't  
call DELETE triggers instead, because they're intended to stop  
something being deleted, and it might be okay to DELETE this thing if  
you're immediately going to INSERT another row that satisfies the  
requirement.

I can only suggest that you handle the constraints testing yourself,  
in your code.  You will know, from the design of your database,  
whether your new row should be considered a replacement for an  
existing one.  You're going to have to replace your INSERT OR REPLACE  
with your own check for other constraints, and then deciding in your  
own code what commands to execute.  I don't think it will be possible  
to write code to do this properly for an arbitrary database.

>>   Or do the INSERT, allow it to fail if
>> it will, then do the UPDATE.

This previous suggestion of mine won't work at all, given that there's  
no way to tell which of the existing records you think you're updating.

I conclude that INSERT OR REPLACE isn't compatible with triggers, or  
that triggers have to support FOR EACH TRANSACTION as well as ROW and  
STATEMENT, or that INSERT OR REPLACE has to treat a primary key  
conflict differently to some other kind of conflict.  There are  
drawbacks to all three of these.  I can't think of a way to do it that  
suits SQLite's small/neat/obvious design criteria.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] plans for completion of INSERT OR REPLACE INTO?

[James Gregurich]

so you are suggesting that I put an INSERT in a C loop checking for a  
constraint violation failure. if I get one, I use errmsg to get the  
"column  is not unique" message and extract . Then, I issue a  
DELETE to clear out rows that match the value of . is that correct?





On Jul 6, 2009, at 3:53 AM, Simon Slavin wrote:

> (Sorry, hit 'Send' before I meant to.)
>
> On 6 Jul 2009, at 6:34am, James Gregurich wrote:
>
>> a question for the sqlite developers.
>>
>> The inability of "INSERT OR REPLACE" to maintain referential  
>> integrity
>> leaves me with no mechanism to implement a feature in my project that
>> I was intending to provide.  Are there any plans to add in the
>> functionality for "INSERT OR REPLACE" to call delete triggers so that
>> referential integrity can be maintained?
>
> It should not call DELETE triggers since it never deletes.  It should
> call either INSERT triggers or UPDATE triggers depending on which one
> it decides to do.
>
> In the meantime, you can do the same thing yourself: instead of
> calling INSERT OR REPLACE, test to see which one would happen then
> call either INSERT or UPDATE.  Or do the INSERT, allow it to fail if
> it will, then do the UPDATE.  The triggers on both of those
> combinations will work correctly.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] plans for completion of INSERT OR REPLACE INTO?

[Simon Slavin]

(Sorry, hit 'Send' before I meant to.)

On 6 Jul 2009, at 6:34am, James Gregurich wrote:

> a question for the sqlite developers.
>
> The inability of "INSERT OR REPLACE" to maintain referential integrity
> leaves me with no mechanism to implement a feature in my project that
> I was intending to provide.  Are there any plans to add in the
> functionality for "INSERT OR REPLACE" to call delete triggers so that
> referential integrity can be maintained?

It should not call DELETE triggers since it never deletes.  It should  
call either INSERT triggers or UPDATE triggers depending on which one  
it decides to do.

In the meantime, you can do the same thing yourself: instead of  
calling INSERT OR REPLACE, test to see which one would happen then  
call either INSERT or UPDATE.  Or do the INSERT, allow it to fail if  
it will, then do the UPDATE.  The triggers on both of those  
combinations will work correctly.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] plans for completion of INSERT OR REPLACE INTO?

[Simon Slavin]

On 6 Jul 2009, at 6:34am, James Gregurich wrote:

> The inability of "INSERT OR REPLACE" to maintain referential integrity
> leaves me with no mechanism to implement a feature in my project that
> I was intending to provide.  Are there any plans to add in the
> functionality for "INSERT OR REPLACE" to call delete triggers so that
> referential integrity can be maintained?

It should not call DELETE triggers.  It should call either INSERT or  
UPDATE triggers depending on which one it decides to do.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] plans for completion of INSERT OR REPLACE INTO?

[James Gregurich]

a question for the sqlite developers.

The inability of "INSERT OR REPLACE" to maintain referential integrity  
leaves me with no mechanism to implement a feature in my project that  
I was intending to provide.  Are there any plans to add in the  
functionality for "INSERT OR REPLACE" to call delete triggers so that  
referential integrity can be maintained?

thanks,
James
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users