/ 
DBR-trunk_275
/ DBR::Manual

DRAFT

This documentation is in early draft form!

NAME

DBR - Database Repository ORM (object-relational mapper).

SYNOPSIS

# common packages
use DBR;
use DBR::Util::Logger;
use DBR::Util::Operator;

# typical connection
my $logger = DBR::Util::Logger->new( -logpath => './dbr.log', -logLevel => 'debug2' );
my $dbr = DBR->new( -logger => $logger, -conf => './dbr.conf' );
my $dbh = $dbr->connect( 'car_dealer' );  # instance handle

# metadata access (dbr tools)
my $meta = $dbr->get_instance( 'dbrconf' );  #

VERSION

1.06b

svn trunk is currently 266.

DESCRIPTION

DBR stands for Database Repository, and functions as an ORM (Object Relational Mapper) to your database.

DBR tries to make your use of a Database resource safe, concise, efficient and clear. The objective is to have your code access and use objects, not spend time expressing future needs (SQL, fields to pull, joins, etc) in one place and using them in another.

FEATURES

concise

A one-liner will often suffice to prepare the data you need. After that, you just use the data and the DBR objects will do (or already have done) the right thing. For example:

while ($dbrh->orders->where( 'customer.name' => LIKE '%Jones' )->next) {
  print "Order ".$_->order_id." shipped ".$_->shipment.method.name."\n";
}

would be the equivalent of writing out some SQL like:

select
  o.order_id,
  m.name
from
  orders o,
  customers c,
  shipments s,
  ship_methods m
where
  c.name like '%Jones' and
  o.customer_id = c.cust_id and
  o.shipment_id = s.shipment_id and
  s.method_id = m.method_id;
smart

DBR learns what your code will need, and will pre-fetch the data.

In the example above, DBR learns about your access to the shipping method name the first time the code runs, and will build the equivalent query on subsequent runs.

efficient

Most performance-critical DBR objects are blessed arrayrefs instead of hashrefs.

Access to large quantities of data are automatically chunked behind the scenes.

convenient

Database fields can hold a representation of data that you can access in raw or formatted form via automatic translators that wrap the field.

Currently available for Dollars, Unixtime, Percent and Enumeration.

no SQL

Table joins are replaced with the names of relationships associated with foreign keys. All you end up doing is:

$car->model->make->name

or

where( 'car.model.make.name' => 'Ford' )
post-fetch organization

Create a lookup map (hash):

# what preference did customers have for shipping method by gender last year?

$dbh->orders->
  where( order.date => BETWEEN( '1/1/2008', '1/1/2009' ) )->
    hashmap_multi( 'order.date.month', 'shipment.method.name', 'customer.gender' );

or

# get purchase items grouped by shipment method and order

$dbh->items->
  where( 'order.customer' => $customer_id )->
    hashmap_multi( 'order.shipment.method.name', 'order.order_id' );

CONFIGURATION

Configuration File

Contains the information needed to get to the configuration/metadata database.

This database contains all the information about schemas and instances you will be connecting to.

For a SQLite config database, your config will define:

name=dbrconf
class=master
dbfile=/path/to/database
type=SQLite
dbr_bootstrap=1

really, all you need to usually do is customize the dbfile path.

For a MySQL config database, your config will define:

name=dbrconf
class=master
hostname=db.host.domain.com
database=myapp
user=dbusername
password=dbpassword
type=Mysql
dbr_bootstrap=1

typically customize just hostname, database, user and password.

The class could also be "query", for a read-only replicated copy, for example.

Bootstrap

When you create a new() DBR, you typically point it at the above config file.

From there, all further actions to your application databases are thru the DBR object.

Debug Logging

A common logger instance is required by DBR and shared by all related objects.

METADATA

Schema

Instance

Table

Field

Enumeration

API

Be sure to also check out:

http://code.google.com/p/perl-dbr/wiki/ObjectsAndMethods

DBR

new

get_instance

connect

timezone

remap

unmap

Instance Handle

TABLENAME

begin

commit

rollback

select (v1)

insert (v1)

update (v1)

delete (v1)

Object

where

all

get

insert

enum

parse

Resultset

next

each

hashmap_multi

hashmap_single

count

values

raw_hashrefs

raw_arrayrefs

raw_keycol

Record

FIELDNAME

get

RELATIONNAME

gethash

set

delete

Translator

Dollars

dollars

format

cents

Unixtime

date

time

datetime

fancytime

fancydate

fancydatetime

unixtime

midnight

endofday

Enum

handle

name

in

id

Operators

use DBR::Util::Operator;

GT (greater-than)

where( 'item.price' => GT 3.59 );

LT (less-than)

where( 'item.price' => LT 3.59 );

GE (greater-than-or-equal-to)

where( 'item.price' => GE 3.59 );

LE (less-than-or-equal-to)

where( 'item.price' => LE 3.59 );

NOT

LIKE

where( name => LIKE( '%ing' )

NOTLIKE

where( name => LIKE( 'George%' )

BETWEEN

where( price => BETWEEN( 3.00, 4.50 ) )

NOTBETWEEN

where( price => NOTBETWEEN( 50, 100 ) )

Logger

logErr

logWarn

logInfo

logDebug

verbose

logDebug2

very verbose

logDebug3

really, really verbose

TOOLS

dbr-admin

dbr-load-spec

dbr-dump-spec

dbr-scan-db

TODO

dbr-admin

This tool works, but it has various quirks that make it a bit difficult to use at present.

fast metadata browser

DBR needs a fast and effective browser, especially for looking up the relationship names and enumeration handles. The dbr-admin tool is helpful but clunky, the dbr-spec-dump also can be use for this purpose, but...

cross-module scoping

DBR's current pre-fetch scoping support only works within a file. It needs to be extended. For now, don't pass DBR objects between packages, unless they are in the same file.

debug support

Attempting to Dumper data with DBR objects will explode with metadata you never wanted to see. DBR needs dumper support.

OR support

DBR doesn't want to you to make an OR query. It usually results in an inefficient table scan. In fact, you just can't with DBR! All the constraints in a where() are ANDed together.

Should you be able to OR?

BUGS

MySQL sub-queries

DBR will magically perform a sub-query when you use the reverse direction of a relationship. For example, given an e-commerce scenario with an Order that has Items, the item has a foreign key to its order, so order.items would be traversing that foreign key in the reverse direction, causing a sub-query of items matching the order's pkey. MySQL handles the sub-query very poorly, and depending on the volume of data, you may need to identify the order pkeys of interest first, and then use where( order_id => $order_ids ) explicitly instead.

perl-dbr project

See http://code.google.com/p/perl-dbr/issues/list.

SEE ALSO

Google Code project: http://code.google.com/p/perl-dbr

API Diagram: http://code.google.com/p/perl-dbr/wiki/ObjectsAndMethods

AUTHOR

Daniel Norman, dnorman@drjays.com.

COPYRIGHT AND LICENSE

Copyright 2009, Daniel Norman

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.