Wykład 5: Zaawansowany SQL

Zaawansowany SQL

         Typy danych SQL i schematy Więzy integralności (spójności) Wyzwalacze (

triggers

) Procedury składowane (

stored procedures

) Funkcje Autoryzacja Role Zanurzony SQL Dynamiczny SQL

Typy danych czas-data

   

date :

 Data zawierające (4 cyfry) rok, miesiąc i dzień Przykład:

date

‘2007-1-27’

time :

 czas w godzinach, minutach i sekundach.

Przykład:

time

‘11:55:30’

time

‘11:55:30.65’

timestamp

: data oraz czas  Przykład:

timestamp

‘2007-1-27 11:55:30.75’

interval



:

Przedział czasu Przykład: interval ‘1’ day   odejmowanie jednej wartości date/time/timestamp od innej daje wartość typu

interval

Wartości typu

interval

mogą być dodawane do wartości date/time/timestamp

Typy danych czas-data (cd.)

 Możemy pobierać wartości poszczególnych pól z wartości date/time/timestamp   Przykład:

extract

(

year from

r.starttime) MSSQL używa funkcji DAY, MONTH, YEAR, DATEADD, DATEDIFF, DATEPART oraz GETDATE:  SELECT YEAR(starttime) from r;  Możemy rzutować łańcuchy znaków na wartości typu date/time/timestamp  Przykład: 

cast

< wyrażenie tekstowe>

as date

 Przykład MSSQL: 

cast

('1 październik 2003'

as datetime

)

Typy definiowalne

  

create type

– taka konstrukcja w SQL tworzy typ definiowalny (typ uzytkownika) create type zloty as numeric (12,2) final

Tylko ORACLE wspiera tą konstrukcję!

create domain

- taka konstrukcja w SQL-92 tworzy dziedziny typów - definiowalnych create domain person_name char (20)

not null

  Typy i dziedziny są podobne. Dziedziny mogą posiadać więzy np.

not null

Większość producentów SZRBD nie wspiera tych konstrukcji!

Więzy dla dziedzin

  

Domain constraints

są podstawową postacią więzów spójności. Sprawdzają wartości wprowadzane do bazy i sprawdzają czy porównania w kwerendach mają sens:  CREATE DOMAIN VALID_EMPL_IDS INTEGER  CHECK (VALUE BETWEEN 101 AND 199); Nowe dziedziny mogą być tworzone z istniejących typów danych  Przykład: create domain zloty numeric (12, 2); create domain euro numeric (12,2); Nie można przyrównać/przypisać wartości typu zloty do wartości typu

euro.

 Ale możemy przekształcić typy tak jak poniżej: ( cast r .

A as euro ) ( Powinno również przemnożyć wynik przez kurs wymiany)

Typy opisujące duże obiekty

  Obiekty takie jak zdjęcia, pliki wideo, ... są przechowywane w bazie jako

large object

: 

blob

: binary large object danych – obiekt jest kolekcją binarnych danych, których interpretacji dokonuje aplikacja poza systemem bazy  

clob

: character large object – kolekcja znaków Jeżeli w kwerendzie znajduje się zapytanie o duże obiekty zwracany jest wskaźnik a nie sam obiekt.

Przykład ORACLE:  Typ BLOB pozwala na przechowanie do 8 terabajtów danych binarnych w bazie danych.

 Typ CLOB pozwala na przechowanie do 8 terabajtów jednobajtowych znaków w bazie danych.   Typ NCLOB wielobajtowe CBLOB. Typ BFILE przechowuje duże dane binarne w plikach zewnętrznych wzlędem bazy danych.

Więzy spójności

 Więzy spójności zapobiegają przypadkowemu uszkodzeniu bazy danych. Sprawdzają, czy zmiany w bazie nie powodują utraty spójności danych.  Rachunek oszczędnościowy musi mieć stan co najmniej 30,000.00

  Pensja pracownika nie może być mniejsza niż 7 zł za godzinę Klient musi posiadać telefon (niepusta wartość atrybutu)

Więzy spójności dla pojedynczej relacji

   

not null primary key unique check

(

P

)

,

gdzie

P

jest predykatem

Więzy

not null

 Deklarujemy, że oddzial_nazwa dla relacji

aktywa

jest

not null

oddzial_nazwa char (15)

not null

 Dziedzina

Euro

ma być

not null

create domain Euro numeric (12,2)

not null

Więzy

unique

  

unique

(

A

1 ,

A

2 , …,

A

m ) Specyfikacja unique stwierdza, że atrybuty

A

1,

A

2, …

A

m tworzą klucz kandydujący.

W przeciwieństwie do kluczy głównych klucze kandydujące mogą być puste (null)

Klauzula

check



check

(

P

)

,

gdzie

P

jest predykatem (

MySQL nie realizuje klauzuli check)

Przykład: Deklarujemy

oddzial_nazwa jako klucz główny

i żądamy aby wartości aktywów nie były ujemne.

create table oddzial ( oddzial_nazwa char (15)

,

oddzial_miasto

char

(30),

aktywa

integer

,

primary key

(

oddzial_nazwa

)

,

check

(

aktywa >=

0)) Zbliżają się wybory, w MS SQL sprawdzamy, czy wyborca ma 18 lat w dniu wyborów:

ALTER TABLE

CK_wiek_18 wyborca

CHECK ADD CONSTRAINT

(DateDiff(yy,DateofBirth, DateofVote)>=18);

Klauzula

check



check

może być wykorzystane jako więzy dla krotek (w poprzednim przypadku warunek dotyczył jednego atrybutu, poniżej mamy dwa atrybuty wymienione w warunku) Przykład: CREATE TABLE Campus ( location char(25), enrollment integer, rank integer, CHECK(enrollment >= 10,000 OR rank > 5) );

Klauzula

check

(cd.)

 W standardzie SQL-92 klauzula

check

pozwala na ograniczanie dziedzin :  Można jej użyć np. do sprawdzenia czy stawka godzinowa jest większa od wartość określona prawem.

  create domain stawka_godzina numeric(5,2) constraint sprawdz_stawke check (

value

> = 4.00) W ten sposób więzy są nałożone na dziedzinę atrybutu i zapewniają, że nikt w bazie nie może nam przypisać stawki mniejszej Klauzula

constraint

sprawdz_stawke

jest opcjonalna; wykorzystywana przy sygnalizacji, jakie więzy zostały naruszone przy modyfikacji danych.

Więzy referencyjnej spójności

  Zapewniają, że wartość pojawiająca się w jednej relacji dla danego zbioru atrybutów pojawi się również w innej relacji dla jakiegoś zbioru atrybutów.

 Przykład: Jeśli “Centum” jest nazwą oddziału pojawiającą się w jednej z krotek w relacji

rachunek

, to musi istnieć odpowiednia krotka w relacji

oddzial

dla oddzia łu “Centrum”.

Klucze główne, klucze kandydujące oraz klucze obce mogą być specyfikowane jako części polecenia SQL

create table

:   Klauzula główny.

primary key wymienia atrybuty tworzące klucz Klauzula unique [key] kandydujący.

