Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

Re: [OT] MySQL recalibrating a sort-index

by erix (Parson)
on Feb 16, 2018 at 11:50 UTC ( #1209291=note: print w/replies, xml ) Need Help??


in reply to [OT] MySQL recalibrating a sort-index

UPDATE: I added an CONSTRAINT DEFERRABLE to the create table, which renders LanX comment obsolete. Sorry about that./UPDATE

This is not for Oracle MySQL, I think (I didn't find the necessary functionality). This is just for fun, therefore Postgres :P

This won't work for you but you expressed some interest (or what I took to be interest) in the CB so here goes:

Below is the (p)sql-inside-bash that I cobbled together; I inserted the database-output in the script ( /* commented out */ ) for better reading.

#!/bin/sh t=ctetree echo " \set insert_location 2.5 begin transaction; drop table if exists $t ; create table $t ( f_node_id int primary key , f_parent_id int , f_name text , f_sort float , constraint tree_parent_sort_uniq_idx unique (f_parent_id, f_sort) d +eferrable ); insert into $t values (142, 2, 'B06', 1) , (143, 2, 'B2L', 2) , (144, 2, 'B2M', 3) , (145, 2, 'B2N', 4) , (146, 2, 'B2O', 5) ; \echo -- base data: table $t; /* -- base data: f_node_id | f_parent_id | f_name | f_sort -----------+-------------+--------+-------- 142 | 2 | B06 | 1 143 | 2 | B2L | 2 144 | 2 | B2M | 3 145 | 2 | B2N | 4 146 | 2 | B2O | 5 (5 rows) */ insert into $t values (394, 2, '*Inserted 1st*', :insert_location); update $t set f_sort = floor(f_sort)+1 where f_sort >= :insert_locatio +n; /* THIS BETTER REPLACED BY... see my later posts downstream */ \echo -- new begin state, after 1st insert and renumbered: table $t order by f_sort; /* -- new begin state, after 1st insert and renumbered: f_node_id | f_parent_id | f_name | f_sort -----------+-------------+----------------+-------- 142 | 2 | B06 | 1 143 | 2 | B2L | 2 394 | 2 | *Inserted 1st* | 3 144 | 2 | B2M | 4 145 | 2 | B2N | 5 146 | 2 | B2O | 6 (6 rows) */ with inserted as ( insert into $t values (395, 2, '*Inserted 2nd*', :insert_location) returning f_sort ) update $t set f_sort = floor(f_sort)+1 where f_sort > (select f_sort +from inserted); \echo -- after 2nd insert table $t order by f_sort; /* -- after 2nd insert f_node_id | f_parent_id | f_name | f_sort -----------+-------------+----------------+-------- 142 | 2 | B06 | 1 143 | 2 | B2L | 2 395 | 2 | *Inserted 2nd* | 2.5 394 | 2 | *Inserted 1st* | 4 144 | 2 | B2M | 5 145 | 2 | B2N | 6 146 | 2 | B2O | 7 (7 rows) */ update $t set f_sort = floor(f_sort)+1 where f_sort <> floor(f_sort); \echo -- after 2nd insert - renumbered table $t order by f_sort; /* -- after 2nd insert - renumbered f_node_id | f_parent_id | f_name | f_sort -----------+-------------+----------------+-------- 142 | 2 | B06 | 1 143 | 2 | B2L | 2 395 | 2 | *Inserted 2nd* | 3 394 | 2 | *Inserted 1st* | 4 144 | 2 | B2M | 5 145 | 2 | B2N | 6 146 | 2 | B2O | 7 (7 rows) */ -- -- remove rows 2 and 3, and insert them after the row where f_sort +is 5. -- \set location_to_insert 5 \echo -- initial: table $t order by f_sort; /* -- initial: f_node_id | f_parent_id | f_name | f_sort -----------+-------------+----------------+-------- 142 | 2 | B06 | 1 143 | 2 | B2L | 2 395 | 2 | *Inserted 2nd* | 3 394 | 2 | *Inserted 1st* | 4 144 | 2 | B2M | 5 145 | 2 | B2N | 6 146 | 2 | B2O | 7 (7 rows) */ create temp table _t_$t ( like $t including all ); \echo -- intermediary 1, empty temp table (to receive deleted rows): table _t_$t order by f_sort; /* -- intermediary 1, empty temp table (to receive deleted rows): f_node_id | f_parent_id | f_name | f_sort -----------+-------------+--------+-------- (0 rows) */ \echo -- with deleted -> d. rows inserted into tmp table with deleted as ( delete from $t where f_sort in (2,3) returning * ) insert into _t_$t select * from deleted; \echo -- intermediary 2 (filled with the deleted rows): table _t_$t order by f_sort; /* -- intermediary 2 (filled with the deleted rows): f_node_id | f_parent_id | f_name | f_sort -----------+-------------+----------------+-------- 143 | 2 | B2L | 2 395 | 2 | *Inserted 2nd* | 3 (2 rows) */ \echo -- with updated -> rows inserted in main table with updated as ( update _t_$t set f_sort = :location_to_insert + f_sort / 1000 returning * ) insert into $t table updated; create temp table _t as select *, row_number() over () as new_number from (select * from $t order by f_sort) f order by new_number ; \echo -- semi-ready: table _t order by f_sort; /* -- semi-ready: f_node_id | f_parent_id | f_name | f_sort | new_number -----------+-------------+----------------+--------+------------ 142 | 2 | B06 | 1 | 1 394 | 2 | *Inserted 1st* | 4 | 2 144 | 2 | B2M | 5 | 3 143 | 2 | B2L | 5.002 | 4 395 | 2 | *Inserted 2nd* | 5.003 | 5 145 | 2 | B2N | 6 | 6 146 | 2 | B2O | 7 | 7 (7 rows) */ update $t t set f_sort = _t.new_number from _t where _t.f_node_id = t.f_node_id --> join and _t.new_number <> t.f_sort --> avoid writes ; \echo -- ready: table $t order by f_sort; /* -- ready: f_node_id | f_parent_id | f_name | f_sort -----------+-------------+----------------+-------- 142 | 2 | B06 | 1 394 | 2 | *Inserted 1st* | 2 144 | 2 | B2M | 3 143 | 2 | B2L | 4 395 | 2 | *Inserted 2nd* | 5 145 | 2 | B2N | 6 146 | 2 | B2O | 7 (7 rows) */ rollback; -- clean up " | psql -Xqa | less -iSR

UPDATE: slightly tweaked UPDATEs

Replies are listed 'Best First'.
Re^2: [OT] MySQL recalibrating a sort-index
by LanX (Archbishop) on Feb 16, 2018 at 18:42 UTC
    > This is not for Oracle MySQL, I think (I didn't find the necessary functionality).

    MariaDB to be precise. (Sorry, I adapted to the sloppy wording of my department.)

    > This is just for fun, therefore Postgres :P

    Oh, I expected nothing less - than excellence - from you! :P

    I'll translate it to a "minor form of SQL", have a deeper look into it, and give feedback.

    (Not sure if the floor approach works for multiple changes.)

    Thanks! :)

    Cheers Rolf
    (addicted to the Perl Programming Language and ☆☆☆☆ :)
    Wikisyntax for the Monastery

      Yeah, the other way is more general: create, then join to a temporary table. (that floor() was just a first try, I should have removed it)

      insert into $t values (394, 2, '*Inserted 1st*', :insert_location); -- update $t set f_sort = floor(f_sort)+1 where f_sort >= :insert_loca +tion; -- meh -- instead: -- create temporary table create temp table _t as select f_node_id, f_sort, row_number() over () as new_number from ( select f_node_id, f_sort from $t order by f_sort ) as f order by new_number ; -- update table update $t t set f_sort = _t.new_number from _t where _t.f_node_id = t.f_node_id --> join expression and _t.new_number <> t.f_sort --> avoid unnecessary writes ; drop table _t; --> remove temp table

      It's verbose-ugly but it works without repeating the values.

        I haven't tested it yet, but why does an UPDATE from a temp table have no conflict with the UNIQUEness of f_sort (see the UPDATE in the OP)?

        Cheers Rolf
        (addicted to the Perl Programming Language and ☆☆☆☆ :)
        Wikisyntax for the Monastery

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1209291]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (8)
As of 2019-12-06 12:36 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Strict and warnings: which comes first?



    Results (155 votes). Check out past polls.

    Notices?