[sqlite] how into insert row into middle of table with integer primary key

[Shane Dev]

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

[Clemens Ladisch]

Post by Shane Dev
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

[Hick Gunter]

Not without deleting and reinserting (or alternatively, updating) every single row past the desired insert position, since you have declared that the id field is a synonym for the internal rowid.

Had you declared "id real primary key" you could have gotten away with using the arithmetic mean of the ids bordering the desired insert position, but this would still only allow a certain number of in-between inserts (more if they are truly random, with the extreme case being ordered inserts after an initial load).

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:sqlite-users-***@mailinglists.sqlite.org] Im Auftrag von Shane Dev
Gesendet: Sonntag, 19. November 2017 21:37
An: SQLite mailing list <sqlite-***@mailinglists.sqlite.org>
Betreff: [EXTERNAL] [sqlite] how into insert row into middle of table with integer primary key

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-***@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) [phone] - 0

May be privileged. May be confidential. Please delete if not the addressee.

[Simon Slavin]

Post by Shane Dev
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.

[Clemens Ladisch]

Post by Simon Slavin
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

[Simon Slavin]

Post by Clemens Ladisch

Post by Simon Slavin
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.

[David Raymond]

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.000000 sys 0.000000
Error: UNIQUE constraint failed: fruit.id


-----Original Message-----
From: sqlite-users [mailto:sqlite-users-***@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-***@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Peter Da Silva]

If you want to maintain something like a user-selected display order, I would suggest adding an explicit “display order” column.

[R Smith]

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

Post by Shane Dev
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
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Igor Korot]

Hi,

Post by R Smith
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
id | fruit | SortOrder
1 | Apple | 100
2 | Pear | 200
3 | Kiwi | 300 etc...
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
UPDATE fruit SET id = -id-1 WHERE id >= 2;
UPDATE fruit SET id = -id WHERE id < 0;
INSERT INTO fruit (2,'Banana');
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.

Post by R Smith
Cheers,
Ryan

Post by Shane Dev
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
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[R Smith]

Post by Igor Korot

Post by R Smith
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.

[Shane Dev]

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?

Post by R Smith
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
id | fruit | SortOrder
1 | Apple | 100
2 | Pear | 200
3 | Kiwi | 300 etc...
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
UPDATE fruit SET id = -id-1 WHERE id >= 2;
UPDATE fruit SET id = -id WHERE id < 0;
INSERT INTO fruit (2,'Banana');
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

Post by Shane Dev
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
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Simon Slavin]

Post by Shane Dev
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

Post by Shane Dev
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.

[Jens Alfke]

Post by Simon Slavin
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

[R Smith]

Post by Jens Alfke

Post by Simon Slavin
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.

[Jens Alfke]

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

[Simon Slavin]

Post by Jens Alfke

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.

[Igor Korot]

Simon,

Post by Simon Slavin

Post by Jens Alfke

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.

Post by Simon Slavin
Simon.
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Shane Dev]

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-***@mailinglists.sqlite.org/msg106788.html)
is elegant, but might be slow if the table had many entries.

Post by Igor Korot
Simon,

Post by Simon Slavin

Post by Jens Alfke

Post by R Smith
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.

Post by Simon Slavin

Post by Jens Alfke
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.

Post by Simon Slavin
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.

Post by Simon Slavin
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.

Post by Simon Slavin
Simon.
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Clemens Ladisch]

Post by Shane Dev
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.

Post by Shane Dev
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.

Post by Shane Dev
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.

Post by Shane Dev
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
23 5 banana
5 69 pear
69 NULL kiwi

Now insertion requires only updating one other pointer. (But querying
must be done with a CTE.)


Regards,
Clemens

[petern]

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

Post by Shane Dev
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 (
sqlite.org/msg106788.html)
is elegant, but might be slow if the table had many entries.

Post by Igor Korot
Simon,

Post by Simon Slavin

Post by Jens Alfke

