Notice: Undefined index: mode in /home/misc/mst/public_html/common.php on line 63 Notice: Undefined index: mode in /home/misc/mst/public_html/common.php on line 65 Notice: Undefined index: mode in /home/misc/mst/public_html/common.php on line 67 Notice: Undefined index: mode in /home/misc/mst/public_html/common.php on line 69 Notice: Undefined variable: base in /home/misc/mst/public_html/lecture.php on line 36 Bazy danych – 8. Programowanie – MIM UW

Zagadnienia

8. Programowanie

Zajmiemy się teraz programami używającymi baz danych. Mogą one znajdować się w dwóch miejscach:

  • na serwerze bazy danych jako procedury składowane (składniowo mogą to być funkcje);

  • na maszynie wykonującej programy aplikacyjne, zwanej potocznie klientem bazy danych, choć w rzeczywistości może to być serwer aplikacji (np. serwer WWW), obsługujący wielu klientów równocześnie.

Zaczniemy od procedur składowanych. Są one przechowywane w bazie danych na serwerze i tam też są wykonywane. Aby użyć takiej funkcji, należy ją wywołać bezpośrednio z polecenia SQL lub pośrednio przez umieszczenie jej wywołania w wyzwalaczu.

8.1. Funkcje

Funkcje definiuje się w Postgresie używając konstrukcji

CREATE FUNCTION nazwa(parametr typ, ...)
RETURNS typ-wyniku AS $$
  treść-funkcji
$$ LANGUAGE nazwa-języka;
W starszych wersjach Postgresa treść funkcji otaczało się apostrofami, było to jednak niewygodne, bo wymagało dublowania wszystkich apostrofów wewnątrz treści.

Najłatwiej zdefiniować funkcje w języku SQL. Treść takiej funkcji to ciąg poleceń SQL lub pojedyncze polecenie, np.

CREATE FUNCTION dodaj7(i int4) RETURNS int4 AS $$
  SELECT i + 7;
$$ LANGUAGE sql;

Ponieważ w Postgresie polecenia SELECT można używać do obliczania wyrażeń nie związanych z bazą danych

bd> SELECT 2 + 5 AS siedem;
siedem
------
7
więc najprostsze funkcje można łatwo testować, na przykład pisząc:
bd> SELECT dodaj7(4) AS wynik;
wynik
-----
11

Funkcje napisane w SQL zwracają jako wartość wynik ostatniego polecenia w treści, musi to być SELECT. Jeśli wynik nie ma być zbiorem (co wynika z typu funkcji), będzie to pierwszy wiersz z tego zapytania (warto więc zadbać o ewentualne ORDER BY ;-).

Jeśli wynik zapytania był pusty, to jest zwracane NULL.

Dla funkcji SQL, która z założenia nie zwraca nic rozsądnego typem wyniku powinno być void, nie może ona wtedy kończyć się zapytaniem SELECT

CREATE FUNCTION oczyść () RETURNS void AS $$
  DELETE FROM Zwierz
  WHERE waga <= 0;
$$ LANGUAGE SQL;
SELECT oczyść();

8.2. PL/pgSQL

Normalnie jednak procedury bazy danych definiuje się nie w SQL, lecz w językach proceduralnych. Takim językiem dla serwera bazy danych w Postgresie jest najczęściej PL/pgSQL. Nazwa jest dość dziwna, ale pochodzi od języka PL/SQL, używanego przez DBMS Oracle.

Aby móc pisać procedury składowane w jakimś języku programowania, musi być on zainstalowany w bazie danych na serwerze. Nie dotyczy to języków SQL ani C, bo ich obsługa jest wbudowana w serwer.

Dla innych standardowych języków zawartych w dystrybucji Postgresa istnieje skrypt powłoki createlang, wykonujący instalację wybranego języka. Tak więc aby zainstalować PL/pgSQL w bazie danych template1 wystarczy napisać (w powłoce na serwerze)

