Basi di Dati e Sistemi Informativi

1. Basi di Dati e SistemiInformativi Il Linguaggio SQL Home page del corso: http://www.cs.unibo.it/~difelice/dbsi/

2. Il Linguaggio SQL • SQL (Structured Query Language) e’ illinguaggio di riferimento per le basi di datirelazionali. • Diverse versioni del linguaggio: • SQL-86  Costrutti base • SQL-89  Integrita’ referenziale • SQL-92 (SQL2)  Modellorelazionale, struttura a livelli • SQL:1999 (SQL3)  Modello ad oggetti • SQL:2003 (SQL3)  Nuoveparti: SQL/JRT, SQL/XML • SQL:2006 (SQL3)  Estensione di SQL/XML • SQL:2008 (SQL3)  Lieviaggiunte

3. Il Linguaggio SQL • Oltre ad icostrutti base di SQL2 visti fin qui, esistonomolticostruttiavanzati (i) definiti in SQL3 e/o (ii) dipendentidallospecificoDBMS. • Procedure (Stored Procedures) • Trigger • Permessi • Transazioni

4. Il Linguaggio SQL Stored Procedures  Frammenti di codice SQL, con la possibilita’ di specificare un nome,deiparametri in ingressoe deivalori di ritorno. Procedure ModificaStipendio (:MatricolaNew: varchar(20), : StipendioNewsmallint) update Impiegati set Stipendio= : StipendioNew where Matricola = :MatricolaN Ogni DBMS offreestensioniproceduralidifferenti …

5. Il Linguaggio SQL SQL DB APPLICAZIONE ESTERNA MODELLO senza STORED PROCEDURE TABELLE DATI NOME PROCEDURA + PARAMETRI MODELLO con STORED PROCEDURE DB APPLICAZIONE ESTERNA • Efficienza • Maggiore espressivita’ • … TABELLE DATI PROCEDURE

6. Il Linguaggio SQL Esempio: definizione di funzioniin MySQL: CREATE FUNCTION function_name RETURNS type_return … List of SQL routine statements CREATE FUNCTION echo(s CHAR(20)) RETURNS CHAR(50) RETURN(s) mysql>> SELECT echo(“Hello”);

7. Il Linguaggio SQL Esempio: definizione di funzioniin PostgreSQL: CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement) RETURNS anyelement AS $$ DECLARE result ALIAS for $0 BEGIN result:=v1 + v2 +v3; RETURN result END

8. Il Linguaggio SQL • Generalmente, le estensioniproceduraliconsentono di: • Crearefunzionie procedure trigger-based. • Aggiungerestrutture di controlloal linguaggio SQL (es. cicli, strutturecondizionali if then else, etc). • Dichiararevariabili e tipi di datouser-defined. • Definirefunzioniavanzateedottimizzate, chesonoritenute “sicure” dal DBMS.

9. Il Linguaggio SQL • Ogni DBMS offreunasuaestensioneprocedurale: • PL/SQL  Linguaggio di Oracle Server • SQL PL  Linguaggio di IBM DB2 • PL/pgSQL  Linguaggio di Postgres • …

10. Il Linguaggio SQL • Costruttiproceduraliin PostgreSQL: • Costrutticondizionali • if <Condizione> then … else … endif • IF user_id <> 0 THEN • UPDATE USERS • SET USERS.email=v_email • WHERE (USERS.user_id = user_id)

11. Il Linguaggio SQL • Costruttiproceduraliin PostgreSQL: • Costruttiiterativi (while) • while (Expression) LOOP • statements • END LOOP • WHILE ncycle>0 LOOP • UPDATE SALARY • SET SALARY.amount=SALARY.amount -100 • ncycle:=ncycle -1; • END LOOP

12. Il Linguaggio SQL • Costruttiproceduraliin PostgreSQL: • Costruttiiterativi (for) • For record_or_row IN query LOOP • statements • END LOOP • FOR Studente IN SELECT * FROM STUDENTI LOOP • UPDATE ESAMI • SET Voto=30 • WHERE (Corso=“Basi di Dati”) • END LOOP

