NAME

DBomb - Database Object Mapping

SYNOPSIS

  # First, generate the modules from the db.
  $ dbomb-gen -u john -p xxx --all --gen-modules --split-dir=. --module-prefix="Foo"  dbname
 
  # Use them..
  use DBI;
  use DBomb;
  DBomb->dbh(DBI->connect(...));

  package Customer;
  use base qw(DBomb::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 DBomb::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 DBomb;
  use Customer;
  use Some_Other_DBO_Object;

  DBomb->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 DBomb::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 DBomb::Query::Update->set( name => '$new_name' )->where( "cust_id = $cust_id")->update;
  $cust = new Customer($cust_id);
      $cust->name($new_name);
      $cust->update;

DESCRIPTION

DBomb 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 DBomb, described below.

LAYER 1 - DBomb::Query

The DBomb::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 DBomb::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 DBomb::Query objects offers a few advantages over straight DBI calls:

Advantages of using DBomb::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 DBomb methods return partially constructed DBomb::Query objects.

LAYER 2 - DBomb::Base

The DBomb::Base layer consists of a base class (DBomb::Base) and some glue to the DBomb::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 DBomb::Query (Layer 1) objects. Many Layer 2 methods returns partially constructed DBomb::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 DBomb::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 DBomb 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 DBomb.

4. DBomb 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 DBomb object by calling insert(), update(), delete(), etc., then the database should not be modified. The inverse is also true.

5. All DBomb 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 DBomb::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, DBomb::Query and DBomb::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.

DBomb::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.

DBomb::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)

DBomb::Query::Insert

Represents an insertion object.

new

 new DBomb::Query::Insert()
 new DBomb::Query::Insert(@columns)
 new DBomb::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.

DBomb::Query::Delete

new

 $d = new DBomb::Query::Delete()
 $d = new DBomb::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)

DBomb

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

DBomb::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

DBomb 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 DBomb::Base did not recognize. e.g., if you pass a $dbh to new(), then it will not be passed to through init() because DBomb 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 DBomb::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 DBomb::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 DBomb::Base, it inherits all of DBomb::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 DBomb 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 DBomb 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

DBomb $Revision: 1.26 $

AUTHOR

John Millaway <millaway@cpan.org>

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

cpanm

cpanm DBomb

CPAN shell

perl -MCPAN -e shell
install DBomb

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 - DBomb::Query

Advantages of using DBomb::Query over DBI

LAYER 2 - DBomb::Base

API GUIDELINES

PUBLIC OBJECTS AND METHODS

NAMESPACE ISSUES

PERFORMANCE

VERSION

AUTHOR

Module Install Instructions