(<- précédent)

Les fonctionnalités XPath de MySQL 5

MySQL 5 offre la possibilité de pouvoir exécuter des requêtes XPath sur un flux XML, grâce à la fonction extractValue().

Le résultat récupéré est la concaténation de la valeur textuelle de chacun des nœuds trouvés (chaque valeur étant dissociée des autres par un espace blanc).

Comment récupérer la valeur de l’élément racine d’un flux XML ?

Extraire des données d’une variable contenant du XML

/*Contenu du fichier XML */

shell> cat formateur.xml

<opensource>

Les formateurs de l'équipe sont :

<formateur domaines="MySQL PHP">

<nom>Dasini</nom>

<prenom>Olivier</prenom>

</formateur>

<formateur domaines="PHP XML">

<nom>Allard</nom>

<prenom>Fabien</prenom>

</formateur>

<formateur domaines="Linux MySQL">

<nom>Dumont</nom>

<prenom>Pierre</prenom>

</formateur>

et sont passionnés par l'open source.

</opensource>

/* Initialisation de la variable @xml avec le contenu du fichier formateur.xml */

mysql> SET @xml = LOAD_FILE( './formateur .xml');

/* Récupération de la valeur de l’élément racine */

mysql> SELECT extractValue( @xml, '/opensource')\G

*************************** 1. row ***************************

extractValue( @xml, '/opensource'):

Les formateurs de l’équipe sont :

et sont passionnés par l’open source.

Comme vous pouvez le constater les éléments enfants de l’élément racine sont supprimés de la sélection : seul le contenu propre des éléments recherchés est donc intégré au résultat.

Autre exemple, on veut sélectionner le deuxième formateur de notre flux XML, ainsi que les formateurs qui ont pour domaine de compétence MySQL:

Extraire le deuxième formateur et les formateurs MySQL

mysql> SELECT extractValue( @xml, ‘/opensource/formateur[2]/nom’) AS Deuxieme, extractValue( @xml, ‘/opensource/formateur[contains( @domaines, "MySQL")]/nom’) AS MySQL;

+———-+—————+

| Deuxieme | MySQL |

+———-+—————+

| Allard | Dasini Dumont |

+———-+—————+

Comme vous avez pu le constater, XPath est également un langage d’interrogation. Examinons d’un peu plus près sa syntaxe.

Une expression XPath représente ce qui est appelé un chemin de localisation (très similaire au chemin permettant d’identifier un fichier) dans lequel chaque nœud est séparé des autres avec un /.

Généralement, la recherche s’effectue en fonction de deux types de nœuds distincts :

• les nœuds éléments, renseignés par leur nom,

• les attributs, indiqués par un @ accolé à leur nom.

Ainsi l’expression formateur/@domaines réfère l’attribut appelé domaines des éléments nommés formateur.

Pour pouvoir filtrer plus finement les éléments, XPath dispose des prédicats.

Chacun de ces prédicats est spécifié dans une paire de crochets [expression].

Une expression peut être soit une simple évaluation, soit utiliser une (ou plusieurs) fonction(s).

Par exemple formateur[@domaines="Linux MySQL"][contains( nom, "Dumont")]/prenom retrouve tous les prénoms des formateurs qui ont pour domaines Linux MySQL et qui possèdent un nom valant Dumont.

Les chemins de localisation utilisés peuvent être indiqués de façon absolue ou de façon relative. Dans ce deuxième cas, la recherche s’effectue en fonction du nœud contexte.

Dans l’expression précédente, l’attribut domaines testé est celui de l’élément formateur.

Enfin, il est possible de se déplacer dans l’arborescence (un . représente le nœud courant, et .. le nœud parent).

Ainsi formateur/prenom[../@domaines="Linux MySQL"][contains( ../nom, "Dumont")] est équivalente à l’expression précédente.

(à suivre… utiliser XML avec MySQL 5.1 : Extraire les données d’un flux XML stocké en base)

Tags: xml, xpath

