Building an app with jQuery dataTables and other extensions

I recently needed to build an application for my employer for managing some data.  It needed to present the data in a useful and aesthetically pleasing manner.  Users needed the ability to manipulate the data in an intuitive and intelligent fashion.  And it needed to be built from scratch quickly.

The App

The app was an integration and automation toolset for installing, configuring and managing Linux servers. We had a number of disparate tools that all handled pieces of the process, but nothing that pulled everything together from a full system lifecycle perspective. The tools that were in use when I got there were:

• Cobbler
• CFengine
• OCS Inventory
• Splunk
• Zenoss
• Confluence

Building servers was mostly a manual process. Configuring them was a separate process. Monitoring, another. Documentation, another. etc…

I built a dashboard and toolset that provided an interface into all of the tools being used so that systems engineers could go to a single UI and access everything they need. For example, the server build process required the user to touch each of the tools above to enter data about the server to be built. There was no single authoritative database. With this tool, the engineer was presented with a single form to enter data about the server. The utility populated Cobbler, CFengine, and OCS Inventory. Post-build tools that ran automatically after the build populated Splunk and Confluence. It cut down server build time from hours of engineer and wall clock time to less than 30 minutes of engineer time and about an hour of wall clock time.

There were still systems that the automation didn’t touch – mostly because other groups controlled them and didn’t want automation from systems they didn’t control. So, there was still manual effort for machine account creation in AD and DNS. There was still manual steps required for assigning authorization requirements for a new server because the authorization tool in use didn’t have any reachable APIs. Monitoring with Zenoss had to be started by hand. But, still. It was a huge step in the right direction for automation.

As I was developing the app, I learned a lot.  I learned jQuery and javaScript.  I learned how to build a mod_perl AJAX back-end.  I learned how to use Perl DBIx::Class.  I learned jQueryUI and dataTables and jeditable.  Lots of fun tools which made the app look great and made the overall UI much more useful than anything I could have done with my previous skills using just HTML and PHP.

It’s a very interesting exercise trying to document this project in a linear fashion, one component at a time. The application certainly wasn’t built that way. There was a lot of back and forth between the various code components. Adding functionality in one place usually affected the functions in another place. This was a much more iterative process than might be implied by reading this doc.

But, first a screenshot of the finished product.  It actually doesn’t look like much.  It’s just a table with some data and some nice formatting.  But there are lots of useful functional pieces in and around the table that make it interesting and worth documenting.

Nothing that you haven’t seen on a hundred web sites.  But putting it together was not as completely simple as it could have been.

Functionality

So just what does this app do? Is this simply a visual representation of a single SQL database table? Yes and no. It started out that way. But it turned out that there were a lot more capabilities required. It’s kind of amusing that doing this stuff was as complicated as it was. A Spreadsheet handles all of these functions easily. But a spreadsheet within a web browser isn’t simple. I needed to do this without using any commercial software – everything needed to be open source and free.

Base dataTables

The base dataTables package provides a nicely styled visualization of basic HTML table data. You can attach it to an HTML table and it will make the table look good and provide some instant functionality.

• Pagination – dataTables will take your data and break it down into ten row chunks that you can then page through.
• Filtering – dataTables provides a search box that will let you filter any of the fields of data by the string you type in the box.
• Sorting – You can sort the data by any column or combination of columns.
• Page Length – You can change the number of rows that are displayed in a single “page” view of the data.

Aesthetics

I added jQueryUI to make the table look better, and also to provide some widget functions that are used by some of the other components, including dialog boxes and autocomplete.

Server Side Processing

The first thing I added to the dataTables definition was a server side processing component which grabbed the data with an AJAX call rather than pulling it from the HTML. This required writing the base of the back end code to retrieve the data. All of the base features listed above had to be handled by the back end, rather than the front end. For example, the filter needed to be implemented as criteria for the query so only the filtered rows were returned.

Editing

Editing functions are provided by the dataTables makeEditable plugin. This provides the basic ability to edit cells in the table and have the changes sent to the server. It also has row add and delete functionality. I implemented all of these features, but eventually, I replaced the delete functionality with a mechnsim that would let me delete multiple rows instead of the single row this provided.

More filtering

The basic filtering with dataTables lets you provide a single search criteria that is applied to all fields. We needed to do more granular searching where we search one field for one criteria and another field for a different criteria. So I added the search fields at the bottom of each column which allow multi column filtering.

Autocomplete

For editing and adding certain columns, we wanted to suggest values based on what was already in the database, but we didn’t want to restrict the user to only using those values. So I added Autocomplete functionsality to some of the fields for both the in-table editing and for the fields when adding rows with a form.

Multiselect

There are a couple of different techniques for selecting multiple rows with dataTables. We wanted to use checkboxes in addition to being able to select the row. So, I implemented a mechanism for multiselect that doesn’t use the dataTables mechanisms directly. And then I replaced the delete functionality from makeEditable because I liked mine more.

The Data

The data came from the dataTables examples found at datatables.net.  I should mention that the documentation and advice found there were instrumental in many aspects of the design of this app.

The data is a simple table of information about Web browsers.  I don’t even know if the versions and names are accurate.  It doesn’t particularly matter though.  The key is that it is a good sample data set for building a table around.  The database I used for the sample is MySQL.  The DDL for the table is below:

CREATE TABLE `browsers` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`RenderingEngine` varchar(45) default NULL,
`Browser` varchar(45) default NULL,
`Platforms` varchar(45) default NULL,
`EngineVersion` varchar(45) default NULL,
`CSSGrade` varchar(45) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM CHARSET=latin1;

And, here’s the data:

INSERT INTO `browsers` VALUES
('','Trident','Internet Explorer 4.0','Win 95+','4','X'),
('','Trident','Internet Explorer 5.0','Win 95+','5','C'),
('','Trident','Internet Explorer 5.5','Win 95+','5.5','A'),
('','Trident','Internet Explorer 6','Win 98+','6','A'),
('','Trident','Internet Explorer 7','Win XP SP2+','7','A'),
('','Trident','AOL browser (AOL desktop)','Win XP','6','A'),
('','Gecko','Firefox 1.0','Win 98+ / OSX.2+','1.7','A'),
('','Gecko','Firefox 1.5','Win 98+ / OSX.2+','1.8','A'),
('','Gecko','Firefox 2.0','Win 98+ / OSX.2+','1.8','A'),
('','Gecko','Firefox 3.0','Win 2k+ / OSX.3+','1.9','A'),
('','Gecko','Camino 1.0','OSX.2+','1.8','A'),
('','Gecko','Camino 1.5','OSX.3+','1.8','A'),
('','Gecko','Netscape 7.2','Win 95+ / Mac OS 8.6-9.2','1.7','A'),
('','Gecko','Netscape Browser 8','Win 98SE+','1.7','A'),
('','Gecko','Netscape Navigator 9','Win 98+ / OSX.2+','1.8','A'),
('','Gecko','Mozilla 1.0','Win 95+ / OSX.1+','1','A'),
('','Gecko','Mozilla 1.1','Win 95+ / OSX.1+','1.1','A'),
('','Gecko','Mozilla 1.2','Win 95+ / OSX.1+','1.2','A'),
('','Gecko','Mozilla 1.3','Win 95+ / OSX.1+','1.3','A'),
('','Gecko','Mozilla 1.4','Win 95+ / OSX.1+','1.4','A'),
('','Gecko','Mozilla 1.5','Win 95+ / OSX.1+','1.5','A'),
('','Gecko','Mozilla 1.6','Win 95+ / OSX.1+','1.6','A'),
('','Gecko','Mozilla 1.7','Win 98+ / OSX.1+','1.7','A'),
('','Gecko','Mozilla 1.8','Win 98+ / OSX.1+','1.8','A'),
('','Gecko','Seamonkey 1.1','Win 98+ / OSX.2+','1.8','A'),
('','Gecko','Epiphany 2.20','Gnome','1.8','A'),
('','Webkit','Safari 1.2','OSX.3','125.5','A'),
('','Webkit','Safari 1.3','OSX.3','312.8','A'),
('','Webkit','Safari 2.0','OSX.4+','419.3','A'),
('','Webkit','Safari 3.0','OSX.4+','522.1','A'),
('','Webkit','OmniWeb 5.5','OSX.4+','420','A'),
('','Webkit','iPod Touch / iPhone','iPod','420.1','A'),
('','Webkit','S60','S60','413','A'),
('','Presto','Opera 7.0','Win 95+ / OSX.1+','-','A'),
('','Presto','Opera 7.5','Win 95+ / OSX.2+','-','A'),
('','Presto','Opera 8.0','Win 95+ / OSX.2+','-','A'),
('','Presto','Opera 8.5','Win 95+ / OSX.2+','-','A'),
('','Presto','Opera 9.0','Win 95+ / OSX.3+','-','A'),
('','Presto','Opera 9.2','Win 88+ / OSX.3+','-','A'),
('','Presto','Opera 9.5','Win 88+ / OSX.3+','-','A'),
('','Presto','Opera for Wii','Wii','-','A'),
('','Presto','Nokia N800','N800','-','A'),
('','Presto','Nintendo DS browser','Nintendo DS','8.5','C/A<sup>1</sup>'),
('','KHTML','Konqureror 3.1','KDE 3.1','3.1','C'),
('','KHTML','Konqureror 3.3','KDE 3.3','3.3','A'),
('','KHTML','Konqureror 3.5','KDE 3.5','3.5','A'),
('','Tasman','Internet Explorer 4.5','Mac OS 8-9','-','X'),
('','Tasman','Internet Explorer 5.1','Mac OS 7.6-9','1','C'),
('','Tasman','Internet Explorer 5.2','Mac OS 8-X','1','C'),
('','Misc','NetFront 3.1','Embedded devices','-','C'),
('','Misc','NetFront 3.4','Embedded devices','-','A'),
('','Misc','Dillo 0.8','Embedded devices','-','X'),
('','Misc','Links','Text only','-','X'),
('','Misc','Lynx','Text only','-','X'),
('','Misc','IE Mobile','Windows Mobile 6','-','C'),
('','Misc','PSP browser','PSP','-','C'),
('','Other browsers','All others','-','-','U');

The Back-end

The back-end is written in Perl.  The cool thing about this is that the main Perl code doesn’t know anything about the structure of the database or the table.  It just needs to know how to connect to the database and where the DBIx::Class schema is.  All of the information about what table to work with and what columns to use comes from the client.

DBIx::Class setup

Working with DBIx::Class is relatively simple.  First you have DBIx::Class create the object-relational schema using DBIx::Class::Schema::Loader.  The entire Perl script for generating the classes is below

#!/usr/bin/perl
use strict;
use warnings;
use DBIx::Class::Schema::Loader qw/make_schema_at/;
make_schema_at(
'DTDemo::Schema',
{debug => 0, dump_directory => '/usr/lib/perl5/site_perl/5.8.8/',
generate_pod => 1, },
['dbi:mysql:browsers:localhost:3306:','browser','browsing'],
);

The important components of this script are:

• DTDemo::Schema – This is the component you will need to include in your back-end application to use the Schema.
• dump_directory – This is where the DTDemo folder will get created, with the Schema folder underneath. If you put it in the appropriate site_perl directory for your system, you won’t need to set the PATH for your app to find it.
• dbi:mysql…. – This line is your connection info. The dbi connection string and the user id and password for connecting to the database. You will use the same information in the application.

The Handler

The main back-end piece is a mod_perl handler that consists of a few code chunks. For illustrative purposes, I pulled out most (if not all) of the error checking code. Error checking is good. You should have some.

The first chunk of code is basic application setup and some global variables.

package DTDemo::DTBackEnd;

use strict;
use warnings;

use Apache2::Request;
use Apache2::Const -compile => qw(OK NOT_FOUND LOG_INFO);
use APR::Const -compile => qw(ENOTIME);
use JSON;
use DateTime;
use Data::Dumper;
use DBIx::Class::ResultSet;
use DTDemo::Schema;

my $Dbi;
my $User;
my $Passwd;
my $table;

Important things to note here:

• package DTDemo::DTBackEnd; – This will need to match what you put in your Apache httpd config file. I’ll have a sample of that in here further down.
• use DTDemo::Schema; – This needs to match the make_schema_at from the script that created the DBIx::Class tables.

The second is the controller. This is the piece that handles the request from Apache HTTPD and decides what to do with it. It uses a switch block to send requests to the appropriate subroutines.

sub handler {
  my $r = shift;
  my @pieces;
  my $command;
  my $data;
  my $status;

  my $req = Apache2::Request->new($r);
  @pieces = split( '/', $r->uri);
  $command = $pieces[@pieces-2];
  $table = $pieces[@pieces-1];

  $Dbi = $r->dir_config('Dbi');
  $User = $r->dir_config('User');
  $Passwd = $r->dir_config('Passwd');
  SWITCH: for ($command) {
    if (/^Table$/) {
      $data = dataTable($r);
      $r->content_type('application/JSON');
      $r->print($data);
      return Apache2::Const::OK;
      last SWITCH;
    }
    if (/^Update$/) {
      $status = Update($r);
      $r->content_type('text/plain');
      $r->print($status);
      return Apache2::Const::OK;
      last SWITCH;
    }
    if (/^Add$/) {
      $data = Add($r);
      $r->content_type('text/plain');
      $r->print($data);
      return Apache2::Const::OK;
      last SWITCH;
    }
    if (/^Delete$/) {
      $status = Delete($r);
      $r->content_type('text/plain');
      $r->print($status);
      return Apache2::Const::OK;
      last SWITCH;
    }
    if(/^AutoComplete$/) {
      $data = autoComplete($r);
      $r->content_type('application/JSON');
      $r->print($data);
      return Apache2::Const::OK;
      last SWITCH;
    }
  }
}

Notes for this segment:

• DBI components – The back-end grabs the DBI credentials and connect string from the httpd config file. That’s the $r->dir_config pieces.
• pieces – This is the breakdown of the URL that called the back-end. It assumes that the last chunk before any parameters is the name of the table you are working with. The chunk before that is the function that you are calling.
• commands – The command blocks each call a function, then wrap up the returned data and ship it back to the requesting client. Note that every block returns Apache2::Const::OK. Since this is always called by AJAX, this lets the javaScript code on the front-end handle all the errors.

Let’s look at each of the main subroutines. When we get to the one that retrieves the data for generating the dataTable, there are a few subroutines that it calls that we’ll need to go through.

Delete

This one is a simple one. It takes the table from the request string. Then it grabs the id of the row to delete from the request parameters (POST or GET). Then it deletes the row using DBIx::Class. The field name of the id field is hardcoded to “id”. This is probably not optimal, and I should make this something that can be set remotely like the table name. Next version.

sub Delete {

  my $r = shift;
  my $req = Apache2::Request->new($r);

  my $Schema=DTDemo::Schema->connect($Dbi,$User,$Passwd);

  $Schema->resultset($table)->search({ id => $req->param('id') })->delete;
  return "";
}

Update

Update is not much more complex than Delete. Again, I hardcode the id field to “id”. This is designed to update one field for one record at a time. This mechanism works best with the various edit methods coming in from the front-end.

