Correct way for INSERT OR REPLACE and COMMIT

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Correct way for INSERT OR REPLACE and COMMIT

Cecil Westerhof-5
I have the following Tcl code:
    set rollback "
    INSERT OR REPLACE INTO pipRollback
    (pipType, package, old, new)
    VALUES
    (:pip, :package, :old, :new)"

    db eval {BEGIN TRANSACTION}
    foreach verbose ${packagesVerbose} {
        .
        .
        .
        if {${doAppend}} {
            append packages "${package} "
            db eval ${rollback}
        }
    }
    db eval {COMMIT TRANSACTION}

I suppose it is the correct way, but is there something I should change?

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

Re: Correct way for INSERT OR REPLACE and COMMIT

R Smith-2
On 2018/07/19 8:35 AM, Cecil Westerhof wrote:

> I have the following Tcl code:
>      set rollback "
>      INSERT OR REPLACE INTO pipRollback
>      (pipType, package, old, new)
>      VALUES
>      (:pip, :package, :old, :new)"
>
>      db eval {BEGIN TRANSACTION}
>      foreach verbose ${packagesVerbose} {
>          .
>          .
>          .
>          if {${doAppend}} {
>              append packages "${package} "
>              db eval ${rollback}
>          }
>      }
>      db eval {COMMIT TRANSACTION}
>
> I suppose it is the correct way, but is there something I should change?

It looks perfect as far as replacing things go. The only note I would
add is to be aware that if you insert a row that already has an existing
Key value (I will assume here your Primary Key is "package") , then the
original row will be deleted first (firing any possible ON DELETE
triggers and possibly Foreign Key constraints[1]) and then be
re-inserted (firing any ON INSERT, but not ON UPDATE) with the new values.

A better option, to fix all this, is the new upsert feature which
doesn't delete-and-re-insert, but in stead takes the logic of "Insert if
needed, else update" firing the correct triggers/constraints for the
required action.

The correct format is given here:
https://sqlite.org/lang_UPSERT.html

And your query will need to be re-written like this (again, assuming
"package" is the primary key, but it will work for any other PK field or
combination of fields):

     INSERT INTO pipRollback (pipType, package, old, new)
       VALUES (:pip, :package, :old, :new)
     ON CONFLICT (package) DO UPDATE
       SET (pipType, old, new) = (:pip, :old, :new)


Lastly, I thought I would add an example in case of multiple Key fields,
if only to show how trivial the difference is. So assuming your Primary
Key (or perhaps another UNIQUE constraint) was declared on multiple
columns (pipType, package), the same query would now look like this:

     INSERT INTO pipRollback (pipType, package, old, new)
       VALUES (:pip, :package, :old, :new)
     ON CONFLICT (pipType, package) DO UPDATE
       SET (old, new) = (:old, :new)

Note1 - The upsert feature is only available since SQLite version
3.24.0, so you have to be up-to-date.
Note2 - Upsert doesn't currently work for Virtual Tables.



Cheers!
Ryan

[1] - I'm not actually 100% sure the ON DELETE Foreign Key constraints
will fire for REPLACE, or that the ON UPDATE won't fire - have not had
the use-case, perhaps someone else might confirm.


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

Re: Correct way for INSERT OR REPLACE and COMMIT

Cecil Westerhof-5
2018-07-19 11:37 GMT+02:00 R Smith <[hidden email]>:

> On 2018/07/19 8:35 AM, Cecil Westerhof wrote:
>
>> I have the following Tcl code:
>>      set rollback "
>>      INSERT OR REPLACE INTO pipRollback
>>      (pipType, package, old, new)
>>      VALUES
>>      (:pip, :package, :old, :new)"
>>
>>      db eval {BEGIN TRANSACTION}
>>      foreach verbose ${packagesVerbose} {
>>          .
>>          .
>>          .
>>          if {${doAppend}} {
>>              append packages "${package} "
>>              db eval ${rollback}
>>          }
>>      }
>>      db eval {COMMIT TRANSACTION}
>>
>> I suppose it is the correct way, but is there something I should change?
>>
>
> It looks perfect as far as replacing things go. The only note I would add
> is to be aware that if you insert a row that already has an existing Key
> value (I will assume here your Primary Key is "package") , then the
> original row will be deleted first (firing any possible ON DELETE triggers
> and possibly Foreign Key constraints[1]) and then be re-inserted (firing
> any ON INSERT, but not ON UPDATE) with the new values.
>

​I should have been more clear: the primary key is: pipType, package.
The field pipType contains the type of pip: pip2, or pip3.
Package contains the package, for example ​youtube-dl.

A better option, to fix all this, is the new upsert feature which doesn't
> delete-and-re-insert, but in stead takes the logic of "Insert if needed,
> else update" firing the correct triggers/constraints for the required
> action.
>
> The correct format is given here:
> https://sqlite.org/lang_UPSERT.html


​I had seen that, but it works from 3.24.0, my version in Tcl is: 3.16.2.
That is why I use INSERT OR REPLACE.



> And your query will need to be re-written like this (again, assuming
> "package" is the primary key, but it will work for any other PK field or
> combination of fields):
>
>     INSERT INTO pipRollback (pipType, package, old, new)
>       VALUES (:pip, :package, :old, :new)
>     ON CONFLICT (package) DO UPDATE
>       SET (pipType, old, new) = (:pip, :old, :new)
>
>
> Lastly, I thought I would add an example in case of multiple Key fields,
> if only to show how trivial the difference is. So assuming your Primary Key
> (or perhaps another UNIQUE constraint) was declared on multiple columns
> (pipType, package), the same query would now look like this:
>
>     INSERT INTO pipRollback (pipType, package, old, new)
>       VALUES (:pip, :package, :old, :new)
>     ON CONFLICT (pipType, package) DO UPDATE
>       SET (old, new) = (:old, :new)
>
> Note1 - The upsert feature is only available since SQLite version 3.24.0,
> so you have to be up-to-date.
> Note2 - Upsert doesn't currently work for Virtual Tables.
>

​Thanks. I will look how easy/difficult it is to upgrade to 3.24.0.​


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