Hi, Shane, On Wed, Nov 22, 2017 at 12:40 AM, Shane Dev <devshan...@gmail.com> 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 <ikoro...@gmail.com> wrote: > >> Simon, >> >> On Tue, Nov 21, 2017 at 4:48 PM, Simon Slavin <slav...@bigfraud.org> >> wrote: >> > >> > >> > On 21 Nov 2017, at 10:09pm, Jens Alfke <j...@mooseyard.com> wrote: >> > >> >>> On Nov 21, 2017, at 1:56 AM, R Smith <rsm...@rsweb.co.za> 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
