:: Visual Foxpro, Foxpro for DOS
• Войти
Рекурсивное удаление из одной таблицы
ry

Сообщений: 2113
Дата регистрации: 24.09.2007
ry
26.08.09 17:01:28
Есть таблица составных деталей с "древесной" структурой, т.е. код/код предка/название, например: 


CREATE CURSOR tree (det_id i, parent_id i, name c(10))

INSERT INTO tree VALUES (1,0,'Деталь1')

INSERT INTO tree VALUES (2,0,'Деталь2')

INSERT INTO tree VALUES (3,2,'Деталь3')

INSERT INTO tree VALUES (4,2,'Деталь4')

INSERT INTO tree VALUES (5,4,'Деталь5')

INSERT INTO tree VALUES (6,4,'Деталь6')

INSERT INTO tree VALUES (7,5,'Деталь7')
Как в такой таблице красиво сделать рекурсивное удаление всех потомков при удалении записи (чтобы при удалении детали №4 удалились детали №5,6,7)? И можно ли это сделать через ХП?



Исправлено 1 раз(а). Последнее : ry, 26.08.09 17:02
Ratings: 0 negative/0 positive
Re: Рекурсивное удаление из одной таблицы
Igor Korolyov

Сообщений: 34580
Дата регистрации: 28.05.2002
Igor Korolyov
26.08.09 19:06:47
Отчего ж нельзя - можно конечно. Именно рекурсивно и делается. Главное разделять внутреннее состояние процедуры для каждой итерации - например используя LOCAL переменные/массивы, или делая запросы в курсоры с уникальными именами, или цикл с сохранением "текущей обрабатываемой записи" в локальной переменной. 



DelTree (2)



PROCEDURE DelTree (tnID)

LOCAL laChilds[1], ln1

SELECT det_id FROM tree WHERE parent_id = m.tnID INTO ARRAY laChilds

FOR ln1 = 1 TO _TALLY

 DelTree (laChilds[m.ln1])

ENDFOR

DELETE FROM tree WHERE det_id = m.tnID

ENDPROC


------------------
WBR, Igor
Ratings: 0 negative/0 positive
Re: Рекурсивное удаление из одной таблицы
Igor Korolyov

Сообщений: 34580
Дата регистрации: 28.05.2002
Igor Korolyov
26.08.09 19:41:58
А можно пойти и "с другой стороны". 

DELETE FROM tree WHERE det_id = 4 OR IsChildOf(4, Det_id)



PROCEDURE IsChildOf(tnParent_id, tnID)

LOCAL laParent[1]

SELECT parent_id FROM tree WHERE det_id = m.tnID INTO ARRAY laParent

IF _TALLY = 1

 RETURN laParent[1] = m.tnParent_id OR IsChildOf(m.tnParent_id, laParent[1])

ELSE

 RETURN .F.

ENDIF

ENDPROC

К сожалению не удаётся заставить фокс исполнять рекурсивно вызов процедуры из запроса в этой же процедуре Типа
SELECT parent_id FROM tree WHERE det_id = m.tnID AND (parent_id = m.tnParent_id OR IsChildOf(m.tnParent_id, parent_id)) INTO ARRAY laExists
Падает бедняга


------------------
WBR, Igor
Ratings: 0 negative/0 positive
Re: Рекурсивное удаление из одной таблицы
leonid

Сообщений: 3204
Откуда: Рига
Дата регистрации: 03.02.2006
leonid
26.08.09 21:44:41
Без рекурсии тоже можно написать 


DelTree (2)  



PROCEDURE DelTree (tnID)

m.tl=0

select id from tree where id=tnID into cursor tmp1 nofilter

do while _tally>m.tl

    m.tl=_tally

    select id from tree where id=tnID ;

    union all ;

    select id from tree ;

        where parentid in (select id from tmp1) into cursor tmp2

    select * from tmp2 into cursor tmp1 nofilter

enddo

delete from tree where id in (select id from tmp1)

use in select("tmp1")

use in select("tmp2")
Кстати, было бы интересно посмотреть, что быстрее работает?
Ratings: 0 negative/0 positive
Re: Рекурсивное удаление из одной таблицы
ry

