SQL – JDD (DDL)

1. SQL – JDD (DDL) Język definicji danych (Data Definition/Description Language)

2. Elementy bazy danych • Tabele, perspektywy • Dziedziny, typy • Indeksy, sekwencje • Schematy, użytkownicy, prawa • Wyzwalacze i asercje • Procedury użytkownika • Katalog danych (data dictionary) SQL - język definicji danych

3. Tabela • CREATE {TEMPORARY|TEMP} TABLE nazwa tabeli (kolumna1dziedzina1[DEFAULTwartosc_domyslna1] [wiezy_kolumny_1], kolumna2 dziedzina2[DEFAULTwartosc_domyslna2] [więzy_kolumny_2],... [więzy_tabeli],... ) SQL - język definicji danych

4. Dziedziny standardowe • Logiczna: bool, boolean(TRUE, FALSE, UNKNOWN); • Znakowe: char, char(n) lub character(n), varchar(n) lub character varying(n); • Bitowe: bit(n), bit varying(n); • Liczowe dokładne: int lub integer, shortint, numeric(n,p), decimal(n,p),… • Liczbowe zmiennopozycyjne: float(p), real, double… • Daty i czasu: date, time, timestamp [WITH TIME ZONE], interval p to k • Duze obiekty: text lub clob (varchar), blob. • Liczniki: SERIAL (AUTO_INCREMENT), CREATE SEQUENCE • Przekształcanie typu: CAST(wartosc AS typ), wartosc::typ, typ wartosc, SQL - język definicji danych

5. Więzy kolumny • [CONSTRAINTnazwa więzu] NOT NULL | NULL | DEFAULTwartość domyślna | PRIMARY KEY | UNIQUE | CHECK(warunek) | REFERENCEStabela [(kolumna)] [akcja referencyjna] [integralność referencyjna] [DEFERRABLE | IMMEDIATE] [INITIALLY IMMEDIATE | INITIALLY DEFERRED] SQL - język definicji danych

6. Akcja referencyjna • ON { DELETE | UPDATE } { RESTRICT | NO ACTION | CASCADE | SET NULL | SET DEFAULT } SQL - język definicji danych

7. Integralność referencyjna • Domyślnie – jeżeli jakaś kolumna klucza obcego jest pusta, to integralność referencyjna nie jest kontrolowana; • MATCH FULL – jeśli wszystkie kolumny klucza obcego są puste, to integralność referencyjna nie jest kontrolowana; przeciwnie – musi istnieć odpowiedni rekord w tabeli nadrzędnej; • MATCH PARTIAL – rekord podrzędny pasuje do rekordu nadrzędnego, jeśli zgadza się z nim na niepustych polach klucza obcego. SQL - język definicji danych

8. Więzy tabeli • [CONSTRAINT <nazwa więzu>] PRIMARY KEY (<lista kolumn>) | UNIQUE (<lista kolumn>) | FOREIGN KEY (<lista kolumn>) REFERENCES <tabela>(<lista kolumn>) [<akcja referencyjna>][<integralność referencyjna>]| CHECK <warunek> [DEFERRED | IMMEDIATE] SQL - język definicji danych

9. Więzy – moment kontroli • Spełnienie więzu może być kontrolowane natychmiast po wprowadzeniu, aktualizacji rekordu z tabeli (IMMEDIATE); • Może być jednak odroczone do momentu wypełnienia transakcji (DEFERRED); • Zmiany więzów nie muszą powodować kontroli całej aktualnej zawartości bazy danych pod kątem ich poprawności (naruszenia więzów dla innych rekordów, które zostały wcześniej wstawione do bazy). • SET CONSTRAINTS {ALL|nazwa,…} {DEFERRED | IMMEDIATE} SQL - język definicji danych

10. Dziedzinaużytkownika • CREATE DOMAIN <nazwa> AS <dziedzina_zdefiniowana> [DEFAULT <wartość domyślna>][<więzy dziedzinowe>] • <wartość domyślna>::= <stała> | NULL | <f. bez arg.> • <więzy dziedzinowe>::= [CONSTRAINT <nazwa więzu>] CHECK ( VALUE { IN (v1,v2,...,vk) | [IS][NOT] NULL | <operator porównania> <wartość>| BETWEEN <w1> AND <w2>| LIKE <wzorzec>| IN (SELECT…) }) SQL - język definicji danych

