RFC : AJAX + DBI = DBIx::LiveGrid

[jZed]

I'd appreciate comments on a new module I've developed. Part of the POD is shown below. Please grab the tarball and let me know what you think.

Update See here for a demo of LiveGrids.

=pod

=head1 NAME

DBIx::LiveGrid -- Ajax LiveGrid tables from any DBI data source

=head1 SYNOPSIS

B<Automatically generate a basic sortable and scrollable
liveGrid table>

This will read an Ajax request; auto-generate a SQL query 
with ORDER BY, LIMIT and WHERE clauses; then send the 
results of the query as an Ajax response composed of rows in
an XHTML table.

 use DBI;
 my $dbh        = DBI->connect( ... any DBI datasource ... );
 my $table_name = 'countries'
 my @fields     = qw/name population human_development_index/;
 my @where      = ('population > 100000000');

 DBIx::LiveGrid->run( $dbh, $table_name, \@fields, \@where );

 __END__

B<Or you can fine-tune the SQL and/or the XHTML yourself,
see the POD>

=head1 DESCRIPTION

This module provides a link between Rico LiveGrids 
(dynamically scrollable database tables within web pages) 
and DBI (Perl's database interface).  With a half dozen 
lines of perl script and a short HTML section, you can 
create AJAX web windows into any DBI accessible database.

DBIx::LiveGrid lets you build web pages containing tables 
which are dynamically sortable and scrollable.  From the 
user's perspective, live grids work like google maps -- as 
you scroll through the grid, the data is dynamically 
refreshed from the database.  Users can also sort on any  
column, simply by clicking on the column's header.

From the programmer's perspective, DBIx::LiveGrid is an
Ajax handler - it supplies XML data to XmlHttpRequests which
dynamically update parts of web pages.  It requires a server

(a short CGI or mod_perl script you write to create and use 
a DBIx::LiveGrid object) and an HTML client (a short HTML 
page which you create based on supplied templates).

On the client-side, DBIx::LiveGrid works in conjunction with
two open source, easily available AJAX libraries (rico.js 
and prototype.js).  Rico developed the LiveGrid portion of 
these libraries from work on very large databases (at Sabre 
Airline Solutions) and have optimized the client end to 
request only the data it needs at any one time, and to 
buffer and cache data as needed.

On the server-side, DBIx::LiveGrid works in conjunction with
L<SQL::Abstract> and especially with L<SQL::Abstract::Limit>
to translate Rico's requests for specific chunks of data
into SQL clauses appropriate for any DBI data source. Or, if
you prefer, you can skip the auto-generation and build your 
own SQL.

With Rico's optimzed AJAX on the frontend, DBIx::LiveGrid 
and SQL::Abstract::Limit in the middle, and DBI at the 
backend, you can serve very large databases and never query 
or send more than small chunks of data at any one time.

=cut

Enjoy!

[Ovid]

This is wonderful and I could quickly see something like this working its way into Bricolage 2.0. However, I've one slight request: would it be possible to decouple the LiveGrid interface from DBI? While I can certainly write a wrapper for my code that will present a DBI interface, I'd much rather see a clean interface that takes advantage of allomorphism. In other words, tell me the interface your module expects and I'd like to be able to offer that interface to this class. We're doing strange things with databases and just offering up the database object to this class makes things less useful.

[jZed]

would it be possible to decouple the LiveGrid interface from DBI?

It already is that way :-). By default the module uses DBI and SQL::Abstract to return an AoA (e.g. from selectall_arrayref) but I make it explicit that you can generate the AoA any way you'd like (with or without DBI), as long as you remember to do things like check the user's request values before sending them somewhere dangerous. As long as you build this AoA yourself and don't call the query_database() method, the module will happily work without DBI. The same is true for its XHTML building and XML cleaning routines - if you don't like the built-ins, just substitute your own. The curtailed synopsis above shows only the run() method, but for finer control you create a liveGrid object with new(), which does not take $dbh as a parameter.

[InfiniteLoop]

jZed, this is cool! I have one question:
 When I scroll down and back up, the grid hits the server for the data. Why can't the data/view be cached ?

[jZed]

AFAIK, it has a buffer of pageSize (70 rows in the example) and it should only hit the server when it can't find what it needs in that buffer. How are you determining that its hitting the server with each scoll?

[InfiniteLoop]

I scrolled all the way down. Waited for the grid to fetch the values and scrolled back to the top.

[zby]

Really great!

I have a question - in the application I plan I need to put links in the data cells. The links should go back to the same table filtered by the value in the cell. I see you display the address of the table with the position and order in the "Listing movies ..." link, and I want to ask what is the easiest way to extract it and use (after adding the filtering parameters) inside the cells.

And one more note - in firefox with big fonts I cannot see the scrollbar.

[jZed]

Well, I'm still thinking about that one since it's something I plan to do too. I may add the ability automatically, but currently, you need to do it manually by replacing the $liveGrid->build_ajax_table() method with your own method. The method should accept an array of arrayrefs AoA (which will have been built automatically from a selectall_arrayref call or else built manually if you prefer). Loop through the array and add anything you want around the data values. Just remember that what you send must be valid XML. Since XHTML is a subset of XML, this can include XHTML, in this case anchor hrefs. See the docs for build_ajax_table() for details.

I'm afraid I can't be much help in terms of things like fonts and scrollbars -- I wrote the Perl end, not the JavaScript end (except for the make_grid() JavaScript) - all of the other JavaScript is from the folks at openRico and prototype. See their site (the same site as the demo above) and help forum for possible scrollbar fixes. One thing I know is that you probably do *not* want cells that are more than a row deep since that can throw off the scroller.

