DBIx::DataModel - UML-based Object-Relational Mapping (ORM) framework
DBIx::DataModel is a framework for building Perl abstractions (classes, objects and methods) that interact with relational database management systems (RDBMS). Of course the ubiquitous DBI module is used as a basic layer for communicating with databases; on top of that, DBIx::DataModel provides facilities for generating SQL queries, joining tables automatically, navigating through the results, converting values, building complex datastructures and packaging the results in various formats.
DBIx::DataModel
More explanations are given in the "DESCRIPTION" section below, after the synopsis.
This is the 3rd generation of DBIx::DataModel. Compatibility layers with older versions may be loaded on demand. Changes are documented in DBIx::DataModel::Doc::Delta_v3, DBIx::DataModel::Doc::Delta_v2 and DBIx::DataModel::Doc::Delta_v1.
This section is a short appetizer on the main features of DBIx::DataModel. The first part is about schema declaration; the second part is about how to use that schema in database requests.
Load DBIx::DataModel.
use DBIx::DataModel;
Declare the schema, either in shorthand notation :
DBIx::DataModel->Schema('My::Schema');
or in verbose form :
DBIx::DataModel->define_schema( class => 'My::Schema', %options, );
This automatically creates a Perl class named My::Schema.
My::Schema
Various parameters may be specified within %options, like for example special columns to be filled automatically or to be ignored in every table :
%options
my $last_modif_generator = sub {$ENV{REMOTE_USER}.", ".scalar(localtime)}; my %options = ( auto_update_columns => {last_modif => $last_modif_generator}, no_update_columns => [qw/date_modif time_modif/], );
Declare a "column type" with some handlers, either in shorthand notation :
My::Schema->Type(Date => from_DB => sub {$_[0] =~ s/(\d\d\d\d)-(\d\d)-(\d\d)/$3.$2.$1/}, to_DB => sub {$_[0] =~ s/(\d\d)\.(\d\d)\.(\d\d\d\d)/$3-$2-$1/}, validate => sub {$_[0] =~ m/(\d\d)\.(\d\d)\.(\d\d\d\d)/}, );
My::Schema->metadm->define_type( name => 'Date', handlers => { from_DB => sub {$_[0] =~ s/(\d\d\d\d)-(\d\d)-(\d\d)/$3.$2.$1/}, to_DB => sub {$_[0] =~ s/(\d\d)\.(\d\d)\.(\d\d\d\d)/$3-$2-$1/}, validate => sub {$_[0] =~ m/(\d\d)\.(\d\d)\.(\d\d\d\d)/}, });
This does not create a Perl class; it just defines an internal datastructure that will be attached to some columns in some tables. Here are some other examples of column types :
# 'percent' conversion between database (0.8) and user (80) My::Schema->metadm->define_type( name => 'Percent', handlers => { from_DB => sub {$_[0] *= 100 if $_[0]}, to_DB => sub {$_[0] /= 100 if $_[0]}, validate => sub {$_[0] =~ /1?\d?\d/}), }); # lists of values, stored as scalars with a ';' separator My::Schema->metadm->define_type( name => 'Multivalue', handlers => { from_DB => sub {$_[0] = [split /;/, $_[0] || ""] }, to_DB => sub {$_[0] = join ";", @$_[0] if ref $_[0]}, }); # adding type information for the DBD handler to inform Oracle about XML data My::Schema->metadm->define_type( name => 'XML', handlers => { to_DB => sub {$_[0] = [{dbd_attrs => {ora_type => ORA_XMLTYPE}}, $_[0]] if $_[0]}, });
Declare the tables, either in shorthand notation :
# Perl Class DB Table Primary key # ========== ======== =========== My::Schema->Table(qw/Employee T_Employee emp_id /) ->Table(qw/Department T_Department dpt_id /) ->Table(qw/Activity T_Activity act_id /);
My::Schema->metadm->define_table( class => 'Employee', db_name => 'T_Employee', primary_key => 'emp_id', ); My::Schema->metadm->define_table( class => 'Department', db_name => 'T_Department', primary_key => 'dpt_id', ); My::Schema->metadm->define_table( class => 'Activity', db_name => 'T_Activity', primary_key => 'act_id', );
Each table then becomes a Perl class prefixed with the Schema name, i.e. My::Schema::Employee, etc.
My::Schema::Employee
Declare column types within these tables :
# type name => applied_to_columns # ========= ================== My::Schema::Employee->metadm->set_column_type(Date => qw/d_birth/); My::Schema::Activity->metadm->set_column_type(Date => qw/d_begin d_end/); My::Schema::Activity->metadm->set_column_type(Percent => qw/activity_rate/);
Declare associations or compositions in UML style, either in shorthand notation :
# class role multiplicity join # ===== ==== ============ ==== My::Schema->Composition([qw/Employee employee 1 emp_id /], [qw/Activity activities * emp_id /]) ->Association([qw/Department department 1 /], [qw/Activity activities * /]);
My::Schema->define_association( kind => 'Composition', A => { table => My::Schema::Employee->metadm, role => 'employee', multiplicity => 1, join_cols => [qw/emp_id/], }, B => { table => My::Schema::Activity->metadm, role => 'activities', multiplicity => '*', join_cols => [qw/emp_id/], }, ); My::Schema->define_association( kind => 'Association', A => { table => My::Schema::Department->metadm, role => 'department', multiplicity => 1, }, B => { table => My::Schema::Activity->metadm, role => 'activities', multiplicity => '*', }, );
Declare a n-to-n association
My::Schema->Association([qw/Department departments * activities department/], [qw/Employee employees * activities employee/]); # or My::Schema->define_association( kind => 'Association', A => { table => My::Schema::Department->metadm, role => 'departments', multiplicity => '*', join_cols => [qw/activities department/], }, B => { table => My::Schema::Employee->metadm, role => 'employees', multiplicity => '*', join_cols => [qw/activities employee/], }, );
For details that could not be expressed in a declarative way, just add a new method into the table class :
package My::Schema::Activity; sub active_period { my $self = shift; $self->{d_begin} or croak "activity has no d_begin"; $self->{d_end} ? "from $self->{d_begin} to $self->{d_end}" : "since $self->{d_begin}"; }
Declare how to automatically expand objects into data trees
My::Schema::Activity->metadm->define_auto_expand(qw/employee department/);
perl -MDBIx::DataModel::Schema::Generator \ -e "fromDBI('dbi:connection:string')" -- \ -schema My::New::Schema > My/New/Schema.pm
See DBIx::DataModel::Schema::Generator.
use My::Schema; use DBI; my $dbh = DBI->connect($dsn, ...); My::Schema->dbh($dbh); # single-schema mode # or my $schema = My::Schema->new(dbh => $dbh); # multi-schema mode
Search employees whose name starts with 'D' (the select API is taken from SQL::Abstract::More)
my $empl_D = My::Schema->table('Employee')->select( -where => {lastname => {-like => 'D%'}}, );
idem, but we just want a subset of the columns, and order by age.
my $empl_F = My::Schema->table('Employee')->select( -columns => [qw/firstname lastname d_birth/], -where => {lastname => {-like => 'F%'}}, -order_by => 'd_birth', );
Print some info from employees.
foreach my $emp (@$empl_D) { print "$emp->{firstname} $emp->{lastname}, born $emp->{d_birth}\n"; }
As demonstrated here, each database row is just a Perl hashref, with database columns as hash keys (unlike most other ORMs where columns are accessed through method calls).
Column 'd_birth' is directly printable because it has been automatically converted to the appropriate format through the 'from_DB' handler associated with column type 'date'.
Follow the joins through path methods
foreach my $act (@{$emp->activities}) { printf "working for %s from $act->{d_begin} to $act->{d_end}", $act->department->{name}; }
Path methods can take arguments too, like select()
select()
my $recent_activities = $dpt->activities(-where => {d_begin => {'>=' => '2005-01-01'}}); my @recent_employees = map {$_->employee(-columns => [qw/firstname lastname/])} @$recent_activities;
Export the data : get related records and insert them into a data tree in memory; then remove all class information and export that tree.
$_->expand('activities') foreach @$empl_D; My::Schema->unbless($empl_D); my $export = {employees => $empl_D}; use Data::Dumper; print Dumper ($export); # export as PerlDump use XML::Simple; print XMLout ($export); # export as XML use JSON; print to_json($export); # export as Javascript use YAML; print Dump ($export); # export as YAML
Note: the unbless step is optional; it is proposed here because some exporter modules will not work if they encounter a blessed reference.
unbless
Select columns from several tables through a database join
my $lst = My::Schema->join(qw/Employee activities department/) ->select(-columns => [qw/lastname dept_name d_begin/], -where => {d_begin => {'>=' => '2000-01-01'}});
Same thing, but forcing INNER joins
my $lst = My::Schema->join(qw/Employee <=> activities <=> department/) ->select(...);
Instead of retrieving directly a list of records, get a statement :
my $statement = My::Schema->join(qw/Employee activities department/) ->select(-columns => [qw/lastname dept_name d_begin/], -where => {d_begin => {'>=' => '2000-01-01'}}, -result_as => 'statement');
Retrieve a single row from the statement
my $single_row = $statement->next or die "no more records";
Retrieve several rows at once
my $rows = $statement->next(10); # arrayref
Go to a specific page and retrieve the corresponding rows
my $statement = My::Schema->join(qw/Employee activities department/) ->select(-columns => [qw/lastname dept_name d_begin/], -result_as => 'statement', -page_size => 10); $statement->goto_page(3); # absolute page positioning $statement->shift_pages(-2); # relative page positioning my ($first, $last) = $statement->page_boundaries; print "displaying rows $first to $last:"; some_print_row_method($_) foreach @{$statement->page_rows};
For fetching related rows : prepare a statement before the loop, execute it at each iteration.
my $statement = $schema->table($name)->join(qw/path1 path2/); $statement->prepare(-columns => ..., -where => ...); my $list = $schema->table($name)->select(...); foreach my $obj (@$list) { my $related_rows = $statement->execute($obj)->all; # or my $related_rows = $statement->bind($obj)->select; ... }
Fast statement : each data row is retrieved into the same memory location (avoids the overhead of allocating a hashref for each row). This is faster, but such rows cannot be accumulated into an array (they must be used immediately) :
my $fast_stmt = ..->select(..., -result_as => "fast_statement"); while (my $row = $fast_stmt->next) { do_something_immediately_with($row); }
my $json = $source->select(..., -result_as => 'json'); my $yaml = $source->select(..., -result_as => 'yaml'); my $tree = $source->select(..., -result_as => [categorize => ($key1, $key2)]); $source->select(..., -result_as => [tsv => $name_of_tab_separated_file]); $source->select(..., -result_as => [xlsx => $name_of_Excel_file]); ...
my $table = $schema->table($table_name); # if the primary key is supplied by the client $table->insert({pk_column => $pk_val, col1 => $val1, col2 => $val2, ...}); # if the primary key is generated by the database my $id = $table->insert({col1 => $val1, col2 => $val2, ...}); # This assumes your DBD driver implements last_insert_id. # insert multiple records using a list of arrayrefs -- first arrayref # defines column names $table->insert( [qw/ col1 col2 /], [qw/ val11 val12 /], [qw/ val22 val22 /], ); # alternatively, insert multiple records expressed as a list of hashes $table->insert( {col1 => val11, col2 => val12}, {col1 => val21, col2 => val22}, ); # insert into a related table (the foreign key will be filled automatically) $an_employee->insert_into_activities({d_begin => $today, dpt_id => $dpt});
# update on a set of columns, primary key included my $table = $schema->table($table_name); $table->update({pk_column => $pk, col1 => $val1, col2 => $val2, ...}); # update on a set of columns, primary key passed separately $table->update(@primary_key, {col1 => $val1, col2 => $val2, ...}); # update all records matching some condition $table->update(-set => {col1 => $val1, col2 => $val2, ...}, -where => \%condition); # update() invoked as instance method instead of class method $obj->update({col1 => $val1, ...}); # updates specified columns $obj->update; # updates all columns stored in memory
# delete() as class method my $table = $schema->table($table_name); $table->delete(@primary_key); # delete all records matching some condition $table->delete(-where => \%condition); # delete() as instance method $obj->delete;
There are many other CPAN modules offering somewhat similar features, like DBIx::Class, Rose::DB::Object, Jifty::DBI, Fey::ORM, just to name a few ... there is more than one way to do it! Frameworks in this family are called object-relational mappings (ORMs) -- see http://en.wikipedia.org/wiki/Object-relational_mapping.
The DESIGN chapter of this documentation explains the main design decisions of DBIx::DataModel. Some of the strong points are :
centralized, UML-style declaration of tables and relationships (instead of many files with declarations such as 'has_many', 'belongs_to', etc.)
limited coupling with the database schema : there is no need to declare every column of every table; DBIx::DataModel only needs to know about tables, associations, primary keys and foreign keys
exposure of database operations like joins, bulk updates, subqueries, etc. The database is not hidden behind object-oriented programming concepts, as some other ORMs try to do, but rather made to explicitly collaborate with the object-oriented layer.
efficiency through a very lightweight infrastructure (row objects are just blessed hashrefs) and through fine tuning of interaction with the DBI layer (prepare/execute, fetch into reusable memory location, etc.)
usage of SQL::Abstract::More for an improved API over SQL::Abstract (named parameters, additional clauses, simplified 'order_by', support for values with associated datatypes, etc.)
clear conceptual distinction between
data sources (tables and joins),
database statements (stateful objects representing stepwise building of an SQL query and stepwise retrieval of results),
data rows (lightweight hashrefs containing nothing but column names and values)
simple syntax for joins, with the possibility to override default INNER JOIN/LEFT JOIN properties, and with clever usage of Perl multiple inheritance for simultaneous access to the methods of all tables that participate in that join
nested, cross-database transactions
choice between 'single-schema' mode (default, more economical) and 'multi-schema' mode (optional, more flexible, but a little more costly in memory)
Here are some limitations of DBIx::DataModel, in comparison with other Perl ORMs :
DBIx::DataModel knows very little about the database schema (only tables, primary and foreign keys, and possibly some columns, if they need special 'Types'); therefore it provides no support for schema changes (and seldom needs to know about them).
DBIx::DataModel does not keep track of data mutations in memory, and therefore provides no support for automatically propagating changes into the database; the client code has explicitly manage insert and update operations.
insert
update
Cascaded inserts and deletes are supported, but not cascaded updates. This would need 'insert or update', which is not supported.
Although the basic principles are quite simple, there are many details to discuss, so the documentation is quite long. In an attempt to accommodate for different needs of readers, the documentation has been structured as follows :
The DESIGN chapter covers the architecture of DBIx::DataModel, its main distinctive features and the motivation for such features; it is of interest if you are comparing various ORMs, or if you want to globally understand how DBIx::DataModel works, and what it can or cannot do. This chapter also details the concept of statements.
The QUICKSTART chapter is a guided tour that summarizes the main steps to get started with the framework.
The REFERENCE chapter is a complete reference to all methods, structured along usage steps : creating a schema, populating it with table and associations, parameterizing the framework, and finally data retrieval and manipulation methods.
The COOKBOOK chapter provides some recipes for common ORM tasks.
The INTERNALS chapter documents the internal structure of the framework, for programmers who might be interested in extending it.
The GLOSSARY defines terms used in this documentation, and points to the software constructs that implement these terms.
The DELTA_v3, DELTA_v2 and DELTA_v1 chapters summarize the differences between major versions.
The DBIx::DataModel::Schema::Generator documentation explains how to automatically generate a schema from a DBI connection, from a SQL::Translator description or from an existing DBIx::Class|DBIx::Class schema.
DBI
DBIx::Class|DBIx::Class
Presentation slides are also available at http://www.slideshare.net/ldami/dbix-datamodel-endetail
Upon loading, DBIx::DataModel::Join adds a coderef into global @INC (see "require" in perlfunc), so that it can generate a class on the fly when retrieving frozen objects from "thaw" in Storable. This should be totally harmless unless you do some very special things with @INC.
@INC
Bugs should be reported via the CPAN bug tracker at http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBIx-DataModel.
Sources are stored in an open repository at http://github.com/damil/DBIx-DataModel.
Laurent Dami, <dami AT cpan DOT org<gt>
Thanks to
Ross Attril Cedric Bouvier Terrence Brannon Alex Solovey Sergiy Zuban
who contributed with ideas, bug fixes and/or improvements.
Copyright 2005-2018 by Laurent Dami.
This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
To install DBIx::DataModel, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::DataModel
CPAN shell
perl -MCPAN -e shell install DBIx::DataModel
For more information on module installation, please visit the detailed CPAN module installation guide.