/ 
DBIx-XHTML_Table-0.80
River stage zero No dependents
/ XHTML_Table

NAME

DBIx::XHTML_Table - Create XHTML tables from SQL queries

SYNOPSIS

  use DBIx::XHTML_Table;

  my $table = XHTML_Table->new(
    $datasource, $username, $password
  ) || die "could not connect to database\n";

  $table->exec_query("
	SELECT TITLE,ALBUM,YEAR,GENRE
	FROM MP3.SONGS
	WHERE YEAR=? AND GENRE=?
	ORDER BY ARTIST
  ",[$year,$genre]);

  # save a raw copy for later
  my $raw = $table->get_table;

  # start tweaking the table
  $table->modify_tag('TABLE',{
	  border      => 1,
	  cellspacing => 0,
  });

  # modify all <THEAD> <TH> tags
  $table->modify_tag('TH',{
	  bgcolor => 'black',
	  style   => 'Color: white;',
  }, 'header');

  # modify only <TD> tags for TITLE column
  $table->modify_tag('TD',{
	  align   => 'right',	# although align is deprecated
	  bgcolor => '#ABACAB',
  },'title');

  # modify only <TD> tags for ALBUM and YEAR columns
  # columns -  in this case, each column will permuate
  # the colors and the alignment value
  $table->modify_tag('TD',{
	  width   => 200,
	  align   => [qw(left center right)],
	  bgcolor => [qw(green blue red)],
  },[qw(album year)]);

  # impress the boss with rotating row colors
  # (this would override the above method)
  $table->set_row_colors(
	["#D0D0D0", "#B0B0B0")],
	[qw(album year)],
  );

  # suppress duplicates on a group
  $table->set_group('album',1);

  # since we can, sum up the years column
  # totals will be wrapped in <TH>'s belonging to <TFOOT/>
  # optional sprintf mask can be used for formatting
  $table->calc_totals('year','%.02f');

  # and if you have a well behaved group set . . .
  $table->calc_subtotals('year','%.02f');

  # print out the complete table
  print $table->get_table;

  # compare it with the raw version
  print $raw;

DESCRIPTION

XHTML_Table will execute SQL queries and return the results wrapped in XHTML tags. Methods are provided for determining which tags to use and what their attributes will be. Tags such as <TABLE>, <TR>, <TH>, and <TD> will be automatically generated, you just have to specify what attributes they will use.

This module was created to fill a need for a quick and easy way to create on the fly XHTML tables from SQL queries, for the purpose of 'quick and dirty' reporting. If you find yourself needing more power over the display of your report, you should look into templating methods such as HTML::Template or Template-Toolkit. Another viable substitution for this module is to use DBIx::XML_RDB and XSL stylesheets, but some browsers are still not XML compliant, and XHTML_Table has the advantage of displaying at least something on browsers that are not XML or XHTML compliant. At the worst, only the XHTML tags will be ignored, and not the content of the report.

The user is highly recommened to become familiar with the rules and structure of the new XHTML tags used for tables. A good, terse reference can be found at http://www.w3.org/TR/REC-html40/struct/tables.html

Additionally, a simple TUTORIAL is included in this documentation toward the end, just before the third door, down the hall, past the chickens and through a small gutter (just keep scrolling down).

CONSTRUCTOR

style 1
$obj_ref = new DBIx::XHTML_Table($dsource,$usr,$passwd)

Construct a new XHTML_Table object by supplying the database connect information: datasource, user, password:

my $table = new XHTML_Table($dsource,$usr,$passwd) || die;

The constuctor will simply pass the arguments to the connect() method from DBI.pm - see DBI as well as the one for your corresponding DBI driver module - DBD::Oracle, DBD::Sysbase, DBD::mysql, etc.

style 2
$obj_ref = new DBIx::XHTML_Table($DBH)

The previous signature will result in the database handle being created and destroyed behind the scenes. If you need to keep the database connection open, create one yourself and pass it to the constructor:

my $DBH   = DBI->connect($dsource,$usr,$passwd) || die;
my $table = new XHTML_Table($DBH);
  # do stuff
$DBH->disconnect;

OBJECT METHODS

exec_query
$table->exec_query($sql,[$bind_vars])

