# #!/bin/perl
#
# conversion HELPER script MSSQL-->PgSQL
# with licencion problem is only text converter for any text
#
#  NO WARRANTY; NO FULL FUNCIONALITY. 
#  GNU GPL 2 LICENCE.
# 
# opensource 277% faster.
# 
# default encoding: utf-8 (do not use ucs-2 !!!)
#
# only for structure. no for datas.
# very _BAD_ perl coding, very ugly hack.
#
# sorry. devel of script is CLOSED.
# need to write next version in yacc & C. 
# pgsql do not yet support storage procedure.
# but script may be HELPfull.
#
# sequences: grep -ai '^.*CREATE SEQUENCE' output.sql | sed 's/^--- \(.*\)/\1/g'
# 
#  convert to unicode (recode windows-1250..u8 ms_dump.sql) .
#  		  [from ucs-2 recode u2..u8 ms_dump.sql]   
#
# inport to pgsql is case insensitive ==> no need to convert names
#
#  usage: perl script.pl ms_dump.sql > output.sql
#         psql -d your_database -f output.sql
#	   and       vim output.sql 
#	              |--> repair error and syntax
#
# create table, function, procedure and alter statement must begin 
#         with string    '[dbo].'  .
#  don't ignore /* */ comments
# 

###################################################################

#   get name of database and begin of block 

    sub Get_Database_Info  {

      my (@input_data) = @_;
      local ($tmp_line);

	foreach $tmp_line (@input_data)   {
	
	    if ($tmp_line =~ /^\s*CREATE\s+DATABASE \[([^\]]+)\]/ )   {

#		return $1;
		print "CREATE DATABASE ".$1." WITH ENCODING=\"UNICODE\";\n";
			return;
	    }
        }
    }


#  basic parsing storage procedure or function
#  procedure or function --> return type
#  fill ';' after blank line:: not implemented
#  get base sql structure and this convert

  sub Parse_Procedure  {

      my (@input_data) = @_;
      local ($tmp_line);
      
        
    foreach $tmp_line (@input_data)   {
    
    # shit [] away
	$tmp_line =~ s/\[([^\]]+)\]/\1/g;
    
    # TEMPORARY table
	
	$tmp_line =~ s/CREATE\s+TABLE\s+#([^\s]+)/CREATE TEMPORARY TABLE \1_temp/g;
	
    #    #table_name--> table_temp
    
	$tmp_line =~ s/\s+(FROM|INTO|UPDATE|TABLE)\s+#([^\s\$\)]+)/ \1 \2\_temp/g;
        
  # TOP--> LIMIT
  
  #  dbo.--> ''
	 $tmp_line =~ s/(\s+)dbo\./\1/g;
  
  #  GO --> away
	$tmp_line =~ s/^GO\s*$//;
   
  # \n<blank_line>\n --> ;\n\n
  
#    $tmp_line =~ s/^([^;]+)$/\1;/;
  
   	print "--- UNSUPPORTED: ".$tmp_line;
   }

   return;

  }
    

#   add keys and references data
# split
# get identification
# expand
# get index
#
  sub Get_Keys_Data     {

      my (@input_data) = @_;
      local ($line);
      local ($tmp_line);
      local ($table_name);

# print "<<<<@input_data>>>>\n";

	$line = join('', @_);
	$line =~ s/\n/ /g;

#      print "[[[[$line]]]]\n";


	if($line =~ /^\s*ALTER\s+TABLE\s+\[dbo\]\.\[([^\]]+)\]\s+ADD\s+(.*)/)   {

	   $table_name = $1;
	   $line = $2;

	}   else   {

		return;
	}

        # split by ','

#	print "$2\n\n";
#	die;

	@tmp_array = split(",", $line);
#	print "LINE: @tmp_array\n";
#	die;

		
	foreach $tmp_line (@tmp_array)   {
		
		$tmp_line =~ s/^\s+//;
		$tmp_line =~ s/\s+$//;

		$tmp_line =~ s/\s{2,}/ /g;

#		print "LINE: $tmp_line\n";

	    if($tmp_line =~ /^CONSTRAINT \[([^\]]+)\] FOREIGN KEY \( \[([^\]]+)\] \) REFERENCES \[dbo\]\.\[([^\]]+)\] \( \[([^\]]+)\] \)/)   {

	   print "ALTER TABLE $table_name ADD CONSTRAINT $1 ";
	   print "FOREIGN KEY $2 REFERENCES $3($4);\n";

	    }    elsif($tmp_line =~ /^CONSTRAINT \[([^\]]+)\] DEFAULT \(([^\)]+)\) FOR \[([^\]]+)\]/)   {

	   print "ALTER TABLE $table_name ALTER COLUMN $3 ";
	   print "SET DEFAULT $2;\n";


	    }   else    {

		print "--- KEY NOT PARSED:: LINE: ALTER TABLE $table_name ADD $tmp_line\n";
	
	    }
		
	}

  }

  #  get index data

    sub Get_Index_Data  {

      my (@input_data) = @_;
      local ($item_number) = 0;
      local ($tmp_line);

# print "<<<<@input_data>>>>\n";

	foreach $tmp_line (@input_data)   {
	
	    if ($tmp_line =~ /^\s*CREATE\s+INDEX\s+\[([^\]]+)\]\s+ON\s+\[dbo\]\.\[([^\]]+)\][(]\[([^#][^\]]+)\][)]/ )   {

		print "CREATE INDEX $1 ON $2($3);\n";
	
	    }

	}
   }


# get data from column of CREATE TABLE statement 
#  convert data types and (NOT NULL\|NULL)

sub Get_Table_Column    {

      my ($column, $table_name) = @_;
      local($column_name) = "";

  # column name
#  print "COLUMN: $column \n";
  
    	   $column =~ s/^\s*//; 

	if($column =~ /^\[([^\]]+)\]\s+/)   {
	
	    print "$1 ";
	    $column_name = $1;
	    
		$column =~ s/^\s*\[([^\]]+)\]\s+//;

	}   else   {

		print "--- column not identificated \n";
		       return;

	}

  # parse data types
    # varchar

	if($column =~ /^\[varchar\]\s+\(([0-9]+)\)/)    {

	  print "character varying($1)";

 # character (length)
	}  elsif($column =~ /^\[char\]\s+\(([0-9]+)\)/)    {

	  print "character ($1)";

# character

      }   elsif($column =~ /^\[char\]\s+/)    {

	  print "character ";

      }   elsif( ($column =~ /^\[timestamp\]\s+/)  or
                   ($column =~ /^\[datetime\]\s+/))    {

	  print "timestamp without time zone";

      }   elsif($column =~ /^\[image\]\s+/)    {

	  print "oid ";

      }   elsif($column =~ /^\[datetime\]\s+/)    {

	  print " ";


   # another data types

        }    elsif($column =~ /^\[([^\]]+)\]/)  {

		    print "$1";

	}  else   {

	     print "--- COLUMN TYPE NOT FOUND";

	}

   # IDENTITY --> CREATE SEQUENCE 

        if($column =~ /\s+IDENTITY\s*\(/)  {

	    print " DEFAULT nextval('".$column_name."_seq')";
	    print "\n--- CREATE SEQUENCE ".$column_name."_seq;\n";

	}

    # is NOT NULL or NULL

	if($column =~ /\s+(NOT NULL|NULL)\s*[,]?$/)    {			
	     print " ".$1."\n";

	} 

	   return;
 }
  

