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

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



On Sun, Nov 19, 2017 at 3: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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

On 22 Nov 2017, at 8:30pm, Shane Dev  wrote:

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

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

[Keith Medcalf]

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




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

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

[Igor Korot]

Hi,

On Wed, Nov 22, 2017 at 2:30 PM, Shane Dev  wrote:
> On 22 November 2017 at 17:08, Igor Korot  wrote:
>
>> 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
>> 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-users@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-users@mailinglists.sqlite.org/msg106865.html)
> 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.

>
>
>> Unless you can sow us that the time required to retrieve the
>> sorting data will SIGNIICANTLY
>> differ in both cases.
>>
>> I am ready to hear arguments against this approach. ;-)
>>
>> Thank you.
>>
>> >
>> >
>> > On 22 November 2017 at 00:11, Igor Korot  wrote:
>> >
>> >> Simon,
>> >>
>> >> On Tue, Nov 21, 2017 at 4:48 PM, Simon Slavin 
>> >> wrote:
>> >> >
>> >> >
>> >> > On 21 Nov 2017, at 10:09pm, Jens Alfke  wrote:
>> >> >
>> >> >>> On Nov 21, 2017, at 1:56 AM, R Smith  wrote:
>> >> >>>
>> >> >>> That assumes you are not starting from an integer part (like 4000)
>> and
>> >> hitting the exact same relative insert spot every time, which /can/
>> happen,
>> >> but is hugely unlikely.
>> >> >>
>> >> >> Not to beat this into the ground, but: it’s not that unlikely. Let’s
>> >> say you sort rows by date. You’ve already got some entries from 2015 in
>> >> your database, and some from 2017. Someone now inserts 60 entries from
>> >> 2016, and to be ‘helpful’, they insert them in chronological order.
>> Wham,
>> >> this immediately hits that case.
>> >> >
>> >> > Yes, if you use this method, you do need to renumber them every so
>> >> often.  You assess this when you’re working out (before + after) / 2,
>> and
>> >> you do it using something like the double-UPDATE command someone came up
>> >> with earlier.
>> >> >
>> >> > But that just brings us back to the question of why OP wants to store
>> ID
>> >> numbers which might change.
>> >>
>> >> Homework exercise?
>> >> Stupid requirements?
>> >>
>> >> Thank you.
>> >>
>> >> >
>> >> > Simon.
>> >> > ___
>> >> > sqlite-users mailing list
>> >> > sqlite-users@mailinglists.sqlite.org
>> >> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> >> ___
>> >> sqlite-users mailing list
>> >> sqlite-users@mailinglists.sqlite.org
>> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> >>
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@mailinglists.sqlite.org
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Shane Dev]

On 22 November 2017 at 17:08, Igor Korot  wrote:

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


> Unless you can sow us that the time required to retrieve the
> sorting data will SIGNIICANTLY
> differ in both cases.
>
> I am ready to hear arguments against this approach. ;-)
>
> Thank you.
>
> >
> >
> > On 22 November 2017 at 00:11, Igor Korot  wrote:
> >
> >> Simon,
> >>
> >> On Tue, Nov 21, 2017 at 4:48 PM, Simon Slavin 
> >> wrote:
> >> >
> >> >
> >> > On 21 Nov 2017, at 10:09pm, Jens Alfke  wrote:
> >> >
> >> >>> On Nov 21, 2017, at 1:56 AM, R Smith  wrote:
> >> >>>
> >> >>> That assumes you are not starting from an integer part (like 4000)
> and
> >> hitting the exact same relative insert spot every time, which /can/
> happen,
> >> but is hugely unlikely.
> >> >>
> >> >> Not to beat this into the ground, but: it’s not that unlikely. Let’s
> >> say you sort rows by date. You’ve already got some entries from 2015 in
> >> your database, and some from 2017. Someone now inserts 60 entries from
> >> 2016, and to be ‘helpful’, they insert them in chronological order.
> Wham,
> >> this immediately hits that case.
> >> >
> >> > Yes, if you use this method, you do need to renumber them every so
> >> often.  You assess this when you’re working out (before + after) / 2,
> and
> >> you do it using something like the double-UPDATE command someone came up
> >> with earlier.
> >> >
> >> > But that just brings us back to the question of why OP wants to store
> ID
> >> numbers which might change.
> >>
> >> Homework exercise?
> >> Stupid requirements?
> >>
> >> Thank you.
> >>
> >> >
> >> > Simon.
> >> > ___
> >> > sqlite-users mailing list
> >> > sqlite-users@mailinglists.sqlite.org
> >> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Chris Locke]

