#! /bin/perl

# Name : autoscript.pl
# Usage : autoscript <dbname> <tablename> <infile>
# Description :  Creates data script for the in data file for a table.
# Programmer : Vijay Mitra
# Date : Jul 27 2001
#
#

use DBI;

######################## FEW HASH DEFINES ##################################
$FIELD_TERMINATOR='\t' ;
$INT    ='int' ;
$FLOAT    ='float' ;
$CHAR   ='char' ;
$VARCHAR ='varchar';
$DATETIME = 'datetime' ;
$USERIDTYPE ='user_id_type';
$PRIMARY_KEY ='Primary';
$NULL ='';
$NULLVALUE ='NULL';
$GETDATE = 'getdate()' ;
$END_AND_PATTERN = ' AND $' ;
$YES = 'Y' ;
$DOUBLEQUOTE = '"';
$SINGLEQUOTE ='\'';

### Header line
$OUT_HEADER = "
& Automated script generated by $0
&
& NAME        : $outFile
&           
&           
& DESCRIPTION : This macro creates database data.
&          
&           
& AMENDMENT HISTORY:
&           
& Version no.    Description                            Author    Date
&           
&           
&begin_parameters
    server_name      server_name: string,required
    user_name        user_name: string,required
    password         password: string,hidden,required
    database         database: string,required
&end_parameters

&attach_input

!isql -server_name &server_name& -user_name &user_name& -password &password&
GO

USE &database&
GO

/************************************************************/
/* ---------- CREATE DATA  ---------- */
/************************************************************/

BEGIN TRAN
GO 

";

### Trailer line
$OUT_TRAILER = "

COMMIT TRAN
GO

quit
&detach_input 

";



#===========================================================================
#                     START OF MAIN PROGRAM
#===========================================================================