sub Update {
  my $r = shift;
  my $req = Apache2::Request->new($r);
  my %where = ();

  my $id = $req->param('id');
  my $value = $req->param('value');
  my $idfield = "id";

  my $field = ($req->param('columnName'));

  push @{$where{'-or'}},{ $idfield => $id };

  my $Schema=DTDemo::Schema->connect($Dbi,$User,$Passwd);
  my $rs = $Schema->resultset($table)->search(\%where);

  my $row = $rs->next;
  $row->$field($value);
  $row->update;

  my $status = $value;
  return $status;
}

Add

The Add subroutine relies completely on the client to send the necessary records and data. It assumes that every POST or GET parameter represents a field to be added to the particular table, and that all of the required fields will be present. It then builds a hash of the field names and values which it passes to the DBIx::Class create method. The $new_row->id that is returned is the generated unique ID for the created record. This assumes that you have an autoincrement or other mechanism for generating a unique ID for a record. The ‘id’ in this case is not the name of the field. It’s the name of the DBIx::Class method that returns a generated ID.

sub Add {
  my $r = shift;
  my $req = Apache2::Request->new($r);

  my $Schema=DTDemo::Schema->connect($Dbi,$User,$Passwd);
  my %values;
  my @param_names = $req->param;

  for my $param (@param_names) {
    $values{$param} = $req->param($param);
  }
  my $new_row = $Schema->resultset($table)->create( {
    %values
  });

  return $new_row->id;
}

AutoComplete

The autoComplete subroutine generates the list of possible autocomplete values working in conjunction with the jQueryUI AutoComplete widget. This gets called for every keystroke when someone is typing in one of the autocomplete fields. It gets the table, the field and the search string all from the AJAX request from the client.

sub autoComplete {
  my $r = shift;
  my $req = Apache2::Request->new($r);
  my $field = $req->param('field');
  my $term = $req->param('term');
  my @list;
  my $attrs;
  my %where;

  push @{$where{'-or'}},{ $field => { like => '%'.$term.'%'}};
  $attrs = {
    columns => $field,
    distinct => 1,
    order => {"-asc" => $field }
  };

  my $Schema=DTDemo::Schema->connect($Dbi,$User,$Passwd);
  my $rs = $Schema->resultset($table)->search(\%where, $attrs);
  while (my $row = $rs->next) {
    push @list, $row->$field;
  }

  my $json= new JSON;
  my $data = $json->encode(\@list);
  return $data;
}

dataTable

This is the meat of the dataTables implementation. It is adapted from the back-end module created by Alexander Becker available at http://datatables.net/development/server-side/perl_mvc. There are some significant differences because Alexandar used CGI::Application to interact with the web server and SQL::Abstract to interact with the database, where I used Apache2::Request and DBIx::Class. DBIx::Class actually uses SQL::Abstract under the covers.

sub dataTable {

  my $r = shift;
  my $req = Apache2::Request->new($r);
  my @fields;
  my @aaData;
  my $iFilteredTotal;
  my $iTotal;

   # Paging

  my $limit = $req->param('iDisplayLength') || 10;
  my $offset="0";
  if($req->param('iDisplayStart') ) {
    $offset = $req->param('iDisplayStart');
  }

  # -- Ordering
  my @order = _generate_order_clause($r);

  # -- Filtering
  my %where = _generate_where_clause($r);

  # -- get table contents
  @fields=();
  # Set up the field names based on what the client sent
  for ( my $j = 0; $j < $req->param('iColumns'); $j++) {
    push @fields, $req->param('mDataProp_'.$j);
  }

  $iFilteredTotal = _get_filtered_total( $r, $req, \%where );
  @aaData = _get_table_content( $r, \@fields, \%where, \@order, $limit, $offset );
  $iTotal = _get_total_record_count( );

  # -- build final data structure
  my %sOutput = (
    sEcho => int($req->param('sEcho')),
    iTotalRecords => int($iTotal),
    iTotalDisplayRecords => int($iFilteredTotal),
    aaData => \@aaData,
  );

  my $json= new JSON;
  my $response = $json->encode(\%sOutput);

  return $response;
} # /dataTable

This routine generates the criteria and the attributes for the query that will get executed. It then calls a few routines to generate the reponse data and some record count meta data. Then it packages up the results and returns them to the controller, which returns them to the client.

Ordering

Ordering is handled by this small subroutine, which pulls the sort information out of the dataTables GET request.

sub _generate_order_clause {
  my $r = shift;
  my $req = Apache2::Request->new($r);

  my @orderlist=();

  if (defined ($req->param('iSortCol_0') ) ) {
    for ( my $l = 0; $l < $req->param('iSortingcols'); $l++) {

      my $direction = '-' . $req->param('sSortDir_'.$l);
      my $column_name=_fnColumnToField ($r, $req->param('iSortCol_'.$l) );
      push @orderlist, { $direction => $column_name };
    }
  }
  return @orderlist;
} # _generate_order_clause

Searching

The WHERE clause is generated by this routine which gets the details from the dataTables GET request. Modifications to this routine include the ability to search either globally for a value, or for values in a specific column or set of columns.

sub _generate_where_clause {
  my $r = shift;
  my $req = Apache2::Request->new($r);

  my %where = ();

  if ( defined ($req->param('sSearch') ) ){
    for ( my $j = 0; $j < $req->param('iColumns'); $j++) {
      my $search_string = $req->param('sSearch');
      my $searchable_ident = 'bSearchable_'.$j;
      if ($req->param($searchable_ident) and $req->param($searchable_ident) eq 'true') {
        my $column = _fnColumnToField($r, $j );
        my $csearch = 'sSearch_'.$j;
        if ( defined ( $req->param($csearch) ) and length($req->param($csearch)) > 0 ) {
          $search_string = $req->param($csearch);
          push @{$where{'-and'}},{ $column => { like => '%'.$search_string.'%'}};
        } else {
          push @{$where{'-or'}},{ $column => { like => '%'.$search_string.'%'}};
        }
      }
    }
  }
  return %where;
}

Field list

The field list comes from the mData parameters of the javaScript dataTables definition. These show up on the server as mDataProp parameters. The benefit of handling column names this way is that the server side component doesn’t need to know about the table structure.

sub _fnColumnToField {
  my $r = shift;
  my $k = shift;
  my $req = Apache2::Request->new($r);
  my %dispatcher;
  my $field;

  if (! defined ($k) ) {
    $k=0;
  }
  $field = $req->param("mDataProp_".$k);
  return $field;
}

Row counts

There are two row counts that are returned by the server to dataTables. The first is the total record count for the entire table. Thats the _get_total_record_count routine. The second one – _get_filtered_total – gets a record count with the same where clause as the requested data applied.

sub _get_total_record_count {
  my $Schema=DTDemo::Schema->connect($Dbi,$User,$Passwd);
  my $rs = $Schema->resultset($table);
  my $cnt = $rs->count;

  return $cnt;
} # /_get_total_record_count

sub _get_filtered_total {
  my $r = shift;
  my $req = shift;
  my $where_href = shift or die("Missing where clause (href).");

  my $Schema=DTDemo::Schema->connect($Dbi,$User,$Passwd);
  my $rs = $Schema->resultset($table)->search($where_href);
  my $rv = $rs->count;

  return $rv;
} # /_filtered_get_filtered_total

Table Content

The routine that puts all the portions of the query together and actually queries the database is called _get_table_content. As with the Update and Delete routines, there is a hardcoded assumption that the id field for the table is called ‘id’. The “DT_RowId” value is the row ID that dataTables will assign to each row in the table. MakeEditable and some of the other front-end code use this value to generate the id for update and delete requests.

