Thursday, June 21, 2012

Implementing the jQuery DataTables plugin in a 'classic' Domino Web application.

Well, it's been a really long time since I posted anything here. I started a new job last year, which has involved working on traditional Notes and Domino web databases in a large company with a very old infrastructure - Note 6.5 and 7 clients, Domino 6.5 through to 8.0.1. So no recent xPages for me.

But I have been doing a lot of work with jQuery, because the company's default browser is *still* IE6, and jQuery eases the pain.

We recently started implementing the jQuery DataTables plugin for handling things like search results, because you can include features for in memory paging, filtering and sorting with very little effort. It's quick, cross browser compatible, and can use data already on the page or fetch it via AJAX.

The internal company web application I am working on is fairly typical. It is an older Domino application which has grown over time, been worked on by many developers and contains a huge variety of code styles and approaches. There are a number of databases, most of which have their own search page. These normally have a group of <select> elements used to filter the data. The user makes their choices then clicks 'Search' whereapon the form is reloaded with the search parameters appended to the URL. The form's WebQueryOpen agent would use the URL parameters to build and execute the document search, then render the data as table rows inside a rich text field, which was wrapped in pass-thru HTML. JavaScript was then used to parse the HTML and re-rendered it in a paged table.

There were problems with this approach - firstly it was slow. The round trip to the sever for a full page reload, the time it took to run the WQO agent and write the HTML into the rich text field, and the time to parse and re-render the table were noticeable. Secondly, it only worked in IE due to the way the script was loaded, and thirdly, more than about 500 results would cause the JavaScript to time out.

As jQuery had already been added to most of the databases, we decided to replace the existing system with DataTables whenever we get the chance. Firstly, the old JavaScript library needs to be removed and replaced by the DataTables plugin (download the plugin and load the .js file wherever you have your jQuery source and reference it the same way). Then we need to implement DataTables using the current page HTML. To do so you just initialize it like this:

$(document).ready( function() {
   $('#tableId').dataTable();
});

Simple, isn't it. Of course there was a bit of work to do with styling, but the plugin downloads with a sample stylesheet that gets you well under way. The immediate impact was a significant speed improvement and a much larger result set could be processed before timing out (about a 5 fold increase).

The hardest bit was cleaning up our existing HTML. The plugin is a little unforgiving. The table you target must be properly formed with both <thead>and <tbody> elements, the <thead> must contain at least one row with <th> elements and the <tbody> must contain at least one row of<td> elements. The number of th and td elements in the rows must match.

For some of the search pages, that is as far as we went. However as time and budget permit, we are also replacing the DOM source (generated by page reload + WQO agent) by an Ajax data souce. The same lotusscript agents can be re-used by simply modifying them to print JSON instead of appending HTML to a field. The existing parts of the agents which use the URL parameters to generate the search and build the document collection can be retained (including all the tested business logic). The JSON object you need to generate is defined in the DataTables API. and is basically an array of arrays in an object called aaData. the api documentation has examples. Then we remove the rich text field, create an empty table with the required number of columns using pass-through HTML and hide it with styling, then put the following in the Search button click handler:


    var sUrl = ;// create your URL along the lines of 
       //http://my server.mydomain.com/path/db.nsf/myAgant?OpenAgent&cat=xyz&p2=abc
    $('#tableId').show();

    $('#tableId').dataTable( {
        "bProcessing": true,
        "sAjaxSource": sUrl
      });

As the JSON your agent creates can contain HTML, so one of your columns can be easily configured as a link. Depending on your data you should also consider testing for and escaping invalid characters before you print your agent output. Now the search results are loaded without a page refresh, and the overall result is a faster, snappier, more modern search results table.

There are a larger number of properties you can set when initializing the table, including turning on or of the paging and filtering, controlling the sorting, defining callback functions to be called at various stages of the table rendering, adding classes to style the table, hiding columns and more.

Once you get your head around the basics of setting it up it is easy to use and very flexible. It includes support for the jQueryUI themes and is a great way to add a little 'shiny' touch to those apps not yet ready for xPages.

8 comments:

Alan Hurt said...

Good read. I've used jqGrid in classic Domino, but not Datatables. I would say its a viable argument that these jQuery grid plugins actually offer more functionality than the basic Dojo grid does in XPages. (XPages does not use the Dojo enhanced grid)

nick wall said...

