NAME

SQL::Abstract::Plugin::InsertMulti - add mysql bulk insert supports for SQL::Abstract

SYNOPSIS

use SQL::Abstract;
use SQL::Abstract::Plugin::InsertMulti;

my $sql = SQL::Abstract->new;
my ($stmt, @bind) = $sql->insert_multi('people', [
  +{ name => 'foo', age => 23, },
  +{ name => 'bar', age => 40, },
]);

DESCRIPTION

SQL::Abstract::Plugin::InsertMulti is enable bulk insert support for SQL::Abstract. Declare 'use SQL::Abstract::Plugin::InsertMulti;' with 'use SQL::Abstract;', exporting insert_multi() and update_multi() methods to SQL::Abstract namespace from SQL::Abstract::Plugin::InsertMulti. Plugin system is depends on 'into' options of Sub::Exporter.

Notice: please check your mysql_allow_packet parameter using this module.

METHODS

insert_multi($table, \@data, \%opts)

my ($stmt, @bind) = $sql->insert_multi('foo', [ +{ a => 1, b => 2, c => 3 }, +{ a => 4, b => 5, c => 6, }, ]);
# $stmt = q|INSERT INTO foo( a, b, c ) VALUES ( ?, ?, ? ), ( ?, ?, ? )|
# @bind = (1, 2, 3, 4, 5, 6);

@data is HashRef list. %opts details is below.

• ignore

  Use 'INSERT IGNORE' instead of 'INSERT INTO'.

• update

  Use 'ON DUPLICATE KEY UPDATE'. This value is same as update()'s data parameters.

• update_ignore_fields

  update_multi() method is auto generating 'ON DUPLICATE KEY UPDATE' parameters:

  my ($stmt, @bind) = $sql->update_multi('foo', [qw/a b c/], [ [ 1, 2, 3 ], [ 4, 5, 6 ] ]);
  # $stmt = q|INSERT INTO foo( a, b, c ) VALUES ( ?, ?, ? ), ( ?, ?, ? ) ON DUPLICATE KEY UPDATE a = VALUES( a ), b = VALUES( b ), c = VALUES( c )|
  # @bind = (1, 2, 3, 4, 5, 6);
  

  given update_ignore_fields,

  my ($stmt, @bind) = $sql->update_multi('foo', [qw/a b c/], [ [ 1, 2, 3 ], [ 4, 5, 6 ] ], +{ update_ignore_fields => [qw/b c/], });
  # $stmt = q|INSERT INTO foo( a, b, c ) VALUES ( ?, ?, ? ), ( ?, ?, ? ) ON DUPLICATE KEY UPDATE a = VALUES( a )|
  # @bind = (1, 2, 3, 4, 5, 6);
  

insert_multi($table, \@field, \@data, \%opts)

my ($stmt, @bind) = $sql->insert_multi('foo', [qw/a b c/], [ [ 1, 2, 3 ], [ 4, 5, 6 ] ]);
# $stmt = q|INSERT INTO foo( a, b, c ) VALUES ( ?, ?, ? ), ( ?, ?, ? )|
# @bind = (1, 2, 3, 4, 5, 6);

@data is ArrayRef list. See also ["insert_multi($table, \@data, \%opts)"](#insert_multi($table, \@data, \%opts)) %opts details.

update_multi($table, \@data, \%opts)

@data is HashRef list. See also ["insert_multi($table, \@data, \%opts)"](#insert_multi($table, \@data, \%opts)) %opts details.

my ($stmt, @bind) = $sql->update_multi('foo', [ [ 1, 2, 3 ], [ 4, 5, 6 ] ]);
# $stmt = q|INSERT INTO foo( a, b, c ) VALUES ( ?, ?, ? ), ( ?, ?, ? ) ON DUPLICATE KEY UPDATE a = VALUES( a ), b = VALUES( b ), c = VALUES( c )|
# @bind = (1, 2, 3, 4, 5, 6);

update_multi($table, \@field, \@data, \%opts)

my ($stmt, @bind) = $sql->update_multi('foo', [qw/a b c/], [ +{ a => 1, b => 2, c => 3 }, +{ a => 4, b => 5, c => 6, }, ]);
# $stmt = q|INSERT INTO foo( a, b, c ) VALUES ( ?, ?, ? ), ( ?, ?, ? ) ON DUPLICATE KEY UPDATE a = VALUES( a ), b = VALUES( b ), c = VALUES( c )|
# @bind = (1, 2, 3, 4, 5, 6);

@data is ArrayRef list. See also ["insert_multi($table, \@data, \%opts)"](#insert_multi($table, \@data, \%opts)) %opts details.

AUTHOR

Toru Yamaguchi zigorou@cpan.org

Thanks ma.la http://subtech.g.hatena.ne.jp/mala/. This module is based on his source codes.

SEE ALSO

• http://subtech.g.hatena.ne.jp/mala/20090729/1248880239
• http://gist.github.com/158203
• SQL::Abstract
• Sub::Exporter

LICENSE

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.