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.9999% for the occasional bug
perhaps. :)
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
