Section 3.8. DBD::Chart | Perl for Oracle Dbas

3.8 DBD::Chart

If you're an Oracle DBA who needs to visualize and report upon lots of complex information, particularly performance statistics, in graphical form, you will benefit from the amazing DBD::Chart . Just two of its many possibilities are displayed in Figure 3-13.

Figure 3-13. Two examples of what DBD::Chart can do

DBD::Chart provides a mechanism within Perl for rendering pie charts, bar charts , line, point, area, and candlestick graphs, and HTML image maps via the use of SQL. The neat thing about DBD::Chart is that it uses Perl DBI methods to create charts directly, rather than requiring you to invoke yet another programming interface. For example, a SELECT statement is used to output a particular chart type, and the WHERE clause is used to determine its dimensions.

If you tried to produce a chart without DBD::Chart , you'd have to select database row information into Perl arrays and then process the arrays separately to create the charts via a special Perl charting API. With DBD::Chart , you can do all this in one operation that is very SQL-like. For example, when you create a new chart, you do it with a CREATE statement just as if you're creating a table. When you insert information into the chart, you do this with an INSERT statement, as if you're adding a row to a table. This is a very neat idea. We particularly like it because virtually all of the dynamic charts we ever create come directly from databases.

DBD::Chart is particularly useful with either Perl/Tk or Perl CGI, when run in conjunction with Perl DBI. (Image maps can also be linked to CGI programs, with HTML usage.) You can see from the following breakdown of the code used to generate the two images in Figure 3-13 just how close DBD::Chart is to ordinary DBI. (See Appendix B, for a summary of the DBI API.)

Obtain Perl DBI and connect with the DBD::Chart driver:

 use DBI; use strict; my $dbh = DBI->connect('  dbi:Chart:  ', undef, undef,                        { PrintError => 1, RaiseError => 1 });

We now can create a pie chart, with various rugby football information, in exactly the same way we might create an Oracle table with DBD::Oracle , and then select from it afterwards:

 my @game_plan = qw(points possesion penalties goals turnovers yardage); my @game_values = (70, 64, 18, 16, 19, 22); $dbh->do(  'CREATE   TABLE   gamepie (   Segment varchar(10),   First integer)');  my $sth = $dbh->prepare('  INSERT INTO gamepie VALUES(?, ?)  ');    for (my $i = 0; $i <= $#game_plan; $i++) {    $sth->execute($game_plan[$i], $game_values[$i]); } $sth = $dbh->prepare(    "  SELECT PIECHART, IMAGEMAP FROM gamepie   WHERE WIDTH=700 AND HEIGHT=600 AND   TITLE = 'Sample Pie Chart' AND   SIGNATURE = 'Copyright(C) 2002, Jared Still' AND   3-D=1 AND   COLORS=(red, white, blue, lyellow, lgray, pink)  " ); $sth->execute; my $row = $sth->fetchrow_arrayref;

Having created the chart in memory via the use of SQL, we now output it to a PNG file. We then drop the memory structure, as if dropping a table:
```
 open(PIE, '>gamepie.png'); binmode PIE; print PIE $$row[0]; close PIE;    $dbh->do('DROP table gamepie'); 
```

We now create a three-axis bar chart. In this particular example, we'll cover ancient English sites of special interest (at least to one of the authors):

 $dbh->do('CREATE TABLE spiritaxis ( Month char(3), Visitors integer, Monument varchar(11))');    my @months = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec); my @monuments =  qw(Stonehenge Avebury SilburyHill Glastonbury WhiteHorse); $sth = $dbh->prepare('INSERT INTO spiritaxis VALUES(?, ?, ?)'); foreach my $month (@months) { foreach my $visitors (@monuments) {       $sth->execute($month, 1 * int(rand(2000)), $visitors);    } } $sth = $dbh->prepare(    "SELECT BARCHART, IMAGEMAP        FROM spiritaxis      WHERE WIDTH=700 AND HEIGHT=600 AND            TITLE = 'Visitors Per Saturday' AND            SIGNATURE = 'Copyright(C) 2002, Andy Duncan' AND            X-AXIS = 'Month' AND            Y-AXIS = 'Visitors' AND            Z-AXIS = 'Monument' AND            COLORS=(white) AND            SHOWGRID = 1"                     ); $sth->execute; $row = $sth->fetchrow_arrayref;

As before, we output the chart's memory structure into a file and then reclaim the memory by dropping the table:

 open(BAR, '>spiritaxis.png'); binmode BAR; print BAR $$row[0]; close BAR;    $dbh->do('  DROP  table spiritaxis');

DBD::Chart is an amazing piece of work. May it live long and prosper . In the following sections we'll explain how to install this tool.

3.8.1 Preparing DBD::Chart

DBD::Chart possesses an almost infinite number of uses, limited only by the SQL you can choose to fill it. It's available here:

http://www.presicient.com/dbdchart

http://www.cpan.org/authors/id/D/DA/DARNOLD

Although DBD::Chart itself is 100% pure Perl, it does rely on one other Perl module, GD.pm , which itself requires several other non-Perl C libraries. Fortunately, these libraries are all open source and either available to compile from source on Unix, or built into the precompiled GD.pm ActivePerl package.

3.8.2 Installing DBD::Chart on Unix

GD.pm relies upon three separate C libraries. Fortunately, we've already installed the ones required for zlib and PNG (see the earlier discussion under StatsView) so we only need one more library (we also recommend a JPEG library for completeness). Just to give you a sense of the scope of DBD::Chart , we've included every download in Table 3-4 for use with either Perl CGI or Perl/Tk. Collect your unclaimed tarballs, and then we'll work through the entire shooting gallery.

Table 3-4. DBD::Chart's related Unix downloads

Download	Download addresses	Example tarball
zlib	http://www.gzip.org/zlib/, http://www.zlib.org	zlib.tar.gz
PNG	http://www.libpng.org/pub/png	libpng-1.0.12.tar.gz
jpeg-6b	ftp://ftp.uu.net/graphics/jpeg, http://www.ijg.org	jpegsrc.v6b.tar.gz
gd	http://www.boutell.com/gd	gd-1.8.4.tar.gz
GD.pm	http://www.cpan.org/authors/id/LDS	GD-1.33.tar.gz
Tk::PNG ^[7]	http://www.cpan.org/authors/id/NI-S	Tk-PNG-2.005.tar.gz
Tk::JPEG	http://www.cpan.org/authors/id/NI-S	Tk-JPEG-2.014.tar.gz

^[7] Although Tk::PNG and Tk::JPEG are only required for the use of DBD::Chart with Perl/Tk modules, we thought it would be useful to include them here as part of the full set.

3.8.2.1 JPEG

To complement our PNG library, we can load up JPEG support from the Independent JPEG Group (IJG). Although the lossless PNG graphics format is better for sharp letters and line drawings, JPEG's lossy nature allows you to create massively compressed files while still retaining a human perception of high quality (see the sidebar for a definitions of these terms).

Lossless versus Lossy

You will often hear the terms "lossless" or "lossy" used when referring to graphics images or compression algorithms. Lossless decompression preserves every part of an original file so it can be reproduced exactly as it was, no matter how small the compressed file gets. Think of lossless compression as being like a squashed-up handkerchief stuffed very small into a pocket; it can be uncompressed later as a complete, flat-ironed handkerchief. With lossy compression, on the other hand, the reduced storage technique throws away bits of the original file so when it is uncompressed it looks essentially the same, but lacks the completeness of the original.

The trick to saving room is to throw away only those bits that aren't essential later on. Think of someone making an annotated sketch of your monogrammed handkerchief. Embroiderers can take this sketch and stitch you another monogrammed hanky without needing to see the complete original. All they need are the cloth dimensions and the position, size , color , and shape of your initials . The stitch technique may be entirely different, but the difference may be visible only under a microscope. And the storage required for the embroidering instructions is massively reduced, or "lossy," leading to much cheaper information transmission for the price of an invisible reduction in similarity to the original.

This quality makes JPEG a very popular image content system for photographic usage on the Internet. Because the Tk::JPEG module is currently more widely available than Tk::PNG , particularly within ActivePerl, it's also a good format to use with Perl/Tk canvas applications. Let's take a look:

Read carefully through the install.doc document to ensure that the configuration provides all the options you require:
```
 $ gzip -d jpegsrc.v6b.tar.gz $ tar xvf jpegsrc.v6b.tar $ cd jpeg-6b $ vi README  install.doc  
```
You may be able to move straight into the following commands:
```
 $ ./configure $ make $ make test 
```
The test step compares several JPEG files, which come with the download, with program compilations. Several of these test images should resemble the rose seen earlier in Figure 3-7, which is also borrowed by Tk::JPEG for its own testing. Note, however, that the various .jpg files will be of different physical sizes because of lossy compression.
Once the testing is complete, we can move on to the installation:
```
 $ make install $ make install-lib 
```
Our default installation, on Linux, put the library and C header files into /usr/lib and /usr/include rather than /usr/local/ . You will want to be aware of this during installation, so we can pick up the library correctly later.

3.8.2.2 The gd library

Next we load up Thomas Boutell's gd library. The reason this library is required here is because it drives Lincoln Stein's GD.pm Perl package, which itself is relied upon by DBD::Chart . A splendid web of intrigue, indeed! Follow these steps:

Unpack the download:

 $ gzip -d gd-1.8.4.tar.gz $ tar xvf gd-1.8.4.tar $ cd gd-1.8.4

The best help is available by browsing the download's index.html file. In accordance with the instructions provided, we changed the Makefile in several ways. We added the JPEG library to the main required libraries:
```
 #LIBS=-lgd -lpng -lz -lm LIBS=-lgd -lpng -lz  -ljpeg  -lm 
```
Because the JPEG libraries and C header files had defaulted to be installed in /usr/lib and /usr/include , our Makefile had to be adjusted accordingly :
```
 INCLUDEDIRS=-I. -I/usr/include/freetype2 -I/usr/include/X11 \  -I/usr/X11R6/include/X11 -I/usr/local/include  -I/usr/include  LIBDIRS=-L. -L/usr/local/lib  -L/usr/lib  -L/usr/lib/X11 -L/usr/X11R6/lib 
```
(When you install JPEG on your own setup, your defaults may set to /usr/local/lib and /usr/local/include . If this happened , then the previous changes to INCLUDEDIRS and LIBDIRS will be unnecessary.)

We then tried:

 $ make $ make install

This installed the main gd library and header files in the following places:

 /usr/local/lib/  libgd.a  /usr/local/include/  gd.h

When you install gd , you also obtain the gddemo program. If you run this program, you'll find that it creates a new PNG file, demoout.png , directly from a supplied one, demoin.png , which is a snazzy picture of a space shuttle:
```
 $ ./gddemo 
```
If this fails to work the first time, you may have to play around with the LD_LIBRARY_PATH variable, for example:
```
 $ export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib 
```
The new file should be the original space shuttle, but overlaid with decorative imagery, as seen earlier on the right side of Figure 3-7.

3.8.2.3 GD.pm

Although he is better known for his work with CGI.pm and mod_perl (covered in the following chapters), Lincoln Stein has also created another great piece of work in GD.pm . This program provides a Perl front end to Thomas Boutell's gd C library. Follow these steps:

Because we're relying on the gd module's being available, this step differs from the standard vanilla Perl configuration:
```
 $ gzip -d GD-1.33.tar.gz $ tar xvf GD-1.33.tar $ cd GD-1.33 $ perl Makefile.PL 
```
The preceding step will ask various questions. Your mileage may vary. (Before completing the installation of GD.pm , you may wish to acquire FreeType and XPM (X PixMap) support. Information is available in the GD.pm download bundle. If you aren't interested, just say no):
```
  Build JPEG support? [y]  Build FreeType support? [y] Build XPM support? [y] 
```

Next, compile and test the module:

 $ make $ make test

During the test stage, we're looking for output similar to the following:

 ...  All tests successful  , 2 subtests skipped. Files=1, Tests=10,   0 wallclock secs ( 0.49 cusr +  0.06 csys =  0.55 CPU)

You may also want to complete an optional step to provide a helpful GD.html documentation file in the current directory:
```
 $ make html 
```
We're now ready to:
```
 $ make install 
```
The GD.pm Perl package should now be loaded and ready to fire.

3.8.2.4 Completing the DBD::Chart installation

We can finally nail the main event:

 $ gzip -d DBD-Chart-0.60.tar.gz $ tar xvf DBD-Chart-0.60.tar $ cd DBD-Chart-0.60 $ vi README $ perl Makefile.PL $ make $ make test         # There may not be too much here just yet! 8-) $ make install

DBD::Chart is now fully installed. If you want to test this program, the best way is to go to the /examples directory, move all the current *.png files to another directory, and then run all the example Perl programs to create new PNGs ” for example:

 $ cd examples $ mkdir tmp_safe $ mv *.png tmp_safe $ perl simpcandle.pl

You should now find a handful of PNGs in the current directory, similar to those we first showed you in Figure 3-13. For specific DBD::Chart use with Perl/Tk programs, such as Orac, we also need to install the Tk::JPEG module. While we're at it, we'll also compile the Tk::PNG module here for Unix ” it's bound to turn up as a package on ActivePerl sooner or later for Win32 users (it will make lossless PNG usage on Perl/Tk that much more attractive).

3.8.2.5 Tk::PNG

Follow these steps to install Tk::PNG :

Everything should run smoothly during this installation, because TK::PNG is expecting the zlib and PNG libraries to be just where we put them earlier. Well, that's the plan. For variations, scan the README file thoroughly for complete information:
```
 $ gzip -d Tk-PNG-2.005.tar.gz $ tar xvf Tk-PNG-2.005.tar $ cd Tk-PNG-2.005 $ vi  README  $ perl Makefile.PL $ make 
```
The make test step should pop up the PNG picture we showed back in Figure 3-7, as also used by the original libpng installation:
```
 $ make test ...  All tests successful  . Files=1, Tests=4,  2 wallclock secs  ( 0.33 cusr +  0.03 csys =  0.36 CPU) 
```
Now carry out the install:
```
 $ make install 
```

3.8.2.6 Tk::JPEG

Let's wrap up by installing Tk::JPEG :

 $ gzip -d Tk-JPEG-2.014.tar.gz $ tar xvf Tk-JPEG-2.014.tar $ cd Tk-JPEG-2.014

As with the PNG installation, the make test step should pop up the JPEG picture of the rose, shown in Figure 3-7, as seen with the earlier installation of the JPEG libraries. If this occurs, the launchpad is ready:
```
 $ perl Makefile.PL $ make $ make test $ make install 
```

3.8.3 Installing DBD::Chart on Win32

In absolute contrast with the Unix installation of DBD::Chart , the DBD::Chart installation on Win32 via ActiveState is a miniscule effort. The folks at ActiveState have locked up the hard work of installing the C libraries deep inside their GD.pm package.

To install DBD::Chart on Win32, follow these steps:

As earlier with the Perl/Tk download, connect your PC to the Internet and run the PPM program:
```
 C:\> ppm 
```
Now install the ActivePerl GD and Tk::JPEG packages by typing:
```
 PPM> install GD PPM> install Tk-JPEG PPM> exit 
```
For those who have waded through the Unix install, we're embarrassed to say that this is really all you have to do to get zlib , PNG, JPEG, gd, and GD.pm onto Win32. When the revolution comes, there shall be a reckoning!

3.8.3.1 Loading DBD::Chart for ActivePerl

Although ActiveState may lack a DBD::Chart package in its library, the package is relatively straightforward to add because it's 100% pure Perl:

Get hold of the latest download file, such as DBD-Chart-0.60.tar.gz , from:

http://www.cpan.org/authors/id/D/DA/DARNOLD
Unzip the tarball to the a temporary directory, such as:
```
 C:\DBD-Chart-0.60 
```
Go to the main ..\DBD directory where ActivePerl keeps its modules, and copy in DBD::Chart 's main Chart.pm module. For example:
```
 C:\DBD-Chart-0.60> cd C:\Perl\site\lib\DBD C:\Perl\site\lib\DBD> copy C:\DBD-Chart-0.60\  Chart.pm  . 
```
Now create a new subdirectory under ..\DBD , itself named Chart :
```
 C:\Perl\site\lib\DBD> mkdir Chart 
```
Enter this subdirectory, and copy Plot.pm to it from the ..\Chart directory within the download area:
```
 C:\Perl\site\lib\DBD> cd Chart C:\Perl\site\lib\DBD\Chart> copy C:\DBD-Chart-0.60\Chart\  Plot.pm  . 
```
This completes the Win32 installation of DBD::Chart . (We'll cover another way of installing larger pure-Perl modules, using NMAKE, in Chapter 6, where a slightly longer setup can save a lot of copying by hand.) To test our Tk::JPEG system, we fired up Orac to see if it could detect both DBD::Chart and Tk::JPEG as being successfully installed. The resulting, slightly lossy tablespace allocations chart is displayed in Figure 3-14.

Figure 3-14. DBD::Chart examines tablespace allocations