Repository navigation
Expand file tree
/
Copy pathcreate_pg_tables.sql
More file actions
179 lines (163 loc) · 3.33 KB
/
Copy pathcreate_pg_tables.sql
File metadata and controls
179 lines (163 loc) · 3.33 KB
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
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
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
DROP TABLE IF EXISTS md5s;
CREATE TABLE md5s (
_id SERIAL PRIMARY KEY,
md5 char(32) NOT NULL,
is_protein boolean
);
DROP TABLE IF EXISTS functions;
CREATE TABLE functions (
_id SERIAL PRIMARY KEY,
name text NOT NULL
);
DROP TABLE IF EXISTS organisms_ncbi;
CREATE TABLE organisms_ncbi (
_id SERIAL PRIMARY KEY,
name text NOT NULL,
tax_domain text,
tax_kingdom text,
tax_phylum text,
tax_class text,
tax_order text,
tax_family text,
tax_genus text,
tax_species text,
taxonomy text,
ncbi_tax_id integer
);
DROP TABLE IF EXISTS sources;
CREATE TABLE sources (
_id SERIAL PRIMARY KEY,
name text NOT NULL,
source text,
description text,
type text,
url text,
email text,
link text,
title text,
version text,
download_path text[],
download_file text[],
download_date date NOT NULL DEFAULT CURRENT_DATE,
protein_ids integer,
ontology_ids integer,
rna_ids integer,
md5s integer,
uniq_md5s integer,
organisms integer,
ncbi_organisms integer,
contigs integer,
functions integer
);
DROP TABLE IF EXISTS md5_protein;
CREATE TABLE md5_protein (
_id SERIAL PRIMARY KEY,
md5 char(32) NOT NULL,
id text NOT NULL,
function integer REFERENCES functions(_id),
organism integer REFERENCES organisms_ncbi(_id),
source integer REFERENCES sources(_id)
);
DROP TABLE IF EXISTS md5_ontology;
CREATE TABLE md5_ontology (
_id SERIAL PRIMARY KEY,
md5 char(32) NOT NULL,
id text NOT NULL,
function integer REFERENCES functions(_id),
source integer REFERENCES sources(_id)
);
DROP TABLE IF EXISTS md5_rna;
CREATE TABLE md5_rna (
_id SERIAL PRIMARY KEY,
md5 char(32) NOT NULL,
id text NOT NULL,
function integer REFERENCES functions(_id),
organism integer REFERENCES organisms_ncbi(_id),
source integer REFERENCES sources(_id),
tax_rank integer
);
DROP TABLE IF EXISTS md5_lca;
CREATE TABLE md5_lca (
md5 char(32) NOT NULL,
tax_domain text,
tax_phylum text,
tax_class text,
tax_order text,
tax_family text,
tax_genus text,
tax_species text,
tax_strain text,
level integer
);
DROP TABLE IF EXISTS md5_organism_unique;
CREATE TABLE md5_organism_unique (
md5 integer REFERENCES md5s(_id),
organism integer REFERENCES organisms_ncbi(_id),
source integer REFERENCES sources(_id)
);
DROP TABLE IF EXISTS aliases_protein;
CREATE TABLE aliases_protein (
_id SERIAL PRIMARY KEY,
id text NOT NULL,
alias_id text,
alias_source text
);
DROP TABLE IF EXISTS contigs;
CREATE TABLE contigs (
_id SERIAL PRIMARY KEY,
name text NOT NULL,
description text,
length integer,
organism integer REFERENCES organisms_ncbi(_id)
);
DROP TABLE IF EXISTS id2contig;
CREATE TABLE id2contig (
_id SERIAL PRIMARY KEY,
id text NOT NULL,
contig integer REFERENCES contigs(_id),
strand integer,
low integer,
high integer
);
DROP TABLE IF EXISTS ontology_seed;
CREATE TABLE ontology_seed (
_id SERIAL PRIMARY KEY,
level1 text,
level2 text,
level3 text,
level4 text,
id text
);
DROP TABLE IF EXISTS ontology_kegg;
CREATE TABLE ontology_kegg (
_id SERIAL PRIMARY KEY,
level1 text,
level2 text,
level3 text,
level4 text,
id text
);
DROP TABLE IF EXISTS ontology_eggnog;
CREATE TABLE ontology_eggnog (
_id SERIAL PRIMARY KEY,
level1 text,
level2 text,
level3 text,
id text,
type text
);
DROP TABLE IF EXISTS ontologies;
CREATE TABLE ontologies (
_id SERIAL PRIMARY KEY,
level1 text,
level2 text,
level3 text,
level4 text,
id text,
source integer REFERENCES sources(_id)
);
DROP TABLE IF EXISTS counts;
CREATE TABLE counts (
type text PRIMARY KEY,
count integer
);