If you sort rule is by Date (and time) and ID as a tie breaker, then you should have your sort key be THAT, not just your ID field (I.e., your index/order would be Data, ID not just ID)

An auto-increment primary key is to give every record a unique id to refer to it, the only order that it can easily hold is order of creation.


Now you are saying that the new record should be between two records with the same time stamp. Now you are saying that while originally you said ordered by time stamp, with equal keys having a stable order, to them having a specific order (not just needing to be stable). Is your time stamp not precise enough and from other data you know better? (if so, you need a better time-stamp in the index, perhaps as a supplemental field for the fraction of the second.

The Summary, an auto-increment field shouldn't be used to enforce an order that comes from something else. It can provide a 'stable' (but arbitrary) order and the end of the sort condition, but you should not anticipate changing the auto-index values to try and create some other order.


On 10/14/16 9:29 AM, Thom Wharton wrote:
Hello,

I have a table of records in a Sqlite DB. It contains 5 records. Each record has a time-stamp which is not guaranteed to be unique. To preserve order (which is important in my project), I've given the table an integer primary key (called ID) that is auto-increment. Let's say I have the following table...


ID        Date                       Type     Size  Data

1        10OCT-08:13:47      Ether    28    sddsgsd...

2        10OCT-08:13:52      Ether    77    fdasfdsdsddssdg...

3        10OCT-08:13:52      Ether    44    zeasfkkfa...

4        10OCT-08:13:57      Ether    33    dartdg...

5        10OCT-08:14:03      Ether    51    afafsfafa...


I want to be able to programmatically insert a new record anywhere in that table. Let's suppose I want to create a new record between the records whose ID are 2 and 3. This new record would need to take the ID of 3, and all subsequent records would need to have their primary keys updated.

Is there a way to do this automagically (like a specialized INSERT command?) in Sqlite?

Thanks,

Thom Wharton



_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


--
Richard Damon

_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to