DataTables logo DataTables

This site contains the legacy documentation for DataTables v1.9 and earlier for reference only.
DataTables 1.9 was End Of Life in 2014. Do not use it for new work.
The current release of DataTables can always be found on DataTables.net.

Server-side processing | Perl (CGI::Application) with MySQL

Important - Do not use this script for new development - it will not work! It is no longer maintained and does not support DataTables 1.10+'s server-side processing mode. This page is retained only for legacy users and it will likely be removed in the near future.

Alexander Becker has done a great job with this Perl script, and as kindly made it available to the community. The set up for integration with DataTables is broken into multiple different sections, each of which is well commented, noting where changes are required for using with your own database.

Feature set

Initialisation code

The Perl script requires an extra parameter to be sent to it (rm), for which we can use fnServerData:

$(document).ready(function() {
	$('#example').dataTable( {
		"bProcessing": true,
		"bServerSide": true,
		"sAjaxSource": "/cgi-bin/test/datatables/run.cgi",
		"fnServerData": function ( sSource, aoData, fnCallback, oSettings ) {
			/* Add some extra data to the sender */
			aoData.push( { "name": "rm", "value": "table_data" } );
			$.getJSON( sSource, aoData, function (json) { 
				/**
				 * Insert an extra argument to the request: rm.
				 * It's the the name of the CGI form parameter that
				 * contains the run mode name. Its value is the
				 * runmode, that produces the json output for
				 * datatables.
				 **/
				fnCallback(json)
			} );
		}
	} );
} );

Configuration file

This project uses a configuration file to store the connection information for the DBMS. Below is an example configuration file for CGI::Application::Plugin::ConfigAuto. It's intended to look like a Perl script.

#!/usr/bin/perl

use strict;
use warnings;

my %CFG = (
	db => {
		dsn => 'DBI:mysql:test:localhost',
		username => 'test',
		password => 'test',
		attributes => {
			RaiseError => 1,
			AutoCommit => 1,
		},
	}
);

\%CFG;

Server side (Perl) code

This is the server-side processing script. As it is a cgi executable, it has to be located in the cgi-executables directory of the webserver. Also, the shebang has to match the local perl installation. On unix like OS, the file should be chmodded as executable (chmod 755) and be owned by whoever is running the webserver.

This is an example using the MVC framework CGI::Application and SQL::Abstract, a library to generate SQL from Perl data structures.

#!/usr/bin/perl

=head1 COPYRIGHT

Script:    DataTables server-side script for Perl and MySQL
Copyright: 2010 - Alexander Becker
License:   This library is free software; you can redistribute it and/or modify 
             it under the same terms as Perl itself, either Perl version 5.8.8 or, 
             at your option, any later version of Perl 5 you may have available.

=cut

package DataTablesServer;

use strict;
use warnings;
use 5.010;
use base qw/CGI::Application/;

use CGI::Application::Plugin::ConfigAuto (qw/cfg/);
use CGI::Application::Plugin::JSON 'to_json';
use CGI::Application::Plugin::DBH (qw/dbh_config dbh/);
use SQL::Abstract::Limit;
use Data::Dumper qw/Dumper/;

our $VERSION = 0.2;

=head1 NAME

DataTablesServer - Server-side processing implementation for DataTables

=head1 SYNOPSIS

  use strict;
  use warnings;
  use FindBin qw/$Bin/;
  
  my $app = DataTablesServer->new(
  	PARAMS => {
  		cfg_file => $Bin . '/config/test.config',
  	},
  );
  $app->run();

=head1 DESCRIPTION

This is a demo package implementing a tiny CGI script, that provides json data
for the jQuery plugin DataTables. This is what you specify as "sAjaxSource".

=head1 METHODS

=cut

=head2 cgiapp_init()

Open database connection, setup config files, etc.

=cut

sub cgiapp_init {
	my $self = shift;
	
	# Set some defaults for DFV unless they already exist.  
	$self->param('dfv_defaults') ||
        $self->param('dfv_defaults', {
                missing_optional_valid => 1,
                filters => 'trim',
                msgs => {
                    any_errors => 'some_errors',
                    prefix     => 'err_',
                    invalid    => 'Invalid',
                    missing    => 'Missing',
                    format => '<span class="dfv-errors">%s</span>',
                },
        });
	
	# -- set up database
	my $db = $self->cfg('db') or die('Missing config param: db');
	$self->dbh_config($db->{dsn}, $db->{username}, $db->{password}, $db->{attributes});
	
} # /cgiapp_init




