2018-07-19 11:37 GMT+02:00 R Smith <ryansmit...@gmail.com>:

> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to