The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
Gtk2-Ex-DBITableFilter-0.03
River stage zero No dependents
++
/ Gtk2::Ex::DBITableFilter

NAME

Gtk2::Ex::DBITableFilter - A high level widget to present large amounts of data fetched using DBI. Also provides data filtering capabilities.

DESCRIPTION

May be you are dealing with tons of relational data, safely tucked away in an RDBMS, accessible using DBI, and may be you would like to view them in a Gtk2 widget. The ideal widget (in most cases) is the Gtk2::TreeView or its younger cousin, the Gtk2::Ex::Simple::List.

But then you start worrying about questions like,

- How do I prevent the UI from hanging while reading all the data ?

- How do I present all the data in the TreeView without causing it to explode ?

Gtk2::Ex::DBITableFilter comes to rescue !!

Gtk2::Ex::DBITableFilter is a higher level widget built using Gtk2::Ex::Simple::List to achieve the following.

1. Ensure that arbitrary SQLs can be executed to fetch the data.

2. Ensure that UI does not hang while SQL is being executed.

3. Provide some kind of paging functionality. Do not display all fetched data in one shot, instead spread it into multiple pages with buttons to navigate between pages.

4. Provide some kind of data filtering capability. (Spreadsheets for example allow the user to filter the data by column using a dropdown box).

SYNOPSIS

        use Gtk2::Ex::DBITableFilter;
        
        # Either define a new thread or using an existing thread
        my $mythread = Gtk2::Ex::Threads::DBI->new( {
                dsn     => "dbi:SQLite2:data.dbl",
                user    => undef,
                passwd  => undef,
                attr    => { RaiseError => 1, AutoCommit => 0 }
        });
        
        # Define a list
        my $slist = Gtk2::Ex::Simple::List->new (
                 undef          => 'bool',
                'ID'            => 'text',
                'Name'          => 'text',
                'Description'   => 'text',
                'Quantity'      => 'int',
        );

        my $pagedlist = Gtk2::Ex::DBITableFilter->new($slist);
        $pagedlist->add_choice(2, 
                [[0,'cat'], [1,'rat'], [1,'dog'], [0,'elephant'], [0,'lion'], [0,'tiger']]
        );
        $pagedlist->set_simple_sql(\&fetch_easy);
        $pagedlist->set_thread($mythread);

        sub fetch_easy {
                my ($params) = @_;
                my $names       = $params->{params}->{columnfilter}->{2};
                my $descpattern = $params->{params}->{columnfilter}->{3} || '';
                my $valuelimit  = $params->{params}->{columnfilter}->{4}->[0];
                my $names_str = combine(@$names);
                return (qq{
                        select 
                                marked, id, name, description, quantity
                        from 
                                animals
                        where 
                                name in $names_str and 
                                description like '%$descpattern%'
                });
        }

SCREENSHOTS

http://ofey.blogspot.com/2005/09/gtk2exdbitablefilter.html

METHODS

new($slist);

Constructor accepts a Gtk2::Ex::Simple::List as the argument.

        my $pagedlist = Gtk2::Ex::DBITableFilter->new($slist);

set_simple_sql(\&call_back);

This method can be used to simplify things to a great extent. If you are using this method, then you don't need to explicitly define count_using(\&call_back) and fetch_using(\&call_back).

Instead you just need to define set_simple_sql(\&call_back).

The callback in this case will return the SQL query to be executed. Note that you don't need to specify limit information in the SQL. Also, the count is obtained by modifying this SQL internally and executing it against the RDBMS.

Internally, this method will use a subquery select count(*) from ($sql) temp. Therefore, this method will work only if your underlying RDBMS supports subselects.

Fortunately, most of the RDBMS do (SQLite, MySQL, Oracle, DB2).

        sub fetch_easy {
                my ($params) = @_;
                my $names       = $params->{params}->{columnfilter}->{2};
                my $descpattern = $params->{params}->{columnfilter}->{3} || '';
                my $valuelimit  = $params->{params}->{columnfilter}->{4}->[0];
                my $names_str = combine(@$names);
                return (qq{
                        select 
                                selected, id, name, description, quantity
                        from 
                                animals
                        where 
                                name in $names_str and 
                                description like '%$descpattern%'
                });
        }

Look at examples/filtered-table-sqlite.pl for an example usage of this function.

count_using(\&call_back);

Define a callback function to count the records. This call back function will be called with certain parameters. Look at the example ...

        sub count_records {
                my ($dbh, $params) = @_;
                my $names       = $params->{params}->{columnfilter}->{2};
                my $descpattern = $params->{params}->{columnfilter}->{3} || '';
                my $valuelimit  = $params->{params}->{columnfilter}->{4}->[0];
                my $names_str = combine(@$names);
                my $sth = $dbh->prepare(qq{
                        select 
                                count(*)
                        from 
                                animals
                        where
                                name in $names_str and 
                                description like '%$descpattern%' and
                                quantity $valuelimit
                });
                $sth->execute();
                my @result_array;
                while (my @ary = $sth->fetchrow_array()) {
                        push @result_array, $ary[0];
                }
                return \@result_array;  
        }

This function will give you full flexibility in defining and executing your SQL and meddling with the resultset if you like.

If your RDBMS does not support subselects, then you have to use this route.

fetch_using(\&call_back);

Define a callback function to fetch the records. This call back function will be called with certain parameters. Look at the example ...

        sub fetch_records {
                my ($dbh, $params) = @_;
                my $names       = $params->{params}->{columnfilter}->{2};
                my $descpattern = $params->{params}->{columnfilter}->{3} || '';
                my $valuelimit  = $params->{params}->{columnfilter}->{4}->[0];
                my $names_str = combine(@$names);
                my $sth = $dbh->prepare(qq{
                        select 
                                selected, id, name, description, quantity
                        from 
                                table1
                        where 
                                name in $names_str and 
                                description like '%$descpattern%' and
                                quantity $valuelimit
                        limit  
                                $params->{limit}->{start}, $params->{limit}->{step}
                });
                $sth->execute();
                my @result_array;
                while (my @ary = $sth->fetchrow_array()) {
                        push @result_array, \@ary;
                }
                return \@result_array;  
        }

This function will give you full flexibility in defining and executing your SQL and meddling with the resultset if you like.

set_thread($mythread);

The $mythread object here is an instance of Gtk2::Ex::Threads::DBI

        $pagedlist->set_thread($mythread);

SEE ALSO

Gtk2::Ex::Threads::DBI Gtk2::Ex::ComboBox Gtk2::Ex::Simple::List

AUTHOR

Ofey Aikon, <ofey.aikon at cpan dot org>

ACKNOWLEDGEMENTS

To the wonderful gtk-perl-list.

COPYRIGHT & LICENSE

Copyright 2005 Ofey Aikon, All Rights Reserved.

This library is free software; you can redistribute it and/or modify it under the terms of the GNU Library General Public License as published by the Free Software Foundation; either version 2.1 of the License, or (at your option) any later version.

This library is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Library General Public License for more details.

You should have received a copy of the GNU Library General Public License along with this library; if not, write to the Free Software Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307 USA.