createlang plpgsql template1
Można zamiast tego użyć polecenia CREATE LANGUAGE, ale jest to bardziej kłopotliwe (choć konieczne, jeśli nie mamy bezpośredniego dostępu do komputera z serwerem).

Do usuwania języka proceduralnego służy polecenia DROP LANGUAGE albo skrypt droplang.

Język programowania PL/pgSQL (Procedural Language/postgreSQL) rozszerza SQL o typowe konstrukcje spotykane w proceduralnych jezykach imperatywnych. Podstawową jednostką w PL/SQL jest blok, programy buduje się z zagnieżdżonych bloków.

W blokach wolno używać instukcji SQL służących do manipulowania danymi (np. SELECT, INSERT, UPDATE, DELETE) oraz instrukcji sterowania transakcjami. Poza tym w blokach można używać typowych instrukcji takich jak przypisanie, instrukcja warunkowa, pętle, wywołania procedur.

Dla instrukcji SELECT jest używana postać rozszerzona, pozwalająca umieszczać wyszukane wartości w zmiennych PL/pgSQL. Instrukcje definiowania danych, takie jak CREATE, DROP czy ALTER, nie są dozwolone.

Bloki PL/pgSQL umieszcza się w treści funkcji, które można uruchamiać zwykłą instrukcją SELECT z poziomu programu psql.

8.2.1. Bloki

Blok jest podstawową konstrukcją języka PL/pgSQL. Składnia bloku wygląda następująco:

[DECLARE
  deklaracje zmiennych, stałych i procedur lokalnych>]
BEGIN
  instrukcje
END;
(nawiasy kwadratowe oznaczają część opcjonalną, nie są elementem składni).

Przykład bloku poniżej

DECLARE
  a NUMERIC(5);
BEGIN
  SELECT COUNT(*) INTO a
  FROM EMP
  WHERE ENAME LIKE 'A%';
  IF a > 0 THEN
    INSERT INTO TEMP VALUES(a);
  END IF;
  RAISE NOTICE 'OK';
END;

8.3. Wyzwalacze

Wyzwalacze są to procedury wykonywane automatycznie przy zajściu pewnego zdarzenia, np. wstawieniu nowego wiersza do określonej tabeli. Pierwotnie wyzwalacze nie miały służyć do zapewnienia legalności stanów bazy (od tego są warunki integralności i asercje), lecz do zapewnienia legalności przejść między stanami.

Ponieważ jednak większość DBMS nie implementuje asercji (nie jest to zresztą łatwe), wyzwalaczy najczęściej używa się do realizacji złożonych ograniczeń, których nie mozna wyrazić w poleceniach takich jak CREATE TABLE.

Opis wyzwalacza obejmuje trzy składowe

  • Zdarzenie: modyfikacja pewnej tabeli, np. ,,wstawienie to tabeli Gatunki”.

  • Warunek: wyrażenie Booleowskie w SQL.

  • Akcje: polecenia do wykonania, najczęsciej zapisywane w SQL lub PL/SQL.

Składnia wyzwalacza napisanego w SQL (mniej więcej zgodna ze standardem) jest następująca:

CREATE [OR REPLACE] TRIGGER nazwa
{BEFORE | AFTER} INSERT OR DELETE OR UPDATE
ON tabela
FOR EACH {ROW | STATEMENT}
EXECUTE
polecenie;

Dla wyzwalaczy określa się, czy ich akcje mają być wykonane przed czy po właściwej operacji (BEFORE lub AFTER). Ponadto dla wyzwalacza określony jest jeden z dwóch poziomów: wiersza lub zdania.

Wyzwalacz poziomu zdania jest odpalany tylko raz dla całego polecenia SQL, natomiast wyzwalacz poziomu wiersza jest odpalany niezależnie dla każdego modyfikowanego wiersza.

Spróbujmy napisać wyzwalacz, który przy usunięciu wiersza z tabeli Gatunki w odpowiadających mu wierszach w tabeli Zwierz ustawi NULL w kolumnie gatunek.

