Catapulse

Dans cet article nous découvrirons à l'aide d'un exemple comment utiliser DBIx et ses confrères pour manipuler les données de multiples bases. Nous survolerons aussi la migration, le versionning, la représentation graphique des tables ...

Dans les lignes qui suivent il peut y avoir (il y a surement) des erreurs, imprécisions. N'hésitez pas à m'en faire part. Une dernière chose avant de commencer les sources Perl sont disponibles ICI.

DBIx est un ensemble de modules Perl permettant de triturer toute base de données sans en connaitre précisement le language. La base de ces modules s'appuie sur DBIx::Class, ce dernier fourni l'abstraction aux base de données et représente les tables comme de simples classes. L'accès aux données devenant alors trivial.

Pour comprendre DBIx il faut en connaitre le vocabulaire, qui se résume à:

ResultSource : Il s'agit ni plus ni moins de la table elle meme.

ResultSet: C'est la requete

Nous avons la table et la requête, il nous manque encore le plus important: la base de donnée et bien sûr la défnition des tables de cette dernière: c'est le schema. Là ce sera le role que DBIx::Class::Schema devra assurer. Nous verrons plus tard comment à l'aide de DBIx::Class::Schema::Loader nous générerons automatiquement de ce fameux schema.

Dans les exemples qui vont suivrent nous utiliserons principalement SQLite comme base de données transactionnelle, elle est idéale pour le développement d'une application. Celle-ci pourra alors migrer vers des bases de production en reconfigurant simplement le schéma.

Commençons fort avec une base comprenant 3 tables ' member, role et member_role ... grandement utile dans un système d'authentification par exemple :)

OK j'avais dis qu'il n'était pas utile de connaitre le SQL pour utiliser DBIX::Class et pourtant en voilà. Il s'agit du code SQL écrit dans notre premier fichier SQL : test.sql

Il définit la création de nos trois tables. Le répertoire 'DBIX' sera notre répertoire de travail, nous y collerons le fichier test.sql.

mkdir DBIX

-- --------------------------------------------------------
--  `member`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `member`;

CREATE TABLE IF NOT EXISTS `member` (
  id        INTEGER PRIMARY KEY,
  username  VARCHAR(30) NOT NULL,
  password  VARCHAR(40) NOT NULL,
  email     VARCHAR(100) NOT NULL default '',
  url       VARCHAR(100) NOT NULL default '',
  active    INTEGER(1)
);


-- --------------------------------------------------------
--  `role`
-- --------------------------------------------------------

DROP TABLE IF EXISTS `role`;

CREATE TABLE role (
  id       INTEGER PRIMARY KEY,
  name     VARCHAR(30)
);


-- --------------------------------------------------------
--  `member_role` - Mapping
-- --------------------------------------------------------

DROP TABLE IF EXISTS `member_role`;

CREATE TABLE member_role (
  id      INTEGER PRIMARY KEY,
  member_id  INTEGER REFERENCES member,
  role_id    INTEGER REFERENCES role
);

A noter tout de même la dernière table member_role qui associe un member, en fait une référence à la table member, a un role ou du moins là aussi une référence à la table role. Avec ce type de table de mappage on peut associer plusieurs roles à un membre. Nous verrons plus tard son exploitation avec les DBIx::Class::Relationship.

Création de la base SQLite :

mkdir db
sqlite3 db/test.db < test.sql

Vérifions le contenu la base db/test.db avec quelques commandes spécifique à SQLite qu'il faut néanmoins connaitre. Attention elles débutent toutes par un point '.'.

.help             : indispensable
.tables           : liste les tables de la base
.schema NOM_TABLE : le code de la table
.quit             : pour sortir

Ca suffira amplement pour s'y retrouver. Maintenant on lance courageusement :

sqlite3 db/test.db

SQLite version 3.5.9
Enter ".help" for instructions
sqlite> .tables
member       member_role  role       
sqlite> .schema member
CREATE TABLE `member` (
  id        INTEGER PRIMARY KEY,
  username  VARCHAR(30) NOT NULL,
  password  VARCHAR(40) NOT NULL,
  email     VARCHAR(100) NOT NULL default '',
  url       VARCHAR(100) NOT NULL default '',
  active    INTEGER(1)
);
sqlite> select * from member;
sqlite> 

Remarquer que SQLite comprend parfaitement le SQL, encore une chance :) C'est sûr qu'un pauvre select n'est pas très convaincant mais SQLite en fait bien plus.

On a maintenant tous les ingrédients pour débuter notre recherche ...

