#!/usr/bin/perl -w
#
# $Id: genSubCmp.pl,v 1.2 2003/01/15 16:29:50 mpeppler Exp $
#
# Generate configuration files for rs_subcmp.

use strict;

use Sybase::Simple;
use Getopt::Long;

use Data::Dumper;

my %opts;

GetOptions(\%opts, "pds=s", "rds=s", "db=s", "user=s", "pwd=s");

usage() unless $opts{pds} && $opts{rds} && $opts{db};

my $pds = $opts{pds};
my $rds = $opts{rds};
my $db  = $opts{db};
my $usr = $opts{user} || 'sa';
my $pwd = $opts{pwd};

# First, select all the tables from the source data server:

my $pds_dbh = new Sybase::Simple $usr, $pwd, $pds;
$pds_dbh->ExecSql("use $db");
my $source_tables = $pds_dbh->ArrayOfScalar("
   select name 
     from sysobjects 
    where type = 'U'
      and sysstat2 & 1024 != 1024
      and name not in ('rs_lastcommit', 'rs_threads')
");

# Find tables in the target
my $rds_dbh = new Sybase::Simple $usr, $pwd, $pds;
$rds_dbh->ExecSql("use $db");
my $target_tables = $rds_dbh->ArrayOfScalar("
   select name 
     from sysobjects 
    where type = 'U'
      and sysstat2 & 1024 != 1024
");

# There's no point trying to run rs_subcmp unless the tables exist
# in both the source and the target, so find out the intersection
# of the two lists of tables:
my %tables = map { $_ => 1 } @$source_tables;
grep($tables{$_}++, @$target_tables);

# Now the %tables hash will have one entry for all the tables, but the
# value will be 2 if the table exists in both servers.

foreach my $tab (sort(keys(%tables))) {
    next if $tables{$tab} < 2;
    genSubCmp($pds_dbh, $tab);
}

sub genSubCmp {
    my $dbh = shift;
    my $tab = shift;

    warn "Processing $tab\n";

    my $cols = $dbh->ArrayOfHash("
   select Column_name = c.name,
          Type = t.name,
          Nulls = convert(bit, (c.status & 8)),
          Ident = convert(bit, (c.status & 0x80))
     from syscolumns c, systypes t
    where c.id = object_id('$tab')
      and c.usertype *= t.usertype
");

#    print Dumper($cols) if $tab eq 'Notes';

    # Use the first unique index as the primary key.
    my $indexes = $dbh->ArrayOfArray("sp_helpindex $tab");
    my @keys;
    foreach my $ix (@$indexes) {
	if($ix->[1] =~ /unique/) {
	    @keys = split(/\s*,\s*/, $ix->[2]);
	    last;
	}
    }

    if(!@keys) {
	warn "Can't find primary key for $tab, skipping";
	return -1;
    }
    open(OUT, ">${tab}_subcmp.cfg") || die "Can't open ${tab}_subcmp.cfg: $!";
    print OUT "# rs_subcmp configuration file for $tab\n";
    print OUT "PDS = $pds\n";
    print OUT "RDS = $rds\n";
    print OUT "PDB = $db\n";
    print OUT "RDB = $db\n";
    print OUT "PTABLE = $tab\n";
    print OUT "RTABLE = $tab\n";

    my $select = buildSelect($tab, \@keys, $cols);

    print OUT "RSELECT = $select\n";
    print OUT "PSELECT = $select\n";
    print OUT "PUSER = sa\n";
    print OUT "RUSER = sa\n";
    print OUT "PPWD = funky\n"; # XXX - FIXME
    print OUT "RPWD = funky\n";
    foreach (@keys) {
	print OUT "KEY = $_\n";
    }
    print OUT "RCHARSET = utf8\n";
    print OUT "SCHARSET = utf8\n";
    print OUT "SORT_ORDER = nocase\n";
    print OUT "SORT_ORDER_ALL_COLS = nocase\n";
    print OUT "UNICODE_SORT_ORDER = nocase\n";
    print OUT "UNICODE_SORT_ORDER_ALL_COLS = nocase\n";
    print OUT "RECONCILE = N\n";
    print OUT "RECONCILE_CHECK = N\n";
    print OUT "VISUAL = N\n";
    foreach (@$cols) {
	if($_->{Ident}) {
	    print OUT "IDENTITY = $_->{Column_name}\n";
	}
    }
    
    
    close(OUT);
}

sub buildSelect {
    my $tab  = shift;
    my $keys = shift;
    my $cols = shift;

    my $sql = 'select ';

#    print Dumper($cols);
    foreach (@$cols) {
	next if ($_->{Type} eq 'text' || $_->{Type} eq 'image' || $_->{Type} eq 'timestamp');
#	print Dumper($_);
	$sql .= "$_->{Column_name}, ";
    }
    foreach (@$cols) {
	next unless ($_->{Type} eq 'text' || $_->{Type} eq 'image');
#	print Dumper($_);
	$sql .= "$_->{Column_name}, ";
    }
    $sql =~ s/,\s+$//;
    $sql .= " from $tab order by ";
    $sql .= join(',', @$keys);

    return $sql;
}

sub usage {
    die "Usage: $0 --pds <primary ds> --rds <replicant ds> --db <database> [--user <name>] [--pwd <password>]\n";
}