blib/lib/DBIx/Tree.pm

filter: Statementstmt 66.8% 121 / 181 Branchbran 57.1% 40 / 70 Conditioncond 48.0% 24 / 50 MC/DCmcdc 27.5% 8 / 29 Subroutinesub 76.9% 10 / 13 Podpod 50.0% 2 / 4 totaltotal 59.0% 205 / 347 CC 58 SCAR 44.2
CC
58
Cov
61%
_traverse_linear
306.0
_do_query
18.0
new
11.0
CRAP 82.7
↑ Summary
1 package DBIx::Tree;
2  
3 2 ▶use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
4 2 ▶use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
5 2 ▶use warnings qw(FATAL utf8); # Fatalize encoding glitches.
Subroutine BEGIN: called CC=1 SCAR=0.0
6  
7 2 ▶use Carp;
Subroutine BEGIN: called CC=1 SCAR=0.0
8  
9 2 ▶use DBI;
Subroutine BEGIN: called CC=1 SCAR=0.0
10  
11 2 our $VERSION = '1.98';
12  
13 # ------------------------------------------------
14  
15 sub do_query
16 0 ▶{
Subroutine do_query: not called CC=2 SCAR=17.9
Pod undocumented
17 0 my $self = shift;
18  
19 0 ▶ carp "do_query() is now a private function - you need not call it yourself"
Branch: 1 branch
BranchTrueFalseTotal
if $^W 0 0 0
20 if $^W;
21  
22 0 $self->_do_query(@_);
23  
24 } # End of do_query.
25  
26 # ------------------------------------------------
27  
28 sub _do_query
29 95 ▶{
Subroutine _do_query: called CC=18 SCAR=28.9
30 95 my ($self, $parentid, $id, $level) = @_;
31  
32 95 my $sth;
33  
34 95 ▶ unless ($sth = $self->{sth}) {
Branch: 2 branches
BranchTrueFalseTotal
if ($sth = $self->{'sth'}) { } 63 32 95
if (do { not ref $self->{'sth'} }) { } 42 21 63
35 32 my $columns = join(', ', @{ $self->{columns} } );
36  
37 32 my $sql = "SELECT $columns FROM " . ($self->{table});
38 32 ▶ if ( $self->{match_data} ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{"match_data"}) 6 26 32
39 6 $sql .= " WHERE $self->{data_column} like '$self->{match_data}%'";
40 }
41 $sql .= ' order by ' . $self->{order_column} . ' ' .
42 32 $self->{order_direction} ;
43 32 ▶ if ( $self->{limit} ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{"limit"}) 4 28 32
44 4 $sql .= " LIMIT $self->{limit_left}";
45 }
46 32 $sth = $self->{dbh}->prepare($sql);
47 } elsif (!ref $self->{sth}) {
48 42 $sth = $self->{dbh}->prepare($self->{sth});
49 } else {
50 21 $sth = $self->{sth};
51 }
52  
53 95 ▶ if (defined($parentid) || defined($id)) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $parentid or defined $id) 94 1 95
Condition100%3/3
defined $parentid or defined $id
l!l&&r!l&&!r
8771
MC/DC100%2/2
defined $parentid or defined $id
defined $parentid defined $id
Truth table100%3/3
A or B
A= defined $parentidB= defined $id
ABresult
1X1
011
000
54 # need to modify the statement
55 94 my $sql = $sth->{Statement};
56 94 my $conj = 'WHERE';
57  
58 94 my ($where, $extra);
59 94 ▶ if ($sql =~ m/\s+WHERE\s+(.*)/i) {
Branch: 1 branch
BranchTrueFalseTotal
if ($sql =~ /\s+WHERE\s+(.*)/i) { } 6 88 94
60 6 $where = $1;
61 6 ($extra) = $where =~ m/((?:GROUP\s+BY|ORDER\s+BY|LIMIT).*)/si;
62 6 $where =~ s/((?:GROUP\s+BY|ORDER\s+BY|LIMIT).*)//si;
63 6 $sql =~ s/\s+WHERE\s+.*//; # strip where/extra off sql
64 } else {
65 88 $where = '';
66 88 ($extra) = $sql =~ m/((?:GROUP\s+BY|ORDER\s+BY|LIMIT).*)/si;
67 88 $sql =~ s/((?:GROUP\s+BY|ORDER\s+BY|LIMIT).*)//si;
68 }
69  
70 94 ▶ if ($where) {
Branch: 1 branch
BranchTrueFalseTotal
if ($where) 6 88 94
71 6 $where = "$conj ( $where )";
72 6 $conj = 'AND';
73 }
74  
75 94 ▶ if (defined $parentid) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $parentid) 87 7 94
76 87 $where .= "$conj $self->{parent_id_column} = ?";
77 87 $conj = ' AND';
78 }
79  
80 94 ▶ if (defined $id) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $id) 7 87 94
81 7 $where .= "$conj $self->{id_column} = ?";
82 7 $conj = ' AND';
83 }
84  
85 94 $sql .= " $where $extra";
86 94 $sth = $self->{dbh}->prepare_cached($sql);
87 }
88  
89 95 ▶ my $rc = $sth->execute(defined $parentid ? $parentid : (),
Branch: 2 branches
BranchTrueFalseTotal
defined $parentid ? : 87 8 95
defined $id ? : 7 88 95
90 defined $id ? $id : ()
91 );
92 95 ▶ if (!$rc) {
Branch: 1 branch
BranchTrueFalseTotal
unless ($rc) 0 95 95
93 0 carp("Could not issue query: $DBI::errstr");
94 0 return 0;
95 }
96  
97 95 $self->{data} = $sth->fetchall_arrayref({});
98 95 ▶ $sth->finish if $sth->{Active};
Branch: 1 branch
BranchTrueFalseTotal
if $sth->{"Active"} 0 95 95
99  
100 95 ▶ if (!defined($level) || ($level >= $self->{threshold}) ) {
Branch: 1 branch
BranchTrueFalseTotal
if (not defined $level or $level >= $self->{"threshold"}) 93 2 95
Condition100%3/3
not defined $level or $level >= $self->{"threshold"}
l!l&&r!l&&!r
1922
MC/DC100%2/2
not defined $level or $level >= $self->{"threshold"}
not defined $level $level >= $self->{"threshold"}
Truth table100%3/3
not(A) or B
A= not defined $levelB= $level >= $self->{"threshold"}
ABresult
1X1
011
000
101 93 $self->{limit_left} -= @{$self->{data}};
102 }
103 95 ▶ $self->{limit_left} = 0 if $self->{limit_left} < 0;
Branch: 1 branch
BranchTrueFalseTotal
if $self->{"limit_left"} < 0 39 56 95
104  
105 95 1; # return success
106  
107 } # End of _do_query.
108  
109 # ------------------------------------------------
110  
111 sub _handle_node
112 87 ▶{
Subroutine _handle_node: called CC=9 SCAR=22.3
113 87 my ($self,
114 $id,
115 $item,
116 $parentids,
117 $parentnames,
118 $level) = @_;
119  
120 87 ▶ unless (defined $item) {
Branch: 1 branch
BranchTrueFalseTotal
unless (defined $item) 7 80 87
121 7 $self->_do_query(undef, $id, $level); # special root finding invocation
122 7 $item = $self->{data}->[0]->{$self->{data_column}};
123 }
124  
125 # $item is not defined when the constructor is called with:
126 # o match_data = 'Some value', and
127 # o start_id = Some value, and (presumably)
128 # o The id of the match data is not start_id.
129 # In this case, the above special call for finding the root does
130 # not return a valid value for item.
131  
132 87 ▶ if (defined($item) && $self->{method} && ($level >= $self->{threshold}) )
Branch: 1 branch
BranchTrueFalseTotal
if (defined $item and $self->{"method"} and $level >= $self->{"threshold"}) 84 3 87
Condition83%5/6
defined $item and $self->{"method"} and $level >= $self->{"threshold"}
!ll&&!rl&&r
2184
defined $item and $self->{"method"}
!ll&&!rl&&r
2085
MC/DC66%2/3
defined $item and $self->{"method"} and $level >= $self->{"threshold"}
defined $item $self->{"method"} $level >= $self->{"threshold"}
Truth table75%3/4
A and B and C
A= defined $itemB= $self->{"method"}C= $level >= $self->{"threshold"}
ABCresult
0XX0
10X0
1100
1111
133 {
134 $self->{method}->
135 84 ( item => $item,
136 level => $level,
137 id => $id,
138 parent_id => $parentids,
139 parent_name => $parentnames );
140 }
141  
142 87 $self->_do_query($id, undef, $level);
143 87 push @{$parentids}, $id;
144 87 push @{$parentnames}, $item;
145  
146 87 for my $child (@{$self->{data}}) {
147 $self->_handle_node($child->{$self->{id_column}},
148 $child->{$self->{data_column}},
149 80 $parentids,
150 $parentnames,
151 $level+1);
152 }
153  
154 87 pop @{$parentids};
155 87 pop @{$parentnames};
156  
157 87 ▶ if (defined($item) && $self->{post_method} && ($level >= $self->{threshold}) )
Branch: 1 branch
BranchTrueFalseTotal
if (defined $item and $self->{"post_method"} and $level >= $self->{"threshold"}) 0 87 87
Condition50%3/6
defined $item and $self->{"post_method"} and $level >= $self->{"threshold"}
!ll&&!rl&&r
8700
defined $item and $self->{"post_method"}
!ll&&!rl&&r
2850
MC/DC0%0/3
defined $item and $self->{"post_method"} and $level >= $self->{"threshold"}
defined $item $self->{"post_method"} $level >= $self->{"threshold"}
Truth table50%2/4
A and B and C
A= defined $itemB= $self->{"post_method"}C= $level >= $self->{"threshold"}
ABCresult
0XX0
10X0
1100
1111
158 {
159 $self->{post_method}->
160 0 ( item => $item,
161 level => $level,
162 id => $id,
163 parent_id => $parentids,
164 parent_name => $parentnames );
165 }
166  
167 } # End of _handle_node.
168  
169 # ------------------------------------------------
170  
171 sub new
172 4 ▶{
Subroutine new: called CC=10 SCAR=23.9
Pod documented
173 4 my $proto = shift;
174 4 ▶ my $class = ref($proto) || $proto;
Condition33%1/3
ref $proto || $proto
l!l&&r!l&&!r
040
MC/DC0%0/2
ref $proto || $proto
ref $proto $proto
Truth table33%1/3
A || B
A= ref $protoB= $proto
ABresult
1X1
011
000
175 4 my $self = {};
176 4 bless ($self, $class);
177  
178 4 my %args = @_;
179  
180 4 $self->{dbh} = $args{connection};
181  
182 # $self->{dbh}->trace(1);
183 4 $self->{dbh}->{RaiseError} = 1;
184  
185 4 $self->{table} = $args{table};
186 4 $self->{method} = $args{method};
187 4 $self->{post_method} = $args{post_method};
188 4 ▶ $self->{sth} = $args{sth} || $args{sql};
Condition100%3/3
$args{'sth'} || $args{'sql'}
l!l&&r!l&&!r
112
MC/DC100%2/2
$args{'sth'} || $args{'sql'}
$args{'sth'} $args{'sql'}
Truth table100%3/3
A || B
A= $args{'sth'}B= $args{'sql'}
ABresult
1X1
011
000
189  
190 4 my $columns = $args{columns};
191 4 $self->{columns} = $columns;
192 4 $self->{id_column} = $columns->[0];
193 4 $self->{data_column} = $columns->[1];
194 4 $self->{parent_id_column} = $columns->[2];
195 4 ▶ $self->{order_column} = $columns->[3] if $#$columns > 2;
Branch: 1 branch
BranchTrueFalseTotal
if $columns->$#* > 2 0 4 4
196 4 ▶ $self->{order_column} ||= $self->{data_column};
Condition33%1/3
$self->{"order_column"} ||= $self->{"data_column"}
l!l&&r!l&&!r
040
MC/DC0%0/2
$self->{"order_column"} ||= $self->{"data_column"}
$self->{"order_column"} $self->{"data_column"}
Truth table33%1/3
A ||= B
A= $self->{"order_column"}B= $self->{"data_column"}
ABresult
1X1
011
000
197 4 ▶ $self->{order_direction} = $columns->[4] if $#$columns > 3;
Branch: 1 branch
BranchTrueFalseTotal
if $columns->$#* > 3 0 4 4
198 4 ▶ $self->{order_direction}||= ''; # hush undefined warnings
Condition50%1/2
$self->{"order_direction"} ||= ""
l!l
04
MC/DC0%0/1
$self->{"order_direction"} ||= ""
$self->{"order_direction"}
Truth table50%1/2
A
A= $self->{"order_direction"}
Aresult
11
00
199  
200 4 ▶ $self->{start_id} = $args{start_id} || 1;
Condition50%1/2
$args{'start_id'} || 1
l!l
40
MC/DC0%0/1
$args{'start_id'} || 1
$args{'start_id'}
Truth table50%1/2
A
A= $args{'start_id'}
Aresult
11
00
201 4 ▶ $self->{threshold} = $args{threshold} || 1;
Condition50%1/2
$args{'threshold'} || 1
l!l
04
MC/DC0%0/1
$args{'threshold'} || 1
$args{'threshold'}
Truth table50%1/2
A
A= $args{'threshold'}
Aresult
11
00
202 4 $self->{match_data} = $args{match_data};
203 4 $self->{limit} = $args{limit};
204  
205 4 ▶ $self->{recursive} = $args{recursive} || 1;
Condition50%1/2
$args{'recursive'} || 1
l!l
04
MC/DC0%0/1
$args{'recursive'} || 1
$args{'recursive'}
Truth table50%1/2
A
A= $args{'recursive'}
Aresult
11
00
206  
207 4 return $self;
208  
209 } # End of new.
210  
211 # ------------------------------------------------
212  
213 sub traverse
214 7 ▶{
Subroutine traverse: called CC=6 SCAR=19.3
Pod documented
215 7 my $self = shift;
216  
217 # allow local arguments to override defaults set in constructor:
218 7 my %args = @_;
219 7 ▶ while (my ($key, $val) = each %args) {
Branch: 1 branch
BranchTrueFalseTotal
if (() = each %args) 5 7 12
220 5 ($self->{$key}, $args{$key}) = ($args{$key}, $self->{$key})
221 }
222  
223 # reset limit counter:
224 7 $self->{limit_left} = $self->{limit};
225  
226 7 my $rc;
227 7 ▶ unless ($self->{recursive} || ($self->{threshold} gt 1 && $self->{limit}) ) {
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{'recursive'} or $self->{'threshold'} gt 1 and $self->{'limit'}) { } 7 0 7
Condition16%1/6
$self->{"recursive"} or $self->{"threshold"} gt 1 and $self->{"limit"}
l!l&&r!l&&!r
700
$self->{"threshold"} gt 1 and $self->{"limit"}
!ll&&!rl&&r
000
MC/DC0%0/3
$self->{"recursive"} or $self->{"threshold"} gt 1 and $self->{"limit"}
$self->{"recursive"} $self->{"threshold"} gt 1 $self->{"limit"}
Truth table25%1/4
A or B and C
A= $self->{"recursive"}B= $self->{"threshold"} gt 1C= $self->{"limit"}
ABCresult
1XX1
0111
00X0
0100
228 0 $rc = $self->_traverse_linear;
229 } else {
230 7 $rc = $self->_traverse_recursive;
231 }
232  
233 # restore object defaults:
234 7 ▶ while (my ($key, $val) = each %args) {
Branch: 1 branch
BranchTrueFalseTotal
if (() = each %args) 5 7 12
235 5 ($self->{$key}, $args{$key}) = ($args{$key}, $self->{$key})
236 }
237  
238 7 return $rc;
239  
240 } # End of traverse.
241  
242 # ------------------------------------------------
243  
244 sub _traverse_linear
245 0 ▶{
Subroutine _traverse_linear: not called CC=17 SCAR=57.2
246 0 my $self = shift;
247  
248 0 $self->_do_query();
249  
250 0 my ($current, @order, @stack);
251  
252 0 my (%id_cols, %id_pnts);
253  
254 0 my $i = -1;
255 0 foreach my $aitem (@{ $self->{data} }) {
256 0 $i++;
257 0 ▶ if ( defined $aitem->{$self->{parent_id_column}} ) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $$aitem{$self->{"parent_id_column"}}) 0 0 0
258 0 push @{ $id_pnts{ $aitem->{$self->{parent_id_column}} } }, $aitem->{$self->{id_column}};
259 }
260 0 ▶ if ( defined $aitem->{$self->{id_column}} ) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $$aitem{$self->{"id_column"}}) 0 0 0
261 0 $id_cols{ $aitem->{$self->{id_column}} } = $i;
262 }
263 }
264  
265 0 my $level = 1;
266  
267 # this non-recursive algorithm requires the use of a stack in order
268 # to process each element. After each element is processed, it is
269 # removed from the stack and its children on the next level are
270 # added to the stack. Then it starts all over again until we run out
271 # of elements.
272  
273 0 push @order, $self->{start_id};
274 0 push @stack, 1;
275  
276 # $level starts out at 1. Every time we run out of items to process
277 # at the current level (if $levelFound == 0) $level is
278 # decremented. If we get to 0, we have run out of items to process,
279 # and can call it quits.
280  
281 0 my (@parent_id, @parent_name);
282  
283 0 ▶ while ($level) {
Branch: 1 branch
BranchTrueFalseTotal
if ($level) 0 0 0
284  
285 # search the stack for an item whose level matches $level.
286  
287 0 my $levelFound = 0;
288 0 my $i = -1;
289 0 foreach my $index (@stack) {
290 0 $i++;
291 0 ▶ if ($index == $level) {
Branch: 1 branch
BranchTrueFalseTotal
if ($index == $level) 0 0 0
292  
293 # if we have found something whose level is equal to $level,
294 # set the variable $current so we can refer to it later. Also,
295 # set the flag $levelFound
296  
297 0 $current = $order[$i];
298 0 $levelFound = 1;
299  
300 # since we've found record we don't need it on stack
301  
302 0 splice(@order,$i,1);
303 0 splice(@stack,$i,1);
304  
305 0 last;
306 }
307 }
308  
309 # if we found something at the current level, its id will be in
310 # $current, so let's process it. Otherwise, we drop through this,
311 # decrement $level, and if $level is not 0, start the process over
312 # again.
313  
314 0 ▶ if ($levelFound) {
Branch: 1 branch
BranchTrueFalseTotal
if ($levelFound) { } 0 0 0
315  
316 ######################################
317 #
318 # loop through the array of rows until we find the record with
319 # the id that matches $current. This is the id of the item we
320 # pulled off of $stack
321 #
322 ######################################
323 0 my $item;
324  
325 0 my $aryitem = $id_cols{ $current };
326 0 ▶ if (defined $aryitem) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $aryitem) 0 0 0
327  
328 ###############################
329 #
330 # the data column is used to get $item, which is the label
331 # in the tree diagram.
332 #
333 # The cartid property is the id of the shopping cart that
334 # was created in the new method
335 #
336 ###############################
337 0 $item = $self->{data}->[$aryitem]->{$self->{data_column}};
338  
339 ###############################
340 #
341 # if the calling program defined a target script, define
342 # this item on the tree as a hyperlink. include variables
343 # for id and cartid.
344 #
345 # Otherwise, just add the item as it is.
346 #
347 ###############################
348 $self->{method}->
349 ( item => $item,
350 level => $level,
351 id => $current,
352 parent_id => \@parent_id,
353 parent_name => \@parent_name )
354 0 ▶ if ($self->{method} && $level >= $self->{threshold});
Branch: 1 branch
BranchTrueFalseTotal
if $self->{"method"} and $level >= $self->{"threshold"} 0 0 0
Condition0%0/3
$self->{"method"} and $level >= $self->{"threshold"}
!ll&&!rl&&r
000
MC/DC0%0/2
$self->{"method"} and $level >= $self->{"threshold"}
$self->{"method"} $level >= $self->{"threshold"}
Truth table0%0/3
A and B
A= $self->{"method"}B= $level >= $self->{"threshold"}
ABresult
0X0
100
111
355  
356  
357 }
358  
359 #################################
360 #
361 # add all the children (if any) of the current item to the stack
362 #
363 ###############################
364  
365 0 my $aitem = $id_pnts{ $current };
366 0 ▶ if (defined $aitem) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $aitem) 0 0 0
367 0 foreach my $id ( @{ $aitem } ) {
368 0 push @stack, $level + 1;
369 0 push @order, $id;
370 }
371 }
372  
373 0 ▶ if ($item && $current) {
Branch: 1 branch
BranchTrueFalseTotal
if ($item and $current) 0 0 0
Condition0%0/3
$item and $current
!ll&&!rl&&r
000
MC/DC0%0/2
$item and $current
$item $current
Truth table0%0/3
A and B
A= $itemB= $current
ABresult
0X0
100
111
374 0 push @parent_id, $current;
375 0 push @parent_name, $item;
376 }
377 0 $level++ ;
378  
379 } else {
380  
381 0 my $current = pop @parent_id;
382 0 my $item = pop @parent_name;
383  
384 0 ▶ if ($self->{post_method} && ($level >= $self->{threshold}) )
Branch: 1 branch
BranchTrueFalseTotal
if ($self->{"post_method"} and $level >= $self->{"threshold"}) 0 0 0
Condition0%0/3
$self->{"post_method"} and $level >= $self->{"threshold"}
!ll&&!rl&&r
000
MC/DC0%0/2
$self->{"post_method"} and $level >= $self->{"threshold"}
$self->{"post_method"} $level >= $self->{"threshold"}
Truth table0%0/3
A and B
A= $self->{"post_method"}B= $level >= $self->{"threshold"}
ABresult
0X0
100
111
385 {
386 $self->{post_method}->
387 0 ( item => $item,
388 level => $level,
389 id => $current,
390 parent_id => \@parent_id,
391 parent_name => \@parent_name );
392 }
393  
394 0 $level--;
395 }
396  
397 }
398  
399 0 return 1;
400  
401 } # End of _traverse_linear.
402  
403 # ------------------------------------------------
404  
405 sub _traverse_recursive
406 7 ▶{
Subroutine _traverse_recursive: called CC=1 SCAR=0.0
407 7 my $self = shift;
408  
409 7 $self->_handle_node($self->{start_id}, undef, [], [], 1);
410  
411 } # End of _traverse_recursive.
412  
413 # ------------------------------------------------
414  
415 sub tree
416 0 ▶{
Subroutine tree: not called CC=2 SCAR=17.9
Pod undocumented
417 0 ▶ carp("tree() use is deprecated; use traverse() instead.\n")
Branch: 1 branch
BranchTrueFalseTotal
if $^W 0 0 0
418 if $^W;
419  
420 0 my $self = shift;
421 0 return $self->traverse(@_);
422  
423 } # End of tree.
424  
425 # ------------------------------------------------
426  
427 2 1;
428  
429 =pod
430  
431 =head1 NAME
432  
433 DBIx::Tree - Generate a tree from a self-referential database table
434  
435 =head1 Synopsis
436  
437 use DBIx::Tree;
438 # have DBIx::Tree build the necessary SQL from table & column names:
439 my $tree = new DBIx::Tree(connection => $dbh,
440 table => $table,
441 method => sub { disp_tree(@_) },
442 columns => [$id_col, $label_col, $parent_col],
443 start_id => $start_id);
444 $tree->traverse;
445  
446 # alternatively, use your own custom SQL statement
447  
448 my $sql = <<EOSQL;
449 SELECT nodes.id, labels.label, nodes.parent_id
450 FROM nodes
451 INNER JOIN labels
452 ON nodes.id = labels.node_id
453 WHERE labels.type = 'preferred label'
454 ORDER BY label ASC
455  
456 EOSQL
457  
458 my $tree = new DBIx::Tree(connection => $dbh,
459 sql => $sql,
460 method => sub { disp_tree(@_) },
461 columns => ['id', 'label', 'parent_id'],
462 start_id => $start_id);
463  
464 $tree->traverse;
465  
466 # or use an already prepared DBI statement handle:
467  
468 my $sth = $dbh->prepare($sql);
469 my $tree = new DBIx::Tree(connection => $dbh,
470 sth => $sth,
471 method => sub { disp_tree(@_) },
472 columns => ['id', 'label', 'parent_id'],
473 start_id => $start_id);
474  
475 $tree->traverse;
476  
477 =head1 Description
478  
479 When you've got one of those nasty self-referential tables that you
480 want to bust out into a tree, this is the module to check out.
481 Assuming there are no horribly broken nodes in your tree and (heaven
482 forbid) any circular references, this module will turn something like:
483  
484 food food_id parent_id
485 ================== ======= =========
486 Food 001 NULL
487 Beans and Nuts 002 001
488 Beans 003 002
489 Nuts 004 002
490 Black Beans 005 003
491 Pecans 006 004
492 Kidney Beans 007 003
493 Red Kidney Beans 008 007
494 Black Kidney Beans 009 007
495 Dairy 010 001
496 Beverages 011 010
497 Whole Milk 012 011
498 Skim Milk 013 011
499 Cheeses 014 010
500 Cheddar 015 014
501 Stilton 016 014
502 Swiss 017 014
503 Gouda 018 014
504 Muenster 019 014
505 Coffee Milk 020 011
506  
507 into:
508  
509 Food (001)
510 Dairy (010)
511 Beverages (011)
512 Coffee Milk (020)
513 Whole Milk (012)
514 Skim Milk (013)
515 Cheeses (014)
516 Cheddar (015)
517 Stilton (016)
518 Swiss (017)
519 Gouda (018)
520 Muenster (019)
521 Beans and Nuts (002)
522 Beans (003)
523 Black Beans (005)
524 Kidney Beans (007)
525 Red Kidney Beans (008)
526 Black Kidney Beans (009)
527 Nuts (004)
528 Pecans (006)
529  
530 See the examples/ directory for two Tk examples.
531  
532 =head1 Installation
533  
534 Install L<DBIx::Tree> as you would for any C<Perl> module:
535  
536 Run:
537  
538 cpanm DBIx::Tree
539  
540 Note: cpanm ships in App::cpanminus. See also App::perlbrew.
541  
542 or run:
543  
544 sudo cpan DBIx::Tree
545  
546 or unpack the distro, and then either:
547  
548 perl Build.PL
549 ./Build
550 ./Build test
551 sudo ./Build install
552  
553 or:
554  
555 perl Makefile.PL
556 make (or dmake or nmake)
557 make test
558 make install
559  
560 =head1 Constructor and Initialization
561  
562 =head2 Calling new()
563  
564 C<new()> is called as C<< my($obj) = DBIx::Tree -> new(k1 => v1, k2 => v2, ...) >>.
565  
566 It returns a new object of type C<DBIx::Tree>.
567  
568 Key-value pairs accepted in the parameter list:
569  
570 =over 4
571  
572 =item o columns => $ara_ref
573  
574 A reference to a list of three column names that can be found in the
575 table/result set:
576  
577 id_col: The name of the column containing the unique id.
578 label_col: The name of the column containing the textual data
579 of the row, like a name.
580 parent_col: The name of the column containing the id of the
581 row's parent.
582  
583 Optional additional columns; note that these will only be used in
584 queries built by DBIx::Tree from 'table' specifications - i.e. they
585 will not be used with 'sth'- or 'sql'-type query parameters
586 (presumably you can provide this functionality yourself when using one
587 of those query types).
588  
589 order_col: The name of a column to use for ordering the results;
590 defaults to the column name specified by label_col.
591 This column name does not need to exist in the result
592 set, but should exist in the table being queried.
593  
594 order_dir: An SQL directive specifying the directionality of the
595 ordering; for most databases this is either 'ASC' or
596 'DESC'. The default is an empty string, which leaves
597 the decision to the database (in most cases, this will
598 be ascending)
599  
600 =item o connection => $dbh
601  
602 A DBI connection handle. This parameter is always required. Earlier versions of this doc said it was
603 not necessary when using the $sth option, but in that case omitting it gets an error on prepare_cached.
604  
605 =item o limit => $integer
606  
607 Limit the number of rows using an SQL LIMIT clause - not all SQL
608 servers support this. This feature was supplied by Ilia Lobsanov
609 <ilia@lobsanov.com>
610  
611 =item o match_data => $string
612  
613 The value of a partial match to look for - if this is supplied, only
614 rows whose label_col matches (match_data + '%') this will be
615 selected. This feature was supplied by Ilia Lobsanov
616 <ilia@lobsanov.com>
617  
618 =item o method => $sub_name
619  
620 A callback method to be invoked each time a tree item is
621 encountered. This method will be given a hash as a parameter,
622 containing the following elements:
623  
624 item: the name of the item
625 level (1-n): the nesting level of the item.
626 id: the unique id of the item.
627 parent_id: an array ref containing the geneology of parent id's
628 for the current item
629 parent_name: an array ref containing the geneology of parent name's
630 for the current item
631  
632 If the 'threshold' parameter has been set (either via the new()
633 constructor or in the call to traverse()), the callback will only
634 occur if the tree item is 'threshold' or more levels deep in the
635 hierarchy.
636  
637 =item o post_method => $sub_name
638  
639 A callback method to be invoked after all the children of a tree item
640 have been encountered. This method will be given a hash as a
641 parameter, containing the following elements:
642  
643 item: the name of the item
644 level (0-n): the nesting level of the item.
645 id: the unique id of the item.
646 parent_id: an array ref containing the geneology of parent id's
647 for the current item
648 parent_name: an array ref containing the geneology of parent name's
649 for the current item
650  
651 If the 'threshold' parameter has been set (either via the new()
652 constructor or in the call to traverse()), the callback will only
653 occur if the tree item is 'threshold' or more levels deep in the
654 hierarchy.
655  
656 =item o recursive => $Boolean
657  
658 Specifies which of two methods DBIx::Tree will use to traverse the
659 tree. The default is non-recursively, which is efficient in that it
660 requires only a single database query, but it also loads the entire
661 tree into memory at once. The recursive method queries the database
662 repetitively, but has smaller memory requirements. The recursive
663 method will also be more efficient when an alternative start_id is
664 specified. Note that if you supply both a limit argument and a
665 threshold argument (implying that you want to see at most N records at
666 or below the given threshold), the recursive method will be used
667 automatically for efficiency.
668  
669 =item o sql => $sql_statement
670  
671 A string containing a custom "SELECT" SQL query statement that returns
672 the hierarchical data. Unnecessary if all of the id/label/parent
673 columns come from the same table specified by the 'table' parameter.
674 Use only when you need to bring in supplementary information from
675 other tables via custom "joins". Note that providing an 'sql'
676 argument will override any other 'table' specification.
677  
678 =item o start_id => $integer
679  
680 The unique id of the root item. Defaults to 1. May be overriden by
681 the 'start_id' argument to traverse().
682  
683 =item o sth => $db_sth
684  
685 A prepared (but not yet executed!) DBI statement handle. Unnecessary
686 if you plan to provide either a basic table name via 'table' or a
687 custom SQL statement via 'sql'. Note that providing an 'sth' argument
688 will override any other 'sql' or 'table' specification.
689  
690 =item o table => $table_name
691  
692 The database table containing the hierarchical data. Unnecessary if
693 you plan to provide either a custom SQL statement via the 'sql'
694 parameter or a prepared DBI statement handle via the 'sth' parameter.
695  
696 =item o threshold => $integer
697  
698 The level in the hierarchical tree at which to begin processing items.
699 The root of the tree is considered to be at level 1. May be overriden
700 by the 'threshold' argument to traverse().
701  
702 =back
703  
704 =head1 Methods
705  
706 =head2 new(%args)
707  
708 my $tree = new DBIx::Tree(connection => $dbh,
709 table => $table,
710 sql => $sql,
711 sth => $sth,
712 method => sub { disp_tree(@_) },
713 columns => [$id_col, $label_col, $parent_col],
714 start_id => $start_id,
715 threshold => $threshold,
716 match_data => $match_data,
717 limit => $limit
718 recursive => 1 || 0);
719  
720 =head2 traverse(%args)
721  
722 Begins a depth-first traversal of the hierarchical tree. The optional
723 %args hash provides locally overriding values for the identical
724 parameters set in the new() constructor.
725  
726 =head1 TODO
727  
728 Graceful handling of circular references.
729 Better docs.
730 Rewrite the algorithm.
731 Separate data acquisition from data formatting.
732  
733 =head1 See Also
734  
735 L<DBIx::Tree::Persist>.
736  
737 L<Parse::Taxonomy>.
738  
739 L<Tree>.
740  
741 L<Tree::Binary>.
742  
743 L<Tree::DAG_Node>. My favourite.
744  
745 L<Tree::DAG_Node::Persist>.
746  
747 L<Tree::Persist>.
748  
749 L<Tree::Simple>.
750  
751 L<Tree::Simple::Visitor::Factory>.
752  
753 =head1 Machine-Readable Change Log
754  
755 The file Changes was converted into Changelog.ini by L<Module::Metadata::Changes>.
756  
757 =head1 Repository
758  
759 L<https://github.com/ronsavage/DBIx-Tree>
760  
761 =head1 Support
762  
763 Bugs should be reported via the CPAN bug tracker at
764  
765 L<https://github.com/ronsavage/DBIx-Tree/issues>
766  
767 =head1 Authors
768  
769 Brian Jepson, bjepson@ids.net
770  
771 This module was inspired by the Expanding Hierarchies example that I
772 stumbled across in the Microsoft SQL Server Database Developer's
773 Companion section of the Microsoft SQL Server Programmer's Toolkit.
774  
775 Jan Mach <machj@ders.cz> contributed substantial performance
776 improvements, ordering handling for tree output, and other bug fixes.
777  
778 Aaron Mackey <amackey@virginia.edu> has continued active development
779 on the module based on Brian Jepson's version 0.91 release.
780  
781 Co-maintenance since V 1.91 is by Ron Savage <rsavage@cpan.org>.
782 Uses of 'I' in previous versions is not me, but will be hereafter.
783  
784 =cut
↑ Summary