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:

• Perl Primer by Pat Gunn
• The Perl Tutorial by tiztag.com
•  The getopts howto by Alex Batko
• http://www.perldoc.com is a great documentation source
• O'Reilly's Programming Perl is a good book on the subject

PERL Script with command line options

It's been a long time since I have done a tutorial about PERL scripting, so I cracked my fingers and got down to hacking some code to create a basic command line script with options.

To give you an idea, I want to be able to run a command and pass some optional flags to it. For instance:

perl-script -h -username nassar

It turns out that this is a very simple thing to do because PERL has an inbuilt getopts function.

#!/usr/bin/perl -w
#
# This is a perl script that uses warnings (hence the -w flag)
#
# This perl scripts is a test of the getopts function

# 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';

# Output the display menu if asked for
if (defined $options{h}) {
    print "The '-h' flag was invoked\n\n";
}

# Output the display menu if asked for
if (defined $options{d}) {
    print "The '-d' flag was invoked\n\n";
}

print "USERNAME: " .$username . "\nPASSWORD: " . $password . "\nHOSTNAME: " . $hostname . "\nDATABASE: " . $database . "\n\n";

exit 0;

 Make sure the script is set to be executable and you will get output similar to the following:

nassar@computer:~$ ./test.pl -u test -d -h
The '-h' flag was invoked

The '-d' flag was invoked

USERNAME: test
PASSWORD: testslair
HOSTNAME: localhost
DATABASE: testslairdb

nassar@computer:~$

References:

• Perl Primer by Pat Gunn
• The Perl Tutorial by tiztag.com
•  The getopts howto by Alex Batko