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
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
