SQL Catalogues, Vues, Autorisations, Déclencheurs

1. SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

2. SQL catalogues • Les catalogues SQL sont des tables gérées par le SGBD, dans la metabase • SYSTABLES : une ligne pour chaque table • SYSCOLUMNS : une ligne pour chaque colonne • SYSINDEXES : une ligne pour chaque indexe Base Metabase

3. SYSTABLES

4. SYSCOLUMNS

5. SYSINDEXES • Pas de champ REMARKS

6. Requêtes aux catalogues • select TBNAME from SYSCOLUMNSwhere NAME = ‘S#’ • select COUNT(*) from SYSTABLESwhere CREATOR = ‘Witold’ • Mises a jour: • par le système seulement • pourquoi ?

7. Commande COMMENT • Pour MAJ les champs REMARKS comment on table S is 'fournisseur’ comment on column SP.P# is ‘clé étrangère’ • Exercice:créer un commentaire sur SYSINDEXES

8. SQL Vues • Vue SQL = une table virtuelle, par opposition a une table réelle (de base) • Vue SQL Vue ANSI-SPARC = Base virtuelle • Vue SQL: • Une expression de sélection mémorisée

9. Image d'usager d'un SGBD relationnel SQL V4 V3 V1 V2 Virtuel T1 T2 T3 Réel T4 F1 arbre-B F2 arbre-B F1 hash. Physique

10. Définition d’une vue SQL • CREATE VIEW v-nom [(colonne [, colonne]...)] AS sous-requête [ WITH CHECK OPTION ] ; • CREATE VIEW good_suppliers AS SELECT S#, STATUS, CITY FROM S WHERE STATUS > 15; 15 Val. réelles Val. virtuel. 15

