NAME

Class::DBI::Lite - Lightweight ORM for Perl

SYNOPSIS

Please take a look at Class::DBI::Lite::Tutorial for an introduction to using this module.

DESCRIPTION

Class::DBI::Lite offers a simple way to deal with databases in an object-oriented way.

One class (the Model class) defines your connection to the database (eg: connectionstring, username and password) and your other classes define interaction with one table each (your entity classes).

The Entity classes subclass the Model class and automatically inherit its connection.

Class::DBI::Lite relies heavily on Ima::DBI::Contextual, SQL::Abstract and Scalar::Util.

Class::DBI::Lite does not leak memory and is well-suited for use within mod_perl, Fast CGI, CGI and anywhere else you might need it.

BACKGROUND

I used Class::DBI for a few years, a few years ago, on a very large project, under mod_perl. This was back in 2002-2003 when the ORM (Object-Relational Mapper) scene was still fairly new.

While it saved me a great deal of typing, I was amazed at the complexity of Class::DBI's internal code. After some time I found myself spending more effort working around problems caused by Class::DBI than I could stand.

Many people encountered the same problems I encountered (transactions, database connection sharing issues, performance, etc) and they all went and began writing DBIx::Class.

DBIx::Class went in a direction away from the database while I wanted to get closer to the database. As close as I could possibly get without wasting time. I also wanted to keep some simple logic in my Entity classes (those classes that represent individual tables). I didn't want my ORM to do too much magic, think too much or do anything not immediately apparent. I didn't care about many-to-many relationships or automatic SQL join clauses. Vendor-specific LIMIT expressions simply were not a concern of mine.

So...I reimplemented (most) of the Class::DBI interface in a way that I preferred. I left out some things that didn't matter to me (eg: many-to-many relationships, column groups) and added some things I needed frequently (eg: transactions, single-field triggers, mod_perl compatibility).

PHILOSOPHY

Class::DBI::Lite is intended to minimize the boiler-plate code typically written in most applications. It is not intended to completely insulate developers from interacting with the database directly.

Class::DBI::Lite is not a way to avoid learning SQL - it is a way to avoid writing boring, repetitive, "boiler-plate" SQL.

PUBLIC PROPERTIES

connection( $dsn, $username, $password )

Sets the DSN for your classes.

package App::db::model;

use base 'Class::DBI::Lite::mysql';

__PACKAGE__->connection('DBI:mysql:dbname:localhost', 'username', 'password' );

db_Main

Returns the active database handle in use by the class.

Example:

my $dbh = App::db::artist->db_Main;
my $sth = $dbh->prepare("select * from artists");
$sth->execute();
...

table

Returns the name of the table that the class is assigned to.

Example:

print App::db::artist->table; # 'artists'

columns

Returns a list of field names in the table that the class represents.

Given the following table:

create table artists (
  artist_id   integer unsigned not null primary key auto_increment,
  name        varchar(100) not null,
) engine=innodb charset=utf8;

We get this:

print join ", ", App::db::artist->columns;
# artist_id, name

trace( 1:0 )

(New in version 1.018)

Setting trace to 1 or 0 will turn on or off SQL logging to STDERR.

Example:

# Start seeing all the SQL:
App::db::artist->trace( 1 );

# We will see some SQL when the next line is executed:
my @users = App::db::artist->search_like( name => 'Rob%' );

# Turn it off again:
App::db::artist->trace( 0 );

By default, trace is turned off.

STATIC METHODS

create( %info )

Creates a new object and returns it.

Example:

my $artist = App::db::artist->create( name => 'Bob Marley' );

find_or_create( %info )

Using %info a search will be performed. If a matching result is found it is returned. Otherwise a new record will be created using %info as arguments.

Example:

my $artist = App::db::artist->find_or_create( name => 'Bob Marley' );

retrieve( $id )

Given the id of a record in the database, returns that object.

Example:

my $artist = App::db::artist->retrieve( 1 );

Same as the following SQL:

SELECT *
FROM artists
WHERE artist_id = 1

retrieve_all( )

Returns all objects in the database table.

Example:

my @artists = App::db::artist->retrieve_all;

Same as the following SQL:

SELECT * FROM artists

