1 ###############################################
3 # Prepared Statements test on #
4 # "nested sets" representing hierarchies #
6 ###############################################
8 # Source: http://kris.koehntopp.de/artikel/sql-self-references (dated 1999)
9 # Source: http://dbmsmag.com/9603d06.html (dated 1996)
12 drop table if exists t1;
15 # "Nested Set": This table represents an employee list with a hierarchy tree.
16 # The tree is not modeled by "parent" links but rather by showing the "left"
17 # and "right" border of any person's "region". By convention, "l" < "r".
18 # As it is a tree, these "regions" of two persons A and B are either disjoint,
19 # or A's region is completely contained in B's (B.l < A.l < A.r < B.r:
20 # B is A's boss), or vice versa.
21 # Any other overlaps violate the model. See the references for more info.
24 id INTEGER AUTO_INCREMENT PRIMARY KEY,
25 emp CHAR(10) NOT NULL,
26 salary DECIMAL(6,2) NOT NULL,
30 prepare st_ins from 'insert into t1 set emp = ?, salary = ?, l = ?, r = ?';
32 # Initial employee list:
33 # Jerry ( Bert () Chuck ( Donna () Eddie () Fred () ) )
34 set @arg_nam= 'Jerry'; set @arg_sal= 1000; set @arg_l= 1; set @arg_r= 12;
35 execute st_ins using @arg_nam, @arg_sal, @arg_l, @arg_r ;
36 set @arg_nam= 'Bert'; set @arg_sal= 900; set @arg_l= 2; set @arg_r= 3;
37 execute st_ins using @arg_nam, @arg_sal, @arg_l, @arg_r ;
38 set @arg_nam= 'Chuck'; set @arg_sal= 900; set @arg_l= 4; set @arg_r= 11;
39 execute st_ins using @arg_nam, @arg_sal, @arg_l, @arg_r ;
40 set @arg_nam= 'Donna'; set @arg_sal= 800; set @arg_l= 5; set @arg_r= 6;
41 execute st_ins using @arg_nam, @arg_sal, @arg_l, @arg_r ;
42 set @arg_nam= 'Eddie'; set @arg_sal= 700; set @arg_l= 7; set @arg_r= 8;
43 execute st_ins using @arg_nam, @arg_sal, @arg_l, @arg_r ;
44 set @arg_nam= 'Fred'; set @arg_sal= 600; set @arg_l= 9; set @arg_r= 10;
45 execute st_ins using @arg_nam, @arg_sal, @arg_l, @arg_r ;
49 # Three successive raises, each one is 100 units for managers, 10 percent for others.
50 prepare st_raise_base from 'update t1 set salary = salary * ( 1 + ? ) where r - l = 1';
51 prepare st_raise_mgr from 'update t1 set salary = salary + ? where r - l > 1';
53 set @arg_percent= .10;
57 execute st_raise_base using @arg_percent;
58 execute st_raise_mgr using @arg_amount;
64 # Now, increase salary to a multiple of 50 (checks for bug#6138)
65 prepare st_round from 'update t1 set salary = salary + ? - ( salary MOD ? )';
67 execute st_round using @arg_round, @arg_round;