Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
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] Ответить | Цитировать Сообщить модератору |
Glory Member Откуда: Сообщений: 104760 |
Использовать системное представление, в котором хранится информация о всех таких констрейтах |
||
11 фев 14, 12:22 [15550439] Ответить | Цитировать Сообщить модератору |
iap Member Откуда: Москва Сообщений: 47047 |
Каскадных FK нет - значит: 1. Отключить (или удалить, но тогда сначала заскриптовать) констрейнты 2. Удалить записи 3. Включить (или создать подготовленным вначале скриптом) констрейнты. |
11 фев 14, 12:38 [15550538] Ответить | Цитировать Сообщить модератору |
design21 Member Откуда: Minsk Сообщений: 59 |
ну может есть идеи алгоритма?
Отключать боюсь - может нарушится целостность, если вдруг не совсем корректно данные помечались при удалении. Если к примеру родительская запись помечена удаленной, а дочерняя нет. |
||||||
11 фев 14, 12:56 [15550703] Ответить | Цитировать Сообщить модератору |
Glory Member Откуда: Сообщений: 104760 |
Хм FK констрейты могут представлять собой дерево Делаем рекурсивный запрос к системному представлению и строим иерерхию таблиц. Потом начинаем с самого нижнего уровня этой иерархии перебирать таблицы, проерять в них наличие поля, строить динамический запрос с удалением. И выполнять этот запрос Вполне возможно, что рекурсивным запросом можно создать текст динамического запроса сразу для всех таблиц |
||
11 фев 14, 13:06 [15550769] Ответить | Цитировать Сообщить модератору |
HandKot Member Откуда: Sergiev Posad Сообщений: 3015 |
включить каскадное удаление и просто удалять? |
||
11 фев 14, 13:07 [15550786] Ответить | Цитировать Сообщить модератору |
Glory Member Откуда: Сообщений: 104760 |
Вот тут кстати есть непонятный момент. Не будет так, что в родительской таблице запись помечена как удаленная, а в дочерней не все зависимые записи помечены как удаленные ? |
||
11 фев 14, 13:10 [15550807] Ответить | Цитировать Сообщить модератору |
HandKot Member Откуда: Sergiev Posad Сообщений: 3015 |
Glory, тоже об этом думал, но решил, что родительский уровень перекрывает дочерний уровень. Иначе задача нерешаема |
11 фев 14, 13:14 [15550844] Ответить | Цитировать Сообщить модератору |
Glory Member Откуда: Сообщений: 104760 |
Тогда все намного сложнее Потому что в запросе на удаление из дочерней таблицы придется писать соединение с родительской таблицей. |
||
11 фев 14, 13:16 [15550859] Ответить | Цитировать Сообщить модератору |
HandKot Member Откуда: Sergiev Posad Сообщений: 3015 |
Glory, я может неправильно выразился. Имелось ввиду, что если родитель "удалён", то и удалять все дочерние НЕЗАВИСИМО от статуса. Поэтому не надо никаких соединение с родительской таблицей и решается всё простым каскадным удалением ЗЫЖ сам грешу иногда каскадным удалением, хоть и понимаю, что чревато |
11 фев 14, 13:20 [15550882] Ответить | Цитировать Сообщить модератору |
Glory Member Откуда: Сообщений: 104760 |
Ой, я перепутал вас с ТС-ом. С каскадными - все понятно. Непонятно, как у ТС обстоит дело с приоритетами признака удаления |
||
11 фев 14, 13:22 [15550900] Ответить | Цитировать Сообщить модератору |
design21 Member Откуда: Minsk Сообщений: 59 |
Если родитель удален, то можно удалять дочерние записи |
||||
11 фев 14, 13:35 [15550982] Ответить | Цитировать Сообщить модератору |
design21 Member Откуда: Minsk Сообщений: 59 |
Чтобы строить дерево, надо сначала определить корневые таблицы. Я так понимаю признаком корневой таблицы будет отсутствие дочерних? |
||||
11 фев 14, 13:38 [15551009] Ответить | Цитировать Сообщить модератору |
Glory Member Откуда: Сообщений: 104760 |
sys.foreign_keys |
||
11 фев 14, 13:43 [15551045] Ответить | Цитировать Сообщить модератору |
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] Ответить | Цитировать Сообщить модератору |
design21 Member Откуда: Minsk Сообщений: 59 |
invm, Спасибо, огромнейшее! |
11 фев 14, 14:38 [15551473] Ответить | Цитировать Сообщить модератору |
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] Ответить | Цитировать Сообщить модератору |
design21 Member Откуда: Minsk Сообщений: 59 |
Скрипт по указанной ссылке оказался не такой уж и хороший. Скрипты генерирует неправильные. А у нас опять возникла задача очистки таблиц. Но теперь не всех, а только некоторых, не полная а от записей помеченных удаленными. Дополнительно надо уделить из связанных таблиц исторические данные. Например вот так: 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] Ответить | Цитировать Сообщить модератору |
Glory Member Откуда: Сообщений: 104760 |
- Оптимизировать полученные запросы - Удалять частями |
||
31 мар 14, 09:24 [15809314] Ответить | Цитировать Сообщить модератору |
Crimean Member Откуда: Сообщений: 13148 |
так все же, "удалять" или "помечать"? если "удалять" и задача стоит "именно так" (удалять!), то почему бы и не каскадирование? само все схлопнется + фильтры и т.д. если же "помечать", то правила игры могут быть достаточно лукавыми и просто прохода по связям может быть недостаточно - намекаю на то, что должна быть весьма прозрачная постановка от прикладного уровня ибо, к примеру, зачастую, если элемент справочника "метят" как "удаленный", то это означает невозможность добавлять новые зависимые элементы со ссылками на этот "удаленный" элемент. но никак автоматом не делает существующие уже ссылающиеся элементы "удаленными" - для этого нужна явная "воля" пользователя ну и - да - объемы.. и тут с каскадированием, действительно, можно серьезно влететь, да и с обеспечением доступности системы на время такого удаления могут быть серьезные проблемы, чаще "такое" в регламент некий выносят со всеми втекающими и вытекающими - четкое оговаривание кого, когда, куда и зачем.. |
31 мар 14, 21:13 [15813549] Ответить | Цитировать Сообщить модератору |
Все форумы / Microsoft SQL Server | ![]() |