NOTE: If you want to sort all of the records or do paging, use search_where like this:

my @artists = App::db::artist->search_where({ 1 => 1}, {order_by => 'name DESC'});

Same as the following SQL:

SELECT *
FROM artists
WHERE 1 = 1
ORDER BY name DESC

That "WHERE 1 = 1" is a funny way of telling the database "give them all to me".

has_many( ... )

Declares a "one-to-many" relationship between this two classes.

package App::db::artist;
...
__PACKAGE__->has_many(
  albums  =>
    'App::db::album' =>
      'album_id'
);

The syntax is:

__PACKAGE__->has_many(
  $what_they_are_called =>
    $the_class_name =>
      $the_foreign_key_field_from_the_other_class
);

The result is this:

my @albums = $artist->albums;
$artist->add_to_albums( name => 'Legend' );

That's the same as:

my @albums = App::db::artist->search(
  artist_id => $artist->id
);

belongs_to( ... )

Declares that instances "this" class exists only as a feature of instances of another class.

For example, "songs" exist as features of "albums" - not the other way around.

Example:

package App::db::album;
...
__PACKAGE__->belongs_to(
  artist  =>
    'App::db::artist' =>
      'artist_id'
);

So that's:

__PACKAGE__->belongs_to(
  $the_method_name =>
    $the_class_name =>
      $my_foreign_key_field
);

construct( $hashref )

Blesses the object into the given class, even if we don't have all the information about the object (as long as we get its primary field value).

Example:

for( 1..5 ) {
  my $artist = App::db::artist->construct({ artist_id => $_ });
  
  # name is automatically "fleshed out":
  print $artist->name;
}

eval { do_transaction( sub { ... } ) }

Executes a block of code within the context of a transaction.

Example:

# Safely update the name of every album:
eval {
  App::db::artist->do_transaction( sub {
  
    # Your transaction code goes here:
    my $artist = App::db::artist->retrieve( 1 );
    foreach my $album ( $artist->albums ) {
      $album->name( $artist->name . ': ' . $album->name );
      $album->update;
    }
  });
};

if( $@ ) {
  # There was an error:
  die $@;
}
else {
  # Everything was OK:
}

search( %args )

Returns any objects that match all elements in %args.

Example:

my @artists = App::db::artist->search( name => 'Bob Marley' );

my $artist_iterator = App::db::artist->search( name => 'Bob Marley' );

Returns an array in list context or a Class::DBI::Lite::Iterator in scalar context.

search_like( %args )

Returns any objects that match all elements in %args using the LIKE operator.

Example:

my @artists = App::db::artist->search_like( name => 'Bob%' );

my $artist_iterator = App::db::artist->search_like( name => 'Bob%' );

Returns an array in list context or a Class::DBI::Lite::Iterator in scalar context.

Both examples would execute the following SQL:

SELECT * FROM artists WHERE name LIKE 'Bob%'

search_where( \%args, [\%sort_and_limit] )

Returns any objects that match all elements in %args as specified by %sort_and_limit.

Returns an array in list context or a Class::DBI::Lite::Iterator in scalar context.

Example 1:

my @artists = App::db::artist->search_where({
  name => 'Bob Marley'
});

Same as this SQL:

SELECT *
FROM artists
WHERE name = 'Bob Marley'

Example 2:

my @artists = App::db::artist->search_where({
  name => 'Bob Marley'
}, {
  order_by => 'name ASC LIMIT 0, 10'
});

Same as this SQL:

SELECT *
FROM artists
WHERE name = 'Bob Marley'
ORDER BY name
LIMIT 0, 10

Example 3:

my @artists = App::db::artist->search_where([
  name => { '!=' => 'Bob Marley' },
  genre => 'Rock',
]);

Same as this SQL:

SELECT *
FROM artists
WHERE name != 'Bob Marley'
OR genre = 'Rock'

Because search_where uses SQL::Abstract to generate the SQL for the database, you can look there for more detailed examples.

Specifying OrderBy, Limit and Offset separately:

my @artists = App::db::artist->search_where({
  name => 'Bob Marley'
}, {
  order_by  => 'name ASC',
  limit     => $how_many,
  offset    => $start_where,
});

