Autor

Artur Kozubski

 

Funkcje użytkownika w procedurach składowanych SQL Firebird – integracja z podprogramami C/C++

 

Firebird to znakomity, darmowy System Zarządzania Relacyjnymi Bazami Danych  (ang. RDBMS Relational Database Management System) wywodzący się bezpośrednio od komercyjnego serwera InterBase 6.0 firmy Inprise (obecnie Borland), którego źródła zostały upowszechnione jako open source w roku 2000. Pozwala między innymi na używanie procedur składowanych (ang. stored procedures) i wyzwalaczy (ang. triggers). Procedury składowane i wyzwalacze pozwalają na przeniesienie części logiki biznesowej na serwer bazy danych i uwolnienie aplikacji klienckiej od zadań związanych np. z zaawansowanym filtrowaniem pobieranych danych, czy złożonymi obliczeniami, co w znaczący sposób upraszcza ją oraz zwiększa jej niezawodność i wydajność. Unika się w ten sposób ściągania dużych ilości danych na stronę aplikacji klienta i wymagane operacje odbywają się po stronie maszyny, na której uruchomiony jest Firebird.

   Możliwości, jakie oferują procedury języka SQL są wystarczające w większości przypadków. Jednak na pewnym poziomie złożoności implementowanych mechanizmów, realizacja niektórych zadań w samym SQLu jest poważnie utrudniona, mało wydajna lub nawet niemożliwa. W takich sytuacjach, ze względu na charakter wąsko wyspecjalizowanego języka, trzeba skorzystać z możliwości, jakie dają języki uniwersalne. Do takich języków należą między innymi C i C++. Tematem niniejszego artykułu jest właśnie rozszerzenie funkcjonalności procedur SQLa przy pomocy zewnętrznych podprogramów pisanych w języku C. Jeśli używasz Firebirda, napotkałeś na problem, którego nie możesz rozwiązać za pomocą samego SQLa i nie używałeś wcześniej UDFów, to ten artykuł jest dla Ciebie.

Funkcje definiowane przez użytkownika

Firebird umożliwia intergację procedur języka SQL z innymi językami za pomocą tzw. UDFów (ang. UDF User Defined Function). W naszym przypadku będzie to język C jako naturalny język Firebirda w którym to został napisany (obecnie Firebird jest rozwijany w języku C++). Oczywiście nic nie stoi na przeszkodzie w użyciu dowolnego innego języka umożliwiającego tworzenie bibliotek dynamicznych DLL (ang. DLL Dynamic Link Library), jak na przykład Delphi lub nawet Visual Basic. W wyjątkowej sytuacji można też użyć asemblera (na przykład w celu wykorzystania takich technologii jak: MMX, 3DNow! czy SSE). Osobiście uważam, że do tego celu najlepiej nadaje się właśnie C lub C++.

   Poniżej przedstawię podstawowe zasady tworzenia UDFów w dwóch najpopularniejszych środowiskach komercyjnych i w jednym open source.

Czym właściwie są UDFy?

Zanim zabierzemy się za kodowanie, wypadałoby dokładniej dowiedzieć się, czym są UDFy w Firebird i poznać ogólne zasady ich działania.

   UDF to nic innego jak zwykły, skompilowany podprogram, zawarty w dołączanej dynamicznie bibliotece DLL i wywoływany z poziomu kodu SQL jako funkcja. W Linuksie są to pliki bibliotek obiektów dzielonych SO (ang. Shared Objects).

   Od strony składniowej w języku SQL, deklaracja funkcji zewnętrznej została przedstawiona na Listingu 1.

 

DECLARE EXTERNAL FUNCTION NAZWA_FUNKCJI

   <LISTA TYPÓW ARGUMENTÓW> [BY DESCRIPTOR]

   RETURNS <TYP ZWRACANEJ WARTOŚCI> BY <SPOSÓB ZWRACANIA WYNIKU>

   ENTRY_POINT 'punkt_startu' MODULE_NAME 'nazwa_pliku_dll';

Listing 1. Składnia deklaracji zewnętrznej funkcji języka SQL w DDL (ang. Data Definition Language)

 

Poszczególne parametry oznaczają:

   NAZWA_FUNKCJInazwa pod jaką funkcja jest widziana w kodzie SQL

   <LISTA TYPÓW ARGUMENTÓW> – lista, oddzielanych przecinkiem, typów argumentów  

   funkcji (opcjonalne wyrażenie BY DESCRIPTOR powoduje przekazanie do UDFa adresu tzw.  

   deskryptora  parametru (ang. descriptor), zamiast adresu jego wartości – opis w dalszej części

   artykułu).

   <TYP ZWRACANEJ WARTOŚCI> – typ zwracanej przez funkcję wartości

   <SPOSÓB ZWRACANIA WYNIKU> – jeden z trzech możliwych sposobów zwracania wyniku

   działania funkcji: przez wartość (ang. by value),  przez referencję (ang. by reference),

   dynamicznie przez referencję (ang. by reference (FREE_IT))

   punkt_startu – nazwa funkcji pod jaką została zdefiniowana w C i skompilowana do pliku DLL

   nazwa_pliku_dll – nazwa pliku DLL zawierającego zewnętrzne funkcje

W sekcji RETURNS można umieścić, zamiast typu zwracanej wartości, specjalny zapis: PARAMETER n, gdzie n oznacza numer argumentu liczony od 1. Zapis ten wskazuje, że n–ty argument, przekazywany do funkcji, ma być traktowany jako jej wartość zwrotna (dokładnie jego adres, ponieważ jest to wskaźnik). Na ogół jest to ostatni argument funkcji. Jeśli używamy ostatniego argumentu do przekazania wyniku, to funkcja zadeklarowana w C z m argumentami  jest deklarowana w SQLu z m-1 argumentami a typ zwracanego argumentu wpisujemy w sekcji RETURNS zamiast wyrażenia PARAMETER m. Tak więc, z poziomu kodu SQL również wywołuje się ją z m-1 argumentami. Pominięty argument jest wstawiany na stos, podczas wywołania funkcji, automatycznie przez silnik bazy (ang. database engine). Ma to znaczenie w przypadku, gdy funkcja nie może zwrócić danego typu danych w tradycyjny sposób, jak ma to miejsce między innymi w przypadku typu BLOB (ang. Binary Large OBject), który opiszę w części poświęconej UDFom przetwarzającym BLOBy.

 Wszystkie obiekty w bazie danych, zarządzanej przez serwer Firebird, mają możliwość zdefiniowania uprawnień dla operacji wykonywanych na nich przez poszczególnych użytkowników (ang. grants). Są to między innymi: SELECT, INSERT, UPDATE, DELETE i EXECUTE dla procedur składowanych. Wyjątkami są: wyzwalacze, generatory i UDFy, które nie posiadają systemu uprawnień. UDFy są traktowane jako element języka SQL, tak jak wbudowane funkcje: GEN_ID, UPPER, CURRENT_DATE, CURRENT_TIMESTAMP, CAST, itp.

Szczegóły techniczne i uwagi praktyczne

Przed przejściem do demonstracji tworzenia UDFów, muszę zapoznać Czytelnika z podstawowymi zasadami pisania tych funkcji i ich działaniem, aby uniknął problemów w przypadku samodzielnego implementowania UDFów.

   Typy argumentów i zwracanej wartości muszą odpowiadać typom z języka C. Podstawowe typy C i ich odpowiedniki w SQL przedstawione są poniżej:

 

 

Jako argumenty nie mogą być używane typy złożone. Można jednak obejść to ograniczenie stosując  typ BLOB. Należy podkreślić, że wszystkie parametry UDFów są przekazywane do funkcji przez wskaźnik (referencję), czyli przed wywołaniem funkcji, na stos odkładane są adresy argumentów a nie ich wartości. Trzeba, więc o tym pamiętać i odpowiednio zadeklarować argumenty w kodzie C/C++ jako wskaźniki w C i wskaźniki lub referencje w C++. Częstą pomyłką jest właśnie zapomnienie o tej konwencji i kompilowanie funkcji wykorzystujących argumenty typów liczbowych przekazywane przez wartość, co po wywołaniu takiej funkcji, prowadzi do pozornie tajemniczego błędnego działania procedury SQL, ponieważ przekazane wartości zmiennych są traktowane jako adresy, oczywiście błędne. Liczba argumentów jest ograniczona do dziesięciu, co nie stanowi poważnego problemu, ponieważ przeciętnie wykorzystuje się do czterech argumentów maksymalnie. Jeśli do funkcji są przekazywane argumenty typu BLOB, dostęp do ich zawartości jest bardziej złożony niż w przypadku, wcześniej przedstawionych, typów prostych. Jak wcześniej nadmieniłem, UDF otrzymuje adresy parametrów (wskaźniki) wskazujące na obszary pamięci przechowujące ich wartości. Jeśli przekazywany jest parametr typu BLOB, funkcja otrzymuje adres specjalnej struktury opisującej dane BLOBa, zamiast bezpośredniego adresu danych. Dopiero ta struktura umożliwia dostęp do właściwych danych. Budowa tej struktury jest przedstawiona na Listingu 2.

 