Post by R Smith
That assumes you are not starting from an integer part (like 4000)

and

Post by Igor Korot
hitting the exact same relative insert spot every time, which /can/

happen,

Post by Igor Korot
but is hugely unlikely.

Post by Simon Slavin

Post by Jens Alfke
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.

Post by Simon Slavin
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.

Post by Simon Slavin
But that just brings us back to the question of why OP wants to store

ID

Post by Igor Korot
numbers which might change.
Homework exercise?
Stupid requirements?
Thank you.

Post by Simon Slavin
Simon.
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[R Smith]

Post by Shane Dev
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. :)

[Igor Korot]

Hi, Shane,

Post by Shane Dev
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 (
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.

Post by Shane Dev

Post by Igor Korot
Simon,

Post by Simon Slavin

Post by Jens Alfke

Post by R Smith
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.

Post by Simon Slavin

Post by Jens Alfke
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.

Post by Simon Slavin
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.

Post by Simon Slavin
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.

Post by Simon Slavin
Simon.
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Shane Dev]

Post by Igor Korot
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

Post by Igor Korot
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-***@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-***@mailinglists.sqlite.org/msg106865.html)
might more be efficient. However, querying may be slower.

Post by Igor Korot
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.

Post by Hick Gunter

Post by Igor Korot
Simon,

Post by Simon Slavin

Post by Jens Alfke

Post by R Smith
That assumes you are not starting from an integer part (like 4000)

and

Post by Hick Gunter

Post by Igor Korot
hitting the exact same relative insert spot every time, which /can/

happen,

Post by Hick Gunter

Post by Igor Korot
but is hugely unlikely.

Post by Simon Slavin

Post by Jens Alfke
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,

Post by Hick Gunter

Post by Igor Korot
this immediately hits that case.

Post by Simon Slavin
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

Post by Hick Gunter

Post by Igor Korot
you do it using something like the double-UPDATE command someone came up
with earlier.

Post by Simon Slavin
But that just brings us back to the question of why OP wants to store

ID

Post by Hick Gunter

Post by Igor Korot
numbers which might change.
Homework exercise?
Stupid requirements?
Thank you.

Post by Simon Slavin
Simon.
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Igor Korot]

Hi,

Post by Shane Dev

Post by Igor Korot
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

Post by Igor Korot
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 (
might be the best choice. Otherwise, Clemens Ladisch's linked list in SQL
solution (
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.

Post by Shane Dev

Post by Igor Korot
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.

Post by Hick Gunter

Post by Igor Korot
Simon,

Post by Simon Slavin

Post by Jens Alfke

Post by R Smith
That assumes you are not starting from an integer part (like 4000)

and

Post by Hick Gunter

Post by Igor Korot
hitting the exact same relative insert spot every time, which /can/

happen,

Post by Hick Gunter

Post by Igor Korot
but is hugely unlikely.

Post by Simon Slavin

Post by Jens Alfke
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,

Post by Hick Gunter

Post by Igor Korot
this immediately hits that case.

Post by Simon Slavin
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

Post by Hick Gunter

Post by Igor Korot
you do it using something like the double-UPDATE command someone came up
with earlier.

Post by Simon Slavin
But that just brings us back to the question of why OP wants to store

ID

Post by Hick Gunter

Post by Igor Korot
numbers which might change.
Homework exercise?
Stupid requirements?
Thank you.

Post by Simon Slavin
Simon.
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Simon Slavin]

Post by Shane Dev
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.

[Chris Locke]

Post by Shane Dev
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" <***@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-***@mailinglists.
sqlite.org/msg106788.html)
is elegant, but might be slow if the table had many entries.

Post by Shane Dev
Simon,

Post by Simon Slavin

Post by Jens Alfke

Post by R Smith
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.

Post by Simon Slavin

Post by Jens Alfke
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.

Post by Simon Slavin
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.

Post by Simon Slavin
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.

Post by Simon Slavin
Simon.
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Jens Alfke]