> 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"  wrote:

Hi Igor,

Homework exercise? No, this is purely a hobby project in my free time. My
goal is see how much logic can moved from application code to the database.

Why do I want store ID numbers whose values may change? Why not. Obviously,
this would be bad idea if the ID column was referenced by other column /
table. In that case, I would have created a different table such as

sqlite> .sch fruit
CREATE TABLE fruit(id integer primary key, sort integer unique, name text);

However, this just moves the problem from the id to the sort column. I
still have to consider how to manage changes to values in the sort column.
Apparently there is no single SQL statement which can insert a record in to
any arbitrary sort position. Even if I use the stepped approach (fruit.sort
= 100, 200, 300 ...) or define sort as real unique, I will still need to
determine if it is necessary to reset the gaps between sort column values.
Peter Nichvolodov's trigger solution (
https://www.mail-archive.com/sqlite-users@mailinglists.
sqlite.org/msg106788.html)
is elegant, but might be slow if the table had many entries.


On 22 November 2017 at 00:11, Igor Korot  wrote:

> Simon,
>
> On Tue, Nov 21, 2017 at 4:48 PM, Simon Slavin 
> wrote:
> >
> >
> > On 21 Nov 2017, at 10:09pm, Jens Alfke  wrote:
> >
> >>> On Nov 21, 2017, at 1:56 AM, R Smith  wrote:
> >>>
> >>> That assumes you are not starting from an integer part (like 4000) and
> hitting the exact same relative insert spot every time, which /can/
happen,
> but is hugely unlikely.
> >>
> >> Not to beat this into the ground, but: it’s not that unlikely. Let’s
> say you sort rows by date. You’ve already got some entries from 2015 in
> your database, and some from 2017. Someone now inserts 60 entries from
> 2016, and to be ‘helpful’, they insert them in chronological order. Wham,
> this immediately hits that case.
> >
> > Yes, if you use this method, you do need to renumber them every so
> often.  You assess this when you’re working out (before + after) / 2, and
> you do it using something like the double-UPDATE command someone came up
> with earlier.
> >
> > But that just brings us back to the question of why OP wants to store ID
> numbers which might change.
>
> Homework exercise?
> Stupid requirements?
>
> Thank you.
>
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Igor Korot]

Hi, Shane,

On Wed, Nov 22, 2017 at 12:40 AM, Shane Dev  wrote:
> Hi Igor,
>
> Homework exercise? No, this is purely a hobby project in my free time. My
> goal is see how much logic can moved from application code to the database.
>
> Why do I want store ID numbers whose values may change? Why not. Obviously,
> this would be bad idea if the ID column was referenced by other column /
> table. In that case, I would have created a different table such as
>
> sqlite> .sch fruit
> CREATE TABLE fruit(id integer primary key, sort integer unique, name text);
>
> However, this just moves the problem from the id to the sort column. I
> still have to consider how to manage changes to values in the sort column.
> Apparently there is no single SQL statement which can insert a record in to
> any arbitrary sort position. Even if I use the stepped approach (fruit.sort
> = 100, 200, 300 ...) or define sort as real unique, I will still need to
> determine if it is necessary to reset the gaps between sort column values.
> Peter Nichvolodov's trigger solution (
> https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg106788.html)
> is elegant, but might be slow if the table had many entries.

What I don't understand is why do you need to do that?
Database idea is to store the data and then retrieve them in any way
you want at any given time.