So if your $how_many were 10, and your $start_where were zero (0) then that would be the same as:

SELECT *
FROM artists
WHERE name = 'Bob Marley'
ORDER BY name ASC
LIMIT 0, 10

count_search( %args )

Returns the number of records that match %args.

Example:

my $count = App::db::album->count_search( name => 'Greatest Hits' );

count_search_like( %args )

Returns the number of records that match %args using the LIKE operator.

Example:

my $count = App::db::artist->count_search_like(
  name  => 'Bob%'
);

count_search_where( \%args )

Returns the number of records that match \%args.

Examples:

my $count = App::db::album->count_search_where({
  name  => { LIKE => 'Best Of%' }
});

my $count = App::db::album->count_search_where({
  genre => { '!=' => 'Country/Western' }
});

As with search_where(), the count_search_where() class method uses SQL::Abstract to generate the SQL for the database.

sth_to_objects( $sth )

Takes a statement handle that is ready to fetch records from. Returns the results as objects.

Example:

my $sth = App::db::artist->db_Main->prepare("SELECT * FROM artists");
$sth->execute();
my @artists = App::db::artist->sth_to_objects( $sth );

This method is very useful for when your SQL query is too complicated for search_where().

add_trigger( $event => \&sub )

Specifies a callback to be executed when a specific event happens.

Examples:

package App::db::artist;
...
__PACKAGE__->add_trigger( after_create => sub {
  my ($self) = @_;
  
  warn "You just created a new artist: " . $self->name;
});

There are 6 main trigger points at the class level and 2 trigger points for every field:

Class Triggers

before_create( $self )

Called just before a new record is created. $self is a hashref blessed into the object's class and contains only the values that were provided for its creation.

So, given this trigger:

package App::db::album;
...
__PACKAGE__->add_trigger( before_create => sub {
  my ($self) = @_;
  
  warn "ID = '$self->{album_id}', Name = '$self->{name}";
});

If we ran this code:

my $album = App::db::album->create( name => 'Legend' );

We would see this output:

ID = '', Name = 'Legend'

Because the value for album_id has not been assigned by the database it does not yet have a value.

after_create( $self )

Called just after a new record is created. $self is the new object itself.

So given this trigger:

package App::db::album;
...
__PACKAGE__->add_trigger( after_create => sub {
  my ($self) = @_;
  
  warn "ID = '$self->{album_id}', Name = '$self->{name}";
});

If we ran this code:

my $album = App::db::album->create( name => 'Legend' );

We would see this output:

ID = '1', Name = 'Legend'

before_update( $self )

Called just before changes are saved to the database. $self is the object to be updated.

Example:

package App::db::album;
...
__PACKAGE__->add_trigger( before_update => sub {
  my ($self) = @_;
  
  warn "About to update album " . $self->name;
});

after_update( $self )

Called just after changes are saved to the database. $self is the object that was updated.

Example:

package App::db::album;
...
__PACKAGE__->add_trigger( after_update => sub {
  my ($self) = @_;
  
  warn "Finished updating album " . $self->name;
});

NOTE: If you make changes to $self from within an after_update you could enter into a recursive loop in which an update is made that causes an update to be made which causes an update to be made which causes an update to be made which causes an update to be made which causes an update to be made which causes an update to be made which causes an update to be made which causes an update to be made which causes an update to be made which causes an update to be made which causes an update to be made which causes an update to be made which causes an update to be made which causes an update to be made which causes an update to be made which causes an update to be made which causes an update to be made which causes an update to be made which causes an update to be made which...and so on.

DO NOT DO THIS:

package App::db::album;
...
__PACKAGE__->add_trigger( after_update => sub {
  my ($self) = @_;
  
  # This will cause problems:
  warn "Making a recursive problem:";
  $self->name( 'Hello ' . rand() );
  $self->update;
});

before_delete( $self )

Called just before something is deleted.

Example:

package App::db::album;
...
__PACKAGE__->add_trigger( before_delete => sub {
  my ($self) = @_;
  
  warn "About to delete " . $self->name;
});

after_delete( {$primary_field => $id} )

Called just after something is deleted.

NOTE: Since the object itself is deleted from the database and memory, all that is left is the id of the original object.

