Christophe Nowicki

Malgré tout le mal qu’il est possible de penser ou de dire sur le format de fichier CSV, celui-ci présente beaucoup d’avantages :

• il est facilement manipulable pour les utilisateurs à l’aide d’un tableur comme Calc, Gnumeric ou bien Excel ;
• il permet d’écrire rapidement et facilement des routines d’import / export ;
• il permet d’échanger les données entre des bases et des systèmes hétérogène.

Mais ce format de fichier ne dispose pas d’un système de validation. Dans la plupart des cas ce sont les routines d’import / export qui prennent en charge la validation des données et de leur mise en forme.

Ce travail est pénible, répetitif et ennuyeux mais nécessaire si vous ne voulez pas qu’un fichier mal formaté ne corrompe les données de votre base.

L’objectif de cette article est de proposer une solution simple permettant la validation d’un fichier CSV en utilisant les fonctionnalités offertes par les schémas XML.

Les exemples fournis sont écrit à l’aide du language de programmation PHP5.

Transformation CSV vers XML

Votre fichier doit contenir sur la 1ère ligne, le nom de chaque cologne :

“CHANNEL”;”COMPANY”;”BVD_ID_Groupe_1″;”BVD_ID_Groupe_2″;”GENRE”;”URL”;”OTHER_URL”;”COMPETENT_AUTHORITY”
“Canal+ Film 1″;”C MORE ENTERTAINMENT AB”;”NL33268595″;”DE8330261794″;”CIN”;”http://www.canalplus.se”;”http://www.sbsbroadcasting.com/, http://www.prosiebensat1.de/”;”RTVV”
“Canal+ Film 2″;”C MORE ENTERTAINMENT AB”;”NL33268595″;”DE8330261794″;”CIN”;”http://www.canalplus.se”;”http://www.sbsbroadcasting.com/, http://www.prosiebensat1.de/”;”RTVV”
“Canal+ Film 2 Sport Weekend”;”C MORE ENTERTAINMENT AB”;”NL33268595″;”DE8330261794″;”CIN”;”http://www.canalplus.se”;”http://www.sbsbroadcasting.com/, http://www.prosiebensat1.de/”;”RTVV”
“Canal+ Film 3″;”C MORE ENTERTAINMENT AB”;”NL33268595″;”DE8330261794″;”CIN”;”http://www.canalplus.se”;”http://www.sbsbroadcasting.com/, http://www.prosiebensat1.de/”;”RTVV”
“Canal+ Film HD”;”C MORE ENTERTAINMENT AB”;”NL33268595″;”DE8330261794″;”CIN”;”http://www.canalplus.se”;”http://www.sbsbroadcasting.com/, http://www.prosiebensat1.de/”;”RTVV”

Pour transformer le fichier CSV en XML, il suffit de lire le fichier CSV avec la fonction fgetcsv et mettre les données dans des balisent XML : csv_to_xml.php

Une fichier CSV converti en XML ressemble à cela : channel.xml

Validation du fichier XML

Il ne reste plus qu’à écrire le schéma permettant de valider le fichier XML : channel.xsd

Vous pouvez tester vos schéma en ligne de commande en utilisant la commande xmllint issue du paquet Debian libxml2-utils :


$ xmllint --noout --schema channel.xsd channel.xml
channel.xml validates


Validation en PHP

Une fois que votre schéma est correct et permet de valider le contenu du fichier XML, il faut l’intégrer directement dans votre application : validate.php

Conculsion

Cette méthode apporte les avantages suivants :

• Les règles de validation sont décrites directement via le schéma, et celui-ci dispose de nombreuses fonctionnalités tels les types de bases, les expression rationnelle, les séquences, etc…
• Les messages d’erreur sont compréhensibles pour l’utilisateur même, s’il faut parfois les traduire (en convertissant les numéros de lignes et certaines notions) ;
• La simplicité ;0)

Cette technique a été mise en oeuvre dans le cadre du projet MAVISE pour valider plus d’une vingtaine de formats de fichiers différents.

Il existe des outils tel que SQLSync permettant de synchroniser les données d’une base PostgreSQL. Mais cette outil, ne permet pas de faire une copie partiel des données.
Dans la plus part des cas, il faut programmer la synchronisation soit même, l’objectif de mon article est de vous proposer une solution simple et élégante.

La problèmatique

Des données sensibles se trouvent dans la base principale et il ne faut synchoniser qu’une partie de ces données vers les autres bases. L’architecture dispose des caractèristiques suivantes : Le schèma est identique sur chaque base ; Les opèrations d’écriture se font seulement sur la base principale ; Les bases sont accessibles en réseau.

