gistfile1.pl

package OreOre::SQLMaker;

use strict;
use base qw(SQL::Abstract);

# $stmt = INSERT INTO table (col1, col2, col3) VALUES (?,?,?),(?,?,?)
# @bind = (val1, val2, val3, val21, val22, val23)
sub insert_multi {
    my $self = shift;
    my $table = shift;
    my @data = @_;
    return unless @data;

    my (@cols, $length, @bind, $options);
    # $sql->insert_multi(table, [\%value, \%value, \%value]);
    if (ref $data[0]->[0] eq "HASH") {
        $length = scalar @{ $data[0] };
        @cols = sort keys %{ $data[0]->[0] };
        @bind = map { @{$_}{@cols} } @{$data[0]};
        $options = $data[1];
    }
    # $sql->insert_multi(table, [name, age], [[bob, 20], [jack, 30]]);
    elsif (ref $data[0] eq "ARRAY") {
        @cols = @{ $data[0] };
        @bind = map { @{$_} } @{ $data[1] };
        $options = $data[2];
        $length = scalar @{ $data[1] };
    }

    my $ph = substr ('?,' x @cols, 0, -1); #  ?,?,?
    my $values_ph = "($ph),";              #  (?,?,?),
    # LOW_PRIORITY DELAYED HIGH_PRIORITY IGNORE
    my $option = $options || "";

    my $stmt = sprintf(<<'SQL', $option, $table, join(",", @cols), substr( $values_ph x $length, 0, -1 ));
INSERT %s INTO %s (%s) VALUES %s
SQL
    return ($stmt, @bind);
}

# use ON DUPLICATE KEY UPDATE
sub update_multi {
    my $self = shift;
    my ($table, @data) = @_;
    my ($stmt, @bind) = $self->insert_multi(@_);
    my @cols;
    if (ref $data[0]->[0] eq "HASH") {
        @cols = sort keys %{ $data[0]->[0] };
    } elsif (ref $data[0] eq "ARRAY") {
        @cols = @{ $data[0] };
    }
    $stmt .= "ON DUPLICATE KEY UPDATE ";
    my @vals = map { sprintf('%s=VALUES(%s)', $_, $_) } @cols;
    $stmt .= join(",", @vals);
    return ($stmt, @bind);
}

1;