DBIx::Tree - Perl module for generating a tree from a self-referential table

NAME

DBIx::Tree - Perl module for generating a tree from a self-referential table

SYNOPSIS

  use DBIx::Tree;
  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->do_query;
  $tree->tree;

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)

There are examples in the examples directory - one plain text example, and two Tk examples.

Constructor arguments

  my $tree = new DBIx::Tree(connection => $dbh, 
                            table      => $table,
                            method     => sub { disp_tree(@_) },
                            columns    => [$id_col, $label_col, $parent_col],
                            start_id   => $start_id,
                            match_data => $match_data,
                            limit      => $limit);

connection

A DBI connection handle.

table

The database table containing the hierarchical data.

method

A callback method to be invoked each time a tree item is encountered. This method will be given a hashtable 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.

columns:

A list of three columns from the table:

  id_col:     The unique id.
  label_col:  The textual data of the row, like a name.
  parent_col: The id of the row's parent.

start_id

The unique id of the root item.

match_data

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>

limit

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>

TODO

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

AUTHOR

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.