#!/usr/bin/perl -w
use Data::Dumper;
use Getopt::Long;
use strict;
# MySQL to PostgreSQL dump file converter
#
# For usage: perl mysql2pgsql.perl --help
#
# homepage: http://www.rot13.org/~dpavlin/projects.html
# 1999-12-15 DbP -- Dobrica Pavlinusic <dpavlin@rot13.org>
# 1999-12-26 DbP don't make serial from auto_increment, create all manually
#		 (to set start value right)
# 2000-01-11 DbP now creates sequences with correct value
# 2000-04-25 DbP import into CVS (at cvs.linux.hr)
# 2001-01-29 tpo -- Tomas Pospisek <tpo@sourcepole.ch>:
#		 1) make script comply to usage:
#		 2) make script output to STDOUT instead of STERR
#		 3) change verbosity behaveour
#		 4) add debug option
# see rest of changelog at http://cvs.linux.hr/cvsweb.cgi/sql/mysql2pgsql
# 2003-12-16 jsp -- Joe Speigle <joe.speigle@jklh.us>:
#		converts: s/\) *Type=MyISAM;/);/i, enum data type -> references, 
#		auto_increment->sequences
# 2004-01-13 jsp -- moved project to gborg; both the above declined ownership
# 2004-06-29 converts: year(4), year(2)
# homepage: gborg.postgresql.org 


# command line options
my( $opt_debug, $opt_help);
# general values
my ($out, $size);
# variables for constructing pre-create-table entities
my $create_sql='';	# if empty we are not making a create statement
my %enum_datafield=(); #  holds enumeration choices
my (@column_values,$enum_column, $seq);
my $table="";
my $pre_create_references='';


GetOptions("debug", "help");

my $DEBUG = $opt_debug || 0;
my $HELP = $opt_help || 0;


if (($HELP) || ! defined($ARGV[0]) || ! defined($ARGV[1])) {
	print "Usage: perl $0 {--verbose|--help|--debug} mysql_dump_file.sql pg_dump_file.sql\n";
	print "\t* OPTIONS\n";
	print "\t--verbose tees to pg_dump_file.sql and STDOUT during conversion\n";
	print "\t--debug does ?? \n";
	print "\t--help prints this message \n";
	print "\t* REQUIRED ARGUMENTS\n";
	if (defined ($ARGV[0])) {
		print "\tmysql_dump_file.sql ($ARGV[0])\n";
	} else {
		print "\tmysql_dump_file.sql (undefined)\n";
	}
	if (defined ($ARGV[1])) {
		print "\tpg_dump_file.sql ($ARGV[1])\n";
	} else {
		print "\tpg_dump_file.sql (undefined)\n";
	}
	exit 1;
} 

open(IN,"<$ARGV[0]") || die "can't open mysql dump file $ARGV[0]";
open(OUT,">$ARGV[1]") || die "can't open pg dump file $ARGV[1]";
print OUT "--\n";
print OUT "-- Generated from mysql2pgsql.perl\n";
print OUT "-- (c) 2004, PostgreSQL Inc.\n";
print OUT "--\n";
print OUT "\n";


# create separate table to reference and to hold mysql's enum data type
# values.  All this table's creation before create table
# definition 
# and create a reference to that table in the create table statement
sub convert_enum_to_references_table {
	my ($d, $e, $max_length, $value, $temp, $temp_table_name);
	if ($DEBUG) {
		$d = Data::Dumper->new([\%enum_datafield]);
		print $d->Dump;
	}
	foreach $enum_column (keys %enum_datafield) {
		if ($DEBUG) {
			$e = Data::Dumper->new([$enum_datafield{$enum_column}]);
			print $e->Dump;
		}
		$temp_table_name = ${table} . "_" . ${enum_column} .."_constraint_table";
		$max_length=0;
		@column_values = split /,/, $enum_datafield{$enum_column};
		if ($DEBUG) {
			 print STDERR  "---- column_values = " . $#column_values . "\n";
		}
		# find max length of values in enumeration
		foreach (@column_values) {
			if ($max_length < length($_)) { $max_length = length($_); }
		}
		$pre_create_references .= qq~create table $temp_table_name  ("${enum_column}" varchar( $max_length ) primary key)\\g\n~;
		foreach $value (@column_values) {  
		if ($DEBUG) {
			print STDERR "---- column_value = " . $value . "\n";
		}
			if ($value =~ m/'/) {
			$pre_create_references .= qq~insert into $temp_table_name   values (  $value  )\\g\n~; # ad ' for ints and varchars	
			} else {
				$pre_create_references .= qq~insert into $temp_table_name values ('${value}')\\g\n~; # ad ' for ints and varchars	
			}
		}
	}
	# now append to $pre_create_references the " create table " clause which references $temp_table_name
	print OUT $pre_create_references;
	print OUT $create_sql;
} # END sub convert_enum_to_references_table 

