#!/usr/bin/perl

use strict;

# -----------------------------------------
# --- Used to check through the code of stored procedures
# ---  to see which stored procedures are used
# --- Also looks at the local tables that are used by
# ---  the stored procedures.
# -----------------------------------------

# ---- define constants for database names
use constant CDB_NAME       => "database_1..";   # -- current DB
use constant CDB_OTHER_NAME => "database_2..";  # -- other DB used
use constant CDB_DBO_NAME   => "dbo.";        # -- some sp and tables use this 

# ---- stored procedures that start with these are system owned 
use constant CDB_SYS_1_NAME => "dt_";
use constant CDB_SYS_2_NAME => "sp_sys_";


my $db_name;   
$db_name = CDB_NAME;
print STDERR "===== This database is named $db_name\n";
$db_name = CDB_OTHER_NAME;
print STDERR "===== The other database is named $db_name\n";

# -----------------------------------------
# ----- Some variables to dump debug info
# -----------------------------------------
my $debugging;    $debugging = 0;
my $debugging_1;  $debugging_1 = 0;    # --- used to check comment processing
my $debugging_2;  $debugging_2 = 0;    # --- used to check word processing

# -----------------------------------------
# ---- define output file names
# -----------------------------------------
my $sp_contents_name; $sp_contents_name = "results_SP_info.txt";
my $sp_final_name;    $sp_final_name = "results_final_info.txt";
my $sp_error_name;    $sp_error_name = "results_errors.txt";

# -----------------------------------------
# ---- define constants for comments
# -----------------------------------------
use constant CSTYLE_EOL => "--";    # --- These are "until end of line" comments
use constant CSTYLE_START_BLOCK => "/*";  # -- starts a comment block
use constant CSTYLE_END_BLOCK => "*/";    # -- ends a comment block

# -----------------------------------------
# --- Requires 3 input files:
# ---   the dump of all stored procedures
# ---   the dump of all local tables
# ---   a listing of all stored procedure code
# -----------------------------------------

