Wykład 4

Programowanie aplikacji baz danych po stronie serwera

Streszczenie

W tym wykładzie omówimy deklaratywne więzy spójności stanowiące jeden z elementów aplikacji bazy danych po stronie serwera.

Ponadto dla pełności obrazu tworzenia aplikacji w Oracle podamy informacje o interakcyjnym systemie poleceń SQL*Plus – nieodzownym przy tworzeniu bazy danych Oracle i testowaniu aplikacji.
 

4.1 Aplikacja po stronie serwera bazy danych

Tworząc aplikację należy podjąć decyzję, jaka jej część ma się znajdować na serwerze bazy danych, a jaka na komputerze użytkownika, czyli po stronie klienta. Nie ma wątpliwości, że dane oraz mechanizm wykonywania instrukcji SQL, powinny się znajdować po stronie serwera, natomiast „przyjazny” interfejs użytkownika – po stronie klienta. Natomiast jeśli chodzi o przetwarzanie danych, opinie są podzielone. Z punktu widzenia „interesu” bazy danych lepiej, żeby odbywało się ono na serwerze, który jest w stanie centralnie sprawować kontrolę nad spójnością danych. Ze względu na możliwość precyzyjniejszej diagnostyki, obsługa błędów dla przetwarzania danych może być lepiej zrobiona na stacji klienckiej.
 

4.2 Deklaratywne więzy spójności

Więzy spójności mogą być sprawdzane zarówno po stronie serwera, jak i po stronie klienta. Bardziej naturalnym miejscem sprawdzania jest serwer, który obsługuje wszystkie możliwe aplikacje utworzone dla bazy danych. Lepiej jest mieć jeden mechanizm gwarantujący spełnienie więzów spójności niż wiele, potencjalnie niespójnych.

Są dwie podstawowe metody określania więzów spójności na serwerze: albo za pomocą mechanizmu deklaratywnych więzów spójności – w definicjach tabel (instrukcje CREATE TABLE i ALTER TABLE) albo za pomocą mechanizmu wyzwalaczy bazy danych, o których będzie mowa w wykładzie 6.

O więzach spójności wspominaliśmy już w wykładzie 1. Tam były one przedstawiane jako części instrukcji CREATE TABLE i ALTER TABLE, teraz będą omawiane jako część oprogramowania tworzącego aplikację, które umieszczamy po stronie serwera.

Zadaniem więzów spójności jest zagwarantowanie tego, aby dane w bazie danych wiernie odzwierciedlały świat rzeczywisty, dla którego baza danych została zaprojektowana. System bazy danych ma zapewnić, aby więzy spójności pozostawały prawdziwe przy wszystkich operacjach, jakie mogą zostać wykonane na bazie danych w postaci transakcji użytkowników, wyzwalaczy bazy danych, ładowania lub importu danych do bazy danych.

Są dwa typy deklaratywnych więzów spójności:

1. więzy spójności encji,
2. więzy spójności referencyjnej.

Więzy spójności encji ograniczają możliwe wartości, jakie mogą się pojawić w wierszu tabeli. Oto te więzy:

1. Więzy klucza głównego PRIMARY KEY – wartości w określonych kolumnach jednoznacznie identyfikują wiersz tabeli. W kolumnach klucza głównego nie jest dozwolona pseudo-wartość NULL . Automatycznie jest zakładany indeks na kolumnach tworzących klucz główny. Może być określony tylko jeden klucz główny dla jednej tabeli.

2. Więzy klucza jednoznacznego UNIQUE – wartości w określonych kolumnach jednoznacznie identyfikują wiersz tabeli. W kolumnach klucza jednoznacznego jest dozwolona pseudo-wartość NULL . Automatycznie jest zakładany indeks na kolumnach tworzących klucz jednoznaczny. Może być określony więcej niż jeden klucz jednoznaczny dla jednej tabeli.

3. Więzy NOT NULL – w kolumnie nie jest dozwolona pseudo-wartość NULL.

4. Więzy CHECK – warunek, który ma być prawdziwy dla wszystkich wierszy  w tabeli. Nie może zawierać podzapytania ani funkcji zmiennych w czasie, jak Sysdate lub User. Może zawierać nazwy jednej lub więcej kolumn.

Przy definiowaniu klucza obcego nie można się odwoływać ani do synonimu tabeli, ani do tabeli znajdującej się w innej (odległej) bazie danych. Samo jego zdefiniowanie nie powoduje automatycznie utworzenia indeksu, jak w przypadku klucza głównego i jednoznacznego. Ze względu na znaczenie klucza obcego przy złączaniu tabel projektant bazy danych powinien sam zaplanować założenie indeksu na kluczu obcym przy użyciu instrukcji CREATE INDEX.