11. SQL - język definicji danych

12. Perspektywa (tabela wirtualna) • CREATE VIEW <nazwa perspektywy> [<nazwa kolumny>,...] AS <zapytanie SELECT definiujące perspektywę> [ WITH [CASCADED|LOCAL] CHECK OPTION] SQL - język definicji danych

13. Perspektywa - implementacja • Obliczanie wartości na żądanie – znaczenie analogiczne jak wyrażenie tabelowe w klauzuli FROM • Perspektywa materializowana – zapytanie definiujące perspektywę jest wyliczane i przechowywane jako tabela przez SZBD. Zapytania do perspektywy są realizowane przez wgląd w tę tabelę. Modyfikacje perspektywy mogą być przeprowadzane przez: • ponowne obliczenie zapytania; • aktualizację tylko tych rekordów, które dotknęła modyfikacja. • Rozkład – jest metodą polegającą na przekształceniu zapytania dotyczącego perspektywy tak, by dotyczyło tabel bazowych, a warunki wyboru pochodzące z definicji perspektywy zostały włączone w definicję tabeli SQL - język definicji danych

14. Perspektywa – modyfikacje danych • Perspektywa jest modyfikowalna, gdy dotyczy jednej tabeli i występują w niej wszystkie kolumny niepuste (z nałożonym warunkiem NOT NULL lub PRIMARY KEY) z tej tabeli. • Perspektywa może być modyfikowalna, gdy powstała z kilku tabel, ale występują w niej kolumny kluczy i kolumny niepuste z wszystkich tabel, które są złączane w celu utworzenia perspektywy. SQL - język definicji danych

15. Perspektywa – modyfikacje danych Perspektywa nie może być modyfikowalna, gdy: • zawiera operator DISTINCT; • zwraca kolumnę wyliczaną; • jest definiowana przez zapytanie grupujące; • jest definiowana przez zapytanie zawierające UNION, EXCEPT lub INTERSECT; • Modyfikacja może być niebezpieczna, gdy w warunku WHERE zapytania definiującego występuje zapytanie zagnieżdżone odwołujące się do tabeli występującej w klauzuli FROM tego zapytania, czyli warunek selekcji w perspektywie jest zależny od aktualnej zawartości tabeli, na bazie której jest zdefiniowana perspektywa SQL - język definicji danych

16. Perspektywa – WITH CHECK OPTION • WITH CHECK OPTION • rekord wstawiony poprzez perspektywę musi należeć do perspektywy; • rekord zmodyfikowany poprzez perspektywę nadal musi należeć do perspektywy. • CASCADED – wstawienie lub modyfikacja rekordu do perspektywy lub perspektywy na jej podstawie zdefiniowanej, nie może spowodować zniknięcia rekordu z danej perspektywy; • LOCAL – rekord może zniknąć po modyfikacji z perspektywy, o ile zniknie z tabeli, na podstawie której jest zdefiniowana perspektywa. SQL - język definicji danych

17. Indeksy • CREATE [UNIQUE] INDEX <nazwa indeksu> ON <nazwa tabeli> (<lista atrybutów>) [USING { HASH | BTREE | RTREE }] SQL - język definicji danych

18. Sekwencje • CREATE SEQUENCE <nazwa> [INCREMENT <przyrost>] [START <wartoscPocz>] [MINVALUE <wartoscMin>] [MAXVALUE <wartoscMaks>] [CYCLE|NOCYCLE] [ORDER|NOORDER] … • Zastosowanie: • nextval(‘sekw1’); • setval(‘sekw1’,wartosc); SQL - język definicji danych

19. Zmiany w schemacie BD • DROP – usunięcie danego obiektu (DOMAIN, TABLE, VIEW, INDEX, SEQUENCE) z opcją RESTRICT (domyślnie) lub CASCADE; usunięcie z opcją RESTRICT nie powiedzie się, jeśli istnieją obiekty zależne od danego; z opcją CASCADE usuwamy wszystkie obiekty zależne (kasujemy perspektywy, likwidujemy więzy klucza obcego odwołujące się do likwidowanej tabeli,…) • ALTER – modyfikacja definicji; możliwe operacje, to: • [DROP | ADD ] COLUMN] • [DROP | ADD ] CONSTRAINT • [DROP | SET ] [DEFAULT...][CHECK...] SQL - język definicji danych

