NAME
Class::DBI::Plugin::HTML - Generate HTML Tables and Forms in conjunction with Class::DBI
SYNOPSIS
# Inside of your sub-class of Class::DBI add these lines:
use Class::DBI::Plugin::HTML;
use Class::DBI::Pager;
use Class::DBI::AbstractSearch;
use Class::DBI::Plugin::AbstractCount;
use Class::DBI::Plugin::RetrieveAll;
.....
# Inside your script you will be able to use this modules
# methods on your table class or object as needed.
use ClassDBIBaseClass;
use URI::Escape;
use CGI;
my $cgi = CGI->new();
my $cdbi_plugin_html = Baseball::Master->html();
$cdbi_plugin_html->data_table->addRow('Last Name','First Name','Bats' , 'Throws' ,
'Height (ft)','(inches)',
'Weight','Birth Year' );
my %params;
map { $params{$_} =
uri_unescape($cgi->param("$_"))
} $cgi->param();
$cdbi_plugin_html->params( \%params );
$cdbi_plugin_html->exclude_from_url([ 'page' ]);
# attribute style
# created based on params and exclude values from above
# auto sets the query_string value
$cdbi_plugin_html->url_query();
# set the page name (script)
$cdbi_plugin_html->page_name('cdbitest.pl');
# indicate which columns to display
$cdbi_plugin_html->display_columns( [ 'lastname','firstname',
'bats' ,'throws',
'ht_ft' ,'ht_in',
'wt' ,'birthyear' ]
);
# indicate which columns to exclude, inverse of display above
$cdbi_plugin_html->exclude_columns();
# indicate the base class to work with, this is optional,
# if you should create you object via a call to
# Class::DBI::Plugin::HTML vs. a Class::DBI sub class
# this assures the correct sub class is used for data collection
$cdbi_plugin_html->cdbi_class( 'Baseball::Master' );
# indicate the style of navigation to provide
$cdbi_plugin_html->navigation_style( 'both' );
print qq~<fieldset><legend>Filter by First Letter of Last Name</legend>~;
print $cdbi_plugin_html->string_filter_navigation(
-column => 'lastname',
-position => 'begins',
);
print qq~</fieldset>~;
$cdbi_html->only('firstname');
print $cdbi_plugin_html->build_table();
my $nav = $cdbi_plugin_html->html_table_navigation();
print qq!<div algin="center">$nav</div>\n!;
$cdbi_plugin_html->add_bottom_span($nav);
print $cdbi_plugin_html->data_table;UPGRADE WARNING
As of the .8 release there have been changes to the methods and how they work. It is likely that scripts built with older versions WILL break. Please read below to find specific changes that may negatively impact scripts built using the the releases prior to .8. The .9 release contains some minor modifications that could in some chases break your scripts, test carefully before upgrading in a production environment.
DESCRIPTION
The intention of this module is to simplify the creation of HTML tables and forms without having to write the HTML, either in your script or in templates.
It is intended for use inside of other frameworks such as Embperl, Apache::ASP or even CGI. It does not aspire to be its own framework. If you are looking for a framework based on Class::DBI I suggest you look into the Maypole module.
See FilterOnClick below for more on the purpose of this module.
Tables are created using HTML::Table. The use of HTML::Table was selected because it allows for several advanced sorting techniques that can provide for easy manipulation of the data outside of the SQL statement. This is very useful in scenarios where you want to provide/test a sort routine and not write SQL for it. The more I use this utility the less likely it seems that one would need to leverage this, but it is an option if you want to explore it.
This module is semi-stable, but production use is still not advised. If the maintainer is lazy and this warning appears in a 1.x or greater release you can ignore it.
Feedback on this module, its interface, usage, documentation etc. is welcome.
FilterOnClick
This module provides a generic implementation of a technique I codifed in 2000 inside some one off CGI scripts. That technique within its problem space produced a significantly easier to navigate database record view/action system for those that used it.
As of release .9 98% of that functionality is replicated within this module, plus some new features.
The concept, at its core, is relatively simple in nature. You filter the results in the table by clicking on values that are of interest to you. Each click turns on or off a filter, which narrows or expands the total number of matching records. This allows for identifying abnormal entries, trends or errors simply by paging, searching or filtering through your data. If you configure the table appropriately you can even link to applications or web pages to allow you edit the records.
An example FilterOnClick session would consist of something like this: You get a table of records, for our example lets assume we have four columns: "First Name" aka FN, "Last Name" aka LN , "Address" , and "Email". These columns are pulled from the database and placed into an HTML table on a web page. The values in the FN , LN and Email address columns are links back to the script that generated the original table, but contain filter information within the query string. In other words the link holds information that will modify the SQL query for the next representation of data.
Presently there are six (6) built in filter types for within tables and three (3) more that are specific to string based matches outside of the table itself. (see string_filter_navigation method below for info on the second three)
The six html table level filters are 'only','contains','beginswith','endswith' 'variancepercent','variancenumerical'. The where clause that is created within the module automatically is passed through to the Class::DBI::AbstractSearch module, which is in turn based on the SQL::Abstract module. In other words, you are not required to create any SQL statements or add any code to your Class::DBI base class.
Back to the example at hand. Lets say the database has 20K records the sort order was set to LN by default. The FN column has been configured with an 'only' filter. In the FN list you see the FN you are looking for so you click on it, when the script runs it auto-generates a new filter (query) that now only shows records that match the FN you clicked on. If you click on the FN column a second time the filter based on FN is removed.
The filter of the table is cascading, you can perform it across multiple columns. So if you want to find all the 'Smith's' with email addresses like 'aol.com' you could click first on an email address containing 'aol.com' and then a last name of 'Smith', provided you configured a proper filter code for the table.
You can see FilterOnClick in action at: http://cdbi.gina.net/cdbitest.pl
Example code to create a FilterOnClick column value ( see the build_table method ):
Match Exactly
$html->only('column_name');
column_name => 'only'Match Beginning of column value with string provided
$html->beginswith('column_name' , 'string'); # new way, can be done anywhereMatch ending of column value with string provided $html->endswith('column_name , 'string'); # new way, can be done anywhere
Filter to columns that contain a particular string (no anchor point)
$html->contains('column_name' , 'string'); # new way, can be done anywhere Show records with a numerical variance of a column value
$html->variancenumerical('column_name' , number); # new way, can be done anywhereShow records with a percentage variance of a column value
$html->variancepercent('column_name' , number); # new way, can be done anywhereCONFIGURATION FILE
As of version .9 you can assign many of the attributes via a configuration file See the examples directory for a sample ini file
METHOD NOTES
The parameters are passed in via a hash for most of the methods. The Class::DBI::Plugin::HTML specific keys in the hash are preceeded by a hypen (-). Column names can be passed in with their own anonymous subroutine (callback) if you needed to produce any special formating or linkage.
METHODS
html
Creates a new Class::DBI::Plugin::HTML object
$cdbi_html = MyClassDBIModule->html();debug
Wants: 1 or 0
Defaults to: 0
Valid in Conifguration File: Yes
Set to one to turn on debugging output. This will result in a considerable amount of information being sent to the browser output so be sure to disable in production. Can be set via method or configuration file.
$cdbi_html->debug(1);params
Wants: Hash reference of page paramters
Defaults to: {} (empty hash ref)
Set the params that have been passed on the current request to the page/script
$cdbi_html->params( {
param1 => 'twenty'
} );Using CGI
use URI::Escape;
my %params;
map { $params{$_} =
uri_unescape($cgi->param("$_"))
} $cgi->param();
$cdbi_html->params( \%params );Using Apache::ASP
$cdbi_html->params( $Request->Form() );Using Embperl
$cdbi_html->params( \%fdat );config
Wants: configuration key, value is optional
Defatuls to: na
Configuration values can be accessed directly or via the config method. This is allowed so you know where the value you are calling is being assigned from.
To get get a value:
$cdbi_html->config("searchable");To set a value do this:
$cdbi_html->config('searchable',1);display_columns
Wants: Array ref of column names
Defaults to: List of all columns available if left unassigned
Valid in configuration file: Yes
The list (array ref) of field names you want to create the columns from. If not sent the order the fields in the database will appear will be inconsistent. Works for tables or forms.
$cdbi_html->display_columns( [ 'lastname','firstname',
'bats' ,'throws',
'ht_ft' ,'ht_in',
'wt' ,'birthyear' ]
);exclude_from_url
Wants: Array reference
Defaults to: [] (emptry array ref)
Key/value pair to be removed from auto generated URL query strings. The key for the page should be one of the items here to avoid navigation issues
$cdbi_html->exclude_from_url( [ 'page' ] );form_table
Wants: HTML::Object
Defaults to: HTML::Object
Returns: HTML::Object
$cdbi_html->form_table(); # get current form table object
$cdbi_html->form_table($html_table_object); # set form table objectThere is no need to set this manually for simple forms.
navigation_style
Wants: string, either 'block' or 'both'
Defaults to: block
Valid in Configuration File: Yes
Returns: Current setting
$cdbi_html->navigation_style('both');column_to_label
Wants: Hash reference
Defaults to: empty
$cdbi_html->column_to_label(
'firstname' => 'First Name',
'lastname' => 'Last Name'
);Presently not active, but will be in 1.0 release
cdbi_class
(string) - sets or returns the table class the HTML is being generated for
config_file
Returns the name of the config_file currently in use
rows
Wants: Number
Defaults to: 15
Sets the number of rows the table output by build_table will contain per page
$cdbi_html->rows(20);html_table
Wants: HTML::Table object
Defaults to: HTML::Table object
This is only useful if want to either create your own HTML::Table object and pass it in or you want to heavily modify the resulting table from build_table. See the HTML::Table module for more information.
build_table
Wants: Hash reference
Defatuls to: na
Returns: HTML::Table object
Accepts a hash of options to define the table parameters and content. This method returns an HTML::Table object. It also sets the data_table method to the HTML::Table object generated so you can ignore the return value and make further modifications to the table via the built in methods.
See Synopsis above for an example usage.
The build_table method has a wide range of paramters that are mostly optional.
exclude_columns
Wants: Arrary reference
Defaults to: na
Valid in configuration File: Yes
Returns: When called with no argument, returns current value; an array ref
Removes fields even if included in the display_columns list. Useful if you are not setting the columns or the columns are dynamic and you want to insure a particular column (field) is not revealed even if someone accidently adds it some where.
data_table
Wants: HTML::Table object
Defaults to: na
Returns: HTML::Table object is assigned
Allows for you to pass in an HTML::Table object, this is handy if you have setup the column headers or have done some special formating prior to retrieving the results.
pager_object
Wants: Class::DBI::Pager object
Defaults to: Class::DBI::Pager object
Returns: Current pager_object
Allows you to pass in a Class::DBI::Pager based object. This is useful in conjunction with the html_table_navigation method. If not passed in and no -records have been based it will use the calling class to perform the lookup of records.
As of version .9 you do not need to assign this manually, it will be auto populated when call to 'html' is made.
records
Wants: Array reference
Defaults to: na
Returns: present value
Expects an anonymous array of record objects. This allows for your own creation of record retrieval methods without relying on the underlying techniques of the build_table attempts to automate it. In other words you can send in records from none Class::DBI sources, but you lose some functionality.
where
Wants: Hash reference
Defaults to: Dynamically created hash ref based on query string values, part of the FilterOnClick process.
Expects an anonymous hash that is compatiable with Class::DBI::AbstractSearch
order_by
Wants: scalar
Returns: current value if set
Passed along with the -where OR it is sent to the retrieve_all_sort_by method if present. The retrieve_all_sort_by method is part of the Class::DBI::Plugin::RetrieveAll module.
page_name
Wants: scalar
Returns: current value if set
Valid in Configuration file: Yes
Used within form and querystring creation. This is the name of the script that is being called.
query_string
Wants: scalar
Returns: current value if set
It is not required to set this, it auto generated through the FilterOnClick process, is useful for debugging.
rowcolor_even
Wants: Valid HTML code attribute
Defaults to: '#ffffff'
Returns: Current value if set
Valid in Configuration file: Yes
Define the even count row backgroud color
rowcolor_odd
Wants: Valid HTML code attributes
Defaults to: '#c0c0c0'
Valid in Configuration file: Yes
Define the odd count row backgroud color
rowclass
Valid in Configuration file: Yes
(optional) - overrides the -rowcolor above and assigns a class (css) to table rows
no_mouseover
Valid in Configuration file: Yes
Turns off the mouseover feature on the table output by build_table
mouseover_class
Valid in Configuration file: Yes
The CSS class to use when mousing over a table row
searchable
Valid in Configuration file: Yes
Enables free form searching within a column
mouseover_bgcolor
Valid in Configuration file: Yes
Color for mouseover if not using a CSS definition. Defaults to red if not set
filtered_class
Valid in Configuration file: Yes
Defines the CSS class to use for columns that currently have an active Filter
ascending_string
Wants: string (can be image name)
Default to: '^'
Valid in Configuration file: Yes
The string used to represent the ascending sort filter option. If value ends with a file extension assumes it is an image and adds approriate img tag.
descending_string
Wants: string (can be an image name)
Defaults to: 'v'
Valid in Configuration file: Yes
The string used to represent the descending sort filter option. If value ends with a file extension assumes it is an image and adds approriate img tag.
rowclass_odd
Valid in Configuration file: Yes
The CSS class to use for odd rows within the table
navigation_separator
Valid in Configuration file: Yes
The seperator character(s) for string filter navigation
page_navigation_separator
Valid in Configuration file: Yes
The seperator for page navigation
table_field_name
(code ref || (like,only) , optional) - You can pass in anonymous subroutines for a particular field by using the table field name (column). Example:
first_name => sub {
my ($name,$turl) = @_;
if ($turl =~ /ONLY\-first_name/) {
$turl =~ s/ONLY\-first_name=[\w\-\_]+//;
} else {
$turl .= "&ONLY-first_name=$name";
}
return qq!<a href="test2.pl?$turl">$name</a>!;
},build_form
Accepts a hash of options to define the form options. Values can be left blank for the value on keys in form element names if you want to use the form fill in technique described in this document.
#!/usr/bin/perl
use CDBIBaseball;
use Data::Dumper;
use URI::Escape;
use CGI;
use strict;
my $cgi = CGI->new();
my $html = Baseball::Master->html();
print $cgi->header();
my %params;
map { $params{$_} =
uri_unescape($cgi->param("$_"))
} $cgi->param();
$html->html_table(-align=>'center');
$html->params( \%params );
$html->page_name('formcdbitest.pl');
$html->display_columns( [ 'lastname','firstname',
'bats' ,'throws',
'ht_ft' ,'ht_in',
'wt' ,'birthyear' ]
);
$html->exclude_columns();
$html->hidden_fields( { lahmanid => '1234' } );
$html->cdbi_class( 'Baseball::Master' );
$html->navigation_style( 'both' );
$html->column_to_label( {
lastname => 'Last Name',
firstname => 'First Name',
bats => 'Bats',
ht_ft => 'Height (ft)',
ht_in => 'Height (in)',
wt => 'Weight',
birthyear => 'Birthyear',
throws => 'Throws',
}
);
print $html->build_form();no_submit
The submit button can be removed by sending -no_submit as an attribute to the build_form method or by setting the no_submit tag to 1. Default is 0;
no_form_tag
The opening HTML form tag can be removed by sending -no_from_tag as an attribute to the build_form method or by setting the no_form_tag method to 1. Default is 0;
$html->no_form_tag(1);hidden_fields
Hidden fields can be added by putting a hash ref into the hidden_fields accessor or by sending in a hash ref with the -hidden_fields attribute of the build_form method.
html_table_navigation
Creates HTML anchor tag (link) based navigation for datasets. Requires Class::DBI::Pager. Navigation can be in google style (1 2 3 4) or block (previous,next).
my $nav = $cdbi_plugin_html->html_table_navigation(
-pager_object => $pager,
# pass in -navigation with block as the value for
# next/previous style
# "google" style is the default
-navigation_style => 'block',
-page_name => 'test2.pl',
);
print "'$nav'\n";fill_in_form
Wrapper method for HTML::FillInForm, pass the arguments you would normally pass into HTML::FillInForm.
my $params = { user_name => 'trs80', first_name => 'TRS' };
my $ignore = [ 'last_name' ];
print $html->fill_in_form(
scalarref => \$form,
fdat => $params,
ignore_fields => $ignore
);add_bottom_span
Places the content you pass in at the bottom of the HTML::Table object passed in. Used for adding "submit" buttons or navigation to the bottom of a table.
search_ref
Creates the URL and where statement based on the parameters based into the script. This method sets the query_string accessor value and returns the where hash ref.
$cdbi_plugin_html->search_ref(
# hash ref of incoming parameters (form data or query string)
# can also be set via the params method instead of passed in
-params => \%params,
# the like parameters by column (field) name that the
# SQL statement should include in the where statement
-like_column_map => { 'first_name' => 'A%' },
);url_query
Creates the query portion of the URL based on the incoming parameters, this method sets the query_string accessor value and returns the query string
$cdbi_plugin_html->url_query(
# pass in the parameters coming into the script as a hashref
-params => \%params,
# items to remove from the url, extra data that
# doesn't apply to the database fields
-exclude_from_url => [ 'page' ],
);string_filter_navigation
my ($filter_navigation) = $cdbi_plugin_html->string_filter_navigation(
-position => 'ends'
);This method creates navigation in a series of elements, each element indicating a item that should appear in a particular column value. This filter uses anchor points to determine how to qualify the search. The anchor points are: BEGINSWITH ENDSWITH CONTAINS
The items in the 'strings' list will only be hrefs if they items in the database match the search, if you prefer them not to be displayed at all pass in the -hide_zero_match
The allowed parameters to pass into the method are:
hide_zero_match
Removes items that have no matches in the database from the strings allowed in the final navigation.
-position (optional - default is 'begin') - Tells the method how to do the match, allowed options are any case of 'begin' , 'end' or 'contains'. These options can be the entire anchor points as outlined above, but for ease of use only the aforemention is enforced at a code level.
query_string
(optional) - See methods above for documentation
navigation_list
(optional, array_ref - default is A-Z) - Array ref containing the strings to filter on.
navigation_column
Indicates which column the string filter will occur on. If you want to provide a filter on multiple columns it is recommended that you create multiple string_filter_navigation. Can be set via method, string_filter_navigation argument or configuration file
-page_name - The name of page that the navigation should link to
navigation_alignment
Set HTML attribute alignment for the page navigation.
navigation_seperator
$cdbi_html_table->navigation_seperator('::');
-or-
-navigation_seperator => '::' # argument passed into string_filter_navigation
-or-
navigation_sperator=:: in the configuration file(optional, default two non-breaking spaces) - The characters to place between each item in the list.
align
(optional, defaults to center) - defines the alignment of the navigation
no_reset
don't include the filter reset link in the output
form_select
this methods expects the following:
-value_column # column containing the value for the option in the select
-text_column # column containing the text for the optoin in the select (optional)
-selected_value # the value to be selected (optional)
-no_select_tag # returns option list only (optional)FILTERS
Filters are generated with the build_table method. Filters allow for cascading drill down of data based on individual cell values. See Example page for a demo.
beginswith
Declare a begins with match on a column
$cdbi_html_table->beginswith('column_name','A');
# where 'A' is the value to match at the beginningendswith
$cdbi_html_table->endswith('column_name','A');
# where 'A' is the value to match at the end of the column contentscontains
$cdbi_html_table->contains('column_name','A');
# where 'A' is the value to match anywhere in the column contentsvariancepercent
$cdbi_html_table->variancepercent('column_name',2);
# where '2' is the allowed percentage of variance to filter onvariancenumerical
$cdbi_html_table->variancenumerical('column_name',2);
# where '2' is the allowed variance to filter on based
# if value for 'column_name' is clickedonly
$cdbi_html_table->only('column_name');
# creates a filter on 'column_name' cells to match the value in the cell
# clickedINTERNAL METHODS/SUBS
If you want to change behaviors or hack the source these methods are subs should be reviewed as well.
get_records
Finds all matching records in the database
create_order_by_links
add_number
determine_columns
Finds the columns that are to be displayed
auto_hidden_fields
add_hidden
create_auto_hidden_fields
BUGS
Unknown at this time.
SEE ALSO
Class::DBI, Class::DBI::AbstractSearch, Class::DBI::AsForm, HTML::Table, Class::DBI::Pager
AUTHOR
Aaron Johnson solution@gina.net
THANKS
Thanks to my Dad for buying that TRS-80 in 1981 and getting me addicted to computers.
Thanks to my wife for leaving me alone while I write my code :^)
The CDBI community for all the feedback on the list and contributors that make these utilities possible.
CHANGES
Changes file included in distro
COPYRIGHT
Copyright (c) 2004 Aaron Johnson. All rights Reserved. This module is free software. It may be used, redistributed and/or modified under the same terms as Perl itself.