def createBinPathTable(self, verbose = 0):
if not self._iDb.doesTableExist(self._table):
return
if verbose > 0:
print "creating %s for fast access" % (self._table_idx)
self._iDb.dropTable(self._table_idx)
sql_cmd = "CREATE TABLE %s ( path int unsigned, idx int unsigned, contig varchar(255), min int, max int, strand int unsigned)"% (self._table_idx)
self._iDb.execute(sql_cmd)
sql_cmd = "CREATE INDEX id ON %s ( path );"% (self._table_idx)
self._iDb.execute(sql_cmd)
sql_cmd = "CREATE INDEX ibin ON %s ( idx );"% (self._table_idx)
self._iDb.execute(sql_cmd)
sql_cmd = "CREATE INDEX icontig ON %s ( contig );"% (self._table_idx)
self._iDb.execute(sql_cmd)
sql_cmd = "CREATE INDEX imin ON %s ( min );"% (self._table_idx)
self._iDb.execute(sql_cmd)
sql_cmd = "CREATE INDEX imax ON %s ( max );"% (self._table_idx)
self._iDb.execute(sql_cmd)
sql_cmd = "CREATE INDEX istrand ON %s ( strand );"% (self._table_idx)
self._iDb.execute(sql_cmd)
rangeTable = "%s_range" % (self._table)
self._iDb.dropTable(rangeTable)
self._iDb.path2path_range(self._table)
table = TablePathAdaptator(self._iDb, rangeTable)
if not table.isEmpty():
tmp_file = "%s.tmp%s" % (self._table, str(os.getpid()))
out = open(tmp_file, "w")
contigs = table.getQueryList()
for c in contigs:
lpath = table.getPathListFromQuery(c)
for i in lpath:
idx = i.range_query.findIdx()
max = i.range_query.getMax()
min = i.range_query.getMin()
strand = i.range_query.isOnDirectStrand()
out.write("%d\t%d\t%s\t%d\t%d\t%d\n"%(i.id, idx, i.range_query.seqname, min, max, strand))
out.close()
sql_cmd="LOAD DATA LOCAL INFILE '%s' INTO TABLE %s FIELDS ESCAPED BY '' "%\
(tmp_file, self._table_idx)
self._iDb.execute(sql_cmd)
self._iDb.updateInfoTable(self._table_idx, self._table + " bin indexes")
os.remove(tmp_file)
