#!/mv/local/bin/perl

use Scriptalicious
    -progname => "fix-sequences";

=head1 NAME

fix-sequences - Fix up sequences in an RT database

=head1 SYNOPSIS

 fix-sequences user/password

=head1 DESCRIPTION

This script updates all sequences in an RT Oracle instance so that
they are all at least as high as the highest ID in the corresponding
table.

It is recommended to use this script after using contributed RT
scripts, which may not be using the RT Perl API correctly or be using
parts of the RT Perl API which are not as thoroughly tested and
debugged as the web interface.

=head1 COMMAND LINE OPTIONS

=over

=item B<-e, --edit=s///>

Give a short Perl fragment to be used to go from a table name to a
sequence name.  The default is C<s/$/_seq/>.

=item B<-i, --id=column>

Specify the name of the corresponding ID column in tables.  Defaults
to C<id>.

=item B<-h, --help>

This help page

=item B<-v, --verbose>

Specify verbose operation

=back

=cut

use strict;
use DBI;

my $edit_frag = 's/$/_seq/';
my $id_col = 'id';
getopt("edit|e=s" => \$edit_frag,
       "id|i=s" => \$id_col,
      );

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,
		       { RaiseError => 0,
			 PrintError => 0,
		       })
    or barf "DBI connect failed; ".$DBI::errstr;

my %ent;
for my $entity ( qw(table sequence) ) {

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

    while ( my $row = $sth->fetchrow_arrayref ) {
	# skip `recycle bin' tables ... not entirely necessary but
	# just to keep quiet.
	($ent{$entity}{lc($row->[0])} = 1)
	    unless $row->[0] =~ m/BIN\$/;
    }
}

my ($fixed, $ok, $bad) = (0, 0, 0);

TABLE:
for my $table ( keys %{$ent{table}} ) {
    my $sequence = $table;
    eval '$sequence =~ '.$edit_frag;
    barf "edit fragment died; $@" if $@;

    if ( $ent{sequence}{$sequence} ) {

	my $x = $dbh->selectall_arrayref(<<"SQL")
SELECT
    ${sequence}.nextval,
   (SELECT
        max(${table}.${id_col})
    FROM
        ${table})
FROM
    DUAL
SQL
	    or do {
		moan("DBI operation failed; does ${table} have a "
		     ."`${id_col}' column?  error = ".$dbh->errstr);
		$bad++;
		next TABLE;
	    };

	my ($next_seq, $max_id);
	eval { ($next_seq, $max_id) = @{ $x->[0] } };
	barf "bogon returned from DBI; ".
	    do{require 'Data/Dumper.pm';Dumper($x)} if $@;

	whisper("$table max_id is: $max_id, "
		."$sequence.currval is: $next_seq");

	my $diff = ($max_id - $next_seq + 1);

	if ( $diff < 1 and $diff > -5  ) {
	    mutter "table ${table} has max. ID $max_id - $next_seq is OK";
	    $ok++;
	} else {
	    say "sequence $sequence needs updating by $diff";

	    ($dbh->do("DROP SEQUENCE ${sequence}") &&
	     $dbh->do(<<"SQL"))
CREATE SEQUENCE ${sequence}
    START WITH ${\($max_id + 1)}
    INCREMENT BY 1
SQL
		or do {
		    moan("DBI operation failed; error = ".$dbh->errstr);
		    $bad++;
		    next TABLE;
		};

	    $fixed++;
	}
    } else {
	moan "found table $table, but no sequence $sequence";
	$bad++;
    }
}

say "operation finished, $ok table(s) OK, $fixed table(s) fixed";
if ( $bad ) {
    say "$bad table(s) missing sequence or had an error";
}

$dbh->disconnect();