Pass the query off to the database with hopes of data being returned. The first argument is scalar that contains the SQL code, the second argument can either be a scalar for one bind variable or an array reference for multiple bind vars:

  $table->exec_query("
      SELECT BAR,BAZ FROM FOO
	  WHERE BAR = ?
	  AND   BAZ = ?
  ",[$foo,$bar]);

Consult DBI for more details on bind vars.

After the query successfully exectutes, the results will be stored interally as a 2-D array. The XHTML table tags will not be generated until get_table is invoked, and the results can be modified via map_column().

get_table
$table->get_table($sans_title,$sans_whitespace)

Creates and returns the XHTML table. The first argument is a non-zero, defined value that suppresses the column titles. The column footers can be suppressed by not calculating totals, and the body can be suppressed by an appropriate SQL query. The caption and colgroup cols can be suppressed by not modifying them. The column titles are the only part that has to be specifically told not to generate, and this is where you do that.

print $table->get_table;      # produces titles by default
print $table->get_table(1);   # does not produce titles

The second argument is another non-zero, defined value that will result in the output having no text aligning whitespace, that is no newline(\n) and tab(\t) charatcters.

modify_tag
$table->modify_tag($tag,$args,[$cols])

This method will store a 'memo' of what attributes you have assigned to various tags within the table. When the table is rendered, any memos will be used to create attributes. The first argument is the name of the tag you wish to modify the attributes of. You can supply any tag name you want without fear of halting the program, but the only tag names that are handled are <TABLE> <CAPTION> <THEAD> <TFOOT> <TBODY> <COLGROUP> <COL> <TR> <TH> and <TD>. The tag name will be converted to uppercase, so you can use any case you want.

The next argument is a reference to a hash that contains the attributes you wish to apply to the tag. For example, this set attributes for the <TABLE> tag:

$table->modify_tag('table',{
    border => 2,
    width  => '100%',
    foo    => 'bar',
}),

Each key in the hash will be uppercased, and each value will be surrounded in quotes. The foo=>bar entry illustrates that typos in attribute names will not be caught by this module. Any valid XHTML attribute can be used. Yes. Even JavaScript.

You can even use an array reference as the value for one of the keys:

$table->modify_tag('td',{
    bgcolor => [qw(red purple blue green yellow orange)],
}),

Now each <TD> tag will get a color from the list, one at time. When the last index is reached (orange), the next <TD> tag will get the first index (red), continuing just like a circular queue until no more <TD> tags are left.

This feature changes attributes in a horizontal fasion, each new element is popped from the array every time a <TD> tag is created for output. Use set_row_color when you need to change colors in a vertical fashion. Unfortunately, no method exists to allow other attributes besides bgcolor to permutate in a vertical fashion.

The last argument is optional and can either be a scalar representing a single column or area, or an array reference containing multilple columns or areas. The columns will be the corresponding names of the columns from the SQL query. The areas are one of three values: HEADER, BODY, or FOOTER. The columns and areas you specify are case insensitive.

# just modify the titles
$table->modify_tag('TH',{
    bgcolor => '#bacaba',
},'header');

You cannot currently mix areas and columns.

If the last argument is not supplied, then the attributes will be applied to the entire table via a global memo. However, entries in the global memo are only used if no memos for that column or area have been set:

# all <TD> tags will be set
$table->modify_tag('TD',{
    class => 'foo',
});

# except those for column BAR
$table->modify_tag('TD',{
    class => 'bar',
},'bar');

The order of the execution of the previous two methods calls is commutative - it doesn't matter.

The value of an attribute is not limited to a single scalar. If you specify an array reference of values, the values will be permutated across the tags. This feature is really meant to be used by <TD> tags in the BODY area, but other tags may produce interesting results.

A final caveate is setting the <CAPTION> tag. This one breaks the signature convention:

$table->modfify_tag('CAPTION', $value, $atr);

Since there is only one <CAPTION> allowed in an XHTML table, there is no reason to bind it to a column or an area:

# with attributes
$table->modify_tag('caption','A Table Of Contents',{
    class => 'body',
});

# without attributes
$table->modify_tag('caption','A Table Of Contents');

The only tag that cannot be modified by this method is the <COL> tag. Use add_colgroup to add these tags instead.

add_colgroup
$table->add_colgroup($cols)

Add a new <COL> tag and attributes. The only argument is reference to a hash that contains the attributes for this <COL> tag. Multiple <COL> tags require multiple calls to this method. The <COLGROUP> tag pair will be automagically generated if at least one <COL> tag is added.

Advice: use <COL> and <COLGROUP> tags wisely, don't do this:

# bad
for (0..39) {
  $table->add_colgroup({
      foo => 'bar',
  });
}

When this will suffice:

# good
$table->modify_tag('colgroup',{
    span => 40,
    foo  => 'bar',
});

You should also consider using <COL> tags to set the attributes of <TD> and <TH> instead of the <TD> and <TH> tags themselves, especially if it is for the entire table:

  $table->add_colgroup({
      span  => $table->get_col_count(),
	  class => 'body',
  });
map_col
$table->map_col($subroutine,[$cols])

Map a supplied subroutine to all the body <TD> tag's cdata for the specified columns. The first argument is a reference to a subroutine. This subroutine should shift off a single scalar at the beginning, munge it in some fasion, and then return it. The second argument is the column or columns to apply this subroutine to. The body data will be permanently changed by your subroutine. Example:

# uppercase the data in column DEPARTMENT
$table->map_col( sub { return uc shift }, 'department');

One temptation that needs to be addressed is using this method to color the cdata inside a <TD> tag pair. Don't be tempted to do this:

  # don't be tempted to do this
  $table->map_col(sub {
    return qq|<font color="red">| . shift . qq|</font>|;
  }, 'first_name');

  # when this will work (and you dig CSS)
  $table->modify_tag({
	  style => 'Color: red;',
  }, 'first_name');

Another good candidate for this method is turning the cdata into an anchor:

$table->map_col(sub {
  my $raw = shift;
  return qq|<a href="/foo.cgi?process=$raw">$raw</font>|;
}, 'category');

This method permantly changes the data, so use it wisely and sparringly.

set_row_colors
$table->set_row_colors([$colors],[$cols])>

Assign a list of colors to the body cells for specified columns or the entire table if none specified. This is not the same as using an array reference as the value for a tag's BGCOLOR attribute. That method rotates on each column (think horizontally), this one rotates on each row (think vertically). However:

  # this:
  $table->modify_tag('td',{
	  bgcolor => [qw(green green red red)],
  },[qw(first_name last_name)]);

  # is the same as:
  $table->set_row_colors(
      [qw(red green)],
	  [qw(first_name last_name)],
  });

