Перемещение поддерева транзитивного замыкания с MySQL

Question

Я пытаюсь преобразовать таблицу транзитивного замыкания в систему, которая в настоящее время использует списки смежности, используя MySQL, на основе рецепта, приведенного в SQL Antipatterns. Тем не менее, я столкнулся с трудностями в реализации движущихся поддеревьев.

Я сконструировал предельное упрощение существующей системы и таблицы закрытия для усилий по разработке и перенесу это на реальные данные, как только получу их для удовлетворительной работы. Мои таблицы следующие:

CREATE TABLE `product` ( 
    `id` BigInt( 255 ) AUTO_INCREMENT NOT NULL, 
    `parent` BigInt( 255 ) NOT NULL, 
    `description` VarChar( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
     PRIMARY KEY ( `id` )
 )
CHARACTER SET = utf8
COLLATE = utf8_general_ci
ENGINE = InnoDB

CREATE TABLE `closure` ( 
    `ancestor` BigInt( 255 ) NOT NULL, 
    `decendant` BigInt( 255 ) NOT NULL, 
    `depth` BigInt( 255 ) NOT NULL,
     PRIMARY KEY ( `ancestor`,`decendant` )
 )
CHARACTER SET = utf8
COLLATE = utf8_general_ci
ENGINE = InnoDB;
CREATE INDEX `ancestordepth` USING BTREE ON `closure`( `ancestor`, `depth` )

Мои тестовые данные следующие:

Product
=======
1,0,"Test 1"
2,0,"Test 2"
3,0,"Test 3"
4,1,"Test 4"
5,1,"Test 5"
6,1,"Test 6"
7,4,"Test 7"
8,4,"Test 8"
9,4,"Test 9"
10,7,"Test 10"

Closure
=======
1,1,0
1,4,1
1,5,1
1,6,1
1,7,2
1,8,2
1,9,2
1,10,3
2,2,0
3,3,0
4,4,0
4,7,1
4,8,1
4,9,1
4,10,2
5,5,0
6,6,0
7,7,0
7,10,1
8,8,0
9,9,0
10,10,0

Я реализовал триггеры, которые вставляют строки в таблицу закрытия при создании строки в таблице продукта и удаляют строки из таблицы закрытия, когда строки продукта удаляются, и они работают нормально, но ограничения MySQL мешают мне получить вариант обновления (где родитель в таблице продуктов меняется) на работу.

Если бы я хотел обновить узел 4, чтобы он был дочерним по отношению к узлу 2 вместо узла 1.

Книга антипаттернов SQL дает запросы для этого. Первый предназначен для разрыва существующего поддерева путем удаления соответствующих строк из таблицы замыканий.

DELETE
FROM closure
WHERE decendant IN (
    SELECT decendant
    FROM closure
    WHERE ancestor = 4
)
AND ancestor IN (
    SELECT ancestor
    FROM closure
    WHERE decendant = 4
    AND ancestor != decendant
)

Но, конечно, MySQL не позволит вам сделать это из-за недостатка в его дизайне, который не позволяет вам изменять любую таблицу, которую вы используете в подзапросе.

Я пытаюсь переписать запрос в самостоятельное соединение, потому что тогда я могу удалить строки из этого. Я изменил исходный запрос, чтобы выбрать вместо удаления, потому что это работает, и я могу использовать его в качестве основы для сравнения. Однако моя попытка повторить запрос с объединениями возвращает пустой набор.

SELECT *
FROM closure AS a
JOIN closure AS b ON a.ancestor = b.ancestor AND a.decendant = b.decendant
JOIN closure AS c ON a.ancestor = c.ancestor AND a.decendant = c.decendant
WHERE b.ancestor = 4
AND c.decendant = 4
AND c.ancestor != c.decendant

Я должен быть в состоянии сохранить запрос, который выполняется относительно просто, потому что он должен идти в триггере. Я также не могу использовать временные таблицы, потому что работающий SQL-сервер работает в кластере, и в прошлом у нас были проблемы, когда использование временных таблиц нарушало репликацию. Если кто-то может помочь с переписыванием запроса в форму, которая позволит мне удалять строки в MySQL, я был бы признателен.

Answer 1

Следующее, кажется, правильный способ сделать это в MySQL

    DELETE a
    FROM closure AS a
    JOIN closure AS b ON a.decendant = b.decendant
    LEFT JOIN closure AS c ON b.ancestor = c.ancestor and a.ancestor = c.decendant
    WHERE b.ancestor = OLD.id
    AND c.ancestor IS NULL;