MySQL::Insert - extended inserts for MySQL via DBI

    # Insert two rows into sample_table using $dbh database handle

    use MySQL::Insert;

    $MySQL::Insert::MAX_ROWS_TO_QUERY = 1000;

    my $inserter = MySQL::Insert->new( $dbh, 'sample_table', [ @field_names ] );

    $inserter->insert_row( { fldname => 'fldvalue1' } );
    $inserter->insert_row( { fldname => 'fldvalue2' } );

    # Insert row into sample_table using $dbh database handle
    # If fldvalue3 is passed as scalar ref then it is not quoted
    # Used to insert MySQL built-in functions like NOW() and NULL values.

    $inserter->insert_row( { fldname => \'NOW()' } );

    undef $inserter;

package MySQL::Insert;

use warnings;
use strict;

our $MAX_ROWS_TO_QUERY = 1000;

our $VERSION = '0.05';

sub new {
    my $type = shift;

    my $self = { };
    $self = bless $self, $type;
    $self->_init( @_ );
    return $self;
}

sub _init {
    my ($self, $dbh, $table, $fields) = @_;

    $self->{_dbh} = $dbh;
    $self->{_table} = $table;

    $self->set_fields( $fields );

    $self->{_total_rows} = 0;
    $self->{_do_append_row_to_query} = 0;
    $self->{_query_exists} = 0;
}

sub set_fields {
    my $self = shift;

    return unless @_ && $_[0];

    my @fields = ref $_[0] ? @{$_[0]} : @_;

    $self->{_fields} = \@fields;
    $self->{_name_fields} = join ", ", map "`$_`", @fields;

    return 1;
}

sub get_fields {
    my ($self) = @_;

    return unless $self->{_fields};
    return wantarray ? @{$self->{_fields}} : scalar @{$self->{_fields}};
}

DESTROY {
    my $self = shift;

    $self->_finish_current_row;

    $self->_execute_query();
}

sub insert_row {
    my ($self, $new_row) = @_;

    my $query_executed = $self->_finish_current_row();

    $self->{_do_append_row_to_query} = 1;
    $self->{_current_row} = $new_row;

    return $query_executed;
}

# Private methods

sub _finish_current_row {
    my $self = shift;

    my $query_executed;

    if ( $self->{_do_append_row_to_query} ) {

	if ( $self->{_total_rows} >= $MAX_ROWS_TO_QUERY ) {
	    $query_executed = $self->_execute_query();
	}

	$self->_append_row_to_query_rows;

	$self->{_do_append_row_to_query} = 0;
    }

    return $query_executed;
}

sub _execute_query {
    my $self = shift;

    return if ! $self->{_query_exists};

    my $query = 
	qq|INSERT IGNORE $self->{_table} ($self->{_name_fields}) VALUES |
	. join(", ", @{ $self->{_query_rows} }) . ";\n";

    my $result = $self->{_dbh}->do( $query ) or return;

    # clear everyting
    $self->{_query_exists} = 0;
    $self->{_total_rows} = 0;
    $self->{_query_rows} = [];

    return $result;
}

sub _append_row_to_query_rows {
    my ($self) = @_;

    unless ($self->get_fields()) {
	$self->set_fields( keys %{$self->{_current_row}} );
    }

    my @data_row;
    for my $field ($self->get_fields()) {

	my $value = $self->{_current_row}->{ $field };
	
	# do not quote scalar refs
        if ( ref $value eq 'SCALAR' ) {
            push @data_row, ${ $value } || q{''};
        }
	else {
            push @data_row, $self->{_dbh}->quote( $value || $value );    
        }
    }

    push @{$self->{_query_rows}}, "\n\t(".join(', ', @data_row).")";

    $self->{_query_exists} = 1;
    $self->{_total_rows}++;
}

1; # End of MySQL::Insert