Les bases de données à partir d'un cas concret : Etude du logiciel de blog dotclear
1 / 23
J. Dubois – Lycée Eiffel – DIJON
Table des matières 1.
Présentation ................................................................................................................3 Démarche .......................................................................................................................3 Architecture d'un blog.....................................................................................................3 Que se passe-t-il lorsque l'on accède à la page d'accueil du blog ?...................................4 2. Cahier des charges ......................................................................................................4 3. Schéma conceptuel .....................................................................................................5 Vocabulaire ....................................................................................................................5 Démarche pour construire un schéma conceptuel ............................................................5 Application pour le blog .................................................................................................6 4. Schéma logique relationnel .........................................................................................8 Passage du schéma conceptuel au schéma relationnel......................................................8 Intégrité référentielle et formes normales ........................................................................9 Vocabulaire d'un schéma logique..................................................................................10 Schéma logique relationnel résultant.............................................................................10 5. Analyse à posteriori de dotclear ................................................................................11 Le schéma relationnel ...................................................................................................11 Les différents types de clé utilisés .................................................................................11 Les utilisateurs selon dotclear .......................................................................................12 La gestion des multi-catégories .....................................................................................13 6. Introduction à SQL ...................................................................................................14 Initialisation avant l'installation de dotclear...................................................................14 Création des tables........................................................................................................14 Insertion d'un enregistrement dans une table .................................................................15 Requête de traitement des données................................................................................15 7. PHP : un langage de scripts pour le web....................................................................16 En général.....................................................................................................................16 Avec Dotclear...............................................................................................................17 8. Références ................................................................................................................18 Logiciels de blog téléchargeables..................................................................................18 Documentation base de données, SQL, MySQL............................................................18 Documentation spécifique à dotclear.............................................................................18 Documents divers .........................................................................................................18 9. Annexes....................................................................................................................18 TP1 : Prise en main de Dotclear............................................................................................19 TD : découverte du langage SQL ..........................................................................................21 TP2 : Personnalisation de Dotclear .......................................................................................22
2 / 23
J. Dubois – Lycée Eiffel – DIJON
Bases de données : étude d'un moteur de Blog1 1.
Présentation
Démarche On va étudier le moteur de blog Dotclear (v1.2.7.1), qui fonctionne avec PHP et MySQL. Pour cela, on va faire une analyse à priori de la base de données nécessaire pour une telle application : • définition du cahier des charges (point de vue utilisateur) • schéma conceptuel • schéma logique relationnel Ensuite, nous étudierons à posteriori la base de données de Dotclear et analyserons les différences avec notre schéma logique. Enfin, nous verrons le langage SQL pour interroger notre base de données et comment l'insérer dans du code PHP pour ajouter des fonctionnalités à notre blog et créer un plugin d'administration.
Architecture d'un blog Un blog est un site web qui propose des articles rédigés par un ou plusieurs auteurs, les articles sont stockés dans une base de données. Le site web a deux interfaces bien distinctes : Front-end : interface pour le visiteur (qui vient lire les articles et les commenter) Back-end : interface pour le rédacteur (qui vient écrire des articles et organiser l'architecture du site) Lorsque l'on crée un Blog gratuit chez skyrock, blogger ou over-blog (pour ne citer qu'eux, il en existe plein d'autres), on a toujours accès à ces deux interfaces. L'objectif de ce papier est de voir ce qu'il y a derrière. On va utiliser dotclear, mais on aurait pu travailler sur WordPress ou WordPress MU (version multi-blog de WordPress, plus compliqué pour une première approche de l'architecture d'un blog). Ces applications sont écrites en PHP et utilisent MySQL comme base de données. Dans un premier temps, nous allons donc voir l'architecture de notre blog. Back-end
Serveur WEB + Serveur BdD
Front-end
On peut voir sur le schéma ci-dessus les deux interfaces ainsi que l'hébergement. Il faut considérer la représentation comme : "plus on est loin du centre, moins on a de privilèges sur 1
Ce cours est destiné à des élèves de BTS IRIS. Je ne suis expert ni en base de données, ni en PHP, je dois juste former mes élèves pour qu'ils puissent lire une structure de base de données et l'interroger pour pouvoir en extraire les informations voulues … Ce cours s'est déroulé sur 12 h de cours et 24 h de TP.
3 / 23
J. Dubois – Lycée Eiffel – DIJON
les données".
Ainsi, un visiteur n'a accès qu'au cercle bleu (le Front-end), un auteur a accès aux deux cercles bleu et vert (Front-end et Back-end) et l'hébergeur a accès au cercle orange qui contient tous les scripts ainsi que le contenu de notre blog. C'est la structure des données et l'organisation de l'application chez l'hébergeur qui seront traitées dans ce cours. Nous utiliserons WAMP comme assise pour dotclear, le serveur Web sera donc Apache (v2.0.59), la Base de données MySQL (v5.0.27) et nous aurons à disposition l'interpréteur PHP (v5.2.0).
Que se passe-t-il lorsque l'on accède à la page d'accueil du blog ? Dans la barre de navigation, on accède à notre blog par http://127.0.0.1/blog/index.php. http://127.0.0.1/blog/index.php
1
Serveur Web
6
1 : Requête auprès du serveur Web 2 : le serveur détecte que la page contient du code PHP, il fait appel à l'interpréteur PHP pour le traiter 3 : L'interpréteur PHP interroge la base de données pour avoir les informations nécessaires à la construction de la page. 4 : la base de données renvoie les réponses aux différentes requêtes. 5 : L'interpréteur PHP met en forme les informations venant de la base de données et génère une page HTML. 6 : Le serveur Web sert notre requête d'origine en nous renvoyant la page HTML générée par PHP.
2.
2
5
Interpréteur PHP
3
4
Base de données
Cahier des charges2
Un blog est un type de site web composé d'articles (posts) accessibles chacun séparément par une URL (Unique Ressource Location). Chaque post a un titre, une date d'édition, une date de publication, un auteur, un corps. Les articles sont référencés dans des catégories (il faudra étudier le besoin d'avoir une ou plusieurs catégories par article, cela interviendra dans les différents schémas). Le clic sur une catégorie permet de lister tous les articles de cette catégorie. Plusieurs personnes peuvent publier sur le même blog avec des permissions différentes (administrateur, rédacteur), L'identification d'un utilisateur se fera par login et mot de passe. Tout lecteur peut réagir à un article en ajoutant un commentaire (composé de titre, date, auteur, corps, adresse du site de l'auteur, mail de l'auteur). Un lecteur intéressé par un article de votre blog peut le citer sur son blog et indiquer ce lien sur votre blog (comme ça les autres lecteurs peuvent aller voir les sites qui parlent de ce post) : c'est un trackback (composé de titre, date, auteur, corps, URL de l'article distant). Enfin, on souhaite pouvoir avoir sur notre blog une liste de liens vers les sites que l'on aime et que l'on veut faire partager à nos lecteurs (ça s'appelle un blogroll). 2
Il me semble judicieux d'établir ce cahier des charges lors d'un travail collectif. Celui présenté n'est qu'un exemple, support pour ce qui suit, qui doit très facilement être adapté en fonction de la réflexion des élèves.
4 / 23
J. Dubois – Lycée Eiffel – DIJON
Quelques questions n'ont pas encore de réponses (nous ne répondrons pas à toutes) : Faut-il gérer plusieurs blogs dans la même base de données ? Plusieurs personnes (enregistrées) peuvent-elle contribuer au même article ? (= un article peut-il avoir plusieurs auteurs ?) Un article peut-il appartenir à plusieurs catégories ? Il faudra étudier attentivement ces questions lors de la conception de nos schémas. Dans un premier temps, nous répondrons NON à ces 3 questions.
3.
Schéma conceptuel
Vocabulaire Ce schéma met en relation les différentes entités identifiées. Il est indépendant de tout système informatique et prévu pour traduire le cahier des charges dans un formalisme graphique compréhensible par le client et le développeur. Une entité est définie comme un objet clairement identifiable qui peut être concret (livre, personne, …) ou abstrait (compte en banque). Elle est décrite par des attributs (caractéristiques ou propriétés) parmi lesquelles on définit un identifiant composé d'un ou plusieurs attributs (qui lèvera toute ambiguïté sur les différents enregistrements) par exemple, un numéro INSEE identifie de manière unique une personne française. Ce numéro n'est plus une clé valide dès que des étrangers sont enregistrés dans la base. Une relation représente les liens présents entre les différentes entités. Les relations n'ont pas d'existence propre. Une relation peut concerner une ou plusieurs entités (s'il n'y a qu'une entité, la relation est dite réflexive par exemple la relation Mariage sur une table Personne est réflexive). Le nombre d'entités entrant dans une relation s'appelle la dimension de cette relation. (une relation de dimension deux est binaire, de dimension 3 est ternaire, …) On appelle cardinalité le nombre de participation d'une entité à une relation. Si l'on considère la relation entre une facture et un produit, les différentes cardinalités correspondent au cas suivant : 1 – 1 : On ne peut indiquer qu'un produit par facture (pas très pratique), un produit ne peut apparaître que sur une seule facture 1 – N : On peut avoir un produit sur plusieurs factures, mais on est toujours limité à une seule ligne produit par facture (ou inversement) M – N : On a plusieurs lignes par facture et un produit peut être facturé plusieurs fois.
Démarche pour construire un schéma conceptuel3 Déterminer la liste des entités Pour toutes les entités, définir la liste des attributs puis déterminer l'identifiant Déterminer les relations entre les entités Pour chaque relation, définir les attributs propres à la relation (date, prix, … par exemple), vérifier la dimension et définir la cardinalité. e) Valider le schéma auprès des utilisateurs C'est cette démarche que nous allons suivre pour construire notre schéma. Nous autovaliderons notre schéma puisque nous sommes les utilisateurs de blogs… Sauf exception, un schéma conceptuel ne doit pas d'information redondante (par exemple, attributs calculables à partir d'autres attributs dans la base, transitivité, …). a) b) c) d)
Un même cahier des charges peut conduire à plusieurs schémas conceptuels différents.
3
Cette démarche est tirée du livre 'Conception de bases de données relationnelles en pratique'. Après l'avoir testée en cours, je pense qu'il est plus simple de définir les relations entre les entités avant d'établir la liste des attributs des entités. Cela permet de mieux repérer tous les points d'accroches possibles (entités et relations) pour un attribut.
5 / 23
J. Dubois – Lycée Eiffel – DIJON
Application pour le blog a)
Liste des entités
post, categorie, lien, utilisateur, commentaire, trackback b)
Liste des attributs des entités
A partir du cahier des charges, voici un premier jet de liste des entités : post (titre, date_edit, date_pub, corps, auteur, categorie) categorie (nom, URL) lien (nom, URL) utilisateur (login, mdp, permission) commentaire (titre, date, auteur, corps, URL_site, mail_auteur) trackback (titre, date, auteur, corps, URL_distant) Quels sont les identifiants possibles ? Pour chaque entité, il va falloir rajouter un attribut qui contiendra un entier qu'il faudra incrémenter à chaque enregistrement et qui assurera l'unicité des enregistrements. On peut considérer que le nom du lien peut être utilisé comme identifiant, (on ne rajoute pas d'attribut à cette entité) La liste attributs des entités est donc comme suit : post (id_post, titre, date_edit, date_pub, corps, auteur, categorie) categorie (id_cat, nom, URL) lien (nom, URL) utilisateur (id_user, login, mdp, permission) commentaire (id_comm, titre, date, auteur, corps, URL_site, mail_auteur) trackback (id_tb, titre, date, auteur, corps, URL_dist) Une question se pose : l'auteur et la catégorie d'un post sont-ils des attributs du post, des entités à part entière ou des relation entre les entités ? c)
Les relations entre entités
On constate que la table lien n'est en relation avec aucune autre table (ce qui n'est pas étonnant puisque c'est un module indépendant de notre blog) Les noms des relations sont discutables, l'essentiel est dans le repérage de ces relations ainsi que des attributs qui en font parti.
6 / 23
J. Dubois – Lycée Eiffel – DIJON
lien
categorie
post
0-N id_cat nom URL
appartient à 1-1
1-1 utilisateur
écrit
id_user login mdp permission
nom URL
id_post titre date_edit date_pub corps auteur categorie
0-N cite
0-N
1-1
trackback
réagit à id_tb titre date corps auteur URL_dist
0-N 1-1 commentaire id_comm titre date corps auteur URL_site mail_auteur
On, constate que l'attribut categorie de post correspond plus à la relation appartient à qu'à l'entité. De même, l'attribut auteur correspond à la relation écrit. (l'auteur d'un post est l'utilisateur qui l'a écrit). On peut aussi se poser des questions sur la date d'un post, d'un commentaire ou d'un trackback, dépend-elle de l'entité ou de la relation ? On aurait même pu définir la date comme une entité indépendante. Vous avez ci-après les représentations de la relation réagit à pour ces deux cas de figure. post
commentaire
post
id_post titre date_edit date_pub corps
id_comm titre corps auteur URL_site mail_auteur
id_post titre date_edit date_pub corps
réagit à date
réagit à
date
commentaire id_comm titre corps auteur URL_site mail_auteur
Ces deux schémas sont envisageables, j'ai choisi de laisser la date comme un attribut des entités, cela peut être discutable, mais ne change pas grand chose pour la suite des opérations. Il nous faut donc revoir notre liste des attributs des entités comme suit : 7 / 23
J. Dubois – Lycée Eiffel – DIJON
post (id_post, titre, date_edit, date_pub, corps) categorie (id_cat, nom, URL) lien (nom, URL) utilisateur (id_user, login, mdp, permission) commentaire (id_comm, titre, date, auteur, corps, URL_site, mail_auteur) trackback (id_tb, titre, date, auteur, corps, URL_dist) d)
relations : attributs, dimensions, cardinalités
Toutes les relations sont dépourvues d'attribut propre. cite : relie trackback à post, elle est binaire et de cardinalité 1 – N, un post peut avoir plusieurs trackbacks. réagit à : relie commentaire à post, elle est binaire et de cardinalité 1 – N, un post peut avoir plusieurs commentaires. appartient à : relie post à categorie, elle est binaire et de cardinalité 1 – N, une categorie peut contenir plusieurs posts. Elle devient de cardinalité M – N, si l'on souhaite qu'un post puisse appartenir à plusieurs catégories. écrit : relie post à utilisateur, elle est binaire et de cardinalité 1 – N, un utilisateur peut écrire plusieurs posts. Elle devient de cardinalité M – N, si l'on souhaite qu'un post puisse avoir plusieurs auteurs.
4.
Schéma logique relationnel
Pour l'instant, on a fait un schéma joli, intéressant, qui permet de bien comprendre les interactions entité–relation. Cela n'est pas encore un schéma logique de base de données. Le schéma logique va nous définir les tables que nous allons trouver dans notre base.
Passage du schéma conceptuel au schéma relationnel Les règles simples pour passer du schéma conceptuel au schéma logique sont les suivantes. a)
Entités
Une entité est traduite en une table dont : • le nom est le nom de l'entité, • les colonnes sont les attributs de l'entité, • la clé est l'identifiant de l'entité. On passe donc de (entité, attribut, identifiant) à (table, colonne, clé primaire). Si une table n'a qu'une colonne, elle est supprimée (la colonne devient un attribut d'une des entités avec lesquelles elle est en relation). b)
Relations
Une relation de cardinalité M – N est traduite par une table dont : • le nom est le nom de la relation, • les clés primaires des entités qui participent à la relation sont reportées comme colonnes de la nouvelle table, • les attributs spécifiques de la relation sont les autres colonnes de la table. Une relation de cardinalité 1 – N est traduite par : • Un report de clé de la table côté N dans la table côté 1. (on rajoute une (ou plusieurs) colonne(s) dans la table côté 1 correspondant à la clé primaire de la table côté N). • Une table spécifique lorsque la relation a des attributs propres. La clé de la tables est l'identifiant de l'entité côté 1. Une relation de cardinalité 1 – 1 est généralement traduite par une fusion des tables qu'elle relie (quand c'est une bijection). Dans certains cas particuliers, on peut préférer avoir une table pour la relation ou faire un report de colonne comme pour les autres cardinalités. 8 / 23
J. Dubois – Lycée Eiffel – DIJON
Un bon exemple où il n'est pas intéressant de fusionner 2 tables entrant dans une relation 1-1 est présenté ci-dessous : HOMME 0-1
FEMME
marié à
0-1
Pour cet exemple, il est intéressant de garder une table spécifique qui précisera qui est marié à qui, plutôt que de fusionner les deux tables ce qui conduirait à une organisation moins lisible. Attention, ces règles de transformation d'entités et relations en tables nécessitent de prendre des précautions pour éviter des fonctionnements aberrants de la base. C'est le problème d'intégrité référentielle.
Intégrité référentielle et formes normales Le problème de l'intégrité référentielle est de pouvoir supprimer des enregistrements d'une table nécessaires pour comprendre les informations d'autres tables. Ainsi, si l'on supprime le service d'une entreprise avant d'avoir ré-affecté tout son personnel, on va avoir des employés qui travaillent dans un service qui n'existe plus : que font-ils alors ? Monsieur CODD, qui a développé la théorie de l'algèbre relationnelle, a déterminé plusieurs niveaux de "qualité" des tables, appelées formes normales. Les formes normales constituent un ensemble hiérarchique de règles progressivement plus restrictives. Nous ne nous occuperont que des 3 premières formes normales. a)
Première forme normale (1NF)
Une table est 1NF si toutes ses colonnes sont atomiques (= on peut pas les diviser en souscolonnes), non répétitives (on a pas 3 colonnes livre_emprunté), gardent toujours la même signification dans le temps. b)
Deuxième forme normale (2NF)
Une table est 2NF si toutes ses colonnes dépendent de la totalité de la clé primaire. Matricule
Nom
Num_projet
Heures
40726 Lelong 1 23 40752 Dubois 2 17,5 40752 Dubois 3 10 … Cette table n'est pas 2NF car la clé est soit Matricule-Num_projet, soit Nom-Num_projet. Dans tous les cas, une colonne (Nom ou Matricule) ne dépend que d'une partie de la clé. c)
Troisième Forme normale (3NF)
Une table est 3NF si toutes les colonnes dépendent directement de la clé primaire. Num_salarié
Nom
Date_Naiss
Service
Nom_Service
Num_chef
40726 Lelong 25/12/1959 5 Commercial 40734 40752 Dubois 23/06/1975 3 Informatique 40725 Cette table n'est pas 3NF car la clé primaire est Num_salarié, les colonnes Nom, Date_Naiss et Service dépendent directement de toute la clé primaire, les colonnes Nom_Service et Num_Chef dépendent de la clé primaire en passant par Service (il y a une dépendance transitive entre Num_chef et Num_salarié qui passe par Service).on dit que la 3NF supprime les problèmes de transitivité. Une fois que l'on a toutes nos tables en 3NF, on peut envisager un travail sérieux sur nos bases de données… Nous n'irons pas voir les spécificités de 4NF et 5NF.
9 / 23
J. Dubois – Lycée Eiffel – DIJON
Il faut bien garder en tête que la conception de bases de données est un métier que vous ne serez pas amener à exercer dans les premiers temps. Ce rapide apperçu sur la normalisation est donc bien suffisant pour éviter les grosses boulettes lors de la création d'une base de données simple. d)
Intégrité référentielle
Cette notions intervient à chaque fois qu'une relation se traduit par un report de clé d'une table dans une autre : toute valeur de la colonne recopiée doit exister dans la colonne source. Ceci doit être vrai lors de la création de l'enregistrement mais aussi pendant toute la vie de la base de données. L'intégrité référentielle consiste à vérifier cette règle à chaque modification ou suppression de valeur dans l'une des colonnes. L'intégrité référentielle permet par exemple sur notre blog d'empêcher de supprimer une catégorie alors que des posts s'y rattachent encore. Si l'on a bien respecté les règles de conversion du schéma conceptuel vers le schéma relationnel, on aboutit sur un schéma 3NF.
Vocabulaire d'un schéma logique Dans une table, une ligne s'appelle en enregistrement ou tuple. La clé primaire assure l'unicité d'un enregistrement dans une table. Une clé reportée dans une table s'appelle une clé étrangère. Ce sont elles qui permettent d'assurer l'intégrité référentielle.
Schéma logique relationnel résultant4 post (id_post, titre, date_edit, date_pub, corps, id_cat, id_user) categorie (id_cat, nom, URL) lien (nom, URL) utilisateur (id_user, login, mdp, permission) commentaire (id_comm, titre, date, auteur, corps, URL_site, mail_auteur, id_post) trackback (id_tb, titre, date, auteur, corps, URL_dist, id_post) Les clés étrangères ont été notées en italiques. On constate que l'on retrouve les colonnes auteur et categorie (qui ont juste changé de nom …). Maintenant, on sait justifier ces champs grâce à notre analyse. Lors de l'implantation de notre base dans un SGBD-R, nous devrons faire attention à bien configurer l'intégrité référentielle. Attention, MySQL ne gère l'intégrité référentielle que pour les tables InnoDB…
4
Ce schéma peut se faire en TD, à partir des règles définies précédemment.
10 / 23
J. Dubois – Lycée Eiffel – DIJON
5.
Analyse à posteriori de dotclear5
Le schéma relationnel Voici le schéma relationnel de la base de données utilisée par dotclear (les tables ont été définies avec l'identifiant plop_ afin de pouvoir utiliser le code plopdump.sql). plop_user (user_id, user_level, user_pwd, user_nom, user_pseudo, user_email, user_post_format, user_edit_size, user_pref_cat, user_lang, user_delta, user_post_pub) plop_post (post_id, user_id, cat_id, post_dt, post_creadt, post_upddt, post_titre, post_titre_url, post_chapo, post_chapo_wiki, post_content, post_content_wiki, post_notes, post_pub, post_selected, post_open_comment, post_open_tb, nb_comment, nb_trackback, post_lang) plop_comment (comment_id, post_id, comment_dt, comment_upddt, comment_auteur, comment_email, comment_site, comment_ip, comment_pub, comment_trackback) plop_categorie (cat_id, cat_libelle, cat_desc, cat_libelle_URL, cat_ord) plop_session (ses_id, ses_time, ses_start, ses_value) plop_log (user_id, table, key, date, ip, log) plop_ping (ping_id, post_id, ping_url, ping_dt) plop_link (link_id, href, label, title, lang, rel, position) On retrouve les tables plop_link, plop_user, plop_post, plop_categorie et plop_comment avec quelques colonnes supplémentaires mais pas de table pour les trackback. En effet, les trackbacks sont assimilés à des commentaires et c'est la colonne comment_trackback de la table plop_comment qui permet de faire la différence entre commentaire et trackback. On constate l'apparition de 3 nouvelles tables : plop_log enregistre ce qui s'est passé du point de vue administration sur le blog (création d'utilisateur, création de post, login, logout, …) plop_session gère les paramètres de connexion à l'aide des cookies (on peut vérifier par exemple avec FireFox dans outils…>>option>>vie privée>>afficher les cookies …, vous retrouverez le cookie correspondant à la connexion avec l'identifiant ses_id …) plop_ping quant à elle enregistre les adresses des articles sur lesquelles on a fait des trackback. Ces 3 tables rajoutent des informations certes intéressantes mais qui n'étaient pas prévues dans le cahier des charges originel, il est donc normal que nous ne les ayons pas dans notre schéma. On peut constater que la table plop_post contient deux colonnes nb_comment et nb_trackback qui contiennent le nombre de commentaires et de trackbacks relatifs au post. Ces valeurs sont calculées à partir de la table plop_comment. Cela contredit ce que l'on avait dit au début comme quoi il ne devait pas y avoir d'attributs calculés. En effet, dans certains cas, il est plus simple d'enregistrer un résultat calculé plutôt que de refaire le calcul à chaque fois.
Les différents types de clé utilisés On retrouve dans toutes les tables une PRIMARY KEY qui correspond à la clé primaire de chaque table. La table plop_log fait exception à cette règle puisqu'elle n'a aucune clé définie : cela se justifie par le fait que c'est un historique des actions d'administration effectuées sur la base. Le soucis d'identifiant assurant l'unicité des enregistrements sur une telle table n'a pas lieu dêtre. On aurait aussi bien pu stocker cet historique dans un fichier texte mais quand on a une BdD sous la main, c'est dommage…
5 Cette partie de cours est une synthèse qui se déroule après un TP de découverte de dotclear (installation, peuplement de la base, analyse des tables créées lors de l'installation, utilisation du blog pour voir son fonctionnement).
11 / 23
J. Dubois – Lycée Eiffel – DIJON
Dans la table plop_categorie, deux clés UNIQUE sont définies sur les colonnes cat_libelle et cat_libelle_url. Ce ne sont pas des clés mais comme le libellé est le mot qui définit la catégorie, il faut bien qu'il soit unique pour éviter les confusions. Il en va de même pour l'URL qui comme son nom l'indique est unique … Ceci nous permet de dire aussi que la colonne cat_id n'est pas utile du point de vue du schéma de la base, par contre, pour l'interrogation par un programme, il est plus facile de parcourir une liste d'entiers consécutifs (index) par incrémentation qu'une liste de termes… Dans la table plop_comment, une clé INDEX est définie sur la colonne post_id. Cette clé est utile pour aider le moteur de base de données à optimiser ses requêtes. On peut constater que 2 INDEX comparables ont été créés dans la table plop_post chacun sur 2 colonnes simultanément. Enfin, dans la table plop_post, on peut constater la présence de 3 clés FULLTEXT qui sont utilisées pour indiquer au moteur de base de données que la recherche peut se faire à l'intérieur du texte, sur un mot ou un groupe de mots … Enfin, pour traiter les problèmes d'intégrité référentielle, il existe une FOREIGN KEY qui correspond à la (ou aux) colonnes reportées dans une table lors de la transcription du schéma conceptuel en schéma relationnel. Cette clé n'est pas gérée par les tables MyISAM de MySQL, pour pouvoir l'utiliser, il faut déclarer les tables de type InnoDB.
Les utilisateurs selon dotclear Lors de l'installation de dotclear, il est demandé le nom et le mot de passe d'un utilisateur pour accéder à la base de données. On peut ensuite, si l'on est administrateur de l'application dotclear définir des utilisateurs avec des permissions différentes (administrateur, rédacteur, …). A quoi correspondent ces différents utilisateurs ? Pour bien comprendre comment tout cela se passe, un bon petit dessin peut être utile :
BdD dotclear
Application DotClear
admin
Adm_DC
rédacteur rédacteur avancé En fait, l'application dotclear définit plusieurs niveaux d'utilisateur (administrateur, rédacteur, rédacteur avancé, inactif) et gère à son niveau les permissions de chacun. Lors de la connexion à la base de données, elle utilise toujours le nom et le mot de passe fournis lors de l'installation (cet utilisateur doit avoir les permissions maximales sur la base de données). Il est donc important de noter que la gestion des permissions n'est pas gérée au niveau de la base de données mais au niveau de l'application. 12 / 23 J. Dubois – Lycée Eiffel – DIJON
La gestion des multi-catégories Un plug-in de dotclear exite pour pouvoir inscrire un article dans plusieurs catégories. Il faut le décompresser dans le répertoire /ecrire/tools de votre blog. Vous devez ensuite aller dans l'ongle outil de l'interface d'administration de votre blog et cliquer sur gestionnaire des plugins. Vous verrez la liste des plugins présents sur votre blog (répertoires présents dans /ecrire/tools). Cliquer sur Mulitcat, cela installe le plugin de gestion des multi-catégories. Ce plugin doit permettre de modifier la cardinalité de la relation entre catégorie et post. On avait un schéma conceptuel comme suit qui se traduit dans le schéma relationnel par un report de la clé id_cat comme une colonne supplémentaire de la table post. post
caétégorie id_cat nom URL
0-N
appartient à
1-1
id_post titre date_edit date_pub corps
On a maintenant un nouveau schéma conceptuel (seules les cardinalités changent). post
caétégorie id_cat nom URL
0-N
appartient à
1-M
id_post titre date_edit date_pub corps
Ce nouveau schéma introduit une modification de notre schéma relationnel : en effet, la cardinalité de la relation est maintenant N – M, il va donc nous falloir introduire une nouvelle table pour rendre compte de cette nouvelle cardinalité. caétégorie
appartient à
post
id_cat nom URL
id_cat id_post
id_post titre date_edit date_pub corps
Tout cela est très beau sur le papier mais en fait, on installe notre plugin par-dessus une base de données existante. Il ne va donc rien modifier aux tables d'origine mais juste rajouter la table plop_post_cat (qui correspond exactement à appartient à de notre schéma). On aura donc toujours notre article qui appartient à une catégorie "principale", définie par id_cat de la table plop_post, puis à toutes les autres catégories souhaitées, défini dans la table plop_post_cat.
13 / 23
J. Dubois – Lycée Eiffel – DIJON
6.
Introduction à SQL6
Le langage SQL est utilisé pour exécuter une requête auprès d'une base de données. Nous allons voir quelques requêtes de base, à partir de notre cas concret. Ces requêtes sont toutes des requêtes qui fonctionnent sur MySQL, je ne garantis pas du tout leur fonctionnement sur d'autres logiciels de bases de données, chacun adaptant à sa sauce la norme SQL (en plus, il y en a plusieurs versions …)
Initialisation avant l'installation de dotclear Nous devons créer une base de données blog et un administrateur local (login= admin, mot de passe = password) pour notre moteur de blog. Pour cela, il faut se connecter à MYSQL en tant qu'administrateur général et créer la nouvelle base : cd c:\wamp\mysql\bin mysql –u root #lancement de mysql par l'administrateur général Il faut maintenant créer la base de données appelée blog : CREATE DATABASE blog;
Il faut enfin créer l'administrateur local de cette base de données, c'est lui qui sera utilisé par dotclear pour se connecter à la base pour toutes les opérations. GRANT ALL PRIVILEGES ON blog.* TO 'admin'@'localhost' IDENTIFIED BY 'password';
La connexion au serveur avec cet utilisateur se passera ainsi : mysql –u admin –p
Le paramètre –p signifiant que vous voulez que MySQL vous demande votre mot de passe.
Création des tables C'est cette opération qui est exécutée sur la base de données blog lors de l'installation de dotclear (la requête ci-dessous est tirée du fichier plopdump.sql) CREATE TABLE `plop_categorie` ( `cat_id` int(11) NOT NULL auto_increment, `cat_libelle` varchar(255) NOT NULL default '', `cat_desc` longtext, `cat_libelle_url` varchar(255) NOT NULL default '', `cat_ord` int(11) default NULL, PRIMARY KEY (`cat_id`), UNIQUE KEY `cat_libelle_url` (`cat_libelle_url`), UNIQUE KEY `cat_libelle` (`cat_libelle`) ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
On peut regarder rapidement cette requête et comprendre que l'on commence par dire qu'on va créer une table qui va s'appeler plop_categorie. Puis, on va y insérer des colonnes cat_id, cat_libelle, cat_desc, cat_libelle_URL et cat_ord en définissant à chaque fois le type des données, si la valeur peut être nulle et la valeur par défaut si elle est non nulle. La valeur auto_increment correspond à une valeur auto incrémentée par la base, utilisée pour les clés primaires numériques. Ensuite, on définit la clé primaire et les clés uniques Enfin, on défini le type de table (MySQL propose plusieurs moteurs de tables : ISAM, MyISAM, InnoDB, …), la prochaine valeur à utiliser pour l'auto_increment ainsi que le jeu de caractères.
6
Ce paragraphe est une synthèse d'un TD découverte du SQL sur machine avec la doc de MySQL.
14 / 23
J. Dubois – Lycée Eiffel – DIJON
Insertion d'un enregistrement dans une table Nous allons ajouter une catégorie à notre liste de catégorie : INSERT INTO `plop_categorie` VALUES ( '', 'Web 2.0', '', 'Web-20', '7' );
On voit la table dans laquelle on rajoute un enregistrement plop_categorie puis les valeurs correspondant à cet enregistrement. La première valeur, correpsondant à cat_id n'est pas spécifiée, elle sera définie par le moteur grâce à l'attribut auto_increment. Les différentes valeurs doivent apparaître dans l'ordre dans lesquelles les colonnes ont été définies lors de la création de la table. Ainsi, 'Web 2.0' va être dans la colonne cat_libelle, 'Web-20' dans la colonne cat_libelle_URL et 7 dans la colonne cat_ord. La colonne cat_desc restera vide pour cet enregistrement.
Requête de traitement des données Maintenant que notre base est peuplée grâce à plopdump.sql, il va nous falloir effectuer des requêtes de sélection sur notre base. Voici quelques requêtes utiles pour gérer un blog, elles sont déjà implémentées dans dotclear. Afficher le contenu de la table catégorie : SELECT * FROM plop_categorie;
Liste des catégories : SELECT cat_nom FROM plop_categorie;
Nombre d'article par cat_id : SELECT cat_id, COUNT(*) FROM plop_post GROUP BY cat_id;
Les titres des articles dont la cat_id vaut 2 : SELECT post_titre FROM plop_post WHERE cat_id=2;
Les titres des articles parus le 18 octobre 2007 : SELECT post_titre FROM plop_post WHERE post_dt LIKE '2007-10-18';
La variable post_dt est définie de type DATETIME, nous ne pouvons pas utiliser le = avec ce type de valeur, nous devons utiliser LIKE. La liste de tous les titres d'articles avec le libellé de la catégorie dont ils dépendent : SELECT post_titre AS titre, cat_libelle AS libelle FROM plop_post P, plop_categorie C WHERE P.cat_id = C.cat_id;
Les titres des articles de la catégorie 'Web 2.0' : SELECT post_titre FROM plop_post WHERE cat_id= (SELECT cat_id FROM plop_categorie WHERE cat_libelle='Web 2.0' );
Cette requête permet de montrer l'utilisation des sous-requêtes. SELECT post_titre FROM plop_post P, plop_categorie C WHERE P.cat_id = C.cat_id AND C.cat_libelle = 'Web 2.0';
15 / 23
J. Dubois – Lycée Eiffel – DIJON
On voit une autre requête qui nous donne le même résultat que la précédente : il n'existe pas une seule requête pour arriver au résultat souhaité. Les titres des articles qui ont des commentaires et le nombre de commentaires : SELECT P.post_id AS PID, COUNT(C.comment_id) AS nb_comment FROM plop_post P, plop_comment C WHERE P.post_id = C.post_id GROUP BY P.post_id;
Seuls les articles ayant des commentaires sont retournés : on ne voit pas qu'un post peut ne pas avoir de commentaire : lorsque la cardinalité minimale d'une entité dans une relation est '0', une jointure interne (utilisation de WHERE) ne permet pas de voir les enregistrements n'intervenant pas dans la relation. Les titres de tous les articles avec le nombre de commentaires afférents : SELECT P.post_id AS PID, COUNT(C.comment_id) AS nb_comment FROM plop_post P LEFT OUTER JOIN plop_comment C ON P.post_id = C.post_id GROUP BY P.post_id;
Tous les articles sont maintenant renvoyés avec pour chacun le nombre de commentaires qui s'y rapportent même s'il n'y a aucun commentaire.
7.
PHP : un langage de scripts pour le web7
En général On a vu lors d'un cours précédent sur la communication par ligne série que l'on devait passer par plusieurs étapes pour utiliser un tel composant (ouverture, configuration, utilisation, fermeture). Pour ce qui est d'une base de données, on va respecter les mêmes étapes. Vous avez dans le tableau ci-dessous la démarche pour utiliser une base de données, en mode console et en PHP avec en parallèle les opérations correspondantes pour le port série. fonctionnalité
ligne de commande
Connexion à la base
Sélection de la base
USE blog
Requête sur la base
SELECT * plop_user
Affichage des résultat de la requête de
+ mysql_connect() passe
mysql -u admin -p
mot de (password)
Fermeture connexion
PHP
la exit
mysql_select_db FROM mysql_query()
// avec port série
ouverture
configuration utilisation
mysql_fetch_assoc() mysql_fetch_array() mysql_fetch_row() mysql_close()
Fermeture
Voici ci-dessous un script tableau.php qui génère une page HTML présentant dans un tableau la liste des titres de tous les articles du blog ainsi que leur date de parution.
7
L'objectif est de montrer rapidement l'utilisation de PHP pour interroger une base de données et afficher les résultats dans une page web.
16 / 23
J. Dubois – Lycée Eiffel – DIJON
tableau.php les articles de Blog
Bienvenue,
Voici un tableau récapitulatif des articles de 'Blog' triés par date :
Avec Dotclear8 Dotclear est toujours connecté, nous n'avons qu'à programmer la partie utilisation de la connexion. Il n'est pas nécessaire de refaire une connexion à la base, il ne faut surtout pas se déconnecter de la base, cela empêcherait le bon fonctionnement de toutes les requêtes suivantes. Deux objets existent pour interroger la base de données, $con et $res qu'il faut déclarer global dans la fonction et que l'on utilise comme cela
