/ 
DBIx-DBO-0.40
River stage zero No dependents
/ DBIx::DBO

NAME

DBIx::DBO - An OO interface to SQL queries and results. Easily constructs SQL queries, and simplifies processing of the returned data.

SYNOPSIS

use DBIx::DBO;

# Create the DBO
my $dbo = DBIx::DBO->connect('DBI:mysql:my_db', 'me', 'mypasswd') or die $DBI::errstr;

# Create a "read-only" connection (useful for a replicated database)
$dbo->connect_readonly('DBI:mysql:my_db', 'me', 'mypasswd') or die $DBI::errstr;

# Start with a Query object
my $query = $dbo->query('my_table');

# Find records with an 'o' in the name
$query->where('name', 'LIKE', '%o%');

# And with an id that is less than 500
$query->where('id', '<', 500);

# Exluding those with an age range from 20 to 29
$query->where('age', 'NOT BETWEEN', [20, 29]);

# Return only the first 10 rows
$query->limit(10);

# Fetch the rows
while (my $row = $query->fetch) {

    # Use the row as an array reference
    printf "id=%d  name=%s  status=%s\n", $row->[0], $row->[1], $row->[4];

    # Or as a hash reference
    print 'id=', $row->{id}, "\n", 'name=', $row->{name};

    # Update/delete rows
    $row->update(status => 'Fired!') if $row->{name} eq 'Harry';
    $row->delete if $row->{id} == 27;
}

DESCRIPTION

This module provides a convenient and efficient way to access a database. It can construct queries for you and returns the results in easy to use methods.

Once you've created a DBIx::DBO object using one or both of connect or connect_readonly, you can begin creating Query objects. These are the "workhorse" objects, they encapsulate an entire query with JOINs, WHERE clauses, etc. You need not have to know about what created the Query to be able to use or modify it. This makes it valuable in environments like mod_perl or large projects that prefer an object oriented approach to data.

The query is only automatically executed when the data is requested. This is to make it possible to minimise lookups that may not be needed or to delay them as late as possible.

The Row object returned can be treated as both an arrayref or a hashref. The data is aliased for efficient use of memory. Row objects can be updated or deleted, even when created by JOINs (If the DB supports it).

METHODS

new

DBIx::DBO->new($dbh);
DBIx::DBO->new(undef, $readonly_dbh);

Create a new DBIx::DBO object from existsing DBI handles. You must provide one or both of the read-write and read-only DBI handles.

connect

$dbo = DBIx::DBO->connect($data_source, $username, $password, \%attr)
    or die $DBI::errstr;

Takes the same arguments as DBI->connect for a read-write connection to a database. It returns the DBIx::DBO object if the connection succeeds or undefined on failure.

connect_readonly

Takes the same arguments as connect for a read-only connection to a database. It returns the DBIx::DBO object if the connection succeeds or undefined on failure.

Both connect & connect_readonly can be called on a DBIx::DBO object to add that respective connection to create a DBIx::DBO with both read-write and read-only connections.

my $dbo = DBIx::DBO->connect($master_dsn, $username, $password, \%attr)
    or die $DBI::errstr;
$dbo->connect_readonly($slave_dsn, $username, $password, \%attr)
    or die $DBI::errstr;

table

$dbo->table($table);
$dbo->table([$schema, $table]);
$dbo->table($table_object);

Create and return a new Table object. Tables can be specified by their name or an arrayref of schema and table name or another Table object.

query

$dbo->query($table, ...);
$dbo->query([$schema, $table], ...);
$dbo->query($table_object, ...);

Create a new Query object from the tables specified. In scalar context, just the Query object will be returned. In list context, the Query object and Table objects will be returned for each table specified.

my($query, $table1, $table2) = $dbo->query(['my_schema', 'my_table'], 'my_other_table');

row

$dbo->row($table || $table_object || $query_object);

Create and return a new Row object.

selectrow_array, selectrow_arrayref, selectrow_hashref, selectall_arrayref

$dbo->selectrow_array($statement, \%attr, @bind_values);
$dbo->selectrow_arrayref($statement, \%attr, @bind_values);
$dbo->selectrow_hashref($statement, \%attr, @bind_values);
$dbo->selectall_arrayref($statement, \%attr, @bind_values);

These convenience methods provide access to DBI->selectrow_array, DBI->selectrow_arrayref, DBI->selectrow_hashref, DBI->selectall_arrayref methods. They default to using the read-only DBI handle.

do

$dbo->do($statement)         or die $dbo->dbh->errstr;
$dbo->do($statement, \%attr) or die $dbo->dbh->errstr;
$dbo->do($statement, \%attr, @bind_values) or die ...

This provides access to the DBI->do method. It defaults to using the read-write DBI handle.

table_info

$dbo->table_info($table);
$dbo->table_info([$schema, $table]);
$dbo->table_info($table_object);

Returns a hashref containing PrimaryKeys, Columns and Column_Idx for the table. Mainly for internal use.

disconnect

