On 21/06/2013 2:29 PM, jhnlmn wrote:
Ryan Johnson <ryan.johnson@...> writes:
Q1: Is C1 *always* NULL in a newly-inserted row, or does the application
sometimes insert some arbitrary value?
Q2: Does the transition from NULL to calculation(C2) mean something
special to the application?
...
Scenario 3: C1=NULL has a specific meaning in your application
C1=NULL is just a flag, whose only meaning is that update was not done yet
on the recently inserted row.
If in Windows Explorer you enter a folder with few thousand music files,
then Explorer will show the list of files in the left column of detailed
view instantaneously, but then it will begin slowly fill up other columns
(Title, Artist, Album).
My application is not an Explorer, but it has a similar pattern - show the
list of item names ASAP and then prepare and store item attributes on the
background. This is a pretty common pattern, I guess. So, in my minimal
example C2 is the item url and C1 is item attributes (in reality there will
be several columns with attributes: C11, C12, etc., but C1=NULL means that
attributes were not prepared yet and C1!=NULL means that attributes are ready).
Scenario 2: C1=calculation(C2) is the default to use when the user fails
to supply C1;
No, in my case user does not touch the DB - he can only add or delete items,
but all DB modifications are done by my app and I have a full control over
all the values. All I need is to find the most efficient way to do that.
Therefore, I do not believe that I need any triggers. I guess they will
simply slow down insertion and update (however I did not measure this yet).
In those rare cases when there is a need to reset attributes on an already
existing item, I would rather delete the entire row and insert a new one and
treat it as a completely new item.
So if I understand correctly, you start out with only C2, use C2 to
drive an expensive/slow computation that derives all other fields in the
row, and set C1 != NULL when the derivation completes? And you don't
want a trigger because that would pin the slow computation on the
inserting (application) thread rather than the batch-updating
(background) thread?
In that case, your AUTOINCREMENT approach is probably the best you can do...
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