update One thing you can try wrt the scrollbars is to replace the make_grid() Javascript call with an actual hard-coded XHTML table (with cells containing non-breaking spaces) that will serve as the container for data. To see how this is constructed, read the Rico docs and do view source on the Rico demo. The main thing is to have the grid ID (data_grid in the examples) appear in the ids of the table as shown in the Rico demo.

[srdst13]

Would it be possible include a hook or code_ref for a column to make transformations of the data simple? For example, you could imagine wanting to color certain fields red based on a value in the field. This could be easily done outside of your module context, but it might make a nice addition, since it would certainly streamline the process from DBI-->AJAX.

Sean

[zby]

I was thinking about doing it the other way around - submitting the links as data in the AoA of data (and somehow disabling the html escaping of data entities).

[mje]

Thanks for this.

I had a few problems using DBD::ODBC and MS SQL Server. With a table of 1000 rows:

What I found is the data grid displayed the first 70 rows repeatedly as you scrolled. I tracked this down to the SQL being run and then found SQL::Abstract::Limit says $order must be specified when $rows is specified. DBIx::LiveGrid only sets $order if you click on a column heading. I verified this with SQL::Abstract::Limit which produces the following incorrect SQL when $order is not specified:

SELECT * FROM
(
    SELECT TOP 10 * FROM
    (
        SELECT TOP 30  f1, f2 FROM bench_char 
    ) AS foo
    
) AS bar

and the following correct SQL when $order is specified:

SELECT * FROM
(
    SELECT TOP 10 * FROM
    (
        SELECT TOP 30  f1, f2 FROM bench_char ORDER BY f1 ASC
    ) AS foo
    ORDER BY f1 DESC
) AS bar
ORDER BY f1 ASC

I did a rough change to DBIx::LiveGrid to work around this:

sub query_database {
    my($self,$dbh,$table_name,$fields,$where,$order)=@_;
    my $rows     = $self->get('ajax_page_size');
    my $offset   = $self->get('ajax_offset');
    my $sort_dir = $self->get('ajax_sort_dir');
    my $sort_col = $self->get('ajax_sort_col');
       $sort_col = $self->clean_param('sort_col',$sort_col);
    my @porder    = ();
    if ($sort_col) {
        @porder   = ("$sort_col $sort_dir");
        $order = \@porder;
    }
    require SQL::Abstract::Limit;
    my $abstract = SQL::Abstract::Limit->new( limit_dialect => $dbh );
    my( $stmt, @bind ) = $abstract->select( $table_name
                                          , $fields
                                          , $where
                                          , $order
                                          , $rows
                                          , $offset
                                          );

and then changed livegrid.cgi:

my $dbh        = DBI->connect("dbi:ODBC:test", "Martin_Evans", "easyso
+ft", {RaiseError=>1,PrintError=>0});
my $table_name = "bench_char";
my @fields     = qw/f1 f2/;
my @order = qw/f1/;

DBIx::LiveGrid->run( undef, $dbh, $table_name, \@fields, undef, \@orde
+r );

I hope this helps.

BTW, I think there is a bug in IE 6.0.2800.1106 which means the row data is not updated every time when scrolling past the rowset points (70).

[jZed]

Thanks for spotting this and finding the right place to fix. I'll add a default sort-order on the first column but allow programmers to over-ride it.

The Rico site forum has a lot of bug reports and fixes for browser scrolling bugs, I'd imagine they've seen the one you mention, but you might want to post it over there.

[wazoox]

Wow, it's so astounding I want to use hit right now! If only I could upvote you several times for this one :)

[diotalevi]

Please fix your CSS so links are underlined. I didn't notice that you'd linked to things until I just happened to be moving my mouse over the text. Italics aren't the right visual clue that there's a link there.

[jZed]

Which CSS links? Where? Are you talking about on the Rico demo page? That page is done by the folks at openRico, not me. Do you mean in the sample CSS included with the module? That's just a sample, change it however you want.

[Anonymous Monk]

Please fix your tone of voice. I didn't notice that you were actually a nice person until I just happened to be moving my mouse to clicking get going on your home node. Complaints aren't the right verbal tool to add your personal link to.

[poqui]

Where the heck are my brains/ eyes?!?

I SWEAR I read it as
RFC : AJAX + DBI = DBIx::LiveGirl
and thought "How funny, a nerd DBI interface to a Live Girl!"

[matija]

Is there any information on when DBIx::LiveGrid might make it to CPAN? I'd be very interested in using it in some of my stuff...

[Anonymous Monk]

I tried the example code but it is not working for me. Is the following statement correct? It looks to me that the run sub will eat up the 1st parameter, that is, $dbh, and will only pass the rest to the query_database sub. Then the query_database sub will try to use $table_name as a db handle. Not sure if my understanding after reading the module code is correct. DBIx::LiveGrid->run( $dbh, $table_name, \@fields, \@where );

[jZed]

Perhaps there is something else going on, try running it from the command line rather than calling it from the browser. I'll be changing the interface to be all named parameters rather than positional parameters but here's what is happening currently:

sub run {
    my( $self, $cfg, @query_params ) = @_;
    my $liveGrid   = $self->new( %$cfg );
    my $db_table   = $liveGrid->query_database(     
        @query_params
    );
    # ...
}

So it expects to be called like this:

   DBIx::LiveGrid->run(
      \%cfg, $dbh, $table, \@fields, \@where
   );

The first parameter "$self" is "DBX::LiveGrid".

[Anonymous Monk]

Yes, that is what I meant. We need to add \%cfg to the following statement in the documentation: DBIx::LiveGrid->run( $dbh, $table, \@fields, \@where );