On 1/31/07, Dennis Cote <[EMAIL PROTECTED]> wrote:
Shane Harrelson wrote:
> On 1/31/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>>
>> The official way to find the conflicting entry is to do a query.
>>
>> SELECT rowid FROM table WHERE uniquecolumn=?;
>>
>>
>
> Thank you for the reply. I assumed this was most likely the case, and
> as I said in my original email, I was hoping to avoid having to do a
> seperate select query for the sake of speed... especially since the
> value I needed was so tantalizing close in the VDBE struct.
>
Shane,
Why do you want the rowid of the conflicting row?
I only ask because I suspect there may be a better way to do what you
want to do.
Dennis Cote
I have two tables, an "Objects" table with a foreign key into a second
"Strings" table which is composed of unique values. It is a many to
one relationship, that is, several Objects may reference the same
String. When an Object is added, its associated String is added to
the Strings table. If the String already exists in the Strings
table, I'd like the new Object to reference the existing copy.
Currently, I've implemented it as so (leaving out error handling, etc.):
begin transaction
insert into Strings (value) VALUES ( 'foo')
if string insert result is SQLITE_OK
get rowid of last insert (sqlite3_last_insert_rowid)
else if result is SQLITE_CONSTRAINT
select rowid from Strings where value = 'foo'
end if
if rowid
insert into Objects (string_id) VALUES (rowid)
end if
if no error
commit transaction
else
rollback transaction
end if
With my dataset, there is about a 10% chance of the string being a
duplicate -- that is about 1 in 10 string inserts hit the UNIQUE
constraint violation.
I've tested "viloating" the internals of the VBDE and pulling the
rowid of the duplicate off the VBDE stack when the SQLITE_CONSTRAINT
result is returned and it is measurably (5-10%) faster then doing the
subsequent SELECT.
Any help or suggestions with how to do this better would be appreciated.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
