SQL Catalogues, Vues, Autorisations, D

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
• Exercicecré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 dune 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 gt 15

15
Val. réelles
Val. virtuel.
15
11
Vues SQL

• Vue avec une jointureCREATE 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 ltnom Dgt ON lttable Tgt lt définition
  de Dgt
• lt définition de D gt FOR INSERT UPDATE
  DELETE,... WITH ENCRYPTION AS ltprogramme P gt
• P a une syntaxe et le lang. de progr. arbitraires
• en particulier
• IF UPDATE (ltattributgt) AND OR lt attribut
  gt, ... ltprogramme P' gt
• 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)
  gt 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 ) lt 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 _at_min_lvl tinyint,
  _at_max_lvl tinyint, _at_emp_lvl tinyint, _at_job_id
  smalintSelect _at_min_lvl min_lvl, _at_max_lvl
  max_lvl, _at_emp_lvl i.job_lvl, _at_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 (_at_job_id 1) and (emp_lvl ltgt 10)
  begin raiserror ('si Job id 1 alors level
  10', 16, -1) rollbackendelseif not (_at_emp_lvl)
  between _at_min_lvl and _at_max_lvl)begin raiserror (
  niveau pour job_idd est entre d et d.',
  16,-1, _at_job_id, _at_min_lvl, _at_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 -gt BD2 -gt 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 -gt 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
FIN
Pour en savoir plus

• Manuels de DB2, SQL- Server Sybase, Oracle,
  MsAcces..
• "SQL-2 . P. Delmal.
• DeBoeck Université, 1995.

38
(No Transcript)