Module: Builder

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

Instance Method Summary collapse

Methods included from XlsParser

load_meta_fields

Instance Method Details

#format_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
 
# File 'lib/vardb/snp_db_build.rb', line 7

def format_matrix(type)
  
  host = ConfigData.get_connection

  if type == 'pg'
    conn = PGconn.connect(:host => host[:host], :port => host[:port], :dbname => host[:dbname], :user => host[:user], :password => host[:password])

    puts "formatting annotations table..."
    conn.exec("CREATE TABLE annotations (id numeric(11) PRIMARY KEY, cds varchar(128), transcript varchar(128), transcript_id varchar(128), info text, orientation varchar(128), cds_locus varchar(128), codon_pos varchar(128), codon varchar(128), peptide varchar(128), amino_a varchar(128), syn varchar(128))")

    puts "formatting snps table..."
    conn.exec("CREATE TABLE snps (id numeric(11) PRIMARY KEY, locus numeric(11), annotation_id numeric(11))")

    puts "formatting samples table..."
    conn.exec("CREATE TABLE samples (id numeric(11) PRIMARY KEY, name varchar(128))")

    puts "formatting samples_snps join table..."
    conn.exec("CREATE TABLE samples_snps (sample_id numeric(11), snp_id numeric(11))")

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

    puts "formatting annotations table..."
    db.execute "      create table annotations (\n      id numeric(11) PRIMARY KEY,\n      cds varchar(128),\n      transcript varchar(128),\n      transcript_id varchar(128),\n      info text,\n      orientation varchar(128),\n      cds_locus varchar(128),\n      codon_pos varchar(128),\n      codon varchar(128),\n      peptide varchar(128),\n      amino_a varchar(128),\n      syn varchar(128)\n      );\n      SQL\n\n    puts \"formatting snps table...\"\n    db.execute <<-SQL\n      create table snps (\n      id numeric(11) PRIMARY KEY,\n      locus numeric(11),\n      annotation_id numeric(11)\n      );\n      SQL\n\n    puts \"formatting samples table...\"\n    db.execute <<-SQL\n      create table samples (\n      id numeric(11) PRIMARY KEY,\n      name varchar(128)\n      );\n      SQL\n\n    puts \"formatting sample_snps join table...\"\n    db.execute <<-SQL\n      create table samples_snps (\n      sample_id numeric(11),\n      snp_id numeric(11)\n      );\n      SQL\n\n  end \nend\n"

#format_metadata(type) ⇒ Object 



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
100
101
102
103
104
 
# File 'lib/vardb/snp_db_build.rb', line 75

def format_metadata(type)
  host = ConfigData.get_connection

  
  metadata_fields = XlsParser.load_meta_fields(ConfigData.get_metadata)

  metadata_field_names = ""

  metadata_fields.each do |name|
    name << " varchar(128)"
    metadata_field_names << name
  end

  if type == 'pg'
    conn = PGconn.connect(:host => host[:host], :port => host[:port], :dbname => host[:dbname], :user => host[:user], :password => host[:password])

    puts "formatting sample metadata table..."
    conn.exec("CREATE TABLE sample_metadata (id numeric (11) PRIMARY KEY#{metadata_field_names})")
  
  elsif type == 'sqlite'
    db = SQLite3::Database.new "sqlite_db/#{host[:dbname]}.db"

    puts "formatting sample metadata table..."
    db.execute "      create table sample_metadata (\n        id numeric(11) PRIMARY KEY\#{metadata_field_names}\n      );\n      SQL\n  end\nend\n"