Table des matières

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:

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


Modélisation des données

Quelques notions de base

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 modèle conceptuel de la base de données générique 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

SELECT vues.transformation_xml(3)

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:

Pour exporter le résultat:

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;