PTI SGBD

Epreuve de Pratique des Technologies Informatiques
Compte rendu d’activité
Session 2008
Fiche n°1
Alexandre K. (http://ploufix.free.fr)
Nature de l’activité
Création d’une base de connaissances avec PostgreSQL
Contexte : Le responsable technique souhaite la mise en place d’une base de connaissances afin de pouvoir
rassembler de manière centralisée certaines données techniques. De cette manière, les informations recueillies
doivent être retrouvées plus facilement. En effet, il est indispensable de mettre en place une solution organisée qui
contienne les données et dispose de différents modes de recherches.
Objectif : Réaliser et assurer la maintenance de la base de données et de son interface.
Compétences mises en œuvre pour la réalisation de cette activité.
C21
C24
C25
C35
C37
Installer et configurer un micro-ordinateur
Installer et configurer un système de gestion de base de données
Installer un applicatif
Actualiser une solution informatique
Administrer et maintenir une base de données
Conditions de réalisation
Matériels :
Un poste
Un serveur
(Environnement virtualisé avec VMWare)
Logiciels :
Un serveur Web (Apache)
Un SGBD (PostgreSQL)
Un navigateur web
Durée : 45 mn.
Autres contraintes et difficultés : La recherche par mots-clés est complexe et nécessite des outils plus efficaces que le
langage SQL (LIKE) afin d’obtenir des résultats pertinents.
Description et déroulement de l’activité
Situation initiale : Les informations techniques concernant les différentes interventions sont enregistrées sous format
papier ou format numérique sur le poste de chaque technicien.
Situation finale : Les informations sont stockées sur la base de données et sont accessibles par un site web. Chaque
technicien peut ajouter, modifier, supprimer certaines informations selon ses droits.
Epreuve de Pratique des Technologies Informatiques
Session 2008
Déroulement de l’activité par étapes
Présentation de la base de données :
La base de connaissances doit posséder un système de recherche multicritère : catégorie, utilisateur et
mots-clés. L’interface doit également posséder un système d’identification pour gérer les accès.
Seuls les propriétaires des fiches ou les utilisateurs avec des droits d’administration peuvent modifier ou
supprimer une fiche.
Il est possible de lier des fichiers aux fiches, et toute modification sur une fiche fait l’objet d’un
enregistrement précisant la date et l’objet de la modification.
Les fiches figurent obligatoirement dans une catégorie. Les catégories peuvent contenir des sous-catégories
et la recherche sur une catégorie doit inclure les entrées des sous-catégories.
Modèle conceptuel de données :
Modèle logique de données :
fiches (fiche_id, fiche_sujet, fiche_date, fiche_contenu, #cat_id, #user_id);
utilisateurs (user_id, user_login, user_mdp, user_nom, user_prenom, user_mail, user_statut);
categories (cat_id, cat_nom, cat_rang, cat_parent);
fichiers (fichier_id, fichier_location, fichier_type, fichier_libelle, fichier_regexp, #fiche_id);
revisions (modif_id, modif_date, modif_historique, #fiche_id);
Création de la base :
CREATE DATABASE base_connaissances WITH ENCODING='UTF8';
Création des rôles et des utilisateurs :
CREATE ROLE Gestionnaires;
CREATE ROLE Utilisateurs;
Les utilisateurs vont hériter des droits des groupes.
Epreuve de Pratique des Technologies Informatiques
Session 2008
CREATE USER Administrateur ENCRYPTED PASSWORD 'operations' IN ROLE Gestionnaires;
CREATE USER DUPONT ENCRYPTED PASSWORD 'pti1' IN ROLE Utilisateurs;
Création de l’espace de stockage pour les tables :
Définition de deux espaces : Fiches et Gestion
CREATE TABLESPACE Fiches LOCATION E'E:\\Fiches';
GRANT CREATE ON TABLESPACE Fiches TO public;
CREATE TABLESPACE Gestion LOCATION E'E:\\Gestion';
GRANT CREATE ON TABLESPACE Gestion TO public;
Création des tables
Requête SQL pour la création de la table fichiers qui va gérer la relation entre les fiches et des fichiers.
On ajoute directement les clés primaires et étrangères en fonction du modèle logique de données.
CREATE TABLE fichiers (
fichier_id serial NOT NULL,
fichier_type varchar(10),
fichier_libelle varchar(30),
fichier_location varchar(100),
fichier_regexp varchar(100),
fiche_id integer,
PRIMARY KEY (fichier_id),
FOREIGN KEY (fiche_id) REFERENCES fiches(fiche_id) ON DELETE CASCADE
) TABLESPACE Gestion;
On précise l’espace de stockage et une contrainte ON DELETE CASCADE sur la clé étrangère fiche_id
pour supprimer automatiquement les entrées en relation avec cette clé lors de la suppression d’une
fiche
Suppression d’une fiche avec la mise en œuvre de « ON DELETE CASCADE ».
DELETE FROM fiches WHERE fiche_id = ‘1’;
La suppression de cette fiche implique la suppression de toutes les entrées en relation avec la fiche
possédant l’identifiant fiche_id = 1 sur les tables fichiers et révisions.
Requête SQL pour insérer des catégories:
Le champ cat_id est auto incrémenté c’est-à-dire qu’il ne reste plus qu’à préciser le nom de la
catégorie, son rang et sa catégorie parente dans le cas d’une sous catégorie.
INSERT INTO categories (cat_nom, cat_rang, cat_parent) VALUES ('Microsoft', 1, NULL);
INSERT INTO categories (cat_nom, cat_rang, cat_parent) VALUES ('Windows XP', 2, 7);
Epreuve de Pratique des Technologies Informatiques
Session 2008
Attribution des droits sur les tables pour les rôles.
GRANT ALL ON fiches, categories, utilisateurs, revisions, fichiers TO GROUP Administrateurs ;
GRANT ALL ON fiches,fichiers,revisions, utilisateurs TO GROUP Gestionnaires ;
GRANT SELECT ON utilisateurs, categories TO GROUP Gestionnaires ;
GRANT SELECT ON fiches, fichiers, revisions, categories, utilisateurs TO GROUP Utilisateurs ;
Pour que les utilisateurs puissent faire appel à la requête SEQUENCE qui permet d’incrémenter les
champs de type SERIAL, il faut leur accorder les droits d’exécuter cette requête sur les séquences
correspondantes.
GRANT ALL ON categories_cat_id_seq, fiches_fiche_id_seq, fichiers_fichier_id_seq, utilisateurs_user_id_seq,
revisions_modif_id_seq TO GROUP Administrateurs,Gestionnaires ;
En se connectant avec le compte « utilisateur », le bon fonctionnement des droits peut être vérifié.
Requête de recherche de données.
La requête suivante permet une recherche utilisant l’ensemble des critères simultanément :
Recherche par mots-clés (port ET rdp OU relai ET messagerie)
Recherche par catégorie (Microsoft)
Recherche par auteur de la fiche (DUPONT)
SELECT cat_nom, fiche_date, fiche_sujet, utilisateurs.user_nom, utilisateurs.user_prenom,
fiche_contenu
FROM categories, fiches, utilisateurs
WHERE (SELECT cat_id FROM categories WHERE cat_nom = 'Microsoft') IN (cat_parent,
categories.cat_id)
AND utilisateurs.user_nom = 'DUPONT'
AND categories.cat_id = fiches.cat_id
AND fiches.user_id = utilisateurs.user_id
AND to_tsvector('french', fiche_sujet || fiche_contenu) @@ to_tsquery ('french','port & rdp |
relai & messagerie')
ORDER BY fiches.fiche_id DESC LIMIT 10 OFFSET 0;
Résultat après exécution de cette requête sous pgAdmin III :
Les catégories « Windows Server » et « Windows XP » sont des sous-catégories de « Microsoft »
On constate que l’on obtient uniquement les données créées par l’utilisateur recherché dans la catégorie
voulue et dont les mots clés figurent dans le sujet ou le contenu. La recherche par mots-clés est assurée
par l’outil « tsearch2 » de PostgreSQL qui se base sur l’utilisation d’un dictionnaire et d’une liste de mots
d’exclusion.
Il est également possible de classer les résultats selon un score de pertinence afin de présenter à
l’utilisateur les résultats qui vont au mieux correspondre à sa recherche dans le cas d’un tri avec la clause
ORDER BY sur le score généré par « tsearch2 ».
Epreuve de Pratique des Technologies Informatiques
Session 2008
Sauvegarde et restauration
L’outil pg_dump all permet d’extraire les données d’une base vers un fichier.
pg_dumpall.exe > F:\backup\sauvegarde.sql –c -i -U postgres
Pour lancer cette commande en tâche planifiée, il faut fournir la configuration pour l’adresse, le port, le
nom de la base, le nom du compte et son mot de passe dans %APPDATA%\postgresql\pgpass.conf
Pour restaurer cette base, il suffit d’exécuter le script de sauvegarde. La base est ensuite à nouveau
entièrement fonctionnelle.
\i C:/sauvegarde.sql
On restaure la sauvegarde
Création de vues pour faciliter les requêtes
Afin d’éviter de devoir taper de longues requêtes, il est utile de créer des vues sur des requêtes
courantes.
CREATE OR REPLACE VIEW "ConsulterBase" AS
SELECT cat_nom as Catégorie, utilisateurs.user_nom as Auteur, fiche_date as Date,
fiche_sujet as Sujet, fiche_contenu as Description
FROM categories, fiches, utilisateurs
WHERE categories.cat_id = fiches.cat_id
AND fiches.user_id = utilisateurs.user_id
ORDER BY fiches.fiche_id DESC;
Ainsi, on peut dorénavant exécuter cette requête à la place de celle présentée ci-dessus.
SELECT * FROM "ConsulterBase";
Utilisation depuis l’interface web avec PHP
Pour que les informations de la base de données puissent être consultées sans pour autant devoir
connaître le langage SQL, il est plus convivial et plus rapide d’utiliser une interface.
Voici une copie d’écran partielle de l’interface web après l’exécution d’une recherche :
Analyse des résultats obtenus.
Objectif Atteint : La base de données est fonctionnelle. Les contraintes sur les tables permettent de maintenir la
cohérence des données et d’effectuer ainsi des requêtes performantes afin d’obtenir rapidement les informations.
Bilan : L’utilisation d’une base SQL sur PostgreSQL répond parfaitement au cahier des charges de cette application.
De plus, cette solution est sous licence BSD et distribuée gratuitement. Enfin, sa disponibilité sur bon nombre de
plates formes, dont Windows et GNU/Linux, permet un large choix de systèmes concernant son intégration avec
d’autres applications (Apache, PHP).