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
