view release on metacpan or  search on metacpan

lib/CGI/OptimalQuery.pm  view on Meta::CPAN

=item B<< SELECT_ALIAS >> (STRING)

is the alias for the select field. This alias is used throughout the rest of the configuration to describe the field.

=item B<< 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.

=item B<< 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.

=item B<< NAME >> (STRING | undef)

label describing the field name. If C<undef>, field cannot be selected by user and is considered hidden.

=item B<< OPTIONS >> (HASHREF | undef)

The following KEY/VALUES below describe OPTIONS used by the select configuration.

=over

=item B<< select => [COLALIAS1, COLALIAS2] >>

=item B<< 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.

=item B<< 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

=item B<< 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

=item B<< db_formatter => CODEREF >>

sub {
  my ($val) = @_;
  return $val;
}

# format a value before it is executed in the database

=item B<< 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.

=item B<< 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.

=item B<< select_sql => (STRING | ARRAYREF) >>

=item B<< filter_sql => (STRING | ARRAYREF) >>

=item B<< sort_sql => (STRING | ARRAYREF) >>

SQL to use instead of the default SQL for the select for the context described.

=item B<< 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 '...

  DATE_COL => ['DEP1', 'trunc(dep1.date_field)', 'My Date',
                 { date_format => 'MM/DD/YYYY' } ]

=back

=back

=item B<< 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.

=over

=item B<< JOIN_ALIAS >> (STRING)

is the alias for the table or inline view decribed in the JOIN_SQL.

=item B<< DEP >> (STRING | ARRAYREF | undef)

describes required joins that this join depends upon. This should be C<undef> if and only if this is defining the driving data set.


=item B<< 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...

=item B<< 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.


=item B<< OPTIONS >> (undef | HASHREF)


The following KEY/VALUES below describe OPTIONS used by the joins configuration.

=over

=item B<< always_join => 1 >>

tells OptimalQuery to always include join in query. Usfual when the join itself influences the number of results returned. Alternatively, an inline view could be constructed that performs the joins as part of the driving data set.

=item B<< 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:

=item B<< new_cursor_order_by => "some_field.id" >>

=back

=back

=item I<< OPTIONAL CONFIGURATION >>

The following KEY/VALUES below for C<< %CONFIG >> in the call to C<new> are NOT required.


=item B<< AutoSetLongReadLen => 1 >>

Tells OptimalQuery to automatically set C<< $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 usin...


=item B<< 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.

=item B<< debug => 0 >>

sends debug info to the error_handler (STDERR is default)

=item B<< 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 C<< $areq->log_error($msg) >>.


=item B<< filter => "[SELECT_COL_ALIAS] like 'foo' AND .." >>

=item B<< hiddenFilter => "[SELECT_COL_ALIAS] like 'foo' AND .." >>

=item B<< 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 ...

For example:

  <a href=/Search?filter=".escape_uri("[NAME] like 'foo'")

