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