The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
Relations-Display-0.92
River stage zero No dependents
++
/ Relations::Display

NAME

Relations::Display - DBI/DBD::mysql Query Graphing Module

SYNOPSIS

  # DBI, Relations::Display Script that creates a 
  # matrix, table, and graph from a query. 

  use DBI;
  use Relations;
  use Relations::Query;
  use Relations::Abstract;
  use Relations::Display;

  $dsn = "DBI:mysql:watcher";

  $username = "root";
  $password = '';

  $dbh = DBI->connect($dsn,$username,$password,{PrintError => 1, RaiseError => 0});

  $abstract = new Relations::Abstract($dbh);

  $display = new Relations::Display(-abstract   => $abstract,
                                    -query      => {-select   => {total  => "count(*)",
                                                                  first  => "'Bird'",
                                                                  second => "'Count'",
                                                                  third  => "if(gender='Male','Boy','Girl')",
                                                                  tao    => "if(gender='Male','Yang','Yin')",
                                                                  sex    => "gender",
                                                                  kind   => "sp_name",
                                                                  id     => "species.sp_id",
                                                                  fourth => "(species.sp_id+50)",
                                                                  vert   => "2",
                                                                  horiz  => "1.5"},
                                                    -from     => ['bird','species'],
                                                    -where    => ['species.sp_id=bird.sp_id',
                                                                  'species.sp_id < 4'],
                                                    -group_by => ['sp_name','gender','first','second'],
                                                    -order_by => ['gender','sp_name']},
                                    -x_axis     => 'first,kind,id,fourth',
                                    -legend     => 'second,third,tao,sex,vert,horiz',
                                    -y_axis     => 'total',
                                    -hide       => 'fourth,third,vert,horiz',
                                    -vertical   => 'vert',
                                    -horizontal => 'horiz');

  $matrix = $display->get_matrix();

  $table = $display->get_table();

  $display->set(-chart  => 'bars',
                -width  => 400,
                -height => 400,
                -settings => {y_min_value => 0,
                              y_max_value => 3,
                              y_tick_number => 3,
                              transparent => 0}
                );

  $gph = $display->get_graph();

  $gd = $gph->gd();

  open(IMG, '>test.png') or die $!;
  binmode IMG;
  print IMG $gd->png;

  $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://relations.sourceforge.net

DESCRIPTION

WHAT IT DOES

The Relations::Display object takes in your query, 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 value throughout the query results have their value placed in the title of the graph, while the fields with different values throughout have their value 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, named and hashed argument calling style. This is because some routines have as many as fifteen 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

  $display->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.

If you use the hashed argument calling style, such as

  $display->add({x_axis   => 'Book,ISBN',
                 legend   => 'Publisher,Category,Discount',
                 settings => {interlaced => 0},
                 hide     => 'ISBN'});

or

  $display->add({-x_axis   => 'Book,ISBN',
                 -legend   => 'Publisher,Category,Discount',
                 -settings => {interlaced => 0},
                 -hide     => 'ISBN'});

the order does not matter, but the names, and curly braces do, (minus signs are optional). You should consult the function defintions later in this document to determine the names to use.

In the hashed arugment style, no dashes are needed, but they won't cause problems if you put them in. Neither case nor order matters in the argument list. settings, Settings, SETTINGS are all acceptable. If a hash is the first argument, Relations::Display assumes that is the only argument that matters, and ignores any other arguments after the {}'s.

QUERY ARGUMENTS

Some of the Relations functions recognize an argument named query. This argument can either be a hash or a Relations::Query object.

The following calls are all equivalent for $object->function($query).

  $object->function({select => 'nothing',
                     from   => 'void'});

  $object->function(Relations::Query->new(-select => 'nothing',
                                          -from   => 'void'));

LIST OF RELATIONS::DISPLAY FUNCTIONS

An example of each function is provided in 'test.pl'.

