From 6e51d2d339036d36e6294b1bbed27e5c18a6a2f5 Mon Sep 17 00:00:00 2001
From: Don Armstrong <don@donarmstrong.com>
Date: Mon, 12 Dec 2011 13:57:56 -0800
Subject: [PATCH] move gty1 index creation; update mssql psql conversion

---
 .../human_gty1_indexes_creation.pl            |  0
 utils/mssql_psql_conversion.pl                | 29 ++++++++++++++++---
 2 files changed, 25 insertions(+), 4 deletions(-)
 rename {schema/human_9606_schema => utils}/human_gty1_indexes_creation.pl (100%)

diff --git a/schema/human_9606_schema/human_gty1_indexes_creation.pl b/utils/human_gty1_indexes_creation.pl
similarity index 100%
rename from schema/human_9606_schema/human_gty1_indexes_creation.pl
rename to utils/human_gty1_indexes_creation.pl
diff --git a/utils/mssql_psql_conversion.pl b/utils/mssql_psql_conversion.pl
index d7aea89..0882bb3 100755
--- 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;
-- 
2.39.5