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
