NAME

DBO - Database Object Mapping

SYNOPSIS

 # First, generate the modules from the db.
 $ dbo-gen -u john -p xxx --all --gen-modules --split-dir=. --module-prefix="Foo"  dbname

 # Use them..
 use DBI;
 use DBO;
 DBO->dbh(DBI->connect(...));

 package Customer;
 use base qw(DBO::Base);

 Customer->def_data_source  ('my_db', 'cust_tbl');
 Customer->def_column       ('cust_id',  { accessor => 'id', auto_increment => 1 });
 Customer->def_column       ('name'};
 Customer->def_accessor     ('address',  { column => 'addr'});
 Customer->def_accessor     ('affiliate_id');
 Customer->def_accessor     ('now', { expr => 'now()', cache => 0} );

 Customer->def_primary_key  ([qw(cust_id  cust_loc)]);
 Customer->def_key          ([qw(name affiliate_id)]);


 Customer->def_has_a        ('affiliate', 'Affiliate', +{})
 Customer->def_has_a        ('affiliate', [qw(name aff_id)], 'Affiliate_table', [qw(c_name id)]);

 Customer->def_has_many     ('orders', 'Order',+{})
 Customer->def_has_many     ('orders', 'Order', ['cust_id'], ['id'],+{});
 Employee->def_has_many     ('supervisors', 'Supervisor',
                                           new DBO::Query->select('super_id')
                                           ->from('emp_super')
                                           ->join('employee')
                                           ->using('emp_id')
                                           ->where(+{emp_id => '?'}), sub { shift->emp_id });

 Customer->def_select_group ([qw(name address)]);

 ## Main program
 package main;
 use DBO;
 use Customer;
 use Some_Other_DBO_Object;

 DBO->resolve(); # IMPORTANT! Do this once, after all 'use' objects.


 $all_customers = Customer->selectall_arrayref;
 for  (@$all_customers){
     $_->name( ucfirst lc $_->name );
     $_->update;
 }

 Customer->insert->values($name, $addr, $afid)->insert;
 Customer->insert({ name => $name, addr => $addr, affiliate_id => $afid});
 $cust = new Customer();
     $cust->name($name);
     $cust->address($addr);
     $cust->affiliate_id($afid);
     $cust->insert;
 new DBO::Query::Insert(qw(name addr affiliate_id))
   ->into('cust_tbl')
   ->values($name,$addr,$afid)
   ->insert;

 Customer->update->set( 'name', $new_name )->where({ cust_id => $cust_id })->update;
 new DBO::Query::Update->set( name => '$new_name' )->where( "cust_id = $cust_id")->update;
 $cust = new Customer($cust_id);
     $cust->name($new_name);
     $cust->update;

DESCRIPTION

DBO is a mapping from database tables (or joins) to perl objects. The object accessors are columns in the table (or join).

There are currently two layers to DBO, described below.

LAYER 1 - DBO::Query

The DBO::Query layer is a set of simple object wrappers around standard SQL statements, which maintain their own $dbh and $sth handles, and can remember bind_values. Here is an example:

my $rows = new DBO::Query($dbh)
         ->select(@column_names)
         ->from($table)
         ->selectall_arrayref(@values);

# Same thing:
my $rows = $dbh->selectall_arrayref(
        "SELECT @{[join q/,/, @column_names]} FROM $table" );

When used alone (without LAYER 2), the DBO::Query objects offers a few advantages over straight DBI calls:

Advantages of using DBO::Query over DBI

Bind values can be assigned at any time.
Hides SQL and prevents string errors.
Cleaner code.

However, the real power comes in Layer 2, where DBO methods return partially constructed DBO::Query objects.

LAYER 2 - DBO::Base

The DBO::Base layer consists of a base class (DBO::Base) and some glue to the DBO::Query layer. The base class is intended to be subclassed for each table, view, and join in your database. Your subclass will define mappings to the database tables and columns, and will inherit useful methods for storing and retrieving its underlying data. The following example assumes that the 'Customer' object mappings have been defined already (as in the SYNOPSIS):

my $c = new Customer();
$c->name($name);
$c->insert;
$c->update;
$c->delete;

The layer 2 objects make extensive use of DBO::Query (Layer 1) objects. Many Layer 2 methods returns partially constructed DBO::Query objects. For example, to build a query on the Customer table, do this:

my $query = Customer->select->where->({region_code => '?'});
my $customers = $query->selectall_arrayref($region);
my $others = $query->selectall_arrayref($other_region);

There are two important things going on here. First, the $query object behaves like a layer 1 DBO::Query object, but it fetches Customer objects, not rows. Second, the query can be cached and reused just like a DBI prepared statement handle.

API GUIDELINES

These are the guidelines adhered to by the DBO public API. Any exceptions you discover are bugs.

1. Column names may be fully qualified.

Any API method that expects a column name should also accept the fully qualified column name as "$database.$table.$column". In most cases, the column name by itself is ok.

2. Keys are listrefs.

All keys (primary keys, foreign keys, etc.) should be specified as a reference to a perl array containing the column names or values. For flexibility, single-column primary keys can be specified as scalars.

