SQL: cloning a sub-tree in a (node_id,parent_id) table

the idea is to build the clone inside a temp table with node_ids starting from 0 and to later add those IDs to max(ID) of the target table, hence the topology is kept and duplicate ID conflicts are impossible

# begin transaction delimiter // BEGIN NOT ATOMIC SET @v_level=0; DROP TABLE IF EXISTS t_copy; CREATE TEMPORARY TABLE t_copy SELECT f_node_id, f_parent_id from t_tm1_export_import_tree LIMIT 0; ALTER TABLE t_copy ADD f_cloned_id INT(10) UNSIGNED; ALTER TABLE t_copy ADD f_level INT(10) UNSIGNED ; ALTER TABLE t_copy ADD INDEX(f_level); ALTER TABLE t_copy MODIFY COLUMN f_node_id INT auto_increment PRIMARY +KEY; # --- INIT tree roots INSERT INTO t_copy (f_cloned_id, f_parent_id, f_level ) SELECT f_node_id, NULL, @v_level FROM t_tm1_export_import_tree WHERE f_node_id = 22 ; # --- MariaDB doesn't allow self joins with tmp tables :-/ # i.e need a second temp table DROP TABLE IF EXISTS parents; CREATE TEMPORARY TABLE parents SELECT * from t_copy WHERE f_level = @v_level; # --- recursive Copy WHILE ( row_count() ) DO SET @v_level = @v_level+1; INSERT INTO t_copy (f_parent_id, f_cloned_id, f_level) SELECT P.f_node_id, T.f_node_id, @v_level FROM t_tm1_export_import_tree as T JOIN PARENTS AS P ON T.f_parent_id = P.f_cloned_id ; TRUNCATE TABLE parents; INSERT INTO parents SELECT * from t_copy WHERE f_level = @v_level ; END WHILE; # ====== result # --- add to max ID of target table SELECT max(f_node_id) into @target_id from t_tm1_export_import_tree; IF (FALSE) THEN INSERT INTO t_tm1_export_import_tree SELECT t_copy.f_node_id + @target_id, t_copy.f_parent_id + @target_id, T.f_bezeichner, T.f_sort, T.f_insert, "Y" AS f_duplicate FROM t_copy JOIN t_tm1_export_import_tree AS T ON t_copy.f_cloned_id = T.f_node_id; END IF; END// # --- dump t_copy for testing IF (TRUE) THEN SELECT * FROM t_copy JOIN t_tm1_export_import_tree AS T ON t_copy.f_cloned_id = T.f_node_id; END IF;

sunblocker for Eily:

original idea here: Re: Blocking users

/* sundialsvc4 */ .node-from-647953, /* Newest nodes */ #id-397425 .nnt-auth-647953 /* RAT */ // others to follow { display: none; };

older stuff

Find missing lines

select N.*,C.* from t_tm1_export_import_view_copy AS C left join t_tm1_export_import_view_neu AS N ON C.f_transferID = N.f_transferID AND C.f_dim = N.f_dim AND C.f_elementNr = N.f_elementNr where N.f_dim is NULL

Headlines in the monastery

head 1

head 2

head 3

head 4

head 5

head 6

Things I miss in Perl5

In kind of descending order my wish list.

(brainstormed example code is just sketched and not tested)

Function Signatures

sub ($a,$b,$c) { }

optimally with


use method syntax to apply builtins

$string->match(/x/); $aref->grep B{ };

A REPL in core (allowing lexicals)

Seriously it's so much easier to test and experiment in a REPL

Built-in aliasing for lexicals

*a=$b only works with package-vars

A simple "in" operator

~~ is too confusing

if ($a in @a) { }; $a in 1..1000 # but lazy

Gather/Take to create iterators like in Perl6 or like Python generators

Multiple loop variables

for my [$a,$b,$c] (@list) { }

like  while (my ($a,$b,$c) = splice @list,0,3) {} but not destrcutive.

A shorter block syntax

sub to pass lamdas as argument w/o prototype at any position is too long

(approach ruby in brevity)

maybe  func 1,2,3, B{ $a + $b }

A more modular inner structure / documentation

builtins length and special vars could be attributes of a class string

perlfunc is too long!!!

Optional types in declaration

it's already possible to type at declaration my $a int but it's ignored.

This could be used for optimization of inner loops.


sub bla { doc "this function blas"; return "bla" } print &bla->doc();

Easier introspection in general

seriously playing around with typeglobs and stashes is no fun, a simpler interface could be so easy

%main::->grep B{ /_test$/ and isARRAY }; print map { $_->sig() . "\n" . $_->doc() } grep2 {isCODE} %main::

preview page