Merge ~cjwatson/launchpad:stormify-pofile-queries into launchpad:master
- Git
- lp:~cjwatson/launchpad
- stormify-pofile-queries
- Merge into master
Proposed by
Colin Watson
Status: | Merged |
---|---|
Approved by: | Colin Watson |
Approved revision: | bb55082e49bfdfe3924bbc61133e31a2920bea4b |
Merge reported by: | Otto Co-Pilot |
Merged at revision: | not available |
Proposed branch: | ~cjwatson/launchpad:stormify-pofile-queries |
Merge into: | launchpad:master |
Diff against target: |
310 lines (+102/-112) 1 file modified
lib/lp/translations/model/pofile.py (+102/-112) |
Related bugs: |
Reviewer | Review Type | Date Requested | Status |
---|---|---|---|
Ioana Lasc | Approve | ||
Review via email:
|
Commit message
Convert queries in lp.translations
Description of the change
To post a comment you must log in.
Revision history for this message

Ioana Lasc (ilasc) : | # |
review:
Approve
Preview Diff
[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1 | diff --git a/lib/lp/translations/model/pofile.py b/lib/lp/translations/model/pofile.py |
2 | index 112a86d..92016e1 100644 |
3 | --- a/lib/lp/translations/model/pofile.py |
4 | +++ b/lib/lp/translations/model/pofile.py |
5 | @@ -1,4 +1,4 @@ |
6 | -# Copyright 2009-2018 Canonical Ltd. This software is licensed under the |
7 | +# Copyright 2009-2020 Canonical Ltd. This software is licensed under the |
8 | # GNU Affero General Public License version 3 (see the file LICENSE). |
9 | |
10 | """`SQLObject` implementation of `IPOFile` interface.""" |
11 | @@ -30,10 +30,13 @@ from storm.expr import ( |
12 | Exists, |
13 | Join, |
14 | LeftJoin, |
15 | + Like, |
16 | + like_escape, |
17 | Not, |
18 | Or, |
19 | Select, |
20 | SQL, |
21 | + Union, |
22 | ) |
23 | from storm.info import ClassAlias |
24 | from storm.store import ( |
25 | @@ -58,9 +61,7 @@ from lp.services.database.interfaces import ( |
26 | from lp.services.database.sqlbase import ( |
27 | flush_database_updates, |
28 | quote, |
29 | - quote_like, |
30 | SQLBase, |
31 | - sqlvalues, |
32 | ) |
33 | from lp.services.mail.helpers import get_email_template |
34 | from lp.services.propertycache import cachedproperty |
35 | @@ -98,6 +99,7 @@ from lp.translations.model.potmsgset import ( |
36 | credits_message_str, |
37 | POTMsgSet, |
38 | ) |
39 | +from lp.translations.model.potranslation import POTranslation |
40 | from lp.translations.model.translationimportqueue import collect_import_info |
41 | from lp.translations.model.translationmessage import TranslationMessage |
42 | from lp.translations.model.translationtemplateitem import ( |
43 | @@ -161,93 +163,90 @@ class POFileMixIn(RosettaStats): |
44 | Returned values are POTMsgSet ids containing them, expected to be |
45 | used in a UNION across all plural forms. |
46 | """ |
47 | - translation_match = """ |
48 | - -- Find translations containing `text`. |
49 | - -- Like in findPOTMsgSetsContaining(), to avoid seqscans on |
50 | - -- POTranslation table, we do ILIKE comparison on them in |
51 | - -- a subselect which is first filtered by the POFile. |
52 | - SELECT TranslationMessage.potmsgset |
53 | - FROM TranslationMessage |
54 | - JOIN TranslationTemplateItem |
55 | - ON TranslationMessage.potmsgset |
56 | - = TranslationTemplateItem.potmsgset |
57 | - WHERE |
58 | - TranslationTemplateItem.potemplate = %(potemplate)s AND |
59 | - TranslationMessage.language = %(language)s AND |
60 | - TranslationMessage.msgstr%(plural_form)d IN ( |
61 | - SELECT POTranslation.id FROM POTranslation WHERE |
62 | - POTranslation.id IN ( |
63 | - SELECT DISTINCT(msgstr%(plural_form)d) |
64 | - FROM TranslationMessage AS tm_ids |
65 | - JOIN TranslationTemplateItem |
66 | - ON tm_ids.potmsgset=TranslationTemplateItem.potmsgset |
67 | - WHERE |
68 | - TranslationTemplateItem.potemplate |
69 | - = %(potemplate)s AND |
70 | - TranslationTemplateItem.sequence > 0 AND |
71 | - tm_ids.language=%(language)s |
72 | - ) AND |
73 | - POTranslation.translation |
74 | - ILIKE '%%' || %(text)s || '%%') |
75 | - """ % dict(potemplate=quote(pofile.potemplate), |
76 | - language=quote(pofile.language), |
77 | - plural_form=plural_form, |
78 | - text=quote_like(text)) |
79 | - return translation_match |
80 | + # Find translations containing `text`. |
81 | + # Like in findPOTMsgSetsContaining(), to avoid seqscans on |
82 | + # POTranslation table, we do ILIKE comparison on them in a subselect |
83 | + # which is first filtered by the POFile. |
84 | + msgstr_column_name = "msgstr%dID" % plural_form |
85 | + tm_ids = ClassAlias(TranslationMessage, "tm_ids") |
86 | + return Select( |
87 | + TranslationMessage.potmsgsetID, |
88 | + tables=( |
89 | + TranslationMessage, |
90 | + Join( |
91 | + TranslationTemplateItem, |
92 | + TranslationMessage.potmsgset == |
93 | + TranslationTemplateItem.potmsgsetID)), |
94 | + where=And( |
95 | + TranslationTemplateItem.potemplate == pofile.potemplate, |
96 | + TranslationMessage.language == pofile.language, |
97 | + getattr(TranslationMessage, msgstr_column_name).is_in(Select( |
98 | + POTranslation.id, |
99 | + And( |
100 | + POTranslation.id.is_in(Select( |
101 | + getattr(tm_ids, msgstr_column_name), |
102 | + tables=( |
103 | + tm_ids, |
104 | + Join( |
105 | + TranslationTemplateItem, |
106 | + tm_ids.potmsgset == |
107 | + TranslationTemplateItem.potmsgsetID)), |
108 | + where=And( |
109 | + TranslationTemplateItem.potemplate == |
110 | + pofile.potemplate, |
111 | + TranslationTemplateItem.sequence > 0, |
112 | + tm_ids.language == pofile.language), |
113 | + distinct=True)), |
114 | + Like( |
115 | + POTranslation.translation, |
116 | + u"%" + text.translate(like_escape) + u"%", |
117 | + u"!", case_sensitive=False)))))) |
118 | |
119 | def _getTemplateSearchQuery(self, text): |
120 | """Query for finding `text` in msgids of this POFile. |
121 | """ |
122 | - english_match = """ |
123 | - -- Step 1a: get POTMsgSets where msgid_singular contains `text` |
124 | - -- To avoid seqscans on POMsgID table (what LIKE usually |
125 | - -- does), we do ILIKE comparison on them in a subselect first |
126 | - -- filtered by this POTemplate. |
127 | - SELECT POTMsgSet.id |
128 | - FROM POTMsgSet |
129 | - JOIN TranslationTemplateItem |
130 | - ON TranslationTemplateItem.potmsgset=POTMsgSet.id AND |
131 | - TranslationTemplateItem.potemplate=%s |
132 | - WHERE |
133 | - (POTMsgSet.msgid_singular IS NOT NULL AND |
134 | - POTMsgSet.msgid_singular IN ( |
135 | - SELECT POMsgID.id FROM POMsgID |
136 | - WHERE id IN ( |
137 | - SELECT DISTINCT(POTMsgSet.msgid_singular) |
138 | - FROM POTMsgSet |
139 | - JOIN TranslationTemplateItem |
140 | - ON TranslationTemplateItem.potmsgset = POTMsgSet.id |
141 | - WHERE |
142 | - TranslationTemplateItem.potemplate=%s AND |
143 | - TranslationTemplateItem.sequence > 0 |
144 | - ) AND |
145 | - msgid ILIKE '%%' || %s || '%%')) |
146 | - UNION |
147 | - -- Step 1b: like above, just on msgid_plural. |
148 | - SELECT POTMsgSet.id |
149 | - FROM POTMsgSet |
150 | - JOIN TranslationTemplateItem |
151 | - ON TranslationTemplateItem.potmsgset=POTMsgSet.id AND |
152 | - TranslationTemplateItem.potemplate=%s |
153 | - WHERE |
154 | - (POTMsgSet.msgid_plural IS NOT NULL AND |
155 | - POTMsgSet.msgid_plural IN ( |
156 | - SELECT POMsgID.id FROM POMsgID |
157 | - WHERE id IN ( |
158 | - SELECT DISTINCT(POTMsgSet.msgid_plural) |
159 | - FROM POTMsgSet |
160 | - JOIN TranslationTemplateItem |
161 | - ON TranslationTemplateItem.potmsgset = POTMsgSet.id |
162 | - WHERE |
163 | - TranslationTemplateItem.potemplate=%s AND |
164 | - TranslationTemplateItem.sequence > 0 |
165 | - ) AND |
166 | - msgid ILIKE '%%' || %s || '%%')) |
167 | - """ % (quote(self.potemplate), quote(self.potemplate), |
168 | - quote_like(text), |
169 | - quote(self.potemplate), quote(self.potemplate), |
170 | - quote_like(text)) |
171 | - return english_match |
172 | + def select_potmsgsets(column_name): |
173 | + # Get POTMsgSets where `column_name` contains `text`. |
174 | + # To avoid seqscans on POMsgID table (which LIKE usually does), |
175 | + # we do ILIKE comparison on them in a subselect first filtered |
176 | + # by this POTemplate. |
177 | + msgid_column = getattr(POTMsgSet, column_name) |
178 | + msgid_columnID = getattr(POTMsgSet, column_name + "ID") |
179 | + return Select( |
180 | + POTMsgSet.id, |
181 | + tables=( |
182 | + POTMsgSet, |
183 | + Join( |
184 | + TranslationTemplateItem, |
185 | + And( |
186 | + TranslationTemplateItem.potmsgset == POTMsgSet.id, |
187 | + TranslationTemplateItem.potemplate == |
188 | + self.potemplate))), |
189 | + where=And( |
190 | + msgid_column != None, |
191 | + msgid_columnID.is_in(Select( |
192 | + POMsgID.id, |
193 | + And( |
194 | + POMsgID.id.is_in(Select( |
195 | + msgid_columnID, |
196 | + tables=( |
197 | + POTMsgSet, |
198 | + Join( |
199 | + TranslationTemplateItem, |
200 | + TranslationTemplateItem.potmsgset == |
201 | + POTMsgSet.id)), |
202 | + where=And( |
203 | + TranslationTemplateItem.potemplate == |
204 | + self.potemplate, |
205 | + TranslationTemplateItem.sequence > 0))), |
206 | + Like( |
207 | + POMsgID.msgid, |
208 | + u"%" + text.translate(like_escape) + u"%", |
209 | + u"!", case_sensitive=False)))))) |
210 | + |
211 | + return Union( |
212 | + select_potmsgsets("msgid_singular"), |
213 | + select_potmsgsets("msgid_plural")) |
214 | |
215 | def _getOrderedPOTMsgSets(self, origin_tables, query): |
216 | """Find all POTMsgSets matching `query` from `origin_tables`. |
217 | @@ -255,8 +254,6 @@ class POFileMixIn(RosettaStats): |
218 | Orders the result by TranslationTemplateItem.sequence which must |
219 | be among `origin_tables`. |
220 | """ |
221 | - if isinstance(query, six.string_types): |
222 | - query = SQL(query) |
223 | results = IMasterStore(POTMsgSet).using(origin_tables).find( |
224 | POTMsgSet, query) |
225 | return results.order_by(TranslationTemplateItem.sequence) |
226 | @@ -264,10 +261,9 @@ class POFileMixIn(RosettaStats): |
227 | def findPOTMsgSetsContaining(self, text): |
228 | """See `IPOFile`.""" |
229 | clauses = [ |
230 | - 'TranslationTemplateItem.potemplate = %s' % sqlvalues( |
231 | - self.potemplate), |
232 | - 'TranslationTemplateItem.potmsgset = POTMsgSet.id', |
233 | - 'TranslationTemplateItem.sequence > 0', |
234 | + TranslationTemplateItem.potemplate == self.potemplate, |
235 | + TranslationTemplateItem.potmsgset == POTMsgSet.id, |
236 | + TranslationTemplateItem.sequence > 0, |
237 | ] |
238 | |
239 | if text is not None: |
240 | @@ -291,11 +287,10 @@ class POFileMixIn(RosettaStats): |
241 | self, plural_form, text) |
242 | search_clauses.append(translation_match) |
243 | |
244 | - clauses.append( |
245 | - "POTMsgSet.id IN (" + " UNION ".join(search_clauses) + ")") |
246 | + clauses.append(POTMsgSet.id.is_in(Union(*search_clauses))) |
247 | |
248 | return self._getOrderedPOTMsgSets( |
249 | - [POTMsgSet, TranslationTemplateItem], ' AND '.join(clauses)) |
250 | + [POTMsgSet, TranslationTemplateItem], And(clauses)) |
251 | |
252 | def getFullLanguageCode(self): |
253 | """See `IPOFile`.""" |
254 | @@ -422,27 +417,22 @@ class POFile(SQLBase, POFileMixIn): |
255 | @property |
256 | def contributors(self): |
257 | """See `IPOFile`.""" |
258 | - # Avoid circular import. |
259 | + # Avoid circular imports. |
260 | from lp.registry.model.person import Person |
261 | + from lp.translations.model.pofiletranslator import POFileTranslator |
262 | |
263 | # Translation credit messages are "translated" by |
264 | # rosetta_experts. Shouldn't show up in contributors lists |
265 | # though. |
266 | admin_team = getUtility(ILaunchpadCelebrities).rosetta_experts |
267 | |
268 | - contributors = Person.select(""" |
269 | - POFileTranslator.person = Person.id AND |
270 | - POFileTranslator.person <> %s AND |
271 | - POFileTranslator.pofile = %s""" % sqlvalues(admin_team, self), |
272 | - clauseTables=["POFileTranslator"], |
273 | - distinct=True, |
274 | - # XXX: kiko 2006-10-19: |
275 | - # We can't use Person.sortingColumns because this is a |
276 | - # distinct query. To use it we'd need to add the sorting |
277 | - # function to the column results and then ignore it -- just |
278 | - # like selectAlso does, ironically. |
279 | - orderBy=["Person.displayname", "Person.name"]) |
280 | - |
281 | + contributors = IStore(Person).find( |
282 | + Person, |
283 | + POFileTranslator.person == Person.id, |
284 | + POFileTranslator.person != admin_team, |
285 | + POFileTranslator.pofile == self).config(distinct=True) |
286 | + contributors = contributors.order_by(*Person._storm_sortingColumns) |
287 | + contributors = contributors.config(distinct=True) |
288 | return contributors |
289 | |
290 | def prepareTranslationCredits(self, potmsgset): |
291 | @@ -548,7 +538,7 @@ class POFile(SQLBase, POFileMixIn): |
292 | """Get query data for fetching all POTMsgSets with translations. |
293 | |
294 | Return a tuple of SQL (clauses, clause_tables) to be used with |
295 | - POTMsgSet.select(). |
296 | + POTMsgSet queries. |
297 | """ |
298 | flag_name = getUtility(ITranslationSideTraitsSet).getForTemplate( |
299 | self.potemplate).flag_name |
300 | @@ -1480,8 +1470,8 @@ class POFileSet: |
301 | |
302 | def getBatch(self, starting_id, batch_size): |
303 | """See `IPOFileSet`.""" |
304 | - return POFile.select( |
305 | - "id >= %s" % quote(starting_id), orderBy="id", limit=batch_size) |
306 | + return IStore(POFile).find( |
307 | + POFile, POFile.id >= starting_id).order_by(POFile.id)[:batch_size] |
308 | |
309 | def getPOFilesWithTranslationCredits(self, untranslated=False): |
310 | """See `IPOFileSet`.""" |