SWApp/createDb.sql at master · StatGenPRD/SWApp · GitHub

1
2
3
4
5
6
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
drop database if exists swapp;
create database if not exists swapp;
use swapp;

## STOPGAP
#drop table if exists swapp;
#
#create table swapp (
#	id integer not null auto_increment,
#	gene varchar(50) not null,
#	snp_gwas varchar(50) not null,
#	snp_ld varchar(50) not null,
#	msh varchar(250) not null,
#	pvalue double not null,
#	gene_score integer not null,
#	gene_rank_min integer not null,
#	evidence varchar(50) not null,
#    gene_best varchar(50) not null,
#    evidence_best varchar(50) not null,
#	pubmedid integer not null,
#	primary key (id)
#) engine = innodb;
#
#load data local infile 'data/swapp.txt'
#into table swapp
#ignore 1 lines
#(gene, snp_gwas, snp_ld, msh, pvalue, gene_score, gene_rank_min, evidence, gene_best, evidence_best, pubmedid);

# STOPGAP Best LD
drop table if exists swapp.bestld;

create table swapp.bestld (
	id integer not null auto_increment,
	gene varchar(50) not null,
	snp_ld varchar(50) not null,
	snp_gwas varchar(50) not null,
	msh varchar(250) not null,
	pvalue double not null,
	gene_score integer not null,
	gene_rank_min integer not null,
	evidence varchar(50) not null,
    gene_best varchar(50) not null,
    evidence_best varchar(50) not null,
	pubmedid integer not null,
	primary key (id)
) engine = innodb;

load data local infile 'data/swapp.bestld.txt'
into table swapp.bestld
ignore 1 lines
(gene, snp_gwas, snp_ld, msh, pvalue, gene_score, gene_rank_min, evidence, gene_best, evidence_best, pubmedid);

# STOPGAP gene MeSH
drop table if exists swapp.gene_mesh;

create table swapp.gene_mesh (
	id integer not null auto_increment,
	gene varchar(50) not null,
	snp_gwas varchar(50) not null,
	snp_ld varchar(50) not null,
	msh varchar(250) not null,
	pvalue double not null,
	gene_score integer not null,
	gene_rank_min integer not null,
	evidence varchar(50) not null,
    gene_best varchar(50) not null,
    evidence_best varchar(50) not null,
	pubmedid integer not null,
	primary key (id)
) engine = innodb;

load data local infile 'data/swapp.gene.mesh.txt'
into table swapp.gene_mesh
ignore 1 lines
(gene, snp_gwas, snp_ld, msh, pvalue, gene_score, gene_rank_min, evidence, gene_best, evidence_best, pubmedid);