wtorek, 24 stycznia 2012

Sprawdzanie danych przed operacjami modyfikacji

Jednym z najprostrzych sposobów na uniknięcie błedów, które mogą pojawić się podczas wykonywanie skryptów jest weryfikacja danych, które ewentualnie powinny być zmodyfikowane.
W momencie kiedy pisze jakiś skrypt moja kontrola nad nim kończy się w momencie kiedy zostanie dodany on to repozytorium kodu.
Później nie mam już żadnego wpływu na to kto będzie go uruchamiał i jak wiele razy. Tak więc aby zabezpieczyć się przed ewentualnymi błędami, które mogą wyniknąć z wielokrotnego wykonania skryptów zawsze staram się działać w tej kwesti proaktywanie.

Dodawanie danych tylko jeżeli ich nie ma

W przypadku, gdy nowe dane powinny zostać dodane do tabeli najłatwiejszym sposobem unikcięcia błędów jest sprawdzenie czy danych tych przypadkiem w tabeli już nie ma.
Weźmy dla przykładu tabelę z kodami pocztowymi
   1: create table Postcodes (
   2:     postcode char(6) not null primary key,
   3:     city nvarchar(30)
   4: )

Załóżmy teraz, że musimy dodać następujące dane do tabeli
1: insert into Postcodes (postcode, city)
   2: select '30-001', N'Kraków' union
   3: select '30-123', N'Kraków' union
   4: select '00-001', N'Warszawa'

Pierwzze wykonanie skryptu oczywiście się powiedzie, ale jak już wspomniałem wcześniej bardzo często kontrola nad tym kto wykonuje i ile razy skrypt jest niemożliwa.

Wykonując powyższy skryp dodawania danych ponownie pojawi się błąd następującej treści.

Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'PK__Postcode__F27E9E8B0DAF0CB0'. Cannot insert duplicate key in object 'dbo.Postcodes'.

The statement has been terminated.


Niespodzianek tego typu łatwo jest uniknąć pod warunkiem, że dane które mają być dodane na wstępie zostaną zweryfikowane pod kątem ich istnienia w tabeli.

Modyfikując zapytanie w następujący sposób problem tego ile razy skrypt zostanie uruchomiony przestaje istnieć.
   1: ;with cte as (
   2: select '30-001' postcode, N'Kraków' city union
   3: select '30-123', N'Kraków' union
   4: select '00-001', N'Warszawa'
   5: )
   6:  
   7: insert into Postcodes (postcode, city)
   8: select postcode, city from cte where not exists (
   9:     select * from postcodes where postcode = cte.postcode
  10: )

W przypadku SQL Servera 2008 i wyższych zapytanie można by zmodyfikować i użyć MERGE
   1: ;with cte as (
   2: select '30-001' postcode, N'Kraków' city union
   3: select '30-123', N'Kraków' union
   4: select '00-001', N'Warszawa'
   5: )
   6:  
   7: merge Postcodes p
   8: using cte c
   9: on p.postcode = c.postcode
  10: when not matched then
  11: insert (postcode, city)
  12: values (postcode, city);

Weryfikacja danych przed aktualizacją

Podobny problem może zaistnieć w przypadku aktualizacji danych

Załóżmy, że omyłkowo do tabeli został dodany kod pocztowy 00-000 zamiast 00-001
   1: if not exists(select * from Postcodes where postcode = '00-000')
   2: insert into Postcodes (postcode, city)
   3: values('00-000', N'Warszawa')
Wykonując aktualizację bez sprawdzenie czy poprawny kod pocztowy już istnieje narażamy sie ponownie na błąd
   1: update Postcodes
   2: set postcode = '00-001'
   3: where postcode = '00-000'

Msg 2627, Level 14, State 1, Line 1

Violation of PRIMARY KEY constraint 'PK__Postcode__F27E9E8B0DAF0CB0'. Cannot insert duplicate key in object 'dbo.Postcodes'.

The statement has been terminated.


Zabezpieczenie się przed tego typu problemem wymaga tylko jednej dodatkowej lini kodu
   1: if not exists(select * from Postcodes where postcode = '00-001')
   2: update Postcodes
   3: set postcode = '00-001'
   4: where postcode = '00-000'
Jak widac na powyższych przykładach zabezpieczenie się przed błędami, które mogą wyniknąć z wielokrotnego uruchamiania skryptów wymaga niewielkiej ilości dodatkowego kodu.

W kolejnym poście napiszę o operacjach modyfikacji struktury bazy danych i jak się zabezpieczyć przed wpadkami związanymi wielokrotnym wykonywaniem skryptów.

Prześlij komentarz