oracletool.pl

用perl写的Oracle管理工具

#!/usr/bin/perl

#   Copyright (c) 1998,1999,2000 Adam vonNieda
#
#   You may distribute under the terms of either the GNU General Public
#   License or the Artistic License, as specified in the Perl README file,
#   with the exception that it cannot be placed on a CD-ROM or similar media
#   for commercial distribution without the prior approval of the author.

#   This software is provided without warranty of any kind. If your server
#   melts as a result of using this script, that's a bummer. But it won't.

require 5.003;

use strict;
use CGI qw(:standard);
use File::Basename;
use FileHandle;

if (! eval "require DBI") {
   ErrorPage("It appears that the DBI module is not installed!");
}

my ($VERSION,$scriptname,$query,$database,$namesdatabase,$schema,$textarea_w);
my ($debug,$object_type,$object_name,$statement_id,$user,$whereclause,$textarea_h);
my ($expire,$username,$password,$dbh,$sql,$majversion,$minversion,$rowdisplay);
my ($oracle7,$oracle8,$oracle8i,$db_block_size,$title,$heading,$cursor,$banner);
my ($logging,$explainschema,$bgcolor,$headingcolor,$fontcolor,$infocolor,$font,$fontsize);
my ($linkcolor,$cellcolor,$bordercolor,$description,%themes,$schema_cols,$menufontsize);
my ($expiration,$oraclenames,$theme,$repository,$logfile,%plugins,$config_file);
my ($encryption_string,$bgimage,$menuimage,$encryption_enabled,$copyright,$headingfont);
my ($headingfontcolor);


$VERSION = "1.2.0";

# Edit the following if you want to use a config file not named "oracletool.ini".

$config_file = "oracletool.ini";

Main();

#=============================================================
# Nothing but subroutines from here on.
#=============================================================

sub Main {

   my ($dbstatus);

# Unbuffer STDOUT
   $|++;

# Find out the name this script was invoked as.
   $scriptname = $ENV{'SCRIPT_NAME'};

# Get the data from the elements passed in the URL.
   $query		= new CGI;
   $database		= $query->param('database');
   $namesdatabase	= $query->param('namesdatabase');
   $schema		= $query->param('schema');
   $explainschema	= $query->param('explainschema');
   $object_type		= $query->param('object_type');
   $object_name         = $query->param('arg');
   $statement_id	= $query->param('statement_id');
   $user		= $query->param('user');
   $whereclause		= $query->param('whereclause');
   $expire		= $query->param('expire');
   $password		= $query->param('password');

# Set the page colors / font etc.
# Attempt to get a cookie containing the users theme.
# Set to a default theme if none is found.

   $theme = cookie("OracletoolTheme");
   $theme = "Default1" unless ($theme);

# Get the settings from the config file.
   parseConfig();

# Decide whether to display copyright in all SQL statements.
   if ($ENV{'DISPLAY_COPYRIGHT'}) {
      $copyright = "/* Oracletool v$VERSION is copyright 1998,1999,2000 Adam vonNieda, Kansas USA */ ";
   } else {
      $copyright = "";
   }

   logit("Enter subroutine Main");

# Check for cookie encryption functionality.

   encryptionEnabled();

# Set the properties that will override the default theme.
   doProperties();

# If $namesdatabase is not null, then they have entered
# a names-resolved database. Change the $database value
# to the $namesdatabase value.
   $database = $namesdatabase if $namesdatabase;

# If $database is "About_oracletool" then
# show the "About" page.
   if ( $database && $database  eq "About_oracletool" ) {
      about();
   }

# The $user variable will get passed to get session info
# for an individual user. If no individual user is passed
# then it defaults to % (All users)
   $user 		= "%" unless $user;

# Get rid of the +'s on multi-word object types.
   $object_type =~ s/\+/ / if $object_type;

# If invoked standalone, show main page with database list.
   if ( ! defined $database ) {
      createMainPage();
      exit;
   }

# Skip the password verification for setting theme. Theme
# will be sent to browser as cookie.
   if ($object_type eq "SETTHEME") {
      setTheme();
   }

# Skip the password verification for setting Properties. Properties
# will be sent to browser as cookie.
   if ($object_type eq "SETPROPS") {
      setProperties();
   }

# Skip the password verification for explain plan. Password
# will be entered on the explain plan screen.
   if ($object_type eq "EXPLAIN") {
      enterExplainPlan();
   }

# Skip the password verification for running explain plan. 
# Password will be passed (hidden, and no cookie).
   if ($object_type eq "RUNEXPLAINPLAN") {
      runExplainPlan();
   }

# Add a password if no cookie is found, or if incorrect. 
   if ($object_type eq "ADDPASSWORD")     {
      $username = $query->param('username');
      $password = $query->param('password');
      addPasswd($database,$username,$password);
   }

# Attempt to get username and password cookies for connecting to the specified database. 
   ($username,$password) = split / /, GetPasswd($database);

# If no cookie is found, do not try to connect to the database,
# just go directly to the password screen.
   unless ($username && $password) {
      EnterPasswd($database);
   }

# Make connection to the database
   $dbh = dbConnect($database,$username,$password);

# If invoked the first time after selecting the database,
# start creating the frames.

   if ( $object_type eq "FRAMEPAGE" ) {
      framePage();
   }

# Find out if we are dealing with Oracle7 or Oracle8
   logit("   Getting oracle version");
   $sql = "$copyright
SELECT MAX(SUBSTR(RELEASE,1,1)),
       MAX(SUBSTR(RELEASE,3,1))
   FROM SYS.V_\$COMPATIBILITY
";

   $cursor = $dbh->prepare($sql);
   $cursor->execute;
   (($majversion,$minversion) = $cursor->fetchrow_array);
   $cursor->finish;
   if ( $majversion eq "7" ) {
      logit("   This is an Oracle7 database.");
      $oracle7 = "Yep";
    } else {
      $oracle8 = "Yep";
      logit("   This is an Oracle8 database.");
      if ($minversion eq "1") {
         logit("   This is an Oracle8i database.");
         $oracle8i = "Yep";
      }
   }

# See what status the database is in (OPEN,MOUNTED etc...). 

   $dbstatus = dbStatus();

# Display the menu on the left side of the screen.
# This connects to the database as well, hence the
# $username variable. Connection is for determining 
# version, OPS etc. Certain buttons will or will not
# be display based on some queries.

   if ( $object_type eq "MENU" ) {
      showMenu($username);
   }

# Find out the database block size
   $db_block_size = getDBblocksize();

# Get the Server banner to display the version info.
   $banner = getBanner();

# Create the header for the HTML page.
 
   $title      = "$database: Oracletool v$VERSION connected as $username";
   $heading    = "";

   Header($title,$heading,$font,$fontsize,$fontcolor,$bgcolor);

# The Director subroutine will direct the script to the appropriate
# subroutines based on the parameters passed, namely $object_type

   Director();

# Disconnect from the database

   $dbh->disconnect;

# Finish the HTML page.

   Footer();

   logit("Exit subroutine Main");
}