#   dej jmeno tabulky a zacatek bloku 

    sub Get_Table_Data  {

      my (@input_data) = @_;
      local ($item_number) = 0;
      local ($tmp_line);
      local ($table_name);
      

my ($i);
	foreach $tmp_line (@input_data)   {

# print $i++;

	    if ($tmp_line =~ /^\s*CREATE\s+TABLE\s+\[dbo\]\.\[([^#][^\]]+)\]/ )   {

			print "CREATE TABLE ";
			print $1;
			$table_name = $1;
			print " (\n";	

	     }   else   {
   # only line with data
	
	$was_item = 0;

		if($tmp_line =~ /^\s*\[([^\]]+)\]\s+\[([^\]]+)\]/)    {
			
		if($item_number > 0)   {
		
			print ",\n";
		
		}
#		     print $1." ".$2;

			   Get_Table_Column($tmp_line, $table_name);

		     $item_number++;
			$was_item = 1;

		}


	    }
	}
	    
	    print ");\n";
	    return;
	    
    }

 #     get action from block

   sub Get_Action   {

	@data_block = @_;

#	print @data_block;
	print "\n\n";

# decide action

   # decide database
   
	if ( ($database_name eq "")  and
	        (@data_block[0] =~ /^\s*CREATE\s+DATABASE\s+\[([^\]]+)\]/ ) )  {

	    print "---parsing database info\n";
		Get_Database_Info(@data_block);
   # table
   
	} elsif  
	     (@data_block[0] =~ /^\s*CREATE\s+TABLE\s+\[dbo\]\.\[([^#][^\]]+)\]/ )   {
	    print "---parsing table\n";
		Get_Table_Data(@data_block);
   # indexes
	} elsif  
	     (@data_block[0] =~ /^\s*CREATE\s+INDEX\s+/ )   {
	    print "---parsing index\n";
	    Get_Index_Data(@data_block);

   # keys
	} elsif  
	     (@data_block[0] =~ /^\s*ALTER\s+TABLE\s+\[dbo\]\.\[([^\]]+)\]/ )   {
	    print "---parsing keys, default values\n";
	#    print @data_block;
	    Get_Keys_Data(@data_block);

   # procedures
	} elsif  
	     (@data_block[0] =~ /^\s*CREATE\s+PROCEDURE\s+dbo\.([^ ]+)/ )   {
     	
	    print "---parsing storage procedure\n";
		    Parse_Procedure(@data_block);
	    

	} elsif  
	     (@data_block[0] =~ /^\s*CREATE\s+FUNCTION\s+dbo\.([^ ]+)/ )   {
     	
	    print "---parsing function\n";
		    Parse_Procedure(@data_block);

   }   else   {

	printf "---unknown item\n";

   }

  }

  #################### main #######################################

  use utf8;

  $block = 0;
  @data_block = ();
  $database_name = "";
  
# enter file name
  
   open(DATA, $ARGV[0])
      or die ("Could not open input file !");

	print "-- begin dump\n";

    while($line = <DATA>)    {
 
       if($block == 1)  {
#      	    (!$line =~ /^GO/) 

    	  push(@data_block, $line);
       }


     if( ($block != 1) and
          ($line =~ /^\s*(CREATE|ALTER)/)  )  {

		$block = 1;
			push(@data_block, $line);
			print "--begin block\n";
    
#	print "<<<<<<<<<<<<<";
#	print @data_block;
#	print ">>>>>>>>>>>>>>\n\n";

    #  block exists --> decide first
    
    } 
    
    if( ($line =~ /^GO/) and
		($block == 1) )   {

	    Get_Action(@data_block);
    # data process
    #
    	@data_block = ();
	$block = 0;
    
		print "-- end block\n";
	}
	  
    }

	print "-- end dump\n";
		
  close(DATA);