Rozdział 4. Interfejsy PostgreSQL

Po zapoznaniu się z podstawowym sposobem interaktywnego dostępu do bazy PostgreSQL z interpretera psql, który korzysta z języka SQL, możemy zająć się sprawą dostępu do bazy danych z kodu programu. Odbywa się to bardzo podobnie i wszystkie znane polecenia używane dotychczas w wierszu poleceń działają w kodzie niemal natychmiast.

Dostęp do PostgreSQL z kodu programu

Do baz PostgreSQL można uzyskać dostęp z wielu języków programowania. Autorzy znają jedynie takie:

• C

• C++

• Java

• Perl

• Python

• PHP

• Tcl

Prawdopodobnie istnieją jeszcze inne języki obsługujące PostgreSQL, ale o nich na razie nic nie wiemy.

Istnieje także sterownik ODBC, który umożliwia dostęp z wielu innych systemów, włącznie z klientami MS Windows, które mogą korzystać ze źródeł danych ODBC, takimi jak na przykład Access.

Głównym językiem używanym w przykładach w naszej książce jest język C i z tego właśnie języka chcemy mieć dostęp do naszej bazy danych PostgreSQL. Istnieją dwie metody uzyskiwania dostępu do PostgreSQL z kodu programu.

• Pierwszą jest utrzymane w tradycyjnym stylu korzystanie z biblioteki o nazwie libpq. W celu uzyskania dostępu do bazy trzeba wywoływać funkcje biblioteczne z kodu programu.

• Druga metoda polega na osadzaniu poleceń SQL w kodzie języka C i ich przetwarzaniu przez preprocesor przed ostateczną kompilacją. Według terminologii PostgreSQL nazywa się to ecpg. Jest to podejście bardzo podobne do użycia preprocesora języka C przetwarzającego polecenia #include i #define przed przekazaniem kodu do głównego kompilatora. Zagadnienie to z pewnością jest znane użytkownikom niektórych komercyjnych produktów, takich jak PRO*C firmy Oracle i ESQLC firmy Informix, ponieważ każdy z nich spełnia w większym lub mniejszym stopniu standard ANSI opisujący osadzanie poleceń SQL.

W tym rozdziale pokażemy praktyczne zastosowanie obydwu metod, co ułatwi dokonanie wyboru metody najwłaściwszej dla potrzeb użytkownika lub najwygodniejszej do pracy.

Biblioteka libpq

Ogólnie rzecz biorąc, funkcje występujące w bibliotece libpq można pogrupować na trzy następujące kategorie:

• Funkcje do zarządzanie połączeniami.

• Funkcje służące do uruchamiania poleceń SQL.

• Funkcje zwracające wyniki zapytań.

Omówimy te grupy po kolei. W bibliotece libpq przez lata zgromadziły się pewne przestarzałe funkcje, które są utrzymywane tylko dla zachowania zgodności ze starymi wersjami. Nie będziemy się tu nimi zajmować i pokażemy tylko te, które powinny być używane w nowszych programach. Jeśli przy przeglądaniu jakiegoś starszego kodu libpq spostrzeżemy nieznane wywołania funkcji bibliotecznych, to zawsze możemy zapoznać się z nimi w dokumentacji dostępnej na stronie internetowej PostgreSQL pod adresem: http://www.postgresql.org.

Aby skorzystać z którejś funkcji libpq, należy:

• Dołączyć plik nagłówkowy libpq-fe.h.

• Dopisać katalog pgsql do ścieżki zawierającej biblioteki dołączane podczas kompilacji.

• Skonsolidować program z biblioteką pq.

Końcówka „fe” w nazwie libpq-fe oznacza interfejs (od słów front-end). Aby skompilować plik używający libpq, należy użyć polecenia:

$ gcc -o program -I/usr/include/pgsql program.c -lpg

Oczywiście, szczegółowe parametry zależą od położenia katalogów instalacyjnych w danym systemie. Jeśli znajdują się one w innym miejscu, to trzeba będzie inaczej określić położenie katalogu zawierającego pliki dołączane i wskazać alternatywny katalog biblioteki za pomocą opcji -L/usr/local/pgsql/lib.

Funkcje do obsługi połączeń z bazą danych

Zaleca się połączenia z bazą danych PostgreSQL za pomocą polecenia PQconnectdb. Czasem należy użyć opcji -i, gdy ma być uruchomiony demon postmaster nasłuchujący na gniazdach TCP/IP i domeny UNIX.

PGconn *PQconnectdb(const char *conninfo);

Napis conninfo definiujący połączenie może zawierać sekwencję parametrów i wartości oddzielonych spacjami. Jeżeli w samej wartości ma znaleźć się spacja, to należy ją ująć w apostrofy. Parametry, którym nie nadaje się wartości w sposób jawny, uzyskują domyślnie wartości NULL. Również funkcje biblioteczne uzyskują wartości domyślne lub wartości zdefiniowane przez zmienne środowiskowe. Można definiować następujące parametry połączenia:

host	Nazwa komputera, z którym będzie zestawiane połączenie. Domyślnie jest to komputer lokalny.
port	Numer portu, na którym będzie utrzymywane połączenie. Domyślnie jest to standardowy port używany przez PostgreSQL o numerze 5432.
dbname	Nazwa bazy danych, z którą chcemy się połączyć. Domyślnie jest to taka sama nazwa jak nazwa zalogowanego użytkownika systemu Linux.
user	Nazwa użytkownika bazy danych. Domyślnie jest to nazwa używana przy logowaniu.
password	Hasło dostępu do bazy.
options	Wymagane opcje śledzenia.
tty	Plik lub terminal używany jako wyjście komunikatów o błędach wytwarzanych przez program.

Za każdym parametrem następuje znak równości, a następnie wartość tego parametru. Do połączenia się np. z bazą template1 na komputerze gw1 należy użyć następującego polecenia:

conn = PQconnectdb("host=gw1 dbname=template1");

Wskaźnik o wartości NULL jest zwracany tylko wtedy, gdy biblioteka nie zdoła przydzielić obiektu, z którym ma nastąpić połączenie. Nawet jeśli otrzymamy wskaźnik nie mający wartości NULL, to nadal trzeba sprawdzać, czy połączenie nastąpiło — musimy w tym celu wywoływać funkcję PQstatus.

ConnStatusType PQstatus(PGconn *conn);

Funkcja ta zwraca jedną z dwóch wartości: albo CONNECTION_OK, albo CONNECTION_BAD. Znaczenie ukryte jest w ich nazwach. Po udanym nawiązaniu połączenia z bazą pozostaje ono w takim stanie, chyba że występują problemy z siecią lub oddalona baza danych zostanie zamknięta.

Jeśli występują problemy z połączeniem, to tekst komunikatu o błędzie można uzyskać poprzez wywołanie funkcji:

char *PQerrorMessage(PGconn *conn);

Funkcja ta zwraca wskaźnik do ustalonego obszaru pamięci, a więc dany tekst komunikatu może być tekstem generowanym przy późniejszych wywołaniach funkcji z biblioteki libpq. Jeśli trzeba zamknąć połączenie z powodu zakończenia pracy programu lub awarii, należy czynić to za pomocą funkcji:

void PQfinish(PGconn *conn);

Funkcja ta musi być zawsze wywoływana, nawet gdy połączenie się nie udało. Jest to potrzebne, ponieważ powoduje ona nie tylko zamknięcie połączenia z bazą, ale także zwolnienie obszaru pamięci i innych zasobów związanych z połączeniem. Niewłaściwie zamknięte połączenie może spowodować, że program będzie wymagał nadmiernego zwiększenia zasobów systemowych.

Po zamknięciu połączenia wskaźnik obiektu połączeniowego nie oznacza niczego sensownego i nie może być przekazywany jako parametr do jakichkolwiek funkcji. Dobrym zwyczajem programisty powinno być nadanie mu wartości NULL tuż po wywołaniu funkcji PQfinish.

Znamy już kilka funkcji i możemy napisać pierwszy program służący do połączenia się z serwerem PostgreSQL. Nie będzie on wprawdzie zbyt użyteczny, ponieważ służy tylko do testowania połączenia, ale dzięki niemu uczynimy pierwszy krok. Musimy pamiętać o zmianie nazwy serwera i użytkownika na nazwy używane lokalnie, oraz o utworzeniu bazy danych o nazwie takiej jak nazwa użytkownika (tworzenie bazy opisaliśmy w poprzednim rozdziale).

#include <stdlib.h>

#include <stdio.h>

#include <libpq-fe.h>

int main()

{

PGconn *conn;

const char *connection_str = "host=localhost dbname=template1";

conn = PQconnectedb(connection_str);

if (PQstatus(conn) == CONNECTION_BAD) {

fprintf(stderr, "Connection to %s failed, %s, connection_str,

PQerrorMessage(conn));

} else {

printf("Connected OK\n");

}

PQfinish(conn);

return EXIT_SUCCESS;

}

Program ten bardzo łatwo można przeanalizować: najpierw tworzony jest napis używany jako parametr połączenia z bazą template1 na serwerze localhost, potem następuje próba połączenia, wyświetlenie komunikatu (jeżeli wystąpi błąd) i zamknięcie połączenia przed zakończeniem działania programu.

Uruchamianie poleceń SQL

Okazuje się niespodziewanie, że uruchamianie zapytań na serwerze jest bardzo proste. Istnieje tylko jedna funkcja służąca do wywołania polecenia i trzy funkcje wykorzystywane do sprawdzenia wyniku i odczytu informacji o błędach. Uruchomienie polecenia SQL odbywa się następująco:

PGresult *PQexec(PGconn *conn, const char *sql_string);

Funkcja ta zwraca wskaźnik o wartości NULL tylko w nadzwyczajnych okolicznościach, więc musi on być przechwytywany, ponieważ w przeciwnym wypadku można otrzymać wyniki na podstawie wskaźnika innej funkcji:

ExecStatusType *PQresultStatus(PGresult *result);

Wynik zawiera wyliczenie typu ExecStatusType, które ma jedną z podanych niżej wartości:

PGRES_EMPTY_QUERY	Nic nie zostało zrobione.
PGRES_COMMAND_OK	Polecenie zostało wykonane poprawnie, lecz żadne dane nie zostały zwrócone, ponieważ nie było to polecenie SELECT.
PGRES_TUPLES_OK	Polecenie zostało wykonane poprawnie i jakieś dane mogły być zwrócone.
PGRES_COPY_OUT	Trwa operacja kopiowania do pliku zewnętrznego.
PGRES_COPY_IN	Trwa operacja kopiowania z pliku zewnętrznego.
PGRES_BAD_RESPONSE	Zdarzyło się coś nieoczekiwanego.
PGRES_NONFATAL_ERROR	Wystąpił błąd niekrytyczny.
PGRES_FATAL_ERROR	Wystąpił błąd krytyczny.

Zwróćmy szczególną uwagę na definicję PGRES_TUPLES_OK. Otrzymanie takiej odpowiedzi oznacza, że polecenie SELECT zostało wykonane poprawnie, ale jednocześnie nie znaczy to, że jakieś dane zostały zwrócone. W następnym podrozdziale zobaczymy, jak sprawdzić, czy rzeczywiście dane zostały zwrócone. Błędy typu COPY dotyczą ładowania bazy danych lub tworzenia kopii zapasowej tej bazy.

Jeśli chcemy otrzymać tekst komunikatu o błędzie, to używamy funkcji:

const char *PQresultErrorMessage(PGresult *result);