DBIx::Class::Schema::Loader va grandement nous aider dans la transformation de notre base fraichement créée en classe DBIx::Class. Ce module recherche les tables de la base db/test.db et les transforme en code Perl. Un petit mot à propos du DSN que nous verrons souvent apparaitre par la suite. Il s'agit de la représentation de la chaine de connexion à la base, ainsi pour notre base SQLite, le DSN sera ' dbi:SQLite:db/test.db'. Pour une base Mysql nous aurions un DSN équivalent ' dbi:mysql:dbname=test;host=localhost;user=USER;password=PASSWORD'.

La commande magique suivante fait tout le travail:

mkdir lib
perl -MDBIx::Class::Schema::Loader=make_schema_at,dump_to_dir:./lib -e 'make_schema_at("DB::Schema", { debug => 1 }, [ "dbi:SQLite:db/test.db","SQLite" ])'

La méthode make_schema_at du module DBIx::Class::Schema::Loader est utilisée pour créer dans le répertoire ./lib le schéma de la base définie par son DSN.

find lib
lib/
lib/DB
lib/DB/Schema
lib/DB/Schema/MemberRole.pm
lib/DB/Schema/Role.pm
lib/DB/Schema/Member.pm
lib/DB/Schema.pm

Et Ô merveille nos tables ont bien été découvertes :)

Notre schema lib/DB/Schema.pm (dont nous hériterons dans nos scripts de test) va alors charger les tables avec la méthode load_classes :

package DB::Schema;

use strict;

use warnings;

use base 'DBIx::Class::Schema';

__PACKAGE__->load_classes;

Dans ce cas toutes les tables sont chargées, il est toutefois possible de limiter ce chargement :

__PACKAGE__->load_classes(qw/Member Role/);

Voyons la définition des tables découvertes, par exemple la table lib/DB/Schema/Member.pm :

package DB::Schema::Member;

use strict;
use warnings;

use base 'DBIx::Class';

__PACKAGE__->load_components("Core");
__PACKAGE__->table("member");
__PACKAGE__->add_columns(
  "id",
  { data_type => "INTEGER", is_nullable => 0, size => undef },
  "username",
  { data_type => "VARCHAR", is_nullable => 0, size => 30 },
  "password",
  { data_type => "VARCHAR", is_nullable => 0, size => 40 },
  "email",
  { data_type => "VARCHAR", is_nullable => 0, size => 100 },
  "url",
  { data_type => "VARCHAR", is_nullable => 0, size => 100 },
  "active",
  { data_type => "INTEGER", is_nullable => 0, size => 1 },
);
__PACKAGE__->set_primary_key("id");
__PACKAGE__->has_many(
  "member_roles",
  "DB::Schema::MemberRole",
  { "foreign.member_id" => "self.id" },
);

Notre table hérite de DBIx::Class et son module Core est chargé par la load_components. D'autres composants spécialisés pourront modifier le comportement de cette dernière. UTF8Columns par exemple force les accès en UTF8.

Le nom de la table est ensuite spécifié et les colonnes de la table sont décrites.

Un point intéressant à soulever est la présence du ' has_many', il s'agit d'une RelationShip. Elle indique qu'un nouvelle méthode ' member_roles' fait la relation entre la clé étrangère ' member_id' de la table DB::Schema::MemberRole et la clé ' id'. En gros un appel à cette méthode nous retournera tous les roles d'un member définie par son ' id'. Mettons cela de coté nous y reviendrons.

Notre schéma étant défini, il est enfin possible de jouer avec nos tables. Le script ' test.pl' suivant insert quelques données en base.

#!/usr/bin/perl

use warnings; 
use strict;
use lib 'lib';
use DB::Schema;

my $schema = DB::Schema->connection('dbi:SQLite:db/test.db');



# Un membre
my $member_data = {  username => 'joe', 
                     password => 'pass1',
                     email    => 'joe@dalton.org',
                     url      => 'http://dalton.org',
                     active   => 1 };

$schema->resultset('Member')->create( $member_data );

DB::Schema instancie un nouveau STORAGE, DBIx::Class::Storage::DBI::SQLite dans notre cas.

Très simplement l'utilisateur 'joe' est stocké en base, nous pouvons ensuite y accéder avec son 'id' :

my $user = $schema->resultset('Member')->find('1');
print 'email=' . $user->email . "\n";

Ou encore à partir de son nom :

my $user = $schema->resultset('Member')->search( { username => 'joe'} )->first;

Remarquez que la méthode ' first' à été utilisé, contrairement au find qui retourne un seul utilisateur, le search pourrait retourné plusieurs utilisateurs portant le nom 'user'.