# reset when moving from each "create table" to "insert" part of dump
sub reset_vars() {
	$create_sql="";
	$pre_create_references='';
	# empty %enum_datafield for next " create table" statement 
	%enum_datafield=();
	$enum_column='';
}


while(<IN>) {

	# comments or empty lines
	if (/^#/ || /^$/ || /^--/) { 
		#print OUT $_;
        
		next;
	}
	if (/^USE\s*([^;]*);/) { 
		print OUT "\\c ". $1; 
		next;
	}
	if ($create_sql ne "") { 		# we are inside create table statement so lets process datatypes
		if (/\);/i) {	# end of create table squence
			$create_sql =~ s/,$//g;	# strip last , inside create table
			&convert_enum_to_references_table;
			&reset_vars();
		  next;
		} elsif (/ISAM/i) { # end of create table sequence
			s/\) *Type=MyISAM;/);/i;  
			$create_sql =~ s/,$//g;	# strip last , inside create table
			$create_sql .= $_;
			&convert_enum_to_references_table;
			&reset_vars();
			next;
		}
		if (/(\w*)\s+enum\(((?:['"]\w+['"]\s*,)+['"]\w+['"])\)(.*)$/i) { # enum handling 
			$enum_column=$1;
			$enum_datafield{$enum_column} = $2;  # 'abc','def', ... 
			$_ =  qq~ $1 varchar CONSTRAINT ${table}_${enum_column}_constraint REFERENCES ${table}_${enum_column}_constraint_table (${enum_column}) $3\n~;
		} elsif (/(\w*)\s+year\(4\)(.*)$/i) { # can be integer OR string 1901-2155
			$enum_column=$1;
			my @year_holder = ();
			my $year;
			for (1901 .. 2155) {
					$year = "'$_'";
				unless ($year =~ /2155/) { $year .= ','; }
				 push( @year_holder, $year);
			}
			$enum_datafield{$enum_column} = join('','',@year_holder);   # '1901','1902', ... 
			$_ =  qq~ $1 varchar CONSTRAINT ${table}_${enum_column}_constraint REFERENCES ${table}_${enum_column}_constraint_table (${enum_column}) $2\n~;
		} elsif (/(\w*)\s+year\(2\)(.*)$/i) { # can be integer OR string 1901-2155
			$enum_column=$1;
			my @year_holder = ();
			my $year;
			for (1970 .. 2069) {
				$year = "'$_'";	
				if ($year =~ /2069/) { next; }
				push( @year_holder, $year);
			}
			push( @year_holder, '0000');
			$enum_datafield{$enum_column} = join(',',@year_holder);   # '1971','1972', ... 
			$_ =  qq~ $1 varchar CONSTRAINT ${table}_${enum_column}_constraint REFERENCES ${table}_${enum_column}_constraint_table (${enum_column}) $2\n~;
		} elsif (/^[\s\t]*(\w*)\s*.*int.*auto_increment/i) { 		# int,auto_increment -> serial
			$seq = qq~${table}_${1}_seq~;
			$pre_create_references.="create sequence $seq \;\n\n";
			s/[\s\t]*([a-zA-Z_0-9]*)\s*.*int.*auto_increment[^,]*/$1 integer DEFAULT nextval('$seq'::text) NOT NULL/ig;
			#  MYSQL: data_id mediumint(8) unsigned NOT NULL auto_increment,
			$create_sql.=$_;
			next;
		# int type conversion
		} elsif (/(\w*)int\(\d+\)/i) {
			$size=$1;
			$size =~ tr [A-Z] [a-z];
			if ($size eq "tiny" || $size eq "small") {
				$out = "int2";
			} elsif ($size eq "big") {
				$out = "int8";
			} else {
				$out = "int4";
			}
			s/\w*int\(\d+\)/$out/g;
		}

		# nuke int unsigned
		s/(int\w+)\s+unsigned/$1/gi;

		# blob -> text
		s/\w*blob/text/gi;
		# tinytext/mediumtext -> text
		s/tinytext/text/gi;
		s/mediumtext/text/gi;

		# char -> varchar
		# PostgreSQL would otherwise pad with spaces as opposed
		# to MySQL! Your user interface may depend on this!
		s/\s+char/ varchar/gi;
        s/\s+longtext/ text/gi;

		# nuke date representation (not supported in PostgreSQL)
		s/datetime default '[^']+'/timestamp/i;
        s/datetime default null/timestamp default null/i;
		s/date default '[^']+'/date/i;
		s/time default '[^']+'/time/i;

		# change not null datetime field to null valid ones
		# (to support remapping of "zero time" to null
		s/datetime not null/timestamp not null default now\(\)/i;

		# nuke size of timestamp
		s/timestamp\([^)]*\)/timestamp/i;

		# double -> float8
		s/double\([^)]*\)/float8/i;

        # float(6,2) -> numeric(6,2)
        s/float\((\d+),[\ ]?(\d+)\)/numeric\($1, $2\)/i;
        
        
        # add unique to definition of type (MySQL separates this)
        if (/^\s+unique\ key\ *(\w+)\ *\((\w+)\)/i) {
            #$create_sql .=~ s/($1)([^,]+)/$1$2 unique/i;
            $create_sql .= "constraint ".$1." unique (".$2.")\n";
            #print("constraint ".$1." unique (".$2.")\n");
            next;
        }
        # FIX: unique for multipe columns (col1,col2) are unsupported!
        next if (/\bunique\ key\ *\w+\ *\([a-zA-Z_0-9\-]+\s*,\s*([a-zA-Z_0-9\-]+\s*[,]?\s*)+\)/i);
        next if (/^\s+key\ *\w+\ *\([a-zA-Z_0-9\-\(\)]+\)/i);
        next if (/^\s+key\ *\w+\ *\([a-zA-Z_0-9\-]+\s*,\s*([a-zA-Z_0-9\-]+\s*[,]?\s*)+\)/i);
        
        #  KEY physical_filename (physical_filename(10))

		#if (/^\s+key\b/i && !/^\s+primary\ key\s+/i && !/^\s+unique\ key\s+/i) {
		#	s/KEY(\s+)[^(]*(\s+)/$1 UNIQUE $2/i;		 # hack off name of the non-primary key
            #s/KEY(\s+)[^(]*(\s+)/NONE/i;
        #    print("hey");
		#}


		# fix dates '0000-00-00 00:00:00' (should be null)
		s/default '0000\-00\-00 00:00:00'/\ /gi;
		s/'0000-00-00'/null/gi;
		s/'00:00:00'/null/gi;
		
        

		# quote column names
		s/(^\s*)(\S+)(\s*)/$1"$2"$3/gi if (!/\bkey\b/i);

		# remap colums with names of existing system attribute 
        if (/"oid"/i) {
            s/"oid"/"_oid"/g;
            print STDERR "WARNING: table $table uses column \"oid\" which is renamed to \"_oid\"\nYou should fix application manually! Press return to continue.";
            my $wait=<STDIN>;
        }
        s/oid/_oid/i if (/key/i && /oid/i); # fix oid in key
        $create_sql.=$_;
        } #  END of if ($create_sql ne "") i.e. were inside create table statement so processed datatypes
    else {	# not inside create table
        #---- fix data in inserted data: (from MS world)
        # FIX: disabled for now
		if (00 && /insert into/i) {
			s!\x96!-!g; # --
			s!\x93!"!g; # ``
			s!\x94!"!g; # ''
			s!\x85!... !g;  # \ldots
			s!\x92!`!g;
		}

    if (/^insert\ into\ /i) {
        s/"/'/gi;
        s/'00000000000000'/now()/gi;
        #s/'(\d{4})(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})'/jh/gi;
        s/([12]\d\d\d)([01]\d)([0-3]\d)([0-2]\d)([0-6]\d)([0-6]\d)/$1-$2-$3 $4:$5:$6/;
        #'20040922202151'
    }

		# protect ; in inserts
		#while (/('[^']*);([^']*)'/) {
		#	s/('[^']*);([^']*')/$1 _dotcol_ $2/g;
		#}

		if (/create\s*table/i) {
			$create_sql = $_;
			/create\s*table\s*(\w+)/i;
			$table=$1 if (defined($1));
		} else {
			print OUT $_;
		}
	} # end of if inside create_table
} # END while(<IN>)