#!/usr/bin/perl
package FormatSchema;
use Exporter;
@ISA = ('Exporter');
@EXPORT_OK = qw(displayPage);

#####################################################################
#     Program : FormatSchema.pm 
#     Author  : Gail Binkley
#     Date    : 2001-01-31 / 2003-03-19
# Description : This module displays table specifications for any database
#               and generates a file for downloading
#
# This module takes database specific arguments: 
# $database, $user, $owner, $tmpdir, $tmpurl, $helpurl
#
#####################################################################
use strict; 
use DBI;
use CGI qw/:standard :html3/;

use File::Basename; # for the dirname function
 
use lib dirname($ENV{DOCUMENT_ROOT})."/lib/common";
use Schema;
use lib dirname($ENV{DOCUMENT_ROOT})."/lib/dictyBase";
use FormatdictyBase qw(PrintSmallLogoPageTop Divider75);

#######################################################################
sub displayPage {
#######################################################################
# connect to database, initialize schema object, print page, disconnect

        my ($database, $user, $owner, $tmpdir, $tmpurl, $helpurl) = @_;

        # Get table parameter passed via url
	my $query = new CGI;
        my $table = uc($query->param('table'));

        # Print top of web page
	my $db = uc($database);
	if ($owner eq 'SGR') {
	    $db = 'SGR';
	}

	my $title = "$db Schema Specifications";

	print $query->header();
	&PrintSmallLogoPageTop($database, $title, $helpurl);
	print &Divider75;

	# Initialize schema object
	my $schemaObj = Schema->new(database=>$database,
				    user=>$user,
				    owner=>$owner,
				    table=>$table);
	
	if ($table eq "ALL") {

	    &printAllTableInfo(\$schemaObj, $database, $owner, $tmpdir, $tmpurl);
	    
	    &printAllColumnInfo(\$schemaObj, $database, $owner, $tmpdir, $tmpurl);
	    
	    &printAllPKInfo(\$schemaObj, $database, $owner, $tmpdir, $tmpurl);
	    
	    &printAllFKInfo(\$schemaObj, $database, $owner, $tmpdir, $tmpurl);
	    
	    &printAllIndexInfo(\$schemaObj, $database, $owner, $tmpdir, $tmpurl);
	    
	    &printAllConstraintInfo(\$schemaObj, $database, $owner, $tmpdir, $tmpurl);
	    
	    &printAllSequenceInfo(\$schemaObj, $database, $owner, $tmpdir, $tmpurl);


	} else { # Display only 1 table at a time

	    # Open table download file for writing
	    my $filename = "${db}_${table}.xls";
	    
	    open (OUT, ">$tmpdir$filename") || 
		die "TableSpecifications.pm: Can't open '$filename' for writing: $!\n";
    
	    &printTableInfo(\$schemaObj, $database, $tmpdir, $tmpurl, $filename);
	    
	    print br;
	    
	    &printColumnInfo(\$schemaObj, $query, $database, $tmpdir, $filename);
	    
	    &printPKInfo(\$schemaObj, $query, $database, $tmpdir, $filename);
	    
	    &printFKInfo(\$schemaObj, $query, $database, $tmpdir, $filename);
	    
	    &printIndexInfo(\$schemaObj, $query, $database, $tmpdir, $filename);
	    
	    &printConstraintInfo(\$schemaObj, $query, $database, $tmpdir, $filename);
	    
	    &printSequenceInfo(\$schemaObj, $query, $database, $tmpdir, $filename);

	    close (OUT);

	}

        # Print bottom of web page
	print "<p>";
	print end_html;

}

#######################################################################
sub printTableInfo {
#######################################################################
# Print table information

    my ($schemaObjRef, $database, $tmpdir, $tmpurl, $file) = @_;

    my $tableName = $$schemaObjRef->table;
    my $tableComment = $$schemaObjRef->tableComment;
    my $numRows = $$schemaObjRef->tableNumRows;

    # Print HTML page
    print h3("Table : <font color=red>$tableName</font> <font size=-1>[<a href=$tmpurl$file>download</a>]</font>");
    print "<blockquote>$tableComment</blockquote>";

    print "Number of rows : $numRows";
    print br;

    # Print download file
    my @file_rows;
    push (@file_rows, "\nTable : $tableName\n");
    push (@file_rows, "$tableComment\n");
    push (@file_rows, "Number of rows : $numRows\n");

    &PrintFile($database, $tmpdir, $file, @file_rows);
}


