#!/mv/local/bin/perl

use Scriptalicious
    -progname => "nuke-schema";

=head1 NAME

nuke-schema - Obliterate an Oracle schema

=head1 SYNOPSIS

 nuke-schema user/password

=head1 DESCRIPTION

This script empties the specified schema, by dropping its tables,
views, synonyms and sequences.  Use with reckless abandon.

There is no way to delete another user's schema.

Note that the script doesn't actually delete anything unless you run
with the -y switch.

=head1 COMMAND LINE OPTIONS

=over

=item B<-y, --yes>

Don't just pretend - actually drop the schema.

=item B<-h, --help>

This help page

=item B<-v, --verbose>

Specify verbose operation

=back

=cut

use strict;
use DBI;

my $dont_pretend;
getopt("yes|y" => \$dont_pretend);

my $cred = shift or abort "no login credentials given";

my ($user, $password) = ($cred =~ m{^([^/]+)(?:/(.*))?$});
$password or moan "connecting without a password";

whisper "connecting to the $user schema";
my $dbh = DBI->connect("dbi:Oracle:", $user, $password);

for my $entity ( qw(table view synonym sequence) ) {

    my $sth = $dbh->prepare (<<"SQL") or barf $dbh->errstr;
SELECT
    ${entity}_name
FROM
    user_${entity}s
SQL
    $sth->execute() or barf $dbh->errstr;

    my @entity;
    while ( my $row = $sth->fetchrow_arrayref ) {
	push @entity, $row->[0]
	    unless $row->[0] =~ m/BIN\$/;
    }

    say "dropping ".@entity." ${entity}s";
    for my $item ( @entity ) {
	$item =~ s{"}{""}g;
	mutter "dropping $entity $item";
	$dbh->do("drop $entity \"$item\"")
	    if $dont_pretend;
    }
}

$dbh->do("purge recyclebin")
    if $dont_pretend;
$dbh->disconnect();

say "re-run with --yes option to actually clear the schema."
    unless $dont_pretend;