So, given this trigger...

package App::db::album;
...
use Data::Dumper;
__PACKAGE__->add_trigger( after_delete => sub {
  my ($obj) = @_;
  
  warn "Deleted an album: " . Dumper($obj);
});

...we might see the following output:

Deleted an album: $VAR1 = {
  album_id => 123
};

Field Triggers

before_update_<field>( $self, $old_value, $new_value )

Called just before a field's value is updated.

So, given the following trigger...

package App::db::album;
...
__PACKAGE__->add_trigger( before_update_name => sub {
  my ($self, $old_value, $new_value) = @_;
  
  warn "About to change name from '$old_value' to '$new_value'";
});

...called with the following code...

my $artist = App::db::artist->create( name => 'Bob Marley' );
my $album = $artist->add_to_albums( name => 'Legend' );

# Now change the name:
$album->name( 'Greatest Hits' );
$album->update; # <--- the trigger is called right here.

...we would see the following output:

About to change the name from 'Legend' to 'Greatest Hits'

after_update_<field>( $self, $old_value, $new_value )

Called just after a field's value is updated.

So, given the following trigger...

package App::db::album;
...
__PACKAGE__->add_trigger( after_update_name => sub {
  my ($self, $old_value, $new_value) = @_;
  
  warn "Changed name from '$old_value' to '$new_value'";
});

...called with the following code...

my $artist = App::db::artist->create( name => 'Bob Marley' );
my $album = $artist->add_to_albums( name => 'Legend' );

# Now change the name:
$album->name( 'Greatest Hits' );
$album->update; # <--- the trigger is called right here.

...we would see the following output:

Changed the name from 'Legend' to 'Greatest Hits'

find_column( $name )

Returns the name of the column, if the class has that column.

Example:

if( App::db::artist->find_column('name') ) {
  warn "Artists have names!";
}

get_table_info( )

Returns a Class::DBI::Lite::TableInfo object fully-populated with all of the information available about the table represented by a class.

So, given the following table structure:

create table artists (
  artist_id   integer unsigned not null primary key auto_increment,
  name        varchar(100) not null
) engine=innodb charset=utf8;

Here is the example:

my $info = App::db::artist->get_table_info();

my $column = $info->column('name');
warn $column->name;           # 'name'
warn $column->type;           # varchar
warn $column->length;         # 100
warn $column->is_pk;          # '0' (because it's not the Primary Key)
warn $column->is_nullable;    # 0 (because `not null` was specified on the table)
warn $column->default_value;  # undef because no default value was specified
warn $column->key;            # undef because not UNIQUE or PRIMARY KEY

foreach my $column ( $info->columns ) {
  warn $column->name;
  warn $column->type;
  warn $column->length;
  warn $column->is_pk;
  ...
  # If the column is an 'enum' field:
  warn join ', ', @{ $column->enum_values };
}

Returns a Class::DBI::Lite::Pager object.

Example:

# Step 1: Get our pager:
my $pager = App::db::artist->pager({
  name => { LIKE => 'Bob%' }
}, {
  order_by    => 'name ASC',
  page_number => 1,
  page_size   => 20,
});

# Step 2: Show the items in that recordset:
foreach my $artist ( $pager->items ) {
  # Do stuff with $artist:
  print $artist->name;
}

See Class::DBI::Lite::Pager for more details and examples.

sql_pager( { data_sql => $str, count_sql => $str, sql_args => \@array }, { page_number => 1, page_size => 10 } )

Returns a Class::DBI::Lite::Pager object.

Example:

# Step 1: Get our pager:
my $pager = App::db::artist->sql_pager({
  data_sql  => "SELECT * FROM artists WHERE name LIKE ?",
  count_sql => "SELECT COUNT(*) FROM artists WHERE name LIKE ?",
  sql_args  => [ 'Bob%' ],
}, {
  page_number => 1,
  page_size   => 20,
});

# Step 2: Show the items in that recordset:
foreach my $artist ( $pager->items ) {
  # Do stuff with $artist:
  print $artist->name;
}

See Class::DBI::Lite::Pager for more details and examples.

OBJECT METHODS

Field Methods

For each of the fields in your table, an "accessor" method will be created.

So, given the following table structure:

create table artists (
  artist_id   integer unsigned not null primary key auto_increment,
  name        varchar(100) not null,
) engine=innodb charset=utf8;

And the following class:

package App::db::artist;

use strict;
use warnings 'all';
use base 'My::Model';

__PACKAGE__->set_up_table('artists');

1;# return true:

The App::db::artist class would have the following methods created:

artist_id

Returns the value of the artist_id field the database. This value is read-only and cannot be changed.
name

Gets or sets the value of the name field the database.

To get the value of the name field, do this:
```
my $value = $artist->name;
```
To set the value of the name field, do this:
```
$artist->name( "New Name" );
```
To save those changes to the database you must call update:
```
$artist->update;
```

Overriding Setters and Getters

The accessors/mutators ("setters" and "getters") can be individually overridden within your entity class by implementing _set_foo($self, $value) or _get_foo($self) methods.

NOTE: In practice this may be more useful for the _get_* methods, as the _set_* methods are usually best left to triggers.

id

Always returns the value of the object's primary column.

Example:

$album->id == $album->album_id;
$artist->id == $artist->artist_id;

update()

Causes any changes to an object to be saved to the database.

Example:

$artist->name( 'Big Bob' );
$artist->update;

delete()

Deletes the object from the database. The object is then re-blessed into the special class Class::DBI::Lite::Object::Has::Been::Deleted.

Example:

$album->delete;

discard_changes()

Causes any changes made to the object that have not been stored in the database to be forgotten.

Example:

my $artist = App::db::artist->create( name => 'Bob Marley' );
$artist->name( 'Big Bob' );

$artist->discard_changes;

ADVANCED TOPICS

Master/Slave Configuration

In your My::db::model class:

Instead of:

__PACKAGE__->connection( $dsn, $user, $pass );

Do this:

__PACKAGE__->set_master( $dsn, $user, $pass );

__PACKAGE__->set_slaves(
  [ $dsn1, $user1, $pass1 ],
  [ $dsn2, $user2, $pass2 ],
  [ $dsn3, $user3, $pass3 ],
);

Your slaves will be shuffled.

Writes will always* go to the master, reads will always go to the slaves.

*Unless you are inside of a transaction, in which case all reads will also go to the master.

If you want to switch to a different slave, call 'switch_slave' on your main model class:

My::db::model->switch_slave();

In an ASP4 environment you could add a line like that to an ASP4::RequestFilter.

AUTHOR

LICENSE

This software is Free software and may be used and redistributed under the same terms as perl itself.

To install Class::DBI::Lite, copy and paste the appropriate command in to your terminal.

cpanm

cpanm Class::DBI::Lite

CPAN shell

perl -MCPAN -e shell
install Class::DBI::Lite

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

SYNOPSIS

DESCRIPTION

BACKGROUND

PHILOSOPHY

PUBLIC PROPERTIES

connection( $dsn, $username, $password )

db_Main

table

columns

trace( 1:0 )

STATIC METHODS

create( %info )

find_or_create( %info )

retrieve( $id )

retrieve_all( )

has_many( ... )

belongs_to( ... )

construct( $hashref )

eval { do_transaction( sub { ... } ) }

search( %args )

search_like( %args )

search_where( \%args, [\%sort_and_limit] )

count_search( %args )

count_search_like( %args )

count_search_where( \%args )

sth_to_objects( $sth )

add_trigger( $event => \&sub )

Class Triggers

before_create( $self )

after_create( $self )

before_update( $self )

after_update( $self )

before_delete( $self )

after_delete( {$primary_field => $id} )

Field Triggers

before_update_<field>( $self, $old_value, $new_value )

after_update_<field>( $self, $old_value, $new_value )

find_column( $name )

get_table_info( )

pager( \%where, { order_by => 'fields ASC', page_number => 1, page_size => 10 } )

sql_pager( { data_sql => $str, count_sql => $str, sql_args => \@array }, { page_number => 1, page_size => 10 } )

OBJECT METHODS

Field Methods

Overriding Setters and Getters

id

update()

delete()

discard_changes()

ADVANCED TOPICS

Master/Slave Configuration

SEE ALSO

AUTHOR

LICENSE

Module Install Instructions