My Lab 5 Code
#!/usr/bin/perl -w
use DBI;
$db = DBI->connect("DBI:CSV:f_dir=.") || die "Cannot connect: $DBI::errstr\n";
$create = $db->prepare("CREATE TABLE weather (
state varchar(20) not null,
city varchar(32) not null,
precip real,
meanhigh real,
meanlow real)");
$create->execute() || die "Cannot create the person file!\n";
################################# Problem 2 ##############################
#!/usr/bin/perl -w
use DBI;
use Getopt::Std;
die "\n" if !getopts("a:d:u:s:l", \%opts);
die "Usage: $0 [-adusl]" if @ARGV != 0;
die "Only one option allowed at a time!\n" if keys(%opts) > 1;
die "Must use one option!\n" if keys(%opts) == 0;
$db = DBI->connect("DBI:CSV:f_dir=.") || die "Cannot connect: $DBI::errstr\n";
DoAdds($opts{'a'}, $db) if $opts{'a'};
DoDeletes($opts{'d'}, $db) if $opts{'d'};
DoUpdates($opts{'u'}, $db) if $opts{'u'};
DoStateListing($opts{'s'}, $db) if $opts{'s'};
DoDBListing($db) if $opts{'l'};
sub DoAdds
{
my ($filename, $db) = @_;
my ($state, $city, $precip, $meanhigh, $meanlow, $query, $rowref, $insert);
open(ADDS, $filename) || die "Cannot open $filename!\n";
while (<ADDS>)
{
chomp;
($state, $city, $precip, $meanhigh, $meanlow) = split /:/;
$query = $db->prepare("SELECT * FROM weather
WHERE state = '$state' AND city = '$city'");
$query->execute || die "Cannot query the weather database!\n";
$rowref = $query->fetchrow_arrayref;
if ($rowref)
{
print "Will not add duplicate record for $state, $city!\n";
next;
}
$insert =
$db->prepare("INSERT INTO weather (state,city,precip,meanhigh,meanlow)
VALUES ('$state', '$city', $precip, $meanhigh, $meanlow)");
$insert->execute || die "Cannot insert into weather database!\n";
}
close ADDS;
}
sub DoDeletes
{
my ($filename, $db) = @_;
my ($state, $city, $query, $rowref, $delete);
open(DELETES, $filename) || die "Cannot open DELETES file!\n";
while (<DELETES>)
{
chomp;
($state, $city) = split /:/;
$query = $db->prepare("SELECT * FROM weather
WHERE state = '$state' AND city = '$city'");
$query->execute || die "Cannot query the weather database!\n";
$rowref = $query->fetchrow_arrayref;
if (!$rowref)
{
print "Cannot delete record with $state,$city! Not found!\n";
next;
}
$delete = $db->prepare("DELETE FROM weather WHERE state = '$state' AND
city = '$city'");
$delete->execute || die "Cannot delete from weather database!\n";
}
close DELETES;
}
sub DoUpdates
{
my ($filename, $db) = @_;
my ($state, $city, $query, $rowref, $update);
open(DELETES, $filename) || die "Cannot open DELETES file!\n";
while (<DELETES>)
{
chomp;
($state, $city, $precip, $meanhigh, $meanlow) = split /:/;
$query = $db->prepare("SELECT * FROM weather
WHERE state = '$state' AND city = '$city'");
$query->execute || die "Cannot query the weather database!\n";
$rowref = $query->fetchrow_arrayref;
if (!$rowref)
{
print "Cannot update record with $state,$city! Not found!\n";
next;
}
$update = $db->prepare("UPDATE weather SET precip = $precip,
meanhigh = $meanhigh, meanlow = $meanlow
WHERE state = '$state' AND city = '$city'");
$update->execute || die "Cannot delete from weather database!\n";
}
close DELETES;
}
sub DoStateListing
{
my ($state, $db) = @_;
my ($count, $StateListing, $rowref, $city, $precip,
$meanhigh, $meanlow);
$state =~ s/^\s*//;
$state = join(" ", map {uc($_)} split /\s+/, $state);
$count = 0;
$StateListing = $db->prepare("SELECT * FROM weather
WHERE state = '$state'");
$StateListing->execute || die "Cannot query weather database!\n";
while ($rowref = $StateListing->fetchrow_arrayref)
{
$count++;
if ($count == 1)
{
print "City Precip. Mean High Mean Low\n",
"---- ------- --------- --------\n";
}
($state, $city, $precip, $meanhigh, $meanlow) = @$rowref;
printf "%-21s%-12s%-12.1f%-9.1f\n",
$city, $precip, $meanhigh, $meanlow;
}
print "No data for state: $state\n" if $count == 0;
}
sub DoDBListing
{
my ($db) = shift;
my ($DBListing, $rowrefs, $rowref, $state, $city, $precip,
$meanhigh, $meanlow);
$DBListing = $db->prepare("SELECT * FROM weather
ORDER BY state, city ASC");
$DBListing->execute || die "Cannot execute DB listing!\n";
$rowrefs = $DBListing->fetchall_arrayref;
print
"State City Precip. Mean High Mean Low\n",
"----- ---- ------- --------- --------\n";
foreach $rowref (@$rowrefs)
{
($state, $city, $precip, $meanhigh, $meanlow) = @$rowref;
printf "%-20s%-22s%-12.1f%-10.1f%5.1f\n",
$state, $city, $precip, $meanhigh, $meanlow;
}
}
########################## Proof of Correctness!! ##########################
voyager 3% cat asg52g.add
NORTH CAROLINA:RALEIGH:43.3:60.4:48.5
ARIZONA:PHOENIX:7.1:86.4:64.2
NORTH CAROLINA:ASHVILLE:35.4:57.3:34.2
NORTH CAROLINA:RALEIGH:44.3:55.1:44.2
ARIZONA:TUCSON:11.1:80.4:55.2
ARIZONA:FLAGSTAFF:14.2:66.4:30.5
PENNSYLVANIA:PHILADELPHIA:39.6:65.4:46.4
NEW YORK:BUFFALO:40.2:57.3:28.2
NEW YORK:NEW YORK:41.4:66.4:50.0
WYOMING:CASPER:15.2:55.2:22.4
voyager 4% asg52g.pl -a asg52g.add
Will not add duplicate record for NORTH CAROLINA, RALEIGH!
voyager 5% asg52g.pl -l
State City Precip. Mean High Mean Low
----- ---- ------- --------- --------
ARIZONA FLAGSTAFF 14.2 66.4 30.5
ARIZONA PHOENIX 7.4 88.5 65.2
ARIZONA TUCSON 11.1 80.4 55.2
NEW YORK BUFFALO 40.2 57.3 28.2
NEW YORK NEW YORK 41.4 66.4 50.0
NORTH CAROLINA ASHVILLE 35.4 57.3 34.2
NORTH CAROLINA RALEIGH 43.3 60.4 48.5
PENNSYLVANIA PHILADELPHIA 39.6 65.4 46.4
WYOMING CASPER 15.2 55.2 22.4
voyager 6% asg52g.pl -s " aRIZONa "
City Precip. Mean High Mean Low
---- ------- --------- --------
PHOENIX 7.1 86.4 64.2
TUCSON 11.1 80.4 55.2
FLAGSTAFF 14.2 66.4 30.5
voyager 7% asg52g.pl -s "UPPer REvolta"
No data for state: UPPER REVOLTA
voyager 8% cat asg52g.add2
WYOMING:CASPER:14.2:50.3:33.2
NEW MEXICO:CARLSBAD:9.7:70.2:44.3
voyager 9% asg52g.pl -a asg52g.add2
Will not add duplicate record for WYOMING, CASPER!
voyager 10% asg52g.pl -l
State City Precip. Mean High Mean Low
----- ---- ------- --------- --------
ARIZONA FLAGSTAFF 14.2 66.4 30.5
ARIZONA PHOENIX 7.4 88.5 65.2
ARIZONA TUCSON 11.1 80.4 55.2
NEW MEXICO CARLSBAD 9.7 70.2 44.3
NEW YORK BUFFALO 40.2 57.3 28.2
NEW YORK NEW YORK 41.4 66.4 50.0
NORTH CAROLINA ASHVILLE 35.4 57.3 34.2
NORTH CAROLINA RALEIGH 43.3 60.4 48.5
PENNSYLVANIA PHILADELPHIA 39.6 65.4 46.4
WYOMING CASPER 15.2 55.2 22.4
voyager 1% cat asg52g.update
ARIZONA:PHOENIX:7.4:88.5:65.2
NEW MEXICO:ALBUQUERQUE:8.8:67.5:44.3
voyager 2% asg52g.pl -u asg52g.update
Cannot update record with NEW MEXICO,ALBUQUERQUE! Not found!
voyager 13% cat asg52g.del
ARIZONA:FLAGSTAFF
NEW YORK:BUFFALO
voyager 14% asg52g.pl -d asg52g.del
# Note below that Flagstaff, AZ and Buffalo, NY are gone!
# Note that Phoenix, AZ has been updated!
voyager 15% asg52g.pl -l
State City Precip. Mean High Mean Low
----- ---- ------- --------- --------
ARIZONA PHOENIX 7.4 88.5 65.2
ARIZONA TUCSON 11.1 80.4 55.2
NEW MEXICO CARLSBAD 9.7 70.2 44.3
NEW YORK NEW YORK 41.4 66.4 50.0
NORTH CAROLINA ASHVILLE 35.4 57.3 34.2
NORTH CAROLINA RALEIGH 43.3 60.4 48.5
PENNSYLVANIA PHILADELPHIA 39.6 65.4 46.4
WYOMING CASPER 15.2 55.2 22.4