Adam Hopkins

NAME

DataTables - a server-side solution for the jQuery DataTables plugin

SYNOPSIS

  use DataTables;
  my $dt = DataTables->new(user=>'user',pass=>'pass',db=>'db'); #set inital values to connect to db

  #set table to select from
  $dt->tables(["dinosaurs"]);

  #set columns to select in same order as order of columns on page
  $dt->columns(["height","size","lovability"]);                  

  #print json back to browser
  $dt->print_json;                                               

  #if you wish to do something with the json yourself
  my $json = $dt->json;                                        

  # EXAMPLE WITH JOINS

  # Assume the following two tables:
  
  ----------        ------------
  |  pets  |        |  owners  |
  ----------        ------------
   id               id
   name             name
   owner_id         

  # Now we will join the tables on owners.id=pets.owner_id

  # the first key is a number because
  # order must be kept to match column order
  my %columns = (
                   0=>{"name"=>"owners"},
                   1=>{"name"=>"pets", AS=>"pet_name"}, # renaming isn't necessary here, unless you wish to use patterns
                );

  my %index_cols = ( 
                        "pets"=>"id",
                        "owners"=>"id",
                   );

  $dt->columns(\%columns);
  $dt->index_cols(\%index_cols); #Not necessary to set index columns
                                 #since both index columns are id and this is what
                                 #DataTables defaults to

  $dt->join_clause("owners.id=pets.owner_id");

  $dt->print_json;

  # Assume in the example above we know that all pets love scooby snacks, and we'd like to represent
  # that in our output. We can do that like so:
  my %patterns = ( 
                      "pet_name"=>"[% pet_name %] loves scooby snacks!", 
                 );
                 # notice if we didn't rename pets.name as "pet_name" in the example above
                 # and we had used name for both owners and pets, both
                 # columns would receive this pattern

  $dt->patterns(\%patterns);

  $dt->print_json;

  # A more realistic example might be putting a '$' before a money value,
  # but personally I find the scooby snacks example more useful

  # NOTE: Any getter/setter method can be set initially when creating the DataTables object can be passed into new()

DESCRIPTION

This module is an easy way to integrate server-side with the jQuery DataTables plugin. It supports basic features like displaying columns from a single table, but also supports more advanced features such as:

  1. Selecting columns from multiple tables via join

  2. Formatting output of returned columns

  3. Adding extra conditions to the where and join clauses

  4. Uses DBI place holders to help prevent SQL injection

METHODS

new(...)

Creates and returns a new DataTables object.

    my $dt = DataTables->new();

DataTables has options that allow you to receive your data exactly as you want it. Any of the method names below can be used in your new declaration to help initialize your object.

Here is an explicit list of all of the options and their defaults:

    tables  => undef,
    columns   => undef,
    user  => undef,
    pass  => undef,
    db  => undef,
    host  => "localhost",
    port  => "3306",
    patterns  => {},
    join_clause  => '',
    where_clause  => '',
    index_col  => "id",
    index_cols => undef,

tables

    $dt->tables(["table1","table2"]);

This method allows you to set which tables you want to select from in your SQL query. However, if you set "columns" to a hashref and not an arrayref, there is no need to set "tables".

columns

Columns can take in an arrayref or a hashref.

Arrayref:

    $dt->columns(["column1","column2"]);

OR if you are joining among multiple tables:

    $dt->columns(["table1.column1","table2.column2"]);

Keep in mind that if you use the patterns feature of this module, the column name given as the key of the pattern must match what you call the column here. So if you put "table1.column1", you must identify the pattern with that name. Also, you should list the columns in the order that they are on your page.

Hashref:

    my %columns = (
                        0=>{"column1"=>"table1"},
                        1=>{"column2"=>"table1"},
                           2=>{"column3"=>"table2", AS=>"new_col"},
                    );
    $dt->columns(\%columns);

Here the numbers are necessary because hashes don't keep order, so you must use numbers in order to specify the ordering of the columns on the page. The AS key allows you to specify what you would like to call that column (this relates to the SQL "AS" feature). This could be useful if you wanted to use the patterns feature of this module and had two columns in different tables named the same thing, because then they would both get the pattern! Also, if you provide a hashref for "columns", there is no need to supply the tables; DataTables will figure that out for you.

index_cols

    $dt->index_cols({"table1"=>"index_col1","table2"=>"index_col2"});

Here you need to provide the indexed columns for all of the tables you are selecting from. However, if you do not provide an indexed column for a table, DataTables will default to using "id". If you are only selecting from one table, you can just use "index_col".

index_col

    $dt->index_col("id");

You can use this to set your indexed column if you are only selecting from one table. It defaults to "id".

patterns

    $dt->patterns({"column1"=>"[% column1 %] rocks!"});

This method sets the patterns that you want to use for particular columns. You identify the pattern by using the column as a key, and then specify where in your pattern you would like the value to go by placing the name of the column between "[% %]". The name of the column must be the name that you specified in "columns". If you used a hashref in columns and specified the "AS" key, then you must use the value for that "AS" key.

user

    $dt->user("user");

Sets the user for the database.

pass

    $dt->pass("password");

Sets the password for the database.

db

    $dt->db("database");

Sets the database to use.

host

    $dt->host("localhost");

Sets the host to connet to for the database. Defaults to localhost.

port

    $dt->port("3306");

Sets the port to connect on for the database. Defaults to 3306.

join_clause

    $dt->join_clause("table1.id=table2.table1_id");

This lets you specify the condition that you want to join on if you are joining multiple tables. You can extend it with AND's and OR's if you wish.

where_clause

    $dt->where_clause("account_id=5");

This lets you specify extra conditions for the where clause, if you feel you need to specify more than what DataTables already does.

    $dt->print_json();

I recommend using this method to display the information back to the browser once you've set up the DataTables object. It not only prints the json out, but also takes care of printing the content-type header back to the browser.

json

    my $json = $dt->json();
    print "Content-type: application/json\n\n";
    print $json;

The json() method returns the json to you that the jQuery DataTables plugin is expecting. What I wrote above is essentially what the print_json() method does, so I suggest that you just use that.

REQUIRES

1 DBI
2 JSON::XS
3 CGI

EXPORT

This module has no exportable functions.

ERRORS

If there is an error, it will not be reported client side. You will have to check your web server logs to see what went wrong.

SEE ALSO

DataTables jQuery Plugin

AUTHOR

Adam Hopkins <lt>srchulo@cpan.org<gt>

COPYRIGHT AND LICENSE

Copyright (C) 2012 by Adam Hopkins

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.