/ 
MySQL-QueryMulti-0.08
River stage zero No dependents
/ MySQL::QueryMulti

NAME

MySQL::QueryMulti - module for querying multiple MySQL databases in parallel

VERSION

Version 0.08

SYNOPSIS

 my $qm = MySQL::QueryMulti->new;
 $qm->connect(
		[ get_dsn('pet1'), $ENV{DBI_USER}, $pass ],
		[ get_dsn('pet2'), $ENV{DBI_USER}, $pass ],
		... repeat as necessary ...,
		{ AutoInactiveDestroy => 0 }
 );

 $qm->prepare( "select * from pet order by owner" );  
 my $sth = $qm->execute; 

 while ( my @row = $sth->fetchrow_array ) {
	 print "@row\n";
 }

DESCRIPTION

MySQL::QueryMulti is a module that allows the user to query multiple MySQL databases in parallel and get an aggregated/concatentated result set back.

Requirements: * must have "create temporary table" privileges across all databases * schemas must be identical

MySQL::QueryMulti is built using DBI and hence has nearly identical method calls (connect, prepare, and execute). See method descriptions below.

The primary use case for this is when you have a sharded database environment.

See link for more info on sharding:

SUBROUTINES/METHODS

new( %hash );

Object constructor. Accepts an optional hash of arguments.

Arguments:

raise_error( 0|1 )

Allows you to change the behavior of error handling. The default is to throw an exception. Pass true or false to modify behavior as necessary.

connect ( [ $dsn, $user, $pass ], [ $dsn2, $user, $pass ], ..., { DBI attributes } )

Method for establishing a connection to a set of databases. The arguments are similar to DBI::connect except you pass an array of array references that each contain their respective DBI::connect arguments (dsn, user, password). Attributes are only specified once (as the last arg) and applied to each connection automatically.

Passing the attributes "RaiseError" and "PrintError" will have no effect. The "raise_error" attribute of MySQL::QueryMulti controls that behavior.

Returns true on success or false on error.

example:

    $qm->connect( [ get_dsn('pet1'), $ENV{DBI_USER}, $pass ], [ get_dsn('pet2'), $ENV{DBI_USER}, $pass ], ... repeat as necessary ..., { AutoInactiveDestroy => 0 } );

prepare

Identical to DBI::prepare except it does the prepare for all databases in the set.

Returns true on success or false on error.

execute

Identical to DBI::execute except it returns either a statement handle or the number of rows affected depending on the type of query. A statement handle is returned for select queries. The number of affected rows for all others.

Returns a statement handle or the number of affected rows on success. Returns undef on error.

LIMITATIONS

* This does not provide true parallelism in that it leverages the 
  "async" feature of DBD::MySQL.  You could accomplish true parallelism with 
  threads or the heavier fork/exec, but that adds extra complexity (especially
  if you have to recompile the mysql client libs with threading enabled).  
  This keeps things simple and still provides reasonable performance.
  
* Does not work with count or sum aggregate functions.

* Stored procedures have not been tested so use them at your own risk.

AUTHOR

John Gravatt, <gravattj at cpan.org>

BUGS

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

SUPPORT

You can find documentation for this module with the perldoc command.

perldoc MySQL::QueryMulti

You can also look for information at:

ACKNOWLEDGEMENTS

LICENSE AND COPYRIGHT

Copyright 2012 John Gravatt.

This program is free software; you can redistribute it and/or modify it under the terms of either: the GNU General Public License as published by the Free Software Foundation; or the Artistic License.

See http://dev.perl.org/licenses/ for more information.