Docs For Class MySQLTableReport

Class MySQLTableReport

Description

class MySQLTableReport Generic reporting class. Given a configuration file, that describes the tables and fields to be searched, and information to connect to a database, take form data and generate an SQL query to run.

The report object is made to address a certain class of reports: Time series data, to aggregate by some arbitrary column and return the SUM/MIN/MAX/AVG/etc of other columns.

Basic usage:

$report = new MySQLTableReport( $datasource, $tables, $report);
// run the query and get the result and header data
$result = $report->execute($sql);
$columns = $report->get_column_names();
// ... display as desired.

the parameters above have the following forms:

$datasource = array(
'host' => $host, // the mysql server hostname
'port' => $port, // optional port number
'user' => $user, // the user credential
'password' => $pass, // the password for the user
'db' => $db, // the database name
);
$tables = array(
'fact_table_name' => 'fact', // table_name => alias
'dimension_table_name' => 'dimension', // table_name => alias
);

The aliases used for tables are mostly anything you choose *except* that one table must have the alias 'fact'. This is considered the root table to which any additional tables can be joined. There can be only one fact table, but you can have any number of dimension tables as long as each have unique aliases

$report = array(
// the JOIN clause for any dimension tables. Specify the exact clause used
// for each table alias
'join' => array(
'dimension' => "USING (id)",
),
// fields for the tables; defined by the table's alias and not the real table name
// this allows the exact same report to be used on tables with the same structure
// but different names
'fields' => array(
'fact' => array(
'name' => 'clear|where',
// note that these aren't field names in the table, but we need
// a place to put them for the form processor to handle them
// the filters here will make sure the data gets added to the right
// part of the query, and not the WHERE clause like other fields.
'group' => 'group',
'order' => 'order',
'having' => 'having',
'limit' => 'limit',
),
'dimension' => array(
'date' => 'date_range|clear|where',
'price' => 'ge|clear|where'
)
), // end fields
// custom fields are allowed as well
'custom_fields' => array(
'epoch' => 'FROM_UNIXTIME(date)',
'snippet' => 'LEFT(info,15)'
)
);

author: Gavin Towey <gavin@box.com>
todo: describe the config format in more detail
todo: create a base class
todo: abstract aggregate function handling (or move to the config)
todo: create a pear package out of this
license: contact

Located in /MySQLTableReport.php (line 96)

Method Summary

MySQLTableReport __construct (array $datasource, array $tables, array $report)

\MySQLTableReport clear (string $col_name, string $var_name, string $expression, [ $op = null])

array date_range (string $col_name, string $var_name, string $expression)

array execute ([string $sql = null])

\MySQLTableReport from (array $table)

array ge (string $col_name, string $var_name, string $expression)

srray get_column_names ()

array get_custom_fields ()

array get_distinct_values (string $table, string $colname)

array get_form_fields ()

array get_form_field_values ()

array get_pivot_values (string $col_name)

string get_search_uri ()

array get_tables ()

string get_table_by_alias (string $alias)

array get_table_fields ([string $table_name = null])

\MySQLTableReport group (string $col_name, string $var_name, string $expression)

array gt (string $col_name, string $var_name, string $expression)

\MySQLTableReport having ( $key, $field, string $expression, string $col_name, string $var_name)

\MySQLTableReport join (array $table)

array le (string $col_name, string $var_name, string $expression)

array like (string $col_name, string $var_name, string $expression)

\MySQLTableReport limit ( $key, $field, string $expression, string $col_name, string $var_name)

array lt (string $col_name, string $var_name, string $expression)

array ne (string $col_name, string $var_name, string $expression)

\MySQLTableReport order ( $key, $field, string $expression, string $col_name, string $var_name)

\MySQLTableReport|string pivot (string $col_name, string $var_name, string $expression)

void process_form_data ()

string query ()

void raw_where (string $key, string $field, string $expression)

\MySQLTableReport select (string $field, string $alias, string $aggregate)

void set_pivot_values (string $col_name, array $values)

\MySQLTableReport where (string $key, string $var_name, string $value, [string $op = null])

Methods

Constructor __construct (line 127)

create a new instance, pass configuration information describing the datasource and the report tables and fields.

MySQLTableReport __construct (array $datasource, array $tables, array $report)

array $datasource: Database connection information required :host,user,password,db; optional: port
array $tables: Tables to use for this report. format is array( 'table_name' => 'alias' ) there must be at least one "fact" table, and optionally a "dimension" table
array $report: config array describing the table structure and other options

clear (line 567)

Remove blank strings as values in form fields.

