Reporting is an important aspect of many applications, but also one of
the most dynamic. No matter how well defined the project specifications are,
reporting needs always change. The Query Builder class is a method to enable
the application's users to perform ad hoc querying with very little work
on the part of the developer.
Query Builders are one of the most complex objects in Marmot to use,
but they provide the developer a powerful tool to enable clients to be self
sufficient. It is important to realize, however, that Query Builder have
some limitations and expectations. Read the documentation careful and start
with a simple Query Builder, working your way up with minor changes.
Don't forget to test every possible combination of criteria and
reporting as well, to identify an problems before clients experience them.
Query builders can operate as one of two types, table (the default) or
class. A table query builder assumes you are querying directly against one
or more tables in a supported database. A class query builder works with
objects descended from the MU_Object base class to define relatationships on
the fly.
Query builder also support user selected or application specified
output fields, giving the application and user more flexibility in exactly
what data is displayed.
Query Builders are implemented in the MU_Query_Builder class. The
following example shows a simple, single table query builder.
<?php
$presentation = new MU_Presentation();
$body =& $presentation->add_body();
$dbh = new MU_DataSource(APP_DATASOURCE);
$today = new MU_Date('now', '%m/%d/%Y %H:%M:%S');
$yesterday = $today->calculate('-1 day');
$services = $dbh->queryall_list('
select lower(name), display_name
from was_service
order by display_name
');
$actions = $dbh->queryall_list('Manipulating rows as they are added
select action_id, action
from was_log_action
order by action
');
global $services, $actions;
$qb = new MU_Query_Builder('log_query_rbon');
$qb->type = 'table';
$qb->report_builder = 'on';
$qb->default_container = 'was_log';
$qb->datasource = APP_DATASOURCE;
$qb->record_limit = 100;
$qb->title = 'Search for records where:';
$qb->help = 'Enter search criteria and click "Search".';
$qb->set_row_callback('convert_codes');
$qb->add_table('was_log');
$qb->add_field('uniqueid', array('input_type' => 'text', 'default_criteria' => 'eq',
'convert' => 'value:lowercase', 'sortable' => '1'), 'UniqueID');
$qb->add_field('service', array('input_type' => 'list', 'list_source' => $services,
'sortable' => '1'), 'Service');
$qb->add_field('action', array('input_type' => 'list', 'list_source' => $actions,
'sortable' => '1'), 'Action');
$qb->add_field('timestamp', array('input_type' => 'text', 'default_criteria' => 'gt',
'convert' => 'value:date', 'input_hint' => 'mm/dd/yyyy hh24:mi:ss',
'data_format' => '%m/%d/%Y %H:%M:%S', 'default_value' => $yesterday,
'sortable' => '1'), 'Timestamp');
$qb->add_field('ip_address', array('input_type' => 'text'), 'IP Address');
$body->add_object($qb);
$qb->output($body);
$presentation->display( array (
'title' => 'WAS Log Viewer',
'appname' => APPNAME
));
function convert_codes (&$data) {operates
global $services, $actions;
$data['service'] = $services[$data['service']];
$data['action'] = $actions[$data['action']];
}
?>
This example creates a query builder that allows the user to specify
criteria for searching and fields to view in the output.
We first create a new query builder object:
$qb = new MU_Query_Builder('log_query_rbon');
The first parameter is the unique name of this query builder object.
Next we set some attributes.
$qb->type = 'table';
$qb->report_builder = 'on';
$qb->default_container = 'was_log';
$qb->datasource = APP_DATASOURCE;
$qb->record_limit = 100;
$qb->title = 'Search for records where:';
$qb->help = 'Enter search criteria and click "Search".';
This particular query builder is running in table mode with the
report builder enabled.
Since this is a table mode query builder, we need to add our table.
$qb->add_table('was_log');
If more than one table was to be available, we would also supply
limit conditions used to join the tables in a query.
Now we add fields representing the criteria a user can search on.
$qb->add_field('uniqueid', array('input_type' => 'text', 'default_criteria' => 'eq',
'convert' => 'value:lowercase', 'sortable' => '1'), 'UniqueID');
$qb->add_field('service', array('input_type' => 'list', 'list_source' => $services,
'sortable' => '1'), 'Service');
$qb->add_field('action', array('input_type' => 'list', 'list_source' => $actions,
'sortable' => '1'), 'Action');
$qb->add_field('timestamp', array('input_type' => 'text', 'default_criteria' => 'gt',
'convert' => 'value:date', 'input_hint' => 'mm/dd/yyyy hh24:mi:ss',
'data_format' => '%m/%d/%Y %H:%M:%S', 'default_value' => $yesterday,
'sortable' => '1'), 'Timestamp');
$qb->add_field('ip_address', array('input_type' => 'text'), 'IP Address');
The next two command first add the query builder object to our
presentation object, and then tell the query builder to create its output
in the body object.
$body->add_object($qb);
$qb->output($body);
The presentation object is then displayed as normal the query
builder handles all user input.