RFC : AJAX + DBI = DBIx::LiveGrid

I've created a module that marries DBI with AJAX to support LiveGrids -
dynamically updateable portions of web pages that work like google
maps. As you scroll through the grid, the contents are buffered on the
client-side, and limited on the server-side and only small sections are
refreshed at any one time. This means that very large databases
(anything accessible via DBI) can be served without either querying or
sending more than small chunks for each request.

You can create a LiveGrid server as a CGI or mod_perl script with just a
few lines of perl code and create a LiveGrid client with just a few
lines of JavaScript (included with the distro).

DBIx::LiveGrid is based on the open source Rico and Prototype AJAX
libraries and optionally makes use of SQL::Abstract and
SQL::Abstract::Limit to construct queries, limits, and offsets.

For a demo of a LiveGrid, see http://openrico.org/rico/livegrid.page.

For a bit more about the modules, see:
http://www.perlmonks.org/?node_id=492067.

To grab the tarball:
http://www.vpservices.com/jeff/programs/DBIx-LiveGrid-0.01.t ar.gz

I'd value all comments and criticisms, namespace suggestions, etc.

--
Jeff

Re: RFC : AJAX + DBI = DBIx::LiveGrid

Very nice Jeff! I can vision many uses for this module.


On Wed, 2005-09-14 at 16:09 -0700, Jeff Zucker wrote:
> I've created a module that marries DBI with AJAX to support LiveGrids -
> dynamically updateable portions of web pages that work like google
> maps. As you scroll through the grid, the contents are buffered on the
> client-side, and limited on the server-side and only small sections are
> refreshed at any one time. This means that very large databases
> (anything accessible via DBI) can be served without either querying or
> sending more than small chunks for each request.
>
> You can create a LiveGrid server as a CGI or mod_perl script with just a
> few lines of perl code and create a LiveGrid client with just a few
> lines of JavaScript (included with the distro).
>
> DBIx::LiveGrid is based on the open source Rico and Prototype AJAX
> libraries and optionally makes use of SQL::Abstract and
> SQL::Abstract::Limit to construct queries, limits, and offsets.
>
> For a demo of a LiveGrid, see http://openrico.org/rico/livegrid.page.
>
> For a bit more about the modules, see:
> http://www.perlmonks.org/?node_id=492067.
>
> To grab the tarball:
> http://www.vpservices.com/jeff/programs/DBIx-LiveGrid-0.01.t ar.gz
>
> I'd value all comments and criticisms, namespace suggestions, etc.
>
--
Scott T. Hildreth

RE: RFC : AJAX + DBI = DBIx::LiveGrid

Jeff,

Nice one.

However, it does not quite work with my DBD and database. What I see is the
first 70 rows repeated as I scroll through the table. The SQL being executed
does not seem correct:

select * from
(
select top 70 * from
(
select top 70 col,col from table
) as foo
) as bar

then when you go past the 70th row I get variations like:

select * from
(
select top 70 * from
(
select top 350 col,col from table
) as foo
) as bar

which obviously returns the same first 70 rows.

I'm investigating. I think the issue may be in SQL-Abstract-Limit. Do you have
any ideas?

Martin
--
Martin J. Evans
Easysoft Ltd, UK
Development


On 14-Sep-2005 Jeff Zucker wrote:
> I've created a module that marries DBI with AJAX to support LiveGrids -
> dynamically updateable portions of web pages that work like google
> maps. As you scroll through the grid, the contents are buffered on the
> client-side, and limited on the server-side and only small sections are
> refreshed at any one time. This means that very large databases
> (anything accessible via DBI) can be served without either querying or
> sending more than small chunks for each request.
>
> You can create a LiveGrid server as a CGI or mod_perl script with just a
> few lines of perl code and create a LiveGrid client with just a few
> lines of JavaScript (included with the distro).
>
> DBIx::LiveGrid is based on the open source Rico and Prototype AJAX
> libraries and optionally makes use of SQL::Abstract and
> SQL::Abstract::Limit to construct queries, limits, and offsets.
>
> For a demo of a LiveGrid, see http://openrico.org/rico/livegrid.page.
>
> For a bit more about the modules, see:
> http://www.perlmonks.org/?node_id=492067.
>
> To grab the tarball:
> http://www.vpservices.com/jeff/programs/DBIx-LiveGrid-0.01.t ar.gz
>
> I'd value all comments and criticisms, namespace suggestions, etc.
>
> --
> Jeff

