add gcrma data and affymetrix probe annotation loaders

[dbsnp.git] / utils / load_gcrma_data.pl

diff --git a/utils/load_gcrma_data.pl b/utils/load_gcrma_data.pl
new file mode 100755 (executable)
index 0000000..4b42e2e
--- /dev/null
+++ b/utils/load_gcrma_data.pl
@@ -0,0 +1,120 @@
+#!/usr/bin/perl
+
+use warnings;
+use strict;
+
+use DBI;
+
+
+my $dbh = DBI->connect("dbi:Pg:service=snp",'','',{AutoCommit => 1}) or
+    die "Unable to connect to database: ".$DBI::errstr;
+
+my %sth;
+$sth{insert_sample} = $dbh->prepare(<<'END') // die "Unable to prepare insert sample statement: ".$dbh->errstr;
+INSERT INTO gcrma_samples (tissue) VALUES (?);
+END
+
+$sth{select_sample} = $dbh->prepare(<<'END') // die "Unable to prepare select sample statement: ".$dbh->errstr;
+SELECT * FROM gcrma_samples WHERE tissue = ?;
+END
+
+$sth{insert_probe} = $dbh->prepare(<<'END') // die "Unable to prepare insert probe statement: ".$dbh->errstr;
+INSERT INTO affy_probe (probe) VALUES (?);
+END
+
+$sth{select_probe} = $dbh->prepare(<<'END') // die "Unable to prepare select probe statement: ".$dbh->errstr;
+SELECT * FROM affy_probe WHERE probe = ?;
+END
+
+$sth{insert_reading} = $dbh->prepare(<<'END') // die "Unable to prepare insert reading statement: ".$dbh->errstr;
+INSERT INTO gcrma_expression (probe,sample,expression) VALUES (?,?,?);
+END
+
+
+
+my @samples;
+
+use Term::ProgressBar;
+use Fcntl qw(:seek);
+
+my @ifh;
+for my $ifn (@ARGV) {
+    my $ifh = IO::File->new($ifn,'r') or
+       die "Unable to open $ifn for reading: $!";
+    push @ifh,$ifh;
+}
+
+# read avg.csv file
+for my $ifh (@ifh) {
+    my $p;
+    if ($ifh->seek(0,SEEK_END)) {
+        $p = Term::ProgressBar->new({count => $ifh->tell,
+                                     remove => 1,
+                                     ETA=>'linear'});
+        $ifh->seek(0,SEEK_SET);
+    }
+    while (<$ifh>) {
+        chomp;
+        my @line = split /\s*,\s*/;
+        if (not @samples) {
+            shift @line;
+            for my $sample (@line) {
+                push @samples, insert_sample($dbh,\%sth,$sample);
+            }
+            next;
+        }
+        my $probe = insert_probe($dbh,\%sth,shift @line);
+        $dbh->do('COPY gcrma_expression (probe,sample,expression) FROM STDIN;');
+        for (0..$#line) {
+            $dbh->pg_putcopydata("$probe\t$samples[$_]\t$line[$_]\n");
+        }
+        $dbh->pg_putcopyend();
+        if (defined $p) {
+            $p->update($ifh->tell);
+        }
+    }
+}
+
+sub insert_sample{
+    my ($dbh,$sth,$sample) = @_;
+    my $rv = $sth->{insert_sample}->execute($sample) //
+        die "Unable to execute statement properly: ".$dbh->errstr;
+    $sth->{insert_sample}->finish;
+    return select_sample(@_);
+}
+
+sub select_sample {
+    my ($dbh,$sth,$sample) = @_;
+    my $rv = $sth->{select_sample}->execute($sample) //
+        die "Unable to execute statement properly: ".$dbh->errstr;
+    my ($sample_id) = map {ref $_ ?@{$_}:()}
+       map {ref $_ ?@{$_}:()} $sth->{select_sample}->fetchall_arrayref([0]);
+    $sth->{select_sample}->finish;
+    return $sample_id;
+}
+
+sub insert_probe{
+    my ($dbh,$sth,$probe) = @_;
+    my $rv = $sth->{insert_probe}->execute($probe) //
+        die "Unable to execute statement properly: ".$dbh->errstr;
+    $sth->{insert_probe}->finish;
+    return select_probe(@_);
+}
+
+sub select_probe {
+    my ($dbh,$sth,$probe) = @_;
+    my $rv = $sth->{select_probe}->execute($probe) //
+        die "Unable to execute statement properly: ".$dbh->errstr;
+    my ($probe_id) = map {ref $_ ?@{$_}:()}
+       map {ref $_ ?@{$_}:()} $sth->{select_probe}->fetchall_arrayref([0]);
+    $sth->{select_probe}->finish;
+    return $probe_id;
+}
+
+sub insert_reading{
+    my ($dbh,$sth,$probe,$sample,$reading) = @_;
+    my $rv = $sth->{insert_reading}->execute($probe,$sample,$reading) //
+        die "Unable to execute statement properly: ".$dbh->errstr;
+    $sth->{insert_reading}->finish;
+}
+