Post by Simon Slavin
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

[Olaf Schmidt]

Post by 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

[R Smith]

Post by Jens Alfke

Post by Simon Slavin
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

[Peter Da Silva]

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.

[Wout Mertens]

Post by Jens Alfke
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".

[R Smith]

Post by Shane Dev
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).

Post by Shane Dev
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.

Post by Shane Dev
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 10000
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

[R Smith]

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);

[petern]

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!]

Post by Shane Dev
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?

Post by R Smith
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

Post by R Smith
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

Post by R Smith
to add a column called SortOrder or FruitOrder or the like. In this

column

Post by R Smith
you can then assign the values automatically in steps of 10 or 100, so

you

Post by R Smith
id | fruit | SortOrder
1 | Apple | 100
2 | Pear | 200
3 | Kiwi | 300 etc...
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
UPDATE fruit SET id = -id-1 WHERE id >= 2;
UPDATE fruit SET id = -id WHERE id < 0;
INSERT INTO fruit (2,'Banana');
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,

Post by R Smith
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

Post by Shane Dev
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

Post by R Smith

Post by Shane Dev
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
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Tony Papadimitriou]

Possible something like this may work:

create table fruit(id integer primary key, name text);

insert into fruit values
(1,'apple'),
(2,'pear'),
(3,'kiwi');

select * from fruit order by id;

begin;
update fruit set id = -id where id > 1;
update fruit set id = 1-id where id < 0;
end;

insert into fruit values(2,'banana');

select * from fruit order by id;

-----Original Message-----
From: Shane Dev
Sent: Sunday, November 19, 2017 10:37 PM
To: SQLite mailing list
Subject: [sqlite] how into insert row into middle of table with
integerprimary key

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-***@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Keith Medcalf]

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.

Post by David Raymond
-----Original Message-----
From: sqlite-users [mailto:sqlite-users-
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?

Post by R Smith
This question pops up from time to time.
I will show a correct query script to achieve this below, but I

want to

Post by R Smith
emphasize what others have said: Data in an RDBMS has no intrinsic

order,

Post by R Smith
it's all SETs, and if you artificially bestow order to the data

itself (as

Post by R Smith
opposed to the eventual output) then you are doing something that's

very

Post by R Smith
bad in database design.
To be specific, if the fruit in your DB needs ORDER as a property,

best is

Post by R Smith
to add a column called SortOrder or FruitOrder or the like. In

this column

Post by R Smith
you can then assign the values automatically in steps of 10 or 100,

so you

Post by R Smith
id | fruit | SortOrder
1 | Apple | 100
2 | Pear | 200
3 | Kiwi | 300 etc...
INSERT INTO fruit(fruit, SortOrder) VALUES ('Banana',150);
is simply trivial. (The 150 can be computed from splitting the

difference

Post by R Smith
between the precedent and decedent). Non-Integer is best.
Eventually though, you might need to do maintenance and reset the

gaps or

Post by R Smith
such.
Anyway, enough preaching - this query script will fix your

Situation in

Post by R Smith
UPDATE fruit SET id = -id-1 WHERE id >= 2;
UPDATE fruit SET id = -id WHERE id < 0;
INSERT INTO fruit (2,'Banana');
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

Post by R Smith
other tables linking to a very specific record. Imagine your query

that

Post by R Smith
added fruit to recipes where needed has the fruit's primary keys

shuffled,

Post by R Smith
the next day will see some really weird recipes when Banana ends up

where

Post by R Smith
Pear was intended. Next you'll want to insert Watermelon... :)
Cheers,
Ryan

Post by Shane Dev
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

Post by R Smith

Post by Shane Dev
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
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-

users

Post by R Smith
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-

users
_______________________________________________
sqlite-users mailing list
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[Keith Medcalf]

Post by Shane Dev

Post by Igor Korot
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;

[Stephen Chrzanowski]

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.

Post by Shane Dev
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
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users