Author: spadkins
Date: Fri Dec 5 08:50:45 2008
New Revision: 12140
Modified:
p5ee/trunk/App-Repository/lib/App/Repository/Oracle.pm
Log:
added insert_rows() logic for multiple rows per insert statement for Oracle
Modified: p5ee/trunk/App-Repository/lib/App/Repository/Oracle.pm
==============================================================================
--- p5ee/trunk/App-Repository/lib/App/Repository/Oracle.pm (original)
+++ p5ee/trunk/App-Repository/lib/App/Repository/Oracle.pm Fri Dec 5
08:50:45 2008
@@ -240,7 +240,7 @@
# $insert_sql = $rep->_mk_insert_rows_sql ($table, [EMAIL PROTECTED], [EMAIL
PROTECTED], \%options);
# i.e. $options->{replace}
-sub TBD_mk_insert_rows_sql {
+sub _mk_insert_rows_sql {
&App::sub_entry if ($App::trace);
my ($self, $table, $cols, $rows, $options) = @_;
@@ -257,12 +257,19 @@
}
my $column_defs = $self->{table}{$table}{column};
- my $insert = $options->{replace} ? "replace" : "insert";
- my $sql = "$insert into $table\n (" . join(", ", @$cols) . ")\nvalues\n";
+ #INSERT ALL
+ # INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
+ # INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
+ # INTO suppliers (supplier_id, supplier_name) VALUES (3000, 'Google')
+ #SELECT * FROM dual;
+
+ my $sql = "insert all\n";
+ my $sql_row_prefix = " into $table (" . join(",", @$cols) . ") values ";
my ($value, $colnum, $quoted, $row, $col);
if ($rows && $#$rows > -1 && ref($rows->[0]) eq "ARRAY") {
for (my $rownum = 0; $rownum <= $#$rows; $rownum++) {
$row = $rows->[$rownum];
+ $sql .= $sql_row_prefix;
for ($colnum = 0; $colnum <= $#$cols; $colnum++) {
$col = $cols->[$colnum];
$value = $row->[$colnum];
@@ -278,14 +285,15 @@
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";
+ $sql .= ")\n";
}
}
else { # if $row is a HASH or OBJECT ...
for (my $rownum = 0; $rownum <= $#$rows; $rownum++) {
$row = $rows->[$rownum];
+ $sql .= $sql_row_prefix;
for ($colnum = 0; $colnum <= $#$cols; $colnum++) {
$col = $cols->[$colnum];
$value = $row->{$col};
@@ -301,31 +309,18 @@
if ($column_defs->{$col}{dbexpr_update}) {
$value = sprintf($column_defs->{$col}{dbexpr_update},
$value);
}
- $sql .= ($colnum == 0) ? " ($value" : ", $value";
- }
- $sql .= ($rownum < $#$rows) ? "),\n" : ")\n";
- }
- }
- if (!$options->{replace} && $options->{update}) {
- my $update = $options->{update};
- $sql .= "on duplicate key update";
- my $first_update_column = 1;
- for ($colnum = 0; $colnum <= $#$cols; $colnum++) {
- $col = $cols->[$colnum];
- if (!ref($update) || $update->{$col}) {
- $sql .= "," if (!$first_update_column);
- $first_update_column = 0;
- $sql .= "\n $col = values($col)";
+ $sql .= ($colnum == 0) ? "($value" : ",$value";
}
+ $sql .= ")\n";
}
- $sql .= "\n";
}
+ $sql .= "select * from dual\n";
&App::sub_exit($sql) if ($App::trace);
$sql;
}
# $nrows = $rep->_insert_rows ($table, [EMAIL PROTECTED], [EMAIL PROTECTED]);
-sub TBD_insert_rows {
+sub _insert_rows {
&App::sub_entry if ($App::trace);
my ($self, $table, $cols, $rows, $options) = @_;
$self->{error} = "";
@@ -366,16 +361,6 @@
print $App::DEBUG_FILE "\n";
}
- # The MySQL "insert ... on duplicate key update ..." statement
returns 2 rows affected
- # when the insert gets a collision and causes an update. So we
have to make this
- # adjustment. I don't know if it affects the "replace ..."
statement in a similar way,
- # but I figure this can't hurt.
- if ($options->{update} || $options->{replace}) {
- if ($retval > $nrows_this_insert) {
- $retval = $nrows_this_insert;
- }
- }
-
$nrows += $retval;
$rownum += $maxrows;
}
@@ -388,8 +373,8 @@
my ($fh);
if (!$rows_ref) {
my $file = $rows; # assume it is a file name
- open(App::Repository::MySQL::FILE, $file) || die "Unable to open
$file for reading: $!";
- $fh = \*App::Repository::MySQL::FILE;
+ open(App::Repository::Oracle::FILE, $file) || die "Unable to open
$file for reading: $!";
+ $fh = \*App::Repository::Oracle::FILE;
}
else {
$fh = $rows; # assume it is a file handle
@@ -421,7 +406,7 @@
}
$self->{numrows} = $nrows;
if (!$rows_ref) {
- close(App::Repository::MySQL::FILE);
+ close(App::Repository::Oracle::FILE);
}
}
if ($debug_sql) {
@@ -444,20 +429,14 @@
if ($import_method eq "basic") {
$nrows = $self->SUPER::import_rows($table, $columns, $file, $options);
}
- # DOESN'T WORK YET
- #elsif ($import_method eq "insert") {
- # $nrows = $self->insert_rows($table, $columns, $file, $options);
- #}
+ elsif ($import_method eq "insert") {
+ $nrows = $self->insert_rows($table, $columns, $file, $options);
+ }
else {
my $context_options = $self->{context}{options};
my $prefix = $context_options->{prefix};
my $debug_sql = $context_options->{debug_sql};
my ($timer, $elapsed_time);
- if ($debug_sql) {
- $timer = $self->_get_timer();
- print $App::DEBUG_FILE "DEBUG_SQL: import_rows()\n";
- print $App::DEBUG_FILE "$table (", join(",", @$columns), ")\n";
- }
my $datfile = $file;
@@ -466,9 +445,14 @@
$filebase =~ s/\.dat$//;
my $dbname = $self->{dbname};
- my $column_hash = sha1_hex(join(",", @$columns));
+ my $control_key_source = join(",", @$columns);
+ if ($options && %$options) {
+ my @option_keys = sort keys %$options;
+ $control_key_source .= ":" . join(",", map { $_ => $options->{$_}
} @option_keys);
+ }
+ my $control_key = sha1_hex($control_key_source);
- my $ctlfile =
"$prefix/data/app/Repository/$dbname/$table.$column_hash.ctl";
+ my $ctlfile =
"$prefix/data/app/Repository/$dbname/$table.$control_key.ctl";
if (! -f $ctlfile) {
mkdir("$prefix/data") if (! -d "$prefix/data");
mkdir("$prefix/data/app") if (! -d "$prefix/data/app");
@@ -496,15 +480,16 @@
}
my $cmd = "sqlldr userid=$self->{dbuser}/$self->[EMAIL PROTECTED]
data=$datfile control=$ctlfile bad=$badfile log=$logfile errors=0
$sqlldr_options > $outfile 2>&1";
- #print STDERR "sqlldr userid=$self->{dbuser}/$self->[EMAIL PROTECTED]
data=$datfile control=$ctlfile bad=$badfile log=$logfile errors=0
$sqlldr_options > $outfile 2>&1\n";
- my $rc = system($cmd);
- my $exit_value = $rc >> 8;
if ($debug_sql) {
- $elapsed_time = $self->_read_timer($timer);
- print $App::DEBUG_FILE "DEBUG_SQL: import_rows=[$nrows]
($elapsed_time sec) $DBI::errstr\n";
+ $timer = $self->_get_timer();
+ print $App::DEBUG_FILE "DEBUG_SQL: import_rows() :
$table.$control_key_source\n";
+ print $App::DEBUG_FILE "system> $cmd\n";
}
+ my $rc = system($cmd);
+ my $exit_value = $rc >> 8;
+
my $badfile_size = (-s $badfile || 0);
if ($rc || $badfile_size) {
# failed
@@ -523,6 +508,11 @@
unlink($logfile);
unlink($outfile);
}
+
+ if ($debug_sql) {
+ $elapsed_time = $self->_read_timer($timer);
+ print $App::DEBUG_FILE "DEBUG_SQL: import_rows=[$nrows]
($elapsed_time sec) $DBI::errstr\n";
+ }
}
&App::sub_exit($nrows) if ($App::trace);
@@ -615,7 +605,7 @@
sub TBDexplain_sql {
my ($self, $sql) = @_;
my $dbh = $self->{dbh};
- # NOTE: MySQL "explain" only works for "select".
+ # NOTE: Oracle "explain" only works for "select".
# We convert "update" and "delete" to "select" to explain them.
if (defined $dbh) {
if ($sql =~ s/^delete/select */is) {
@@ -708,11 +698,5 @@
return(undef, uc($dbschema), uc($table), "%");
}
-#INSERT ALL
-# INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
-# INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
-# INTO suppliers (supplier_id, supplier_name) VALUES (3000, 'Google')
-#SELECT * FROM dual;
-
1;