sub _get_table_content {
  my $r = shift;
  my $fields_aref = shift or die("Missing fields.");
  my $where_href = shift or die("Missing where clause.");
  my $order_href = shift or die("Missing order clause.");
  my $limit = shift or die("Missing limit");
  my $offset = shift;
  my %attrs;
  my $rs;
  my $req = Apache2::Request->new($r);

  $attrs{'order_by'} =  $order_href;
  $attrs{'offset'} = $offset;

  if ($limit >= 0) {
    $attrs{'rows'} = $limit;
  }

  my $Schema=DTDemo::Schema->connect($Dbi,$User,$Passwd);
  $rs = $Schema->resultset($table)->search($where_href, \%attrs);

  my @aaData=();
  while (my $row = $rs->next) {
    my @jRow=();
    my %entry;
    my $sData;
    for ( my $j = 0; $j < $req->param('iColumns'); $j++) {
      my $sField = $req->param('mDataProp_'.$j);
      $sData = $row->get_column($sField);
      if ( $sField =~ 'id' ) {
        my %rowId = ( 'DT_RowId' => $sData );
        push @jRow, %rowId;
      }
      %entry= ($sField => $sData);
      push @jRow,  %entry  ;
    }
    push @aaData, { @jRow } ;
  }
  return @aaData;
} # /_get_table_content

Configuration

The configuration file for the back end is an Apache httpd config file segment. On Redhat-esque systems, it’s a file that goes in /etc/httpd/conf.d. The file contains the info needed to tell Apache how to contact the back end. It also contains the credentials for communicating with the database. An example config file looks like this.

PerlModule DTDemo::DTBackEnd

<Location /DTBackEnd>
  PerlSetVar Dbi        dbi:mysql:browsers:localhost:3306:
  PerlSetVar User       browser
  PerlSetVar Passwd     browsing

  PerlSetEnv DBIC_TRACE 0
  SetHandler            modperl
  PerlResponseHandler   DTDemo::DTBackEnd
</Location>

Note that the DBIC_TRACE flag can be set to ‘1’. If you do so, your httpd logs will contain a lot of information about what DBIx::Class is doing underneath the covers. This can be very useful when debugging more complex queries.

The Front End

Much of the back end described above only makes sense in the context of the front end, and vice versa. After reading through the front end descriptions here, it may make sense to re-read the back end section. Just a thought.

The front end is a combination of HTML, javaScript and CSS, all working together. All of the included javaScript libraries are open source and easily available. All of the CSS files with one exception come with various javaScript modules. I won’t include the source of any of the easily available components in this page.

Head

The HTML section contains the links to the javaScript and CSS files as well as all of the javaScript in this app. The HTML segment for the includes is below. Afterwards, I’ll put in links to sources of the various libraries.

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Server Side Processing Demo</title>

<link type="text/css" href="css/ui-lightness/jquery-ui-1.8.22.custom.css" rel="stylesheet" />
<link type="text/css" href="css/jquery.dataTables.css" rel="stylesheet" />
<link type="text/css" href="css/dtdemo.css" rel="stylesheet" />
<script type="text/javascript" src="js/jquery-1.8.1.js"></script>
<script type="text/javascript" src="js/jquery-ui-1.9.0.custom.js"></script>
<script type="text/javascript" src="js/jquery.dataTables.js"></script>
<script type="text/javascript" src="js/jquery.validate.js"></script>
<script type="text/javascript" src="js/jquery.dataTables.editable.js"></script>
<script type="text/javascript" src="js/jquery.jeditable.js"></script>

I can’t emphasize enough how valuable the resources below have been with putting this stuff together. The documentation ranges from good to amazing, but it’s all useful.

• jQuery – http://jquery.com
• jQueryUI – http://jqueryui.com
• dataTables – http://datatables.net
• validate – http://bassistance.de/jquery-plugins/jquery-plugin-validation/
• dataTables.makeEditable – http://code.google.com/p/jquery-datatables-editable/
• jeditable – http://www.appelsiini.net/projects/jeditable

HTML Components

There isn’t a lot of HTML code in this app. There’s the structure of the table, and a couple of forms.

Table Definition

The table needs to define the header and the footer for the table. The header is used by dataTables to know how many columns there will be. The footer is where we put the per-column search functionality. The body of the table needs to exist, but it is empty since we will be using AJAX to get the data for the table. The buttons on the bottom are for adding, deleting and editing the entries in the table.

Note that this is the first place where we are putting anything that is specific to our data. The column headers here are purely descriptive and not used anyplace else. So they can be whatever you want. If you wanted to make this generic, you could leave the column names blank and fill them in later with javaScript.

<table id="dtFullTable" class="display">
  <thead>
    <tr>
      <th><input type="checkbox" id="dtFullTableSelectAll" /></th>
      <th>Browser</th>
      <th>Rendering Engine</th>
      <th>Platforms</th>
      <th>Engine Version</th>
      <th>CSS Grade</th>
    </tr>
  </thead>
  <tbody>
  </tbody>
  <tfoot>
    <tr>
      <th>-</th>
      <th><input tabindex="1" type="text" name="search_browser" value="Search Browser" class="dtFoot search_init" /></th>
      <th><input tabindex="2" type="text" name="search_rendering_engine" value="Search Rendering Engine" class="dtFoot search_init" /></th>
      <th><input tabindex="3" type="text" name="search_platforms" value="Search Platform" class="dtFoot search_init" /></th>
      <th><input tabindex="4" type="text" name="search_engine_version" value="Search Engine Version" class="dtFoot search_init" /></th>
      <th><input tabindex="5" type="text" name="search_css_grade" value="Search CSS Grade" class="dtFoot search_init" /></th>
    </tr>
  </tfoot>
</table>
<button id='btnAddNewRow' />
<button id='btnBulkDelete'>Delete Selected Browsers</button>
<button id='btnBulkEdit'>Edit Selected Browsers</button>

The tabindex on the inputs in the footer are important. They gave me a way to identify each of the input fields with a number that would correspond to the correct field definition in the javaScript below.

Add Record Form

The form for adding new records gets defined in the HTML. It won’t show up on the page when the page is loaded because the makeEditable plugin hides it.

<form id="formAddNewRow" action="#" title="Add a new Browser">
  <input type="hidden" name="id" id="id" rel="0" />
  <table>
    <tr>
      <td>
        <label for="renderingengine" class="add-form-label">Rendering Engine</label><br />
        <input size="20" type="text" name="renderingengine" id="renderingengine" class="add-form-input required" rel="2" />
      </td>
      <td>
        <label for="browser" class="add-form-label">Browser</label><br />
        <input size="20" type="text" name="browser" id="browser" class="add-form-input required" rel="1" />
      </td>
    </tr>
    <tr>
      <td>
        <label for="platforms" class="add-form-label">Platforms</label><br />
        <input size="20" type="text" name="platforms" id="platforms" class="add-form-input required" rel="3" />
      </td>
      <td>
        <label for="engineversion" class="add-form-label">Engine Version</label><br />
        <input size="20" type="text" name="engineversion" id="engineversion" class="add-form-input required" rel="4" />
      </td>
    </tr>
    <tr>
      <td>
        <label for="cssgrade" class="add-form-label">CSS Grade</label><br />
        <input size="20" type="text" name="cssgrade" id="cssgrade" class="add-form-input required" rel="5" />
      </td>
    </tr>
  </table>
</form> <!-- formAddNewRow -->

Things of note in this form:

• name – The input names are the same as the field names in the database. These names get used to set the parameter names when the form is converted into a POST request by makeEditable.
• id – The ids are used for adding any functionality to the form. In this example, I attach autocompletes to renderingengine and platforms. The code for this will be described below.
• class – Beyond the obvious styling uses of class, we’ve got some validation included here. The required class tells the jQuery validation plugin that these fields cannot be empty when the form is submitted. There are lots of other types of validations that can be included. See the validations plugin page for more info.
• rel – This relates the fields to column numbers of the table. MakeEditable requires this. But, I don’t think it’s actually used for anything. It could be used to generate the field names from the dataTables description. It’s possible that if I didn’t specify the name attributes of the inputs that makeEditable would use these instead. I should probably read the code and find out one of these days.

Bulk Edit Form

Bukl edit doesn’t make a lot of sense with this particular data set, but it did for the original app I developed this stuff for. So, I left a minimal bulk edit capability in here. The bulk edit form is relatively simple:

<form id="formBulkEdit" action="#" title="Bulk Edit">
  <label for="bulk-edit-cssgrade" class="add-form-label">CSS Grade</label><br />
  <input size="30" type="text" id="bulk-edit-cssgrade" class="add-form-input" />
<form>

Note that the id for this one is not exactly the field name. The field name is included and will be extracted from here by the javascript when the form is submitted.

Confirm Delete Dialog

This is a simple dialog that has space for the javaScript to insert the list of items to be deleted and to ask the user for confirmation before deleting.

<div id="confirmation_dialog" title="Bulk Delete Confirmation">
  <p>You are about to delete the browsers listed below.  Please confirm this action.</p>
  <p id="confirmation_browser_list" class="confirmation_browser_list"></p>
</div>

JavaScript

The javaScript is the main code for the front end. All of the complex work is done here. Getting all of these plugins working together required some complex coding. There aren’t as many discrete components within the javaScript. The central piece is the dataTable. However, there are a bunch of other chunks of functionality inside the dataTables definition. I will try to describe things as discretely as I can.

Starting at the Top