13. Il Linguaggio SQL • Oltre ad icostrutti base di SQL2 visti fin qui, esistonomolticostruttiavanzati (i) definiti in SQL3 e/o (ii) dipendentidallospecificoDBMS. • Procedure (Stored Procedures) • Trigger • Permessi • Transazioni

14. Il Linguaggio SQL ORDINE • Vorreiimplementare un comportamento del tipo: • Ognivoltachevieneinserito/modificato un nuovoordine con piu’ di 5 quantita’ nel DB vieneinviatauna mail al Titolaredell’azienda ..

15. Il Linguaggio SQL ORDINE MAGAZZINO ACQUISTO • Vorreiimplementare un comportamento del tipo: • Ognivolta in cui l’utentefaun’ordine, siaggiorna la tabellaMagazzino, e nelcaso non ci sianopiu’ prodotti di queltipo, siaggiornianche la tabellaAcquisti …

16. Il Linguaggio SQL • Trigger (o regoleattive)  meccanismi di gestionedella base di datibasatisulparadigma ECA (Evento/Condizione/Azione). • Evento: primitive per la manipolazionedeidati (insert, delete, update) • Condizione: Predicatobooleano • Azione: sequenza di istruzioni SQL, talvolta procedure SQL specifiche del DBMS.

17. Il Linguaggio SQL • Trigger (o regoleattive)  meccanismi di gestionedella base di datibasatisulparadigma ECA (Evento/Condizione/Azione). • A cheservonoi Trigger? • 1.Garantireilsoddifacimento di vincoli di integrita’ referenziale, e/o specificaremeccanismi di reazione ad hoc in caso di violazionedeivincoli!

18. Il Linguaggio SQL CORSI ESAMI Q. Cheaccade se un valorenellatabellaesternavienecancellato o vienemodificato? A. Il vincolo di integrita’ referenzialenellatabellainternapotrebbe non esserepiu’ valido! Cosa fare?

19. Il Linguaggio SQL • Trigger (o regoleattive)  meccanismi di gestionedella base di datibasatisulparadigma ECA (Evento/Condizione/Azione). • A cheservonoi Trigger? • 2.Specificareregoleaziendali(business rules), ossiavincoligenericisulo schema della base di dati • (es. Un impiegato non puo’ avere un aumento di stipendiosuperiore al 10%, penaannullamentodellatransazione).

20. Il Linguaggio SQL • Trigger (o regoleattive)  meccanismi di gestionedella base di datibasatisulparadigma ECA (Evento/Condizione/Azione). SINTASSI SQL3 Create trigger Nome ModoEvento on Tabella [referencing Referenza] [for each Livello] [when (IstruzioneSQL)] Istruzione/ProceduraSQL

