On Saturday, 15 October, 2016 16:44, Richard Damon <rich...@damon-family.org>, wrote:
> Summary of what I have seen: > Schema for table has an auto-increment primary key, and a Date-Time > field (which has duplicate values) > Records are to be retrieved in Date-Time order, with duplicate values > needing to be retrieved in a consistent (and perhaps even specified by > something else) > The OP was careful to initially insert the records in Date-Time order, > and records with identical Date-Times in the 'proper' order (so the > auto-increment field just happens to be the proper total order). > He then wants to later add a new record, and wants the auto-increment to > 'automatically' update to keep it in total order. > It has been pointed out that this is NOT the way an auto-increment field > is intended to work. > Some people have come out with various ways to implement a sort field > that leaves gaps to allow at least some level of inserting new records > between old records. > My thought is that if the first level of sort IS by Date-Time, and then > by some other condition, then the real solution is to use an ORDER BY > clause on the Date-Time field and then some other field to implement the > duplicate value ordering (and likely have a covering index so this ORDER > BY is efficient). This extra field doesn't need to be globally unique, > just unique within a given value of Date-Time, so the practical issues > in processing the primary key are greatly reduced (at minimum, if you > need to renumber records, there are a lot less that need to be > renumbered). As described the first level sort cannot be by DateTime since the datetime field does not contain sortable data (it is neither an offset from some epoch nor it is an ISO-8601 formatted string (biggest datepart to littlest datepart in fixed width format per part) expressed in UTC. (datetime stored as localtime values are inherently unsortable for any location in which the timezone has ever changed offset -- which for most wall-clocks is at least twice per year). If the datetime were stored so as to permit sorting by datetime such that an ORDER BY could be used, the whole issue would not exist. Duplicates could be eliminated simply by using greater precision in the datetime data stored. The whole reason for using a "manual sortation field" is so that a human can decide what order to display the data in via a "manual" operation. > On 10/15/16 3:12 PM, Delvin wrote: > > Afternoon all, > > > > I am replying to this thread because I am a little confused here. From > what I have gotten from this thread, someone wants to be able to insert a > record into a table based on an arbitrary record number (i.e. if a table > already has records number 1, 2, 3, 4, etc., have the ability to insert a > new record number 3 and changing the record numbers (old record number) 3, > 4, etc. > > > > My confusing is this - are record numbers arbitrary to start with (since > the table can be sorted in any manner) and the only reason for the record > number column is to insure that the primary key is unique. > > > > Thanks > > Delvin > > > > > > -----Original Message----- > > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Jens Alfke > > Sent: Saturday, 15 October, 2016 13:42 > > To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> > > Subject: Re: [sqlite] Inserting a new record (anywhere) into a table of > ordered records that have an integer auto-increment primary key > > > > > >> On Oct 15, 2016, at 11:12 AM, Keith Medcalf <kmedc...@dessus.com> > wrote: > >> > >>> Is there a way to do this automagically (like a specialized INSERT > >>> command?) in Sqlite? > >> Unfortunately no, there is no way to do this on *ANY* database that > uses the relational database model. > > There’s no need to dive into database theory! I’ll play devil’s advocate > and say that this could pretty easily be done in SQLite by writing a > simple extension function like in inbetween(a, b) that takes two strings > and returns a string that sorts in between them, as I described > previously. > > > > Then you just make the table’s primary key a string and do > > INSERT INTO mytable (ID, …) VALUES (inbetween($firstid, $secondid), > …) where $firstid and $secondid are the the primary keys of the two rows > you want to insert between. > > > > In real life you’d probably just implement inbetween() as part of your > program instead of as a SQLite function; I just wanted to prove that a > relational database can in fact do this. > > > > —Jens _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
