Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Очистка таблиц путем обхода в правильном порядке.  [new]
design21
Member

Откуда: Minsk
Сообщений: 59
Добрый день всем
Имеется БД, в каждой таблице котрой есть поле DeletedTime, идентифицирующее запись как удаленную.
Возникла задача очистить базу данных от записей помеченных удаленными.

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

Поле, являющееся первичным ключем всегда имеет имя TableNameId, такое же имя имеет поле в связанной таблице.

CREATE TABLE [dbo].[UserResponsibilityProfileDescription](
	[UserResponsibilityProfileDescriptionId] [uniqueidentifier] NOT NULL PRIMARY KEY,
	[DeletedTime] [datetime2](7) NULL,
	[Name] [nvarchar](50) NOT NULL)

CREATE TABLE [dbo].[UserResponsibilityProfile](
	[UserResponsibilityProfileId] [uniqueidentifier] NOT NULL PRIMARY KEY,
	[DeletedTime] [datetime2](7) NULL,
	[UserResponsibilityProfileDescriptionId] [uniqueidentifier] NOT NULL,
        CONSTRAINT [FK_UserResponsibilityProfileActivity_UserResponsibilityProfile] FOREIGN KEY([UserResponsibilityProfileId])
        REFERENCES [dbo].[UserResponsibilityProfile] ([UserResponsibilityProfileId])


Возможно кто-то может поделится идеями?
11 фев 14, 12:19    [15550415]     Ответить | Цитировать Сообщить модератору
 Re: Очистка таблиц путем обхода в правильном порядке.  [new]
Glory
Member

Откуда:
Сообщений: 104760
design21
Возможно кто-то может поделится идеями?

Использовать системное представление, в котором хранится информация о всех таких констрейтах
11 фев 14, 12:22    [15550439]     Ответить | Цитировать Сообщить модератору
 Re: Очистка таблиц путем обхода в правильном порядке.  [new]
iap
Member

Откуда: Москва
Сообщений: 47047
Каскадных FK нет - значит:
1. Отключить (или удалить, но тогда сначала заскриптовать) констрейнты
2. Удалить записи
3. Включить (или создать подготовленным вначале скриптом) констрейнты.
11 фев 14, 12:38    [15550538]     Ответить | Цитировать Сообщить модератору
 Re: Очистка таблиц путем обхода в правильном порядке.  [new]
design21
Member

Откуда: Minsk
Сообщений: 59
Glory
design21
Возможно кто-то может поделится идеями?

Использовать системное представление, в котором хранится информация о всех таких констрейтах


ну может есть идеи алгоритма?



iap
Каскадных FK нет - значит:
1. Отключить (или удалить, но тогда сначала заскриптовать) констрейнты
2. Удалить записи
3. Включить (или создать подготовленным вначале скриптом) констрейнты.



Отключать боюсь - может нарушится целостность, если вдруг не совсем корректно данные помечались при удалении. Если к примеру родительская запись помечена удаленной, а дочерняя нет.
11 фев 14, 12:56    [15550703]     Ответить | Цитировать Сообщить модератору
 Re: Очистка таблиц путем обхода в правильном порядке.  [new]
Glory
Member

Откуда:
Сообщений: 104760
design21
ну может есть идеи алгоритма?

Хм
FK констрейты могут представлять собой дерево
Делаем рекурсивный запрос к системному представлению и строим иерерхию таблиц.
Потом начинаем с самого нижнего уровня этой иерархии перебирать таблицы, проерять в них наличие поля, строить динамический запрос с удалением.
И выполнять этот запрос
Вполне возможно, что рекурсивным запросом можно создать текст динамического запроса сразу для всех таблиц
11 фев 14, 13:06    [15550769]     Ответить | Цитировать Сообщить модератору
 Re: Очистка таблиц путем обхода в правильном порядке.  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3015
design21
ну может есть идеи алгоритма?

включить каскадное удаление и просто удалять?
11 фев 14, 13:07    [15550786]     Ответить | Цитировать Сообщить модератору
 Re: Очистка таблиц путем обхода в правильном порядке.  [new]
Glory
Member

Откуда:
Сообщений: 104760
design21
Имеется БД, в каждой таблице котрой есть поле DeletedTime, идентифицирующее запись как удаленную.
Возникла задача очистить базу данных от записей помеченных удаленными.

Вот тут кстати есть непонятный момент.
Не будет так, что в родительской таблице запись помечена как удаленная, а в дочерней не все зависимые записи помечены как удаленные ?
11 фев 14, 13:10    [15550807]     Ответить | Цитировать Сообщить модератору
 Re: Очистка таблиц путем обхода в правильном порядке.  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3015
Glory, тоже об этом думал, но решил, что родительский уровень перекрывает дочерний уровень. Иначе задача нерешаема
11 фев 14, 13:14    [15550844]     Ответить | Цитировать Сообщить модератору
 Re: Очистка таблиц путем обхода в правильном порядке.  [new]
Glory
Member

Откуда:
Сообщений: 104760
HandKot
но решил, что родительский уровень перекрывает дочерний уровень.

Тогда все намного сложнее
Потому что в запросе на удаление из дочерней таблицы придется писать соединение с родительской таблицей.
11 фев 14, 13:16    [15550859]     Ответить | Цитировать Сообщить модератору
 Re: Очистка таблиц путем обхода в правильном порядке.  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3015
Glory, я может неправильно выразился. Имелось ввиду, что если родитель "удалён", то и удалять все дочерние НЕЗАВИСИМО от статуса. Поэтому не надо никаких соединение с родительской таблицей и решается всё простым каскадным удалением

ЗЫЖ сам грешу иногда каскадным удалением, хоть и понимаю, что чревато
11 фев 14, 13:20    [15550882]     Ответить | Цитировать Сообщить модератору
 Re: Очистка таблиц путем обхода в правильном порядке.  [new]
Glory
Member

Откуда:
Сообщений: 104760
HandKot
Glory, я может неправильно выразился. Имелось ввиду, что если родитель "удалён", то и удалять все дочерние НЕЗАВИСИМО от статуса. Поэтому не надо никаких соединение с родительской таблицей и решается всё простым каскадным удалением

Ой, я перепутал вас с ТС-ом.
С каскадными - все понятно. Непонятно, как у ТС обстоит дело с приоритетами признака удаления
11 фев 14, 13:22    [15550900]     Ответить | Цитировать Сообщить модератору
 Re: Очистка таблиц путем обхода в правильном порядке.  [new]
design21
Member

Откуда: Minsk
Сообщений: 59
Glory
HandKot
Glory, я может неправильно выразился. Имелось ввиду, что если родитель "удалён", то и удалять все дочерние НЕЗАВИСИМО от статуса. Поэтому не надо никаких соединение с родительской таблицей и решается всё простым каскадным удалением

Ой, я перепутал вас с ТС-ом.
С каскадными - все понятно. Непонятно, как у ТС обстоит дело с приоритетами признака удаления


Если родитель удален, то можно удалять дочерние записи
11 фев 14, 13:35    [15550982]     Ответить | Цитировать Сообщить модератору
 Re: Очистка таблиц путем обхода в правильном порядке.  [new]
design21
Member

Откуда: Minsk
Сообщений: 59
Glory
design21
ну может есть идеи алгоритма?

Хм
FK констрейты могут представлять собой дерево
Делаем рекурсивный запрос к системному представлению и строим иерерхию таблиц.
Потом начинаем с самого нижнего уровня этой иерархии перебирать таблицы, проерять в них наличие поля, строить динамический запрос с удалением.
И выполнять этот запрос
Вполне возможно, что рекурсивным запросом можно создать текст динамического запроса сразу для всех таблиц


Чтобы строить дерево, надо сначала определить корневые таблицы. Я так понимаю признаком корневой таблицы будет отсутствие дочерних?
11 фев 14, 13:38    [15551009]     Ответить | Цитировать Сообщить модератору
 Re: Очистка таблиц путем обхода в правильном порядке.  [new]
Glory
Member

Откуда:
Сообщений: 104760
design21
Чтобы строить дерево, надо сначала определить корневые таблицы.

sys.foreign_keys
11 фев 14, 13:43    [15551045]     Ответить | Цитировать Сообщить модератору
 Re: Очистка таблиц путем обхода в правильном порядке.  [new]
invm
Member

Откуда: Москва
Сообщений: 9633
design21,

with r as
(
 select
  t.object_id, quotename(schema_name(t.schema_id)) + N'.' + quotename(t.name) as table_name, 1 as [level],
  '(' + cast(t.object_id as varchar(max)) + ')' as [path]
 from
  sys.tables t
 where
  not exists(select 1 from sys.foreign_keys where referenced_object_id = t.object_id)

 union all

 select
  fk.referenced_object_id, quotename(object_schema_name(fk.referenced_object_id)) + N'.' + quotename(object_name(fk.referenced_object_id)) as table_name, r.[level] + 1,
  r.[path] + '(' + cast(fk.referenced_object_id as varchar(max)) + ')'
 from
  r join
  sys.foreign_keys fk on fk.parent_object_id = r.object_id
 where
  r.[path] not like '%(' + cast(fk.referenced_object_id as varchar(max)) + ')%'
),
x as
(
 select
  *, row_number() over (partition by table_name order by [level] desc) as rn
 from
  r
)
select
 *
from
 x
where
 rn = 1
order by
 [level], table_name
option
 (maxrecursion 0);
11 фев 14, 14:18    [15551311]     Ответить | Цитировать Сообщить модератору
 Re: Очистка таблиц путем обхода в правильном порядке.  [new]
design21
Member

Откуда: Minsk
Сообщений: 59
invm,

Спасибо, огромнейшее!
11 фев 14, 14:38    [15551473]     Ответить | Цитировать Сообщить модератору
 Re: Очистка таблиц путем обхода в правильном порядке.  [new]
design21
Member

Откуда: Minsk
Сообщений: 59
Нашел еще один очень интересный скрипт по поднятому мной вопросу вот тут:
Get foreignkey relations to n level in tree structure and Generate Delete script

Скрипт для конкретной таблицы стрит дерево зависимостей и геренирует запросы необходимые для их последовательной очистки, кроме того позволяет наложить доволнительно условие, чтобы например удалялись не все записи.

К сообщению приложен файл (GetForeignKeyRelationship - Usage.sql - 2Kb) cкачать
17 фев 14, 00:45    [15575041]     Ответить | Цитировать Сообщить модератору
 Re: Очистка таблиц путем обхода в правильном порядке.  [new]
design21
Member

Откуда: Minsk
Сообщений: 59
design21
Нашел еще один очень интересный скрипт по поднятому мной вопросу вот тут:
Get foreignkey relations to n level in tree structure and Generate Delete script

Скрипт для конкретной таблицы стрит дерево зависимостей и геренирует запросы необходимые для их последовательной очистки, кроме того позволяет наложить доволнительно условие, чтобы например удалялись не все записи.



Скрипт по указанной ссылке оказался не такой уж и хороший. Скрипты генерирует неправильные.

А у нас опять возникла задача очистки таблиц. Но теперь не всех, а только некоторых, не полная а от записей помеченных удаленными. Дополнительно надо уделить из связанных таблиц исторические данные.

Например вот так:

IF OBJECT_ID('[dbo].[Session]') IS NOT NULL
DROP TABLE [dbo].[Session]		
IF OBJECT_ID('[dbo].[UnifiedSession]') IS NOT NULL
DROP TABLE [dbo].[UnifiedSession]
IF OBJECT_ID('[dbo].[ContactObject]') IS NOT NULL
DROP TABLE [dbo].[ContactObject]


CREATE TABLE [dbo].[ContactObject] (ContactObjectId INT IDENTITY(1,1) PRIMARY KEY
                          , Name nvarchar(max)
						  , DeletedTime datetime2(7))



CREATE TABLE [dbo].[UnifiedSession] (UnifiedSessionId INT IDENTITY(1,1) PRIMARY KEY
                           , ContactObjectIdFrom INT FOREIGN KEY REFERENCES ContactObject(ContactObjectId)
                           , ContactObjectIdTo INT FOREIGN KEY REFERENCES ContactObject(ContactObjectId))


CREATE TABLE [dbo].[Session] (SessionId INT IDENTITY(1,1) PRIMARY KEY
                            , UnifiedSessionId INT FOREIGN KEY REFERENCES [dbo].[UnifiedSession](UnifiedSessionId)
                            , ContactObjectId INT FOREIGN KEY REFERENCES [dbo].[ContactObject]([ContactObjectId]))




INSERT INTO [dbo].[ContactObject] (Name,DeletedTime)
VALUES ('ContactObject1',NULL),('ContactObject2', getdate()) 

INSERT INTO [dbo].[UnifiedSession] (ContactObjectIdFrom, ContactObjectIdTo)
VALUES (1,2),(2,1),(2,2),(1,1)

INSERT INTO [dbo].[Session] (UnifiedSessionId,ContactObjectId)
VALUES (1,2),(2,1),(1,1),(1,2)

/* Очистка [dbo].[Session] */
DELETE FROM s
FROM dbo.[Session] s
JOIN dbo.[UnifiedSession] us ON s.UnifiedSessionId = s.UnifiedSessionId
JOIN dbo.[ContactObject] co ON us.ContactObjectIdFrom = co.ContactObjectId
WHERE co.DeletedTime IS NOT NULL

DELETE FROM s
FROM dbo.[Session] s
JOIN dbo.[UnifiedSession] us ON s.UnifiedSessionId = s.UnifiedSessionId
JOIN dbo.[ContactObject] co ON us.ContactObjectIdTo = co.ContactObjectId
WHERE co.DeletedTime IS NOT NULL


/* Очистка [dbo].[UnifiedSession] */
DELETE FROM us
FROM dbo.[UnifiedSession] us 
JOIN dbo.[ContactObject] co ON us.ContactObjectIdFrom = co.ContactObjectId
WHERE co.DeletedTime IS NOT NULL

DELETE FROM us
FROM dbo.[UnifiedSession] us
JOIN dbo.[ContactObject] co ON us.ContactObjectIdTo = co.ContactObjectId
WHERE co.DeletedTime IS NOT NULL


/* Очистка [dbo].[ContactObject] */
DELETE FROM co
FROM dbo.[ContactObject] co 
WHERE co.DeletedTime IS NOT NULL


но дополнительно проблема осложняется тем, что в таблицах очень много данных, и соединение длинных цепочек таблиц выполняется очень долго.

Поэтому просто генерация таких скриптов для удаления не устраивает.
Что делать?
30 мар 14, 22:07    [15808394]     Ответить | Цитировать Сообщить модератору
 Re: Очистка таблиц путем обхода в правильном порядке.  [new]
Glory
Member

Откуда:
Сообщений: 104760
design21
Что делать?

- Оптимизировать полученные запросы
- Удалять частями
31 мар 14, 09:24    [15809314]     Ответить | Цитировать Сообщить модератору
 Re: Очистка таблиц путем обхода в правильном порядке.  [new]
Crimean
Member

Откуда:
Сообщений: 13148
так все же, "удалять" или "помечать"?
если "удалять" и задача стоит "именно так" (удалять!), то почему бы и не каскадирование? само все схлопнется + фильтры и т.д.
если же "помечать", то правила игры могут быть достаточно лукавыми и просто прохода по связям может быть недостаточно - намекаю на то, что должна быть весьма прозрачная постановка от прикладного уровня
ибо, к примеру, зачастую, если элемент справочника "метят" как "удаленный", то это означает невозможность добавлять новые зависимые элементы со ссылками на этот "удаленный" элемент. но никак автоматом не делает существующие уже ссылающиеся элементы "удаленными" - для этого нужна явная "воля" пользователя
ну и - да - объемы.. и тут с каскадированием, действительно, можно серьезно влететь, да и с обеспечением доступности системы на время такого удаления могут быть серьезные проблемы, чаще "такое" в регламент некий выносят со всеми втекающими и вытекающими - четкое оговаривание кого, когда, куда и зачем..
31 мар 14, 21:13    [15813549]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить