Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages

  
 
  

home | help
DBIx::Tree(3)	      User Contributed Perl Documentation	 DBIx::Tree(3)

NAME
       DBIx::Tree - Generate a tree from a self-referential database table

Synopsis
	 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;

Description
       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.

Installation
       Install DBIx::Tree as you would for any "Perl" module:

       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

Constructor and	Initialization
   Calling new()
       "new()" is called as "my($obj) =	DBIx::Tree -> new(k1 =>	v1, k2 => v2,
       ...)".

       It returns a new	object of type "DBIx::Tree".

       Key-value pairs accepted	in the parameter list:

       o columns => $ara_ref
	   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)

       o connection => $dbh
	   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.

       o limit => $integer
	   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>

       o match_data => $string
	   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>

       o method	=> $sub_name
	   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.

       o post_method =>	$sub_name
	   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

	   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.

       o recursive => $Boolean
	   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.

       o sql =>	$sql_statement
	   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.

       o start_id => $integer
	   The unique id of the	root item.  Defaults to	1.  May	be overriden
	   by the 'start_id' argument to traverse().

       o sth =>	$db_sth
	   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.

       o table => $table_name
	   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.

       o threshold => $integer
	   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().

Methods
   new(%args)
	 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);

   traverse(%args)
       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.

TODO
       Graceful	handling of circular references.  Better docs.	Rewrite	the
       algorithm.  Separate data acquisition from data formatting.

See Also
       DBIx::Tree::Persist.

       Tree.

       Tree::Binary.

       Tree::DAG_Node. My favourite.

       Tree::DAG_Node::Persist.

       Tree::Persist.

       Tree::Simple.

       Tree::Simple::Visitor::Factory.

Machine-Readable Change	Log
       The file	Changes	was converted into Changelog.ini by
       Module::Metadata::Changes.

Repository
       <https://github.com/ronsavage/DBIx-Tree>

Support
       Email the author, or log	a bug on RT:

       <https://rt.cpan.org/Public/Dist/Display.html?Name=DBIx-Tree>.

Authors
       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.

perl v5.24.1			  2014-12-18			 DBIx::Tree(3)

NAME | Synopsis | Description | Installation | Constructor and Initialization | Methods | TODO | See Also | Machine-Readable Change Log | Repository | Support | Authors

Want to link to this manual page? Use this URL:
<https://www.freebsd.org/cgi/man.cgi?query=DBIx::Tree&sektion=3&manpath=FreeBSD+12.0-RELEASE+and+Ports>

home | help