#! /usr/local/bin/sybperl 

# name: index.pl
# author: Todd Boss
# purpose: index drop/recreate script
#
# creation history
# date          author  purpose
# 05/09/97      T.Boss  finished creating...first successful dryrun.
# 05/13/97	T.Boss	added parameter, multiple server capability, cleaned
#			up output dirs.  Fixed all status/status2 field logic.
# 06/04/97	T.Boss	added ability to recognize/drop/recreate constraints
# 08/05/97	T.Boss	added all supported servers as possible candidates
#  			(mdb, peter, syb_dicom_svr, new_crimson added)
#			Added in unless clause for foreign references
# 08/07/97	T.Boss	cleaned up split statements, made code more intuitive
#			final tuning; checked against DbArtisan output
# 08/11/97	T.Boss	bug fix; comment out &spawn, fix alter table stmnt.
# 08/13/97	T.Boss	Discovered Sybase has alter table bug w/ alternate
#   table owners besides dbo...fixed in 11.03.1 but it means some constraints
#   won't drop correctly if the table is owned by someone besides dbo.

# Logic of script:  4 scenarios for each table
# - table has no indexes; one entry in sysindexes w/ indid=0.  ACTION: skip it
# - table has only a clustered index; one entry in sysindexes w/ indid=1.
#   ACTION: drop and recreate clustered index w/ sorted data option
# - table has non-clustered indexes but no clustered index; at least
#   two entries in sysindexes; one w/ indid=0, and one w/ indid>=2.  ACTION:
#   drop and recreate all non-clustered indexes in parallel
# - table has both clustered and non-clustered indexes: at least two entries
#   in sysindexes, one w/ indid=1 (the clustered) and on w/ indid>=2.  ACTION:
#   drop and recreate clustered index, which then causes all the non-clustered
#   indexes to drop and recreate automatically.
#
# coincidentally, dropping and recreating an index automatically issues an
# update statistics command on the table.  