typedef struct blobcallback {

   short (*blob_get_segment)(void *hnd, unsigned char *buffer, ISC_USHORT buf_size, 

   ISC_USHORT *result_len);

   void *blob_handle;

   ISC_LONG blob_number_segments;

   ISC_LONG blob_max_segment;

   ISC_LONG blob_total_length;

   void (*blob_put_segment)(void * hnd, unsigned char* buffer, ISC_USHORT buf_size);

   ISC_LONG (*blob_lseek)(void * hnd, ISC_USHORT mode, ISC_LONG offset);

} *BLOBCALLBACK;

Listing 2. Struktura opisująca obiekt typu BLOB, zawarta w pliku ibase.h

 

Znaczenie pól struktury jest następujące:

 

Funkcja C odwołująca się do BLOBów powinna używać argumentów typu *blobcallback. Jeśli funkcja ma zwracać wynik typu BLOB, to należy ją zadeklarować w C jako void, a zwracane dane dodać jako ostatni argument typu *blobcallback. Nie stosujemy wtedy słowa kluczowego return na końcu funkcji wraz ze zwracaną wartością, jak to ma miejsce w tradycyjnych funkcjach C/C++. Chcąc zwrócić wynik, odwołujemy się do ostatniego argumentu funkcji.

Oprócz BLOBów segmentowych istnieją jeszcze BLOBy strumieniowe (ang. stream BLOBs), o których nię będę pisać (zainteresowanych Czytelników odsyłam do dokumentacji na stronie Firebirda – http://www.ibphoenix.com).

 W części opisującej składnię deklaracji UDFa w SQLu, była wzmianka o deskryptorach parametrów. Są to struktury opisujące przekazywane parametry. Struktura ta jest przedstawiona na Listingu 3.

 

typedef struct paramdsc {

   unsigned char dsc_dtype;

   signed char dsc_scale;

   ISC_USHORT dsc_length;

   short dsc_sub_type;

   ISC_USHORT dsc_flags;

   unsigned char *dsc_address;

} PARAMDSC;

Listing 3. Struktura deskryptora parametru przekazywanego do funkcji użytkownika, zawarta w pliku ibase.h

 

Znaczenie pól struktury jest następujące:

(1 – CHAR, 2 – CSTRING, 3 – VARCHAR, 8 – SMALLINT, 9 – INTEGER, 11 – FLOAT,

12 – DOUBLE PRECISION, 14 – DATE, 15 – TIME, 16 – TIMESTAMP, 17 – BLOB,

19 – NUMERIC(18, s))

 

Deskryptory pozwalają między innymi na przekazywanie, za pomocą jednego parametru, danych o dowolnym typie. Silnik bazy nie sprawdza wtedy zgodności typów i przekazuje deskryptory, a UDF może odpowiednio zareagować w zależności od bieżącego typu argumentu. Przykładowo można przekazać do UDFa liczbę całkowitą INTEGER lub jej postać tekstową VARCHAR(n), a UDF na podstawie pola dsc_type, może dokonać konwersji łańcucha tekstowego do typu INTEGER i wykonać wymagane obliczenia bez powodowania błędu. Jest to, w pewnym sensie, odpowiednik typu Variant z języka BASIC i innych języków niewymagających ścisłej zgodności typów. Jednak nie należy nadużywać tego mechanizmu, ponieważ kod może stać się niejasny i podatny na błędy.

 W przypadku wartości zwracanej mamy wybór, jak wcześniej wspomniałem, sposobu przekazania jej do kodu nadrzędnego. Domyślnym sposobem jest zwrot przez wskaźnik (referencję), czyli umieszczenie w akumulatorze procesora (rejestr EAX) adresu zmiennej przechowującej wynik działania funkcji. Funkcja może zwrócić daną numeryczną przez wartość. Liczby całkowite (char, short, int) są zwracane jako bezpośrednie wartości w akumulatorze (EAX), a zmiennoprzecinkowe pojedynczej i podwójnej precyzji (float, double) w rejestrze szczytu stosu koprocesora (ST0).

   Funkcje operujące na łańcuchach znaków i zwracające łańcuchy muszą zwracać je przez referencję. Zaleca się korzystanie z referencji dynamicznej z użyciem modyfikatora FREE_IT.

Funkcje zadeklarowane z tym modyfikatorem muszą samodzielnie zarezerwować pamięć na zwracany łańcuch za pomocą dedykowanej funkcji void *ib_util_malloc(long), o której napiszę szczegółowo w dalszej części artykułu, omawiającej konkretne przykłady implementacji UDFów w różnych środowiskach programistycznych. Pamięć przydzielona tą funkcją, zostanie automatycznie zwolniona przez serwer Firebird, po wykorzystaniu wyniku tej funkcji w procedurze SQL.

   Preferowane użycie dynamicznych referencji wymaga pewnego wyjaśnienia. Serwer Firebird jest rozpowszechniany w dwóch wersjach: Super Server i Classic Server. Wersja Classic tworzy dla każdego użytkownika oddzielny proces, natomiast w wersji Super istnieje jeden proces serwera, w ramach którego tworzone są oddzielne wątki dla każdego użytkownika (lub przydzielane są w kolejce ze stałej puli pewnej liczby wątków). Super Server lepiej sprawuje się w systemach jednoprocesorowych, a Classic Server w wieloprocesorowych. Częściej jest używana wielowątkowa wersja Super Server, tak więc zakładam, że piszemy funkcje dla tego serwera w wersji 1.5. Z tego względu funkcje muszą być pisane z zachowaniem pewnych zasad bezpieczeństwa na wypadek wywołania ich jednocześnie przez kilka wątków w ramach jednego procesu (ang. thread safe).

 

Podstawowe zasady pisania funkcji w środowisku wielowątkowym:

 

Po tej, dużej z konieczności, dawce „praktycznej teorii” czas przejść do praktycznych przykładów tworzenia UDFów w języku C.

 

UWAGA: Wszystkie pliki z przykładami są umieszczone w dziale „Pliki”.

Tworzymy UDFy w Microsoft Visual C++

Na początek stworzymy kilka prostych, testowych UDFów w najpopularniejszym środowisku do tworzenia aplikacji w C/C++ dla systemu Windows, jakim jest składnik pakietu MS Visual Studio – Visual C++. W artykule używam wersji 6.0 tego środowiska. Jednak zanim zabierzemy się za tworzenie biblioteki,  musimy przygotować środowisko do pracy, udostępniając mu niezbędne pliki nagłówkowe i biblioteki statyczne. Odszukujemy katalog Firebirda (w moim komputerze jest to: C:\Program Files\Firebird\Firebird_1_5). Z podkatalogu include kopiujemy wszystkie pliki nagłówkowe (ib_util.h, ibase.h, iberror.h) do katalogu Include Visual C++ (w moim komputerze jest to: C:\Program Files\Microsoft Visual Studio\VC98\Include). Następnie z podkatalogu lib kopiujemy pliki bibliotek statycznych (ib_util_ms.lib, fbclient_ms.lib). Przykłady zawarte w tym artykule będą wykorzystywać tylko dwa pliki: ib_util.h i ib_util_ms.lib. Teraz środowisko Visual C++ jest przygotowane do współpracy z Firebirdem.

   W celu utworzenia biblioteki dynamicznej wybieramy z menu opcję File->New... . Otwiera się okno wyboru typu projektu pokazane na Rysunku 1.

 

Rysunek 1. Okno wyboru typu nowo tworzonego projektu w Visual C++

 

Upewniamy się czy w sekcji Platforms zaznaczone jest pole Win32, w polu Project name podajemy nazwę projektu, przykładowo “msvc_udf”, wybieramy położenie plików projektu w polu Location i wybieramy typ projektu – Win32 Dynamic-Link Library. Po kliknięciu OK ukazuje się okienko wyboru rodzaju projektu biblioteki DLL (Rysunek 2).

 

Rysunek 2. Okno wyboru rodzaju projektu biblioteki DLL

 

Zostawiamy pierwszą domyślną opcję – An empty DLL project. Klikamy Finish. Po tych czynnościach musimy dodać do projektu pliki z kodem źródłowym biblioteki. Ponownie wybieramy opcję File->New..., ale teraz okno otwiera się z aktywną zakładką Files zamiast Projects, co widać na Rysunku 3.

 

Rysunek 3. Okno nowego pliku dołączanego do projektu

 

Powtarzamy czynności analogicznie jak przy tworzeniu projektu wybierając typ pliku C++ Source File i nadajemy mu nazwę msvc_udf.cpp. Po dodaniu pliku .CPP, trzeba w ten sam sposób dodać plik nagłówkowy .H, o nazwie msvc_udf.h, wybierając typ C/C++ Header File. Następnie należy zmienić domyślną konfigurację Debug na Release wybierając opcję Build->Set Active Configuration... lub korzystając ze skrótu w pasku narzędzi (jeśli jest włączony). Musimy jeszcze zmienić ustawienia kompilatora i linkera. W tym celu wybieramy Project->Settings... lub wciskamy kombinację klawiszy [Alt+F7]. Pojawia się okno Project settings, w którym wybieramy zakładkę C/C++, a następnie z listy CategoryCode generation (Rysunek 4).

 

Rysunek 4. Okno opcji kompilatora

 

Możemy wybrać typ procesora, dla którego będzie generowany kod (domyślnie jest ustawiona opcja Blend*, co oznacza, że będzie generowany mieszany kod zoptymalizowany dla procesorów Pentium i Pentium Pro). Z listy Use run-time library wybieramy koniecznie opcję Multithreaded. Zostawiamy domyślną konwencję wywołania z listy Calling conventions, tzn. __cdecl*. W oryginalnej dokumentacji InterBase 6.0 jest napisane, że trzeba użyć konwencji __stdcall, jednak nie należy tego robić, ponieważ UDFy nie będą działały i każda próba wywołania funkcji zewnętrznej w SQLu spowoduje błąd serwera. Teraz wybieramy zakładkę Link i opcję General z listy Category, gdzie zmieniamy dołączane biblioteki statyczne (pliki .LIB). Okno z aktywną zakładką linkera jest pokazane na Rysunku 5.

Rysunek 5. Okno opcji linkera

 

Modyfikujemy pole Object/library modules. Możemy usunąć wszystkie domyślne biblioteki, gdyż nie będą nam potrzebne. Koniecznie musimy dodać bibliotekę ib_util_ms.lib, zawierającą odwołanie do dynamicznej biblioteki ib_util.dll. W niej znajduje się funkcja ib_util_malloc. Po tych zmianach możemy już zamknąć okno przyciskiem OK.

   Otwieramy plik msvc_udf.cpp korzystając z okna Workspace umieszczonego z lewej strony ekranu (zakładka FileView) i wpisujemy kod zawarty na Listingu 2.

 

#define WIN32_LEAN_AND_MEAN 

 

#include <string.h>

#include <windows.h>

#include <ib_util.h>

#include "msvc_udf.h"

 

 

BOOL APIENTRY DllMain(HANDLE, DWORD, LPVOID)

{

   return true;

}

Listing 2. Kod szablonu biblioteki UDFów w Visual C++

 

Zdefiniowanie symbolu WIN32_LEAN_AND_MEAN wyłącza rzadziej używane struktury z pliku windows.h, przyspieszając tym samym kompilację. Teraz możemy dodawać własne funkcje. Przykładowy zestaw funkcji tekstowych i numerycznych został przedstawiony na Listingu 3 i Listingu 4. Kod jest bogato komentowany, więc myślę, że wszelkie dodatkowe wyjaśnienia są zbędne.

 

/*

 *  MSVC_UDF.DLL - msvc_udf.cpp

 * 

 *  Autor: Artur Kozubski  [05.01.2006]

 *

 *  Przykładowa biblioteka zewnętrznych funkcji dla procedur składowanych

 *  i wyzwalaczy SQL serwera Firebird.

 *  Wersja dla: Microsoft Visual C++ 6.0

 */

 

#define WIN32_LEAN_AND_MEAN // Wyłączamy zbędne struktury z pliku windows.h

 

#include <math.h>     // Będziemy korzystać z funkcji matematycznych

#include <string.h>   // Będziemy korzystać ze standardowych funkcji tekstowych C (str*)

#include <windows.h>  // Będziemy korzystać z funkcji tekstowych Windows (lstr*)

#include <ib_util.h>  // Biblioteka zawierająca funkcję ib_util_malloc

#include "msvc_udf.h" // Prototypy eksportowanych funkcji UDF

 

 

/*

 *  Punkt startu biblioteki DLL.

 */

BOOL APIENTRY DllMain(HANDLE, DWORD, LPVOID)

{

   return true;

}

 

/* *** Funkcje prywatne *** */

 

/*

 *  Funkcja sprawdza czy łańcuch s2 jest zawarty w s1

 *  i zwraca jego pozycję lub NULL w przeciwnym wypadku.

 *  Funkcja jest używana zamiast strstr ze względu na

 *  wykorzystanie windowsowej funkcji lstrcmpi

 *  porównującej łańcuchy z polskimi znakami bez

 *  rozróżniania ich wielkości.

 */

char *str_in_str(char *s1, char *s2)

{

   int i;

   int len2 = lstrlen(s2);

   int max_i = lstrlen(s1) - len2;              

   char *sp;

   char buf[256];

 

 

   for (i = 0; i <= max_i; i++) {

      sp = s1 + i;

      strncpy(buf, sp, len2);

      buf[len2] = 0;

      if (!lstrcmpi(buf, s2)) return sp;

   }

 

   return NULL;

}

 

/* *** Funkcje eksportowane *** */

 

/*

 *  Funkcja zamienia wszystkie podłańcuchy find łańcucha

 *  expression na łańcuchy replacewith.

 */

char *udf_str_replace(char *expression, char *find, char *replacewith)

{

   // Alokacja pamięci na bufor wynikowy - maksymalnie 255 znaków + NULL

   char *out_str = (char *)ib_util_malloc(256);

   int len1 = lstrlen(expression), len2 = lstrlen(find), len3 = lstrlen(replacewith);      

   char *ptr, *src_str;

 

 

   // Jeśli długość poszukiwanego łańcucha jest większa od

   // długości przeszukiwanego, to zwrot przeszukiwanego łańcucha

   if (len2 > len1) {

      strcpy(out_str, expression);

 

      return out_str; // Zwrot adresu bufora wynikowego - Firebird sam zwolni pamięć

   }

 

   out_str[0] = 0;

 

   // Jeśli przeszukiwany łańcuch jest pusty, to zwrot pustego łańcucha

   if (!expression || !len1) return out_str; 

 

   // Jeśli poszukiwany łańcuch jest pusty, to zwrot przeszukiwanego łańcucha

   if (!find || !len2) {

      strcpy(out_str, expression);

 

      return out_str; // Zwrot adresu bufora wynikowego - Firebird sam zwolni pamięć

   }

 

   // Jeśli zamiana na łańcuch pusty, to skopiowanie expression do out_str

   // z pominięciem podłańcuchów find

   if (!replacewith || !len3) {

      src_str = expression;

      ptr = str_in_str(expression, find);

      while (ptr) {

         strncat(out_str, src_str, ptr - src_str);

         src_str = ptr + len2;

         ptr = str_in_str(src_str, find);

      }

      strcat(out_str, src_str);

 

      return out_str; // Zwrot adresu bufora wynikowego - Firebird sam zwolni pamięć

   }

 

   // Zamiana wszystkich podłańcuchów find na replacewith

   src_str = expression;

   ptr = str_in_str(expression, find);

   while (ptr) {

      strncat(out_str, src_str, ptr - src_str);

      strcat(out_str, replacewith);

      src_str = ptr + len2;

      ptr = str_in_str(src_str, find);

   }

   strcat(out_str, src_str);

 

   return out_str; // Zwrot adresu bufora wynikowego - Firebird sam zwolni pamięć

}

 

 

/*

 * Prosta funkcja zwracająca długość podanego łańcucha.

 */

int udf_str_len(char *str)

{

   return lstrlen(str); // Zwrot przez wartość - by value

}

 

 

/*

 * Funkcja wykonująca binarny iloczyn logiczny podanych argumentów (AND).

 */

int udf_bin_and(int *val1, int *val2)

{

   return (*val1 & *val2);

}

 

 

/*

 * Funkcja oblicza sinus argumentu podanego w stopniach.

 */

double udf_sin(double *ang)

{

   return sin(*ang * 3.14159265 / 180.0);

}

 

 

/*

 * Jako ciekawostka - funkcja sprawdzająca czy procesor jest

 * wyposażony w obsługę instrukcji MMX.

 * Oczywiście można dodawać zewnętrzne funkcje wykorzystujące

 * SSE i 3DNow!, które nie są rozpoznawane przez Visual C++ 6.0,

 * skompilowane w np. NetWide Assemblerze. :-)

 * Wystarczy dodać gotowy plik .OBJ do projektu i wpisać

 * odpowiedni prototyp funkcji zawartej w pliku .OBJ.

 */

int udf_is_mmx()

{

   int mmx_bit = 0;

   __asm

   {

      // Sprawdzenie czy procesor obsługuje instrukcję CPUID

  pushfd

  pop    ecx

  mov    eax, ecx

  xor    eax, 00200000h

  push   eax

  popfd

  pushfd

  pop    eax

  xor    eax, ecx

  je     no_cpuid

      // W EAX funkcja 1 CPUID - informacje techniczne

  mov    eax, 1

  cpuid

      // Jeśli 23 bit EDX ustawiony, to jest MMX

  and    edx, 800000h

  mov    mmx_bit, edx

no_cpuid:

      // Brak obsługi CPUID (80386 lub 80486)

   }

 

   if (mmx_bit)

      return 1;

   else

     return 0;

}

 

 

/*

 * Druga ciekawostka - funkcja zwracająca nazwę producenta procesora.

 */

char *udf_cpu_vendor()

{

   char *vendor = (char *)ib_util_malloc(13);

 

 

   vendor[0] = 0;

 

   __asm

   {

      // Sprawdzenie czy procesor obsługuje instrukcję CPUID

  pushfd

  pop    ecx

  mov    eax, ecx

  xor    eax, 00200000h

  push   eax

  popfd

  pushfd

  pop    eax

  xor    eax, ecx

  je     no_cpuid

      // W EAX funkcja 0 CPUID - nazwa producenta

  xor    eax, eax

  cpuid

      // W EDI adres bufora wyjściowego

  mov    edi, vendor

      // Rejestry EBX:EDX:ECX zawierają 12-znakową nazwę producenta

  mov    [edi], bl

  shr    ebx, 8

  mov    [edi + 1], bl

  shr    ebx, 8

  mov    [edi + 2], bl

  shr    ebx, 8

  mov    [edi + 3], bl

 

  mov    [edi + 4], dl

  shr    edx, 8

  mov    [edi + 5], dl

  shr    edx, 8

  mov    [edi + 6], dl

  shr    edx, 8

  mov    [edi + 7], dl

 

  mov    [edi + 8], cl

  shr    ecx, 8

  mov    [edi + 9], cl

  shr    ecx, 8

  mov    [edi + 10], cl

  shr    ecx, 8

  mov    [edi + 11], cl

  mov    [edi + 12], 0

no_cpuid:

      // Brak obsługi CPUID (80386 lub 80486)

   }

 

   return vendor;

}

Listing 3. Przykładowa biblioteka UDFów – plik główny

 

Po skompilowaniu biblioteki kopiujemy plik msvc_udf.dll z podkatalogu msvc_udf\Release do podkatalogu Firebird\Firebird_1_5\UDF. Po tej operacji zalecam zrestartować serwer, bo nowe funkcje nie zawsze są rozpoznawane bezpośrednio po skopiowaniu do katalogu UDFów. Aby móc korzystać z naszych UDFów musimy zadeklarować je w serwerze SQL, używając przedstawionej wcześniej składni deklaracji funkcji zewnętrznych. Zapewne każdy użytkownik Firebirda ma jakieś sprawdzone narzędzie administracyjne, więc nie będę szczegółowo opisywał deklarowania UDFów od strony serwera, tylko pokażę ogólnie jak to wygląda przykładowo w programie EMS IBManager będącym dziełem rosyjskich programistów. Bardzo dobrym programem, również rosyjskim, jest IB Expert, z interfejsem podobnym do IBManagera – program ma jednak większe możliwości usprawniające pisanie oraz debugowanie procedur SQL i jest szybszy. IB Expert jest dostępny za darmo w wersjach: IBExpert Educational Version i IBExpert Personal Edition, przeznaczonych do nauki. Pierwsza wersja jest dla szkół i posiada pełną funkcjonalność z wyjątkiem ograniczenia obsługi baz 

danych do 50 MB. Druga wersja nie ma ograniczeń co do rozmiaru baz danych, ale ma ograniczoną funkcjonalność w stosunku do pełnej wersji. Program można ściągnąć ze strony http://www.ibexpert.com. Na stronie Firebirda http://www.ibphoenix.com jest lista różnych aplikacji administracyjnych, zarówno komercyjnych jak i open source.

   Jako przykład posłużą dwie funkcje: udf_str_replace i udf_str_len. Po połączeniu się z bazą rozwijamy drzewo obiektów i otwieramy menu kontekstowe węzła UDFs (Rysunek 6).

 

Rysunek 6. Tworzenie nowej funkcji użytkownika w IBManager

 

Pojawia się okno z Rysunku 7.

 

Rysunek 7. Okno edycji UDFa – udf_str_replace

 

Pola parametrów funkcji wypełniamy zgodnie z jej prototypem w C. Po kliknięciu przycisku Compile lub wciśnięciu kombinacji [Ctrl+F9] pojawia się okno z automatycznie wygenerowanym kodem SQL i pytaniem o zatwierdzenie (ang. Commit) lub cofnięcie (ang. Rollback) operacji. Po zatwierdzeniu funkcja zostaje dodana do bazy danych. Jej kod można obejrzeć i ewentualnie ręcznie zmodyfikować wybierając zakładkę DDL – Rysunek 8.

 

Rysunek 8. Okno kodu SQL funkcji użytkownika

 

Przykład ustawienia parametrów dla drugiej funkcji jest pokazany na Rysunku 9. Pozostałe funkcje dodajemy analogicznie.

 

Rysunek 9. Okno edycji UDFa – udf_str_len

 

W celu przetestowania dodanych UDFów stworzyłem testową procedurę SQL – Listing 4. Wyniki jej działania są widoczne na Rysunku 10.

 

CREATE PROCEDURE TEST_UDF

RETURNS (

   OUT_STR1 VARCHAR (255),

   OUT_STR2 VARCHAR (255),

   SLEN1 INTEGER,

   SLEN2 INTEGER,

   MMX_PRESENT INTEGER,

   CPU_MANUFACTURER VARCHAR (12),

    BINARY_AND INTEGER,

    SIN_VAL DOUBLE PRECISION)

AS

 

DECLARE VARIABLE str_exp VARCHAR(255);

 

BEGIN

/* Test Visual C++ */

   str_exp = 'MECHATRONIKA';

   out_str1 = str_replace(str_exp, 'mecha', 'ELEK');

   out_str2 = str_replace(str_exp, 'tronika', 'NIKA');

 

   slen1 = str_len(out_str1);

   slen2 = str_len(out_str2);

 

   mmx_present = is_mmx();

   cpu_manufacturer = cpu_vendor();

 

   binary_and = bin_and(255, 127);

 

   sin_val = sin(30);

 

   SUSPEND;

END

Listing 4. Przykładowa procedura SQL korzystająca z UDF-ów

 

Rysunek 10. Wyniki wykonania testowej procedury SQL wykorzystującej UDFy

 

Jeśli UDFy nie chcą się skompilować, trzeba sprawdzić czy poprawnie została wpisana nazwa funkcji w pliku DLL (entry point). Czasami kompilatory robią niemiłą niespodziankę zapisując funkcje w pliku DLL pod zmodyfikowanymi nazwami (ang. name decoration), co często jest przyczyną denerwujących problemów z kompilacją w SQLu. Jeśli nazwa jest poprawna to można rozłączyć i ponownie połączyć się z bazą – to powinno usunąć problem. Ostatecznie pozostaje zrestartowanie serwera Firebird.

Tworzymy UDFy w Borland C++ Builder

   Już wiemy jak pisać UDFy w MS Visual C++. Teraz pokażę jak je tworzyć w drugim popularnym, komercyjnym środowisku jakim jest Borland C++ Builder. W artykule posługuję się wersją 6.0 ale wszystko powinno działać w starszych wersjach, ponieważ stosujemy tylko podstawowe funkcje.

   Na początek musimy przygotować wszystkie potrzebne pliki. Jako, że C++ Builder jest produktem firmy, która stworzyła InterBase – protoplastę Firebirda, jest on już wyposażony w bibliotekę ib_util.lib (w Visual C++ musieliśmy skopiować ib_util_ms.lib z katalogu Firebirda). Pozostaje tylko skopiowanie pliku fbclient_bor.lib i plików nagłówkowych z katalogu Firebirda, tak jak to robiliśmy dla Visual C++ (u mnie katalogiem plików nagłówkowych Buildera jest: C:\Program Files\Borland\CBuilder6\Include\). C++ Builder umożliwia kompilowanie kodu bibliotek dynamicznych jako C lub C++. Będziemy używać C, więc plik ib_util.h wymaga modyfikacji. W linii extern “C” void *ib_util_malloc(long); kasujemy zapis “C”, ponieważ jest on potrzebny dla kompilatorów traktujących kompilowany kod jako C++ i informuje je o kodzie zgodnym z C. Jeśli C++ Builder jest ustawiony na kompilację kodu w języku C, powyższy zapis spowoduje błąd kompilacji (kompilatory C nie znają takiego oznaczenia – jest ono używane przez kompilatory C++) W Visual C++ nie ma rozdziału na C i C++ podczas tworzenia bibliotek DLL, dlatego nie trzeba było modyfikować tego pliku. W Visual C++ domyślnie każdy plik jest traktowany jako kod C++. Można wymusić kompilację jako C nadając plikowi rozszerzenie .C zamiast .CPP ale nie jest to warte trudu, gdyż właśnie zapis “C” rozwiązuje

cały problem zgodności C++ z C.

   Aby utworzyć bibliotekę dynamiczną w C++ Builder wybieramy z menu opcję File->New->Other.... Pojawia się okienko wyboru typu projektu (Rysunek 11). Wybieramy ikonę DLL Wizard.

 

Rysunek 11. Okno wyboru typu tworzonego projektu w C++ Builder

 

Po zatwierdzeniu wyboru pojawia się okno widoczne na Rysunku 12.

 

Rysunek 12. Okno parametrów, tworzonej w C++ Builder, biblioteki DLL

 

Zostawiamy domyślne ustawienia bez zmian. Po zamknięciu okna pojawia się okno z kodem źródłowym zawierające automatycznie dodaną definicję funkcji DllMain. Wybieramy z menu Project->Add to project... lub wciskamy [Shift+F11]. Z rozwijanej listy umieszczonej w dolnej części okna wybieramy typ pliku Library file (*.lib). Przechodzimy do podkatalogu bibliotek statycznych C++ Buildera i wybieramy plik ib_util.lib. Dodajemy do projektu plik nagłówkowy w ten sam sposób jak tworzyliśmy projekt, tylko wybieramy ikonę Header File zamiast DLL Wizard.

Zapisujemy plik jako bcb_udf.h podobnie jak cały projekt bcb_udf.bpr i plik z kodem biblioteki bcb_udf.c.

Wybieramy opcję Project->Options... lub wciskamy [Ctrl+Shift+F11]. Przechodzimy do zakładki  Compiler i kilkamy przycisk Release. Następnie w zakładce Advanced Compiler możemy wybrać typ procesora i konwencję wywoływania. Należy zostawić domyślną konwencję C (_cdecl) – Rysunek 13.

 

Rysunek 13. Okno zaawansowanych ustawień kompilatora C++ Builder

 

W tym momencie projekt jest już prawie gotowy do kompilacji. Jednak próba skompilowania go wywoła błąd linkera: Unresolved external '_ib_util_malloc'. Dzieje się tak, ponieważ w pliku biblioteki ib_util.lib funkcja ib_util_malloc została wyeksportowana bez znaku podkreślenia (ang. underscore), a kompilator ustawiony na konwencję C, spodziewa się nazwy poprzedzonej znakiem podkreślenia. Aby rozwiązać ten problem tworzymy w katalogu projektu, dowolnym edytorem tekstowym ASCII (może być Notepad), plik z rozszerzeniem DEF, o następującej treści: IMPORTS _ib_util_malloc = ib_util.ib_util_malloc. Następnie dodajemy go do projektu tak samo, jak dodawany wcześniej plik ib_util.lib, ale wybieramy z listy typ Module definition file (*.def).

Po wykonaniu powyższych czynności możemy zacząć pisać swoje funkcje tak jak w poprzednim przykładzie i skompilować bibliotekę (jednak funkcje z wstawkami asemblerowymi wymagają zmian w celu dostosowania do specyfiki kompilatora bcc32).

Tworzymy UDFy w Bloodshed Dev-C++

   Ostatnim środowiskiem, jakie chcę przedstawić, jest Bloodshed Dev-C++. Jest to projekt  open source stworzony w Delphi i wykorzystujący windowsową wersję darmowego kompilatora gcc – MinGW. Pakiet IDE Dev-C++ wraz z MinGW można ściągnąć ze strony http://www.bloodshed.net. W artykule opisuję wersję 5 Beta 9.2 (4.9.9.2).

   Zakładam, że Dev-C++ jest już zainstalowany, więc przechodzę od razu do rzeczy. Przed rozpoczęciem budowy biblioteki UDFów w Dev-C++ musimy oczywiście dostarczyć pliki nagłówkowe i biblioteki importowe. Do katalogu include (u mnie jest to katalog: C:\Dev-Cpp\include) kopiujemy z katalogu Firebirda pliki .H tak jak w przypadku wcześniej opisywanych środowisk. Musimy usunąć wpis “C” z pliku ib_util.h z tych samych powodów, co w przypadku kompilacji w C++ Builder. Więcej problemów będziemy mieli z

biblioteką ib_util.lib. Dev-C++ nie posiada jej a w katalogu Firebirda jest tylko wersja dla Visual C++. Jednak okazuje się, że MinGW używa tego samego formatu LIB co Visual C++. Tak więc, możemy użyć pliku ib_util_ms.lib. Musimy go skopiować do podkatalogu lib pod zmienioną nazwą libib_util.a (gcc używa takiej konwencji dla nazw bibliotek statycznych). Możemy uruchomić Dev-C++. Z menu wybieramy opcję File->New->Project.... Pojawia się okno widoczne na Rysunku 14.

 

Rysunek 14. Okno opcji nowego projektu w Dev-C++

 

Wybieramy język projektu – C Project, jego typ – ikona DLL oraz nazwę. Po zamknięciu okna Dev-C++ tworzy pliki dllmain.c i dll.h zawierające automatycznie wygenerowany szablon kodu biblioteki DLL. Pliki możemy zapisać pod inną nazwą. Wybieramy z menu opcję Project->Project Options lub wciskamy [Alt+P]. Uaktywniamy zakładkę Parameters pokazaną na Rysunku 15. W sekcji Linker  wciskamy przycisk Add Library or Object, przechodzimy do podkatalogu lib, gdzie odszukujemy wcześniej skopiowany plik libib_util.a i dodajemy go do projektu.

 

Rysunek 15. Okno parametrów kompilatora w Dev-C++

 

Następnie modyfikujemy początek pliku z kodem źródłowym zamieniając domyślne wpisy na przedstawione na Listingu 5.

 

#define WIN32_LEAN_AND_MEAN  // Wyłączamy zbędne struktury z pliku windows.h

 

#include <math.h>// Będziemy korzystać z funkcji matematycznych

#include <string.h>        // Będziemy korzystać ze standardowych funkcji tekstowych C (str*)

#include <windows.h>  // Będziemy korzystać z funkcji tekstowych Windows (lstr*)

#include <ib_util.h>      // Biblioteka zawierająca funkcję ib_util_malloc

#include "devc_udf.h"

Listing 5. Początek pliku devc_udf.c

 

Musimy również zmodyfikować plik nagłówkowy devc_udf.h do postaci pokazanej na Listingu 6.

 

#ifndef _DLL_H_

#define _DLL_H_

 

#if BUILDING_DLL

# define DLLIMPORT __declspec (dllexport)

#else /* Not BUILDING_DLL */

# define DLLIMPORT __declspec (dllimport)

#endif /* Not BUILDING_DLL */

 

DLLIMPORT char *udf_str_replace(char *expression, char *find, char *replacewith);

DLLIMPORT int udf_str_len(char *str);

DLLIMPORT int udf_bin_and(int *val1, int *val2);

DLLIMPORT double udf_sin(double *ang);

DLLIMPORT int udf_is_mmx();

DLLIMPORT char *udf_cpu_vendor();

 

#endif /* _DLL_H_ */

Listing 6. Zmodyfikowany plik devc_udf.h

 

Tak jak w przypadku C++ Buildera, wstawki asemblerowe wymagały zmian w stosunku do naszego pierwowzoru z Visual C++. Jednak w Dev-C++ zmiany były znaczne ze względu na używany kompilator gcc. Domyślnie gcc używa w asemblerze inline składni AT&T, która znacząco różni się od składni Intela. Aby oszczędzić sobie trochę pracy, warto skorzystać z opcji kompilatora -masm=intel, która włącza obsługę składni Intela we wstawkach asemblerowych. Opcje kompilatora możemy wpisywać w oknie Project Options. Wybieramy z menu opcję Project->Project Options lub wciskamy [Alt+P]. Uaktywniamy zakładkę Parameters pokazaną na Rysunku 15. W sekcji Compiler: dopisujemy w polu tekstowym opcję -masm=intel i zamykamy okno. Oczywiście kod asemblerowy i tak będzie wymagał znacznych zmian ale nie trzeba będzie zamieniać go na konwencję AT&T. Wszystkie trzy projekty są umieszczone w dziale „Pliki”, tak więc Czytelnik może zapoznać się z różnicami w zapisie wstawek asemblerowych dla poszczególnych kompilatorów C/C++.

   Wywołania UDFów możemy wstawiać w dowolnych miejscach kodu SQL, np: w zapytaniach SELECT jako funkcje operujące na pobieranych polach i modyfikujące ich zawartość przed zwróceniem z zapytania, w klauzuli WHERE przy sprawdzaniu warunków, w poleceniach INSERT, UPDATE i DELETE, itp.

Praktyczne przykłady wykorzystania UDFów

Nadal będziemy używać tej samej bazy danych, ale stworzymy nową bibliotekę o nazwie text_search (tym razem zrobiłem to tylko w Visual C++, ale sądzę, że po zapoznaniu się z przykładowymi projektami poprzednio przedstawionej biblioteki, przeniesienie jej do innych środowisk nie powinno nastręczać problemów).

   Biblioteka ta, zawiera funkcje wspomagające przetwarzanie dużych ilości danych tekstowych zapisanych jako BLOBy. Przykładowo danymi tego typu mogą być opisy produktów w sklepie internetowym, tekstowe raporty, opisy słownikowe i encyklopedyczne, itp. Potrzebujemy możliwości porównywania polskich znaków bez rozróżniania ich wielkości. Porównywanie tekstów ,bez rozróżniania wielkości liter, umożliwia operator CONTAINING. Jednak operator ten nie umożliwia przeszukiwania, bez rozróżniania wielkości znaków narodowych, kolumn typu BLOB. Działa tylko w przypadku standardowego zbioru znaków. Rozwiązaniem jest napisanie UDFa, który wykona właściwe porównanie z uwzględnieniem polskich znaków. Dodatkowo

przeszukiwanie tekstów powinno odbywać się jak najszybciej. Podstawowym sposobem przyspieszenia wyszukiwania danych w tabelach jest używanie indeksów (struktury typu b-tree umożliwiające wyszukiwanie wierszy bez odwoływania się do samej tabeli i przeglądania jej wiersz po wierszu). Jednak kolumny typu BLOB nie mogą być indeksowane. Chcąc uniknąć przeszukiwania całej tabeli BLOBów, wyraz po wyrazie, możemy sami zaimplementować mechanizm indeksujący zawartość BLOBów, oparty na tabeli słownika wyrazów zawartych w BLOBach. Przeszukiwanie takiej tabeli powinno trwać krócej, ze względu na stałą długość danych (INTEGER, VARCHAR) i możliwość ich zindeksowania przez silnik bazy danych. Dodatkowo wiersze tabeli słownika będą zajmować znacznie mniej miejsca niż wiersze w tabeli z tekstami, co powinno korzystnie wpłynąć na buforowanie i w konsekwencji na dodatkowe przyspieszenie przeszukiwania słownika. Dzięki powyższemu mechanizmowi ograniczamy do minimum ilość odwołań do tabeli z BLOBami. Ceną jaką płacimy za to, jest znaczne wydłużenie czasu wstawiania, uaktualniania i kasowania danych w tabeli tekstów. Zakładamy jednak, że baza nie będzie często modyfikowana i będzie raczej służyć jako zbiór danych głównie do odczytu. Uwaga ta tyczy się również zwykłych automatycznych indeksów dodawanych przez RDBMS.

 Ponadto chcemy mieć możliwość bezpośredniego wczytywania plików do tabeli z danymi tekstowymi. Tu również z pomocą przychodzi nam odpowiedni UDF.

 

Budowę bazy danych zaczynamy od utworzenia tabel:

id INTEGER NOT NULL – klucz główny

text BLOB sub_type 1 segment size 1024 – tekst

word_id INTEGER NOT NULL – klucz główny

word VARCHAR (30) character set WIN1250 collate WIN1250 – słowo

len SMALLINT – długość słowa

idx_id integer NOT NULL – klucz główny

word_id INTEGER NOT NULL – klucz słowa w słowniku (relacja)

text_id INTEGER NOT NULL – klucz tekstu w tabeli TEXT_DATA (relacja)

word_count INTEGER NOT NULL – ilość wystąpień słowa w tekście

delims VARCHAR (255) character set WIN1250 collate WIN1250

 

Dla tabeli TEXT_DICTIONARY tworzymy unikalny indeks dwukolumnowy dla kolumn: WORD i LEN, przyspieszający jej przeszukiwanie i zabezpieczający przed duplikacją danych. Podobnie tworzymy unikalny indeks dla tabeli TEXT_INDEX, obejmujący kolumny: WORD_ID i TEXT_ID. Wyposażamy tabelę TEXT_DATA w zestaw wyzwalaczy automatycznie uruchamiających procedurę indeksowania podczas modyfikacji jej zawartości. Ich treść można zobaczyć w przykładowej bazie w dziale „Pliki”.

Algorytm indeksowania obrazuje schemat blokowy na Rysunku 16.

 


Rysunek 16. Algorytm indeksowania tekstowych BLOBów w tabeli TEXT_DATA

 

Procedura realizująca ten algorytm jest pokazana na Listingu 7.

 

CREATE PROCEDURE ADD_TO_INDEX (

   TEXT_ID INTEGER,

   INPUT_TEXT BLOB sub_type 1 segment size 1024)

AS

 

/* Procedura indeksująca dodawany do bazy tekst */

 

DECLARE VARIABLE delim VARCHAR(255);

 

DECLARE VARIABLE word_list BLOB;

DECLARE VARIABLE list_size INTEGER;

 

DECLARE VARIABLE word_len SMALLINT;

DECLARE VARIABLE word_cnt INTEGER;

DECLARE VARIABLE word VARCHAR(255);

 

DECLARE VARIABLE i INTEGER;

 

DECLARE VARIABLE dict_word_id INTEGER;

 

 

BEGIN

   SELECT delims FROM delimiters INTO :delim;

 

/* Budowa listy słów */

   word_list = txt_build_word_list(input_text, delim);

   list_size = txt_list_size(word_list);

 

   IF (list_size > 0) THEN

   BEGIN

      i = 1;

      WHILE (i <= list_size) DO

      BEGIN

      /* Przetwarzanie kolejnych słów z listy */

         word_len = txt_get_len_from_wlist(word_list, i);

         word_cnt = txt_get_cnt_from_wlist(word_list, i);

         word = txt_get_word_from_wlist(word_list, i);

 

      /* Sprawdzenie czy dodawane słowo jest już w słowniku */

         dict_word_id = 0;

         SELECT word_id FROM text_dictionary WHERE str_compare(word, :word) = 0

         INTO :dict_word_id;

     

         IF (dict_word_id = 0) THEN

         BEGIN

         /* Brak w słowniku - można dodać */

            dict_word_id = GEN_ID(text_dictionary_word_id_gen, 1);

            INSERT INTO text_dictionary(word_id, word, len)

            VALUES (:dict_word_id, str_upper(:word), :word_len);

         END

     

      /* Wstawienie indeksu dla danego słowa w bieżącym tekście */

         INSERT INTO text_index(word_id, text_id, word_count)

         VALUES (:dict_word_id, :text_id, :word_cnt);

     

         i = i + 1;

      END

   END

END

Listing 7. Procedura składowana SQL wykonująca indeksowanie według algorytmu z Rysunku 16

 

W procedurze widać wiele wywołań różnych UDFów, które pochodzą właśnie z biblioteki text_search.dll. Z racji ograniczonego miejsca nie zamieszczam w artykule kodu pozostałych procedur: wczytującej pliki do BLOBów oraz wyszukujących, przy pomocy słownika i indeksu, BLOBy zawierające poszukiwane wyrazy. Również kod biblioteki text_search jest skrócony i zawiera najważniejsze funkcje istotne z punktu widzenia tworzenia UDFów. Pełna wersja jest dostępna na płycie. Na Listingu 8. jest przedstawiony pełny plik nagłówkowy wspomnianej biblioteki.

 

#ifdef __cplusplus

extern "C" {

#endif

 

#define UDF_EXPORT __declspec(dllexport)

 

/* Definicja struktury danych elementu kolejki słów */

typedef struct WORD_INDEX {

   char *word;        // Słowo

   char wlen;         // Długość słowa

   int  wcount;       // Ilość słów word w tekście

   WORD_INDEX* prev;  // Poprzedni element

   WORD_INDEX* next;  // Następny element

} WORD_INDEX;

 

 

/* BLOB */

UDF_EXPORT void txt_build_word_list(const blobcallback *input_text, const char *delimiters, blobcallback *word_list);

UDF_EXPORT int txt_list_size(const blobcallback *word_list);

UDF_EXPORT int txt_get_len_from_wlist(const blobcallback *word_list, const int *n);

UDF_EXPORT char *txt_get_word_from_wlist(const blobcallback *word_list, const int *n);

UDF_EXPORT int txt_get_cnt_from_wlist(const blobcallback *word_list, const int *n);

UDF_EXPORT int txt_word_cnt(const blobcallback *input_text, const char *delimiters);

UDF_EXPORT char *txt_get_word(const blobcallback *input_text, const int *n, const char *delimiters);

UDF_EXPORT void txt_clear_html(const blobcallback *input_text, blobcallback *output_text);

UDF_EXPORT int txt_str_in_txt(const blobcallback *input_text, const char *s);

UDF_EXPORT void txt_load_from_file(const char *path, blobcallback *output_text);

/* CSTRING, VARCHAR */

UDF_EXPORT int str_compare(const char *str1, const char *str2);

UDF_EXPORT int str_compare_csens(const char *str1, const char *str2);

UDF_EXPORT int str_chars_exists(const char *s, const char *chars);

UDF_EXPORT int str_char_cnt(const char *s, const char *chr);

UDF_EXPORT char *str_upper(const char *s);

UDF_EXPORT int str_word_cnt(char *s, char *delimiters);

UDF_EXPORT char *str_get_word(char *s, int *n, char *delimiters);

UDF_EXPORT int str_in_str(const char *s1,const char *s2);

 

#ifdef __cplusplus

}

#endif

Listing 8. Plik nagłówkowy text_search.h

 

Aby skompilować tą bibliotekę, trzeba oprócz biblioteki ib_util_ms.lib, dodać biblioteki systemu Windows: kernel32.lib i user32.lib. Standardowo biblioteki te są dodawane automatycznie podczas  tworzenia nowego projektu wraz z innymi, niepotrzebnymi w tym projekcie.

 

#define WIN32_LEAN_AND_MEAN  // Wyłączamy zbędne struktury z pliku windows.h

 

#include <stdio.h>        // Będziemy korzystać z funkcji obsługi plików

#include <stdlib.h>       // Będziemy korzystać z funkcji malloc

#include <string.h>       // Będziemy korzystać ze standardowych funkcji tekstowych C (str*)

#include <windows.h>      // Będziemy korzystać z funkcji tekstowych Windows (lstr*)

#include <ibase.h>        // Będziemy korzystać z definicji struktury blobcallback

#include <ib_util.h>      // Biblioteka zawierająca funkcję ib_util_malloc

#include "text_search.h"  // Prototypy eksportowanych funkcji UDF

 

 

/*

 *  Punkt startu biblioteki DLL.

 */

BOOL APIENTRY DllMain(HANDLE, DWORD, LPVOID)

{

   return true;

}

 

 

/* *** Funkcje eksportowane *** */

 

 

/*

 *  Funkcja budująca listę słów w przetwarzanym tekście.

 */

void txt_build_word_list(const blobcallback *input_text, const char *delimiters, blobcallback *word_list)

{

   char *blob_buffer;

   char *word;

   unsigned char word_len, *pword_len;

   unsigned short out_len, *word_count;

   char *start_pos, *word_list_buf;   

   WORD_INDEX *word_queue = NULL, *cur_index = NULL;

 

 

// Zabezpieczenie przed pustymi BLOBami

   if (!input_text || !input_text->blob_handle || !word_list || !word_list->blob_handle) return;

 

// Alokacja pamięci na największy segment BLOBa

   blob_buffer = (char *)malloc(input_text->blob_max_segment + 1);

 

// Kolejne pobieranie wszystkich segmentów BLOBa

   while ((*input_text->blob_get_segment)(input_text->blob_handle, (unsigned char *)blob_buffer, input_text->blob_max_segment, &out_len)) {

      blob_buffer[out_len] = 0;

 

   // Pobranie pierwszego słowa

      start_pos = blob_buffer;

      word = get_word(start_pos, 1, delimiters, &word_len, &start_pos);

      start_pos++;

 

      while (word[0]) {

         add_word2queue(&word_queue, &cur_index, word, word_len);  // Dodanie słowa do kolejki

         free(word);

         word = get_word(start_pos, 1, delimiters, &word_len, &start_pos);  // Pobranie kolejnego słowa

         start_pos++;

      }

      free(word);

   }

 

   word_list_buf = (char *)malloc(258);

 

   if (word_queue) {

   // Budowanie listy słów na podstawie zawartości kolejki word_queue

      cur_index = word_queue;

 

      do {

      /* Pobranie z kolejki słowa wraz z jego długością i ilością powtórzeń

         Format danych listy w BLOBie:

         unsigned char  - długość słowa

         char[]         - słowo (bez znaku null na końcu, maksymalnie 255 znaków)

         unsigned short - ilość powtórzeń słowa w tekście */

         pword_len = (unsigned char *)word_list_buf;

         word_count = (unsigned short *)(word_list_buf + cur_index->wlen + 1);

         *pword_len = cur_index->wlen;            

         memcpy(word_list_buf + 1, cur_index->word, *pword_len);

         *word_count = cur_index->wcount;

      // Zapisanie segmentu w BLOBie wyjściowym

          (*word_list->blob_put_segment)(word_list->blob_handle, (unsigned char *)word_list_buf, *pword_len + 3);

 

          cur_index = cur_index->next;

      } while (cur_index);                         

   }

 

   free(word_list_buf);

   free(blob_buffer);

   delete_queue(&word_queue);

}

 

 

/*

 *  Funkcja zwracająca n-te słowo w BLOBie.

 */

char *txt_get_word(const blobcallback *input_text, const int *n, const char *delimiters)

{

   char *buf = (char *)ib_util_malloc(256);  // Alokacja pamięci na bufor wyjściowy CSTRING(256)

   char *blob_buffer;

   char *pbuf_src, *pbuf_dst;                 

   char str[2];                    

   int delim, wn;

   unsigned short out_len;

   

   

   buf[0] = 0;

// Zabezpieczenie przed pustymi BLOBami

   if (!input_text || !input_text->blob_handle) return buf;

 

   blob_buffer = (char *)malloc(input_text->blob_max_segment + 1);  // Alokacja pamięci na największy segment BLOBa

   

   while ((*input_text->blob_get_segment)(input_text->blob_handle, (unsigned char *)blob_buffer, input_text->blob_max_segment, &out_len)) {

      blob_buffer[out_len] = 0;

      pbuf_src = blob_buffer;

 

      *buf = 0;

      pbuf_dst = buf;        

      str[1] = 0;

      wn = 0;

      delim = 1;

 

      while (*pbuf_src) {

         str[0] = *pbuf_src;

         if (!strpbrk(str, delimiters) && (unsigned char)str[0] >= 32) {

            if (delim) {

               wn++;

               delim = 0;

            }

         } else delim = 1;

         if (wn == *n)

            if (!strpbrk(str, delimiters) && (unsigned char)str[0] >= 32) *pbuf_dst++ = *pbuf_src;

         pbuf_src++;

      };

      *pbuf_dst = 0;

   }

 

   free(blob_buffer);

 

   return buf;

}

 

 

/*

 *  Funkcja sprawdzająca czy w BLOBie input_text zawiera się podłańcuch s.

 */

int txt_str_in_txt(const blobcallback *input_text, const char *s)

{

   char *blob_buffer, *pbuf;

   unsigned short out_len;

 

 

// Zabezpieczenie przed pustymi BLOBami

   if (!input_text || !input_text->blob_handle) return 0;

 

// Alokacja pamięci na największy segment BLOBa

   blob_buffer = (char *)malloc(input_text->blob_max_segment + 1);

 

   while ((*input_text->blob_get_segment)(input_text->blob_handle, (unsigned char *)blob_buffer, input_text->blob_max_segment, &out_len)) {

      blob_buffer[out_len] = 0;

      if (str_in_str(blob_buffer, s)) {

        free(blob_buffer);

 

        return 1;

      }

   }

   

   free(blob_buffer);

 

   return 0;

}

 

 

/*

 *  Funkcja wczytująca do BLOBa zawartość pliku (binarnego lub tekstowego).

 */

void txt_load_from_file(const char *path, blobcallback *output_text)

{   

   char *buf;

   unsigned short bytes_read;

   FILE *f;

 

 

// Zabezpieczenie przed pustymi BLOBami

   if (!output_text || !output_text->blob_handle) return;

 

// Alokacja pamięci na bufor odczytu

   buf = (char *)malloc(512);

 

   f = fopen(path, "rb");

   if (!f) {

   // Jeśli plik nie istnieje, to zapisanie w BLOBie wyjściowym komunikatu błędu

      sprintf(buf, "Nie znaleziono pliku: %s.", path);

      (*output_text->blob_put_segment)(output_text->blob_handle, (unsigned char *)buf, lstrlen(buf));

      free(buf);

 

      return;

   }

   

// Załadowanie całego pliku porcjami po 512 bajtów

   while ((bytes_read = fread(buf, 1, 512, f)) > 0)

      (*output_text->blob_put_segment)(output_text->blob_handle, (unsigned char *)buf, bytes_read);

 

   free(buf);

   fclose(f);

}

 

 

/*

 *  Funkcja porównująca dwa łańcuchy bez rozróżniania wielkości liter.

 */

int str_compare(const char *str1, const char *str2)

{

   return lstrcmpi(str1, str2);

}

 

 

/*

 *  Funkcja porównująca dwa łańcuchy z rozróżnianianiem wielkości liter.

 */

int str_compare_csens(const char *str1, const char *str2)

{

   return lstrcmp(str1, str2);

}

 

 

/*

 *  Funkcja zamieniająca wszystkie litery w s na wielkie.

 */

char *str_upper(const char *s)

{

   char *out_buf = (char *)ib_util_malloc(256);

 

 

   lstrcpy(out_buf, s);

   CharUpper(out_buf);

 

   return out_buf;

}

 

 

/*

 *  Funkcja zwracająca n-te słowo z łańcucha s, oddzielane separatorami z listy delimiters.

 */

char *str_get_word(char *s, int *n, char *delimiters)

{

   char *buf = (char *)ib_util_malloc(256);

   char *pbuf;                    

   char str[2];                    

   int delim, wn, len = 0;

   

   

   if (*s) {

      *buf = 0;

      pbuf = buf;        

      str[1] = 0;

      wn = 0;

     delim = 1;

      while (*s && len < 255) {

         str[0] = *s;

         if (!strpbrk(str, delimiters)) {

            if (delim) {

               wn++;

               delim = 0;

            }

         } else delim = 1;

         if (wn == *n)

            if (!strpbrk(str, delimiters)) {

               *pbuf++ = *s;

               len++;

            }

         s++;

      };

      *pbuf = 0;

   }

   

   return buf;

}

Listing 9. Plik text_search.cpp – fragment

 

Przedstawioną bibliotekę można rozszerzyć o nowe, nieobecne w niej, kryteria poszukiwania tekstu. Można dodać kompresję BLOBów poprzez zdefiniowanie podtypu użytkownika (user sub_type), będącego liczbą ujemną w przeciwieństwie do standardowych, rozpoznawanych przez silnik. Następnie tworzymy UDFy obsługujące kompresję i dekompresję. Jednak najlepiej użyć do tego celu filtru (ang. blob filter), który jest specjalną odmianą UDFa, służącego do automatycznej konwersji pomiędzy różnymi typami BLOBów, bez potrzeby jawnego wywoływania UDFa konwertującego w kodzie SQL. Kolejnym dodatkiem może być UDF zapisujący zawartość BLOBa w zewnętrznym pliku (przykładowa biblioteka umożliwia tylko ładowanie zewnętrznych plików do BLOBów). Można dodać zaawansowane funkcje obliczające podobieństwo tekstów, wyznaczające czytelność tekstów, czy dokonujące jakichś analiz statystycznych.

 

Na koniec przedstawiam jeszcze jeden przykład „z życia wzięty”. Jest to para procedur SQL do generowania liczb kontrolnych kont bankowych i ich weryfikacji, wykorzystująca zewnętrzną funkcję napisaną w C (zaznaczam, że zamieszczony poniżej kod C nie jest mojego autorstwa – autor nieznany).

Niektóre funkcje UDF do operowania na łańcuchach tekstowych obecne w kodzie procedur SQL, nie zostały tu pokazane (f_strcopy, str_del_chars, strlen).

 

__declspec(dllexport) int modulo(unsigned char *input, int *n);

Listing 10. Deklaracja prototypu funkcji UDF

 

unsigned int divide(unsigned char *input, unsigned int divisor)

{

   unsigned int output = 0, temp = 0, temp1;

 

 

   while (*input >= '0' && *input <= '9') {

              output *= 10;

              temp += (unsigned int)*input - '0';

              output += temp1 = temp / divisor;

              temp -= temp1 * divisor;

              temp *= 10;

              input++;

   }

 

   return output;

}

 

//------------------------------------------------------------------------------

 

unsigned int divide1(unsigned char *input)

{

   unsigned int output = 0;

 

 

   while (*input >= '0' && *input <= '9') {

              output *= 10;

              output += (unsigned int)*input - '0';

              input ++;

   }

 

   return output;

}

//------------------------------------------------------------------------------

 

unsigned int modulo9(unsigned char *input)

{

   unsigned int output = 0;

 

   while (*input >= '0' && *input <= '9') {

      output += (unsigned int)*input - '0';

      input ++;

   }

 

   return output % 9;

}

 

//------------------------------------------------------------------------------

 

unsigned char *substract(unsigned char *output, unsigned char *input, unsigned int len, unsigned int n)

{

   signed int temp;

   unsigned int carry = 0;

 

 

   output [len] = '\0';

 

   do {

      len--;

 

      temp = (signed int)input [len] - '0' - n % 10 - carry;

      if (temp < 0) {

        temp += 10;

        carry = 1;

     } else carry = 0;

 

     output [len] = (unsigned char)temp + '0';

     n /= 10;

   } while (len);

 

   return output;

}

 

//------------------------------------------------------------------------------

 

int modulo(unsigned char *input, int *n)

{

   unsigned int output;

   unsigned int len = 0;

   unsigned char *out_string;

 

 

   while (input[len] >= '0' && input [len] <= '9') len ++;

 

   out_string = (unsigned char*)malloc(len + 1);

 

   if (*n == 9)

      output = modulo9(input);

   else {

      output = divide1(substract(out_string, input, len, *n * (divide(input, *n))));

      output %= *n;

   }

 

   free(out_string);

 

   return output;

}

Listing 11. Kod eksportowanej funkcji UDF i wewnętrznych funkcji pomocniczych

 

DECLARE EXTERNAL FUNCTION STR_MODULO

    CSTRING(256),

    INTEGER

RETURNS INTEGER BY VALUE

ENTRY_POINT 'modulo' MODULE_NAME 'strlib'

Listing 12. Deklaracja funkcji UDF liczącej modulo n z liczby podanej jako łańcuch tekstowy (numer konta)

 

CREATE PROCEDURE IPH_CALC_ACCOUNT_NUMBER (

    klient_id INTEGER,

    zamow_id INTEGER)

RETURNS (

    account_number VARCHAR(32))

AS

 

DECLARE VARIABLE lk VARCHAR(2);

DECLARE VARIABLE filia VARCHAR(8);

DECLARE VARIABLE mnemonik VARCHAR(4);

DECLARE VARIABLE konto VARCHAR(30);

DECLARE VARIABLE k_id VARCHAR(6);

DECLARE VARIABLE z_id VARCHAR(6);

DECLARE VARIABLE temp_number VARCHAR(30);

DECLARE VARIABLE i INTEGER;

DECLARE VARIABLE j INTEGER;

 

BEGIN

  SELECT filia, mnemonik FROM bre_bank_konto INTO :filia, :mnemonik;

 

/*** Numer indywidualnego konta klienta na podstawie ID klienta i zamówienia ***/

  k_id = CAST(klient_id AS VARCHAR(6));

  z_id = CAST(zamow_id AS VARCHAR(6));

 

/* Uzupełnienie k_id wiodącymi zerami do długości 6 cyfr */

  i = 1;

  j = 6 - strlen(k_id);

  temp_number = '';

 

  WHILE (i <= j) DO

  BEGIN

    temp_number = temp_number || '0';

    i = i + 1;

  END

 

  k_id = temp_number || k_id;

 

/* Uzupełnienie z_id wiodącymi zerami do długości 6 cyfr */

  i = 1;

  j = 6 - strlen(z_id);

  temp_number = '';

 

  WHILE (i <= j) DO

  BEGIN

    temp_number = temp_number || '0';

    i = i + 1;

  END

 

  z_id = temp_number || z_id;

 

  konto = filia || mnemonik || k_id || z_id;

 

/*** Obliczenie liczby kontrolnej LK ***/

  temp_number = konto || '252100';

  i = 98 - str_modulo(temp_number, 97);

  lk = CAST(i AS VARCHAR(2));

  IF (i < 10) THEN lk = '0' || lk;

 

/*** Zbudowanie numeru konta IPH ***/

  account_number = lk || ' ' || f_strcopy(konto, 0, 4) || ' ' || f_strcopy(konto, 4, 4) || ' ';

  account_number = account_number || f_strcopy(konto, 8, 4) || ' ' || f_strcopy(konto, 12, 4) || ' ';

  account_number = account_number || f_strcopy(konto, 16, 4) || ' ' || f_strcopy(konto, 20, 4);

 

  SUSPEND;

END

 

 

CREATE PROCEDURE IPH_CHECK_ACCOUNT_NUMBER (

    account_number VARCHAR(32))

RETURNS (

    result INTEGER)

 

AS

 

DECLARE VARIABLE temp_number VARCHAR(32);

 

BEGIN

/* Sprawdzenie poprawności liczby kontrolnej w numerze konta */

  temp_number = f_strcopy(str_del_chars(account_number, ' '), 2, 24);

  temp_number = temp_number || '2521' || f_strcopy(account_number, 0, 2);

  result = str_modulo(temp_number, 97);

 

  SUSPEND;

END

Listing 13. Definicje procedur SQL wykorzystujących funkcję modulo

 

 Artykuł oczywiście nie opisuje wszystkich możliwości jakie daje mechanizm UDFów. Do takich należą z pewnością filtry obiektów binarnych (ang. BLOB filters), jednak jest to bardziej złożone zagadnienie i jego opis wykracza po za ramy i tak już obszernego artykułu. Powyżej opisane metody w zupełności wystarczają do większości zadań z jakimi możemy spotkać się w praktyce programistycznej. UDFy stanowią potężne narzędzie w rękach programisty baz danych. Pozwalają implementować w SQLu praktycznie dowolne mechanizmy, mające oczywiście sensowne zastosowanie z punktu widzenia procedur składowanych SQL. Przedstawione przykłady mogą służyć jako szablony projektów do dalszych, bardziej zaawansowanych, eksperymentów z UDFami.  Zewnętrzne funkcje użytkownika dają zupełnie nową jakość w procesie tworzenia aplikacji bazodanowych, czyniąc SQL elastycznym językiem, nie ograniczonym wbrew pozorom, tylko do samego pobierania, przetwarzania i składowania danych.