| #!/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); |