Ceci est une ancienne révision du document !
À rendre pour le 17/10/2010 23h30 via spiral, envoyer un fichier SQL commenté
Accéder à oracle comme indiqué dans l'aide.
Utiliser le code suivant pour mettre en place le schéma de base de données utilisé pour ce TP.
-- Schema CREATE TABLE membre(id INTEGER PRIMARY KEY, nom varchar2(40), email varchar2(100)); CREATE TABLE salle(id INTEGER PRIMARY KEY, moderateur INTEGER REFERENCES membre(id), titre varchar2(100)); CREATE TABLE message(id INTEGER PRIMARY KEY, auteur INTEGER REFERENCES membre(id), salle INTEGER REFERENCES salle(id), parent INTEGER, date_envoi TIMESTAMP, titre varchar2(100), corps varchar2(1000)); ALTER TABLE message ADD CONSTRAINT fk_parent FOREIGN KEY (parent) REFERENCES message(id); -- Donnees INSERT INTO membre VALUES(1,'Toto','toto@blagues.com'); INSERT INTO membre VALUES(2,'Titi','titi@canaries.com'); INSERT INTO membre VALUES(3,'Rominet','sylvestre@eating-birds.com'); INSERT INTO membre VALUES(4,'Bugs Bunny','bugs@whatsupdoc.com'); INSERT INTO salle VALUES(1,1,'Bonne humeur'); INSERT INTO salle VALUES(2,NULL,'Dessins animés'); INSERT INTO message VALUES(1,1,1,NULL,sysdate,'Toto à l''école','C''est Toto à l''école. Sa prof lui demande: '||CHR(10)||' - Toto quelle est la 5 ème lettre de l''alphabet? '||CHR(10)||' - Euh ?'); INSERT INTO message VALUES(2,3,2,NULL,sysdate,'Les plus beaux personnages','Ce sont les chats, évidement !'); INSERT INTO message VALUES(3,2,2,2,sysdate,'Re: Les plus beaux personnages','Non, ce sont les canaris !'); INSERT INTO message VALUES(4,3,2,3,sysdate,'Re: Les plus beaux personnages','Les chats !'); INSERT INTO message VALUES(5,2,2,4,sysdate,'Re: Les plus beaux personnages','Grand mère, à l''aide !!!'); INSERT INTO message VALUES(6,4,2,2,sysdate,'Re: Les plus beaux personnages','Moi, je dis que ce sont les lapins !'); commit;
Oracle fournit des fonctions SQL qui permettent de gérer du XML en tant que type de donnée (XMLType
).
La fonction XMLElement
permet de créer un élément XML. L'expression suivante permet de créer un élément nommé prix
contenant un noeud texte dont la valeur est donnée par l'attribut prix_vente
XMLElement(name "prix", prix_vente)
On peut en particulier l'utiliser dans une requête:
SELECT XMLElement(name "prix", prix_vente) FROM ventes
Il est possible de spécifier plusieurs enfants pour un élément en les séparant par des virgules. Il est également possible d'imbriquer les appels à XMLElement afin de construire un morceau de document XML plus complexe.
On se replace dans le contexte d'une base de données sur le forum de discussions. Ecrire une requête qui pour chaque message génère un élément XML “message
” avec un élément “contenu
” incluant le corps du message et un élément “date
” incluant la date du message.
<color black/#ffcccc>Correction:</color>
SELECT xmlelement(name "message", xmlelement(name "contenu", corps), xmlelement(name "date", date_envoi)) FROM message;
La fonction XMLAttributes
permet d'ajouter des attributs à un élément. Le nom par défaut utilisé pour chaque attribut XML est le nom de l'attribut SQL dont on a pris la valeur. Il est également possible d'utiliser la notation AS un_nom
afin de changer le nom de l'attribut XML (de manière similaire au nom des colonnes dans un SELECT). L'exemple suivant crée des éléments “article
” avec un attribut XML “ident
” donné par l'attribut SQL ident
, un attribut XML “prix
” donné par l'attribut SQL prix_vente
et un contenu texte donné par l'attribut SQL description
:
XMLElement(name "article", XMLAttributes(ident,prix_vente AS "prix"), description)
Ecrire une requête qui pour chaque message génère un élément “message
” avec un attribut “id_message
”, un attribut “date
”, un attribut “auteur
” ayant pour valeur l'identifiant de l'auteur et enfin le corps du message comme contenu.
<color black/#ffcccc>Correction:</color>
SELECT xmlelement(name "message", xmlattributes(id AS "id_message", date_envoi AS "date", auteur), corps) FROM message;
Que peut-on remarquer sur la casse des nom d'attributs? Modifier la requête (si besoin) pour que tous les noms soient en minuscules.
<color black/#ffcccc>Correction:</color>
SELECT xmlelement(name "message", xmlattributes(id AS "id_message", date_envoi AS "date", auteur AS "auteur"), corps) FROM message;
La fonction XMLForest
crée pour chacun de ses arguments un élément (dont on peut optionnellement préciser le nom avec un AS
) qui contient du texte correspondant à la valeur calculée pour cet argument. Par exemple:
XMLElement(name "article", XMLAttributes(ident,prix_vente AS "prix"), XMLForest(nom_article AS "nom",description)
Modifier la requête précédente en utilisant cette fonction pour mettre le corps du message dans un élément “CORPS
” et l'email de l'auteur dans un élément “mailAuteur
”.
<color black/#ffcccc>Correction:</color>
SELECT xmlelement(name "message", xmlattributes(message.id AS "id_message", date_envoi AS "date", auteur AS "auteur"), xmlforest(corps, email AS "mailAuteur")) AS resultat FROM message, utilisateur WHERE auteur=utilisateur.id;
La fonction XMLAgg
est une fonction d'agrégation pour le type XML (donc typiquement à utiliser en conjonction avec un GROUP BY
). Son effet est de mettre les unes à la suite des autres les différentes valeurs de l'expression passée en argument. La requête suivante illustre son fonctionnement:
SELECT XMLElement(name "departement", XMLAttributes(deptno), XMLAgg(XMLElement(name "employe",ename))) AS RESULTAT FROM scott.emp GROUP BY deptno;
Modifier la requête précédente pour ajouter à chaque élément message un élément “reponse
” avec comme contenu l'identifiant de la réponse, et cela pour chacune des réponses à ce message (on ne traitera que les messages ayant des réponses).
<color black/#ffcccc>Correction:</color>
SELECT xmlelement(name "message", xmlattributes(m1.id AS "id_message", m1.date_envoi AS "date", m1.auteur AS "auteur"), xmlforest(m1.corps, email AS "mailAuteur"), XMLAgg(XMLElement(name "reponse", XMLAttributes(m2.id AS "id_reponse"))) ) AS resultat FROM message m1, message m2, utilisateur WHERE m1.id = m2.parent AND m1.auteur=utilisateur.id GROUP BY m1.id, m1.date_envoi, m1.auteur, m1.corps, email;
Remplacer la jointure sur les messages par un LEFT OUTER JOIN. Que peut-on remarquer pour les messages n'ayant pas de réponse?
<color black/#ffcccc>Correction:</color>
SELECT xmlelement(name "message", xmlattributes(m1.id AS "id_message", m1.date_envoi AS "date", m1.auteur AS "auteur"), xmlforest(m1.corps, email AS "mailAuteur"), XMLAgg(XMLElement(name "reponse", XMLAttributes(m2.id AS "id_reponse"))) ) AS xml FROM message m1 LEFT OUTER JOIN message m2 ON m1.id = m2.parent, utilisateur WHERE m1.auteur=utilisateur.id GROUP BY m1.id, m1.date_envoi, m1.auteur, m1.corps, email;
<color black/#ffcccc>Les messages sans réponse possèdent un élément <reponse> vide.</color>
Créer une vue qui à chaque identifiant de message associe sa représentation XML telle que décrite ci-dessus.
<color black/#ffcccc>Correction:</color>
CREATE OR REPLACE VIEW message_id_xml AS SELECT m1.id, xmlelement(name "message", xmlattributes(m1.id AS "id_message", m1.date_envoi AS "date", m1.auteur AS "auteur"), xmlforest(m1.corps, email AS "mailAuteur"), XMLAgg(XMLElement(name "reponse", XMLAttributes(m2.id AS "id_reponse"))) ) AS xml FROM message m1 LEFT OUTER JOIN message m2 ON m1.id = m2.parent, membre WHERE m1.auteur=membre.id GROUP BY m1.id, m1.date_envoi, m1.auteur, m1.corps, email;
Créer une vue qui crée un document XML contenant l'ensemble des messages sous la forme donnée précédemment.
<color black/#ffcccc>Correction:</color>
CREATE OR REPLACE VIEW messages_xml AS SELECT xmlelement(name "messages", xmlagg(xml)) AS messages FROM message_id_xml;
<color black/#ffcccc> On peut tester avec:</color>
SELECT (messages) FROM messages_xml
La fonction XMLQuery
permet d'exécuter une requête XQuery afin de générer un morceau de document XML. Il est possible de passer à la requête XQuery certaines valeurs XML construites à partir de la requête SQL via l'utilisation du mot clé PASSING
. L'exemple suivant illustre l'utilisation de cette fonction:
SELECT XMLQuery('<objet>{$id}</objet>' PASSING XMLElement(name "some_id", id_obj) AS "id" RETURNING CONTENT) FROM mes_objets;
Mettre au point une requête XQuery permettant de transformer une représentation XML des messages en une représentation HTML ayant l'aspect suivant:
Message 25 de bob@aol.com:
Bonjour, c'est moi
Réponses: message 45 message 49
<li> <p>Message 25 de <a href="mailto:bob@aol.com">bob@aol.com</a>:</p> <p>Bonjour, c'est moi</p> <p>Réponses: message 45 message 49</p> </li>
<color black/#ffcccc>Correction:</color>
SELECT XMLQuery(' let $msg := $m/message, $mail := $msg/mailAuteur/text() return <li> <p>Message {string($msg/@id_message)} de <a href="mailto:{$mail}">{$mail}</a></p> <p>{$msg/CORPS/text()}</p> <p>Réponses: { for $rep in $msg/reponse[@id_reponse] return concat("message ",$rep/@id_reponse) } </p> </li> ' PASSING xml AS "m" RETURNING CONTENT) FROM message_id_xml;
Faire une vue de la requête précédente, puis créer une autre vue qui va générer le document HTML entier.
<color black/#ffcccc>Correction:
Pour créer la première vue, il suffit d'ajouter create or replace view message_html as au début de la requête et d'ajouter AS ITEM
après RETURNING CONTENT)
. Le code pour la deuxième vue est: </color>
CREATE OR REPLACE VIEW tous_messages_html AS SELECT xmlelement(name "html", xmlelement(name "body", xmlelement(name "ul", xmlagg(item) ) ) ) AS html_code FROM message_html;
<color black/#ffcccc>Pour tester:</color>
SELECT (html_code) FROM tous_messages_html