NAME
DBIx::DataModel::Doc::Cookbook - Helpful recipes
DOCUMENTATION CONTEXT
This chapter is part of the DBIx::DataModel manual.
DESCRIPTION
This chapter provides some recipes for common ORM tasks.
SCHEMA DECLARATION
Automatically generate a schema
A schema skeleton can be produced automatically from the following external sources : a DBI connection, a SQL::Translator parser, or a DBIx::Class schema. See DBIx::DataModel::Schema::Generator.
Object inflation/deflation
Here is an example of inflating/deflating a scalar value from the database into a Perl object :
# declare column type
use Date::Simple;
$schema->ColumnType(Date_simple =>
fromDB => sub {Date::Simple->new($_[0]) if $_[0] },
toDB => sub {$_[0] = $_[0]->as_str if $_[0] },
);
# apply column type to columns
My::Table1->ColumnType(Date_simple => qw/d_start d_end/);
My::Table2->ColumnType(Date_simple => qw/d_birth/);Caveat: the fromDB / toDB functions do not apply automatically within -where conditions. So the following would not work :
use Date::Simple qw/today/;
my $rows = My::Table->select(-where => {d_end => {'<' => today()}});because today() returns a Date::Simple object that will not be understood by SQL::Abstract when generating the SQL query. DBIx::DataModel is not clever enough to inspect the -where conditions and decide which column types to apply, so you have to do it yourself :
my $today = today()->as_str;
my $rows = My::Table->select(-where => {d_end => {'<' => $today}});Schema versioning
Currently DBIx::DataModel has no specific support for schema versioning. Choose DBIx::Class instead.
DATA RETRIEVAL
Aggregator functions
Use normal SQL syntax for aggregators, and give them column aliases (with a vertical bar |) in order to retrieve the results.
my $row = $source->select(-columns => [qw/MAX(col1)|max_col1
AVG(col2)|foo
COUNT(DISTINCT(col3))|bar/],
-where => ...,
-resultAs => 'firstrow');
print "max is : $row->{max_col1}, average is $row->{foo}";Or you can dispense with column aliases, and retrieve the results directly into an arrayref, using -resultAs => 'flat_arrayref' :
my $array_ref = $source->select(-columns => [qw/MAX(col1)
AVG(col2)
COUNT(DISTINCT(col3))/],
-where => ...,
-resultAs => 'flat_arrayref');
my ($max_col1, $avg_col2, $count_col3) = @$array_ref;Caveat: currently, fromDB handlers do not apply to aggregator functions (this might be improved in a future version).
Database functions or stored procedures
Like above: normal SQL syntax and column aliases.
my $rows = $source->select(-columns => [qw/FOOBAR(col1,col2)|foobar
(col3+99)|big_col3/],
-where => ...,
);
print "$_->{foobar} and $_->{big_col3}" foreach @$rows;Nested queries
my $subquery = $source1->select(..., -resultAs => 'subquery');
my $rows = $source2->select(
-columns => ...,
-where => {foo => 123, bar => {-not_in => $subquery}}
);Hashref inflation
There is no need for a hashref inflator: rows returned by a select() can be used directly as hashrefs. For example here is a loop that prints a hash slice from each row :
my $rows = My::Table->select(...);
my @print_cols = qw/col3 col6 col7/;
foreach my $row (@$rows) {
print @{$row}{@print_cols};
}In fact, each row is a blessed hashref. This can be a problem with some external modules like JSON that croaks when encoding a blessed reference. In that case you can use the unbless function
foreach my $row (@$rows) {
$schema->unbless($row);
print to_json($row);
}Custom SQL
Create a DBIx::DataModel::View to encapsulate your SQL.
DATA UPDATE
Transaction
# anonymous sub containing the work to do
my $to_do = sub {
$table1->insert(...);
$table2->delete(...);
};
# so far nothing has happened in the database
# now do the transaction
$schema->doTransaction($to_do);Nested transaction
$schema->doTransaction(sub {
do_something;
$schema->doTransaction(sub {
some_nested_code();
});
$schema->doTransaction(sub {
some_other_nested_code();
});
});Generating random keys
Override the _singleInsert method
package MySchema::SomeTable;
sub _singleInsert {
my ($self) = @_;
my $class = ref $self;
my ($key_column) = $class->primKey;
for (1..$MAX_ATTEMPTS) {
my $random_key = int(rand($MAX_RANDOM));
$self->{$key_column} = $random_key;
eval {$self->_rawInsert; 1}
and return $random_key; # SUCCESS
# if duplication error, try again; otherwise die
last unless $DBI::errstr =~ $DUPLICATE_ERROR;
}
croak "cannot generate a random key for $class: $@";
}Cascaded insert
First insert an arrayref of subrecords within the main record hashref; then call insert on that main record. See example in insert() reference. The precondition for this to work is that the relationship between the two classes should be a Composition. A datastructure containing the keys of all generated records can be retrieved by using the option
my $tree_of_keys = $table->insert(..., -returning => {});Cascaded delete
# first gather information tree from the database
my $author = Author->fetch($author_id);
my $distribs = $author->expand('distributions');
$_->expand('modules') foreach @$distribs;
# then delete the whole tree from the database
$author->delete;This only works if the relationship between classes is a Composition. The expand operations retrieve related records and add them into a tree in memory. Then delete removes from the database all records found in the tree; therefore this is not a "true" cascaded delete, because the client code is responsible for fetching the related records.
True cascaded delete is best implemented directly in the database, rather than at the ORM layer.
Timestamp validation
[WRITE EXAMPLE ]
(make sure that the record was not touched between the time it was presented to the user (display form) and the time the user wants to update or delete that record).