sub dbClosed {

   logit("Enter subroutine dbClosed");
   
   Header($title,$heading,$font,$fontsize,$fontcolor,$bgcolor);
   if ($object_name) {
      logit("   SQL passed to dbClosed: \n$object_name");
      runSQL($object_name);
   } else {
      logit("   No SQL passed, displaying worksheet.");
      enterWorksheet();
   }

   logit("Exit subroutine dbClosed");

   exit;
}

sub dbStatus {

# See what status the database is in.

   my ($cursor,$sql,$dbstatus);

   logit("Enter subroutine dbStatus");

   if ($oracle8) {
      logit("   We are Oracle8, checking database status.");
      $sql = "$copyright
SELECT
   STATUS
FROM V\$INSTANCE
";
      $cursor = $dbh->prepare($sql) or ErrorPage("Error: $DBI::errstr");
      $cursor->execute;
      $dbstatus = $cursor->fetchrow_array;
      $cursor->finish;
   } else {
      logit("   We are Oracle7, assuming database is open.");
      $dbstatus = "OPEN";
   }

   logit("   Database was found to be $dbstatus.");

   if ($dbstatus ne "OPEN") {
      dbClosed();
   }

   logit("Exit subroutine dbStatus");

   return($dbstatus);
}

sub statsPackInstalled {

   logit("Enter subroutine statsPackInstalled");

   my ($sql,$count);

   $sql = "
SELECT
   COUNT(*) 
FROM DBA_OBJECTS
   WHERE OBJECT_NAME = 'STATSPACK'
AND OBJECT_TYPE = 'PACKAGE'
";

   $count = recordCount($sql);

   logit("Exit subroutine statsPackInstalled");

   return($count);

}

sub loginfo {

   my $text = shift;

   if ($logging) {
      open (LOG,">>$logfile") or ErrorPage("Oracletool error! Cannot open log file \"$logfile\"! You need to disable logging or choose a filename that you have permission to write to.");
      print LOG "$text\n";
      close (LOG);
   }
}

sub logit {

   my $text = shift;

   if ($debug) {
      open (LOG,">>$logfile") or ErrorPage("Oracletool error! Cannot open log file \"$logfile\"! You need to disable logging or choose a filename that you have permission to write to.");
      print LOG "$text\n";
      close (LOG);
   }
}

sub parseConfig {

   my ($parameter,$eq,$val,$plugin);
   my $mytheme = $theme;
   my ($description,@themevars,$key,$themevarcount);

   open(CONFIG,"$config_file")
      or ErrorPage("Can't open config file $config_file. Reason: $!.");

   while (<CONFIG>) {

      next if ((/^$/) || (/^\s+$/) || (/^\s+#/) || (/^#/));

      chop;

      ($parameter,$val) = split(/=/);

      $parameter =~ s/^\s+//;
      $parameter =~ s/\s+$//;
      $val =~ s/^\s+//;
      $val =~ s/\s+$//;

      $parameter = uc($parameter);

      if ($parameter eq "EXPIRATION") {
         $expiration		= "$val";
         next;
      }
      if ($parameter eq "ORACLENAMES") {
         $oraclenames		= "Yep";	
         next;
      }
      if ($parameter eq "DEBUG") {
         $debug			= "Yep";
         next;
      }
      if ($parameter eq "LOGGING") {
         $logging		= "Yep";
         next;
      }
      if ($parameter eq "LOG") {
         $logfile		= "$val";
         next;
      }
      if ($parameter eq "ENCRYPTION_STRING") {
         $encryption_string	= "$val";
         next;
      }

   # Add plugins

      if ($parameter eq "PLUGIN") {
         $plugin = $val;
         next;
      }
      if ($parameter eq "PROGRAM") {
         $plugins{$plugin} = $val; 
         next;
      }

   # Add themes..

      if ($parameter eq "THEME") {
         $theme			= "$val";	
         $themevarcount++;
         next;
      }
      if ($parameter eq "DESCRIPTION") {
         $description		= "$val";
         $themevarcount++;
         next;
      }
      if ($parameter eq "BGCOLOR") {
         $bgcolor		= "$val";
         $themevarcount++;