DataTables logo DataTables

This is a legacy documentation page and the methods described may not work with DataTables 1.10+.
Please see the upgrade notes for 1.10.

Server-side processing | Ruby (CGI) with MySQL

This is an example implementation of a server-side script which can be used with a Ruby scripting environment. It is configured as a CGI script (so remember to chmod 755 etc) but could readily be modified to be used in a Ruby scripting framework.

To use the code on your own server, simply change the $columns array to list the columns you wish to include from your database, set $indexColumn to a column which is indexed (for speed), $table to the table name, and finally fill in your database connection parameters to $databaseInfo. You may also have to change the #! line to point towards your Ruby engine.

Feature set

Code

#!/usr/bin/ruby -w

# Script:    DataTables server-side script for PHP and MySQL
# Copyright: 2010 - Allan Jardine
# License:   GPL v2 or BSD (3-point)

# Modules
require 'rubygems'
require 'cgi'
require 'active_record/vendor/mysql.rb'
#require "mysql"

# CGI header
puts "Content-Type: text/html\n\n"


# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
# Easy set varaibles
#

# Array of database columns which should be read and sent back to DataTables
$columns = [ 'engine', 'browser', 'platform', 'version', 'grade' ]

# Indexed column (used for fast and accurate table cardinality)
$indexColumn = "id"

# DB table to use
$table = "ajax"

# Database connection information
$databaseInfo = Hash[
  "host" => "",
  "user" => "",
  "pass" => "",
  "db"   => ""
]


# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
# If you just want to use the basic configuration for DataTables with PHP server-side, there is
# no need to edit below this line
#

class DataTablesServer
  @query = ""
  @json = ""
  @cgi = nil
  @dbh = nil
  
  def initialize()
    @cgi = CGI.new
    @dbh = Mysql.real_connect($databaseInfo['host'], $databaseInfo['user'], $databaseInfo['pass'],
      $databaseInfo['db'] )
    
    # Create the SQL queries
    limit = paging()
    order = ordering()
    filter = filtering()
    
    # Get the data
    query = "
		  SELECT SQL_CALC_FOUND_ROWS #{$columns.join(', ')}
		  FROM   #{table}
		  #{filter}
		  #{order}
		  #{limit}
    "
    resultData = @dbh.query( query );
    
    # Calculate number of rows in data set - after filtering
    query = "
      SELECT FOUND_ROWS()
    "
    cadinalityFiltered = @dbh.query( query ).fetch_row[0]
    
    # Calculate number of rows in data set without filtering
    query = "
      SELECT COUNT(#{$indexColumn})
      FROM #{table}
    "
    cadinality = @dbh.query( query ).fetch_row[0]
    
    # Output
    output = '{'
    output += '"sEcho": '+@cgi['sEcho'].to_i.to_s+', '
    output += '"iTotalRecords": '+cadinality+', '
    output += '"iTotalDisplayRecords": '+cadinalityFiltered+', '
    output += '"aaData": [ '
    
    while row = resultData.fetch_hash do
      output += '['
      for i in (0..$columns.length-1)
        if $columns[i] != "version"
          output += '"'+row[ $columns[i] ].escape_single_quotes+'",'
        else
          output += (row[ $columns[i] ]=="0") ?
            '"-",' :
            '"'+row[ $columns[i] ]+'",'
        end
      end
      output = output.chop
      output += '],'
    end
    
    output = output.chop
    output += '] }';
    
    puts output
  end
  
  
  #
  # filtering
  # Create the 'WHERE' part of the SQL string
  #
  def filtering()
    filter = ""
    if @cgi['sSearch'] != ""
      filter = "WHERE "
      for i in (0..$columns.length-1)
        filter += "#{$columns[i]} LIKE '%#{@cgi['sSearch']}%' OR "
      end
      filter = filter.chop.chop.chop
    end
    return filter
  end
  
  
  #
  # ordering
  # Create the 'ORDER BY' part of the SQL string
  #
  def ordering()
    order = ""
    if @cgi['iSortCol_0'] != "" && @cgi['iSortingCols'].to_i > 0
      order = "ORDER BY  "
      for i in (0..@cgi['iSortingCols'].to_i-1)
        order += "#{$columns[ @cgi['iSortCol_'+i.to_s].to_i ]} "+
          "#{@dbh.escape_string( @cgi['sSortDir_'+i.to_s] )}, "
      end
    end
    
    return order.chop.chop
  end
  
  
  #
  # paging
  # Create the 'LIMIT' part of the SQL string
  #
  def paging()
    @limit = ""
    if @cgi['iDisplayStart'] != "" && @cgi['iDisplayLength'] != -1
      @limit = "LIMIT #{@dbh.escape_string( @cgi['iDisplayStart'] )}, "+
        "#{@dbh.escape_string( @cgi['iDisplayLength'] )}"
    end
    return @limit
  end
  
  def sendOutput()
    
  end
end

# Add an escape method to string
class String
  def escape_single_quotes
    self.gsub(/'/, "\\\\'")
  end
end

# Perform the server-side actions for DataTables
dtserver=DataTablesServer.new()
dtserver.sendOutput()