Сообщений: 2113
Дата регистрации: 24.09.2007
ry
26.08.09 23:58:48
leonid
Кстати, было бы интересно посмотреть, что быстрее работает?



SET TALK OFF



CREATE CURSOR tree (det_id i, parent_id i, name c(13))  

INSERT INTO tree VALUES (1,0,'Деталь1')

FOR i=1 TO 10000

    INSERT INTO tree VALUES (i,ROUND(RAND()*i,0),'Деталь'+LTRIM(STR(i)))  

ENDFOR 



nStart=SECONDS()

DelTree (4)  

nEnd=SECONDS() 

? nEnd-nStart

RECALL ALL 



nStart=SECONDS()

DelTree2 (4)  

nEnd=SECONDS() 

? nEnd-nStart



 

  PROCEDURE DelTree (tnID)  

  LOCAL laChilds[1], ln1  

  SELECT det_id FROM tree WHERE parent_id = m.tnID INTO ARRAY laChilds  

  FOR ln1 = 1 TO _TALLY  

   DelTree (laChilds[m.ln1])  

  ENDFOR  

  DELETE FROM tree WHERE det_id = m.tnID  

  ENDPROC

  

  PROCEDURE DelTree2 (tnID)  

  m.tl=0  

  select det_id from tree where det_id=tnID into cursor tmp1 nofilter  

  do while _tally>m.tl  

      m.tl=_tally  

      select det_id from tree where det_id=tnID ;  

      union all ;  

      select det_id from tree ;  

          where parent_id in (select det_id from tmp1) into cursor tmp2  

      select * from tmp2 into cursor tmp1 nofilter  

  enddo  

  delete from tree where det_id in (select det_id from tmp1)  

  use in select("tmp1")  

  use in select("tmp2")

Вариант Леонида несопоставимо быстрее: у меня выходили соотношения по затратам времени 4.688/0.328, 16.625/0.375, 20.656/0.438 и т.д.
Во втором варианте Игоря на строке 
RETURN laParent[1] = m.tnParent_id OR IsChildOf(m.tnParent_id, laParent[1])
выдается ошибка "Insufficient stack space".

Спасибо за идеи!
Ratings: 0 negative/0 positive
Re: Рекурсивное удаление из одной таблицы
LUCIAN

Сообщений: 343
Откуда: Лида Беларусь
Дата регистрации: 25.03.2008
LUCIAN
27.08.09 11:21:46
Похожая тема рассматривалась тут:
forum.foxclub.ru
Ratings: 0 negative/0 positive
Re: Рекурсивное удаление из одной таблицы
Igor Korolyov

Сообщений: 34580
Дата регистрации: 28.05.2002
Igor Korolyov
27.08.09 13:35:42
Ну по сути твой цикл это и есть рекурсия - только без лишних вызовов, и с "накоплением" результата разузлования дерева

В таких таблицах обычно есть индексы и по id и по parent_id, и вместо запросов может оказаться эффективнее INDEXSEEK.
А если нету триггеров ссылочной целостности (т.е. порядок удаления узлов можно не соблюдать), то можно просто удалить нужный элемент, и потом в цикле крутить удаление появившихся "сирот". 
DELETE FROM tree WHERE det_id = 4

DO WHILE _TALLY > 0

 DELETE FROM tree WHERE parent_id <> 0 AND parent_id NOT IN (SELECT det_id FROM tree)

ENDDO

Insufficient stack space - это сколько ж уровней в дереве что штатного стека не хватило


------------------
WBR, Igor
Ratings: 0 negative/0 positive
Re: Рекурсивное удаление из одной таблицы
leonid

