[OT] MySQL recalibrating a sort-index

by LanX (Saint) 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}

Re^3: [OT] MySQL recalibrating a sort-index

by erix (Prior) on Feb 16, 2018 at 20:53 UTC

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

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

Re^4: [OT] MySQL recalibrating a sort-index

by LanX (Saint) on Feb 16, 2018 at 23:34 UTC

Re^4: [OT] MySQL recalibrating a sort-index

by LanX (Saint) on Feb 16, 2018 at 22:43 UTC

Re^5: [OT] MySQL recalibrating a sort-index

by erix (Prior) on Feb 17, 2018 at 08:13 UTC

Some notes below your chosen depth have not been shown here

Re: [OT] MySQL recalibrating a sort-index
by choroba (Cardinal) on Feb 16, 2018 at 08:08 UTC

SELECT 1 + COUNT(1)
INTO @sort
FROM t_tree AS t
WHERE t.f_parent_id = 2
LIMIT 1;

UPDATE t_tree AS t
SET f_sort = (@sort := @sort - 1)
WHERE T.f_parent_id = 2
ORDER BY f_sort DESC;
[download]

($q=q:Sq=~/;[c](.)(.)/;chr(-||-|5+lengthSq)`"S|oS2"`map{chr |+ord
}map{substrSq`S_+|`|}3E|-|`7**2-3:)=~y+S|`+$1,++print+eval$q,q,a,
[download]

[reply]
[d/l]
[select]

by LanX (Saint) on Feb 16, 2018 at 09:52 UTC

> What do you mean by "creating code for each case"?

Re-numbering from the end covers only the insert case.
If you delete elements you need to renumber from the start.
If you move elements you need to delete and insert...
(Move is even trickier because in which state are the elements in between? And what happens if an error occurs then?)
There are even two kinds of "move", moving inside the same array and moving from another array.
(But I think this is easy to handle, just change the parent_id in the second case)
Finally there is the copy case, where elements must be (deeply) cloned before inserting. (But this problem exists in all models)

Hope this is clearer without needing to produce examples.

My approach allows to stack multiple such operations and just to renumber at the end.

Cheers Rolf
_{(addicted to the Perl Programming Language and ☆☆☆☆ :)

Wikisyntax for the Monastery}

Re: [OT] MySQL recalibrating a sort-index (replace)
by LanX (Saint) on Feb 17, 2018 at 00:23 UTC

REPLACE

DROP TABLE IF EXISTS t_tree_insert, _t;

CREATE TABLE `t_tree_insert` (
    `f_node_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `f_parent_id` INT(10) UNSIGNED NULL DEFAULT '0',
    `f_name` VARCHAR(200) NOT NULL,
    `f_sort` FLOAT NULL DEFAULT NULL,
    PRIMARY KEY (`f_node_id`),
    UNIQUE INDEX `sort` (`f_parent_id`, `f_sort`),
    INDEX `f_parent_id` (`f_parent_id`),
    FULLTEXT INDEX `f_name` (`f_name`)
)
COMMENT='test tree move operations'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=0
;


insert into t_tree_insert values
    (130, 1, 'other array', 1)
  , (142, 2, 'B06', 1)
  , (143, 2, 'B2L', 2)
  , (144, 2, 'B2M', 3)
  , (200, 2, 'INSERT', 3.5)
  , (145, 2, 'B2N', 4)
  , (146, 2, 'B2O', 5)
  , (150, 3, 'other array', 1)

;

select * from t_tree_insert order by f_parent_id, f_sort;

set @sort=0;

create temporary table _t as 
   select f_node_id, f_parent_id, f_name, (@sort:=@sort+1) as f_sort
   from  t_tree_insert as T
   where f_parent_id = 2
   order by T.f_sort
   ;

select * from _t;


replace 
   into t_tree_insert 
   (f_node_id, f_parent_id, f_name, f_sort)
   select * from _t
;
     
DROP TABLE _t;


select * from t_tree_insert order by f_parent_id, f_sort;
[download]

t_tree_insert

130	1	other array	1
142	2	B06	1
143	2	B2L	2
144	2	B2M	3
200	2	INSERT	3,5
145	2	B2N	4
146	2	B2O	5
150	3	other array	1

_t

142 2 B06 1

143 2 B2L 2

144 2 B2M 3

200 2 INSERT 4

145 2 B2N 5

146 2 B2O 6

t_tree_insert

130 1 other array 1

142 2 B06 1

143 2 B2L 2

144 2 B2M 3

200 2 INSERT 4

145 2 B2N 5

146 2 B2O 6

150 3 other array 1

probably I can even do it without a temp table...

Cheers Rolf
_{(addicted to the Perl Programming Language and ☆☆☆☆ :)

Wikisyntax for the Monastery}

by LanX (Saint) on Feb 17, 2018 at 00:30 UTC

> probably I can even do it without a temp table...

looks good! :)

DROP TABLE IF EXISTS t_tree_insert;

CREATE TABLE `t_tree_insert` (
    `f_node_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `f_parent_id` INT(10) UNSIGNED NULL DEFAULT '0',
    `f_name` VARCHAR(200) NOT NULL,
    `f_sort` FLOAT NULL DEFAULT NULL,
    PRIMARY KEY (`f_node_id`),
    UNIQUE INDEX `sort` (`f_parent_id`, `f_sort`),
    INDEX `f_parent_id` (`f_parent_id`),
    FULLTEXT INDEX `f_name` (`f_name`)
)
COMMENT='test tree move operations'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=0
;


insert into t_tree_insert values
    (130, 1, 'other array', 1)
  , (142, 2, 'B06', 1)
  , (143, 2, 'B2L', 2)
  , (144, 2, 'B2M', 3)
  , (200, 2, 'INSERT', 3.5)
  , (145, 2, 'B2N', 4)
  , (146, 2, 'B2O', 5)
  , (150, 3, 'other array', 1)

;

select * from t_tree_insert order by f_parent_id, f_sort;

set @sort=0;

replace 
   into t_tree_insert 
   (f_node_id, f_parent_id, f_name, f_sort)
   select f_node_id, f_parent_id, f_name, (@sort:=@sort+1) as f_sort
      from  t_tree_insert as T
      where f_parent_id = 2
      order by T.f_sort
;
     


select * from t_tree_insert order by f_parent_id, f_sort;
[download]

Cheers Rolf
_{(addicted to the Perl Programming Language and ☆☆☆☆ :)

Wikisyntax for the Monastery}

UPDATE

But I'm suspecting that temporarily dropping the "unique index" constraint and only updating the row f_sort is considerably faster for larger tables.

Re: [OT] MySQL recalibrating a sort-index
by Anonymous Monk on Feb 17, 2018 at 01:22 UTC

www.sitepoint.com/hierarchical-data-database/ ... This old post from another site] describes another way to do it – modified pre-order traversal trees (MPTTs). Strangely, I did not immediately identify suport for them in CPAN but they surely must be in there somewhere.

by LanX (Saint) on Feb 17, 2018 at 01:59 UTC

Of course I knew this article already, it deals with structure which can be described as hashes of hashes.

But my f_sort expands the model to also include arrays.

Please note that the described downsides are rather obsolete for trees with a maximal depth, as long as you can use Self Joins and Updatable Views.

And even a dynamic depth could be handled by redefining the view.

Cheers Rolf
_{(addicted to the Perl Programming Language and ☆☆☆☆ :)

Wikisyntax for the Monastery}