Subversion Repositories DevTools

########################################################################
# COPYRIGHT - VIX IP PTY LTD ("VIX"). ALL RIGHTS RESERVED.
#
# Module name   : jats_rm_query.pl
# Module type   : JATS Utility
# Compiler(s)   : Perl
# Environment(s): jats
#
# Description   : Execute a simple RM SQL query
#
# Usage         : See POD at the end of this file
#
#......................................................................#

require 5.008_002;
use strict;
use warnings;

use Pod::Usage;
use Getopt::Long;

use Pod::Usage;
use Getopt::Long;
use JatsError;
use JatsRmApi;
use JatsSystem;
use FileUtils;
use ConfigurationFile;
use JatsProperties;
use File::Copy;
use DBI;
my $RM_DB;

# ReadOnly credentails to known databases
my $newRMCred  = ['jdbc:oracle:thin:@auawsards002:1521:RELEASEM', 'RM_READONLY', 'Tp8WmmDKMq2Z'];
my $oldRMCred  = ['jdbc:oracle:thin:@auawsards001:1521:RELEASEM', 'RM_READONLY', 'RM_READONLY'];
my $testRMCred  = ['jdbc:oracle:thin:@relmanu1.coidtfba5ouc.ap-southeast-2.rds.amazonaws.com:1521:RELMANU1', 'RM_READONLY', 'RM_READONLY'];
my $rmCred;

my $VERSION = 1.0;
my $opt_help=0;
my $opt_verbose=0;
my $opt_debug=0;
my $opt_pulse;
my $opt_user;
my $opt_passwd;
my $opt_server;
my $opt_test;


#-------------------------------------------------------------------------------
# Function        : Mainline Entry Point
#
# Description     :
#
# Inputs          :
#
my $result = GetOptions (
                "help:+"        => \$opt_help,
                "manual:3"      => \$opt_help,
                "verbose:+"     => \$opt_verbose,
                "debug:+"       => \$opt_debug,
                "pulse!"        => \$opt_pulse,
                "test!"         => \$opt_test,
                "user:s"        => \$opt_user,
                "password:s"    => \$opt_passwd,
                "server:s"      => \$opt_server,
                );

                #
                #   UPDATE THE DOCUMENTATION AT THE END OF THIS FILE !!!
                #

#
#   Process help and manual options
#
pod2usage(-verbose => 0, -message => "Version: $VERSION") if ($opt_help == 1 || ! $result);
pod2usage(-verbose => 1) if ( $opt_help == 2 );
pod2usage(-verbose => 2) if ( $opt_help > 2 );
pod2usage(-verbose => 0, -message => "Version: $VERSION") if ( $#ARGV < 0 );

#
#   Configure the error reporting rmMerge_process now that we have the user options
#
ErrorConfig( 'name'    =>'RMQRY',
             'verbose' => $opt_verbose,
             'debug' => $opt_debug,
            );
Error('No Query') unless (length($ARGV[0]) > 0);

# Configue the Database to use
$rmCred = $opt_pulse ? $newRMCred : $oldRMCred; 
$rmCred = $opt_test ? $testRMCred : $rmCred;
$rmCred->[0] = $opt_server if defined $opt_server; 
$rmCred->[1] = $opt_user if defined $opt_user; 
$rmCred->[2] = $opt_passwd if defined $opt_passwd; 

my $colData;
my $newData = getDataFromRm('Data', $ARGV[0], $rmCred, {dump => $opt_verbose, oneRow => 0, 'error' => 'No data found', colData => \$colData});

#
#   Determine the max width of every dislpay column
#       Add Column Names as the first row of Data
#       Process column names and raw data
#
my @colNames;
foreach my $entry ( @{$colData}) {
    $entry =~ s~^(.*?):~~;
    push @colNames, $1;
}
my @colWidth;
my $colNum = ($#$colData);
unshift @{$newData}, \@colNames;

foreach my $entry ( @{$newData}) {
    for my $i (0 .. $colNum) {
        if (!defined $entry->[$i] ) {
            $entry->[$i] = '(null)'
        }
        my $len = length($entry->[$i]);
        if ( !defined($colWidth[$i]) || $len > $colWidth[$i]) {
            $colWidth[$i] = $len
        }
    }
}
#
#   Generate a sprintf formatted line
#   With no width arg on the last element to prevent space filling
#
my $fmtline ='';
my $fmtJoiner = '';
pop @colWidth;
foreach (@colWidth) {
    $fmtline .= $fmtJoiner . '%-' . $_ . 's';
    $fmtJoiner = ', ';
}
$fmtline .= $fmtJoiner . "%-s\n";

#
#   Display the data
#
foreach my $entry ( @{$newData}) {
    printf($fmtline, @{$entry});
}

#-------------------------------------------------------------------------------
# Function        : getDataFromRm 
#
# Description     : Get an array of data from RM 
#
# Inputs          : $name           - Query Name
#                   $m_sqlstr       - Query
#                   $rmRef          - Ref to RM
#                   $options        - Ref to a hash of options
#                                       sql     - show sql
#                                       data    - show data
#                                       dump    - show results
#                                       oneRow  - Only feth one row
#                                       error   - Must find data
#                                       colData - Ref to place to store colum data
#                                       
# Returns         : ref to array of data
#
sub getDataFromRm
{
    my ($name,$m_sqlstr, $rmRef, $options ) = @_;
    my @row;
    my $data;

    if (ref $options ne 'HASH') {
        $options = {}; 
    }

    $ENV{GBE_RM_LOCATION} = $rmRef->[0];
    $ENV{GBE_RM_USERNAME} = $rmRef->[1];
    $ENV{GBE_RM_PASSWORD} = $rmRef->[2];
    connectRM(\$RM_DB, $opt_verbose);

    if ($options->{sql}) {
        Message("$name: $m_sqlstr")
    }
    my $sth = $RM_DB->prepare($m_sqlstr);
    if ( defined($sth) )
    {
        if ( $sth->execute( ) ) {
            if ($options->{colData}) {
                ${$options->{colData}} = $sth->fetch_columndata();
            }
            if ( $sth->rows ) {
                while ( @row = $sth->fetchrow_array ) {
                    if ($options->{data}) {
                        Message ("$name: @row");
                    }
                    #Debug0("$name: @row");
                    push @{$data}, [@row];

                    last if $options->{oneRow};
                }
            }
            $sth->finish();
        } else {
            Error("Execute failure:$name: $m_sqlstr", $sth->errstr() );
        }
    } else {
        Error("Prepare failure:$name" );
    }
    disconnectRM(\$RM_DB);

    if (!$data && $options->{error}) {
        Error( $options->{error} );
    }

    if ($data && $options->{oneRow}) {
        $data = $data->[0];
    }
 
    if ($options->{dump}) {
        DebugDumpData("$name", $data);
    }
    return $data;
}

#-------------------------------------------------------------------------------
#   Documentation
#

=pod

=head1 NAME

rm_query - Perform ad-hoc queries against the Release Manager database

=head1 SYNOPSIS

jats rm_query [options] 'sql command'

 Options:
    -help              - brief help message
    -help -help        - Detailed help message
    -man               - Full documentation
    -verbose[=n]       - Enable additional output
    -pulse             - Use Pulse Database
    -test              - Use Test Database
    -user=nnn          - Specify alternate user name
    -password=nnn      - Specify alternate password
    -server=nnn        - Specify alternate server string

=head1 OPTIONS

=over 8

=item B<-help>

Print a brief help message and exits.

=item B<-help -help>

Print a detailed help message with an explanation for each option.

=item B<-versbose[=n]>

Enable additional output

=item B<-pulse>

This option will case the command to access the Pulse database. The default is to use the standard
Release Manager database and readonly credentails.

=item B<-test>

This option will case the command to access the Release Manager Test database.

=item B<-user=nnn>

Specify an alternate user name for the database connection.

=item B<-password=nnn>

Specify an alternate password for the database connection.

=item B<-server=nnn>

Specify an alternate server connection string for the database connection.

=back

=head1 EXAMPLE

jats rm_query  "select PV_ID,PKG_ID,PKG_VERSION,PKG_LABEL,SRC_PATH from release_manager.package_versions where pv_id=1223354"

If no data is found the utility will return a non-zero exit code.

=cut