Trees and stored procuders in MySQL 5

Recently I had to deal with a project having everything based on a tree model stored in DB. I researched for methods for managing trees/hierarchies in DB and found Joe Celko's article and several more based on the same approach of nested sets.

As I very much believe in a data integrity I reached for MySQL 5 new feature of stored routines. Here is a listing which may help you to get your hierarchies up and running in a snap.
The table.
This table can be extended with further fields. The 'node_add' stored procedure adds node to the tree and returns back the id of a newly inserted record so you can update the row with further data.

CREATE TABLE `tree` (
`node_id` int(10) unsigned NOT NULL auto_increment,
`parent_id` int(10) unsigned NOT NULL,
`lft` int(10) unsigned NOT NULL,
`rgt` int(10) unsigned NOT NULL,
`name` varchar(255) character set latin1 NOT NULL default '',
PRIMARY KEY  (`node_id`),
UNIQUE KEY `name` (`name`,`parent_id`),
KEY `parent_id` (`parent_id`),
KEY `lft` (`lft`),
KEY `rgt` (`rgt`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8'

Stored procedure for adding a node to the tree. As mentioned above it returns back an id of newly inserted row, which can be used to populate the rest of the row.

DELIMITER $$

DROP PROCEDURE IF EXISTS `node_add` $$
CREATE PROCEDURE `node_add`(IN i_parent_id INT, IN i_name VARCHAR(255))
BEGIN

DECLARE a_rgt INT;

IF i_parent_id = 0 THEN
SELECT rgt INTO a_rgt FROM tree WHERE parent_id = 0 ORDER BY rgt DESC LIMIT 1;

IF a_rgt IS NULL THEN
  # The first item in a table
  SET a_rgt = 0;
END IF;

INSERT INTO tree SET parent_id = 0, lft = a_rgt + 1, rgt = a_rgt + 2, title = i_title, name = i_name;

ELSE

# Right value of parent becomes left value of new item
SELECT rgt INTO a_rgt FROM tree WHERE node_id = i_parent_id;

IF a_rgt IS NOT NULL THEN

  # Make a room
  UPDATE tree SET lft = lft + 2 WHERE lft > a_rgt;
  UPDATE tree SET rgt = rgt + 2 WHERE rgt > a_rgt - 1;

  # Insert new item
  INSERT INTO tree SET parent_id = i_parent_id, lft = a_rgt, rgt = a_rgt + 1, name = i_name;

  # Return an ID of newly inserted item
  SELECT @new_id := last_insert_id() AS node_id;

END IF;

END IF;

END $$

DELIMITER ;

Stored procedure for deleting a node.

DELIMITER $$

DROP PROCEDURE IF EXISTS `node_delete` $$
CREATE PROCEDURE `node_delete`(IN i_node_id INT)
BEGIN

DECLARE a_lft, a_rgt, a_width INT;

SELECT lft, rgt, rgt - lft + 1
INTO a_lft, a_rgt, a_width
FROM tree
WHERE node_id = i_node_id;

IF a_lft IS NOT NULL THEN
# Erase node
DELETE FROM tree WHERE lft BETWEEN a_lft AND a_rgt;

# Close the gap
UPDATE tree SET lft = lft - a_width WHERE lft > a_rgt;
UPDATE tree SET rgt = rgt - a_width WHERE rgt > a_rgt;
END IF;

END $$

DELIMITER ;

Stored procedure for moving a node in a tree. This procedure takes an id of a node which should be moved and an id of a node after which we want to place the first node. If the first node contains further ancestors procedure moves them with the node.

DELIMITER $$

DROP PROCEDURE IF EXISTS `node_move` $$
CREATE PROCEDURE `node_move`(IN i_node_id INT, IN i_previous_id INT)
BEGIN

DECLARE new_lft, new_parent_id, width, c_lft, c_rgt, diff INT;

IF i_previous_id = 0 THEN
SET new_lft = 1;
SET new_parent_id = 0;
ELSE
SELECT rgt + 1, parent_id INTO new_lft, new_parent_id FROM tree WHERE node_id = i_previous_id;
END IF;

SELECT rgt - lft + 1 INTO width FROM tree WHERE node_id = i_node_id;

# Make a room
UPDATE tree SET lft = lft + width WHERE lft > new_lft - 1;
UPDATE tree SET rgt = rgt + width WHERE rgt > new_lft - 1;

# Put subtree to a created space
SELECT lft, rgt, @diff := lft - new_lft INTO c_lft, c_rgt, diff FROM tree WHERE node_id = i_node_id;
UPDATE tree SET lft = lft - diff, rgt = rgt - diff WHERE lft BETWEEN c_lft AND c_rgt;
UPDATE tree SET parent_id = new_parent_id WHERE node_id = i_node_id;

# Close the gap
UPDATE tree SET lft = lft - width WHERE lft > c_lft;
UPDATE tree SET rgt = rgt - width WHERE rgt > c_rgt;

END $$

DELIMITER ;