Разработка системы обновления ПО холдинга часть 1. Вырастить дерево в SQL.

Каждую пятницу у нас на работе делают план отделу ПО. К счастью, эта участь в основном проходит мимо меня, т.к. на мне висит большая часть технической поддержки. Но вот в одну из таких пятниц очередь добралась и до меня.

Итак задание:

1)Разработать систему хранения информации для софта и его привязки к торговым объектам. При этом,
торговые объекты входят в юрлицо, а юрлицо может входить в холдинг. Получается дерево с нижним уровнем «торговый объект» и верхним «холдинг», софт же может привязываться на любом уровне этого дерева. При этом торговые объекты могут перемещаться между юрлицами, юрлица между холдингами(или вовсе быть вне холдингов) и т.д.
2)В связи с отсутствием данных для всей этой причуды, разработать web-интерфейс для операций добавления,
удаления и редактирования уровней дерева. Ну и для добавления, удаления и привязки софта к объектам дерева.
3)Выгружать полученное дерево в MongoDB согласно заданным правилам.

Довольно таки нетривиальная задача для веб-проекта, не смотря на то что звучит не сложно. Изначально я начал описывать дерево и его поведение в php. Не смотря на то что БД MySQL стоит на той же виртуальной машине, что и веб-сервер, кол-во запросов к базе меня начало смущать. Да и доработке такой скрипт очень тяжело подвергался, если его кроме меня кто дорабатывать возьмется. В итоге через несколько часов было решено отказаться от дурной идеи и начать с более глубокого изучения SQL. В интернете полно «примеров» сложных запросов, но они довольно тривиальны.
Уже зная принцип работы триггеров в MSSQL я хотел построить дерево на них, но оказалось в MySQL триггеры не поддерживают Dynamic SQL. Проще говоря, я не могу засунуть содержимое ячейки в переменную, а потом добавить столбец с именем соответствующему этой переменной в другую таблицу и т.п. С идеей триггеров пришлось распрощаться, остался вариант с процедурами и таблицами связей.

Итак было решено организовать три таблицы — три уровня дерева: холдинг, юрлицо, торговый объект. А также две таблицы связей, для связки уровней дерева. Дополнительно таблица с софтом и таблица связки объектов с их uid. Всю логику работы дерева изложить в процедурах, а ограничения задать в индексах таблиц.

В итоге получил таблица холдингов:

CREATE TABLE `holding` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `soft` varchar(255) NOT NULL DEFAULT '',
  `data` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `name` (`name`,`soft`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;

Таблица юрлиц:

CREATE TABLE `jurlico` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `soft` varchar(255) NOT NULL DEFAULT '',
  `data` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `name` (`name`,`soft`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8;

Таблица объектов:

CREATE TABLE `object` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `soft` varchar(255) DEFAULT NULL,
  `data` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `name` (`name`,`soft`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8;

Таблица связи холдинг-юрлицо:

CREATE TABLE `link_holding` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `holding` varchar(255) DEFAULT NULL,
  `jurlico` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `jurlico` (`jurlico`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

Таблица связи юрлицо-объект:

CREATE TABLE `link_jurlico` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `jurlico` varchar(255) DEFAULT NULL,
  `object` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `object` (`object`)
) ENGINE=InnoDB AUTO_INCREMENT=75 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

Таблица связи объект-uid:

CREATE TABLE `link_object` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `object` varchar(255) DEFAULT NULL,
  `guid` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `object` (`object`),
  UNIQUE KEY `guid` (`guid`)
) ENGINE=InnoDB AUTO_INCREMENT=75 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

Таблица с софтом(изначально столбца hash не было, но потом решил его добавить. это повлияло и на разработку):

CREATE TABLE `software` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `path` varchar(255) DEFAULT NULL,
  `localpath` varchar(255) DEFAULT NULL,
  `hash` varchar(255) DEFAULT NULL,
  `data` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=utf8;

Таблица uid-ов холдингов и юрлиц:

CREATE TABLE `guid_for_jur_hold` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `holding` varchar(255) DEFAULT NULL,
  `jurlico` varchar(255) DEFAULT NULL,
  `guid` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `guid` (`guid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

Первую процедуру, которую написал — это добавление софта, она пожалуй и самая простая. Позже при выгрузке понял, что нужно фиксировать удаленный софт, поэтому добавил еще одну таблицу для этого(тут ничего особенного):

CREATE TABLE `remove_soft` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

Ну и сама процедура, на которой я разобрался с принципом работы процедур, выводом необходимых параметров во временную таблицу, работе с переменными и проверку на существование индекса в таблице. С IF знакомиться не пришлось, т.к. тут все языки схожи. Ну и простейшие запросы я и до этого знал. Собственно процедура:

CREATE DEFINER=`***`@`*******` PROCEDURE `proc_software`(v_command varchar(255), v_name varchar(255), v_path varchar(255), v_localpath varchar(255))
BEGIN
  IF v_command = 'add' THEN
    IF !EXISTS(SELECT `name` FROM `software` WHERE `name`=v_name ORDER BY Id DESC LIMIT 1) THEN
      INSERT INTO `software` (`name`, `path`, `localpath`) VALUES (v_name, v_path, v_localpath);
      SET @Answer = concat(v_name, ' добавлено в список ПО!');
      SET @Ctrl = concat('add');
    ELSE
      SET @Answer = concat(v_name, ' уже существует в списоке ПО!');
      SET @Ctrl = concat('removeandadd');
    END IF;
  ELSEIF v_command = 'remove' THEN
    IF EXISTS(SELECT `name` FROM `software` WHERE `name`=v_name ORDER BY Id DESC LIMIT 1) THEN
      DELETE FROM `software` WHERE `name` = v_name;
      DELETE FROM `holding` WHERE `soft` = v_name;
      DELETE FROM `jurlico` WHERE `soft` = v_name;
      DELETE FROM `object` WHERE `soft` = v_name;
      INSERT INTO `remove_soft` (`name`) VALUES (v_name);
      SET @Answer = concat(v_name, ' удалено из списка ПО!');
      SET @Ctrl = concat('remove');
    ELSE
      SET @Answer = concat(v_name, ' отсутствует в списоке ПО!');
      SET @Ctrl = concat('error');
    END IF;
  ELSEIF v_command = 'update' THEN
    IF EXISTS(SELECT `name` FROM `software` WHERE `name`=v_name ORDER BY Id DESC LIMIT 1) THEN
      UPDATE `software` SET `name` = v_name, `path` = v_path, `localpath` = v_localpath where `name` like v_name;
      SET @Answer = concat(v_name, ' обновлено в списке ПО!');
      SET @Ctrl = concat('update');
    ELSE
      SET @Answer = concat(v_name, ' отсутствует в списоке ПО!');
      SET @Ctrl = concat('error');
    END IF;
  END IF;
  SELECT @Answer, @Ctrl;
END;

Вышеуказанной процедурой можно добавлять, удалять и обновлять софт. Что ж перейдем к объектам. Вот тут я узнал про оператор IN в MySQL, очень полезный, я скажу, оператор. Позволяет в запросе использовать выборку полученную другим запросом(SELECT). Вот собственно процедура добавления, удаления и обновления объекта:

CREATE DEFINER=`***`@`******` PROCEDURE `proc_object`(v_command varchar(255), v_object varchar(255), v_guid varchar(255), v_jurlico varchar(255))
BEGIN
  IF v_command = 'add' THEN
    IF !EXISTS(SELECT `object` FROM `link_jurlico` WHERE `object` LIKE v_object) THEN
      IF !EXISTS(SELECT `object` FROM `link_object` WHERE `guid` LIKE v_guid) THEN
        INSERT INTO `link_jurlico` SET `object` = v_object, `jurlico` = v_jurlico;
        INSERT INTO `link_object` SET `object` = v_object, `guid` = v_guid;
        SET @Answer = concat(v_object, ' добавлено в список объектов!');
      ELSE
        SET @Answer = concat(v_guid, ' уже принадлежит объекту!');
      END IF;
    ELSE
      SET @Answer = concat(v_object, ' уже существует в списоке объектов!');
    END IF;
  ELSEIF v_command = 'remove' THEN
    IF EXISTS(SELECT `object` FROM `link_jurlico` WHERE `object` LIKE v_object) THEN
      DELETE FROM `object` WHERE `name` = v_object;
    	DELETE FROM `link_object` WHERE `object` = v_object;
      DELETE FROM `link_jurlico` WHERE `object` = v_object;
      SET @Answer = concat(v_object, ' удалено из списка объектов!');
    ELSE
      SET @Answer = concat(v_object, ' отсутствует в списке объектов!');
    END IF;
  ELSEIF v_command = 'update' THEN
    IF EXISTS(SELECT `object` FROM `link_jurlico` WHERE `object` LIKE v_object) THEN
      IF !ISNULL(v_guid) THEN
        UPDATE `link_object` SET `guid` = v_guid where `object` like v_object;
        SET @Answer = concat(v_object, ' изменен GUID!');
      END IF;
      IF !ISNULL(v_jurlico) THEN
        UPDATE `link_jurlico` SET `jurlico` = v_jurlico where `object` like v_object;
        DELETE FROM `object` WHERE `name` LIKE v_object AND `soft` IN (SELECT `soft` FROM `jurlico` WHERE `name` LIKE v_jurlico);
        SET @Answer = concat(@Answer, v_object, ' изменено юрлицо!');
      END IF;
    ELSE
      SET @Answer = concat(v_object, ' отсутствует в списке объектов!');
    END IF;
  END IF;
  SELECT @Answer;
END;

Т.к. юрлица у нас существуют в связке с объектом и создаются процедурой выше, осталось создать холдинги. Что ж еще больше вложенных запросов, отточим навык:

CREATE DEFINER=`****`@`**********` PROCEDURE `proc_holding`(v_command varchar(255), v_holding varchar(255), v_jurlico varchar(255))
BEGIN
  IF v_command = 'add' THEN
    IF EXISTS(SELECT `jurlico` FROM `link_jurlico` WHERE `jurlico`=v_jurlico ORDER BY Id DESC LIMIT 1) THEN
      IF (!EXISTS(SELECT `holding` FROM `link_holding` WHERE `jurlico`=v_jurlico AND `holding`=v_holding ORDER BY Id DESC LIMIT 1) AND (v_holding <> '')) THEN
        IF !EXISTS(SELECT `holding` FROM `link_holding` WHERE `jurlico`=v_jurlico ORDER BY Id DESC LIMIT 1) THEN
          INSERT INTO `link_holding` (`holding`, `jurlico`) VALUES (v_holding, v_jurlico);
          DELETE FROM `jurlico` WHERE `name` LIKE v_jurlico AND `soft` IN (SELECT `soft` FROM `holding` WHERE `name` LIKE v_holding);
          DELETE FROM `object` WHERE `name` IN (SELECT `object` FROM `link_jurlico` WHERE `jurlico` LIKE v_jurlico) AND `soft` IN (SELECT `soft` FROM `holding` WHERE `name` LIKE v_holding);
          SET @Answer = concat(v_jurlico, ' добавлен в список холдинга ', v_holding, '!');
        ELSE
          UPDATE `link_holding` SET `holding` = v_holding where `jurlico` like v_jurlico;
          DELETE FROM `jurlico` WHERE `name` LIKE v_jurlico AND `soft` IN (SELECT `soft` FROM `holding` WHERE `name` LIKE v_holding);
          DELETE FROM `object` WHERE `name` IN (SELECT `object` FROM `link_jurlico` WHERE `jurlico` LIKE v_jurlico) AND `soft` IN (SELECT `soft` FROM `holding` WHERE `name` LIKE v_holding);
          SET @Answer = concat(v_jurlico, ' добавлен в список холдинга ', v_holding, '!');
        END IF;
      ELSE
        IF (v_holding <> '') THEN
          SET @Answer = concat(v_jurlico, ' уже назначено холдингу ', v_holding, '!');
        ELSE
          DELETE FROM `link_holding` WHERE `jurlico` = v_jurlico;
          SET @Answer = concat(v_jurlico, ' удалено из холдинга!');
        END IF;
      END IF;
    ELSE
      SET @Answer = concat('Не существует ни одного объекта с юрлицом ', v_jurlico, '!');
    END IF;
  ELSEIF v_command = 'remove' THEN
    IF EXISTS(SELECT `holding` FROM `link_holding` WHERE `holding`=v_holding ORDER BY Id DESC LIMIT 1) THEN
      DELETE FROM `link_holding` WHERE `holding` = v_holding;
      SET @Answer = concat(v_jurlico, ' удален из холдинга ', v_holding, '!');
#       DELETE FROM `holding` WHERE `name` = v_holding;
    ELSE
      SET @Answer = concat('Холдинг ', v_holding, ' не существует!');
    END IF;
  END IF;
  SELECT @Answer; 
END;

Ну а т.к. позже оказалось что нужно(по факту может и не нужно, но пусть будет) добавим процедуру, которая будет связывать uid и холдинг или юрлицо:

CREATE DEFINER=`****`@`********` PROCEDURE `proc_guid_to`(v_to varchar(255), v_toval varchar(255), v_guid varchar(255))
BEGIN
  IF v_to = 'holding' THEN
    IF EXISTS(SELECT `holding` FROM `link_holding` WHERE `holding` LIKE v_toval) THEN
      IF (!EXISTS(SELECT `guid` FROM `guid_for_jur_hold` WHERE `guid` LIKE v_guid) AND !EXISTS(SELECT `guid` FROM `link_object` WHERE `guid` LIKE v_guid)) THEN
        IF EXISTS(SELECT `holding` FROM `guid_for_jur_hold` WHERE `holding` LIKE v_toval) THEN
          UPDATE `guid_for_jur_hold` SET `guid` = v_guid where `holding` like v_toval;
          SET @Answer = concat(v_guid, ' обновлен холдингу ', v_toval, '!');
        ELSE
          INSERT INTO `guid_for_jur_hold` SET `holding` = v_toval, `guid` = v_guid;
          SET @Answer = concat(v_guid, ' добавлен холдингу ', v_toval, '!');
        END IF;
      ELSE
        SET @Answer = concat(v_guid, ' уже существует!');
      END IF;
    ELSE
      SET @Answer = concat('Холдингу ', v_toval, ' не присвоено ни одно юрлицо!');
    END IF;
  ELSEIF v_to = 'jurlico' THEN
    IF EXISTS(SELECT `jurlico` FROM `link_jurlico` WHERE `jurlico` LIKE v_toval) THEN
      IF (!EXISTS(SELECT `guid` FROM `guid_for_jur_hold` WHERE `guid` LIKE v_guid) AND !EXISTS(SELECT `guid` FROM `link_object` WHERE `guid` LIKE v_guid)) THEN
        IF EXISTS(SELECT `jurlico` FROM `guid_for_jur_hold` WHERE `jurlico` LIKE v_toval) THEN
          UPDATE `guid_for_jur_hold` SET `guid` = v_guid where `jurlico` like v_toval;
          SET @Answer = concat(v_guid, ' обновлен юрлицу ', v_toval, '!');
        ELSE
          INSERT INTO `guid_for_jur_hold` SET `jurlico` = v_toval, `guid` = v_guid;
          SET @Answer = concat(v_guid, ' добавлен юрлицу ', v_toval, '!');
        END IF;
      ELSE
        SET @Answer = concat(v_guid, ' уже существует!');
      END IF;
    ELSE
      SET @Answer = concat('Юрлицу ', v_toval, ' не присвоен ни один объект!');
    END IF;
  END IF;
  SELECT @Answer;
END;

Ну и самая большая процедура, привязка софта к уровням дерева(по возможности отлавливал ошибки на уровне SQL, в крайнем случае можно было бы их отловить на уровне передачи в функцию из php):

Показать

CREATE DEFINER=`******`@`********` PROCEDURE `proc_soft_to`(v_command varchar(255), v_to varchar(255), v_toval varchar(255), v_soft varchar(255))
BEGIN
  IF EXISTS(SELECT `name` FROM `software` WHERE `name`=v_soft ORDER BY Id DESC LIMIT 1) THEN
    IF v_command = 'add' THEN
      IF v_to = 'object' THEN
        IF EXISTS(SELECT `object` FROM `link_jurlico` WHERE `object`=v_toval ORDER BY Id DESC LIMIT 1) THEN
          SELECT `jurlico` FROM `link_jurlico` WHERE `object`=v_toval ORDER BY Id DESC LIMIT 1 INTO @Jurlico;
          SELECT `holding` FROM `link_holding` WHERE `jurlico`=@Jurlico ORDER BY Id DESC LIMIT 1 INTO @Holding;
          IF (!EXISTS(SELECT `name` FROM `holding` WHERE `name`=@Holding AND `soft`=v_soft) AND !EXISTS(SELECT `name` FROM `jurlico` WHERE `name`=@Jurlico AND `soft`=v_soft)) THEN
            INSERT INTO `object` (`name`, `soft`) VALUES (v_toval, v_soft);
            SET @Answer = concat(v_soft, ' добавлен в список ПО объекта ', v_toval, '!');
          ELSE
            SET @Answer = concat(v_soft, ' уже добавлен юрлицу или холдингу!');
          END IF;
        ELSE
          SET @Answer = concat(v_toval, ' отсутствует в списке объектов!');
        END IF;
      ELSEIF v_to = 'jurlico' THEN
        IF EXISTS(SELECT `jurlico` FROM `link_jurlico` WHERE `jurlico`=v_toval ORDER BY Id DESC LIMIT 1) THEN
          SELECT `holding` FROM `link_holding` WHERE `jurlico`=v_toval ORDER BY Id DESC LIMIT 1 INTO @Holding;
          IF !EXISTS(SELECT `name` FROM `holding` WHERE `name`=@Holding AND `soft`=v_soft) THEN
            INSERT INTO `jurlico` (`name`, `soft`) VALUES (v_toval, v_soft);
            DELETE FROM `object` WHERE `name` IN (SELECT `object` FROM `link_jurlico` WHERE `jurlico` LIKE v_toval) AND `soft` LIKE v_soft;
            SET @Answer = concat(v_soft, ' добавлен в список ПО юрлица ', v_toval, '!');
          ELSE
            SET @Answer = concat(v_soft, ' уже добавлен холдингу!');
          END IF;
        ELSE
          SET @Answer = concat(v_toval, ' отсутствует в списке юрлиц!');
        END IF;
      ELSEIF v_to = 'holding' THEN
        IF EXISTS(SELECT `holding` FROM `link_holding` WHERE `holding`=v_toval ORDER BY Id DESC LIMIT 1) THEN
          INSERT INTO `holding` (`name`, `soft`) VALUES (v_toval, v_soft);
          DELETE FROM `object` WHERE `name` IN (SELECT `object` FROM `link_jurlico` WHERE `jurlico` IN (SELECT `jurlico` FROM `link_holding` WHERE `holding` LIKE v_toval)) AND `soft` LIKE v_soft;
          DELETE FROM `jurlico` WHERE `name` IN (SELECT `jurlico` FROM `link_holding` WHERE `holding` LIKE v_toval) AND `soft` LIKE v_soft;
          SET @Answer = concat(v_soft, ' добавлен в список ПО холдинга ', v_toval, '!');
        ELSE
          SET @Answer = concat(v_toval, ' отсутствует в списке холдингов!');
        END IF;
      END IF;  
    ELSEIF v_command = 'remove' THEN
      IF v_to = 'object' THEN
        IF EXISTS(SELECT `object` FROM `link_jurlico` WHERE `object`=v_toval ORDER BY Id DESC LIMIT 1) THEN
          SELECT `name` FROM `holding` WHERE `name` IN (SELECT `holding` FROM `link_holding` WHERE `jurlico` IN (SELECT `jurlico` FROM `link_jurlico` WHERE `object` LIKE v_toval)) AND `soft` = v_soft INTO @V_holding;
          IF EXISTS(SELECT @V_holding) THEN
            INSERT INTO `jurlico` (`name`, `soft`) SELECT `jurlico`,v_soft FROM `link_holding` WHERE `holding` LIKE @V_holding;
            DELETE FROM `holding` WHERE `name` LIKE @V_holding AND `soft` LIKE v_soft;
          END IF;
          SELECT `name` FROM `jurlico` WHERE `name` IN (SELECT `jurlico` FROM `link_jurlico` WHERE `object` LIKE v_toval) AND `soft` = v_soft INTO @V_jurlico;
          IF EXISTS(SELECT @V_jurlico) THEN
            INSERT INTO `object` (`name`, `soft`) SELECT `object`,v_soft FROM `link_jurlico` WHERE `jurlico` LIKE @V_jurlico;
            DELETE FROM `jurlico` WHERE `name` LIKE @V_jurlico AND `soft` LIKE v_soft;
          END IF;
          DELETE FROM `object` WHERE `name` LIKE v_toval;
          SET @Answer = concat(v_soft, ' удален из списка ПО объекта ', v_toval, '!');
        ELSE
          SET @Answer = concat(v_toval, ' отсутствует в списке объектов!');
        END IF;
      ELSEIF v_to = 'jurlico' THEN
        IF EXISTS(SELECT `jurlico` FROM `link_jurlico` WHERE `jurlico`=v_toval ORDER BY Id DESC LIMIT 1) THEN
          IF EXISTS(SELECT `name` FROM `holding` WHERE `name` IN (SELECT `holding` FROM `link_holding` WHERE `jurlico` LIKE v_toval) AND `soft` = v_soft) THEN
            INSERT INTO `jurlico` (`name`, `soft`) SELECT `jurlico`,v_soft FROM `link_holding` WHERE `holding` IN (SELECT `holding` FROM `link_holding` WHERE `jurlico` LIKE v_toval) AND `jurlico` <> v_toval;
          END IF;
          DELETE FROM `object` WHERE `name` IN (SELECT `object` FROM `link_jurlico` WHERE `jurlico` LIKE v_toval) AND `soft` LIKE v_soft;
          DELETE FROM `jurlico` WHERE `name` LIKE v_toval AND `soft` LIKE v_soft;
          DELETE FROM `holding` WHERE `name` IN (SELECT `holding` FROM `link_holding` WHERE `jurlico` LIKE v_toval) AND `soft` LIKE v_soft;
          SET @Answer = concat(v_soft, ' удален из списка ПО юрлица ', v_toval, '!');
        ELSE
          SET @Answer = concat(v_toval, ' отсутствует в списке юрлиц!');
        END IF;
      ELSEIF v_to = 'holding' THEN
        IF EXISTS(SELECT `holding` FROM `link_holding` WHERE `holding`=v_toval ORDER BY Id DESC LIMIT 1) THEN
          DELETE FROM `object` WHERE `name` IN (SELECT `object` FROM `link_jurlico` WHERE `jurlico` IN (SELECT `jurlico` FROM `link_holding` WHERE `holding` LIKE v_toval)) AND `soft` LIKE v_soft;
          DELETE FROM `jurlico` WHERE `name` IN (SELECT `holding` FROM `link_holding` WHERE `holding` LIKE v_toval) AND `soft` LIKE v_soft;
          DELETE FROM `holding` WHERE `name` LIKE v_toval AND `soft` LIKE v_soft;
          SET @Answer = concat(v_soft, ' удален из списка ПО холдинга ', v_toval, '!');
        ELSE
          SET @Answer = concat(v_toval, ' отсутствует в списке холдингов!');
        END IF;
      END IF;
    END IF; 
  ELSE
    SET @Answer = concat(v_soft, ' отсутствует в списке софта!');
  END IF; 
  SELECT @Answer;
END;

Итого мы получили бэкэнд с такой вот структурой

Что важно в бэкэнде, связка у нас работает при помощи процедур. Их можно оперативно изменить, даже не владея php. Объекты связаны по наименованию, а не по uid, т.к. есть определенный синтаксис этих наименований и их можно сравнить с таблицами в других базах. Uid-ов изначально нигде нет. Таблица параметров софта расширяема и не затрагивает саму структуру дерева.

UPD: процедура, которая парсит дерево в таблицу(выборку нижнего уровня):

CREATE DEFINER = '*****'@'********'
PROCEDURE update_soft.proc_select_objectsoft()
BEGIN
  DROP TABLE IF EXISTS ObjectSoftTemp;
  CREATE TEMPORARY TABLE ObjectSoftTemp (`object` varchar(255),`guid` varchar(255),`soft` varchar(255),`path` varchar(255));
 
  INSERT ObjectSoftTemp (`object`, `guid`, `soft`, `path`)
    SELECT `link_jurlico`.`object`,`link_object`.`guid`,`holding`.`soft`,`software`.`path`
    FROM `holding` JOIN `software`,`link_holding`,`link_jurlico`,`link_object`
    WHERE `software`.`name` LIKE `holding`.`soft`
    AND `holding`.`name` LIKE `link_holding`.`holding`
    AND `link_holding`.`jurlico` LIKE `link_jurlico`.`jurlico`
    AND `link_jurlico`.`object` LIKE `link_object`.`object`
    ORDER BY `path`;
 
  INSERT ObjectSoftTemp (`object`, `guid`, `soft`, `path`)
    SELECT `link_jurlico`.`object`,`link_object`.`guid`,`jurlico`.`soft`,`software`.`path`
    FROM `jurlico` JOIN `software`,`link_jurlico`,`link_object`
    WHERE `software`.`name` LIKE `jurlico`.`soft`
    AND `jurlico`.`name` LIKE `link_jurlico`.`jurlico`
    AND `link_jurlico`.`object` LIKE `link_object`.`object`
    ORDER BY `path`;
 
  INSERT ObjectSoftTemp (`object`, `guid`, `soft`, `path`)
    SELECT `link_jurlico`.`object`,`link_object`.`guid`,`object`.`soft`,`software`.`path`
    FROM `object` JOIN `software`,`link_jurlico`,`link_object`
    WHERE `software`.`name` LIKE `object`.`soft`
    AND `object`.`name` LIKE `link_jurlico`.`object`
    AND `link_jurlico`.`object` LIKE `link_object`.`object`
    ORDER BY `path`;
 
  SELECT * FROM ObjectSoftTemp ORDER BY `object`, `path`;
 
END

Siarhei Dudko iT blog

Здесь я буду писать всякие интересные штуки из моей жизни и работы

Разработка системы обновления ПО холдинга часть 1. Вырастить дерево в SQL.