|
6
|
1 # Copyright INRA (Institut National de la Recherche Agronomique)
|
|
|
2 # http://www.inra.fr
|
|
|
3 # http://urgi.versailles.inra.fr
|
|
|
4 #
|
|
|
5 # This software is governed by the CeCILL license under French law and
|
|
|
6 # abiding by the rules of distribution of free software. You can use,
|
|
|
7 # modify and/ or redistribute the software under the terms of the CeCILL
|
|
|
8 # license as circulated by CEA, CNRS and INRIA at the following URL
|
|
|
9 # "http://www.cecill.info".
|
|
|
10 #
|
|
|
11 # As a counterpart to the access to the source code and rights to copy,
|
|
|
12 # modify and redistribute granted by the license, users are provided only
|
|
|
13 # with a limited warranty and the software's author, the holder of the
|
|
|
14 # economic rights, and the successive licensors have only limited
|
|
|
15 # liability.
|
|
|
16 #
|
|
|
17 # In this respect, the user's attention is drawn to the risks associated
|
|
|
18 # with loading, using, modifying and/or developing or reproducing the
|
|
|
19 # software by the user in light of its specific status of free software,
|
|
|
20 # that may mean that it is complicated to manipulate, and that also
|
|
|
21 # therefore means that it is reserved for developers and experienced
|
|
|
22 # professionals having in-depth computer knowledge. Users are therefore
|
|
|
23 # encouraged to load and test the software's suitability as regards their
|
|
|
24 # requirements in conditions enabling the security of their systems and/or
|
|
|
25 # data to be ensured and, more generally, to use and operate it in the
|
|
|
26 # same conditions as regards security.
|
|
|
27 #
|
|
|
28 # The fact that you are presently reading this means that you have had
|
|
|
29 # knowledge of the CeCILL license and that you accept its terms.
|
|
|
30
|
|
|
31 from commons.core.sql.TableSetAdaptator import TableSetAdaptator
|
|
|
32 from commons.core.coord.SetUtils import SetUtils
|
|
|
33
|
|
|
34 ## Adaptator for Set tables with bin indexes
|
|
|
35 #
|
|
|
36 class TableBinSetAdaptator(TableSetAdaptator):
|
|
|
37
|
|
|
38 ## constructor
|
|
|
39 #
|
|
|
40 # @param iDb DbMySql instance instance of DbMySql
|
|
|
41 # @param tableName string table name (default = "")
|
|
|
42 #
|
|
|
43 def __init__(self, iDb, tableName = ""):
|
|
|
44 TableSetAdaptator.__init__(self, iDb, tableName)
|
|
|
45 self._table_idx = "%s_idx" % (self._table)
|
|
|
46
|
|
|
47 ## Insert a set instance in a set bin table
|
|
|
48 #
|
|
|
49 # @param iSet set instance an instance of set object
|
|
|
50 # @param delayed boolean an insert delayed or not
|
|
|
51 #
|
|
|
52 def insASetInSetAndBinTable(self, iSet, delayed = False):
|
|
|
53 self.insert(iSet, delayed)
|
|
|
54 iSet.seqname = iSet.seqname.replace("\\", "\\\\")
|
|
|
55 iSet.name = iSet.name.replace("\\", "\\\\")
|
|
|
56 bin = iSet.getBin()
|
|
|
57 max = iSet.getMax()
|
|
|
58 min = iSet.getMin()
|
|
|
59 strand = iSet.isOnDirectStrand()
|
|
|
60 sql_prefix = ''
|
|
|
61 if delayed:
|
|
|
62 sql_prefix = 'INSERT DELAYED INTO '
|
|
|
63 else:
|
|
|
64 sql_prefix = 'INSERT INTO '
|
|
|
65 sql_cmd = sql_prefix + '%s VALUES (%d,%f,"%s",%d,%d,%d)'\
|
|
|
66 %(self._table_idx,\
|
|
|
67 iSet.id,\
|
|
|
68 bin,\
|
|
|
69 iSet.seqname,\
|
|
|
70 min,\
|
|
|
71 max,\
|
|
|
72 strand)
|
|
|
73 self._iDb.execute(sql_cmd)
|
|
|
74
|
|
|
75 ## Delete set corresponding to a given identifier number in set and bin set table
|
|
|
76 # @param id integer identifier number
|
|
|
77 # @note old name was delSet_from_num
|
|
|
78 #
|
|
|
79 def deleteFromIdFromSetAndBinTable(self, id):
|
|
|
80 self.deleteFromId(id)
|
|
|
81 sql_cmd = 'delete from %s where path=%d' % (self._table_idx, id)
|
|
|
82 self._iDb.execute(sql_cmd)
|
|
|
83
|
|
|
84 ## Delete path corresponding to a given list of identifier number
|
|
|
85 #
|
|
|
86 # @param lId integer list list of identifier number
|
|
|
87 # @note old name was delSet_from_listnum
|
|
|
88 #
|
|
|
89 def deleteFromListIdFromSetAndBinTable(self, lId):
|
|
|
90 if lId != []:
|
|
|
91 self.deleteFromIdList(lId)
|
|
|
92 sql_cmd = 'delete from %s where path=%d' % (self._table_idx, lId[0])
|
|
|
93 for i in lId[1:]:
|
|
|
94 sql_cmd += " or path=%d" % (i)
|
|
|
95 self._iDb.execute(sql_cmd)
|
|
|
96
|
|
|
97 ## Join two set by changing id number of id1 and id2 path
|
|
|
98 # to the least of id1 and id2
|
|
|
99 #
|
|
|
100 # @param id1 integer id path number
|
|
|
101 # @param id2 integer id path number
|
|
|
102 # @return id integer new id
|
|
|
103 # @note old name was joinSet
|
|
|
104 #
|
|
|
105 def joinTwoSetsFromSetAndBinTable(self, id1, id2):
|
|
|
106 self.joinTwoSets(id1, id2)
|
|
|
107 if id1 < id2:
|
|
|
108 new_id = id1
|
|
|
109 old_id = id2
|
|
|
110 else:
|
|
|
111 new_id = id2
|
|
|
112 old_id = id1
|
|
|
113 sql_cmd = 'UPDATE %s SET path=%d WHERE path=%d'\
|
|
|
114 % (self._table_idx, new_id, old_id)
|
|
|
115 self._iDb.execute(sql_cmd)
|
|
|
116 return new_id
|
|
|
117
|
|
|
118 ## Get a new id number from set bin table
|
|
|
119 #
|
|
|
120 def getNewId(self):
|
|
|
121 sql_cmd = 'select max(path) from %s;' % (self._table_idx)
|
|
|
122 self._iDb.execute(sql_cmd)
|
|
|
123 max_id = self._iDb.fetchall()[0][0]
|
|
|
124 if max_id != None:
|
|
|
125 return int(max_id)+1
|
|
|
126 else:
|
|
|
127 return 1
|
|
|
128
|
|
|
129 ## Get a set list instance between start and end parameters
|
|
|
130 # using the bin scheme
|
|
|
131 #
|
|
|
132 # @param seqName reference seq name
|
|
|
133 # @param start start coordinate
|
|
|
134 # @param end end coordinate
|
|
|
135 # @return lSet set list
|
|
|
136 # @note old name was getSetList_from_qcoord
|
|
|
137 #
|
|
|
138 def getSetListFromQueryCoord(self, seqName, start, end):
|
|
|
139
|
|
|
140 min_coord = min(start,end)
|
|
|
141 max_coord = max(start,end)
|
|
|
142
|
|
|
143 sql_cmd = 'select path from %s where contig="%s" and ('\
|
|
|
144 % (self._table + "_idx", seqName)
|
|
|
145 for i in xrange(8, 2, -1):
|
|
|
146 bin_lvl = pow(10, i)
|
|
|
147 if int(start/bin_lvl) == int(end/bin_lvl):
|
|
|
148 bin = float(bin_lvl + (int(start / bin_lvl) / 1e10))
|
|
|
149 sql_cmd += 'bin=%f' % (bin)
|
|
|
150 else:
|
|
|
151 bin1 = float(bin_lvl + (int(start / bin_lvl) / 1e10))
|
|
|
152 bin2 = float(bin_lvl + (int(end /bin_lvl) / 1e10))
|
|
|
153 sql_cmd += 'bin between %f and %f' % (bin1, bin2)
|
|
|
154 if bin_lvl != 1000:
|
|
|
155 sql_cmd += " or "
|
|
|
156
|
|
|
157 sql_cmd += ") and min<=%d and max>=%d" % (max_coord, min_coord);
|
|
|
158 self._iDb.execute(sql_cmd)
|
|
|
159 res = self._iDb.fetchall()
|
|
|
160 lId = []
|
|
|
161 for i in res:
|
|
|
162 lId.append(int(i[0]))
|
|
|
163 lSet = self.getSetListFromIdList(lId)
|
|
|
164 return lSet
|
|
|
165
|
|
|
166 ## Get a set list instances strictly included between start and end parameters
|
|
|
167 # using the bin scheme
|
|
|
168 #
|
|
|
169 # @param seqName reference seq name
|
|
|
170 # @param start start coordinate
|
|
|
171 # @param end end coordinate
|
|
|
172 # @return lSet set list
|
|
|
173 # @note old name was getInSetList_from_qcoord
|
|
|
174 # @warning the implementation has been changed : I added the two first lines
|
|
|
175 #
|
|
|
176 def getSetListStrictlyIncludedInQueryCoord(self, contig, start, end):
|
|
|
177 min_coord = min(start,end)
|
|
|
178 max_coord = max(start,end)
|
|
|
179 lSet = self.getSetListFromQueryCoord(contig, start, end)
|
|
|
180 lSetStrictlyIncluded = []
|
|
|
181 for iSet in lSet:
|
|
|
182 if iSet.getMin() > min_coord and \
|
|
|
183 iSet.getMax() < max_coord:
|
|
|
184 lSetStrictlyIncluded.append(iSet)
|
|
|
185
|
|
|
186 return lSetStrictlyIncluded
|
|
|
187
|
|
|
188 ## Get a list of the identifier Id contained in the table bin
|
|
|
189 #
|
|
|
190 # @return lId list of int list of identifier
|
|
|
191 # @note old name was getSet_num
|
|
|
192 #
|
|
|
193 def getIdList(self):
|
|
|
194 sql_cmd = 'select distinct path from %s;' % (self._table_idx)
|
|
|
195 self._iDb.execute(sql_cmd)
|
|
|
196 res = self._iDb.fetchall()
|
|
|
197 lId = []
|
|
|
198 for t in res:
|
|
|
199 lId.append(int(t[0]))
|
|
|
200 return lId
|
|
|
201
|
|
|
202 ## Get a list of the query sequence name contained in the table bin
|
|
|
203 #
|
|
|
204 # @return lSeqName list of string list of query sequence name
|
|
|
205 # @note old name was getContig_name
|
|
|
206 #
|
|
|
207 def getSeqNameList(self):
|
|
|
208 sql_cmd = 'select distinct contig from %s;' % (self._table_idx)
|
|
|
209 self._iDb.execute(sql_cmd)
|
|
|
210 res = self._iDb.fetchall()
|
|
|
211 lSeqName = []
|
|
|
212 for t in res:
|
|
|
213 lSeqName.append(t[0])
|
|
|
214 return lSeqName
|
|
|
215
|
|
|
216 ## Insert a Set list with the same new identifier in the table bin and set
|
|
|
217 #
|
|
|
218 # @note old name was insAddSetList
|
|
|
219 #
|
|
|
220 def insertListInSetAndBinTable(self, lSets, delayed = False):
|
|
|
221 id = self.getNewId()
|
|
|
222 SetUtils.changeIdInList( lSets, id )
|
|
|
223 for iSet in lSets:
|
|
|
224 self.insASetInSetAndBinTable(iSet, delayed)
|
|
|
225
|
|
|
226 ## Insert a set list instances In table Bin and Set and merge all overlapping sets
|
|
|
227 #
|
|
|
228 # @param lSets reference seq name
|
|
|
229 # @note old name was insMergeSetList
|
|
|
230 #
|
|
|
231 def insertListInSetAndBinTableAndMergeAllSets(self, lSets):
|
|
|
232 min, max = SetUtils.getListBoundaries(lSets)
|
|
|
233 oldLSet = self.getSetListFromQueryCoord(lSets[0].seqname, min, max)
|
|
|
234 oldQueryhash = SetUtils.getDictOfListsWithIdAsKey(oldLSet)
|
|
|
235 qhash = SetUtils.getDictOfListsWithIdAsKey(lSets)
|
|
|
236 for lNewSetById in qhash.values():
|
|
|
237 found = False
|
|
|
238 for currentId, oldLsetById in oldQueryhash.items():
|
|
|
239 if SetUtils.areSetsOverlappingBetweenLists(lNewSetById, oldLsetById):
|
|
|
240 oldLsetById.extend(lNewSetById)
|
|
|
241 oldLsetById = SetUtils.mergeSetsInList(oldLsetById)
|
|
|
242 self.deleteFromIdFromSetAndBinTable(currentId)
|
|
|
243 found = True
|
|
|
244 if not found:
|
|
|
245 self.insertListInSetAndBinTable(lNewSetById)
|
|
|
246 else:
|
|
|
247 id = self.getNewId()
|
|
|
248 SetUtils.changeIdInList(oldLsetById, id)
|
|
|
249 self.insertListInSetAndBinTable(oldLsetById)
|
|
|
250
|
|
|
251 ## Insert a set list instances In table Bin and Set after removing all overlaps between database and lSets
|
|
|
252 #
|
|
|
253 # @param lSets reference seq name
|
|
|
254 # @note old name was insDiffSetList
|
|
|
255 #
|
|
|
256 def insertListInSetAndBinTableAndRemoveOverlaps(self, lSets):
|
|
|
257 min, max = SetUtils.getListBoundaries(lSets)
|
|
|
258 oldLSet = self.getSetListFromQueryCoord(lSets[0].seqname, min, max)
|
|
|
259 oldQueryHash = SetUtils.getDictOfListsWithIdAsKey(oldLSet)
|
|
|
260 newQueryHash = SetUtils.getDictOfListsWithIdAsKey(lSets)
|
|
|
261 for lNewSetById in newQueryHash.values():
|
|
|
262 for lOldSetById in oldQueryHash.values():
|
|
|
263 if SetUtils.areSetsOverlappingBetweenLists(lNewSetById, lOldSetById):
|
|
|
264 lNewSetById = SetUtils.getListOfSetWithoutOverlappingBetweenTwoListOfSet(lOldSetById, lNewSetById)
|
|
|
265 self.insertListInSetAndBinTable(lNewSetById)
|
