move gty1 index creation; update mssql psql conversion

diff --git a/schema/human_9606_schema/human_gty1_indexes_creation.pl b/schema/human_9606_schema/human_gty1_indexes_creation.pl
deleted file mode 100755 (executable)
index c4f0340..0000000
--- a/schema/human_9606_schema/human_gty1_indexes_creation.pl
+++ /dev/null
@@ -1,67 +0,0 @@
-#!/usr/bin/perl
-
-use warnings;
-use strict;
-my @chr = (1..22,
-          qw(AltOnly MT NewSs NotOn PAR Un X Y)
-         );
-my %chr;
-@chr{@chr} = @chr;
-$chr{NewSs} = 'ss';
-
-my %args;
-@args{map {my $a = $_; lc($a)} @ARGV} = 1;
-
-if ($args{drop}) {
-    for my $chr (@chr) {
-       print "DROP TABLE SubInd_ch${chr};\n";
-    }
-    print "DROP TABLE SubInd;\n";
-}
-if ($args{create}) {
-    print <<END;
-CREATE TABLE SubInd (
-       chr VARCHAR NOT NULL ,
-       batch_id int NOT NULL ,
-       subsnp_id int NOT NULL ,
-       submitted_ind_id int NOT NULL ,
-       submitted_strand_code int NULL ,
-       allele_flag int NULL ,
-       gty_id int NULL ,
-       submitted_rs int NULL
-);
-END
-    for my $chr (@chr) {
-       print "CREATE TABLE SubInd_ch${chr} (CHECK (chr = '$chr{${chr}}')) INHERITS (SubInd);\n";
-    }
-}
-if ($args{trigger}) {
-    print <<EOF;
-CREATE OR REPLACE FUNCTION subind_insert_trigger()
-RETURNS TRIGGER AS \$\$
-BEGIN
-EOF
-    for my $chr (@chr) {
-       print <<EOF;
-IF (NEW.chr = '${chr}') THEN
-   INSERT INTO SubInd_ch${chr} VALUES (NEW.*);
-EOF
-       print "ELS";
-    }
-    print <<EOF;
-E
-RAISE EXCEPTION 'chr out of range!';
-END IF;
-RETURN NULL;
-END;
-\$\$
-LANGUAGE plpgsql;
-EOF
-}
-if ($args{index}) {
-    for my $chr (@chr) {
-       print "CREATE INDEX ON SubInd_ch${chr}(subsnp_id);\n";
-       print "CREATE INDEX ON SubInd_ch${chr}(submitted_ind_id,subsnp_id,gty_id);\n";
-    }
-}
-

diff --git a/utils/human_gty1_indexes_creation.pl b/utils/human_gty1_indexes_creation.pl
new file mode 100755 (executable)
index 0000000..c4f0340
--- /dev/null
+++ b/utils/human_gty1_indexes_creation.pl
@@ -0,0 +1,67 @@
+#!/usr/bin/perl
+
+use warnings;
+use strict;
+my @chr = (1..22,
+          qw(AltOnly MT NewSs NotOn PAR Un X Y)
+         );
+my %chr;
+@chr{@chr} = @chr;
+$chr{NewSs} = 'ss';
+
+my %args;
+@args{map {my $a = $_; lc($a)} @ARGV} = 1;
+
+if ($args{drop}) {
+    for my $chr (@chr) {
+       print "DROP TABLE SubInd_ch${chr};\n";
+    }
+    print "DROP TABLE SubInd;\n";
+}
+if ($args{create}) {
+    print <<END;
+CREATE TABLE SubInd (
+       chr VARCHAR NOT NULL ,
+       batch_id int NOT NULL ,
+       subsnp_id int NOT NULL ,
+       submitted_ind_id int NOT NULL ,
+       submitted_strand_code int NULL ,
+       allele_flag int NULL ,
+       gty_id int NULL ,
+       submitted_rs int NULL
+);
+END
+    for my $chr (@chr) {
+       print "CREATE TABLE SubInd_ch${chr} (CHECK (chr = '$chr{${chr}}')) INHERITS (SubInd);\n";
+    }
+}
+if ($args{trigger}) {
+    print <<EOF;
+CREATE OR REPLACE FUNCTION subind_insert_trigger()
+RETURNS TRIGGER AS \$\$
+BEGIN
+EOF
+    for my $chr (@chr) {
+       print <<EOF;
+IF (NEW.chr = '${chr}') THEN
+   INSERT INTO SubInd_ch${chr} VALUES (NEW.*);
+EOF
+       print "ELS";
+    }
+    print <<EOF;
+E
+RAISE EXCEPTION 'chr out of range!';
+END IF;
+RETURN NULL;
+END;
+\$\$
+LANGUAGE plpgsql;
+EOF
+}
+if ($args{index}) {
+    for my $chr (@chr) {
+       print "CREATE INDEX ON SubInd_ch${chr}(subsnp_id);\n";
+       print "CREATE INDEX ON SubInd_ch${chr}(submitted_ind_id,subsnp_id,gty_id);\n";
+    }
+}
+

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;