Module: Populator

Includes:
XlsParser
Included in:
Vardb
Defined in:
lib/vardb/database_populator.rb

Instance Method Summary collapse

Methods included from XlsParser

load_meta_fields

Instance Method Details

#populate_matrix(type) ⇒ Object 



7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
 
# File 'lib/vardb/database_populator.rb', line 7

def populate_matrix(type)

  host = ConfigData.get_connection

  if type == 'pg'
#PG Connection
conn = PGconn.connect(:host => host[:host], :port => host[:port], :dbname => host[:dbname], :user => host[:user], :password => host[:password])
  
#Matrix File PG Command Preparation
conn.prepare('load_snps', 'INSERT INTO snps (id, locus, annotation_id) values ($1, $2, $3)')
conn.prepare('load_annos', 'INSERT INTO annotations (id, cds, transcript, transcript_id, info, orientation, cds_locus, codon_pos, codon, peptide, amino_a, syn ) values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)')
conn.prepare('load_samples_snps', 'INSERT INTO samples_snps (sample_id, snp_id) values ($1, $2)')
conn.prepare('load_samples', 'INSERT INTO samples (id, name) values ($1, $2)')

  elsif type == 'sqlite'
#SQLite Connection
db = SQLite3::Database.new "sqlite_db/#{host[:dbname]}.db"

  end



  #Matrix File Load-ins
  text=File.open(ConfigData.get_matrix).read

  linenum = 1
  sample_number = 1

  snps = []
  anno_tabs = []
  anno_vals = []

  text.each_line do |line|
(header, line_data) = line.split(' ', 2)
if (header == '#snp_pos')
  puts "populating snps table..."
  snps = line_data.split("\t")
  snp_counter = 1
  snps.each do |locus|
    if type == 'pg' 
      conn.exec_prepared('load_snps', [snp_counter, locus, snp_counter])
    elsif type == 'sqlite'
      db.execute("INSERT INTO snps (id, locus, annotation_id) VALUES (?,?,?)", [snp_counter, locus, snp_counter])
    end
    snp_counter += 1
  end  
elsif (header == '#annotation')
  puts "populating annotations table..."
  anno_tabs = line_data.split("\t")
  anno_tabs.each { |tab| anno_vals << tab.split(',', 11) }
  anno_counter = 1
        anno_vals.each do |anno|
         anno.insert(0, anno_counter)
         if anno[1].match('intergenic')
           if type == 'pg'
              conn.exec_prepared('load_annos', [ anno[0], 0, 0, 0,  anno[1], 0, 0, 0, 0, 0, 0, 0 ])
            elsif type == 'sqlite'
              db.execute("INSERT INTO annotations (id, cds, transcript, transcript_id, info, orientation, cds_locus, codon_pos, codon, peptide, amino_a, syn ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)", [ anno[0], 0, 0, 0,  anno[1], 0, 0, 0, 0, 0, 0, 0 ])
            end
            else 
              if type == 'pg'
                  conn.exec_prepared('load_annos', [ anno[0], anno[1], anno[2], anno[3], anno[4], anno[5], anno[6], anno[7], anno[8], anno[9], anno[10], anno[11] ])
                elsif type == 'sqlite'
                  db.execute("INSERT INTO annotations (id, cds, transcript, transcript_id, info, orientation, cds_locus, codon_pos, codon, peptide, amino_a, syn ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)", [ anno[0], anno[1], anno[2], anno[3], anno[4], anno[5], anno[6], anno[7], anno[8], anno[9], anno[10], anno[11] ])
                end  
            end
            anno_counter += 1  
        end
else
  if sample_number == 1 then
      puts "loading reference..."
    else
      puts "loading in sample #{sample_number - 1}..."
  end
  if type == 'pg'
    conn.exec_prepared('load_samples', [sample_number, header])
  elsif type == 'sqlite'
    db.execute("INSERT INTO samples (id, name) VALUES (?,?)", [sample_number, header])
  end
  line_data.split("\t").each_with_index do |n, i|
    if (n == '1')
      if type == 'pg'
        conn.exec_prepared('load_samples_snps', [sample_number, i])
      elsif type == 'sqlite'
        db.execute("INSERT INTO samples_snps (sample_id, snp_id) VALUES (?,?)", [sample_number, i])
      end
    end
       end
       sample_number += 1
end
    linenum += 1
  end
end

#populate_metadata(type) ⇒ Object 



101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
 
# File 'lib/vardb/database_populator.rb', line 101

def populate_metadata(type)

  host = ConfigData.get_connection

  #Excel Spreadsheet Command Preparaton
  metadata_fields = XlsParser.load_meta_fields(ConfigData.get_metadata)

  metadata_fields_string = "id "

  metadata_fields.each do |item|
metadata_fields_string << item
  end

  metadata_values_string = "$1 "

  metadata_fields.length.times do |i|
    metadata_values_string << ", $#{i+2}"
  end

  if type == 'pg'
#PG Connection
conn = PGconn.connect(:host => host[:host], :port => host[:port], :dbname => host[:dbname], :user => host[:user], :password => host[:password])
conn.prepare('load_metadata', "INSERT INTO sample_metadata (#{metadata_fields_string}) values (#{metadata_values_string})")
  elsif type == 'sqlite'
#SQLite Connection
db = SQLite3::Database.new "sqlite_db/#{host[:dbname]}.db"
  end

  #Excel Spreadsheet Load-ins
  s = Roo::Excel.new(ConfigData.get_metadata)
  s.default_sheet = s.sheets.first

  row = 2

  puts "populating sample metadata..."

  until s.cell(row, 1).nil?
row_contents = ["#{row-1}"]
metadata_fields.length.times do |i|
  row_contents << "#{s.cell(row, i)}"
end
if type == 'pg'
  conn.exec_prepared('load_metadata', row_contents)
elsif type == 'sqlite'
  db.execute("INSERT INTO sample_metadata (#{metadata_fields_string}) VALUES (#{metadata_values_string})", row_contents)
end
row += 1
  end
end