Generic procedure to rebuild a MySQL Closure table

example_usage.sql

-- typical adjacent list table (parent/child)
CREATE TABLE `attribute` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `parent_id` bigint(20) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `parent_id_idx` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- typical closure structure
CREATE TABLE attribute_closure
(
id INT AUTO_INCREMENT NOT NULL,
ancestor BIGINT UNSIGNED NOT NULL,
descendant BIGINT UNSIGNED NOT NULL,
depth INT NOT NULL,
INDEX IDX_C39BD415B4465BB (ancestor),
INDEX IDX_C39BD4159A8FAD16 (descendant),
INDEX IDX_8AD2C4C5AE4E353B (depth),
UNIQUE INDEX IDX_1E108C591D07696B (ancestor, descendant),
PRIMARY KEY(id)
)
DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB;


-- closure table name, origin table, origin table's parent column
CALL populate_closure('attribute_closure', 'attribute', 'parent_id');

procedure.sql

DELIMITER ;;
CREATE PROCEDURE `populate_closure`(in closuretbl varchar(32),in sourcetbl varchar(32),in parent varchar(32))
BEGIN
  DECLARE distance int;
  SET @qry = CONCAT('
  TRUNCATE TABLE `',closuretbl,'`
      ');
  PREPARE s0 FROM @qry;
  EXECUTE s0;

  SET @distance = 0;
  -- seed closure with self-pairs (distance 0)
  SET @qry = CONCAT('
  INSERT INTO `',closuretbl,'` (ancestor, descendant, depth)
    SELECT id, id, @distance
      FROM `',sourcetbl,'`
      ');
  PREPARE s1 FROM @qry;
  EXECUTE s1;

  -- for each pair (root, leaf) in the closure,
  -- add (root, leaf->child) from the base table
  SET @qry = CONCAT('
  INSERT INTO `',closuretbl,'` (ancestor, descendant, depth)
    SELECT `',closuretbl,'`.ancestor, `',sourcetbl,'`.id, @distance
      FROM `',closuretbl,'`, `',sourcetbl,'`
        WHERE `',closuretbl,'`.descendant = `',sourcetbl,'`.`',parent,'`
        AND `',closuretbl,'`.depth = @distance - 1
    ');
  PREPARE s2 FROM @qry;

  REPEAT
    SET @distance = @distance + 1;
    EXECUTE s2;
  UNTIL ROW_COUNT() = 0 END REPEAT;
END;;
DELIMITER ;