Lokalne reguły segregowania danych

Marek Wierzbicki

Wiele informacji przechowywanych w różnych bazach to dane tekstowe. Nazwisko, nazwa czy adres klienta to dane istniejące chyba we wszystkich firmowych bazach na całym świecie. Pełną funkcjonalność procedur czy aplikacji osiągamy wtedy, gdy informacje te możemy efektywnie sortować, porównywać i przeszukiwać. Zbiór reguł i zasad rządzących tymi działaniami jest jednoznacznie określony przez sposób segregowania danych, zwany w MS SQL collation. Wcześniejsze wersje umożliwiały stosowanie jednej reguły segregowania w stosunku do wszystkich obiektów jednej instalacji SQLa. Od wersji 2000 możliwe jest stosowanie lokalnie różnych reguł. W dzisiejszym artykule pokażę kilka przykładów takiej lokalności i uwypuklę szczegóły stosowania takich rozwiązań.

Zanim przejdę do opisu używania różnych metod segeregowana danych, powinienem wytłumaczyć co w Microsoftowej implementacji SQL kryje się pod pojęciem collation. Dla uproszczenia przyjmijmy, że zajmujemy się tylko polskim fragmentem tego problemu, to znaczy interesuje nas tylko sortowanie i porównywanie danych zapisanych z użyciem języka polskiego. Oczywiście problem będzie miał zbliżone rozwiązanie w języku czeskim, węgierskim czy suahilli.

Wrażliwość porównań

Zacznijmy od kwestii porównywania ze sobą różnych znaków (liter). Pozornie wszystko jest bardzo proste. Wiadomo, że znaki "A" i "B" są różne, i nie ma co do tego żadnych wątpliwości. Ale jak jest ze znakami "A" i "a"? Biorąc pod uwagę wartość numeryczną kodów obu znaków, są one różne. Jednak nie zawsze takie rozróżnienie jest konieczne (czasem nawet nie jest wskazane). Wyobraźmy sobie system, który gromadzi dane pewnego rodzaju przypisując je do nazwiska i imienia (oczywiście system taki byłby bardzo zawodny, gdyż może istnieć wiele osób o tym samym imieniu i nawisku, ale pomińmy ten problem). Korzystanie z natywnego (binarnego) porównania skutkowałoby tym, że dane gromadzone dla osoby "KOWALSKI JAN" byłyby rozdzielne w stosunku do danych, zgromadzonych dla osoby"Kowalski Jan". Jeśli pamiętamy, że wiele modułów tego samego projektu wykonywane jest rozłącznie (a często przez różne firmy) oraz jeśli uwzględnimy różne przyzwyczajenia poszczególnych operatorów systemu mogłoby się okazać, że jedna soba istnieje w bazie kilka razy, a system traktuje ją jako różne osoby. Aby zapobiec takim przypadkom, oraz ułatwić przeszukiwanie danych wprowadzono możliwość zastosowania porównywań, które będzie niewrażliwe na wielkość liter (case insensitive). Przeciwieństwem tego przypadku jest wrażliwość na wielkość liter (case sensitive), które skutkuje tym, że takie same litery duże i małe (minuskuły i manuskułu) są traktowane tak samo jak różne litery (to znaczy "A"<>"a").

Innym wariantem tej kwestii jest wrażliwość na znaki diakrytyczne. Korzystanie z komputerów w epoce przed Windowsowej często wiązało się z problemami stosowania tak zwanych znaków narodowych (w Polsce ą, ę ś, ć, ż, ź, ń, ł, ó). Znaki te nie występowały (a często do dziś nie występują) na komputerowych klawiaturach. Zanim powstały różne standardy kodowania polskich liter (w tym popularna Mazovia, która używała oryginalnych znaków angielskich tzw. "górnych", które były najbardziej podobne do polskich), problem ten był rozwiązywany na dwa sposoby. Pierwszy (można jeszcze spotkać, jeśli koresponujemy po Polsku z osobami mieszkającymi za granicą) to stosowanie tzw. pisowni "polskawej", czyli zapis tekstu po polsku z odrzuceniem tak zwanych ogonków ("ę" to "e", "ą" to "a" itd.). Drugi (stosowany zwłaszcza na wydrukach w drukarkach igłowych) to nakładanie ogonków (przecinków, akcentów czy znaków przekreślenia) na zwykłe (angielskie) litery. Uwzględnienie obu tych przypadków umożliwi bezproblemowe zrozumienie znaczenia pojęć "wrażliwość na akcenty" (accent sensitive, czyli na przykład "ą"<>"a") i "niewrażliwe na akcenty" (accent insensitive, czyli na przykład "ą"="a").

