tslominski

--
-- Structure de la table `ps_category`
--

DROP TABLE IF EXISTS `ps_category`;
CREATE TABLE IF NOT EXISTS `ps_category` (
  `id_category` int(10) unsigned NOT NULL auto_increment,
  `id_parent` int(10) unsigned NOT NULL,
  `id_shop_default` int(10) unsigned NOT NULL default '1',

tslominski

DELIMITER ;;

DROP PROCEDURE IF EXISTS EdgeListToNestedSet;;

CREATE PROCEDURE EdgeListToNestedSet( edgeTable CHAR(64), idCol CHAR(64), parentCol CHAR(64) )
BEGIN
  DECLARE maxrightedge, rows INTEGER DEFAULT 0;
  DECLARE trees, current INTEGER DEFAULT 1;
  DECLARE nextedge INTEGER DEFAULT 2;
  DECLARE msg CHAR(128);

tslominski

# REGENERATION of leafs

  CALL EdgeListToNestedSet( 'ps_category', 'id_category', 'id_parent');

	UPDATE
		ps_category c
	SET
		c.level_depth = (SELECT ABS(n.top)-2 FROM nestedsettree n WHERE n.nodeId = c.id_category),
		c.nleft = (SELECT n.leftedge -1 FROM nestedsettree n WHERE n.nodeId = c.id_category),
		c.nright = (SELECT n.rightedge - 1 FROM nestedsettree n WHERE n.nodeId = c.id_category);