Oczywiście to samo można osiągnąc łatwiej używając więzów klucza zewnętrznego, ale zobaczymy jak można to zrobić wyzwalaczem.

CREATE TRIGGER DelGat
AFTER DELETE ON Gatunki
FOR EACH ROW EXECUTE
UPDATE Zwierz
SET gatunek = NULL
WHERE gatunek = OLD.gatunek;

A teraz inny przykład. Załóżmy, że dla każdego gatunku w tabeli Gatunki chcemy w dodatkowej osobnej kolumnie przechowywać liczbę zwierzaków tego gatunku. Zaczniemy od dodania odpowiedniej kolumny

ALTER Gatunki
ADD ile INTEGER DEFAULT 0 CHECK (ile > 0);

Teraz pora na wyzwalacze

CREATE TRIGGER InsZwierz
AFTER INSERT ON Zwierz
FOR EACH ROW EXECUTE
UPDATE Gatunki
SET ile = ile + 1
WHERE gatunek = NEW.gatunek;
CREATE TRIGGER DelZwierz
AFTER DELETE ON Zwierz
FOR EACH ROW EXECUTE
UPDATE Gatunki
SET ile = ile - 1
WHERE gatunek = OLD.gatunek;

8.3.1. Wyzwalacze w Postgresie

Wyzwalacze w Postgresie definiuje się w PL/pgSQL, używając uprzednio zdefiniowanych bezargumentowych funkcji zwracających specjalny typ TRIGGER.

Funkcja połączona z wyzwalaczem otrzymuje dane za pośrednictwem struktury TriggerData, a nie przez zwykłe parametry funkcyjne, dlatego procedur tych nie należy wywoływać bezpośrednio.

Sa one wywoływane niejawnie przez wyzwalacz, ilekroć wystąpi zdarzenie z nim związane. Zdarzeniem może być próba wykonania jednej z operacji SQL INSERT, DELETE lub UPDATE.

Deklaracja wyzwalacza w PL/pgSQL ma następującą składnię:

CREATE TRIGGER nazwa
  BEFORE | AFTER INSERT | DELETE | UPDATE [OR ...]
  ON tabela
  [FOR EACH ROW | STATEMENT]
  EXECUTE PROCEDURE nazwa-funkcji( [argumenty]);

Funkcja wyzwalacza musi zostać zdefiniowana przed utworzeniem wyzwalacza. Nazwa wyzwalacza musi być unikalna dla danej tabeli i przydaje się przy usuwaniu wyzwalacza. Poza tym wyzwalacze tego samego typu uruchamia się w kolejności alfabetycznej według ich nazw.

Modyfikatory BEFORE i AFTER określają, czy funkcja wyzwalacza ma być wywoływana przed czy po właściwej akcji.

Można określić do trzech rodzajów zdarzeń (INSERT, DELETE lub UPDATE) uruchamiających wyzwalacz używając spójnika OR. Przykłady:

... INSERT ON R ...
... INSERT OR DELETE OR UPDATE ON R ...

Opcja FOR EACH ROW określa, że wyzwalacz jest poziomu wiersza, tzn. będzie odpalany osobno dla każdego zmienianego wiersza tabeli. Domyślnie wyzwalacz jest poziomu całej instrukcji.

Argumenty wywołania w definicji wyzwalacza są rzadko używane i powinny być literałami. Dzięki nim mozna tej samej funkcji używać w różnych wyzwalaczach. Najprostszy przykład to funkcja wpisująca informacje do dodatkowej tabeli zawierającej dziennik modyfikacji, gdzie parametrem mogłaby być nazwa modyfikowanej tabeli.

Usuwając wyzwalacz trzeba oprócz jego nazwy podać nazwę tabeli

DROP TRIGGER nazwa ON tabela;

W treści funkcji związanej z wyzwalaczem są dostępne dwie zmienne rekordowe NEW i OLD, odnoszące się do nowej i starej zawartości wiersza. Procedura wyzwalacza dla wstawiania i modyfikacji powinna zwracać odpowiedni rekord, zwykle jest to po prostu NEW.