Deklaratywne więzy spójności mogą być definiowane na dwa sposoby: albo razem z definicją kolumny w-linii, albo poza definicją kolumny poza-linią.

Oto składnia definicji w-linii:
 

[NOT] NULL {UNIQUE|PRIMARY KEY} CHECK (warunek) REFERENCES tabela[(kolumna)][ON DELETE CASCADE] REFERENCES tabela[(kolumna)][ON DELETE SET NULL]

Składnia więzów definiowanych poza-linią jest rozszerzeniem składni definicji w-linii:
 

{UNIQUE|PRIMARY KEY} (kolumna,…) CHECK (warunek) FOREIGN KEY (kolumna,…) REFERENCES tabela(kolumna,…)[ON DELETE [CASCADE|SET NULL]]

W każdym z powyższych przypadków można poprzedzić więzy napisem:
 

CONSTRAINT nazwa_więzów

nadając w ten sposób im nazwę, za pomocą której można na nich operować, np. włączać je, wyłączać lub usuwać.

Klucz główny złożony z jednej kolumny można utworzyć zarówno przy użyciu więzów definiowanych w-linii, jak i więzów definiowanych poza-linią. Natomiast klucz główny złożony z więcej niż jednej kolumny może być utworzony tylko przy użyciu więzów definiowanych poza-linią. Oto przykład:
 

CREATE TABLE Pożyczki(     Num_kon NUMBER(6,0),     Num_poż NUMBER(6,0),     Typ_poż VARCHAR2(8) CONSTRAINT Typ_poż_ck        CHECK (Typ_pożycz IN ('AUTO', 'DOM', 'OSOBISTE')),     Wielk NUMBER(8,0) CONSTRAINT Wielk_nn NOT NULL,     Data_poż DATE DEFAULT Sysdate,     Akcept_przez VARCHAR2(25)        CONSTRAINT Akc_ko REFERENCES Kierownicy(Nazwisko_kier),     CONSTRAINT Poż_kg PRIMARY KEY(Num_kon, Num_poż),     CONSTRAINT Konto_ko FOREIGN KEY(Num_kon) REFERENCES Klienci(Num_kon) );

W powyższym przykładzie została użyta konstrukcja określania wartości domyślnej dla kolumny

DEFAULT Sysdate

ustalającą datę pożyczki na dzień dzisiejszy, to jest dzień wprowadzania informacji o pożyczce do bazy danych. W specyfikacji wartości domyślnej mogą występować stałe i funkcje SQL także User, Sysdate. Nie mogą za to występować ani nazwy kolumn, ani funkcje PL/SQL, ani sekwencje.
 

CREATE TABLE Pracownicy(    Id_prac NUMBER(7,0) PRIMARY KEY,    Pesel NUMBER(11,0) UNIQUE NOT NULL,    Imię VARCHAR2(25) NOT NULL,    Nazwisko VARCHAR2(25) NOT NULL,    Szef NUMBER(7,0)         CONSTRAINT Szef_ko REFERENCES Pracownicy,    Nazwa_działu VARCHAR2(35),    Miejsce VARCHAR2(35),    Zarobki NUMBER(8,2),    Premia NUMBER(8,2),    CONSTRAINT Dz_ko FOREIGN KEY (Nazwa_działu, Miejsce) REFERENCES Działy(Nazwa, Miasto),    CONSTRAINT P_ck CHECK(0.1*Zarobki<=Premia AND Premia<=0.5*Zarobki) );

W przypadku warunku występującego w CHECK akceptowane są wiersze, których wartością jest True lub Null. System nie pozwala, aby w tabeli pojawił się wiersz dający wartość False (przy próbie wprowadzenia sygnalizowany jest błąd).

Zwróćmy uwagę na to, że w powyższej instrukcji wystąpił przykład odwołania się klucza obcego (Szef) do klucza głównego (Id_pracownika) w tej samej tabeli.

Akcje referencyjne

Przy zwykłej semantyce instrukcji DELETE, jeśli narusza ona warunek istnienia obiektu wskazywanego przez wartość klucza obcego, podnoszony jest błąd i instrukcja nie zostaje wykonana. Natomiast przy zastosowaniu opcji ON DELETE CASCADE, wiersz tabeli nadrzędnej (wskazywanej przez odwołanie klucza obcego) zostaje nie tylko usunięty, ale powoduje jednocześnie usunięcie wszystkich wierszy tabeli podrzędnej (tj. zawierającej klucz obcy), w których wartości klucza obcego wskazują na usuwany wiersz - oczywiście pod warunkiem, że usunięcie tych wierszy jest możliwe bez naruszenia innych więzów referencyjnych – w przeciwnym razie wykonanie instrukcji DELETE zostaje wstrzymane.