Rzadziej (w naszej strefie językowej) stosowane są dwa inne przełączniki wrażliwości. Kana sensitive oznacza wrażliwość na sylaby katakana i hiragana języka japońskiego. Width sensitive oznacza wrażliwość na szerokość liter (nie jestem językoznawcą, więc nie mam bladego pojęcia jakiego języka może dotyczyć ten problem).

Uwzględnienie dwóch podstawowych reguł porównań doprowadzi nas do istnienia (dla większości języków) czterech wariantów metod porównywania liter:

CS_AS - wrażliwe na wielkość, wrażliwe na akcenty
CS_AI - wrażliwe na wielkość, nie wrażliwe na akcenty
CI_AS - nie wrażliwe na wielkość, wrażliwe na akcenty
CI_AI - nie wrażliwe na wielkość, nie wrażliwe na akcenty

Wymienione określenia mają sens wyłącznie wtedy, gdy stosujemy porównanie słownikowe (litera po literze), a nie binarne.

Oczywiście z kwestii porównywania znaków wynika również kolejność sortowania tekstów z użyciem odpowiednich reguł. Wiadomo bowiem, że jednym z podstawowych działań w trakcie sortowania jest porównywanie ze sobą poszczególnych znaków i ocena, który jest "większy", a który "mniejszy" (który powinien "stać" bliżej początku, a który bliżej końca).

Lokalność dyrektywy collation

Jak już napisałem, wcześniejsze wersje MS SQLa umożliwiały stosowanie wyłącznie globalnych reguł segregowania danych (w obrębie całej bazy, lub nawet całej instalacji). W MS SQL 2000 sytuacja znacznie się poprawiła. Obecnie nawet w obrębie jednej tabeli możliwe jest stosowanie różnych reguł. Tabela utworzona w pokazany sposób umożliwia przechowywanie danych tekstowych z uwzględnieniem wrażliwości na wielkość liter (kolumna txt_cs) jak i jej braku (kolumna txt_ci):

CREATE TABLE mwi_test_csi(
id int,
txt_cs char(2) collate SQL_Polish_Cp1250_CS_AS,
txt_ci char(2) collate SQL_Polish_Cp1250_CI_AS
)

Brak określenia collate oznacza, że dla danej kolumny stosowane jest domyślne ustawienie tej bazy.

Oczywiście fakt, że dana kolumna w tabeli nie jest wrażliwa na wielkość znaków nie oznacza wcale, że wszystkie dane są w niej przechowywane wyłącznie z użyciem jednego typu znaków. W tabeli o zaproponowanej przeze mnie strukturze mogą się znaleźć teksty zawierające zarówno wielkie jak i małe litery. Na potrzeby tego artykułu pokazaną tabelę wypełniłem 6 rekordami. Ich zawartość pokazuje, że dane są w niej przechowywane bez żadnej modyfikacji:

id          txt_cs txt_ci 
----------- ------ ------ 
1           AA     AA
2           ab     AB
3           AC     ac
4           ad     ad
5           AB     ab
6           AD     AD

Wrażliwość na wielkość liter pojawia się dopiero przy próbie filtrowania danych. Po wykonaniu komendy:

select * from mwi_test_csi where txt_cs='ab'

na ekranie zobaczymy tylko jeden wiersz:

id          txt_cs txt_ci 
----------- ------ ------ 
2           ab     AB