=head2 setup()

Defined runmodes. We have exactely 1 runmode, that provides the json data for
the table. See table_data() for how this is done.

=cut

sub setup {
	my $self = shift;
	
	$self->start_mode('table_data');
	$self->run_modes([qw/
		table_data
	/]);
	
} # /setup




=head2 table_data()

Return table content as json. Evaluates query for global filtering and
ordering information. The database is queried to collect the data.

=cut

sub table_data {
	my $self = shift;
	my $q = $self->query();


	# -- Define the name of the table that holds the data.
	my $table = 'ajax';
	
	# -- Define the column names of the table. These names have to match the
	# names of the columns in $table.
	my @fields = qw/engine browser platform version grade/;
	
	# -- Paging
	my $limit = $q->param('iDisplayLength') || 10;
	my $offset = 0;
	if( $q->param('iDisplayStart') ) {
		$offset = $q->param('iDisplayStart');
	}
	
	# -- Ordering
	my @order = $self->_generate_order_clause();

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


	# -- get table contents
	my @aaData = $self->_get_table_content( $table, \@fields, \%where, \@order, $limit, $offset );


	# -- get meta information about the resultset
	my $iFilteredTotal = $self->_get_filtered_total( $table, \@fields, \%where );
	my $iTotal = $self->_get_total_record_count( $table );


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

	return $self->to_json(\%sOutput);
} # /table_data




=head2 _generate_order_clause()

Evaluate query for odering information. If present, generate order clause, if
not, returns empty order clause.

=cut

sub _generate_order_clause {
	my $self = shift;
	my $q = $self->query();
	
	my @order = ();
	
	if( defined $q->param('iSortCol_0') ){
		for( my $i = 0; $i < $q->param('iSortingCols'); $i++ ) {
			# build direction, must be '-asc' or '-desc' (cf. SQL::Abstract)
			# we only get 'asc' or 'desc', so they have to be prefixed with '-'
			my $direction = '-' . $q->param('sSortDir_'.$i);
			
			# We only get the column index (starting from 0), so we have to
			# translate the index into a column name.
			my $column_name = $self->_fnColumnToField( $q->param('iSortCol_'.$i) );
			push @order, { $direction => $column_name };
		}
	}
	
	return @order;
} # /_generate_order_clause




=head2 _generate_where_clause()

Evaluate global search information, that is, information by which each field
has to be restricted. If present, generate matching conditions for each
searchable column (searchability indicated by query parameters) and combine
as disjunction (OR).

NOTE this does not match the built-in DataTables filtering which does it
word by word on any field. It's possible to do here, but concerned about
efficiency on very large tables, and MySQL's regex functionality is very
limited.

=cut

sub _generate_where_clause {
	my $self = shift;
	my $q = $self->query();
	
	my %where = ();
	
	if( defined $q->param('sSearch') ) {
		my $search_string = $q->param('sSearch');
		for( my $i = 0; $i < $q->param('iColumns'); $i++ ) {
			# Iterate over each column and check if it is searchable.
			# If so, add a constraint to the where clause restricting the given
			# column. In the query, the column is identified by it's index, we
			# need to translates the index to the column name.
			my $searchable_ident = 'bSearchable_'.$i;
			if( $q->param($searchable_ident) and $q->param($searchable_ident) eq 'true' ) {
				my $column = $self->_fnColumnToField( $i );
				push @{$where{'-or'}}, { $column => {-like => '%'.$search_string.'%' } };
			}
		}
	}
	
	return %where;
} # /_generate_where_clause




=head2 _fnColumnToField( $i )

Maps a number to a column name. Column names are a priori knowlegde
(= hardcoded).

$i might be 0.

=cut

sub _fnColumnToField {
	my $self = shift;
	my $i = shift // die("Missing column index i.");
	
	# Note: we could have used an array, but for dispatching purposes, this is
	# more readable.
	my %dispatcher = (
		# column => 'rowname',
		0 => 'engine',
		1 => 'browser',
		2 => 'platform',
		3 => 'version',
		4 => 'grade',
	);
	
	die("No such row index defined.") unless exists $dispatcher{$i};

	return $dispatcher{$i};
} # /_fnColumnToField




