Revenir à la page d'accueil.

Installation de la base de données pour l'exercice

Instructions pour l'installation de la base de données

Utilisation de la base de données

Fonctionnement d'une application web

Les principes de fonctionnement d'une application web utilisant une base de données relationnelle pour stocker les informations sont présentés dans ces articles:

Serveur web

Serveur informatique

Système de gestion de base de données

Cette image présente schématiquement les des outils que nous allons utiliser:

Modélisation des données

Quelques notions de base

Modèle entité-association

La méthode Merise

Merise et modélisation de données

ERD – entity relationship diagramm

Application de cette méthode aux informations historiques

Il y a différentes manières d'aborder la question de la modélisation des données historiques. L'approche présentée ici est issue de l'expérience du projet symogih.org.

On trouvera une explication détaillée de la méthode dans cet article et dans ce document les modèles conceptuels qui seront présentés au cours.

Le <span style="color:red;font-weight:bold;">modèle conceptuel de la base de données générique</span> mis à disposition se trouve dans ce fichier . Il faut le télécharger et le garder sous la main.

Production des données

Pour des exemples de production de données, voir les exercices 4 et 5.

Transformation des textes encodés en XML

Créer dans la base de données un objet de type “objet digital” qui correspond au texte qu'on est en train d'encoder.

Lui donner un nom et ajouter un descriptif du texte encodé. On peut aussi décrire l'état d'avancement du codage.

Copier le texte encodé en entier et le coller dans le champs 'texte' de l'objet en question

Sauvegarder

Ouvrir une boîte de dialogue SQL

Exécuter la requête suivante, qui utilise la fonction vues.transformation_xml([argument]) tout en mettant en argument de la fonction le numéro d'identifiant de l'objet digital qui contient le texte à transformer

SELECT vues.transformation_xml(3)

Si le texte a été encodé correctement (balises ouvertes et fermées, encodées selon les indications du wiki, etc.), le texte est réécrit en XML et copie dans le champs 'texte_xml' de la table objet.

Sinon, apparaît un message d'erreur qui indique, dans les premières lignes du message, l'élément qui pose problème et qu'il faudra corriger dans le texte encodé d'origine.

La suite du message contient la suite des erreurs provoquées par cette première erreur: celle-ci corrigée, tout rentre dans l'ordre

Si plusieurs erreurs se présentent, il faudra les corriger l'une après l'autre jusqu'à l'obtention du message: “Effectué”.

Requêtes SQL / XPath

Une fois les textes transformés en XML avec la fonction indiquée ci-dessous, on peut intérroger la base de données et extraire des informations.

Pour ce faire, on utilise la langage SQL en le combinant avec le langage XPATH que PostgreSQL implémente également à condition que le champs sur lequel porte la requête soit de type 'XML'.

Pour apprendre la syntaxe des requêtes SQL, voir la manuel PostreSQL, chapitres 2 et 7. Pour apprendre celle du XPATH, un excellent tutoriel rédigé par Wieslaw Zielonka est disponible sur internet

Pour exécuter les requêtes ci-dessous:

ouvrir la base de données avec pgAdminIII, sélectionner le dossier rouge 'schémas' dans l'arboréscence, puis ouvrir la boît de dialoque 'SQL': bouton loupe.

copier la requête dans l'Editeur SQL

exécuter la requête avec le bouton vert en forme de flèche

le résultat s'affichera dans le panneau de sortie en bas

Pour exporter le résultat:

sous CSV = dans le menu 'Fichier' sélectionner 'Exporter…', choisir un nom de fichier et un emplacement (attention à mettre le suffixe .csv après le nom)

pour visualiser directement le résultat avec un navigateur, sélectionner dans le menu 'Fichier' la commande 'Rapport rapide', choisir de nouveau un fichier de sortie (tout en bas), ne rien toucher aux autres paramètres et appuyer sur OK

le résultat apparaîtra immédiatement dans votre navigateur web

Un exemple de requête:

WITH tw1 AS
(
SELECT UNNEST(xpath ('//in',texte_xml)) texte_xml
FROM objets.objet
WHERE pk_objet = 3
)
SELECT tw1.texte_xml,
(((xpath ('/in/@ana',tw1.texte_xml))[1])::text)::INTEGER cle_type_information,
t1.nom type_information,
((xpath('/in/dat/@when',tw1.texte_xml))[1])::text DATE,
((xpath('/in/en/@ref',tw1.texte_xml))[1])::text acteur,
t2.nom nom_acteur
FROM tw1
JOIN vues.vm_objet_vue_de_base t1 ON t1.pk_objet = ( ( (xpath ('/in/@ana',tw1.texte_xml))[1])::text)::INTEGER
LEFT JOIN vues.vm_objet_vue_de_base t2 ON t2.pk_objet = ( ((xpath('/in/en/@ref',tw1.texte_xml))[1])::text)::INTEGER

