Satellog Database Documentation

APPENDIX D

Downloading and populating the GeneNote tables in Satellog

@@@@@@@@@@@@@@@@@

@ GeneNote Data @

@@@@@@@@@@@@@@@@@

Get the GeneNote dataset from GEO: http://www.ncbi.nlm.nih.gov/geo/ Dataset ID: GSE803

# Execute

/home/perseusm/genenote/genenote_parser.pl GSE803.txt

######################

# genenote_parser.pl #

######################

#!/usr/bin/perl

# genenote_parser.pl

# usage: genenote_parser.pl GSE803.txt

# Perseus Missirlis - Jan 15, 2004

use strict;

use DBI;

####################

# Global Variables #

####################

# DBI

my ($dsn) = "DBI:mysql:schz_db:athena.bcgsc.ca";

my ($user_name) = "schz_rw";

my ($password) = "repeat";

my ($dbh, $sth);

my (@ary);

#######################

# Connect to Database #

#######################

$dbh = DBI->connect ($dsn, $user_name, $password, { RaiseError => 1 });

##############################################################################

# read infile specified on the cmd line                                      #

# read multiple coords of CAG/CTG repeat locations from STDIN one line at a time # ##############################################################################

my $tissue;

my $array;

my $number;

my $last_insert_id;

while (<>) {

  chomp;

if ($_ =~ /\!Sample_title\s+\=\s+Normal\s+((\S+\s+\S+)|\S+)\s+\S+\s+(\S+)\s+\S+\s+(\S+)/) {

       $tissue       = $1;

       $array = $3;

       $number       = $4;

       } elsif ($_ =~ /(\S+)\s+(\d+\.\d+)\s+(\S)/) {

       my $id_ref = $1;

       my $value = $2;

       my $call = $3;

#      print "id_ref $id_ref value $value call $call tissue $tissue array $array number $number\n";

       $sth = $dbh->prepare ("INSERT INTO GeneNote VALUES('NULL','$id_ref','$value','$call','$tissue','$array','$number')");

                        $sth->execute ();

                     $last_insert_id = $sth-> {mysql_insertid};

                     print "The id of the last record inserted into the db is $last_insert_id\n";

       }

print "outside of loop this is the last record $last_insert_id\n\n"; }

##############

# End script #

##############

This will populate the GeneNote database and have it ready for future queries.