NAME

DBIx::Path - resolve a path in an in-database tree or directed graph

SYNOPSIS

    use DBIx::Path;
    my $root=DBIx::Path->new(dbh => $dbh, table => 'treetable');
    my $node=$root->resolve(split '/', 'one/two/three')
		or die "Can't resolve path: $! at $DBIx::Path::FAILED[0]";
    print $_->name, "\t", $_->id, "\n" for $node->list;

DESCRIPTION

DBIx::Path is used to navigate through trees or directed graphs described by self-referential SQL database tables. It can be used to browse most trees where each row contains a unique ID and the ID of its parent, as well as graphs structured similarly but with multiple parents for a given node (presumably with the actual data for a given ID stored in a different table).

The module is designed to work with tables with at least three columns. One is called the parent ID (pid); one is called the name; and one is the ID. The combination of a particular pid and name must be unique in the entire table. The three columns can be of any type, but pid and id should probably be of the same type. The columns can have any name you want--you're not confined to "id", "pid", and "name". It is possible to have the id and name be the same column.

An example layout:

CREATE TABLE tree (
    pid INTEGER NOT NULL,
    name VARCHAR(16) NOT NULL,
    id INTEGER NOT NULL,
    PRIMARY KEY (pid, name)
)

In MySQL, you might want to use a layout with a TIMESTAMP column:

CREATE TABLE tree (
    pid INTEGER NOT NULL,
    name VARCHAR(16) NOT NULL,
    id INTEGER NOT NULL,
    mtime TIMESTAMP,
    PRIMARY KEY (pid, name)
)

In this table, name and ID are one:

CREATE TABLE folders (
    parent VARCHAR(16) NOT NULL,
    name VARCHAR(16) NOT NULL,
    PRIMARY KEY (pid, name)
)

The parent/child relationship is expressed through the pid field--each node contains its parent's ID in its pid field. DBIx::Path's primary purpose is to retrieve the ID for a particular pid/name combination, with the ability to descend through the tree via the resolve method.

An object of type DBIx::Path represents a node, and in this document it will always be referred to as a node.

Constructor

new

$root_node = DBIx::Path->new(%config);

Manually creates an object to represent the tree's root node. Note that there doesn't actually need to be an ID in the table for this node, although it doesn't hurt! The pid and name methods will always return undef for a node created through new, but nodes created through new otherwise behave identically to those created with get, set, add, or resolve.

The arguments comprise a hash (not a hash reference) with the following keys:

• dbh

  An already-opened DBI database handle. Required.

• table

  The name of the table containing the tree/graph being traversed. Required.

• id_column

  The name of the ID field. Optional; defaults to "id".

• pid_column

  The name of the pid field. Optional; defaults to "pid".

• name_column

  The name of the name field. Optional; defaults to "name".

• id

  The ID of the root. Optional; defaults to 0. Note that id cannot be undef (NULL), due to the SQL used to retrieve nodes.

The return value is a DBIx::Path object, which can then be used normally.

Methods

get

$node = $node->get( $name )

The get method retrieves the immediate child of $node named $name. It returns undef and sets $! to ENOENT if no child by that name exists.

add

$node = $node->add( $name, $id )

Adds $id as a child of $node named $name, then returns the newly-added node. This is done with a SQL INSERT statement. If the statement failed (because, for example, (pid, name) is a primary key and that particular combination already exists), returns undef and sets $! to EEXIST.

del

$ok = $node->del( $name )

Deletes the relation between $node and $name. Returns 1 if successful. If the SQL statement failed, returns undef and sets $! to ENOENT.

set

$node = $node->set( $name, $id )

Deletes the old relation between $node and $name, then creates a new one making $id the child of $node named $name. A simple wrapper around del and add. Return values are the same as add, but note that $! may still be set due to the result of del.

This method may be subject to race conditions. Patches to fix this problem in a cross-database manner are welcome.

resolve

$node = $node->resolve( @components )

The resolve method traverses the provided path; that is, it looks up the child of $node named $components[0], then looks up the child of the just-retrieved node named $components[1], and so on.

Return value is the same as get when a name anywhere in @components does not resolve.

After it is run, @DBIx::Path::RESOLVED will contain all components it resolved, and $DBIx::Path::PARENT will contain the node resolved from $RESOLVED[-1]. That is, after a successful run, @RESOLVED will be a copy of the @components list, and $PARENT will contain the parent of the returned node.

After a failed attempt to resolve, @RESOLVED will contain all components that resolved successfully. An additional variable, @DBIx::Path::FAILED, will contain the remaining components. $PARENT will contain the node which didn't have a child named $FAILED[0]. These variables are intended to augment the simple ENOENT placed in $! by get.

list

@nodes=$node->list()

Returns an unordered list of nodes which are children of the current node.

This method does not operate recursively; the DBIx::Tree module would be more appropriate for that purpose.

Accessors

id

$id = $node->id()

Returns the ID of the current node.

pid

$pid = $node->pid()

Returns the parent ID used to resolve the current node. Returns undef for the root node.

name

$name = $node->name()

Returns the name used to resolve the current node. Returns undef for the root node.

Diagnostics

DBIx::Path primarily communicates errors to the caller by returning undef and setting $! to an appropriate value. However, it does throw a few exceptions.

Invalid or missing %s

One of the required parameters to new (either the database handle or the table name) was omitted, or something that clearly wasn't a handle or table name (such as an undef value) was passed. Please check your code.

DBIx::Path->new: Couldn't prepare '%s' statement: %s

new prepares several SQL statements which are used by the other methods. This message indicates that the indicated statement was invalid. This could indicate a bad table name or invalid whatever_column settings; it could also mean that the SQL used by DBIx::Path isn't recognized by your DBD.

Check the parameters you're passing to DBIx::Path->new, then make sure the SQL at the indicated line number is valid for your server. The text after the second colon is the DBI error message.

BUGS AND ENHANCEMENTS

The implementation of set may be vulneurable to race conditions. Other than that, there are no known bugs at this time; however, I'm not that experienced with the DBI, so God only knows if I've missed something important.

Some enhancements I'm considering are:

• Hooks on the basic operations (get, add, del, possibly set and list). Subclassing may make this unnecessary, however.

• Methods that select all descendents of the current node and return them in various useful forms. (These would have to not curl up into a ball and cry in the face of circular references.)

• Reverse lookups--given two nodes, figure out how to get from one to the other.

Patches to implement these, or to fix bugs, are much appreciated; send them to <brentdax@cpan.org> and start the subject line with something like "[PATCH DBIx::Path]".

SEE ALSO

DBI, DBD::*, DBIx::Tree, File::Path

AUTHOR

Brent 'Dax' Royal-Gordon, <brentdax@cpan.org>

COPYRIGHT

Copyright 2005 Brent 'Dax' Royal-Gordon. All rights reserved.

This program is free software; it may be used, redistributed, and/or modified under the same terms as Perl itself.

cpanm DBIx::Path

perl -MCPAN -e shell
install DBIx::Path