On 2/2/07, Dennis Cote <[EMAIL PROTECTED]> wrote:
Shane Harrelson wrote: > On 2/2/07, A. Pagaltzis <[EMAIL PROTECTED]> wrote: >> * Nicolas Williams <[EMAIL PROTECTED]> [2007-02-01 00:10]: >> > Sounds like you should want to use INSERT OR IGNORE ... INTO >> > Strings and then SELECT the rowid of the string for use in >> > INSERTing INTO Object. >> >> That was my first thought as well. Does SQLite return the row's >> last_insert_id correctly in that case, though? >> >> Regards, >> -- >> Aristotle Pagaltzis // <http://plasmasturm.org/> >> > > If INSERT OR IGNORE ... INTO set the rowid of the "ignored" row such > that it could be retried with sqlite3_last_insert_rowid() , that would > work as well. I don't think it does though... I'm off to check. Shane,You shouldn't need a value from last_insert_rowid. Can't you just do this. begin transaction; insert or ignore into Strings (value) VALUES ('foo'); insert into Objects (string_id) VALUES ((select rowid from Strings where value = 'foo')); commit transaction; You want the rowid of the row that holds the given string, whether it was just inserted or not. If Strings has an index on value (which it will have if you declare value to be the primary key) then it will be used to quickly locate any pre-existing record during the first insert. After that, all the needed pages will be cached and the subquery in the second insert should execute very quickly. HTH Dennis Cote
Yes, I could do that, but the point of this thread was to find someway around doing the subsequent SELECT on a constraint collision during an INSERT into the strings table. I'm using the C API, and looking at the internals of the VBDE, the rowid responsible for the constraint collision is sitting right there at the top of the VBDE stack after the INSERT -- it just seems sort of a shame that there's not a way to retrieve it -- and that my only recourse is to do a SELECT. Regardless of how fast the SELECT is or how much is cached, it will be slower then simply getting the value of the rowid found during the INSERT. ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