So all you need to know that there is a record inserted into the table
"fruit" on the schema
"garden".
Then when the time comes by you can retrieve the records with the
"ORDER BY" clause.
Whether you will sort the data by alphabet - fruit_name" or by number
increment - "Sort_order"
doesn't really matter.
Inserting the record is an implementation detail which shouldn't
bother you at all.

Unless you can sow us that the time required to retrieve the
sorting data will SIGNIICANTLY
differ in both cases.

I am ready to hear arguments against this approach. ;-)

Thank you.

>
>
> On 22 November 2017 at 00:11, Igor Korot  wrote:
>
>> Simon,
>>
>> On Tue, Nov 21, 2017 at 4:48 PM, Simon Slavin 
>> wrote:
>> >
>> >
>> > On 21 Nov 2017, at 10:09pm, Jens Alfke  wrote:
>> >
>> >>> On Nov 21, 2017, at 1:56 AM, R Smith  wrote:
>> >>>
>> >>> That assumes you are not starting from an integer part (like 4000) and
>> hitting the exact same relative insert spot every time, which /can/ happen,
>> but is hugely unlikely.
>> >>
>> >> Not to beat this into the ground, but: it’s not that unlikely. Let’s
>> say you sort rows by date. You’ve already got some entries from 2015 in
>> your database, and some from 2017. Someone now inserts 60 entries from
>> 2016, and to be ‘helpful’, they insert them in chronological order. Wham,
>> this immediately hits that case.
>> >
>> > Yes, if you use this method, you do need to renumber them every so
>> often.  You assess this when you’re working out (before + after) / 2, and
>> you do it using something like the double-UPDATE command someone came up
>> with earlier.
>> >
>> > But that just brings us back to the question of why OP wants to store ID
>> numbers which might change.
>>
>> Homework exercise?
>> Stupid requirements?
>>
>> Thank you.
>>
>> >
>> > Simon.
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@mailinglists.sqlite.org
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Peter Da Silva]

On 11/22/17, 1:43 AM, "sqlite-users on behalf of R Smith" 
 
wrote:
> 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.
 

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

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

[R Smith]

On 2017/11/22 8:40 AM, Shane Dev wrote:

However, this just moves the problem from the id to the sort column. I
still have to consider how to manage changes to values in the sort column.
Apparently there is no single SQL statement which can insert a record in to
any arbitrary sort position. Even if I use the stepped approach (fruit.sort
= 100, 200, 300 ...) or define sort as real unique, I will still need to
determine if it is necessary to reset the gaps between sort column values.


So it's not so much a solution you are after, it's a quick and easy 
one-line-of-sql solution.


Sorry to inform you, that doesn't exist in any SQL engine, because order 
is not intrinsic to data and overwhelmingly often it is just not 
important inside the DB. In the same way integer values are not stored 
with thousand separators, because that is not important inside a DB, 
it's only humans that like to see things ordered and formatted, so the 
DB engine may let the output be controlled for format and order etc, but 
it doesn't maintain that sort of thing internally and as such have no 
integrated functionality to deal with it internally.


More importantly, there are a myriad ways to maintain the kinds of 
ordering we've discussed, some of them are more efficient in one kind of 
use case, and others are more efficient in other cases.
Why should the Database engine get to decide which to use? It should be 
your choice.


We often see here questions that indicate the poster was informed by 
friends or colleagues: "Oh you should use a DB, it's much quicker and 
easier", and while that is true in the long run, it is often mistaken to 
mean: "It's quicker and easier /for you to program/" - something that is 
also mostly true, but the real statement should read: "It's quicker and 
easier at /correctly handling data/".


Emphasis there on *correctly*. It doesn't offer quick and easy 
short-cuts where those do not also underpin a good data-handling 
practice.  Not a good DB engine anyway.
Put another way: It doesn't offer millions of tools you /may/ need in 
data-handling, it offers a few tolls you /will/ need and ensures those 
work 100% accurate and consistent so you don't have to care about that 
in your programming. The rest is up to you.



Cheers,
Ryan

PS: Yeah I know, adjust that figure to 99.% for the occasional bug 
perhaps. :)


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

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