20. Schematy i użytkownicy • CREATE USER nazwa [CREATEDB | CREATEUSER | IN GROUP nazwaGrupy ] • CREATE GROUP nazwaGrupy [opcje-jak-przy-uzytkowniku] • CREATE ROLE nazwaRoli [opcje-jak-przy-uzytkowniku] • CREATE SCHEMA [nazwa] [AUTHORIZATION uzytkownik] • Ogólna nazwa obiektu b.d.:schemat.obiekt SQL - język definicji danych

21. Prawa Dowolny SZBD powinien zapewniać również poufność naszych danych oraz możliwość regulowania oraz limitowania dostępu do danych. Dlatego w SQL-u zdefiniowano tzw. prawa, które właściciel obiektu może dać (GRANT) lub odebrać (REVOKE) innym użytkownikom. Istnieje też możliwość przekazania otrzymanych wcześniej praw (GRANT OPTION). UWAGA: użytkownik ma pełne prawa do obiektów, które utworzył; tych praw nie można mu odebrać (sam też nie może się pozbyć prawa usuwania i modyfikowania swoich obiektów). SQL - język definicji danych

22. Składnia do nadawania praw GRANT prawa ON element-bazy-danych TO lista-użytkowników [WITH GRANT OPTION] Prawa, które można nadać to: • dla tabel, perspektyw: SELECT, INSERT, DELETE, UPDATE, REFERENCES; • dla indeksów, dziedzin, sekwencji, funkcji: USAGE, UPDATE, EXECUTE; • REFERENCES dotyczy możliwości odwołania się do danej struktury w więzach integralności (np. asercje,więzy integralności referencyjnej); USAGE odnosi się do dziedzin i elementów schematu innych niż relacje; użytkownik definiujący perspektywę musi mieć prawo SELECT do tabel, do których odwołuje się w perspektywie; • można także nadać wszystkie prawa (ALL [PRIVILEGES]) lub prawa wszystkim użytkownikom (PUBLIC). SQL - język definicji danych

23. Przykłady nadawania praw root: GRANT REFERENCES(ido), SELECT ON osoba TO mariusz; - potrzebne by mariusz mógł stworzyć poniższe: perspektywę (SELECT) i tabelę (REFERENCES) mariusz: CREATE VIEW absolwenci AS SELECT * FROM osoba WHERE kato=‘abs’; mariusz: CREATE TABLE moi_koledzy (ident INT REFERENCES osoba, adres VARCHAR, tel VARCHAR(20)); root: GRANT SELECT, INSERT ON osoba TO mariusz WITH GRANT OPTION; mariusz: GRANT SELECT ON absolwenci TO PUBLIC; root: GRANT USAGE ON DOMAIN imiona TO jacek, placek WITH GRANT OPTION; SQL - język definicji danych

24. Składnia REVOKE • Odbieranie nadanych wcześniej praw (innych niż GRANT) REVOKE prawa ON element-bazy-danych FROM lista-użytkowników [CASCADE|RESTRICT]; • Odbieranie prawa GRANT REVOKE GRANT OPTION FOR prawa ON element-bazy-danych FROM lista- użytkowników [CASCADE|RESTRICT] SQL - język definicji danych

25. REVOKE • Podobne jak w przypadku GRANT w zdaniu REVOKE mogą wystąpić ALL PRIVILEGES oraz PUBLIC. • Prawa są „autoryzowane” - jeśli ktoś otrzymał takie samo prawo od wielu użytkowników to odebranie praw przez jednego użytkownika nie powoduje odebrania tego prawa przyznanego przez innych użytkowników (czyli dopiero, gdy wszystkie osoby odbiorą to prawo zostanie ono utracone) • Jeśli użytkownik przekazał prawo, które uzyskał z klauzulą GRANT OPTION, to odebranie mu tej opcji powoduje, że prawo, które przekazał innym, jest im odbierane. • Prawo trzeba „odbierać” tą samą drogą, jaką było nadane – jeśli A nadał prawo X użytkownikowi B WITH GRANT OPTION, a B nadał to prawo C, to A nie może odebrać prawa bezpośrednio C (REVOKE X FROM C) – musi odebrać „GRANT OPTION” użytkownikowi B (z klauzulą CASCADE), a to spowoduje odebranie prawa X użytkownikowi C. SQL - język definicji danych