Natomiast przy zastosowaniu opcji ON DELETE SET NULL przy usuwaniu wiersza, do którego są odwołania przez wartości kluczy obcych, następuje wstawienie pseudo-wartości Null jako wartości klucza obcego.

W Oracle poprzez akcje referencyjne nie są realizowane inne opcje występujące w Standardzie, jak:
 

ON DELETE SET DEFAULT

oznaczająca wstawienie wartości domyślnej jako wartości klucza obcego; jak również analogiczne opcje dla operacji UPDATE:
 

ON UPDATE [CASCADE|SET NULL|SET DEFAULT]

W Oracle można je zaprogramować za pomocą wyzwalaczy bazy danych (wykład 6).

Klucz obcy a NULL

Gdy klucz obcy składa się z kilku kolumn i w jednej z nich jest wartość Null, wtedy system akceptuje taką wartość wiersza bez żadnych dodatkowych sprawdzeń. Gdybyśmy chcieli realizować semantykę polegającą na tym, że albo wszystkie wartości klucza obcego są Null, albo żadna z nich nie jest, wówczas należałoby zagwarantować to za pomocą odpowiedniego warunku CHECK, np. aby zagwarantować ten warunek dla kolumn A i B należałoby użyć następującego warunku:
 

CONSTRAINT A_B_ck CHECK (((A IS NOT NULL) AND (B IS NOT NULL)) OR ((A IS NULL) AND (B IS NULL)))

 
Operacje na więzach spójności

Więzy spójności dodaje się tak jak nowe kolumny używając instrukcji ALTER TABLE, np.

ALTER TABLE Emp
ADD (CONSTRAINT Sal_ck CHECK (sal >5000));

ALTER TABLE Emp ENABLE CONSTRAINT Sal_ck;

ALTER TABLE Emp DISABLE CONSTRAINT Sal_ck;

ALTER TABLE emp DROP CONSTRAINT Sal_ck;

Od tego miejsca do końca wykładu 6 skupimy się na konstrukcjach należących do Oracle. Nie będziemy już tego faktu dalej podkreślać.

4.3 System SQL*Plus

SQL*Plus jest językiem i jednocześnie interakcyjnym systemem umożliwiającym wprowadzanie i wykonywanie instrukcji języka SQL, skryptów złożonych z instrukcji języka SQL i SQL*Plus, bloków języka PL/SQL oraz wywołań procedur. Służy do tworzenia bazy danych, wprowadzania do niej zmian i do testowania procedur i części aplikacji. Możliwe jest wręcz układanie prostych aplikacji w postaci skryptów poleceń języka SQL*Plus związanych z wprowadzaniem danych, wyszukiwaniem danych i drukowaniem raportów – czynności te oczywiście są realizowane dużo lepiej przez specjalistyczne programy Oracle, jak np. Oracle Forms, które jednak są dużo bardziej skomplikowane i wymagają dużo silniejszego sprzętu. Za to, SQL*Plus jest prosty i może działać praktycznie na każdym komputerze. Każdy programista i administrator systemu Oracle używa go.

W SQL*Plus można deklarować zmienne dwóch rodzajów: wiązania i podstawienia, których można używać w instrukcjach SQL i PL/SQL do wzajemnej komunikacji wartości.

Wyliczymy teraz kilka najważniejszych poleceń języka SQL*Plus, z których korzysta się tworząc bazę danych i aplikację.

• START nazwa_pliku – wykonaj skrypt (w postaci ciągu) poleceń SQL i SQL*Plus zapisany w pliku o podanej nazwie (zamiast START można używać skrótu @);

• ED nazwa_pliku – wywołaj standardowy edytor np. notepad albo vi;

• SPOOL nazwa_pliku – zapisuj kolejne polecenia i ich wyniki w pliku o podanej nazwie;

• SPOOL OFF – zamknięcie poprzednio otwartego przez SPOOL pliku;

• SET PAUSE ON – zatrzymywanie przewijania wyświetlania wyników po wyświetleniu zawartości jednego ekranu;

• SET AUTOCOMMIT ON – wykonywanie operacji zatwierdzania COMMIT po każdej zmianie danych (a nie tylko na koniec pomyślnej transakcji);

• SET SERVEROUTPUT ON - wypisywanie wyników realizacji  kodu PL/SQL na ekran (więcej na ten temat w następnym wykładzie);

• SET PAGESIZE n – ustal wielkość jednej strony na n linii;

• HOST polecenie – wykonaj polecenie systemu operacyjnego;

• EXIT – zakończ sesję SQL*Plus – wyloguj się z Oracle;

• DESCRIBE nazwa – wyświetl schemat obiektu (tabeli, perspektywy, procedury, funkcji) o podanej nazwie;