This is a strong possibiliy that this method will be deprecated to make way for a method that handles any attribute, not just bgcolor. If so, this method will just hand the arguments to the new method, so as not to break any clients.

set_null_value
$table->set_null_value($new_null_value)

Change to defualt null_value (&nbsp;) Usefull if you are dealing with numbers - you can set this zero.

set_group
$table->set_group($column);

Assign one column as the main column. Every time a new row is encountered for this column, a <TBODY> tag is written. An optional second argument that contains a non-zero value will cause duplicates to be permanantly eliminated for this row. An optional third argument specifies what value to replace for duplicates, default is &nbsp;

# replace duplicates with the global 'null_value'
$table->set_group('Branch',1);

# replace duplicates with a new value
$table->set_group('Branch',1,'----');

# or in a more Perl-ish way
$table->set_group('Branch',nodups=>'----');

Don't assign a column that has a different value each row, choose one that is a super class to the rest of the data, for example, pick artist over song, since an artist has several songs. This method does not modify data.

calc_totals
$table->calc_totals([$cols],$mask)

Computes totals for specified columns. The first argument is the column or columns to sum, again a scalar or array reference is the requirement. Non-numbers will be ignored, negatives and floating points are supported, but you have to supply an appropriate sprintf mask, which is the optional second argument, in order for the sum to be correctly formatted. See sprintf for further details.

calc_subtotals
$table->calc_subtotals([$cols],$mask)

Computes subtotals for specified columns. It is manditory that you first specify a group via set_group() before you call this method. Each subtotal is tallied from the rows that have the same value in the column that you specified to be the group. At this point, only one subtotal can be calculated and displayed. Plans for implementing N number of subtotals and groups are not on my list, but if enough feedback is generated to warrent it, I will get to work. Better yet, send me a patch. :)

get_col_count
$scalar = $table->get_col_count()

Returns the number of columns in the table.

get_row_count
$scalar = $table->get_row_count()

Returns the numbers of body rows in the table.

TUTORIAL

This section provides a quick tutorial for you to learn about the available methods and the somewhat proper way to use them. For simplicity's sake, the sample database table is nothing more than a glorified flat file, but it will suffice:

+----------------------+
|Child    Parent   Take|
+----------------------+
|bugs     Mo         5 |
|daffy    Larry      4 |
|donald   Larry      4 |
|porky    Curly      7 |
|mickey   Mo         8 |
|goofy    Curly      9 |
|cartman  Mo         2 |
+----------------------+

Call this table bar, and let's assign it to database foo. The important thing to note about this table is that one column is numbers, and the other two have a 1 to M relationship with each other (that is, one Parent can have many Children). You will probably never encounter a database table like this in production, but many of the result sets returned by a database do have a similar structure.

Step 1. Establish a connection to the database server.