# --- The 3 files we use as inputs
if ($#ARGV != 2) 
{
  print "process_sp_data <sp names> <table names> <sp listing>\n"; 
  die "Need to pass in 3 file names";
}

my $sp_name_file;     $sp_name_file = $ARGV[0];
my $table_name_file;  $table_name_file = $ARGV[1];
my $sp_listing_file;  $sp_listing_file = $ARGV[2];

# ---- Make sure the user knows which files we are using
print STDERR "   Stored procedure names from $sp_name_file \n";
print STDERR "   Table names from $table_name_file \n";
print STDERR "   Stored procedure listing from $sp_listing_file \n";

open (SPNAMES, "<$sp_name_file") || die "Couldn't open SP names $sp_name_file";
open (TABLENAMES, "<$table_name_file") || die "Couldn't open Table names $table_name_file";
open (SPDATA, "<$sp_listing_file") || die "Couldn't open SP data $sp_listing_file";

# ---- Everybody reads strings into $readline
my $readline;

# ---- provide a general array for string splitting
my @arrayX;

# -----------------------------------------
# ----- A: read all stored procs into a pair of hashes
# -----------------------------------------
# ---- The hash declarations
my %sp_orig_hash;
my %sp_compare_hash;

$readline = <SPNAMES>;    # -- eat the header name
while ($readline = <SPNAMES>)
{
  # --- The data is full of nonprinting characters 
  # --- so eliminate them. (w = words, d = digits, s = spaces)
#  $readline =~ s/[^\w\d\s]//g;

  $readline =~ s/\r\n/\n/g;    # -- handle possible CR-LF case
  chomp $readline;             # -- eliminate the \n 

  # --- Format should be: (tab-delimited)
  # ---  procname  <junk>  
  my $spname;

  (@arrayX) = split (/\t/, $readline);
  $spname = $arrayX[0];
  if (length($spname) < 1) { print STDERR "Bad spname? $readline\n"; }

  # --- lower case it for comparison purposes
  $spname =~ tr/A-Z/a-z/;

  # ---- SUPPRESSION of SYSTEM stored processes
  if ((index($spname, CDB_SYS_1_NAME) == 0) ||
      (index($spname, CDB_SYS_2_NAME) == 0))
  {
     print STDERR "Suppressing system stored process: $spname\n";
  }
  else
  {
    # --- now save it (and the original name)
    $sp_orig_hash {$spname} =  $arrayX[0];
    $sp_compare_hash {$spname} = 0;
  }
}
close SPNAMES;

# -----------------------------------------
# ----- B: read all table names into a pair of hashes
# -----------------------------------------
# ---- The hash declarations
my %tbl_orig_hash;
my %tbl_compare_hash;

$readline = <TABLENAMES>;    # -- eat the header name
while ($readline = <TABLENAMES>)
{
  # --- The data is full of nonprinting characters 
  # --- so eliminate them. (w = words, d = digits, s = spaces)
#  $readline =~ s/[^\w\d\s]//g;

  $readline =~ s/\r\n/\n/g;    # -- handle possible CR-LF case
  chomp $readline;             # -- eliminate the \n 

  # --- Format should be: (tab-delimited)
  # ---  tablename  <junk>  
  my $t_name;

  (@arrayX) = split (/\t/, $readline);
  $t_name = $arrayX[0];
  # --- lower case it for comparison purposes

  $t_name =~ tr/A-Z/a-z/;
  # --- now save it (and the original name)
  $tbl_orig_hash {$t_name} =  $arrayX[0];
  $tbl_compare_hash {$t_name} = 0;
}
close TABLENAMES;

# -----------------------------------------
# ----- C: At this point we have master hashes of
# -----    stored procedures and tables
# ----- So now we want to actually process the file
# -----------------------------------------

# --- Open output file
open (OUTFILE, ">  $sp_contents_name");
open (ERRORFILE, "> $sp_error_name");

# ---------------------------------------------------
# --- A "create procedure xxxxx" indicates start of stored_proc
# --- A "go" indicates the end of a stored_proc
# --- Comments are indicated by  "/* */" and "--"
# ---------------------------------------------------

# ---- state variables
my $sp_found;       $sp_found = 0;
my $comment_block;  $comment_block = 0;

my $curr_sp_name;   $curr_sp_name = "";

# --- A pair of hashes to store tables and hashes 
# ---  found in a given StoredProc
my %found_table_hash;  %found_table_hash = ();
my %found_sp_hash;    %found_sp_hash = ();

# --- And a master hash of the stored procs we have dumped info on
my %sp_processed_hash;    %sp_processed_hash = ();

# ---- Define variables used in while loop
my $check_line;
my $cr_index;
my $pr_index;
my $go_found;
my @sp_name_array;

while ($readline = <SPDATA>)
{
  # --- The data is full of nonprinting characters 
  # --- so eliminate them. (w = words, d = digits, s = spaces)
  # ---  We also want to see . / * -
##  $readline =~ s/[^\w\d\s\.\-\*\/]//g;

  $readline =~ s/\r\n/\n/g;    # -- handle possible CR-LF case
  chomp $readline;             # -- eliminate the \n 

  # -- convert every line to lowercase (makes searching 
  # --  easier and stored_procs aren't case sensitive)
  $readline = lc ($readline);

  # --- Often times a ( ends up NEXT to a stored proc or table name
  # --- so we will add a space before them
  $readline =~ s/\(/ \(/g;

  if ($debugging) 
  {
    my $temp_val;
    $temp_val = length($readline);
    print STDERR "X: ==$readline== length = $temp_val\n";
  }

  # ------------------------------------------------------------
  # -- A. check for two types of comments
  # --    NOTE: Will NOT handle "/* */ /* */" type comments
  # --     (ie a pair of full comment blocks in one line)
  # ------------------------------------------------------------
  $check_line = $readline;
  my $check_index;
  my $check_2_index;
  my $comment_line;

  # ---- If in a comment block - 
  # ----  Look for */ -
  # ----     if found eliminate all through that (and turn off block)
  # ----     else eliminate entire line (since it is commented out)
  if ($comment_block == 1)
  {
    $check_index =  index($readline, CSTYLE_END_BLOCK);
    if ($check_index > -1)
    {
      $check_index += 2;  # --- eliminate the */
      $readline = substr($readline, $check_index);
      $comment_block = 0;
    }
    else { $readline = ""; }   # --- whole line is in comment block
  }
  if ($debugging_1) 
  {  
    print STDERR "EOC check: =$readline= (CB = $comment_block)\n"; 
  } 
   
  # ---- If not in a comment block - 
  # ----  Look for /* -
  # ----     if found eliminate all after that (to a */)
  # ----        and if there is NO */, then set comment block on
  if ($comment_block == 0)
  {
    $check_index =  index($readline, CSTYLE_START_BLOCK);
    if ($check_index > -1)
    {
       # --- Save the comment part and the valid data
       $comment_line = substr ($readline, ($check_index + 2)); 
       if ($check_index == 0) { $readline = ""; }  # -- At start of line
       else { $readline = substr ($readline, 0, $check_index); }

       # -- see if there is an ending */
       $check_2_index =  index($comment_line, CSTYLE_END_BLOCK); 
       if ($check_2_index > -1)
       {
         $readline .=  " ";
         $check_2_index += 2;  # --- eliminate the */
         $readline .= substr ($comment_line, $check_2_index);
       } 
       else { $comment_block = 1; }   # -- set the comment block indicator
    }
  } 

  if ($debugging_1) 
  {  
    print STDERR "BOC check: =$readline= (CB = $comment_block)\n"; 
  }
 
  # --- Finally handle the -- style comments
  # --- We have defined these as CSTYLE_EOL
  $check_index =  index($readline, CSTYLE_EOL);
  if ($check_index > -1)
  {
     # --- Save everything before the --
     $readline = substr ($readline, 0, $check_index); 
  }

  if ($debugging) 
  { 
    print STDERR "cmt chk: =$readline=  (CB = $comment_block)\n"; 
  } 

  # ------------------------------------------------------------
  # -- B. check for "create procedure xxxxx" - 
  # ---- This indicates a stored proc name
  # ------------------------------------------------------------

  # -- Returns location of search string
  # --  -1 means not found
  $cr_index = index($readline, "create");
  $pr_index = index($readline, "procedure");

  if (($cr_index > -1) && ($pr_index > -1) &&
      ($pr_index > $cr_index))
  {
    # --- ERROR CHECK : If $sp_found == 1 then we thought
    # ---  we were still within a stored proc - missed a GO
    if ($sp_found == 1)
    {
      print STDERR "ERROR? $curr_sp_name didn't have a GO\n";
      dump_stored_proc_data();
    }

    # --- sometimes these end in the form [sp_name]
    # --- so we'll eliminate the []
    $readline =~ s/\[//g;
    $readline =~ s/\]//g;

    @sp_name_array = split (/\s+/, $readline);
    # -- Array index 2 should contain the stored procedure name
    print STDERR "   ===> start stored proc: $sp_name_array[2]\n";
  
    # ---- Assign the name 
    $curr_sp_name = $sp_name_array[2]; 

    # --- Strip off a leading dbo. on the name
    if (index($curr_sp_name, CDB_DBO_NAME) == 0)
    {
       $curr_sp_name = substr ($curr_sp_name, length(CDB_DBO_NAME));
    }

    # --- Mark that we are WITHIN a stored proc
    $sp_found = 1;

    # --- And clear the readline (since there SHOULDN'T be anything else)
    $readline = "";

    # --- For cheap thrills - see if this stored_proc is in the hash
    if (!exists($sp_compare_hash{$curr_sp_name}))
    {
      print STDERR "   HMMM - stored proc $curr_sp_name not in the sp hash!\n";
      print ERRORFILE " $curr_sp_name has a DIFFERENT external name\n";
    }
    else { $sp_processed_hash{$curr_sp_name}++; }
  }

  # ------------------------------------------------------------
  # -- C. Check for a GO
  # --- This indicates the END of the stored proc
  # ------------------------------------------------------------
  @arrayX = split (/\s+/, $readline);

  $go_found = 0;
  my $ix;
  for ($ix = 0; $ix <= $#arrayX; $ix++)
  {
###    print "   --$arrayX[$ix]-- \n";
    if ($arrayX[$ix] eq "go") { $go_found = 1; }
  }
 
  if ($go_found == 1)
  {
    if ($debugging) {  print STDERR "  ====> GO found in $readline\n"; }

    # --- If we were processing a stored proc, then dump results
    if ($sp_found == 1) 
    { 
      dump_stored_proc_data(); 
    }
    else  
    {
      if ($debugging) {  print STDERR "   GO found w/o a stored proc active\n"; }
    }
  } 
  
  # ------------------------------------------------------------
  # -- D. Look for stored proc and table names  
  # ---   only need to do this if in a stored proc
  # ------------------------------------------------------------
  if ($sp_found == 1) 
  {
    @arrayX = split (/\s+/, $readline);

    for ($ix = 0; $ix <= $#arrayX; $ix++)
    {
       my $curr_word;
       $curr_word = $arrayX[$ix];

       if ($debugging_2) { print STDERR "  >>>> $curr_word <<< A\n"; }

       # --- see if word has the database name on the front
       if (index($curr_word, CDB_NAME) == 0)
       {
          # -- strip it off (cause it isn't needed)
          $curr_word = substr ($curr_word, (length(CDB_NAME)));
          if ($debugging_2) { print STDERR "  >>>> $curr_word <<< B\n"; }
       }

       # --- see if word has the dbo name on the front
       # --- In this case we will handle database.dbo.whatever as 
       # ---  well as dbo.whatever
       my $dbo_index; 
       $dbo_index = index($curr_word, CDB_DBO_NAME);
       if ($dbo_index > -1)
       {
          # -- strip it off (cause it isn't needed)
          # -- if dbo.xxxx then eliminate the dbo.
          if ($dbo_index == 0)
          {
            $curr_word = substr ($curr_word, (length(CDB_DBO_NAME)));
          }
          else  # ---- assume that it is a reference to a table
          {
            $found_table_hash{$curr_word}++;
          }
          if ($debugging_2) { print STDERR "  >>>> $curr_word <<< B\n"; }
       }

       # --- see if word has the other database name on the front
       if (index($curr_word, CDB_OTHER_NAME) == 0)
       {
          # --- if so, then it is a reference to a table
          # ---  so save it on the table hash
          $found_table_hash{$curr_word}++;
          if ($debugging_2) { print STDERR "  >>>> Other DB table <<<\n"; }
       }
     
       # --- see if word is in either of the hashes
       # --- First the stored procedures
       if (exists($sp_compare_hash{$curr_word}))
       {
          $found_sp_hash{$curr_word}++;
          $sp_compare_hash{$curr_word}++;
          if ($debugging_2) { print STDERR "  >>>>  DB stored proc <<<\n"; }
       }

       # --- Then the tables
       if (exists($tbl_compare_hash{$curr_word}))
       {
          $found_table_hash{$curr_word}++;
          $tbl_compare_hash{$curr_word}++;
          if ($debugging_2) { print STDERR "  >>>>  DB table <<<\n"; }
       }
    }
  }
}  # ------- END of reading in and processing file

# -------------------------------------------------
# --- dump the final process (shouldn't happen)
# -------------------------------------------------
if ($sp_found == 1) 
{
  print STDERR "ERROR? Dumped a final stored proc without a GO\n"; 
      dump_stored_proc_data(); 
}


# -------------------------------------------------
# ---- Dump a file of ALL the SP and Tables used
# ---- Mark potential orphans
# -------------------------------------------------
close OUTFILE;    # --- close file we are dumping individual SP info to

# ---- open our new file
open (OUTFILE, ">  $sp_final_name");
$db_name = CDB_NAME;
print OUTFILE "Results for stored procs and tables in $db_name\n\n";

my $key;
my $orphan_val;
my $count_value;
my $val_out_name;

print OUTFILE "\n";
print OUTFILE "==============================================\n";
print OUTFILE "STORED PROCEDURES\n";
print OUTFILE "==============================================\n";
foreach $key (sort keys %sp_compare_hash)
{
  if (exists($sp_orig_hash{$key})) { $val_out_name = $sp_orig_hash{$key}; }
  else  { $val_out_name = $key; }

  $count_value = $sp_compare_hash{$key};
  if ($count_value == 0) { $orphan_val = "ORPHAN?"; }
  else { $orphan_val = "       "; }

  print OUTFILE "$orphan_val\t$val_out_name\t$count_value\n";
}

print OUTFILE "\n";
print OUTFILE "==============================================\n";
print OUTFILE "TABLES\n";
print OUTFILE "==============================================\n";
foreach $key (sort keys %tbl_compare_hash)
{
  if (exists($tbl_orig_hash{$key})) { $val_out_name = $tbl_orig_hash{$key}; }
  else  { $val_out_name = $key; }

  $count_value = $tbl_compare_hash{$key};
  if ($count_value == 0) { $orphan_val = "ORPHAN?"; }
  else { $orphan_val = "       "; }

  print OUTFILE "$orphan_val\t$val_out_name\t$count_value\n";
}
close OUTFILE;

# -------------------------------------------------
# ---- Now output the final errors
# -------------------------------------------------
print ERRORFILE "\n";
print ERRORFILE "==============================================\n";
print ERRORFILE "The following stored procedures are misnamed internally\n\n";

foreach $key (sort keys %sp_compare_hash)
{
  if (!exists($sp_processed_hash{$key}))
  {
    print ERRORFILE "   $key\n";
  }
}

# ========================================================
# ---- Define subroutines below here
# ========================================================

# --------------------------------------------------
# --- Dump the found SP and Tables for current SP
# --- Reset the major variables
# --------------------------------------------------
sub dump_stored_proc_data 
{
  print OUTFILE "==============================================\n";
  print OUTFILE "STORED PROCEDURE: $curr_sp_name\n";
  print OUTFILE "==============================================\n";
  print OUTFILE "Calls these stored procedures:\n";

  my $key;
  my $tbl_out_name;
  my $sp_out_name;

  # --- Dump SP info
  foreach $key (sort keys %found_sp_hash)
  {
    if (exists($sp_orig_hash{$key})) { $sp_out_name = $sp_orig_hash{$key}; }
    else  { $sp_out_name = $key; }

    print OUTFILE "\t$sp_out_name\t$found_sp_hash{$key}\n";
  }

  print OUTFILE "--------------------------------------------\n";
  print OUTFILE "Calls these tables\n";

  # --- Dump Table info
  foreach $key (sort keys %found_table_hash)
  {
    if (exists($tbl_orig_hash{$key})) { $tbl_out_name = $tbl_orig_hash{$key}; }
    else  { $tbl_out_name = $key; }

    print OUTFILE "\t$tbl_out_name\t$found_table_hash{$key}\n";
  }
  print OUTFILE "\n";

  # --- Reset the major variables
  $sp_found = 0;
  $comment_block = 0;

  $curr_sp_name = "";
  %found_table_hash = ();
  %found_sp_hash = ();
}