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() 1000000 Run Time: real 0.006 user 0.000000 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() 1000001 Run Time: real 0.006 user 0.004000 sys 0.000000 On Tue, Nov 21, 2017 at 10:40 PM, 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. > > > 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
