how into insert row into middle of table with integer primary key

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
39 messages Options
12
Reply | Threaded
Open this post in threaded view
|

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
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how into insert row into middle of table with integer primary key

Clemens Ladisch
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] how into insert row into middle of table with integer primary key

Hick Gunter
In reply to this post by Shane Dev
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:[hidden email]] Im Auftrag von Shane Dev
Gesendet: Sonntag, 19. November 2017 21:37
An: SQLite mailing list <[hidden email]>
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
[hidden email]
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) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how into insert row into middle of table with integer primary key

Simon Slavin-3
In reply to this post by Shane Dev


On 19 Nov 2017, at 8:37pm, Shane Dev <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how into insert row into middle of table with integer primary key

Clemens Ladisch
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how into insert row into middle of table with integer primary key

David Raymond
In reply to this post by Simon Slavin-3
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:[hidden email]] 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how into insert row into middle of table with integer primary key

Peter da Silva
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how into insert row into middle of table with integer primary key

Simon Slavin-3
In reply to this post by Clemens Ladisch


On 20 Nov 2017, at 2:57pm, Clemens Ladisch <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how into insert row into middle of table with integer primary key

R Smith
In reply to this post by Shane Dev
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how into insert row into middle of table with integer primary key

Igor Korot
Hi,

On Mon, Nov 20, 2017 at 10:12 AM, R Smith <[hidden email]> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how into insert row into middle of table with integer primary key

R Smith


On 2017/11/20 6:33 PM, Igor Korot wrote:

> On Mon, Nov 20, 2017 at 10:12 AM, R Smith <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how into insert row into middle of table with integerprimary key

Tony Papadimitriou
In reply to this post by Shane Dev
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how into insert row into middle of table with integer primary key

Shane Dev
In reply to this post by R Smith
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 <[hidden email]> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how into insert row into middle of table with integer primary key

Simon Slavin-3


On 20 Nov 2017, at 9:31pm, Shane Dev <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how into insert row into middle of table with integer primary key

R Smith
In reply to this post by Shane Dev


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


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how into insert row into middle of table with integer primary key

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



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how into insert row into middle of table with integer primary key

petern
In reply to this post by Shane Dev
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 <[hidden email]> 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 <[hidden email]> 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
> >> [hidden email]
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how into insert row into middle of table with integer primary key

Keith Medcalf
In reply to this post by Shane Dev

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-
>[hidden email]] 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 <[hidden email]> 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
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>users
>>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how into insert row into middle of table with integer primary key

Jens Alfke-2
In reply to this post by Simon Slavin-3


> On Nov 20, 2017, at 2:05 PM, Simon Slavin <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: how into insert row into middle of table with integer primary key

R Smith


On 2017/11/21 7:35 AM, Jens Alfke wrote:
>
>> On Nov 20, 2017, at 2:05 PM, Simon Slavin <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12