# set unbuffered i/o for STDOUT
select((select(STDOUT), $|=1)[$[]);

##########
# handle parameter passed to script (parameter is server name)
# if its blank, echo usage info and exit

$server=$ARGV[0];
if ($server eq "") {
   die "Usage: index.pl \n";
}   

##########
# read in required files/libraries

#require "spawn.pl"; # contains &spawn defenition
require "sql.pl"; # contains &sql defenition

##########
# set SYBASE variable; this is so the script knows where interfaces is.
# Also tell the program where its secret password files are...

$ENV{'SYBASE'} = "/export/sybase11";
#$pwdir="/export/home/tboss";
$pwdir="/export/syb_ops";

##########
# DDL Generation options; the script will write the ddl to the $ddl_dir.  
# $create_new_ddl will write the ddl array to a file in the $ddl_dir
# If $create_new_ddl is set to false, the program will discard the ddl it
# creates after sending it for execution

# Future: make this a parameter command line...

#$ddl_dir="/export/home/tboss/ddl";
$ddl_dir="/export/syb_ops/index_ddl";
$create_new_ddl="true";

##########
# subroutine definitions

# This just populates the sa password for each server.  Borrowed from 
# nightly.pl

sub populate_sa_pw {
   local($server) = @_;
   if ($server eq "prod11") {
      $pwfile="$pwdir/.prod11sapw";
   }
   elsif ($server eq "prod") {
      $pwfile="$pwdir/.prodsapw";
   }
   elsif ($server eq "crimson") {
      $pwfile="$pwdir/.crimsonsapw";
   }
   elsif ($server eq "crimson_dev") {
      $pwfile="$pwdir/.crimson_devsapw";
   }
   elsif ($server eq "peter") {
      $pwfile="$pwdir/.petersapw";
   }
   elsif ($server eq "mdb") {
      $pwfile="$pwdir/.mdbsapw";
   }
   elsif ($server eq "syb_dicom_svr") {
      $pwfile="$pwdir/.syb_dicom_svrsapw";
   }
   elsif ($server eq "new_crimson") {
      $pwfile="$pwdir/.new_crimsonsapw";
   }           
   else {
      die "Server name passed not valid...please check server name.\n";
   }                            

   open(PWFILE,"$pwfile");
   $sapw = ;
   chop($sapw);
   close(PWFILE);
}
 
# PrintCols a sub used in the getddl which is borrowed from dbschema.pl.
# I added the "last if ($col eq "NULL") so the create index statements look
# nice when printed to @ddl_array

sub PrintCols {
   local(@array) = @_;
   #printf "PrintCols array is @array\n";
   $first = 1;
   while ($col = shift (@array)) {
      last if ($col eq '*');
      last if ($col eq "NULL");
      push(@ddl_array, ", ") if !$first;
      $first = 0;
      push(@ddl_array, "$col"); 
   }
}


###############
# This code is stolen/adapted from dbschema.pl version 1.16, authored by
# Michael Peppler (the sybperl author), substantially rewritten by D.Whitmarsh
# Added "with sorted data" clause at end for clustered indexes

# Logic; we create 
sub getddl {
   local($ownertable,$indid,$type,$ind_name) = @_;
   #printf "inside getddl...$ownertable, $indid, $type, $ind_name \n\n";

   @indexdetail = &sql($dbproc,< $ddl_dir/$server/$db/$ownertable.$ind_name");
      #open(DDL_FILE,"> $ddl_dir/test");
   
      while ($line = shift(@ddl_array)) {
         print DDL_FILE "$line";
      }
      close(DDL_FILE);

   } # if

} #sub writeddl

###############
sub executeddl {
   local(@ddl_array) = @_;

   # execute it

   while ($line = shift(@ddl_array)) {
      if ($line eq "go\n\n") {
         &sql($d,$sql_line);
         $sql_line = "";
      }
      else {
         $sql_line = $sql_line.$line;
      }
   }

   # this clears the @ddl_array
   $#ddl_array=-1;

   return(@ddl_array);
}

###############
   
sub every_table {
   $dbproc = &dblogin("sa", $sapw);
   &dbuse($dbproc, "master");

   #@dbs = &sql($dbproc, "select name from sysdatabases order by name");
   @dbs = ("common");

# Future: wait for Sybase bug to be fixed so we can drop CDB tables (they're
# owned by an alternate dbo...to drop them currently we'd have to setuser
# cdr_owner.  

   foreach $db (@dbs) {
      next if ($db =~ /tempdb|pubs2|model|sybsecurity|CDB/);
      &dbuse($dbproc, "$db");
      @tbl_owner = &sql($dbproc, "select u.name, s.name from sysusers u,
                    sysobjects s where u.uid = s.uid and s.type = 'U'
                    --order by s.name ",".");
      #@tbl_owner = ("dbo.PatIDIssue");
      #printf "Array tbl_owner is: @tbl_owner\n";


      foreach $ownertable (@tbl_owner) {

# We skip all tables that have data in excess of 250mb b/c the index creation
# would take forever.  We'll create a special procedure for these later.
# We also are skipping known problem tables in common

# Future: determine this threshold automatically via sp_spaceused or
# something?

         #printf "$ownertable\n"; 
         next if ($ownertable =~ /dbo\.lab_results|dbo\.lab_attribute_value_pairs|dbo\.cdr_lab_results|dbo\.cdr_lab_results_housekeeping|dbo\.hss|dbo\.cdrchem|dbo\.misdata|dbo\.patient_info/);
         @tbl_index=&sql($dbproc,"select indid,name,status2 from sysindexes 
                    where id=object_id('$ownertable') "," ");
         #printf "Array tbl_index is: @tbl_index\n";
         $numindexes=@tbl_index;

         #printf "@tbl_index[0]\n";
         ($indid,$ind_name,$status2) = split(" ",@tbl_index[0]);
         #printf " top of loop: $indid, $ind_name, $status2\n";

# Quick note to explain how we tell if a clustered index entry in sysindexes
# is really a constraint: the status2 field we pull is comprised of binary
# status control bits.  The status2 field supports the following combinations:
# (these descriptions taken from Sybooks, Cptr2 of Reference Suppliment)
#   1: constraint has foreign key dependencies
#   2: index supports primary key/unique delcarative constraint 
#   4: Index includes an Identity column
#   8: user didn't specify a constraint name upon constraint-creation
#   16: Large i/o's enabled; no effect on index ddl generation
#   32: MRU cache stratego enabled; no effect on index ddl generation
# If field 2 is existant in status2, we know its a constraint.  The common
# combos we see are 15, 6, 3, and 2.  By performing the action "status2 & 2"
# we perform a bitwise comparison operation in perl which will determine if
# the status field does indeed contain the field "2" indicating a constraint.
# Additionally, through these 4 cases, if we have a status2 & 1, then the
# constraint is a foreign key constraint and cannot be dropped; we skip it
# (which is not that bad a deal since its just a RI constraint and not a
# index-creating constraint).

         if ($numindexes == 1) {
            if ($indid == 0) {

# CASE 1: table has no indexes; one entry in sysindexes w/ 
# indid=0.  ACTION: skip it

               #printf "Case 1 table\n";
            }
            else {

# CASE 2: table has only a clustered index; one entry in sysindexes w/ 
# indid=1.  ACTION: drop and recreate index/constraint w/ sorted data option

               #printf "Case 2 table\n";
               if ($status2 & 2) {
                  #printf "its a constraint\n";
                  unless ($status2 & 1) {
                     &getddl($ownertable,1,"constraint",$ind_name);
                     &writeddl(@ddl_array);
                     @ddl_array=&executeddl(@ddl_array);
                  }
               }
               else {
                  #printf "its a normal index\n";
                  &getddl($ownertable,1,"index",$ind_name);
                  &writeddl(@ddl_array);
                  @ddl_array=&executeddl(@ddl_array);
               } 
            } 
         } 
         else {

# In Cases 3 and 4, we add the "unless $indid = 255
# because sybase stores a line in sysindexes w/ indid = 255 for all tables
# containing a text chain...the status and status2 bits contain non-index
# related status fields referring to text chain parameters in this case.

            if ($indid == 0) {

# CASE 3: table has no clustered index but has at least one 
# nonclustered index.  ACTION: grab ddl for all the non-
# clustered indexes and create in parallel

               #printf "Case 3 table\n";
               for ($i=1; $i<$numindexes; $i++) {
                  #printf "@tbl_index[$i]\n";
                  ($indid,$ind_name,$status2) = split(" ",@tbl_index[$i]);
                  #printf "$indid, $ind_name, $status2\n";
                  if ($status2 & 2) {
                     #printf "its a table level constraint\n";
                     unless ($status2 & 1) {
                        &getddl($ownertable,$indid,"constraint",$ind_name);
                        &writeddl(@ddl_array);
                        @ddl_array=&executeddl(@ddl_array);
                     }
                  }
                  else {
                     #printf "its a normal index\n";
                     unless ($indid == 255) {
                        &getddl($ownertable,$indid,"index",$ind_name);
                        &writeddl(@ddl_array);
                        @ddl_array=&executeddl(@ddl_array);
                     }
                  }
               }
            }
            else {

# CASE 4: table has a clustered index and at least one non-
# clustered index.  ACTION: If its a constraint, drop/recreate and
# then spawn all non-clustered index creations.  If its clustered index
# is truly an index, then drop it and recreate, which forces all non-clustered
# indexes to create.

               #printf "Case 4 table\n";
               if ($status2 & 2) {
                  #printf "its a table level constraint\n";
                  unless ($status2 & 1) {
                     &getddl($ownertable,$indid,"constraint",$ind_name);
                     &writeddl(@ddl_array);
                     @ddl_array=&executeddl(@ddl_array);
                  }

                  for ($i=1; $i<$numindexes; $i++) {
                     ($indid,$ind_name,$status2) = split(" ",@tbl_index[$i]);
                     #printf "its a normal index\n";
                     unless ($indid == 255) {
                        &getddl($ownertable,$indid,"index",$ind_name);
                        &writeddl(@ddl_array);
                        @ddl_array=&executeddl(@ddl_array);
                     }
                  }
               }
               else {
                  #printf "its a normal index\n";
                  &getddl($ownertable,1,"index",$ind_name);
                  &writeddl(@ddl_array);
                  @ddl_array=&executeddl(@ddl_array);
               } 
            } # previous else
         } # if ($numindexes == 1)
      } # foreach $ownertable (@tables)
   } # foreach $db (@dbs)

   &dbclose($dbproc);

} # sub every_table

### Main ###

&populate_sa_pw($server);
$ENV{'DSQUERY'} = "$server"; 
#printf "####### $ENV{'DSQUERY'} ########\n"; 

&every_table;