Zauważmy, że tekstowy komunikat o błędzie otrzymujemy tu w inny sposób niż przy funkcjach obsługujących połączenia, gdzie stosuje się PQerrorMessage.

Często opłaca się znać liczbę wierszy, na które zadziałało polecenie SQL. Dotyczy to zwłaszcza polecenia DELETE, ponieważ PostgreSQL traktuje jako udane rónież wykonanie poprawnego składniowo polecenia, które faktycznie nie usunie żadnych wierszy.

Liczbę wierszy objętych poleceniami INSERT, UPDATE i DELETE można znaleźć za pomocą PQcmdTuples.

const char *PQcmdTuples(PGresult *result);

Zwróćmy uwagę na to, że takie wywołanie zwraca char *result, czyli ciąg cyfr kończący się wartością NULL w formacie znakowym, a nie liczbę całkowitą, której można się spodziewać. Wyznaczenie liczby wierszy objętych przez polecenie SELECT jest bardziej złożone i omówimy je później.

Zakończymy teraz omawianie kodu wynikowego i zajmiemy się przekazaniem do biblioteki informacji o konieczności zwolnienia przydzielonej pamięci. Podobnie jak w kodzie dotyczącym połączeń, błąd popełniony tutaj spowoduje „wycieki pamięci” (ang. memory leaks) w naszej aplikacji.

void PQclear(PQresult *result);

Inną funkcją, która pasuje do tego podrozdziału i która może być przydatna przy wyszukiwaniu błędów, jest:

const char *PQresStatus(ExecStatusType status);

Przekształca ona numer statusu na komunikat tekstowy.

Wiemy już teraz wystarczająco dużo, aby napisać nasz pierwszy program w języku C, który będzie uruchamiał polecenia SQL. Ponieważ nie mówiliśmy jeszcze o tym, jak uzyskiwać wyniki zapytań, więc na razie zajmiemy się uruchomianiem polecenia DELETE. Oto pierwsza funkcja o nazwie del1.c wykonująca coś użytecznego i rozszerzająca pierwotną funkcję con1.c. Będziemy łączyć się z bazą danych rick na serwerze gw1.

W tym rozdziale opiszemy operacje na pojedynczej tabeli o nazwie children w bazie danych, którą utworzyliśmy w poprzednim rozdziale. Tabelę tę można w razie potrzeby utworzyć ponownie za pomocą polecenia SQL użytego w interpreterze psql:

CREATE TABLE children (

childno SERIAL,

fname VARCHAR,

age INTEGER

);

W podanym niżej kodzie zaznaczone są różnice między con1.c i del1.c:

#include <stdlib.h>

#include <stdio.h>

#include <libpq-fe.h>

int main()

