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