http://www.perlmonks.org?node_id=492067

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!

Replies are listed 'Best First'.
Re: RFC : AJAX + DBI = DBIx::LiveGrid
by Ovid (Cardinal) on Sep 15, 2005 at 01:25 UTC

    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.

    Cheers,
    Ovid

    New address of my CGI Course.

      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.
Re: RFC : AJAX + DBI = DBIx::LiveGrid
by InfiniteLoop (Hermit) on Sep 15, 2005 at 03:08 UTC
    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 ?
      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?
        I scrolled all the way down. Waited for the grid to fetch the values and scrolled back to the top.
Re: RFC : AJAX + DBI = DBIx::LiveGrid
by zby (Vicar) on Sep 15, 2005 at 10:55 UTC
    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.

      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.

        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

        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).
Re: RFC : AJAX + DBI = DBIx::LiveGrid
by mje (Curate) on Sep 16, 2005 at 13:43 UTC

    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).

      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.

Re: RFC : AJAX + DBI = DBIx::LiveGrid
by wazoox (Prior) on Sep 15, 2005 at 11:15 UTC
    Wow, it's so astounding I want to use hit right now! If only I could upvote you several times for this one :)
Re: RFC : AJAX + DBI = DBIx::LiveGrid
by diotalevi (Canon) on Sep 15, 2005 at 15:19 UTC
    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.
      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.
      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.
Re: RFC : AJAX + DBI = DBIx::LiveGrid
by poqui (Deacon) on Sep 19, 2005 at 20:55 UTC
    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!"
Re: RFC : AJAX + DBI = DBIx::LiveGrid
by matija (Priest) on Mar 22, 2006 at 16:23 UTC
    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...
Re: RFC : AJAX + DBI = DBIx::LiveGrid
by Anonymous Monk on Sep 21, 2005 at 19:55 UTC
    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 );
      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".
        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 );