TP DBI Interface d'accès aux BD SQL

API Perl-DBI

Documentation sur DBI= Database Interface

$perldoc DBI (ne pas se connecter comme root) accessible en ligne (au format POD) sous Unix
$perldoc DBD::mysql doc sur le pilote particulier Mysql
le livre de référence : Programmer Perl DBI, par Alligator Descartes et Tim Bunce (O'Reilly).
http://www.mysql.com/doc/en/Perl_DBI_Class.html

Présentation

DBI est une interface d'accès générale aux bases de données SQl, indépendante des moteurs des SGBD
C'est une couche logicielle chargée d'unifier les concepts et de fixer les méthodes que chaque pilote d'accès aux BD particulières aura la charge d'implémenter en fonction des caractéristiques du moteur de BD.

L'architecture d'une application de DBI

             .-.   .--------------.   .------------.
.-------.    | |---| Pilote Mysql |---|  mysqld    |
|scripts|    | |   `--------------'   `------------'
| Perl  |    |D|   .--------------.   .-------------.
|       |----|B|---| Pilote Oracle|---|Moteur Oracle|
|use DBI|    |I|   `--------------'   `-------------'
|       |    | |   .--------------.   .-------------.
|       |    | |---|  Postgresql  |---| postmaster  |
|       |    | |   .--------------.   .-------------.
|       |    | |... autres pilotes
`-------'    | |...

Nous allons parcourir un exemple simple mais significatif pour mettre en évidence les particularités du module DBI

Exemple de code Perl-Mysql commenté (cdi0.pl)

#!/usr/bin/perl -w
 # charger le module DBI
use DBI;
 # fixer la source de données $dns (=database name source) et le compte utilisateur
 # (l'utilisateur doit bien sûr avoir les droits d'accès à cette base)
$dsn   = "dbi:mysql:cdi";
$login = "admin";
$mdp   = "admin";

 ########################################################################################################
 # Se connecter à la source de données (avec ce compte valide sur la base) revient à créer un objet     #
 # La méthode connect de la classe DBI renvoie une référence vers cet objet, qui est un                #
 # identifiant de connexion, noté usuellement $dbh (=DataBase Handle).                              #
 # Les méthodes sont appelées suivant la syntaxe usuelle orientée-objet $dbh -> methode          #
 ########################################################################################################

$dbh = DBI->connect($dsn, $login, $mdp) or die "Echec de la connexion\n";

 # la soumission d'une requête avec Perl-DBI s'effectue en 3 phases
 # 1) La préparation de la requête est assurée par la méthode dbh->prepare
 #   elle s'applique à la chaine exprimant la requête
 #   et renvoie un identifiant de requête ($sth = STatement Handle)
 #      (Certains moteurs effectuent à ce stade une pré-compilation de la requête
 #       ce n'est pas supporté par Mysql jusqu'ici)

$table = "documents";
 # on veut afficher 3 champs de cette table : voici la requête écrite en SQL
$requete = "SELECT titre, auteur, editeur FROM $table ";
$sth = $dbh->prepare($requete);

 # 2) on exécute la requête préparée en adressant la méthode execute au descripteur $sth
$sth->execute();

 # 3) on s'adresse à $sth pour extraire les données de l'ensemble-résultat, ligne par ligne
 # chaque appel à $sth -> fetchrow_array extrait une ligne du résultat et la place dans une liste
 # notée ici @enr. Quand toutes les lignes ont été obtenues, on sort de la boucle
while (@enr = $sth -> fetchrow_array) {
print "@enr\n";
}
$sth -> finish;
$dbh -> disconnect;

 # meilleure façon, pour afficher les valeurs des champs individuellement
 #  print "Titre\t Auteur\t Editeur \n";
 #  while ( ($t, $a, $e) = $sth -> fetchrow_array) {
 #    printf "%50s %30s %20s\n", $t, $a, $e ;
 #  }

Phase de préparation, en général

En cas de traitements répétitifs, notamment insérés dans des boucles, on prépare des requêtes paramétrées, les valeurs des champs étant renseignées juste avant la soumission de la requete. Pour cela on se contente à la phase de préparation, d'insérer dans la requête des caractères '?' pour marquer la place des paramètres. Ces marqueurs seront donc remplacés par des valeurs effectives juste avant la phase d'exécution, en respectant l'ordre de leur apparition dans la chaine de requête.

# Préparation avec marquage
 $requete = "SELECT titre, auteur, genre FROM $table ";
 $requete .= " WHERE titre like ? and genre = ?" ;
 $sth = $dbh->prepare($requete);

# affectation ordonnée des marqueurs, puis exécution
 $sth->bind_param(1,'%mis%');
 $sth->bind_param(2,'roman');
 $nb = $sth->execute();

# récupération des romans dont le titre contient "mis"
 while ( @ligne = $sth -> fetchrow_array) {
 print "@ligne\n" ;

Cas particuliers

Pour une requete ponctuelle, de type INSERT, UPDATE, .. il est plus direct d'utiliser la méthode do() qui enchaine les phases de préparation et d'exécution :
```
 $req="INSERT INTO inscrits (nom, prenom, classe) VALUES('toto','jules','5A');
 $dbh -> do($req);
```

Dans le cas prévisible où il n'y a qu'une ligne résultat, on peut utiliser selectrow_array

 $req= "select count(*) from $table";
 @enr = $dbh -> selectrow_array($req);
 print "@enr\n";

Bien souvent, on ne souhaite pour une requete SELECT qu'obtenir l'affichage global des résultats avant d'envisager un traitement. Pour cela, utiliser la méthode $sth->dump_results.
```
$sth = $dbh->prepare("SELECT titre, auteur, editeur FROM documents");
$sth->execute();
$sth->dump_results;
```

Pour trouver le nombre de champs du résultat et l'afficher

$nbChamps = $sth->{'NUM_OF_FIELDS'};
print "Nombre de champs = $nbChamps\n";

TP

#################### Préparations ####################

Une base de données nommée cdi, composée de 4 tables doit etre installée sur le serveur Mysql hébergé par votre machine. Un compte spécifique (user=admin, password=admin) doit etre créé et posséder les privilèges (droits) courants pour gérer cette base.
Avant de tenter d'y accéder par scripts Perl, il est prudent de vérifier le bon fonctionnement du serveur et l'accessibilité de la base par admin.

Passer en session superviseur (su, mot de passe fctice). Mysql est-il en exécution ?
```
# service mysql status
# ps aux | grep mysql
```
Lancer le client Mysql en ligne de commande, comme root et mettre un mot de passe fctice
```
# mysql
mysql>SET PASSWORD FOR root= PASSWORD('fctice');
```

root crée la base cdi, puis restaure les tables avec le fichier de commandes cdi.sql

mysql>create database cdi
mysql> quit
# mysql -u root -p cdi < cdi.sql
# mysql -u root -p cdi
mysql>show tables;
....

Créer le compte admin/admin et lui donner des droits (ALL Privileges pour accorder des droits complets) sur la base cdi
```
mysql> GRANT select,insert,update,delete
ON  cdi.*
TO admin@localhost
IDENTIFIED BY "admin";
```

Retour en session stage (exit), puis test rapide avec le "client" mysql

$ mysql -u admin -p
 --- donner le mot de passe : admin ---
mysql> use cdi
mysql> describe documents;
mysql> select * from inscrits;
mysql> quit
$

Par la suite, on pourra controler les résultat des requêtes passées à travers les scripts Perl en passant les requêtes SQL directement au client mysql (ou avec l'utilitaire phpMyAdmin fonctionnant avec Apache+php. Bon, ne paraîtrait-il pas curieux tout de même de faire appel ici à PHP pour contrôler le travail de PERL ;-)

#################### lister une table de CDI (cdi0.pl) ####################

Reprendre et expérimenter le code commenté donné dans l'introduction

#################### accès à la base CDI (cdi1.pl) ####################

Ecrire le script cdi1.pl , destiné à donner le nombre d'enregistrements présents dans les tables de la base. Il est conseillé de procéder par étapes et de suivre le plan proposé
Remarque : on pourra ensuite simplifier en utilisant selectrow_array

#!/usr/bin/perl -w
# cdi1.pl, compter les enregistrements des tables

# définir quelques variables :
$dsn =
$login = "admin";
$mdp = "admin";
%tables=('1'=>'documents','2'=>'inscrits','3'=>'demandes','4'=>'prets');
# charger le module DBI

# connexion de admin à la base cdi ou affichage d'un message de sortie
$dbh =

# afficher la liste des tables, à partir du hachage %tables, sous la forme :
#  1 --> documents
#  2 --> inscrits
print "Liste des tables :\n";
foreach ...
 print ...;

# saisie d'un numéro
print "Choisir une table :\n";
print "numéro = ";
$num=<>;
.....
# attention, vérifier l'existence de la clé dans le hachage
$table= .... if ...

# écriture de la requête sous forme de chaine
$requete = "SELECT count(*) FROM $table";
# préparer, exécuter
.........
# extraire l'unique valeur du champ, donc pas besoin de boucle ..
.........
# affichage du résultat
print "Il y a .... enregistrements dans la table $table\n";

# écriture de ce qui précéde en UNE ligne avec selectrow_array
print "En une ligne, je trouve ", .... ," enregistrements !\n";

#################### Interroger la base (cdi2.pl) #####################

Ecrire le script cdi2.pl, Il s'agit d'interroger interactivement la table inscrits : en fournisssant la classe et une partie du nom, obtenir la liste des élèves correspondants.

#!/usr/bin/perl -w
# cdi2.pl, extractions de la base CDI
# meme début que cdi1.pl

$table = "inscrits";

$requete= "SELECT * FROM $table WHERE .................";
$sth = $dbh->prepare($requete);
# lire la classe
  ...............
# lire une partie du nom

# effectuer les liaisons
$sth->bind_param( ......);

$sth->execute();

$nbchamps = $sth->{'NUM_OF_FIELDS'};
print "Nb de champs = $nbchamps\n";
while (..............) {
print "...........\n";
}

Prolongement (cdi2bis.pl)
Effectuez un travail semblable pour obtenir la liste des livres classés par auteur puis par titre dont l'utilisateur fournit le genre. Pour éviter la saisie du genre, on fera saisir par l'utilisateur un numéro. Il faudra donc lui afficher une liste à partir d'un hachage construit par requete SQL.

############### Ajout d'un nouvel enregistrement (cdi3.pl) #############

Faire saisir les nom, prénom et classe du nouvel inscrit.
Ecrire la requete d'insertion, la préparer puis l'exécuter.

Prolongement :
La saisie est remplacée par la lecture de lignes ayant le format nom prenom classe avec un séparateur à fixer.

################# Génération dynamique de variables (parametrer.pl) ###############

Il s'agit d'un procédé très utilisé pour générer lors de l'exécution un ensemble de variables, lesquelles servent ensuite de paramètres de configuration à un serveur ou une application (cas concret : dans le serveur SambaEdu3, le script /etc/LcSeConfig.ph est appelé à chaque connexion samba d'un utilisateur).
On demande :

l'étude la structure de la table params, à restaurer dans la base test.
la connexion au moteur Mysql et à la base test
l'écriture de la requete SELECT pour récupérer les 2 champs de la table params nommés name et value
afficher rapidement le résultat avec la méthode dump_results
puis créer, affecter et afficher les variables dont le nom est contenu dans $name avec la valeur stockée dans $value, avec la syntaxe : ${$name} = $value;

################# transfert fichier --> table Mysql (importer.pl) ###############

Il s'agit d'écrire un utilitaire permettant d'insérer des enregistrements dans une table MySql, à partir de valeurs stockées dans un fichier de texte

récupérer par saisie les 3 paramètres : nom du fichier, noms de la base et de la table
connexion au moteur Mysql et à la base
préparation de la requete d'insertion avec des marqueurs
ouvrir le fichier en lecture s'il existe
pour chaque ligne on crée la liste des champs
déclarer les liaisons aux valeurs et exécuter.
vérifier en listant la table
faire des propositions pour généraliser de façon à pouvoir importer un fichier quelconque, comment alors créer la structure de la table d'accueil ?

############# Copier une base mysql dans postgresql (copie-my-pg.pl)##########

Il s'agit de se connecter à 2 bases de données appelées cdi toutes 2 pour simplifier, puis de copier la table cdi.documents de mysql vers la base postgresql.
Remarquez la simplicité due à l'existence de l'interface unificatrice DBI ... par rapport à PHP !

---------------------------------------------------------------------
#! /usr/bin/perl -w
# Connexion simultanée à 2 BD, l'une sous PostgreSQL, l'autre sous MySQL.
###############################
use DBI;
# Réalise la connexion en utilisant le pilote Pg pour PostgreSQL
$dbhpg = DBI->connect("dbi:Pg:dbname=cdi","","")
    or die "Impossible de se connecter à la base PostgreSQL : $DBI::errstr\n";

$dbhmy = DBI->connect("dbi:mysql:cdi","admin","admin")
    or die "Impossible de se connecter à la base mSQL : $DBI::errstr\n";

############# Mini-projet (analyse.pl)##########

Il s'agit de la suite du TP "Filtrer des lignes dans un fichier de log".
Il faut stocker dans une table les 6 champs suivants (heure,ip,ressource,taille,client,referer) pour chaque ligne du fichier, correspondant à un succès (accès réussi à la ressource demandée : code <400), en filtrant un certain nombre de types de fichiers (jpg, gif, png, ..) qui sont indiqués dans un fichier filtre.txt

ANNEXES et précisions

Syntaxe générale de connexion à un serveur distant

$dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";
$dbh = DBI->connect($dsn, $user, $password) or die "echec de la connexion !\n";

Probleme des apostrophes : $bd -> quote()
Parametres lies a des instructions (livre dbi p.132) : peut permettre de generer des requêtes SQL a la volee
Dans ce cas, se dispenser de l'utilisation de la methode quote()
Gestion des erreurs : positionner la clause RaiseError à 1
$dbh = DBI->connect($dns, $user, $mdp, {RaiseError => 1} ); etablit la connexion à la base de données et renvoie un descripteur (ou undef en cas d'échec)
$ok = $dbh -> ping(); la connexion au serveur est-elle active ?
traite la chaine pour insérer des "" et protège les caractères spéciaux SQL par des \
```
$sth -> quote($value);
```
prépare et exécute la requête, et renvoie le nombre de lignes résultat et -1 si ce nombre est inconnu
la liste de valeurs liées doit correspondre au nombre de ?
```
$descripteur_ligne = $dbh -> do($requete, @bind_values);
```

exécute la requête indiquée par $requete

@row = $dbh ->selectrow_array($requete, @bind_values);

Résumé de l'API

Méthodes de la classe DBI	Description
`use DBI;`	inclure le module d'accès aux BD
`$dsn ="DBI:mysql:$bd:[$hote:$port]";`	Définir une source de données (par défaut $hote = localhost et (pour mysql) $port=3306)
`$dbh=DBI->connect($dsn,$login,$mdp);`	tente la connexion au serveur et à la base spécifiée (ici avec chargement dynamique du pilote DBD::mysql). En cas de succès, retourne un descripteur de connexion à la base (mysql) En cas d'échec, retourne undef (interprété comme faux) et renseigne $DBI::err et $DBI::errstr.
`$sth->finish ; $dbh->disconnect;`	termine le traitement et libère les ressources déconnexion du serveur
`$sth=$dbh->prepare($requete);`	prépare la requête avant exécution (recommandé), et renvoie un descripteur de traitement
`$cr=$sth->execute([@valeurs]);`	exécute la requête préparée, avec passage des éventuelles valeurs des paramètres liés (dans l'ordre des positions indiquées par ? dans la requête) et retourne un compte-rendu entier (utilisable comme booléen) Pour les requêtes non SELECT, retourne le nombre de lignes affectées ou "0E0" s'il n'y en a pas, ce que PERL interpréte comme 0 (faux)
`$cr=$dbh->do($requete[, @valeurs]);`	prépare et exécute la requête SQL, de façon plus rapide, s'il n'y a pas de paramètres (l'éventuelle liste @valeurs des valeurs liées doit correspondre aux marqueurs ?).
`$dbh->quote($requete);`	pour résoudre les pbs des apostrophes dans les chaines de requête à insérer, plus généralement protège les caractères spéciaux SQL par des \
@liste=$sth->fetchrow_array; while (@liste = $sth->fetchrow_array) { print "$liste[0]\t$liste[1]...\n"; }	Extrait la ligne de données suivante et la retourne sous forme d'un tableau indicé contenant les valeurs des champs (ou undef dans le cas de valeurs NULL). Quand le parcours est fini, la liste retournée est vide et l'affectation est évaluée à FALSE, en contexte booléen (dans une condition while)
`@ligne=$dbh->selectrow_array($requete[,@valeurs]);`	Combine préparation, exécution de la requête passé directement à la base.
`$ligne=$sth->fetchrow_arrayref;`	Recherche la ligne de données suivante et la retourne sous forme d'une référence vers une liste de champs (ou undef).
`fetchall_arrayref;`	recherche toutes les données et les stockent dans une référence vers une liste de listes.

Compléments

Intérêt de la préparation
La phase de préparation consiste en un dialogue permis par l'instruction $dbh->prepare($requete_a_preparer) entre DBI et le moteur de la base. La chaine représentant une requête, même avec des marqueurs de paramètres liés, est analysée syntaxiquement, les permissions d'accès aux tables vérifiées .. et renvoie éventuellement un descripteur de requête qui sera utilisé pour la soumission de la requête, puis pour obtenir l'extraction des donnees (pour SELECT)
Liaisons de paramètres
Elles ne sont pas supportées réellement par le moteur Mysql, c'est le pilote DBD::mysql qui le simule son traitement
La forme $sth->execute(val1, val2, ...) est un raccourci qui est remplacé en interne par les liaisons suivantes avec bind_param
```
$sth->bind_param(1,val1);
$sth->bind_param(2,val2);
.....
$sth->execute();
```
Gestion des transactions
On peut indiquer si les traitements effectués doivent être immédiatement portés dans la base de données ou bien doivent etre considérés comme provisoires. Dans ce dernier cas, une action de validation globale (ou d'abandon) est nécessaire, on dit alors (dénomination anglaise) qu'on effectue un "commit"
```
# Connexion à la base de données avec activation des transactions et de la gestion des erreurs
$dbh = DBI->connect( "dbi:mysql:$bd" , { AutoCommit => 0,RaiseError => 1 } );
  # ou  pour désactiver l'autocommit   # $dbh -> {AutoCommit}=0 ;
.. modifications sur la base ..
  # si tout le traitement est validé, on "commet" les modifications
$dbh -> commit or die $sth->errstr ;
  # sinon, on annule les modifications faites avant l'erreur
$dbh->rollback();
```
Mysql ne supporte pas les transactions (contrairement à Postgresql, et au pilote DBD::Pg). Pour Mysql, l'attribut AutoCommit d'un descripteur de bd ($dbh) est donc implictement toujours 1
Gestion des erreurs
Positionner la clause RaiseError à 1, lors de la connexion au serveur
$dbh = DBI->connect($dns, $user, $mdp, {RaiseError => 1} );

Utilisation des méthodes renvoyant des références
$sth->fetchrow_arrayref, à la différence de $sth->fetchrow_array, renvoie non pas un tableau, mais une référence scalaire vers un tableau.
Comparer les 2 syntaxes :

$dbh = DBI->connect($dsn, $login, $mdp) or die "Echec de la connexion\n";
$table = "documents";
$requete = "SELECT titre, auteur, editeur FROM $table ";
$sth = $dbh->prepare($requete);
$sth->execute();
print "Titre\t Auteur\t Editeur \n";
# syntaxe usuelle
while ( @ligne = $sth -> fetchrow_array) {
   print "@ligne[0]   @ligne[1]   @ligne[2]\n" ;
}
# variante plus explicite
while ( ($t, $a, $e) = $sth -> fetchrow_array) {
   print "$t   $a   $e\n" ;
}
# avec référence liste
while ( $ref = $sth -> fetchrow_arrayref) {
   print "$ref->[0]   $ref->[1]   $ref->[2]\n" ;
}
# avec référence hachage
while ( $ref = $sth -> fetchrow_hashref) {
   print "$ref->[titre]   $ref->[auteur]   $ref->[editeur]\n" ;
}

$sth->fetchall_arrayref permet de récupérer d'un coup les références vers le jeu de résultats de la requete : la valeur scalaire renvoyée est une référence vers un tableau de référence de chaque ligne de résultat

# donc pas besoin de boucle, $ref pointe vers une structure englobant toutes les données du résultat
$ref = $sth -> fetchall_arrayref;
# une boucle pour récupérer chaque référence vers un tableau
foreach $r (@$ref) {
   print "$r->[0]   $r->[1]   $r->[2]  $r->[3]\n" ;
}