• EXECUTE nazwa_procedury(...) – wykonaj procedurę;

• COLUMN nazwa FORMAT An – ustal szerokość wyświetlania kolumny napisowej o podanej nazwie na n znaków;

• COLUMN nazwa FORMAT 99999 – ustal szerokość wyświetlania kolumny liczbowej o podanej nazwie (9 reprezentuje jedną cyfrę);

• VARIABLE X NUMBER (lub CHAR(n) lub VARCHAR2(n), gdzie n jest stałą będącą liczbą całkowitą większą od 0) – deklaracja zmiennej wiązania o nazwie X;

• EXECUTE :X:= wyrażenie – przypisz na zmienną wiązania X wartość wyrażenia. Po ustaleniu wartości, zmienne wiązania poprzedzone dwukropkiem można używać w instrukcjach SQL i PL/SQL.

• PRINT X – wypisz wartość zmiennej wiązania X;

• ACCEPT Zmienna PROMPT 'Podaj wartość: ' – utwórz zmienną  podstawienia o nazwie Zmienna i pobierz od użytkownika jej wartość. Następnie zmienne podstawienia poprzedzone znakiem & można używać w instrukcjach SQL i PL/SQL;

• &zmienna, &&zmienna  - wskazują na zmienne podstawienia w instrukcjach w SQL i w SQL*Plus. SQL*Plus wstawia wartość za zmienną podstawienia. Jeśli zmienna nie ma wartości zdefiniowanej to SQL*Plu prosi o wartość za każdym razem gdy napotka zmienną z jednym „&” bądź tylko za pierwszym razem gdy napotka zmienną z dwoma „&&”;

• SET VERIFY OFF - system nie przypomina o wartościach zmiennych podstawienia przy ich użyciu.

Przykłady zastosowania zmiennych wiązania i podstawienia są przedstawione w następnym wykładzie.

Uwaga

Wersja programu SQL*Plus, o nazwie iSQL*Plus, działa przez Internet na przeglądarce internetowej. Niestety niektóre użyteczne cechy SQL*Plus nie są obsługiwane w iSQL*Plus, między innymi polecenia ACCEPT i SPOOL.
 

Podsumowanie

Na tym wykładzie zajmowaliśmy się dwoma tematami związanymi z tworzeniem aplikacji bazodanowej po stronie serwera. Pierwszy temat to deklaratywne więzy spójności - definiowane razem z tabelami. Są one sprawdzane przez serwer bazy danych przy każdej operacji na danych. Jeśli wykonanie operacji powodowałoby naruszenie więzów, serwer generuje wyjątek i nie dopuszcza do wykonania operacji.

Drugi przestudiowany temat to informacja o interakcyjnym systemie poleceń SQL*Plus – nieodzownym przy tworzeniu bazy danych i testowaniu aplikacji korzystających z bazy danych Oracle.
 

Słownik pojęć

więzy spójności encji - więzy spójności dotyczące samych instancji encji bez ich związku z powiązanymi encjami.

UNIQUE - więzy klucza jednoznacznego oznaczające, że wartości w określonych kolumnach jednoznacznie identyfikują wiersz. Jest dozwolona pseudo-wartość NULL.

NOT NULL – więzy oznaczające, że w kolumnie nie jest dozwolona pseudo-wartość NULL.

CHECK - więzy w postaci warunku, który ma być prawdziwy lub Null dla wszystkich wierszy  w tabeli.

więzy spójności referencyjnej - więzy zapewniające, że zbiór wartości w kolumnach klucza obcego jest zawsze podzbiorem zbioru wartości odpowiadającego mu klucza głównego lub jednoznacznego (z dokładnością do NULL).

SQL*Plus - język i jednocześnie interakcyjny system umożliwiający wprowadzanie i wykonywanie instrukcji języka SQL, skryptów złożonych z instrukcji języków SQL i SQL*Plus, bloków języka PL/SQL oraz wywołań procedur. Na przykład, describe jest to instrukcja SQL*Plus powodująca wyświetlenie na ekranie schematu obiektu bazy danych takiego jak tabela, perspektywa czy procedura.
 

Zadania

1. Opracuj skrypt zakładający bazę danych klientów i składanych przez nich zamówień. Uwzględnij warunki poprawności danych w postaci deklaratywnych więzów spójności oraz akcji referencyjnych.
    
2. Podaj definicję tabeli Emp (jako instrukcję CREATE TABLE Emp1...) razem ze wszystkimi naturalnymi dla danych pracownika więzami spójności.
   
3. Napisz skrypt SQL*Plus służący do wprowadzania nowego pracownika (dane mają być wprowadzone na bieżąco). Po wprowadzeniu do bazy danych (pamiętaj o wykonaniu COMMIT), wypisz wprowadzone wartości na ekran.