new

  $display = new Relations::Display($abstract,
                                    $query,
                                    $chart,
                                    $width,
                                    $height,
                                    $prefix,
                                    $x_axis,
                                    $y_axis,
                                    $legend,
                                    $aggregate,
                                    $settings,
                                    $hide,
                                    $vertical,
                                    $horizontal,
                                    $matrix,
                                    $table);

  $display = new Relations::Display(-abstract   => $abstract,
                                    -query      => $query,
                                    -chart      => $chart,
                                    -width      => $width,
                                    -height     => $height,
                                    -prefix     => $prefix,
                                    -x_axis     => $x_axis,
                                    -y_axis     => $y_axis,
                                    -legend     => $legend,
                                    -aggregate  => $aggregate,
                                    -settings   => $settings,
                                    -hide       => $hide,
                                    -vertical   => $vertical,
                                    -horizontal => $horizontal,
                                    -matrix     => $matrix,
                                    -table      => $table);

Creates creates a new Relations::Display object.

$abstract - The Relations::Abstract object to use. This must be sent because Relations::Display uses the $abstract object to report errors. If this is not sent, the program will die.

$query - The Relations::Query object to run to get the display data. This is 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. All three of these must be set. There are no defaults.

$prefix - The prefix to put before the auto generated label.

$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.

$y_axis and $aggregate- The field to use for the y axis values of the graph and how those values are to be stored. If you using a GD:Graph module that requires aggregate data, like boxplot, then set $aggregate to 1. Else, forget about it.

$settings - GD::Graph settings to set on the graph object. Must be a hash of settings to set keyed by the setting name. Use this to set the title, x_label, y_label, and legend_label (table only) of the Relations::Display::Table and GD::Graph.

$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, 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 titles 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 of the values already set.

$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 set on the graph object. Must be a hash of settings to set keyed by the setting name. Use this to set the title, x_label, y_label, and legend_label (table only) of the Relations::Display::Table and GD::Graph.

