NAME

DBIx::DataModel - UML-based Object-Relational Mapping (ORM) framework

INTRODUCTION

DBIx::DataModel is a framework for building Perl abstractions (classes, objects and methods) that interact with relational database management systems (RDBMS). Working above the DBI module, it 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.

This framework is complex and rich in functionalities, so a fair amount of time is needed for discovering the documentation and experimenting with the various features.

More explanations are given in the "DESCRIPTION" section below, after the synopsis.

VERSION

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.

SYNOPSIS

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.

in file "My/Schema.pm" (schema declaration)

Schema

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.

Various parameters may be specified within %options, like for example special columns to be filled automatically or to be ignored in every table :

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/],
);

Types

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)/},
 );

or in verbose form :

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]},
  });

Tables

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     /);

or in verbose form :

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.

Column types within tables

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/);

Associations

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 *                  /]);

or in verbose form :

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/],
  },
);

Adding methods into the generated classes

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}";
}

Data tree expansion

Declare how to automatically expand objects into data trees

My::Schema::Activity->metadm->define_auto_expand(qw/employee department/);

Automatic schema generation

perl -MDBIx::DataModel::Schema::Generator      \
     -e "fromDBI('dbi:connection:string')" --  \
     -schema My::New::Schema > My/New/Schema.pm

See DBIx::DataModel::Schema::Generator.

in file "my_client.pl" (schema usage)

Connecting the schema to the database

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

Simple data retrieval

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'.

Methods to follow joins

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()

my $recent_activities
  = $dpt->activities(-where => {d_begin => {'>=' => '2005-01-01'}});
my @recent_employees
  = map {$_->employee(-columns => [qw/firstname lastname/])}
        @$recent_activities;

Data export : just regular hashrefs

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.

Database join

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(...);

Statements and pagination

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};

Efficient use of statements

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);
}

Other kinds of results

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]);
...

Insert

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

# 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

# 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;

DESCRIPTION

Perl ORMs

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.

Strengths of `DBIx::DataModel`

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)

Limitations

Here are some limitations of DBIx::DataModel, in comparison with other Perl ORMs :

no schema versioning: 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).
no object caching nor 'dirty columns': 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 to explicitly manage insert and update operations.
no 'cascaded update' nor 'insert or update': Cascaded inserts and deletes are supported, but not cascaded updates. This would need 'insert or update', which is not supported.

INDEX TO THE DOCUMENTATION

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.

Presentation slides are also available at http://www.slideshare.net/ldami/dbix-datamodel-endetail

SIDE-EFFECTS

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.

SUPPORT AND CONTACT

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.

AUTHOR

Laurent Dami, <dami AT cpan DOT org<gt>

ACKNOWLEDGEMENTS

Thanks to

Ross Attril
Cedric Bouvier
Terrence Brannon
Alex Solovey
Sergiy Zuban

who contributed with ideas, bug fixes and/or improvements.

COPYRIGHT AND LICENSE

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.

	Global
`s`	Focus search bar
`?`	Bring up this help dialog

	GitHub
`g` `p`	Go to pull requests
`g` `i`	go to github issues (only if github is preferred repository)

	POD
`g` `a`	Go to author
`g` `c`	Go to changes
`g` `i`	Go to issues
`g` `d`	Go to dist
`g` `r`	Go to repository/SCM
`g` `s`	Go to source
`g` `b`	Go to file browse

	Search terms
module: (e.g. module:Plugin)
distribution: (e.g. distribution:Dancer auth)
author: (e.g. author:SONGMU Redis)
version: (e.g. version:1.00)

NAME

INTRODUCTION

VERSION

SYNOPSIS

in file "My/Schema.pm" (schema declaration)

Schema

Types

Tables

Column types within tables

Associations

Adding methods into the generated classes

Data tree expansion

Automatic schema generation

in file "my_client.pl" (schema usage)

Connecting the schema to the database

Simple data retrieval

Methods to follow joins

Data export : just regular hashrefs

Database join

Statements and pagination

Efficient use of statements

Other kinds of results

Insert

Update

Delete

DESCRIPTION

Perl ORMs

Strengths of DBIx::DataModel

Limitations

INDEX TO THE DOCUMENTATION

SIDE-EFFECTS

SUPPORT AND CONTACT

AUTHOR

ACKNOWLEDGEMENTS

COPYRIGHT AND LICENSE

Module Install Instructions

Strengths of `DBIx::DataModel`