[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()
100
Run Time: real 0.006 user 0.00 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()
101
Run Time: real 0.006 user 0.004000 sys 0.00


On Tue, Nov 21, 2017 at 10:40 PM, Shane Dev  wrote:

> Hi Igor,
>
> Homework exercise? No, this is purely a hobby project in my free time. My
> goal is see how much logic can moved from application code to the database.
>
> Why do I want store ID numbers whose values may change? Why not. Obviously,
> this would be bad idea if the ID column was referenced by other column /
> table. In that case, I would have created a different table such as
>
> sqlite> .sch fruit
> CREATE TABLE fruit(id integer primary key, sort integer unique, name text);
>
> However, this just moves the problem from the id to the sort column. I
> still have to consider how to manage changes to values in the sort column.
> Apparently there is no single SQL statement which can insert a record in to
> any arbitrary sort position. Even if I use the stepped approach (fruit.sort
> = 100, 200, 300 ...) or define sort as real unique, I will still need to
> determine if it is necessary to reset the gaps between sort column values.
> Peter Nichvolodov's trigger solution (
> https://www.mail-archive.com/sqlite-users@mailinglists.
> sqlite.org/msg106788.html)
> is elegant, but might be slow if the table had many entries.
>
>
> On 22 November 2017 at 00:11, Igor Korot  wrote:
>
> > Simon,
> >
> > On Tue, Nov 21, 2017 at 4:48 PM, Simon Slavin 
> > wrote:
> > >
> > >
> > > On 21 Nov 2017, at 10:09pm, Jens Alfke  wrote:
> > >
> > >>> On Nov 21, 2017, at 1:56 AM, R Smith  wrote:
> > >>>
> > >>> That assumes you are not starting from an integer part (like 4000)
> and
> > hitting the exact same relative insert spot every time, which /can/
> happen,
> > but is hugely unlikely.
> > >>
> > >> Not to beat this into the ground, but: it’s not that unlikely. Let’s
> > say you sort rows by date. You’ve already got some entries from 2015 in
> > your database, and some from 2017. Someone now inserts 60 entries from
> > 2016, and to be ‘helpful’, they insert them in chronological order. Wham,
> > this immediately hits that case.
> > >
> > > Yes, if you use this method, you do need to renumber them every so
> > often.  You assess this when you’re working out (before + after) / 2, and
> > you do it using something like the double-UPDATE command someone came up
> > with earlier.
> > >
> > > But that just brings us back to the question of why OP wants to store
> ID
> > numbers which might change.
> >
> > Homework exercise?
> > Stupid requirements?
> >
> > Thank you.
> >
> > >
> > > Simon.
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Clemens Ladisch]

Shane Dev wrote:
> 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.

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

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

> 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
235   banana
569   pear
69  NULL  kiwi

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


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[R Smith]

On 2017/11/22 2:29 AM, Jens Alfke wrote:



On Nov 21, 2017, at 2:48 PM, Simon Slavin  wrote:

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

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

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

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


On 22 November 2017 at 00:11, Igor Korot  wrote:

> Simon,
>
> On Tue, Nov 21, 2017 at 4:48 PM, Simon Slavin 
> wrote:
> >
> >
> > On 21 Nov 2017, at 10:09pm, Jens Alfke  wrote:
> >
> >>> On Nov 21, 2017, at 1:56 AM, R Smith  wrote:
> >>>
> >>> That assumes you are not starting from an integer part (like 4000) and
> hitting the exact same relative insert spot every time, which /can/ happen,
> but is hugely unlikely.
> >>
> >> Not to beat this into the ground, but: it’s not that unlikely. Let’s
> say you sort rows by date. You’ve already got some entries from 2015 in
> your database, and some from 2017. Someone now inserts 60 entries from
> 2016, and to be ‘helpful’, they insert them in chronological order. Wham,
> this immediately hits that case.
> >
> > Yes, if you use this method, you do need to renumber them every so
> often.  You assess this when you’re working out (before + after) / 2, and
> you do it using something like the double-UPDATE command someone came up
> with earlier.
> >
> > But that just brings us back to the question of why OP wants to store ID
> numbers which might change.
>
> Homework exercise?
> Stupid requirements?
>
> Thank you.
>
> >
> > Simon.
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Wout Mertens]

On Tue, Nov 21, 2017, 11:10 PM Jens Alfke,  wrote:

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

>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Olaf Schmidt]

Am 22.11.2017 um 01:29 schrieb 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

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

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

[Jens Alfke]

> On Nov 21, 2017, at 2:48 PM, Simon Slavin  wrote:
> 
> 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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Igor Korot]

Simon,

On Tue, Nov 21, 2017 at 4:48 PM, Simon Slavin  wrote:
>
>
> On 21 Nov 2017, at 10:09pm, Jens Alfke  wrote:
>
>>> On Nov 21, 2017, at 1:56 AM, R Smith  wrote:
>>>
>>> That assumes you are not starting from an integer part (like 4000) and 
>>> hitting the exact same relative insert spot every time, which /can/ happen, 
>>> but is hugely unlikely.
>>
>> Not to beat this into the ground, but: it’s not that unlikely. Let’s say you 
>> sort rows by date. You’ve already got some entries from 2015 in your 
>> database, and some from 2017. Someone now inserts 60 entries from 2016, and 
>> to be ‘helpful’, they insert them in chronological order. Wham, this 
>> immediately hits that case.
>
> Yes, if you use this method, you do need to renumber them every so often.  
> You assess this when you’re working out (before + after) / 2, and you do it 
> using something like the double-UPDATE command someone came up with earlier.
>
> But that just brings us back to the question of why OP wants to store ID 
> numbers which might change.

Homework exercise?
Stupid requirements?

Thank you.

>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Slavin]

On 21 Nov 2017, at 10:09pm, Jens Alfke  wrote:

>> On Nov 21, 2017, at 1:56 AM, R Smith  wrote:
>> 
>> That assumes you are not starting from an integer part (like 4000) and 
>> hitting the exact same relative insert spot every time, which /can/ happen, 
>> but is hugely unlikely.
> 
> Not to beat this into the ground, but: it’s not that unlikely. Let’s say you 
> sort rows by date. You’ve already got some entries from 2015 in your 
> database, and some from 2017. Someone now inserts 60 entries from 2016, and 
> to be ‘helpful’, they insert them in chronological order. Wham, this 
> immediately hits that case.

Yes, if you use this method, you do need to renumber them every so often.  You 
assess this when you’re working out (before + after) / 2, and you do it using 
something like the double-UPDATE command someone came up with earlier.

But that just brings us back to the question of why OP wants to store ID 
numbers which might change.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Jens Alfke]

> On Nov 21, 2017, at 1:56 AM, R Smith  wrote:
> 
> That assumes you are not starting from an integer part (like 4000) and 
> hitting the exact same relative insert spot every time, which /can/ happen, 
> but is hugely unlikely.

Not to beat this into the ground, but: it’s not that unlikely. Let’s say you 
sort rows by date. You’ve already got some entries from 2015 in your database, 
and some from 2017. Someone now inserts 60 entries from 2016, and to be 
‘helpful’, they insert them in chronological order. Wham, this immediately hits 
that case.

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

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

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

[Jens Alfke]

> On Nov 20, 2017, at 2:05 PM, Simon Slavin  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

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


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

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

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



On Mon, Nov 20, 2017 at 1: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];
>
> 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  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
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >>
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

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

[R Smith]

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

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

[Simon Slavin]

On 20 Nov 2017, at 9:31pm, Shane Dev  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

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


On 20 November 2017 at 17:12, R Smith  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
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

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

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

[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


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


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

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

[Simon Slavin]

On 20 Nov 2017, at 2:57pm, Clemens Ladisch  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

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

[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.00 sys 0.00
Error: UNIQUE constraint failed: fruit.id


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

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

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

[Simon Slavin]

On 19 Nov 2017, at 8:37pm, Shane Dev  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

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