NAME
CGI::OptimalQuery - dynamic SQL query viewer
SYNOPSIS
use CGI::OptimalQuery;
# construct a CGI::OptimalQuery object
my $oq = CGI::OptimalQuery->new({
q => CGI->new(),
dbh => $dbh,
title => 'Employee List',
select => {
'NAME' => ['emp','emp.lname||', '||emp.fname", "Name"],
'DEPT' => ["dept", "dept.name", "Department"]
},
# default shown columns alias list
show => ['NAME', 'DEPT'],
# defined joins included in the from clause
joins => {
'emp' => [ undef, 'employee', undef],
'dept'=> ["emp", "
left join ( SELECT name FROM dept ) dept
on (emp.dept = dept.id)"]
},
options => {
'CGI::OptimalQuery::InteractiveQuery' => {
mutateRecord => sub { my $rec = shift; $$rec{NAME} = ''; }
OQdataRCol => sub { my $rec = shift; return "stuff"; }
}
}
);
# get the count
$oq->get_count();
# get link back to this report
$oq->get_link();
# output view to STDOUT
$oq->output();
DESCRIPTION
Developer describes environment, output options, and database query;
CGI::OptimalQuery provides user with a web interface to view, filter,
sort, and export the data.
Sounds simple, but CGI::OptimalQuery does not write the SQL for the
developer. It is only responsible for gluing the appropriate pieces of
SQL together to form an optimized SQL query, and outputing the results
in a format the user chooses.
This module has been tested for:
1) SQLite
2) MySQL
3) PostgreSQL
4) Microsoft SQL Server (must use MARS_Connection=yes in connection option)
5) Oracle
The important elements the developer describes are what fields (select
elements) the user can see and what data sets (join elements) those
fields come from. Each select and join element can depend upon joins.
For every Optimal Query there is one driving data set. The driving set
does not depend on other data sets. For every row in the driving data
set there can only be one corresponding row when joining the driving
data set to other joins described in the joins configuration hash
reference. This allows Optimal Query to optimize SQL and only include
the appropriate joins when the user has selected a column from one of
those joins. For example: If there are employee and department tables
and the user only wants to see employee fields (not department fields)
then Optimal Query will not join in the department table.
INSTALLATION
Configure your web server to allow it to serve the "Resources" directory
containing static html/js/css files. This directory is located near the
installation path. The easiest way to find it is to execute:
perl -MCGI::OptimalQuery -e '$_=$INC{"CGI/OptimalQuery.pm"};s/\.pm$/\/Resources\n/;print $_'
If you are using Apache, add the following config:
# required CGI::OptimalQuery static files Alias /OptimalQuery/
/usr/local/share/perl5/CGI/OptimalQuery/Resources/
METHODS
new ( \%CONFIG )
Returns an optimal query object.
"\%CONFIG" (a hash reference) describes the environment, query
description, and output options using key and value pairs. Possible
configuration elements are shown below. (required ones are first)
*REQUIRED CONFIGURATION*
The following KEY/VALUES below for %CONFIG in the call to "new" are
required.
title => "This is the title of this Query"
dbh => DBI->connect( .. )
provides OptimalQuery a connection to the database.
show => ['COLALIAS1', 'COLALIAS2']
Default fields to show user when loading OptimalQuery the first time.
This can also be set as a CGI param where the value is a comma
separated list of column aliases. Example: "[NAME], [DOB]".
params => { show => "COLALIAS1,COLALIAS2", filter => "[COLALIAS1]=5",
sort => "[COLALIAS1]", queryDescr => "descr here", hiddenFilter =>
"[COLALIAS1]=5" }
Get user set parameters from this hash instead of CGI params. Useful
if you are constructing an OQ that should not be influenced by CGI
params.
select => { SELECT_ALIAS => [ DEP, SQL, NAME, OPTIONS ], .. }
The select configuration describes what fields from the query can be
selected, filtered, and sorted.
SELECT_ALIAS (STRING)
is the alias for the select field. This alias is used throughout
the rest of the configuration to describe the field.
DEP (STRING | ARRAYREF)
describes required joins that must be included to use the select
specified. The DEP can be written as a string or an array
reference of strings if multiple dependancies for the field exist.
SQL (STRING | ARRAYREF)
SQL to display values for this field. Specified as a string or
array reference where the first element is the SQL and each
element after is a bind value.
NAME (STRING | undef)
label describing the field name. If "undef", field cannot be
selected by user and is considered hidden.
OPTIONS (HASHREF | undef)
The following KEY/VALUES below describe OPTIONS used by the select
configuration.
select => [COLALIAS1, COLALIAS2]
select => "COLALIAS1, COLALIAS2"
Define other select fields to be included in executed SQL.
These fields can be used in custom formatters including the
built in CGI::OptimalQuery::Base::recview_formatter.
formatter => CODEREF
sub { my ($val, $rec, $o, $colAlias) = @_; return $val; }
# Built in formatters to display all field/values specified in
'select' as text. \&CGI::OptimalQuery::Base::recview_formatter
html_formatter => CODEREF
sub { my ($val, $rec, $o, $colAlias) = @_; return $val; }
# Built in formatters to display all field/values specified in
'select' as html.
\&CGI::OptimalQuery::Base::recview_html_formatter
is_hidden => 1
hides the select field and data from being viewed by the user.
Data for this select is still available in callbacks and can
be included in the hiddenFilter.
always_select => 1
tells OptimalQuery to always select the column regardless if
it isn't explicitly being used in the show. This does not
automatically make it shown to the user, but it will be
available to the developer in callbacks.
select_sql => (STRING | ARRAYREF)
filter_sql => (STRING | ARRAYREF)
sort_sql => (STRING | ARRAYREF)
SQL to use instead of the default SQL for the select for the
context described.
date_format => (STRING)
if column is a date and date format is specified, OptimalQuery
will write SQL to use the date format appropriately.
Note: Oracle's date component also has a built-in time
component. If the data is '11/24/2005 14:56:45' and the
date_format is 'MM/DD/YYYY', the date will show up as
'11/24/2005'. If a user tries to filter on date '11/24/2005'
Oracle will only match '11/25/2005 00:00:00' leaving out
results the user probably thinks should be included. In this
case, the developer should trunc the date. Trunc strips the
time component from a date field. Example:
DATE_COL => ['DEP1', 'trunc(dep1.date_field)', 'My Date',
{ date_format => 'MM/DD/YYYY' } ]
joins => { JOIN_ALIAS => [ DEP, JOIN_SQL, WHERE_SQL, OPTIONS ], .. }
describes what tables to join in order to fulfill the dependancies
used by the fields described in the SELECT HASHREF.
JOIN_ALIAS (STRING)
is the alias for the table or inline view decribed in the
JOIN_SQL.
DEP (STRING | ARRAYREF | undef)
describes required joins that this join depends upon. This should
be "undef" if and only if this is defining the driving data set.
JOIN_SQL (STRING | ARRAYREF)
describes the SQL that is used in the join clause for the
generated SQL. Example: "LEFT JOIN dept ON (emp.dep_id =
dept.id)". If this describes the driving table, only the table
name is needed. Inline views can also be used. Make sure you
specify the alias on the view! Example: JOIN ( SELECT * FROM emp
WHERE is_active = 'Y') active_emps
WHERE_SQL (undef | STRING | ARRAYREF)
This is deprecated. It was used to describe the SQL in the where
clause that was needed to join the table described in the from
clause. Since SQL-92 allows developers to put the join SQL in the
join, this should not be used.
OPTIONS (undef | HASHREF)
The following KEY/VALUES below describe OPTIONS used by the joins
configuration.
new_cursor => 1
tells OptimalQuery to open a new cursor for this join. This
can be used to select and filter multi-value fields.
Optionally, an order_by param can be specified to sort the
results returned by the cursor as such:
new_cursor_order_by => "some_field.id"
*OPTIONAL CONFIGURATION*
The following KEY/VALUES below for %CONFIG in the call to "new" are
NOT required.
AutoSetLongReadLen => 1
Tells OptimalQuery to automatically set "$dbh->{SetLongReadLen}". Used
only in Oracle. Enabling this setting may slow down OptimalQuery since
it needs to do extra queries to set the length if LOBS exist. This is
only enabled by default when using Oracle.
check => 0
Tells OptimalQuery to do additional checking to make sure the amount
of rows in the driving table is equal even when including other joins.
It is off by default because there can be a significant performace hit
when enabled.
debug => 0
sends debug info to the error_handler (STDERR is default)
error_handler => sub { ($err) = @_; }
intercept messages sent to the error handler. Very useful if you are
running in a mod_perl env and want to redirect error messages using
"$areq->log_error($msg)".
filter => "[SELECT_COL_ALIAS] like 'foo' AND .."
hiddenFilter => "[SELECT_COL_ALIAS] like 'foo' AND .."
forceFilter => "[SELECT_COL_ALIAS] like 'foo' AND .."
Filters add SQL to the where clause. If a CGI param called 'filter' or
'hiddenFilter' are provided, the CGI param value is used instead. The
value of a forceFilter cannot be overridden. Users can manipulate a
filter using the filter dialog tool. The hiddenFilter can only be
manipulated using the GET param. Filters allow developers and end
users to add SQL to the where clause. The filter grammar is described
in the *FILTER GRAMMAR* section.
For example:
{ OverloadModuleLabel => PerlModuleName, .. }
This is an advanced feature that can help perl guru's change the
factory blueprints for optimal query modules instantiated by
CGI::OptimalQuery.
named_filters => { NORMAL_NAMED_FILTER, CUSTOM_NAMED_FILTER, .. }
allow developers to create complex predefined sql for insertion in the
where clause by the 'filter' and 'hiddenFilter' parameters. There are
two types of named_filters: "normal" and "custom". Normal named
filters are defined with static SQL. Custom named filters are dynamic
and most often take arguments which influence the SQL and bind params
generated via callbacks.
NORMAL_NAMED_FILTER
filterNameAlias => [ DEP, SQL, NAME ]
DEP is a string or an ARRAY of strings describing the dependancies
used by the named filter. SQL is a string or an arrayref with
SQL/bind values that is used in the where clause when the named
filter is enabled. The NAME is used to describe the named filter
to the user running the report.
CUSTOM_NAMED_FILTER
filterNameAlias => {
title => "text displayed on interactive filter",
html_generator =>
sub { my ($q, $prefix) = @_; return $html;},
sql_generator => sub {
my %args = @_;
return [$deps, $sql, $name];
}
}
The html_generator is used by InteractiveFilter to collect input
from the user. The sql_generator converts the named filter &
arguments into deps, sql, and a name. The deps can be returned as
an array ref of string deps if more than one dep exists. The sql
can also be returned as an array ref where the first element is
the sql and the rest are bind values.
named_sorts => { SortName => [ DEP, SQL, NAME], .. }
Named sorts aren't really used that often. They are really implemented
for completeness and work the same way as named_filters.
options => { MODULENAME => { OPT_KEY => OPT_VAL, .. } }
OptimalQuery is made up of several modules. The 'options'
configuration allows developers to configure these modules. See
section InteractiveQuery Options.
options => { 'CGI::InteractiveQuery' => \%opts }
output_handler => sub { print @_; }
override default output handler (print to STDOUT), by defining this
callback.
q => new CGI()
Pass OptimalQuery thr CGI query object. OptimalQuery will
automatically create a new CGI object if one is not passed in.
queryDescr => "Some text describing the query"
The query description is extra text describing the query and does not
affect generated SQL. Can also be set as a CGI param.
resourceURI => "/OptimalQuery"
Path to optional OptimalQuery resources. Default path is shown.
results_per_page_picker_nums => [10,20,50,100,'All']
An interactive query displays a pager mechanism when the result set is
larger than the rows_page param. This array reference allows a
developer to override the default options a user can pick from the
pager.
rows_page => 10
The default rows_page a user has when initially loading
InteractiveQuery. Can also be set as a CGI param.
mutateRecord => sub { }
$$schema{mutateRecord} = sub {
my $rec = shift;
# add html links to the person record
# if user selected the NAME field
if (exists $$rec{NAME}) {
$$rec{NAME} = "".
CGI::escapeHTML($$rec{NAME})."";
}
};
savedSearchUserID => $user_id
InteractiveQuery can optionally save searches to a database so users
can revisit them latter. To do this, saved searches are tied to a
unique user id. See the "Saved Searches" section for more information
on this topic.
savedSearchAlerts => 0 | 1
If savedSearchUserID, is defined, setting savedSearchAlerts to 1 will
enhance dialogs to optionally allow users to configure saved searches
to alert them when records are added, removed, or are present. See the
"Saved Search Alerts" section for additional details on configuring
this feature.
savedSearchAlertMaxRecs => 1000
Specify maximum record UIDs to store in saved search alert previously
seen uid history. The previously stored list of UIDs is used to
determine if a new record has appeared. Don't set this too high - the
pks are stored in the saved search table. The default settings allow
about 14k of data per saved search. (15 char key size * 1000
savedSearchAlertMaxRecs) / 1024 = 14k.
savedSearchAlertEmailCharLimit => 500000
Maximum character limit of saved saerch alert email. If limit is
reached, a message appears instructing the user reduce the size by
hiding fields or modify filters. The default value is 500000 (~ .5MB)
sort => "[COLALIAS1] DESC, [COLALIAS2]"
Default sort to show user when loading OptimalQuery for the first
time. See the "SORT GRAMMAR" section for more information. Sort can
also be specified as a CGI param.
state_params => [ 'form_field1', .. ]
If HTTP GET/POST params are required to dynamically generate a
%CONFIG, the developer can specify the names of the GET/POST params in
this array and OptimalQuery will automatically carry their state.
URI => "/URI/Back/To/This/Page"
The URI back to the page the user is currently on. The default URI is
taken from the REQUEST_URI ENV.
URI_standalone => "/URI/Back/To/This/Page?layout=off"
By request, some developers use a separate URI to turn their layout
system off so OptimalQuery can send the headers for content that can't
be embedded.
output()
Output view to output_handler (STDOUT is default).
INTERACTIVE QUERY OPTIONS
Options for an InteractiveQuery can be set by defining the following
HASHREF in %CONFIG.
$CONFIG{options}{'CGI::OptimalQuery::InteractiveQuery'} =
{ KEY => VAL, .. };
- OR -
$CONFIG{options}{'CGI::OptimalQuery::InteractiveQuery'}{KEY} = {VAL};
disable_select => 1
disable_filter => 1
disable_sort => 1
Disables options for all fields. If you set all three your Optimal
Query will work like a Pager.
htmlExtraHead
Append additional HTML in the head section.
# turns off default inline CSS
$CONFIG{options}{'CGI::OptimalQuery::InteractiveQuery'}{htmlExtraHead} .=
"";
buildEditLink => sub { }
# override the built-in edit link builder
buildEditLink => sub {
my ($o,$rec,$opts) = @_;
return "/link/record?id=$$rec{U_ID};act=edit"'
}
buildNewLink => sub { }
# override the built-in edit link builder
buildNewLink => sub {
my ($o,$opts) = @_;
return "/link/record?act=new"'
}
color => '#cccccc'
specify the background color of the optimal query GUI.
useAjax => 1
Reload the data using ajax. Defaults to 1 unless not specified and
usePopups is set to 0.
NewButton => "new"
editButtonLabel => 'edit'
editLink => '/link/to/record'
OptimalQuery will automatically create an edit and new button if
this is defined. When creating the link, OptimalQuery appends
"?id=$$rec{U_ID};act=new;on_update=OQrefresh" or
"?id=$$rec{U_ID};act=load;on_update=OQrefresh" to the link so the
record module will know which view to load. "OQrefresh" is a
function defined by Optimal Query that an external record module can
call to update the Optimal Query window if a record has been
updated.
htmlFooter => "