The project I'm on, we are using the Dojo framework, and we use dojo grids. I am interested to hear how you deal with large data sets. With dojo grids (we also have double click, right click and a bunch of other stuff going on), once we get to the 200 row mark, rendering client side becomes too high, we do use the "lazy" rendering (there's a whole series of issues with dojo grid rendering perf...sigh). We are trying to get rid of "traditional" concept of views and large search result sets, as we think scrolling though a view\ search with hundreds of docs doesn't make sense. If you are looking for something and you get back more than 200 results, then our point is, your search was not precise enough.

In hindsight, if we could start project again, I think we may have chosen jQuery for this project.

Michelle said...

Nick - I haven't done ant detailed testing, but with IE6 using jQuery, the problems seem to occur around the 1500-2000 records mark. There is also a deferred render setting which boosts this significantly.

I have also found that how you handle any events (eg on click handlers) is significant. I generally attach a single handler to the entire table and process the current target, rather than registering handlers at the td or even lower.

As for why users would want this number of results, I agree - if you are looking for a specific document, this number of results is pointless. If you want the data for reporting purposes, a dedicated export button can open the data as cvs, print to a plain table, email it to the user or some other, more useable solution.

Alan Hurt said...

Nick - I have also used the Dojo framework (enhanced grid) and actually can load large data set, 1000+, reasonably. Unlike Michelle, don't have to worry about IE6 specifically, but can attest to decent performance with more than 200 records using a dojo grid. That being said, I do agree about the search results paradigm.

nick wall said...

@Alan. I'm sure you're right, and as Michelle indicated, maybe we aren't handling handlers most efficiently. Great feedback...thanks.

Ashwin said...

Hi Michelle,

Can we include response documents as well with this plugin?

Regards,
Ashwin

Michelle said...

Hi Ashwin

This plug-in does not really handle response documents in the way that a Notes view does - it has no concept of expand / collapse or parent / child etc. It renders a standard table only.

But if your response documents make sense in a flat list, then your agent can return them & have them listed along with the parents.

The plug-in also provides for a callback function to post-process each row after rendering, so you could add some additional HTML manipulation in a custom function if you wanted to, to change the appearance of the child rows or whatever made sense in the relevant context.

Aron Reynolds said...

Michelle, this was just the article i needed to get started with integrating a better solution to the old clunky Domino web pages we are all accustom to working on for many many years. I as well as of 6 months ago had no xPages experience, JSON objects, Dojo grids, Datatables, etc so after several months learning some xPages, REST services, xAgents, DataTables, a touch of JAVA and of course this great read to get me started here is what I actually did..

At first (months ago) I implemented DataTables producing a JSON object using Lotusscript with a great LotusScript class i found from a gentleman by the name of Troy Reimer. This worked good except could not handle any data sets larger than a couple thousand docs if that and with any server latency issues you may have forget it cut that number in half. I think it took me over 30 seconds to load 4000 docs which is clearly unacceptable so what i did was just load the first 1000 set the datatables per page attribute to 100 docs and allow my users to next page..previous page etc. The filtering provided by Datatables is awesome i love the real-time search capabilities which prevented users having to next/previous page.. Worked wonderful! Sounds great, right! Well, i have 15,000 docs in a view so of course i needed to add a search functionality using FTSearch, etc build the JSON object and serve it back into DataTables etc. UGH i thought. There has got to be a better solution for this.

So after several more months of learning and researching here is what i am doing today.

What i do now is on web open I call an xPage. Ye, an xPage. Create a simple javascript var with a lookup to a profiledocument and in the onLoad event and in your Datatables $(document).ready(function() call that var something like this "sAjaxSource": CallGetJSON looks like this when i pull from my profile doc.."https://domdevostx01.ten-net.net/SASE/SASENomination.nsf/GetJSON.xsp".. GetJSON is the name of the xPage essentially an xAgent if you read up on that and in the afterRenderResponse event I call a Java Class that i modified(creation compliments to Jeff Byrd - Titled "Roll your Own Domino REST Service") that actually produces the JSON object and sends it back to me to be consumed.

Oddly enough I STILL load my data into Datatables and not xPages. I am was not happy about the out of the box xPages view and Dojo grid and our organization will not fund the Dojo grid EXTJS - 600 dollars a license so I stick with Datatables which is awesome. I now can load 20,000+ documents in 5 seconds with all the nice filtering, paging, CSS modifications, etc that Datatables provides.

Not to mention no more LotusScript to write out my JSON objects! Very nice, clean and fast.

Thanks to you Michelle I thought I would share my story you got me started in the right direction and inspired me to go even further!

Ohh the power of Domino who says it's going away!