Basi di Dati e Sistemi Informativi Il Linguaggio SQL Home page del corso: difelice/dbsi
36
Basi di Dati e Sistemi Informativi Il Linguaggio SQL Home page del corso: http://www.cs.unibo.it/~difelice/dbsi/
-
Upload
tore-belloni -
Category
Documents
-
view
217 -
download
1
Transcript of Basi di Dati e Sistemi Informativi Il Linguaggio SQL Home page del corso: difelice/dbsi
Basi di Dati e Sistemi
Informativi
Il Linguaggio SQLHome page del corso:
http://www.cs.unibo.it/~difelice/dbsi/
Il Linguaggio SQL
SQL (Structured Query Language) e’ il linguaggio di riferimento per le basi di dati relazionali.
Diverse versioni del linguaggio: SQL-86 Costrutti base SQL-89 Integrita’ referenziale SQL-92 (SQL2) Modello relazionale, struttura a
livelli SQL:1999 (SQL3) Modello ad oggetti SQL:2003 (SQL3) Nuove parti: SQL/JRT, SQL/XML SQL:2006 (SQL3) Estensione di SQL/XML SQL:2008 (SQL3) Lievi aggiunte
Il Linguaggio SQL
Oltre ad i costrutti base di SQL2 visti fin qui, esistono molti costrutti avanzati (i) definiti in SQL3 e/o (ii) dipendenti dallo specifico DBMS.
Procedure (Stored Procedures)
Trigger
Permessi
Transazioni
Il Linguaggio SQL
Stored Procedures Frammenti di codice SQL, con la possibilita’ di specificare un nome, dei parametri in ingresso e dei valori di ritorno.
Procedure ModificaStipendio (:MatricolaNew: varchar(20), : StipendioNew smallint)
update Impiegatiset Stipendio= : StipendioNewwhere Matricola = :MatricolaN
Ogni DBMS offre estensioni procedurali differenti …
Il Linguaggio SQL
DB
TABELLE
APPLICAZIONEESTERNA
SQL
DATI
DB
TABELLE
APPLICAZIONEESTERNA
NOME PROCEDURA+ PARAMETRI
DATI
PROCEDURE
MODELLO senzaSTORED PROCEDURE
MODELLO conSTORED PROCEDURE
Efficienza Maggiore espressivita’ …
Il Linguaggio SQL
Esempio: definizione di funzioni in MySQL:
CREATE FUNCTION function_nameRETURNS type_return… List of SQL routine statements
CREATE FUNCTION echo(s CHAR(20))RETURNS CHAR(50)RETURN(s)
mysql>> SELECT echo(“Hello”);
Il Linguaggio SQL
Esempio: definizione di funzioni in PostgreSQL:
CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement) RETURNS anyelement AS $$DECLARE
result ALIAS for $0BEGIN
result:=v1 + v2 +v3;RETURN result
END
Il Linguaggio SQL
Generalmente, le estensioni procedurali consentono di:
Creare funzioni e procedure trigger-based.
Aggiungere strutture di controllo al linguaggio SQL (es. cicli, strutture condizionali if then else, etc).
Dichiarare variabili e tipi di dato user-defined.
Definire funzioni avanzate ed ottimizzate, che sono ritenute “sicure” dal DBMS.
Il Linguaggio SQL
Ogni DBMS offre una sua estensione procedurale:
PL/SQL Linguaggio di Oracle Server
SQL PL Linguaggio di IBM DB2
PL/pgSQL Linguaggio di Postgres
…
Il Linguaggio SQL
Costrutti procedurali in PostgreSQL:
Costrutti condizionaliif <Condizione> then … else … endif
IF user_id <> 0 THEN UPDATE USERS
SET USERS.email=v_email WHERE (USERS.user_id = user_id)
Il Linguaggio SQL
Costrutti procedurali in PostgreSQL:
Costrutti iterativi (while)while (Expression) LOOP
statementsEND LOOP
WHILE ncycle>0 LOOPUPDATE SALARYSET SALARY.amount=SALARY.amount -100ncycle:=ncycle -1;
END LOOP
Il Linguaggio SQL
Costrutti procedurali in PostgreSQL:
Costrutti iterativi (for)For record_or_row IN query LOOP
statementsEND LOOP
FOR Studente IN SELECT * FROM STUDENTI LOOPUPDATE ESAMISET Voto=30WHERE (Corso=“Basi di Dati”)
END LOOP
Il Linguaggio SQL
Oltre ad i costrutti base di SQL2 visti fin qui, esistono molti costrutti avanzati (i) definiti in SQL3 e/o (ii) dipendenti dallo specifico DBMS.
Procedure (Stored Procedures)
Trigger
Permessi
Transazioni
Il Linguaggio SQL
Nome Codice Quantita
Xbee Radio Shield 123 3
Arduino Uno Shield 5565 2
Arduino Ethernet 14354 1
ORDINE
Vorrei implementare un comportamento del tipo:
Ogni volta che viene inserito/modificato un nuovo ordine con piu’ di 5 quantita’ nel DB viene inviata una mail al Titolare dell’azienda ..
Il Linguaggio SQL
Nome Codice
Quantita
Xbee Radio Shield
123 3
Arduino Uno Shield
5565 2
Arduino Ethernet 14354 1
ORDINE
Vorrei implementare un comportamento del tipo:
Ogni volta in cui l’utente fa un’ordine, si aggiorna la tabella Magazzino, e nel caso non ci siano piu’ prodotti di quel tipo, si aggiorni anche la tabella Acquisti …
MAGAZZINO
Codice Quantita
123 0
5565 2
14354 1
ACQUISTO
Codice
Q.a Data
123 3 1/2/2012
Il Linguaggio SQL
Trigger (o regole attive) meccanismi di gestione della base di dati basati sul paradigma ECA (Evento/Condizione/Azione).
Evento: primitive per la manipolazione dei dati (insert, delete, update)
Condizione: Predicato booleano Azione: sequenza di istruzioni SQL,
talvolta procedure SQL specifiche del DBMS.
Il Linguaggio SQL
Trigger (o regole attive) meccanismi di gestione della base di dati basati sul paradigma ECA (Evento/Condizione/Azione).
A che servono i Trigger?
1. Garantire il soddifacimento di vincoli di integrita’ referenziale, e/o specificare meccanismi di reazione ad hoc in caso di violazione dei vincoli!
Nome Codice Crediti
Basi di dati 6464 12
Programmazione
1213 12
Sistemi Operativi
1455 6
Corso Studente Voto
0121 4324235245
30L
1213 4324235245
25
1213 9854456565
18
CORSI ESAMI
Q. Che accade se un valore nella tabella esterna viene cancellato o viene modificato?
A. Il vincolo di integrita’ referenziale nella tabella interna potrebbe non essere piu’ valido! Cosa fare?
Il Linguaggio SQL
Il Linguaggio SQL
Trigger (o regole attive) meccanismi di gestione della base di dati basati sul paradigma ECA (Evento/Condizione/Azione).
A che servono i Trigger?
2. Specificare regole aziendali (business rules), ossia vincoli generici sulo schema della base di dati
(es. Un impiegato non puo’ avere un aumento di stipendio superiore al 10%, pena annullamento della transazione).
Il Linguaggio SQL
Create trigger NomeModo Evento on Tabella[referencing Referenza][for each Livello][when (IstruzioneSQL)]Istruzione/ProceduraSQL
Trigger (o regole attive) meccanismi di gestione della base di dati basati sul paradigma ECA (Evento/Condizione/Azione).SINTASSI SQL3
Il Linguaggio SQL
Modo before/after Evento insert/delete/update Referencing qui possono essere
inserite variabili globali… Livello row (Il trigger agisce a livello di
righe/ statement (Il trigger agisce globalmente a livello di tabella)
I trigger possono avere modalita’ immediata oppure differita.
Il Linguaggio SQL
CREATE TRIGGER CHECKAUMENTOBEFORE UPDATE OF CONTO ON IMPIEGATOFOR EACH ROWWHEN (NEW.STIPENDIO > OLD.STIPENDIO * 1.2)SET NEW.STIPENDIO=OLD.STIPENDIO * 1.2
Esempio di Trigger in SQL3
Modo e’ definito come before.Evento e’ definito come update.
Livello e’ definito come row.
Il Linguaggio SQL
Oltre ad i costrutti base di SQL2 visti fin qui, esistono molti costrutti avanzati (i) definiti in SQL3 e/o (ii) dipendenti dallo specifico DBMS.
Procedure (Stored Procedures)
Trigger
Permessi
Transazioni
Il Linguaggio SQL
SQL2/SQL3 prevede meccanismi di controllo di accesso alle risorse del DB (tabelle, viste, domini, etc).
Di default, ogni risorsa appartiene all’utente che l’ha definita … Su ciascuna risorsa sono definiti dei privilegi (grant): insert/update/delete tabelle/viste select tabelle/viste references tabelle/attributi usage domini
Il Linguaggio SQL
Il comando grant consente di assegnare privilegi su una certa risorsa ad utenti specifici.
grant Privilegio on Risorsa/e to Utente/i [with grant option]
L’opzione with grant option consente di propagare il privilegio ad altri utenti del sistema…
grant select on Impiegati to Marco with grant optiongrant delete on Impiegati, Salari to Marco, Michele
Il Linguaggio SQL
Il comando revoke consente di revocare privilegi su una certa risorsa ad utenti specifici.
revoke Privilegio on Risorsa/e from Utente/i [cascade|restrict]
L’opzione cascade agisce ricorsivamente sui privilegi eventualmente concessi da quell’utente …
revoke select on Impiegati to Marco cascaderevoke delete on Impiegati, Salari to Marco, Michele
Il Linguaggio SQL
Oltre ad i costrutti base di SQL2 visti fin qui, esistono molti costrutti avanzati (i) definiti in SQL3 e/o (ii) dipendenti dallo specifico DBMS.
Procedure (Stored Procedures)
Trigger
Permessi
Transazioni
Il Linguaggio SQL
Le transazioni rappresentano unita’ di lavoro elementare che modificano il contenuto di una base di dati.
start transactionupdate SalariImpiegatiset conto=conto*1.2where (CodiceImpiegato = 123)commit work
Le transazionisono compresetra una starttransaction eduna commit/rollback
Il Linguaggio SQL
Le transazioni rappresentano unita’ di lavoro elementare che modificano il contenuto di una base di dati.
start transactionupdate SalariImpiegatiset conto=conto-10where (CodiceImpiegato = 123)if conto >0 commit work;else rollback work
Le transazionisono compresetra una starttransaction eduna commit/rollaback
Il Linguaggio SQL
Ogni DBMS deve garantire le seguenti proprieta’ delle transazioni (proprieta’ acide):Atomicita’ La transazione deve essere eseguita con la regola del “tutto o niente”Consistenza La transazione deve lasciare il DB in uno stato consistente, eventuali vincoli di integrita’ non devono essere violati.
Isolamento L’esecuzione di una transazione deve essere indipendente dalle altre.
Persistenza L’effetto di una transazione che ha fatto commit work non deve essere perso.
Il Linguaggio SQL
DB
Gestione delle transazioni
Gestione della concorrenza
Gestione dell’affidabilita’
Gestore dell’affidabilita’ garantisce atomicita’ e persistenza… COME? Usando log e checkpoint.
Gestore della concorrenza garantisce l’isolamento in caso di esecuzione concorrente di piu’ transazioni.
Il Linguaggio SQL
T1= Read(x); x=x+1; Write(x); Commit WorkT2= Read(x); x=x+1; Write(x); Commit Work
In un sistema reale, le transazioni vengono eseguite in parallelo per ragioni di efficienza / scalabilita’ …
… Tuttavia, l’esecuzione concorrente determina un insieme di problematiche che devono essere gestite …
Se x=3, al termine delle due transazioni x vale 5 (esecuzione sequenziale) … cosa accade in caso di esecuzione concorrente?
Il Linguaggio SQL
Problema1: Perdita di Aggiornamento
Transazione1 (T1) Transazione2 (T2)
Read(x)
x=x+1
Read(x)
x=x+1
Write(x)
Commit work
Write(x)Commit work
T1 scrive 4
T2 scrive 4
Il Linguaggio SQL
Problema2: Lettura sporca
Transazione1 (T1) Transazione2 (T2)
Read(x)
x=x+1
Write(x)
Read(x)
Commit work
Rollback work
T2 legge 4!
Il Linguaggio SQL
Problema3: Letture incosistenti
Transazione1 (T1) Transazione2 (T2)
Read(x)
Read(x)
x=x+1
Write(x)
Commit work
Read(x)Commit work
T1 legge 3!
T1 legge 4!
Il Linguaggio SQL
Problema4: Aggiornamento Fantasma
Transazione1 (T1) Transazione2 (T2)
Read(x)
Read(y)
Read(y)
y=y-100
Read(z)
z=z+100
Write(y), Write(z)
Commit work
Read(z)s=x+y+z; commit work
Vincolo:x+y+zdeveessere =a 1000
Vincoloviolato!!