Si nous exécutons plusieurs fois ce même script 'joe' sera enregistré plusieurs fois en base, ce n'est pas ce que nous souhaitons. Pour y remédier il suffit de préciser que le nom ou encore l'adresse email doivent être unique. Ajoutons cette contrainte unique à la table DB::Schema::Member :

__PACKAGE__->add_unique_constraint(
               constraint_name => [ qw/username email/ ],);

Malheureusement cela ne suffit pas, en effet la base n'est pas 'informée' de l'ajout de cette contrainte. Il nous faut donc la recréée. Et puisque nous avons son schéma qui la décrit nous n'utiliserons plus la méthode bestiale s'appuyant sur sqlite3 mais avec la methode ' deploy' de DBIx::Class::Schema.

rm db/test.db

Crééons le script create_db.pl :

#!/usr/bin/perl

use strict;
use lib 'lib';
use DB::Schema;

my $schema = DB::Schema->connect('dbi:SQLite:db/test.db');

$schema->deploy;

La base db/test.db a été reconstruite en prenant en compte de l'unicité des colonnes 'username' et 'email' :)

Pour s'en convaincre on exécute plusieurs fois le script ' test.pl' :

./test.pl
email=joe@dalton.org
./test.pl 
DBIx::Class::ResultSet::create(): DBI Exception: DBD::SQLite::st execute failed: columns username, email are not unique(19) at dbdimp.c line 403 [for Statement "INSERT INTO member (active, email, password, url, username) VALUES (?, ?, ?, ?, ?)"] at ./test.pl line 18

:)

Après la phase de développement de notre application nous souhaiterions maintenant migrer les données vers une base de production (Mysql, postgres, Oracle, DB2 ...)

Rien de plus simple, il suffit pour cela de modifier la chaine de connexion DSN dans nos scripts ' create_db.pl' et ' test.pl'. Tentons l'expérience avec une base Mysql.

Crééons tout dabord la 'coquille' de la base dans Mysql:

mysqladmin -u user_dba -p create testdbix

Modifions dans create_db.pl la chaine de connexion en :

my $schema = DB::Schema->connect('dbi:mysql:dbname=testdbix;host=localhost;user=USER_DBA;password=PASSWORD');

Et enfin rééxécutons ce dernier. Et c'est tout, la base de notre application tournera dorénavant sous Mysql :) Pour y insérer à nouveau l'utilisateur 'joe' il faudra aussi modifier la chaine de connexion du script 'test.pl'.

Tout cela fonctionne parfaitement mais lorsque l'on veut passer d'une base à l'autre il devient vite fastidieux d'avoir à modifier cette chaine de connexion dans tous les scripts qui y font référence. On va maintenant uniformiser tout ceci.

L'idéal est de stocker la chaine de connexion dans un fichier de configuration auquel tous les scripts feront appel. Mieux encore serait de disposer d'une méthode retournant le schéma sans avoir à se soucier du DSN. C'est ce que nous allons ajouter à DB:Schema qui devient :

package DB::Schema;

use strict;
use warnings;
use Path::Class;
use FindBin;
use lib "$FindBin::Bin/../lib";

use YAML;

use base qw/DBIx::Class::Schema Exporter/;

__PACKAGE__->load_classes;

our @EXPORT = qw/schema/;

sub schema {

  # options utilis�es
  my $attrs   = { add_drop_table => 0, no_comments => 1 };

  my $bin     = dir($FindBin::Bin);
  my $conf    = 'test.yml';
  my $config  = YAML::LoadFile(file($bin->parent, $conf));

  my ($dsn,$user,$pass);
  eval {
    ($dsn, $user, $pass) =
      @{$config->{'DB::Schema'}->{'connect_info'}};
  };

  if ($@ ){
    die "Your DSN line in $conf doesn't look like a valid DSN."

  }

  $dsn =~ s/__HOME__/$FindBin::Bin\/\.\./g;

  my $schema = DB::Schema->connect($dsn, $user, $pass, $attrs);
  return $schema;
}

Le fichier de configuration utilisé ' test.yml' sera écrit au format YAML, il ressemblera à :

---
# Database Model
DB::Schema:
  connect_info:
     - dbi:SQLite:__HOME__/db/test.db
#DB::Schema:
#  connect_info:
#     - dbi:mysql:dbname=testdbix;host=localhost;user=user_dba;password=pw

Ainsi il suffira de décommenter/Commenter le connect_info voulu pour passer d'une base à l'autre.

Dans le répertoire ' script' nous modifierons ' create_db.pl' et ' test.pl'

#!/usr/bin/perl

use strict;

