DBIx::Tree - Generate a tree from a self-referential database table
use DBIx::Tree; # have DBIx::Tree build the necessary SQL from table & column names: my $tree = new DBIx::Tree(connection => $dbh, table => $table, method => sub { disp_tree(@_) }, columns => [$id_col, $label_col, $parent_col], start_id => $start_id); $tree->traverse; # alternatively, use your own custom SQL statement my $sql = <<EOSQL; SELECT nodes.id, labels.label, nodes.parent_id FROM nodes INNER JOIN labels ON nodes.id = labels.node_id WHERE labels.type = 'preferred label' ORDER BY label ASC
EOSQL
my $tree = new DBIx::Tree(connection => $dbh, sql => $sql, method => sub { disp_tree(@_) }, columns => ['id', 'label', 'parent_id'], start_id => $start_id); $tree->traverse; # or use an already prepared DBI statement handle: my $sth = $dbh->prepare($sql); my $tree = new DBIx::Tree(connection => $dbh, sth => $sth, method => sub { disp_tree(@_) }, columns => ['id', 'label', 'parent_id'], start_id => $start_id); $tree->traverse;
When you've got one of those nasty self-referential tables that you want to bust out into a tree, this is the module to check out. Assuming there are no horribly broken nodes in your tree and (heaven forbid) any circular references, this module will turn something like:
food food_id parent_id ================== ======= ========= Food 001 NULL Beans and Nuts 002 001 Beans 003 002 Nuts 004 002 Black Beans 005 003 Pecans 006 004 Kidney Beans 007 003 Red Kidney Beans 008 007 Black Kidney Beans 009 007 Dairy 010 001 Beverages 011 010 Whole Milk 012 011 Skim Milk 013 011 Cheeses 014 010 Cheddar 015 014 Stilton 016 014 Swiss 017 014 Gouda 018 014 Muenster 019 014 Coffee Milk 020 011
into:
Food (001) Dairy (010) Beverages (011) Coffee Milk (020) Whole Milk (012) Skim Milk (013) Cheeses (014) Cheddar (015) Stilton (016) Swiss (017) Gouda (018) Muenster (019) Beans and Nuts (002) Beans (003) Black Beans (005) Kidney Beans (007) Red Kidney Beans (008) Black Kidney Beans (009) Nuts (004) Pecans (006)
See the examples/ directory for two Tk examples.
Install DBIx::Tree as you would for any Perl module:
Perl
Run:
cpanm DBIx::Tree Note: cpanm ships in App::cpanminus. See also App::perlbrew.
or run:
sudo cpan DBIx::Tree
or unpack the distro, and then either:
perl Build.PL ./Build ./Build test sudo ./Build install
or:
perl Makefile.PL make (or dmake or nmake) make test make install
new() is called as my($obj) = DBIx::Tree -> new(k1 => v1, k2 => v2, ...).
new()
my($obj) = DBIx::Tree -> new(k1 => v1, k2 => v2, ...)
It returns a new object of type DBIx::Tree.
DBIx::Tree
Key-value pairs accepted in the parameter list:
A reference to a list of three column names that can be found in the table/result set:
id_col: The name of the column containing the unique id. label_col: The name of the column containing the textual data of the row, like a name. parent_col: The name of the column containing the id of the row's parent.
Optional additional columns; note that these will only be used in queries built by DBIx::Tree from 'table' specifications - i.e. they will not be used with 'sth'- or 'sql'-type query parameters (presumably you can provide this functionality yourself when using one of those query types).
order_col: The name of a column to use for ordering the results; defaults to the column name specified by label_col. This column name does not need to exist in the result set, but should exist in the table being queried. order_dir: An SQL directive specifying the directionality of the ordering; for most databases this is either 'ASC' or 'DESC'. The default is an empty string, which leaves the decision to the database (in most cases, this will be ascending)
A DBI connection handle. This parameter is always required. Earlier versions of this doc said it was not necessary when using the $sth option, but in that case omitting it gets an error on prepare_cached.
Limit the number of rows using an SQL LIMIT clause - not all SQL servers support this. This feature was supplied by Ilia Lobsanov <ilia@lobsanov.com>
The value of a partial match to look for - if this is supplied, only rows whose label_col matches (match_data + '%') this will be selected. This feature was supplied by Ilia Lobsanov <ilia@lobsanov.com>
A callback method to be invoked each time a tree item is encountered. This method will be given a hash as a parameter, containing the following elements:
item: the name of the item level (1-n): the nesting level of the item. id: the unique id of the item. parent_id: an array ref containing the geneology of parent id's for the current item parent_name: an array ref containing the geneology of parent name's for the current item
If the 'threshold' parameter has been set (either via the new() constructor or in the call to traverse()), the callback will only occur if the tree item is 'threshold' or more levels deep in the hierarchy.
A callback method to be invoked after all the children of a tree item have been encountered. This method will be given a hash as a parameter, containing the following elements:
item: the name of the item level (0-n): the nesting level of the item. id: the unique id of the item. parent_id: an array ref containing the geneology of parent id's for the current item parent_name: an array ref containing the geneology of parent name's for the current item
Specifies which of two methods DBIx::Tree will use to traverse the tree. The default is non-recursively, which is efficient in that it requires only a single database query, but it also loads the entire tree into memory at once. The recursive method queries the database repetitively, but has smaller memory requirements. The recursive method will also be more efficient when an alternative start_id is specified. Note that if you supply both a limit argument and a threshold argument (implying that you want to see at most N records at or below the given threshold), the recursive method will be used automatically for efficiency.
A string containing a custom "SELECT" SQL query statement that returns the hierarchical data. Unnecessary if all of the id/label/parent columns come from the same table specified by the 'table' parameter. Use only when you need to bring in supplementary information from other tables via custom "joins". Note that providing an 'sql' argument will override any other 'table' specification.
The unique id of the root item. Defaults to 1. May be overriden by the 'start_id' argument to traverse().
A prepared (but not yet executed!) DBI statement handle. Unnecessary if you plan to provide either a basic table name via 'table' or a custom SQL statement via 'sql'. Note that providing an 'sth' argument will override any other 'sql' or 'table' specification.
The database table containing the hierarchical data. Unnecessary if you plan to provide either a custom SQL statement via the 'sql' parameter or a prepared DBI statement handle via the 'sth' parameter.
The level in the hierarchical tree at which to begin processing items. The root of the tree is considered to be at level 1. May be overriden by the 'threshold' argument to traverse().
my $tree = new DBIx::Tree(connection => $dbh, table => $table, sql => $sql, sth => $sth, method => sub { disp_tree(@_) }, columns => [$id_col, $label_col, $parent_col], start_id => $start_id, threshold => $threshold, match_data => $match_data, limit => $limit recursive => 1 || 0);
Begins a depth-first traversal of the hierarchical tree. The optional %args hash provides locally overriding values for the identical parameters set in the new() constructor.
Graceful handling of circular references. Better docs. Rewrite the algorithm. Separate data acquisition from data formatting.
DBIx::Tree::Persist.
Tree.
Tree::Binary.
Tree::DAG_Node. My favourite.
Tree::DAG_Node::Persist.
Tree::Persist.
Tree::Simple.
Tree::Simple::Visitor::Factory.
The file Changes was converted into Changelog.ini by Module::Metadata::Changes.
https://github.com/ronsavage/DBIx-Tree
Email the author, or log a bug on RT:
https://rt.cpan.org/Public/Dist/Display.html?Name=DBIx-Tree.
Brian Jepson, bjepson@ids.net
This module was inspired by the Expanding Hierarchies example that I stumbled across in the Microsoft SQL Server Database Developer's Companion section of the Microsoft SQL Server Programmer's Toolkit.
Jan Mach <machj@ders.cz> contributed substantial performance improvements, ordering handling for tree output, and other bug fixes.
Aaron Mackey <amackey@virginia.edu> has continued active development on the module based on Brian Jepson's version 0.91 release.
Co-maintenance since V 1.91 is by Ron Savage <rsavage@cpan.org>. Uses of 'I' in previous versions is not me, but will be hereafter.
To install DBIx::Tree, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::Tree
CPAN shell
perl -MCPAN -e shell install DBIx::Tree
For more information on module installation, please visit the detailed CPAN module installation guide.