"Shane Harrelson" <[EMAIL PROTECTED]> wrote:
>
> 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.
>
What you are doing is the most efficient way that I can think of
right off hand. If "violating" the internals is something you want
to do that's fine - just remember that it is likely to break in
irrepaiable ways in some future point release. No tears.
Notice that an sqlite3_last_constraint_rowid() function doesn't really
work because an insert might fail due to multiple constraint violations
all on different rows. In your schema, perhaps, there can be no more
than one constraint violated at a time, but it is easy enough to
construct a schema where multiple rows can violate a constraint, so the
sqlite3_last_constraint_rowid() idea does not generalize well.
--
D. Richard Hipp <[EMAIL PROTECTED]>
Thanks all for the feedback. I was able to get comparable speed-ups
to my "vbde hack" on the string insertion by using a small "cache" of
rowids for previously inserted strings. This reduced the duplicate
inserts to about 1 in 100.
I still think someway of getting the rowid of the record that forced
the query to end with a constraint violation (even if it isn't the
only one) would be useful. Perhaps it could be added to the current
error string which already includes the column name(s).
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