(<- précédent)

Interroger un flux XML avec Xpath

L’emploi de XML ne présenterait pas beaucoup d’intérêt sans la possibilité de pouvoir interroger sa structure pour accéder à une information particulière : le langage XPath répond à cette attente et est (en partie du moins) aujourd’hui intégré à MySQL. Mais avant de voir comment utiliser XPath à travers MySQL, il est nécessaire de se pencher sur la syntaxe de ses expressions, dont la construction la plus importante est sans conteste le chemin de localisation (location path).

Le chemin de localisation

De manière similaire à la navigation au sein d’un système de fichiers, un chemin de localisation se compose d’une succession d’étapes, séparées les unes des autres par un /. La description d’un chemin peut se faire de façon absolue ou relative, auquel cas un nœud contexte est utilisé afin de déterminer à partir de quel endroit doit s’effectuer la recherche.

Chaque étape d’un chemin de localisation peut se diviser en trois parties (axe de recherche, test du nœud et ensemble de prédicats), réunies sous la forme suivante :

Axis::Node-Test[Predicate1][Predicate2]…

L’axe de recherche (Axis)

L’axe de recherche permet de spécifier dans quel sens orienter la sélection des nœuds que l’on veut récupérer par rapport au nœud courant. Ainsi, il est possible de choisir de chercher dans l’ensemble des nœuds des descendants, plutôt que de se limiter (par défaut) aux nœuds directement enfants.

Il existe une dizaine d’axes de recherche, dont les plus usités sont :

• child, qui limite à l’exploration des nœuds éléments directement enfants du nœud contexte,
• descendant, qui englobe, en plus des nœuds enfants, tous les éléments descendants,
• descendant-or-self, identique à descendant avec en plus le nœud contexte inclus,
• ancestor, qui contient tous les nœuds ancêtres,
• ancestor-or-self, identique à ancestor avec en plus le nœud contexte,
• following-sibling, qui fait référence aux éléments frères suivants,
• preceding-sibling, qui fait référence aux éléments frères précédents,
• attribute, qui se cantonne à la recherche dans les nœuds attributs.

Le test du nœud (Node-Test)

Généralement, le test repose sur le nom du nœud que l’on veut retrouver. Cependant, XPath ne se limite pas à la recherche de simples éléments, mais à tous types de nœuds. Il est donc ainsi possible de préciser le type de nœud que l’on veut atteindre grâce aux fonctions suivantes :

• node(), qui valide tous types de nœuds,
• text(), pour les nœuds texte,
• comment(), pour les commentaires,
• processing-instruction(), pour les instructions de traitements.

Les prédicats (Predicates)

Les prédicats, délimités par un « [" et un "]« , permettent de filtrer plus finement l’ensemble des nœuds retrouvés par le chemin de localisation, en évaluant l’expression qu’ils contiennent et en ne retenant que les nœuds qui la valident. Plusieurs prédicats peuvent se suivre, filtrant ainsi un peu plus précisément l’ensemble des nœuds à garder.

Les expressions mises en place dans les prédicats peuvent, en plus des quelques fonctions existantes et des opérateurs habituels, exploiter des ensembles de nœuds (node-sets) récupérés grâce à… des chemins de localisation !

Ainsi, il est possible d’adapter la validité d’un chemin de localisation en fonction même de tout élément de l’arborescence du flux XML.

Par exemple, si l’on considère l’expression XPath suivante :

//article[@rubrique = /site/menu/rubrique[2]]

On récupère tous les articles qui possèdent un attribut rubrique ayant la même valeur que la deuxième rubrique du menu du site.

Cet autre exemple :

//article[not( @rubrique = preceding-sibling::article/@rubrique)]

permet de ne sélectionner que les articles qui n’ont pas leur attribut rubrique égal à l’attribut rubrique d’un article précédent. En d’autres termes, on ne récupère ici que le premier article de chaque rubrique.

Quelques raccourcis