26. REVOKE root:GRANT INSERT ON osoba TO mariusz WITH GRANT OPTION; mariusz: GRANT INSERT ON osoba TO janusz; root: REVOKE INSERT ON osoba FROM janusz; -> janusz ma nadal prawo, bo nadał je mariusz root: GRANT SELECT ON przedmiot TO mariusz, janusz WITH GRANT OPTION; mariusz: GRANT SELECT ON przedmiot TO janusz; root: REVOKE SELECT ON przedmiot FROM janusz; -> janusz ma nadal prawo SELECT – to od mariusza root: REVOKE GRANT OPTION FOR SELECT ON przedmiot FROM mariusz [CASCADE]; -> janusz traci prawo przekazania prawa SELECT – próba odebrania tego prawa bez CASCADE spowoduje ostrzeżenie (ale nie powiedzie się), z opcją CASCADE prawo zostanie odebrane; mariusz: REVOKE SELECT ON przedmiot FROM janusz; -> janusz nie ma już prawa SELECT; SQL - język definicji danych

27. Więzy ogólne, czyli asercje Dotychczas poznane więzy dotyczyły pojedynczej krotki (CHECK dla kolumny i/lub tabeli), klucza głównego (PRIMARY KEY) i alternatywnego (UNIQUE, NOT NULL) oraz ewentualnie prostych związków między krotkami z różnych relacji (FOREIGN KEY…REFERENCES). Czasami chcielibyśmy zapewnić przestrzeganie warunku, który dotyczy całej relacji lub paru relacji. Próba zapisania takiego warunku w definicji jednej z relacji może nie dać zamierzonego efektu – warunek taki jest sprawdzany przy okazji operacji na tej relacji, a nie na innych. SQL - język definicji danych

