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.08
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

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.

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.

DebugSQL

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

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 it's advisable that MRO::Compat is installed if the perl version you are using is less than 5.9.5 as that module will 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 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.