Section 3.1. PerlTk | Perl for Oracle Dbas

3.1 Perl/Tk

The Perl/Tk module, developed Nick Ing-Simmons, is one of the most popular and useful of the Perl extension modules. Perl/Tk is a toolkit that gives Perl the ability to create interactive and full-fledged GUI-driven applications. Writing GUIs can be complex, but Perl/Tk makes it easy by making available standardized libraries of reusable GUI code (widgets and controls) that you can select as appropriate.

For those interested in writing Oracle DBA GUI applications of their own, we'll try to cover all the bases in this chapter, but we'll mostly focus on the tools currently available for those simply looking for ready-to-use database administration and tuning programs. For more information on generic Perl/Tk issues, these are the online and book resources we consider to be best:

http://www.lehigh.edu/~sol0/ptk/ptk.html: Stephen Lidie's central portal, for all things Perl/Tk.
http://www.perltk.org: Didier Ladner's central Perl/Tk resource.
http://www.oreilly.com/catalog/mastperltk: For those who prefer information in book form, we thoroughly recommend Mastering Perl/Tk , by Nancy Walsh and Stephen Lidie (O'Reilly & Associates, 2002). As one of us helped technically review this book, we must admit a bias, but it's the definitive text.

For a quick example of what Perl/Tk can do for you, take a look at the widgets demonstration program in Figure 3-1, which comes automatically with the Perl/Tk installation.

Figure 3-1. The Perl/Tk widget program's image interface

3.1.1 Installing Perl/Tk under Unix

As with virtually every other Perl module we'll discuss in this book, you can install Perl/Tk from the online CPAN system in the following way:

 $ perl -MCPAN -e "shell" cpan shell -- CPAN exploration and modules installation (v1.59_51) ReadLine support enabled    cpan> install Tk  <RETURN>

However, to gain far more control over the install and its tests, and to access all of the install information for the various Unix flavors, get hold of Perl/Tk's latest tarball from the following web site:

http://www.cpan.org/authors/id/NI-S

Once you've downloaded the CPAN source into the temporary directory of your choice, you can install Perl/Tk manually as follows (we've demonstrated this with the Tk800.023.tar.gz file):

 $ gzip -d  Tk800.023.tar.gz  $ tar xvf Tk800.023.tar $ cd Tk800.023

Always comb religiously through the README and INSTALL files to make sure nothing special is required for your machine's setup:

 $ vi README INSTALL

Once you're happy with your setup, you can proceed. Perl/Tk follows the usual pattern for installing most Perl modules. However, it is a large body of code, in comparison to most other Perl modules, so it often takes a few minutes to install, and you must ensure that the user running make test has access to the appropriate X Windows server, by running a command such as xhost + . Once the testing is complete, you'll also need to install Perl/Tk as root :

 $ perl Makefile.PL $ make $ make test $ make install

We're now all set! All that rigid command-line discipline is about to change. Assuming that the installation ran typically smoothly, Perl/Tk is now installed and ready to GUI.

3.1.2 Installing Perl/Tk on Win32

The corresponding ActiveState installation on Win32 is straightforward. Simply connect your PC to the Internet, as in Chapter 2, and then run through the following PPM commands, much as you did when installing Perl DBI and DBD-Oracle:

Fire up an MS-DOS command window and run PPM:
```
 C:\> ppm 
```
Now install ActivePerl's remotely accessed Perl/Tk package by typing:
```
 PPM> install Tk 
```
This may take slightly longer to load than your average ActivePerl package, as it's very large, but should still only take a few minutes.
When the command completes, enter:
```
 PPM> quit 
```
Perl/Tk should now be installed.

3.1.3 Combining Perl/Tk and Perl DBI

To try Perl/Tk on for size , let's try searching for the widgets program demonstrated in Figure 3-1 as follows:

Unix: From the Perl/Tk installation location, look for the ../demos directory, change directory into there, and then type perl widget .
Win32: Go to the C:\Perl\bin directory and type either perl widget or widget.bat .

Those of you who are rolling your own might like to run through Example 3-1. This follows the general Perl/Tk program algorithm shown in Figure 3-2.

Figure 3-2. The basic structure of Perl/Tk programs

Example 3-1. HelloPtk.pl ” trying Perl/Tk on for size

 #!/usr/bin/perl    use strict; use warnings;    # Step 1: Get hold of the main Perl/Tk package.    use  Tk  ;    # Step 2: Create the Main Window.  Use the name of the program,  # held in the special Perl variable   #!/usr/bin/perl use strict; use warnings; # Step 1: Get hold of the main Perl/Tk package. use  Tk  ; # Step 2: Create the Main Window. Use the name of the program, # held in the special Perl variable  $0  , to create the title.  my $mw  = MainWindow->new(-title=>  $0  ); # Step 3: Pack a label onto the screen to hold our initial message. $mw->Label(-text=> "  Hello Perl/Tk  ", -anchor=>'center' )->pack(-side=>'top'); # Step 4: Create a button to neatly exit the program. $mw->Button( -text=>'Exit', -command=>  \&doExit  )->pack(-side=>'bottom'); # Step 5: Launch the Perl/Tk looping process, to display  window.MainLoop( );  # Step 6: Create an exit subroutine. sub  doExit  { exit 0; } 
  , to create the title.  my $mw  = MainWindow->new(-title=>   #!/usr/bin/perl use strict; use warnings; # Step 1: Get hold of the main Perl/Tk package. use  Tk  ; # Step 2: Create the Main Window. Use the name of the program, # held in the special Perl variable  $0  , to create the title.  my $mw  = MainWindow->new(-title=>  $0  ); # Step 3: Pack a label onto the screen to hold our initial message. $mw->Label(-text=> "  Hello Perl/Tk  ", -anchor=>'center' )->pack(-side=>'top'); # Step 4: Create a button to neatly exit the program. $mw->Button( -text=>'Exit', -command=>  \&doExit  )->pack(-side=>'bottom'); # Step 5: Launch the Perl/Tk looping process, to display  window.MainLoop( );  # Step 6: Create an exit subroutine. sub  doExit  { exit 0; } 
  );    # Step 3: Pack a label onto the screen to hold our initial message.    $mw->Label(-text=> "  Hello Perl/Tk  ", -anchor=>'center'           )->pack(-side=>'top');    # Step 4: Create a button to neatly exit the program.    $mw->Button( -text=>'Exit',               -command=>  \&doExit  )->pack(-side=>'bottom');    # Step 5: Launch the Perl/Tk looping process, to display  window.MainLoop(  );  # Step 6: Create an exit subroutine. sub  doExit  { exit 0; }

We've run through the following program steps in a little more detail:

The first part of the program picks up the main Tk package.
We then construct the main window and store its handle within a traditionally named $mw variable. (We've also made use of the built-in $0 Perl variable, which automatically contains the name of a Perl script.)
We create our label, "Hello Perl/Tk", and place it at the top of the screen.
To close the program neatly, in accordance with Figure 3-2, we attach an exit button to the doExit( ) subroutine. If we decide to extend the program later on, you can also add any special cleanup code that is necessary.
Now we can enter Perl/Tk's main looping process. This takes care of all the hard work of tracking mouse movements, button commands, and so on. Once the program's widgets are laid out, the MainLoop traffic cop directs the Perl GUI script to wherever the user desires to go.
Finally, we code our special exit command. Although the exit subroutine is fairly basic in our prototype, you can place any necessary cleanup code here later on ” for example, a graceful database disconnection. We run the program via the following command:
```
 $ perl HelloPtk.pl 
```
You can see HelloPtk.pl in action in Figure 3-3 on both Win32 and Unix.

Figure 3-3. HelloPtk.pl under both Win32 and Linux

Exciting as HelloPtk.pl may be, both in looks and execution, the real fun begins when we start to combine Perl/Tk with Perl DBI. In Example 3-2 we've expanded HelloPtk.pl to work out the time according to SYSDATE. You can see this program at work in Figure 3-4.

Figure 3-4. WhatIsTheTime.pl in action, under Unix

Example 3-2. WhatIsTheTime.pl ” Combining Perl/Tk and Perl DBI

 #!/usr/bin/perl    # Step 1: Get hold of the main Perl/Tk package, DBI, and set the  # Oracle Environment, plus set the database connection and SQL.    use Tk; use  DBI  ; use strict; use warnings;  my $dbh = DBI->connect( 'dbi:Oracle:orcl', 'scott', 'tiger  ',  { RaiseError=>1, AutoCommit=>0 } );  my $sql = qq{  SELECT TO_CHAR(SYSDATE, 'HH:MI:SS') FROM DUAL  };    my $mw = MainWindow->new(-title=>  #!/usr/bin/perl # Step 1: Get hold of the main Perl/Tk package, DBI, and set the # Oracle Environment, plus set the database connection and SQL. use Tk; use  DBI  ; use strict; use warnings;  my $dbh = DBI->connect( 'dbi:Oracle:orcl', 'scott', 'tiger  ',  { RaiseError=>1, AutoCommit=>0 } );  my $sql = qq{  SELECT TO_CHAR(SYSDATE, 'HH:MI:SS') FROM DUAL  }; my $mw = MainWindow->new(-title=>$0); # Set the main window up # Step 2: Get the latest time from the Oracle database. my oracleTime;  getTheOracleTime( )  ; # Step 3: Pack a simple button onto the screen, to ask Oracle for the # current SYSDATE time. Assign the appropriate callback. $mw->Button(-text=>"What's the Time, according to Oracle?", -command=>  \&getTheOracleTime  )->pack(-side=>'top'); # Step 4: Pack a label onto the screen holding the SYSDATE time.  $mw->Label(-textvariable=> \$oracleTime, -anchor=>'center  '  )->pack(-side=>'bottom');  # Create another button to neatly exit the program. $mw->Button( -text=>'Exit', -command=>  \&doExit  )->pack(-side=>'bottom'); # Launch the Perl/Tk looping process, to display the window! :-) MainLoop( ); # Step 5: Create the two required subroutines. sub  getTheOracleTime  { my $sth = $dbh->prepare( $sql ); $sth->execute( ); ($oracleTime) = $sth->fetchrow_array( ); } sub  doExit  {  $dbh->disconnect( ); # A clean and graceful disconnection 8-)  exit; } 
 );            # Set the main window up    # Step 2: Get the latest time from the Oracle database.    my oracleTime;  getTheOracleTime(  )  ;    # Step 3: Pack a simple button onto the screen, to ask Oracle for the  # current SYSDATE time.  Assign the appropriate callback.    $mw->Button(-text=>"What's the Time, according to Oracle?",             -command=>  \&getTheOracleTime  )->pack(-side=>'top');    # Step 4: Pack a label onto the screen holding the SYSDATE time.  $mw->Label(-textvariable=> $oracleTime, -anchor=>'center  '  )->pack(-side=>'bottom');  # Create another button to neatly exit the program.    $mw->Button( -text=>'Exit',               -command=>  \&doExit  )->pack(-side=>'bottom');    # Launch the Perl/Tk looping process, to display the window! :-)    MainLoop(  );    # Step 5: Create the two required subroutines.    sub  getTheOracleTime  {    my $sth = $dbh->prepare( $sql );    $sth->execute(  );    ($oracleTime) = $sth->fetchrow_array(  ); } sub  doExit  {  $dbh->disconnect(  );  # A clean and graceful disconnection 8-)  exit; }

In the following list we've indicated the main differences between this program and the original HelloPtk.pl skeleton:

We acquire Perl DBI, connect to the database, and create our SQL.
We call getTheOracleTime( ) to initialize the $oracleTime variable.
We create a button to call getTheOracleTime( ) and change the display.
We need to create a display label, referencing the $oracleTime variable. The text changes, as appropriate, whenever the call button is pressed.
Finally, we add the new getTheOracleTime( ) function and remember to disconnect cleanly from the database when the doExit( ) function is called.
We run the program by typing the following at the command line:
```
 $ perl WhatIsTheOracleTime.pl 
```

Although this is a rather stripped-down example, it does show how easy it is to quickly combine Perl/Tk and Perl DBI in order to develop your own applications. Before you know it, you'll have built an entire collection of Perl/Tk widgets that do all sorts of wonderful things ” and you'll fail to understand how you ever lived without them.