wymienia atrybuty tworzące klucz  Klauzula foreign key wymienia atrybuty tworzące klucz obcy oraz nawę relacji kojarzonej poprzez klucz obcy. Domyślnie klucz obcy odnosi się do klucza głównego drugiej tabeli.

Więzy spójności w SQL – Przykład

create table klient ( klient_nazwisko char (20)

,

klient_ulica klient_miasto

primary key

(

char char

(30), (30),

klient_nazwisko

)) create table oddzial (

oddzial_nazwa oddzial_miasto aktywa

primary key char

(15)

, char

(30),

numeric

(12,2), (

oddzial_nazwa

))

Więzy spójności w SQL – przykład (cd.)

create table rachunek (

rachunek_numer oddzial_nazwa stan

primary key char

(10)

, char

(15),

integer

, (

rachunek_numer),

foreign key

(

oddzial_nazwa

) references oddzial ) create table depozytor (

klient_nazwisko rachunek_numer

primary key

(

char

(20)

, char

(10)

,

klient_nazwisko, rachunek_numer),

foreign key

(

rachunek_numer

)

foreign key

(

klient_nazwisko

) references rachunek, references klient )

Kaskadowe działanie w SQL

create table rachunek  

. . .

foreign key(oddzial_nazwa) references oddzial

on delete cascade on update cascade

. . .

)

Klauzula

on delete cascade

oddziału w relacji

oddział

spowoduje, że jeśli usuwanie jakiegoś powoduje naruszenie więzów spójności to odpowiednia krotka w relacji

rachunek

zostanie także usunięta.

Podobnie sprawa wygląda dla kaskadowych uaktualnień (zmiana nazwy w tabeli

oddzial z

„Grudziądz” na „Grudziądz Rynek” powinna się przenieść do tabeli

rachunek

).

Kaskadowe działanie w SQL (cd.)

   Jeśli istnieje łańcuch zależności kluczy obcych z

on delete cascade

określonym dla każdej zależności to usuwanie (modyfikacja) na jednym końcu łańcucha propaguje się do drugiego końca (jak kostki domina).

Jeżeli kaskadowe działanie narusza więzy integralności na jakimś stopniu kaskady, system porzuca transakcję.  W wyniku, wszystkie zmiany zostaną wycofane (rollback).

Więzy spójności są sprawdzane na końcu transakcji   Cząstkowe kroki mogą łamać więzy spójności przy założeniu, późniejsze kroki usuną naruszenie W przeciwnym przypadku byłoby niemożliwe opisywanie pewnych faktów, np. wstawienie dwóch krotek, których klucze obce wskazują wzajemnie na siebie: zawieranie małżeństwa

Więzy spójności w SQL (cd.)

   Alternaty wą dla kaskad mogą być: 

on delete set null

 Wstawia w krotce podrzędnej wartości puste  

on delete set default

 Wstawia w krotce podrzędnej watości dpmyślne

on delete restrict



on delete no action (MS SQL)

 Nie pozwala na usuwanie jeśli istnieje krotka zależna Ale wartości puste komplikują „logikę” więzów integralności  jeśli jakikolwiek atrybut klucza obcego ma wartość null, to krotka spełnia więzy integralności z definicji!

W MySQL

set null

warunek

not null

nie może dotyczyć sytuacji gdy pole w tabeli ma (oczywiste!)

Zapewnienia (assertions)

    Nie wszystkie warunki można wyrazić przy pomocy więzów omawianych poprzednio (jak

check

) Zapewnienie (

assertion

) jest predykatem wyrażającym warunek, który zawsze ma spełniać cała baza.

Zapewnienie w SQL przyjmuje postać

create assertion

check

Kiedy wstawione jest „zapewnienie” system sprawdza jego poprawność oraz sprawdza czy predykat jest spełniony przy modyfikacji, która może nie spełniać warunku.  Takie testowanie może wprowadzić duże obciążenie do bazy, zapewnienia powinny być używane z ostrożnością.

 MS SQL ich nie posiada

Przykład zapewnienia



Średnia ocen jest > 3.0 and średnia dochod < 1000

  CREATE ASSERTION Avgs CHECK( 3.0 < (SELECT avg(ocena) FROM Student) AND 1000 > (SELECT avg(dochod) FROM Student)) Student ze średnią < 3.0 może się tylko strać o kampus z rankingiem > 4.  CREATE ASSERTION RestrictApps CHECK( NOT EXISTS (SELECT * FROM Student, Apply, Campus WHERE Student.ID = Apply.ID AND Apply.location = Campus.location AND Student.GPA < 3.0 AND Campus.rank <= 4))

Przykład zapewnienia

 Każdy kredyt posiada przynajmniej jednego kredytobiorcę, który posiada rachunek ze stanem przynajmniej 3000.00

zł create assertion stan_wiezy check

(not exists ( select *

from kredyt

where not exists ( select *

from kredytobiorca, depozytor, rachunek where kredyt.kredyt_numer = kredytobiorca.kredyt_numer and kredytobiorca.klient_nazwisko =

depozytor.klient_nazwisko

and depozytor.rachunek_numer =

rachunek.rachunek_numer

and rachunek.stan >= 3000)))

Przykład zapewnienia

 Suma wszystkich kwot kredytów w każdym oddziale musi być mniejsza od sumy stanów rachunków w tym oddziale.

create assertion suma_wiezy check (

not exists

(

select *

from oddzial

where

(

select sum

(

kwota

) from kredyt where kredyt.oddzial_nazwa =

oddzial.oddzial_nazwa

)

>=

(

select sum

(

stan

) from rachunek where rachunek.oddzial_nazwa =

oddzial.oddzial_nazwa

)))

PROGRAMOWANIE Skarb DBA

(głównie na przykładzie MS SQL Server)

     Struktury proceduralne  Programowanie „wsadowe” (batch)  Zmienne   Instrukcje sterujące Przetwarzanie błędów Procedury składowane Funkcje definiowalne Synonimy Wyzwalacze DML

      Wyzwalacze i procedury składowane – należą do najważniejszych narzędzi DBA (database administrator) oraz DBAD (application developer) Wyzwalacze mogą zaoszczędzić |DBA utraconego czasu i problemów poprzez automatyzację monitorowania stanu bazy i zadań administracyjnych.

Procedury składowane mogą być wykorzystywane do tworzenia skryptów administracyjnych , które będą używane wielokrotnie i zmniejszają czas niezbędny do administracji i szansę na powstanie błędów. To będzie bliższe klasycznemu programowaniu Oprócz tego mamy jeszcze UDFy User Defined Functions Zaczniemy od podstaw: batche, zmienne i instrukcje sterujące!

Batche

   Batch to szereg instrukcji (T-)SQL umieszczonych w jednym pliku W pliku *.sql można umieścić wiele batchy, oddzielamy je instrukcją GO Reguły  Niektóre instrukcje muszą być przesłane w ich własnym batchu: CREATE PROCEDURE, CREATE VIEW, CREATE FUNCTION, CREATE DEFAULT, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER      Zmienne muszą być zdefiniowane i wykorzystane w tym samym batchu Wieloliniowe komentarze /* …. */ muszą się zawierać w jednym batchu Struktury tabel nie mogą być zmieniane w tym samym batchu Błąd kompilacji zatrzyma wykonanie wszystkich instrukcji batcha Błąd wykonania wstrzyma wykonanie kolejnych instrukcji

  USE tempdb; GO CREATE TABLE T1 (C1 int NOT NULL); INSERT INTO T1 VALUES (1); INSERT INTO T1 VALUES (2,2); INSERT INTO T1 VALUES (3); GO SELECT * FROM T1; DROP TABLE T1; GO CREATE TABLE jest kompilowane, po kolei są kompilowane instrukcje INSERT i wykonywane, ale druga Instrukcja zawiera błąd, więc do tablei zostanie dodany tylko jeden wiersz