Można jednak zwracać NULL, co w wyzwalaczach typu BEFORE oznacza, że wykonanie wyzwalacza nie powiodło się i właściwa operacja powinna zostać zignorowana.

Jeśli w funkcji wyzwalacza używa się poleceń SQL, mogą one spowodować uruchomienie innych wyzwalaczy, nazywa się to kaskadowaniem wyzwalaczy. Nie istnieje żadne ograniczenie na liczbę poziomów wywołań kaskadowych, w szczególności możliwe są wywołania rekurencyjne.

Za unikanie nieskończonej rekursji jest odpowiedzialny programista!

Czas na jakiś przykład. Załóżmy, że mamy tabelę

CREATE TABLE Gatunki (
  nazwa VARCHAR(30) PRIMARY KEY,
  kontynent VARCHAR(11),
  chroniony BOOLEAN,
  przysmak VARCHAR(15)
);

Utworzymy w Postgresie wyzwalacz dbający o to, aby nazwa kontynentu rozpoczynała się dużą literą:

CREATE FUNCTION normkont () RETURNS TRIGGER AS $$
BEGIN
  IF NEW.kontynent IS NOT NULL THEN
    NEW.kontynent := lower(NEW.kontynent);
    NEW.kontynent := initcap(NEW.kontynent);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';
CREATE TRIGGER gatwyzw1
  BEFORE INSERT OR UPDATE ON Gatunki
  FOR EACH ROW
  EXECUTE PROCEDURE normkont();

Wykonanie polecenia CREATE TRIGGER jedynie utworzyło wyzwalacz nie wykonując go. Aby uruchomić wyzwalacz (np. w celu przetestowania go) należy spowodować zajście odpowiedniego zdarzenia, w tym przypadku powinno to być wstawienie lub modyfikacja wiersza dla tabeli Gatunki.

8.4. Programowanie aplikacji

Dostęp użytkownika do baz danych odbywa się zwykle przez programy, uruchamiane na stacji roboczej użytkownika (nazywanej często klientem). Programy takie można pisać w dowolnym języku programowania, musi on jednak być wyposażony w interfejs programisty dla SQL (tzw. API — Application Programmer Interface). Interfejs taki może być specyficzny dla danego systemu DBMS lub uniwersalny (np. ODBC, JDBC).

8.4.1. Interfejs dla języka C

Autorzy systemów DBMS prawie zawsze dostarczają interfejs dla języka C. Obejrzymy zatem najpierw podstawowy interfejs systemu PostgreSQL dla języka C, zawarty w bibliotece libpq.

Po pierwsze w programie należy dołączyć odpowiedni plik nagłówkowy:

#include "libpq-fe.h"

Korzystanie w programie z bazy danych wymaga nawiązania połączenia:

PGConn *polaczenie;
polaczenie =
  PQconnectdb("dbname=bd,host=rainbow,user=ja");
if (PQstatus(polaczenie) == CONNECTION_BAD) 
  fprintf(stderr, "Brak polaczenia\backslashn");
  PQfinish(polaczenie);
  exit(1);

Załóżmy, że połączenie powiodło się i w bazie danych znajduje się tabela Gatunki, zawierająca m.in. kolumny gatunek i kontynent. Chcemy wyszukać z tej tabeli, z jakiego kontynentu pochodzi gatunek o podanej nazwie (np. wczytanej z klawiatury).

PGresult *wynik;
wynik = PQexec(polaczenie,
               "SELECT kontynent FROM Gatunki "
               "WHERE gatunek = 'szop pracz'");
if (PQresultStatus(wynik) == PGRES_TUPLES_OK &&
    PQntuples(wynik) == 1)
  printf("Szop pracz pochodzi z %s\backslashn",
         PQgetvalue(wynik, 0, 0));
else
  fprintf(stderr, "Brak odpowiedzi\backslashn");
PQfinish(polaczenie);

8.4.2. Zanurzony (embedded) SQL

