#!/usr/bin/perl # Update links page # Uses postgres table MyLinks to update links page # Gerry Patterson, May 2002 # Downloaded from http://www.pgts.com.au/download/scripts # db connect information goes here -- keep this secure! $connect = "MyConnect"; $SID = "MyDB"; # NB: This string contains a TAB -- It would not recognise '\t' (undocumented feature?) $PSQL = "/usr/local/pgsql/bin/psql -F ' ' -t -A -v ON_ERROR_STOP=1 -U $connect -d $SID"; # temp files -- clean them up with cron'ed job chomp( $sqltmp = `mktemp /tmp/XXXXXX`); die "Cannot create temp file: $sqltmp" unless (-f $sqltmp); $sqlout = "$sqltmp.out"; $sqlerr = "$sqltmp.err"; # ------------------------------------------------------------------------ # run a query using the $PSQL string sub run_sql{ my $status; unlink( $sqlout,$sqlerr); open ( SQLTMP,">$sqltmp") || die "Error Opening temp file $sqltmp"; # open an output channel to $sqlout print SQLTMP '\o ' . "$sqlout\n"; print SQLTMP "$_[0]\n"; close SQLTMP || die "Closing temp file $sqltmp"; $status = system( "$PSQL -f $sqltmp 2>$sqlerr"); if ( $status){ open ( TMP,"$sqlerr") || die "Error opening $sqlerr"; @t = ; die(@t); } } # ------------------------------------------------------------------------ # Main Procedure starts here ... die "usage: $0 html_file\n" unless (@ARGV == 1); run_sql "select category, link, name, description from MyLinks where valid order by category,name;"; open(HTML,$ARGV[0]) || die "Cannot open HTML: $ARGV[0]\n"; $links_flag = 0; while (){ $links_flag++ if (//); if (//){ $links_flag = 0; next; } if ( $links_flag == 0 ){ print $_; next; } next unless ($links_flag == 1); # replace text between .. print ' '; open ( SQLTMP,"$sqlout") || die "Error Opening input file $sqlout"; while ( ){ chomp; my @w = split ( /\t/,$_); unless ( $w[0] eq $category){ $category = "$w[0]"; print " \n"; print " \n"; } print ' '; } print '

Category	Link	Description

$category" . ":

	' . $w[2] . '	' . $w[3] . '

'; $links_flag++; }