Débuter avec DBIx
Table of Contents
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.
Il s'agit d'un traducteur utilisé pour convertir un schema vers un autre. La commande suivante suivante traduit notre schema vers Postgres
sqlt -f DBI --dsn dbi:SQLite:db/test.db -t PostgreSQL > Postgres.sql
Et pourquoi pas une transformation du SQL Postgres vers du SQL Mysql :
sqlt -f PostgreSQL -t MySQL Postgres.sql > MySQL.sql
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

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
![]() |
Les deux lignes qui suffisent a créer, à l'aide de la méthode create_ddl_dir, les schemas SQL des bases MySQL, SQLite et Postgres
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.
Commentaires: