Oct 19, 2012

PERL script to connect and test PostgreSQL database

I decided to follow up my last getopts script about PERL scripting (which greatly improved upon my original PERL efforts) with a script that will attempt to connect to a PostgreSQL database and test if some tables exist or not.

I won't go into too much details and just paste the code here...

#!/usr/bin/perl -w
#
# This is a perl script that uses warnings (hence the -w flag)
#
# This perl scripts aims to test the database connections
# and ensure the schema conforms to the standard.

# Use strict perl
use strict;

# Use the Getopt library
use Getopt::Std;

# Creates a hash variable where we can store our command line options
my %options=();

# Grab the list of command line options (some with optional arguments,
#   denoted by a ':')
#   -u {username}   : Sets the username
#   -p {password}   : Sets the password
#   -H {hostname}   : Sets the hostname
#   -D {database}   : Sets the database
#   -h              : Displays the help
#   -d              : Debugging
getopts("hdu:p:H:D:", \%options);

# Sets some variables based on command line options, otherwise use defaults
my $username = (defined $options{u}) ? $options{u} : 'test';
my $password = (defined $options{p}) ? $options{p} : 'test';
my $hostname = (defined $options{H}) ? $options{H} : 'localhost';
my $database = (defined $options{D}) ? $options{D} : 'testdb';

# Set some environment variables
$ENV{PGPASSWORD}=$password;

# Output the display menu if asked for
if (defined $options{h}) {
    print "Test the database connections and ensure the schema conforms to the standard\n\n";
    print "usage: test.pl [-u <username] [-p <password>] [-H <hostname>]\n\n";
}

# Display the passed variables for debugging purposes
print "USERNAME: " .$username . "\nPASSWORD: " . $password . "\nHOSTNAME: " . $hostname . "\nDATABASE: " . $database . "\n\n" if defined $options{d};

# The auth tables
my @tables = qw(
system.session system.logs system.users);

# Loop through all our declared tables
foreach (@tables)
{
  psql_command($_);
}

# Clear some environmental variables
delete $ENV{PGPASSWORD};

# Exit from the system
exit 0;

# This subroutine runs a psql command
sub psql_command
{
  # Grab some parameters
  my ($table) = @_;
  if(!( defined($table)))
  {
    die "psql_command() was passed some bad arguments!\n";
  }

  my $output = `/usr/bin/psql -U $username -h $hostname -w -d $database -c 'SELECT * FROM $table' 2>&1`;
  if ( $? == -1 )
  {
    print "!!ERROR!! : $!\n";
  } else {
    if (($? >> 8) > 0)
    {
      print "!!ERROR!! : $table does not exist\n";
    } else {
      print "$table exists\n";
    }
  }

  return 0;
}

References:

No comments:

Post a Comment

Thanks for contributing!! Try to keep on topic and please avoid flame wars!!