use FindBin;
use lib "$FindBin::Bin/../lib";
use DB::Schema;

my $schema = schema();
$schema->deploy;

Et

#!/usr/bin/perl

use strict;
use lib "lib";
use DB::Schema;

my $schema = schema();

# Un membre

my $member_data = {
                     username => 'joe',
                     password => 'pass1',
                     email    => 'joe@dalton.org',
                     url      => 'http://dalton.org',
                     active   => 1 };

$schema->resultset('Member')->create( $member_data );


my $user = $schema->resultset('Member')->search( { username => 'joe'} )->first;
print 'email=' . $user->email . "\n";

Voilà nos scripts de création et d'insertion tiennent à peu près la route :)

Précédement nous avons vu que la methode ' deploy' de DBIx::Class:Schema nous assitait dans la création de base de donnée, arrêtons nous un instant sur celle-ci. En fait deploy fait appel à SQL::Translator une suite de modules manipulant les données structurées et pas uniquement le SQL. Ce module est livré avec des scripts qui permettent directement de l'exploiter.

sqlt -f DBI --dsn dbi:SQLite:db/test.db -t PostgreSQL > Postgres.sql

sqlt -f PostgreSQL -t MySQL Postgres.sql > MySQL.sql

5.2. sqlt-diagram

Comme son nom l'indique ce script permet la création de diagramme d'un schema SQL

sqlt -f DBI --dsn dbi:SQLite:db/test.db -t SQLite  > SQLite.sql
sqlt-diagram --from SQLite --db=SQLite SQLite.sql -o sqlite.png

5.3. sqlt-graph

Un peu redondant avec le précédent, il permet aussi la création du graphe d'un schéma en y ajoutant les relations entre tables.

sqlt-graph --from SQLite --db=SQLite SQLite.sql -o sqlite2.png --show-datatypes --show-sizes --show-constraints --natural-join

Le module DBICx::Deploy fourni à mon sens une meilleure représentation visuelle des tables. Ce dernier est accompagné du script dbicdeploy qui peut être utilisé comme suit:

dbicdeploy -Ilib DB::Schema ./ GraphViz
mv DB-Schema-1.x-GraphViz.sql sqlite3.png

my @databases = [ qw/ MySQL SQLite PostgreSQL / ];

$schema->storage->create_ddl_dir($schema, @databases, '0.1', "./db");

ls db/
DB-Schema-0.1-MySQL.sql       DB-Schema-0.1-SQLite.sql
DB-Schema-0.1-PostgreSQL.sql  test.db

Jusqu'a maintenant nous avons surtout manipuler le schema, il est temps de revenir a ce pourquoi les bases ont été créées: le stockage des données.

Pour les manipuler nous allons dabord créer le script 'push_data.pl' qui aura pour but la création aléatoire d'utilisateur et de leur attribuer un/plusieurs rôles.

#!/usr/bin/perl


use strict;
use FindBin;

use lib "$FindBin::Bin/../lib";
use DB::Schema;

my $nb_user = 100;
my $schema = schema();

# charge la table Role
$schema->populate('Role', [
      [ qw/id name/ ],
      [ '1', 'admin' ],
      [ '2', 'edit' ],
      [ '3', 'member' ],
     ]);



while ($nb_user){

  my $username = get_rand(8);
  my $pass     = get_rand(6);
  my $domain   = get_rand(6);
  my $user_data = {
                     username => $username,
                     password => $pass,
                     email    => $username . '@' .$domain . 'org',
                     url      => 'http://' . $domain . 'org',
                     active   => 1 };

  print '.';

  # Insertion de l'utilisateur

  my $user=$schema->resultset('Member')->create( $user_data );

  # Ajout de role(s) a l'utilisateur
  for (my $r; $r<=int(rand(3)+1);$r++){
    my $role_id = int(rand(3)+1);
    my $role = {
                 member_id => $user->id,
                 role_id   => $role_id,};
    $schema->resultset('MemberRole')->find_or_create($role);
  }

  $nb_user--;
}

print "\n";

# retourne un mot de $nb_lettre lettre
sub get_rand{
  my $nb_letters = shift;

  my $rand;
  while ($nb_letters){
    $rand .= chr(int(rand(26)+97));
    $nb_letters--;
  } 
  return $rand;
}

Deux nouvelles méthodes apparaissent, ' populate' qui permet d'un coup d'un seul d'insérer plusieurs lignes de données et ' find_or_create' qui créera un nouvel enregistrement seulement si celui-ci n'est pas existant.