######################## PARAMETERS CHECK ##################################
### Check the no. of parameters
($progname = $0)        =~ s!^.*/!!;
$progname =~ s/\.pl// ;
die "USAGE: $progname  <dbprefix T/H/C> <tablename> <infile> [overwrite output file y/n] \n" unless ($#ARGV == 2 || $#ARGV == 3) ;
print "INFO : Overwrite indicator not passed , default 'OVERWRITE = y' \n" if ($#ARGV == 2) ;

### Get the parameters
$dbPreFix = `echo $ARGV[0] | tr '[a-z]' '[A-Z]'| cut -c1 `;  ## $ARGV[0] is dbPrefix
$filePreFix = `echo $ARGV[0] | tr '[A-Z]' '[a-z]'| cut -c1 `; ## also get the output file prefix
chomp($dbPreFix);
chomp($filePreFix);
$tableName = $ARGV[1];   ## $ARGV[1] is table prefix
$inFile = $ARGV[2];      ## $ARGV[2] is input file name
if ($#ARGV < 3) {   ## If $ARGV[3] exists then that is $overwite indicator
   $overwrite = $YES ;
}
else {
   $overwrite = $ARGV[3] if ($#ARGV == 3) ; 
   $overwrite = `echo $overwrite | tr '[a-z]' '[A-Z]'| cut -c1 `
}

######################### INPUT FILE CHECK ################################
### Check existence of in file 
open (IN , "$inFile") || die "ERROR : Can't open input file $inFile\n" ;

##################### GET DBI PARAMS AND OPEN CHANNEL #####################
### Check that we have all the environment variables we require
$ENV{DB_USER} and $ENV{DSQUERY} and $ENV{DB_PASW} or
     die ("ERROR : $0 requires environment variables DB_USER, DSQUERY, DB_PASW\n");

### Set database connection parameters
$DB_USER=$ENV{DB_USER};
$DB_SERVER=$ENV{DSQUERY};
$DB_PWD=$ENV{DB_PASW};
$DB_PFIX=$ENV{DB_PFIX};

### Work out our database post-fix (if any)
if ( length $DB_PFIX ) {
     $DB_PFIX = "_$DB_PFIX";
} else {
     die ("ERROR : No database postfix\n");
}

### Our DBI parameters are:
$dsn="dbi:Sybase:server=$DB_SERVER";

### Connect DB
$dbh = DBI->connect ($dsn, $DB_USER, $DB_PWD) || die "ERROR : Database connection not made: $DBI::errstr\n";

########################## DATABASE CHECK #################################
### Select DBs from master..sysdatabases
$sql = "SELECT name FROM master..sysdatabases WHERE name LIKE '\%$DB_PFIX' AND name LIKE '${dbPreFix}\%'" ;
$sth = $dbh->prepare($sql);
$sth->execute();

$db_count = 0;
my @databases;
### Get each row and get the DB
while (@row = $sth->fetchrow_array) {
   $_ = $row[0] ;
   s/\s//g;
   $databases[$db_count++] = $_;
}

### If more than one or no database found then error
die "ERROR : No database found for the DB Prefix '$dbPreFix' entered\n" if ($#databases < 0);
die "ERROR : More than one databases found for the DB Prefix '$dbPreFix' entered\n" if ($#databases > 0);

### Get the database to use
$database = $databases[0];

############################## TABLE CHECK #################################
### Use the database selected
$dbh->do("USE $database") || die "ERROR : Database error $dbh->errstr\n" ;

### Prepare sql to check if table exists
$sql = "SELECT  1  FROM sysobjects WHERE name = '$tableName' AND type = 'U'" ;
$sth = $dbh->prepare($sql);
$sth->execute();

$rowcount =0;
while ($sth->fetchrow_array) {
     $rowcount++;
}

### If more than one or no table found then error
die "ERROR : No '$tableName' table found in '$database' database\n" if ($rowcount == 0);
die "ERROR : More than one '$tableName' table found in '$database' database\n" if ($rowcount >  1);

### Get the table
$table = $tableName;

############################## OPEN OUTPUT FILE #################################
### Formulate & open output file name
$outFile = "${filePreFix}b_${table}.dat";
if ( -e $outFile ) {
    print "INFO : Output file '$outFile' already exists .Overwriting ... \n" if ( $overwrite =~ /$YES/) ;
    die "INFO : Remove the file '$outFile' and try again\n" unless ( $overwrite =~ /$YES/) ;
}

open (OUT , "> $outFile") || die "ERROR : Can't open output file $outFile\n" ;

############################ DECLARE VARS AND CALL SUBROUTINES ##################
### Declare some global variables
@columnDataTypes = ();
@insertStatement = ();
@deleteStatement = ();
@indexColumns = ();


### Call subroutine to get column count
$colCount = &get_table_columns() ;

### Call subroutine to get column data types 
get_column_types($colCount);

### Call subroutine to get column data types 
get_index_columns();

### Call subroutine to read file entries and populate temp table
process_input_file($colCount) ;

### Call subroutine to write to file all the statements     
write_output_file() ;
print "INFO : Script for '$inFile' generated in '$outFile'\n" ;

### Finishing touch
close_and_clear();

#===========================================================================
#                  END OF MAIN PROGRAM
#===========================================================================


#~~~~~~~:-)~~~~~~~~~~~~~~~~~~:-)~~~~~~~~~~~~~~~~~~:-)~~~~~~~~~~~~:-)~~~~~~~~

























#===========================================================================
#                       PRIVATE SUBROUTINES   
#===========================================================================

############################ GET COLUMN COUNT ###############################
sub get_table_columns {
    $sql = "";
    $sql = "SELECT COUNT(*) FROM syscolumns C , sysobjects S WHERE S.name = '$table' AND S.type ='U' AND S.id = C.id " ;
    $sth = $dbh->prepare($sql);
    $sth->execute();
    $rowcount =0;
    while (@row = $sth->fetchrow_array) {
	 $_ = $row[0];
	 s/\s//g;
	 $count = $_;
	 $rowcount ++;
    } ## end of while 
    die "ERROR : No columns found in the table '$table' in '$database' database\n" if ($rowcount == 0) ; ## error if no columns found
    return ($count);
}

############################ GET COLUMN TYPES #################################
sub get_column_types {
  ## For each column get the data type 
  $ColCount = $_[0];
  $c = 0;
  $sql = "";
  while ($c < $colCount) {
       $colid = $c+1;
       $sql = "SELECT T.name , C.name
                FROM  syscolumns C ,
                      sysobjects S ,
                      systypes T
                WHERE S.name = '$table'
                AND S.type ='U'
                AND S.id = C.id
                AND C.usertype = T.usertype
                AND C.colid = $colid ";

       $sth = $dbh->prepare($sql);
       $sth->execute() || die "ERROR : Database error $sth->errstr\n" ;
       while (@row = $sth->fetchrow_array) {
            $_ = $row[0];
            s/\s//g;
       } ## end of while 
       $columnDataTypes[$c] =  $_ ;
       $c ++;
  } ## end of while 
}

############################ PROCESS OUTPUT FILE ###############################
sub write_output_file {
  print OUT $OUT_HEADER ; ## Write the header
  ## Write the records
  for $count (0..$#insertStatement) {
	print OUT $deleteStatement[$count] ;
	print OUT "\n\n";
	print OUT $insertStatement[$count] ;
	print OUT "\n\n\n";
  }
  print OUT $OUT_TRAILER ; ## Write the trailer
}

############################ PROCESS INPUT FILE ###############################
sub process_input_file {
  $colCount = $_[0] ;
  $lineCount = 1;
  $insert_sql = "";

  ## Parse thru each line and get all the fields
  while ($line = <IN>) {
     next if ($line =~ /^$/) ;    ## skip blank lines
     chomp($line) ;
     $cnt =  $line =~ tr/	/	/ ;    ## get no. of cols in this line 
     $cols = $cnt +1 ;
     ## error if col count not same
     die "ERROR : Incorrect column count of $cnt found in line $lineCount instead of $colCount\n" if ( $cols != $colCount) ; 
     @data = split(/$FIELD_TERMINATOR/, $line) ;  ## get the array splitted by field terminator

     ## Populate temp table
     $insert_sql  = "INSERT ${table} SELECT ";
     $delete_sql  = "DELETE ${table} WHERE " ;

     ## Put apostrophes and commas according to data type
     $i = 0;
     while ($i < $cols) {
	 ## Check if the data contains a Double quote change it to a single quote
         if ($data[$i] =~ /$DOUBLEQUOTE/) {
	    print "INFO : Double quote found near '$data[$i]' , line $lineCount, changed to Single quote\n";
	    $_ = $data[$i] ;
	    s/$DOUBLEQUOTE/$SINGLEQUOTE/g ;
	    $data[$i] = $_ ;
	 }

         ## Remove unwanted spaces from the data 
	 ($data[$i] = $data[$i]) =~ s/^\s+// ; ## Remove leading spaces
	 ($data[$i] = $data[$i]) =~ s/\s+$// ; ## Remove trailing spaces

	 ## Assign blank/0 for NULL values 
         if ($data[$i] eq $NULL) {
            if ($columnDataTypes[$i] =~ /$INT/ ) {
                 $data[$i]  = 0;
            }
            elsif ($columnDataTypes[$i] =~ /$FLOAT/ ) {
                 $data[$i]  = 0.0;
            }
            elsif ($columnDataTypes[$i] =~ /$CHAR/ ) {
                 $data[$i]  = " " ;
            }
            elsif ($columnDataTypes[$i] =~ /$VARCHAR/ ) {
                 $data[$i]  = " " ;
            }
            elsif ($columnDataTypes[$i] =~ /$DATETIME/ ) {
                 $data[$i]  = "Jan 1 1900 12:00AM" ;
            }
            elsif ($columnDataTypes[$i] =~ /$USERIDTYPE/ ) {
                 $data[$i]  = " " ;
            }
            else {
                 $data[$i]  = " " ;
            }
            #$data[$i]  = $NULLVALUE ;
         }

	 ## Create the DELETE statement with index columns only
         if ($#indexColumns >= 0)  {
	     for $z (0.. $#indexColumns) {
	        if  ($i == $indexColumns[$z][0] - 1) {
         	   if ($columnDataTypes[$i] =~ /$INT/ ) {
             	       $delete_sql .= "$indexColumns[$z][1]  = $data[$i] AND ";
         	   }
         	   elsif ($columnDataTypes[$i] =~ /$FLOAT/ ) {
             	       $delete_sql .= "$indexColumns[$z][1]  = $data[$i] AND ";
         	   }
         	   elsif ($columnDataTypes[$i] =~ /$CHAR/ ) {
             	       $delete_sql .= "$indexColumns[$z][1]  = \"$data[$i]\" AND ";
         	   }
         	   elsif ($columnDataTypes[$i] =~ /$VARCHAR/ ) {
             	       $delete_sql .= "$indexColumns[$z][1]  = \"$data[$i]\" AND ";
         	   }
         	   elsif ($columnDataTypes[$i] =~ /$DATETIME/ ) {
             	       $delete_sql .= "$indexColumns[$z][1]  = \"$data[$i]\" AND ";
         	   }
         	   elsif ($columnDataTypes[$i] =~ /$USERIDTYPE/ ) {
             	       $delete_sql .= "$indexColumns[$z][1]  = \"$data[$i]\" AND ";
         	   }
         	   else {
             	       $delete_sql .= "$indexColumns[$z][1]  = \"$data[$i]\" AND ";
         	   }
		   last; ## if found break loop
	        } ## end of if
	    } ## end of for
	 }  ## end of if

	 ## Create the INSERT statement
	 ## Assign NULL value for NULL values 
         #if ($data[$i] eq $NULL ) {
	 #    $insert_sql .= $NULLVALUE ;
         #}
	 #else {
	     if ($columnDataTypes[$i] =~ /$INT/ ) {
                 $insert_sql .= " $data[$i]  ";
             }
             elsif ($columnDataTypes[$i] =~ /$FLOAT/ ) {
                 $insert_sql .= " $data[$i]  ";
             }
             elsif ($columnDataTypes[$i] =~ /$CHAR/ ) {
                 $insert_sql .= " \"$data[$i]\"  ";
             }
             elsif ($columnDataTypes[$i] =~ /$VARCHAR/ ) {
                 $insert_sql .= " \"$data[$i]\"  ";
             }
             elsif ($columnDataTypes[$i] =~ /$DATETIME/ ) {
	         ## skip the apostrophes for getdate()
	         if ($data[$i] eq $GETDATE) {    
                     $insert_sql .= " $data[$i]  ";
	         }
	         else {
                     $insert_sql .= " \"$data[$i]\" ";
	         }
             }
             elsif ($columnDataTypes[$i] =~ /$USERIDTYPE/ ) {
                 $insert_sql .= " \"$data[$i]\"  ";
             }
             else {
                 $insert_sql .= " \"$data[$i]\"  "; 
             }
	 #}

         if ($i != $cols-1) {
            $insert_sql .= " , ";
         }
	 $i ++;
     } ## end of while of data types

     ## Remove the last AND from the delete_sql string
     $_ = $delete_sql;
     s/$END_AND_PATTERN//g;
     $delete_sql = $_;
     #$dbh->do($insert_sql) || die "ERROR : Database error $dbh->errstr\n" ;
     $deleteStatement[$lineCount-1] = $delete_sql;
     $insertStatement[$lineCount-1] = $insert_sql;
     $lineCount++;
     $cnt =0;
  }  ## end of while of $line read
}


############################ GET INDEX COLUMNS  ###############################
sub get_index_columns {       
    $sql = "";
    $sql = "EXEC sp_helpindex $table";
    $sth = $dbh->prepare($sql);
    $sth->execute() || die "ERROR : Database error $sth->errstr\n" ;

    ## Get the Primary_Key and break out of loop
    while (@row = $sth->fetchrow_array) {
         $_ = $row[0];
         s/\s//g;
	 $_ = $row[2];
         s/\s//g;
	 @indexes = split(/,/);
	 last ;
    } ## end of while

    #die "ERROR : No indexes found for '$table'\n" if ($#indexes < 0) ;
    ## If indexes found then get the colid and names of them
    if ($#indexes >= 0) {

        ## Get the column nos. and name of the Primary_Key
        $sql = "";
        $cnt =0;
        $sql = "SELECT colid , name FROM $database..syscolumns WHERE id = object_id('$database..$table') AND name IN ( ";
        foreach $_  (@indexes) {
    	    s/\s//g ;
	    $index = $_;
	    $sql .= "'$index'";
	    if ($cnt != $#indexes ) {
	        $sql .= " , ";
	    }
	    else {
	        $sql .= " ) ";
	    }

	    $cnt ++;
        }
        $sth = $dbh->prepare($sql);
        $sth->execute() || die "ERROR : Database error $sth->errstr\n" ;

        ## Get the Primary_Key columns nos 
        $rowcount =0;
        $i =0;
        $j =0;
        while (@row = $sth->fetchrow_array) {
             $_ = $row[0];
             s/\s//g;
	     $colno = $_;
             $_ = $row[1];
             s/\s//g;
	     $colname = $_;
	     ## Put values in array
	     $indexColumns[$i][0] = $colno ;
	     $indexColumns[$i][1] = $colname ;
	     $rowcount ++;
	     $i ++;
        } ## end of while
        die "ERROR : No index columns found for $table\n " if ($rowcount == 0);
    }
    ## Else get the first column only
    else {
        print "INFO : No indexes found for '$table' \n";
        print "INFO : Please check the DELETE statements in the output file before scripting the file\n" ;

        ## Get the column nos. and name of the 1st column
        $sql = "";
        $cnt =0;
        $sql = "SELECT colid , name FROM $database..syscolumns WHERE id = object_id('$database..$table') AND colid = 1";
        $sth = $dbh->prepare($sql);
        $sth->execute() || die "ERROR : Database error $sth->errstr\n" ;

        ## Get the Primary_Key columns nos
        $rowcount =0;
        $i =0;
        $j =0;
        while (@row = $sth->fetchrow_array) {
             $_ = $row[0];
             s/\s//g;
             $colno = $_;
             $_ = $row[1];
             s/\s//g;
             $colname = $_;
             ## Put values in array
             $indexColumns[$i][0] = $colno ;
             $indexColumns[$i][1] = $colname ;
             $rowcount ++;
             $i ++;
        } ## end of while
        die "ERROR : No index columns found for $table\n " if ($rowcount == 0);
    }

}

############################ GET DB NAME() ####################################
sub get_db_name {
    $sql_dbname = "select db_name()  ";
    $sth = $dbh->prepare($sql_dbname);
    $sth->execute() || die "ERROR : Database error $sth->errstr\n" ;
    while (@row = $sth->fetchrow_array) {
         $_ = $row[0];
         s/\s//g;
	 print "IN DATABASE $_ \n";
    } ## end of while
}

############################ CLOSE AND CLEAR ##################################
sub close_and_clear {
    ### Close File handles and clear the DBI channel
    $sth->finish();
    $dbh->disconnect();
    close IN ;
    close OUT;
}