Embedded SQL jest to technika bezpośredniego wpisywania poleceń SQL wśród instrukcji języka zanurzającego, w tym przypadku języka C. W PostgreSQL służy do tego narzędzie o nazwie ECPG.

Program w C z wbudowanymi poleceniami SQL jest najpierw przetwarzany preprocesorem ecpg na program w języku C. Preprocesor rozpoznaje polecenia SQL zawarte w programie i zastępuje je wywołaniami funkcji z odpowiednie biblioteki CLI dla SQL.

Otrzymany program przetwarza się normalnym kompilatorem C na program wykonywalny.

ecpg -I/usr/include/ecpg test1.pgc
cc -I/usr/include/ecpg -o test1 test1.c -L/usr/local/lib -lecpg

Wszystkie polecenia SQL w programach muszą być poprzedzane frazą EXEC SQL i kończyć się średnikiem (,,;”). Można je umieszczać gdziekolwiek w programie w C pilnując jedynie, aby deklaracje poprzedzały polecenia wykonywalne.

Przykład:

  int main ()
  
    EXEC SQL BEGIN DECLARE SECTION;
    int w;
    EXEC SQL END DECLARE SECTION;
    ...
    EXEC SQL SELECT wiek INTO :w
             FROM Zwierz
             WHERE imie='Kropka';
    ...
    printf("Kropka waży %d kilo\backslashn", w);
    ...
  

8.5. Laboratorium: poprawność bazy danych

Baza danych zawiera tabele:

  • Komputer(producent, model, typ),

  • PC(model, szybkość, ram, dysk, cdrom, cena),

  • Laptop(model, szybkość, ram, dysk, ekran, cena).

Zdefiniuj w SQL następujące ograniczenia:

Ćwiczenie 8.1

Komputery PC o szybkości mniejszej niż 150 MHz nie mogą być sprzedawane drożej niż za 2500 złotych lub muszą mieć co najmniej 64 MB pamięci RAM.

Rozwiązanie: 
ALTER TABLE PC ADD CONSTRAINT polcen
CHECK (szybkość >= 150 OR cena <= 2500 OR ram >= 64);

Uwaga: jeśli jakieś już istniejące wiersze nie będą spełniały tego warunku, to polecenie zakończy się błędem. Należy je najpierw skorygować, np. poleceniem UPDATE.

Ćwiczenie 8.2

Laptopy o ekranie mniejszym niż 11 cali, które nie mają dysku co najmniej 1GB, są sprzedawane poniżej 3600 złotych.

Rozwiązanie: 
ALTER TABLE Laptop ADD CONSTRAINT polcen1
CHECK (ekran >= 11 OR dysk >= 1 OR cena < 3600);

Zdefiniuj w PL/SQL następujące ograniczenie:

Ćwiczenie 8.3

Żaden laptop nie może być sprzedawany taniej niż PC o tej samej lub mniejszej szybkości i pamięci RAM.

Rozwiązanie: 
CREATE FUNCTION pclaptopfun () RETURNS TRIGGER AS $$
DECLARE
  gorna_cena INTEGER;
BEGIN
  IF NEW.ram IS NULL OR NEW.szybkosc IS NULL OR NEW.cena IS NULL
    RETURN NEW;
  ELSE
    SELECT MAX(cena) INTO gorna_cena
    FROM PC
    WHERE szybkosc <= NEW.szybkosc AND ram <= NEW.ram;
    IF gorna_cena > NEW.cena
      RAISE EXCEPTION 'Za mała cena';
      RETURN NULL;
    ELSE
      RETURN NEW;
    END IF;
  END IF;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER pclaptop

Treść automatycznie generowana z plików źródłowych LaTeXa za pomocą oprogramowania wykorzystującego LaTeXML.

Projekt współfinansowany przez Unię Europejską w ramach Europejskiego Funduszu Społecznego.

Projekt współfinansowany przez Ministerstwo Nauki i Szkolnictwa Wyższego i przez Uniwersytet Warszawski.