WebSVN – DevTools – Diff – /core_devl/trunk/TOOLS/jats_rm_query.pl



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
#

                "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,
                );
 

            );
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";

#                                       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");
                    }

    }
 
    if ($options->{dump}) {
        DebugDumpData("$name", $data);
    }
    return $data;
}
 
#-------------------------------------------------------------------------------
#   Documentation
#

    -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

=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>

Rev 6915	Rev 6922
Line 32...	Line 32...
32	`my $RM_DB;`	32	`my $RM_DB;`
33		33
34	`# ReadOnly credentails to known databases`	34	`# ReadOnly credentails to known databases`
35	`my $newRMCred = ['jdbc:oracle:thin:@auawsards002:1521:RELEASEM', 'RM_READONLY', 'Tp8WmmDKMq2Z'];`	35	`my $newRMCred = ['jdbc:oracle:thin:@auawsards002:1521:RELEASEM', 'RM_READONLY', 'Tp8WmmDKMq2Z'];`
36	`my $oldRMCred = ['jdbc:oracle:thin:@auawsards001:1521:RELEASEM', 'RM_READONLY', 'RM_READONLY'];`	36	`my $oldRMCred = ['jdbc:oracle:thin:@auawsards001:1521:RELEASEM', 'RM_READONLY', 'RM_READONLY'];`
-		37	`my $testRMCred = ['jdbc:oracle:thin:@relmanu1.coidtfba5ouc.ap-southeast-2.rds.amazonaws.com:1521:RELMANU1', 'RM_READONLY', 'RM_READONLY'];`
37	`my $rmCred;`	38	`my $rmCred;`
38		39
39	`my $VERSION = 1.0;`	40	`my $VERSION = 1.0;`
40	`my $opt_help=0;`	41	`my $opt_help=0;`
41	`my $opt_verbose=0;`	42	`my $opt_verbose=0;`
42	`my $opt_debug=0;`	43	`my $opt_debug=0;`
43	`my $opt_pulse;`	44	`my $opt_pulse;`
44	`my $opt_user;`	45	`my $opt_user;`
45	`my $opt_passwd;`	46	`my $opt_passwd;`
46	`my $opt_server;`	47	`my $opt_server;`
-		48	`my $opt_test;`
47		49
48		50
49	`#-------------------------------------------------------------------------------`	51	`#-------------------------------------------------------------------------------`
50	`# Function : Mainline Entry Point`	52	`# Function : Mainline Entry Point`
51	`#`	53	`#`
Line 57...	Line 59...
57	`"help:+" => \$opt_help,`	59	`"help:+" => \$opt_help,`
58	`"manual:3" => \$opt_help,`	60	`"manual:3" => \$opt_help,`
59	`"verbose:+" => \$opt_verbose,`	61	`"verbose:+" => \$opt_verbose,`
60	`"debug:+" => \$opt_debug,`	62	`"debug:+" => \$opt_debug,`
61	`"pulse!" => \$opt_pulse,`	63	`"pulse!" => \$opt_pulse,`
-		64	`"test!" => \$opt_test,`
62	`"user:s" => \$opt_user,`	65	`"user:s" => \$opt_user,`
63	`"password:s" => \$opt_passwd,`	66	`"password:s" => \$opt_passwd,`
64	`"server:s" => \$opt_server,`	67	`"server:s" => \$opt_server,`
65	`);`	68	`);`
66		69
Line 85...	Line 88...
85	`);`	88	`);`
86	`Error('No Query') unless (length($ARGV[0]) > 0);`	89	`Error('No Query') unless (length($ARGV[0]) > 0);`
87		90
88	`# Configue the Database to use`	91	`# Configue the Database to use`
89	`$rmCred = $opt_pulse ? $newRMCred : $oldRMCred;`	92	`$rmCred = $opt_pulse ? $newRMCred : $oldRMCred;`
-		93	`$rmCred = $opt_test ? $testRMCred : $rmCred;`
90	`$rmCred->[0] = $opt_server if defined $opt_server;`	94	`$rmCred->[0] = $opt_server if defined $opt_server;`
91	`$rmCred->[1] = $opt_user if defined $opt_user;`	95	`$rmCred->[1] = $opt_user if defined $opt_user;`
92	`$rmCred->[2] = $opt_passwd if defined $opt_passwd;`	96	`$rmCred->[2] = $opt_passwd if defined $opt_passwd;`
93		97
-		98	`my $colData;`
94	`my ($newData, $colData) = getDataFromRm('Data', $ARGV[0], $rmCred, {dump => $opt_verbose, oneRow => 0, 'error' => 'No data found'});`	99	`my $newData = getDataFromRm('Data', $ARGV[0], $rmCred, {dump => $opt_verbose, oneRow => 0, 'error' => 'No data found', colData => \$colData});`
-		100
95	`#`	101	`#`
96	`# Determine the max width of every dislpay column (except the last)`	102	`# Determine the max width of every dislpay column`
97	`# Add Column Names as the first row of Data`	103	`# Add Column Names as the first row of Data`
98	`# Process column names and raw data`	104	`# Process column names and raw data`
99	`#`	105	`#`
100	`my @colNames;`	106	`my @colNames;`
101	`foreach my $entry ( @{$colData}) {`	107	`foreach my $entry ( @{$colData}) {`
102	`$entry =~ s~^(.*?):~~;`	108	`$entry =~ s~^(.*?):~~;`
103	`push @colNames, $1;`	109	`push @colNames, $1;`
104	`}`	110	`}`
105	`my @colWidth;`	111	`my @colWidth;`
106	`my $colNum = ($#$colData) - 1;`	112	`my $colNum = ($#$colData);`
107	`unshift @{$newData}, \@colNames;`	113	`unshift @{$newData}, \@colNames;`
108		114
109	`foreach my $entry ( @{$newData}) {`	115	`foreach my $entry ( @{$newData}) {`
110	`for my $i (0 .. $colNum) {`	116	`for my $i (0 .. $colNum) {`
111	`if (!defined $entry->[$i] ) {`	117	`if (!defined $entry->[$i] ) {`
112	`$entry->[$i] = ''`	118	`$entry->[$i] = '(null)'`
113	`}`	119	`}`
114	`my $len = length($entry->[$i]);`	120	`my $len = length($entry->[$i]);`
115	`if ( !defined($colWidth[$i]) \|\| $len > $colWidth[$i]) {`	121	`if ( !defined($colWidth[$i]) \|\| $len > $colWidth[$i]) {`
116	`$colWidth[$i] = $len`	122	`$colWidth[$i] = $len`
117	`}`	123	`}`
118	`}`	124	`}`
119	`}`	125	`}`
120	`#`	126	`#`
121	`# Generate a sprintf formatted line`	127	`# Generate a sprintf formatted line`
122	`# With no width arg on the last element`	128	`# With no width arg on the last element to prevent space filling`
123	`#`	129	`#`
124	`my $fmtline ='';`	130	`my $fmtline ='';`
125	`my $fmtJoiner = '';`	131	`my $fmtJoiner = '';`
-		132	`pop @colWidth;`
126	`foreach (@colWidth) {`	133	`foreach (@colWidth) {`
127	`$fmtline .= $fmtJoiner . '%-' . $_ . 's';`	134	`$fmtline .= $fmtJoiner . '%-' . $_ . 's';`
128	`$fmtJoiner = ', ';`	135	`$fmtJoiner = ', ';`
129	`}`	136	`}`
130	`$fmtline .= $fmtJoiner . "%-s\n";`	137	`$fmtline .= $fmtJoiner . "%-s\n";`
Line 148...	Line 155...
148	`# sql - show sql`	155	`# sql - show sql`
149	`# data - show data`	156	`# data - show data`
150	`# dump - show results`	157	`# dump - show results`
151	`# oneRow - Only feth one row`	158	`# oneRow - Only feth one row`
152	`# error - Must find data`	159	`# error - Must find data`
-		160	`# colData - Ref to place to store colum data`
153	`#`	161	`#`
154	`# Returns : ref to array of data`	162	`# Returns : ref to array of data`
155	`# ref to array of Column Data`	-
156	`#`	163	`#`
157	`sub getDataFromRm`	164	`sub getDataFromRm`
158	`{`	165	`{`
159	`my ($name,$m_sqlstr, $rmRef, $options ) = @_;`	166	`my ($name,$m_sqlstr, $rmRef, $options ) = @_;`
160	`my @row;`	167	`my @row;`
161	`my $data;`	168	`my $data;`
162	`my $colData;`	-
163		169
164	`if (ref $options ne 'HASH') {`	170	`if (ref $options ne 'HASH') {`
165	`$options = {};`	171	`$options = {};`
166	`}`	172	`}`
167		173
168	`$ENV{GBE_RM_LOCATION} = $rmRef->[0];`	174	`$ENV{GBE_RM_LOCATION} = $rmRef->[0];`
169	`$ENV{GBE_RM_USERNAME} = $rmRef->[2];`	175	`$ENV{GBE_RM_USERNAME} = $rmRef->[1];`
170	`$ENV{GBE_RM_PASSWORD} = $rmRef->[1];`	176	`$ENV{GBE_RM_PASSWORD} = $rmRef->[2];`
171	`connectRM(\$RM_DB, $opt_verbose);`	177	`connectRM(\$RM_DB, $opt_verbose);`
172		178
173	`if ($options->{sql}) {`	179	`if ($options->{sql}) {`
174	`Message("$name: $m_sqlstr")`	180	`Message("$name: $m_sqlstr")`
175	`}`	181	`}`
176	`my $sth = $RM_DB->prepare($m_sqlstr);`	182	`my $sth = $RM_DB->prepare($m_sqlstr);`
177	`if ( defined($sth) )`	183	`if ( defined($sth) )`
178	`{`	184	`{`
179	`if ( $sth->execute( ) ) {`	185	`if ( $sth->execute( ) ) {`
-		186	`if ($options->{colData}) {`
180	`$colData = $sth->fetch_columndata();`	187	`${$options->{colData}} = $sth->fetch_columndata();`
-		188	`}`
181	`if ( $sth->rows ) {`	189	`if ( $sth->rows ) {`
182	`while ( @row = $sth->fetchrow_array ) {`	190	`while ( @row = $sth->fetchrow_array ) {`
183	`if ($options->{data}) {`	191	`if ($options->{data}) {`
184	`Message ("$name: @row");`	192	`Message ("$name: @row");`
185	`}`	193	`}`
Line 207...	Line 215...
207	`}`	215	`}`
208		216
209	`if ($options->{dump}) {`	217	`if ($options->{dump}) {`
210	`DebugDumpData("$name", $data);`	218	`DebugDumpData("$name", $data);`
211	`}`	219	`}`
212	`return $data, $colData;`	220	`return $data;`
213	`}`	221	`}`
214		222
215	`#-------------------------------------------------------------------------------`	223	`#-------------------------------------------------------------------------------`
216	`# Documentation`	224	`# Documentation`
217	`#`	225	`#`
Line 230...	Line 238...
230	`-help - brief help message`	238	`-help - brief help message`
231	`-help -help - Detailed help message`	239	`-help -help - Detailed help message`
232	`-man - Full documentation`	240	`-man - Full documentation`
233	`-verbose[=n] - Enable additional output`	241	`-verbose[=n] - Enable additional output`
234	`-pulse - Use Pulse Database`	242	`-pulse - Use Pulse Database`
-		243	`-test - Use Test Database`
235	`-user=nnn - Specify alternate user name`	244	`-user=nnn - Specify alternate user name`
236	`-password=nnn - Specify alternate password`	245	`-password=nnn - Specify alternate password`
237	`-server=nnn - Specify alternate server string`	246	`-server=nnn - Specify alternate server string`
238		247
239	`=head1 OPTIONS`	248	`=head1 OPTIONS`
Line 255...	Line 264...
255	`=item B<-pulse>`	264	`=item B<-pulse>`
256		265
257	`This option will case the command to access the Pulse database. The default is to use the standard`	266	`This option will case the command to access the Pulse database. The default is to use the standard`
258	`Release Manager database and readonly credentails.`	267	`Release Manager database and readonly credentails.`
259		268
-		269	`=item B<-test>`
-		270
-		271	`This option will case the command to access the Release Manager Test database.`
-		272
260	`=item B<-user=nnn>`	273	`=item B<-user=nnn>`
261		274
262	`Specify an alternate user name for the database connection.`	275	`Specify an alternate user name for the database connection.`
263		276
264	`=item B<-password=nnn>`	277	`=item B<-password=nnn>`

Subversion Repositories DevTools

(root)/core_devl/trunk/TOOLS/jats_rm_query.pl – Rev 6915 → 6922