Skip to content

Instantly share code, notes, and snippets.

@mala
Created July 29, 2009 15:04
Show Gist options
  • Save mala/158203 to your computer and use it in GitHub Desktop.
Save mala/158203 to your computer and use it in GitHub Desktop.
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment