#!/local/bin/perl
#
# Find reserved words in a MySQL installation
#   (all fields in all tables in all databases)
#
# File: findres.pl
#
# Author: Nem W Schlecht
# Last Modification: $Date: 1998/01/30 19:44:28 $
#
# $Id: findres.pl,v 1.2 1998/01/30 19:44:28 nem Exp nem $
# $Log: findres.pl,v $
# Revision 1.2  1998/01/30 19:44:28  nem
# Slight code cleanup, added more comments
#
# Revision 1.1  1998/01/30 19:37:51  nem
# Initial revision
#
#

use DBI;
use Getopt::Long;

GetOptions(qw(short full host:s testdb:s help));

#
# Usage message
if ($opt_help) {
    my($me)=(split(/\//,$0))[-1];	# grab our name
    print "usage: $me [option]\n";
    print "  --help            this help message\n";
    print "  --full            full tree-like listing [default]\n";
    print "  --short           short listing - just the DSN of the bad db/table/field\n";
    print "  --host hostname   hostname to connect to [localhost]\n";
    print "  --testdb testdb   test database to connect to (remote hosts only) [test]\n";
    exit();
}

#
# Set host name
my($host) = $opt_host || "localhost";
my($testdb);

#
# Get list of databases, use shortcut if localhost
my(@databases);
if ($host eq "localhost") {
    @databases = DBI->data_sources("mysql");
} else {
    $testdb = $opt_testdb || "test";
    my($dbh) = DBI->connect( "dbi:mysql:$testdb:$host" ) || die "Could not connect to $testdb";
    @databases = $dbh->func('_ListDBs');
    $dbh->disconnect();
    for (@databases) {
	$_ = "DBI:mysql:$_";
    }
}

#
# Load reserved words from __DATA__ section
my(%res);
while(<DATA>) {
    chomp;
    my(@words)=split(' ',$_);
    for (@words) {
	$res{$_}=1;
    }
}

#
# Loop over the databases
for (@databases) {
    my($db) = $_;
    my($dbname) = (split(/:/,$db))[2];
    print "$dbname" unless ($opt_short);
    if (defined($res{$dbname})) {
	if ($opt_short) {
	    print "$dbname\n";
	} else {
	    print "   *** RESERVED WORD! ***";
	}
    }
    print "\n" unless ($opt_short);

    #
    # Open connection and get list of tables
    my($dbh) = DBI->connect( "$db:$host" );
    if (!$dbh) {
	print " ** no connect! **\n" unless ($opt_short);
	next;
    }
    my(@tables) = $dbh->func( '_ListTables' );

    #
    # Loop over the tables
    for (@tables) {
	my($tbl) = $_;
	print "  $_" unless ($opt_short);
	if (defined($res{$tbl})) {
	    if ($opt_short) {
		print "$dbname:$tbl\n";
	    } else {
		print "   *** RESERVED WORD! ***";
	    }
	}
	print "\n" unless ($opt_short);

	#
	# Get list of fields
	my($sth) = $dbh->func($tbl, 'listfields');
	my(@fields) = @{ $sth->{NAME} };
	my(@types) = @{ $sth->{type} };
	$sth->finish();

	#
	# Loop over the fields
	for (0..$#fields) {
	    print "    $fields[$_] ($types[$_])" unless ($opt_short);
	    if (defined($res{$fields[$_]})) {
		if ($opt_short) {
		    print "$dbname:$tbl:$fields[$_]\n";
		} else {
		    print "   *** RESERVED WORD! ***";
		}
	    }
	    print "\n" unless ($opt_short);
	}
    }

    #
    # Clean up
    $dbh->disconnect();
    undef($dbh);
}

__DATA__

action add all alter and as asc auto_increment between bigint bit binary
blob both by cascade char character change check column columns create data
database databases date datetime day day_hour day_minute day_second
dayofweek dec decimal default delete desc describe distinct double drop
escaped enclosed enum explain fields float float4 float8 foreign from for
full grant group having hour hour_minute hour_second ignore in index infile
insert int integer interval int1 int2 int3 int4 int8 into is join key keys
leading left like lines limit lock load long longblob longtext match
mediumblob mediumtext mediumint middleint minute minute_second month
natural numeric no not null on option optionally or order outer outfile
partial precision primary procedure privileges read real references rename
regexp repeat replace restrict rlike select set show smallint
sql_big_tables sql_big_selects sql_select_limit sql_log_off straight_join
starting table tables terminated text time timestamp tinyblob tinytext
tinyint trailing to use using unique unlock unsigned update usage values
varchar varying varbinary with write where year year_month zerofill