Mercurial > repos > yufei-luo > s_mart
comparison smart_toolShed/commons/core/sql/TableSetAdaptator.py @ 0:e0f8dcca02ed
Uploaded S-MART tool. A toolbox manages RNA-Seq and ChIP-Seq data.
author | yufei-luo |
---|---|
date | Thu, 17 Jan 2013 10:52:14 -0500 |
parents | |
children |
comparison
equal
deleted
inserted
replaced
-1:000000000000 | 0:e0f8dcca02ed |
---|---|
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("\\", "\\\\") |