Il faut rendre un zip contenant projet maven et le fichier sql commenté comprenant le codes des déclencheurs, des procédures stockées et des vues. Il s'agit d'une mise à jour du projet précédent. Bien indiquer dans les différents fichiers sources le nom et le numéro des deux membres du binôme.

Le rendu devra être effectué avant le 14/11/2010 à 23h30 via spiral

• API Java 6
• API driver Oracle
• Aide mémoire PL/SQL
• Transparents sur les APIs XML

On considère le schéma du PL/SQL et JDBC. On ajoutera le code de ce TP au projet celui du TP précédent.

Les packages des classes ne sont pas toujours indiqués, en particulier si la classe a été auparavant mentionnée avec son package.

Packages utilisés: javax.xml.transform, javax.xml.transform.sax, javax.xml.transform.dom, org.xml.sax, org.xml.sax.helpers, java.sql

→ Créer une classe ForumDAO dans le package epul.bdav.forum. Dans cette classe, ajouter un champ de type java.sql.Connection que l'on initialisera avec un paramètre du constructeur.

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.

→ Écrire 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.

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)

→ Écrire une requête qui pour chaque message génère un élément ”message” avec un attribut ”id”, un attribut ”date”, un attribut ”auteur” ayant pour valeur l'identifiant de l'auteur et enfin le corps du message comme contenu texte.

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”.

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).

→ 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).

→ Remplacer la jointure sur les messages par un LEFT OUTER JOIN. Que peut-on remarquer pour les messages n'ayant pas de réponse?

→ Créer une vue qui à chaque identifiant de message associe sa représentation XML telle que décrite ci-dessus.

→ Créer une vue qui crée un document XML contenant pour chaque salle l'ensemble des ses messages sous la forme donnée précédemment. Modifier les différentes vues pour que le XML produit soit conforme à la DTD fournie au TP XQuery

Le type Java java.sql.SQLXML permet de représenter une donnée XML renvoyée dans un attribut du résultat d'une requête JDBC. Le code suivant montre comment récupérer une javax.xml.transform.Source depuis une résultat d'une requête:

ResultSet rs = ...
while (rs.next()) {
  SQLXML xmldata = rs.getSQLXML(...);
  javax.xml.transform.dom.DOMSource domSource = xmldata.getSource(DOMSource.class);
  Document document = (Document) domSource.getNode();
  // utilisation du document
}

Pour récupérer une javax.xml.transform.sax.SAXSource, il suffit de remplacer DOMSource.class par SAXSource.class.

→ Dans la class ForumDAO, ajouter une méthode getSalleXML qui étant donné une salle requête la vue précédemment créée pour renvoyer une Source qui est une représentation XML du contenu de la salle.

Une séquence oracle est un compteur qui peut être incrémenté à la demande. Il est possible d'utiliser la méthode nextval pour incrémenter la séquence puis récupérer la valeur. La méthode curval permet quand à elle de récupérer la valeur courante de la séquence sans l'incrémenter.

Le code suivant permet de créer une séquence ma_sequence:

CREATE  SEQUENCE ma_sequence
START WITH 1
INCREMENT BY 1
NOMAXVALUE;

→ Créer les séquences seq_membre, seq_salle et seq_message. Mettre à jour leur valeur de seq_membre pour que cette dernière soit supérieure au plus grand identifiant de la table membre. Procéder de la même manière pour les séquences seq_salle et seq_message ¹⁾.

→ Créer un déclencheur sur insertion dans la table message qui met à jour la valeur de la séquence si on essaie d'insérer un message dont l'identifiant est supérieur à la valeur courante de la séquence. Créer des déclencheurs similaires pour les tables salle et message. On pourra utiliser l'instruction ALTER SEQUENCE pour changer cette valeur:

DECLARE
  inc_val INTEGER;
  dummy INTEGER; -- necessaire pour le select
BEGIN
  inc_val := 12;
  EXECUTE IMMEDIATE ('ALTER SEQUENCE seq_membre INCREMENT BY '||inc_val);
  SELECT seq_membre.nextval INTO dummy FROM dual;
  EXECUTE IMMEDIATE ('ALTER SEQUENCE seq_membre INCREMENT BY 1');
END;
/

→ Ajouter une méthode qui insère un message dans la table message via JDBC:

public void insererMessage(int id, int auteur, int salle, Integer parent, Timestamp date_envoi, String titre, String corps)

Remarque: parent peut être null.

→ Ajouter une seconde méthode qui utilise la séquence seq_message pour créer une identifiant pour message à insérer:

public int insererMessageNoId(int auteur, int salle, Integer parent, Timestamp date_envoi, String titre, String corps)

La méthode reverra l'identifiant en question. On pourra utiliser une requête du type:

SELECT ma_sequence.nextval FROM dual

→ Créer les méthodes insererSalle, insererSalleNoId, insererMembre, insererMembreNoId sur le modèle précédent.

Relire les transparents sur les APIs XML.

→ Dans le package epul.bdav.forum, créer une classe ForumImportHandler qui étend org.xml.sax.helpers.DefaultHandler. Elle possèdera un champ dao de type ForumDAO initialisé via un argument du constructeur.

Cette classe sera utilisée en conjonction avec un parser SAX qui appellera des méthodes telles que startDocument, startElement, endElement, etc lors de la lecture d'un fichier XML. Elle devra insérer les données du document dans la base relationnelle. Pour cela il faudra retenir des informations via des champs de la classe. Il faudra en particulier traiter:

• les messages/membre/salle sans identifiant
• les identifiants de membre non numérique (i.e. maintenir un mapping identifiant textuel ↔ identifiant numérique le temps de l'import)
• les liens implicites message ↔ salle (la salle d'un message correspond à l'élément salle qui contient l'élément message traité).

Il sera nécessaire de garder des informations de contexte dans des champs, par exemple le texte contenu dans le dernier élément titre qui a été lu, l'identifiant de la salle courante, etc.

Les exceptions SQLException seront rattrapées et relancées via:

try {
...
} catch (SQLException ex) {
throw new SAXException(ex);
}

Pour convertir une date XML en Timestamp SQL:

new Timestamp(DatatypeFactory.newInstance().newXMLGregorianCalendar(ma_date_xml).toGregorianCalendar().getTimeInMillis())                        

→ Créer une méthode importXML dans la classe ForumDAO prenant en argument une javax.xml.transform.Source et mettant en oeuvre le code suivant pour utiliser le ForumImportHandler:

javax.xml.transform.Transformer copy = javax.xml.transform.TransformerFactory.newInstance().newTransformer();
copy.transform(la_source, new javax.xml.transform.sax.SAXResult(new ForumImportHandler(this)));