=item B<< module => { 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.

=item B<< 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 n...

=over

=item B<< 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...

=item B<< 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. T...

=back

=item B<< 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. 


=item B<< options => { MODULENAME => { OPT_KEY => OPT_VAL, .. } } >>

OptimalQuery is made up of several modules. The 'options' configuration allows developers to configure these modules. See section B<InteractiveQuery Options>.

  options => { 'CGI::InteractiveQuery' => \%opts }

=item B<< output_handler => sub { print @_; } >>

override default output handler (print to STDOUT), by defining this callback.

=item B<< q => new CGI() >>

Pass OptimalQuery thr CGI query object. OptimalQuery will automatically create a new CGI object if one is not passed in.

=item B<< 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.

=item B<< resourceURI => "/OptimalQuery" >> 

Path to optional OptimalQuery resources. Default path is shown.

=item B<< 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.

=item B<< rows_page => 10 >>

lib/CGI/OptimalQuery.pm  view on Meta::CPAN

  # override the built-in edit link builder
  buildEditLink => sub {
    my ($o,$rec,$opts) = @_;
    return "/link/record?id=$$rec{U_ID};act=edit"'
  }

=item B<< buildNewLink => sub { } >>

  # override the built-in edit link builder
  buildNewLink => sub {
    my ($o,$opts) = @_;
    return "/link/record?act=new"'
  }

=item B<< color => '#cccccc' >>

specify the background color of the optimal query GUI.

=item B<< useAjax => 1 >>

Reload the data using ajax. Defaults to 1 unless not specified and usePopups is set to 0.

=item B<< NewButton => "<a href=record.pl class=OQnewBut>new</a>" >>

=item B<< editButtonLabel => 'edit' >>

=item B<< 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 m...

=item B<< htmlFooter => "<h1>this is a footer</h1>" >>

=item B<< htmlHeader => "<h1>this is a header</h1>" >>

=item B<< httpHeader => CGI::header('text/html') >>

override httpHeader content. If you prefer to not have InteractiveQuery send the header, set this value to empty string.

=item B<< mutateRecord => sub { } >>

  mutateRecord => sub {
    my $rec = shift;

    # add html links to the person record 
    # if user selected the NAME field
    if (exists $$rec{NAME}) {
      $$rec{NAME} = "<A HREF=/PersonRecord?id=$$rec{ID}>".
        CGI::escapeHTML($$rec{NAME})."</A>";
    }
  }

=item B<< noEscapeCol => ['NAME'] >>

if certain columns should not be HTML escaped, let OptimalQuery know by adding them to this array.

=item B<< OQdataLCol => sub { } >>

=item B<< OQdataRCol => sub { } >>

Specify custom code to print the first or last column element. This is most often used to generate an view/edit button. If these callbacks are used, the editLink, and buildEditLink are ignored.

  OQdataLCol => sub {
    my ($rec) = @_; 
    return "<button onclick=\"OQopwin('/ViewRecord?id=$$rec{U_ID};on_update=OQrefresh';\">".
           "view</button>"; 
  }

=item B<< OQdocBottom => "bottom of the document (outside form)" >>

=item B<< OQdocTop => "top of the doc (outside form)" >>

=item B<< OQformBottom => "bottom of form" >>

=item B<< OQformTop => "top of form" >>

=item B<< usePopups => 1|0 >>

use popups when opening a record form using the built in buttons The default is 1. If this is set to 1, useAjax default is 1.

=item B<< WindowHeight => INT >>

=item B<< WindowWidth => INT >>

Specify popup window width, height.


=item B<< OQscript => " some javascript code (see examples below) " >>

OQscript gives you unlimited power to alter the output of optimal query by allowing you to enter javascript code that is executed client side
For example to add a new command button:

  OQscript => "
var e = document.getElementById('OQcmds');
e.innerHTML = '".CGI::OptimalQuery::escape_js(q|<button type=button onclick="window.alert('hello there');">hello</button>|)."' + e.innerHTML; 

=back


=head1 SAVED SEARCHES

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. Developers should tell OptimalQuery the user id by defining 'savedSearchUserID' in their %CONFIG.

 $config{savedSearchUserID} = $user_id;

Saved Searches are stored in a table in the database pointed to by the database handle defined in $config{dbh}. The following table must exist before using saved searches.

  -- For mysql:
  CREATE TABLE oq_saved_search (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED,
      FOREIGN KEY fk_oqsavedsearch_userid (user_id)
        REFERENCES XYZ(id) ON DELETE CASCADE,
    uri VARCHAR(100) NOT NULL,
    oq_title VARCHAR(1000) NOT NULL,
    user_title VARCHAR(1000) NOT NULL,
    params TEXT,
    -- 0:disabled, 1:added, 2:removed, 4:present
    alert_mask INT UNSIGNED NOT NULL DEFAULT 0,
    alert_interval_min INT UNSIGNED,
    alert_dow VARCHAR(7),