Laurent Dami > DBIx-DataModel > DBIx::DataModel

Download:
DBIx-DataModel-2.43.tar.gz

Dependencies

Annotate this POD

CPAN RT

Open  0
View/Report Bugs
Module Version: 2.43   Source  

NAME ^

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

VERSION ^

Version 2 of DBIx::DataModel is a major refactoring from versions 1.*, with a number of incompatible changes in the API (classes renamed, arguments renamed or reorganized, etc. -- see DBIx::DataModel::Doc::Delta_v2).

Initial subversions of the 2.* family included a layer of compatibility with version 1.*, so that old applications would continue to work (see DBIx::DataModel::Compatibility::V1). Since version 2.20, this compatibility layer is no longer loaded automatically; however, it can still be added on demand by writing

  use DBIx::DataModel -compatibility => 1.0;

SYNOPSIS ^

in file "My/Schema.pm"

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 SQL type information for the DBD handler
  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 :

  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, on top of the linking table

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

Additional methods

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 "myClient.pl"

Database connection

  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' (select API is taken from SQL::Abstract)

  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. Because of the 'from_DB' handler associated with column type 'date', column 'd_birth' has been automatically converted to display format.

  foreach my $emp (@$empl_D) {
    print "$emp->{firstname} $emp->{lastname}, born $emp->{d_birth}\n";
  }

Methods to follow joins

Follow the joins through role methods

  foreach my $act (@{$emp->activities}) {
    printf "working for %s from $act->{d_begin} to $act->{d_end}", 
      $act->department->{name};
  }

Role 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 $export = My::Schema->unbless({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 associated tables directly from a database join, in one single SQL statement (instead of iterating through role methods).

  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 or 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/role1 role2/);
  $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). 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);
  }

Insert

  my $table = $schema->table($table_name);

  #  If you provide the primary key (called 'my_code' in this example):
  $table->insert({my_code => $pk_val, field1 => $val1, field2 => $val2, ...});

  #  If your database provides the primary key:
  my $id = $table->insert({field1 => $val1, field2 => $val2, ...});
  #  This assumes your DBD driver implements last_insert_id.
  #  If not, you can provide one as an option to the schema.

  #  Insert multiple records using a list of arrayrefs.
  #  First arrayref defines column names
  $table->insert(
                  [qw/  field1  field2  /],
                  [qw/  val11   val12   /],
                  [qw/  val22   val22   /],
                );

  #  Or just insert a list of hashes
  $table->insert(
      {field1 => val11, field2 => val12},
      {field2 => val21, field2 => val22},
  );

  # insertion through the association Employee - Activity
  $an_employee->insert_into_activities({d_begin => $today,
                                        dpt_id  => $dpt});

Update

  # update on a set of fields, primary key included
  my $table = $schema->table($table_name);
  $table->update({pk_field => $pk, field1 => $val1, field2 => $val2, ...});

  # update on a set of fields, primary key passed separately
  $table->update(@primary_key, {field1 => $val1, field2 => $val2, ...});

  # bulk update
  $table->update(-set   => {field1 => $val1, field2 => $val2, ...},
                 -where => \%condition);

  # invoking instances instead of table classes
  $obj->update({field1 => $val1, ...}); # updates specified fields
  $obj->update;                         # updates all fields stored in memory

Delete

  # invoking a table class
  my $table = $schema->table($table_name);
  $table->delete(@primary_key);

  # invoking an instance
  $obj->delete;

DESCRIPTION ^

Introduction

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, and building complex datastructures so that other modules can conveniently exploit the data.

Perl ORMs

There are many other CPAN modules offering somewhat similar features, like Class::DBI, DBIx::Class, Tangram, Rose::DB::Object, Jifty::DBI, Fey::ORM, just to name a few well-known alternatives. Frameworks in this family are called object-relational mappings (ORMs) -- see http://en.wikipedia.org/wiki/Object-relational_mapping. The mere fact that Perl ORMs are so numerous demonstrates that there is more than one way to do it!

For various reasons, none of these did fit nicely in my context, so I decided to write DBIx:DataModel. Of course there might be also some reasons why DBIx:DataModel will not fit in your context, so just do your own shopping. Comparing various ORMs is complex and time-consuming, because of the many issues and design dimensions involved; as far as I know, there is no thorough comparison summary, but here are some pointers :

Strengths of DBIx::DataModel

The DESIGN chapter of this documentation will help you understand the philosophy of DBIx::DataModel. Just as a summary, here are some of its strong points :

DBIx::DataModel is used in production within a mission-critical application with several hundred users, for managing Geneva courts of law.

Limitations

Here are some current limitations of DBIx::DataModel :

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 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 at the moment is not supported either.

Backwards compatibility

Major version 2.0 and 1.0 introduced some incompatible changes in the architecture (see DBIx::DataModel::Doc::Delta_v2 and DBIx::DataModel::Doc::Delta_v1).

Compatibility layers can be loaded on demand by supplying the desired version number upon loading DBIx::DataModel :

  use DBIx::DataModel 2.0 -compatibility => 0.8;

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 accomodate for different needs of readers, it has been structured as follows :

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 take control and 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.

There is a discussion group at http://groups.google.com/group/dbix-datamodel.

Sources are stored in an open repository at http://github.com/damil/DBIx-DataModel.

AUTHOR ^

Laurent Dami, <laurent.dami AT etat ge ch>

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 ^

Copyright 2006-2013 by Laurent Dami.

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

syntax highlighting: