#!/usr/local/bin/perl
#<REPLACE_IF_NECESSARY>

## reppattern - Script to display ALL patterns in a RepServer Domain, including reptable true yes/no, autocorrection and warnings if theres a
#              mismatch between repdefs/subscriptions and setreptable.
#
#    -IMPORTANT - Before start to use this program, please make sure to check <REPLACE_IF_NECESSARY> and fix 
#
#  Vishi - Dec 23 2009, Large Table Name support and FIXED bug in "WARNING .. reptable true but no matching repdef WARNING reptable true" 
## Vishi - Nov 10 2007, trvishi@yahoo.com
#          Please do drop in a mail for any feedback or to report any bugs which you might see
#
#
#
#    -For Usage, please go to the bottom of the screen, or just type reppattern.pl
#      -I have used isql to keep things simpler instead of db libraries.  Make sure its in the path when you run it
#


require "/usr/perl5/5.8.4/lib/getopts.pl";
#<REPLACE_IF_NECESSARY>
# You should find this in the web if you dont have it.

# Buffering
select(STDERR); $| = 1; # make unbuffered
select(STDOUT); $| = 1; # make unbuffered

##
# Global Vairables
##

$QUERYSTR="isql -w20000 -b ";

##
# Get command line args
##

do Getopts ('P:p:U:u:R:T:D:d:S:X:M');

if(defined( $opt_P )) { $reppass = $opt_P; }
if(defined( $opt_p )) { $asepass = $opt_p; }
if(defined( $opt_U )) { $repuser = $opt_U; }
if(defined( $opt_u )) { $aseuser = $opt_u; }
if(defined( $opt_R )) { $repserver = $opt_R; }
if(defined( $opt_T )) { $table = $opt_T; }
if(defined( $opt_D )) { $db = $opt_D; }
if(defined( $opt_S )) { $ds = $opt_S; }
if(defined( $opt_X )) { $rs = $opt_X; }
if(defined( $opt_M )) { $reptable = 1; }else { $reptable =0; }


if( !defined( $repserver )) { &usage; }
if( !defined( $repuser )) { $repuser = "sa"; }
if( !defined( $aseuser )) { $aseuser = "sa"; }


##
# get the repserver password if we need it
##

while( !defined( $reppass ) || ! $reppass ) {
    system 'stty', '-echo';
    print "Password for $repserver / $repuser : ";
    chop( $reppass = <STDIN> );
    print "\n";
}
##
# get ase user password if not provided in command line
##

while( !defined( $asepass ) || ! $asepass ) {
    system 'stty', '-echo';
    print "Password for $aseserver / $aseuser : ";
    chop( $asepass = <STDIN> );
    print "\n";
}
if( defined( $table ) || defined($ds) || defined ($db) || defined($rs) ) {
  print "\n FILTERS Used -> Object: $table , DataServer: $ds, Database: $db, RepServer: $rs\n";
}

if ( !defined $table ) { $table='NOTDEFINED' }


###########################################################
# Main
############################################################

##
# Step 1. Get RSSD of the given repserver
# Step 2. Find the IDServer
# Step 3. Get RSSD of IDServer
# Step 4. Get List of RepServers from IDServer
# Step 5. Foreach Repserver in IDServer, get unique primdb / repdb
# Step 6. Consolidate output
##

##
# Step 1. Get RSSD of the given repserver
##
#print "Step 1. Get RSSD of the given repserver $repserver \n";

system 'stty', 'echo';
        $sql_command = $QUERYSTR . " -U " . $repuser . " -S" . $repserver . " <<EOF | sed s/'Password:'/''/1
$reppass
admin rssd_name
go
EOF";
my @outp=`$sql_command`;
processerror(@outp);
($aseserver,$asedb)=split(' ',$outp[1]);
chomp $aseserver; chomp $asedb; 

##
# Step 2. Find the IDServer
##
#print "Step 2. Find the IDServer from $aseserver $asedb \n";

system 'stty', 'echo';
        $sql_command = $QUERYSTR . " -U " . $aseuser . " -S" . $aseserver . " <<EOF | sed s/'Password:'/''/1
$asepass
use $asedb
go
select charvalue from rs_config where optionname = 'id_server'
go
EOF";
my @outp=`$sql_command`;
processerror(@outp);
my $idserver=$outp[1];
chomp($idserver);


##
# Step 3. Get RSSD of IDServer
##

#print "Step 3. Get RSSD of IDServer $idserver end \n";
system 'stty', 'echo';
        $sql_command = $QUERYSTR . " -U " . $repuser . " -S" . $idserver . " <<EOF | sed s/'Password:'/''/1
$reppass
admin rssd_name
go
EOF";
print "Connecting to ID server $idserver \n";
my @outp=`$sql_command`;
processerror(@outp);
($idaseserver,$idasedb)=split(' ',$outp[1]);
chomp $idaseserver; chomp $idasedb; 


##
# Step 4. Get List of RepServers from IDServer
##
#print "Step 4. Get List of RepServers from IDServer $idaseserver $idasedb \n";

system 'stty', 'echo';
        $sql_command = $QUERYSTR . " -U " . $aseuser . " -S" . $idaseserver . " <<EOF | sed s/'Password:'/''/1
$asepass
use $idasedb
go
set nocount on
go
select convert(varchar,name) from rs_sites 
go
EOF";
my @outp=`$sql_command`;
processerror(@outp);
my @allsites=@outp;

##
# Step 5. Foreach Repserver in IDServer, get unique primdb / repdb
##
#print "Step 5. Foreach Repserver in IDServer, get unique primdb  repdb \n";

chomp $idserver,$idaseserver,$idasedb;
#print "--Given Server $repserver, ID Server for that is $idserver \n";
print "--Found the following repservers in the same domain for $repserver \n";
print " @allsites \n";

my @masterlist,@uniquelist,@tablelist;
for (my $i=1; $i <=$#allsites; $i++ ) {
    @curlist=getpatterns($allsites[$i]);
    push (@masterlist,@curlist);
}
my @warmstandby;
for (my $i=1; $i <=$#allsites; $i++ ) {
    @curlist=getwarmstandby($allsites[$i]);
    push (@warmstandby,@curlist);
}

##
# Step 6. Consolidate output
##

%seen = ();
foreach $item (@masterlist) {
    push(@uniquelist, $item) unless $seen{$item}++;
}
@uniquelist = sort(@uniquelist);
if ( "$table" eq "NOTDEFINED" ) { 
    print "                                                                  PATTERNS                                                            \n";
    print "----------------------------------------------------------------------------------------------------------------------------------------------------\n";
    print " PRS                            PDS.PDB                                            RRS                            RDS.RDB\n";
    print "----------------------------------------------------------------------------------------------------------------------------------------------------\n";
    print @uniquelist;
    print "                                                                  WARMSTANDBY PATTERNS                                                          \n";
    print "----------------------------------------------------------------------------------------------------------------------------------------------------\n";
    print " PRS                            PDS.PDB                                            RRS                            RDS.RDB\n";
    print "----------------------------------------------------------------------------------------------------------------------------------------------------\n";
}else {
    #When theres autocorrection, sites other than the owning site show it as zero, this loop is to clean up that
    my %finalhash;
    foreach (@uniquelist)
    {
        #print "$_ \n";
        next if ($_ =~ /^\s*$/);
        my $key=substr $_,0,195;
        my $value=substr $_,195,1;
        #print " key - $key  value - $value \n";
        $finalhash{$key}=$value;
    }
    #Now assign it back to an array and sort print as hash wont guarantee sorting.  Might be a better way of doing this.
    my @tmparr;
    while ( my ($key, $value) = each(%finalhash) ) {
        push(@tmparr,$key ." ".$value."\n")
    }
    #print %finalhash; 
    #print "WITHOUT setreptable DISPLAY \n";
        #print sort(@tmparr);

    #Check all tables that is marked for replication and compare it with above.

    %seen2= ();
    my $ctr=0;
    my @pdspdb;
        foreach $currow (@tmparr) {
        @curpdspdb=split(/,/,$currow);
            $tmp=$curpdspdb[2];    
        push(@pdspdb,$tmp) unless $seen2{$tmp}++;
    }
    #print " Primary dbs \n @pdspdb \n";
    my @reptablemaster;
    my %reptablehash;
    foreach $pdspdb (@pdspdb) {
        #print " PDSPDB $pdspdb \n ";
        @reptable=getreptable($pdspdb);
        foreach (@reptable) {
            chomp;
            $reptablehash{$_}=1;
            #print "REPTABL is $_ END\n";
        }
        push (@reptablemaster,@reptable);
    }
    #print @reptablemaster;
    my @finaldisplay;
    my @repdefarr;
        foreach $currow (@tmparr) {
        chomp $currow;
        @curpdspdbtable=split(/,/,$currow);
            $tmp=",".$curpdspdbtable[2].",".$curpdspdbtable[3]." ";    
        #print "REPPTMP is $tmp END\n";
        if ($reptablehash{$tmp} eq 1){ push (@finaldisplay,$currow.",1\n"); } else { push (@finaldisplay,$currow.",0\n"); }
        push (@repdefarr,$tmp);
    }
    #print "FINAL DISPLAY \n";
    print "                                                                  PATTERNS                                                            \n";
    print "---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n";
    print " PRS            ,PDS.PDB                      ,Table                        ,RepDef                        ,MinCol, RRS          , RDS.RDB                      , subscription                ,AC,Reptable\n";
    print "---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n";

        print sort(@finaldisplay);

    #Find tables which are marked with reptable true but has no repdef/subscription

    %repdefhash=map {$_, 1} @repdefarr;
    my @difference = grep {!$repdefhash {$_}} @reptablemaster;
    print "---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n";
    print "WARNING .. reptable true but no matching repdef \n";
    print map { "$_ \n"} @difference;
    print "---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\n";
    print "                                                                  WARMSTANDBY PATTERNS  [object level not supported yet]                                  \n";
    print "----------------------------------------------------------------------------------------------------------------------------------------------------\n";
    print " PRS                            PDS.PDB                                            RRS                            RDS.RDB\n";
    print "----------------------------------------------------------------------------------------------------------------------------------------------------\n";
}
%seen3 = ();
foreach $item (@warmstandby) {
    push(@warmstandbyunique, $item) unless $seen3{$item}++;
}
print sort(@warmstandbyunique);



##
# End Main
##

sub getreptable {
    my($pdspdb)=@_;
    chomp $pdspdb;
    ($cursrv,$curdb)=split('\.',$pdspdb);
    print "Checking sp_setreptable for $cursrv $curdb \n";
        $sql_command = $QUERYSTR . "-s',' -U " . $aseuser . " -S" . $cursrv . " <<EOF | sed s/'Password:'/''/1
$asepass
set nocount on
go
use $curdb
go
select '$pdspdb'+','+convert(varchar,name) from sysobjects where type='U' and (sysstat & -32768)=-32768
and name like '%$table%'
go
EOF";
my @outp=`$sql_command`;
my $error=processerror2(@outp);
if ( $error gt 0 ) {
  print "WARNING: Error in getreptable for $pdspdb , skipping this and proceeding \n";
  print @outp;    
  return " " ;
}
@outp;
}
sub getwarmstandby {
    my($currepserver)=@_;
chomp $currepserver;
print "--Getting pattern for $currepserver \n";
        $sql_command = $QUERYSTR . "-s','  -U " . $repuser . " -S" . $currepserver . " <<EOF | sed s/'Password:'/''/1
$reppass
admin logical_status
go
EOF";
my @outp=`$sql_command`;
if ( $serverfound eq 1 ) {
  print "WARNING: $currepserver is not found in interfaces... skipping check, check and cleanup RepServer if its decommissioned \n";
  return " " ;
}
if ( $serverfound eq 2 ) {
  print "WARNING: $currepserver cannot connect ... skipping check, Could be password different \n";
  return " " ;
}
if ( $serverfound eq 3 ) {
  print "WARNING: $currepserver cannot connect ... skipping check, Could be down \n";
  return " " ;
}
if ( $serverfound eq 4 ) {
  print "WARNING: $currepserver cannot connect ... Unknown error, skipping this server \n";
  return " " ;
}
my @warmtmp,@warm;
foreach $currow (@outp){
    @warmtmp=split(',',$currow);    
    push(@warm,$warmtmp[6].','.$warmtmp[2].','.$warmtmp[6].','.$warmtmp[4]."\n");
}
@warm;
}


sub getpatterns {
    my($currepserver)=@_;
chomp $currepserver;
print "--Getting pattern for $currepserver \n";
        $sql_command = $QUERYSTR . " -U " . $repuser . " -S" . $currepserver . " <<EOF | sed s/'Password:'/''/1
$reppass
admin rssd_name
go
EOF";
my @outp=`$sql_command`;
my $serverfound=processerror2(@outp);
if ( $serverfound eq 1 ) {
  print "WARNING: $currepserver is not found in interfaces... skipping check, check and cleanup RepServer if its decommissioned \n";
  return " " ;
}
if ( $serverfound eq 2 ) {
  print "WARNING: $currepserver cannot connect ... skipping check, Could be password different \n";
  return " " ;
}
if ( $serverfound eq 3 ) {
  print "WARNING: $currepserver cannot connect ... skipping check, Could be down \n";
  return " " ;
}
if ( $serverfound eq 4 ) {
  print "WARNING: $currepserver cannot connect ... Unknown error, skipping this server \n";
  return " " ;
}
($laseserver,$lasedb)=split(' ',$outp[1]);
chomp $laseserver; chomp $lasedb; 
#print "Table is $table \n";

        $sql_command = $QUERYSTR . "-s',' -U " . $aseuser . " -S" . $laseserver . " <<EOF | sed s/'Password:'/''/1
$asepass
set nocount on
go
use $lasedb
go

/* Create a temp table to dump info for the repdefs */
create table #rs_repinfo
(
        objid          binary(8) null,
        objname        varchar(255) null,
        prs            varchar(30) null,
        prds            varchar(30) null,
        prdb            varchar(30) null,
        phys_tablename  varchar(255) null,
        phys_objowner  varchar(30) null,
        deliver_as_name varchar(255) null,
        repl_objowner  varchar(30) null,
        crdate          datetime null,
        type            varchar(4) null,
        dbid            int null,
        phys_owner_tab  varchar (20) null,
        repl_owner_tab  varchar (20) null,
        pcontroller    int,
    min_cols    char(1),
)
go

/* Initialize #rs_repinfo from rs_databases, rs_objects, rs_sites */
insert into #rs_repinfo (objid, objname, prs, prds, prdb, phys_tablename,
      phys_objowner, deliver_as_name, repl_objowner, crdate,type, dbid, phys_owner_tab, repl_owner_tab, pcontroller,min_cols)
select ro.objid, ro.objname, rs.name, rd.dsname, rd.dbname,
      ro.phys_tablename, ro.phys_objowner, ro.deliver_as_name,
      ro.repl_objowner, ro.crdate, 
      case 
        when objtype =  'R'
        then 'Tbl'
                else objtype
      end,rd.dbid,
      case 
        when phys_objowner != ' '
                  then substring(phys_objowner+'.'+phys_tablename, 1, 20)
                else  phys_tablename
      end,
      case 
        when repl_objowner != ' '
                  then substring(repl_objowner+'.'+deliver_as_name, 1, 20)
                else deliver_as_name
      end,
      rs.id,
    case
        when ro.attributes & 4 != 0
                then 'Y'
                else 'N'
      end
from  rs_objects ro,  rs_sites rs, rs_databases rd
where  ro.prsid = rs.id and
      rd.dbid =  ro.dbid and
      ro.objname not like 'rs_in%'  and
      ro.objname not like 'rs_drp%'
go

if ('$table' != 'NOTDEFINED')
begin
select distinct 'PRS' = convert(char(15),ps.name),
        'Primary DS.DB' = convert(char(30),ro.prds+'.'+ro.prdb ),
        'Table' = convert(varchar(30),ro.phys_tablename),
        'RepDef' = convert(varchar(30),ro.objname),
        'MinCol' = min_cols,
                'RRS' = convert(char(15),ps2.name),
                'Replicate DS.DB' = convert(char(30),rd.dsname+'.'+rd.dbname ),
        'Subscription Name' = substring(subname, 1, 30),
        'AC' = substring(isnull(convert(char(1), robj.attributes), '0'), 1, 3)
            from #rs_repinfo ro, rs_subscriptions rs, rs_databases rd, rs_users ru, rs_sites ps, rs_sites ps2, rs_repobjs robj
where  ro.objid = rs.objid and
        rs.rownerid *= ru.uid and
        rs.objid  *= robj.objid and
        rs.dbid  *= robj.dbid and
        rs.dbid = rd.dbid and
        ro.pcontroller = ps.id and
        rd.prsid = ps2.id and
        ro.objname like '%$table%' and 
        ro.prdb not like '%RSSD%'  and
    (ro.prds like '%$ds%' or rd.dsname like '%$ds%') and
    (ro.prdb like '%$db%' or rd.dbname like '%$db%') and
        (ps.name like '%$rs%' or ps2.name like '%$rs%')  
        order by ro.prds, ro.prdb  
end
else
begin
select distinct 'PRS' = convert(char(30),ps.name),
        'Primary DS.DB' = convert(char(50),ro.prds+'.'+ro.prdb + '('+convert(varchar,ro.dbid)+')'),
                'RRS' = convert(char(30),ps2.name),
                'Replicate DS.DB' = convert(char(50),rd.dsname+'.'+rd.dbname+ '('+convert(varchar,rd.dbid)+')')
            from #rs_repinfo ro, rs_subscriptions rs, rs_databases rd, rs_users ru, rs_sites ps, rs_sites ps2
where  ro.objid = rs.objid and
        rs.rownerid *= ru.uid and
        rs.dbid = rd.dbid and
        ro.pcontroller = ps.id and
        rd.prsid = ps2.id and
        ro.prdb not like '%RSSD%' and
    (ro.prds like '%$ds%' or rd.dsname like '%$ds%') and
    (ro.prdb like '%$db%' or rd.dbname like '%$db%') and 
        (ps.name like '%$rs%' or ps2.name like '%$rs%')  
        order by ro.prds, ro.prdb  
end
go
EOF";
my @outp=`$sql_command`;
my $noerrors=processerror2(@outp);
#print @outp;
#($laseserver,$lasedb)=split(' ',$outp[1]);
#chomp $laseserver; chomp $lasedb; 
if ($noerrors eq 0) {
return @outp;
} 
if ( $noerrors eq 1 ) {
  print "WARNING: $laseserver is not found in interfaces... skipping check, check and cleanup RepServer if its decommissioned \n";
  return " " ;
}
if ( $noerrors eq 2 ) {
  print "WARNING: $laseserver cannot connect ... skipping check, Could be password different \n";
  return " " ;
}
if ( $noerrors eq 3 ) {
  print "WARNING: $laseserver cannot connect ... skipping check, Could be down \n";
  return " " ;
}
if ( $noerrors eq 4 ) {
  print "WARNING: $laseserver cannot connect ... Unknown error, skipping this server \n";
  return " " ;
}
}


sub processerror {
    my(@output)=@_;
    foreach (@output) {
            if (( $_ =~ m/Msg/) || ( $_ =~ m/ct_connect/)) { 
                print @output;
                          exit;
        }
    }
}
sub processerror2 {
    my(@output)=@_;
    foreach (@output) {
            if (( $_ =~ m/Msg/) || ( $_ =~ m/ct_connect/)) { 
            if ($_ =~ m/Requested server name not found/) {
                return 1;
            }
            if ($_ =~ m/attempt to connect to the server failed/) {
                return 2;
            } 
            if ($_ =~ m/call to connect two endpoints failed/) {
                return 3;
                        }else {
                return 4;
            }
            print @output;
            #Dont exit here, try to process whatever server that can be connected.
                  #exit;
        }
    }
return 0;
}

#############################################################################################
# Standard usage error
############################################################################################
sub usage {

print STDERR <<EOF;

        Usage : reppattern.pl -R repserver [-U repuser ] [-P reppassword ] [-u aseuser] [-p asepass ] [ -T object ] [ -D Database ] [ -S DataServer ] [ -X  RepServer ] [ -M ]

          --Required Parameters

                  repserver  - can be any repserver in the domain and it will fetch all the patterns
          repuser    - default is sa, mention if otherwise
          aseuser    - default is sa, mention if otherwise  
          reppassword - prompted if not given in command line (Should be the same password for all RepServers in the domain) 
          asepass    - prompted if not given in command line (Should be the same password for all RSSD)

          Filters

          object      - Only show matching Table or Function (Wildcard).  If you want to show all objects use -T ''
          Database    - Only show matching Primary/Replicate Database (Wildcard).  
          DataServer  - Only show matching Primary/Replicate Data Server (Wildcard).  
          RepServer  - Only show matching Replication Server (Wildcard).  

        Note:
        1. This program is more concentrated on table to table replication setup.
        2. WARM Standby - Only prints db level info and object level is not supported. Neither is sp_reptostandby.  Am hoping to get this 
          implemented soon.
        3. DOES NOT support ASE 11.X RSSD versions, due to case statements
        4. DOES NOT support LTMs.
        5. Functions search is not fully tested.

        Examples:

        To display db level information     --> $reppattern.pl -R REPSERVER 
        To display Table level information     --> $reppattern.pl -R REPSERVER -T 'table_patt'
        To display All tables            --> $reppattern.pl -R REPSERVER -T ''

EOF
        exit 1;
}