Zmienne

     Zmienną definiujemy poprzedzają jej nazwę małpą (@) W jednym batchu możemy użyć do 10^4 zmiennych DECLARE @Var1 int; DECLARE @Var2 as varchar(25); DECLARE @Var3 decimal(5,2), @Var4 int; Przypisanie wartości: SET @Var1 = 5; SET @Var2 = ‘A varchar string’; SELECT @Var2 = ‘Another varchar string’, @Var3 = 123.45

Zmienne 2

   Inna forma przypisania (przy pomocy zapytania do bazy SELCT) USE PPDB; DECLARE @CustName varchar(50); SELECT @CustName = CustomerName FROM Customer WHERE CustomerID = 1; Funkcje systemowe (np.

zmiennymi globalnymi @@Error ) nazywane czasami (błędnie )

Instrukcje sterujące

  BEGIN … END grupuje instrukcje używane razem z IF, WHILE, CASE IF … ELSE  USE AdventureWorks; GO UPDATE HumanResources.EmployeePayHistory

SET PayFrequency = 4 WHERE EmployeeID = 1; IF @@ERROR <> 0 -- funkcja sysytemowa BEGIN PRINT ‘An error occured in the previous statement.’; RETURN; END ELSE PRINT ‘No error occured in the previous statement.’;

Instrukcje sterujące 2

   WHILE  DECLARE @Counter int; SET @Counter = 1; WHILE (@Counter <= 10) BEGIN PRINT @Counter; SET @Counter = @Counter + 1; END Powyżej mamy przykład typowej pętli, ale instrukcję WHILE można też wykorzystywać z warunkiem EXISTS do wykonywania operacji na wierszach tabeli  WHILE EXISTS (SELECT * FROM T1 WHERE C1 = 1)BEGIN -- Wykonaj jakieś operacje na wierszach -- tabeli T1 z warunkiem C1 = 1 END W instrukcji WHILE można korzystać z BREAK i CONTINUE, których użycie jest typowe dla pętli („oczywista oczywistość”).

Instrukcje sterujące 3

   CASE USE AdventureWorks2008; GO SELECT Name, CASE Name WHEN ‘Human Resources’ THEN ‘HR’ WHEN ‘Finance’ THEN ‘FI’ WHEN ‘Information Services’ THEN ‘IS’ WHEN ‘Executive’ THEN ‘EX’ WHEN ‘Facilities and Maintenance’ THEN ‘FM’ END AS Abbreviation FROM AdventureWorks2008.HumanResources.Department

WHERE GroupName = ‘Executive General and Administration’; Instrukcja CASE jest używana w celu zamiany wartości kolumny w zapytaniu SELECT. W Transact-SQL CASE przetwarza wiersz po wierszu

Zarządzanie błędami

    Błędy składni Błędy wykonania  PRINT ‘Przed błędem’; SELECT 1/0; PRINT ‘Po błędzie’; Komunikaty błędów    Numer błędu (powyżej 50 000 błędy definiowalne przez użytkownika) Waga błędu (Severity Level) – powyżej 10 mają charakter informacyjny Stan (State) (liczba wskazujące gdzie pojawił się błąd?)  Numer linii  Tekst komunikatu Przykład:  Przed błędem    Msg 8134, Level 16, State 1, Line 2 Divide by zero error encountered.

Po błędzie

Zarządzanie błędami 2

     Blok TRY … CATCH … Składnia  BEGIN TRY -- Kod mogący generować błędy END TRY BEGIN CATCH -- Logika obsługi błędów END CATCH; Blok CATCH musi następować zaraz po bloku TRY Wewnątrz bloku CATCH można użyć kilku funkcji informacyjnych (por. następny slajd) Funkcje te użyte poza blokiem CATCH zwrócą NULL

Zarządzanie błędami 3

   Funkcje informacyjne bloku CATCH  ERROR_LINE()     ERROR_NUMBER() ERROR_MESSAGE() ERROR_PROCEDURE() jeśli błąd pojawił się w procedurze jej nazwa jest zwracana, w przeciwnym razie NULL ERROR_SEVERITY()  ERROR_STATE() Przykład: USE AdventureWorks2008; BEGIN TRY SELECT 1/0; END TRY BEGIN CATCH INSERT INTO dbo.ErrorLog (Line, Number, ErrorMsg, [Procedure], Severity, [State]) VALUES (ERROR_LINE(), ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_PROCEDURE(), ERROR_SEVERITY(), ERROR_STATE()); END CATCH;

Zarządzanie błędami 4

  Funkcja @@ERROR  Zwraca tylko numer błędu   Instrukcje typu  SELECT 1/0  PRINT @@ERROR Ale co będzie wynikiem poniższego kodu?

 SELECT 1/0; IF @@ERROR <> 0 PRINT @@ERROR ;   Warunek IF @@ ERROR <> 0 resetuje wartość funkcji !!!

Należy wiec użyć zmiennej pośredniej, która zapamięta stan funkcji  DECLARE @SaveError int; SELECT 1/0; SET @SaveError = @@ERROR; IF @SaveError <> 0 PRINT @SaveError ; Funkcji @@ERROR używamy głównie ze względu na kompatybilność ze starszymi wersjami SQL Server np. 2000

Zarządzanie błędami 5

   Generacja błędów:   Czasami chcemy / musimy wygenerować własne błędy (nie przewidziane przez system) Posługujemy się wtedy procedurą składowaną sp_addmessage   Przykład: EXEC sp_addmessage 50005, -- Message ID 10, -- Severity Level ‘ID bieżącej bazy: %d, nazwa bazy: %s.’; Instrukcja RAISEERROR wygeneruje odpowiedni błąd, Składnia:  RAISERROR ( { msg_id | msg_str | @local_variable }  { ,severity ,state }   [ ,argument [ ,...n ] ] ) [ WITH option [ ,...n ] ]

Zarządzanie błędami 6

   Przykład:  DECLARE @DBID int; DECLARE @DBNAME nvarchar(128); SET @DBID = DB_ID(); SET @DBNAME = DB_NAME(); RAISERROR (50005, 10, -- Severity.

1, -- State.

@DBID, -- First substitution argument.

@DBNAME); -- Second substitution argument.

GO Można też generować błędy bez dodawania komunikatów do systemu  RAISERROR (‘Custom Message’, 10, -- Severity 1); -- State Oprócz dodawania komunikatów mamy też ich usuwanie:  sp_dropmessage

Procedury składowane (MSSQL)

      Procedury składowane są zbiorami operacji przechowywanymi na serwerze i wykonywanymi przez klienta aplikacji.

Wartości parametrów mogą być przekazywane do procedury przechowywanej jako wejścia.

