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
