bulk insert and update support for SQL::Abstract
Perl
Switch branches/tags
Nothing to show
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Failed to load latest commit information.
lib/SQL/Abstract/Plugin
t
xt
.gitignore
.travis.yml
Build.PL
Changes
LICENSE
MANIFEST
MANIFEST.SKIP
META.json
README.md
cpanfile

README.md

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

LICENSE

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