Here is where the mileage varies. It is vital that you understand that different databases handle different arguments for connection. Read DBI as well the one for the DBD module you installed.

For this example, assume that we are using MySql on a server named boo, and we can connect with the password for user baz (remember that our database was 'foo'):

my $table = XHTML_Table->new(
  'DBI:mysql:foo:boo', 'baz', '********'
) || die "could not connect to database\n";

Step 2. Execute a SQL query

Lets get all the rows, parent first, child second, the take third, all ordered by parent, then child:

$table->exec_query("
  SELECT PARENT,CHILD,TAKE
  FROM BAR
  ORDER BY PARENT,CHILD
");

Step 3. Make a table

At this point, we have the means to retrieve a very basic XHTML table. Everything will be displayed nice and lined up, but it is an eye sore. Start by modifying the <TABLE> tag:

# to see this example progress from simple to complex
# add each of the following snippets, one at a time,
# to your code, just before the call to get_table

$table->modify_tag('TABLE',{
  border      => 2,
  cellspacing => 0,
  width       => '40%',
  rules       => 'groups',
  summary     => 'weekly takes',
});

Now the header row of table column names:

$table->modify_tag('TH',{
  bgcolor  => '#CC77C',
},'header');

Add a caption:

$table->modify_tag('CAPTION','This Weeks Takes');

Let's sum up how much the kids took this week:

$table->calc_totals('take');

Hey, a new column! Let's color it blue:

$table->modify_tag('TH',{
  bgcolor  => '#9999DD',
},'footer');

The duplicate names in the Parent's column is annoying, you can pick one and only one column to suppress duplicates on, via set_group():

$table->set_group('parent',1);

Get a running subtotal of each of the kid's takes. You can only calculate one subtotal that is based off of the group you designated, Parent:

$table->calc_subtotals('take');

Even more rows added! Change their color as well:

$table->modify_tag('TH',{
  bgcolor  => '#AAAAF9',
},'body');

Hmmm, now the take column looks off-balance, change the aligment:

$table->modify_tag('TD',{
  align  => 'center',
},'take');

And finally, spice up the body rows with alternating colors:

$table->set_row_colors(['#FF99FF','#DD99DD']);

Experiment, have fun with it. Go to PerlMonks and download extremely's Web Color Spectrum Generator and use it suply a list of colors to set_color. You can find it at http://www.perlmonks.org/index.pl?node_id=70521

BUGS

Yes. But I prefer to call them features. See TODO.

TODO

I consider this module to be 95% complete, all features work and the orginal requirements were all completed. However, I do not feel that is ready for a version of 1.0 just yet. Some more issues need to be addressed and solved:

Clean up calc_totals() and calc_subtotals()

These two methods wouldn't get a C from any college level Computer Science II course. But they do work, so I decided to include them in this release. Expect to see these two refactored sometime in the future, as well as the subroutine that uses the data they create - _build_body() As always, patches are welcome. :)

Allow multiple groups

Only being able to set one group is limiting, but unless there is a practicle need to do so, I probably won't bother implenting this one. Patches are welcome.

Give finer tuning on group colors

It might be nice to allow groups columns to only change colors when the group changes, not just the row. I'll be looking for a way to implement this cleany. And why stop with colors? The set_row_colors probably should be deprecated and a new method that handles any attribute would be ideal.

Add a method to map subs to the titles

Just like map_col, a new method that changes the titles <TH> tag's cdata would be nice. For example, changing the titles to anchor links that point back to the CGI script a sort query variable set to the name of the cdata. Instant front-end to sortable reports.

Make map_col behave itself

The previous item could be easily solved if map_col did not alter the original data.

Enclose body rows in <TBODY> and </TBODY>

Currently I am implementing grouping with single <TBODY/> tags. This is perfectly legal, because the closing tag is completely optional. But it seems to me that all rows should be fully enclosed - however, the subroutine that handles this, _build_body(), is fairly 'cornered in' and would reuquire some serious refactoring. Big thanks to PerlMonk's "extremely" for pointing this out to me.

CREDITS

Many thanks to the Perl Monks community, in partiuclar to 'OeufMayo' for convincing me to write XHTML_Table and not HTML_Table. Thanks to Matt Sergeant for contributing DBIx::XML_RDB, the module that inspired this module.

SEE ALSO

DBI

AUTHOR

Jeffrey Hayes Anderson <captvanhalen@yahoo.com>

COPYRIGHT

Copyright (c) 2001 Jeffrey Hayes Anderson. All rights reserved. DBIx::XHTML_Table is free software; it may be copied, modified, and/or redistributed under the same terms as Perl itself.