DBI extension module for rendering charts directly from any DBI and SQL compatible database. DBIx::Chart extends SQL to return a VARBINARY image value using aggregate function syntax to define the type of chart, and various pseudo-columns to define the properties of the image. The following types of charts are supported (via DBD::Chart):

pie charts
bar charts
histograms
box & whisker charts
linegraphs
pointgraphs (scatter graphs)
areagraphs
candlestick graphs
Gantt charts
quadtree aka treemap graphs

Why DBIx::Chart ? One of the most common uses for data extracted from a database is visualization. Prior to DBIx::Chart, extra software was always required, and extra programming steps needed to render those charts, and the APIs and/or tools for generating the charts often didn't mate well with existing SQL tools. With DBIx::Chart, every DBMS appears to have an internal rendering engine, accessed entirely via SQL.

DBIx::Chart is a pure Perl subclass of DBI.

Current Version

Release 0.05

DBIx-Chart-0.05.tar.gz

Prerequisites and Conformance

DBIx::Chart requires the following Perl modules(versions are recommended only, as they're the version I built with...if you're feeling frisky, you can try older or newer versions.):

Perl version 5.8.0
DBI version 1.28.
DBD::Chart 0.80
GD module 1.32
GD::Text (aka GDTextUtil) module 0.80
Time::Local
DBD::CSV 1.024 (for running the test script)

In addition, the following (non-Perl) libraries are required:

libgd
libpng
zlib
jpeg-6b (for JPEG support)

Since DBIx::Chart is a simple subclass of DBI, all the usual DBI functions are supported.

EXAMPLES

Refer to the included t/plottest.t test script for complete details of connecting, populating datapoints, and fetching the image and any associated imagemap. Here's a bit of code to generate a piechart from an Oracle connection:

use DBIx::Chart;
my $dbh = DBIx::Chart->connect('dbi:Oracle:bunceville', $user, $pass)
    or die "Cannot connect\n";
