Hi Shaunn.
Shaunn Johnson wrote:
> Howdy:
>
> I have a script where I would like to connect to
> my database (PostgreSQL) and do a dump
> depending on what day it is. I'm having
> problems trying to figure out why I either can't
> open a file to write to it inside the loop, or, if
> I create a file outside of the for loop, nothing
> is written in the file.
>
> What am I doing wrong?
>
> When I run the script as-is, I do not get an error,
> but I don't get the results that I expect, either.
>
> If I change the script so that I open the file before
> the for loop, I get and error (if the $file does not
> exist previously) saying:
>
> [snip error]
>
> sh: /usr/local/home/shaunn/tmp/backup_list.txt: No such file or directory
>
> [/snip error]
>
> Which is true - I do *not* have a file just yet ... but why should it
> care at this point?
>
> The sql inside the script works, as I've test that by hand. Even
> so, the script should give *some* kind of error. It does not.
>
> Perhaps another pair of eyes can tell me what I'm doing
> wrong ...
>
> [snip script]
>
> #!/usr/bin/perl
> use strict;
> use diagnostics;
> use DBI;
>
> # creating an alternative backup strategy for
> # the database - gonna be ugly ...
>
> # create a few variables
> my $addr='[EMAIL PROTECTED]';
> #my $outfile=`date +%d%b%Y`;
> my $outfile=`date | cut -f 1 -d ' '`;
> my $datetype=`date`;
> my $file='/usr/local/home/joe/tmp/backup_list.txt';
> my $matchday=`date +%a`;
> #chop $outfile;
I don't much like all this shelling out to fetch the date
when Perl is happy to do it itself.
> # define the connection to the database and user or give up
>
> my $dbh=DBI->connect('dbi:Pg:dbname=test_db', 'postgres')
> or die "Can not connect: $!";
>
> # create an sql to get the initial list of tables
>
> # backup tables that go from A - C
> my $onesql = qq| select
> relname
> from pg_class
> where relname ~ '^[A-Ca-c]'
> and relkind = 'r'
> and relname not like 'pg_%'
> order by 1;
> > ;
>
[remaining identical (apart from the regex) SQL
statements snipped]
This one is different. Is there a reason?
> # backup tables that go from Q - R
> my $fivesql = qq| select
> relname
> from pg_class
> where relname ~ '^[Q-Rq-r]'
> and relkind = 'r'
> and relname not like 'pg_%'
> and relname not like 'ref_%'
> order by 1;
> > ;
>
> # test the database handler and prep the sql statements for
> # execution
>
> # this opens a file, but nothing is written to it
> #
>
> #open (FILE, ">$file") || die "Can nae open $file: $!\n";
>
> if ($matchday eq 'Sun') { #do for SUNDAY
> open (FILE, ">$file") || die "Can nae open $file: $!\n";
> my $sth=$dbh->prepare($onesql) or die "Error =", DBI::errstr;
>
> unless ($sth->execute) { #check execution of sql
> print"\n\tExecute failed for stmt:\n\t$onesql\nError = ",
> DBI::errstr;
> $sth->finish;
> $dbh->disconnect;
> die "\n\t\tClean up finished\n";
> }
>
> # the work - this is the loop
>
> #open (FILE, ">$file") || die "Can nae open $file: $!\n";
> print FILE "Today is: $datetype\n\n";
> print FILE "These are the tables that have been backed up:\n\n";
>
> while ( my($table)=$sth->fetchrow ) {
> print "Backing up $table...\n";
> #print `echo 'postgres
> #
> # ' | /usr/bin/pg_dump -u -t $table test_db | /bin/gzip >
> /s/hmp/backup/$table.$outfile.gz`;
> print FILE "$table", "\n\n";
> print FILE "Please review\n\n";
> }
>
> #print FILE "Please review\n\n";
>
> close FILE;
> } #end SUNDAY
[remaining identical day blocks snipped]
>
> #close FILE;
>
>
> # maybe i should mail something to me anyway
>
> my $sendmailtable = "/usr/sbin/sendmail $addr < $file";
> print `$sendmailtable`;
>
>
> $dbh->disconnect;
I started looking at your problem but soon realised
that you and I were both handicapped by lack of clarity
in your coding. At first I was going to post a suggestion
that you form each day's work into a subroutine and
call it through a hash table, but when I checked what
the difference between the days was it boiled down to
a single regex in the SELECT statement. It may be that
the days turn out to be more distinct than they are here,
but I still think 350 lines of code is overkill!
Also it's important to use some whitespace. Indent your
code blocks, so that they stand out visually, and put
spaces around most operators.
Here's what I cam up with. It should work and I've
no idea what was wrong with your first try, but software
should /look/ like it works!
HTH,
Rob
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
# creating an alternative backup strategy for
# the database - gonna be ugly ...
# create a few variables
my $addr = '[EMAIL PROTECTED]';
#my $outfile = `date +%d%b%Y`;
my $outfile = `date | cut -f 1 -d ' '`;
my $datetype = `date`;
my $file = '/usr/local/home/joe/tmp/backup_list.txt';
my $matchday = `date +%a`;
my %backup;
@backup{ qw / Sun Mon Tue Wed Thu Fri Sat / } = qw/ a-c d e-o p q-r s t-z /;
# Define the connection to the database and user or give up
#
my $dbh=DBI->connect('dbi:Pg:dbname=test_db', 'postgres')
or die "Can not connect: $!";
# Create an SQL to get the initial list of tables
#
my $sth=$dbh->prepare(<<END) or die "Error =", DBI::errstr;
select relname
from pg_class
where relname ~ ?
and relkind = 'r'
and relname not like 'pg_%'
order by 1;
END
# Build the regex for today
#
my $range = $backup{$matchday};
$range = sprintf "^[%s%s]", uc $range, lc $range;
# Fetch the data and die if we failed
#
my $rows = $sth->execute($range);
unless ($rows) {
print"\n\tExecute failed\nError = ", DBI::errstr;
$sth->finish;
$dbh->disconnect;
die "\n\t\tClean up finished\n";
}
# Open the log file and print the header
#
open FILE, ">$file" or die "Can nae open $file: $!\n";
print FILE "Today is: $datetype\n\n";
print FILE "These are the tables that have been backed up:\n\n";
# The work - this is the loop
#
while ( my $table = $sth->fetchrow ) {
print "Backing up $table...\n";
#print `echo 'postgres
#
# ' | /usr/bin/pg_dump -u -t $table test_db | /bin/gzip >
/s/hmp/backup/$table.$outfile.gz`;
print FILE "$table", "\n\n";
print FILE "Please review\n\n";
}
# All done
#
close FILE;
$dbh->disconnect;
# Maybe i should mail something to me anyway
#
my $sendmailtable = "/usr/sbin/sendmail $addr < $file";
print `$sendmailtable`;
__END__
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
