A rendre pour le 1^er octobre par mail à votre chargé de TP.

Rendre un compte rendu + le fichier sql contenant toute les définitions (sans les appels qui testent les procédures et les fonctions).

Connexion à iSQLPlus (seulement depuis l'université): http://b710ntb.univ-lyon1.fr:5560/isqlplus/

On souhaite réaliser une base de données pour stocker un forum de discussion sur internet. Les utilisateurs du forum devront être inscrits, la base de donnée contiendra donc un profil de l’utilisateur (nom, mail, date d’inscription, nombre de participation au forum).

Le forum sera divisé en différentes salles de discussion, chaque salle aura une thématique et un de ses utilisateurs jouera le rôle de modérateur. Un modérateur sera automatiquement nommé dans une salle lorsqu’il sera l’utilisateur ayant posté le plus de message dans cette salle.

Dans chaque salle de discussion, les utilisateurs pourront créer de nouveaux sujets ou répondre à des messages existants. Un message aura un auteur, une date de rédaction et du texte. Les discussions auront une structure arborescentes : un message pourra avoir un unique parent et de multiple enfants. Un sujet pourra être ouvert ou clôt (dans ce cas on ne pourra plus poster).

• Transparents de cours PL/SQL
• Rappels sur PL/SQL sur le site http://www.techonthenet.com/oracle/

Créez les tables suivantes :

CREATE  TABLE utilisateur(id_utilisateur INTEGER PRIMARY KEY, 
                          nom VARCHAR2(32),mail VARCHAR2(64) UNIQUE,
                          inscription DATE,
                          nb_action INTEGER);
 
CREATE  TABLE salle(id_salle INTEGER PRIMARY KEY ,
                    theme VARCHAR2(128),
                    id_moderateur INTEGER  REFERENCES utilisateur(id_utilisateur),
                    nb_thread INTEGER);
 
CREATE TABLE message(id_message INTEGER PRIMARY KEY,
                     id_parent INTEGER NULL REFERENCES message(id_message),
                     id_auteur INTEGER REFERENCES utilisateur(id_utilisateur),
                     date_envoi TIMESTAMP,
                     id_salle INTEGER REFERENCES salle(id_salle),
                     corps VARCHAR2(512),
                     etat VARCHAR2(6) CHECK ( etat IN ('OUVERT','CLOT')));

Prenez le temps de comprendre le schéma de la base de données et les contraintes d’intégrités.

Pour débugger, utiliser la procédure DBMS_OUTPUT.PUT_LINE( … ). Attention, cette instruction n'affiche rien sauf si on exécute auparavant durant la session:

SET SERVEROUTPUT ON;

Les clés des tables sont des entiers, pour obtenir des entiers différents on utilise sous Oracle des séquences :

CREATE  SEQUENCE seq_utilisateur
START WITH 1
INCREMENT BY 1
NOMAXVALUE;

Créer de la même façon des séquences pour générer la clé des salles et la clé des messages.

Créer une procédure PL/SQL pour ajouter un utilisateur du forum à la base de données.

CREATE OR REPLACE PROCEDURE nouvel_utilisateur(pnom IN VARCHAR2,pmail IN VARCHAR2)
AS
/* déclaration*/
BEGIN
 
/*  instuctions*/
 
END ;
/
SHOW errors /*pour voir les erreurs de compilation*/

Cette procédure encapsule l’insertion dans la table, elle s’occupe d’obtenir le nouveau numéro dans la séquence et la date du jour. Pour obtenir cette date vous pouvez utiliser SELECT CURRENT_DATE FROM DUAL.

Créer quelques utilisateurs en appelant la fonction :

CALL nouvel_utilisateur('bob','bob@aol.com');

Créer une procédure pour ajouter une salle au forum. Créer quelques salles.

CREATE OR REPLACE PROCEDURE nouvelle_salle(ptheme IN VARCHAR2)

Créer une procédure pour poster un message qui sera le départ d'un nouveau fil de discussion. Créer une seconde procédure qui créée une réponse à un message précédent.

CREATE OR REPLACE PROCEDURE nouveau_message(pid_salle IN INTEGER, auteur IN INTEGER,msg IN VARCHAR2)

CREATE OR REPLACE PROCEDURE nouvelle_reponse(pere IN INTEGER, auteur IN INTEGER, msg IN VARCHAR2)

Après avoir inséré le nouveau message dans la table message, la procédure mettra éventuellement (si besoin) à jour id_moderateur dans la salle où a été posté le message.

Poster quelques messages et vérifier la bonne mise à jour du modérateur.

On souhaite compter le nombre de fois où un utilisateur intervient sur le forum. Pour cela créer un trigger qui se déclenche lors de l’insertion d’un message et incrémente l'attribut nb_action de l’auteur du message.

CREATE OR REPLACE TRIGGER trig_compte_action
AFTER INSERT ON message
FOR EACH ROW
DECLARE
	/* declarations */
BEGIN
	/* instructions*/
END;
/

Créer quelques messages et vérifier la bonne exécution du trigger.

Créer une procédure pour clore un sujet (1 message). La procédure générera une erreur si l’utilisateur n’est pas modérateur dans la salle où se trouve le sujet qu’il souhaite clore.

CREATE OR REPLACE PROCEDURE clore_sujet(pid_message IN INTEGER, pid_utilisateur IN INTEGER)

Créer un trigger pour empêcher de poster des réponses dans un sujet clôt.

On souhaite empêcher la suppression d’un message ayant des réponses. Comment faire ? un trigger est-il nécessaire ? Supprimer quelques messages et vérifier que cette contrainte d’intégrité est bien vérifiée.

Écrire une requête ou une procédure permettant d'afficher les messages et les réponses sous forme arborescente (parcours en profondeur préfixe de la forêt d'arbres représentant les fils de discussion).

Exemple d'affichage:

bob@aol.com : Un message de bob ! [OUVERT]
bill@aol.com : Un message de bill ! [ OUVERT]
----> bob@aol.com : Une réponse de bob ! [OUVERT]
---------> bill@aol.com : Une réponse de bill ! [CLOT]
-----> jim@aol.com : Une réponse de Jim ! [OUVERT]
jim@aol.com : Un message de Jim [OUVERT]

Pour afficher la sortie dans une procédure n’oubliez pas d’exécuter

 SET SERVEROUTPUT ON;

Il est recommandé d'utiliser la construction CONNECT BY (voir ici) plutôt qu'une procédure récursive.

Créer une procédure pour clore un fil de discussion (l’ensemble des messages du fil). Cette procédure prendra en paramètre un message faisant parti du fil (pas forcément la racine du fil, mais le fil devra être clôt depuis sa racine). La procédure prendra également en paramètre l’identifiant de l’utilisateur souhaitant clore le thread. La procédure générera une erreur si l’utilisateur n’est pas modérateur dans la salle où se trouve le sujet qu’il souhaite clore. Utilisez la procédure permettant de clore un sujet. Vous pouvez décomposez ce problème en sous problèmes (par exemple une fonction ou une requête trouvant la racine d’un fil, et une autre fermant récursivement le fil).