Long running DBI queries (gtk2 , threads)

[ofey aikon <ofey aikon gmail com>]

I want to have my perl-gtk app query a database using DBI and display
the query results.  Some of the queries can take minutes to run and a
naive implementation would mean all GUI interaction was blocked until
the $dbh->execute returned.

This seems to be a fairly common problem.
http://mail.gnome.org/archives/gtk-perl-list/2004-November/msg00055.html
http://mail.gnome.org/archives/gtk-perl-list/2005-August/msg00140.html

In my case, I've cooked up a solution using ithreads. It worked fine 
for me (on win32). So I decided to abstract away all the thread
related code into a separate package so that the main code will remain
fairly clean (no thread related code in the main)

Attached is the new (beta) package. MyThread.pm. An example is also attached.

# --------------------------------- #
USAGE:
# --------------------------------- #

The usage is fairly straightforward.

Use the C<register_sql> to register a sql to be executed and a
call_back to be called post-execution. You get to refer to this thing
by a name so that you can call it later.

$mythread->register_sql('<some name>', \&call_sql, \&call_back);

Later on, this registered sql can be triggered by calling 
$mythread->execute('<some name>',[<list of parameters>]);

$dbh and $sth cannot be 'shared' between threads. So I have to shuttle
parameters and resultset between threads to get this thing going. Of
course, I am using C<Storable qw(freeze thaw)> to exchange data
structures.

        sub call_sql {
                my ($dbh, $sqlparams) = @_;
                my $params = thaw $sqlparams; # Don't forget to thaw
                my $sth = $dbh->prepare(qq{
                        # my complicated long query that takes a long time to complete
                        select a.field2, b.field2 from table1 a, table2 b where a.field1 = b.field1
                        and a.field2 like ?
                });
                $sth->execute('%'.$params->[0].'%');
                
                # We HAVE TO load all required data into an array and send it back
                # Main thread does not have access to the $dbh and $sth...
                # Atleast I haven't figured out a way to 'share' these handles
                my @result_array;
                while (my @ary = $sth->fetchrow_array()) {
                        push @result_array, \ ary;
                }
                return \ result_array;
        }

The C<@result_array> that was created cannot be meddled with from the
main thread. Instead, you'll get a frozen version of that through the
call_back function.

        sub call_back {
                my ($result_array) = shift;
                @{$slist->{data}} = ();
                foreach my $x (thaw $result_array) { # Don't forget to thaw once more
                        push @{$slist->{data}}, @$x;
                }
        }

# --------------------------------- #

This stuff works for me in my toy app on win32. If any one sees
pitfalls with this approach please do let me know. Like I said before,
I am still a newbie on threads(gtk2-perl). This is a problem which I
'have to' solve in my app and the approach seems good enough according
to me.

I can't use pipes because they don't behave quite well on win32. Also,
spawning off a new process to do dbi query may be ugly.

If no one sees issues with this approach, I'll load this into cpan
under the name

Gtk2::Ex::threads::DBI

Regards,

_Ofey.

Attachment: MyThreadDBI.pm
Description: Binary data

Attachment: mythread-usage.pl
Description: Binary data