Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

LanX's scratchpad

by LanX (Cardinal)
on Sep 03, 2008 at 12:42 UTC ( #708739=scratchpad: print w/replies, xml ) Need Help??

alert("lanx");

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

  • default settings,
  • named args
  • validation

Autoboxing

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.

Doc-strings

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

Log In?
Username:
Password:

What's my password?
Create A New User
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (3)
As of 2020-08-05 02:06 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Which rocket would you take to Mars?










    Results (35 votes). Check out past polls.

    Notices?