CSCI399
Autumn Session, 2009

Exercise 2

This exercise covers some preliminary work that you should attempt before you start Assignment 2. Most of the work for this exercise will involve the use of “xterm” terminal sessions in your Ubuntu environment, and use of a text editor. (The simple gedit text editor, available via menu, should suffice.)

1. Check out the Perl environment:

 
 
perl -version
perldoc perlfunc
 

2. Try some of the example code from the lectures; e.g. the "cross word puzzle player's cheat program":

 
 
#!/share/bin/perl
 
open(INPUT, "/usr/dict/words") || die;
 
print "Enter the word pattern you seek : ";
$wordpat = <STDIN>;
chomp($wordpat);
 
while(<INPUT>) {
 if( /^$wordpat$/ ) { print $_; }
}
 

(The Ubuntu systems don't have a copy of the word dictionary, but you can get one from the main banshee server's /usr/dict/words – just copy it to your own directory while trying the exercise, delete it later.)
Experiment with different regular expression patterns, e.g.:

 
$ perl words.pl
Enter the word pattern you seek : ..ac...s
gracious
spacious
$ perl words.pl
Enter the word pattern you seek : ..ac.*s
abacus
deaconess
fractious
glacis
gracious
spacious

(Make sure you understand how the words fit the regular expression patterns.)

3. Perl competes with more specialized languages (sh, Python etc) as a scripting language for routine systems administration tasks. This little exercise builds a Perl program that does a small subset of what can be achieved using the Unix find command; but it is still a useful little fragment. The program scrambles through a directory tree, searching through all accessible subdirectories for files whose names contain a given string.

An example Unix find command looking for README files in one of the JDK installations is (this example was run on banshee, not the Ubuntu systems):

 
$ find /packages/java/jdk/1.6.0_04 -name README* 
/packages/java/jdk/1.6.0_04/db/demo/programs/localcal/README
/packages/java/jdk/1.6.0_04/db/demo/programs/scores/README
/packages/java/jdk/1.6.0_04/README.html
/packages/java/jdk/1.6.0_04/README_ja.html
...
...

Your DirectoryLister.pl program is to:

1. Take a command line arguments with partial file name that is to be found and the name of a directory.
2. This initial directory name is used to initialize a list of directories that are to be processed.
3. The processing loop:
• opens the next directory from the list;
• processes each entry in the directory; the fully qualified pathnames for files whose names match the search pattern are printed; entries that correspond to readable subdirectories get added to the list of directories that are to be processed in later iterations.

If you were to sort the outputs from the find program and the directory lister program, then these sorted outputs should be identical.

4. Set up an environment that will allow the use of sqlplus and Perl programs that use DBI to access the Oracle database.
   You will need to define and export the following environment variables:

 
PATH=$PATH:/usr/local/instantclient_10_2
export PATH
SQLPATH=/usr/local/instantclient_10_2
export SQLPATH
LD_LIBRARY_PATH=/usr/local/instantclient_10_2
TNS_ADMIN=/usr/local/instantclient_10_2
export LD_LIBRARY_PATH
export TNS_ADMIN
ORACLE_HOME=/usr/local/instantclient_10_2
export ORACLE_HOME

You can define those settings in a little script that you run each time you want to use sqlplus or Perl, or add them to your .profile.

5. Use sqlplus to access your schema in the Oracle database:

 
$ sqlplus userid@csci

(You should have received an email from yuan, yuan@uow.edu.au, with your initial Oracle password; you may wish to change this to something not so easily guessed.)

6. You will need a datatable with a few records to test access in each of the environments. Try the following (you can substitute NRL or AFL teams for the soccer teams if you prefer):

 
drop table soccerleague;
drop sequence leagueseq;
 
create sequence leagueseq increment by 1 start with 1;
 
create table soccerleague (
 gameid number primary key,
 played date,
 location varchar(64),
 team1 varchar(32),
 team2 varchar(32),
 score1 number,
 score2 number
);
 
insert into soccerleague values (
 leagueseq.nextval,
  TO_DATE('2007-08-26','YYYY-MM-DD'),
  'Members Equity Stadium',
  'Perth', 'Newcastle', 0, 0);
 
  ...
  

7. Test your Perl DBI connection with a simple Perl script that accesses your table, something like the following:

 
 
use DBI;
$dbh = DBI->connect("dbi:Oracle:CSCI","homer","doh") || die "It didn't connect";
 
print "Connected\n";
 
$searchhandle = $dbh->prepare("select * from soccerleague");
 
$searchhandle->execute || die "Select request failed because $DBI::errstr";
 
 
while(@row=$searchhandle->fetchrow_array) {
 print "@row\n";
}
 

(Use your own userid and password, homer has changed his password.)
You should get output like the following:

 
 
1 26-AUG-07 Members Equity Stadium Perth Newcastle 0 0
2 26-AUG-07 Westpac Stadium Wellington Melbourne 2 2
3 26-AUG-07 Suncorp Stadium Roar Adelaide 2 2
4 26-AUG-07 Sydney Football Stadium Central coast Sydney 1 0
5 02-SEP-07 Energy Australia Stadium Newcastle Roar 1 1
...
 

8. Create a Perl script that will generate a HTML page with similar data. The code will be something like:

 
 
#!/usr/bin/perl 
 
use DBI;
 
 
print "Content-type: text/html\n\n";
 
$ENV{"ORACLE_HOME"} = "/usr/local/instantclient_10_2";
$ENV{"LD_LIBRARY_PATH"} = "/usr/local/instantclient_10_2";
$ENV{"TNS_ADMIN"} = "/usr/local/instantclient_10_2";
$ENV{"ORACLE_SID"} = "csci";
$ENV{"TWO_TASK"} = "csci";
 
$data_source = "dbi:Oracle:CSCI";
 
$dbh = DBI->connect($data_source, "homer", "doh", { AutoCommit => 1}) ||
  die "Sorry, the database is not currently accessible";
 
$teamsh = $dbh->prepare("SELECT * FROM soccerleague");
 
print <<HERE;
<html>
 <head>
 <title>Soccer league</title>
 </head>
 <body>
 <h1>Soccer results</h1>
 <br>
HERE
 
$teamsh->execute ||
 die "Couldn't access team data";
 
while(@row=$teamsh->fetchrow_array) {
 print "@row&ltbr>;
}
 
$dbh->disconnect;
 
print <<TAIL;
 
 </body>
</html>
TAIL
 
 

You can test this Perl script by simply running it.
When it works, copy your file to your public_html directory, renaming it so that the extension is .cgi (rather than .pl) e.g. Soccer.cgi. Give read-execute permission to others.
Aim your browser at http://localhost/~username/Soccer.cgi.

9. Modify your Soccer.cgi Perl script so that the data are presented in a HTML table.

When you have completed those exercises you can start on Assignment 2.