Disconnect both the read-write & read-only connections to the database.

Common Methods

These methods are accessible from all DBIx::DBO* objects.

dbo

This DBO object.

dbh

The read-write DBI handle.

rdbh

The read-only DBI handle, or if there is no read-only connection, the read-write DBI handle.

config

$global_setting = DBIx::DBO->config($option);
DBIx::DBO->config($option => $global_setting);
$dbo_setting = $dbo->config($option);
$dbo->config($option => $dbo_setting);

Get or set the global or this DBIx::DBO config settings. When setting an option, the previous value is returned. When getting an option's value, if the value is undefined, the global value is returned.

Available config options

AutoReconnect

Boolean setting to store the connection details for re-use. Before every operation the connection will be tested via ping() and reconnected automatically if needed. Changing this has no effect after the connection has been made. Defaults to false.

CacheQuery

Boolean setting to cause Query objects to cache their entire result for re-use. The query will only be executed automatically once. To rerun the query, either explicitly call run or alter the query. Defaults to false.

DebugSQL

Set to 1 or 2 to warn about each SQL command executed. 2 adds a full stack trace. Defaults to 0 (silent).

OnRowUpdate

Set to 'empty', 'simple' or 'reload' to define the behaviour of a Row after an update. 'empty' will simply leave the Row empty after every update. 'simple' will set the values in the Row if they are not complex expressions, otherwise the Row will be empty. 'reload' is the same as 'simple' except it also tries to reload the Row if possible. Defaults to 'simple'.

QuoteIdentifier

Boolean setting to control quoting of SQL identifiers (schema, table and column names).

UseHandle

Set to 'read-write' or 'read-only' to force using only that handle for all operations. Defaults to false which chooses the read-only handle for reads and the read-write handle otherwise.

Global options can also be set when use'ing the module:

use DBIx::DBO QuoteIdentifier => 0, DebugSQL => 1;

STORABLE

Storable hooks have been added to these objects to make freezing and thawing possible. When a DBIx::DBO object is frozen the read-only and read-write databse handles are not stored with the object, so you'll have to reconnect them afterwards.

my $query = $dbo->query('customers');
$query->where('status', '=', 'payment due');
my $frozen = Storable::nfreeze($query);

...

my $query = Storable::thaw($frozen);
# Replace the DBO after thawing
$query->dbo = $dbo;
while (my $row = $query->fetch) {
...

Please note that Storable before version 2.38 was unable to store Row objects correctly. This only affected Row objects that had not detached from the parent Query object. To force a Row to detach, simply call the private _detach method on the row.

$row->_detach;
my $frozen = Storable::nfreeze($row);

SUBCLASSING

For details on subclassing the Query or Row objects see: "SUBCLASSING" in DBIx::DBO::Query and "SUBCLASSING" in DBIx::DBO::Row. This is the simple (recommended) way to create objects representing a single query, table or row in your database.

DBIx::DBO can be subclassed like any other object oriented module.

package MySubClass;
our @ISA = qw(DBIx::DBO);
...

The DBIx::DBO object is used to create Table, Query and Row objects. The classes these objects are blessed into are provided by _table_class, _query_class & _row_class methods. So to subclass all the DBIx::DBO::* objects, we need to provide our own class names via those methods.

package MySubClass;
our @ISA = qw(DBIx::DBO);

sub _table_class { 'MySubClass::Table' }
sub _query_class { 'MySubClass::Query' }
sub _row_class   { 'MySubClass::Row' }

...

package MySubClass::Table
our @ISA = qw(DBIx::DBO::Table);

...

Now all new objects created will be blessed into these classes.

This leaves only the DBIx::DBO::DBD hidden class, which acts as a SQL engine. This class is also determined in the same way as other objects, so to subclass DBIx::DBO::DBD add a _dbd_class method to DBIx::DBO with the new class name.

sub _dbd_class { 'MySubClass::DBD' }

Since databases differ slightly in their SQL, this class contains all the SQL specific calls for different DBDs. They are found in the class DBIx::DBO::DBD::xxx where xxx is the name of the driver for this DBI handle. A MySQL connection would have a DBD class of DBIx::DBO::DBD::mysql, and SQLite would use DBIx::DBO::DBD::SQLite. These classes would both inherit from DBIx::DBO::DBD.

When subclassing DBIx::DBO::DBD, because it uses multiple inheritance, the 'C3' method resolution order is required. This is setup for you automatically when the connection is first made. These classes are also automatically created if they don't exist.

AUTHOR

Vernon Lyon, <vlyon AT cpan.org>

SUPPORT

You can find more information for this module at:

BUGS

Please report any bugs or feature requests to bug-dbix-dbo AT rt.cpan.org, or through the web interface at http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DBIx-DBO. I will be notified, and then you'll automatically be notified of progress on your bug as I make changes.

COPYRIGHT & LICENSE

Copyright 2009-2014 Vernon Lyon, all rights reserved.

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

SEE ALSO

DBI, DBIx::SearchBuilder.