use strict;
my $VERSION="1.00";

my $schema_fn = $ARGV[0] || "schemadump_pu.txt";
my $output_fn = $ARGV[1] || "mysql_create_pu.sql";

print <<WHATIDO;
$0 V$VERSION
This program changes a file created with from Centura Sqltalk 
by the command:

   unload schema c:\\temp\\schema.txt

Please call this program like this:

   $0 schema.txt mysqloutput.sql

Inputfile is now: $schema_fn
Outputfile is now: $output_fn

Press RETURN to continue...
WHATIDO
my $dummy = <STDIN>;

# There must be a schema file
die "Could not find necessary schema-file $schema_fn" unless -e $schema_fn;

my @sql = &sqlfromcenturaschema(fn=>$schema_fn);
@sql = &centurasql2mysql(@sql);

print "Writing $output_fn\n";
open (OUT, ">$output_fn");
print OUT "$_\n" foreach @sql;
close OUT;
exit;

sub centurasql2mysql {
	my @sql = @_; 
	my @mysql;

	my $killuser = "SYSADM";
	foreach (@sql) {
		s/^\s+//;
		# I do not need several commands
		next if /^set loadversion/i;
		next if /^grant /i;
		next if /^create public synonym/i;
		next if /^create view/i;
		next if /^store/i;
		next if /^set /i;
		next if /^dbattribute /i;


		# the word comment is quoted: comment -> "comment"
		s/\"comment\"/comment/igs;

		# change datatypes
		# 
		s/double precision/double/ig;	
		s/long varchar/double/ig;	
		s/ char\(/ varchar(/ig;
	
		# I don't need pctfree 10
		s/\s+pctfree\s+\d+\s*//i;
		# Schema in Centura is with a username which owns the tables
		# like this: create table sysadm.address (...
		s/^(create|alter)( table )(\w+\.)(.+)$/$1$2$4/igs;
		# Centurasyntax: create unique index sysadm.indname1 
		#                on sysadm.tablename
		# Mysqlsyntax: same without username
		s/^(create( unique){0,1} index )(\w+\.)(.+)( on )(\w+\.)(.+)/$1$4$5$7/igs;
		# Centurasyntax: alter table xx primary key...
		# Mysql:         alter table xx add primary key...
		s/^(alter table )(\w+)( primary key)/$1$2 add $3/igs;
		# Mysql likes to have a semicolon at the end of its 
		# commands
		s/$/;/;

		push @mysql, $_;
	}
	return @mysql;
}


sub sqlfromcenturaschema {
	# read a schemafile from gupta created like this:
	# unload schema c:\temp\create.sql
	my (%args) = @_;
	print "Reading $args{fn}\n";
	open SCH, $args{fn} || die "Could not open $args{fn}";
	# read file until "/" in a new line
	my @sql;
	my $line=undef;
	while (<SCH>) {
		# kill dos-newlines
		s/\x0a//g;
		s/\x0d//g;
		# / in a new line means that this command is ready
		# next command will begin in the next line
		# save this whole command in the list @sql
		if ( /^\// ) {
			push @sql, $line;
			$line = undef;
			next;
		}
		$line .= "$_\n";
	}
	close SCH;
	return @sql;
}

