Author: spadkins
Date: Tue Jan 23 14:32:38 2007
New Revision: 8684
Modified:
p5ee/trunk/App-Repository/lib/App/Repository/MySQL.pm
Log:
add {options}{update} to create 'on duplicate key update' clause. allow
_mk_insert_rows_sql() to work on arrays of hashrefs
Modified: p5ee/trunk/App-Repository/lib/App/Repository/MySQL.pm
==============================================================================
--- p5ee/trunk/App-Repository/lib/App/Repository/MySQL.pm (original)
+++ p5ee/trunk/App-Repository/lib/App/Repository/MySQL.pm Tue Jan 23
14:32:38 2007
@@ -134,39 +134,65 @@
my $dbh = $self->{dbh};
if ($#$cols == -1) {
- $self->{error} = "Database->_mk_insert_row_sql(): no columns
specified";
+ $self->{error} = "Database->_mk_insert_rows_sql(): no columns
specified";
return();
}
- my $tabcols = $self->{table}{$table}{column};
+ my $column_defs = $self->{table}{$table}{column};
my $insert = $options->{replace} ? "replace" : "insert";
my $sql = "$insert into $table\n (" . join(", ", @$cols) . ")\nvalues\n";
my ($value, $colnum, $quoted, $row, $col);
- for (my $rownum = 0; $rownum <= $#$rows; $rownum++) {
- $row = $rows->[$rownum];
- for ($colnum = 0; $colnum <= $#$cols; $colnum++) {
- $col = $cols->[$colnum];
- if (!defined $row || $#$row == -1) {
- $value = "?";
- }
- else {
+ if ($rows && $#$rows > -1 && ref($rows->[0]) eq "ARRAY") {
+ for (my $rownum = 0; $rownum <= $#$rows; $rownum++) {
+ $row = $rows->[$rownum];
+ for ($colnum = 0; $colnum <= $#$cols; $colnum++) {
+ $col = $cols->[$colnum];
$value = $row->[$colnum];
if (!defined $value) {
$value = "NULL";
}
else {
- $quoted = (defined $tabcols->{$col}{quoted}) ?
($tabcols->{$col}{quoted}) : ($value !~ /^-?[0-9.]+$/);
+ $quoted = (defined $column_defs->{$col}{quoted}) ?
($column_defs->{$col}{quoted}) : ($value !~ /^-?[0-9.]+$/);
if ($quoted) {
$value = $dbh->quote($value);
}
}
+ if ($column_defs->{$col}{dbexpr_update}) {
+ $value = sprintf($column_defs->{$col}{dbexpr_update},
$value);
+ }
+ $sql .= ($colnum == 0) ? " ($value" : ", $value";
}
- if ($tabcols->{$col}{dbexpr_update}) {
- $value = sprintf($tabcols->{$col}{dbexpr_update}, $value);
+ $sql .= ($rownum < $#$rows) ? "),\n" : ")\n";
+ }
+ }
+ else { # if $row is a HASH or OBJECT ...
+ for (my $rownum = 0; $rownum <= $#$rows; $rownum++) {
+ $row = $rows->[$rownum];
+ for ($colnum = 0; $colnum <= $#$cols; $colnum++) {
+ $col = $cols->[$colnum];
+ $value = $row->{$col};
+ if (!defined $value) {
+ $value = "NULL";
+ }
+ else {
+ $quoted = (defined $column_defs->{$col}{quoted}) ?
($column_defs->{$col}{quoted}) : ($value !~ /^-?[0-9.]+$/);
+ if ($quoted) {
+ $value = $dbh->quote($value);
+ }
+ }
+ if ($column_defs->{$col}{dbexpr_update}) {
+ $value = sprintf($column_defs->{$col}{dbexpr_update},
$value);
+ }
+ $sql .= ($colnum == 0) ? " ($value" : ", $value";
}
- $sql .= ($colnum == 0) ? " ($value" : ", $value";
+ $sql .= ($rownum < $#$rows) ? "),\n" : ")\n";
+ }
+ }
+ if ($options->{update}) {
+ $sql .= "on duplicate key update";
+ foreach $col (@$cols) {
+ $sql .= "\n $col = values($col)";
}
- $sql .= ($rownum < $#$rows) ? "),\n" : ")\n";
}
&App::sub_exit($sql) if ($App::trace);
$sql;
@@ -223,7 +249,6 @@
$rows = []; # we will be refilling this buffer
my %options = ( %$options ); # make a copy so it can be modified
$options{maxrows} = 100;
- $sql = $self->_mk_insert_row_sql($table, $cols);
$nrows = 0;
while (1) {
$rows = $self->_read_rows_from_file($fh, $cols, \%options);
@@ -316,7 +341,6 @@
$rows = []; # we will be refilling this buffer
my %options = ( %$options ); # make a copy so it can be modified
$options{maxrows} = 100;
- $sql = $self->_mk_insert_row_sql($table, $cols);
$nrows = 0;
while (1) {
$rows = $self->_read_rows_from_file($fh, $cols, \%options);