Afin de faciliter la lecture et l’écriture de requêtes XPath, il existe bien heureusement un ensemble de raccourcis pratiques:

Raccourcis Xpath

self::node()	.
parent::node()	..
attribute::name	@name
//descendant-or-self::node()/	//
element[position() = X]	element[X]

(à suivre… utiliser XML avec MySQL 5.1 : Les fonctionnalités Xpath de MySQL 5)

Tags: xml, xpath

(<- précédent)

Insérer du XML dans MySQL

Un fichier XML, n’est finalement qu’un fichier contenant du texte et insérer du texte dans un SGBDR, est loin d’être la chose la plus difficile. Une simple requête INSERT fait l’affaire. Cela nous donne quelque chose comme ceci :

INSERT INTO ma_table (champ_texte) VALUES (‘<?xml version= »1.0″?>…’)

Cela dit, si le flux XML est important, cette façon de faire n’est guère pratique. En fait, la méthode la plus simple est d’utiliser la fonction MySQL: LOAD_FILE().

Cette fonction prend en argument un fichier et renvoie son contenu sous la forme d’une chaîne de caractères. A noter que vous devez avoir le droit FILE pour exécuter cette commande, et que le fichier doit se trouver sur la machine hôte (en local):

Insérer un fichier XML dans MySQL

mysql> CREATE DATABASE IF NOT EXISTS xml;

mysql> USE xml;

mysql> CREATE TABLE rss (

id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

type CHAR(30) NOT NULL,

flux_rss TEXT )

ENGINE=MyISAM;

mysql> INSERT INTO xml.rss VALUES (NULL, ‘cinema’, LOAD_FILE(‘./cinema.xml’));

Arrêtons nous quelques instants sur la commande LOAD XML qui est apparue avec la version 6 de MySQL (MySQL 6.0.3). Cette commande permet d’importer simplement le contenu d’un fichier XML dans une table. Elle a une syntaxe assez proche de la commande LOAD DATA que vous connaissez peut-être, et qui elle aussi facilite le travail lorsque le besoin d’importer des données d’un fichier texte se fait sentir. L’exemple suivant nous montre un exemple d’utilisation de LOAD XML:

Utilisation de la commande LOAD XML de MySQL 6

mysql6> USE xml;

mysql6> CREATE TABLE consultant (

id_consultant TINYINT UNSIGNED NOT NULL PRIMARY KEY,

nom CHAR(40),

prenom CHAR(40)

)ENGINE=MyISAM;

mysql6> LOAD XML LOCAL INFILE ‘consultant .xml’

INTO TABLE consultant

ROWS IDENTIFIED BY ‘<consultant>’;

mysql6> SELECT * FROM consultant;

+—————+——–+—————–+

| id_consultant | nom | prenom |

+—————+——–+—————–+

| 1 | Dasini | Olivier |

| 2 | Allard | Fabien |

| 3 | Pascal | Borghino |

| 4 | Arnaud | Gadal |

+—————+——–+—————–+

(à suivre… utiliser XML avec MySQL 5.1 : Interroger un flux XML avec Xpath)

Tags: xml, xpath

MySQL avec son modèle relationnel et XML avec son approche hiérarchique ont en commun la vocation d’organiser et de structurer des ensembles de données.

En regardant de plus près, ces technologies peuvent offrir des solutions complémentaires :

• MySQL s’adapte à merveille à la gestion de systèmes d’informations éventuellement lourds et complexes,

• XML a su devenir un langage universel permettant aux applications de communiquer et d’échanger des informations.

Leur complémentarité apparaît alors évidente : XML rend possible l’exploitation et le partage des informations contenues dans MySQL par des applications distantes.

Générer du XML avec les programmes clients mysql et mysqldump

La version de MySQL utilisée pour cette article est la 5.1.22.

Générer du XML avec MySQL est très simple. La première possibilité est d’utiliser le client texte mysql avec l’option xml ou X. Un fois connecté au serveur MySQL, les résultats des commandes sont sortis sous format XML comme ont peut le voir :

Générer du XML en mode console

shell> mysql -u daz -p –xml

mysql> SHOW SCHEMAS ; /*Liste de tous les schémas (databases)*/

<?xml version= »1.0″?>

<resultset statement= »show schemas » xmlns:xsi= »http://www.w3.org/2001/

XMLSchema-instance »>

<row>

<field name= »Database »>information_schema</field>

</row>

<row>

<field name= »Database »>world</field>

</row>

<row>

<field name= »Database »>xml</field>

</row>

</resultset>

mysql> USE xml /*connexion au schéma qui se nomme xml */

Database changed

mysql> SHOW TABLES ; /*Liste de toutes les tables du schéma xml*/

<?xml version= »1.0″?>

<resultset statement= »SHOW TABLES » xmlns:xsi= »http://www.w3.org/2001/

XMLSchema-instance »>

<row>

<field name= »Tables_in_xml »>rss</field>

</row>

</resultset>

L’exemple suivant nous montre que le résultat d’une requête peut être redirigé vers un fichier. On peut par exemple créer le fichier ville_fra.xml avec le contenu de la table city du schéma world ou plus généralement avec le résultat d’une requête.

Création d’un fichier XML avec le client texte mysql

shell>mysql -X -e » SELECT * FROM world.city WHERE countrycode = ‘fra’; » –default-character-set=utf8 > /xml_file/city_fra.xml

shell> more city_fra.xml

<?xml version= »1.0″?>

<resultset statement= »SELECT * FROM world.city WHERE countrycode = ‘fra’ » xmlns:xsi= »http://www.w3.org/2001/XMLSchema-instance »>

<row>

<field name= »ID »>2974</field>

<field name= »Name »>Paris</field>

<field name= »CountryCode »>FRA</field>

<field name= »District »>Île-de-France</field>

<field name= »Population »>2125246</field>

</row>

<row>

<field name= »ID »>2975</field>

<field name= »Name »>Marseille</field>

<field name= »CountryCode »>FRA</field>

<field name= »District »>Provence-Alpes-Côte</field>

<field name= »Population »>798430</field>

</row>

<row>

<field name= »ID »>2976</field>

<field name= »Name »>Lyon</field>

…

</row>

</resultset>

L’autre façon d’exporter du SQL dans un fichier au format XML est d’utiliser le client texte qui permet de faire des sauvegardes (dump) mysqldump. Il devient alors possible d’obtenir un backup , structures et données, au format XML du schéma world:

Générer une sauvegarde au format XML

shell> mysqldump -u daz -p –xml –default-character-set=utf8 world > sauvegarde.xml

<?xml version= »1.0″?>

<mysqldump xmlns:xsi= »http://www.w3.org/2001/XMLSchema-instance »>

<database name= »world »>

<table_structure name= »city »>

<field Field= »ID » Type= »int(11) » Null= »NO » Key= »PRI » Extra= »auto_increment » />

…

</table_structure>

<table_data name= »city »>

<row>

<field name= »ID »>1</field>

…

</field>

</row>

</table_data>

</database>

</mysqldump>

(à suivre… utiliser XML avec MySQL 5.1 : Insérer du XML dans MySQL)

Tags: xml, xpath

Numéro un pour les applications en ligne, MySQL se positionne maintenant sur le secteur des applications d’entrepôts de données et d’informatique décisionnelle…

http://dasini.net/blog/presentations/?#presentation_mysql51

PDF à télécharger

Tags: csv, event, MySQL 5.1, MySQL Cluster, mysqlslap, partitionnement, Présentation, xml, xpath

(<- précédent)

Le moteur de stockage CSV

