#!/usr/bin/perl
#
# Name:
#	list-indexes.pl
#
# Purpose:
#	List the indexes on a table.
#
# Author:
#	Ron Savage <ron@savage.net.au>
#
# Notes:
#	Set DBI_DSN=dbi:mysql:name-of-db
#	or  DBI_DSN=dbi:Pg:dbname=name-of-db
#	Set DBI_USER=a-name
#	Set DBI_PASS=a-password

use strict;
use warnings;

use DBI;

# -----------------------------------------------

eval{require DBD::mysql};
eval{require DBD::Pg};

print "DBI V $DBI::VERSION. \n";
print "DBD::mysql V $DBD::mysql::VERSION. \n";
print "DBD::Pg V $DBD::Pg::VERSION. \n";
print "\n";

my($target)	= shift || die("Usage: $0 table-name");
my($dbh)	= DBI -> connect();
my($sth)	= $dbh -> table_info(undef, undef, '%', 'Table');
my($info)	= $sth -> fetchall_arrayref({});

my($table, @table_name, $s);

for $table (@$info)
{
	push @table_name, $$table{'TABLE_NAME'};

#	print map{$s = $$table{$_} ? $$table{$_} : 'NULL'; "$_ => $s. \n"} sort keys %$table;
#	print "\n";
}

my($column);

for my $table_name (grep{/^$target$/} @table_name)
{
	print "Table: '$table_name'. \n";
	print "\n";
	print '-' x 50, "\n";
	print "Primary key info: \n";
	print "\n";

	$sth = $dbh -> primary_key_info(undef, undef, $table_name);

	if (defined $sth)
	{
		$info = $sth -> fetchall_arrayref({});

		for $column (@$info)
		{
			print map{$s = $$column{$_} ? $$column{$_} : 'NULL'; "$_ => $s. \n"} sort keys %$column;
		}
	}

	print '-' x 50, "\n";
	print "Foreign key infomation for table: '$table_name': \n";
	print "\n";

	for my $foreign_table (grep{! /^$table_name$/} @table_name)
	{
		$sth = $dbh -> foreign_key_info(undef, undef, $table_name, undef, undef, $foreign_table);

		if (defined $sth)
		{
			$info = $sth -> fetchall_arrayref({});

			for $column (@$info)
			{
				print "Foreign table: '$foreign_table'. \n\n";
				print map{$s = $$column{$_} ? $$column{$_} : 'NULL'; "$_ => $s. \n"} sort keys %$column;
				print "\n";
			}
		}
	}

	print '-' x 50, "\n";
	print '-' x 50, "\n";
}