=head2 _get_table_content( $table, \@fields, \%where, \@order, $limit, $offset )

This method returns the data, that will be displayed in the table. Might be an
empty array. If there exist results, return value will be a two-dimensonal
array.

Basically, this method builds the SQL and fetches the results.

=cut

sub _get_table_content {
	my $self = shift;
	my $table = shift or die("Missing table.");
	my $fields_aref = shift or die("Missing fields.");
	my $where_href = shift or die("Missing where clause (href).");
	my $order_href = shift or die("Missing order clause (href).");
	my $limit = shift // die("Missing limit");
	my $offset = shift // die("Missing offset");

	my $q = $self->query();
	my $dbh = $self->dbh();

	my $sql = SQL::Abstract::Limit->new( limit_dialect => $dbh );
	my ( $stmt, @bind ) = $sql->select( $table, $fields_aref, $where_href, $order_href, $limit, $offset );
	
	my $sth = $dbh->prepare($stmt) or die("Error preparing sql: " . DBI->errstr() . "\nSQL: $sql\n");
    my $rv = $sth->execute(@bind) or die("Error executing sql: " . DBI->errstr() . "\nSQL: $sql\nBind: @bind");

	my @aaData = ();
	while( my @a = $sth->fetchrow_array() ) {
		push @aaData, \@a;
	}

	$sth->finish(); # clean up
	
	return @aaData;
} # /_get_table_content




=head2 _get_total_record_count( $table )

Get the number of records in the table, regardless of restrictions of the
where clause or the limit clause. Used to display the total number of records
without applied filters.

=cut

sub _get_total_record_count {
	my $self = shift;
	my $table = shift or die("Missing table name.");
	
	my $dbh = $self->dbh();
	my $sql = qq~SELECT COUNT(engine) AS count FROM $table~;
	my $sth = $dbh->prepare($sql) or die("Error preparing sql: " . DBI->errstr() . "\nSQL: $sql\n");
	my $rv = $sth->execute() or die("Error executing sql: " . DBI->errstr() . "\nSQL: $sql\n");
	
	my $cnt = -1;
	while( my $href = $sth->fetchrow_hashref() ) {
		$cnt = $href->{count};
	}
	
	return $cnt;
} # /_get_total_record_count




=head2 _get_filtered_total( $table, \@fields, \%where )

Get the total number of filtered records (in resprect of filters by the where
clause, without limit). This accounts for the "search" field of data tables.


=cut

sub _get_filtered_total {
	my $self = shift;
	my $table = shift or die("Missing table.");
	my $fields_aref = shift or die("Missing fields.");
	my $where_href = shift or die("Missing where clause (href).");
	
	my $dbh = $self->dbh();
	my $sql = SQL::Abstract::Limit->new( limit_dialect => $dbh );
	my ( $stmt, @bind ) = $sql->select( $table, $fields_aref, $where_href );
	
	my $sth = $dbh->prepare($stmt) or die("Error preparing sql: " . DBI->errstr() . "\nSQL: $sql\n");
	my $rv = $sth->execute(@bind) or die("Error executing sql: " . DBI->errstr() . "\nSQL: $sql\nBind: @bind");
	
	return $rv;
} # /_get_filtered_total



=head1 SEE ALSO

DataTables, a great jQuery plugin for table data: L<http://www.datatables.net>

The german perl community: L<http://de.perl.org> (they speak english, too)

L<CGI::Application>, L<SQL::Abstract>, L<ConfigAuto>

=head1 AUTHOR

Alexander Becker, E<lt>c a p f a n @ g m x . d eE<gt>

=head1 COPYRIGHT AND LICENSE

Copyright (C) 2009 by Alexander Becker

This library is free software; you can redistribute it and/or modify it under
the same terms as Perl itself, either Perl version 5.8.8 or, at your option,
any later version of Perl 5 you may have available.

=cut

1;

use strict;
use warnings;
use FindBin qw/$Bin/;

my $app = DataTablesServer->new(
	PARAMS => {
		cfg_file => $Bin . '/config/test.config',
	},
);
$app->run();