RE: RFC : AJAX + DBI = DBIx::LiveGrid

Jeff,

The problem I'm experiencing may be to do with the following from
SQL::Abstract::Limit:

=====
select( $table, \@fields, $where, [ \@order, [ $rows, [ $offset ], [ $dialect ]
] ] )

Same as SQL::Abstract::select, but accepts additional $rows, $offset and
$dialect parameters.

The $order parameter is required if $rows is specified.
=====

and LiveGrid containing:

=====
# I think order needs to be settable in query_database
my @order = ();

my( $stmt, @bind ) = $abstract->select( $table_name
, $fields
, $where
, \@order
, $rows
, $offset
);
=====

i.e. no order specified. I tried in SQL::Abstract::Limit and it generates
SQL that does not return the correct rows (as quoted in my previous email)
unless "order" is specified.

Martin
--
Martin J. Evans
Easysoft Ltd, UK
Development

On 16-Sep-2005 Martin J. Evans wrote:
> Jeff,
>
> Nice one.
>
> However, it does not quite work with my DBD and database. What I see is the
> first 70 rows repeated as I scroll through the table. The SQL being executed
> does not seem correct:
>
> select * from
> (
> select top 70 * from
> (
> select top 70 col,col from table
> ) as foo
> ) as bar
>
> then when you go past the 70th row I get variations like:
>
> select * from
> (
> select top 70 * from
> (
> select top 350 col,col from table
> ) as foo
> ) as bar
>
> which obviously returns the same first 70 rows.
>
> I'm investigating. I think the issue may be in SQL-Abstract-Limit. Do you
> have
> any ideas?
>
> Martin
> --
> Martin J. Evans
> Easysoft Ltd, UK
> Development
>
>
> On 14-Sep-2005 Jeff Zucker wrote:
>> I've created a module that marries DBI with AJAX to support LiveGrids -
>> dynamically updateable portions of web pages that work like google
>> maps. As you scroll through the grid, the contents are buffered on the
>> client-side, and limited on the server-side and only small sections are
>> refreshed at any one time. This means that very large databases
>> (anything accessible via DBI) can be served without either querying or
>> sending more than small chunks for each request.
>>
>> You can create a LiveGrid server as a CGI or mod_perl script with just a
>> few lines of perl code and create a LiveGrid client with just a few
>> lines of JavaScript (included with the distro).
>>
>> DBIx::LiveGrid is based on the open source Rico and Prototype AJAX
>> libraries and optionally makes use of SQL::Abstract and
>> SQL::Abstract::Limit to construct queries, limits, and offsets.
>>
>> For a demo of a LiveGrid, see http://openrico.org/rico/livegrid.page.
>>
>> For a bit more about the modules, see:
>> http://www.perlmonks.org/?node_id=492067.
>>
>> To grab the tarball:
>> http://www.vpservices.com/jeff/programs/DBIx-LiveGrid-0.01.t ar.gz
>>
>> I'd value all comments and criticisms, namespace suggestions, etc.
>>
>> --
>> Jeff

Re: RFC : AJAX + DBI = DBIx::LiveGrid

Martin J. Evans wrote:

>Jeff,
>
>
> The $order parameter is required if $rows is specified.
>
>
Ah, yes, of course, thanks for spotting that. To confirm, does it scroll
correctly when you do specify an order (i.e. by clicking on a column
heading to toggle ordering by that column)? If it does then I guess I
will need to have a default order clause (which makes sense). I guess
I'll default to ordering by the first column unless the script specifies
otherwise.

--
Jeff

RE: [dbi] Re: RFC : AJAX + DBI = DBIx::LiveGrid

On 16-Sep-2005 Jeff Zucker wrote:
> Martin J. Evans wrote:
>
>>Jeff,
>>
>>
>> The $order parameter is required if $rows is specified.
>>
>>
> Ah, yes, of course, thanks for spotting that. To confirm, does it scroll
> correctly when you do specify an order (i.e. by clicking on a column
> heading to toggle ordering by that column)?

Yes.

> If it does then I guess I
> will need to have a default order clause (which makes sense). I guess
> I'll default to ordering by the first column unless the script specifies
> otherwise.

Pretty much what I did.

>
> --
> Jeff

I added comments to your permonks node which detailed what I did.
http://www.perlmonks.org/?node_id=492067

Martin
--
Martin J. Evans
Easysoft Ltd, UK
Development