On Friday, 14 October, 2016 07:29, Thom Wharton <twhar...@northpointdefense.com> wrote:
> 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? Unfortunately no, there is no way to do this on *ANY* database that uses the relational database model. You see, in the relational database model there is no such things as "records". What you have above (a table) consists of a "bag of tuples". There is no order to them. They are merely a "bag" (known mathematically as a set). When you issue a query (SELECT) you project sets and conditions and obtain a set of data as a result (another bag of data). If you wish an order, you tell it what order to put it in (using an ORDER BY clause). Due to the inherently sequential nature of modern computing technology most programming languages are unable to deal with the data in its native form (a set) and therefore you retrieve and work on them one after each, in forward order only, in the order you requested presentation using the ORDER BY clause. The "integer primary key" (whether you use autoincrement of not) is simply a unique key in each record that is derived sequentially (unless you provide it) based on the previous (or biggest ever previously used, if you use autoincrement) number in that field, to uniquely identify the contents of that tuple. It does not contain any useful information which you should be manipulating in any way other than using it as a linkage to perhaps other related tuples in other tables. See https://en.wikipedia.org/wiki/Relational_model > Thanks, > Thom Wharton _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