Il s'agit d'une simple boucle d'insertion de $nb_user utiliateurs et d'ajout de rôle(s) à chacun d'eux. Dailleurs on peut vérifier qu'il y a bien autant d'INSERT en base en éxécutant ce script en mode debug :

DBIC_TRACE=1 script/push_data.pl

Il est aussi possible de loguer les requêtes effectuées dans un fichier log :

$schema->storage->debug(1);
$schema->storage->debugfh(
     IO::File->new('sql.log', 'w')
    );

Il est possible d'éxécuter tous types de requêtes avec des WHERE, des OR, des jointures et bien d'autres ...

Je veux tous les utilisateurs dont le nom commence par un 'x' :

my $users = $schema->resultset('Member')->search( { username => { like => 'x%'}} );
print 'nb user=' . $users->count . "\n";


# Affichage du resultat
while ( my $u = $users->next ){
  print "Username:" . $u->username . "\n";
  print "Password:" . $u->password . "\n";
  print "-"x20 . "\n";
}

Et maintenant j'aimerai bien connaitre les rôles de chacun. Pour cela nous allons mettre en place une nouvelle relation spécifiant que les utilisateurs peuvent avoir plusieurs roles. Dans la table 'Member' ajoutons après :

__PACKAGE__->has_many(
  "member_roles",
  "DB::Schema::MemberRole",
  { "foreign.member_id" => "self.id" },
);

la ligne suivante

__PACKAGE__->many_to_many( roles => 'member_roles', 'role_id' );

qui stipule de retourner les rôles de la relation précédente member_roles.

Pour récupérer les roles d'un utilisateur, il suffiera d'insérer dans la boucle :

  my $roles = $u->roles;
  while( my $r =  $roles->next ){
    print "roles=" . $r->name . "\n";
  }

Pour plus d'infos sur les relationships voir : DBIx::Class::Relationship et bien sur DBIx::Class::ResultSet

C'est certainement trop court pour expliquer toutes les méthodes disponibles avec DBIx::Class, néanmoins ça permet de mettre le pied à l'étrier, l'étape suivante étant DBIx::Class::Manual::Cookbook

Disposer d'une methode qui liste tout les utilisateurs ayant le role admin, ou qui supprime tous les utilisateurs du domaine toto.org ou ...

Et bien c'est permis avec DBIx::Class::ResultSetManager qui rend possible la customisation de nos tables. Ce module est cependant considéré comme experimental.

Première chose à faire, hériter de cette fonctionnalité :

__PACKAGE__->load_components(qw/ResultSetManager Core/);

Attention a respecter l'ordre des modules.

Ensuite ajoutons la methode ' count_users_where' à la table Member :

sub count_users_where : ResultSet{
  my $self      = shift;
  my $condition = shift;

  my $rs = $self->search($condition);
  return $rs->count;
}

Attention au ( : ResultSet ) qui précise la nature de la méthode.

Et enfin dans notre script nous pouvons y faire appel comme ceci:

print "nb nbuser dont le nom se termine par un 'a':" . 
  $schema->resultset('Member')->count_users_where(
                                     { username => { like => '%a' } }
                                                 ) .
  "\n";

DBIX::Class::Schema offre la fonction create_ddl_dir parfaite pour mettre en place le versionning de nos bases et tables. Elle transforme un schema en fichier SQL avec au passage la gestion des versions :)

Lorsque l'on débute un projet le schema doit avoir une VERSION. Cette ligne est donc à ajouter à lib/DB/Schema.pm :

our $VERSION = 1;

Ensuite nous crééons le répertoire de stockage des SQL générés.

mkdir db/upgrades

Et pour finir notre script de versionning ' ./script/version_schema.pl' :

#!/usr/bin/perl -w
use strict;

use warnings;
use FindBin;
use lib "$FindBin::Bin/../lib";
use DB::Schema;

my $version = DB::Schema->VERSION;

my $schema  = schema();


$schema->create_ddl_dir(
    ['SQLite'],
    $version > 1 ? $version : undef,
    'sql',
    $version ? $version-1 : $version
);

Et c'est tout :)

On exécute une première fois ce script, il génère alors le fichier db/upgrades/DB-Schema-1-SQLite.sql qui n'est autre que le SQL de création des tables. Après avoir travailler sur le projet nous souhaitons en faire une nouvelle version. Modification du numéro de VERSION en '2' et éxécution de ./script/version_schema.pl . et on s'attend à trouver la version 2. Perdu il s'agit de la version 1-2 qui est le différentiel des deux versions. Ce SQL avec l'utilisation de table temporaire et d'ALTER permet très simplement d'upgrader le schema et le contenu des différentes versions d'un projet.