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)