Making SVG graphs with MySQL Perl Stored Procedures

It is possible to generate SVG graphs directly without requiring access to the Google Chart Servers. Perl provides a wealth of libraries which can create SVG graphs (but most not quite as pretty as the charts which the Google service creates).

This could be invaluable for people who want to serve all the information from their own website - for example, SSL encrypted websites - where some 'secured' web browsers may not display embedded content which is not sourced from the same domain.

Here is an example of something which would provide much the same data as the example posted by Ruturaj Vartakand Walter Heck.

For my demonstration, I have used the SVG::TT::Graph modules, which the Pie chart is currently limited to a maximum of 12 segments but there are other Perl modules available for generating SVG graphs.

package DemoGraphs;

use 5.008008;
use strict;
use warnings;
use Symbol qw(delete_package);
use DBI;
use SVG::TT::Graph::Pie
require Exporter;
our @ISA = qw(Exporter);
our @EXPORT_OK = qw(  );
our @EXPORT = qw( benchmark );
our $VERSION = '0.01';

# Preloaded methods go here.


# -- returns a single row resultset
# CREATE PROCEDURE test.db_size_graph(width int, height int)
#        READS SQL DATA
#        DYNAMIC RESULT SETS 1
#        LANGUAGE Perl
#        EXTERNAL NAME "DemoGraphs::db_size_graph";
#

sub db_size_graph($$)
{
  my ($width, $height) = @_;
  my $dbh = DBI->connect('DBI:mysql:test', undef, undef);

  my $data = $dbh->selectall_arrayref(q(
 SELECT
  t.table_schema,
  ROUND(SUM(t.data_length + t.index_length) / 1024)
   AS data_length_schema
 FROM
  information_schema.tables t
 GROUP BY
  t.table_schema
 ORDER BY
  t.table_schema
  )) or die $dbh->errstr;

  my @fields = ();
  my @data = (); 
 
  my $row;

  foreach $row (@$data) {
    push @fields, $row->[0];
    push @data, $row->[1];
  }

  my $graph = SVG::TT::Graph::Pie->new({
    # Required
    'fields'  => \@fields,
    # Optional
    'height'  => $height,
    'width'  => $width,
    'show_graph_title' => 1,
    'graph_title' => 'Database Sizes (KB)',
    'key'  => 1,
   });

  $graph->add_data({
    'data'  => \@data,
    'title'  => 'Database Sizes (KB)',
  });

  return {
    'svg' => $graph->burn(),
  };
}

1;
__END__