Most cases, the forms you create can have empty fields which mean those conditions should be omitted from the WHERE clause. However, the form will send and empty string. When you want an empty field to be removed from the WHERE clause, pass it through the clear filter first.

'fields' => array( 'dimension' => array( 'hostname' => 'clear|where', // if hostname is blank, do not include it in the query. ) )

access: public

\MySQLTableReport clear (string $col_name, string $var_name, string $expression, [ $op = null])

string $col_name: the name of the form field as a column
string $var_name: the form variable name
string $expression: the value of the field
$op

date_range (line 541)

look for a range of date values for the given column, and return values to be added to the WHERE clause

This is used when you have a column like "invoice_date" in the report, but what you really want to search for is a range of dates bewteen a given start and end date.

To do that, create form fields with _start and _end added to the name, and pass the column to this processor. It will search for the appropriate form fields and build the range.

The config settings would look like:

'fields' => array( 'dimension' => array( 'invoice_date' => 'date_range|clear|where', ) )

return: the list of expressions to pass to the where function
access: public

array date_range (string $col_name, string $var_name, string $expression)

string $col_name: the base column name
string $var_name: the base field variable name
string $expression: ignored

execute (line 888)

Execute the generated query on the configured databse and return a result handle

return: array that contains the result set
throws: Exception if there is an error executing the query
access: public

array execute ([string $sql = null])

string $sql: optional sql to execute.

from (line 362)

define the primary table to select from

access: public

\MySQLTableReport from (array $table)

array $table: The table to select from; the format is array(table_name, alias)

ge (line 599)

apply a "greater than or equal to" operator to a WHERE condition, instead of the default equality matching

By default a configuration section like this would produce equality matching:

'fields' => array( 'dimension' => array( 'price' => 'clear|where', // generates SQL such as: WHERE price = <some value> ) )

If you need a range of values, include the appropriate operator as a filter:

'fields' => array( 'dimension' => array( 'price' => 'clear|ge|where', // generates SQL such as: WHERE price >= <some value> ) )

return: condition to pass to next filter
access: public

array ge (string $col_name, string $var_name, string $expression)

string $col_name: The column name
string $var_name: The field variable name
string $expression: The field value

get_column_names (line 874)

retuns a list of all column names. These will be exactly the same as the columns returned by the query.

return: the list of column names
access: public

srray get_column_names ()

get_custom_fields (line 269)

returns a list of custom fields names.

Custom fields are additional columns that can be used in the SELECT clause, but not as WHERE or other conditions. They are defined in the configuration used to create the object

return: the custom field list
access: public

array get_custom_fields ()

get_distinct_values (line 320)

given a table and column, find all the unique values. This is a utility method often used when building dropdown lists on a search form, or getting values for pivot operations.

return: the list of unique values
access: public

array get_distinct_values (string $table, string $colname)

string $table: the table name
string $colname: the column name

get_form_fields (line 247)

return the list of form fields defined by the configuration parameters used to construct this object. Field names are prefixed by the table *alias*

so if the configuration section looked like : 'fields' => array( 'fact' => array( 'checksum' => '...', ), 'dimension' => array( 'hostname' => '...', ),

The result would be an array with the values ('fact-checksum', 'dimension-hostname').

These are the form field names that will be checked to build the search parameters.

return: the form fields
access: public

array get_form_fields ()

get_form_field_values (line 335)

return an associate array with form_field_name => value for all fields.

return: the array of field names and values
access: public

array get_form_field_values ()

get_pivot_values (line 180)

return the list of values for a given pivot column

return: the list of values defined by set_pivot_values
access: public

array get_pivot_values (string $col_name)

string $col_name: the name of the pivot column

get_search_uri (line 923)

return a urlencoded string of parameters that were used in this report.

return: The url string
access: public

string get_search_uri ()

get_tables (line 205)

returns the list of table names, not the aliases

access: public

array get_tables ()

get_table_by_alias (line 216)

gets the concrete name of a table for the given alias

return: The real table name
throws: Exception if the alias doesn't exist
access: public

string get_table_by_alias (string $alias)

string $alias: The alias name to fetch the table for

get_table_fields (line 279)

select the field names for the report tables from the database.

return: the list of columns defined in the database tables.
access: public

array get_table_fields ([string $table_name = null])

string $table_name: optional table name. If none is provided, all tables defined in the report will be queried.

group (line 403)

set the GROUP BY expression

access: public

\MySQLTableReport group (string $col_name, string $var_name, string $expression)

string $col_name: the name of the form field as a column
string $var_name: the form variable name
string $expression: the group by expression

gt (line 621)

greater than: see documentation for ge()

return: condition to pass to next filter
access: public

array gt (string $col_name, string $var_name, string $expression)

string $col_name: The column name
string $var_name: The field variable name
string $expression: The field value

having (line 443)

set the HAVING clause

access: public

\MySQLTableReport having ( $key, $field, string $expression, string $col_name, string $var_name)

string $col_name: the name of the form field as a column
string $var_name: the form variable name
string $expression: the havin expression
$key
$field

join (line 373)

add a table to the JOIN clause

access: public

\MySQLTableReport join (array $table)

array $table: The table to join; the format is array(table_name, alias)

le (line 610)

less than or equal to: see documentation for ge()

return: condition to pass to next filter
access: public

array le (string $col_name, string $var_name, string $expression)

string $col_name: The column name
string $var_name: The field variable name
string $expression: The field value

like (line 654)

like: see documentation for ge()

return: condition to pass to next filter
access: public

array like (string $col_name, string $var_name, string $expression)

string $col_name: The column name
string $var_name: The field variable name
string $expression: The field value

limit (line 430)

set the LIMIT clause

access: public

\MySQLTableReport limit ( $key, $field, string $expression, string $col_name, string $var_name)

string $col_name: the name of the form field as a column
string $var_name: the form variable name
string $expression: the limit expression
$key
$field

lt (line 632)

less than: see documentation for ge()

return: condition to pass to next filter
access: public

array lt (string $col_name, string $var_name, string $expression)

string $col_name: The column name
string $var_name: The field variable name
string $expression: The field value

ne (line 643)

not equals: see documentation for ge()

return: condition to pass to next filter
access: public

array ne (string $col_name, string $var_name, string $expression)

string $col_name: The column name
string $var_name: The field variable name
string $expression: The field value

order (line 417)

set the ORDER BY clause

access: public

\MySQLTableReport order ( $key, $field, string $expression, string $col_name, string $var_name)

string $col_name: the name of the form field as a column
string $var_name: the form variable name
string $expression: the order by expression
$key
$field

pivot (line 493)

preform a pivot on a column. Get the unique list of values

and return them as a conditional aggregate expression to be added to the select clause. Right now only one aggregate type is supported: SUM Also a little black magic is used to get the column name from the synthetic column name needed here. The column in the form and config should be called: pivot-{$column_name} and it's value should be the column to return when the expression is true.

For example, if you have a hostname column with a count of signups, and you want to pivot on the hostname and return the aggregate signups for each host as its own column, then the form field should look like:

<input type="checkbox" name="dimension-pivot-hostname" value="signups" /> Count signups per-host

The report object config would look like:

'fields' => array( 'dimension' => array( 'pivot-hostname' => 'pivot|select', ) )

Then remember to set the unique list of value for this pivot operation:

$report = new MySQLTableReport( ... ); $hosts = $report->get_distinct_values('dimension','hostname'); $report->set_pivot_values('dimension-pivot-hostname', $hosts);

access: public

\MySQLTableReport|string pivot (string $col_name, string $var_name, string $expression)

string $col_name: The name of the pivot column
string $var_name: The field variable name
string $expression: The column to return in the IF($col_name}='value' ... ) expression

process_form_data (line 705)

Read all form data and process values. This will be called automatically by query() and execute() methods.

access: public

void process_form_data ()

query (line 790)

generate the SQL query and return it as a string.

return: the SQL query build by this report object
access: public

string query ()

raw_where (line 455)

raw_where is an unprocessed string that is added to the WHERE clause

access: public

void raw_where (string $key, string $field, string $expression)

string $key: ignored
string $field: ignored
string $expression: The raw WHERE expression

select (line 351)

add a column to the select field list

access: public

\MySQLTableReport select (string $field, string $alias, string $aggregate)

string $field: the field name
string $alias: the field alias
string $aggregate: how to optionally aggregate values in this column

set_pivot_values (line 170)

pivot operations require some setup -- this defines the list of values to turn into additional columns when we ask the report to pivot a column.

access: public

void set_pivot_values (string $col_name, array $values)

string $col_name: the name of the column to pivot
array $values: the list of values

where (line 387)

add a condition to the WHERE clause.

access: public

\MySQLTableReport where (string $key, string $var_name, string $value, [string $op = null])

string $key: the full column name including table alias
string $var_name: the name of the form variable for this column
string $value: the form value
string $op: the conditional operator to use; default =

Documentation generated on Mon, 12 Mar 2012 11:59:41 -0700 by phpDocumentor 1.4.4