#
# MySQL upgrade script for upgrade database from version 2.0.21 to 2.0.22
#

 

#-----------------------------------------------------------------------------
#-- hierarchy
#-----------------------------------------------------------------------------

ALTER TABLE hierarchy DROP COLUMN path;

#-----------------------------------------------------------------------------
#-- hierarchy_history
#-----------------------------------------------------------------------------

ALTER TABLE hierarchy_history DROP COLUMN path;

#-----------------------------------------------------------------------------
#-- hierarchy_hierarchy_history
#-----------------------------------------------------------------------------

ALTER TABLE hierarchy_hierarchy_history
      MODIFY COLUMN parent_hierarchy_history_id INTEGER NOT NULL,
      MODIFY COLUMN hierarchy_history_id INTEGER NOT NULL;

#-----------------------------------------------------------------------------
#-- hierarchy_hierarchy
#-----------------------------------------------------------------------------

ALTER TABLE hierarchy_hierarchy
      MODIFY COLUMN parent_hierarchy_id INTEGER NOT NULL,
      MODIFY COLUMN hierarchy_id INTEGER NOT NULL;

#-----------------------------------------------------------------------------
#-- hierarchy_i18n
#-----------------------------------------------------------------------------

ALTER TABLE hierarchy_i18n
    ADD KEY `name_index`(`name`);

#-----------------------------------------------------------------------------
#-- hierarchy_relative
#-----------------------------------------------------------------------------



CREATE TABLE `hierarchy_relative`
(
  `parent_hierarchy_id` INTEGER  NOT NULL,
  `child_hierarchy_id` INTEGER  NOT NULL,
  `distance` INTEGER default 0 NOT NULL,
  PRIMARY KEY (`parent_hierarchy_id`,`child_hierarchy_id`),
  CONSTRAINT `hierarchy_relative_FK_1`
    FOREIGN KEY (`parent_hierarchy_id`)
    REFERENCES `hierarchy` (`id`)
    ON DELETE CASCADE,
  INDEX `hierarchy_relative_FI_2` (`child_hierarchy_id`),
  CONSTRAINT `hierarchy_relative_FK_2`
    FOREIGN KEY (`child_hierarchy_id`)
    REFERENCES `hierarchy` (`id`)
    ON DELETE CASCADE
)Type=InnoDB;

INSERT INTO hierarchy_relative(parent_hierarchy_id, child_hierarchy_id, distance)
   SELECT hierarchy_id, id, 0 FROM hierarchy WHERE hierarchy_id IS NOT NULL;

INSERT INTO hierarchy_relative(parent_hierarchy_id, child_hierarchy_id, distance)
   SELECT parent_hierarchy_id, hierarchy_id, 0 FROM hierarchy_hierarchy;

delimiter //
CREATE PROCEDURE fill_hierarchy_relative()
BEGIN

  SET @x = 1;
  WHILE @x > 0 DO
    BEGIN
      INSERT INTO hierarchy_relative(parent_hierarchy_id, child_hierarchy_id, distance)
          SELECT DISTINCT parent_hierarchy_relative.parent_hierarchy_id, child_hierarchy_relative.child_hierarchy_id,
                 MIN(parent_hierarchy_relative.distance + child_hierarchy_relative.distance) + 1 AS distance
          FROM hierarchy_relative parent_hierarchy_relative JOIN hierarchy_relative child_hierarchy_relative
            ON (parent_hierarchy_relative.child_hierarchy_id = child_hierarchy_relative.parent_hierarchy_id)
          WHERE child_hierarchy_relative.child_hierarchy_id NOT IN (SELECT hierarchy_relative.child_hierarchy_id
              FROM hierarchy_relative WHERE hierarchy_relative.parent_hierarchy_id = parent_hierarchy_relative.parent_hierarchy_id)
          GROUP BY parent_hierarchy_relative.parent_hierarchy_id, child_hierarchy_relative.child_hierarchy_id;
      SET @x = row_count();
    END;
  END WHILE;
END //

delimiter ;

CALL fill_hierarchy_relative();

DROP PROCEDURE fill_hierarchy_relative;


#-----------------------------------------------------------------------------
#-- hierarchy_path
#-----------------------------------------------------------------------------

DROP TABLE hierarchy_path;



