Perl array references... yuck

#!/usr/bin/perl
# -------------------------------------------------------- 
# I hate Perl's syntax for scalars vs arrays vs hashes and 
# all of the nasty tricks in between for referencing them. 
# 
# I want to iterate over a hash table of arrays and then 
# pass each array to a function.  
# -------------------------------------------------------- 
# From the Camel Book:

%HoA = (
    flintstones    => [ "fred", "barney" ],
    jetsons        => [ "george", "jane", "elroy" ],
    simpsons       => [ "homer", "marge", "bart" ],
);

# Here's the book's way to loop through this:  

for $family ( keys %HoA ) {
    print $family . ": ";
    for $i ( 0 .. $#{ $HoA{$family} } ) {
        print $HoA{$family}[$i] . " "; 
    }
    print "\n";
}

# If I want to grab each array that comes out I need to 
# deference it with {} and declare it an an array with @
# I can't just say:  @fam_arr = @HoA{$family};
# So for the array that comes out which I refer to as  
# a scalar, I'm now going to refer to as an array:  

foreach $family ( keys %HoA ) {
    @fam_arr = @{ $HoA{$family} }; 
    print $family . ": ";
    foreach $name (@fam_arr) {
        print $name . " "; 
    }
    print "\n";
}

# Since Perl's parameter passing to functions expands arrays 
# into all of their values, I need to be explicit when I pass
# the array that I'm passing a reference to the array with \@
# The function then takes the scalar reference and I then tell 
# it to deference it with {} and treat it as an array with @

foreach $family ( keys %HoA ) {
    @fam_arr = @{ $HoA{$family} }; 
    print $family . ": ";
    print_list(\@fam_arr);
    print "\n";
}

sub print_list {
    my ($fam_arr) = @_;
    foreach $name (@{$fam_arr}) {
        print $name . " "; 
    }
}

# I find this to be excessive compared to similar languages.
# Perhaps I don't know a better way with Perl or this is just
# not a paradigm Perl promotes.  I'm trying to abstract some 
# things to shift focus away from what I want to do to each
# array in a real program and I've done it, but I feel I had
# to introduce what seems like some ugly reference tricks.  

Perl talking to Oracle

There is some data in an Oracle database that I need to retrieve regularly with a Perl script. So I need some sort of Oracle client to connect to the database so I can develop and and I need a library for my script to use to talk to it regularly. Oracle offers such a library. After your register you can get some non-Free software which is a pain to install. I'll now rant about the software.

Oracle says:

1. Download the appropriate Instant Client packages for your platform. All installations REQUIRE the Basic package.
2. Unzip the packages into a single directory such as "instantclient".
3. Set the library loading path in your environment to the directory in Step 2 ("instantclient"). On many UNIX platforms, LD_LIBRARY_PATH is the appropriate environment variable.
4. Start your application and enjoy.

The above implies that someone new to Oracle will have an easy time configuring and using this software. Not so. It assumes that you have some other files in your configuration and some other environment variables which it doesn't set.

Missing Secret Steps:

I installed three RPMs (using RHEL4U5):

 instantclient-basic-linux32
 instantclient-sdk-linux32
 instantclient-sqlplus-linux32

which unpack files in /usr/lib/oracle/10.2.0.3/client/lib and set up symlinks for sqlplus. The first think I see when I try to run it is:

sqlplus: error while loading shared libraries: libsqlplus.so: cannot
open shared object file: No such file or directory

Since I've got them installed in /usr/lib/oracle/10.2.0.3/client/lib I simply set my environment variable as per Oracle's instructions:

 LD_LIBRARY_PATH=/usr/lib/oracle/10.2.0.3/client/lib

I googled a little bit and found this so I also needed to set some other variables:

ORACLE_HOME=/home/oracle
ORACLE_BASE=/home/oracle
LD_LIBRARY_PATH=/usr/lib/oracle/10.2.0.3/client/lib
PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_HOME
export ORACLE_BASE
export LD_LIBRARY_PATH
export PATH