#
#    example: create a pie chart
#
$rsth = $dbh->prepare(
"SELECT region, sum(sales) FROM widget_sales_tbl
 WHERE year=2002 AND product='widgets'
 GROUP BY region
 RETURNING PIECHART(*)
 WHERE WIDTH=400 AND HEIGHT=400 AND
 TITLE = '2002 Widget Sales By Region' AND
 COLOR IN ('red', 'green', 'blue', 'lyellow', 'lpurple') AND
 BACKGROUND='lgray' AND
 SIGNATURE='Copyright(C) 2001, GOWI Systems, Inc.'");
$rsth->execute;
$rsth->bind_col(1, \$buf);
$rsth->fetch;

The following set of charts were rendered using DBIx::Chart. The SQL statement used to create a chart is available by clicking the indicated link above each image.

Simple Linegraph with Logo See the SQL

select * from simpline
    returning linegraph(*), imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND X_AXIS='Some Domain'
    AND Y_AXIS='Some Range'
    AND TITLE='Linegraph Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND LOGO='t/gowilogo.png'
    AND FORMAT='PNG'
    AND SHOWGRID=1
    AND LINEWIDTH=4
    AND MAPNAME='simpline'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'
    AND COLOR='newcolor'
    AND SHAPE='fillcircle'
    AND SHOWVALUES=1

Simple Pointgraph with Logo See the SQL

select * from simpline
    returning pointgraph(x,y), imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND X_AXIS='Some Domain'
    and Y_AXIS='Some Range'
    AND TITLE='Scattergraph Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND LOGO='t/gowilogo.png'
    AND FORMAT='PNG'
    AND SHOWGRID=0
    AND MAPNAME='simpscat_map'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'
    AND SHOWVALUES=1

Simple Areagraph See the SQL

select * from simpline
    returning areagraph(*), imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND X_AXIS='Some Domain'
    AND Y_AXIS='Some Range'
    AND TITLE='Areagraph Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND LOGO='t/gowilogo.png'
    AND FORMAT='PNG'
    AND SHOWGRID=1
    AND MAPNAME='simparea_map'
    AND COLOR='newcolor'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML' AND SHOWVALUES=0

Symbolic Range Linegraph See the SQL

select * from symline
    returning linegraph(*), imagemap
    where WIDTH=500 AND HEIGHT=500
    AND X_AXIS='Some Domain'
    and Y_AXIS='Some Range'
    AND TITLE='Symbolic Domain Linegraph Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND LOGO='t/gowilogo.png'
    AND FORMAT='PNG'
    AND SHOWGRID=1
    AND MAPNAME='symline_map'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML' AND COLOR=newcolor AND SHAPE=fillcircle

Simple Barchart See the SQL

select * from symline
    returning barchart(*), imagemap
    where WIDTH=500 AND HEIGHT=500
    AND X_AXIS='Some Domain'
    and Y_AXIS='Some Range'
    AND TITLE='Barchart Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND FORMAT='PNG'
    AND SHOWVALUES=1
    AND MAPNAME='simpbar_map'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML' AND COLOR=newcolor

Iconic Barchart See the SQL

select * from symline
    returning barchart(*), imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND X_AXIS='Some Domain'
    and Y_AXIS='Some Range'
    AND TITLE='Iconic Barchart Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND FORMAT='PNG'
    AND SHOWVALUES=1
    AND ICON='t/pumpkin.png'
    AND MAPNAME='iconbars_map'
    AND SHOWGRID=1
    AND GRIDCOLOR='blue'
    AND TEXTCOLOR='dbrown'
    AND MAPSCRIPT='ONCLICK="alert(''Got X=:X, Y=:Y'')"'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'

Iconic Histogram See the SQL

select * from symline
    returning histogram(*), imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND X_AXIS='Some Domain'
    AND Y_AXIS='Some Range'
    AND TITLE='Iconic Histogram Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND FORMAT='PNG'
    AND ICON='t/pumpkin.png'
    AND MAPNAME='iconhisto_map'
    AND SHOWGRID=1
    AND GRIDCOLOR='red'
    AND TEXTCOLOR='newcolor'
    AND MAPSCRIPT='ONCLICK="alert(''Got X=:X, Y=:Y'')"'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'

Simple Box & Whisker Chart See the SQL

select * from simpbox
    returning boxchart(*), imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND X_AXIS='Some Domain'
    AND TITLE='Boxchart Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND FORMAT='PNG'
    AND COLORS IN ('newcolor', 'red')
    AND SHOWVALUES=1
    AND MAPNAME='simpbox_map'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'

Simple Candlechart See the SQL

select * from simpcandle
    returning candlestick(*), imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND X_AXIS='Some Domain'
    AND Y_AXIS = 'Price'
    AND TITLE='Candlestick Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND FORMAT='PNG'
    AND COLORS IN ('newcolor')
    AND SHAPE='fillsquare'
    AND SHOWVALUES=1
    AND SHOWGRID=1
    AND MAPNAME='simpcandle_map'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'

Basic Piechart See the SQL

select * from simppie
    returning piechart(*), imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND X_AXIS='Some Domain'
    AND TITLE='Piechart Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND FORMAT='PNG'
    AND COLORS IN ('red', 'blue', 'newcolor', 'green', 'yellow')
    AND MAPNAME='simppie_map'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'

3D Piechart See the SQL

select * from simppie
    returning piechart(*), imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND X_AXIS='Some Domain'
    AND TITLE='3-D Piechart Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND FORMAT='PNG'
    AND COLORS IN ('red', 'blue', 'newcolor', 'green', 'yellow')
    AND THREE_D=1
    AND MAPNAME='pie3d_map'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'

3D Barchart See the SQL

select * from simpline
    returning barchart(*), imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND X_AXIS='Some Domain'
    AND Y_AXIS='Some Range'
    AND TITLE='3-D Barchart Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND FORMAT='PNG'
    AND COLORS IN ('orange')
    AND THREE_D=1
    AND SHOWGRID=1
    AND MAPNAME='bar3d_map'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'

3 Axis Barchart See the SQL

select * from bar3axis
    returning barchart(*), imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND TITLE='3 Axis Barchart Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND X_AXIS='Region'
    AND Y_AXIS='Sales'
    AND Z-AXIS='Quarter'
    AND FORMAT='PNG'
    AND COLORS IN ('red')
    AND SHOWGRID=1
    AND SHOWVALUES=1
    AND MAPNAME='bar3axis_map'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'

Simple Histogram See the SQL

select * from simppie
    returning histogram(*), imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND TITLE='Histogram Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND X_AXIS='Some Domain'
    AND Y_AXIS='Some Range'
    AND FORMAT='PNG'
    AND COLOR IN ('red', 'green', 'orange', 'blue', 'newcolor')
    AND SHOWGRID=1
    AND SHOWVALUES=1
    AND MAPNAME='simphisto_map'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'

3D Histogram See the SQL

select * from simppie
    returning histogram(*), imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND TITLE='Histogram Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND X_AXIS='Some Domain'
    AND Y_AXIS='Some Range'
    AND FORMAT='PNG'
    AND COLOR='orange'
    AND THREE_D=1
    AND SHOWGRID=1
    AND SHOWVALUES=1
    AND MAPNAME='histo3d_map'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'

3 Axis Histogram See the SQL

select * from bar3axis
    returning histogram(*), imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND TITLE='3 Axis Histogram Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND X_AXIS='Region'
    AND Y_AXIS='Sales'
    AND Z_AXIS='Quarter'
    AND FORMAT='PNG'
    AND COLORS='red'
    AND SHOWGRID=1
    AND SHOWVALUES=1
    AND MAPNAME='histo3axis_map'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'

Temporal Domain Linegraph See the SQL

select * from templine
    returning linegraph(xdate, y), imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND TITLE='Temporal Domain Linegraph Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND X_AXIS='Some Domain'
    AND Y_AXIS='Some Range'
    AND X_ORIENT='VERTICAL'
    AND LOGO='t/gowilogo.png'
    AND FORMAT='PNG'
    AND COLORS=newcolor
    AND SHOWGRID=1
    AND SHOWVALUES=1
    AND MAPNAME='templine_map'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'

Temporal Domain/Range Linegraph See the SQL

select * from templine2
    returning linegraph(*), imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND TITLE='Temporal Range Linegraph Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND X_AXIS='Some Domain'
    AND Y_AXIS='Some Range'
    AND X_ORIENT='VERTICAL'
    AND LOGO='t/gowilogo.png'
    AND FORMAT='PNG'
    AND COLORS=newcolor
    AND SHOWGRID=1
    AND SHOWVALUES=1
    AND SHAPE=fillcircle
    AND MAPNAME='templine2_map'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'

Logarithmic Temporal Linegraph See the SQL

select * from logtempline
    returning linegraph(*), imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND TITLE='Logarithmic Temporal Range Linegraph Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND X_AXIS='Some Domain'
    AND Y_AXIS='Some Range'
    AND X_ORIENT='VERTICAL'
    AND Y-LOG=1
    AND FORMAT='PNG'
    AND COLORS=newcolor
    AND SHOWGRID=1
    AND SHOWVALUES=1
    AND SHAPE=fillcircle
    AND MAPNAME='logtempline_map'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'

Temporal Barchart See the SQL

select * from templine
    returning barchart(*), imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND TITLE='Temporal Barchart Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND X_AXIS='Some Domain'
    AND Y_AXIS='Some Range'
    AND FORMAT='PNG'
    AND COLORS=red
    AND SHOWVALUES=1
    AND MAPNAME='tempbar_map'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'

Temporal Histogram See the SQL

select * from templine2
    returning histogram(*), imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND TITLE='Temporal Histogram Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND X_AXIS='Some Domain'
    AND Y_AXIS='Some Range'
    AND FORMAT='PNG'
    AND COLORS=blue
    AND SHOWVALUES=1
    AND MAPNAME='temphisto_map'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'

Composite Line/Pointgraph See the SQL

select * from
    (select * from simpline
    returning linegraph(*)
        where color=newcolor
        AND shape='fillcircle') simpline,
    (select  * from simppie
    returning pointgraph(*)
        where color=blue
        AND shape='opensquare') simppt
    returning image, imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND TITLE='Composite Line/Pointgraph Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND X_AXIS='Some Domain'
    AND Y_AXIS='Some Range'
    AND FORMAT='PNG'
    AND MAPNAME='complinept_map'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'

Composite Line/Point/Areagraph See the SQL

select * from
    (select * from simpline
    returning linegraph(*)
        where color=newcolor
        AND shape=fillcircle) simpline,
    (select * from simppie
    returning pointgraph(*)
        where color=blue
        AND shape=opensquare) simppt,
    (select * from complpa
    returning areagraph(*)
        where color=red) simparea
    returning image, imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND TITLE='Composite Line/Point/Areagraph Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND X_AXIS='Some Domain'
    AND Y_AXIS='Some Range'
    AND FORMAT='PNG'
    AND MAPNAME='complpa_map'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'

Composite Line/Point/Areagraph/Barchart See the SQL

select * from
    (select * from simpline
    returning linegraph(*)
        where color=newcolor
        AND shape=fillcircle) simpline,
    (select * from simppie
    returning pointgraph(*)
        where color=blue
        AND shape=opensquare) simppt,
    (select * from complpa
    returning areagraph(*)
        where color=green) simparea,
    (select * from complpa
    returning barchart(*)
        where color=red) simpbar
    returning image, imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND TITLE='Composite Bar/Line/Point/Areagraph Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND X_AXIS='Some Domain'
    AND Y_AXIS='Some Range'
    AND FORMAT='PNG'
    AND MAPNAME='compblpa_map'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'

Composite Linegraph/Boxchart See the SQL

select * from
    (select * from complnbox
    returning linegraph(*)
    where color=red
    AND shape=fillcircle) simpline,
    (select * from simpbox
    returning boxchart(*)
        where color=newcolor) simpbox
    returning image, imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND TITLE='Composite Box
    AND Line Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND X_AXIS='Some Domain'
    AND Y_AXIS='Some Range'
    AND FORMAT='PNG'
    AND MAPNAME='complnbox_map'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'

Composite Line/Line/Box/Boxchart See the SQL

select * from
    (select * from complnbox
    returning linegraph(*)
    where color=newcolor
    AND shape=fillcircle
        and showvalues=1) simpline,
    (select * from simpbox
    returning boxchart(*)
    where color=newcolor) simpbox,
    (select * from compllbb
    returning linegraph(*)
    where color=red
    AND shape=fillcircle
        and showvalues=0) simpline2,
    (select * from simpbox2
    returning boxchart(*)
    where color=red) simpbox2
    returning image, imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND TITLE='Composite Multiple Box
    AND Line Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND X_AXIS='Some Domain'
    AND Y_AXIS='Some Range'
    AND FORMAT='PNG'
    AND MAPNAME='compllbb_map'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'

Composite Histograms See the SQL

select * from
    (select * from simppie returning histogram(*)
        where color=red) histo1,
    (select * from complpa returning histogram(*)
        where color=blue) histo2
    returning image, imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND TITLE='Composite Histogram Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND X_AXIS='Some Domain'
    AND Y_AXIS='Some Range'
    AND FORMAT='PNG'
    AND THREE_D=1
    AND SHOWVALUES = 1
    AND MAPNAME='comphisto_map'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'

Composite Barcharts See the SQL

select * from
    (select * from simppie
    returning barchart(*)
        where color=red) bars1,
    (select * from complpa
    returning barchart(*)
        where color=blue) bars2
    returning image, imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND TITLE='Composite Barchart Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND X_AXIS='Some Domain'
    AND Y_AXIS='Some Range'
    AND FORMAT='PNG'
    AND SHOWVALUES = 1
    AND SHOWGRID=1
    AND MAPNAME='compbars_map'
    AND ICONS=('t/pumpkin.png', 't/turkey.png' )
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'

Dense Linegraph See the SQL

select * from
    (select * from densesin
    returning linegraph(*)
        where color=red) densesin,
    (select * from densecos
    returning linegraph(*)
        where color=blue) densecos
    returning image
    where WIDTH=500
    AND HEIGHT=500
    AND TITLE='Composite Dense Linegraph Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND X_AXIS='Angle (Radians)'
    AND Y_AXIS='Sin/Cos'
    AND FORMAT='PNG'

Dense Areagraph See the SQL

select * from
    (select * from densesin
    returning areagraph(*)
        where color=red) densesin,
    (select * from densecos
    returning areagraph(*)
        where color=blue) densecos
    returning image
    where WIDTH=500
    AND HEIGHT=500
    AND TITLE='Composite Dense Areagraph Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND X_AXIS='Angle (Radians)'
    AND Y_AXIS='Sin/Cos'
    AND FORMAT='PNG'

Simple Gantt Chart See the SQL

select * from simpgantt
    returning gantt(*), imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND TITLE='Simple Gantt Chart Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND X_AXIS='Tasks'
    AND Y_AXIS='Schedule'
    AND COLOR=red
    AND LOGO='t/gowilogo.png'
    AND MAPNAME='simpgantt_map'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'
    AND X_ORIENT='VERTICAL'
    AND FORMAT='PNG'

Stacked Barchart See the SQL

select * from stackbar
    returning barchart(*), imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND X_AXIS='Some Domain'
    AND Y_AXIS='Some Range'
    AND TITLE='Stacked Barchart Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND FORMAT='PNG'
    AND SHOWVALUES=1
    AND STACK=1
    AND MAPNAME='stackbar_map'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'
    AND COLORS IN ('yellow', 'blue')

Stacked Iconic Barchart See the SQL

select * from stackbar
    returning barchart(*), imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND X_AXIS='Some Domain'
    and Y_AXIS='Some Range'
    AND TITLE='Stacked Iconic Barchart Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND FORMAT='PNG'
    AND SHOWVALUES=1
    AND STACK=1
    AND ICONS IN ('t/pumpkin.png', 't/turkey.png')
    AND MAPNAME='stackbar_map'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'

Stacked Areagraphs See the SQL

select * from stackbar
    returning areagraph(*), imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND X_AXIS='Some Domain'
    AND Y_AXIS='Some Range'
    AND TITLE='Stacked Areagraph Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND FORMAT='PNG'
    AND SHOWVALUES=1
    AND STACK=1
    AND MAPNAME='stackarea_map'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'
    AND COLORS IN ('red', 'green')

Stacked Histogram See the SQL

select * from stackbar
    returning histogram(*), imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND X_AXIS='Some Domain'
    AND Y_AXIS='Some Range'
    AND TITLE='Stacked Histogram Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND FORMAT='PNG'
    AND SHOWVALUES=1
    AND STACK=1
    AND MAPNAME='stackhisto_map'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'
    AND COLORS IN ('red', 'green')

Stacked Candlecharts See the SQL

select * from stackcandle
    returning candlestick(*), imagemap
    where WIDTH=300
    AND HEIGHT=500
    AND X_AXIS='Some Domain'
    AND Y_AXIS = 'Price'
    AND TITLE='Stacked Candlestick Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND FORMAT='PNG'
    AND COLORS IN ('newcolor', 'red')
    AND SHOWGRID=1
    AND STACK=1
    AND MAPNAME='stackcandle_map'
    AND LINEWIDTH=5
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'

Multiple Linegraph See the SQL

select * from stackbar
    returning linegraph(*), imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND X_AXIS='Some Domain'
    AND Y_AXIS='Some Range'
    AND TITLE='Multiline NULL Shape, Map Modifier Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND FORMAT='PNG'
    AND SHOWVALUES=1
    AND MAPNAME='multilinemm_map'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'
    AND COLORS IN ('red', 'green')
    AND SHAPES IN (NULL, 'filldiamond')

Quadtree aka Treemap See the SQL

SELECT * FROM myquad
returning QUADTREE(*), IMAGEMAP
WHERE COLORS IN ('red', 'black', 'green')
    AND WIDTH=500
    AND HEIGHT=500
    AND TITLE='My Quadtree'
    AND MAPTYPE='HTML'
    AND MAPNAME='quadtree_map'
    AND MAPURL=
'http://www.presicient.com/cgi-bin/quadtree.pl?group=:X&item=:Y&value=:Z&intensity=:PLOTNUM'

Stacked 3D Barchart See the SQL

select * from stackbar
    returning barchart(*), imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND X_AXIS='Some Domain'
    AND Y_AXIS='Some Range'
    AND TITLE='Stacked 3-D Barchart Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND FORMAT='PNG'
    AND SHOWVALUES=1
    AND STACK=1
    AND THREE_D=1
    AND MAPNAME='stack3Dbar_map'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'
    AND COLORS IN ('yellow', 'blue')

Stacked 3D Histogram See the SQL

select * from stackbar
    returning histogram(*), imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND X_AXIS='Some Domain'
    AND Y_AXIS='Some Range'
    AND TITLE='Stacked 3-D Histogram Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND FORMAT='PNG'
    AND SHOWVALUES=1
    AND STACK=1
    AND THREE_D=1
    AND MAPNAME='stack3Dhisto_map'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'
    AND COLORS IN ('red', 'green')

Timestamp Domain Linegraph See the SQL

select * from tmstamp
    returning linegraph(*), imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND X_AXIS='Some Domain'
    AND Y_AXIS='Some Range'
    AND TITLE='Timestamp Domain Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND FORMAT='PNG'
    AND SHOWVALUES=1
    AND MAPNAME='tmstamp_map'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'
    AND COLORS IN ('yellow', 'blue')

Floating Stacked Areagraphs See the SQL

select * from floatbar
    returning areagraph(*), imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND X_AXIS='Some Domain'
    AND Y_AXIS='Some Range'
    AND TITLE='Floating Stacked Areagraph Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND FORMAT='PNG'
    AND SHOWVALUES=1
    AND STACK=1
    AND ANCHORED=0
    AND MAPNAME='floatarea_map'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'
    AND COLORS IN ('green', 'yellow', 'red')

Floating Stacked Histogram See the SQL

select * from floatbar
    returning histogram(*), imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND X_AXIS='Some Domain'
    and Y_AXIS='Some Range'
    AND TITLE='Floating Stacked Histogram Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND FORMAT='PNG'
    AND SHOWVALUES=1
    AND STACK=1
    AND ANCHORED=0
    AND MAPNAME='floathisto_map'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'
    AND COLORS IN ('red', 'green', 'orange')

Floating Stacked Barcharts See the SQL

select * from floatbar
    returning barchart(*), imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND X_AXIS='Some Domain'
    AND Y_AXIS='Some Range'
    AND TITLE='Floating Stacked Barchart Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND FORMAT='PNG'
    AND SHOWVALUES=1
    AND STACK=1
    AND ANCHORED=0
    AND MAPNAME='floatbar_map'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'
    AND COLORS IN ('yellow', 'blue', 'red')

Multiwidth Linegraph See the SQL

select * from
    (select * from floatbar
    returning areagraph(*)
    where anchored=0
        and stack=1
    AND colors in ('blue', 'yellow', 'red')),
    (select * from regline
    returning linegraph(*)
        where color='newcolor'
    AND showvalues=1 ) regline,
    (select * from fatline
    returning linegraph(*)
        where color='lgray'
    AND linewidth=10) fatline,
    (select * from midline
    returning linegraph(*)
        where color='green'
    AND linewidth=4) midline
    returning image, imagemap
    where WIDTH=500
    AND HEIGHT=500
    AND TITLE='Variable Width Linegraph Test'
    AND SIGNATURE='(C)2002, GOWI Systems'
    AND X_AXIS='Some Domain'
    AND Y_AXIS='Some Range'
    AND FORMAT='PNG'
    AND MAPNAME='multwidth_map'
    AND MAPURL='http://www.gowi.com/cgi-bin/sample.pl?x=:X&y=:Y&z=:Z&plotno=:PLOTNUM'
    AND MAPTYPE='HTML'

Driver Specific Behavior

Data Source Name

The dsn string passed to DBIx::Chart->connect() is identical to the equivalent DBI connect string.

Data Types

Refer to DBD::Chart's user guide for details regarding

how column data is applied to the various types of charts
how the data types of the columns are treated when charted
what chart properties can be defined and how they behave
how imagemaps are generated

SQL Dialect

Charts are rendered using the following SELECT statement syntax:

Simple form:

<select-stmt> RETURNING <chart-function> [, IMAGEMAP ] WHERE <chart-prop-expr> [AND ...]

Composite form:

SELECT * FROM (<chart-subquery>)[ qry-name] [, (<chart-subquery>)[ qry-name]...] RETURNING IMAGE [, IMAGEMAP] WHERE <chart-prop-expr> [AND ...]

chart-subquery := <select-stmt> RETURNING <chart-function> WHERE <chart-prop-expr> [AND ...]

chart-function := any of

LINEGRAPH( * | column-list )
AREAGRAPH( * | column-list )
POINTGRAPH( * | column-list )
BARCHART( * | column-list )
HISTOGRAM( * | column-list )
BOXCHART( * | column-list )
PIECHART( * | column-list )
GANTTCHART( * | column-list )
QUADTREE( * | column-list )
IMAGE

<select-stmt> := any valid SELECT statement, including any grouping, ordering, or other qualifiers

chart-prop-expr := <property> < = | IN > <literal-list>

literal-list := <? | literal [, literal-list ]>

(Note: support for using returned columns of a charted query to specify chart property values is planned for a future release.)

When IMAGEMAP is included in the column list, text is generated for either

an HTML client-side imagemap
a Perl compatible array of hashrefs of value and coordinate attributes

Various <properties>s from the WHERE clause provide base URL or scripting tags to be associated with the imagemap. See IMAGEMAPS below for details.

When any of the various $sth->fetch() operations is applied to the SELECT statement:

For PNG, JPEG, or GIF formats, images are returned as VARBINARY objects representing the binary image data.
Imagemaps are returned as VARCHAR objects containing the HTML imagemap text.

General Restrictions:

ordering of wedges and bars in pie and barcharts is determined by the order the rows are returned by the associated SELECT statement.
Ordering of plot points in line, area, and point graphs with numeric and temporal domains is ascending left to right on the X-axis, ascending bottom to top on Y-axis. Graphs with symbolic domains are plotted in the order they are INSERT'd.
When generating area graphs with multiple range valuesets (i.e., more the 1 set of Y values), the order in which the range valuesets are plotted is column 2, column 3,...column N. This order may cause some ranges to be completely obscured by the areagraph of a succeding range. You'll need to experiment with the order of your range valuesets to get the best view.
Support for JPEG images on Windows platforms with ActiveState Perl requires installing the Tk::JPEG PPM from ActiveState.
Support for GIF image formats requires a compatible version of the GD modules (pre 1.20).
Composite images can only be constructed from compatible chart types:
- piecharts, Gantt charts, quadtrees, and 3-axis barcharts/histograms must be used alone (i.e., a single graph composite).
- histograms are only compatible with other histograms
- 3-d barcharts are only compatible with other 3-d barcharts
- all other types are compatible with one another
The domain types for the individual graphs in a composite image must be compatible, i.e., numeric with numeric, temporal with temporal, and symbolic with symbolic.
Named derived table queries cause the specified name to be used to identify the associated plot in any legend generated for the composite image. If no name is given for a derived table query, the default name "PLOTn", where n is the sequence number of the query in the image, will be used.

Colormaps

The colors used to render the various image elments can be adjusted by the application by modifying the predefined COLORMAP table. The COLORMAP table is predefined as follows:

CREATE TABLE CHART.COLORMAP (
	Name VARCHAR(30),	-- name of color
	RedValue INTEGER,	-- value of red component
	GreenValue INTEGER,	-- value of green component
	BlueValue INTEGER,	-- value of blue component
);

The color component values must be specified between 0 and 255.

Refer to DBD::Chart for the predefined values in the COLORMAP table.

For example, to define a new color:

$dbh->do('INSERT INTO COLORMAP VALUES('chartreuse', 230, 179, 180);

To modify an existing color:

$dbh->do("UPDATE COLORMAP SET REDVALUE=100 WHERE NAME='marine'");

To fetch the current values of an existing color:

$sth = $dbh->prepare("SELECT * FROM COLORMAP NAME='marine'");
$sth->execute;
$row = $sth->fetchrow_arrayref;

Finally, a generic parameterized statement can be used to define colors at runtime:

$sth = $dbh->prepare("INSERT INTO COLORMAP VALUES(?, ?, ?, ?)");
$sth->execute('marine', 127, 127, 255);

Note that using non-alphanumeric characters in color names may cause parsing problems, so stick to alphanumerics, e.g., 'red3'.

Error Handling

Any errors generated from improper SQL usage are flagged with an error value of -1, and appropriate text in the errstr. Errors emanating from DBI, or any underlying DBI driver, will be flagged in err and errstr with whatever info is returned.

Diagnostics

DBI provides the trace() function to enable various levels of trace information. DBIx::Chart currently doesn't add any traces to this.

Driver Specific Attributes

While the application does not have direct access to the DBD::Chart handles used by DBIx::Chart, the following attributes will be passed to DBD::Chart when provided:

chart_noverify
chart_map_modifier

Any other attributes will be passed to the driver defined by the connect() DSN string.

Restrictions and Limitations

Quoted identifiers are not supported in the chart column list
Expressions, literals, and placeholders are not supported in the chart column list
DBIx::Chart relies on the DBI statement handle NAME and TYPE attributes to locate the specified column positions and datatypes in the returned result set. If the returned NAME strings are not fully qualified (i.e., of the form alias.columnname), DBIx::Chart currently cannot disambiguate columns with the same names, and will always use the first matching column in the NAME list. Furthermore, if the datasource does not provide NAME or TYPE attributes, the application must use the chart_type_map driver-specific attribute for DBD::Chart to supply that information. Refer to both the DBD::Chart homepage and the DBIx::Chart t/plottest.t test script for example usage of chart_type_map.

Change History

Release 0.04:
- Fixed placeholder mapping for source statements
- Fixed bad sth subclassing
Release 0.03:
- Several bug fixes for DBIx::Threaded testing
Release 0.02:
- Several bug fixes for SQL::Preproc testing
Release 0.01:
- Coded; first beta release

TO DO List

some limited meta-data capability
additional chart support

Acknowledgements

Many thanks to all the authors of the various GD, PNG, JPEG, and zlib modules. Special thanks to Tim Bunce for providing sublassing, and suggesting SQL syntax.

References

Official DBI Site
DBI Docs with navigation (hosted by Presicient)
CPAN
Perl.org
Perl.com
DBD::Chart
ActiveState (for Perl software for Windows)

Author

Dean Arnold, Presicient Corp.

Copyright

Permission is granted to use this software according to the terms of the Perl Artistic License.

Perl DBI Extension For Rendering Charts and Graphs