[svn:p5ee] r8684 - p5ee/trunk/App-Repository/lib/App/Repository

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);