Логічне видалення даних

Для посилення підтримки цілісності даних багато розробників перешкоджають фізичній видаленню інформації – вони замінюють його логічним видаленням Найбільш поширеним методом є створення спеціального бітового стовпця прапора видалення Коли користувач видаляє деякий рядок з клієнтського застосування, тригер маркує її як віддалену, встановлюючи прапор в значення true Прапор логічного видалення може бути реалізований декількома способами

■ Його установку може виконувати саме клієнтську програму

■ Це завдання може бути покладена на збережену процедуру

■ Тригер INSTEAD OF може перехоплювати інструкції DELETE і замість фізичного видалення встановлювати прапор

Прапор логічного видалення не варто розглядати як якийсь просунутий На замітку метод Ще СУБД DBASE III використовувала цей прапор для маркування вилучених рядків, а фізичне видалення здійснювала тільки при операції стиснення файлу

Здатність логічного видалення є непоганим методом, застосовуваним в добре продуманих базах даних Однак рекомендується розглядати цей метод при проектуванні баз даних в останню чергу, оскільки сама реалізація займає масу часу, до того ж будь-яка зміна схеми бази може зруйнувати систему логічного видалення У справжньому розділі ми розглянемо систему логічного видалення рядків в одній таблиці При цьому будуть розглянуті проблеми, повязані з нею, та запропоновано шляхи їх обходу

Тригери логічного видалення

Тригери INSTEAD OF реалізують систему логічного видалення на рівні таблиці і гарантують її постійну функціональність Ці тригери переслідують дві мети: перетворення фізичного видалення в логічне і реалізацію деякого методу реального фізичного видалення рядків

Пропонований тригер дозволяє користувачеві sa фізично видалити будь-який рядок, таким чином надаючи метод очищення бази даних Тригер INSTEAD OF не має рекурсивного дії, так що міститься в ньому інструкція DELETE не приведе до повторного його викликом і буде виконана Перша команда в даному прикладі змінює структуру таблиці Product, додаючи в неї бітовий прапор IsDeleted:

ALTER TABLE Product

ADD IsDeleted BIT NOT NULL DEFAULT 0

CREATE Trigger Product_LogicalDelete On dboProduct INSTEAD OF Delete AS

IF (suser_sname() = sa)

BEGIN

PRINT фізичне видалення

DELETE FROM dboProduct FROM dboProduct JOIN Deleted

ON ProductProductID = DeletedProductID

END

ELSE

BEGIN

PRINT логічне видалення

UPDATE Product

SET IsDeleted = 1 FROM dboProduct JOIN Deleted

ON ProductProductID = DeletedProductID

END

Для тестування тригера логічного видалення ми виконаємо наступний запит, який видаляє рядки з таблиці Product Слід особливо відзначити, що я зареєструвався в системі як Noli \ Paul:

DELETE Product

WHERE Code = 4053

Отримаємо наступний результат:

Логічне видалення

Тепер подивимося на прапор логічного видалення:

SELECT Code, IsDeleted FROM dboProduct WHERE Code = 1053

Буде отримано наступний результат:

Code      IsDeleted

1053     1

Підключившись заново як користувач sa, я знову спробував виконати ту ж інструкцію DELETE:

DELETE Product

WHERE Code = 1053

Результат буде наступний:

Фізичне видалення (1 row (s) affected)

(1 row(s) affected)

Перший рядок (1 row (s) affected) стала результатом вихідної інструкції DELETE Незважаючи на те що вона була перехоплена тригером INSTEAD OF і вихідне видалення ігнорувалося, інструкція відрапортувала про успіх операції Другий рядок (1 row (s) affected) стала результатом виконання інструкції DELETE вже в тригері Product_LogicalDelete Насправді саме ця інструкція і видалила фізично рядок таблиці

Відновлення логічно вилучених рядків

Перш ніж бути фізично віддаленої користувачем sa, рядок може бути відновлена ​​установкою значення стовпця IsDeleted в значення false Якщо в базі даних реалізований метод забезпечення безпеки даних на рівні рядків, описаний раніше в цьому розділі, то тригер AFTER UPDATE може перевірити наявність у користувача адміністративних привілеїв на даний рядок і відновити значення стовпця IsDeleted в 0

Фільтрація логічно вилучених рядків

Головною проблемою в реалізації схеми логічного видалення є те, що будь-яка інструкція SELECT повинна враховувати прапор IsDelete, в іншому випадку видалені дані можуть нашкодити результату операції Кращою гарантією того, що клієнтські програми витягують тільки поточні та коректні дані, є використання для відбору даних збережених процедур

У той же час, коли користувач намагається виконати написаний вручну запит, немає ніякої гарантії того, що він врахує прапор IsDeleted Тут краще надати йому тільки уявлення, а також табличні користувача функції для вилучення даних, при цьому обмеживши коло осіб, допущених до них, за допомогою засобів системи безпеки SQL Server

Каскадне логічне видалення

Це саме той випадок, коли логічне видалення даних перетворюється на суцільний кошмар Якщо первинна рядок фізично віддаляється, то вторинна рядок, яка не має сенсу без первинної, також повинна бути вилучена Питається, чи можна в логічному видаленні також реалізувати принцип каскадного

Якщо логічно видаляється замовлення, то повязані з ним рядки можуть бути також логічно видалені при наступних операціях читання або запису Обидва ці методи мають свої проблеми

Реалізація каскадного видалення під час читання

