TP SQL/XML
TP Génération de XML via SQL
Ce TP sera réalisé sous psql
(disponible en salle TP Linux). Chaque étudiant se verra attribuer un compte postgresql via tomuss. Voir la documentation de l’accès à PostgreSQL à l’université.
Ce TP n’est pas à rendre. Des questions pourront cependant être posées en contrôle ou en examen sur les techniques abordées dans ce TP.
Introduction
L’objectif de ce TP est de pratiquer la création de (fragments de) documents XML directement à partir de données relationnelles en SQL. Le TP consiste dans un premier temps à lire et comprendre la Documentation PostgreSQL sur les fonctions XML. On pourra aussi au besoin regarder (sans le faire) le TP “Générer du XML dans Oracle”. Dans un deuxième temps, on créera des vues à interroger. Enfin on répondra aux questions dans un fichier de réponses en s’inspirant du TP cité ci-dessus.
Vues relationnelles
Les données relationnelles seront accessibles en créant dans votre compte des accès aux tables arbres de documentation de la base pedago
(attention à changer LoginPostgres et MotDePasse):
-- on crée le serveur "distant", ici vers la base 'pedago'
-- on lève ici les droits d'écritures (que les utilisateurs n'ont pas de toute façon)
CREATE SERVER IF NOT EXISTS ro_local_bd_pedago
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'bd-pedago.univ-lyon1.fr', port '5432', dbname 'pedago', updatable 'false');
-- pour annuler
-- DROP SERVER IF EXISTS ro_local_bd_pedago;
-- on dit que l'utilisateur local LoginPostgres accède au distant via le même compte LoginPostgres : il faut préciser le mot de passe
CREATE USER MAPPING IF NOT EXISTS FOR LoginPostgres SERVER ro_local_bd_pedago OPTIONS (user 'LoginPostgres', password 'MotDePasse');
-- pour annuler
-- DROP USER MAPPING IF EXISTS FOR LoginPostgres SERVER ro_local_bd_pedago;
-- enfin pour ajouter, ici DEUX TABLES DU SCHEMA mif04_xml
IMPORT FOREIGN SCHEMA mif04_xml
LIMIT TO (arbres_foret_2011, documentation_2011)
FROM SERVER ro_local_bd_pedago INTO public;
-- on teste
SELECT COUNT(*)
FROM arbres_foret_2011;
-- On créée des vues pour travailler plus confortablement
create view arbres as
select * from arbres_foret_2011;
create view documentation as
select * from documentation_2011;
-- On vérifie tous les objets créés:
\d
Pour la suite du TP, si vous utilisez
psql
pour executer les requêtes, pensez à utliser la commande\x
pour changer l’affichage des résultats.
Le document de description arbres décrit les attributs de la vue arbres
.
La vue documentation
décrit, pour un certain nombre d’attributs (DONNEE
) de la table arbre, une correspondance entre la valeur (CODE
) dans la table et une description textuelle (LIBELLE
). Par exemple la liste des couples valeur/description pour l’attribut ESPAR
de la vue arbres
peut être obtenue par la requête suivante:
SELECT code, libelle
FROM documentation
WHERE donnee = 'ESPAR';
Il est également possible de réaliser ce tp chez vous, soit en utilisant le VPN, soit en utilisant une base PostgreSQL locale. Il convient alors d’insérer les données dans la base via le script suivant: forets-postgres.sql.gz
Génération de XML
Il s’agit d’écrire des requêtes SQL afin de générer du XML en réponse aux requêtes en français ci-dessous. Ce XML se situera toujours dans une colonne vxml
dans le résultat de la requête. Il sera toujours conforme à la DTD indiquée dans la question.
Exemple
“Donner le titre de chaque film, un film étant identifié par son id
.”\
DTD:
<!DOCTYPE titre [
<!ELEMENT titre (#PCDATA)>
]>
Réponse:
SELECT XMLElement(name "titre", titre) as vxml, id
FROM film
Pour les requêtes complexes, mettez d’abord au point une requête SQL classique dont vous modifierez ensuite la clause
SELECT
pour produire du XML
Questions
- Donner, pour chaque arbre de la parcelle 613376 (
IDP
), sa hauteur totale (HTOT
).27 ou 8 réponses selon que l’on met des hauteurs vides ou non.<!DOCTYPE arbre [ <!ELEMENT arbre(hauteur)> <!ELEMENT hauteur (#PCDATA)> ]>
- Reprendre la question précédente et ajouter un attribut
id
(valeur obtenue viaA
) dans l’élémentarbre
.27 ou 8 réponses selon que l’on met des hauteurs vides ou non.<!DOCTYPE arbre [ <!ELEMENT arbre(hauteur)> <!ATTLIST arbre id CDATA #REQUIRED> <!ELEMENT hauteur (#PCDATA)> ]>
- Ajouter, toujours pour la parcelle 613376, et lorsqu’elle est disponible, le nom de l’espèce.
Utiliser pour cela le libellé disponible via la vue
DOCUMENTATION
. Attention, les codes sont réemployés, par exempleSELECT * FROM DOCUMENTATION WHERE CODE = '02';
renvoie 9 résultats. Il faut donc filtrer cette vue en ne gardant que lesDONNEE
correspondant à l’attribut dont on veut leLIBELLE
. (par exemple, pour l’attributESPAR
, on veutDOCUMENTATION.DONNEE = 'ESPAR'
). Remarque: il y a deux arbres dont le code espece n’est pas dans documentation. On doit avoir le même nombre de résultats qu’à la question précédente (utiliser e.g. un XXX OUTER JOIN).<!DOCTYPE arbre [ <!ELEMENT arbre(hauteur,espece?)> <!ATTLIST arbre id CDATA #REQUIRED> <!ELEMENT hauteur (#PCDATA)> <!ELEMENT espece (#PCDATA)> ]>
- Donner pour chaque espèce (valeur distincte
ESPAR
) son nom (si disponible) et le nombre d’arbres de cette espece.115 réponses<!DOCTYPE espece [ <!ELEMENT espece (nom?,quantite)> <!ATTLIST espece code CDATA #REQUIRED> <!ELEMENT nom (#PCDATA)> <!ELEMENT quantite (#PCDATA)> ]>
- Pour chaque parcelle dont l’identifiant (
IDP
) est inférieur ou égal à 600200, donner l’identifiant de la parcelle et la liste //sans doublons// des espèces présentes dans cette parcelle (son codeESPAR
sous forme d’attribut XML et pour celles dont on le connait, leur nom sous forme de texte).13 réponses<!DOCTYPE parcelle [ <!ELEMENT parcelle (espece*)> <!ATTLIST parcelle id CDATA #REQUIRED> <!ELEMENT espece (#PCDATA)> <!ATTLIST espece code CDATA #REQUIRED> ]>
- Pour chaque parcelle dont l’identifiant (
IDP
) est inférieur ou égal à 600200, donner l’identifiant de la parcelle, le nombre d’arbres de cette parcelle et la liste //sans doublons// des espèces présentes dans cette parcelle (son codeESPAR
sous forme d’attribut XML et pour celles dont on le connait, leur nom sous forme de texte).13 réponses<!DOCTYPE parcelle [ <!ELEMENT parcelle (nb,espece*)> <!ATTLIST parcelle id CDATA #REQUIRED> <!ELEMENT nb (#PCDATA)> <!ELEMENT espece (#PCDATA)> <!ATTLIST espece code CDATA #REQUIRED> ]>
- Pour les espèces présentes dans entre 2 et 5 parcelles, donner leur code, leur nom et la liste //sans doublons// des parcelles (
IDP
) contenant des arbres de cette espèce.15 réponses<!DOCTYPE espece [ <!ELEMENT espece (nom,parcelle+)> <!ATTLIST espece code CDATA #REQUIRED> <!ELEMENT nom (#PCDATA)> <!ELEMENT parcelle EMPTY> <!ATTLIST parcelle id CDATA #REQUIRED> ]>
- Reprendre la question précédente en ajoutant le nombre d’arbres de l’espèce concernée dans la parcelle concernée sous forme de texte dans l’élément
parcelle
.15 réponses<!DOCTYPE espece [ <!ELEMENT espece (nom,parcelle+)> <!ATTLIST espece code CDATA #REQUIRED> <!ELEMENT nom (#PCDATA)> <!ELEMENT parcelle (#PCDATA)> <!ATTLIST parcelle id CDATA #REQUIRED> ]>