-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathdb.txt
104 lines (90 loc) · 3.5 KB
/
db.txt
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
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
create table protein(
UniProtAccession varchar(20),
GeneName varchar(20),
GOBP text,
GOCC varchar(2000),
GOMF varchar(2000),
primary key (UniProtAccession)
);
create table disease(
MADID varchar(20),
DiseaseName varchar(500),
DiseaseCategory varchar(100),
ExternalDiseaseIDs varchar(1000),
MeSHTreeNumber varchar(500),
primary key (MADID)
);
create table protein_disease_association(
ProteinDiseaseAssociationID varchar(20),
UniProtAccession varchar(20),
MADID varchar(20),
primary key (ProteinDiseaseAssociationID),
constraint fk_disease_association foreign key (MADID) references disease(MADID),
constraint fk_uniprot_association foreign key (UniProtAccession) references protein(UniProtAccession)
);
create table nucleotide_variation(
MPMutationID varchar(20),
dbSNPID varchar(20),
AminoAcidChange varchar(300),
NucleotideVariation varchar(300),
UniProtAccession varchar(20),
MADID varchar(20),
SourceID varchar(1000),
primary key (MPMutationID),
constraint fk_disease_nv foreign key (MADID) references disease(MADID),
constraint fk_uniprot_nv foreign key (UniProtAccession) references protein(UniProtAccession)
);
create table expression(
DiseaseProteinExpressionID varchar(20),
ExpressionVariation varchar(50),
ExpressionMolecule varchar(50),
Sample varchar(100),
Method varchar(200),
UniProtAccession varchar(20),
MADID varchar(20),
Remarks varchar(500),
PMID varchar(40),
primary key (DiseaseProteinExpressionID),
constraint fk_disease_exp foreign key (MADID) references disease(MADID),
constraint fk_uniprot_exp foreign key (UniProtAccession) references protein(UniProtAccession)
);
create table study_metadata(
StudyID varchar(30),
DiseaseCategory varchar(100),
DiseaseName varchar(500),
Sample varchar(200),
FoldChangeSubjects varchar(200),
ConditionState varchar(500),
GEOAccession varchar(20),
primary key (StudyID, DiseaseCategory)
);
create table kegg_pathway(
PathwayProteinInteractionID varchar(20),
UniProtAccession varchar(20),
Pathway varchar(1000),
PathwayID varchar(20),
AdjustedPvalue double,
primary key (PathwayProteinInteractionID),
constraint fk_uniprot_kegg_pathway foreign key (UniProtAccession) references protein(UniProtAccession)
);
create table mitocarta_pathway(
PathwayProteinInteractionID varchar(20),
UniProtAccession varchar(20),
Pathway varchar(1000),
primary key (PathwayProteinInteractionID),
constraint fk_uniprot_mitocarta_pathway foreign key (UniProtAccession) references protein(UniProtAccession)
);
create database mpld;
grant all on mpld.* to 'sudipto'@'localhost';
SET GLOBAL local_infile=1;
quit
sudo mysql --local-infile=1
use mpld;
load data local infile '/home/sudipto/data/projects/mpld/input/protein.csv' into table protein ignore 1 lines;
load data local infile '/home/sudipto/data/projects/mpld/input/disease.csv' into table disease ignore 1 lines;
load data local infile '/home/sudipto/data/projects/mpld/input/protein_disease_association.csv' into table protein_disease_association ignore 1 lines;
load data local infile '/home/sudipto/data/projects/mpld/input/snp.csv' into table nucleotide_variation ignore 1 lines;
load data local infile '/home/sudipto/data/projects/mpld/input/expression.csv' into table expression ignore 1 lines;
load data local infile '/home/sudipto/data/projects/mpld/input/study_metadata.csv' into table study_metadata ignore 1 lines;
load data local infile '/home/sudipto/data/projects/mpld/input/Pathway/KEGG.csv' into table kegg_pathway ignore 1 lines;
load data local infile '/home/sudipto/data/projects/mpld/input/Pathway/MitoCarta.csv' into table mitocarta_pathway ignore 1 lines;