svn commit: r1440978 - /vcl/site/trunk/content/comm/commprojects/derby-parser.pl

[jf...@apache.org]

Author: jfthomps
Date: Thu Jan 31 14:30:08 2013
New Revision: 1440978

URL: http://svn.apache.org/viewvc?rev=1440978&view=rev
Log:
initial add

Added:
    vcl/site/trunk/content/comm/commprojects/derby-parser.pl   (with props)

Added: vcl/site/trunk/content/comm/commprojects/derby-parser.pl
URL: http://svn.apache.org/viewvc/vcl/site/trunk/content/comm/commprojects/derby-parser.pl?rev=1440978&view=auto
==============================================================================
--- vcl/site/trunk/content/comm/commprojects/derby-parser.pl (added)
+++ vcl/site/trunk/content/comm/commprojects/derby-parser.pl Thu Jan 31 14:30:08 2013
@@ -0,0 +1,195 @@
+#!/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);

Propchange: vcl/site/trunk/content/comm/commprojects/derby-parser.pl
------------------------------------------------------------------------------
    svn:eol-style = native