/ 
DBIx-DBO-0.10
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 DBIx::DBO::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 DBIx::DBO::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 DBIx::DBO::Table object. Tables can be specified by their name or an arrayref of schema and table name or another DBIx::DBO::Table object.

query

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

Create a new DBIx::DBO::Query object from the tables specified. In scalar context, just the Query object will be returned. In list context, the Query object and DBIx::DBO::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_object);
$dbo->row($query_object);

Create and return a new DBIx::DBO::Row object.

selectrow_array

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

This provides access to the DBI->selectrow_array method. It defaults to using the read-only DBI handle.

selectrow_arrayref

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

This provides access to the DBI->selectrow_arrayref method. It defaults to using the read-only DBI handle.

selectall_arrayref

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

This provides access to the DBI->selectall_arrayref method. It defaults to using the read-only 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.

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.

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.

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 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. It has no effect after the connection has been made. 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).

QuoteIdentifier

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

RowClass

Override the class name for new Row objects. Row objects created will be blessed into this class, which should inhereit from DBIx::DBO::Row.

StoreRows

Boolean setting to cause Query objects to store 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.

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;

SUBCLASSING

DBIx::DBO supports multiple inheritance. For this reason MRO::Compat is required if the perl version you are using is less than 5.9.5 to ensure that the 'C3' method resolution order is used.

When subclassing DBIx::DBO::xxx, please note that the objects created with their new methods are blessed into DBD driver specific modules. 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.

When you subclass DBIx::DBO, it affects inheritance for all objects created by this DBO. For example, if using MySQL and a subclass of DBIx::DBO named MySubClass, then the object returned from "connect" would be blessed into MySubClass::DBD::mysql which would inherit from both MySubClass and DBIx::DBO::DBD::mysql. Any objects created by this DBO will have a similar inheritance path. For example, a new Query object would be blessed into MySubClass::Query::DBD::mysql which would in turn inherit from both MySubClass::Query and DBIx::DBO::Query::DBD::mysql. These classes are 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-2011 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.