As you can see from the above I actually created a user oracle (with no password (i.e. !! in /etc/shadow)) and made an empty home directory. The RPMs won't create the oracle home directory which is this somewhat arbitrary place where oracle stores some config files.

Next time I tried to run it I saw:

Message file sp1.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software
directory

Turns out that you need this file in your ORACLE_HOME. Luckily I had a server where Oracle was installed and I found an sqlplus directory in the ORACLE_HOME which I scp'd over to my ORACLE_HOME:

# ls -R $ORACLE_HOME/sqlplus/
sqlplus/:
admin  doc  lib  mesg

sqlplus/admin:
glogin.sql  help  plustrce.sql  pupbld.sql

sqlplus/admin/help:
helpbld.sql  helpdrop.sql  helpus.sql  hlpbld.sql

sqlplus/doc:
cpyr.htm  oracle.gif  README.htm

sqlplus/lib:
env_sqlplus.mk  ins_sqlplus.mk  s0afimai.o

sqlplus/mesg:
cpyus.msb  sp1us.msb  sp2us.msb  sp3us.msb
cpyus.msg  sp1us.msg  sp2us.msg  sp3us.msg

I then tried to start sqlplus and saw: ORA-12162: TNS:net service name is incorrectly specified. Turns out that the names of the systems that you want to connect to are stored in a tnsnames.ora file. This files content's can look just like this:

FOO =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = 
        (PROTOCOL = TCP)
        (HOST = db-server.tld)
        (PORT = 2321)
      )
    )
    (CONNECT_DATA = (SID = FOO)
    )
  )

and then you can pass it arguments like this:

 sqlplus user@foo

it will then lookup foo as above and prompt you for a password. Of course you'd change foo to your DB name and HOST to your DB server.