Сообщений: 3204
Откуда: Рига
Дата регистрации: 03.02.2006
leonid
27.08.09 14:05:19
Игорь, я не знаю, как это на фоксе, но, например на DB2, такие конструкции, как 
... NOT IN (SELECT ...
не оптимизируются.
Ratings: 0 negative/0 positive
Re: Рекурсивное удаление из одной таблицы
ry

Сообщений: 2113
Дата регистрации: 24.09.2007
ry
27.08.09 14:06:23
Igor Korolyov
Insufficient stack space - это сколько ж уровней в дереве что штатного стека не хватило
Это в случае приведенного мною примера для сравнения скорости алгоритмов. Там в тестовом курсоре код родителя формируются через RAND, так что уровней не намного меньше, чем записей Но ошибка вылетает даже при 50 записях в таблице (хотя при 20 ее нет)
Ratings: 0 negative/0 positive
Re: Рекурсивное удаление из одной таблицы
Igor Korolyov

Сообщений: 34580
Дата регистрации: 28.05.2002
Igor Korolyov
27.08.09 14:56:54
А в оракле есть штатные средства работы с древовидными структурами - connect by в частности - ничего "своего" для разузлования изобретать не надо А удаление "подчинённых" реализуется вообще без кода - декларативно, "каскадным" констрейном ссылочной целостности


------------------
WBR, Igor
Ratings: 0 negative/0 positive
Re: Рекурсивное удаление из одной таблицы
LUCIAN

Сообщений: 343
Откуда: Лида Беларусь
Дата регистрации: 25.03.2008
LUCIAN
27.08.09 15:25:10
CREATE CURSOR tree (det_id i, parent_id i, name c(10))  

  INSERT INTO tree VALUES (1,0,'Деталь1')  

  INSERT INTO tree VALUES (2,0,'Деталь2')  

  INSERT INTO tree VALUES (3,2,'Деталь3')  

  INSERT INTO tree VALUES (4,2,'Деталь4')  

  INSERT INTO tree VALUES (5,4,'Деталь5')  

  INSERT INTO tree VALUES (6,4,'Деталь6')  

  INSERT INTO tree VALUES (7,5,'Деталь7')

  

SELECT  * FROM TREE  WHERE  det_id=4 INTO CURSOR CTR

SELECT  * FROM TREE  WHERE  det_id=4 INTO CURSOR CTRU

KZ=_TALLY

DO WHILE KZ>0

    SELECT TREE.* FROM TREE,CTR WHERE CTR.det_id=TREE.parent_id INTO CURSOR CTR

    KZ=_TALLY

    SELECT * FROM CTRU UNION ALL SELECT * FROM CTR INTO CURSOR CTRU

ENDDO

DELETE FROM TREE WHERE STR(det_id,10)+STR(parent_id,10)  IN ;

 (SELECT STR(det_id,10)+STR(parent_id,10) FROM CTRU)

SELE TREE

BROW
Ratings: 0 negative/0 positive
Re: Рекурсивное удаление из одной таблицы
msa_tech
Автор

Сообщений: 103
Откуда: Сыктывкар
Дата регистрации: 29.07.2009
msa_tech
27.08.09 15:27:50
Igor Korolyov
А в оракле есть штатные средства работы с древовидными структурами - connect by в частности - ничего "своего" для разузлования изобретать не надо А удаление "подчинённых" реализуется вообще без кода - декларативно, "каскадным" констрейном ссылочной целостности
Добавлю (не сочтите за рекламу ) и выделю - выбрать всю иерархию одним запросом, на любую нужную глубину, + контроль зацикливания (в 10-ке), и еще много чего...
Ratings: 0 negative/0 positive
Re: Рекурсивное удаление из одной таблицы
ry

Сообщений: 2113
Дата регистрации: 24.09.2007
ry
27.08.09 23:55:38
LUCIAN
CREATE CURSOR tree (det_id i, parent_id i, name c(10))  

  INSERT INTO tree VALUES (1,0,'Деталь1')  

  INSERT INTO tree VALUES (2,0,'Деталь2')  

  INSERT INTO tree VALUES (3,2,'Деталь3')  

  INSERT INTO tree VALUES (4,2,'Деталь4')  

  INSERT INTO tree VALUES (5,4,'Деталь5')  

  INSERT INTO tree VALUES (6,4,'Деталь6')  

  INSERT INTO tree VALUES (7,5,'Деталь7')

  

SELECT  * FROM TREE  WHERE  det_id=4 INTO CURSOR CTR

SELECT  * FROM TREE  WHERE  det_id=4 INTO CURSOR CTRU

KZ=_TALLY

DO WHILE KZ>0

    SELECT TREE.* FROM TREE,CTR WHERE CTR.det_id=TREE.parent_id INTO CURSOR CTR

    KZ=_TALLY

    SELECT * FROM CTRU UNION ALL SELECT * FROM CTR INTO CURSOR CTRU

ENDDO

DELETE FROM TREE WHERE STR(det_id,10)+STR(parent_id,10)  IN ;

 (SELECT STR(det_id,10)+STR(parent_id,10) FROM CTRU)

SELE TREE

BROW
В таком варианте работает очень медленно из-за использования функции STR(). Если же заменить выражение на числовое det_id*1000000+parent_id (разрядность подбирается заведомо больше, чем разрядность det_id), то работает даже быстрее, чем вариант Леонида.
p.s. Оценивал только скорость, правильность удаления еще не проверял...
Ratings: 0 negative/0 positive
Re: Рекурсивное удаление из одной таблицы
ry

Сообщений: 2113
Дата регистрации: 24.09.2007
ry
27.08.09 23:57:34
msa_tech
Igor Korolyov
А в оракле есть штатные средства работы с древовидными структурами - connect by в частности - ничего "своего" для разузлования изобретать не надо А удаление "подчинённых" реализуется вообще без кода - декларативно, "каскадным" констрейном ссылочной целостности
Добавлю (не сочтите за рекламу ) и выделю - выбрать всю иерархию одним запросом, на любую нужную глубину, + контроль зацикливания (в 10-ке), и еще много чего...
С ораклом не работал, но реклама замечательная
Ratings: 0 negative/0 positive
Re: Рекурсивное удаление из одной таблицы
Naomi

Сообщений: 1796
Дата регистрации: 09.10.2003
Naomi
28.08.09 08:15:51
SQL Server 2005+ hierarchical CTE - позволяют работать с древовидной структурой
Ratings: 0 negative/0 positive
Re: Рекурсивное удаление из одной таблицы
LUCIAN

Сообщений: 343
Откуда: Лида Беларусь
Дата регистрации: 25.03.2008
LUCIAN
28.08.09 09:18:16
Цитата:
В таком варианте работает очень медленно из-за использования функции STR(). Если же заменить выражение на числовое det_id*1000000+parent_id (разрядность подбирается заведомо больше, чем разрядность det_id), то работает даже быстрее, чем вариант Леонида.
p.s. Оценивал только скорость, правильность удаления еще не проверял...
На самом деле можно не использовать функцию STR() или det_id*1000000+parent_id так как для однозначного определения того что надо удалять из TREE достаточно DET_ID,поэтому процедура удаления примет вид: 
CREATE CURSOR tree (det_id i, parent_id i, name c(10))  

  INSERT INTO tree VALUES (1,0,'Деталь1')  

  INSERT INTO tree VALUES (2,0,'Деталь2')  

  INSERT INTO tree VALUES (3,2,'Деталь3')  

  INSERT INTO tree VALUES (4,2,'Деталь4')  

  INSERT INTO tree VALUES (5,4,'Деталь5')  

  INSERT INTO tree VALUES (6,4,'Деталь6')  

  INSERT INTO tree VALUES (7,5,'Деталь7')

 

SELECT  * FROM TREE  WHERE  det_id=4 INTO CURSOR CTR

SELECT  * FROM TREE  WHERE  det_id=4 INTO CURSOR CTRU

KZ=_TALLY

DO WHILE KZ>0

    SELECT TREE.* FROM TREE,CTR WHERE CTR.det_id=TREE.parent_id INTO CURSOR CTR

    KZ=_TALLY

    SELECT * FROM CTRU UNION ALL SELECT * FROM CTR INTO CURSOR CTRU

ENDDO

DELETE FROM TREE WHERE DET_ID  IN ;

 (SELECT DET_ID FROM CTRU)

  

 SELECT TREE

 BROW
Ratings: 0 negative/0 positive


:: Список Форумов•Список тем•Поиск•Войти
Извините, только зарегистрированные пользователи могут оставлять сообщения в этом форуме.

On-line: 21 PaulWist vech  (Гостей: 19)
© 2000-2024 Fox Club 
Яндекс.Метрика