#######################################################################
sub printColumnInfo {
#######################################################################
# Print column information

    my ($schemaObjRef, $query, $database, $tmpdir, $file) = @_;

    my $columnInfo = $$schemaObjRef->columnInfo;

    my (@rows, @columnHeaders, $headers, @file_rows, $file_headers);

    my $default_flag = 0;

    # iterate over each row to determine if there are any default values
    foreach my $column_row (@{$columnInfo}){

	my ($column, $type, $length, $precision, $scale, $nullable, $default, $comment) = @{$column_row};

	# bail as soon as there is one default value
	if (defined $default) {
	    $default_flag = 1;
	    last;
	}
    }

    # customize table headings
    if  ($default_flag == 0) {
	@columnHeaders = ("Column Name", "Data Type", "Length", "Null?", "Description");
	$file_headers = "Column Name\tData Type\tLength\tNull?\tDescription";
    } else {    
	@columnHeaders = ("Column Name", "Data Type", "Length", "Null?", "Default Value", "Description");
	$file_headers = "Column Name\tData Type\tLength\tNull?\tDefault Value\tDescription";
    }
    
    foreach my $header (@columnHeaders) {
       $headers .= th($header);
    }

    # Push Column headers first onto stack for printing file
    push (@file_rows, "\n$file_headers\n");

    # iterate over each row manipulating certain data
    # create array @rows for printing the table
    foreach my $column_row (@{$columnInfo}){
	my ($column, $type, $length, $precision, $scale, $nullable, $default, $comment) = @{$column_row};

	if ($nullable eq "N") {
	    $nullable = "NO" ;
	} else {
	    $nullable = "YES";
	}

	if ($type eq "NUMBER") {
            if ($scale == 0) {
		$length = $precision;
	    } else {
		$length = "$precision,$scale";
	    }
	}

	if  ($default_flag == 0) {
	    push (@rows, td($column).td($type).td($length).td($nullable).td($comment));
	    push (@file_rows, "$column\t$type\t$length\t$nullable\t$comment\n");
	} else {
	    push (@rows, td($column).td($type).td($length).td($nullable).td($default).td($comment));
	    push (@file_rows, "$column\t$type\t$length\t$nullable\t$default\t$comment\n");
	    
	}

        # if a cell is empty put in a non-breaking space
	$rows[$#rows]=~s/<TD><\/TD>/<TD>&nbsp;<\/TD>/gi; 	
    }

    # Print HTML page and download file only if data available
    if (@{$columnInfo}) {
	&PrintTable ($query, $headers, @rows);
	&PrintFile($database, $tmpdir, $file, @file_rows);
    }
}


###########################################################################
sub printPKInfo {
###########################################################################
    my ($schemaObjRef, $query, $database, $tmpdir, $file) = @_;

    my $PKInfo = $$schemaObjRef->PKInfo;

    my (@rows, @columnHeaders, $headers, $pk, @pks);
    my ($file_headers, @file_rows);

    # customize table headings
    @columnHeaders = ("Primary Key Name", "Primary Key Column(s)");
    $file_headers = "Primary Key Name\tPrimary Key Column(s)";

    foreach my $header (@columnHeaders) {
	$headers .= th($header);
    }
    
    # Push Column headers first onto stack for printing file
    push (@file_rows, "\n$file_headers\n");
    
    # iterate over each row
    # if composite PK, combine all columns into one cell
    foreach my $pk_row (@{$PKInfo}){
	
	my ($constraint, $column, $position) = @{$pk_row};
	
	$pk = $constraint;
	push (@pks, $column);
	
    }
    
    # create array @rows for printing the table
    my $pk_columns = join(", ",@pks); 
    push (@rows, td($pk).td($pk_columns));
    # if a cell is empty put in a non-breaking space
    $rows[$#rows]=~s/<TD><\/TD>/<TD>&nbsp;<\/TD>/gi; 	
    
    push (@file_rows, "$pk\t$pk_columns\n");

    # Print HTML page and download file only if data available
    if (@{$PKInfo}) {
	&PrintTable ($query, $headers, @rows);
	&PrintFile($database, $tmpdir, $file, @file_rows);
    }
}


###########################################################################
sub printFKInfo {
###########################################################################
    my ($schemaObjRef, $query, $database, $tmpdir, $file) = @_;

    my $FKInfo = $$schemaObjRef->FKInfo;

    my (@rows, @columnHeaders, $headers, $file_headers, @file_rows);
    my (%constraintName, $row_constraint, $row_column, $row_ref_table, $row_ref_column);

    # customize table headings
    @columnHeaders = ("Foreign Key Name", "Foreign Key Column(s)", "References");
    $file_headers = "Foreign Key Name\tForeign Key Column(s)\tReferences";

    foreach my $header (@columnHeaders) {
       $headers .= th($header);
    }

    # Push Column headers first onto stack for printing file
    push (@file_rows, "\n$file_headers\n");

    # iterate over each row
    # if composite FK, combine in one cell
    foreach my $fk_row (@{$FKInfo}){

	my ($constraint, $column, $ref_table, $ref_column, $position) = @{$fk_row};

	if (!exists($constraintName{$constraint})) {
	 
	    if ($row_constraint) {
		# create array @rows for printing the table
		push (@rows, td($row_constraint).td($row_column).td($row_ref_table." (".$row_ref_column.")"));    
		# if a cell is empty put in a non-breaking space
		$rows[$#rows]=~s/<TD><\/TD>/<TD>&nbsp;<\/TD>/gi; 	

		push (@file_rows, "$row_constraint\t$row_column\t$row_ref_table($row_ref_column)\n");
	    }

	    $row_constraint = $constraint;
	    $row_column = $column;
	    $row_ref_table = $ref_table;
	    $row_ref_column = $ref_column;

	} else {

	    $row_column .= ", $column";
	    $row_ref_column .= ", $ref_column)";

	}

	# Add constraint name to lookup hash
	$constraintName{$constraint} = 1;

    }

    # Print last row of the array @rows for printing the table
    push (@rows, td($row_constraint).td($row_column).td($row_ref_table." (".$row_ref_column.")"));    
    # if a cell is empty put in a non-breaking space
    $rows[$#rows]=~s/<TD><\/TD>/<TD>&nbsp;<\/TD>/gi; 	

    push (@file_rows, "$row_constraint\t$row_column\t$row_ref_table($row_ref_column)\n");
	
    # Print HTML page and download file only if data available
    if (@{$FKInfo}) {
	&PrintTable ($query, $headers, @rows);
	&PrintFile($database, $tmpdir, $file, @file_rows);
    }
}

###########################################################################
sub printIndexInfo {
###########################################################################
    my ($schemaObjRef, $query, $database, $tmpdir, $file) = @_;

    my $indexInfo = $$schemaObjRef->indexInfo;

    my (@rows, @columnHeaders, $headers, $file_headers, @file_rows);
    my (%indexRow, $row_index, $row_unique, $row_column);

    # customize table headings
    @columnHeaders = ("Index Name", "Unique?", "Indexed Column(s)");
    $file_headers = "Index Name\tUnique?\tIndexes Column(s)";

    foreach my $header (@columnHeaders) {
       $headers .= th($header);
    }

    # Push Column headers first onto stack for printing file
    push (@file_rows, "\n$file_headers\n");

    # iterate over each row
    # if composite index, combine in one cell
    foreach my $index_row (@{$indexInfo}){

	my ($index, $unique, $column) = @{$index_row};

	if (!exists($indexRow{$index})) {

	    if ($row_index) {
		# create array @rows for printing the table
		push (@rows, td($row_index).td($row_unique).td($row_column));
		# if a cell is empty put in a non-breaking space
		$rows[$#rows]=~s/<TD><\/TD>/<TD>&nbsp;<\/TD>/gi;

		push (@file_rows, "$row_index\t$row_unique\t$row_column\n");
	    }

	    if ($unique eq "NONUNIQUE") {
		$unique = "NO" ;
	    } else {
		$unique = "YES";
	    }

	    $row_column = $column;
	    $row_index = $index;
	    $row_unique = $unique;

	} else {

	    # add composite columns to array
	    $row_column .= ", $column";
	    
	}

	# Add index name to lookup hash
	$indexRow{$index} = 1;
    }

    # Print last row of the array @rows for printing the table
    push (@rows, td($row_index).td($row_unique).td($row_column));
    # if a cell is empty put in a non-breaking space
    $rows[$#rows]=~s/<TD><\/TD>/<TD>&nbsp;<\/TD>/gi;

    push (@file_rows, "$row_index\t$row_unique\t$row_column\n");
    
    # Print HTML page and download file only if data available
    if (@{$indexInfo}) {
	&PrintTable ($query, $headers, @rows);
	&PrintFile($database, $tmpdir, $file, @file_rows);
    }

}

###########################################################################
sub printConstraintInfo {
###########################################################################
    my ($schemaObjRef, $query, $database, $tmpdir, $file) = @_;

    my $constraintInfo = $$schemaObjRef->constraintInfo;

    my (@rows, @columnHeaders, $headers, $file_headers, @file_rows);

    # customize table headings
    @columnHeaders = ("Constraint Name", "Constraint Column", "Type", "Description");
    $file_headers = "Constraint Name\tConstraint Column\tType\tDescription";

    foreach my $header (@columnHeaders) {
       $headers .= th($header);
    }

    # Push Column headers first onto stack for printing file
    push (@file_rows, "\n$file_headers\n");

    # iterate over each row
    foreach my $constraint_row (@{$constraintInfo}){

	my ($constraint, $column, $type, $condition) = @{$constraint_row};
	
	if ($condition =~ /NULL/) {
	    $type = "NULL";
	} else {
	    $type = "CHECK";
	}

	# create array @rows for printing the table
	push (@rows, td($constraint).td($column).td($type).td($condition));
	# if a cell is empty put in a non-breaking space
	$rows[$#rows]=~s/<TD><\/TD>/<TD>&nbsp;<\/TD>/gi; 	

	push (@file_rows, "$constraint\t$column\t$type\t$condition\n");	
    }
    
    # Print HTML page and download file only if data available
    if (@{$constraintInfo}) {
	&PrintTable ($query, $headers, @rows);
	&PrintFile($database, $tmpdir, $file, @file_rows);
    }
}


###########################################################################
sub printSequenceInfo {
###########################################################################
    my ($schemaObjRef, $query, $database, $tmpdir, $file) = @_;

    my $sequenceInfo = $$schemaObjRef->sequenceInfo;

    my (@rows, @columnHeaders, $headers, $file_headers, @file_rows);

    # customize table headings
    @columnHeaders = ("Sequence Name", "Column Name");
    $file_headers = "Sequence Name\tColumn Name";

    foreach my $header (@columnHeaders) {
       $headers .= th($header);
    }
    
    # Push Column headers first onto stack for printing file
    push (@file_rows, "\n$file_headers\n");

    # iterate over each row
    foreach my $sequence_row (@{$sequenceInfo}){

	my ($column, $sequence) = @{$sequence_row};

	# create array @rows for printing the table
	push (@rows, td($sequence).td($column));
	# if a cell is empty put in a non-breaking space
	$rows[$#rows]=~s/<TD><\/TD>/<TD>&nbsp;<\/TD>/gi; 	

	push (@file_rows, "$sequence\t$column\n");	
    }

    # Print HTML page and download file only if data avilable
    if (@{$sequenceInfo}) {
	&PrintTable ($query, $headers, @rows);
	&PrintFile($database, $tmpdir, $file, @file_rows);
    }
}

#######################################################################
sub printAllTableInfo {
#######################################################################
# Print all table information to a file

    my ($schemaObjRef, $database, $owner, $tmpdir, $tmpurl) = @_;

    my $allTableInfo  = $$schemaObjRef->allTableInfo;

    my $db = uc($database);
    if ($db eq "MAD") {
	$db = "SMD";
    }
    if ($owner eq 'SGR') {
	$db = 'SGR';
    }

    # Open table download file for writing
    my $filename = "${db}_AllTableInfo.xls";
    open (OUT, ">$tmpdir$filename") || 
	die "TableSpecifications.pm: Can't open '$filename' for writing: $!\n";
    
    # Print HTML page
    print "Schema information for all tables are available in tab-delimited files.  The first line of each file are the column headings.";
    print br;
    print br;
    print "<a href=$tmpurl$filename>Download Oracle Table Info</a> (table name, row count, description)";

    # Print datafile headers
    print OUT "Table Name\tRow Count\tTable Description\n";

    foreach my $table_row (@{$allTableInfo}) {

	my ($table_name, $count, $description) = @{$table_row};

	# Print datafile body
	print OUT "$table_name\t$count\t$description\n";

    }
    close (OUT);
}

#######################################################################
sub printAllColumnInfo {
#######################################################################
# Print column information

    my ($schemaObjRef, $database, $owner, $tmpdir, $tmpurl) = @_;

    my $allColumnInfo = $$schemaObjRef->allColumnInfo;

    my $db = uc($database);
    if ($db eq "MAD") {
	$db = "SMD";
    }
    if ($owner eq 'SGR') {
	$db = 'SGR';
    }

    # Open table download file for writing
    my $filename = "${db}_AllColumnInfo.xls";
    open (OUT, ">$tmpdir$filename") || 
	die "TableSpecifications.pm: Can't open '$filename' for writing: $!\n";

    print br;
    print "<a href=$tmpurl$filename>Download Oracle Column Info</a> (table name, column name, data type, length, null, default value, description)";

    # Print datafile headers
    print OUT "Table Name\tColumn Name\tData Type\tLength\tNull?\tDefault Value\tDescription\n";

    foreach my $column_row (@{$allColumnInfo}) {

	my ($table_name, $column, $type, $length, $precision, $scale, $nullable, $default, $comment) = @{$column_row};

	if ($nullable eq "N") {
	    $nullable = "NO" ;
	} else {
	    $nullable = "YES";
	}

	if ($type eq "NUMBER") {
            if ($scale == 0) {
		$length = $precision;
	    } else {
		$length = "$precision,$scale";
	    }
	}
	# Print datafile body
	print OUT "$table_name\t$column\t$type\t$length\t$nullable\t$default\t$comment\n";

    }
    close (OUT);
}

###########################################################################
sub printAllPKInfo {
###########################################################################
    my ($schemaObjRef, $database, $owner, $tmpdir, $tmpurl) = @_;

    my $allPKInfo = $$schemaObjRef->allPKInfo;

    my $db = uc($database);
    if ($db eq "MAD") {
	$db = "SMD";
    }
    if ($owner eq 'SGR') {
	$db = 'SGR';
    }

    # Open table download file for writing
    my $filename = "${db}_AllPKInfo.xls";
    open (OUT, ">$tmpdir$filename") || 
	die "TableSpecifications.pm: Can't open '$filename' for writing: $!\n";

    my (%pkRow, @file_rows, $row_table, $row_constraint, $row_column);
    foreach my $pk_row (@{$allPKInfo}){
	
	my ($table, $constraint, $column, $position) = @{$pk_row};
	
	my $tabcon = $table.$constraint;

	if (!exists($pkRow{$tabcon})) {
	    
	    if ($row_table) {
		push (@file_rows, "$row_table\t$row_constraint\t$row_column\n");
	    }
	    
	    $row_column = $column;
	    $row_table = $table;
	    $row_constraint = $constraint;

	} else {
	    
	    # add composite columns to array
	    $row_column .= ", $column";
	    
	}
	
	# Add index name to lookup hash
	$pkRow{$tabcon} = 1;

    }

    # Print last row of the array @file_rows for printing the table    
    push (@file_rows, "$row_table\t$row_constraint\t$row_column\n");    

    # Only print to web page and datafile if data exists
    if (@{$allPKInfo}) {

	print br;
	print "<a href=$tmpurl$filename>Download Oracle Primary Key Info</a> (table_name, primary key name, primary key columns)";

	# Print datafile headers
	print OUT "Table Name\tPrimary Key Name\tPrimary Key Columns(s)\n";

	# Print datafile body
	foreach my $file_row (@file_rows) {
	    print OUT "$file_row";
	}
	close (OUT);
    }
}

###########################################################################
sub printAllFKInfo {
###########################################################################
    my ($schemaObjRef, $database, $owner, $tmpdir, $tmpurl) = @_;

    my $allFKInfo = $$schemaObjRef->allFKInfo;

    my $db = uc($database);
    if ($db eq "MAD") {
	$db = "SMD";
    }
    if ($owner eq 'SGR') {
	$db = 'SGR';
    }

    # Open table download file for writing
    my $filename = "${db}_AllFKInfo.xls";
    open (OUT, ">$tmpdir$filename") || 
	die "TableSpecifications.pm: Can't open '$filename' for writing: $!\n";

    my (%fkRow, @file_rows, $row_table, $row_constraint, $row_column);
    my ($row_ref_table, $row_ref_column);

    foreach my $fk_row (@{$allFKInfo}){

	my ($table_name, $constraint, $column, $ref_table, $ref_column, $position) = @{$fk_row};

	my $tabcon = $table_name.$constraint;

	if (!exists($fkRow{$tabcon})) {
	 
	    if ($row_constraint) {
		push (@file_rows, "$row_table\t$row_constraint\t$row_column\t$row_ref_table($row_ref_column)\n");
	    }

	    $row_table = $table_name;
	    $row_constraint = $constraint;
	    $row_column = $column;
	    $row_ref_table = $ref_table;
	    $row_ref_column = $ref_column;

	} else {

	    $row_column .= ", $column";
	    $row_ref_column .= ", $ref_column)";

	}

	# Add constraint name to lookup hash
	$fkRow{$tabcon} = 1;

    }

    # Print last row of the array @file_rows for printing the table
    push (@file_rows, "$row_table\t$row_constraint\t$row_column\t$row_ref_table($row_ref_column)\n");
	
    # Only print to web page and datafile if data exists
    if (@{$allFKInfo}) {

	print br;
	print "<a href=$tmpurl$filename>Download Oracle Foreign Key Info</a> (table_name, foreign key name, foreign key columns, references)";

	# Print datafile headers
	print OUT "Table Name\tForeign Key Name\tForeign Key Columns(s)\tReferences\n";

	# Print datafile body
	foreach my $file_row (@file_rows) {
	    print OUT "$file_row";
	}
	close (OUT);
    }
}

###########################################################################
sub printAllIndexInfo {
###########################################################################
    my ($schemaObjRef, $database, $owner, $tmpdir, $tmpurl) = @_;

    my $allIndexInfo = $$schemaObjRef->allIndexInfo;

    my $db = uc($database);
    if ($db eq "MAD") {
	$db = "SMD";
    }
    if ($owner eq 'SGR') {
	$db = 'SGR';
    }

    # Open table download file for writing
    my $filename = "${db}_AllIndexInfo.xls";
    open (OUT, ">$tmpdir$filename") || 
	die "TableSpecifications.pm: Can't open '$filename' for writing: $!\n";

    my (%indexRow, $row_table, $row_index, $row_unique, $row_column, @file_rows);

    foreach my $index_row (@{$allIndexInfo}){

	my ($table_name, $index, $unique, $column) = @{$index_row};

	my $tabind = $table_name.$index;

	if (!exists($indexRow{$tabind})) {

	    if ($row_index) {
		push (@file_rows, "$row_table\t$row_index\t$row_unique\t$row_column\n");
	    }

	    if ($unique eq "NONUNIQUE") {
		$unique = "NO" ;
	    } else {
		$unique = "YES";
	    }

	    $row_table = $table_name;
	    $row_column = $column;
	    $row_index = $index;
	    $row_unique = $unique;

	} else {

	    # add composite columns to array
	    $row_column .= ", $column";
	    
	}

	# Add index name to lookup hash
	$indexRow{$tabind} = 1;
    }

    # Print last row of the array @rows for printing the table
    push (@file_rows, "$row_table\t$row_index\t$row_unique\t$row_column\n");

    # Only print to web page and datafile if data exists
    if (@{$allIndexInfo}) {

	print br;
	print "<a href=$tmpurl$filename>Download Oracle Index Info</a> (table_name, index name, unique, indexes columns)";
	
	# Print datafile headers
	print OUT "Table Name\tIndex Name\tUnique?\tIndexes Column(s)\n";
	
	# Print datafile body
	foreach my $file_row (@file_rows) {
	    print OUT "$file_row";
	}   
	close (OUT);
    }
}

###########################################################################
sub printAllConstraintInfo {
###########################################################################
    my ($schemaObjRef, $database, $owner, $tmpdir, $tmpurl) = @_;

    my $allConstraintInfo = $$schemaObjRef->allConstraintInfo;

    my $db = uc($database);
    if ($db eq "MAD") {
	$db = "SMD";
    }
    if ($owner eq 'SGR') {
	$db = 'SGR';
    }

    # Open table download file for writing
    my $filename = "${db}_AllConstraintInfo.xls";
    open (OUT, ">$tmpdir$filename") || 
	die "TableSpecifications.pm: Can't open '$filename' for writing: $!\n";

    # Print datafile headers
    print OUT "Table Name\tConstraint Name\tConstraint Column\tType\tDescription\n";

    # iterate over each row
    foreach my $constraint_row (@{$allConstraintInfo}){

	my ($table_name, $constraint, $column, $type, $condition) = @{$constraint_row};
	
	if ($condition =~ /NULL/) {
	    $type = "NULL";
	} else {
	    $type = "CHECK";
	}

	print OUT "$table_name\t$constraint\t$column\t$type\t$condition\n";
    }
    close (OUT);

    # Only print to web page if data exists
    if (@{$allConstraintInfo}) {
	print br;
	print "<a href=$tmpurl$filename>Download Oracle Constraint Info</a> (table_name, constraint name, constraint column, type, description)";
    }

}

###########################################################################
sub printAllSequenceInfo {
###########################################################################
    my ($schemaObjRef, $database, $owner, $tmpdir, $tmpurl) = @_;

    my $allSequenceInfo = $$schemaObjRef->allSequenceInfo;

    my $db = uc($database);
    if ($db eq "MAD") {
	$db = "SMD";
    }
    if ($owner eq 'SGR') {
	$db = 'SGR';
    }

    # Open table download file for writing
    my $filename = "${db}_AllSequenceInfo.xls";
    open (OUT, ">$tmpdir$filename") || 
	die "TableSpecifications.pm: Can't open '$filename' for writing: $!\n";

    # Print datafile headers
    print OUT "Table Name\tSequence Name\tSequence Column\n";

    # iterate over each row
    foreach my $sequence_row (@{$allSequenceInfo}){

	my ($table_name, $column, $sequence) = @{$sequence_row};

	print OUT "$table_name\t$column\t$sequence\n";
    }

    close (OUT);

    # Only print to web page if data exists
    if (@{$allSequenceInfo}) {
	print br;
	print "<a href=$tmpurl$filename>Download Names of Oracle Sequence No.s</a> (table_name, sequence name, sequence column)";
    }

}


###########################################################################
sub PrintTable {
###########################################################################
# Given column title in $row and array of data in @rows, output 
# them in a table format.

        my ($query, $headers, @rows) = @_;
        print $query->table({-border=>1},

                        TR({-align=>'LEFT', -valign=>'TOP'},

                           [$headers, @rows])), br;
  
        print br;
}

###########################################################################
sub PrintFile {
###########################################################################
# Given a file name, column headers and array of data in @rows,
# print them to a file.

    my ($database, $tmpdir, $file, @rows) = @_;
    
    open (OUT, ">>$tmpdir$file") || 
	die "TableSpecifications.pm: Can't open $file: $!\n";

    foreach my $row (@rows) {
	print OUT "$row";
    }

}