21. Il Linguaggio SQL • Modo  before/after • Evento  insert/delete/update • Referencing  qui possonoessereinseritevariabiliglobali… • Livello  row (Il trigger agisce a livello di righe/ statement (Il trigger agisceglobalmente a livello di tabella) • I trigger possonoaveremodalita’ immediataoppuredifferita.

22. Il Linguaggio SQL • Esempio di Trigger in SQL3 • CREATE TRIGGER CHECKAUMENTO • BEFORE UPDATE OF CONTO ON IMPIEGATO • FOR EACH ROW • WHEN (NEW.STIPENDIO > OLD.STIPENDIO * 1.2) • SET NEW.STIPENDIO=OLD.STIPENDIO * 1.2 • Modo e’ definito come before. • Evento e’ definito come update. • Livello e’ definito come row.

23. Il Linguaggio SQL • Oltre ad icostrutti base di SQL2 visti fin qui, esistonomolticostruttiavanzati (i) definiti in SQL3 e/o (ii) dipendentidallospecificoDBMS. • Procedure (Stored Procedures) • Trigger • Permessi • Transazioni

24. Il Linguaggio SQL • SQL2/SQL3 prevedemeccanismi di controllo di accessoallerisorse del DB (tabelle, viste, domini, etc). • Di default, ognirisorsaappartieneall’utentechel’hadefinita … Su ciascunarisorsasonodefinitideiprivilegi (grant): • insert/update/delete  tabelle/viste • select  tabelle/viste • references tabelle/attributi • usage  domini

25. Il Linguaggio SQL Il comandograntconsente di assegnareprivilegisuunacertarisorsa ad utentispecifici. grant Privilegio on Risorsa/e to Utente/i [with grant option] L’opzionewith grant option consente di propagareilprivilegio ad altriutenti del sistema… grant select on Impiegati to Marco with grant option grant delete on Impiegati, Salari to Marco, Michele

26. Il Linguaggio SQL Il comandorevokeconsente di revocareprivilegisuunacertarisorsa ad utentispecifici. revoke Privilegio on Risorsa/e from Utente/i [cascade|restrict] L’opzionecascade agiscericorsivamente sui privilegieventualmenteconcessi da quell’utente … revoke select on Impiegati to Marco cascade revoke delete on Impiegati, Salari to Marco, Michele

27. Il Linguaggio SQL • Oltre ad icostrutti base di SQL2 visti fin qui, esistonomolticostruttiavanzati (i) definiti in SQL3 e/o (ii) dipendentidallospecificoDBMS. • Procedure (Stored Procedures) • Trigger • Permessi • Transazioni

28. Il Linguaggio SQL Le transazionirappresentanounita’ di lavoroelementarechemodificanoilcontenuto di una base di dati. start transaction update SalariImpiegati set conto=conto*1.2 where (CodiceImpiegato = 123) commit work Le transazioni sonocomprese traunastart transaction ed unacommit/ rollback

29. Il Linguaggio SQL Le transazionirappresentanounita’ di lavoroelementarechemodificanoilcontenuto di una base di dati. start transaction update SalariImpiegati set conto=conto-10 where (CodiceImpiegato = 123) if conto >0 commit work; else rollback work Le transazioni sonocomprese traunastart transaction ed unacommit/ rollaback

30. Il Linguaggio SQL Ogni DBMS devegarantire le seguentiproprieta’ delletransazioni (proprieta’ acide): Atomicita’  La transazionedeveessereeseguita con la regola del “tuttoo niente” Consistenza La transazionedevelasciareil DB in unostatoconsistente, eventualivincoli di integrita’ non devonoessereviolati. Isolamento  L’esecuzione di unatransazionedeveessereindipendentedallealtre. Persistenza  L’effetto di unatransazioneche ha fattocommit work non deveessereperso.

31. Il Linguaggio SQL Gestionedelletransazioni Gestionedellaconcorrenza Gestionedell’affidabilita’ DB Gestoredell’affidabilita’  garantisceatomicita’ e persistenza … COME? Usandolog e checkpoint. Gestoredellaconcorrenza  garantiscel’isolamento in caso di esecuzioneconcorrente di piu’ transazioni.

32. Il Linguaggio SQL In un sistemareale, le transazionivengonoeseguite in parallelo per ragioni di efficienza / scalabilita’ … … Tuttavia, l’esecuzioneconcorrentedetermina un insieme di problematichechedevonoesseregestite … T1= Read(x); x=x+1; Write(x); Commit Work T2= Read(x); x=x+1; Write(x); Commit Work Se x=3, al terminedelle due transazioni x vale 5 (esecuzionesequenziale) … cosaaccade in caso di esecuzioneconcorrente?

33. Il Linguaggio SQL Problema1: Perdita di Aggiornamento T2 scrive 4 T1 scrive 4

34. Il Linguaggio SQL Problema2: Letturasporca T2 legge 4!

35. Il Linguaggio SQL Problema3: Lettureincosistenti T1 legge 3! T1 legge 4!

36. Il Linguaggio SQL Problema4: Aggiornamento Fantasma Vincolo: x+y+z deve essere = a 1000 Vincolo violato!!