La solution

Pour résoudre le problème, j’ai testé plusieurs approches avant de retenir l’utilisation d’un fonction de hachage dont voici le principe :

• chaque table doit disposer d’un identifiant unique. (champ id de type SERIAL) ;
• un programme récupère le couple id et la sum de hachage pour chaque table de chaque base qui doit être synchroniseés ;
• il compare le couple et si celui-ci est diffèrent alors les données sont mise à jour.

Implementation au niveau de la base de données

Le calcul de la somme de hachage se fait un niveau de la base de données à l’aide d’un fonction :

SELECT id,md5 FROM get_table_md5('nom_de_la_table');
 id |               md5
----+----------------------------------
  4 | 9f3bcd2fae528244669613ae0466cc3c
  5 | 1f3bcd2fa24528244669613ae66cc3czd
...
(42 row)

Voici le code de la function PL/Perl:

CREATE LANGUAGE plperl;
CREATE TYPE table_md5 AS (id INTEGER, md5 TEXT);
CREATE OR REPLACE FUNCTION get_table_md5(varchar) RETURNS SETOF table_md5 AS $$
    my ($rv, $status, $nrows, $row);
    # Get Table OID
    $rv = spi_exec_query(\"SELECT c.oid AS oid FROM pg_catalog.pg_class c
        LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
        WHERE c.relname LIKE '$_[0]';\");
    $status = $rv->{status};
    $nrows = $rv->{processed};
    return undef if ($nrows != 1);
    my $oid = $rv->{rows}[0]->{oid};
    # Get Table attributs
    $rv = spi_exec_query(\"SELECT attname,atttypid FROM pg_catalog.pg_attribute a
        WHERE a.attrelid = $oid AND a.attnum > 0 AND NOT a.attisdropped\");
    $status = $rv->{status};
    $nrows = $rv->{processed};
    return undef if ($nrows < 1);
    my $atts;
    my $atts_not_null;
    foreach my $rn (0 .. $nrows - 1) {
        if ($rv->{rows}[$rn]->{atttypid} == 16) {
            $atts .= ” (CASE WHEN $rv->{rows}[$rn]->{attname} THEN ‘t’ ELSE ‘f’ END) AS $rv->{rows}[$rn]->{attname}”;
        } else {
            $atts .= ” $rv->{rows}[$rn]->{attname}::text”;
        }
        $atts_not_null .= ” (CASE WHEN $rv->{rows}[$rn]->{attname} IS NULL THEN ‘’ ELSE $rv->{rows}[$rn]->{attname} END)”;
        $atts .= ‘,’ if ($rn != $nrows - 1);
        $atts_not_null .= ‘ || ‘ if ($rn != $nrows - 1);
    }
    # Calc MD5
    my $sql = “SELECT id, MD5($atts_not_null) AS md5 FROM (SELECT $atts FROM $_[0]) AS $_[0];”;
    $rv = spi_exec_query($sql);
    $nrows = $rv->{processed};
    foreach my $rn (0 .. $nrows - 1) {
        return_next({
            id  => $rv->{rows}[$rn]->{id},
            md5 => $rv->{rows}[$rn]->{md5}
        });
    }
    return undef;
$$ LANGUAGE plperl;

Pour utiliser cette fonction vous avez besoin du support du language de programmation PL/Perl dans Postgres, celui-ci se trouve dans le paquet Debian : postgresql-plperl-

La gestion des conditions

Le choix des données à synchroniser se fait individuellement pour chaque table à l’aide de la clause WHERE de la manère suivante :

SELECT id,md5 FROM get_table_md5('nom_de_la_table') WHERE id IN(SELECT id FROM nom_de_la_table WHERE nom_de_la_table.champ LIKE 'sync');
 id |               md5
----+----------------------------------
  1 | fdd56eabd4bb997e453e33f0022d46c1
(1 row)

Implementation du script de synchronisation

La synchronisation des données peut être réalisée à l’aide de n’importe quel language de programmation,
il suffit juste de disposer d’un accèss à la base de données.

Voici un exemple de script écrit par Emmanuel Saracco en PHP5 qui se repose sur les modules PEAR suivants :

• DB : pour l’accès à la base de donnée.
• Console_Getopt : pour la gestion de la ligne de commande.

Ce script nécessite un fichier de configuration, en voici un exemple.
Ce fichier contiens la configuration des diffèrentes base de données et les conditions sous-forme de clauses where.

Conclusion

Cette méthode de synchronisation fonctionne, elle est en production chez un client pour une base de données de plus de 100 tables, elle permet de synchronisé le contenu d’une base vers deux autres.
Les avantages de cette méthode sont les suivants :

• le fait de pouvoir choisir le contenu de la clause where permet de réalisé des régles de synchronisation très complexes ;
• les performances sont très bonnes car les transfert réseau sont limités ;
• il est possible d’écrire le script de synchronisation avec n’importe quel language de programmation.

Je vous passe l’ensemble de la propagande habituelle sur les Web Services ( l’architecture SOA, Annuaires de services, SOAP, etc…).

Pour ma part, je pense qu’il existe deux grandes familles de WebServices :

• Contrôle : l’éxécution d’action
• Données : la mise à disposition d’informations

Pour la première famille, l’ensemble des techniques disponibles (SOAP, WSDL, UDDI) couvrent les besoins.
Quant à la seconde famille, je trouve que ces techniques sont bien trop complexes à mettre en oeuvre.

Problèmatique

Imaginons que vous avez besoin de mettre à disposition le contenu d’une base de données relationnelles ( liste de contacts, tickets, commandes en cours, etc. ) à l’aide d’un Service Web.

1/ Solution envisagée : le développement d’une Interface SOAP spécifique à votre problèmatique

Le développement de celle-ci sera long, car il vous faudra :

• désigner l’interface
• développer les composants
• tester le fonctionnement
• écrire la documentation

2/ Solution envisagée : adopter la méthodologie REST

J’ai découvert cette méthodologie suite à un article du journal du Net : SOAP, XML-RPC et EST : différences et intérêts

Elle est spécifiée dans le mémoire de Roy Thomas Fielding : Architectural Styles and the Design of Network-based Software Architectures

Ce qui est remarquable dans cette méthodologie, c’est :

• sa simplicité
• la réutilisation de protocoles standards éxistants du web
• la possiblité de mettre en cache le résultat de la requête de manière transparente.

Le project PHP REST SQL

Par la suite, j’ai découvert le projet PHP REST SQL, celui-ci permet de mettre à disposition le contenu d’une base de données MySQL via une interface Web.

Ce projet consiste en une classe, qui s’interface avec une base de données MySQL et qui permet à l’aide de requêtes HTTP de récupèrer les données de la base, créer, modifier et supprimer des champs.

Ma contribution : abstraction par rapport aux bases de données

Actuellement, le projet ne supporte que les bases de type MySQL, j’ai donc réécrit la classe pour prendre en comptes toutes les bases de données supportées par le module PEAR MDB ( MySQL, PostGreSQL, Oracle, Frontbase, Querysim, Interbase/Firebird, MSSQL et SQLite).

J’ai testé son fonctionnement avec 3 bases (MySQL, PostGreSQL et SQLite).

Cette contribution est disponible sur SourceForge : database abstraction (MySQL, Postgres and SQLite Support).

Installation et Configuration

Installer et mettre en oeuvre le classe, il suffit de :
1/ Télécharger la classe :

$ wget 'http://sourceforge.net/tracker/download.php?group_id=126371&atid=705494&file_id=180054&aid=1498241'  -O php_rest_sql.tar.gz

2/ L’extracter dans votre arborescence Web :

tar xzvf php_rest_sql.tar.gz -C /var/www

3/ Editer le fichier de configuration phprestsql.ini.

4/ Editer un fichier .htacces et activer le module rewrite dans Apache.

Et voila !
Vous pouvez accéder au contenu de votre base de données directement à l’aide d’un navigateur.

Il ne vous reste plus qu’à écrire un client pour accéder aux données, à l’aide de la bibilothèque PHP CURL par exemple.

Conclusion

J’ai soumis mon patch à Paul James, l’auteur du projet et proposé l’idée de transformer celui-ci en module Pear. Il faut savoir que l’auteur a développé le project Tonic autour du même concept.

Mais il s’agit d’un veritable framework de développement web et qui par conséquent est beaucoup plus intrusif.

The sqlite_fetch_object function is not implemented in the PECL extension version 1.0.3.
I’ve rewrite it sqlite_fetch_object in PHP :

// An empty class
class bidon {
}
	
// Fetch resultset as an object
function sqlite_fetch_object(&$resource){
                $arr =  sqlite_fetch_array($resource);
                $obj = new bidon();
                foreach ($arr as $key => $value) {
                        # Check is valid $T_VARIABLE
                        if (ereg(\"[a-zA-Z_\x7f-\xff][a-zA-Z0-9_\x7f-\xff]*\", $key)) {
                                $obj->$key;
                        }
                }
                return $obj;
}

Voila