of 38 /38
SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

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

Embed Size (px)

Citation preview

Page 1: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

1

SQL SQL Catalogues, Vues, Autorisations,

Déclencheurs

Witold Litwin

Page 2: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

2

SQL cataloguesSQL 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

Page 3: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

3

SYSTABLESSYSTABLES

NAME CREATOR COLCOUNT REMARKS ...

S Witold 4 fournisseur ...

SP Witold 5 fourniture ...

P Witold 5 piece ...

Page 4: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

4

SYSCOLUMNSSYSCOLUMNS

NAME TBNAME CREATOR COLTYPE REMARKS...

S# S Witold CHAR oid ...

SNAME S Witold CHAR nom ...

STATUS S Witold SMALLINT ...

... ... ... ... ...

P# SP Witold CHAR cleetrangere

...

... ... ... ... ...

Page 5: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

5

SYSINDEXESSYSINDEXES

NAME TBNAME CREATOR ...

XS S Witold ...

XSC S Witold ...

XSP SP Witold ...

• Pas de champ REMARKS

Page 6: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

6

Requêtes aux cataloguesRequêtes aux catalogues

select TBNAME from SYSCOLUMNSwhere NAME = ‘S#’

select COUNT(*) from SYSTABLESwhere CREATOR = ‘Witold’

Mises a jour:– par le système seulement

pourquoi ?

Page 7: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

7

Commande Commande COMMENTCOMMENT

Pour MAJ les champs REMARKScomment on table S is 'fournisseur’

comment on column SP.P# is ‘clé étrangère’

Õ Exercice:créer un commentaire sur SYSINDEXES

Page 8: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

8

SQL VuesSQL 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

Page 9: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

9

Image d'usager d'un SGBD relationnel SQL

V1 V2

T1T2 T3

T4

Virtuel

Réel

Physique F1arbre-B

F2arbre-B

F1hash.

V3V4

Page 10: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

10

Définition d’une vue SQLDéfinition d’une vue SQL

CREATE VIEW v-nom [(colonne [, colonne]...)] AS sous-requête [ WITH CHECK OPTION ] ;

CREATE VIEW good_suppliers ASSELECT S#, STATUS, CITYFROM S WHERE STATUS > 15;

15

15Val. réelles Val. virtuel.

Page 11: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

11

Vues SQLVues 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# ;

Page 12: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

12

Vues SQLVues SQL

Vue avec une fonction agrégat:

CREATE VIEW PQ (P#, TOTQTY) AS SELECT P#, SUM (QTY)

FROM SPGROUP BY P# ;

Page 13: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

13

Mises à jour de vuesMises à 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

Page 14: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

14

CHECK OPTIONCHECK OPTION

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

Page 15: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

15

CHECK OPTIONCHECK OPTION

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

S1...15

S2...20

S5...17

S7...5

S1...5

S2...20

S5...17

S7...5

Sans CHECK OPTION

Page 16: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

16

CHECK OPTIONCHECK OPTION

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

S1...15

S2...20

S5...17

S7...5

Avec CHECK OPTION

S1...15

S2...20

S5...17

S7...5

Abort

Page 17: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

17

GRANTGRANT

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 ;

Page 18: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

18

REVOKEREVOKE

Révoque l'autorisation de GRANTREVOKE 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

Page 19: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

19

Confidentialité dans SQLConfidentialité 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 !

Page 20: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

20

MsAccessMsAccess

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

Page 21: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

21

DéclencheursDéclencheurs(triggers)(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

Page 22: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

22

DéclencheursDéclencheurs(Exemple)(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

Page 23: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

23

DéclencheursDéclencheurs(Exemple)(Exemple)

Une requête arrive :

SDelete FromS Where S# = 'S1' ;

Page 24: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

24

DéclencheursDéclencheurs(Exemple)(Exemple)

la requête définie dans D s'exécute

S SP

action déclenchéepar D

Delete FromS Where S# = 'S1' ;

Delete FromSP Where S# = 'S1' ;

Page 25: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

25

DéclencheursDéclencheurs(Résultat)(Résultat)

S SP

action déclenchéepar D

Page 26: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

26

DéclencheursDéclencheurs(Syntaxe SQL-Server, Sybase)(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

Page 27: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

27

DéclencheursDéclencheurs(triggers)(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

Page 28: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

28

Exemples (SQL-Server)Exemples (SQL-Server) Create Trigger S1 on S

for delete asif (select count (*) from SP, deleted d where SP.S# = d.S#) > 0then

BeginRollbackPrint "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 */

Page 29: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

29

Exemples (SQL-Server)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 */

Page 30: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

30

Exemples (SQL-Server)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'

Page 31: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

31

Exemples (SQL-Server)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 smalint

Select @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)rollback

endelseif 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 transaction

endgo

Page 32: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

32

DéclencheursDéclencheurs(Dangers etc.)(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 ?

Page 33: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

33

DéclencheursDéclencheurs(action au-delà d'une BD)(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

Page 34: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

34

DéclencheursDéclencheurs(Exemple OK)(Exemple OK)

email

"envoies ordre

d'achat urgent

de :P# à :S

#"

Page 35: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

35

DéclencheursDéclencheurs(Ex. MAJ et Danger potentiel)(Ex. MAJ et Danger potentiel)

email

"envoies ordre

d'achat urgent

de :P# à :S

#"

Boucle infiniedevient possible

Page 36: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

36

ExercisesExercises

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

Page 37: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

37FINFIN

Manuels de DB2, SQL- Server Sybase, Oracle, MsAcces..

"SQL-2 . P. Delmal.

DeBoeck Université, 1995.

Pour en savoir plusPour en savoir plus

Page 38: 1 SQL SQL Catalogues, Vues, Autorisations, Déclencheurs Witold Litwin

38