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)" %opts details.

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

@data is HashRef list. See also "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)" %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.

LICENSE

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