move gty1 index creation; update mssql psql conversion

[dbsnp.git] / utils / human_gty1_indexes_creation.pl

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";
+    }
+}
+