Szöszmösz az adatbázisban

Múlt héten elkezdett egy adatbázisban némelyik rekord köhögni, nem akarta rendesen visszaadni a WCF service és furcsa hibákat írogatott. A hiba valami olyasmire utalt, hogy nem sikerült valami automatikus konverzió:

Unable to translate Unicode character \uD83D at index 0 to specified code page.

Említ még EncoderExceptionFallbackBuffert, UnsafeGetUTF8Length-et, szóval gyanús az egész.

Hmm, kérdezzük csak le ezt a sort simán Management Studio-ból, mit mutat? Valami szöveg, blablabla, és egy szöszmösz a végén. De ez mi a fene lehet? Gyors netes keresés a fenti kódra: ez egy smiley. Ezzel meg is érkeztünk a probléma gyökeréhez.

MSSQL 2008-at használunk, van egy nvarchar(x) oszlopunk, amiben természtesen UTF szöveget tárolunk. De UTF és UTF között is van különbség, hiszen van UTF-8, UTF-16, UTF-32, big endian, little endian…

A Windows és az MSSQL eredetileg az UCS-2 encodingot implementálta, amit sokszor pongyolán csak Unicode-nak hívunk (többek között azért mert nagyjából mondjuk megfelel az UTF-8-nak, bővebben ezen a linken). Abban az időben az UTF-16 szabvány még nem volt végleges, ezért a biztosra mentek. Ez később változott, de mivel a kompatibilitás fontos, nem halt ki teljesen.

Az UTF-8 karakterek 0 és 65535 között vannak (0x0000 – 0xFFFF), az UTF-16 karakterek 0 és 1114111 (0x000 – 0x10FFFF), amiben a 65535 és alatti rész 2 byte-on, az a feletti 4 byte-on tárol egy-egy karaktert. Az UTF-8-ban van két range, amit annak idején szabadon hagytak (0xD800 – 0xDBFF és 0xDC00 – 0xDFFF) és ezek kettős kombinációival tudják belőni a maradék karaktereket. Ezeket a kettős kombinációkat hívják Surrogate Paireknek és az így leírt karaktereket hívják Supplementaty Charactereknek.

De hát mondhatnánk, ez mit érdekel minket? Megmondtuk, hogy az adatbázisunk unicode szövegeket fog tartalmazni, legyen már olyan szíves ezt lekezelni! Hát nincs olyan szíves. Illetve nincs olyan szíves, ha nem adunk neki egy kis segítséget.

Az MSSQL fejlődése során eljött az az idő, amikor ez már problémákat okozott, és nem csak néha-néha, mint nálunk. Ezért bevezették MSSQL 2012-től a teljes UTF-16 támogatást, de ismét csak a kompatibilitásra gondolva nem kérdés nélkül: létrehoztak számára egy külön collation-sorozatot, amit az SC postfix jelez (nem CS, mint case sensitive, hanem SC mint supplementary characters).

Tehát ha ilyen karaktereket szeretnénk tárolni, akkor ez a collation kell nekünk azon az oszlopon, amit érint. Ez egyrészt jótékonyan kihat a fenti problémára, kezelni tudjuk vele a Surrogate Paireket, másrészt a különféle karakter-berhelő funkciók is jól fognak működni (mint például a len, a sort vagy az összehasonlítások, a fenti MS linken erről is kaptok infót).

Sajnos MSSQL 2012 alatt ennek nincs hivatalos támogatása, nincs ilyen collation és így hirtelen csak egy megoldás jut eszembe.

Ez pedig az, hogy a szövegünket nem nvarchar mezőben tároljuk, hanem varbinary-ban. Ebbe ugye mehet minden, nem nagyon számít neki szöveg kódolás és hasonlók. Cserébe viszont nem is tudjuk rendese szövegként használni SQL-ben, vagyis indexelés, keresés, string funkciókat csak konvertálás után tudunk rajta érvényesíteni, ami nem a legjobb megoldás. A rendezést meg elfelejthetjük úgy, ahogy van.

Illetve itt egy másik lehetőség: kiszedni minden olyan karaktert, ami nem fér be a nem SC collation-ű oszlopunkba még mielőtt eljutna az SQL szerverig.