DLPActivity.pl/YELL
- Rewrote DLPAcctivity.pl, a Clearquest reporting tool that emails DLP engineers about Clearquest defects
- Look into YELL scripts
« January 2007 | Main | April 2007 »
Just about the time we were getting the Single Signon working I realized that we also have command line or a script based API in which we allow others to call GPDB functionality. Part of that functionality is logging in with gpdb_login. Well since we didn't come through Single Signon nro a web page how do we get the authorization to log into the database? Answer is we don't. Single Signon is only good for web apps where the only way into the application is through the web.
The security guys did mention something about a command line alternative but it didn't seem like it would fit the bill. I believe that it only allows you to authenticate as your currently logged in user. What if, for example, user A wished to connect to GPDB as user B and knows user B's username and password? What when a script wished to login as an admin on behalf of all users. These questions need to be answered.
Joe Ramey had worked on getting the Oracle Linux client API library to work with Perl. I am trying to validate that this works on all supported architectures where all supported architectures is considered the subset of:
Normal access to SQL databases in Perl usually consists of use'ing two different Perl modules. First a DBI.pm module which is the DataBase Independent interface and the DBD or DataBase Driver module for the particular SQL database involved. We are interested in Oracle here but others include mSQL, MySQL, etc.
Couple this with the fact that we wish to use the standard Perl (e.g. /apps/perl/5.8.3/bin/perl) and standard Perl modules (/apps/perl/modules-<datecode> where datecode is some combination of a year/month date code whose apparent LCD seems to be modules-0412).
So first our Perl script should start with:
#!/apps/perl/5.8.3/bin/perl use strict; use warnings; # Standard CDOE modules (Modules of the month!) - need this to pick up DBI.pm use lib "/apps/perl/modules-0412/lib";
So as the comment indicates we are picking up the DBI.pm module from here. Next, normally, we'd simply include the lib path for the Oracle DBD.pm (or in the special case of Oracle, the Oracle.pm file) as well as the supporting shared library Oracle.so (which is architecturally dependent) by coding the following:
# Perl/Oracle libraries use lib "/apps/oracle/perl/10.2.0.1.0/lib";
However, since Linux client support is not included in /apps/oracle/perl/10.2.0.1.0/lib (yet) we need to pick up Joe's stuff:
# Linux API is in Ramey's home dir... use lib "/home/ramey/oracle/perl/10.2.0.1.0/lib";
The next excerpt from my code, shown in it's entirety later, shows a lib path I used to use that allows Solaris clients to work correctly, however we wish to move away from this special cased library thus it's commented out here:
# This is a non standard Oracle #use lib "/apps/perl/5.8.0/lib/perl5/site_perl/5.8.0";
As the full code listing that follows shows I then use my current development libraries and attempt to log into the development version of GPDB, and Oracle database, and retrieve and display some rudimentary data:
Entire code listing of ~x0062320/testgpdb.pl
#!/apps/perl/5.8.3/bin/perl
use strict;
use warnings;
# Standard CDOE modules (Modules of the month!) - need this to pick up DBI.pm
use lib "/apps/perl/modules-0412/lib";
# Perl/Oracle libraries
use lib "/apps/oracle/perl/10.2.0.1.0/lib";
# Linux API is in Ramey's home dir...
use lib "/home/ramey/oracle/perl/10.2.0.1.0/lib";
# This is a non standard Oracle
#use lib "/apps/perl/5.8.0/lib/perl5/site_perl/5.8.0";
# Current development GPDB modules
use lib "/web/gpdb/cgi-bin";
use GPDB::gpdb;
# Log into GPDB - read only
gpdb_login (undef, undef, "OracleDevelopment");
# Get a project
my @projects = gpdb_getProject ("uma");
# Play with it a little bit...
my %project = %{$projects [0][0]};
print "Project: ${project {PROJECT}{NAME}}\n";
print "Site: ${project {PROJECT}{SITE_NAME}}\n";
print "Vobs:\n";
foreach (@{$project {CLEARCASE}}) {
my %cc = %{$_};
print "\t${cc {VOB_TAG}}\n";
} # foreach
print "done\n";
Here's the results running on the target architectures:
$ ssh stashu testgpdb.pl
DBI connect('host=dflorad01.itg.ti.com;sid=flddsync;port=1521','cm_gpdb_readonly',...) failed: ERROR OCIEnvNlsCreate. Check ORACLE_HOME env var, NLS settings, permissions, etc. at /web/gpdb/cgi-bin/GPDB/DBHelper.pm line 136
Could not connect to database. Check $ORACLE_HOME. at /web/gpdb/cgi-bin/GPDB/primitive.pm line 81
$ ssh fad01 testgpdb.pl
Project: uma
Site: Dallas
Vobs:
/cdb/dspumatdl
/cdb/uma
/cdb/uma_2.2
/cdb/uma_2.6a
done
$ ssh drteeth testgpdb.pl
Project: uma
Site: Dallas
Vobs:
/cdb/dspumatdl
/cdb/uma
/cdb/uma_2.2
/cdb/uma_2.6a
done
$ ssh fad11 testgpdb.pl
Can't load
'/home/ramey/oracle/perl/10.2.0.1.0/lib/i686-linux-thread-multi/auto/DBI/DBI.so' for module DBI: /lib/libc.so.6: version `GLIBC_2.3' not found (required by
/home/ramey/oracle/perl/10.2.0.1.0/lib/i686-linux-thread-multi/auto/DBI/DBI.so) at /apps/perl/5.8.3/lib/5.8.3/i686-linux-thread-multi/DynaLoader.pm line 229.
at /home/ramey/oracle/perl/10.2.0.1.0/lib/i686-linux-thread-multi/DBI.pm line 259
BEGIN failed--compilation aborted at /home/ramey/oracle/perl/10.2.0.1.0/lib/i686-linux-thread-multi/DBI.pm line 259.
Compilation failed in require at /web/gpdb/cgi-bin/GPDB/primitive.pm line 24.
BEGIN failed--compilation aborted at /web/gpdb/cgi-bin/GPDB/primitive.pm line 24.
Compilation failed in require at /web/gpdb/cgi-bin/GPDB/gpdb.pm line 38.
BEGIN failed--compilation aborted at /web/gpdb/cgi-bin/GPDB/gpdb.pm line 38.
Compilation failed in require at ./testgpdb.pl line 20.
BEGIN failed--compilation aborted at ./testgpdb.pl line 20.
So as we can see this is not working for Solaris or Linux 64-bit Intel.
Chris Davey wrote:Andrew
have update the /apps/cmpackages as per the web page ane try the new version of mkview_linked but faile to run:
sunos
warmflash,a0741187> ./mkview_linked install_driver(mysql) failed: Can't load '/apps/cmpackages/perl/lib/site_perl/5.8.0/sun4-solaris-thread-multi/auto/DBD/mysql/mysql.so' for module DBD::mysql: ld.so.1: mkview_linked: fatal: libmysqlclient.so.12: open failed: No such file or directory at /apps/cmpackages/perl/lib/5.8.0/sun4-solaris-thread-multi/DynaLoader.pm line 229. at (eval 1) line 3 Compilation failed in require at (eval 1) line 3. Perhaps a required shared library or dll isn't installed where expected at ./mkview_linked line 54 warmflash,a0741187> ls -l /apps/cmpackages/perl/lib/site_perl/5.8.0/sun4-solaris-thread-multi/auto/DBD/mysql total 712 -r--r--r-- 1 amemgr ameadm 0 Mar 16 09:05 mysql.bs -r-xr-xr-x 1 amemgr ameadm 360080 Mar 16 09:05 mysql.so*linux
ace.tiuk.ti.com,a0741187> ./mkview_linked install_driver(mysql) failed: Can't load '/apps/cmpackages/perl/lib/site_perl/5.8.0/i686-linux-thread-multi/auto/DBD/mysql/mysql.so' for module DBD::mysql: libmysqlclient.so.12: cannot open shared object file: No such file or directory at /apps/cmpackages/perl/lib/5.8.0/i686-linux-thread-multi/DynaLoader.pm line 229. at (eval 1) line 3 Compilation failed in require at (eval 1) line 3. Perhaps a required shared library or dll isn't installed where expected at ./mkview_linked line 54 ace.tiuk.ti.com,a0741187> ls -l /apps/cmpackages/perl/lib/site_perl/5.8.0/sun4-solaris-thread-multi/auto/DBD/mysql ls: /apps/cmpackages/perl/lib/site_perl/5.8.0/sun4-solaris-thread-multi/auto/DBD/mysql: No such file or directoryThe problem seems to be that you lack /apps/mysql/<version> which is what we used to build the DBD::mysql package. We are working on alternate solutions. Meantime could you try the following:
- mkdir /apps/mysql
- On Solaris (warmflash): rsync -rlz stashu.dal.design.ti.com:/apps/mysql/4.0.16 /apps/mysql
- On Linux (ace): rsync -rlz drteeth.dal.design.ti.com:/apps/mysql/4.0.17 /app/mysql
Try mkview_linked. Let us know.
Nix the rsyncing of /apps/mysql and redo the rsync of /apps/cmpackages. We're put a copy of mysql under /apps/cmpackages. After refreshing your /apps/cmpackages area you should see an /apps/cmpackages/mysql directory. Then test mkview_linked again.
In order to get mkview_linked to use DBD::mysql, the Perl package of mysql must be compiled and installed. Perl packages often glue some subsystem's API (in this case MySQL), normally written in C or something like that to a Perl package (i.e. a .pm file). So on the one end we have architecturally dependent code (usually a .a or .so library) and on the other we have (hopefully) architecturally neutral and interpreted Perl code (the .pm module). Therefore the process usually involves a makefile, execution of gcc to compile the glue code between the Perl and the subsystem's API as well as installing into the proper place, all of these files.
When Perl then calls the module DynaLoader.pm gets involved to load the appropriate architecturally dependent libraries so that calls to the Perl subroutines flow through the glue code and the appropriate API is called.
Since architecturally dependent code is involved we need to build this for the different architectures that we support. Further, MySQL in particular, has it's own library, libmysqlclient.a, which contains the API itself. So the glue code (mysql.so) needs to locate and load the appropriate libmysqlclient.a before it can successfully call an API. Finally there are issues of the version number of the, in this case, client software and the server software. Suffice to say, this is not always easy stuff!
Oh and we need mention that we are also talking about a large corporation here with various sites and the like; in most cases MySQL's client libraries will not be available locally nor of the right version - IOW we cannot trust that the MySQL client libraries will be local.
Oh and did I mention that mysql is not officially supported by TI...
We had hoped that /apps/mysql/<version>, while not officially supported, would be widely available but on our first guinea pi... ah... test subject, TI UK, we found that not to be the case. It looked like we had two ways to go: either 1) copy the /apps/mysql/<version> over to the remote sites or 2) include the /apps/mysql/<version> trees under /apps/cmpackages as our own mini distribution. I've implemented the latter. Now refreshing your /apps/cmpackages tree will carry along the necessary MySQL client libraries necessary to support mkview_linked and friends in most situations.
Seems this set of scripts didn't like the additional of the Make licensing because Make licensing often had 0 usage. The 0 was being carried forward and used as a denominator for some divisions causing divide by zero errors. I fixed up these errors which then caused it to loop indefinitely. Seems a for loop didn't properly anticipate having a usage of 0. Fixed
After downloading DBD::mysql from http://search.cpan.org/~capttofu/DBD-mysql-4.003/lib/DBD/mysql.pm I encountered some problems. First one needs to use the appropriate perl, one that has a compatible DBI module already. I found this in /app/cmpackages/perl/bin/perl. The more standard perl, /apps/perl/5.8.3/bin/perl seems to have a newer DBI module that is not compatible with the DBD::mysql that I downloaded.
Then one needs to give the proper parameters to generate the Makefile:
$ export PATH=$PATH:/apps/mysql/4.0.16/bin $ /apps/cmpackages/perl/bin/perl Makefile.PL \ > --libs="-L/apps/mysql/4.0.16/lib/mysql -lmysqlclient -lz" \ > --cflags=-I/apps/mysql/4.0.16/include/mysql \ > --testhost=mysql01.dal.design.ti.com \ These will not always be the same > --testuser=ccprojects \ This will change > --testpassword=ccpr0jects This will change (note the 0 not O)
Note that we explicitly use /apps/cmpackages/perl/bin/perl here. I would rather use /apps/perl/5.8.3/bin/perl but it's DBI appears to be too new for this version of DBD::mysql (!).
This will produce a Makefile which you then do:
$ make $ make test
Alas, normally one would do a make install but that would attempt to install into /usr/local/perl, not something we wish to do. And there doesn't appear to be a way to pack into Makefile.PL where you want to eventually install this module when make install is run, so we need to actually modify the Makefile itself.
Turns out that perl Makefile.PL <options> specifies which perl to use thus the following appears in the generated Makefile:
INSTALLPRIVLIB = /apps/cmpackages/perl/lib/5.8.0 INSTALLSITELIB = /apps/cmpackages/perl/lib/site_perl/5.8.0 INSTALLVENDORLIB = INSTALLARCHLIB = /apps/cmpackages/perl/lib/5.8.0/sun4-solaris-thread-multi INSTALLSITEARCH = /apps/cmpackages/perl/lib/site_perl/5.8.0/sun4-solaris-thread-multi INSTALLVENDORARCH = INSTALLBIN = /apps/cmpackages/perl/bin INSTALLSITEBIN = /apps/cmpackages/perl/bin INSTALLVENDORBIN = INSTALLSCRIPT = /apps/cmpackages/perl/bin
Thus this means a make install will install the module into whichever Perl installation we specified in the perl Makefile.PL <options>! Alas we cannot write to those areas yet...