On Sat, 16 Nov 2013 23:59:35 +0100, Petite Abeille <petite.abei...@gmail.com> wrote:
> >On Nov 16, 2013, at 11:02 PM, Kees Nuyt <k.n...@zonnet.nl> wrote: > >> For the application, the merge would look like a single >> INSERT INTO merge_t statement. > >Hmmmm…. clever lateral thinking, but I doubt this will fly in practice :) > >Two main issues: > >(1) ‘or ignore’ is most likely inappropriate as unrelated > constraint violations will trigger it (e.g. null constraint). > Resulting in a no-op altogether as neither insert nor update > will do anything in practice. Leading to loss of data. I would mitigate that by only offering key columns (primary and unique, probably also foreign keys) to the insert, and rely on default constraints for the value of all other columns. Default values should not violate any check constraints. The update would take care of the other columns. The INSERT INTO merge_t would use OR ABORT or OR ROLLBACK to handle the case that the triggered update fails due to check constraints. >(2) Both insert & update statement will execute irrespectively > of the state of the data, doubling the workload. After the insert attempt, all related pages will be in the cache, so it would double the computation workload at most, not the I/O workload. > And, really, at this point (additional views, instead of triggers, > highjacking of DML semantics, silent loss of data), the cure might > seem worst than the disease. > > As far as I can tell, there is no way to reasonably emulate MERGE > in SQLite, short of resorting to some external programming logic. I agree my solution would only be a workaround, and the required DML is not really "elegant", but from the applications perspective it's not too bad. Perhaps better than having to maintain external programming logic. I'll implement this workaround some day, when I have a use case, and be punished by reality :) -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