3. Methods that look like DBI methods should behave like DBI methods.

Methods with names borrowed from DBI (e.g, prepare(), execute(), selectall_arrayref()) should behave like their DBI counterparts. If you are familiar with DBI, than there should be no surprises from DBO.

4. DBO objects never do more than you expect them to do.

It should be clear from your code whether it triggers a database action. For example, if you do not explicitly modify values in a DBO object by calling insert(), update(), delete(), etc., then the database should not be modified. The inverse is also true.

5. All DBO objects have a dbh() method.

All objects should contain, or have the ability to procure a database handle. Typically, this is done through package globals -- the top level being DBO::dbh(). The idea is that you need only set the $dbh handle once in your code.

6. A DBI handle ($dbh) can be passed to most methods.

Wherever sensible, DBO::Query and DBO::Base methods will accept a $dbh as an argument. The new(), prepare(), and execute() methods are sensible places to pass a $dbh handle. The $dbh will be safely plucked from the arguments. For example,

$query->execute(@bind_values, $dbh, @more_bind_values);

PUBLIC OBJECTS AND METHODS

This is a partial list of public objects and their methods.

DBO::Query

Represents a query object. You can safely pass a $dbh to most methods, which will be stored for later prepare() and execute() calls.

new

new Query()
new Query(column_names)
new Query($dbh,column_names)

from

$q->from($table_name, $table_name...)

join

$q->join($right)
$q->join($left, $right)

right_join

$q->right_join($right)
$q->right_join($left, $right)

left_join

$q->left_join($right)
$q->left_join($left, $right)

where

$q->where(EXPR, @bind_values)

and

$q->and (EXPR, @bind_values)

or

$q->or (EXPR, @bind_values)

prepare

$q->prepare()
$q->prepare($dbh)

execute

$q->execute(@bind_values)
$q->execute($dbh,@bind_values)

fetchrow_arrayref

$q->fetchrow_arrayref()

fetchall_arrayref

$q->fetchall_arrayref()

fetchcol_arrayref

$q->fetchcol_arrayref()

selectall_arrayref

$q->selectall_arrayref(@bind_values)
$q->selectall_arrayref($dbh, @bind_values)

selectcol_arrayref

$q->selectcol_arrayref(@bind_values)
$q->selectcol_arrayref($dbh, @bind_values)

clone

returns a deep copy. Not finished or tested!
Note: The database handle will be shared by the clone, and
the internal statement handle will set to undef in the clone.

DBO::Query::Update

Represents an SQL UPDATE.

new

new()
new($dbh,[column_names,...])

table

$q->table($table_name)

set

$q->set({ name => value})
$q->set( name => value)

update

Same as prepare->execute

$q->update()
$q->update(@bind_values)
$q->update($dbh,@bind_values)

where

$q->where(EXPR, @bind_values)

prepare

$q->prepare()
$q->prepare($dbh)

execute

$q->execute()
$q->execute(@bind_values)
$q->execute($dbh,@bind_values)

DBO::Query::Insert

Represents an insertion object.

new

new DBO::Query::Insert()
new DBO::Query::Insert(@columns)
new DBO::Query::Insert($dbh,[@columns])

insert

Same as prepare->execute

$q->insert()
$q->insert(@bind_values)
$q->insert($dbh,@bind_values)

columns

$q->columns(names or infos or values)
$q->columns([names or infos or values])

into

$q->into($table_name)
$q->into($table_info)

values

$q->values($values..)
$q->values(Value objects....)

prepare

$q->prepare()
$q->prepare($dbh)

execute

$q->execute(@bind_values)
$q->execute($dbh,@bind_values)

clone

returns a deep copy. Not finished or tested! @note The database handle will be shared by the clone, and the internal statement handle will set to undef in the clone.

DBO::Query::Delete

new

$d = new DBO::Query::Delete()
$d = new DBO::Query::Delete($dbh)

from

$d->from($tables)

delete

Same as prepare->execute.

$d->delete()
$d->delete(@bind_values)
$d->delete($dbh,@bind_values)

prepare

$d->prepare()
$d->prepare($dbh)

execute

$d->execute()
$d->execute(@bind_values)
$d->execute($dbh,@bind_values)

where

$d->where(EXPR, @bind_values)

and

$d->and (EXPR, @bind_values)

or

$d->or (EXPR, @bind_values)

DBO

DBO->resolve(): This static method should be called once from within your application, after using all DBO subclasses -- typically upon startup. The main purpose is to cross-reference all your multi-table relationships (e.g., has_a, has_many). DBO->resolve() will croak if it fails.

DBO::Base

This is the base class that provides all the functionality for your subclasses. It is not meant to be called directly.

new

Do not override the new() method. Override the init() method instead.

$new_obj = new MyClass()
$existing_obj = new MyClass($PrimaryKeyValue)

## these are not as useful
$obj = new MyClass($pk_column)
$obj = new MyClass([$pk_part1, $pk_part2] )
$obj = new MyClass($dbh)

init

DBO will call the init() method from within new(). The default init() method is empty, and is meant to be optionally overridden in the subclass. For example,

sub init {
    my ($self, @args) = @_;
    ## etc.
}