Якщо замовлення логічно видалений, то його рядки повинні бути виключені з будь-яких обчислень, повязаних з відкритими замовленнями Одним з можливих методів реалізації є обєднання з таблицею замовлень і включення в пропозицію WHERE поля Order IsDeleted Це завдання може стати досить складною, якщо логічний звязок поширюється на безліч рівнів З власного досвіду знаю, що реалізація каскадного логічного видалення в операціях читання може геть-чисто вбити продуктивність, так як число таблиць, обєднань та пропозицій WHERE зростає експоненціально, щоб покрити всілякі логічні комбінації

Реалізація каскадного видалення під час запису

Якщо вторинні рядки логічно видаляються в процесі логічного видалення первинних, то наступним операціям читання немає ніякої необхідності перевіряти первинну таблицю на предмет логічно віддалених рядків Проблеми виникають тоді, коли вторинні або первинні рядки були видалені самостійно

Тут можна використовувати два прапори, один з яких є маркером логічного видалення рядка, а другий-маркером каскадного логічного видалення Навіть якщо сьогоднішнє рішення викличе проблеми тільки в майбутньому, це все одно викликає головний біль Припустимо, що замовлення логічно видалений і логічна операція видалення направлена ​​в таблицю рядків замовлення Одна з рядків замовлення вказує на товар, який також логічно видаляється Згодом при відновленні замовлення всі його рядки будуть також логічно відновлені, за винятком тієї, яка вказує на логічно віддалений товар

Існують два можливих рішення цієї проблеми Перше з них полягає в додаванні прапора логічного каскадного видалення для кожного відносини зовнішнього ключа таблиці Це зробить програмний код більш заплутаним (особисто мені це рішення не подобається)

Друге рішення полягає у використанні єдиного прапора каскадного видалення і створенні більш складної системи відновлення, яка перевіряє кожне з відносин первинної таблиці, перш ніж відновити рядок Незважаючи на те що цей метод передбачає дуже багато роботи, це рішення я вважаю кращим

Логічне видалення і посилальна цілісність

Реалізація повноцінної системи логічного видалення піднімає ще одну проблему – підтримання посилальної цілісності Цей принцип буде порушуватися при будь-якій спробі звернення до рядка, яка була логічно вилучена Механізму декларативної посилальної цілісності SQL Server не важно, чи була логічно видалена рядок, – йому важливо, щоб вона фізично присутня в таблиці

База даних з системою логічного видалення вимагає складного тригера підтримки посилальної цілісності, який перевіряє не лише існування значення первинного ключа головної таблиці, але і прапор IsDeleted

Маркування неактивності

Системи, в яких реалізований механізм логічного видалення, часто мають ще один рівень, що визначає бездіяльність рядки, наприклад прапор активності або застарілості даних Ці прапори дозволяють користувачеві маркувати рядки як недійсні без необхідності видалення даних Наприклад, в лабораторії R & D, що займається розробкою нових матеріалів, дослідникам не хочеться постійно стикатися з тисячами застарілих версій формул У той же час вони не хочуть видаляти і самі існуючі дані Маркування формули як недійсною дозволяє приховати її, проте у разі необхідності – витягти

Архівування даних

Старі дані часто не потрібні в повсякденній роботі, тому їх можна безболісно заархівувати або перемістити в іншу базу даних Найпростіше заархівувати дані, перемістивши їх в окрему таблицю, що має ту ж структуру, що і початкова При цьому таблиця може знаходитися як в поточній базі даних, так і в іншій

Архівування даних є чудовою альтернативою логічного видаленню При цьому абсолютно зникають проблеми підтримки посилальної цілісності і каскадних логічних вилучень

Збережена процедура може запросто реалізувати вставку даних в архівну таблицю і видалення їх з поточною Наведемо приклад:

CREATE PROCEDURE pProduct_Archive (

@Code CHAR(15) )

AS

SET NoCount ON BEGIN TRANSACTION

INSERT Product_Archive SELECT *

FROM dboProduct WHERE Code = @Code IF @@ERROR &lt&gt 0 BEGIN

ROLLBACK TRANSACTION RETURN

END

DELETE dboProduct WHERE Code = @Code IF @@ERROR &lt&gt 0 BEGIN

ROLLBACK TRANSACTION END

COMMIT TRANSACTION RETURN

Збереженій процедурі, швидше за все, доведеться переміщати рядки декількох таблиць Наприклад, при архівуванні замовлень слід перемістити рядки таблиці [Order] і таблиці OrderDetail

Резюме

У цьому розділі ми змусили Т-SQL вирішити безліч складних питань Складну обробку даних і підтримку правил бізнес-логіки найкраще реалізувати за допомогою серверного програмного коду Тільки коли правило реалізовано на сервері, гарантується його стовідсоткове коректне виконання Реалізацію правил за межами сервера, швидше за все, можна розглядати як виняток Серверний програмний код ідеально підходить для процедур INSERT, UPDATE, DELETE і FETCH, складних бізнес-правил, підтримки складної посилальної цілісності, ведення журналів аудиту даних і логічного видалення

У наступному розділі ми продовжимо вивчення теми програмування на Т-SQL і розглянемо рішення ще більш наближених до практики завдань

11&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp

Джерело: Нільсен, Пол Microsoft SQL Server 2005 Біблія користувача : Пер з англ – М: ООО ІД Вільямс , 2008 – 1232 с : Ил – Парал тит англ

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*