The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.
 / 
DBIx-DBO-0.13
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_object);
  $dbo->row($query_object);

Create and return a new 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. Changing this 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).

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.

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

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 modules use multiple inheritance, because objects created are blessed into DBD driver specific classes. For this to function correctly they must use the 'C3' method resolution order. To simplify subclassing this is automatically set for you when the objects are first created.

For example, if using MySQL and a subclass of DBIx::DBO named MySubClass, then the object returned from the "connect", "connect_readonly" or "new" method would be blessed into MySubClass::DBD::mysql which would inherit from both MySubClass and DBIx::DBO::DBD::mysql. These classes are automatically created if they don't exist.

In this way it is fairly trivial to override most of the methods, but not all of them. This is because some methods are common to all the classes and are defined in DBIx::DBO::Common. And new Table, Query and Row objects created will still be blessed into DBIx::DBO::* classes. The classes these new objects are blessed into are provided by _table_class, _query_class and _row_class methods. To override these methods and subclass the whole of DBIx::DBO::*, we need to provide our own MySubClass::Common package with new class names for our objects.

  package MySubClass::Common;
  our @ISA = qw(DBIx::DBO::Common);
  
  sub _table_class { 'MySubClass::Table' }
  sub _query_class { 'MySubClass::Query' }
  sub _row_class   { 'MySubClass::Row' }

Also ensure that we inherit from this common class.

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

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

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

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

In this example we will have subclassed all the modules.

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.