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

Author: spadkins
Date: Tue Dec 12 11:36:47 2006
New Revision: 8388

Modified:
   p5ee/trunk/App-Repository/lib/App/Repository/MySQL.pm

Log:
trying to find a data load method that is stable for 5.1.14+

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 Dec 12 11:36:47 2006
@@ -260,6 +260,88 @@
     return($nrows);
 }
 
+###################################################################
+# This routine was written because a reliable data load method is
+# needed for MySQL 5.1.14+.  There are instabilities in this beta
+# version of software that cause "load data local infile" and 
+# extended inserts to both hang the server.  Now I am trying to
+# write the extended inserts out to a file and load it with the
+# "mysql" client program.
+###################################################################
+# $nrows = $rep->insert_rows_mysql ($table, \@cols, \@rows);
+sub insert_rows_mysql {
+    &App::sub_entry if ($App::trace);
+    my ($self, $table, $cols, $rows, $options) = @_;
+    $self->{error} = "";
+    my ($sql, $retval);
+   
+    my $dbh = $self->{dbh};
+    return 0 if (!defined $dbh);
+
+    my $nrows = 0;
+    my $ok = 1;
+    my $context_options = $self->{context}{options};
+    my $debug_sql = $context_options->{debug_sql};
+    my $explain_sql = $context_options->{explain_sql};
+    my ($timer, $elapsed_time);
+    if ($debug_sql) {
+        $timer = $self->_get_timer();
+    }
+    my $rows_ref = ref($rows);
+    if ($rows_ref eq "ARRAY") {
+        $sql = $self->_mk_insert_rows_sql($table, $cols, $rows);
+        if ($debug_sql) {
+            print "DEBUG_SQL: _insert_rows()\n";
+            print $sql;
+        }
+        $retval = $dbh->do($sql);
+        if ($debug_sql) {
+            print "DEBUG_SQL: retval [$retval] $DBI::errstr\n";
+            print "\n";
+        }
+
+        $nrows = $retval;
+        $self->{numrows} = $nrows;
+        if ($retval != $#$rows + 1) {
+            $ok = 0;
+        }
+    }
+    else {
+        my ($fh, $sqlfh);
+        my $file = $rows;          # $rows must be a file name
+        open(App::Repository::MySQL::FILE, $file) || die "Unable to open $file for reading: $!";
+        $fh = \*App::Repository::MySQL::FILE;
+        open(App::Repository::MySQL::SQL, "| gzip > $file.sql.gz") || die "Unable to open $file.sql.gz for writing: $!";
+        $sqlfh = \*App::Repository::MySQL::SQL;
+        $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);
+            last if ($#$rows == -1);
+            $sql = $self->_mk_insert_rows_sql($table, $cols, $rows);
+            print $sqlfh $sql, ";\n";
+            $nrows += ($#$rows + 1);
+        }
+        if (!$rows_ref) {
+            close(App::Repository::MySQL::FILE);
+            close(App::Repository::MySQL::SQL);
+        }
+        my $cmd = "zcat $file.sql.gz | mysql --host=$self->{dbhost} --user=$self->{dbuser} --password=$self->{dbpass} $self->{dbname}";
+        $retval = system($cmd);
+        if ($retval) {
+            $nrows = 0;
+        }
+        $self->{numrows} = $nrows;
+    }
+    $self->{sql} = $sql;
+    $self->{numrows} = $nrows;
+    &App::sub_exit($nrows) if ($App::trace);
+    return($nrows);
+}
+
 sub _load_table_key_metadata {
     &App::sub_entry if ($App::trace);
     my ($self, $table) = @_;
@@ -419,9 +501,9 @@
     }
     elsif ($import_method eq "insert") {
         $nrows = $self->insert_rows($table, $columns, $file, $options);
-        #open(App::Repository::MySQL::FILE, $file) || die "Unable to open $file for reading: $!";
-        #$nrows = $self->insert_rows($table, $columns, \*App::Repository::MySQL::FILE, $options);
-        #close(App::Repository::MySQL::FILE);
+    }
+    elsif ($import_method eq "insert_mysql") {
+        $nrows = $self->insert_rows_mysql($table, $columns, $file, $options);
     }
     else {
         my $local = $options->{local};