wtorek, 24 stycznia 2012

Sprawdzanie obiektów przed operacją modyfikacji schematu bazy danych

W poprzednim poście pisałem na temat sprawdzania danych przed ich modyfikacją. Obiecałem także napisać kilka słów na temat pisania skryptów zmieniających schemat bazy danych, które będą mogły być wykonane wielokrotnie.
Poniższy skrypt stworzy przykładową strukturę bazy danych.
   1: USE TEMPDB
   2: GO
   3:  
   4: CREATE TABLE COMPANY (
   5:     COMPANY_ID INT IDENTITY NOT NULL PRIMARY KEY,
   6:     NAME NVARCHAR(20) NOT NULL,
   7:     DELETED BIT
   8: )
   9: GO
  10:  
  11: CREATE TABLE BRANCH (
  12:     BRANCH_ID INT NOT NULL IDENTITY PRIMARY KEY,
  13:     COMPANY_ID INT NOT NULL,
  14:     NAME NVARCHAR(20) NOT NULL,
  15:     CONSTRAINT FK_BRANCH_COMPANY FOREIGN KEY(COMPANY_ID) REFERENCES COMPANY(COMPANY_ID)
  16: )
  17: GO
  18:  
  19: ;WITH CTE AS (
  20: SELECT 'Company 1' AS NAME, 0 AS DELETED UNION
  21: SELECT 'Company 2', 0 
  22: )
  23:  
  24: INSERT INTO COMPANY (NAME, DELETED)
  25: SELECT NAME, DELETED FROM CTE 
  26: WHERE NOT EXISTS(SELECT * FROM COMPANY WHERE NAME = CTE.NAME)
  27:  
  28: ;WITH CTE AS (
  29:     SELECT 'Branch 1' AS NAME, 'Company 1' AS COMPANY_NAME UNION
  30:     SELECT 'Branch 2', 'Company 1' UNION
  31:     SELECT 'Branch 1', 'Company 2' UNION
  32:     SELECT 'Branch 2', 'Company 2'
  33: ), CTE2 AS (
  34:     SELECT CTE.NAME, COMPANY.COMPANY_ID FROM CTE
  35:     INNER JOIN COMPANY
  36:     ON CTE.COMPANY_NAME = COMPANY.NAME
  37: )
  38:  
  39: INSERT INTO BRANCH (COMPANY_ID, NAME)
  40: SELECT COMPANY_ID, NAME FROM CTE2
  41: WHERE NOT EXISTS(SELECT * FROM BRANCH WHERE NAME = CTE2.NAME AND COMPANY_ID = CTE2.COMPANY_ID)

Załóżmy, że wystąpiły zmiany w specyfikacji aplikacji, która korzysta z powyższych tabel i kolumna DELETED powinna zostać przeniesiona z tabeli COMPANY do tabeli BRANCH.

Zadanie to zrealizuje prosty skrypt
   1: ALTER TABLE BRANCH ADD DELETED BIT

W przypadku pierwszego wykonania wszystko zadziała poprawnie i kolumna zostanie dodane do tabeli BRANCH. Jak już wspomniałem w poprzednim poście przeważnie moja kontrola nad wykonywaniem skryptów kończy się w momencie dodania kodu do repozytorium. Z racji tego, że nie przepadam za odpowiadaniem na maile związane z błędami wykonania skryptów zawsze staram się zabezpieczyć przed sytuacją gdzie skrypt wykonany jest ponownie i błąd następującej treści mógłby wystąpić.


Msg 2705, Level 16, State 4, Line 1

Column names in each table must be unique. Column name 'DELETED' in table 'BRANCH' is specified more than once.


Spawdzanie czy dana kolumna już istnieje w tabeli


Tak jak w przypadku operacji DML, opisanych w poprzednim poście, tak i tym razem rozwiązanie wymaga dodania tylko jednego warunku.

Poniższy skrypt może być wykonywany wielokrotnie, bez obaw, że pojawi się błąd jak powyżej.

   1: IF NOT EXISTS(SELECT * FROM SYS.COLUMNS WHERE NAME = 'DELETED' AND OBJECT_ID = OBJECT_ID('BRANCH'))
   2: ALTER TABLE BRANCH ADD DELETED BIT

Zapytanie podobnego typu może być wykonane także dla innych typów obiektów. Indeksy, tabele, widoki, wszystkie te elementy istnieją w widokach systemowych. Mogą zostać one odpytane przed dodaniem nowego obiektu.

Sprawdzanie czy kolumna istnieje przed aktualizacją danych


Załóżmy, że nowe wymagania dotyczące projektu mówią także, że wartość DELETED z tabeli COMPANY musi być zapisane kolumnie DELETED w tabeli BRANCH, oraz że kolumna DELETED musi być usunięta z tabeli COMPANY po całej aktualizacji.

Skrypt ten mógłby przyjąć następującą postać

   1: UPDATE BRANCH 
   2: SET DELETED = COMPANY.DELETED
   3: FROM COMPANY
   4: INNER JOIN BRANCH
   5: ON BRANCH.COMPANY_ID = COMPANY.COMPANY_ID
   6:  
   7: IF EXISTS(SELECT * FROM SYS.COLUMNS WHERE NAME = 'DELETED' AND OBJECT_ID = OBJECT_ID('COMPANY'))
   8: ALTER TABLE COMPANY
   9: DROP COLUMN DELETED
  10: GO

Wszystko będzie działać pod warunkiem, że skrypt nie zostanie wykonany ponownie. Wtedy przy próbie kompilacji wysypie się on bo kolumna DELETED już nie istnieje w tabeli COMPANY.

Problemu tego typu nie da się uniknąć poprzez dodanie tylko i wyłącznie następującego warunku przed aktualizacją danych
   1: IF EXISTS(SELECT * FROM SYS.COLUMNS WHERE NAME = 'DELETED' AND OBJECT_ID = OBJECT_ID('COMPANY'))

Aby obejść ten problem i mieć święty spokój w momencie kiedy skrypt ponownie byłby uruchomiony trzeba skorzystać z dynamicznego SQL-a.

Zapytanie, które będzie aktualizować dane przyjmie wtedy następującą postać
   1: IF EXISTS(SELECT * FROM SYS.COLUMNS WHERE NAME = 'DELETED' AND OBJECT_ID = OBJECT_ID('COMPANY'))
   2: BEGIN
   3:     DECLARE @SQL NVARCHAR(MAX)
   4:     SET @SQL = 'UPDATE BRANCH 
   5:     SET DELETED = COMPANY.DELETED
   6:     FROM COMPANY
   7:     INNER JOIN BRANCH
   8:     ON BRANCH.COMPANY_ID = COMPANY.COMPANY_ID'
   9:     
  10:     EXEC(@SQL)
  11: END

Warto więc dodać kilka lini kodu więcej, aby mieć pewność, że w momencie wielokrotnego wykonania skryptu nie zostaną zgłoszone jakiekolwiek błędy.

Prześlij komentarz