Parametry wyjściowe mogą być używane do zwracania wartości zmiennej do kodu wywołującego.

Procedura składowana może mieć s sumie do 2100 parametrów.

Pojedyncza wartość całkowita jest zazwyczaj używana do wskazywania sukcesu lub porażki (wykonania procedury).

Istnieje wiele operacji, które mogą być wykonywane przez procedury przechowywane w bazie danych:   Zmiana struktury bazy danych i wykonywanie zdefiniowanych przez użytkownika transakcje są powszechnymi operacjami Procedury składowane mogą być wykorzystane do zwracania wyniku zapytania SELECT, ale istnieją lepsze narzędzia

Procedury składowane 2

  Omówimy budowanie procedur składowanych Transact-SQL, ale możliwe jest również zbudowanie takich procedur przy użyciu .NET (CLR) Zalety:  Bezpieczeństwo:  Prawo do wykonania procedury składowanej jest przyznawane niezależnie od dostępu do obiektów bazy danych których ona dotyczy.

 Użytkownik, który uzyskuje dostęp do wykonania procedury składowanej może wykonywać wszystkie operacje w procedurze przechowywanej.

Możliwe jest również , że wykonujemy ją jako inny użytkownik.

 Modularne programowanie:    Wielokrotne wykorzystywanie, Skomplikowane procedury mogą być rozbijane na bloki Czas wykonania   Procedury są kompilowane raz (w zasadzie) Czas przesyłania kodu do serwera

Procedury składowane 3

  Wiele operacji bazodanowych może być wykonanych przez inne obiekty/struktury Procedury składowane mogą wykonać prawie wszystkie operacje. Ale poniższe są zabronione:  Tworzenie lub modyfikacja następujących obiektów :  Aggregate  Default  Function  Procedure  Rule  Schema  Trigger  View   Instrukcja USE SET PARSEONLY lub warianty SHOWPLAN

Procedury składowane 4

       Procedura może zwrócić więcej niż jeden zbiór rezultatów do wywołującej ją aplikacji. Funkcje tablicowe definiowalne przez użytkownika są lepszym rozwiązaniem jeśli ma być zwrócony jeden wynik. Wyniki procedury nie mogą być używane w klauzuli FROM kwerendy (istnieje funkcja OPENQUERY(), która pozwala na obejście tego ograniczenia).

Procedury mogą korzystać z tablic tymczasowych. Tablica tymczasowa istnieje tylko na czas działania procedury. Procedura zagnieżdżona może korzystać z tablic tymczasowych utworzonych przez procedurę wywołującą (nadrzędną).

Odwołując się do obiektów wewnątrz procedury zalecane jest używanie nazwy schematu, unika się w ten sposób błędów związanych z domyślnym przeszukiwaniem bazy przez procedurę.

Procedury składowane 5

 Składnia  CREATE PROC[EDURE] [schema_name.]proc_name [({@param1} type1 [ VARYING] [= default1] [OUTPUT])] {, …} [WITH {RECOMPILE | ENCRYPTION | EXECUTE AS 'user_name'}] [FOR REPLICATION] AS batch | EXTERNAL NAME method_name    

schema_name

– nazwa schematu do którego jest przypisywana tworzona procedura.

proc_name

– oczywista … Parametr procedury składowanej ma taki sam sens logiczny jak zmienna lokalna w batchu

@param1

– nazwa pierwszego parametru   

type1

- typ pierwszego parametru

default1

– opcjonalna wartość domyślna (może być NULL)

OUTPUT

– wskazuje, że parametr może zwrócić wartość z procedury do systemu (wywołującej aplikacji)

Procedury składowane 6

    Prekompilowana postać procedury jest przechowywana na serwerze Opcja

WITH RECOMPILE

spowoduje, że procedura będzie rekompilowana przed każdym użyciem.



To niszczy jedną z ważnych zalet procedur.

Klauzula

EXECUTE AS

określa kontekst bezpieczeństwa (jako kto) wykonywania procedury. W ten sposób można kontrolować, którego konta użyje baza danych do sprawdzenia uprawnień do obiektów, z których korzysta procedura.

Domyślnie tylko członkowie ról

sysadmin, db_owner

oraz

db_ddladmin

mogą wykorzystywać instrukcję CREATE PROCEDURE. Ale zgodnie z zasadami, członkowie tych ról mogą przekazać te uprawnienia innym użytkownikom przy pomocy polecenia GRANT CREATE PROCEDURE.

