1 / 51

V ues

V ues. Vues. Table deriveé des tables existants (table de base) Accès adapte a besoin d'application (couche externe) Stockage des requêtes Protection des donneés Utilization Recherche comme table de base MAJ largement limiteé. CREATE VIEW.

edward-powell
Download Presentation

V ues

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Vues

  2. Vues • Table deriveé des tables existants (table de base) • Accès adapte a besoin d'application (couche externe) • Stockage des requêtes • Protection des donneés • Utilization • Recherche comme table de base • MAJ largement limiteé

  3. CREATE VIEW CREATE VIEWcapitale_olympique(année, pays, capitale) ASSELECT année, JO.pays, capitale FROM JO, Pays WHERE JO.pays = Pays.nom

  4. Utilisation des Vues • SELECTDISTINCT capitale FROMcapitale_olympique ; • UPDATE capitale_olympiqueSET capitale = 'Berlin' WHERE pays = 'RDA';

  5. MAJ des vues • Les modifications sont possibles si la requête ne contient pas de • Jointure • Opérateur d’agrégation • Attribut calculé dans le SELECT • UNION, INTERSECT, DIFFERENCE, DISTINCT, GROUP BY, ORDER BY

  6. Exemple • CREATE VIEW pays_europenneAS SELECT * FROM PaysWHERE continent = 'Europe' ; • UPDATE pays_europenneSET capitale = 'Berlin' WHERE nom = 'RDA'; Le changement est propagé vers la table de base!

  7. DELETE ? Exemple – MAJ impossible • CREATE VIEW comparaison AS SELECT P1.nom, P1.population, P2.nom, P2.populationFROM Pays P1, Pays P2WHERE P1.population > P2.population ; Il n'existe aucune possibilité d‘éliminer seulementce tuple et de maintenir la consistance avec les tables de base

  8. Vue Materialisée • Normalement, seulement la définition de la vue est stockée • Vue materialisée • Crée une novelle table contenant les résultats de la requête utilisée dans la définition de la vue • Si les tables de base changent: MAJ de la vue matérialisée CREATE MATERIALIZED VIEW capitale_olympique(année, pays, capitale)REFRESH FAST ON COMMITASSELECT année, JO.pays, capitale FROM JO, Pays WHERE JO.pays = Pays.nom ;

  9. Commandes sur les vues • CREATE [OR REPLACE] VIEW nom_vue [ ( nv_nom_col)*] AS requête [WITH READ ONLY] [WITH CHECK OPTION] ; • OR REPLACE: remplace une vue existante • WITH READ ONLY: interdit les MAJ • WITH CHECK OPTION: insertions et modifications seulement si le tuple résultant est sélectionné par la vue • DROP VIEW nom_vue; • RENAME nom_vue TO nv_nom; • ALTER VIEW nom_vue COMPILE; • Recalcule une vue

  10. Maintenir les contraintes d'intégrité

  11. Contraintes d'intégrité • Plusieurs possibilités pour les maintenir • Définition des tables • Assertions • Triggers • Vues

  12. Rappel • Contrainte sur une colonne (contrainte-col) • [NOT] NULL • UNIQUE • PRIMARY KEY • Contraintes sur une table (contrainte-table) • UNIQUE (nom-col)* • PRIMARY KEY (nom-col)* • FOREIGN KEY (nom-col)* REFERENCES nom_table [(nom-col)*]

  13. Identifiant externe • Maintenir l’intégrité référentielle • Lors de suppressions ou modifications d'un tuple dont l'identifiant est référencé par un identifiant externe • Exemple de problème • Pays(nom, capitale, population, surface)JO(lieu, pays, année) • Suppression d'un tuple de Pays:DELETE FROM Pays WHERE nom='Grece';

  14. Maintenir integrite referentielle • Possibilités • Interdire l'opération • Mettre les valeurs de l'identifiant externe à NULL ou à une valeur défaut • En cas du suppression: éliminer aussi le tuple avec l'identifiant externe • En cas du modification: modifier également la valeur de l'identifiant externe

  15. Exemple CREATE TABLE JO ( année DECIMAL(4,0), lieu VARCHAR(15), pays VARCHAR(20), PRIMARY KEY (année), FOREIGN KEY (pays) REFERENCES Pays (nom) ON DELETE SET NULL ON UPDATE CASCADE );

  16. Contraintes complexes • Maintenir les conditions logique sur les tuples • Exemple • Pays(nom, capitale, population, surface)JO(lieu, pays, année) • JO.année  1896 • 3 jeux olympiques au maximum dans le même pays

  17. Assertion simple • Spécification de la condition pendant la définition de la table • La condition est contrôlée avec chaque modification ou insertion • Exemple CREATE TABLE JO ( année DECIMAL(4,0), lieu VARCHAR(15), pays VARCHAR(20), PRIMARY KEY (année), FOREIGN KEY (pays) REFERENCES Pays (nom) ON DELETE SET NULL ON UPDATE CASCADE CHECK(année  1896) );

  18. Assertion avec requête CREATE TABLE JO ( année DECIMAL(4,0), lieu VARCHAR(15), pays VARCHAR(20), PRIMARY KEY (année), FOREIGN KEY (pays) REFERENCES Pays (nom) ON DELETE SET NULL ON UPDATE CASCADE, CHECK(année  1896) CHECK(3  ALL (SELECT COUNT(*) FROM JO GROUP BY pays) );

  19. Assertion globale • Condition sur plusiers tables • Definition independant d'une table • Exemple: deux jeux olympiques consécutifs ne se déroulent pas sur le même continent • CREATE ASSERTION JO_consecutive CHECKNOT EXISTS (SELECT * FROM JO JO1,JO JO2, Pays P1, Pays P2WHERE JO1.pays=P1.nom AND JO2.pays=P2.nom AND JO1.année=JO2.année+4 AND P1.continent=P2.continent); Les assertions complexes sont très coûteuses !

  20. Triggers

  21. Trigger • Concept de base de données active • Exécuter une opération suite à un changement survenu dans la base de données • Maintenir les contraintes d'intégrité • Logging et auditing des changements • Calculer des valeurs dérivées • Maintenir des règles de business • Maintenir des données répliquées et des vues complexes materialisées etc.

  22. Evénement – Condition - Action • Un trigger est activé par un événement • Insertion, suppression ou modification sur une table • Si le trigger est activé, une condition est évaluée • Prédicat ou requête • Une requête est vraie si le résultat n'est pas vide • Si la condition est vraie l'action est exécutée • Insertion, suppression ou modification de la base de données (ou programme externe)

  23. événement condition action Exemple • CREATE TRIGGER MAJ_Pays AFTER UPDATE population ON Pays FOR EACH ROW WHEN surface > 0 BEGIN UPDATE Pays SET pop_avg = population/surface; END;

  24. Exécution d'un trigger • Avant, après ou au lieu de l'événement • AFTER | BEFORE | INSTEAD • Invocation de l'événement une fois par table ou pour tous les tuples affectés • [FOR EACH ROW] • Association avec une transaction • A la fin de la transaction en cours • Transaction séparée

  25. Références aux valeurs modifiées • CREATE TRIGGER MAJ_Log AFTER UPDATE ON PaysFOR EACH ROW WHEN (new.surface <> old.surface) BEGIN INSERT INTO Pays_log (nom, surface) VALUES (:new.nom, 'surface_changée'); END;

  26. Syntaxe des triggers (SQL 1999) CREATE TRIGGER [OR REPLACE] nom-du-trigger BEFORE | AFTER | INSTEAD OF INSERT | UPDATE | DELETE OF noms-d'attributs ON nom-table [FOR EACH ROW] WHEN (condition) <bloc PL/SQL ou programme Java ou C >

  27. Attention • Les triggers sont très utiles et puissants • Mais: une utilisation prudente est nécessaire • Attention aux • Invocations de cascade de triggers • Utilisations excessives des ressources • Difficultés pour prévoir les conséquences

  28. Embedded SQL

  29. Embedded SQL - but • Inclure des commandes SQL dans un programme (Java, C, Cobol, Ada, Fortran, Pascal, …) • compléter SQL avec la puissance d’expressivité d’un langage de programmation • Interactions avec l’utilisateur, systèmes périphériques, … • Exemples • calculs, requêtes récursives, conversions, …

  30. Principe d’utilisation • Ecrire dans un LP quelconque un programme normal contenant en plus des ordres SQL • Le langage qui “accueille” les ordres SGBD:le langagehôte • Les ordres accueillis: ordresinclus • Gérer les éventuels problèmes de communication entre le programme et le SGBD • Transmission des données entre le langage hôte et SQL

  31. Ordres inclus • Inclusion • Syntaxe dépend du langage hôte • En général: EXEC SQL <expression SQL> END-EXEC • En C: EXEC SQL <expression SQL> ; • En Java: #SQL { <expression SQL> }; • Exécution • Précompilateur SQL détecte l’expressions SQL incluse • remplacée par un appel à une fonction de la bibliothèque de communication SGBD - langage hôte • transmise au SGBD lors de l’exécution du programme par cette fonction

  32. Transmission des données • Echange entre le SGBD et le programme réalisé au travers d’une zone de communication • Zone de communication = ensemble de variables • Variables d’échange utilisateur (hôtes) • données (insertions, interrogation) • information du programme pour le SGBD • Variables spéciales SGBD • informations du SGBD pour le programme

  33. Transmission de données Exemple – requête (langage hôte C) … EXEC SQL BEGIN DECLARE SECTION; char titre[15]; /* titre obtenu par un utilisateur */ real sal; /* salaire */ EXEC SQL END DECLARE SECTION; … /* Code (omis) pour obtenir un titre */ EXEC SQL SELECT Salaire INTO:sal FROM Compensation WHERE Titre = :titre; /* Code (omis) pour imprimer le resultat */ … Déclaration des variables communes

  34. Transmission d’un ensemble de données • Le résultat d'une requête est une ensemble de données • Le langage hôte ne connaît pas le concept d'ensemble • Incompatibilité des langages ("Language impendance mismatch") • Tampon (Cursor) • zone mémoire nommée du programme à laquelle une requête est associée • taille dynamique réglée à l’exécution • sert à contenir les n-uplets résultant de requête

  35. Avant 1. tuple 1. tuple . . . dernier tuple Apres dernier tuple Tampon • Principe d’utilisation • Déclaration du tampon (DECLARE) • Remplissage du tampon en une seule fois par exécution de la requête (OPEN) • Récupération des n-uplets du tampon un par un (FETCH) • Libération du tampon (CLOSE)

  36. Utilisation d'une tampon … EXEC SQL BEGIN DECLARE SECTION; <Déclaration des variables communes> EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE <cursor-name> [options] CURSOR FOR <query> [options]; EXEC SQL OPEN <cursor-name> ; while(condition) { EXEC SQL FETCH FROM <cursor-name> INTO <shared-variable(s)> ; if(tuple existe) traiter le tuple else break } EXEC SQL CLOSE <cursor-name> ; …

  37. Exemple … EXEC SQL DECLARE tamp1 CURSOR FOR SELECT nom, prenom FROM Etudiant WHERE cle = :cle; EXEC SQL OPEN tamp1 EXEC SQL FETCH FROM tamp1 INTO :nom, :prénom; EXEC SQL CLOSE tamp1; …

  38. Options - Declaration EXEC SQL DECLARE <cursor-name>[INSENSITIVE][SCROLL] CURSOR FOR <query>[ORDER BY <attribute(s)>][FOR READ ONLY]; • INSENSITIVE • le tampon est insensible aux changements dans la relation durant l’ouverture • SCROLL • utilisation des options FETCH • ORDER BY • trie l'ordre des tuples • FOR READ ONLY • l'accès ne change pas le contenu des tuples

  39. Options - Accés EXEC SQL FETCH [FROM] [ NEXT | PRIOR | FIRST | LAST | RELATIVE[+|-]n | ABSOLUTE[+|-]n ]<cursor-name> [INTO <var1>,…, <varn>]

  40. Termination des SQL statements • Unité de travail (= transaction) commence avec le premier ordre SQL ou avec COMMIT/ROLLBACK • COMMIT • Si on veut rendre le résultat de la transaction permanent • Syntaxe: EXEC SQL COMMIT; • ROLLBACK • Si on veut rejeter le résultat de la transaction • Syntax : EXEC SQL ROLLBACK;

  41. Gestion des exceptions : WHENEVER • Gestion automatique des erreurs et warnings • teste tous les évènements qui suivent • En général, un WHENEVER avant le premier ordre SQL exécutable • Syntaxe EXEC SQL WHENEVER <évènement> <action>;

  42. WHENEVER • Evénement • SQLERROR, SQLWARNING • NOT FOUND : si pas de n-uplet trouvé • Action • CONTINUE, STOP (Rollback) • GOTO branch (à éviter), DO f() • DO BREAK et DO CONTINUE (à utiliser pour des boucles)

  43. Exemple – mise a jour #include <stdio.h> EXEC SQL INCLUDE SQLCA; main() { EXEC SQL WHENEVER SQLERROR GOTO error: EXEC SQL CONNECT TO Company; EXEC SQL BEGIN DECLARE SECTION; int pno1, pno2; /* two project numbers */ int amount; /* amount to be transferred */ EXEC SQL END DECLARE SECTION; /* Code (omitted) to read the project numbers and amount */ EXEC SQL UPDATE Project SET Budget = Budget + :amount WHERE Pno = :pno2; EXEC SQL UPDATE Project SET Budget = Budget - :amount WHERE Pno = :pno1; EXEC SQL COMMIT RELEASE; return(0); error: printf(“update failed, sqlcode = %ld\n”, SQLCODE); EXEC SQL ROLLBACK RELEASE; return(-1); }

  44. SQL dynamique • Si l'ordre SQL exact n'est pas connu lors du développement de l'application • SQL n’autorise pas les variables tables ou colonnes • Possibilité d’avoir le texte de l’ordre SQL entier contenu dans une variable • Syntaxe: EXEC SQL EXECUTE IMMEDIATE<Texte_ordre_SQL>;

  45. Exemple EXEC SQL BEGIN DECLARE SECTION; char req[] = “INSERT INTO EMP VALUES(‘E13’, ‘Jean Blanc’,...)”; EXEC SQL END DECLARE SECTION; EXEC SQL EXECUTE IMMEDIATE :req; • Limitations • :reqne peut pas retourner de résultat • :reqne peut pas contenir de paramètres • Chaque fois que :req est exécutée, elle est également compilée  overhead important

  46. Exécution préparée • Forme générale EXEC SQL PREPARE stmt FROM :string • Règle • :stringpeut retourner des résultats d'une requête • :stringpeut contenir des paramètres • stmtn’est pas une variable hôte, mais un identificateur de l’ordre utilisé par le pré-processeur

  47. Exemple EXEC SQL BEGIN DECLARE SECTION ; char tup[] = “INSERT INTO EMP VALUES(‘E13’, ‘Jean Blanc’,...)”; EXEC SQL END DECLARE SECTION; EXEC SQL PREPARE S1 FROM :tup; EXEC SQL EXECUTE S1; ... EXEC SQL EXECUTE S1;

  48. Paramétrisation • Utiliser des caractères joker - ? – là où les chaînes de caractères peuvent apparaître • "INSERT INTO Emp VALUES(?, ?, ?, ?)" • pas à la place d’un nom de relation, de colonne, etc • Utiliser des variables hôtes dans la clause USING pour indiquer les valeurs des paramètres • EXEC SQL EXECUTE S1 USING :eno, :ename, :title, :city • USING ne peut pas être employé avec EXECUTE IMMEDIATE

  49. Exemple … EXEC SQL BEGIN DECLARE SECTION; char tup[] = “INSERT INTO Emp VALUES (?,?,?,?); char eno[3], enom[15], titre[10], ville[12]; EXEC SQL END DECLARE SECTION; EXEC SQL PREPARE S1 FROM :tup; /* obtenir les valeurs de :eno, etc. */ EXEC SQL EXECUTE S1 USING :eno, :ename, :titre, :ville; …

  50. Tampons dynamiques • Si le résultatd'une requête est une ensemble, alors utiliser des tampons dynamiques • Définire des tampons dynamiques similaires à leurs contreparties statiques, mais utiliser • USING pour fournir les paramètres de la rêquete EXEC SQL DECLARE <cursor-name> CURSOR FOR stmt; EXEC SQL OPEN <cursor-name> USING :var1 [,…,:varn]; EXEC SQL FETCH <cursor-name> INTO :out1 [,…,:outk]; EXEC SQL CLOSE <cursor-name> ;

More Related