livegrid.pm

web表格控件

#######################
package DBIx::LiveGrid;
#######################
use CGI;
use vars qw/$vars $VERSION/;

$VERSION = '0.01';

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

sub new {
    my($class,%cfg) = @_;
    my $self = bless \(do{my $anon}), $class;
    my $cgi  = delete $cfg{cgi};
    $cgi ||= CGI->new;
    $vars      = \%cfg;
    for my $param( qw/id offset page_size sort_col sort_dir/ ) {
        my $val =  $self->clean_param( $param, $cgi->param($param) );
        $self->set("ajax_$param", $val );
    }
    return $self;
}

sub clean_param {
    my($self,$key,$val,$fields)=@_;

    # OFFSET MUST BE ZERO OR GREATER ... DEFAULT = 0
    if ($key eq 'offset') {
        $val = 0 unless $val and $val =~ /^\d+$/ and $val > 0;
    }
    # PAGE_SIZE MUST BE A POSITIVE INTEGER LESS THAN 200 ... DEFAULT = 70
    if ($key eq 'page_size') {
        $val = 70 unless $val and $val =~ /^\d+$/ and $val > 0 and $val < 200;
    }
    # SORT_DIR MUST BE 'ASC' OR 'DESC' ... DEFAULT = ASC
    if ($key eq 'sort_dir') {
        $val = 'ASC' unless $val and $val =~ /^(ASC|DESC)$/i;
    }
    # SORT_COL MUST BE A SPECIFIED COLUMN
    if ($key eq 'sort_col' and $fields) {
        my %is_column = map {lc $_=>1} @$fields;
        $val = '' unless $val and $is_column{lc $val};
    }
    return $val;
}

sub query_database {
    my($self,$dbh,$table_name,$fields,$where)=@_;
    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 @order    = ();
    if ($sort_col) {
        @order   = ("$sort_col $sort_dir");
    }
    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
                                          );
    return $dbh->selectall_arrayref( $stmt, {}, @bind );
}

sub build_ajax_table {
    my($self,$table_aoa) = @_;
    my $table_data = '';
    for my $row(@$table_aoa) {
        $table_data .= '<tr>';
        $table_data .= '<td>' . $_ . '</td>' for map {
            defined $_ ? $self->clean_xml($_) : ''
        } @$row;
        $table_data .= "</tr>\n";
    }
    return $table_data;
}

sub clean_xml {
    my($self,$str)=@_;
    $str =~ s/&/&amp;/g;
    $str =~ s/</&lt;/g;
    $str =~ s/>/&gt;/g;
    $str =~ s/"/&quote;/g;
    return $str;
}
sub send_ajax_response {
    my($self,$table_data)=@_;
    my $ajax_id      = ( $self->get('ajax_id') || 'data_grid' ) . '_updater';
    my $xml_encoding = $self->get('xml_encoding') || 'ISO-8859-1';
    print CGI::header('text/xml');
    printf <<'EOT', $xml_encoding,$ajax_id,$table_data;
<?xml version="1.0" encoding="%s" standalone="yes"?>
    <ajax-response>
        <response type="object" id="%s">
            <rows update_ui="true">
                %s
            </rows>
        </response>
    </ajax-response>
EOT
}
sub set {
    my($self,$key,$val) = @_;
    $vars->{$self}->{$key}=$val;
}
sub get {
    my($self,$key) = @_;
    return $vars->{$self}->{$key};
}
sub DESTROY {
    my $self = shift;
    $dbh->disconnect if $dbh;
    delete $vars->{$self};
}
1;
__END__

=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 );


B<Or fine-tune the SQL and/or the XHTML yourself>

This does the same, as the above, but breaks it into steps.  You can
substitute your own methods for the SQL building stage and/or the
HTML building stage.

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

 # create a liveGrid object
 #
 my $liveGrid   = DBIx::LiveGrid->new;

 # let LiveGrid and SQL::Abstract construct your query
 #
 # or substitute your own routine that builds a results table
 # (an AoA such as would be returned by $dbh->selectall_arrayref)
 #
 my $db_table   = $liveGrid->query_database( $dbh
                                           , $table_name
                                           , \@fields
                                           , \%where
                                           );

 # let LiveGrid build your HTML table
 #
 # or substiture your own routine that builds an XHTML table
 # (must be valid XHTML)
 #
 my $ajax_table = $liveGrid->build_ajax_table( $db_table );

 # send the resulting XHTML table as an Ajax response
 #
 $liveGrid->send_ajax_response( $ajax_table );


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

=head1 INSTALLATION

See the attached README file for installation instructions.  The installation requires manually downloading and copying some of the needed AJAX libraries so please do read the README before proceeding.

=head1 CREATING A LIVE GRID