NAME

DBIx::SQLCrosstab::Format - Formats results created by DBIx::SQLCrosstab

SYNOPSIS

use DBIx::SQLCrosstab::Format;
my $dbh=DBI->connect("dbi:driver:database"
    "user","password", {RaiseError=>1})
        or die "error in connection $DBI::errstr\n";

my $params = {
    dbh    => $dbh,
    op     => [ [ 'SUM', 'salary'] ],
    from   => 'person INNER JOIN departments USING (dept_id)',
    rows   => [
                { col => 'country'},
              ],
    cols   => [
                {
                   id => 'dept',
                   value =>'department',
                   from =>'departments'
                },
                {
                    id => 'gender', from => 'person'
                }
              ]
};
my $xtab = DBIx::SQLCrosstab::Format->new($params)
    or die "error in creation ($DBIx::SQLCrosstab::errstr)\n";

my $query = $xtab->get_query("#")
    or die "error in query building $DBIx::SQLCrosstab::errstr\n";

if ( $xtab->get_recs) {
    # do something with records, or use a built-in function
    # to produce a well formatted HTML table
    #
    print $xtab->as_html;

    print $xtab->as_xml;
    print $xtab->as_yaml;
    print $xtab->as_csv('header');
    $xtab->as_xls("xtab.xls");
    use Data::Dumper;
    print Data::Dumper->Dump ([ $xtab->as_perl_struct('hoh')],
            ['hoh']);
    print Data::Dumper->Dump ([ $xtab->as_perl_struct('losh')],
            ['losh']);
    print Data::Dumper->Dump ([ $xtab->as_perl_struct('loh')],
            ['loh']);
}
else {
    die "error in execution $DBIx::SQLCrosstab::errstr\n";
}

DESCRIPTION

DBIx::SQLCrosstab::Format is a class descending from DBIx::SQLCrosstab. Being a child class, it inherits its parent methods and can be used in the same way.

In addition, it provides methods to produce formatted output.

Class methods

new

get_recs

get_query

See DBIx::SQLCrosstab docs for usage and a detailed list of parameters

as_html

Returns a formatted HTML table with headers and values properly inserted, or undef on failure.

as_xml

Returns an XML document containing the whole recordset properly tagged in tree format, or undef on failure.

as_xls($filename)

Creates a MS Excel spreadsheet using Spreadsheet::WriteExcel. Requires a filename (or "-" for stdout).

as_perl_struct($mode)

Returns the recordset as a Perl structure. $mode is one of the following: - lol List of lists - losh List of simple hashes (one key per column) - loh List of hashes, tree-like, with an appropriate tree for each row - hoh Hash of hashes. The resultset as a tree (useful to pass to either XML::Simple or YAML)

as_yaml

Returns the recordset in YAML format. You must have YAML installed for this method to work.

as_csv($headers)

Returns a text of Comma Separated Values, where each value is surronded by double quotes (text) or bare (numbers). If a true value is passed as $header parameter, the first row contains the list of column names, properly quoted and escaped.

Class attributes

In addition to the attributes available in DBIx::SQLCrosstab, the folowing ones become available in this class. They may be useful if you want to implement your own output methods.

Extending DBIx::SQLCrosstab::Format

The appropriate way of extending this class is through inheritance. Just create a descendant of DBIx::SQLCrosstab::Format and implement your new methods. The attributes with the relevant information become available after a call to the private method _find_headers().

The path to extension is something like the following.

First, create a new module:

package DBIx::SQLCrosstab::Format::Extended;
use DBI;
use DBIx::SQLCrosstab;

our $VERSION = '0.1';
require Exporter;
our @ISA= qw(DBIx::SQLCrosstab::Format);
our @EXPORT=qw();
our @EXPORT_OK=qw();

sub as_myformat {
   my $self = shift;
   return undef unless $self->_find_headers();
   my $new_format = 
   do_something_smart_with($self->{recs_tree},
                     $self->{header_formats});
   return $new_format;
}

sub do_something_smart_with {
   my $recs_tree = shift;
   my $header_formats = shift;
   # show off your skills here
}

1;

Then, use the new module as you would use the parent one.

use DBIx::SQLCrosstab::Format::Extended;
my $dbh=DBI->connect("dbi:driver:database"
    "user","password", {RaiseError=>1})
        or die "error in connection $DBI::errstr\n";

my $xtab = DBIx::SQLCrosstab::Format::Extended->new($params)
    or die "error in creation ($DBIx::SQLCrosstab::errstr)\n";

my $query = $xtab->get_query("#")
    or die "error in query building $DBIx::SQLCrosstab::errstr\n";

if ( $xtab->get_recs) {
    print $xtab->as_myformat;
}

{header_formats}

Contains a reference to an array of arrays, one for each level of headers. Each cell is described with a hash containig name, colspan and rowspan values. Available after a call to _find_headers().

{recs_formats}

Contains a refernce to a hash descrbing the structure of the row level. Each level contains a list of fields and relative rowspans. Available after a call to _find_headers().

{recs_tree}

Contains a Tree::DAG_Node object with the structure of the column headers. Available after a call to _find_headers().

{header_tree}

Contains a Tree::DAG_Node object with the structure of the row headers. Available after a call to _find_headers().

SEE ALSO

DBIx::SQLCrosstab

An article at OnLamp, "Generating Database Server-Side Cross Tabulations" (http://www.onlamp.com/pub/a/onlamp/2003/12/04/crosstabs.html) and one at PerlMonks, "SQL Crosstab, a hell of a DBI idiom" (http://www.perlmonks.org/index.pl?node_id=313934).