/
db.py
395 lines (308 loc) · 12 KB
/
db.py
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
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
"""Handle database functions."""
import sqlite3
import sys
import os
from os.path import basename, join, exists
ATRAM_VERSION = 'v2.0'
DB_VERSION = '2.0'
BATCH_SIZE = 1e6 # How many sequence records to insert at a time
def connect(blast_db, check_version=False, clean=False):
"""Create DB connection."""
db_name = '{}.sqlite.db'.format(blast_db)
if clean and exists(db_name):
os.remove(db_name)
if check_version and not exists(db_name):
err = 'Could not find the database file "{}".'.format(db_name)
sys.exit(err)
cxn = sqlite3.connect(db_name)
cxn.execute("PRAGMA page_size = {}".format(2**16))
cxn.execute("PRAGMA busy_timeout = 10000")
cxn.execute("PRAGMA journal_mode = OFF")
cxn.execute("PRAGMA synchronous = OFF")
if check_version:
check_versions(cxn)
return cxn
def aux_db(cxn, temp_dir, blast_db, query_name):
"""Create & attach an temporary database to the current DB connection."""
db_dir = join(temp_dir, 'db')
os.makedirs(db_dir, exist_ok=True)
db_name = '{}_{}_temp.sqlite.db'.format(
basename(blast_db), basename(query_name))
db_name = join(db_dir, db_name)
sql = """ATTACH DATABASE '{}' AS aux""".format(db_name)
cxn.execute(sql)
def aux_detach(cxn):
"""Detach the temporary database."""
cxn.execute('DETACH DATABASE aux')
# ########################### misc functions #################################
# DB_VERSION != version in DB. Don't force DB changes until required. So
# this version will tend to lag ATRAM_VERSION.
def check_versions(cxn):
"""Make sure the database version matches what we built it with."""
version = get_version(cxn)
if version != DB_VERSION:
err = ('The database was built with version {} but you are running '
'version {}. You need to rebuild the atram database by '
'running atram_preprocessor.py again.').format(
version, DB_VERSION)
sys.exit(err)
# ########################## metadata table ##################################
def create_metadata_table(cxn):
"""
Create the metadata table.
A single record used to tell if we are running atram.py against the
schema version we built with atram_preprocessor.py.
"""
cxn.execute('''DROP TABLE IF EXISTS metadata''')
sql = 'CREATE TABLE metadata (label TEXT, value TEXT)'
cxn.execute(sql)
with cxn:
sql = '''INSERT INTO metadata (label, value) VALUES (?, ?)'''
cxn.execute(sql, ('version', DB_VERSION))
cxn.commit()
def get_version(cxn):
"""Get the current database version."""
sql = '''SELECT value FROM metadata WHERE label = ?'''
try:
result = cxn.execute(sql, ('version', ))
return result.fetchone()[0]
except sqlite3.OperationalError:
return '1.0'
# ########################## sequences table ##################################
def create_sequences_table(cxn):
"""Create the sequence table."""
cxn.execute('''DROP TABLE IF EXISTS sequences''')
sql = 'CREATE TABLE sequences (seq_name TEXT, seq_end TEXT, seq TEXT)'
cxn.execute(sql)
def create_sequences_index(cxn):
"""
Create the sequences index after we build the table.
This speeds up the program significantly.
"""
sql = 'CREATE INDEX sequences_index ON sequences (seq_name, seq_end)'
cxn.execute(sql)
def insert_sequences_batch(cxn, batch):
"""Insert a batch of sequence records into the database."""
if batch:
sql = '''INSERT INTO sequences (seq_name, seq_end, seq)
VALUES (?, ?, ?)
'''
cxn.executemany(sql, batch)
cxn.commit()
def get_sequence_count(cxn):
"""Get the number of sequences in the table."""
result = cxn.execute('SELECT COUNT(*) FROM sequences')
return result.fetchone()[0]
def get_shard_cut(cxn, offset):
"""Get the sequence name at the given offset."""
sql = 'SELECT seq_name FROM sequences ORDER BY seq_name LIMIT 1 OFFSET {}'
result = cxn.execute(sql.format(offset))
cut = result.fetchone()[0]
return cut
def get_sequences_in_shard(cxn, start, end):
"""Get all sequences in a shard."""
sql = '''
SELECT seq_name, seq_end, seq
FROM sequences
WHERE seq_name >= ?
AND seq_name < ?
'''
return cxn.execute(sql, (start, end))
# ######################## sra_blast_hits table ###############################
def create_sra_blast_hits_table(cxn):
"""
Reset the DB.
Delete the tables and recreate them.
"""
cxn.execute('''DROP TABLE IF EXISTS aux.sra_blast_hits''')
sql = '''
CREATE TABLE aux.sra_blast_hits
(iteration INTEGER, seq_name TEXT, seq_end TEXT, shard TEXT)
'''
cxn.execute(sql)
sql = '''
CREATE INDEX aux.sra_blast_hits_index
ON sra_blast_hits (iteration, seq_name, seq_end)
'''
cxn.execute(sql)
def insert_blast_hit_batch(cxn, batch):
"""Insert a batch of blast hit records into the database."""
if batch:
sql = '''
INSERT INTO aux.sra_blast_hits
(iteration, seq_end, seq_name, shard)
VALUES (?, ?, ?, ?)
'''
cxn.executemany(sql, batch)
cxn.commit()
def sra_blast_hits_count(cxn, iteration):
"""Count the blast hist for select the iteration."""
sql = '''
SELECT COUNT(*) AS count
FROM aux.sra_blast_hits
WHERE iteration = ?
'''
result = cxn.execute(sql, (iteration, ))
return result.fetchone()[0]
def get_sra_blast_hits(cxn, iteration):
"""Get all blast hits for the iteration."""
sql = '''
SELECT seq_name, seq_end, seq
FROM sequences
WHERE seq_name IN (SELECT DISTINCT seq_name
FROM aux.sra_blast_hits
WHERE iteration = ?)
ORDER BY seq_name, seq_end
'''
cxn.row_factory = sqlite3.Row
return cxn.execute(sql, (iteration, ))
def get_blast_hits_by_end_count(cxn, iteration, end_count):
"""Get all blast hits for the iteration."""
sql = '''
SELECT seq_name, seq_end, seq
FROM sequences
WHERE seq_name IN (SELECT seq_name
FROM sequences
WHERE seq_name IN (SELECT DISTINCT seq_name
FROM aux.sra_blast_hits
WHERE iteration = ?)
GROUP BY seq_name
HAVING COUNT(*) = ?)
ORDER BY seq_name, seq_end
'''
cxn.row_factory = sqlite3.Row
return cxn.execute(sql, (iteration, end_count))
# ####################### contig_blast_hits table #############################
def create_contig_blast_hits_table(cxn):
"""Reset the database. Delete the tables and recreate them."""
cxn.execute('''DROP TABLE IF EXISTS aux.contig_blast_hits''')
sql = '''
CREATE TABLE aux.contig_blast_hits
(iteration INTEGER, contig_id TEXT, description TEXT,
bit_score NUMERIC, len INTEGER,
query_from INTEGER, query_to INTEGER, query_strand TEXT,
hit_from INTEGER, hit_to INTEGER, hit_strand TEXT)
'''
cxn.execute(sql)
sql = '''
CREATE INDEX aux.contig_blast_hits_index
ON contig_blast_hits (iteration, bit_score, len)
'''
cxn.execute(sql)
def insert_contig_hit_batch(cxn, batch):
"""Insert a batch of blast hit records into the database."""
if batch:
sql = '''
INSERT INTO aux.contig_blast_hits
(iteration, contig_id, description, bit_score, len,
query_from, query_to, query_strand,
hit_from, hit_to, hit_strand)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
'''
cxn.executemany(sql, batch)
cxn.commit()
def get_contig_blast_hits(cxn, iteration):
"""Get all blast hits for the iteration."""
sql = '''
SELECT iteration, contig_id, description, bit_score, len,
query_from, query_to, query_strand,
hit_from, hit_to, hit_strand
FROM aux.contig_blast_hits
WHERE iteration = ?
'''
cxn.row_factory = sqlite3.Row
return cxn.execute(sql, (iteration, ))
# ####################### assembled_contigs table #############################
def create_assembled_contigs_table(cxn):
"""Reset the database. Delete the tables and recreate them."""
cxn.execute('''DROP TABLE IF EXISTS aux.assembled_contigs''')
sql = '''
CREATE TABLE aux.assembled_contigs
(iteration INTEGER, contig_id TEXT, seq TEXT,
description TEXT, bit_score NUMERIC, len INTEGER,
query_from INTEGER, query_to INTEGER, query_strand TEXT,
hit_from INTEGER, hit_to INTEGER, hit_strand TEXT)
'''
cxn.execute(sql)
sql = '''
CREATE INDEX aux.assembled_contigs_index
ON assembled_contigs (iteration, contig_id)
'''
cxn.execute(sql)
def assembled_contigs_count(cxn, iteration, bit_score, length):
"""Count the blast hist for the iteration."""
sql = '''
SELECT COUNT(*) AS count
FROM aux.assembled_contigs
WHERE iteration = ?
AND bit_score >= ?
AND len >= ?
'''
result = cxn.execute(sql, (iteration, bit_score, length))
return result.fetchone()[0]
def iteration_overlap_count(cxn, iteration, bit_score, length):
"""Count how many assembled contigs match what's in the last iteration."""
sql = '''
SELECT COUNT(*) AS overlap
FROM aux.assembled_contigs AS curr_iter
JOIN aux.assembled_contigs AS prev_iter
ON ( curr_iter.contig_id = prev_iter.contig_id
AND curr_iter.iteration = prev_iter.iteration + 1)
WHERE curr_iter.iteration = ?
AND curr_iter.seq = prev_iter.seq
AND curr_iter.bit_score >= ?
AND prev_iter.bit_score >= ?
AND curr_iter.len >= ?
'''
result = cxn.execute(
sql, (iteration, bit_score, bit_score, length))
return result.fetchone()[0]
def insert_assembled_contigs_batch(cxn, batch):
"""Insert a batch of blast hit records into the database."""
if batch:
sql = '''
INSERT INTO aux.assembled_contigs
(iteration, contig_id, seq, description,
bit_score, len,
query_from, query_to, query_strand,
hit_from, hit_to, hit_strand)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
'''
cxn.executemany(sql, batch)
cxn.commit()
def get_assembled_contigs(cxn, iteration, bit_score, length):
"""
Get all assembled contigs for the iteration.
We will use them as the queries in the next atram iteration.
"""
sql = '''
SELECT contig_id, seq
FROM aux.assembled_contigs
WHERE iteration = ?
AND bit_score >= ?
AND len >= ?
'''
return cxn.execute(sql, (iteration, bit_score, length))
def get_all_assembled_contigs(cxn, bit_score=0, length=0):
"""Get all assembled contigs."""
sql = '''
SELECT iteration, contig_id, seq, description, bit_score, len,
query_from, query_to, query_strand,
hit_from, hit_to, hit_strand
FROM aux.assembled_contigs
WHERE bit_score >= ?
AND len >= ?
ORDER BY bit_score DESC, iteration
'''
cxn.row_factory = sqlite3.Row
return cxn.execute(sql, (bit_score, length))
def all_assembled_contigs_count(cxn, bit_score=0, length=0):
"""Count all assembed contigs."""
sql = '''
SELECT COUNT(*) AS count
FROM aux.assembled_contigs
WHERE bit_score >= ?
AND len >= ?
'''
result = cxn.execute(sql, (bit_score, length))
return result.fetchone()[0]