The top of the javaScript section of the web page. The global variable asInitVals is the only global and is used to help with the aesthetics of the search boxes at the bottom of the table. The $(document).read(function() { function is what jQuery executes when the browser has finished rendering the HTML. There’s very little HTML in this page, so not much happens before this.

<script type="text/javascript">
var asInitVals = new Array();

$(document).ready(function() {

dataTable

The dataTable definition itself is below. This uses some of the features of dataTables, but not all. The entire object is quite large. I include it here in its entirety in case anyone wants to copy it as-is. After the object, I will repeat it, dissecting it as I go along.

  var oTable = $("#dtFullTable")
    .dataTable({
      "sDom": '<"H"lfr>t<"F"ip>',
      "bJQueryUI": true,
      "iDisplayLength": 10,
      "bProcessing": true,
      "bServerSide": true,
      "sAjaxSource": "/DTBackEnd/Table/Browser",
      "bLengthChange": true,
      "sAjaxDataProp": "aaData",
      "oLanguage" : {
        "sSearch" : "Search all columns:"
      },
      "aoColumnDefs": [
        {
          "bSortable": false,
          "aTargets": [0]
        },
        {
          "bSearchable": true,
          "aTargets": [1,2]
        },
        {
          "mRender": function ( data, type, full ) {
            retVal = '<input type="checkbox" class="idSelectCheckbox" id="idSelect' + full["id"] + '" />';
            return retVal;
          },
        "aTargets": [0]
        },
        { "mData": "id", "aTargets": [0] },
        { "mData": "browser", "aTargets": [1] },
        { "mData": "renderingengine", "aTargets": [2] },
        { "mData": "platforms", "aTargets": [3] },
        { "mData": "engineversion", "aTargets": [4] },
        { "mData": "cssgrade", "aTargets": [5] },
        { "sName": "browser", "aTargets": [1] },
        { "sName": "renderingengine", "aTargets": [2] },
        { "sName": "platforms", "aTargets": [3] },
        { "sName": "engineversion", "aTargets": [4] },
        { "sName": "cssgrade", "aTargets": [5] },
      ],
      "fnDrawCallback": function (oSettings) {
    // setting up the checkboxes to enable or disable the bulk edit button
        $(".idSelectCheckbox").change(function() {
          var flag=false;
          if ( $(this).prop('checked') ) {
            $(this).parents('tr').addClass('row_selected');
          } else {
            $(this).parents('tr').removeClass('row_selected');
          }
          $(".idSelectCheckbox").each(function(j) {
            if ($(this).prop('checked') ) {
              flag=true;
            }
          });
          if ( flag ) {
            $("#btnBulkEdit").prop("disabled",false);
            $("#btnBulkEdit").removeClass("ui-button-disabled");
            $("#btnBulkEdit").removeClass("ui-state-disabled");
            $("#btnBulkDelete").prop("disabled",false);
            $("#btnBulkDelete").removeClass("ui-button-disabled");
            $("#btnBulkDelete").removeClass("ui-state-disabled");
          } else {
            $("#btnBulkEdit").prop("disabled",true);
            $("#btnBulkEdit").addClass("ui-button-disabled");
            $("#btnBulkEdit").addClass("ui-state-disabled");
            $("#btnBulkDelete").prop("disabled",true);
            $("#btnBulkDelete").addClass("ui-button-disabled");
            $("#btnBulkDelete").addClass("ui-state-disabled");
          }
        });
    // Set up a callback on each row left-click select of the whole row.
        $(".idSelectCheckbox").each(function() {
          nId = $(this).prop('id').substring(8);
          nTr = $(this).parents('tr');
          $(nTr).on("click",function(e) {
            if ($(e.target).is(":checkbox")) return;
            $("#idSelect" + $(this).prop('id')).prop('checked', ! $("#idSelect" + $(this).prop('id')).prop('checked'));
            $("#idSelect" + $(this).prop('id')).trigger("change");
          });
        });
      }
    } ).makeEditable( {
// Removed the Delete Button stuff - now handling delete as part of the multi-row select code.
      bDisableEditing: false,
      sUpdateURL: "/DTBackEnd/Update/Browser",
      sAddURL: "/DTBackEnd/Add/Browser",
      //sDeleteURL: "/DTBackEnd/Delete/Browser", // We won't use MakeEditable for delete, so we can use multiselect for delete.
      sSelectedRowClass: "fake", // We don't want makeEditable to show us what's selected.  We do that with the checkboxes managing the row classes.
      oAddNewRowFormOptions:{
        width: 400,
        show: "blind",
        hide: "blind",
        modal: true,
      },
      oAddNewRowButtonOptions: {
        label: "Add new browser...",
        icons: { primary: 'ui-icon-plus' }
      },
      oAddNewRowOkButtonOptions: {
        label: "Submit",
        icons: { primary: 'ui-icon-check' },
        name: "action",
        value: "add-new",
      },
      "sReadOnlyCellClass": "read_only",
      "aoColumns": [
        null, // ID field
        { // Browser
        },
        { // Rendering Engine
          indicator: 'Saving Rendering Engine...',
          tootip: 'Click to select Rendering Engine value',
          type: "autocompleteRE",
          onblur: 'submit',
          sUpdateURL: "/DTBackEnd/Update/Browser",
        },
        { // Platforms
          indicator: 'Saving Platform...',
          tootip: 'Click to select Platform value',
          type: "autocompletePlatform",
          onblur: 'submit',
          sUpdateURL: "/DTBackEnd/Update/Browser",
        },
        { // Engine Version
        },
        { // CSS Grade
        },
      ]
  }); // oTable  - main data table.

Explanations

  var oTable = $("#dtFullTable")

The “#dtFullTable” is the id for the HTML table definition.

    .dataTable({
      "sDom": '<"H"lfr>t<"F"ip>',
      "bJQueryUI": true,
      "iDisplayLength": 10,
      "bProcessing": true,
      "bServerSide": true,
      "sAjaxSource": "/DTBackEnd/Table/Browser",
      "bLengthChange": true,
      "sAjaxDataProp": "aaData",
      "oLanguage" : {
        "sSearch" : "Search all columns:"
      },

The basic dataTable definition.

• sDom – This controls the layout of various dataTables components.
• bJQueryUI – Tells dataTables to use jQueryUI styles where appropriate.
• iDisplayLength – The default number of records to display for each page of data.
• bProcessing – Display a banner telling the user that data is being processed while waiting for AJAX calls to return with data.
• bServerSide – Get the data from a server, rather than from within the HTML.
• sAjaxSource – This is the URL for retrieving the data from the server.
• bLengthChange – Let the user choose the length of the display page.
• sAjaxDataProp – The name of the JSON object within the AJAX returned data which contains the table data.
• oLanguage – This can be used for a bunch of different features. I use it for just one – the instruction to search all columns.

Next is the aoColumnsDefs, or array of column definitions. This contains customizing information about each of the columns, where needed.

      "aoColumnDefs": [
        {
          "bSortable": false,
          "aTargets": [0]
        },

This makes the ID column unsortable. Since we’re replacing the ID numbers with checkboxes below, it doesn’t make sense to have them be sortable.

        {
          "bSearchable": true,
          "aTargets": [1,2,3]
        },

This limits the the fields that will be searched when the “Search all columns” filtering is used.

        {
          "mRender": function ( data, type, full ) {
            retVal = '<input type="checkbox" class="idSelectCheckbox" id="idSelect' + full["id"] + '" />';
            return retVal;
          },
        "aTargets": [0]
        },

mRender replaces the values for the first column of data with checkboxes. The class “idSelectCheckbox” is important as it lets us perform scripting on all of the checkboxes. The id is important as it has the row id embedded in it which can be pulled out for operating on single items.

        { "mData": "id", "aTargets": [0] },
        { "mData": "browser", "aTargets": [1] },
        { "mData": "renderingengine", "aTargets": [2] },
        { "mData": "platforms", "aTargets": [3] },
        { "mData": "engineversion", "aTargets": [4] },
        { "mData": "cssgrade", "aTargets": [5] },

By default, dataTables determines which data item to put in which column based on its postition within the JSON aaData object. Using mData allows you to used named data elements within the JSON aaData object so you don’t need to worry about positionally placing the fields on the back end. This is one of the few places where the actual field names of the table’s columns are used.

        { "sName": "browser", "aTargets": [1] },
        { "sName": "renderingengine", "aTargets": [2] },
        { "sName": "platforms", "aTargets": [3] },
        { "sName": "engineversion", "aTargets": [4] },
        { "sName": "cssgrade", "aTargets": [5] },
      ],

The sName is used by makeEditable the same way that dataTables uses the mData. I think this is redundant and I’ve opened a bug with the makeEditable project to see if they would modify their code to use mData instead. That way you would only need to define the field names and their relationship to the columns once.

The fnDrawCallback is a block of code that is called once the table has been completely populated and drawn. This lets us add hooks to the components of the table itself. Notably, I add hooks to the checkboxes here. These need to be added here, rather than in the mRender section because you can’t add event hooks to objects that haven’t actually been drawn yet.

      "fnDrawCallback": function (oSettings) {
    // setting up the checkboxes to enable or disable the bulk edit button
        $(".idSelectCheckbox").change(function() {
          var flag=false;
          if ( $(this).prop('checked') ) {
            $(this).parents('tr').addClass('row_selected');
          } else {
            $(this).parents('tr').removeClass('row_selected');
          }
          $(".idSelectCheckbox").each(function(j) {
            if ($(this).prop('checked') ) {
              flag=true;
            }
          });

This replacese the “row selected” functionality of the makeEditable plugin. It sets or unsets the row_selected class on each row when the checkbox is checked. It then runs through the list of checkboxes to see if any are set. If any are set, it then enables the bulk edit and the delete buttons.

          if ( flag ) {
            $("#btnBulkEdit").prop("disabled",false);
            $("#btnBulkEdit").removeClass("ui-button-disabled");
            $("#btnBulkEdit").removeClass("ui-state-disabled");
            $("#btnBulkDelete").prop("disabled",false);
            $("#btnBulkDelete").removeClass("ui-button-disabled");
            $("#btnBulkDelete").removeClass("ui-state-disabled");
          } else {
            $("#btnBulkEdit").prop("disabled",true);
            $("#btnBulkEdit").addClass("ui-button-disabled");
            $("#btnBulkEdit").addClass("ui-state-disabled");
            $("#btnBulkDelete").prop("disabled",true);
            $("#btnBulkDelete").addClass("ui-button-disabled");
            $("#btnBulkDelete").addClass("ui-state-disabled");
          }
        });

The next section sets up a callback on the left click of each row to select the row. This involved toggling the checkbox for the clicked row, and explicitly triggering the change event for the checkbox so the code above would be executed.

        $(".idSelectCheckbox").each(function() {
          nId = $(this).prop('id').substring(8);
          nTr = $(this).parents('tr');
          $(nTr).on("click",function(e) {
            if ($(e.target).is(":checkbox")) return;
            $("#idSelect" + $(this).prop('id')).prop('checked', ! $("#idSelect" + $(this).prop('id')).prop('checked'));
            $("#idSelect" + $(this).prop('id')).trigger("change");
          });
        });
      }

Now we get to the makeEditable code.

    } ).makeEditable( {
      sUpdateURL: "/DTBackEnd/Update/Browser",
      sAddURL: "/DTBackEnd/Add/Browser",
      //sDeleteURL: "/DTBackEnd/Delete/Browser", // We won't use MakeEditable for delete, so we can use multiselect for delete.

The URLs are the AJAX calls made for updating a cell, or adding or deleting a row. The sDeleteURL is there but commented out. It would function if it was commented in, but I think the app works better just using the multiselect for delete. I left this in here so it would be obvious that I’m intentionally not using this function.

      sSelectedRowClass: "fake", // We don't want makeEditable to show us what's selected.  We do that with the checkboxes managing the row classes.

This is a cheat of sorts. When selecting a row, makeEditable still marks the row as selected within the dataTables context. But by setting the class to “fake” there’s no visual cue that makeEditable has done this. The user and the effective functionality of the app only see the checkbox based row selections.

      oAddNewRowFormOptions:{
        width: 400,
        show: "blind",
        hide: "blind",
        modal: true,
      },

The oAddNewRowFormOptions object is a collection of properties for the jQueryUI dialog that makeEditable creates for displaying the new row form. These are documented at the jQueryUI site and are barely mentioned in the makeEditable documentation. Actually, I don’t think they’re mentioned at all. I learned this one from the makeEditable source code.

      oAddNewRowButtonOptions: {
        label: "Add new browser...",
        icons: { primary: 'ui-icon-plus' }
      },
      oAddNewRowOkButtonOptions: {
        label: "Submit",
        icons: { primary: 'ui-icon-check' },
        name: "action",
        value: "add-new",
      },

These objects are jQueryUI button objects managed by makeEditable. Again, the porperties are documented at the jQueryUI site.

The next aoColumns is similar to the dataTables aoColumns or aoColumnDefs. aoColumnDefs can be in any order and each option has to have targets assigned. aoColumns are in positional order. In this case, they are telling makeEditable how to handle editing each field. The parameters here are jEditable parameters. Most of the documentation for using alternet editing types for dataTables cells comes from the jEditable site. It’s described there, though, just in the context of editing the contents of a div. I don’t think I ever found really clean documentation on how to do the autocomplete. I picked up hints on stackoverflow.com and on a few blogs. The key here is the “type” which is defined further down in the code.

      "aoColumns": [
        null, // ID field
        { // Browser
        },
        { // Rendering Engine
          indicator: 'Saving Rendering Engine...',
          tootip: 'Click to select Rendering Engine value',
          type: "autocompleteRE",
          onblur: 'submit',
          sUpdateURL: "/DTBackEnd/Update/Browser",
        },
        { // Platforms
          indicator: 'Saving Platform...',
          tootip: 'Click to select Platform value',
          type: "autocompletePlatform",
          onblur: 'submit',
          sUpdateURL: "/DTBackEnd/Update/Browser",
        },
        { // Engine Version
        },
        { // CSS Grade
        },
      ]
  }); // oTable  - main data table.

fnReloadAjax

I got the fnReloadAjax function from the dataTables web site. It’s used when we need to reload the data for a table without reloading the page or finding some other way to re-instantiate the table.

  $.fn.dataTableExt.oApi.fnReloadAjax = function ( oSettings, sNewSource, fnCallback, bStandingRedraw )
  {
    if ( typeof sNewSource != 'undefined' && sNewSource != null ) {
        oSettings.sAjaxSource = sNewSource;
    }
    // Server-side processing should just call fnDraw
    if ( oSettings.oFeatures.bServerSide ) {
        this.fnDraw();
        return;
    }
    this.oApi._fnProcessingDisplay( oSettings, true );
    var that = this;
    var iStart = oSettings._iDisplayStart;
    var aData = [];
    this.oApi._fnServerParams( oSettings, aData );
    oSettings.fnServerData.call( oSettings.oInstance, oSettings.sAjaxSource, aData, function(json) {
        /* Clear the old information from the table */
        that.oApi._fnClearTable( oSettings );
        /* Got the data - add it to the table */
        var aData =  (oSettings.sAjaxDataProp !== "") ?
            that.oApi._fnGetObjectDataFn( oSettings.sAjaxDataProp )( json ) : json;
        for ( var i=0 ; i<aData.length ; i++ )
        {
            that.oApi._fnAddData( oSettings, aData[i] );
        }
        oSettings.aiDisplay = oSettings.aiDisplayMaster.slice();
        if ( typeof bStandingRedraw != 'undefined' && bStandingRedraw === true )
        {
            oSettings._iDisplayStart = iStart;
            that.fnDraw( false );
        }
        else
        {
            that.fnDraw();
        }

        that.oApi._fnProcessingDisplay( oSettings, false );
        /* Callback user function - for event handlers etc */
        if ( typeof fnCallback == 'function' && fnCallback != null )
        {
            fnCallback( oSettings );
        }
    }, oSettings );
  };

tableUpdate

This function makes an ajax call to update one field of one row of the Browser table. It’s used in this sample primarily by the bulk edit features.

  (function($) {
    $.fn.tableUpdate = function(field,id,value) {
      $.ajax({
        url: "/DTBackEnd/Update/Browser",
        data: {
          columnName: field,
          id: id,
          value: value
        },
        success: function(msg) {
          $(".msgText").html(msg);
        }
      });
    }
  })(jQuery);

addInputType

These are the jEditable addInputType calls. They set up the autocomplete for the makeEditable cells in a couple of columns. The documentation for this comes partially from the jEditable site and partially from the jQueryUI documentation. This one was a pain to put together because I couldn’t find an example anywhere of anyone using jQueryUI autocomplete in conjunction with jEditable. Which I was surprised by. Considering how useful this is and how nicely it renders, I would think that more people would have wanted to do this.

  $.editable.addInputType('autocompleteRE', {
    element : $.editable.types.text.element,
    plugin: function (settings, original) {
      $('input', this).autocomplete({
        source: "/DTBackEnd/AutoComplete/Browser?field=renderingengine",
      });
    }
  });

  $.editable.addInputType('autocompletePlatform', {
    element : $.editable.types.text.element,
    plugin: function (settings, original) {
      $('input', this).autocomplete({
        source: "/DTBackEnd/AutoComplete/Browser?field=platforms",
      });
    }
  });

AutoComplete

This sets up the autocomplete for the same two fields for the add new row form. This is straight out of the jQueryUI autoComplete documentation.

  $("#renderingengine").autocomplete({
    source: "/DTBackEnd/AutoComplete/Browser?field=renderingengine",
  });

  $("#platforms").autocomplete({
    source: "/DTBackEnd/AutoComplete/Browser?field=platforms",
  });

Column Searches

The individual column filters required some custom code. I found this on the dataTables site and modified it to fit my application a bit better. The original source would probably have worked fine for this simple example. My mods work better with multiple dataTables on the page.

  $("input.dtFoot").keyup( function () {
      /* Filter on the column (the tabindex) of this element */
    oTable.fnFilter( this.value, $(this).attr("tabindex"));
  });

These next few functions provide the nice user friendly text input boxes for the column footer search boxes. The shaded text that disappears when you click on the box and returns when you clear out your own values from the box.

  $("input.dtFoot").each( function (i) {
      asInitVals[i] = this.value;
  } );
  $("input.dtFoot").focus( function () {
      if ( $(this).hasClass("search_init") )
      {
          $(this).removeClass("search_init");
          this.value = "";
      }
  } );
  $("input.dtFoot").blur( function (i) {
      if ( this.value == "" )
      {
          this.className = "search_init";
          this.value = asInitVals[$("tfoot input").index(this)];
      }
  } );

Bulk Buttons

These are the jQueryUI button definitions for the bulk edit and bulk delete buttons.

  $("#btnBulkEdit").button({
    icons: {
      primary: "ui-icon-pencil"
    },
  });
  $("#btnBulkDelete").button({
    icons: {
      primary: "ui-icon-trash"
    },
  });

Select All

Building the select all functionality was relatively simple. The key here, though, is that you need to not just set the checkboxes to checked or unchecked. You need to trigger the change event on each one. The change event was attached to above, and that’s what drives the selection formatting and whether or not the bulk buttons are enabled.

  $("#dtFullTableSelectAll").on("change",function() {
    if ($(this).prop('checked')) {
      $(".idSelectCheckbox").each(function() {
        $(this).prop('checked',true);
        $(this).trigger("change");
      });
    } else {
      $(".idSelectCheckbox").each(function() {
        $(this).prop('checked',false);
        $(this).trigger("change");
      });
    }
  });

Bulk Delete

This dialog function presents the list of browsers that are selected when the user hits the “Delete Selected Browsers” button. It’s a basic jQueryUI Dialog. I handle the actual delete logic with an AJAX call in response to the user pressing the “Confirm” button. A purist might suggest that the delete AJAX call should be in a separate function and this should just be the dialog box. A purist would probably be right. But this seemed like a small enough code segment to include here, so I left it rather than creating a new function.

  $("#dtFullTableSelectAll").on("change",function() {
  $("#confirmation_dialog").dialog( {
    autoOpen: false,
    modal: true,
    hide: "explode",
    maxHeight: "300",
    height: "300",
    buttons: [
      {
        text: "Confirm",
        id: "confirmation_dialog_confirm",
        click: function() {
          $(".idSelectCheckbox").each(function(i) {
            if ( $(this).prop('checked') ) {
              var nId = $(this).prop('id').substring(8);
              $.ajax({
                url: "/DTBackEnd/Delete/Browser",
                type: "POST",
                async: false,
                data: {id: nId},
              });
            }
          });
          $("#confirmation_dialog").dialog("close");
          $("#dtFullTableSelectAll").prop('checked',false);
          oTable.fnReloadAjax();
        }
      },
      {
        text: "Cancel",
        id: "confirmation_dialog_cancel",
        click: function() {
          $("#confirmation_dialog").dialog("close");
        }
      }
    ]
  });

Note that when the delete function has made all of the delete callse, it reloads the table from the database. Also, the AJAX calls are made with async set to false so that by the time the function gets to the reload, all of the deletes have actaully completed.

Bulk Edit jQueryUI dialog

This is a basic jQueryUI dialog for bulk editing objects. In the app that this is based on, the bulk edit functionality worked with multiple types of fields. For this simple demo, though, there is just the one text input.

  $("#formBulkEdit").dialog( {
    autoOpen: false,
    modal: true,
    width: 400,
    show: "blind",
    hide: "blind",
    buttons: [
      {
        text: "Submit",
        id: "bulk_edit_submit_button",
        click: function() {
          $("#formBulkEdit").submit();
        }
      },
      {
        text: "Cancel",
        id: "bulk_edit_cancel_button",
        click: function() {
          $("#formBulkEdit").dialog("close");
        }
      }
    ],
  });

Bulk Delete Button

This sets up the action for the bulk delete button. It populates the browser list for the delete confirmation dialog, and then it opens up that dialog. It sets the focus on the cancel button, so if the user just hits ‘enter’, the action will be cancelled. For a bulk delete, I just felt that this would be safer.

  $("#btnBulkDelete").on("click",function() {
    var browserlist="";
    $(".idSelectCheckbox").each(function(i) {
      if ( $(this).prop('checked') ) {
        var nTr = $(this).parents('tr');
        browserlist +=  nTr.children().first().next().html() + "<br/>";
      }
    });
    $("#confirmation_browser_list").html(browserlist);
    $("#confirmation_dialog").dialog("open");
    $("#confirmation_dialog_cancel").focus();
  });

Bulk Edit Button

This mostly just pops open the bulk edit form. It does clear out all of the text boxes on the form first, though. If it didn’t do this and you hit the Bulk Edit button for a different set of rows, it would retain the values from the last time you went through the form.

  $("#btnBulkEdit").on("click", function() {
    $("#formBulkEdit input:text").each(function(i) {
      $(this).val("");
    });
    $("#formBulkEdit").dialog("open");
  }); // btnBulkEdit . on click

Disable the bulk buttons

This just disables the bulk buttons at startup. With jQueryUI buttons, disabling the button doesn’t make the button look like it’s disabled. That requires CSS classing as well.

  $("#btnBulkEdit").prop("disabled",true);
  $("#btnBulkEdit").addClass("ui-button-disabled");
  $("#btnBulkEdit").addClass("ui-state-disabled");
  $("#btnBulkDelete").prop("disabled",true);
  $("#btnBulkDelete").addClass("ui-button-disabled");
  $("#btnBulkDelete").addClass("ui-state-disabled");

Bulk Edit Submit

This function walks through all of the row checkboxes and acts on each one that was checked. It then walks through all of the text fields in the bulk edit dialog form (all one of them in this demo) and sees if there is any data in them. If there is, it calls the tableUpdate function for each field for each row. This is where the conversion from bulk to atomic transactions takes place.

  $("#formBulkEdit").submit(function(evt) {
    $(".idSelectCheckbox").each(function(i) {
      if ( $(this).prop('checked') ) {
        var nId = $(this).prop('id').substring(8);
        $("#formBulkEdit input:text").each(function(i) {
          if ( $(this).val() != "") {
            $.fn.tableUpdate($(this).prop('id').substring(10), nId ,$(this).val());
          }
        });
      }
    });
    $("#formBulkEdit").dialog("close");
    oTable.fnReloadAjax();
    $("#dtFullTableSelectAll").prop('checked',false);
    return false;
  });
}); // $(document).ready(function()

</script>

Style Sheet

I don’t think the details of the style sheet are worth going into in as much depth as the code. I present the style sheet that I added to the ones that were distributed with the various libraries. Some of them aren’t even used by the demo code. Mostly, these are about aesthetic choices which will probably be made differently by people other than me.

table.display tr.even.row_selected td {
  background-color: #B0BED9;
}
table.display tr.odd.row_selected td {
  background-color: #9FAFD1;
}

table.display thead th div.DataTables_sort_wrapper {
  position: relative;
  padding-right: 20px;
  padding-right: 20px;
}

table.display thead th div.DataTables_sort_wrapper span {
  position: absolute;
  top: 50%;
  margin-top: -8px;
  right: 0;
}


body {
  font-family: Verdana, Arial, sans-serif; font-size: .9em;
}

.add-form-label {
  font-size: small;
}

.add-form-input-textarea {
  height: 50px;
  width: 400px;
  white-space: normal;
  -webkit-appearance: textarea;
}


.dataTables_filter input {
  font-size: x-small;
}

.dataTables_filter label {
  font-size: x-small;
}

.dataTables_length {
  font-size: x-small;
}

.dataTables_info {
  font-size: x-small;
}

/*
 * jQuery UI specific styling
 */

.paging_two_button .ui-button {
  float: left;
  cursor: pointer;
  * cursor: hand;
}

.paging_full_numbers .ui-button {
  padding: 2px 6px;
  margin: 0;
  cursor: pointer;
  * cursor: hand;
  color: #333 !important;
}
.dataTables_paginate .ui-button {
  margin-right: -0.1em !important;
}

.paging_full_numbers {
  width: 350px !important;
}

.dataTables_wrapper .ui-toolbar {
  padding: 5px;
}

.dataTables_paginate {
  width: auto;
}

.dataTables_info {
  padding-top: 3px;
}

table.display thead th {
  padding: 3px 0px 3px 10px;
  cursor: pointer;
  * cursor: hand;
}

div.dataTables_wrapper .ui-widget-header {
  font-weight: normal;
}


/*
 * Sort arrow icon positioning
 */
table.display thead th div.DataTables_sort_wrapper {
  position: relative;
  padding-right: 20px;
  padding-right: 20px;
}

table.display thead th div.DataTables_sort_wrapper span {
  position: absolute;
  top: 50%;
  margin-top: -8px;
  right: 0;
}
.dataTables_wrapper {
  position: relative;
  clear: both;
}

.dataTables_processing {
  position: absolute;
  top: 0px;
  left: 50%;
  width: 250px;
  margin-left: -125px;
  border: 1px solid #ddd;
  text-align: center;
  color: #999;
  font-size: 11px;
  padding: 2px 0;
}

.dataTables_length {
  width: 40%;
  float: left;
}

.dataTables_filter {
  width: 50%;
  float: right;
  text-align: right;
}

.dataTables_info {
  width: 50%;
  float: left;
}

.dataTables_paginate {
  float: right;
  text-align: right;
}

/* Pagination nested */
.paginate_disabled_previous, .paginate_enabled_previous, .paginate_disabled_next, .paginate_enabled_next {
  height: 19px;
  width: 19px;
  margin-left: 3px;
  float: left;
}
table.display {
  margin: 0 auto;
  width: 100%;
  clear: both;
  border-collapse: collapse;
}

table.display tfoot th {
  padding: 3px 0px 3px 10px;
  font-weight: bold;
  font-weight: normal;
}

table.display tr.heading2 td {
  border-bottom: 1px solid #aaa;
}

table.display td {
  padding: 3px 10px;
}

table.display td.center {
  text-align: center;
}


.ui-widget-overlay {
  background: #000;
  opacity: .6;
  -moz-opacity: 0.6;
  filter: alpha(opacity=60);
}

.search_init {
  color: #bbb;
}

Advertisement