4 use vars qw($DEBUG $buffer);
6 use FS::UID qw(myconnect driver_name);
7 use Scalar::Util qw(refaddr);
11 # this might become a parameter at some point, but right now, you can
12 # "local $FS::Cursor::buffer = X;"
17 FS::Cursor - Iterator for querying large data sets
23 my $search = FS::Cursor->new('table', { field => 'value' ... });
24 while ( my $row = $search->fetch ) {
34 Constructs a cursored search. Accepts all the same arguments as qsearch,
35 and returns an FS::Cursor object to fetch the rows one at a time.
41 my $q = FS::Record::_query(@_); # builds the statement and parameter list
42 my $dbh = myconnect();
46 class => 'FS::' . ($q->{table} || 'Record'),
49 position => 0, # for mysql
53 # the class of record object to return
54 $self->{class} = "FS::".($q->{table} || 'Record');
56 # save for later, so forked children will not destroy me when they exit
59 $self->{id} = sprintf('cursor%08x', refaddr($self));
62 if ( driver_name() eq 'Pg' ) {
63 $statement = "DECLARE ".$self->{id}." CURSOR FOR ".$q->{statement};
64 } elsif ( driver_name() eq 'mysql' ) {
65 # build a cursor from scratch
66 $statement = "CREATE TEMPORARY TABLE $self->{id}
67 (rownum INT AUTO_INCREMENT, PRIMARY KEY (rownum))
71 my $sth = $dbh->prepare($statement)
74 foreach my $value ( @{ $q->{value} } ) {
75 my $bind_type = shift @{ $q->{bind_type} };
76 $sth->bind_param($bind++, $value, $bind_type );
79 $sth->execute or die $sth->errstr;
81 if ( driver_name() eq 'Pg' ) {
82 $self->{fetch} = $dbh->prepare("FETCH FORWARD $buffer FROM ".$self->{id});
83 } elsif ( driver_name() eq 'mysql' ) {
84 # make sure we're not holding any locks on the tables mentioned
86 $dbh->commit if driver_name() eq 'mysql';
87 $self->{fetch} = $dbh->prepare("SELECT * FROM $self->{id} ORDER BY rownum LIMIT ?, $buffer");
101 Fetch the next row from the search results.
106 # might be a little more efficient to do a FETCH NEXT 1000 or something
107 # and buffer them locally, but the semantics are simpler this way
109 if (@{ $self->{buffer} } == 0) {
110 my $rows = $self->refill;
111 return undef if !$rows;
113 $self->{class}->new(shift @{ $self->{buffer} });
118 my $sth = $self->{fetch};
119 $sth->bind_param(1, $self->{position}) if driver_name() eq 'mysql';
120 $sth->execute or die $sth->errstr;
121 my $result = $self->{fetch}->fetchall_arrayref( {} );
122 $self->{buffer} = $result;
123 $self->{position} += $sth->rows;
129 return unless $self->{pid} eq $$;
130 if ( driver_name() eq 'Pg' ) {
131 $self->{dbh}->do('CLOSE '. $self->{id})
132 or die $self->{dbh}->errstr; # clean-up the cursor in Pg
133 } elsif ( driver_name() eq 'mysql' ) {
134 # nothing; the temporary table will evaporate when the
137 $self->{dbh}->rollback;
138 $self->{dbh}->disconnect;
145 Replace all uses of qsearch with this.
149 MySQL doesn't support cursors in interactive sessions, only in stored
150 procedures, so we implement our own. This has not been extensively tested.
152 The cursor will close prematurely if any code issues a rollback/commit. If
153 you need protection against this use qsearch or fork and get a new dbh
155 Normally this issue will represent itself this message.
156 ERROR: cursor "cursorXXXXXXX" does not exist.