|
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
|
|
|
32 from commons.core.sql.ITableSetAdaptator import ITableSetAdaptator
|
|
|
33 from commons.core.sql.TableAdaptator import TableAdaptator
|
|
|
34 from commons.core.coord.Set import Set
|
|
|
35
|
|
|
36
|
|
|
37 ## Adaptator for a Set table
|
|
|
38 #
|
|
|
39 class TableSetAdaptator( TableAdaptator, ITableSetAdaptator ):
|
|
|
40
|
|
|
41 ## Give a list of Set instances having a given seq name
|
|
|
42 #
|
|
|
43 # @param seqName string seq name
|
|
|
44 # @return lSet list of instances
|
|
|
45 #
|
|
|
46 def getListFromSeqName( self, seqName ):
|
|
|
47 sqlCmd = "SELECT * FROM %s" % (self._table)
|
|
|
48 colum2Get, type2Get, attr2Get = self._getTypeColumAttr2Get(seqName)
|
|
|
49 sqlCmd += " WHERE " + colum2Get
|
|
|
50 sqlCmd += " = "
|
|
|
51 sqlCmd = sqlCmd + type2Get
|
|
|
52 sqlCmd = sqlCmd % "'" + attr2Get + "'"
|
|
|
53 lSet = self._iDb.getObjectListWithSQLCmd( sqlCmd, self._getInstanceToAdapt )
|
|
|
54 return lSet
|
|
|
55
|
|
|
56 ## Give a list of set instances overlapping a given region
|
|
|
57 #
|
|
|
58 # @param query string query name
|
|
|
59 # @param start integer start coordinate
|
|
|
60 # @param end integer end coordinate
|
|
|
61 # @return lSet list of set instances
|
|
|
62 #
|
|
|
63 def getListOverlappingCoord(self, query, start, end):
|
|
|
64 sqlCmd = 'select * from %s where chr="%s" and ((start between least(%d,%d) and greatest(%d,%d) or end between least(%d,%d) and greatest(%d,%d)) or (least(start,end)<=least(%d,%d) and greatest(start,end)>=greatest(%d,%d))) ;' % (self._table, query, start, end, start, end, start, end, start, end, start, end, start, end)
|
|
|
65 lSet = self._iDb.getObjectListWithSQLCmd( sqlCmd, self._getInstanceToAdapt )
|
|
|
66 return lSet
|
|
|
67
|
|
|
68 #TODO: to test !!!
|
|
|
69 ## Give a list of Set instances overlapping a given region
|
|
|
70 #
|
|
|
71 # @note whole chains are returned, even if only a fragment overlap with the given region
|
|
|
72 # @param query string query name
|
|
|
73 # @param start integer start coordinate
|
|
|
74 # @param end integer end coordinate
|
|
|
75 # @return lSets list of Path instances
|
|
|
76 #
|
|
|
77 def getChainListOverlappingCoord(self, query, start, end):
|
|
|
78 sqlCmd = "select distinct path from %s where chr='%s' and ((start between least(%d,%d) and greatest(%d,%d) or end between least(%d,%d) and greatest(%d,%d)) or (least(start,end)<=least(%d,%d) and greatest(start,end)>=greatest(%d,%d)));" % (self._table, query,start,end,start,end,start,end,start,end,start,end,start,end)
|
|
|
79 lIdentifiers = self._iDb.getIntegerListWithSQLCmd(sqlCmd)
|
|
|
80 lSets = self.getSetListFromIdList(lIdentifiers)
|
|
|
81 return lSets
|
|
|
82
|
|
|
83 ## Give a list of identifier numbers contained in the table
|
|
|
84 #
|
|
|
85 # @return lId integer list
|
|
|
86 #
|
|
|
87 def getIdList(self):
|
|
|
88 sqlCmd = "select distinct path from %s;" % (self._table)
|
|
|
89 lId = self._iDb.getIntegerListWithSQLCmd( sqlCmd )
|
|
|
90 return lId
|
|
|
91
|
|
|
92 ## Give a list of the distinct seqName/chr present in the table
|
|
|
93 #
|
|
|
94 # @return lDistinctContigNames string list
|
|
|
95 #
|
|
|
96 def getSeqNameList(self):
|
|
|
97 sqlCmd = "SELECT DISTINCT chr FROM %s" % ( self._table )
|
|
|
98 lDistinctContigNames = self._iDb.getStringListWithSQLCmd(sqlCmd)
|
|
|
99 return lDistinctContigNames
|
|
|
100
|
|
|
101 ## Give a list of Set instances having a given seq name
|
|
|
102 #
|
|
|
103 # @param seqName string seq name
|
|
|
104 # @return lSet list of instances
|
|
|
105 #
|
|
|
106 def getSetListFromSeqName( self, seqName):
|
|
|
107 lSets = self.getListFromSeqName(seqName)
|
|
|
108 return lSets
|
|
|
109
|
|
|
110 ## Give a set instances list with a given identifier number
|
|
|
111 #
|
|
|
112 # @param id integer identifier number
|
|
|
113 # @return lSet list of set instances
|
|
|
114 #
|
|
|
115 def getSetListFromId(self, id):
|
|
|
116 SQLCmd = "select * from %s where path=%d;" % (self._table, id)
|
|
|
117 return self._iDb.getObjectListWithSQLCmd( SQLCmd, self._getInstanceToAdapt )
|
|
|
118
|
|
|
119 ## Give a set instances list with a list of identifier numbers
|
|
|
120 #
|
|
|
121 # @param lId integers list identifiers list numbers
|
|
|
122 # @return lSet list of set instances
|
|
|
123 #
|
|
|
124 def getSetListFromIdList(self,lId):
|
|
|
125 lSet = []
|
|
|
126 if lId == []:
|
|
|
127 return lSet
|
|
|
128 SQLCmd = "select * from %s where path=%d" % (self._table, lId[0])
|
|
|
129 for i in lId[1:]:
|
|
|
130 SQLCmd += " or path=%d" % (i)
|
|
|
131 SQLCmd += ";"
|
|
|
132 return self._iDb.getObjectListWithSQLCmd( SQLCmd, self._getInstanceToAdapt )
|
|
|
133
|
|
|
134 ## Return a list of Set instances overlapping a given sequence
|
|
|
135 #
|
|
|
136 # @param seqName string sequence name
|
|
|
137 # @param start integer start coordinate
|
|
|
138 # @param end integer end coordinate
|
|
|
139 # @return lSet list of Set instances
|
|
|
140 #
|
|
|
141 def getSetListOverlappingCoord( self, seqName, start, end ):
|
|
|
142 lSet = self.getListOverlappingCoord( seqName, start, end )
|
|
|
143 return lSet
|
|
|
144
|
|
|
145 ## Delete set corresponding to a given identifier number
|
|
|
146 #
|
|
|
147 # @param id integer identifier number
|
|
|
148 #
|
|
|
149 def deleteFromId(self, id):
|
|
|
150 sqlCmd = "delete from %s where path=%d;" % (self._table, id)
|
|
|
151 self._iDb.execute(sqlCmd)
|
|
|
152
|
|
|
153 ## Delete set corresponding to a given list of identifier number
|
|
|
154 #
|
|
|
155 # @param lId integers list list of identifier number
|
|
|
156 #
|
|
|
157 def deleteFromIdList(self, lId):
|
|
|
158 if lId == []:
|
|
|
159 return
|
|
|
160 sqlCmd = "delete from %s where path=%d" % ( self._table, lId[0] )
|
|
|
161 for i in lId[1:]:
|
|
|
162 sqlCmd += " or path=%d"%(i)
|
|
|
163 sqlCmd += ";"
|
|
|
164 self._iDb.execute(sqlCmd)
|
|
|
165
|
|
|
166 ## Join two set by changing id number of id1 and id2 set to the least of id1 and id2
|
|
|
167 #
|
|
|
168 # @param id1 integer id path number
|
|
|
169 # @param id2 integer id path number
|
|
|
170 #
|
|
|
171 def joinTwoSets(self, id1, id2):
|
|
|
172 if id1 < id2:
|
|
|
173 newId = id1
|
|
|
174 oldId = id2
|
|
|
175 else:
|
|
|
176 newId = id2
|
|
|
177 oldId = id1
|
|
|
178 sqlCmd = "UPDATE %s SET path=%d WHERE path=%d" % (self._table, newId, oldId)
|
|
|
179 self._iDb.execute(sqlCmd)
|
|
|
180
|
|
|
181 ## Get a new id number
|
|
|
182 #
|
|
|
183 # @return new_id integer max_id + 1
|
|
|
184 #
|
|
|
185 def getNewId(self):
|
|
|
186 sqlCmd = "select max(path) from %s;" % (self._table)
|
|
|
187 maxId = self._iDb.getIntegerWithSQLCmd(sqlCmd)
|
|
|
188 newId = int(maxId) + 1
|
|
|
189 return newId
|
|
|
190
|
|
|
191 ## Give the data contained in the table as a list of Sets instances
|
|
|
192 #
|
|
|
193 # @return lSets list of set instances
|
|
|
194 #
|
|
|
195 def getListOfAllSets( self ):
|
|
|
196 return self.getListOfAllCoordObject()
|
|
|
197
|
|
|
198 def _getInstanceToAdapt(self):
|
|
|
199 iSet = Set()
|
|
|
200 return iSet
|
|
|
201
|
|
|
202 def _getTypeColumAttr2Get(self, contig):
|
|
|
203 colum2Get = 'chr'
|
|
|
204 type2Get = '%s'
|
|
|
205 attr2Get = contig
|
|
|
206 return colum2Get, type2Get, attr2Get
|
|
|
207
|
|
|
208 def _getTypeAndAttr2Insert(self, set):
|
|
|
209 type2Insert = ("'%d'","'%s'","'%s'","'%d'","'%d'")
|
|
|
210 attr2Insert = (set.id, set.name, set.seqname, set.start, set.end)
|
|
|
211 return type2Insert, attr2Insert
|
|
|
212
|
|
|
213 def _escapeAntislash(self, obj):
|
|
|
214 obj.name = obj.name.replace("\\", "\\\\")
|
|
|
215 obj.seqname = obj.seqname.replace("\\", "\\\\")
|
