[摘自 http://www.phpmag.net/itr/kolumnen/psecom,id,26,nodeid,207.html - 抱歉大家，这篇文章似乎没有直接的链接 ]

本周的焦点包我们来看一下DB_NestedSet。NestedSet是用来创建一个数据树并将它保存在一个关系数据库内。B_NestedSet和Tree的不同之外在于NestedSet着眼于使用关系数据库作为存储容器并使用不同的算法提升性能。

DB_NestedSet的下降趋势是由于它缺乏好的文档。API文档非常完整并试图通过所有的文档范例来创建一个简单的实例。因为DB_NestedSet没有提供任何的原始数据输出机制，在我们的例子里我选择HTML_Menu driver作为我们的例子。

在开始之前，你需要创建一个数据库，并创建一些表来保存我们的数据。下面的SQL是用来创建基本的表结构的：

DROP TABLE IF EXISTS `nested_set`;
CREATE TABLE `nested_set` (
  `id` int(10) unsigned NOT NULL default '0',
  `parent_id` int(10) unsigned NOT NULL default '0',
  `order_num` tinyint(4) unsigned NOT NULL default '0',
  `level` int(10) unsigned NOT NULL default '0',
  `left_id` int(10) unsigned NOT NULL default '0',
  `right_id` int(10) unsigned NOT NULL default '0',
  `name` varchar(60) NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `right` (`right_id`),
  KEY `left` (`left_id`),
  KEY `order` (`order_num`),
  KEY `level` (`level`),
  KEY `parent_id` (`parent_id`),
  KEY `right_left` (`id`,`parent_id`,`left_id`,`right_id`)
) TYPE=MyISAM;

~~
~~ Table structure for table `nested_set_locks`
~~

DROP TABLE IF EXISTS `nested_set_locks`;
CREATE TABLE `nested_set_locks` (
  `lockID` char(32) NOT NULL default '',
  `lockTable` char(32) NOT NULL default '',
  `lockStamp` int(11) NOT NULL default '0',
  PRIMARY KEY  (`lockID`,`lockTable`)
) TYPE=MyISAM COMMENT='Table locks for comments';

nested_set表包含我们所要创建的结构的数据。你可以任意命名其中的各个字段，但是我选择这些比较清楚的表名以便你能知道各个字段的含义。我们 要在代码中告诉DB_NestedSet使用这些字段。

在我们的例子中我们将为我们的站点创建一个简单的菜单，所有我们需要一个名为'标题’的行，用来保存和结点对应的页面的标题。执行下列的sql语句：

alter table nested_set add url varchar(255);

现在该是写代码的时候。我并不打算讲述所有的DB_NestedSet可以做的事情，希望代码中的注释能够帮你理解。我将使用HTML_Menu的一些代码来显示我们的实例，但不会详细解释。

require_once('HTML/Menu.php');
require_once('DB/NestedSet.php');
require_once('DB/NestedSet/Output.php');

// We start out by defining our database table specifications
$dsn = 'mysql://root:@localhost/nested';

// next we let the point our the table fields to the expected fields
$table = array(
    'id'        => 'id',
    'parent_id' => 'rootid',
    'left_id'   => 'l',
    'right_id'  => 'r',
    'order_num' => 'norder',
    'level'     => 'level',
    'name'      => 'name',
    'title'     => 'title'
);

// And then create a NestedSet instance using the DB driver with our Database and Table

$nestedSet =& DB_NestedSet::factory('DB', $dsn, $table); 

// Then set the names of our table, and how to sort the nodes.
// We have chosed to sort on 'name' but you can use any field from the table.
$nestedSet->setAttr(array(
        'node_table' => 'nested_set', 
        'lock_table' => 'nested_set_locks', 
        'secondarySort' => 'name'
        ];
        
// If a nodeID is specified we print out the name and title
if(isset($_GET['nodeID']]{
    $node_data = $nestedSet->pickNode($_GET['nodeID']);
    echo "<title>$node_data->name :: $node_data->title</title>";
}

// Now we define our data set. This only needs to be done one time.
// Once the information is in your database you will only need to touch it if
// You want to edit it.

// Create a parent item

$parent = $nestedSet->createRootNode(array('name'  => "Menu",
                                           'title' => "Example Menu"), false, true);

// The $parent var now holds the ID of the root node, we will use this when 
// createing subnodes

// Create a Subnode for our Italian recipes. We define the parent node first,
// and then capture the id of this node to use when creating subnodes from this
// node
$italian = $nestedSet->createSubNode($parent, array('name'  => 'Italian',
                                                    'title' => 'Great Food'];                                                      
$greek = $nestedSet->createSubNode($parent, array('name'  => 'Greek',
                                                  'title' => 'My Favorite'];
                                                      

$indian = $nestedSet->createSubNode($parent, array('name'  => 'Indian', 
                                                  'title' => 'The spicier the Better'];
      
$pizza = $nestedSet->createSubNode($italian, array('name' =>'Pizza'];
$nestedSet->createSubNode($pizza, array('name' =>'Pepperoni'];

$nestedSet->createSubNode($pizza, array('name' => 'Quattro Stagioni'];    
    
// Now create some leaf nodes of our indian recipes
$nestedSet->createSubNode($indian, array('name' =>'Butter Chicken'];
$nestedSet->createSubNode($indian, array('name' =>'Tandoori Chicken'];
$nestedSet->createSubNode($indian, array('name' =>'Masala Dosa'];

// And Greek recipes

$nestedSet->createSubNode($greek, array('name' => 'Moussaka'];

// Now our structure is created, if you look in the database you'll be able
// to see how the DB_NestedSet stores this information. The next step is to
// retrieve the data and present it in a pretty format. From now on we will 
// be working with HTML_Menu for the presentation.

// Fetch the entire tree into an array
$data = $nestedSet->getAllNodes(true);

// Create the links
foreach ($data as $id => $node) {
     $data[$id]['url'] = $_SERVER['PHP_SELF'].'?nodeID=' . $node['id'];
}

// Send HTML_Menu the structure, title and URL for the items in our tree
$params = array(
    'structure' => $data,
    'titleField' => 'name',
    'urlField' => 'url');

    
// Create the output driver object	

$output =& DB_NestedSet_Output::factory($params, 'Menu');

// Fetch the menu array
$structure = $output->returnStructure();

// We'll create the new HTML_Menu object, using the 'sitemap' display driver
$menu = & new HTML_Menu($structure, 'sitemap');

// Create the current URL and send it to HTML_Menu
$currentUrl = $_SERVER['PHP_SELF'].'?nodeID=' . $_GET['nodeID'];
$menu->forceCurrentUrl($currentUrl);

// Show the menu

$menu->show();