Wynika to z faktu, że porównanie txt_cs='ab' jest wykonywane z uwzględnieniem reguł segregowania, które obowiązują dla tej kolumny (czyli case sensitive). Oczywiście, jeśli filtracja będzie dotyczyła kolumny o innych regułach:

select * from mwi_test_csi where txt_ci='ab'

wynik będzie inny:

id          txt_cs txt_ci 
----------- ------ ------ 
2           ab     AB
5           AB     ab

Jak widać regułę txt_ci='ab' spełnia zarówno ciąg "ab" jak i "AB".

Jednym ze sposobów na ominięcie problemu wielkości liter w kolumnie, w której jest ona ważna, jest wymuszenie w trakcie sprawdzania konwersji danych do dużych liter, tak jak w przykładzie:

select * from mwi_test_csi where upper(txt_cs)='AB'

efektem wykonania tego polecenia jest zestaw danych:

id          txt_cs txt_ci 
----------- ------ ------ 
2           ab     AB
5           AB     ab

Innym sposobem jest jawne zaznaczenie sposobu porównywania, który przykrywa sposób domyślny:

select * from mwi_test_csi where txt_cs='ab' collate SQL_Polish_Cp1250_CI_AS

efekt działania tego polecenia będzie dokładnie taki sam, jak w poprzednim przykładzie (chociaż przyczyna tego stanu będzie zupełnie inna):

id          txt_cs txt_ci 
----------- ------ ------ 
2           ab     AB
5           AB     ab

Warto też wiedzieć, że nadanie różnym kolumnom różnych kryteriów segregowania danych spowoduje, że niemożliwe będzie proste porównanie ich ze sobą. Uruchomienie polecenia:

select * from  mwi_test_csi where txt_cs=txt_ci

będzie skutkowało wynikiem:

Server: Msg 446, Level 16, State 9, Line 1
Cannot resolve collation conflict for equal to operation.

Dokładnie taki sam efekt osiągniemy przy próbie porównania danych z dwóch tabel o różnych regułach sortowania (np. pochodzących z różnych baz czy nawet podlinkowanych serwerów). Aby temu zapobiec musimy zastosować składnię:

select * from  mwi_test_csi where txt_cs=txt_ci collate polish_bin

co pozwoli otrzymać wynik:

id          txt_cs txt_ci 
----------- ------ ------ 
1           AA     AA
4           ad     ad
6           AD     AD

Zwracam uwagę, że do porównania wykorzystałem tu inne reguły, niż obowiązują w każdej z kolumn (oczywiście można również wykorzystać regułę jednej z kolumn).

Podobnie jak z porównaniem będzie z innymi operacjami na kolumnach o różnym typie segregowania. Działanie:

select (txt_ci+txt_cs) as a from mwi_test_csi

nie powiedzie się (ze względu na konflikt sposobu segregowania - po prostu wynik byłby pozbawiony cechy collation). Aby było to możliwe trzeba zastosować konstrukcję:

select (txt_ci+txt_cs collate Polish_BIN) as a from mwi_test_csi

która nada wynikowi konkretną cechę (w tym wypatku Polish_BIN).

Globalny zasięg dyrektywy collate

W czasie instalacji MS SQL 2000 wybieramy globalny i domyślny sposób traktowania różnych liter. Wpływa to na zachowanie MS SQLa w kilku obszarach. Należy do nich sposób potwierdzania hasła dostępu, traktowanie nazw kolumn, tabel, baz czy linkowanych serwerów oraz na działanie wbudowanych funkcji. Osobiście preferuję standard braku wrażliwości na wielkość liter. Jakkolwiek nieznacznie obniża to bezpieczeństwo danych (wielkość liter w haśle dostępu nie jest ważna), jednak daje komfort zapominania o dokładnej postaci np. nazw kolumn. Preferowane przez mnie podejście ma jednak jedną ważną wadę - wszystkie funkcje działają z uwzględnieniem globalnej reguły collate. Czasami skutkuje to sporymi problemami. Wykonanie działania:

set @txt2=replace(@txt, 'ą', 'a')