28. Składnia asercji CREATE ASSERTION nazwa_asercji CHECK (warunek) gdzie warunek musi być typu logicznego, np. • NOT EXISTS (SELECT * FROM zapis WHERE ido IN (SELECT ido FROM osoba WHERE kato=‘abs’) • 3< ALL(SELECT COUNT(*) FROM (((przedmiot NATURAL JOIN edycja) JOIN sem ON ids=semestr) JOIN kategoria ON (idk=typ)) WHERE sem.nazwa LIKE ‘%letni 2006%’ AND kategoria.nazwa=‘kurs’) Inne przykłady warunków: • do żadnej grupy nie może być zapisanych więcej studentów, niż liczba podana w polu limit; • wykład obowiązkowy musi prowadzić profesor (moglibyśmy go weryfikować, gdybyśmy mieli w bazie tytuły); • aby zapisać się na przedmiot X, trzeba mieć wcześniejszy wpis na przedmiot Y; SQL - język definicji danych

29. Wyzwalacze (TRIGGERS) Czasami chcielibyśmy reagować w bardziej aktywny sposób na sytuacje, gdy dochodzi do naruszenia jakiegoś warunku lub niezmiennika niż tylko nie dopuszczając do tych modyfikacji bazy danych, która ten warunek narusza (być może dla pewnych sytuacji umiemy temu zaradzić). Poza tym czasami to my chcielibyśmy decydować, kiedy warunek ma być sprawdzany (choćby ze względu na wydajność), a w przypadku np. asercji to system o tym decyduje. Poza tym asercje są „drogie”, więc w praktyce nie istnieją. Dlatego firmy zaoferowały coś „tańszego” od asercji, co pozwala nam na kontrolowanie poprawności danych w bazie w pewnych sytuacjach. SQL - język definicji danych

30. Cechy wyzwalaczy • Wyzwalacze (potrzeba ich użycia) są testowane tylko przy zajściu zdarzenia (wstawienie, usunięcie, modyfikacja krotki) określonego przez programistę (projektanta bazy) (w przypadku asercji i więzów krotkowych decyduje o tym SZBD). • Warunek jest testowany w chwili zajścia zdarzenia. • Jeśli zachodzi warunek opisany w wyzwalaczu, to wykonywana jest akcja związania z wyzwalaczem, której zadaniem jest przygotowanie bazy do zmian wywołanych zdarzeniem lub uzupełnienie tych zmian tak, by baza po nich pozostawała w poprawnym stanie. • W akcji wyzwalacza mogą być zawarte zdarzenia, z którymi są związane ich własne wyzwalacze. Są one uruchamiane zarówno w trakcie bezpośredniego wywołania zdarzenia w SQL, jak i wówczas, gdy wydarzenie to zostało wywołane np. przez inny wyzwalacz. • Zdarzenie i akcja wyzwalacza stanowią jedną transakcję – gdy coś się nie powiedzie w trakcie akcji wyzwalacza, to wycofywana jest cała operacja (aż do początku całej transakcji, czyli zdarzenia, które jako pierwsze uruchomiło wyzwalacz). SQL - język definicji danych

31. Składnia wyzwalaczy CREATE TRIGGER nazwa_wyzwalacza {BEFORE|INSTEAD OF |AFTER} {UPDATE [OF nazwa_kolumny] | INSERT | DELETE} ON nazwa_tabeli REFERENCING {OLD|OLD TABLE} AS nazwa_zmiennej1 {NEW|NEW TABLE} AS nazwa_zmiennej2 WHEN (warunek) lista_poleceń_SQL_do_wykonania //akcja [FOR [EACH] {ROW|STATEMENT}] UWAGA: W miejscu klauzul „REFERENCING… WHEN... lista_poleceń” może wystąpić wywołanie procedury „EXECUTE PROCEDURE”, w której wykonywane są analogiczne operacje. SQL - język definicji danych

32. Cechy wyzwalaczy • Akcja może być wykonana przed (BEFORE), po (AFTER) lub zamiast (INSTEAD OF) zdarzenia • Można określać czy akcja ma być wykonywana dla każdej modyfikowanej krotki (FOR EACH ROW) czy tylko raz dla wszystkich krotek zmodyfikowanych w pojedynczej operacji (FOR EACH SENTENCE, czyli domyślnie); • W akcji dostępne są wartości sprzed (OLD, OLD TABLE) zajścia zdarzenia jak i nowe (NEW,NEW TABLE) wartości; • OLD i OLD TABLE mają sens dla DELETE i UPDATE, a NEW i NEW TABLE dla UPDATE i INSERT; • OLD TABLE i NEW TABLE mają sens dla FOR EACH SENTENCE, a OLD i NEW dla FOR EACH ROW. • Akcja jest wykonywana tylko wtedy, gdy spełniony jest warunek z klauzuli WHEN. SQL - język definicji danych

33. Przykład wyzwalacza Uniemożliwienie zapisu ponad limit do grupy: CREATE TRIGGER PilnujLimituPrzyZapisie AFTER INSERT ON zapis REFERENCING NEW AS nowa WHEN ((SELECT limit FROM grupa WHERE idg=nowa.idg)<(SELECT COUNT(*) FROM zapis WHERE idg=nowa.idg)) DELETE FROM zapis WHERE idg=nowa.idg AND ido=nowa.ido FOR EACH ROW SQL - język definicji danych

34. Inny przykład wyzwalacza Uniemożliwienie zapisu ponad limit do grupy (cd.) – konieczne jest także zareagowanie, gdy ktoś zechce obniżyć limit poniżej liczby aktualnie zapisanych w grupie osób: CREATE TRIGGER PilnujLimituPrzyGrupie AFTER UPDATE OF limit ON grupa REFERENCING NEW AS nowa REFERENCING OLD AS stara WHEN (stara.limit>nowa.limit AND EXISTS (SELECT grupa.idg FROM zapis NATURAL JOIN grupa GROUP BY grupa.idg HAVING COUNT(*)>limit) UPDATE grupa SET limit=stara.limit WHERE idg=stara.idg FOR EACH ROW SQL - język definicji danych

35. SQL - język definicji danych