move gty1 index creation; update mssql psql conversion

[dbsnp.git] / utils / mssql_psql_conversion.pl

diff --git a/utils/mssql_psql_conversion.pl b/utils/mssql_psql_conversion.pl
index d7aea89f9d3228ebcb4a71586de2dba850f56751..0882bb3990ef7b29ab25a6278280b2b1166d4153 100755 (executable)
--- a/utils/mssql_psql_conversion.pl
+++ b/utils/mssql_psql_conversion.pl
@@ -1,19 +1,40 @@
 #!/usr/bin/perl
 
+#require perl5.10;
+
 use warnings;
 use strict;
 
+my $temp = '';
 while (<>) {
-    s/\[([^]]+)\]/"$1"/g;
-    s/^\s*GO\s*$/;/;
+    # only add "" if we actually need them
+    s/\[([^]]+)\]/$1 =~ m{\W}?qq("$1"):$1/eg;
+    s/^\s*GO$/;/;
     s/tinyint/smallint/;
     s/binary(?:\s*\([^)]+\))?/bytea/ig;
     s/smalldatetime/TIMESTAMP/ig;
     s/DATETIME/TIMESTAMP/ig;
-    s/(?:NON)?CLUSTERED//g;
+    s/(?:NON)?CLUSTERED\s*//g;
+    s/\s*ASC\s*//g;
     s/int\s*IDENTITY\s*\(\d+,\s*\d+\)/SERIAL/ig;
     # mssql uses stupid names for indexes apparently; ditch them and
     # let pgsql choose
     s/(CREATE\s+(?:UNIQUE\s+)?INDEX\s+)\S+\s+(ON\s+)/$1$2/gi;
-    print $_;
+    # set defaults properly
+#    use re 'debug';
+    s/(?<altertable>ALTER\s+TABLE\s+\S+) # table name
+      \s+ADD\s+CONSTRAINT\s+
+      \S+\s+DEFAULT\s+
+      (?<function>\((?<unbraceddef>(?:[^\(\)]++|(?&function))*)\)) # default value
+      \s+FOR\s+
+      (?<column>\S+) # column
+      /$+{altertable} ALTER COLUMN $+{column} SET DEFAULT $+{unbraceddef};/gix;
+    s/GETDATE\(\)/NOW()/gix;
+    $temp .= $_;
 }
+$temp =~ s/\r//g;
+
+# cleanup \n; madness
+$temp =~ s/\n(;)/$1/g;
+$temp =~ s/\n\n+/\n/g;
+print $temp;