#! /usr/bin/perl

# samp_browse - Allow samp_db database browsing using Web browser

# PREAMBLE
use DBI;
use CGI;
use strict;

# default connection  parameters - all missing
my ($host_name, $user_name, $password) = (undef, undef, undef);
my ($db_name) = "samp_db";

# construct data source
my ($dsn) = "dbi:mysql:$db_name";
$dsn .= ":hostname=$host_name" if $host_name;
$dsn .= ";mysql_read_default_file=/usr/local/apache/conf/samp_db.cnf";

# connect to server
my (%attr) = ( RaiseError => 1 );
my ($dbh) = DBI->connect ($dsn, $user_name, $password, \%attr);
# PREAMBLE

# MAIN-BODY
my ($cgi) = new CGI;

# put out initial part of page
my ($title) = "$db_name Database Browser";
print $cgi->header ();
print $cgi->start_html (-title => $title);
print $cgi->h1 ($title);

# parameters to look for in URL
my ($tbl_name) = $cgi->param ("tbl_name");
my ($sort_column) = $cgi->param ("sort_column");

# if $tbl_name has no value, display a clickable list of tables.
# Otherwise, display contents of the given table.  $sort_column, if
# set, defines the sort column.

if (!$tbl_name)
{
	display_table_list ()
}
else
{
	display_table ($tbl_name, $sort_column);
}

print $cgi->end_html ();
# MAIN-BODY

$dbh->disconnect ();
#exit (0);

# DISPLAY_CELL
# display a value in a table cell; put non-breaking
# space in "empty" cells so borders show up

sub display_cell
{
my ($tag, $value, $encode) = @_;

	$value = $cgi->escapeHTML ($value) if $encode;
	$value = " " unless $value;
	print "<$tag>$value</$tag>\n";
}
# DISPLAY_CELL

# DISPLAY_TABLE_LIST
sub display_table_list
{
my ($ary_ref, $url);

	print "Select a table by clicking on its name:<BR><BR>\n";

	# retrieve reference to single-column array of table names
	$ary_ref =
		$dbh->selectcol_arrayref (qq{ SHOW TABLES FROM $db_name });

	# display table with a border
	print "<TABLE BORDER>\n";
	print "<TR>\n";
	display_cell ("TH", "Table Name", 1);
	print "</TR>\n";
	foreach my $tbl_name (@{$ary_ref})
	{
		$url = $cgi->script_name ();
		$url .= sprintf ("?tbl_name=%s", $cgi->escape ($tbl_name));
		print "<TR>\n";
		display_cell ("TD", $cgi->a ({-href => $url}, $tbl_name), 0);
		print "</TR>\n";
	}
	print "</TABLE>\n";
}
# DISPLAY_TABLE_LIST

# need the _X to make the listelim tag not a subset of the previous one
# DISPLAY_TABLE_X
sub display_table
{
my ($tbl_name, $sort_column) = @_;
my ($sth, $url);

	# if sort column not specified, use first column
	$sort_column = "1" unless $sort_column;

	# present a link that returns user to table list page
	print $cgi->a ({-href => $cgi->script_name ()}, "Show Table List");
	print "<BR><BR>\n";

	$sth = $dbh->prepare (qq{
				SELECT * FROM $tbl_name ORDER BY $sort_column
			});
	$sth->execute ();

	print "<B>Contents of $tbl_name table:</B><BR>\n";

	# display table with a border
	print "<TABLE BORDER>\n";
	# use column names for table headings; make each heading a link
	# that sorts output on the corresponding column
	print "<TR>\n";
	foreach my $col_name (@{$sth->{NAME}})
	{
		$url = $cgi->script_name ();
		$url .= sprintf ("?tbl_name=%s", $cgi->escape ($tbl_name));
		$url .= sprintf ("&sort_column=%s", $cgi->escape ($col_name));
		display_cell ("TH", $cgi->a ({-href => $url}, $col_name), 0);
	}
	print "</TR>\n";

	# display table rows
	while (my @ary = $sth->fetchrow_array ())
	{
		print "<TR>\n";
		foreach my $val (@ary)
		{
			display_cell ("TD", $val, 1);
		}
		print "</TR>\n";
	}

	$sth->finish ();
	print "</TABLE>\n";
}
# DISPLAY_TABLE_X