#!/usr/bin/perl -w

#The intend of the file is to take vcl.sql file and parse it change what is needed in order
#for it to be compatible with Apache Derby Database. Changes that are made are described
#along the file where they are addressed.

#Files to be modified
$input = "vcl.sql";
$output = "vcl-derby.sql";

#Get data from files
open(IN1, $input);
@lines = <IN1>;
close(IN1);

#set variable to outputs
$out = "";

#Here are variable needed for the connection for the database
$db = 'vcl1';
$user = 'vcluser';
$pw = 'vclPassowrd';
$out.= "connect 'jdbc:derby:$db;user=$user;password=$pw;';\n";


#modify the data
foreach $line (@lines) {	
	
	#Remove sql_mode
	if($line =~ /SET SQL_MODE=/) {
		$line = "";
	}
	#Remove the IF NOT EXIST as this is not supported in Derby
	if($line =~ /^(.*)(IF NOT EXISTS )(.*)$/) {
		$line = $1.$3."\n";
	}
	#Remove the engine specification, not supported in Derby
	if($line =~ /^(.*)( ENGINE=)/) {
		$line = $1.";\n";
	}
	#Modify the 'auto_increment' command to 'GENERATED BY DEFAULT AS IDENTITY' 
	#Note Derby offer an option here, 'GENERATED ALWAYS AS IDENTITY', however this is compatible
	#with other sql commands in this file
	if($line =~ /^(.*)(auto_increment)(.*)$/) {
		$line = $1."GENERATED BY DEFAULT AS IDENTITY,"."\n";
	}
	#Modify the 'UNIQUE KEY' command to 'UNIQUE'
	if($line =~ /^(.*)(UNIQUE KEY \`[a-zA-Z\d]+\`)(.*)$/) {
		$line = $1."UNIQUE".$3." \n";
	}
	#Remove the 'KEY' command not the sam in Derby
	if($line =~ /^(.*)(  KEY )(.*)$/) {
		$line = "";
	}
	#Change name of columns and tables as they are keywords in Derby
	#'end' -> 'end1'
	#'order' -> 'order1'
	#'key' -> 'key1'
	#'order' -> 'order1'
	if($line =~ /^(.*)(\`(end|order|key|user))(\`)(.*)$/) {
		$line = $1.$2."1".$4.$5."\n";
	}
	#Remove encapsulation of names `` are not needed for Derby
	while($line =~ /^(.*)(\`)([A-Za-z_\d]+)(\`)(.*)$/) {
		$line = $1.$3.$5." \n";
	}
	#Remove keyword 'unsigned' as this is not supported in Derby
	if($line =~ /^(.*)(unsigned)(.*)$/) {
		$line = $1." ".$3." \n";
	}
	#Remove encampsulation of default integer values '' are not needed in Derby
	if($line =~ /^(.*)(default \'(\d+)\')(.*)$/) {
		$line = $1."default ".$3.$4." \n";
	}
	#Remove 'NULL' keyword, it is not needed to specify that a column can be 'NULL'
	if($line =~ /^(.*)(NULL default NULL)(.*)$/) {
		$line = $1."default NULL".$3." \n";
	}
	#Remove 'COMMENT' statement, it is not supported in Derby
	if($line =~ /^(.*)( COMMENT )(.*)$/) {
		$line = $1.",\n";
	}

	#Type changes
	#'tinyint' -> 'int'
	if($line =~ /^(.*)(tinyint\(\d\) )(.*)$/) {
		$line = $1."int ".$3." \n";
	}
	#'mediumint' -> 'int'
	if($line =~ /^(.*)(mediumint\((\d|\d\d)\) )(.*)$/) {
		$line = $1."int".$4." \n";
	}
	#Integer values do not need to specify a size
	if($line =~ /^(.*)(int\((\d|\d\d)\) )(.*)$/) {
		$line = $1."int ".$4." \n";
	}
	#'text' -> 'clob'
	#'mediumtext' -> 'clob'
	#'longtext' -> 'clob'
	if($line =~ /^(.*)( (long|medium|)text)(.*)$/) {
		$line = $1." clob".$4." \n";
	}
	#Enum type is not supported by Derby
	#'enum' -> 'clob'
	if($line =~ /^(.*)(enum\([\'a-zA-Z0-9\,_]+\))(.*)$/) {
		$line = $1."clob".$3." \n";
	}
	#'datetime' -> 'timestamp'
	if($line =~ /^(.*)(datetime)(.*)$/) {
		$line = $1."timestamp".$3." \n";
	}
	#'timestamp' can not be 'NULL'
	if($line =~ /^(.*)(timestamp default )(NULL,)/) {
		$line = $1.$2."\'0000-00-00 00:00:00\',"."\n";
	}
	#'timestamp' needs a default value
	if($line =~ /^(.*)(timestamp NOT NULL)(,)/) {
		$line = $1.$2." default \'0000-00-00 00:00:00\',"."\n";
	}

	#As we changed 'enum' -> 'clob' Derby does not support uniqueness for strings
	#remove the whole line as this may cause problems
	#There should be a restriction in the web-code to prevent this
	if($line =~ /^(.*)(UNIQUE \(resourcegroupid,privnodeid,type\))(.*)$/) {
		$line = "";
	}

	#'timestamp' can not be skipped add NULL or  0001-01-01 xx:xx:xx' for imagerevision
	if($line =~ /^(.*)(\', 0, 1, NULL, \')(.*)$/) {
		$line = $1."', 0, NULL, 1, NULL, '".$3."\n";
	}
	if($line =~ /^(.*)(deleted, )(production)(.*)$/) {
		$line = $1.$2."datedeleted, ".$3.$4."\n";
	}
	#'timestamp' value of '0000-00-00 xx:xx:xx' is not in the range changed to '0001-01-01 xx:xx:xx'
	if($line =~ /^(.*)(0000-00-00)(.*)$/) {
		$line = $1."0001-01-01".$3."\n";
	}

	#Remove 'ON UPDATE CASCADE' feature as it is not supported
	if($line =~ /^(.*)( ON UPDATE CASCADE)(.*)$/) {
		$line = $1.$3."\n";
	}
	#Each 'ADD CONSTRAIN' statement need to be encampsulated in its own 'ALTER TABLE'
	#we end each row of 'ADD CONSTRAIN' statement with ';'
	if($line =~ /^(.*)(ADD CONSTRAIN)(.*)(,)(\s*)$/) {
		$line = $1.$2.$3.";\n";
	}

	#Add lines to intermediate list @mod
	if($line ne "") {
		push(@mod, $line);
	}

	
	
	
}

#Loop throught the list to add chnages that involve several rows
$max = @mod;
$current = "";
for ($i = 0; $i < $max; $i++) {
	$line = $mod[$i];
	$next = "";
	if ($i < $max - 1) {	
		$next = $mod[$i+1];
	}
	
	#Due to the removal of row with keyword 'KEY" some changes need to be made to the cyntax
	#the last statement in the 'CREATE TABLE' should not end with a ','
	if(($line =~ /^(.*)(,)(\s*)$/) && ($next =~ /^\);$/)) {
		$line =~ /^(.*)(,)(.*)$/;
		$line = $1."\n";
	}

	#Each 'ADD CONSTRAIN' statement need to be encampsulated in its own 'ALTER TABLE'
	if($line =~ /^(.*)(ALTER TABLE)(.*)$/) {
		$current = $line;
	}
	if(($line =~ /^(.*)(ADD CONSTRAIN)(.*)$/) && ($mod[$i-1] =~ /^(.*)(ADD CONSTRAIN)(.*)$/)){
		$out.= $current;
	}

	$out.= $line;
}

#Close the databse connection
$out.="\ndisconnect;\n";
$out.= "exit;\n";

#save the new file
open(OUT1, ">".$output);
print OUT1 $out;
close(OUT1);