Pour des exemples d'application, voir les exercices 4 et 5.

Documentation

Code SQL

Dans cette section on trouvera le code des principales fonctions SQL/PLpgSQL utilisées dans la base de données.

Fonction vues.transformation_xml(integer)

Cette fonction transforme le texte codé avec le système des accolades en un document XML. En argument de la fonction, il faut mettre l'identifiant (la clé primaire) de la ligne dans laquelle se trouve le texte à transformer. Le texte à transformer est stocké dans le champs “texte” et sera retranscrit et enregistré automatiquement par la fonction dans le champs “texte_xml”.

En cas d'incohérence du codage au niveau des accolades dans le texte de départ, un message d'erreur est produit par le serveur PostgreSQL qui perment en régle général, en cherchant le texte en question, de retrouver les passages du texte de départ qui posent problème.

-- Function: vues.transformation_xml(integer)
-- DROP FUNCTION vues.transformation_xml(integer);
CREATE OR REPLACE FUNCTION vues.transformation_xml(INTEGER)
RETURNS CHARACTER VARYING AS
$BODY$
WITH tw0 AS
(
-- transforme les entités XML
SELECT REPLACE(REPLACE(REPLACE(texte,'&','&amp;'),'<','&lt;'),'>','&gt;') txt
FROM objets.objet
WHERE pk_objet = $1
),
tw1 AS
(
-- transforme les accolades en chevrons
SELECT REPLACE(REPLACE(REPLACE(txt,'{/','</'),'{','<'),'}','>') txt
FROM tw0
),
tw2 AS
(
-- traite le cas de balises à deux caractères qui contiennent le type après l'id de l'objet: ajoute l'attribut "type"
SELECT REGEXP_REPLACE(txt,'(<\w{2})([\d\|]+)([a-z]{2})(_?)([\d|\|]*)(>)','\1\2\4\5 type="\3"\6','g') AS txt
FROM tw1
),
tw3 AS
(
-- traite le cas de balises à deux caractères suivies d'un 0: élimine l'attribut "ref" tout en gardant l'attribut @type
SELECT REGEXP_REPLACE(txt,'(<\w{2})(0)(_?)([\d|\|]*)([\s"=\w]*)(>)','\1\3\4\5\6','g') AS txt
FROM tw2
),
tw4 AS
(
-- traite le cas de balises à deux caractères suivies ou non d'un id possédant un '_': ajoute l'attribut @ana
SELECT REGEXP_REPLACE(txt,'(<\w{2})([\d|\|]*)(_)([\d\|]*)([\s"=\w]*)(>)','\1\2\5 ana="\4"\6','g') AS txt
FROM tw3
),
tw5 AS
(
-- traite le cas de balises à deux caractères suivies d'un id : ajoute l'attribut @ref
SELECT REGEXP_REPLACE(txt,'(<\w{2})([\d\|]+)([\s"=\w|]*)(>)','\1 ref="\2" \3\4','g') AS txt
FROM tw4
),
tw6 AS
(
-- traite le cas de balises seg et eud possédant un '_': ajoute l'attribut @ana
SELECT REGEXP_REPLACE(txt,'(<)(seg|eud)(_)([\d|\|]*)(>)','\1\2 ana="\4"\5','g') AS txt
FROM tw5
),
tw7 AS
(
-- traite le cas des dates avec un paramètre d'incertitude en ajoutant l'attribut @cert
SELECT REGEXP_REPLACE(txt,'(<)(dat)([0-9]+)(r|p)(_?)([a-z]{2})(>)','\1\2\3\5\6 cert="\4"\7','g') AS txt
FROM tw6
),
tw8 AS
(
-- traite le cas des dates avec un type en ajoutant l'attribut @type
SELECT REGEXP_REPLACE(txt,'(<)(dat)([0-9]+)(_)([a-z]{2})','\1\2\3 type="\5"','g') AS txt
FROM tw7
),
tw9 AS
(
-- traite le cas des dates en ajoutant l'attribut @when
SELECT REGEXP_REPLACE(txt,'(<)(dat)([0-9]+)','\1\2 when="\3"','g') AS txt
FROM tw8
)
UPDATE objets.objet
SET texte_xml = (concat('<root>',REGEXP_REPLACE(txt, '\|', ' ' , 'g'),'</root>'))::xml
FROM tw9
WHERE pk_objet = $1;
SELECT 'Effectué'::VARCHAR AS resultat;
$BODY$
LANGUAGE SQL VOLATILE
COST 100;
ALTER FUNCTION vues.transformation_xml(INTEGER)
OWNER TO postgres;