Re: [sqlite] how into insert row into middle of table with integer primary key
SQL in any flavor isn't going to get what you want directly, as you've seen in these posts. The data is in your database for a reason. Its data. It isn't supposed to be manipulated in the way you want to do in this thread. Displaying something in a particular order is done at the application level, not the database level. There is an an 'order by' clause, but your use is somewhat invalid. I've used "order by" statements that push things either to the top or bottom of a list when I'm sorting alphabetically (IE: select * from ComponentList order by Enabled=1,Caption;) but that is the most advanced I've ever gotten as I know that the UI should be responsible for dealing with an ordering issue. "ID" fields should never be given to the user. Its a way to identify that a piece of information in your database relates directly to a UI element, something in a list in memory, or whatever. Its not a means to order by anything, but a way to identify. Its not a human consumable bit of information, and shouldn't be managed by a user. In situations where I need to put items in a certain order that isn't alphabetical or numerical sorts (Beyond what I mentioned above), I dig into linked lists and have my application code deal with displaying the information in the order of that list. The application also specifies the relationship between the previous, current, and next item. My application makes a simple select from the database, with no order specified, asking for the items ID, its parent, and its child, and put it into list of custom record sets or classed objects. I loop through the the list I pulled out looking for the item that has ParentID=0 and stake claim that this is the top of the list. Now, when I want to display information from the list, I know where my first/top item is, and I know where I need to go from there to get the rest of the list. When ChildID=0, I know I'm at the end of the list. While working only in memory, if I need to insert information between two items, I get the ParentID and ChildID from the item I want to insert after. I get a new ID either by inserting the record into the database to get the last_insert_id or I look at the largest ID in my recordset, then +1 it. I change the ChildID of the previous record to the new item ID, I change the ParentID of the ChildID record to the new item ID, and then I set the new item IDs parent and child IDs to the ID of the parent and child respectively. If I were using a class, a function would do this for me in memory, and, write the changes to the database, then update the UI however needed. On Sun, Nov 19, 2017 at 3:37 PM, Shane Dev wrote: > Let's say I have a table of fruit - > > sqlite> .sch fruit > CREATE TABLE fruit(id integer primary key, name text); > > with some entries - > > sqlite> select * from fruit; > id|name > 1|apple > 2|pear > 3|kiwi > > Is there an easy way to insert 'banana' between apple and pear while still > maintaining a consistent order of the ID field? > > desired result - > > sqlite> select * from fruit; > 1|apple > 2|banana > 3|pear > 4|kiwi > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how into insert row into middle of table with integer primary key
On 22 Nov 2017, at 8:30pm, Shane Dev wrote: > Imagine I have a GUI element with a drop down list of fruit. The source of > the list is my fruit table and it may have many entries. It might more > convenient to list the popular fruit near the top. In that case the > fruit.sort_order could represent relative popularity of the fruit entries. Storing sort order is not a good way to do this. If you want to list fruits in order of popularity do this: CREATE TABLE fruits (id INTEGER PRIMARY KEY, name TEXT COLLATE NOCASE, numberSold INTEGER); CREATE INDEX fruits_numberSold ON fruits (numberSold); Do not mess with the ID value: never change it, never display it. That’s just for the computer. When a new fruit is introduced INSERT it with a value of 0 for numberSold. When a fruit is sold, UPDATE its row to increase the numberSold value. When you want your list do SELECT name,numberSold FROM fruits ORDER BY numberSold DESC You get your list in the order you want. At no point are the order positions stored in the table. The numbers mean nothing and can change at any minute, so it would be pointless to do so. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how into insert row into middle of table with integer primary key
>> Hi, Shane, >> What I don't understand is why do you need to do that? >Imagine I have a GUI element with a drop down list of fruit. The >source of >the list is my fruit table and it may have many entries. It might >more >convenient to list the popular fruit near the top. In that case the >fruit.sort_order could represent relative popularity of the fruit >entries. Would not it simply make more sense then to have the table defined thusly: create table popularities ( id integer primary key, description text collate nocase unique ); insert into popularities(0, 'Uncommon Fruits'); create table fruits ( id integer primary key, popularity integer not null default (0) references popularities, fruit text not null collate nocase unique ); create unique index fruitorder on fruits (popularity desc, fruit); Then merely set the "popularity" to the "zone" in which you want the fruit to appear (the higher the number the higher group up the list, and still in life-form recognizable scanning order within each zone) -- after creating the popularities zone of course so that you can label those groupings. It would cause me to delete your application immediately if it did not sort entries into alphabetical order for quick location but instead used some addle-minded method of ordering that was illogical and not conducive to immediate recognition. Trust me -- most life forms in the multiverse will see this exactly the same way. select popularity, fruit from fruits order by popularity desc, fruit; ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how into insert row into middle of table with integer primary key
Hi, On Wed, Nov 22, 2017 at 2:30 PM, Shane Dev wrote: > On 22 November 2017 at 17:08, Igor Korot wrote: > >> Hi, Shane, >> >> >> What I don't understand is why do you need to do that? >> > > Imagine I have a GUI element with a drop down list of fruit. The source of > the list is my fruit table and it may have many entries. It might more > convenient to list the popular fruit near the top. In that case the > fruit.sort_order could represent relative popularity of the fruit entries. > > Database idea is to store the data and then retrieve them in any way >> you want at any given time. >> >> So all you need to know that there is a record inserted into the table >> "fruit" on the schema >> "garden". >> Then when the time comes by you can retrieve the records with the >> "ORDER BY" clause. >> Whether you will sort the data by alphabet - fruit_name" or by number >> increment - "Sort_order" >> doesn't really matter. >> Inserting the record is an implementation detail which shouldn't >> bother you at all. >> > > Actually, it interests me. If I knew insertions and updates in the fruit > table were mostly for unpopular fruits, then Peter Nichvolodov's trigger > solution ( > https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg106788.html) > might be the best choice. Otherwise, Clemens Ladisch's linked list in SQL > solution ( > https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg106865.html) > might more be efficient. However, querying may be slower. Then have a popularity column in the table and update it with every single hit using trigger. Then do the query to fill out you list with "ORDER BY popularity". Once again - how the records are inserted is implementation detail which shouldn't be of the concern in any situations. Thank you. P.S.: Basically you are trying to create a problem where there is no problem and a nice and simple solution. > > >> Unless you can sow us that the time required to retrieve the >> sorting data will SIGNIICANTLY >> differ in both cases. >> >> I am ready to hear arguments against this approach. ;-) >> >> Thank you. >> >> > >> > >> > On 22 November 2017 at 00:11, Igor Korot wrote: >> > >> >> Simon, >> >> >> >> On Tue, Nov 21, 2017 at 4:48 PM, Simon Slavin >> >> wrote: >> >> > >> >> > >> >> > On 21 Nov 2017, at 10:09pm, Jens Alfke wrote: >> >> > >> >> >>> On Nov 21, 2017, at 1:56 AM, R Smith wrote: >> >> >>> >> >> >>> That assumes you are not starting from an integer part (like 4000) >> and >> >> hitting the exact same relative insert spot every time, which /can/ >> happen, >> >> but is hugely unlikely. >> >> >> >> >> >> Not to beat this into the ground, but: it’s not that unlikely. Let’s >> >> say you sort rows by date. You’ve already got some entries from 2015 in >> >> your database, and some from 2017. Someone now inserts 60 entries from >> >> 2016, and to be ‘helpful’, they insert them in chronological order. >> Wham, >> >> this immediately hits that case. >> >> > >> >> > Yes, if you use this method, you do need to renumber them every so >> >> often. You assess this when you’re working out (before + after) / 2, >> and >> >> you do it using something like the double-UPDATE command someone came up >> >> with earlier. >> >> > >> >> > But that just brings us back to the question of why OP wants to store >> ID >> >> numbers which might change. >> >> >> >> Homework exercise? >> >> Stupid requirements? >> >> >> >> Thank you. >> >> >> >> > >> >> > Simon. >> >> > ___ >> >> > sqlite-users mailing list >> >> > sqlite-users@mailinglists.sqlite.org >> >> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> >> ___ >> >> sqlite-users mailing list >> >> sqlite-users@mailinglists.sqlite.org >> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> >> >> > ___ >> > sqlite-users mailing list >> > sqlite-users@mailinglists.sqlite.org >> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how into insert row into middle of table with integer primary key
On 22 November 2017 at 17:08, Igor Korot wrote: > Hi, Shane, > > > What I don't understand is why do you need to do that? > Imagine I have a GUI element with a drop down list of fruit. The source of the list is my fruit table and it may have many entries. It might more convenient to list the popular fruit near the top. In that case the fruit.sort_order could represent relative popularity of the fruit entries. Database idea is to store the data and then retrieve them in any way > you want at any given time. > > So all you need to know that there is a record inserted into the table > "fruit" on the schema > "garden". > Then when the time comes by you can retrieve the records with the > "ORDER BY" clause. > Whether you will sort the data by alphabet - fruit_name" or by number > increment - "Sort_order" > doesn't really matter. > Inserting the record is an implementation detail which shouldn't > bother you at all. > Actually, it interests me. If I knew insertions and updates in the fruit table were mostly for unpopular fruits, then Peter Nichvolodov's trigger solution ( https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg106788.html) might be the best choice. Otherwise, Clemens Ladisch's linked list in SQL solution ( https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg106865.html) might more be efficient. However, querying may be slower. > Unless you can sow us that the time required to retrieve the > sorting data will SIGNIICANTLY > differ in both cases. > > I am ready to hear arguments against this approach. ;-) > > Thank you. > > > > > > > On 22 November 2017 at 00:11, Igor Korot wrote: > > > >> Simon, > >> > >> On Tue, Nov 21, 2017 at 4:48 PM, Simon Slavin > >> wrote: > >> > > >> > > >> > On 21 Nov 2017, at 10:09pm, Jens Alfke wrote: > >> > > >> >>> On Nov 21, 2017, at 1:56 AM, R Smith wrote: > >> >>> > >> >>> That assumes you are not starting from an integer part (like 4000) > and > >> hitting the exact same relative insert spot every time, which /can/ > happen, > >> but is hugely unlikely. > >> >> > >> >> Not to beat this into the ground, but: it’s not that unlikely. Let’s > >> say you sort rows by date. You’ve already got some entries from 2015 in > >> your database, and some from 2017. Someone now inserts 60 entries from > >> 2016, and to be ‘helpful’, they insert them in chronological order. > Wham, > >> this immediately hits that case. > >> > > >> > Yes, if you use this method, you do need to renumber them every so > >> often. You assess this when you’re working out (before + after) / 2, > and > >> you do it using something like the double-UPDATE command someone came up > >> with earlier. > >> > > >> > But that just brings us back to the question of why OP wants to store > ID > >> numbers which might change. > >> > >> Homework exercise? > >> Stupid requirements? > >> > >> Thank you. > >> > >> > > >> > Simon. > >> > ___ > >> > sqlite-users mailing list > >> > sqlite-users@mailinglists.sqlite.org > >> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >> ___ > >> sqlite-users mailing list > >> sqlite-users@mailinglists.sqlite.org > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >> > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how into insert row into middle of table with integer primary key
> Why do I want store ID numbers > whose values may change? Why not. Because that's not what the row id column is for. Not strictly. That's why it's called 'id' - it's an identification field. You can't (shouldn't) be using it for other means. A database requirement later might need that column to link to another table. Create the database properly and use the columns properly. Thanks, Chris On 22 Nov 2017 6:40 am, "Shane Dev" wrote: Hi Igor, Homework exercise? No, this is purely a hobby project in my free time. My goal is see how much logic can moved from application code to the database. Why do I want store ID numbers whose values may change? Why not. Obviously, this would be bad idea if the ID column was referenced by other column / table. In that case, I would have created a different table such as sqlite> .sch fruit CREATE TABLE fruit(id integer primary key, sort integer unique, name text); However, this just moves the problem from the id to the sort column. I still have to consider how to manage changes to values in the sort column. Apparently there is no single SQL statement which can insert a record in to any arbitrary sort position. Even if I use the stepped approach (fruit.sort = 100, 200, 300 ...) or define sort as real unique, I will still need to determine if it is necessary to reset the gaps between sort column values. Peter Nichvolodov's trigger solution ( https://www.mail-archive.com/sqlite-users@mailinglists. sqlite.org/msg106788.html) is elegant, but might be slow if the table had many entries. On 22 November 2017 at 00:11, Igor Korot wrote: > Simon, > > On Tue, Nov 21, 2017 at 4:48 PM, Simon Slavin > wrote: > > > > > > On 21 Nov 2017, at 10:09pm, Jens Alfke wrote: > > > >>> On Nov 21, 2017, at 1:56 AM, R Smith wrote: > >>> > >>> That assumes you are not starting from an integer part (like 4000) and > hitting the exact same relative insert spot every time, which /can/ happen, > but is hugely unlikely. > >> > >> Not to beat this into the ground, but: it’s not that unlikely. Let’s > say you sort rows by date. You’ve already got some entries from 2015 in > your database, and some from 2017. Someone now inserts 60 entries from > 2016, and to be ‘helpful’, they insert them in chronological order. Wham, > this immediately hits that case. > > > > Yes, if you use this method, you do need to renumber them every so > often. You assess this when you’re working out (before + after) / 2, and > you do it using something like the double-UPDATE command someone came up > with earlier. > > > > But that just brings us back to the question of why OP wants to store ID > numbers which might change. > > Homework exercise? > Stupid requirements? > > Thank you. > > > > > Simon. > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how into insert row into middle of table with integer primary key
Hi, Shane, On Wed, Nov 22, 2017 at 12:40 AM, Shane Dev wrote: > Hi Igor, > > Homework exercise? No, this is purely a hobby project in my free time. My > goal is see how much logic can moved from application code to the database. > > Why do I want store ID numbers whose values may change? Why not. Obviously, > this would be bad idea if the ID column was referenced by other column / > table. In that case, I would have created a different table such as > > sqlite> .sch fruit > CREATE TABLE fruit(id integer primary key, sort integer unique, name text); > > However, this just moves the problem from the id to the sort column. I > still have to consider how to manage changes to values in the sort column. > Apparently there is no single SQL statement which can insert a record in to > any arbitrary sort position. Even if I use the stepped approach (fruit.sort > = 100, 200, 300 ...) or define sort as real unique, I will still need to > determine if it is necessary to reset the gaps between sort column values. > Peter Nichvolodov's trigger solution ( > https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg106788.html) > is elegant, but might be slow if the table had many entries. What I don't understand is why do you need to do that? Database idea is to store the data and then retrieve them in any way you want at any given time. So all you need to know that there is a record inserted into the table "fruit" on the schema "garden". Then when the time comes by you can retrieve the records with the "ORDER BY" clause. Whether you will sort the data by alphabet - fruit_name" or by number increment - "Sort_order" doesn't really matter. Inserting the record is an implementation detail which shouldn't bother you at all. Unless you can sow us that the time required to retrieve the sorting data will SIGNIICANTLY differ in both cases. I am ready to hear arguments against this approach. ;-) Thank you. > > > On 22 November 2017 at 00:11, Igor Korot wrote: > >> Simon, >> >> On Tue, Nov 21, 2017 at 4:48 PM, Simon Slavin >> wrote: >> > >> > >> > On 21 Nov 2017, at 10:09pm, Jens Alfke wrote: >> > >> >>> On Nov 21, 2017, at 1:56 AM, R Smith wrote: >> >>> >> >>> That assumes you are not starting from an integer part (like 4000) and >> hitting the exact same relative insert spot every time, which /can/ happen, >> but is hugely unlikely. >> >> >> >> Not to beat this into the ground, but: it’s not that unlikely. Let’s >> say you sort rows by date. You’ve already got some entries from 2015 in >> your database, and some from 2017. Someone now inserts 60 entries from >> 2016, and to be ‘helpful’, they insert them in chronological order. Wham, >> this immediately hits that case. >> > >> > Yes, if you use this method, you do need to renumber them every so >> often. You assess this when you’re working out (before + after) / 2, and >> you do it using something like the double-UPDATE command someone came up >> with earlier. >> > >> > But that just brings us back to the question of why OP wants to store ID >> numbers which might change. >> >> Homework exercise? >> Stupid requirements? >> >> Thank you. >> >> > >> > Simon. >> > ___ >> > sqlite-users mailing list >> > sqlite-users@mailinglists.sqlite.org >> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how into insert row into middle of table with integer primary key
On 11/22/17, 1:43 AM, "sqlite-users on behalf of R Smith" wrote: > Oh there are many valid reasons why to have Order in data, one I use > regularly is to dictate the process flow in manufacturing where some thing > needs to go to machine Y before it can move on to machine X, or process E, > for a specific item, has to happen before process B etc. That’s a partial ordering though, based on a dependency graph. You’d want to maintain the dependencies in the database as the ground truth, and when needed generate a topological ordering based on the dependencies. That can be maintained in some kind of cache table, but it’s not something that you would need to dynamically update like the OP but rather regenerate it when the dependencies change. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how into insert row into middle of table with integer primary key
On 2017/11/22 8:40 AM, Shane Dev wrote: However, this just moves the problem from the id to the sort column. I still have to consider how to manage changes to values in the sort column. Apparently there is no single SQL statement which can insert a record in to any arbitrary sort position. Even if I use the stepped approach (fruit.sort = 100, 200, 300 ...) or define sort as real unique, I will still need to determine if it is necessary to reset the gaps between sort column values. So it's not so much a solution you are after, it's a quick and easy one-line-of-sql solution. Sorry to inform you, that doesn't exist in any SQL engine, because order is not intrinsic to data and overwhelmingly often it is just not important inside the DB. In the same way integer values are not stored with thousand separators, because that is not important inside a DB, it's only humans that like to see things ordered and formatted, so the DB engine may let the output be controlled for format and order etc, but it doesn't maintain that sort of thing internally and as such have no integrated functionality to deal with it internally. More importantly, there are a myriad ways to maintain the kinds of ordering we've discussed, some of them are more efficient in one kind of use case, and others are more efficient in other cases. Why should the Database engine get to decide which to use? It should be your choice. We often see here questions that indicate the poster was informed by friends or colleagues: "Oh you should use a DB, it's much quicker and easier", and while that is true in the long run, it is often mistaken to mean: "It's quicker and easier /for you to program/" - something that is also mostly true, but the real statement should read: "It's quicker and easier at /correctly handling data/". Emphasis there on *correctly*. It doesn't offer quick and easy short-cuts where those do not also underpin a good data-handling practice. Not a good DB engine anyway. Put another way: It doesn't offer millions of tools you /may/ need in data-handling, it offers a few tolls you /will/ need and ensures those work 100% accurate and consistent so you don't have to care about that in your programming. The rest is up to you. Cheers, Ryan PS: Yeah I know, adjust that figure to 99.% for the occasional bug perhaps. :) ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how into insert row into middle of table with integer primary key
Shane. Below is a simple benchmark you can play with to decide if that trigger is fast enough for your application. On the time scale of human thinking and reaction time, I've found SQLite code quite responsive and magnitudes easier to maintain than the equivalent application code. FYI, that trigger will fly past the first two UPDATE statements if no id's match the WHERE clauses. So, if your insert collisions are infrequent, there will be no measurable penalty for routinely inserting into the view. CREATE ids(id INTEGER PRIMARY KEY); CREATE VIEW ids_ins AS SELECT * FROM ids; CREATE TRIGGER ids_ins INSTEAD OF INSERT ON ids_ins BEGIN UPDATE ids SET id = -id-1 WHERE id >= NEW.id; UPDATE ids SET id = -id WHERE id < 0; INSERT INTO ids VALUES (NEW.id); END; --insert a million rows sqlite> WITH genids AS (SELECT (1)id UNION ALL SELECT (id+1)id FROM genids) INSERT INTO ids SELECT * FROM genids LIMIT 1e6; Run Time: real 1.903 user 1.136000 sys 0.048000 sqlite> SELECT count() FROM ids; count() 100 Run Time: real 0.006 user 0.00 sys 0.008000 --move a million rows out of the way and back again... sqlite> INSERT INTO ids_ins VALUES(1); Run Time: real 5.853 user 4.732000 sys 0.148000 sqlite> SELECT count() FROM ids; count() 101 Run Time: real 0.006 user 0.004000 sys 0.00 On Tue, Nov 21, 2017 at 10:40 PM, Shane Dev wrote: > Hi Igor, > > Homework exercise? No, this is purely a hobby project in my free time. My > goal is see how much logic can moved from application code to the database. > > Why do I want store ID numbers whose values may change? Why not. Obviously, > this would be bad idea if the ID column was referenced by other column / > table. In that case, I would have created a different table such as > > sqlite> .sch fruit > CREATE TABLE fruit(id integer primary key, sort integer unique, name text); > > However, this just moves the problem from the id to the sort column. I > still have to consider how to manage changes to values in the sort column. > Apparently there is no single SQL statement which can insert a record in to > any arbitrary sort position. Even if I use the stepped approach (fruit.sort > = 100, 200, 300 ...) or define sort as real unique, I will still need to > determine if it is necessary to reset the gaps between sort column values. > Peter Nichvolodov's trigger solution ( > https://www.mail-archive.com/sqlite-users@mailinglists. > sqlite.org/msg106788.html) > is elegant, but might be slow if the table had many entries. > > > On 22 November 2017 at 00:11, Igor Korot wrote: > > > Simon, > > > > On Tue, Nov 21, 2017 at 4:48 PM, Simon Slavin > > wrote: > > > > > > > > > On 21 Nov 2017, at 10:09pm, Jens Alfke wrote: > > > > > >>> On Nov 21, 2017, at 1:56 AM, R Smith wrote: > > >>> > > >>> That assumes you are not starting from an integer part (like 4000) > and > > hitting the exact same relative insert spot every time, which /can/ > happen, > > but is hugely unlikely. > > >> > > >> Not to beat this into the ground, but: it’s not that unlikely. Let’s > > say you sort rows by date. You’ve already got some entries from 2015 in > > your database, and some from 2017. Someone now inserts 60 entries from > > 2016, and to be ‘helpful’, they insert them in chronological order. Wham, > > this immediately hits that case. > > > > > > Yes, if you use this method, you do need to renumber them every so > > often. You assess this when you’re working out (before + after) / 2, and > > you do it using something like the double-UPDATE command someone came up > > with earlier. > > > > > > But that just brings us back to the question of why OP wants to store > ID > > numbers which might change. > > > > Homework exercise? > > Stupid requirements? > > > > Thank you. > > > > > > > > Simon. > > > ___ > > > sqlite-users mailing list > > > sqlite-users@mailinglists.sqlite.org > > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how into insert row into middle of table with integer primary key
Shane Dev wrote: > Why do I want store ID numbers whose values may change? Why not. Because the name "ID" implies that its value _identifies_ the row. If it changes, it is not an ID. > Obviously, this would be bad idea if the ID column was referenced by > other column / table. In that case, I would have created a different > table such as > > CREATE TABLE fruit(id integer primary key, sort integer unique, name text); > > However, this just moves the problem from the id to the sort column. Now updates to the sort column no longer change the rowid, so no longer require moving the entire row around. > I still have to consider how to manage changes to values in the sort > column. You could make updates much easier by dropping the UNIQUE constraint on the sort column. > Apparently there is no single SQL statement which can insert a record > in to any arbitrary sort position. If you have a short list (short enough that the user can rearrange them randomly with the mouse), then just updating all values is no problem. If you have a large list, then you should use a data structure that is more suitable for random insertions. The table above is the equivalent of an array; the equivalent of a linked list would be this: CREATE TABLE fruit ( id INTEGER PRIMARY KEY, next INTEGER REFERENCES fruit, name TEXT ); id next name 42 23 apple 235 banana 569 pear 69 NULL kiwi Now insertion requires only updating one other pointer. (But querying must be done with a CTE.) Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how into insert row into middle of table with integer primary key
On 2017/11/22 2:29 AM, Jens Alfke wrote: On Nov 21, 2017, at 2:48 PM, Simon Slavin wrote: But that just brings us back to the question of why OP wants to store ID numbers which might change. When I’ve run into this before, the requirement has been to support lists with customizable ordering, like an outliner where the user can freely drag the rows up and down. Oh there are many valid reasons why to have Order in data, one I use regularly is to dictate the process flow in manufacturing where some thing needs to go to machine Y before it can move on to machine X, or process E, for a specific item, has to happen before process B etc. The problem is not that "Order" by itself is silly to have in data, the problem is that the OP intended (at first) to gain such order by manipulating/relying on the PRIMARY KEY value expecting the DB itself to have intrinsic order, which is folly. (A bit like changing your Surname to adjust your place in the phone-book or indicate your position in a race result.) I think the OP has been swayed from this view so it is no longer a problem. Only remaining detail is how to best maintain the order when correctly kept as a separate entity. I think the examples already discussed will do perfectly when implemented wisely. Cheers, Ryan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how into insert row into middle of table with integer primary key
Hi Igor, Homework exercise? No, this is purely a hobby project in my free time. My goal is see how much logic can moved from application code to the database. Why do I want store ID numbers whose values may change? Why not. Obviously, this would be bad idea if the ID column was referenced by other column / table. In that case, I would have created a different table such as sqlite> .sch fruit CREATE TABLE fruit(id integer primary key, sort integer unique, name text); However, this just moves the problem from the id to the sort column. I still have to consider how to manage changes to values in the sort column. Apparently there is no single SQL statement which can insert a record in to any arbitrary sort position. Even if I use the stepped approach (fruit.sort = 100, 200, 300 ...) or define sort as real unique, I will still need to determine if it is necessary to reset the gaps between sort column values. Peter Nichvolodov's trigger solution ( https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg106788.html) is elegant, but might be slow if the table had many entries. On 22 November 2017 at 00:11, Igor Korot wrote: > Simon, > > On Tue, Nov 21, 2017 at 4:48 PM, Simon Slavin > wrote: > > > > > > On 21 Nov 2017, at 10:09pm, Jens Alfke wrote: > > > >>> On Nov 21, 2017, at 1:56 AM, R Smith wrote: > >>> > >>> That assumes you are not starting from an integer part (like 4000) and > hitting the exact same relative insert spot every time, which /can/ happen, > but is hugely unlikely. > >> > >> Not to beat this into the ground, but: it’s not that unlikely. Let’s > say you sort rows by date. You’ve already got some entries from 2015 in > your database, and some from 2017. Someone now inserts 60 entries from > 2016, and to be ‘helpful’, they insert them in chronological order. Wham, > this immediately hits that case. > > > > Yes, if you use this method, you do need to renumber them every so > often. You assess this when you’re working out (before + after) / 2, and > you do it using something like the double-UPDATE command someone came up > with earlier. > > > > But that just brings us back to the question of why OP wants to store ID > numbers which might change. > > Homework exercise? > Stupid requirements? > > Thank you. > > > > > Simon. > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how into insert row into middle of table with integer primary key
On Tue, Nov 21, 2017, 11:10 PM Jens Alfke, wrote: > > It’s a lot better to use strings, and just increase the length of the > string as necessary. So to insert in between “A” and “C” you add “B”, then > to insert between “A” and “B” you add “AM”, etc. > Except that you can't insert before "A" :) With numbers you can go negative. Of course you could disallow "A" as the key, start at "B" and then to sort before use "AN". > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how into insert row into middle of table with integer primary key
Am 22.11.2017 um 01:29 schrieb Jens Alfke: When I’ve run into this before, the requirement has been to support lists with customizable ordering, like an outliner where the user can freely drag the rows up and down. Yep. And therefore such cases should be handled at the App-Level IMO... There's a lot of ways to approach that - one that comes to mind (since JSON is in the meantime standard in App-development), is to store such "orderable Groups" in their own JSON-Blob-DBFields (as simple Text - serialized into JSON-Array-format for example). E.g. when we assume that any given "fruit-salad" is stored as a single record (a single Blob) in a table "recipes", then this could look like the following VB-Code... (which interested users could paste e.g. into an Excel-VBA-Module, after installing and referencing the vbRichClient5-COM-wrapper for SQLite): Private Cnn As cMemDB, SQL As String Sub Main() Set Cnn = New_c.MemDB 'create an SQLite InMemory-DB-Instance Cnn.Exec "Create Table Recipes(ID Integer Primary Key, R Text)" InsertNewRecipe MakeRecipe("apple", "pear", "kiwi") 'insert 1st record Dim R As cCollection 'at App-Level, a Recipe is a Collection Set R = GetRecipeByID(1) 'retr. the above inserted Record by ID R.Add "banana", Before:=1 'add banana before Index 1 (pear) UpdateRecipe 1, R 'write the new content of R back into the DB (ID 1) 'check, whether the DB-update was successful, retr. a Collection by ID Debug.Print GetRecipeByID(1).SerializeToJSONString 'search-queries against the JSON-content are possible per Like... SQL = "Select R From Recipes Where R Like '%banana%'" Debug.Print Cnn.GetRs(SQL)(0) 'or when the SQLite-JSONExtension is available, it will allow 'to query the contents of JSON-fields more specifically... SQL = "Select R From Recipes Where json_extract(R,'$[1]')='banana'" Debug.Print Cnn.GetRs(SQL)(0) End Sub The above prints out (the same thing from all 3 Debug-Statements): ["apple","banana","pear","kiwi"] ["apple","banana","pear","kiwi"] ["apple","banana","pear","kiwi"] The critical line in the above main-code (which makes handling the issue per SQL obsolete) is: -> R.Add "banana", Before:=1 (most Array-, List- or Collection-Objects allow such Inserts inbetween, no matter which programming-language). '-- the needed Helper-Functions for the above Main-Routine -- Function MakeRecipe(ParamArray PA()) As cCollection 'returntype of a new Recipe is a JSON-Array-(in a cCollection) Set MakeRecipe = New_c.JSONArray Dim P: For Each P In PA: MakeRecipe.Add P: Next 'copy-over-loop End Function Sub InsertNewRecipe(R As cCollection) Cnn.ExecCmd "Insert Into Recipes(R) Values(?)", _ R.SerializeToJSONString End Sub Function GetRecipeByID(ByVal ID As Long) As cCollection Dim sJSON As String 'first retrieve the JSON-String by ID sJSON = Cnn.GetSingleVal("Select R From Recipes Where ID=" & ID) 'deserialize sJSON into a cCollection Set GetRecipeByID = New_c.JSONDecodeToCollection(sJSON) End Function Sub UpdateRecipe(ByVal ID As Long, R As cCollection) Cnn.ExecCmd "Update Recipes Set R=? Where ID=?",_ R.SerializeToJSONString, ID End Sub Olaf ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how into insert row into middle of table with integer primary key
> On Nov 21, 2017, at 2:48 PM, Simon Slavin wrote: > > But that just brings us back to the question of why OP wants to store ID > numbers which might change. When I’ve run into this before, the requirement has been to support lists with customizable ordering, like an outliner where the user can freely drag the rows up and down. —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how into insert row into middle of table with integer primary key
Simon, On Tue, Nov 21, 2017 at 4:48 PM, Simon Slavin wrote: > > > On 21 Nov 2017, at 10:09pm, Jens Alfke wrote: > >>> On Nov 21, 2017, at 1:56 AM, R Smith wrote: >>> >>> That assumes you are not starting from an integer part (like 4000) and >>> hitting the exact same relative insert spot every time, which /can/ happen, >>> but is hugely unlikely. >> >> Not to beat this into the ground, but: it’s not that unlikely. Let’s say you >> sort rows by date. You’ve already got some entries from 2015 in your >> database, and some from 2017. Someone now inserts 60 entries from 2016, and >> to be ‘helpful’, they insert them in chronological order. Wham, this >> immediately hits that case. > > Yes, if you use this method, you do need to renumber them every so often. > You assess this when you’re working out (before + after) / 2, and you do it > using something like the double-UPDATE command someone came up with earlier. > > But that just brings us back to the question of why OP wants to store ID > numbers which might change. Homework exercise? Stupid requirements? Thank you. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how into insert row into middle of table with integer primary key
On 21 Nov 2017, at 10:09pm, Jens Alfke wrote: >> On Nov 21, 2017, at 1:56 AM, R Smith wrote: >> >> That assumes you are not starting from an integer part (like 4000) and >> hitting the exact same relative insert spot every time, which /can/ happen, >> but is hugely unlikely. > > Not to beat this into the ground, but: it’s not that unlikely. Let’s say you > sort rows by date. You’ve already got some entries from 2015 in your > database, and some from 2017. Someone now inserts 60 entries from 2016, and > to be ‘helpful’, they insert them in chronological order. Wham, this > immediately hits that case. Yes, if you use this method, you do need to renumber them every so often. You assess this when you’re working out (before + after) / 2, and you do it using something like the double-UPDATE command someone came up with earlier. But that just brings us back to the question of why OP wants to store ID numbers which might change. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how into insert row into middle of table with integer primary key
> On Nov 21, 2017, at 1:56 AM, R Smith wrote: > > That assumes you are not starting from an integer part (like 4000) and > hitting the exact same relative insert spot every time, which /can/ happen, > but is hugely unlikely. Not to beat this into the ground, but: it’s not that unlikely. Let’s say you sort rows by date. You’ve already got some entries from 2015 in your database, and some from 2017. Someone now inserts 60 entries from 2016, and to be ‘helpful’, they insert them in chronological order. Wham, this immediately hits that case. (This is similar to the problem that some tree data structures have, where adding entries in sorted order results in the must unbalanced possible tree.) It’s a lot better to use strings, and just increase the length of the string as necessary. So to insert in between “A” and “C” you add “B”, then to insert between “A” and “B” you add “AM”, etc. —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how into insert row into middle of table with integer primary key
On 2017/11/21 7:35 AM, Jens Alfke wrote: On Nov 20, 2017, at 2:05 PM, Simon Slavin wrote: INSERT INTO fruit VALUES ((1 + 2) / 2), 'banana') This gives you a value of 1.5, and puts the new entry in the right place. This solution (which comes up every time this problem is discussed, it seems) is attractive but not very scaleable. All you have to do is add 60 records one at a time after record 1, and you’ll exceed the precision of double-precision floating point and get duplicate values that don’t have a stable sort order. It doesn't really matter That assumes you are not starting from an integer part (like 4000) and hitting the exact same relative insert spot every time, which /can/ happen, but is hugely unlikely. In the very unlikely event that you /are/ inserting at the same spot (let's ignore for a moment that the chosen design is flawed if this is the case) , you definitely can run into the limit of division precision. However, the solution is pretty simple: The moment you assign a Sort Index value that differs from its neighbour by less than, say, 1x10^-8 (that's still many bits away from the limit), then run (or at least flag/schedule for) your Sort-Index re-balancing operation. The fact that a normal double precision float is only 64 bits long is never a reason to panic and doesn't invalidate a solution, though it does mean you need to pay attention. Also worthy to note, this solution is only really great if you have an insanely big dataset or insert loads of entries at a time and so want to defer a more expensive sort re-jig till later. If you only insert one new thing now and again on a medium sized db, then just rejig the Sort indexer immediately. What Jens' point does illustrate is: This solution *must* be accompanied by some Sort-Index re-jigging algorithm. You have however a lot of freedom in choosing the frequency and scope of it. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how into insert row into middle of table with integer primary key
> On Nov 20, 2017, at 2:05 PM, Simon Slavin wrote: > > INSERT INTO fruit VALUES ((1 + 2) / 2), 'banana') > > This gives you a value of 1.5, and puts the new entry in the right place. This solution (which comes up every time this problem is discussed, it seems) is attractive but not very scaleable. All you have to do is add 60 records one at a time after record 1, and you’ll exceed the precision of double-precision floating point and get duplicate values that don’t have a stable sort order. —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how into insert row into middle of table with integer primary key
If you want the fruits sorted (and not duplicated), why not just declare that when defining the table? create table fruits (id integer primary key, fruit text collate nocase unique); and if you want to know the "relative row number" of the fruit simply have your application count them: logicalrow = 0 prepare('SELECT fruit from fruits order by fruit;') while True: if step() == NO MORE ROWS break fruitname = getcolumn(1) logicalrecord++ playWithFruitAndLogicalRecordNumber() wend There is very little use (if any at all) for a "logical row number" is Relational Data ... --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Shane Dev >Sent: Monday, 20 November, 2017 14:31 >To: SQLite mailing list >Subject: Re: [sqlite] how into insert row into middle of table with >integer primary key > >Hi Ryan, > >Nice trick - changing the sign of ID. I agree that changing an >existing >record's ID value would cause problems for any other field / table >that >referenced this key. > >If I used your idea of adding a SortOrder column incremented in steps >of x, >as you suggested, the gaps would start to disappear after many >inserts >between existing records. I suppose the gaps could be reset by >writing >program to call sqlite3_exec with > >select * from fruit order by SortOrder; > >and increment a RowCount variable each time the callback was >triggered, >then update fruit with something like > >update fruit set SortOrder = RowCount*x where id = [id of the row >when it's >callback was triggered]; > >I would to prefer to avoid this solution because it involves mutable >state >(the RowCount variable) which is the "root of all evil" (bugs). Is >there an >SQL statement which could reset the gaps back to x? > > >On 20 November 2017 at 17:12, R Smith wrote: > >> This question pops up from time to time. >> >> I will show a correct query script to achieve this below, but I >want to >> emphasize what others have said: Data in an RDBMS has no intrinsic >order, >> it's all SETs, and if you artificially bestow order to the data >itself (as >> opposed to the eventual output) then you are doing something that's >very >> bad in database design. >> >> To be specific, if the fruit in your DB needs ORDER as a property, >best is >> to add a column called SortOrder or FruitOrder or the like. In >this column >> you can then assign the values automatically in steps of 10 or 100, >so you >> end up with a table like: >> id | fruit | SortOrder >> 1 | Apple | 100 >> 2 | Pear | 200 >> 3 | Kiwi | 300 etc... >> >> >> Then inserting: >> INSERT INTO fruit(fruit, SortOrder) VALUES ('Banana',150); >> >> is simply trivial. (The 150 can be computed from splitting the >difference >> between the precedent and decedent). Non-Integer is best. >> >> Eventually though, you might need to do maintenance and reset the >gaps or >> such. >> >> Anyway, enough preaching - this query script will fix your >Situation in >> SQLite very fast: >> >> UPDATE fruit SET id = -id-1 WHERE id >= 2; >> UPDATE fruit SET id = -id WHERE id < 0; >> INSERT INTO fruit (2,'Banana'); >> >> >> Another way: >> >> UPDATE fruit SET id = -(id * 100); >> UPDATE fruit SET id = -id WHERE id < 0; >> INSERT INTO fruit (150,'Banana'); >> >> >> The reason why this is bad? Mostly a primary Key serves as a lookup >for >> other tables linking to a very specific record. Imagine your query >that >> added fruit to recipes where needed has the fruit's primary keys >shuffled, >> the next day will see some really weird recipes when Banana ends up >where >> Pear was intended. Next you'll want to insert Watermelon... :) >> >> Cheers, >> Ryan >> >> >> On 2017/11/19 10:37 PM, Shane Dev wrote: >> >>> Let's say I have a table of fruit - >>> >>> sqlite> .sch fruit >>> CREATE TABLE fruit(id integer primary key, name text); >>> >>> with some entries - >>> >>> sqlite> select * from fruit; >>> id|name >>> 1|apple >>> 2|pear >>> 3|kiwi >>> >>> Is there an easy way to insert 'banana' between apple and pear >
Re: [sqlite] how into insert row into middle of table with integer primary key
Shane. If you're dead set on paying the cost for brute force mid table id insertion, take a look at INSTEAD OF triggers: https://sqlite.org/lang_createtrigger.html Your example would look like this: CREATE VIEW fruit_ins AS SELECT * FROM fruit; CREATE TRIGGER fruit_ins INSTEAD OF INSERT ON fruit_ins BEGIN UPDATE fruit SET id = -id-1 WHERE id >= NEW.id; UPDATE fruit SET id = -id WHERE id < 0; INSERT INTO fruit VALUES (NEW.id,NEW.fruit); END; INSERT INTO fruit_ins VALUES (2,'Banana'); [Caveat: don't use intentional negative Id's in production without revising this code first!] On Mon, Nov 20, 2017 at 1:31 PM, Shane Dev wrote: > Hi Ryan, > > Nice trick - changing the sign of ID. I agree that changing an existing > record's ID value would cause problems for any other field / table that > referenced this key. > > If I used your idea of adding a SortOrder column incremented in steps of x, > as you suggested, the gaps would start to disappear after many inserts > between existing records. I suppose the gaps could be reset by writing > program to call sqlite3_exec with > > select * from fruit order by SortOrder; > > and increment a RowCount variable each time the callback was triggered, > then update fruit with something like > > update fruit set SortOrder = RowCount*x where id = [id of the row when it's > callback was triggered]; > > I would to prefer to avoid this solution because it involves mutable state > (the RowCount variable) which is the "root of all evil" (bugs). Is there an > SQL statement which could reset the gaps back to x? > > > On 20 November 2017 at 17:12, R Smith wrote: > > > This question pops up from time to time. > > > > I will show a correct query script to achieve this below, but I want to > > emphasize what others have said: Data in an RDBMS has no intrinsic order, > > it's all SETs, and if you artificially bestow order to the data itself > (as > > opposed to the eventual output) then you are doing something that's very > > bad in database design. > > > > To be specific, if the fruit in your DB needs ORDER as a property, best > is > > to add a column called SortOrder or FruitOrder or the like. In this > column > > you can then assign the values automatically in steps of 10 or 100, so > you > > end up with a table like: > > id | fruit | SortOrder > > 1 | Apple | 100 > > 2 | Pear | 200 > > 3 | Kiwi | 300 etc... > > > > > > Then inserting: > > INSERT INTO fruit(fruit, SortOrder) VALUES ('Banana',150); > > > > is simply trivial. (The 150 can be computed from splitting the difference > > between the precedent and decedent). Non-Integer is best. > > > > Eventually though, you might need to do maintenance and reset the gaps or > > such. > > > > Anyway, enough preaching - this query script will fix your Situation in > > SQLite very fast: > > > > UPDATE fruit SET id = -id-1 WHERE id >= 2; > > UPDATE fruit SET id = -id WHERE id < 0; > > INSERT INTO fruit (2,'Banana'); > > > > > > Another way: > > > > UPDATE fruit SET id = -(id * 100); > > UPDATE fruit SET id = -id WHERE id < 0; > > INSERT INTO fruit (150,'Banana'); > > > > > > The reason why this is bad? Mostly a primary Key serves as a lookup for > > other tables linking to a very specific record. Imagine your query that > > added fruit to recipes where needed has the fruit's primary keys > shuffled, > > the next day will see some really weird recipes when Banana ends up where > > Pear was intended. Next you'll want to insert Watermelon... :) > > > > Cheers, > > Ryan > > > > > > On 2017/11/19 10:37 PM, Shane Dev wrote: > > > >> Let's say I have a table of fruit - > >> > >> sqlite> .sch fruit > >> CREATE TABLE fruit(id integer primary key, name text); > >> > >> with some entries - > >> > >> sqlite> select * from fruit; > >> id|name > >> 1|apple > >> 2|pear > >> 3|kiwi > >> > >> Is there an easy way to insert 'banana' between apple and pear while > still > >> maintaining a consistent order of the ID field? > >> > >> desired result - > >> > >> sqlite> select * from fruit; > >> 1|apple > >> 2|banana > >> 3|pear > >> 4|kiwi > >> ___ > >> sqlite-users mailing list > >> sqlite-users@mailinglists.sqlite.org > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >> > > > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how into insert row into middle of table with integer primary key
Oops, didn't concentrate, that query should best be: WITH NewOrder(nid,norder) AS ( SELECT F1.id, (SELECT COUNT(*) * 100 FROM fruit AS F2 WHERE F2.SortOrder < F1.SortOrder) FROM fruit AS F1 ORDER BY F1.id -- This last ORDER BY is important as it forces the above correlated subquery -- to not recompute and causes a temp index on id. ) UPDATE fruit SET SortOrder = (SELECT norder FROM NewOrder WHERE nid = fruit.id); ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how into insert row into middle of table with integer primary key
On 2017/11/20 11:31 PM, Shane Dev wrote: Hi Ryan, Nice trick - changing the sign of ID. I agree that changing an existing record's ID value would cause problems for any other field / table that referenced this key. If I used your idea of adding a SortOrder column incremented in steps of x, as you suggested, the gaps would start to disappear after many inserts between existing records. I suppose the gaps could be reset by writing program to call sqlite3_exec with select * from fruit order by SortOrder; and increment a RowCount variable each time the callback was triggered, then update fruit with something like update fruit set SortOrder = RowCount*x where id = [id of the row when it's callback was triggered]; Well this would not work because the id is no longer in-step with the ordering, in stead the SortOrder column controls ordering, but it may be completely different to the id (which is precisely why we needed it to start with, since we don't wish to jiggle the id around to adjust the order). I would to prefer to avoid this solution because it involves mutable state (the RowCount variable) which is the "root of all evil" (bugs). Agreed. Is there an SQL statement which could reset the gaps back to x? It just so happens there is. :) UPDATE fruit SET SortOrder = (SELECT COUNT(*)*100 FROM fruit AS F WHERE F.id < fruit.id); This will reset the SortOrder indices in steps of 100 from 0 to (n*100) where n is the last record ordinal in the list. I just picked 100 as a thumbsuck, you can of course use anything from 1 to approaching the 64-bit integer limit, but probably 100, 1000 or 1 will do, depending on how often you foresee ordered inserts happening. Also, it's perfectly OK to use Floating point values here, so you can keep inserting even after exhausting the integer divisible limit. Cheers! Ryan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how into insert row into middle of table with integer primary key
On 20 Nov 2017, at 9:31pm, Shane Dev wrote: > I would to prefer to avoid this solution because it involves mutable state > (the RowCount variable) which is the "root of all evil" (bugs). Is there an > SQL statement which could reset the gaps back to x? As others have written, the thing you’re trying to do is difficult. This is because there is not advantage to doing it in SQL. Why store integers at all ? If you want to maintain your own order using an in insertion list maintain a REAL field instead and do > sqlite> select * from fruit; > id|name > 1|apple > 2|pear > 3|kiwi > > Is there an easy way to insert 'banana' between apple and pear while still > maintaining a consistent order of the ID field? INSERT INTO fruit VALUES ((1 + 2) / 2), 'banana') This gives you a value of 1.5, and puts the new entry in the right place. When you want your fruit list produced in the right order, do SELECT name FROM fruits ORDER BY orderNumber and you’ll get your list in the right order. It doesn’t matter that the integers aren’t stored anywhere. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how into insert row into middle of table with integer primary key
Hi Ryan, Nice trick - changing the sign of ID. I agree that changing an existing record's ID value would cause problems for any other field / table that referenced this key. If I used your idea of adding a SortOrder column incremented in steps of x, as you suggested, the gaps would start to disappear after many inserts between existing records. I suppose the gaps could be reset by writing program to call sqlite3_exec with select * from fruit order by SortOrder; and increment a RowCount variable each time the callback was triggered, then update fruit with something like update fruit set SortOrder = RowCount*x where id = [id of the row when it's callback was triggered]; I would to prefer to avoid this solution because it involves mutable state (the RowCount variable) which is the "root of all evil" (bugs). Is there an SQL statement which could reset the gaps back to x? On 20 November 2017 at 17:12, R Smith wrote: > This question pops up from time to time. > > I will show a correct query script to achieve this below, but I want to > emphasize what others have said: Data in an RDBMS has no intrinsic order, > it's all SETs, and if you artificially bestow order to the data itself (as > opposed to the eventual output) then you are doing something that's very > bad in database design. > > To be specific, if the fruit in your DB needs ORDER as a property, best is > to add a column called SortOrder or FruitOrder or the like. In this column > you can then assign the values automatically in steps of 10 or 100, so you > end up with a table like: > id | fruit | SortOrder > 1 | Apple | 100 > 2 | Pear | 200 > 3 | Kiwi | 300 etc... > > > Then inserting: > INSERT INTO fruit(fruit, SortOrder) VALUES ('Banana',150); > > is simply trivial. (The 150 can be computed from splitting the difference > between the precedent and decedent). Non-Integer is best. > > Eventually though, you might need to do maintenance and reset the gaps or > such. > > Anyway, enough preaching - this query script will fix your Situation in > SQLite very fast: > > UPDATE fruit SET id = -id-1 WHERE id >= 2; > UPDATE fruit SET id = -id WHERE id < 0; > INSERT INTO fruit (2,'Banana'); > > > Another way: > > UPDATE fruit SET id = -(id * 100); > UPDATE fruit SET id = -id WHERE id < 0; > INSERT INTO fruit (150,'Banana'); > > > The reason why this is bad? Mostly a primary Key serves as a lookup for > other tables linking to a very specific record. Imagine your query that > added fruit to recipes where needed has the fruit's primary keys shuffled, > the next day will see some really weird recipes when Banana ends up where > Pear was intended. Next you'll want to insert Watermelon... :) > > Cheers, > Ryan > > > On 2017/11/19 10:37 PM, Shane Dev wrote: > >> Let's say I have a table of fruit - >> >> sqlite> .sch fruit >> CREATE TABLE fruit(id integer primary key, name text); >> >> with some entries - >> >> sqlite> select * from fruit; >> id|name >> 1|apple >> 2|pear >> 3|kiwi >> >> Is there an easy way to insert 'banana' between apple and pear while still >> maintaining a consistent order of the ID field? >> >> desired result - >> >> sqlite> select * from fruit; >> 1|apple >> 2|banana >> 3|pear >> 4|kiwi >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how into insert row into middle of table with integer primary key
On 2017/11/20 6:33 PM, Igor Korot wrote: On Mon, Nov 20, 2017 at 10:12 AM, R Smith wrote: The reason why this is bad? Mostly a primary Key serves as a lookup for other tables linking to a very specific record. Imagine your query that added fruit to recipes where needed has the fruit's primary keys shuffled, the next day will see some really weird recipes when Banana ends up where Pear was intended. Next you'll want to insert Watermelon... :) For this specific problem you have a FOREIGN KEY... ON UPDATE CASCADE. But yes - this problem is very weird. Unless its some kind of educational/home work Thank you. Oh, yes!, good thing Igor mentioned this. IF you do have foreign keys on that primary key that CASCADEs updates or revert the children to NULL or such, that given query can be very devastating and/or Painfully slow. Something to keep in mind. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how into insert row into middle of table with integer primary key
Hi, On Mon, Nov 20, 2017 at 10:12 AM, R Smith wrote: > This question pops up from time to time. > > I will show a correct query script to achieve this below, but I want to > emphasize what others have said: Data in an RDBMS has no intrinsic order, > it's all SETs, and if you artificially bestow order to the data itself (as > opposed to the eventual output) then you are doing something that's very bad > in database design. > > To be specific, if the fruit in your DB needs ORDER as a property, best is > to add a column called SortOrder or FruitOrder or the like. In this column > you can then assign the values automatically in steps of 10 or 100, so you > end up with a table like: > id | fruit | SortOrder > 1 | Apple | 100 > 2 | Pear | 200 > 3 | Kiwi | 300 etc... > > > Then inserting: > INSERT INTO fruit(fruit, SortOrder) VALUES ('Banana',150); > > is simply trivial. (The 150 can be computed from splitting the difference > between the precedent and decedent). Non-Integer is best. > > Eventually though, you might need to do maintenance and reset the gaps or > such. > > Anyway, enough preaching - this query script will fix your Situation in > SQLite very fast: > > UPDATE fruit SET id = -id-1 WHERE id >= 2; > UPDATE fruit SET id = -id WHERE id < 0; > INSERT INTO fruit (2,'Banana'); > > > Another way: > > UPDATE fruit SET id = -(id * 100); > UPDATE fruit SET id = -id WHERE id < 0; > INSERT INTO fruit (150,'Banana'); > > > The reason why this is bad? Mostly a primary Key serves as a lookup for > other tables linking to a very specific record. Imagine your query that > added fruit to recipes where needed has the fruit's primary keys shuffled, > the next day will see some really weird recipes when Banana ends up where > Pear was intended. Next you'll want to insert Watermelon... :) For this specific problem you have a FOREIGN KEY... ON UPDATE CASCADE. But yes - this problem is very weird. Unless its some kind of educational/home work Thank you. > > Cheers, > Ryan > > > On 2017/11/19 10:37 PM, Shane Dev wrote: >> >> Let's say I have a table of fruit - >> >> sqlite> .sch fruit >> CREATE TABLE fruit(id integer primary key, name text); >> >> with some entries - >> >> sqlite> select * from fruit; >> id|name >> 1|apple >> 2|pear >> 3|kiwi >> >> Is there an easy way to insert 'banana' between apple and pear while still >> maintaining a consistent order of the ID field? >> >> desired result - >> >> sqlite> select * from fruit; >> 1|apple >> 2|banana >> 3|pear >> 4|kiwi >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how into insert row into middle of table with integer primary key
This question pops up from time to time. I will show a correct query script to achieve this below, but I want to emphasize what others have said: Data in an RDBMS has no intrinsic order, it's all SETs, and if you artificially bestow order to the data itself (as opposed to the eventual output) then you are doing something that's very bad in database design. To be specific, if the fruit in your DB needs ORDER as a property, best is to add a column called SortOrder or FruitOrder or the like. In this column you can then assign the values automatically in steps of 10 or 100, so you end up with a table like: id | fruit | SortOrder 1 | Apple | 100 2 | Pear | 200 3 | Kiwi | 300 etc... Then inserting: INSERT INTO fruit(fruit, SortOrder) VALUES ('Banana',150); is simply trivial. (The 150 can be computed from splitting the difference between the precedent and decedent). Non-Integer is best. Eventually though, you might need to do maintenance and reset the gaps or such. Anyway, enough preaching - this query script will fix your Situation in SQLite very fast: UPDATE fruit SET id = -id-1 WHERE id >= 2; UPDATE fruit SET id = -id WHERE id < 0; INSERT INTO fruit (2,'Banana'); Another way: UPDATE fruit SET id = -(id * 100); UPDATE fruit SET id = -id WHERE id < 0; INSERT INTO fruit (150,'Banana'); The reason why this is bad? Mostly a primary Key serves as a lookup for other tables linking to a very specific record. Imagine your query that added fruit to recipes where needed has the fruit's primary keys shuffled, the next day will see some really weird recipes when Banana ends up where Pear was intended. Next you'll want to insert Watermelon... :) Cheers, Ryan On 2017/11/19 10:37 PM, Shane Dev wrote: Let's say I have a table of fruit - sqlite> .sch fruit CREATE TABLE fruit(id integer primary key, name text); with some entries - sqlite> select * from fruit; id|name 1|apple 2|pear 3|kiwi Is there an easy way to insert 'banana' between apple and pear while still maintaining a consistent order of the ID field? desired result - sqlite> select * from fruit; 1|apple 2|banana 3|pear 4|kiwi ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how into insert row into middle of table with integer primary key
On 20 Nov 2017, at 2:57pm, Clemens Ladisch wrote: > Simon Slavin wrote: >> UPDATE fruit SET id = id+1 WHERE id >=2; > > This is unlikely to work because some ID values can conflict in the > middle of the execution. Which in fact violates formal requirements. Im SQL it is proper to have constraint checks only at the end of an instruction, or only at the end of a transaction. However you are right with respect to SQLite. Which only increases the importantce of the second part of my post: why does OP want to do this ? Renumbering SQL id’s is rare. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how into insert row into middle of table with integer primary key
If you want to maintain something like a user-selected display order, I would suggest adding an explicit “display order” column. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how into insert row into middle of table with integer primary key
That actually doesn't work in SQLite as it checks the primary key uniqueness after every row change, not after all updates have been completed. sqlite> update fruit set id = id + 1 where id >= 2; --EQP-- 0,0,0,SEARCH TABLE fruit USING INTEGER PRIMARY KEY (rowid>?) Run Time: real 0.000 user 0.00 sys 0.00 Error: UNIQUE constraint failed: fruit.id -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin Sent: Monday, November 20, 2017 9:39 AM To: SQLite mailing list Subject: Re: [sqlite] how into insert row into middle of table with integer primary key UPDATE fruit SET id = id+1 WHERE id >=2; INSERT … ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how into insert row into middle of table with integer primary key
Simon Slavin wrote: > UPDATE fruit SET id = id+1 WHERE id >=2; This is unlikely to work because some ID values can conflict in the middle of the execution. Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how into insert row into middle of table with integer primary key
On 19 Nov 2017, at 8:37pm, Shane Dev wrote: > sqlite> select * from fruit; > id|name > 1|apple > 2|pear > 3|kiwi > > Is there an easy way to insert 'banana' between apple and pear while still > maintaining a consistent order of the ID field? > > desired result - > > sqlite> select * from fruit; > 1|apple > 2|banana > 3|pear > 4|kiwi UPDATE fruit SET id = id+1 WHERE id >=2; INSERT … But the real question you need to ask yourself is why you’re doing this. ID numbers in a table are meant to be seen by computers, never humans. Why does an ID number matter to you ? Why aren’t you just inserting your new fruit after the end of the exiting fruits ? Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how into insert row into middle of table with integer primary key
Shane Dev wrote: > CREATE TABLE fruit(id integer primary key, name text); > > id|name > 1|apple > 2|pear > 3|kiwi > > Is there an easy way to insert 'banana' between apple and pear while still > maintaining a consistent order of the ID field? It would be possible, but not easy, especially not in plain SQL. Why don't you compute the order dynamically? Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users