Shane Harrelson wrote:
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.
I think you are looking at this situation wrong. The select is
effectively being done by the first insert anyway. That's how it
determines that there is a constraint violation, by finding an existing
record with the same key. This part of the insert operation is
effectively the same as a select rowid where pk=value. Thats what is
putting the value on the VDBE stack.
If that record already exists it is done, if not it insets a new record.
In either case the pages needed to satisfy the query are now in the page
cache, so a subsequent query will be very fast.
You always need to do the second insert. The only question is what value
are you going to insert. You are artificially creating two different
cases. You are trying to use the return from last_insert_rowid in one
case, or a value you pick of the VDBE stack with custom code in the
other case. When in fact you can always use the same value, the rowid
returned by a query for that primary key after the first insert.
If you wanted to model what the insert or ignore is doing more directly,
you could do something like this.
rowid = select rowid from Strings where value = 'foo';
if rowid is null then
insert into Strings values ('foo');
rowid = last_insert_rowid
end
insert into Objects values (rowid)
Where you do the select first. This wouldn't need to hack into the VDBE
stack like you do in your proposed code.
insert into Strings (value) VALUES ('foo')
if string insert result is SQLITE_OK
rowid = last_insert_rowid
else if result is SQLITE_CONSTRAINT
rowid = get_rowid_from_VDBE_stack
end if
insert into Objects (string_id) VALUES (rowid)
In any case I find it hard to believe that either of these will be
significantly faster than always executing the two simple inserts.
insert or ignore into Strings values (?);
insert into Objects values ((select rowid from Strings where value = ?));
This should be especially true if you are using prepared statements and
bind the string value. The execution time of VDBE code that implements
the explicit subquery is going to be swamped by the I/O time of the
database updates in either case. Note that you don't need to prepare and
execute a separate select query, it is part of the second prepared insert.
This is the gist of it using the C API.
sqlite3_stmt* bgn;
sqlite3_stmt* cmt;
sqlite3_stmt* inString;
sqlite3_stmt* inObject;
//initialization
void init_inserts()
{
sqlite3_prepare_v2(db, "begin transaction", -1, &bgn, 0);
sqlite3_prepare_v2(db, "commit transaction", -1, &cmt, 0);
sqlite3_prepare_v2(db, "insert or ignore into Strings values (?)", -1,
&inString, 0);
sqlite3_prepare_v2(db, "insert into Objects values ((select rowid from
Strings where value = ?))", -1, &inObject, 0);
}
// utility
void do_stmt(sqlite3_stmt* s)
{
sqlite3_step(s);
sqlite3_reset(s);
}
// execution
void myInsertFunction( const char* str)
{
do_stmt(bgn);
sqlite3_bind_text(inString, 1, str, -1, SQLITE_STATIC);
do_stmt(inString);
sqlite3_bind_text(inObject, 1, str, -1, SQLITE_STATIC);
do_stmt(inObject);
do_stmt(cmt);
}
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