$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, 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 titles 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,
                $prefix,
                $x_axis,
                $y_axis,
                $legend,
                $aggregate,
                $settings,
                $hide,
                $vertical,
                $horizontal,
                $matrix,
                $table);

  $display->set(-abstract   => $abstract,
                -query      => $query,
                -chart      => $chart,
                -width      => $width,
                -height     => $height,
                -prefix     => $prefix,
                -x_axis     => $x_axis,
                -y_axis     => $y_axis,
                -legend     => $legend,
                -aggregate  => $aggregate,
                -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 - The Relations::Abstract object to use.

$query - The Relations::Query object to run to get the display data. This is 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. All three of these must be set. There are no defaults.

$prefix - The prefix to put before the auto generated label.

$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.

$y_axis and $aggregate- The field to use for the y axis values of the graph and how those values are to be stored. If you using a GD:Graph module that requires aggregate data, like boxplot, then set $aggregate to 1. Else, forget about it.

$settings - GD::Graph settings to set on the graph object. Must be a hash of settings to set keyed by the setting name. Use this to set the title, x_label, y_label, and legend_label (table only) of the Relations::Display::Table and GD::Graph.

$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, 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 titles 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.

clone

  $clone = $display->clone();

Creates a copy of a Relations::Display object and returns it.

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 and calls the Relations::Abstract object's report_error() function. So, if you create the display object with only $table set, this function will fail because neither the 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. If that fails, it returns nothing and calls the Relations::Abstract object's report_error() function.

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. If that fails, it returns nothing and calls the Relations::Abstract object's report_error() function.

LIST OF RELATIONS::DISPLAY PROPERTIES

abstract - The Relations::Abstract object

query - The Relations::Query object

chart - The name of the GD::Graph module

width - The width of the GD::Graph in pixels

height - The width of the GD::Graph in pixels

prefix - The prefix put before the autogenerated title

x_axis - Array ref of all the x axis fields

y_axis - The y axis field

legend - Array ref of all the legend fields

aggregate - Whether to store the data in aggregate format.

settings - Hash of settings to send to the GD:Graph object, keyed on each property's name.

hide - Hash ref of all the fields to hide. Keyed by field name, with a value of 1.

vertical - Array ref of all the vertical line fields. Display will drawn a vertical line on the graph for value of these fields.

horizontal - Array ref of all the horizontal line fields. Display will drawn a horizontal line on the graph for value of these fields.

matrix - The matrix object. This is returned from the Relations::Abstract's select_matrix() function. Array ref of rows of data, which are hash ref keyed by field name.

table - The table object. See Relations::Display::Table for more info.

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($title,
                                         $x_label,
                                         $y_label,
                                         $legend_label,
                                         $x_axis_values,
                                         $legend_values,
                                         $x_axis_titles,
                                         $legend_titles,
                                         $y_axis_values);

  $table = new Relations::Display::Table(-title         => $title,
                                         -x_label       => $x_label,
                                         -y_label       => $y_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,
                                         -y_axis_values => $y_axis_values);

Creates creates a new Relations::Display::Table object.

$title - The main label for the table. String.

$x_label, $y_label and $legend_label - The labels to use for x axis, y 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.

$y_axis_values - The y axis data for the table. 2D hash ref keyed off the x axis and legend arrays in that order.

LIST OF RELATIONS::DISPLAY::TABLE PROPERTIES

title - The title.

x_label - The x axis label.

y_label - The y axis label.

legend_label - The legend label.

x_axis_values - Array ref of actual x_axis values to key the y_axis_values with.

legend_values - Array ref of actual legend values to key the y_axis_values with.

x_axis_titles - Hash ref of displayed x_axis values. What's left after the fields specified by hide are removed. Keyed by the the x axis values. $table->{x_axis_titles}->{$x_axis_value}

legend_titles - Hash ref of displayed legend values. What's left after the fields specified by hide are removed. Keyed by the the legend values. $table->{legend_titles}->{$legend_value}

y_axis_values - Hash ref of displayed legend values. What's left Teh data of the table. A hash ref of data keyed by the x_axis and legend values in that order. $table->{y_axis_values}->{$x_axis_value}{$legend_value}

CHANGE LOG

Relations-Display-0.92

Fixed X Axis and Legend Evals

Fixed a problem with x axis and legend field values. Any field names that contained spaces would have their values show up as blank in the Graph and Table. That's fixed now.

Relations-Display-0.91

Added Argument Cloning

Instead of just grabbing sent array refs, hash refs, queries and such, Relations::Display now makes complete copies of all these objects. This was done because Relations::Report was messing up the Display's settings when it passed them to Report.

Added Object Cloning

Both Relations::Display and Relations::Display::Table now have clone() functions to create copies of themselves. This will be very useful for Relations::Report's iterate functionality.

Arguments and Properties Renamed

Many of the argument and property names were changed to be more consistent with GD::Graph. This may be annoying to deal with, but better to do it now rather than later.

TO DO

Improve Error Checking

Add some warnings if fields specified in various arguments are not present in the matrix data returned from the query. Add To Text Functionality

Add a to_text() function to both Relations::Display and Relations::Display::Table. This will make it easier to debug.

Pass -w

Clean up the code so it pass 'perl -w'. I completely forgot about making sure everything did that.

OTHER RELATED WORK

Relations (Perl)

Contains functions for dealing with databases. It's mainly used as the foundation for the other Relations modules. It may be useful for people that deal with databases as well.

Relations-Query (Perl)

An object oriented form of a SQL select query. Takes hashes. arrays, 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 database.

Relations-Abstract (Perl)

Meant to save development time and code space. It takes the most common (in my experience) collection of calls to a MySQL database, and changes them to one liner calls to an object.

Relations-Admin (PHP)

Some generalized objects for creating Web interfaces to relational databases. Allows users to insert, select, update, and delete records from different tables. It has functionality to use tables as lookup values for records in other tables.

Relations-Family (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 (Perl)

Module creating graphs 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 graph and/or table built from from the query.

Relations-Report (Perl)

A Web 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 and/or table will be displayed.

Relations-Structure (XML)

An XML standard for Relations configuration data. With future goals being implmentations of Relations in different languages (current targets are Perl, PHP and Java), there should be some way of sharing configuration data so that one can switch application languages seamlessly. That's the goal of Relations-Structure A standard so that Relations objects can export/import their configuration through XML.