11. Vues SQL • Vue avec une jointure:CREATE VIEW F (f#, fnom, p#, pnom) AS SELECT (S#, SNAME, SP.P#, PNAME)FROM S,SP, PWHERE S.S# = SP.S# AND SP.P# = P.P# ;

12. Vues SQL • Vue avec une fonction agrégat: • CREATE VIEW PQ (P#, TOTQTY) AS SELECT P#, SUM (QTY) FROM SP GROUP BY P# ;

13. Mises à jour de vues • Rarement possibles en SQL: • Impossibles quand la vue : • est une projection sur attributs autres que la clé • contient une jointure • contient une fonction agrégat

14. CHECK OPTION • WITH CHECK OPTION protège contre les disparitions de tuples de vues: UPDATE GOOD_SUPPLIERSSET STATUS = ' 5' WHERE S# = 'S1' ;serait alors rejeté.

15. CHECK OPTION • WITH CHECK OPTION protège contre les disparitions de tuples de vues: UPDATE GOOD_SUPPLIERSSET STATUS = ' 5' WHERE S# = 'S1' ;serait alors rejeté. Sans CHECK OPTION S1...15 S1...5 S2...20 S2...20 S5...17 S5...17 S7...5 S7...5

16. CHECK OPTION • WITH CHECK OPTION protège contre les disparitions de tuples de vues: UPDATE GOOD_SUPPLIERSSET STATUS = ' 5' WHERE S# = 'S1' ;serait alors rejeté. Avec CHECK OPTION S1...15 S1...15 Abort S2...20 S2...20 S5...17 S5...17 S7...5 S7...5

17. GRANT • Le système d'autorisation de SQL GRANT SELECT ON TABLE S TO Witold ; GRANT SELECT, UPDATE (STATUS, CITY) ON TABLE S TO Witold, Edwin ; • Pourrait être aussi DELETE, INSERT GRANT ALL ON TABLE S, P TO Me, You, Him ; GRANT SELECT ON TABLE S TO PUBLIC ; GRANT INDEX ON TABLE S TO Me ; GRANT SELECT ON TABLE S TO You WITH GRANT OPTION ;

18. REVOKE • Révoque l'autorisation de GRANT REVOKE SELECT ON TABLE S FROM you ; • La révocation se propage à travers les autorisations de GRANT OPTION • Peut être impossible en pratique dans le SGBD répartie

19. Confidentialité dans SQL • GRANT & REVOKE c'est un aspect • Vues en est une autre • Les deux sont assez primitifs • On peut passer à travers de plusieurs manières: - branchement sur les lignes - accès aux fichiers... • Les anciennes procédures CODASYL ENCODE DECODE seraient fort utiles dans les SGBD relationnels !

20. MsAccess • La BD peut-être stockée codée • la commande ENCRYPTE/DECRYPTE • Le codage/décodage sont transparents à l'utilisation • Le cryptage en général diminue la taille de la base • Mais, on consomme + de CPU • la différence est en général invisible

21. Déclencheurs(triggers) • Une fonction de définition de données pour assurer les contraintes d'intégrité inter-relationnelles • intégrité référentielle surtout • Une manipulation d'une table déclenche celle d'une autre • une suppression d'un S déclenche celle de tout SP avec un même S# • une insertion dans SP déclenche la recherche dans S d'un tuple avec S# correspondant • et le refus d'insertion en cas d'échec

22. Déclencheurs(Exemple) • On a déclaré pour S le déclencheur D: Create Trigger D on S for Delete asDelete From SP Where SP.S# = Delete.S# ; S

23. Déclencheurs(Exemple) • Une requête arrive : S Delete From S Where S# = 'S1' ;

24. Déclencheurs(Exemple) • la requête définie dans D s'exécute S SP Delete From S Where S# = 'S1' ; Delete From SP Where S# = 'S1' ; action déclenchée par D

25. Déclencheurs(Résultat) S SP action déclenchée par D

26. Déclencheurs(Syntaxe SQL-Server, Sybase) CREATE TRIGGER <nom D> ON <table T> < définition de D> • < définition de D > ::= FOR {INSERT | UPDATE | DELETE},... [WITH ENCRYPTION] AS <programme P > • P a une syntaxe et le lang. de progr. arbitraires • en particulier: • IF UPDATE (<attribut>) [{AND | OR} < attribut >], ... <programme P' > • on peut utiliser le langage Control-of-Flow • IFTHEN ELSE, CASE, WHILE, FOR... • voir SQL-Server ou Sybase • Encryption chiffre le commentaire sur le déclencheur dans dans syscomments

27. Déclencheurs(triggers) • Un déclencheur peut se référer à deux tables temporaires: • inserted • ses tuples sont des copies de ceux insérés ou mis à jour dans T par la requête qui a déclenché D • deleted • Idem pour les tuples deletés et ceux mis à jour avec les valeurs d'avant celle-ci. • Un seul déclencheur par table est permis dans SQL-Server 6.5

28. Exemples (SQL-Server) • Create Trigger S1 on S for delete asif (select count (*) from SP, deleted d where SP.S# = d.S#) > 0then Begin Rollback Print "Ce fournisseur a encore des fournitures" Endelse Print "Fournisseur supprimé"go /* Ce déclencheur suppose le delete d'un seul tuple */ /* Les liens d'intégrité ref. dans MsAccess ne permettent pas une telle action */

29. Exemples (SQL-Server) • Create Trigger Test-avg on SP for update asif update Qtyif (select avg (Qty) from SP, inserted i where SP.p# = i.p# ) < 10 then Begin Print "Il faut reapprovisionner cette pièce" Endelse if (select Qty from SP, inserted i where SP.p# = i.p# and SP.S# = i.S# ) = 0 then Begin Print "Plus de pièce de ce fournisseur" End go /* Ce déclencheur suppose la MAJ d'un seul tuple */ /* A étendre par la recherche d'un autre fournisseur de cette pièce et l'impression du message qu'il y en a ou qu'il y en n'a pas d'un tel fournisseur */

30. Exemples (SQL-Server) • create trigger rappel on Sfor update, delete -- déclencheur doubleas raiserror (50007, 16,10) /* Le message 50007 est un rappel qu'il faut notifier le fournisseur concerné */ • create trigger rappel on cours-FCfor update, delete, insertas xp_sendmail 'Natalie', 'N'oublies pas imprimer la nouvelle liste, merci'

31. Exemples (SQL-Server) • Create trigger employee_insupd on employee for insert, update as Declare @min_lvl tinyint, @max_lvl tinyint, @emp_lvl tinyint, @job_id smalintSelect @min_lvl = min_lvl, @max_lvl = max_lvl, @emp_lvl = i.job_lvl, @job_id = i.job_id From employee e, jobs j, inserted i,Where e.emp_id = i. emp_id and i.job_id = j.job_id if (@job_id = 1) and (emp_lvl <> 10) begin raiserror ('si Job id 1 alors level = 10', 16, -1) rollbackendelseif not (@emp_lvl) between @min_lvl and @max_lvl)begin raiserror ( niveau pour job_id:%d est entre %d et %d.', 16,-1, @job_id, @min_lvl, @max_lvl) rollback transactionendgo

32. Déclencheurs(Dangers etc.) • Les manipulations peuvent s'enchaîner • Un déclencheur peut déclencher un autre • on dit que la BD avec des déclencheurs devient active • les déclencheurs sont alors dits règles • 16 niveaux d'enchaînement au max. • SQL Server 6.5 • Les enchaînements sont difficiles à contrôler • atomicité d'une action déclanchée • oui/non ? • boucles infinies • prévenir/détecter ? • effets imprévus ("side-effects") • prévenir/détecter ?

33. Déclencheurs(action au-delà d'une BD) • On peut définir des déclencheurs d'actions hors la BD • multibases • BD1 -> BD2 -> BD3.... • vers d'autres systèmes • email • Dans certains SGBDs les déclencheurs ne sont pas dans SQL. mais dans un langage 4GL • macros MsAccess

34. Déclencheurs(Exemple OK) email "envoies ordre d'achat urgent de :P# à :S#"

35. Déclencheurs(Ex. MAJ et Danger potentiel) Boucle infinie devient possible email "envoies ordre d'achat urgent de :P# à :S#"

36. Exercises • Test de la DF status -> city dans S • MAJs de plusieurs tuples à la fois dans les ex. précedents • nécessitent le SQL imbriqué • Création d'une table temp Chefs avec les chefs, chefs de chefs... caractérisés par leur E# et Enom), à partir de la table des Employés : • Empl (E#, Enom, Chef#) • on appelle une telle op. fermeturetransitive

37. Pour en savoir plus FIN Manuels de DB2, SQL- Server Sybase, Oracle, MsAcces.. "SQL-2 . P. Delmal. DeBoeck Université, 1995.