Where @args contains the arguments that were passed to new() that DBO::Base did not recognize. e.g., if you pass a $dbh to new(), then it will not be passed to through init() because DBO snatched it up.

select

Returns a query that can be used to fetch objects of type MyClass (based on primary key). The @columns array may be column names, aliases, or accessors.

$query = MyClass->select(@optional_columns)
$list_of_MyClass_objects = $query->selectall_arrayref;

select_count

Returns a query object that will return COUNT(*) instead of an object.

MyClass->select_count()

selectall_arrayref

Returns a reference to an array of objects of type MyClass.

MyClass->selectall_arrayref()
MyClass->selectall_arrayref(@bind_values)
MyClass->selectall_arrayref($dbh, @bind_values)

insert

When called as an object method, it immediately inserts a row in the database. If any part of the primary key is an auto_increment column, then the new primary key is fetched. When called as a class method, it returns a DBO::Query::Insert object.

$obj->insert; # SQL INSERT was performed.

$query = MyClass->insert; # SQL was not executed.

update

When called as an object method, it immediately updates the corresponding row in the database. If this objects has no primary key value, then an exception is raised. When called as a class method, it returns a DBO::Query::Update object.

$obj->update; # SQL UPDATE was performed.

$query = MyClass->update; # SQL was not executed.

delete

When called as an object method, it immediately deletes the row in the database corresponding to this object. When called as a class method, it returns a query that can be used to delete objects from the corresponding table.

$obj->delete; # SQL DELETE was executed.

$query = MyClass->delete; # No SQL was executed.

def_data_source

Define the data source for this object.

MyClass->def_data_source( 'database_name', 'table_name');

def_accessor ( $accessor_name, \%options )

Customer->def_accessor( 'id', { column => 'cust_id', auto_increment => 1 } );

Options (explained below):

column            => NAME
auto_increment    => BOOLEAN
select_when_null  => VALUE
update_when_empty => VALUE
select_trim       => BOOLEAN
update_trim       => BOOLEAN
string_mangle     => BOOLEAN

column => NAME

The column name in the database. The default is the accessor name.

auto_increment => BOOLEAN

The column value is generated by the database, and should not be INSERTED.

select_when_null => VALUE

Select VALUE when a column's value is NULL.

update_when_empty => VALUE

Use VALUE instead of the empty string for updates and inserts.

select_trim => BOOLEAN

Trim leading and trailing whitespace after selecting the value from the database.

update_trim => BOOLEAN

Trim leading and trailing whitespace before updating or inserting.

string_mangle => BOOLEAN

Apply all string mangling features to this column. This option is just a shortcut for:

{ select_trim => 1,
  update_trim => 1,
  select_when_null => '',
  update_when_empty => undef }

def_column

Same as def_accessor, but the column name is first, and you can't specify an EXPR as a column. In the options hash, you can provide an accessor key to rename the accessor.

def_has_a

Define a relationship

MyClass->def_has_a ('affiliate', [qw(name aff_id)], 'Affiliate_table', [qw(c_name id)]);
MyClass->def_has_a ($accessor, $many_key, $table, $one_key, $opts)

def_has_many

Define a relationship.

MyClass->def_has_many ( $accessor, $table, [$one_columns], [$many_columns], $opts );
MyClass->def_has_many ( $accessor, $table, $opts );
MyClass->def_has_many ( $accessor, $table, $query, $bind_routine, $opts )

def_select_group

Define a selection group.

MyClass->def_select_group ([ $cols ])
MyClass->def_select_group ( $group => [ $cols...] )

NAMESPACE ISSUES

Since your module ISA DBO::Base, it inherits all of DBO::Base's methods and attributes. Here is a comprehensive list of the methods your module will inherit. Any omissions should be considered a bug.

Public Methods

These methods are documented elsewhere in this manual.

def_accessor
def_column
def_data_source
def_has_a
def_has_many
def_key
def_primary_key
def_select_group

dbh_reader
dbh_writer
delete
init
insert
select
select_count
selectall_arrayref
update

Private Methods

These methods are private, and should not be overridden by your modules.

new            # do not override. use init() instead
_dbo*         # do not call or override any method named _dbo*

PERFORMANCE

The DBO layer does not increase the asymptotic running time of your code as compared to straight DBI. That should keep most of you happy.

For the rest of you speed freaks, here are the ways that DBO optimizes for performance:

calls prepare_cached() instead of prepare()
selectall_arrayref delays object instantiation until necessary
pools reader/writer database handles (TODO)
reuses objects by primary key (TODO)

VERSION

DBO $Revision: 1.26 $

AUTHOR

John Millaway <millaway@cpan.org>

To install DBO, copy and paste the appropriate command in to your terminal.

cpanm

cpanm DBO

CPAN shell

perl -MCPAN -e shell
install DBO

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

LAYER 1 - DBO::Query

Advantages of using DBO::Query over DBI

LAYER 2 - DBO::Base

API GUIDELINES

PUBLIC OBJECTS AND METHODS

NAMESPACE ISSUES

PERFORMANCE

VERSION

AUTHOR

Module Install Instructions