The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.


 Tree::Numbered::DB - a tree that is stored in / tied to a DB table.


  use NumberedTree::DBTree;
  my $dbh = DBI->connect(...);

  # The easy way:
  my $tree = NumberedTree::DBTree->read($table, $dbh);

  # The hard way:
  my $tree = NumberedTree::DBTree->new(source_name => 'a_table', 
                                       source => $dbh);
  while (I aint sick of it) {
        $tree->append(Value => $newValue);


Tree::Numbered::DB is a child class of Tree::Numbered that supplies database tying (every change is immediately reflected in the database) and reading using tables that are built to store a tree (the structure is described below). It's basically the same as Tree::Numbered except for some methods. These, and arguments changes for inherited methods, are also described below. For the rest, please refer to the documentation for Tree::Numbered.

Tree::Numbered::DB allows you to change the relations between the table and the tree, by adding and deleting fields on runtime, thus giving you a lot of flexibility in working with big tables. The mechanism for that is described below in short. A lot about dealing with fields can be found in the docs for Tree::Numbered.

To see a working example, see in the distribution directory.


A table used by this module must have at least 2 columns: the serial number column (by default 'serial') and the parent column (default - 'parent'). There is also a default field column for the field Value ('name') if you want this field to be created. If the default names don't suit you, don't worry - you can supply different names to the constructors.

Serial numbers start from any number greater than zero and must be in an auto-incrementing field. Parent numbers of course are the serial numbers of the parent for each node - the root node always takes parent number 0.

 Example SQL statement to build the table (tested on MySQL):  
 create table places (serial int auto_increment primary key, 
                                  parent int not null, 
                                  name varchar(20));


To create a simple menu with one field as value, the defaults will do. However, if you are looking for something more complex, or if you have an existing table and you can't (or won't) change its collumn names, you'll have to tell the module which fields you want, and which column maps to what field. There are two ways of doing this:

  • Supplying the field name and field value to the constructor (see below).

  • Using the method addField.

Whenever a field is added in any way, the module tries to resolve its mapping in the following order (low precedence first):

  Existing mapping (e.g. from a deleted field, or default) -->
  Mapping for the same field found in the parent of the node -->
  Mapping suplied as an argument.

If no proper mapping can be found, the method that attempted to create the field will fail.

When deleting a field, you have the option of keeping its mapping in memory, allowing you to remount that field easily.


This section only describes methods that are not the same as in Tree::Numbered. Mandatory arguments are marked.


There are two of them:

new (source => source, source_name => source_name, somefield => value, somefield_col => mapping, ...)

creates a new tree object that uses an empty table named <source_name> using a DBI database handle supplied via the source argument. for each field you want to create, you must give a mapping key in the arguments hash. The key is the name of the field postfixed with _col. The value is the name of the collumn to map to that field.

For each mapping key specified, a field will be created, even if you don't specify a starting value.

There are also two special mapping keys you can give to replace module defaults: 'serial_col' will change the mapping for the serial number column from the default to whatever you give it, and 'parent_col' will do the same for the collumn that holds the parent numbers.

Note that you should not add nodes to an existing tree using this method. Instead, use append.


creates a new tree object from a table named SOURCE_NAME that contains tree data as specified above, using a DB handle given in SOURCE. The optional MAP argument takes a reference to a hash of mappings, as described in new. If you do not supply this, the defaults will be used (including the creation of the Value field). As in new, you can use this argument to replace module default mappings.

The data read from the DB will be ordered by the serial numbers.

Overriden and new methods in this class.

Four methods are added to this class:


Activates the truncate SQL command, effectively deleting all data in a table, but not the table itself. This also disposes of the tree object, so you'll have to build a new one after using this method.


Removes information that is specific to this class and re-blesses the entire tree into the parent class. Does not lose fields! Use this method if you just want to read the tree, then do stuff that's not related to the DB.

get/setMapping (field, map)

Either sets the mapping of a field to whatever you give it or gets the current value of the mapping for a field (in that case ther's only one argument, the field name).

Overriden methods that changed arguments:

addField(name, value, mapping)

Adds a field to its node only. New child nodes will inherit it, but old child nodes will not automatically add that field to themselves. The mapping argument is optional if the module can find the mapping using the search order described above, in 'Mapping fields to data attributes.


The following is a categorized list of all available meyhods, for quick reference. Methods that do not appear in the source of this module are marked:

Object lifecycle:

new, read, delete, *append, truncate, revert.

Iterating and managing children:

*nextNode, *reset, *savePlace, *restorePlace, *childCount, *getSubTree, *follow

Fields and mappings

addField, removeField, setField, setFields, *getField, *getFields, *hasField, addMapping, removeMapping


 Please report through CPAN: 
 < >
 or send mail to <> 
 For sugestions, questions and such, email me directly.



The base class.

Tree::Numbered::Tools (by Johan Kuuse)

Tools for easy handling of the tree.

Javascript::Menu, HTML::Widget::SideBar

Hierarchical widgets for web pages that are Tree::Numbered and use Tree::Numbered::DB optionally.


Yosef Meller, <>


Copyright 2003 by Yosef Meller

This module is free software; you can redistribute it and/or modify it under the same terms as Perl itself.