I ended up again borrowing tnsnames.ora from my working server (don't know what I would have done without it). Now, I wasn't certain of where to put it, but I used an evolutionary algorithm, i.e. I was able to waste some time experiementing until I got it right which made me feel like slime trying to evolve. Anyway, I borrowed the entire network directory from my working Oracle server and put it in ORACLE_HOME.

Inside of this directory was the ADMIN directory, at least that's how it was on the server that I borrowed from. I ended up doing a symlink with the lowercase version to finally get it working:

[root@host network]# ll | grep admin
lrwxrwxrwx  1 root   root    6 Aug 21 12:08 admin -> ADMIN/
[root@host network]#

So AFAICT, tnsnames.ora goes in $ORACLE_HOME/network/admin/.

Coming from MySQL all of this is a pain. I like installing my MySQL client with one command. I like being able to specify which host to connect to by passing the -h not editing a tnsnames.ora file (though I did have fun with the S-expressions). I like being able to use my package manager to have instant access to the libraries that I need. Instead the Perl module to talk to the database will complain if it can't find references to the Oracle client above as it tries to compile. Let's talk more about that.

CPAN

Don't think you can just skip here and just use CPAN without Enterprise Grade Genuine Advantage Enterprise Oracle Software.

cpan[3]> install DBD:Oracle
Running install for module 'DBD::Oracle'
...
Trying to find an ORACLE_HOME
Your LD_LIBRARY_PATH env var is set to ''

      The ORACLE_HOME environment variable is not set and I couldn't
      guess it.
      It must be set to hold the path to an Oracle installation
      directory
      on this machine (or a machine with a compatible architecture).
      See the README.clients.txt file for more information.
      ABORTED!
  
Warning: No success on command[/usr/bin/perl Makefile.PL]
  PYTHIAN/DBD-Oracle-1.19.tar.gz
  /usr/bin/perl Makefile.PL -- NOT OK

Even after installing the client as described above and getting sqlplus working from the command line first. I still had some trouble doing the above. I ended up downloading DBD-Oracle-1.19.tar.gz from: cpan into /usr/local/src/ and doing a:

 perl Makefile.PL 
 make 
 make install

I was then finally able to have some code do:

use DBI;
my $dbh = DBI->connect('DBI:Oracle:dbname', 'user', 'pass')
    or die "Couldn't connect to database: " . DBI->errstr;

The above seems to work.

Ruby Scripting Part II

Ruby's Net::SSH is pretty cool. Here's a script to SSH into a set of hosts and do some work. It assumes that you're using sudo with local passwords and well as public keys.

#!/usr/bin/ruby
# -------------------------------------------------------- 
# SSH's into a set of hosts as user (assumes he has sudo)
# Uses local password (gathered earlier) to execute a set 
# of commands as root
# -------------------------------------------------------- 
require 'rubygems' 
require 'net/ssh'
# -------------------------------------------------------- 
# Store systems & commands to be run as whom
user = "user"
hosts = Array.new
hosts[0] = "system1"
hosts[1] = "system2"
cmds = Array.new
cmds[0] = "touch x"
cmds[1] = "touch y"
cmds[2] = "touch z"
# -------------------------------------------------------- 
# Cache tokens
print "Enter local password for hosts (assuming same): "
system("stty -echo") # hide input (password)
password = gets.chomp.to_s
system("stty echo") # show input again
print "\n"
# use ssh agent to cache key
system("ssh-agent")
system("ssh-add")
# -------------------------------------------------------- 
# SSH into each host and sudo execute commands
hosts.each do |host|
  Net::SSH.start(host, user) do |session|
    shell = session.shell.open
    shell.cd "/home/#{user}"
    cmds.each do |cmd|
      shell.echo "#{password} | sudo -S #{cmd}"
    end
    shell.exit
    # give the above commands sufficient time to terminate
    sleep 0.5
    # display the output
    $stdout.print shell.stdout while shell.stdout?
    $stderr.puts "-- stderr: --"
    $stderr.print shell.stderr while shell.stderr?
  end
end
# -------------------------------------------------------- 
# Clean out cached tokens
password = ""
system("ssh-add -d ~/.ssh/id_rsa")

Ruby Scripting

I've got some scripting to do. I'm going to try using Ruby. Aside from a quick rails tutorial, I have never really used Ruby so let's see how easy it is to learn.

I've got to change some IP addresses on a set of Linux boxen. I'm going to encode my data as recursive hash tables. First I'll get comfortable with the syntax by comparing it to some all too familiar PHP. Here's an example:

#!/usr/bin/php -q
<?php
$data = array( 
      "server0" => 
      array(
     "old_ip" => "123.456.234.51",
     "new_ip" => "123.456.7.5",
     "gateway" => "123.456.7.1", 
     "subnet" => "255.255.255.224", 
     ),
      "server1" => 
      array(
     "old_ip" => "123.456.234.52",
     "new_ip" => "123.456.7.6",
     "gateway" => "123.456.7.1", 
     "subnet" => "255.255.255.224", 
     ),
      );

foreach ($data as $host => $fields) {
  print $host . ":\n";
  foreach ($fields as $name => $value) {
    print "\t $name \t $value \n";
  }
}
?>

Translating this to Ruby:

#!/usr/bin/ruby

data = { 
      "server0" => 
        {
     "old_ip" => "123.456.234.51",
     "new_ip" => "123.456.7.5",
     "gateway" => "123.456.7.1", 
     "subnet" => "255.255.255.224", 
        },
      "server1" => 
        {
     "old_ip" => "123.456.234.52",
     "new_ip" => "123.456.7.6",
     "gateway" => "123.456.7.1", 
     "subnet" => "255.255.255.224", 
        },
  }

data.each do |host, fields| 
    puts host + ":\n"
    fields.each do |name, value|
       puts "\t #{name} \t #{value} \n"
    end
end