DBIx::OO::Tree 0.0.4

DBIx::OO::Tree is a Perl module to manipulate hierarchical data using the "nested sets" model.

SYNOPSYS

CREATE TABLE Categories (
id INTEGER UNSIGNED AUTO_INCREMENT PRIMARY KEY,
label VARCHAR(255),

-- these columns are required by DBIx::OO::Tree
parent INTEGER UNSIGNED,
lft INTEGER UNSIGNED NOT NULL,
rgt INTEGER UNSIGNED NOT NULL,
mvg TINYINT DEFAULT 0,

INDEX(lft),
INDEX(rgt),
INDEX(mvg),
INDEX(parent)
);

* * *

package Category;
use base DBIx::OO;
use DBIx::OO::Tree;

__PACKAGE__->table(Categories);
__PACKAGE__->columns(P => [ id ],
E => [ label, parent ]);

# note its not necessary to declare lft, rgt, mvg or parent. We
# declare parent simply because it might be useful, but
# DBIx::OO:Tree works with low-level SQL therefore it doesnt
# require that the DBIx::OO object has these fields.

# the code below creates the structure presented in [1]

my $electronics = Category->tree_append({ label => electronics });
my $tvs = $electronics->tree_append({ label => televisions });
my $tube = $tvs->tree_append({ label => tube });
my $plasma = $tvs->tree_append({ label => plasma });
my $lcd = $plasma->tree_insert_before({ label => lcd });
my $portable = $tvs->tree_insert_after({ label => portable electronics });
my $mp3 = $portable->tree_append({ label => mp3 players });
my $flash = $mp3->tree_append({ label => flash });
my $cds = $portable->tree_append({ label => cd players });
my $radios = Category->tree_append($portable->id,
{ label => 2 way radios });

# fetch and display a subtree

my $data = $electronics->tree_get_subtree({
fields => [qw( label lft rgt parent )]
});
my $levels = Category->tree_compute_levels($data);

foreach my $i (@$data) {
print x $levels->{$i->{id}}, $i->{label}, "n";
}

## or, create DBIx::OO objects from returned data:

my $array = Category->init_from_data($data);
print join("n", (map { x $levels->{$_->id} . $_->label } @$array));

# display path info

my $data = $flash->tree_get_path;
print join("n", (map { $_->{label} } @$data));

# move nodes around

$mp3->tree_reparent($lcd->id);
$tvs->tree_reparent($portable->id);
$cds->tree_reparent(undef);

$plasma->tree_move_before($tube->id);
$portable->tree_move_before($electronics->id);

# delete nodes

$lcd->tree_delete;

This module is a complement to DBIx::OO to facilitate storing trees in database using the "nested sets model", presented in [1]. Its main ambition is to be extremely fast at retrieving data (sacrificing for this the performance of UPDATE-s, INSERT-s or DELETE-s). Currently this module requires you to have these columns in the table:

- id: primary key (integer)
- parent: integer, references the parent node (NULL for root nodes)
- lft, rgt: store the node position
- mvg: used only when moving nodes

"parent" and "mvg" are not esentially required by the nested sets model as presented in [1], but they are necessary for this module to work. In particular, "mvg" is only required by functions that move nodes, such as tree_reparent(). If you dont want to move nodes around you can omit "mvg".

Retrieval functions should be very fast (one SQL executed). To further promote speed, they dont return DBIx::OO blessed objects, but an array of hashes instead. Its easy to create DBIx::OO objects from these, if required, by calling DBIx::OO->init_from_data() (see DBIx::OO for more information).

Insert/delete/move functions, however, need to ensure the tree integrity. Heres what happens currently:

- tree_append, tree_insert_before, tree_insert_after -- these execute
one SELECT and two UPDATE-s (that potentially could affect a lot of
rows).

- tree_delete: execute one SELECT, one DELETE and two UPDATE-s.

- tree_reparent -- executes 2 SELECT-s and 7 UPDATE-s. I know, this
sounds horrible--if you have better ideas Id love to hear them.