Le moteur de stockage CSV n’est pas une vraie nouveauté. Il est disponible depuis MySQL 4.1.4 (MySQL 5.1 pour M.S. Windows). Sa particularité est de stocker les données dans un fichier texte au format CSV (Comma Separated Values) où les données sont séparées par une virgule. Les avantages sont multiples, comme la possibilité d’éditer les données avec un éditeur de texte ou un tableur(Open Office Calc, Excel…) ou encore la possibilité de charger simplement d’énormes volumes de données dans le serveur MySQL.

Créer sa table au format CSV est très simple. On reste dans la logique MySQL de création de table : il faut en fait simplement spécifier CSV comme moteur (il n’est pas possible d’avoir des index, ni du partitionnement).

Pour créer une table au format CSV:

CREATE TABLE t_csv (
   id int, nom CHAR(50),
   prenom CHAR(50)
) ENGINE=CSV;

Le support de XML/XPath

A partir de MySQL version 5.1.5, un support basique de XML est implémenté sous la forme de deux fonctions: ExtractValue et UpdateXML.

• ExtractValue permet d’extraire les valeurs des différentes balises,

• UpdateXML permet de modifier la sortie d’un document XML.

Quelques exemples d’utilisation de ces deux fonctions avec la table t_xml et le fichier XML suivant (contenu dans le champ texte de la table t_xml)

Fichier XML et la table t_xml:

<?xml version="1.0" encoding="ISO-8859-1"?>
<profil>
   <formation categorie="MySQL" nbr_formation="5">
      <formateur>Olivier DASINI</formateur>
      <formateur>Pierre DUMONT</formateur>
      <Entreprise>Anaska</Entreprise>
   </formation>
   <formation categorie="PHP" nbr_formation="5">
      <formateur>Olivier DASINI</formateur>
      <formateur>Cyril PIERRE DE GEYER</formateur>
      <formateur>Romain BOURDON</formateur>
      <formateur>Julien PAULI</formateur>
      <Entreprise>Anaska</Entreprise>
   </formation>
   <formation categorie="Linux" nbr_formation="10">
      <formateur>Pierre DUMONT</formateur>
      <formateur>Fabien ALLARD</formateur>
      <Entreprise>Anaska</Entreprise>
   </formation>
   <formation categorie="Open Office" nbr_formation="3">
      <formateur>Sarah HAIM</formateur>
      <Entreprise>Anaska</Entreprise>
   </formation>
</profil>

CREATE TABLE t_xml (
   id int(11) NOT NULL AUTO_INCREMENT,
   texte text,
   PRIMARY KEY (id)
) ENGINE=MyISAM;

Pour avoir la liste des formateurs :

mysql> SELECT EXTRACTVALUE(texte,'/profil/formation/formateur') AS result FROM
t_xml\G
*************************** 1. row ***************************
result: Olivier DASINI Pierre DUMONT Olivier DASINI Cyril PIERRE DE GEYER Romain
BOURDON Julien PAULI Pierre DUMONT Fabien ALLARD Sarah HAIM
1 row in set (0.00 sec)

Récupérer seulement les formateurs MySQL :

mysql>SELECT  EXTRACTVALUE(texte,'/profil/formation[contains(@categorie,"MySQL")
]/formateur') AS result FROM t_xml\G
*************************** 1. row ***************************
result: Olivier DASINI Pierre DUMONT
1 row in set (0.09 sec)

Remplacer <formateur>Olivier DASINI</formateur> par <formateur>Freshdaz</formateur>

mysql> SELECT UPDATEXML('<profil><formation categorie="MySQL" nbr_formation="5">
<formateur>Olivier DASINI</formateur><formateur>Pierre DUMONT</formateur>
</formation></profil>', '/profil/formation/formateur[contains(.,"Olivier")]'
, '<formateur>Freshdaz</formateur>') AS result\G
*************************** 1. row ***************************
result: <profil><formation categorie="MySQL" nbr_formation="5">
<formateur>Freshdaz</formateur><formateur>Pierre DUMONT</formateur>
</formation></profil>
1 row in set (0.00 sec)

(à suivre… mysqlslap)

Tags: csv, MySQL, MySQL 5.1, xml, xpath