#!/usr/local/bin/perl
#######################################################################
# This CGI is will provide a "user friendly" interface to a table
# inside a database.
#
# The database name has to be provided.
# This script has to be renamed or linked to the same name as the table
# is called.
#
# Yves Dorfsman, Jan 10th 2001
#
#######################################################################
use DBI;
use CGI;
use File::Basename;
# Name of the Database this script is going to go against
$database = "hardware";
# We use the name of the script for the table name
$progname = basename($0);
$table = $progname;
# Connect to the DB
$dbc ||= DBI->connect("dbi:Pg:dbname=$database");
#-----------------------------------------------------------
$pg = new CGI;
$qname = $pg->param('qname');
print "Content-type: text/html\n\n";
print "\n\n";
# Two options:
# 1) qname is not defined, therefore this is a first call, we print an HTML
# FORM with ranks, column names, and text field for an SQL WHERE clause.
#
# 2) qname is a list of the columns name, therefore we have been called
# the the FORM (see 1), and we need to execute an SQL statement, and
# print its result.
if ($qname)
# We got called by the html FORM
{
for (split " ", $qname)
{
# For each column, we get the rank and text field associated with it.
$x = join "", $_, ".", "rank";
$y = join "", $_, ".", "where";
$rank{$_} = $pg->param("$x");
$where{$_} = $pg->param("$y");
}
# When was that table updated
$sqlprep = "SELECT \"when\" from updated where \"table\" = \'$table\'";
$sql1 = $dbc->prepare($sqlprep);
$sql1->execute();
$updated = $sql1->fetchrow_array;
# Now deal with what's inside the table
# Create the SQL ORDER BY statement, fields are ordered by rank.
# At the same time, we start to create a SQL SELECT field
for (sort { $rank{$a} <=> $rank{$b} } keys %rank)
{
# we don't need rank 0
next if ($rank{$_} == 0);
##print "$_ $rank{$_} \n";
$orderfield = join (" ", "$orderfield", ", ") if ($orderfield);
$orderfield = join " ", $orderfield, "\"$_\"";
$selectfield = join ("", "$selectfield", ", ") if ($selectfield);
$selectfield = join " ", $selectfield, "\"$_\"";
$header = join "", $header, "
", "$_";
}
$orderfield = join (" ", "ORDER BY", $orderfield) if ($orderfield);
##print "$orderfield \n";
# Now we create the SQL WHERE close (WHERE colname = "textfield")
while (($key, $value) = each %where)
{
# if a textfield is empty, we don't care
next if ($value eq "");
$wherefield = join (" ", $wherefield, "AND") if ($wherefield);
$wherefield = join " ", $wherefield, "WHERE upper(\"$key\") LIKE upper(\'%$value%\')";
if ($rank{$key} == 0)
{
# we will print any field that has a rank != 0, or a non empty
# textfield. But we have to be carrefull to print it twice (hence the if)
$selectfield = join ("", "$selectfield", ", ") if ($selectfield);
$selectfield = join " ", $selectfield, "\"$key\"";
$header = join "", $header, "
", "$key";
}
}
##print "$wherefield \n";
## print "$selectfield \n";
# If "allfields" is selected, then we use want to do a "select * form"
$allfields = $pg->param('allfields');
if ($allfields eq "on")
{
for (split " ", $qname)
{
if ($rank{$_} == 0 && $where{$_} eq "")
{
$selectfield = join ("", "$selectfield", ", ") if ($selectfield);
$selectfield = join (" ", $selectfield, "\"$_\"");
$header = join ("", $header, "
", $_);
}
}
}
# Now we prepare and execute the SQL query
$sqlprep = join (" ", "SELECT", $selectfield, "FROM $table", $wherefield, $orderfield, ";");
## print "$sqlprep \n";
$sql1 = $dbc->prepare($sqlprep);
$sql1->execute();
# We create an HTML table, with the result of the sql statement in it
print "
Last updated: $updated
\n";
print "
\n";
print "
\n";
print "$header";
while (@row = $sql1->fetchrow_array)
{
print "
";
print "\n";
}
else
{
# First time, we create the HTML "FORM"
#
#----------------------------------------------------------
# Find our the name of the columns in the table
# Unfortunately this part is very Postgres specific....
# but then, the same trick can be used with most DBM, so it should be easy
# to change this part to accomodate another DBM.
# (and yes it is very ugly, but I couldn't come up with anything better)
$run = "echo 'select * from $table where 1=2;'";
$run = join "|", $run, "LD_LIBRARY_PATH=/usr/local/pgsql/lib";
$run = join " ", $run, "/usr/local/pgsql/bin/psql $database 2>&1";
$run = join "|", $run, "head -1 2>&1";
$result = `$run`;
@raw_col = split /\|/, $result;
$nbr_field = 0;
for (@raw_col)
{
s/^\s+//;
s/\s+$//;
push @columns, $_;
$nbr_field++;
}
# Now print a form with a choice of rank and search pattern for each field.
print "Select \"Show all fields\" if you want to see the whole table. ";
print "When you select a rank, the columns will appear in that order, and be ordered using the ranks. ";
print "Any column for which you have selected a rank or some text will appear. ";
print "In the text field, use the SQL \'%\' and \'_\' as opposed to the regex \'*\' and \'?\' for your wildcards. ";
print "