def insert( self, path, delayed = False ):
TablePathAdaptator.insert(self, path, delayed)
self._escapeAntislash(path)
idx = path.range_query.findIdx()
max = path.range_query.getMax()
min = path.range_query.getMin()
strand = path.range_query.isOnDirectStrand()
if delayed:
sql_cmd = 'INSERT DELAYED INTO %s VALUES (%d,%d,"%s",%d,%d,%d)'\
% (self._table_idx,\
path.id,\
idx,\
path.range_query.seqname,\
min,\
max,\
strand)
else:
sql_cmd = 'INSERT INTO %s VALUES (%d,%d,"%s",%d,%d,%d)'\
% (self._table_idx,\
path.id,\
idx,\
path.range_query.seqname,\
min,\
max,\
strand)
self._iDb.execute(sql_cmd)
def deleteFromIdList(self, lNum):
if lNum == []:
return
TablePathAdaptator.deleteFromIdList(self, lNum)
sqlCmd = 'delete from %s where path=%d' % (self._table_idx, lNum[0])
for i in lNum[1:]:
sqlCmd += " or path=%d" % (i)
sqlCmd += ";"
self._iDb.execute(sqlCmd)
def joinTwoPaths(self, id1, id2):
TablePathAdaptator.joinTwoPaths(self, id1, id2)
if id1 < id2:
newId = id1
oldId = id2
else:
newId = id2
oldId = id1
sqlCmd = 'UPDATE %s SET path=%d WHERE path=%d' % (self._table_idx, newId, oldId)
self._iDb.execute(sqlCmd)
return newId
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)
if host == "" or user == "" or passwd == "" or dbname == "":
print "ERROR: missing information about MySQL connection"
sys.exit(1)
db = DbMySql( user, host, passwd, dbname )
if outTable == "":
outTable = "%s_noSSR" % ( qtable )
db.copyTable( qtable, outTable )
qtable = outTable
# prepare the query table
if qtype == "path":
db.createPathIndex( qtable )
qtablePathAdaptator = TablePathAdaptator( db, qtable )
lPathId = qtablePathAdaptator.getIdList()
elif qtype == "set":
db.createSetIndex( qtable )
qtableSetAdaptator = TableSetAdaptator( db, qtable )
lPathId = qtableSetAdaptator.getIdList()
else:
print "ERROR: unknown query table type: %s" % ( qtype )
sys.exit(1)
string = "total number of paths: %i" % ( len(lPathId) )
logging.info( string )
if verbose > 0:
print string
# prepare the subject table
def __init__(self, db, tableName = ""):
TablePathAdaptator.__init__(self, db, tableName)
self._table_idx = "%s_idx" % (self._table)
def deleteFromId(self, num):
TablePathAdaptator.deleteFromId(self, num)
sqlCmd='delete from %s where path=%d;' % (self._table_idx, num)
self._iDb.execute(sqlCmd)
