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, 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 My::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 = My::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 My::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 ", ", My::Artist->columns;
# artist_id, name

STATIC METHODS

create( %info )

Creates a new object and returns it.

Example:

my $artist = My::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 = My::Artist->find_or_create( name => 'Bob Marley' );

retrieve( $id )

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

Example:

my $artist = My::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 = My::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 = My::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 My::Artist;
...
__PACKAGE__->has_many(
  albums  =>
    'My::Album' =>
      'album_id'
);

The syntax is:

__PACKAGE__->has_many(
  $what_they_are_called =>
    $the_class_name =>
      $their_field_name_that_contains_my_primary_column's_value
);

The result is this:

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

That's the same as:

my @albums = My::Album->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.

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 = My::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 {
  My::Artist->do_transaction( sub {
  
    # Your transaction code goes here:
    my $artist = My::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 = My::Artist->search( name => 'Bob Marley' );

my $artist_iterator = My::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 = My::Artist->search_like( name => 'Bob%' );

my $artist_iterator = My::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 = My::Artist->search_where({
  name => 'Bob Marley'
});

Same as this SQL:

SELECT *
FROM artists
WHERE name = 'Bob Marley'

Example 2:

my @artists = My::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 = My::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.

count_search( %args )

Returns the number of records that match %args.

Example:

my $count = My::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 = My::Artist->count_search_like(
  name  => 'Bob%'
);

count_search_where( \%args )

Returns the number of records that match \%args.

Examples:

my $count = My::Album->count_search_like({
  name  => { LIKE => 'Best Of%' }
});

my $count = My::Album->count_search_like({
  genre => { '!=' => 'Country/Western' }
});

sth_to_objects( $sth )

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

Example:

my $sth = My::Artist->db_Main->prepare("SELECT * FROM artists");
$sth->execute();
my @artists = My::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 My::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 My::Album;
...
__PACKAGE__->add_trigger( before_create => sub {
  my ($self) = @_;
  
  warn "ID = '$self->{album_id}', Name = '$self->{name}";
});

If we ran this code:

my $album = My::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 My::Album;
...
__PACKAGE__->add_trigger( after_create => sub {
  my ($self) = @_;
  
  warn "ID = '$self->{album_id}', Name = '$self->{name}";
});

If we ran this code:

my $album = My::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 My::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 My::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 My::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 My::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 My::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 My::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 = My::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 My::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 = My::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( My::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 = My::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;
  ...
}

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

Example:

# Step 1: Get our pager:
my $pager = My::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 = My::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 My::Artist;

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

__PACKAGE__->set_up_table('artists');

1;# return true:

The My::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;
```

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 = My::Artist->create( name => 'Bob Marley' );
$artist->name( 'Big Bob' );

$artist->discard_changes;

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

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

id

update()

delete()

discard_changes()

SEE ALSO

AUTHOR

LICENSE

Module Install Instructions