X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=FS%2FFS%2FCursor.pm;h=faa15f9f6bd89cda31ff61053afdcc80c6d36674;hp=f6d8816a715cd26123f1967781306f51cf0f1949;hb=20f03d52cc6c930f610c0b4466eeeeda54fdbb40;hpb=4ee96719dd1665ef3694aa983cc9046b53e8cc5d

diff --git a/FS/FS/Cursor.pm b/FS/FS/Cursor.pm
index f6d8816a7..faa15f9f6 100644
--- a/FS/FS/Cursor.pm
+++ b/FS/FS/Cursor.pm
@@ -4,7 +4,7 @@ use strict;
 use vars qw($DEBUG $buffer);
 use FS::Record;
 use FS::UID qw(myconnect driver_name);
-use Scalar::Util qw(refaddr);
+use Scalar::Util qw(refaddr blessed);
 
 $DEBUG = 2;
 
@@ -29,23 +29,29 @@ while ( my $row = $search->fetch ) {
 
 =over 4
 
-=item new ARGUMENTS
+=item new ARGUMENTS [, DBH ]
 
 Constructs a cursored search.  Accepts all the same arguments as qsearch,
 and returns an FS::Cursor object to fetch the rows one at a time.
 
+DBH may be a database handle; if so, the cursor will be created on that 
+connection and have all of its transaction state. Otherwise a new connection
+will be opened for the cursor.
+
 =cut
 
 sub new {
   my $class = shift;
+  my $dbh;
+  if ( blessed($_[-1]) and $_[-1]->isa('DBI::db') ) {
+    $dbh = pop;
+  }
   my $q = FS::Record::_query(@_); # builds the statement and parameter list
-  my $dbh = myconnect();
 
   my $self = {
     query => $q,
     class => 'FS::' . ($q->{table} || 'Record'),
     buffer => [],
-    dbh   => $dbh,
     position => 0, # for mysql
   };
   bless $self, $class;
@@ -60,12 +66,25 @@ sub new {
 
   my $statement;
   if ( driver_name() eq 'Pg' ) {
+    if (!$dbh) {
+      $dbh = myconnect();
+      $self->{autoclean} = 1;
+    }
+    $self->{dbh} = $dbh;
     $statement = "DECLARE ".$self->{id}." CURSOR FOR ".$q->{statement};
   } elsif ( driver_name() eq 'mysql' ) {
     # build a cursor from scratch
-    $statement = "CREATE TEMPORARY TABLE $self->{id} 
-      (rownum INT AUTO_INCREMENT, PRIMARY KEY (rownum))
-      $q->{statement}";
+    #
+    #
+    # there are problems doing it this way, and we don't have time to resolve
+    # them all right now...
+    #$statement = "CREATE TEMPORARY TABLE $self->{id} 
+    #  (rownum INT AUTO_INCREMENT, PRIMARY KEY (rownum))
+    #  $q->{statement}";
+
+    # one of those problems is locking, so keep everything on the main session
+    $self->{dbh} = $dbh = FS::UID::dbh();
+    $statement = $q->{statement};
   }
 
   my $sth = $dbh->prepare($statement)
@@ -83,8 +102,11 @@ sub new {
   } elsif ( driver_name() eq 'mysql' ) {
     # make sure we're not holding any locks on the tables mentioned
     # in the query
-    $dbh->commit if driver_name() eq 'mysql';
-    $self->{fetch} = $dbh->prepare("SELECT * FROM $self->{id} ORDER BY rownum LIMIT ?, $buffer");
+    #$dbh->commit if driver_name() eq 'mysql';
+    #$self->{fetch} = $dbh->prepare("SELECT * FROM $self->{id} ORDER BY rownum LIMIT ?, $buffer");
+
+    # instead, fetch all the rows at once
+    $self->{buffer} = $sth->fetchall_arrayref( {} );
   }
 
   $self;
@@ -115,27 +137,29 @@ sub fetch {
 
 sub refill {
   my $self = shift;
-  my $sth = $self->{fetch};
-  $sth->bind_param(1, $self->{position}) if driver_name() eq 'mysql';
-  $sth->execute or die $sth->errstr;
-  my $result = $self->{fetch}->fetchall_arrayref( {} );
-  $self->{buffer} = $result;
-  $self->{position} += $sth->rows;
-  scalar @$result;
+  if (driver_name() eq 'Pg') {
+    my $sth = $self->{fetch};
+    $sth->bind_param(1, $self->{position}) if driver_name() eq 'mysql';
+    $sth->execute or die $sth->errstr;
+    my $result = $self->{fetch}->fetchall_arrayref( {} );
+    $self->{buffer} = $result;
+    $self->{position} += $sth->rows;
+    scalar @$result;
+  } # mysql can't be refilled, since everything is buffered from the start
 }
 
 sub DESTROY {
   my $self = shift;
+  return if driver_name() eq 'mysql';
+
   return unless $self->{pid} eq $$;
-  if ( driver_name() eq 'Pg' ) {
-    $self->{dbh}->do('CLOSE '. $self->{id})
-      or die $self->{dbh}->errstr; # clean-up the cursor in Pg
-  } elsif ( driver_name() eq 'mysql' ) {
-    # nothing; the temporary table will evaporate when the 
-    # session closes.
+  $self->{dbh}->do('CLOSE '. $self->{id})
+    or die $self->{dbh}->errstr; # clean-up the cursor in Pg
+  if ($self->{autoclean}) {
+    # the dbh was created just for this cursor, so it has no transaction 
+    # state that we care about 
+    $self->{dbh}->rollback;
   }
-  $self->{dbh}->rollback;
-  $self->{dbh}->disconnect;
 }
 
 =back
@@ -146,14 +170,8 @@ Replace all uses of qsearch with this.
 
 =head1 BUGS
 
-MySQL doesn't support cursors in interactive sessions, only in stored 
-procedures, so we implement our own.  This has not been extensively tested.
-
-The cursor will close prematurely if any code issues a rollback/commit. If
-you need protection against this use qsearch or fork and get a new dbh
-handle.
-Normally this issue will represent itself this message.
-ERROR: cursor "cursorXXXXXXX" does not exist.
+Still doesn't really support MySQL, but it pretends it does, by simply
+running the query and returning records one at a time.
 
 =head1 SEE ALSO