{

PGconn *conn;

PGresult *result;

const char *connection_str = "host=gw1 dbname=rick";

conn = PQconnectdb(connection_str);

if (PQstatus(conn) == CONNECTION_BAD) {

fprintf(stderr, "Connection to %s failed, %s", connection_str,

PQerrorMessage(conn));

} else {

printf("Connected OK\n");

}

result = PQexec(conn, "DELETE FROM children WHERE fname = "'freda'");

if (!result) {

printf("PQexec command failed, no error code\n");

} else {

switch (PQresultStatus(result)) {

case PGRES_COMMAND_OK:

printf("Command executed OK, %s rows

affected\n", PQcmdTuples(result));

break;

case PGRES_TUPLES_OK:

printf("Query may have returned data\n");

break;

default:

printf("Command failed with code %s, error message %s\n',

PQresStatus(PQresultStataus(result)),

PQresultErrorMessage(result));

break;

}

PQclear(result);

}

PQfinish(conn);

return EXIT_SUCCESS;

}

Jeśli upewnimy się, że w tabeli children znajduje się wiersz zawierający w polu fname nazwę freda, to po kompilacji i uruchomieniu tego programu zobaczymy:

[rick@gw1 psql]$ ./del1

Connected OK

Command executed OK, 1 rows affected

Teraz nie ma już wiersza, który pasowałby do tych kryteriów i mógłby być usunięty. Jeśli więc uruchomimy program ponownie, to nadal jest on wykonywany poprawnie, ale nie dotyczy żadnego wiersza:

[rick@gw1 psql]$ ./del1

Connected OK

Command executed OK, 0 rows affected

Należy dokładnie odróżniać polecenia działające i nie wpływające na wiersze tabeli od poleceń, które nie udają się z powodu błędów składniowych.

Uzyskiwanie wyników zapytań

Przejdziemy teraz do najczęściej używanej, ale także najbardziej złożonej części biblioteki libpq, czyli do pobierania danych.

Podczas pobierania danych z bazy może się pojawić wiele problemów. Przede wszystkim nie będziemy wiedzieli z góry, ile wierszy zostanie pobranych. Jeśli uruchamiamy polecenie SELECT, używając '*' jako nazwy pola (chcąc pobrać wszystkie pola), to możemy nie wiedzieć nawet, ile pól lub jakiego rodzaju dane znajdują się w pobieranych wierszach. Przewidywanie tych okoliczności czyni tę część API bardziej złożoną. Nie trzeba jednak popadać w panikę, po prostu musimy poznać kilka dodatkowych wywołań API.

Najpierw przekształcimy program testowy del1.c na zapytanie zwracające dane i, korzystając z okazji, nieco go zmodyfikujemy, by łatwiej było dodawać nowe funkcje po wykonaniu polecenia SQL. Niech nowy plik nazywa się sel1.c:

#include <stdlib.h>

#include <stdio.h>

#include <libpq-fe.h>

PGconn *conn = NULL;

void tidyup_and_exit();

int main()

{

PGresult *result;

const char *connection_str = "host=gw1 dbname=rick";

conn = PQconnectdb(connection_str);

if (PQstatus(conn) == CONNECTION_BAD) {

fprintf(stderr, "Connection to %s failed, %s", connection_str,

PQerrorMessage(conn));

tidyup_and_exit();

} else {

printf("Connected OK\n");

}

result = PQexec(conn, "SELECT age, fname FROM children WHERE age < '6'");

if (!result) {

printf("PQexec command failed, no error code\n");

tidyup_and_exit();

} else {

switch (PQresultStatus(result)) {

case PGRES_COMMAND_OK:

printf("Command executed OK, %s rows affected\n", PQsmdTuples(result));

break;

case PGRES_TUPLES_OK:

printf("Query may have returned data\n");

break;

default:

printf("Command failed with code %s, error message %s\n",

PQresStatus(PQresultStatus(result)),

PQresultErrorMessage(result));

PQclear(result);

tidyup_and_exit();

break;

}

}

/* Tutaj dodany jest nowy kod */

if (result) PQclear(result);

PQfinish(conn);

return EXIT_SUCCESS;

}

void tidyup_and_exit() {

if (conn != NULL) PQfinish(conn);

exit(EXIT_FAILURE);

}

Dodaliśmy tu nową funkcję o nazwie tidyup_and_exit, która pozwala na wyjście z programu wtedy, gdy nie uda się operacja w bazie danych. Oczywiście, przerywanie działania aplikacji z powodu jednego nieudanego polecenia SQL jest w kodzie produkcyjnym zbyt rygorystyczne, ale dla celów testowych łatwiej pracować z kodem działającym w taki sposób. Zmieniliśmy także polecenie DELETE na polecenie SELECT, które będzie zwracać jakieś dane.

Po uruchomieniu nowej wersji programu można się przekonać, czy kod prawidłowo rozpoznaje możliwość zwracania danych:

[rick@gw1 psql]$ ./sel1

Connected OK

Query may have returned data

Teraz znajdziemy sposób określenia liczby faktycznie zwracanych wierszy. Możemy to zrobić za pomocą wywołania Pqntuples (pamiętając, że w PostgreSQL wiersze to „krotki”):

int PQntuples(PGresult);

Przy zmianie sel1.c na sel2.c trzeba po prostu zmienić jeden wiersz programu, w którym sprawdzany jest kod zwracany przez PQresultStatus:

case PGRES_TUPLES_OK:

printf("Query was OK and returned %d rows\n", PQntuples(result));

break;

Po ponownym uruchomieniu zapytania otrzymamy następujący wynik:

[rick@gw1 psql]$ ./sel2

Connected OK

Query was OK and returned 3 rows

Wygląda to obiecująco, ale potrzebujemy przecież dostępu do pobranych danych. Od tego momentu zajmiemy się więc najszybszym i najprostszym sposobem osiągnięcia tego celu. Sposób ten polega na użyciu jednej ze specjalnych funkcji z biblioteki psql przekazującej wszystkie dane w postaci strumienia do pliku. Funkcja ta jest łatwa w użyciu i stanowi dużą pomoc przy wyszukiwaniu błędów.

Funkcja nazywa się PQprint, a jej wywołanie ma postać:

void PQprint(FILE *stream, PGresult, PQprintOpt *options);

Korzystając z niej, musimy określić strumień wyjściowy, wskaźnik wynikowy zwracany przez polecenie SQL oraz wskaźnik do struktury określającej opcje.

Struktura określająca opcje zdefiniowana w pliku nagłówkowym libpq-fe ma następującą postać:

typedef struct_PQprintOpt

{

pqbool header; /* druk nagłówków pól i zliczenia wierszy */

pqbool align; /* wyrównywanie pól */

pqbool standard; /* przestarzały format */

pqbool html3; /* wyjście w postaci tabel html */

pqbool expanded; /* rozszerzanie tabel */

pqbool pager; /* podział wyjścia na strony, jeśli konieczny */

char *fieldSep; /* separator pól */

char *tableOpt; /* wstaw do HTML: <table ...> */

char *caption; /* tytuł HTML: <caption> */

char ** fieldName; /* tablica zamienników nazw pól, ogr. przez NULL */

} PQprintOpt;

Opcje te umożliwiają zachowanie pewnej kontroli nad danymi wyjściowymi. Można zauważyć, że w pliku nagłówkowym istnieją także inne funkcje służące do zapisu danych do strumieni, ale zastępuje je funkcja PQprint.

Na początku programu musimy zdefiniować dwie nowe zmienne:

FILE *output_stream;

PQprintOpt print_options;

Po pobraniu danych można je wyświetlić:

output_stream = fopen("/dev/tty", "w");

if (output_stream == NULL) {

PQclear(result);

tidyup_and_exit();

}

memset(&print_options, '\0, sizeof(print_options));

print_options.header = 1; /* druk nagłówków */

print_options.align = 1; /* wyrównane pola */

print_options.html3 = 0; /* wyjście jako tabele html */

print_options.fieldSep = "|"; /* separator pola */

print_options.fieldName = NULL; /* alternatywne nazwy pól */

PQprint(output_stream, result, &print_options);

Zwróćmy uwagę na to, że nie zostały wyraźnie podane wszystkie pola w strukturze PQprintOpt, a zapotrzebowanie na pamięć wymaganą przez wartości domyślne zostało określone za pomocą memset. Należy tu jednak pamiętać, że ważne jest określenie separatora pól fieldSep.

Po uruchomieniu programu w tej wersji otrzymamy:

[rick@gw1 psql]$ ./sel3

Connected OK

Query was OK and returned 3 rows

age|fname

---+------

5|Adrian

4|Allen

1|Fred

(3 rows)

Jest to pierwszy fragment naszego osadzonego kodu SQL, który pobiera dane.

Niestety, jest tu kilka problemów. Po pierwsze, kierowanie danych do strumienia spełnia bardzo dobrze swoją rolę przy wyszukiwaniu błędów, ale na pewno nie jest rozwiązaniem, które można zastosować przy faktycznym przetwarzaniu danych. Po drugie, wszystkie dane są pobierane na raz, co może być dobre, jeśli nie ma ich zbyt wiele, ale stanie się nieznośne przy dużej ilości danych.

Kursory

Załóżmy, że nasz program korzysta z wielkiej bazy danych zawierającej tysiące wpisów i że uruchamiamy zapytanie zwracające wszystkie wpisy. Program zażąda wówczas przydzielenia bardzo dużego obszaru pamięci na przechowanie wyników. Ponieważ dostęp do bazy może z dużym prawdopodobieństwem odbywać się z sieci, to potrzebny jest sposób pobierania danych w niewielkich porcjach, np. po jednym wierszu. Istnieje standardowa metoda realizacji takiego zadania stosowana przy pobieraniu danych z bazy SQL do programu w języku C (a także do programów napisanych w innych językach). Mamy tu na myśli tzw. kursory.

Z kursorem nie zetknęliśmy się jeszcze w tej książce, ponieważ zwykle jest on używany w poleceniach SQL osadzonych w zewnętrznych programach lub w funkcjach języka proceduralnego, które są przechowywane w bazie danych (nazywane są często „procedurami zachowanymi”). W zasadzie kursory nie bywają używane w wierszu poleceń. Standard SQL92 określa, że kursory mają być stosowane w osadzonych programach i w większości baz danych są to jedyne miejsca, gdzie można ich użyć. PostgreSQL, rozszerzając standard SQL, pozwala na użycie kursorów także w wierszu poleceń.

Kursor (cursor) oznacza sposób przemieszczania się wewnątrz zestawu wyników polegający na pobieraniu danych małymi porcjami. Aby użyć kursora, należy najpierw go zadeklarować (DECLARE), nadając mu nazwę powiązaną z poleceniem SELECT. Następnie, używając polecenia FETCH, można pobierać dane zazwyczaj wiersz po wierszu, chociaż można je także pobierać w porcjach obejmujących wiele wierszy.

Standard SQL92 (podobnie jak wiele innych) wymaga użycia dodatkowego polecenia OPEN, które służy do otwarcia kursora o podanej nazwie i które musi być umieszczone między poleceniami DECLARE i FETCH. Biblioteka libpq z bazy PostgreSQL nie wymaga takiej operacji, ponieważ polecenie DECLARE wywołuje je automatycznie. Przy okazji omawiania alternatywnego sposobu osadzania kodu SQL w programie w języku C, czyli tzw. ecpg, pokażemy konieczność stosowania polecenia OPEN CURSOR.

Korzystając z pseudokodu możemy zapisać algorytm korzystania z kursora:

BEGIN A TRANSACTION

DECLARE CURSOR mycursor FOR SELECT-statement

[OPEN mycursor]

DO {

FETCH some data from mycursor

Process the row(s) retrived

} WHILE the FETCH command found data

CLOSE mycursor

COMMIT WORK

Składnia deklaracji kursora jest bardzo prosta:

DECLARE nazwa_kursora [BINARY] CURSOR FOR <polecenie_SELECT>

Taka deklaracja tworzy kursor o podanej nazwie i automatycznie otwiera go, jeśli korzystamy z biblioteki libpq. Zauważmy, że kursor jest związany z pojedynczym poleceniem SELECT. Nazwa kursora może być od tego momentu używana w odwołaniach do tego polecenia. Opcja BINARY jest wymagana tylko wówczas, gdy chcemy pobierać dane binarne przechowywane w jakiejś kolumnie tabeli — jest to zaawansowane i rzadko spotykane zagadnienie, a więc nie będziemy się nim tutaj zajmować. Eksperci znający standard SQL92 zauważyli zapewne brak niektórych słów kluczowych SQL, a mianowicie SCROLL i FOR READ ONLY lub FOR UPDATE. W bazie PostgreSQL wszystkie kursory mogą być przemieszczane, a więc słowo kluczowe SCROLL jest ignorowane, chociaż można go tu użyć. PostgreSQL pozwala na zastosowanie kursorów tylko przy odczycie danych z bazy, a nie przy ich modyfikacji w bazie, a zatem słowo FOR jest zbędne (można jednak użyć FOR READ ONLY dla zachowania zgodności ze standardem).

Składnia polecenia FETCH jest również bardzo prosta:

FETCH [FORWARD|BACKWARD] [liczba|ALL|NEXT] [IN nazwa_kursora];

Zazwyczaj słowo FORWARD lub BACKWARD jest pomijane, a domyślnym słowem kluczowym jest FORWARD. Można pobierać (FETCH) określoną liczbę wierszy lub wszystkie wiersze (ALL). Słowo kluczowe NEXT oznacza to samo, co liczba 1. Polecenie SELECT podane w deklaracji kursora określa faktyczny rodzaj i zawartość pobieranych wierszy.

Po zapoznaniu się z tymi zasadami musimy wypróbować je w praktyce. W podanym niżej programie sel4.c zastosowano kursor do pobierania danych z bazy:

#include <stdlib.h>

#include <stdio.h>

#include <string.h>

#include <libpq-fe.h>

PGconn *conn = NULL;

void tidyup_and_exit();

int execute_one_statement(const char *stmt_to_exec, PGresult **result);

int main ()

{

PGresult *result;

int stmt_ok;

const char *connection_str = "host=gw1 dbname=rick";

FILE *output_stream;

PQprintOpt print_options;

conn = PQconnectdb(connection_str);

if (PQstatus(conn) == CONNECTION_BAD) {

fprint(stderr, "Connection to %s failed, %s", connection_str,

PQerrorMessage(conn));

tidyup_and_exit();

} else {

printf("Connected OK\n");

}

stmt_ok = execute_one_statement ("BEGIN WORK", &result);

if (stmt_ok) {

PQclear(result);

stmt_ok = execute_one_statement("DECLARE age_fname_cursor CURSOR FOR

SELECT age, fname FROM children

WHERE age < '6'", &result);

if (stmt_ok) {

PQclear(result);

stmt_ok = execute_one_statement ("FETCH ALL IN age_fname_cursor",

&result);

if (stmt_ok) {

PQclear(result);

stmt_ok = execute_one_statement("COMMIT WORK", &result);

}

}

}

if (stmt_ok) PQclear(result);

PQfinish(conn);

return EXIT_SUCCESS;

}

int execute_one_statement(const char *stmt_to_exec, PGresult **res_ptr) {

int retcode = 1;

const char *str_res;

PGresult *local_result;

printf("About to execute %s\n", stmt_to_exec);

local_result = PQexec(conn, stmt_to_exec);

*res_ptr = local_result;

if (!local_result) {

printf("PQexec command failed, no error code\n");

retcode = 0;

} else {

switch (PQresultStatus(local_result)) {

case PGRES_COMMAND_OK:

str_res = PQcmdTuples(local_result);

if (strlen(str_res) > 0) {

printf("Command executed OK, %s rows affected\n", str_res);

} else {

printf("Command executed OK, no rows affected\n");

}

break;

case PGRES_TUPLES_OK:

printf("Select executed OK, %d rows found\n", PQntuples(local_result));

break;

default:

printf("Command failed with code %s, error message %s\n",

PQresStatus(PQresultStatus(local_result)),

PQresultErrorMessage(local_result));

PQclear(local_result);

retcode = 0;

break;

}

}

return retcode;

} /* execute_one_statement */

void tidyup_and_exit() {

if (conn != NULL) PQfinish(conn);

exit(EXIT_FAILURE);

}

Ważniejsze zmiany w stosunku do poprzedniej wersji kodu zostały wyróżnione w powyższym tekście. Usunięto również fragmenty dotyczące wyświetlania wyników. Wkrótce pokażemy bardziej użyteczny sposób dostępu do pobranych danych.

Po uruchomieniu tego programu otrzymamy następujące komunikaty:

Connected OK

About to execute BEGIN WORK

Command executed OK, no rows affected

About to execute DECLARE age_fname_cursor CURSOR FOR SELECT age, fname FROM

children WHERE age < '6'

Command executed OK, no rows affected

About to execute FETCH ALL IN age_fname_cursor

Select executed OK, 3 rows found

About to execute COMMIT WORK

Command executed OK, no rows affected

Pobieranie danych w pojedynczych wierszach jest teraz bardzo łatwe. Wystarczy zmienić słowo kluczowe ALL na 1 w poleceniu FETCH i sprawdzić, czy rzeczywiście zwracane są pojedyncze wiersze. Polecenie FETCH, podobnie jak SELECT, może się udać bez zwracania żadnych danych.

Zmienionymi wierszami w sel5.c są:

conn = PQconnectdb(connection_str);

if (PQstatus(conn) == CONNECTION_BAD) {

fprint(stderr, "Connection to %s failed, %s, connection_str,

tidyup_and_exit();

} else {

printf("Connected OK\n");

}

stmt_ok = execute_one_statement("BEGIN WORK", &result);

if (stmt)ok) {

PQclear(result);

stmt_ok = execute_one_statement("DECLARE age_fname_cursor CURSOR FOR SELECT

age, fbname FROM children WHERE age < '6'", &result);

stmt_ok = execute_one_statement("FETCH 1 IN age_fname_cursor", &result);

while(stmt_ok && PQntuples(result) > 0) {

PQclear(result);

stmt_ok = execute_one_statement("FETCH NEXT IN age_fname_cursor"'

&result);

}

stmt_ok = execute_one_statement("COMMIT WORK", &result);

}

if (stmt_ok) PQclear(result);

PQfinish(conn);

return EXIT_SUCCESS;

}

Na wyjściu programu otrzymamy:

[rick@gw1 psql]$ ./sel5

Connected OK

About to execute BEGIN WORK

Command executed OK, no rows affected

About to execute DECLARE age_fname_cursor CURSOR FOR SELECT age, fname FROM

children WHERE age < '6'

Command executed OK, no rows affected

About to execute FETCH 1 IN age_fname_cursor

Select executed OK, 1 rows found

About to execute FETCH NEXT IN age_fname_cursor

Select executed OK, 1 rows found

About to execute FETCH NEXT IN age_fname_cursor

Select executed OK, 1 rows found

About to executed FETCH NEXT IN age_fname_cursor

Select executed OK, 0 rows found

About to execute COMMIT WORK

Command executed OK, no rows affected

Jak widać, bardzo łatwo pobiera się dane w postaci pojedynczych wierszy. Jedyną wadą takiego sposobu, która zazwyczaj nie ma większego znaczenia, jest nieznajomość liczby pobieranych danych aż do chwili zakończenia całej operacji. Dzieje się tak, ponieważ PQntuples(result) ma wartość 1 przy pobieraniu jednego wiersza za pomocą polecenia FETCH, czego należało się przecież spodziewać.

Ponieważ mamy już ułatwiony dostęp do danych, to teraz zajmiemy się dostępem do poszczególnych informacji.

Pobieranie informacji o kolumnach

Pierwszą użyteczną porcją informacji pobraną z bazy danych może być informacja o kolumnach tabeli (czyli o ich nazwach i typach przechowywanych w nich danych). Zadanie to można wykonać w prosty sposób za pomocą trzech funkcji: jedna z nich podaje liczbę kolumn, druga podaje nazwę każdej kolumny, a trzecia rozmiar danych w każdej kolumnie. Można oczywiście podać nazwę każdej żądanej kolumny, ale w teorii znany jest przecież z góry typ zawartych w nich danych.

Dobrym zwyczajem jest podawanie nazwy każdej pobieranej kolumny. Dzięki temu nie trzeba obawiać się niespodzianek po dodaniu nowej kolumny do tabeli w bazie danych. Oprócz tego, jeżeli jakaś kolumna zostanie usunięta, to za pomocą programu grep można łatwo wykryć te miejsca w kodzie, w których występuje nazwa usuniętej kolumny. Przy założeniu, że typ kolumny w kodzie nie jest dokładnie określony, można automatycznie dostosować się do jego zmian, określając go dopiero po uruchomieniu programu. Niestety, wymaga to dopisania większych fragmentów kodu i stwarza ryzyko popełnienia większej liczby błędów oraz zmniejsza nieco wydajność kodu.

Liczbę kolumn zwraca funkcja PQnfields:

int PQnfields(PGresult *result);

Nazwę kolumny można określić za pomocą funkcji PQfname, podając w jej wywołaniu indeks kolumny (pierwsza kolumna ma indeks 0):

char *PQfname(PGresult *result, int field_index);

Pewne pojęcie o rozmiarze danych w kolumnie można uzyskać za pomocą funkcji PQfsize. Użyliśmy tu celowo słowa „pojęcie”, ponieważ funkcja ta zwraca rozmiar obszaru pamięci użytego wewnętrznie przez PostgreSQL i np. dla pól o zmiennej długości, takich jak VARCHAR, wynosi on -1.

int PQfsize(PGresult *result, int field_index);

Oczywistym brakiem w zestawie tych trzech funkcji jest to, że nie jest podawany typ kolumny. Niestety, funkcja PQftype, która ma to robić, zwraca typ Oid (czyli definicję typedef dla liczby całkowitej bez znaku). Jest więc to informacja o wewnętrznej reprezentacji typu, która nigdzie nie jest udokumentowana i dlatego jest prawie bezużyteczna. Obecnie nie będziemy więc używać tej funkcji, mając nadzieję, że w kolejnych wydaniach bazy PostgreSQL lub biblioteki libpq zostanie uwzględniona jakaś funkcja zwracająca typ danych w kolumnie.

Możemy teraz wykorzystać zdobytą wiedzę, zmodyfikować nasz program cel5.c i nadać mu nazwę sel6.c. Nie ma znaczenia, który wiersz pobranych danych zostanie użyty do uzyskania informacji o nagłówku kolumny, ponieważ nawet gdy polecenie SELECT nie zwróci żadnych wierszy, to informacja o kolumnach nadal będzie dostępna.

Zmiany w kodzie są niewielkie, a więc pokazujemy tu tylko dopisane fragmenty, nie pokazując całego programu.

Najpierw dodamy prototyp naszej nowej funkcji:

void show_column_info(PGresult *result);

Następnie wywołamy ją przy pobieraniu danych z bazy. Aby pokazać, że wszystko działa jak należy, wywołanie będzie odbywać się za każdym razem, gdy zwracane są dane. Oczywiście, w kodzie produkcyjnym nie stosuje się takiego rozwiązania.

if (stmt_ok) {

PQclear(result);

stmt_ok = execute_one_statement("FETCH 1 IN age_fname_cursor"'

&result);

if (stmt_ok) show_column_info(result);

while(stmt_ok && PQntuples(result) > 0) {

show_column_info(result);

PQclear(result);

stmt_ok = execute_one_statement("FETCH NEXT IN age_fname_cursor",

&result);

}

stmt_ok = execute_one_statement("COMMIT WORK", &result);

}

Mamy więc ostateczną postać funkcji show_column_info:

void show_column_info(PGresult *result) {

int num_columns;

int i;

if (!result) return;

num_columns = PQnfields(result);

printf("%d columns in the result set\n", num_columns);

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

printf("Field %d, Name %s, Internal size %d\n",

i,

PQfname(result, i),

PQfsize(result, i));

}

} /* show_column_info */

Po uruchomieniu tego programu otrzymujemy następujący wynik:

About to execute FETCH NEXT IN age_fname_cursor

Select executed OK, 1 rows found

2 columns in the result set

Field 0, Name age, Internal size 4

Field 1, name fname, Internal size -1

Z powodu braku miejsca nie pokazujemy tu pełnego wyniku, lecz tylko istotny dla nas fragment. Należy zwrócić uwagę na to, że rozmiar fname jest podawany jako -1, ponieważ jest to pole o zmiennym rozmiarze (VARCHAR).

Dostęp do przetworzonych danych

Oczywiste jest, że musimy uzyskać dostęp do pobranych danych. Jak już wspominaliśmy wcześniej, w żaden sensowny sposób nie można poznać typu pobranych danych, a zatem Czytelnik może mieć wątpliwości, w jaki sposób mamy zamiar zapisać to w kodzie programu. Odpowiedź jest bardzo prosta: funkcje z biblioteki libpq zawsze zwracają wartości jako napisy, które można przekształcać we własnym zakresie (w rzeczywistości nie jest to do końca prawdziwe, ponieważ po użyciu słowa kluczowego BINARY w deklaracji kursora dane będą zwracane w postaci binarnej — z tej zaawansowanej właściwości bazy PostgreSQL korzysta jednak niewielka liczba użytkowników).

Musimy więc określić rozmiar obszaru pamięci, który będzie zajęty przez pobrane dane. Zadanie to realizuje funkcja PQgetlength:

int PQgetlength(PGresult *result, int tuple_number, int field_index);

Zauważmy, że występuje tu argument tuple_number, co, jak pamiętamy, oznacza wiersz w terminologii używanej przez autorów PostgreSQL. Jest on użyteczny wówczas, gdy nie korzystamy z kursora i pobieramy wszystkie dane podczas jednej operacji albo żądamy pobrania jednorazowo więcej niż jednego wiersza (tak jak w ostatnim przykładzie). Bez tego argumentu pobieranie kilku wierszy na raz byłoby bezsensowne, ponieważ mielibyśmy dostęp tylko do danych z wiersza pobranego na samym końcu.

Napis reprezentujący dane otrzymujemy za pomocą funkcji PQgetvalue:

char *PQgetvalue(PGresult *result, int tuple_number, int field_index);

Napis ten kończy się wartością NULL. Faktyczna treść napisu jest umieszczona w strukturze PQresult, więc jeśli chcemy ją udostępnić, to należy skopiować ją stamtąd przed wykonaniem jakichś operacji na tej strukturze. W tym momencie bardziej wnikliwi czytelnicy mogą zauważyć przeszkodę — w jakiż to sposób można rozróżnić pusty napis zwracany dlatego, że napis w bazie ma zerową długość, od napisu zwracanego wówczas, gdy pole w bazie ma wartość NULL (co oznacza, że wartość jest nieznana, a nie że pole jest puste)? Odpowiedź na to pytanie daje specjalna funkcja PQgetisnull, która rozróżnia te dwie wartości w bazie danych:

int PQgetisnull(PGresult *result, int tuple_number, int field_index);

Funkcja ta zwraca 1, jeżeli pole w bazie danych ma wartość NULL, w przeciwnym wypadku zwraca 0.

Nareszcie dotarliśmy do takiego miejsca, w którym można utworzyć ostateczną wersję naszego testowego programu, który zwróci dane pobrane z bazy wiersz po wierszu oraz wyświetli informacje o kolumnach i pokaże zawarte w nich dane. Ponieważ chcemy ten program uruchamiać, to ustawimy dodatkowo w jednym z pobieranych wierszy wartość NULL, co posłuży do sprawdzenia poprawności jej wykrywania. W zależności od danych wprowadzonych do tabeli children można do tego celu użyć różnego identyfikatora childno. Załóżmy np. że dla childno równego 9 mamy jako age wpisane 1, zaś polu fname nadamy wartość NULL za pomocą polecenia psql:

UPDATE children set fname = NULL where childno = 9;

Mamy więc ostateczną wersję programu w języku C korzystającego z polecenia SELECT. Nazwaliśmy go sel7.c, zaznaczając główne zmiany w stosunku do wersji poprzedniej i usuwając niektóre wiersze pomocnicze służące do wyszukiwania błędów:

#include <stdlib.h>

#include <stdio.h>

#include <string.h>

#include <libpq-fe.h>

PGconn *conn = NULL;

void tidyup_and_exit();

int execute_one_statement(const char *stmt_to_exec, PGresult **result);

void show_column_info(PGresult *result);

void show_one_row_data (PGresult *result);

int main()

{

PGresult *result;

int stmt_ok;

char *connection_str = "host=gw1 dbname=rick";

FILE *output_stream;

PQprintOpt print_options;

conn = PQconnectdb(connection_str);

if (PQstatus(conn) == CONNECTION_BAD) {

fprint(stderr, "Connection to %s failed, %s", connection_str,

PQerrorMessage(conn));

tidyup_and_exit();

} else {

printf("Connected OK\n");

}

stmt_ok = execute_one_statement("BEGIN WORK", &result);

if (stmt_ok) {

PQclear(result);

stmt_ok = execute_one_statement("DECLARE age_fname_cursor CURSOR FOR

SELECT age, fname FROM children WHERE

age < '6'", &result);

if (stmt_ok) {

PQclear(result);

stmt_ok = execute_one_statement("FETCH 1 IN age_fname_cursor",

&result);

if (stmt_ok) show_column_info(result);

while(stmt_ok && PQntuples(result) > 0) {

show_one_row_data(result);

PQclear(result);

stmt_ok = execute_one_statement("FETCH NEXT IN age_fname_cursor",

&result);

}

stmt_ok = execute_one_statement("COMMIT WORK", & result);

}

}

if (stmt_ok) PQclear(result);

PQfinish(conn);

return EXIT_SUCCESS;

}

int execute_one_statement(const char *stmt_to_exec, PGresult **res_ptr) {

int retcode = 1;

const char *str_res;

PGresult *local_result;

printf("About to execute %s\n", stmt_to_exec);

local_result = PQexec(conn, stmt_to_exec);

*res_ptr = local_result;

if (!local_result) {

printf("PQexec command failed, no error code\n");

retcode = 0;

} else {

switch (PQresultStatus(local_result)) {

case PGRES_COMMAND_OK:

str_res = PQcmmdTuples(local_result);

if (strlen(str_res) > 0) {

printf("Command executed OK, %s rows affected\n", str_res);

} else {

printf("Command executed OK, no rows affected\n");

}

break;

case PGRES_TUPLES_OK:

printf("Select executed OK, %d rows found\n", PQntuples(local_result));

break;

default:

printf("Command failed with code %s, error message %s\n",

PQresStatus(PQresultStatus(local_result)),

PQresultErrorMessage(local_result));

PQclear(local_result);

retcode = 0;

break;

}

}

return retcode;

} /* execute_one_statement */

void show_columns_info(PGresult *result) {

int num_columns = 0;

int i;

if (!result) return;

num_columns = PQnfields(result);

printf("%d columns in the result set\n", num_columns);

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

printf("Field %d, Name %s, Internal size %d\n",

i,

PQfname(result, i),

PQfsize(result, i));

}

} /* show_column_info */

void show_one_row_data(PGresult *result) {

int col;

for(col = 0; col < PQnfields(result); col++) {

printf("DATA: %s\n", PQgetisnull(result, 0, col) ? "<NULL>":

PQgetvalue(result, 0, col));

}

} /* show_one_row_data */

void tidyup_and_exit() {

if (conn != NULL) PQfinish(conn);

exit(EXIT_FAILURE);

}

Zauważmy, że są tu sprawdzane wartości NULL we wszystkich kolumnach. Po uruchomieniu tego programu otrzymamy:

Connected OK

2 columns in the result set

Field 0, Name age, Internal size 4

Field 1, Name fname, Internal size -1

DATA: 4

DATA: Adrian

DATA: 4

DATA: Allen

DATA: 1

DATA: <NULL>

Na tym zakończymy prezentację biblioteki libpq. Opisaliśmy tutaj sposób korzystania z tej biblioteki przy dostępie do danych za pomocą kursorów. Pokazaliśmy również, jak można uzyskać informacje o kolumnach i przetwarzać wartości NULL z bazy danych.

Biblioteka ecpg

Nadeszła pora na zapoznanie się z alternatywnym sposobem połączenia języków SQL i C, polegającym na wstawianiu poleceń SQL do kodu programu w języku C i użyciu preprocesora przed wywołaniem kompilatora tego języka. Istnieje oczywiście biblioteka wykorzystywana jako interfejs między językiem C i bazą danych, ale jej funkcje są ukrywane przez preprocesor.

Biblioteka ecpg rozpowszechniana w pakiecie PostgreSQL spełnia wymagania ANSI dotyczące osadzania poleceń SQL w kodzie C, więc to, co napisano niżej, będzie zapewne znane programistom korzystającym z pakietów PRO*C firmy Oracle lub ESQL-C firmy Informix. Podczas pisania tej książki biblioteka ecpg nie obsługiwała jeszcze niektórych rzadziej używanych właściwości „osadzonego SQL”, zaś standardowa dokumentacja biblioteki dostarczana z PostgreSQL była dość uboga.

Ponieważ zapoznaliśmy się już z podstawami SQL, to ten podrozdział będzie dość krótki. Preprocesor ecpg rozpoznaje w programie fragmenty kodu, które ma przetwarzać, na podstawie sekwencji „exec sql” umieszczonej na początku wiersza. Za tym oznaczeniem następuje właściwe polecenie SQL zakończone średnikiem. W zależności od składni przetwarzany fragment może obejmować jeden wiersz lub cały blok.

Jeżeli chcemy napisać prosty program w języku C, który będzie wywoływał polecenie UPDATE, to osadzamy je po prostu w kodzie źródłowym.

Czy może być coś prostszego? Utwórzmy więc taki program. Zgodnie z tradycją — nazwy plików programów zawierających osadzone polecenia SQL mają końcówkę pgc, zatem nasz program nazwiemy upd1.pgc:

#include <stdlib.h>

exec sql include sqlca;

main() {

exec sql connect to 'rick@gw1';

exec sql BEGIN WORK;

exec sql UPDATE children SET fname = 'Gavin' WHERE childno = 9;

exec sql COMMIT WORK;

exec sql disconnect all;

return EXIT_SUCCESS;

}

Na pierwszy rzut oka trudno doszukać się w tym programu w języku C. Jeżeli jednak pominiemy wiersze rozpoczynające się od exec sql, to zobaczymy strukturę najprostszego programu w tym języku zawierającego tylko funkcję main(). Kompilacja powyższego programu przebiega dwuetapowo. Najpierw jest uruchamiany preprocesor ecpg, a następnie wynik jest przetwarzany przez kompilator języka C i konsolidowany z biblioteką ecpg. Aby kompilacja się udała, należy także dodać w opcji -I odpowiednią ścieżkę do dołączanego pliku nagłówkowego ecpg, aby kompilator mógł go znaleźć. Polecenia służące do kompilacji powyższego programu są więc następujące:

$ ecpg -t -I/usr/include/pgsql upd1.pgc

$ gcc -o upd1 -I/usr/include/pgsql upd1.c -lecpg -lpg

Preprocesor ecpg przetwarza najpierw plik źródłowy, dając w wyniku plik .c, który następnie jest normalnie kompilowany i konsolidowany z dwiema bibliotekami PostgreSQL. Opcja -t w wierszu poleceń wywołującym ecpg oznacza, że chcemy zarządzać własnymi transakcjami, podając bezpośrednio polecenia BEGIN WORK i COMMIT WORK w kodzie programu. Domyślnie ecpg uruchamia transakcję w momencie połączenia się z bazą, co nie jest szkodliwe, lecz autor woli definiować ją sam.

Zwróćmy uwagę na parametr połączenia rick@gw1. Oznacza on żądanie połączenia się z bazą danych rick na serwerze gw1. Nie jest tu wymagane żadne hasło, ponieważ jest to komputer lokalny, na którym łączący się użytkownik jest już zalogowany. Ogólnie mówiąc, należy jednak podawać pełne dane w stylu URL:

<protokół>:<usługa>://<komputer>:<port>/<nazwa_bazy> as <nazwa_połączenia> as <nazwa_użytkownika> using <hasło_logowania>

Konkretny przykład wyjaśni to lepiej. Załóżmy, że używając protokołu tcp chcemy się połączyć z usługą postgresql udostępnianą przez port 5432 na komputerze dbs6. Połączenie do bazy danych rick wywołuje użytkownik neil posługujący się hasłem secret. W takim przypadku do naszego programu należałoby wstawić następujący wiersz:

exec sql connect to tcp:postgresql://dbs6:5432/rick as connect_2 user neil using

secret;

Jeżeli chcemy rozdzielić poszczególne elementy definiujące połączenie, to możemy posłużyć się tzw. zmiennymi systemowymi. Jak wiadomo, odwołania do tych zmiennych są poprzedzane dwukropkiem. Zajmiemy się nimi w dalszych częściach tego rozdziału, zaś tutaj będziemy je traktowali jako zwykłe zmienne języka C.

exec sql BEGIN DECLARE SECTION;

char connect_str[256];

char as_str[25];

char user_str[25];

char using_str[25];

exec sql END DECLARE SECTION;

strcpy(connect_str, "tcp:postgresql://localhost:5432/rick");

strcpy(as_str, "connect_2");

strcpy(user_str, "neil");

strcpy(using_str, "secret");

exec sql connect to :connect_str as :as_str user :user_str using :using_str ;

if (sqlca.sqlcode != 0) {

pg_print_debug(__FILE__, __LINE__, sqlca, "Connect failed");

return DVD_ERR_BAD_DATABASE;

}

Po zapoznaniu się z podstawami przejdźmy do szczegółów zastosowania ecpg.

Prawie zawsze trzeba dołączyć do kodu plik nagłówkowy udostępniający informacje o błędach i statusie bazy PostgreSQL. Ponieważ ten plik ma być przetwarzany przez preprocesor ecpg przed uruchomieniem kompilatora C, to zwykłe polecenie include nie wystarczy. Musimy użyć tu składni exec sql include. Ponieważ istnieje już plik nagłówkowy o nazwie sqlca, który prawie zawsze trzeba dołączyć, to program pgc zazwyczaj rozpoczyna się od następującego wiersza:

exec sql include sqlca;

Powoduje on dołączenie pliku sqlca.h z domyślnego katalogu /usr/include/pgsql (w konkretnej instalacji może on być zlokalizowany gdzie indziej). Ten ważny plik nagłówkowy zawiera deklarację struktury sqlca i zmiennej o tej samej nazwie, które umożliwiają odczyt wyników poleceń SQL. Struktura sqlca jest standardowo używana przy osadzaniu poleceń SQL w kodzie C, ale jej konkretne implementacje mogą się nieco różnić między sobą. W instalacji PostgreSQL omawianej w książce ma ona następującą postać:

struct sqlca

{

char sqlcaid[8];

long sqlabc;

long sqlcode;

struct

{

int sqlerrml;

char sqlerrmc[70];

} sqlerrm;

char sqlerrp[8];

long sqlerrd[6];

char sqlwarn[8];

char sqlext[8];

};

Faktyczna interpretacja zawartości sqlca może wydawać się nieco dziwna. Biblioteka ecpg rozpowszechniana z pakietem PostgreSQL nie zawiera tak wielu funkcji jak np. biblioteki komercyjne rozpowszechniane przez Oracle. Oznacza to, że niektórych elementów tej struktury nie będzie można użyć. Pomimo tego ograniczenia wszystkie ważniejsze funkcje są uwzględnione i można z nich korzystać bez jakichkolwiek problemów.

Podczas przetwarzania struktury sqlca najpierw należy sprawdzić sqlca.code. Jeżeli wartość tego elementu jest mniejsza od zera, oznacza to, że stało się coś naprawdę złego; jeżeli jest zerowa, to wszystko przebiegło pomyślnie. Wartość równa 100 oznacza zaś niemożność znalezienia danych, ale nie błąd.

Jeżeli polecenia INSERT, UPDATE lub SELECT zakończą się pomyślnie, to element sqlca.sqlerrd[2] będzie zawierał liczbę przetworzonych wierszy.

Jeżeli element sqlca.sqlwarn[0] ma wartość „W”, oznacza to wystąpienie mało znaczącego błędu i udane zazwyczaj pobranie danych, których nie udało się przyporządkować do zmiennej systemowej (którą omówimy w dalszych częściach rozdziału).

Jeżeli wystąpi błąd, to sqlca.sqlerrm.sqlerrmc zawiera napis będący treścią komunikatu o tym błędzie.

Komercyjne pakiety zawierają więcej pól w tej strukturze, np. informujących o „kosztach” połączenia, ale pola te nie są obecnie obsługiwane przez PostgreSQL. Ze względu na rzadko spotykane zastosowania tych pól nie będziemy tu odczuwać ich braku.

Podsumujmy więc dotychczasowe wyjaśnienia:

sqlca.sqlcode	Zawiera ujemną wartość dla błędów poważnych, zero dla udanego wywołania, 100 przy niemożności znalezienia danych.
sqlca.sqlerrm.sqlerrmc	Zawiera tekst komunikatu o błędzie.
sqlca.sqlerrd[2]	Zawiera liczbę przetworzonych wierszy.
sqlca.sqlwarn[0]	Zawiera „W”, gdy dane zostały pobrane, lecz nie udało się ich poprawnie przekazać do programu.

Wypróbujmy to wszystko, modyfikując kod upd1.pgc tak, aby dołączyć sqlca i jednocześnie celowo wprowadźmy błąd, używając błędnej nazwy tabeli:

#include <stdlib.h>

#include <stdio.h>

exec sql include sqlca;

main() {

exec sql connect to 'rick@gw1';

exec sql BEGIN WORK;

exec sql UPDATE XXchildren SET fname = 'Emma' WHERE age = 0;

printf("error code %d, message %s, rows %d, warning %c\n", sqlca.sqlcode,

sqlca.sqlerrm.sqlerrmc, sqlca.sqlerrd[2], sqlca.sqlwarn[0]);

exec sql COMMIT WORK;

exec sql disconnect all;

return EXIT_SUCCESS;

}

Plik ten nazwiemy upd2.pgc. Wyróżniono tu fragmenty nie występujące w poprzednim programie. Kompilacja odbywa się podobnie jak poprzednio:

$ ecpg -t -I/usr/include/pgsql upd2.pgc

$ gcc -g -o upd2 -I /usr/include/pgsql/ upd2.c -lecpg -lpg

Po uruchomieniu tego programu generowany jest błąd:

error code -400, message Postgres error: ERROR: xxchildren: Table does not exist.

line 10., rows 0, warning

Jest to trochę za mało informacji, ale spełniają swoje zadanie.

Po tych podstawowych informacjach zajmiemy się najważniejszym problemem: w jaki sposób uzyskać dostęp do danych, które zwracają osadzone polecenia SQL?

Odpowiedź jest bardzo prosta: wykorzystuje się do tego celu tzw. zmienne systemowe dostępne zarówno dla poleceń ograniczonych przez exec sql ... ;, jak i dla zwykłego kompilatora języka C.

W tym celu, zwykle na początku pliku, należy zadeklarować sekcję, która będzie przetwarzana zarówno przez preprocesor ecpg, jak i kompilator C. Deklaracja ta jest umieszczana między dwoma wierszami ograniczającymi:

exec sql begin declare section;

i

exec sql end declare section;

Załóżmy, że chcemy zadeklarować dwie zmienne child_name i child_age, które mają być dostępne dla osadzonych poleceń SQL i kodu w języku C w pozostałej części programu. Oto jak wygląda taka deklaracja:

exec sql begin declare section;

int child_age;

VARCHAR child_name[50];

exec sql end declare section;

Zwróćmy tu uwagę na dwie tajemnicze rzeczy: po pierwsze, na magiczną liczbę 50 oznaczającą długość napisu i po drugie, na to że VARCHAR nie jest typem danych używanym normalnie w języku C. Użyliśmy tu konkretnej wartości liczbowej, ponieważ preprocesor ecpg działa przed kompilatorem C i nie można skorzystać z polecenia #define lub deklaracji stałej. Typ VARCHAR został użyty z tego względu, że typ kolumny fname w SQL nie może być bezpośrednio odwzorowany na typ danych używany w języku C. Musimy trzymać się konwencji stosowanej w PostgreSQL, która zostanie przekształcona na prawidłową strukturę języka C przez preprocesor ecpg. Wynikiem działania takiej deklaracji umieszczonej w kodzie źródłowym jest utworzenie struktury o nazwie child_name zawierającej dwa elementy: tablicę char array o nazwie arr i liczbę całkowitą len służącą do przechowywania rozmiaru. Kompilator C otrzymuje więc następujący kod:

struct varchar_child_name {int len; char arr[50];} child_name;

Mamy więc dwie zmienne dostępne dla SQL i C. Przy pobieraniu danych z bazy i przekazywaniu ich do nazwanych zmiennych używamy pewnego rozszerzenia składni języka SQL, a mianowicie słowa kluczowego „into”, za którym następuje nazwa zmiennej poprzedzona dwukropkiem. Dzięki temu nie pomylą się zmienne systemowe z nazwami zwykłych zmiennych lub tablic. Musimy także pamiętać, że słowo „into” nie oznacza tego, co spotyka się w niektórych rozszerzeniach standardu SQL, czyli interaktywnego pobierania danych z jednej tabeli i przepisania ich do drugiej. Jeśli używamy osadzonych poleceń SQL, to słowo kluczowe „into” ma nieco inne znaczenie.

exec sql SELECT fname ;child_name FROM children WHERE age = :child_age;

Preprocesor ecpg przekształca taką instrukcję w kod języka C, który będzie kompilowany w normalny sposób. Pełny kod programu selp1.pgc ma teraz następującą postać:

#include <stdlib.h>

#include <stdio.h>

exec sql include sqlca;

exec sql begin declare section;

int child_age;

VARCHAR child_name[50];

exec sql end declare section;

main() {

exec sql connect to 'rick@gw1';

exec sql BEGIN WORK;

child_age = 14;

exec sql SELECT fname into :child_name FROM children WHERE age =

:child:age;

printf("error code %d, message %s, rows %d, warning %c\n", sqlca.sqlcode,

sqlca.sqlerrm.sqlerrmc, sqlca.sqlerrd[2], sqlca.sqlwarn[0];

if (sqlca.sqlcode == 0) {

printf("Child's name was %s\n", child_name.arr);

}

exec sql COMMIT WORK;

exec sql disconnect all;

return EXIT_SUCCESS;

}

Ważniejsze zmiany wprowadzone do kodu są tu wyróżnione. Zwróćmy uwagę na to, że aby mieć dostęp do pobranych danych, należy skorzystać z child_name.arr. Deklaracje VARCHAR są wymagane tylko wtedy, gdy chcemy mieć dane poza bazą. Jeżeli chcemy zapisać dane w bazie w polu typu VARCHAR, to wystarczy posłużyć się zwykłym napisem z języka C, kończącym się wartością NULL.

W powyższym programie istnieje potencjalne zagrożenie. Widzimy, że element child_name typu VARCHAR trzeba zadeklarować jako element o stałym rozmiarze nawet wtedy, gdy nie wiemy z góry, jaki będzie rozmiar zwracanych danych. Co się na przykład stanie, gdy jako rozmiar elementu child_name przyjmiemy 3, a pole zachowane w bazie będzie mieć rozmiar większy? W takim przypadku biblioteka ecpg zwróci jedynie trzy pierwsze znaki i ustawi znacznik ostrzegawczy. Sprawdźmy to w praktyce, wprowadzając do programu zmienioną deklarację VARCHAR child_name[3] i uruchamiając go:

error code 0, message, rows 1, warning W

Child's name was Jen

W takim przypadku można także spotkać się z pewnymi komplikacjami, które omówimy za chwilę.

Jak widać, w elemencie sqlca.sqlwarn[0] znalazł się znak ostrzegawczy „W”, a zwracana nazwa została obcięta. Dzieje się tak, ponieważ deklaracja child_name jest przekształcana na strukturę zawierającą tablicę znakową o trzech elementach, czyli nie ma już miejsca na zapamiętanie ogranicznika napisu. Na szczęście działa jeszcze wydruk, gdyż specjalnie użyliśmy w programie formatowania napisów za pomocą polecenia printf. Aby mieć pewność, że dane typu VARCHAR są pobrane do normalnego napisu używanego w języku C, należy sprawdzać zawartość i kopiować napis do oddzielnego miejsca, dodając jawnie ogranicznik NULL. Takie właściwości ma kolejna wersja naszego programu nosząca nazwę selp3.c:

#include <stdlib.h>

#include <stdio.h>

exec sql include sqlca;

exec sql begin declare section;

int child_age;

VARCHAR child_name[50];

exec sql end declare section;

main() {

exec sql connect to 'rick@gw1';

exec sql BEGIN WORK;

child_age = 14;

exec sql SELECT fname into :child_name FROM children WHERE age =

:child_age;

printf("error code %d, message %s, rows %d, warning %c\n", sqlca.sqlcode,

sqlca.sqlerrm.sqlerrmc, sqlca.sqlerrd[2], sqlca.sqlwarn[0];

if (sqlca.sqlcode == 0) {

child_name.arr[sizeof(child_name.arr) -1] = '\0';

printf("Child's name was %s\n, child_name.arr);

}

exec sql COMMIT WORK;

exec sql disconnect all;

return EXIT_SUCCESS;

}

Ponieważ umiemy już pobierać dane z bazy, spróbujemy teraz użyć kursorów łącznie z ecpg do określenia warunków polecenia SELECT i pobierania danych do zmiennych języka C. W odróżnieniu od przykładu podanego dla biblioteki libpq, podczas korzystania z ecpg konieczne jest otwieranie kursora przed użyciem za pomocą słowa kluczowego OPEN (tak przynajmniej było w chwili pisania tej książki). Przykład o nazwie selp4.pgc jest zauważalnie krótszy niż jego odpowiednik wykorzystujący bibliotekę libpq:

#include <stdlib.h>

#include <stdio.h>

exec sql include sqlca;

exec sql begin declare section;

int child_age;

VARCHAR child_name[50];

int req_age;

exec sql end declare section;

main() {

exec sql connect to 'rick@gw1';

exec sql BEGIN WORK;

req_age = 6;

exec sql DECLARE mycursor CURSOR FOR SELECT age, fname FROM children

WHERE age > :req_age;

exec sql OPEN mycursor;

exec sql FETCH NEXT IN mycursor into :child_age, :child_name;

if (sqlca.sqlcode <0)

printf("error code %d, message %s, rows %d, warning %c\n", sqlca.sqlcode,

sqlca.sqlerrm.sqlerrmc, sqlca.sqlerrd[2], sqlca.sqlwarn[0];

while (sqlca.sqlcode == 0) {

if (sqlca.sqlcode >= 0) {

child_name.arr[sizeof(child_name.arr) -1] = '\0';

printf("Child's name and age %s, %d\n", child_name.arr, child_age);

}

exec sql FETCH NEXT IN mycursor into :child_age, :child_name;

if (sqlca..sqlcode <0) printf("error code %d, message %s, rows %d, warning

%c\n", sqlca.sqlcode, sqlca.sqlerrm.sqlerrmc, sqlca.sqlerrfd[2], sqlca.sqlwarn[0];

}

exec sql CLOSE mycursor;

exec sql COMMIT WORK;

exec sql disconnect all;

return EXIT_SUCCESS;

}

Po uruchomieniu tego programu otrzymamy:

Child's name and age Andrew, 10

Child's name and age Jenny, 14

Child's name and age Alex, 11

Niektórzy mogą pomyśleć, że całe to zamieszanie z VARCHAR jest zupełnie bez sensu i znacznie łatwiej będzie użyć napisów o ustalonym rozmiarze, dbając o to, aby rozmiar ten nie był zbyt duży. Niestety, takie podejście stwarza kolejny problem, bowiem PostgreSQL nie może przechowywać znaku \0 w kolumnach typu CHAR. Zamiast tego całe pole jest dopełniane spacjami aż do końca, więc jeśli zachowaliśmy napis „Foo” w polu typu CHAR(10), to pobrawszy go stamtąd dostaniemy „Foo ” i końcowe spacje trzeba będzie usuwać samemu. Uzyskujemy jednak końcowy znak \0 podczas pobierania napisu, a więc jest to zgodne z konwencją stosowaną w języku C.

Przekonamy się teraz, jak można wykrywać wartości NULL, korzystając z ecpg. Podobnie jak w standardowych rozwiązaniach z osadzaniem SQL, tak i w tym przypadku jest to trochę bardziej skomplikowane zadanie niż wówczas, gdy korzysta się z biblioteki libpq. Pamiętając, że NULL oznacza „nieznany”, nie możemy użyć jakiegoś „magicznego” napisu do pokazania wartości NULL, ponieważ w rzeczywistości może on już tam występować.

Aby wykrywanie wartości NULL było możliwe, trzeba zadeklarować dodatkową zmienną (nazywaną często zmienną sygnalizacyjną) śledzącą zachowanie się zmiennej używanej przy pobieraniu danych. Ta dodatkowa zmienna sygnalizacyjna ma nadawaną wartość wówczas, gdy wartością pobranej danej jest NULL. Nazwa nowej zmiennej jest dowolna, chociaż bywa tworzona na podstawie nazwy zmiennej śledzonej, czyli np. ind_nazwa_rzeczywistej_zmiennej lub nazwa_rzeczywistej_zmiennej_ind. Jest ona zawsze typu integer, a jej wartość ujemna oznacza, że związana z nią zmienna ma wartość NULL.

Załóżmy na przykład, że w naszym poprzednim przykładzie chcemy wykrywać wartość NULL w polu age. Deklarujemy więc następującą zmienną dodatkową:

int ind_child_age;

Przy pobieraniu danych za pomocą polecenia FETCH i kursora używamy zarówno zmiennej właściwej, jak i zmiennej sygnalizacyjnej, „sprzęgając” je dwukropkiem:

exec sql FETCH NEXT IN mycursor into :child_age:ind_child_age, :child_name;

Następnie, jeśli ind_child_age nie ma wartości ujemnej, dowiadujemy się, że child_age zawiera poprawne dane — w przeciwnym wypadku dane nie są poprawne, ponieważ w bazie danych występowała wartość NULL. Nasz przykład w końcowej postaci powinien więc uwzględniać takie wykrywanie wartości NULL w bazie danych.

Zmodyfikujmy tabelę w bazie danych w taki sposób, aby zarówno w polu age, jak i w polu fname pojawiła się wartość NULL. Dane testowe będą wyglądały następująco:

SELECT * from children;

childno|fname|age

-------+------+---

1|Andrew| 10

2|Jenny | 14

3|Alex | 11

4|Adrian| 5

19| | 17

16|Emma | 0

18|TBD |

20|Gavin | 4

(8 rows)

Jak widać, w bazie występuje siedemnastolatek o nieznanym imieniu oraz nienarodzone dziecko, którego imię będzie dopiero wybrane, a wiek nie może być jeszcze określony.

Kolejną wersję programu nazwiemy selp5.pgc. Przy okazji użyjemy tu również alternatywnej postaci parametru połączenia.

#include <stdlib.h>

#include <stdio.h>

exec sql include sqlca;

exec sql begin declare section;

int child_age;

int ind_child_age;

VARCHAR child_name[50];

int ind_child_name;

exec sql end declare section;

main() {

exec sql connect to tcp:postgresql://localhost:5432/rick as rick user rick

using secretpassword;

exec sql BEGIN WORK;

exec sql DECLARE mycursor CURSOR FOR SELECT age, fname FROM children;

exec sql OPEN mycursor;

exec sql FETCH IN mycursor into :child_age:ind_child_age,

:child_name:ind_child_name,

if (sqlca.sqlcode < 0)

printf("error code %d, message %s, rows %d, warning %c\n", sqlca.sqlcode,

sqlca.sqlerrm.sqlerrmc, sqlca.sqlerrd[2], sqlca.sqlwarn[0]);

while (sqlca.sqlcode == 0) {

if (sqlca.sqlcode >= 0) {

if (ind_child_name >= 0) {

child_name.arr[sizeof(child_name.arr) -1] = '\0';

} else {

strcpy(child_name.arr, "Unknown");

}

if (ind_child_age >= 0) {

printf("Child's name and age %s, %d\n", child_name.arr, child_age);

} else {

printf("Child's name %s\n", child_name.arr);

}

}

exec sql FETCH NEXT IN mycursor into :child_age:ind_child_age,

:child_name:ind_child_name;

if (sqlca.sqlcode < 0)

printf("error code %d, message %s, rows %d, warning %c\n", sqlca.sqlcode,

sqlca.sqlerrm.sqlerrmc, sqlca.sqlerrd[2], sqlca.sqlwarn[0]);

} /* end of while loop */

exec sql CLOSE mycursor;

exec sql COMMIT WORK;

exec sql disconnect all;

return EXIT_SUCCESS;

}

Fragmenty kodu związane z detekcją wartości NULL są wyróżnione. Po uruchomieniu tego programu otrzymamy:

Child's name and age Andrew, 10

Child's name and age Jenny, 14

Child's name and age Alex, 11

Child's name and age Adrian, 4

Child's name and age Unknown, 17

Child's name and age Emma, 0

Child's name TBD

Child's name and age Gavin, 4

Widać już, że wartości NULL w bazie danych są wykrywane poprawnie.

Na tym kończymy zapoznawanie się z ecpg, czyli osadzonym preprocesorem poleceń SQL używanym w pakiecie PostgreSQL (ang. embedded SQL pre-processor for PostgreSQL).

Jaką metodę zastosować?

Ponieważ mamy dwie metody dostępu do bazy PostgreSQL z poziomu kodu języka C, to którą z nich należy wybrać? Jak zwykle, nie ma tu jednoznacznej poprawnej odpowiedzi. Należy użyć tej metody, która wydaje się najlepiej pasować do zagadnienia i z którą najlepiej się pracuje. Możemy doradzić tylko to, by nie mieszać dwóch metod w jednym projekcie. Po dokonaniu wyboru metody, należy ściśle jej przestrzegać.

Zalety biblioteki libpq są następujące:

• Proste wywołania funkcji, z którymi większość użytkowników jest już zaznajomiona.

• Jest całkiem dobrze udokumentowana.

Wady libpq są następujące:

• Wymaga długiego kodu.

• Trudno wyróżnić polecenia SQL wśród kodu języka C.

Zalety ecpg są następujące:

• Jest to standard osadzania poleceń SQL.

• Polecenia SQL są dobrze widoczne, ponieważ nie są ukryte w wywołaniach funkcji bibliotecznych.

Wady ecpg są następujące:

• Wykrywanie błędów może być utrudnione, ponieważ przed zadziałaniem kompilatora C plik jest przetwarzany przez preprocesor. Oznacza to, że trudniej jest śledzić błędy wykrywane przez kompilator C, zaś użycie debuggera gdb może dawać mylące wyniki ze względu na niewłaściwą numerację wierszy kodu. Preprocesor ecpg wstawia dyrektywy #line do tworzonego kodu w języku C i to zazwyczaj pomaga w wykrywaniu błędów, ponieważ komunikaty o błędach odnoszą się do pierwotnej numeracji wierszy kodu w pliku .pgc. Nie zawsze jednak tego oczekujemy i może to powodować pomyłki w gdb. Można sobie z tym poradzić, zezwalając, by preprocesor ecpg wygenerował plik .c, następnie używając polecenia grep -v '^#line' > _1.c, potem kopiując plik _1.c ponownie do pliku .c utworzonego przez ecpg i na końcu kompilując ten plik. Dzięki takiemu postępowaniu usuniemy dyrektywy włączające numerację wierszy i komunikaty o błędach pochodzące z kompilatora, natomiast polecenia gdb będą dotyczyć numerów wierszy w pliku .c, a nie w pliku .pgc. Takie kłopoty nie są specyfiką samego ecpg, bowiem występują one we wszystkich systemach z osadzonymi poleceniami SQL.

• Wielu użytkowników nie zna tak dobrze tej metody, zna natomiast standardową metodę korzystania z funkcji bibliotecznych.

• Trzeba z góry znać liczbę kolumn i typy danych pobieranych za pomocą poleceń SELECT.

Jeżeli mamy program w języku C, który zawiera niewielki dodatek poleceń SQL, to trudności powstające podczas wykrywania błędów w kodzie utworzonym przez preprocesor mogą decydować o odrzuceniu drugiej metody.

Aplikacja

Ponieważ poznaliśmy już podstawowe informacje o dostępie do bazy PostgreSQL z kodu języka C, nadszedł teraz czas na wykorzystanie tej wiedzy w programie obsługującym naszą bazę danych dla wypożyczalni płyt DVD.

Pierwszym i prawdopodobnie najważniejszy problemem do rozwiązania jest projekt tabel bazy danych. Potrzebna jest tabela przechowująca informacje o kliencie. Musimy także pomyśleć o faktycznych płytach DVD. Ważne jest, aby zdać sobie w tym momencie sprawę z faktu, że są dwa rodzaje informacji o płytach: informacja o filmie umieszczonym na płycie, np. że jest to film „Death and Maiden”, w którym występuje „Sigourney Weaver”, który ma reżysera, datę wydania itd., oraz informacja o faktycznej płycie DVD dostępnej w wypożyczalni. Film istnieje niezależnie od dostępności płyty w wypożyczalni, czyli w danym momencie może się w niej znajdować zero lub więcej płyt. Takie rozważania dowodzą, że informację o filmie i informację o płycie należy przechowywać w dwóch oddzielnych tabelach. Na pewno informacje te są ze sobą powiązane, ale nie są takie same.

Następna porcja informacji do przechowania dotyczy klientów rezerwujących tytuły filmów. Dodamy w tym celu tabelę o nazwie member_booking, która będzie zawierać identyfikator klienta, identyfikator tytułu oraz datę rezerwacji. Działa ona jako połączenie między klientem a zarezerwowanym przez niego filmem. Takie podejście pozwala również na rezerwację tego samego filmu przez więcej niż jednego klienta tego samego dnia, czyli jest to klasyczna relacja „wiele do wielu”. Oczywiście, nasza aplikacja musi kontrolować liczbę faktycznie dostępnych płyt!

Przechodząc do faktycznie wypożyczonych płyt możemy postąpić podobnie, czyli dodać tabelę łączącą płytę z klientem. Musimy tu pamiętać o oczywistej optymalizacji — pomiędzy klientem wypożyczającym płytę a płytą z wypożyczalni może nie wystąpić żadne połączenie albo może wystąpić połączenie „jeden do jednego”. Możemy zatem przechowywać informacje o wypożyczeniach bezpośrednio w tabeli z informacjami o płytach i jako identyfikator klienta wpisywać wartość NULL dla płyt nie wypożyczonych. Takie postępowanie jest nazywane denormalizacją i powinno być stosowane tylko wtedy, gdy mamy pewność, że struktura danych została właściwie przeanalizowana. Takie rozwiązanie stosujemy tutaj głównie po to, aby pokazać jedną z poprawnych metod optymalizacji i dodatkowo uprościć kod.

Potrzebujemy jeszcze trzech tabel pomocniczych: jednej na przechowywanie komunikatów o błędach, jednej na przechowywanie rodzaju filmu i jednej na przechowywanie klasyfikacji filmu. Zarówno rodzaj, jak i klasyfikacja filmu wiążą się bezpośrednio z jego tytułem i są jedynymi występującymi tu wartościami. Mamy więc przed sobą następny wybór. Możemy np. przechowywać informację o rodzaju filmu i klasyfikujący go tekst w tabeli z danymi opisującymi film, pozwalając aplikacji na pobieranie ich z tabel pomocniczych. Rozwiązaniem alternatywnym jest przechowywanie tylko identyfikatorów i użycie w bazie danych relacji odnoszącej się do faktycznej tabeli z przechowywanym tekstem.

Jeżeli tekst jest bardzo krótki i jesteśmy przekonani, że aplikacja będzie używać poprawnych danych, to prawdopodobnie łatwiej będzie przechowywać ten tekst w tabeli z danymi filmu (tytułu). Takie podejście upraszcza strukturę bazy i ułatwia pisanie poleceń SQL. Jeżeli jednak teksty są dłuższe i chcemy być pewni, że będą one poprawne, to w głównej tabeli powinniśmy użyć identyfikatorów tekstu, a sam tekst przechowywać w odrębnej tabeli. Obniża to wymagania odnośnie do pamięci, ponieważ każdy unikatowy napis jest przechowywany tylko w jednym miejscu. Dla celów pokazowych w naszej aplikacji będziemy przechowywać klasyfikację filmu bezpośrednio w tabeli głównej, zaś rodzaj filmu będzie przechowywany w oddzielnej tabeli. Dzięki temu można będzie porównywać obydwie metody.

Zalecamy, by w rzeczywistej aplikacji przechowywać tylko połączenia z rzeczywistymi tabelami zawierającymi dane. Ułatwia to znacznie utrzymanie danych w dobrym stanie, co w bazie danych jest sprawą najważniejszą. Doskonale zbudowana baza danych zawierająca niepoprawne dane jest tylko trochę lepsza niż brak jakiejkolwiek bazy.

Aby ułatwić zarządzanie tabelami w bazie danych, zapiszemy w oddzielnym pliku polecenia SQL wykorzystywane do tworzenia tych tabel. Dzięki temu możemy łatwo je modyfikować i odtwarzać w razie konieczności. Polecenia SQL przechowywane w pliku można uruchamiać, korzystając z polecenia \i file.sql interpretera psql. Oto kod SQL tworzący naszą bazę danych:

create table member (

member_id SERIAL,

member_no CHAR(6) NOT NULL,

title CHAR(4),

fname CHAR(26),

lname CHAR(26) NOT NULL,

house_flat_ref CHAR(26) NOT NULL,

address1 CHAR(51) NOT NULL,

address2 CHAR(51),

town CHAR(51) NOT NULL,

state CHAR(3),

phone CHAR(31),

zipcode CHAR(11) NOT NULL,

CONSTRAINT member_no_uniq UNIQUE(member_no)

);

create table title (

title_id SERIAL,

title_text CHAR(61) NOT NULL,

asin CHAR(11),

director CHAR(51),

genre_id INT,

classification CHAR(11),

actor1 CHAR(51),

actor2 CHAR(51),

release_date CHAR(9),

rental_cost CHAR(7)

);

create table disk (

disk_id SERIAL,

title_id INT NOT NULL,

member_id INT, /* wstawiany po wypożycz., inaczej NULL */

rented_date CHAR(9)

);

create table member_booking (

member_id INT NOT NULL,

title_id INT NOT NULL,

date_required CHAR(9) NOT NULL

);

create tables filmclass (

class_film CHAR(11)

);

create table genre (

genre_id INT NOT NULL,

genre_name CHAR(21),

CONSTRAINT genre_id_uniq UNIQUE(genre_id)

);

create table errtext (

err_code INT,

err_text CHAR(50)

);

Można tu zauważyć dodatkowe ograniczenia, dodane dla przykładu:

CONSTRAINT genre_id_uniq UNIQUE(genre_id)

Nie chcemy, by identyfikator rodzaju filmu genre_id był zmienną typu SERIAL, ponieważ po przeładowaniu danych musimy odtworzyć dokładnie taką samą jego wartość, co poprzednio — w przeciwnym wypadku informacja odnosząca się do tabeli title będzie błędna. Z drugiej strony, wymagana jest także unikatowość tych wartości. Właśnie dlatego dodaliśmy ograniczenie pozwalające na wskazywanie wartości genre_id, jeżeli taka sama wartość jeszcze w bazie nie istnieje.

Na poniższym rysunku pokazano strukturę bazy danych w postaci graficznej:

Nie mamy tu wystarczająco dużo miejsca na prezentację całego kodu, pokażemy więc tylko małe fragmenty ilustrujące proces powstawania aplikacji. Te fragmenty kodu tworzą najniższy poziom aplikacji i są wywoływane po ogólnym sprawdzeniu poprawności działania (np. po upewnieniu się, że jest połączenie z bazą danych lub że argumenty wskaźnikowe nie mają wartości NULL).

Jako przykład takiego kodu podajemy poniżej fragment, który pobiera strukturę opisującą szczegóły tytułu filmowego, wyszukuje odpowiedni identyfikator genre_id i dopisuje wiersz do tabeli title.

int pg_title_insert(dvd_title *title_ptr) {

exec sql BEGIN WORK;

strcpy(title_text, title_ptr->title_text);

strcpy(asin, title_ptr->asin);

strcpy(director, title_ptr->director);

sprintf(genre_name, "%s%c", title_ptr->genre, '%');

strcpy(classification, title_ptr->classification);

strcpy(actor1, title_ptr->actor1);

strcpy(actor2, title_ptr->actor2);

strcpy(release_date, title_ptr->release_date);

strcpy(rental_cost, title_ptr->rental_cost);

/* Wyszukanie genre_id */

exec sql SELECT genre_id INTO :genre_id:genre_id_ind FROM genre WHERE

genre_name LIKE :genre_name;

if ((sqlca.sqlcode < 0) || (sqlca.sqlcode == 100 || (genre_id_ind == 1)) {

pg_print_debug(__FILE__,__LINE__, sqlca, "unknown genre\n");

exec sql ROLLBACK WORK;

return DVD_ERR_BAD_GENRE;

}

exec sql INSERT INFO title(

title_text, asin,

director, genre_id, classification,

actor1, actor2, release_date,

rental_cost)

VALUES (

:title_text, :asin,

:director, :genre_id, :classification,

:actor1, :actor2, :release_date,

:rental_cost);

if (sqlca.sqlcode < 0) {

pg_print_debug(__FILE__, __LINE__, sqlca, "insert into title failed\n");

exec sql ROLLBACK WORK;

return DVD_ERR_BAD_TITLE_TABLE;

} else {

if (sqlca.sqlerrd[2] != 1) {

pg_print_debug(__FILE__, __LINE__, sqlca, "insert into title

failed\n");

exec sql ROLLBACK WORK;

return DVD_ERR_BAD_TITLE_TABLE;

}

}

exec sql SELECT MAX(title_id) INTO :title_id FROM title;

if (sqlca.sqlcode < 0) {

pg_print_debug(__FILE__, __LINE__, sqlca, "select max title

failed\n"0'

exec sql ROLLBACK WORK;

return DVD_ERR_BAD_TITLE_TABLE;

}

exec sql COMMIT WORK;

/* Aktualizacja struktury klienta za pomocą obecnie znanych danych */

title_ptr->title_id = title_id;

return DVD_SUCCESS;

} /* pg_title_insert */

Został tu wyróżniony kod wyszukujący odpowiedni identyfikator genre_id. Zwracamy uwagę na to, że sprawdza się nie tylko poprawność wykonania polecenia SQL (tzn. czy sqlca.sqlcode nie jest mniejsze od 0), ale także to, czy dane zostały zwrócone (tzn. czy sqlca.sqlcode nie było równe 100) oraz czy wartość pobieranego identyfikatora genre_id nie była równa NULL (tzn. czy wartość genre_id_ind nie była równa 1). Ukryte znaczenie takiego użycia genre_id polega na tym, że dopóki aplikacja nie spróbuje umieścić niepoprawnego napisu, dopóty nie ma się co martwić o faktyczne przechowywanie danych — jest to więc godny uwagi podział odpowiedzialności.

Poniżej zamieszczono kod pobierający dane o filmie. Tutaj również ukryto szczegóły przechowywania danych oraz pokazano sposób łączenia tabel (czyli jednoczesnego pobierania danych z więcej niż jednej tabeli).

int pg_title_get(int req_title_id, dvd_title *title_ptr) {

title_id = req_title_id;

exec sql BEGIN WORK;

exec sql SELECT

title_id, title_text, asin,

director, genre_name, classification,

actor1, actor2, release_date,

rental_cost

INTO

:title_id:ind_title_id, :title_text, :asin,

:director, :genre_name, :classification,

:actor1, :actor2, :release_date,

:rental_cost

FROM title, genre WHERE title.title_id = :title_id AND title.genre_id

= genre.genre_id;

if (sqlca.sqlcode < 0) {

pg_print_debug(__FILE__, __LINE__, sqlca, "title get failed\n");

exec sql ROLLBACK WORK;

return DVD_ERR_BAD_TITLE_TABLE;

}

if ((sqlca.sqlcode == 100) || (ind_title_id != 0)) {

pg_print_debug(__FILE__, __LINE__, sqlca, "title get failed - no

entry\n");

exec sql ROLLBACK WORK;

return DVD_ERR_NOT_FOUND;

}

title_ptr->title_id = title_id;

strcpy(title_ptr->title_text, title_text);

strcpy(title_ptr->asin, asin);

strcp(title_ptr->director, director);

strcp(title_ptr->genre, genre_name);

strcp(title_ptr->classification, classification);

strcp(title_ptr->actor1, actor1);

strcp(title_ptr->actor2, actor2);

strcp(title_ptr->release_date, release_date);

strcp(title_ptr->rental_cost, rental_cost);

exec sql COMMIT WORK;

return DVD_SUCCESS;

} /* pg_title_get */

Kolejny interesujący fragment kodu dotyczący filmu służy do wyszukiwania. API umożliwia wyszukiwanie filmu przez klienta na podstawie tytułu, a także nazwiska aktora. Być może trzeba będzie zapisać to jako oddzielne funkcje, ale w SQL bardzo łatwo można to uczynić za pomocą jednego polecenia. Trzeba jedynie pamiętać, że „%” w SQL oznacza dopasowanie do napisu.

int pg_title_search(char *title_to_find, char *name_to_find, int *result_ids[],

int *count) {

int result_size = 0;

int *results = NULL;

if (title_to_find == NULL) strcpy(title_text, "%");

else sprintf(title_text, "%c%s%c", '%', title_to_find, '%');

if (name_to_find == NULL) strcpy(actor1, "%");

else sprintf(actor1, "%c%s%c", '%', name_to_find, '%');

exec sql BEGIN WORK;

exec sql DECLARE mycursor CURSOR FOR SELECT title_id from title WHERE

(title_text LIKE :title_text) AND ((actor1 LIKE :actor1) OR

(actor2 LIKE:actor1)) ORDER by title_text, actor1, actor2;

exec sql OPEN mycursor;

if (sqlca.sqlcode < 0) {

pg_print_debug(__FILE__, __LINE__, sqlca, "mycursor");

exec sql ROLLBACK WORK;

return DVD_ERR_BAD_TABLE;

}

exec sql FETCH NEXT in mycursor INTO :title_id;

while (sqlca.sqlcode == 0) {

result_size++;

results = (int *)realloc(results, sizeof(int) * result_size);

if (results == NULL) { /* Poważny błąd, nie próbujemy pobierać */

exec sql ROLLBACK WORK;

return DVD_ERR_NO_MEMORY;

}

results[result_size - 1] = title_id;

exec sql FETCH NEXT in mycursor INTO :title_id;

} /* while */

if (sqlca.sqlcode < 0) {

pg_print_debug(__FILE__, __LINE__, sqlca, "mycursor");

exec sql ROLLBACK WORK;

return DVD_ERR_BAD_TABLE;

}

exec sql COMMIT WORK;

*result_ids = results;

*count = result_size;

return DVD_SUCCESS;

} /* pg_title_search */

Wynik porządkujemy według pól title i actor1, które najpewniej będą poprawne.

Pozostała części kodu aplikacji, podobnie jak i przydatne fragmenty z tej książki, są dostępne na serwerze ftp wydawnictwa Helion (ftp://ftp.helion.pl/przyklady/zaprli.zip).

Podsumowanie

W tym rozdziale omówiliśmy dwie metody dostępu do bazy danych PostgreSQL z poziomu kodu w języku C. Najpierw opisywaliśmy metodę wykorzystującą konwencjonalne wywołania funkcji bibliotecznych, a następnie pokazaliśmy sposób osadzania poleceń SQL bezpośrednio w kodzie C. Porównaliśmy także obydwie metody, pokazując, że każda z nich ma zalety i wady. Na zakończenie przedstawiliśmy fragmenty kodu naszej przykładowej aplikacji obsługującej wypożyczalnię płyt DVD, która korzysta z dostępu do danych przechowywanych w bazie PostgreSQL.

2 Część I ♦ Podstawy obsługi systemu WhizBang (Nagłówek strony)

2 H:\Książki\!Wit\Zaawansowane programowanie w systemie Linux\6 po jezykowej\R-04-06.doc

---