File Coverage

blib/lib/DBIx/BulkLoader/Mysql.pm

Criterion	Covered	Total	%
statement	36	83	43.3
branch	0	18	0.0
condition			n/a
subroutine	12	23	52.1
pod	10	10	100.0
total	58	134	43.2

package DBIx::BulkLoader::Mysql;

use strict;
use warnings;

our $VERSION = '1.006';

use constant key_count=>0;
use constant key_single_insert=>1;
use constant key_bulk_insert=>2;
use constant key_buffer=>3;
use constant key_sql_insert=>4;
use constant key_sql_columns=>5;
use constant key_bulk_sql=>6;
use constant key_single_sql=>7;
use constant key_data=>8;
use constant key_placeholder_count=>9;


# Below is stub documentation for your module. You'd better edit it!

=head1 NAME

DBIx::BulkLoader::Mysql - Perl extension for mysql bulk loading

=head1 SYNOPSIS

  use DBIx::BulkLoader::Mysql;

  # non repeating portion of the insert statement
  my $insert='insert into bulk_insert (col_a,col_b,col_c) values ';

  # repeating portion of the insert statement
  my $placeholders='(?,?,?)';

  # how many rows to buffer until insert is called
  my $bulk_insert_count=5;

  # db connection
  my $dbh=DBI->connect(db connection info here);
  my $placeholder_count=3;

  my ($bulk,$error)=DBIx::BulkLoader::Mysql->new(
  		dbh=>$dbh
		,sql_insert=>$insert
		,placeholders=>$placeholders
   );
   die $error unless $bulk;

   for( 1 .. 50 ) {
     $bulk->insert(qw(a b c));
   }
   # inserted 50 rows at once

   $bulk->insert(qw(l l x));
   # inserted 0 rows

   $bulk->insert(qw(l l x));
   # inserted 0 rows

   $bulk->flush;
   # inserted 2 rows 1 at a time

=head1 DESCRIPTION

Simple buffering bulk loader interface for mysql.

=head2 EXPORT

None.

=head2 OO Methods

This section covers the OO methods for this package.

=over 4

=item * my ($bulk,$error)=DBIx::BulkLoader::Mysql->new(%hash);

Package constructor.

	$bulk is undef on error
	$error explains why $bulk is undef

Constructor options

                dbh=>$dbh
		 Sets the DBH object

                sql_insert=>$insert
		 Contains the body of the sql statement minus the
		 placeholder segment.

                placeholders=>$placeholders
		 Placeholder segment of the sql statement

                placeholder_count=>3
		 Optional argument
		  If you get strange insert counts or dbi bails
		  set this option manually

                bulk_insert_count=>50
		 Optional argument
		  Sets the number of rows to buffer for insert.

                prepare_args=>{}
		 Optional argument
		  Arguments to be passed to $dbh->prepare
		  See DBD::mysql

=cut

sub new {
	my ($class,%hash)=@_;
	my $s=bless [],$class;
	$s->[key_data]=[];
	$hash{bulk_insert_count}=$hash{bulk_insert_count}
		? $hash{bulk_insert_count} : 50;

	# stop here if we have some bad arguments
	return (undef,'placeholders=>"" not set!') unless $hash{placeholders};
	return (undef,'sql_insert=>"" not set!') unless $hash{sql_insert};
	return (undef,'dbh=>$dbh not set!') unless $hash{dbh};
	unless($hash{placeholder_count}) {
	  for( $hash{placeholders}=~ /\?/g){
	   $hash{placeholder_count}++
	  }
	}
	$s->[key_placeholder_count]=$hash{placeholder_count} ;

	$s->[key_buffer]=
		$hash{placeholder_count} * $hash{bulk_insert_count};

	my $prep_args=$hash{prepare_args} ? $hash{prepare_args} : ({});
	my $single=join ' ',$hash{sql_insert},$hash{placeholders};

	# run the prepare statement
	$s->[key_single_sql]=$single;
	$s->[key_single_insert]=eval {
		$hash{dbh}->prepare(
			$single
			,$hash{prepare_args}
		);
	};
	return undef,"failed to prepare: $single" if $@;


	my @placeholders;
	for(1 .. $hash{bulk_insert_count}) {
		push @placeholders,$hash{placeholders};
	}
	my $bulk=join(' ',$hash{sql_insert},
		join(', ',@placeholders)
	);

	return undef,"failed to prepare: $bulk" if $@;
	$s->[key_bulk_sql]=$bulk;
	$s->[key_bulk_insert]=eval {
		$hash{dbh}->prepare(
			$bulk
			,$hash{prepare_args}
		);
	};

	$s,undef;
}

=item * $bulk->flush;

Empties the placeholder buffer

=cut

sub flush () {
	my ($s)=@_;
	my $row=$s->[key_data];
	while(my @single=splice(@$row,0, $s->get_placeholder_count)) {
		$s->get_prepared_single_sth->execute(@single);
	}
}

sub DESTROY {
	@{$_[0]}=()
}

=item * $bulk->insert($x,$y,$z);

Inserts the placeholder arguments onto the buffer stack. This does not cause an insert, unless the total number of rows is the same as the constructor call "bulk_insert_count=>50".

=cut

sub insert {
	my ($s,@data)=@_;
	my $row=$s->[key_data];
	push @$row,@data;
	if((1 + $#$row)==$s->get_buffer_size) {
		$s->get_prepared_bulk_sth->execute(@$row);
		@$row=();
	}

}

=item * my $columns=$bulk->get_placeholder_count;

Gets the total number of column placeholders.

=cut

sub get_placeholder_count () { $_[0]->[key_placeholder_count] }

=item * my $buffer_size=$bulk->get_buffer_size;

Gets the total size of the array used for insert.

=cut

sub get_buffer_size () { $_[0]->[key_buffer] }

=item * my $sql_single=$bulk->single_sql;

Gets the raw sql statement used for single row inserts.

=cut

sub single_sql() { $_[0]->[key_single_sql] }

=item * my $bulk_sql=$bulk->bulk_sql;

Gets the raw sql statement used for bulk row inserts.

=cut

sub bulk_sql() { $_[0]->[key_bulk_sql] }

=item * my $single_sth=$bulk->get_prepared_single_sth;

Gets the prepared statement handle for single row inserts.

=cut

sub get_prepared_single_sth () { $_[0]->[key_single_insert] }

=item * my $bulk_sth=$bulk->get_prepared_bulk_sth;

Gets the prepared statement handle for bulk row inserts.

=cut

sub get_prepared_bulk_sth () { $_[0]->[key_bulk_insert] }

=item * my @buffer=$bulk->get_buffered_data;

Returns a list containing the current buffered data

=cut

sub get_buffered_data () { @{$_[0]->[key_data]} }

=back

=head1 SEE ALSO

DBI, DBD::mysql

=head1 Source Forge Project

If you find this software usefil please donate to the Source Forge Project.

L

=head1 AUTHOR

Michael Shipper

=head1 COPYRIGHT AND LICENSE

Copyright (C) 2010 by Michael Shipper

This library is free software; you can redistribute it and/or modify
it under the same terms as Perl itself, either Perl version 5.8.4 or,
at your option, any later version of Perl 5 you may have available.


=cut

######################################
#
# End of the package
1;
__END__