9 my $dbh = DBI->connect("dbi:Pg:service=snp",'','',{AutoCommit => 1}) or
10 die "Unable to connect to database: ".$DBI::errstr;
13 $sth{insert_sample} = $dbh->prepare(<<'END') // die "Unable to prepare insert sample statement: ".$dbh->errstr;
14 INSERT INTO gcrma_samples (tissue) VALUES (?);
17 $sth{select_sample} = $dbh->prepare(<<'END') // die "Unable to prepare select sample statement: ".$dbh->errstr;
18 SELECT * FROM gcrma_samples WHERE tissue = ?;
21 $sth{insert_probe} = $dbh->prepare(<<'END') // die "Unable to prepare insert probe statement: ".$dbh->errstr;
22 INSERT INTO affy_probe (probe) VALUES (?);
25 $sth{select_probe} = $dbh->prepare(<<'END') // die "Unable to prepare select probe statement: ".$dbh->errstr;
26 SELECT * FROM affy_probe WHERE probe = ?;
29 $sth{insert_reading} = $dbh->prepare(<<'END') // die "Unable to prepare insert reading statement: ".$dbh->errstr;
30 INSERT INTO gcrma_expression (probe,sample,expression) VALUES (?,?,?);
37 use Term::ProgressBar;
42 my $ifh = IO::File->new($ifn,'r') or
43 die "Unable to open $ifn for reading: $!";
50 if ($ifh->seek(0,SEEK_END)) {
51 $p = Term::ProgressBar->new({count => $ifh->tell,
54 $ifh->seek(0,SEEK_SET);
58 my @line = split /\s*,\s*/;
61 for my $sample (@line) {
62 push @samples, insert_sample($dbh,\%sth,$sample);
66 my $probe = insert_probe($dbh,\%sth,shift @line);
67 $dbh->do('COPY gcrma_expression (probe,sample,expression) FROM STDIN;');
69 $dbh->pg_putcopydata("$probe\t$samples[$_]\t$line[$_]\n");
71 $dbh->pg_putcopyend();
73 $p->update($ifh->tell);
79 my ($dbh,$sth,$sample) = @_;
80 my $rv = $sth->{insert_sample}->execute($sample) //
81 die "Unable to execute statement properly: ".$dbh->errstr;
82 $sth->{insert_sample}->finish;
83 return select_sample(@_);
87 my ($dbh,$sth,$sample) = @_;
88 my $rv = $sth->{select_sample}->execute($sample) //
89 die "Unable to execute statement properly: ".$dbh->errstr;
90 my ($sample_id) = map {ref $_ ?@{$_}:()}
91 map {ref $_ ?@{$_}:()} $sth->{select_sample}->fetchall_arrayref([0]);
92 $sth->{select_sample}->finish;
97 my ($dbh,$sth,$probe) = @_;
98 my $rv = $sth->{insert_probe}->execute($probe) //
99 die "Unable to execute statement properly: ".$dbh->errstr;
100 $sth->{insert_probe}->finish;
101 return select_probe(@_);
105 my ($dbh,$sth,$probe) = @_;
106 my $rv = $sth->{select_probe}->execute($probe) //
107 die "Unable to execute statement properly: ".$dbh->errstr;
108 my ($probe_id) = map {ref $_ ?@{$_}:()}
109 map {ref $_ ?@{$_}:()} $sth->{select_probe}->fetchall_arrayref([0]);
110 $sth->{select_probe}->finish;
115 my ($dbh,$sth,$probe,$sample,$reading) = @_;
116 my $rv = $sth->{insert_reading}->execute($probe,$sample,$reading) //
117 die "Unable to execute statement properly: ".$dbh->errstr;
118 $sth->{insert_reading}->finish;