public
Last active

  • Download Gist
gistfile1.pl
Perl
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58
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;

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.