NAME
Relations::Display - DBI/DBD::mysql Query Graphing Module
SYNOPSIS
# DBI, Relations::Display Script that creates a couple
# graphs from queries.
#!/usr/bin/perl
use DBI;
use Relations::Display;
$dsn = "DBI:mysql:watcher";
$username = "root";
$password = '';
$dbh = DBI->connect($dsn,$username,$password,{PrintError => 1, RaiseError => 0});
my $Display = new Relations::Display($dbh);
$Display->add_member(-name => 'region',
-label => 'Region',
-database => 'watcher',
-table => 'region',
-id_field => 'reg_id',
-select => {'id' => 'reg_id',
'label' => 'reg_name'},
-from => 'region',
-order_by => "reg_name");
$Display->add_member(-name => 'sales_person',
-label => 'Sales Person',
-database => 'watcher',
-table => 'sales_person',
-id_field => 'sp_id',
-select => {'id' => 'sp_id',
'label' => "concat(f_name,' ',l_name)"},
-from => 'sales_person',
-order_by => ["l_name","f_name"]);
$Display->add_lineage(-parent_name => 'region',
-parent_field => 'reg_id',
-child_name => 'sales_person',
-child_field => 'reg_id');
$Display->set_chosen(-label => 'Sales Person',
-ids => '2,5,7');
$available = $Display->get_available(-label => 'Region');
print "Found $available->{count} Regions:\n";
foreach $id (@{$available->{ids_arrayref}}) {
print "Id: $id Label: $available->{labels_hashref}->{$id}\n";
}
$dbh->disconnect();ABSTRACT
This perl library uses perl5 objects to simplify creating graphs from MySQL queries.
The current version of Relations::Display is available at
http://www.gaf3.comDESCRIPTION
WHAT IT DOES
The Relations::Display object takes in your query through a Relations::Query object, along with information pertaining to which field values from the query results are to be used in creating the graph title, x axis label and titles, legend label (not used on the graph) and titles, and y axis data.
It does this by looping through the query while taking into account which fields you want to use for the x axis and legend. While looping, it figures out which of these fields have all the same value throughout the query and which have different values. The fields with the same values have their values placed in the title of the graph, while the fields with different values have their values placed in either the x axis or legend, which is set by the user.
Relations::Display can return either the raw query results in the form of a Relations select_matrix() return value, a Relations::Display::Table object, or a GD::Graph object. It obtains this data in stages. Relations::Display gets its matrix data from the query object, the Relations::Display::Table data from the matrix data, and the GD::Graph data from the Relations::Display::Table data.
CALLING RELATIONS::DISPLAY ROUTINES
All standard Relations::Display routines use both an ordered and named argument calling style. This is because some routines have as many as fourteen arguments, and the code is easier to understand given a named argument style, but since some people, however, prefer the ordered argument style because its smaller, I'm glad to do that too.
If you use the ordered argument calling style, such as
$display->add('Book,ISBN','Publisher,Category,Discount',{interlaced => 0},'ISBN');the order matters, and you should consult the function defintions later in this document to determine the order to use.
If you use the named argument calling style, such as
$famimly->add(-x_axis => 'Book,ISBN',
-legend => 'Publisher,Category,Discount',
-settings => {interlaced => 0},
-hide => 'ISBN');the order does not matter, but the names, and minus signs preceeding them, do. You should consult the function defintions later in this document to determine the names to use.
In the named arugment style, each argument name is preceded by a dash. Neither case nor order matters in the argument list. -name, -Name, and -NAME are all acceptable. In fact, only the first argument needs to begin with a dash. If a dash is present in the first argument, Relations::Display assumes dashes for the subsequent ones.
LIST OF RELATIONS::DISPLAY FUNCTIONS
An example of each function is provided in either 'test.pl' and 'demo.pl'.
new
$display = new Relations::Display($abstract,
$query,
$chart,
$width,
$height,
$x_axis,
$legend,
$data,
$settings,
$hide,
$vertical,
$horizontal,
$matrix,
$table);
$display = new Relations::Display(-abstract => $abstract,
-query => $query,
-chart => $chart,
-width => $width,
-height => $height,
-x_axis => $x_axis,
-legend => $legend,
-data => $data,
-settings => $settings,
-hide => $hide,
-vertical => $vertical,
-horizontal => $horizontal,
-matrix => $matrix,
-table => $table);Creates creates a new Relations::Display object.
$abstract and $query - The Relations::Abstract object to use and the Relations::Query object to send to that database handle. These are unneccesary if you supply a $matrix or $table value.
$chart, $width and $height - The GD::Graph chart type to use, and the width and height of the GD::Graph. Width and height must be set. There is no defaults.
$x_axis and $legend - The fields to use for the x axis and legend values. Can be either a comma delimmitted string, or an array. The names sent must exactly match the field names in the query.
$data - The fields to use for the data values of the graph. The name sent must exactly match the field name in the query.
$settings - GD::Graph settings to to set on the graph object. Must be a hash of settings to set keyed by the setting name.
$hide - The fields to use for the x axis and legend values but to hide on the actual display. Can be either a comma delimmitted string, an array, or a hash with true values keyed by the field names. The names sent must exactly match the field names in the query.
$vertical and $horizontal - The fields to use for drawing vertical and horizontal lines on the graph. These fields must also be in the legend settings settings, since the color of the lines drawn on the graph will be the color of the legend thay are connected to. If the x axis min and max is not set, the vertical lines values indicate on which x axis title to drawn lines on (fractions work I think), ie 0=first x axis title, 1-scound, etc. If the x axis min and max is set, the vertical lines values indicate the numeric graph value on which to drawn lines.
$matrix - Matrix value to use to create the Relations::Display::Table object value and or GD::Graph value. Uneccessary if is you supply a table argument.
$table - Relations::Display::Table value to use to create the GD::Graph object.
add
$display->add($x_axis,
$legend,
$settings,
$hide,
$vertical,
$horizontal);
$display->add(-x_axis => $x_axis,
-legend => $legend,
-settings => $settings,
-hide => $hide,
-vertical => $vertical,
-horizontal => $horizontal);Adds additional settings to a Relations::Display object. It does not override any of the current settings.
$x_axis and $legend - The fields to use for the x axis and legend values. Can be either a comma delimmitted string, or an array. The names sent must exactly match the field names in the query.
$settings - GD::Graph settings to to set on the graph object. Must be a hash of settings to set keyed by the setting name.
$hide - The fields to use for the x axis and legend values but to hide on the actual display. Can be either a comma delimmitted string, an array, or a hash with true values keyed by the field names. The names sent must exactly match the field names in the query.
$vertical and $horizontal - The fields to use for drawing vertical and horizontal lines on the graph. These fields must also be in the legend settings settings, since the color of the lines drawn on the graph will be the color of the legend thay are connected to. If the x axis min and max is not set, the vertical lines values indicate on which x axis title to drawn lines on (fractions work I think), ie 0=first x axis title, 1-scound, etc. If the x axis min and max is set, the vertical lines values indicate the numeric graph value on which to drawn lines.
set
$display->set($abstract,
$query,
$chart,
$width,
$height,
$x_axis,
$legend,
$data,
$settings,
$hide,
$vertical,
$horizontal,
$matrix,
$table);
$display->set(-abstract => $abstract,
-query => $query,
-chart => $chart,
-width => $width,
-height => $height,
-x_axis => $x_axis,
-legend => $legend,
-data => $data,
-settings => $settings,
-hide => $hide,
-vertical => $vertical,
-horizontal => $horizontal,
-matrix => $matrix,
-table => $table);Overrides any current setttings of the Relations::Display object. It does not add to any of the values.
$abstract and $query - The Relations::Abstract object to use and the Relations::Query object to send to that database handle. These are unneccesary if you supply a $matrix or $table value.
$chart, $width and $height - The GD::Graph chart type to use, and the width and height of the GD::Graph. Width and height must be set. There is no defaults.
$x_axis and $legend - The fields to use for the x axis and legend values. Can be either a comma delimmitted string, or an array. The names sent must exactly match the field names in the query.
$data - The fields to use for the data values of the graph. The name sent must exactly match the field name in the query.
$settings - GD::Graph settings to to set on the graph object. Must be a hash of settings to set keyed by the setting name.
$hide - The fields to use for the x axis and legend values but to hide on the actual display. Can be either a comma delimmitted string, an array, or a hash with true values keyed by the field names. The names sent must exactly match the field names in the query.
$vertical and $horizontal - The fields to use for drawing vertical and horizontal lines on the graph. These fields must also be in the legend settings settings, since the color of the lines drawn on the graph will be the color of the legend thay are connected to. If the x axis min and max is not set, the vertical lines values indicate on which x axis title to drawn lines on (fractions work I think), ie 0=first x axis title, 1-scound, etc. If the x axis min and max is set, the vertical lines values indicate the numeric graph value on which to drawn lines.
$matrix - Matrix value to use to create the Relations::Display::Table object value and or GD::Graph value. Uneccessary if is you supply a table argument.
$table - Relations::Display::Table value to use to create the GD::Graph object.
get_matrix
$matrix = $display->get_matrix();Returns the matrix value for a Relations::Display object. If the matrix value is already set in the display object, it returns that. If the matrix value is not set, it attempts to run the query with the abstract. If successful, it returns a matrix created from the query, and set the matrix value for the display object. If that fails, it returns nothing. So, if you create the display object with only $table set, this function will fail because neither the abstract, query, nor matrix value will be set.
get_table
$table = $display->get_table();Returns the Relations::Display::Table value for a Relations::Display object. If the table value is already set in the display object, it returns that. If the table value is not set, it calls its own get_matrix, and tries to create the table from the returned matrix. It'll return the new table object if successful and nothing if it is not.
get_graph
$graph = $display->get_graph();Returns the graph value for Relations::Display object. If the graph value is already set in the display object, it returns that. If the graph value is not set, it calls its own get_table, and tries to create the graph from the returned table. It'll return the new graph object if successful and nothing if it is not.
LIST OF RELATIONS::DISPLAY::TABLE FUNCTIONS
An example of each function is provided in either 'test.pl' and 'demo.pl'.
new
$table = new Relations::Display::Table($main_label,
$x_axis_label,
$legend_label,
$x_axis_values,
$legend_values,
$x_axis_titles,
$legend_titles,
$data);
$table = new Relations::Display::Table(-main_label => $main_label,
-x_axis_label => $x_axis_label,
-legend_label => $legend_label,
-x_axis_values => $x_axis_values,
-legend_values => $legend_values,
-x_axis_titles => $x_axis_titles,
-legend_titles => $legend_titles,
-data => $data);Creates creates a new Relations::Display::Table object.
$main_label - The main label for the table. String.
$x_axis_label and $legend_label - The labels to use for x axis and legend. Strings.
$x_axis_values and $legend_values - The values for the x axis and legend. Array refs.
$x_axis_titles and $legend_titles - The titles (what's to be displayed) for the x axis and legend. Used if there are fields to be hidden. Hash refs keyed by values arrays.
$data - The data for the table. 2D hash ref keyed off the x axis and legend arrays in that order.
OTHER RELATED WORK
Relations
This perl library contains functions for dealing with databases. It's mainly used as the the foundation for all the other Relations modules. It may be useful for people that deal with databases in Perl as well.
Relations::Abstract
A DBI/DBD::mysql Perl module. Meant to save development time and code space. It takes the most common (in my experience) collection of DBI calls to a MySQL databate, and changes them to one liner calls to an object.
Relations::Query
An Perl object oriented form of a SQL select query. Takes hash refs, array refs, or strings for different clauses (select,where,limit) and creates a string for each clause. Also allows users to add to existing clauses. Returns a string which can then be sent to a MySQL DBI handle.
Relations.Admin.inc.php
Some generalized PHP classes for creating Web interfaces to relational databases. Allows users to add, view, update, and delete records from different tables. It has functionality to use tables as lookup values for records in other tables.
Relations::Family
A Perl query engine for relational databases. It queries members from any table in a relational database using members selected from any other tables in the relational database. This is especially useful with complex databases; databases with many tables and many connections between tables.
Relations::Display
An Perl module creating GD::Graph objects from database queries. It takes in a query through a Relations::Query object, along with information pertaining to which field values from the query results are to be used in creating the graph title, x axis label and titles, legend label (not used on the graph) and titles, and y axis data. Returns a GD::Graph object built from from the query.
Relations::Choice
An Perl CGI interface for Relations::Family, Reations::Query, and Relations::Display. It creates complex (too complex?) web pages for selecting from the different tables in a Relations::Family object. It also has controls for specifying the grouping and ordering of data with a Relations::Query object, which is also based on selections in the Relations::Family object. That Relations::Query can then be passed to a Relations::Display object, and a graph or table will be displayed. A working model already exists in a production enviroment. I'd like to streamline it, and add some more functionality before releasing it to the world. Shooting for early mid Summer 2001.