Procedury składowane 7

      Przykład USE sample; GO CREATE PROCEDURE increase_budget (@percent INT=5) AS UPDATE project SET budget = budget + budget*@percent/100; Można tworzyć procedury tymczasowe: lokalne (#nazwa_procedury) i globalne (##nazwa_procedury).

Stosują się do nich podobne zasady jak do tablic tymczasowych Procedury mają (w pewnym sensie) dwie fazy : faza tworzenia i faza wykonania Polecenie EXECUTE wykonuje istniejąca procedurę (kto może wykonywać daną procedurę?)

Procedury składowane 8

    Składnia  [[EXEC[UTE]] [@return_status =] {proc_name| @proc_name_var} {[[@parameter1 =] value | [@parameter1=] @variable [OUTPUT]] | DEFAULT}..

[WITH RECOMPILE] Wszystkie opcje poza @return_status mają analogiczne znaczenie jak w instrukcji tworzenia procedury

@return_status

– przechowuje status wykonania procedury Przykład:  SELECT * FROM project; EXEC increase_budget 7; SELECT * FROM project; GO

Procedury składowane 9

 Przykład z wykorzystaniem opcji OUTPUT  USE sample; GO  CREATE PROCEDURE delete_emp @employee_no INT, @counter INT OUTPUT AS SELECT @counter = COUNT(*) FROM works_on WHERE emp_no = @employee_no DELETE FROM employee WHERE emp_no = @employee_no DELETE FROM works_on WHERE emp_no = @employee_no GO - DECLARE @quantity INT – deklarcja EXECUTE delete_emp @employee_no=28559, @counter=@quantity OUTPUT PRINT @quantity GO

Procedury składowane 10

  Usuwanie procedury  Modyfikacja procedury składowanej  ALTER PROCEDURE …   Jest to praktycznie ta sama składnia co dla CREATE PROCEDURE Po co skoro można DROP PRCEDURE oraz CREATE PROC ?

 DROP PROCEDURE nazwa_procedury Ale wtedy znikają zdefiniowane już uprawnienia

Procedury składowane 11

   Od wersji SQL Server 2008 można do procedury przekazywać parametry o wartościach tabelarycznych (czyli tabele) Jest to jedno z lepszych rozszerzeń wprowadzonych do tej wersji serwera Przykład:   W poniższym kodzie korzystamy z typu tablicowego OrderDetailsType , który musiał być wcześniej zdefiniowany CREATE PROC OrderTransactionUpdateTVP ( @OrderID INT OUTPUT, @OrderDate DateTime, READONLY ) @CustomerID INT, @Details as OrderDetailsType AS SET NoCount ON ; Begin Try Begin Transaction; -- Jeśli @OrderID jest NULL to mamy nowe -- zamówienie a wiêc dodajemy do tabeli ORDER

Procedury składowane 11

GO If @OrderID IS NULL BEGIN; Insert Orders(OrderDate, CustomerID) Values (@OrderDate, @CustomerID); -- Get OrderID value from insert SET @OrderID = Scope_Identity(); END; -- poniższa instrukcja tylko wyświetla zawartość tabeli ale można z nią zrobić dużo więcej...

SELECT * FROM @Details ; Commit Transaction; End Try Begin Catch; RollBack; End Catch RETURN;

 Teraz wykorzystamy tą procedurę Declare @OrderID INT; DECLARE @DetailsTVP as OrderDetailsType; INSERT @DetailsTVP (LineNumber,ProductID,IsNew,IsDirty,IsDeleted) VALUES (5, 101, -1, -1, 0), (2, 999, 0, -1, 0), (3, null, 0, 0, 0);  exec OrderTransactionUpdateTVP @OrderID = @OrderID Output , @CustomerID = '78', @OrderDate = '2008/07/24', @Details = @DetailsTVP; Procedura powinna wypisać wartości z tabeli @DetailsTVP

Funkcje definiowalne (UDF)

   UDF mogą zawierać skomplikowaną logikę T-SQL w kwerendzie i rozwiązywać problemy, które były niemożliwe do rozwiazania lub wymagały użycia kursorów.

Dzisiaj stają się jednym z podstawowych narzędzi programisty baz danych.

Zalety:     Por. pierwsze zdanie slajdu „I’ve solved several nasty problems using user-defined functions ” Paul Nilsen .

Mogą być użyte do budowania nowych funkcji dla skomplikowanych wyrażeń Oferuje podobne zalety jak widoki, gdyż mogą być użyte w klauzuli FROM. Ponadto pozwalają na użycie parametrów, czego nie maja widoki.

Oferują zalety procedur składowanych, gdyż są kompilowane i optymalizowane w ten sam sposób

UDF 2

   Głównym argumentem przeciwko korzystaniu z UDF może być zmniejszenie wydajności, przy niewłaściwym ich użyciu Jakakolwiek funkcja, która ma być użytq w każdym wierszu w warunku WHERE na pewno pogorszy (i to chyba znacznie) wydajność.

Trzy typy UDF    Funkcje skalarne zwracające pojedynczą wartość Funkcje „Inline” o wartościach tabelarycznych. Podobne do widoków Wielo –liniowe funkcje o wartościach tabelarycznych, tworzące zbiór wyników przy pomocy kodu

UDF 3

 Funkcje skalarne  Wartość jest zwracana przez polecenie RETURN  Muszą być deterministyczne – dla tych samych parametrów zwracać tą samą wartość (nie można więc korzystać z newid(), rand() )  Nie mogą modyfikować bazy    Nie mogą zwracać wartości typu blob, text

,

ntext

,

timestamp

,

image ani wartości typu tabelarycznego czy typu kursora.

Nie mogą zawierać TRY

. . .

CATCH ani RAISERROR

.

Mogą wywoływać inne UDFy lub też same siebie(aż do 32 poziomu zagnieżdżenia).

UDF 4

  Szablon funkcji skalarnej: 

CREATE F

U

NCTION

FunctionName (InputParameters)

RET

U

RNS

DataType AS BEGIN; Code; RETURN Expression; END;  Parametry wejścia muszą określać również typ, może być podana wartość domyślna Przykład:  CREATE FUNCTION dbo.fsMultiply (@A INT, @B INT

= 3

) RETURNS INT AS BEGIN; RETURN @A * @B; END; go SELECT dbo.fsMultiply (3,4), dbo.fsMultiply (7,

DEFA

U

LT

);

UDF 5

  Funkcje typu inline Składnia  CREATE FUNCTION FunctionName (InputParameters) RETURNS

Table

AS RETURN (

Select Statement

);   Pełnią rolę podobną do widoków ale mogą mieć parametry Przykład:  USE OrderProcessingSystem; GO CREATE FUNCTION ppinline1(@custcode as int) RETURNS TABLE AS RETURN( SELECT * FROM orders o JOIN products p ON o.product=p.code

WHERE CustomerAccount=@custcode); GO SELECT * FROM ppinline1(4504);

UDF 6

  Wieloliniowe funkcje tabelaryczne Składania:  CREATE FUNCTION FunctionName (InputParamenters)

RETURNS @TableName TABLE (Columns)

AS BEGIN; -- kod, który wypełni tabelę RETURN; END;

UDF 7

 Przykład:  CREATE FUNCTION ppmulti1() RETURNS @pp1 TABLE ( ca int, kwota money) AS BEGIN INSERT @pp1 SELECT CustomerAccount, sum(StoreSales) FROM orders group by CustomerAccount; -- tutaj moze być wiele instrukcji SQLa; RETURN END; GO  SELECT * FROM ppmulti1()

UDF 8

 Skorelowanie UDF  CREATE FUNCTION ppmulti2( @AC as int) RETURNS @pp1 TABLE ( AccountNumber int, kwota money) AS BEGIN IF @AC IS NULL INSERT @pp1 SELECT CustomerAccount, sum(StoreSales) FROM orders group by CustomerAccount; ELSE INSERT @pp1 SELECT CustomerAccount, sum(StoreSales) FROM orders WHERE CustomerAccount=@AC GROUP BY CustomerAccount; RETURN END; GO

UDF 8

 SELECT * FROM ppmulti2(5224) ORDER BY AccountNumber DESC   SELECT c.AccountNumber, Firstname, LastName, kwota FROM Customers C CROSS APPLY ppmulti2(C.AccountNumber);    Wiązanie schematu (schema binding!) Jeśli w definicji funkcji użyjemy opcji  WITH SCHEMA BINDING To nie będziemy mogli usuwać tabel do których odnosi się dana funkcja, a nawet nie będzie można modyfikować tabel, a przynajmniej tej części ich struktury do której odnosi się funkcja.

Wyzwalacze (triggers)

   Wyzwalacz

trigger

jest poleceniem, które jest wykonywane automatycznie jako dodatkowy skutek modyfikacji bazy danych.

Aby zaprojektować wyzwalacz musimy:   określić „czas” i „warunki” w jakich wyzwalacz ma zostać aktywowany określić działania wykonywane przez ten wyzwalacz.

Wyzwalacze wprowadzono do standardu dopiero w SQL-1999, ale w wielu implementacjach istniały już znacznie wcześniej.

Wyzwalacze 2



Ogólna postać:

 CREATE TRIGGER BEFORE | AFTER | INSTEAD OF // optional FOR EACH ROW // optional WHEN () // optional gdzie mogą być: INSERT ON R DELETE ON R UPDATE [OF A1, A2, ..., An] ON R AFTER są najbardziej użyteczne i powszechne. Pozostałe generują problemy i nie zaleca się ich używania (Widom)

Wyzwalacze (triggers)

   : jak zwykle : sekwencja poleceń SQL FOR EACH ROW (/ FOR EACH STATEMENT)   Jeśli obecne wykonuje wyzwalacz raz dla każdej zmienianej krotki. Jeśli nie ma to wykonuje dla każdej instrukcji ( for each statement )   Terminologia: "row-level" kontra "statement-level" W każdym przypadku wyzwalacz wykonuje się po tym jak polecenie się wykona (after statement completes).

Wyzwalacze (triggers)

 : REFERENCING AS AS , itd. może być:  OLD TABLE – poprzednimi wartościami usuniętych lub uaktualnionych krotek, poziom wierszy lub poziom poleceń, DELETE lub UPDATE    NEW TABLE poprzednimi wartościami usuniętych lub uaktualnionych krotek, poziom wierzy lub poziom poleceń, INSERT lub UPDATE OLD ROW – poprzednia wartość usunietej lub uaktualnionej krotki, tylko poziom wierszy, DELETE lub UPDATE NEW ROW poprzednia wartość watawionej lub uaktualnionej krotki, tylko poziom wierszy, INSERT lub UPDATE

Wyzwalacze (przykłady)

     Jeśli wstawiana jest krotka do tabeli Aplikacja dla kandydata z oceną >3.9 i IQ>150 do UMK, ustaw decyzję na tak. CREATE TRIGGER AutoAccept AFTER INSERT ON Aplikacja REFERENCING NEW ROW AS NewApp ( FOR EACH ROW WHEN NewApp.miejsce = ‘UMK' AND 3.9 < (SELECT ocena FROM Student WHERE ID = NewApp.ID) AND 150 < (SELECT IQ FROM Student WHERE ID = NewApp.ID) ) UPDATE Aplikacja SET decyzja = ‘T' WHERE ID = NewApp.ID AND miejsce = NewApp.miejsce AND data = NewApp.data

Wyzwalacze (przykłady)

     To samo ale bez FOR EACH ROW. CREATE TRIGGER AutoAccept AFTER INSERT ON Aplikacja REFERENCING NEW TABLE AS NewApps UPDATE Aplikacja SET decyzja = ‘T' WHERE ((ID,miejsce,data) IN (SELECT ID,miejsce,data FROM NewApps) and NewApp.location = ‘UMK' AND 3.9 < (SELECT ocena FROM Student WHERE ID = NewApp.ID) AND 150 < (SELECT IQ FROM Student WHERE ID = NewApp.ID))

Inny przykład wyzwalacza

  Przypuśćmy, że zamiast pozwalać na ujemne stany na rachunkach bank prowadzi następującą politykę    ustala stan rachunku na zero otwiera kredyt z kwotą równą debetowi nadaje kredytowi numer taki sam jak numer rachunku na którym zrobiono debet Warunkiem wykonania wyzwalacza jest zmiana relacji

rachunek

powodująca, że

stan

przyjmuje wartość ujemną.

Przykład wyzwalacza SQL:1999

create trigger debet_trigger after update on rachunek referencing new row as nrow

for each row

when (nrow.stan < 0)

begin atomic

insert into kredytobiorca (select klient_numer, rachunek_numer from depozytor where nrow.rachunek_numer =

depozytor.rachunek_numer

); insert into kredyt values (

nrow.rachunek_numer, nrow.oddzial_nazwa, -nrow.stan

); update rachunek set stan = 0 where rachunek.rachunek_numer = nrow.rachunek_numer;

end

Wyzwalacze: zdarzenia i akcje w SQL

    Zdarzenie wyzwalającym może być

insert

,

delete

lub

update

Wyzwalacze przy zmianie krotki mogą być ograniczone do określonych atrybutów (stan)  Np. create trigger debet_trigger after update of stan on

rachunek

Można dowoływać się zarówno do wartości przed jak i po modyfikacji 

referencing old row as

: w przypadki usuwania i zmiany 

referencing new row as :

w przypadku wstawiania i zmiany Wyzwalacze mogą być aktywowane przed i po zdarzeniu co może służyć jako dodatkowe więzy. Np. zmienić spacje na null.

create trigger setnull_trigger before update on r referencing new row as nrow

for each row

when nrow.telefon

_numer = ‘ ‘

set nrow.telefon_numer =

null

Różne poziomy „wyzwalania”

 Zamiast wykonywać osobne działanie dla każdego wiersza można wykonać pojedyncze działanie dla wszystkich wierszy podlegających tej transakcji  Używamy

for each statement

zamiast

for each row

  Używamy

table referencing old table

albo

referencing new

aby odwoływać się do tymczasowych tabel (

transition tables

) zawierających zmodyfikowane wiersze Warto stosować w sytuacjach, gdy mamy zmienić dużą liczbę wierszy

Działania zewnętrzne

   Czasami chcemy aby wyzwalacze były aktywowane z zewnątrz  Np.

wykonanie zamówienia produktu , którego ilość w hurtowni znacznie zmalała, włączenie się alarmu, Wyzwalacze nie mogą być wykorzystane do bezpośredniej implementacji działania świata zewnętrznego, ale!  Wyzwalacze mogą być wykorzystane do zapisania w osobnej tabeli działań, które mają być podjęte  Np.

Możemy posiadać proces, który w sposób ciągły analizuję tabelę, przeprowadza działanie zapisane w tabeli i następnie usuwa działanie z tabeli Złóżmy, że hurtownia posiada następujące tabele 

zapasy(produkt, poziom):

Ile tego mamy w hurtowni   

minpoziom(produkt, poziom) :

Jaki jest poziom mimalny produktu

ponow_zam(produkt, liczba):

Ile powinniśmy zamówić jednorazowo

zamowienia(produkt, liczba) :

Zamówienia do wykonania wykonuje je proces zewnętrzny w stosunku do bazy danych

Działania zewnętrzne (cd.)

create trigger zamow_trigger after update of liczba on zapasy referencing old row as orow , new row as nrow

for each row

when nrow.poziom < = ( select poziom from minpoziom where minpoziom.produkt = orow.produkt ) and orow.poziom > (

select

poziom from minpoziom where minpoziom.produkt = orow.produkt )

begin

insert into zamowienia ( select produkt, liczba from ponow_zam where ponow_zam.produkt = orow.produkt )

end

Wyzwalacze w MS-SQL

  CREATE TRIGGER [schema_name.]trigger_name ON {table_name | view_name} [WITH dml_trigger_option [,…]] {FOR | AFTER | INSTEAD OF} { [INSERT] [,] [UPDATE] [,] [DELETE]} } [WITH APPEND] {AS sql_statement | EXTERNAL NAME method_name Klauzula WITH może zawierać dwie różne opcje:   WITH ENCRYPTION wskazuje, że kod T-SQL wyzwalacza powinien być ukryty lub zakodowany WITH EXECUTE AS `nazwa użytkownika”

Wyzwalacze w MS-SQL

create trigger debet_trigger on rachunek

for update as

if inserted.stan < 0

begin

insert into kredytobiorca ( select klient_numer,rachunek_numer from depozytor ,

inserted where inserted

.

rachunek_numer = depozytor.rachunek_numer

) insert into kredyt values (

inserted

.

rachunek_numer

,

inserted

.

oddzial_nazwa

, –

inserted

.stan) update rachunek set stan = 0

end

from rachunek ,

inserted

where rachunek.rachunek_numer =

inserted

.

rachunek_numer

    Nie ma before , zamiast after Slowo kluczowe AS użyto on update rozpoczyna opis działania Zamiast warunku when użyto if inserted, deleted zamiast referencing new/old table

Kiedy nie należy używać wyzwalaczy

    Dawniej wyzwalaczy używano do  obsługi danych podsumowujących (np. całkowita pensja w każdym dziale)  Replikacji bazy danych poprzez zapisywanie zmian do specjalnych relacji ( bazy danych.

change

lub

delta

) i stosowanie osobnego procesu zewnętrznego do zastosowania tych zmian na kopii Dzisiaj robimy to lepiej:  dane podsumowujące obsługujemy poprzez tzw. widoki zmaterializowane  Bazy danych posiadają wbudowane mechanizmy replikacji Zamiast wyzwalaczy stosuje się „enkapsulację” (pojęcie z języków obiektowych)   Definiuje się metody zmieniające dane Działania przeprowadza się jako część tych metod Wyzwalacz może wywołać kolejny wyzwalacz (zapętlenie!)

Wyzwalacze –

Zalety

    Wyzwalcze dostarczają alternatywnego sposobu sprawdzania spójności Wyzwalacz może wychwycić błędy w logice biznesowej na poziomie bazy danych.

Wyzwalacz dostarcza alternatywnego sposobu wykonania zadania w kolejce. Nie trzeba czekać na kolejkę zadań aby wykonać zadanie. Można je wykonać przed lub po zmianach w tabelach bazy danych.

Wyzwalacz jest bardzo przydatny, gdy używamy go do sprawdzanie zmian w tabelach bazy danych.

Wyzwalacze -

Wady

    Wyzwalacz może dostarczyć tylko rozszerzonej walidacji, nie może zastąpić innych walidacji. Niektóre proste walidacje mogą być wykonywane na poziomie aplikacji. Na przykład, mona sprawdzić dane wprowadzane po stronie klienta przy pomocy javascriptlub po stronie serwera przy pomocy PHP lub ASP.NET.

Wyzwalacze wykonują się „niewidzialnie” dla klienta, który łączy się z serwerem bazy danych, tym samym trudno jest wywnioskować co się zdarzyło po stronie serwera. Wyzwalacze wykonują się przy każdej modyfikacji tabeli, zwiększa obciążenie bazy danych i spowalnia system.

Wyzwalacze czy procedury składowane? Zależnie od sytuacji, ale zasadą może być, że jeśli nie mona czegoś zrobić przy pomocy procedury składowanej, powinniśmy użyć wyzwalacza.

Procedury składowane

Bezpieczeństwo



Bezpieczeństwo

– zabezpieczenie przed próbami kradzieży lub modyfikacji danych.

 Poziom bazy danych   Mechanizmy autoryzacji i autentykacji, które pozwalają określonym użytkownikom na dostęp tylko do odpowiednich danych Omawiamy głównie autoryzację   Poziom systemu operacyjnego  Tzw. su systemu operacyjnego mogą zrobić prawie wszystko z bazą danych! Dlatego wymagane są doskonałe zabezpieczenia na tym poziomie Poziom sieci – musimy używać szyfrowania aby zapobiec:  podsłuchowi (nieautoryzowanemu czytaniu komunikatów)  maskaradzie ( udawaniu użytkownika autoryzowanego, przykłady)

Bezpieczeństwo (cd.)

  Poziom fizyczny   Fizyczny dostęp do komputerów (kłódka i klucz) Klęski żywiołowe (wichury, powodzie, pożary, ...)  Odzyskiwanie danych !

Poziom ludzki   Czy użytkownicy nie „sprzedają” dostępu do danych Muszą znać podstawowe zasady wyboru bezpiecznych haseł (nie może to być imię żony i liczba dzieci ani żadne obsceniczne słowo – Seksmisja!!!)

Autoryzacja

Zakresy autoryzacji na częściach bazy danych (por. MySQL) :    

Read

– może przeglądać dane.

Insert

– może wstawiać nowe dane ale nie może zmieniać istniejących.

Update

– może zmieniać ale nie może usuwać.

Delete

– może usuwać.

Zakresy autoryzacji dla schematów bazy danych 

Index

– tworzy i usuwa indeksy.

  

Resources

– może tworzyć nowe relacje.

Alteration

– może modyfikować schematy relacji (zmieniać atrybuty).

Drop

– może usuwać relacje.

Nadawanie uprawnień

     Przekazywanie uprawnień od jednego użytkownika do innego może być reprezentowane przy pomocy grafu .

Węzły tego grafu przedstawiają użytkowników.

Wierzchołkiem grafu jest zawsze administrator bazy (DBA).

Graf dla nadawania uprawnień

update

Linia

U i update

 na

U

j , mówi, że użytkownik

loan

użytkownikowi

U j

.

U

i na tabeli

kredyt

.

nadał uprawnienia

Graf nadawania uprawnień

   

Wymagania

: Wszystkie krawędzie grafu muszą być częścią ścieżki mającej początek na DBA Jeśli DBA odwoła uprawnienia użytkownikowi  Uprawnienia muszą być odebrane uprawnień

U

4 gdyż

U

1 :

U

1 nie ma już  Uprawnienia nie mogą być odebrane również uprawnienia nadane przez

U

2

U

5 gdyż posiada on Nie może być ścieżek, które nie mają połączenia z DBA:   DBA nadaje uprawnienia

U

7

U

7 nadaje uprawnienia

U

8 

U

8 nadaje uprawnienia

U

7  DBA usuwa uprawnienia

U

7 Uprawnienia

U

7 dla

U

8 oraz

U

8 dla

U

7 nie ma już ścieżki od DBA ani do

U

7 muszą zostać usunięte bo ani do

U

8 .

Określanie autoryzacji w SQL

    Polecenie

grant

uprawnień jest używane do przekazania (nadania)

grant

< lista uprawnień>

on

to

< lista użytkowników> < lista użytkowników > ma postać:  identyfikator użytkownika  

public

, co nadaje uprawnienia wszystkim użytkownikom rola ( o rolach później) Nadanie uprawnień do widoku nie implikuje uprawnień do relacji, na których widok jest zbudowany.

Nadający uprawnienia musi posiadać nadawane uprawnienia lub być administratorem bazy danych.

Uprawnienia w SQL

    

select :

pozwala na odczyt danych z relacji, jak również na wykonywanie kwerend z wykorzystaniem widoków  Przykład: nadaj użytkownikom

U

1 ,

U

2 , oraz

select

do relacji

oddzial

:

U

3 uprawnienia grant select on oddzial to U 1

, U

2

, U

3

insert

: uprawnienia do wstawiania krotek

update

: uprawnienia do zmiany wartości atrybutów przy pomocy polecenia SQL update

delete

: uprawnienia do usuwania krotek w relacji

all privileges

: wszystkie dopuszczalne uprawnienia

Uprawnienie do nadawania uprawnień



with grant option

użytkownikom. : pozwala aby użytkownik posiadający uprawnienia mógł przekazywać te uprawnienia innym  Przykład: grant select on oddzial to U

1

with grant option

Odbieranie uprawnień w SQL

      Polecenie

revoke

odbiera uprawnienia.

revoke

< lista uprawnień>

on

from

< lista użytkowników> Przykład: revoke select on oddzial from U

1 , U 2 , U 3

< lista przywilejów> może składać się z jednego słowa takim przypadku odbieramy wszystkie przywileje.

all.

W Jeśli zawiera tracą uprawnienia.

public,

wszyscy użytkownicy, którym nie nadano tego przywileju indywidualnie Można zachować uprawnienia po ich odwołaniu jeżeli nadało je dwóch różnych użytkowników a tylko jeden je odwołał.

Wszystkie przywileje, które zależą od odbieranego są również odbierane.

Ograniczenia autoryzacji w SQL

  SQL nie pozwala na autoryzację na poziomie krotek   Np. nie można spowodować aby student widział tylko krotki odpowiadające swoim danym (

to robimy przy pomocy widoków

) Wraz z rozwojem sieciowych baz danych pojawiają się nowe problemy, gdyż większość użytkowników takich baz posiada jeden (ten sam) identyfikator Zadanie autoryzacji w powyższych przykładach przenosi się na programy aplikacyjne, tzn. poza SQL.

 Zaleta: Szczegółowe rozróżnienie autoryzacji, takie jak dostęp do indywidualnych krotek może być implementowane na poziomie aplikacji zewnętrznej  Wada: autoryzacja spada na programistę, łatwo o błędy np. dziury w systemie zabezpieczeń

Historia zmian

   Historia zmian je wykonał.

– zapis wszystkich zmian (insert/delet/update) na bazie danych razem z informacją kto, kiedy i gdzie ( z jakiego IP) Korzysta się z niej aby wyśledzić odpowiedzialnych za wprowadzenie błędnych danych.

Można je zaimplementować przy pomocy wyzwalaczy ale wiele baz danych posiada wbudowane odpowiednie narzędzia.

Role

    Role “ról” pozwalają na definiowanie zestawu przywilejów dla grupy użytkowników poprzez tworzenie odpowiednich Przywileje można nadawać i odpierać rolom tak samo jak użytkownikom Role rolom mogą być przypisane użytkownikom a także innym SQL:1999 zawiera pojęcie ról create role urzednik create role menadzer

grant select on

oddzial to urzednik grant update (stan) on rachunek to urzednik grant all privileges on rachunek to menadzer grant urzednik to mendzer grant urzednik to alicja, bolek grant menadzer to czeslaw

„Zanurzony” (

Embedded

) SQL

   Standard SQL definiuje zanurzenie SQLa w szeregu standardowych językach programowania takich jak C, Java czy Cobol.

Język do którego wprowadza się polecenia SQL nazywamy

językiem gospodarza (host language)

, a struktury języka udostępnione w ten sposób nazywamy zanurzeniem SQL (embedded SQL) Polecenie

EXEC SQL

używane jest do identyfikowania zanurzonego SQLa przez tzw. preprocesor EXEC SQL END_EXEC Ale w niektórych językach może być nieco inaczej np w Javie: # SQL { …. };

Przykład

 Z poziomu języka gospodarza znajdź nazwy i miasta klientów z kwotą większą niż zmienna suma na jakimkolwiek rachunku.

 Określamy kwerendę SQL i deklarujemy dla niej

cursor

EXEC SQL declare c cursor for select depozytor.klient_nazwisko, klient_miasto from depozytor, klient, rachunek where depozytor.klient_nazwisko = klient.klient_nazwisko and depozytor rachunek_numer =

rachunek.rachunek_numer

and rachunek.stan > :suma END_EXEC

Zanurzony SQL (Cd.)

    Polecenie

open

powoduje wykonanie kwerendy EXEC SQL open c END_EXEC Polecenie

fetch

powoduje, że wartości pojedynczej krotki zostają umieszczone w zmiennych języka gospodarza.

EXEC SQL fetch c into :

cn, :cc

END_EXEC Powtarzanie tej komendy „wyciąga” kolejne krotki z wyniku kwerendy W ramach języka istnieje tzw. obszar komunikacyjny SQLCA i zmienne, które przyjmują odpowiednie wartości przy określonych zdarzeniach, np. zmienna SQLSTATE przyjmuje wartość ‘02000’ jeśli w wyniku kwerendy nie ma już dalszych krotek.

Polecenie

close

powoduje zamknięcie przez bazę danych tymczasowej relacji przechowującej wynik kwerendy .

EXEC SQL close c END_EXEC W Javie jest trochę inaczej (bardziej naturalnie)

Modyfikacje poprzez kursor

  Możemy zmodyfikować bazę z poziomu zanurzonego SQL. Jeżeli chcemy do każdego rachunku w oddziale Toruń dodać 100 zł to najpierw wykonujemy polecenie:

declare

c

cursor for select

*

from

rachunek

where

oddzial_nazwa

for update

= ‘Toruń’ A potem pobieramy kolejne rekordy (fetch) i po każdym pobraniu wykonujemy polecenie

update

rachunek

set

stan = stan

+ 100

where current of

c

ODBC i JDBC

    API (application-program interface) dla programów komunikujących się z serwerami baz danych Aplikacje odwołują się do funkcji API aby   Połączyć się z bazą danych Wysłać polecenia SQL do serwera baz danych  Pobrać krotki wyniku (jedna po drugiej i zapisać je w zmiennych programu) ODBC (Open Database Connectivity) działa z językami C, C++, C#, raz Visual Basic JDBC (Java Database Connectivity) współpracuje Javą

ODBC

  Standard Open DataBase Connectivity (ODBC)  jest standardem dla aplikacji do komunikowanie się z serwerem bazy danych  po to aby    otworzyć połączenie z bazą, wykonywać zapytania i modyfikować bazę danych, pobierać wyniki zapytań.

Aplikacje takie jak GUI, arkusze kalkulacyjne, ... mogą korzystać z ODBC

ODBC (cd.)

   Każdy system bazodanowy „współpracujący z” ODBC dostarcza sterownika (biblioteki) która musi być „linkowana” z programem klienckim.

Kiedy program klienta wywołuje ODBC API, kod w bibliotece komunikuje się z serwerem aby wykonać żądaną operację i zwrócić jej wynik. Połączenie z bazą danych otwiera SQLConnect(). Parametrami są :  uchwyt połączenia,    serwer z którym chcemy się połączyć id użytkownika, hasło

Przykład kodu ODBC

 int ODBCexample() { RETCODE error; HENV env; /* environment */ HDBC conn; /* database connection */ SQLAllocEnv(&env); SQLAllocConnect(env, &conn); SQLConnect(conn, „ferm.fizyka.umk.pl", SQL_NTS, „pp", SQL_NTS, „pppasswd", SQL_NTS); { …. zrób coś w bazie danych … } } SQLDisconnect(conn); SQLFreeConnect(conn); SQLFreeEnv(env);

Funkcje i procedury

  SQL:1999 wprowadza funkcje i procedury   Funkcje/procedury mogą być pisane w SQL lub zewnętrznym języku programowania Niektóre systemy bazodanowe dostarczają funkcji o wartościach typu tabelarycznego (wynikiem funkcji jest relacja)   SQL:1999 dostarcza podstawowego zestawu poleceń typowych dla zwykłych języków programowania pętle, konstrukcja if-then-else, przypisania Wiele systemów baz danych posiada własne rozwiązania niezgodne z tym standardem