powoduje zamianę na literę "a" zarówno liter "ą" jak i "Ą"! Przy transferze danych do innego systemu spotkałem się z koniecznością usunięcia z polskich tekstów wszystkich znaków diakrytycznych. Aby było to możliwe w sposób prawidłowy, w MS SQLu 2000, w którym baza była niewrażliwa na wielkość liter musiałem w tym celu stworzyć funkcję postaci:

CREATE FUNCTION polskawe(@txt char(500)) 
RETURNS char(500)
BEGIN 
declare @txt2 char(500)
set @txt2=replace(@txt, 'ą' collate polish_bin, 'a')
set @txt2=replace(@txt2, 'ć' collate polish_bin, 'c')
set @txt2=replace(@txt2, 'ę' collate polish_bin, 'e')
set @txt2=replace(@txt2, 'ł' collate polish_bin, 'l')
set @txt2=replace(@txt2, 'ń' collate polish_bin, 'n')
set @txt2=replace(@txt2, 'ó' collate polish_bin, 'o')
set @txt2=replace(@txt2, 'ś' collate polish_bin, 's')
set @txt2=replace(@txt2, 'ź' collate polish_bin, 'z')
set @txt2=replace(@txt2, 'ż' collate polish_bin, 'z')
set @txt2=replace(@txt2, 'Ą' collate polish_bin, 'A')
set @txt2=replace(@txt2, 'Ć' collate polish_bin, 'C')
set @txt2=replace(@txt2, 'Ę' collate polish_bin, 'E')
set @txt2=replace(@txt2, 'Ł' collate polish_bin, 'L')
set @txt2=replace(@txt2, 'Ń' collate polish_bin, 'N')
set @txt2=replace(@txt2, 'Ó' collate polish_bin, 'O')
set @txt2=replace(@txt2, 'Ś' collate polish_bin, 'S')
set @txt2=replace(@txt2, 'Ź' collate polish_bin, 'Z')
set @txt2=replace(@txt2, 'Ż' collate polish_bin, 'Z')
return(@txt2)
END

Dokładnie taką samą postać miałaby ta funkcja dla bazy niewrażliwej na znaki diakrytyczne, albowiem użyłem tu reguły sortowania dokładnej (z dokładnością do danych binarnych).

Baza, w której domyślnie zastosowano tryb braku wrażliwości na wielkość liter stwarza również problemy przy próbie sprawdzenia, czy dany wpis zawiera duże litery, czy też nie. Przykładowo uruchomienie komendy:

select * from mwi_test_csi where upper(txt_ci)=txt_ci

spowoduje wybór wszystkich!!! wierszy tabeli. Jest to oczywiste, ponieważ brak wrażliwości na wielkość liter (w tym wypadku w kolumnie txt_ci) oznacza, że tekst skonwertowany na duże litery będzie zawsze równy sam sobie przed konwersją (nawet wtedy, gdy był napisany małymi literami). Jeśli więc chcemy w takiej bazie sprawdzić czy tekst zawiera duże litery musimy użyć na przykład funkcji, którą pokazuję poniżej:

CREATE FUNCTION capital(@txt char(500))
RETURNS char(1)
BEGIN
DECLARE @w char
  IF (@txt = UPPER(@txt) COLLATE Polish_BIN)
    SET @w = 'T'
  ELSE
    SET @w = 'N'
RETURN @w
END

Funkcja ta zwraca jedynie informację, czy przekazany do niej tekst zawiera duże litery czy nie. Oczywiście możliwe są bardziej zaawansowane działania, ale w tym celu funkcję należy stosownie przekształcić.

Podsumowanie

W artykule przedstawiłem tylko zarys całego problemu związanego ze sposobem segregowania danych. Wybór każdego z nich jest najczęściej zależny od algorytmów aplikacji, która używa danej tabeli. Dla niektórych algorytmów nieodzowne jest stosowanie pewnych ustawień, dla innych optymalne są alternatywne Czasami musimy przyjąć standard, który występuje i dopasować się do niego czy chcemy, czy też nie. W takim przypadku udzielone dziś rady mogą okazać się bardzo pomocne.