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 (community) | Approve | ||
Review via email: mp+394702@code.launchpad.net |
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 @@ | |||
7 | 1 | # Copyright 2009-2018 Canonical Ltd. This software is licensed under the | 1 | # Copyright 2009-2020 Canonical Ltd. This software is licensed under the |
8 | 2 | # GNU Affero General Public License version 3 (see the file LICENSE). | 2 | # GNU Affero General Public License version 3 (see the file LICENSE). |
9 | 3 | 3 | ||
10 | 4 | """`SQLObject` implementation of `IPOFile` interface.""" | 4 | """`SQLObject` implementation of `IPOFile` interface.""" |
11 | @@ -30,10 +30,13 @@ from storm.expr import ( | |||
12 | 30 | Exists, | 30 | Exists, |
13 | 31 | Join, | 31 | Join, |
14 | 32 | LeftJoin, | 32 | LeftJoin, |
15 | 33 | Like, | ||
16 | 34 | like_escape, | ||
17 | 33 | Not, | 35 | Not, |
18 | 34 | Or, | 36 | Or, |
19 | 35 | Select, | 37 | Select, |
20 | 36 | SQL, | 38 | SQL, |
21 | 39 | Union, | ||
22 | 37 | ) | 40 | ) |
23 | 38 | from storm.info import ClassAlias | 41 | from storm.info import ClassAlias |
24 | 39 | from storm.store import ( | 42 | from storm.store import ( |
25 | @@ -58,9 +61,7 @@ from lp.services.database.interfaces import ( | |||
26 | 58 | from lp.services.database.sqlbase import ( | 61 | from lp.services.database.sqlbase import ( |
27 | 59 | flush_database_updates, | 62 | flush_database_updates, |
28 | 60 | quote, | 63 | quote, |
29 | 61 | quote_like, | ||
30 | 62 | SQLBase, | 64 | SQLBase, |
31 | 63 | sqlvalues, | ||
32 | 64 | ) | 65 | ) |
33 | 65 | from lp.services.mail.helpers import get_email_template | 66 | from lp.services.mail.helpers import get_email_template |
34 | 66 | from lp.services.propertycache import cachedproperty | 67 | from lp.services.propertycache import cachedproperty |
35 | @@ -98,6 +99,7 @@ from lp.translations.model.potmsgset import ( | |||
36 | 98 | credits_message_str, | 99 | credits_message_str, |
37 | 99 | POTMsgSet, | 100 | POTMsgSet, |
38 | 100 | ) | 101 | ) |
39 | 102 | from lp.translations.model.potranslation import POTranslation | ||
40 | 101 | from lp.translations.model.translationimportqueue import collect_import_info | 103 | from lp.translations.model.translationimportqueue import collect_import_info |
41 | 102 | from lp.translations.model.translationmessage import TranslationMessage | 104 | from lp.translations.model.translationmessage import TranslationMessage |
42 | 103 | from lp.translations.model.translationtemplateitem import ( | 105 | from lp.translations.model.translationtemplateitem import ( |
43 | @@ -161,93 +163,90 @@ class POFileMixIn(RosettaStats): | |||
44 | 161 | Returned values are POTMsgSet ids containing them, expected to be | 163 | Returned values are POTMsgSet ids containing them, expected to be |
45 | 162 | used in a UNION across all plural forms. | 164 | used in a UNION across all plural forms. |
46 | 163 | """ | 165 | """ |
80 | 164 | translation_match = """ | 166 | # Find translations containing `text`. |
81 | 165 | -- Find translations containing `text`. | 167 | # Like in findPOTMsgSetsContaining(), to avoid seqscans on |
82 | 166 | -- Like in findPOTMsgSetsContaining(), to avoid seqscans on | 168 | # POTranslation table, we do ILIKE comparison on them in a subselect |
83 | 167 | -- POTranslation table, we do ILIKE comparison on them in | 169 | # which is first filtered by the POFile. |
84 | 168 | -- a subselect which is first filtered by the POFile. | 170 | msgstr_column_name = "msgstr%dID" % plural_form |
85 | 169 | SELECT TranslationMessage.potmsgset | 171 | tm_ids = ClassAlias(TranslationMessage, "tm_ids") |
86 | 170 | FROM TranslationMessage | 172 | return Select( |
87 | 171 | JOIN TranslationTemplateItem | 173 | TranslationMessage.potmsgsetID, |
88 | 172 | ON TranslationMessage.potmsgset | 174 | tables=( |
89 | 173 | = TranslationTemplateItem.potmsgset | 175 | TranslationMessage, |
90 | 174 | WHERE | 176 | Join( |
91 | 175 | TranslationTemplateItem.potemplate = %(potemplate)s AND | 177 | TranslationTemplateItem, |
92 | 176 | TranslationMessage.language = %(language)s AND | 178 | TranslationMessage.potmsgset == |
93 | 177 | TranslationMessage.msgstr%(plural_form)d IN ( | 179 | TranslationTemplateItem.potmsgsetID)), |
94 | 178 | SELECT POTranslation.id FROM POTranslation WHERE | 180 | where=And( |
95 | 179 | POTranslation.id IN ( | 181 | TranslationTemplateItem.potemplate == pofile.potemplate, |
96 | 180 | SELECT DISTINCT(msgstr%(plural_form)d) | 182 | TranslationMessage.language == pofile.language, |
97 | 181 | FROM TranslationMessage AS tm_ids | 183 | getattr(TranslationMessage, msgstr_column_name).is_in(Select( |
98 | 182 | JOIN TranslationTemplateItem | 184 | POTranslation.id, |
99 | 183 | ON tm_ids.potmsgset=TranslationTemplateItem.potmsgset | 185 | And( |
100 | 184 | WHERE | 186 | POTranslation.id.is_in(Select( |
101 | 185 | TranslationTemplateItem.potemplate | 187 | getattr(tm_ids, msgstr_column_name), |
102 | 186 | = %(potemplate)s AND | 188 | tables=( |
103 | 187 | TranslationTemplateItem.sequence > 0 AND | 189 | tm_ids, |
104 | 188 | tm_ids.language=%(language)s | 190 | Join( |
105 | 189 | ) AND | 191 | TranslationTemplateItem, |
106 | 190 | POTranslation.translation | 192 | tm_ids.potmsgset == |
107 | 191 | ILIKE '%%' || %(text)s || '%%') | 193 | TranslationTemplateItem.potmsgsetID)), |
108 | 192 | """ % dict(potemplate=quote(pofile.potemplate), | 194 | where=And( |
109 | 193 | language=quote(pofile.language), | 195 | TranslationTemplateItem.potemplate == |
110 | 194 | plural_form=plural_form, | 196 | pofile.potemplate, |
111 | 195 | text=quote_like(text)) | 197 | TranslationTemplateItem.sequence > 0, |
112 | 196 | return translation_match | 198 | tm_ids.language == pofile.language), |
113 | 199 | distinct=True)), | ||
114 | 200 | Like( | ||
115 | 201 | POTranslation.translation, | ||
116 | 202 | u"%" + text.translate(like_escape) + u"%", | ||
117 | 203 | u"!", case_sensitive=False)))))) | ||
118 | 197 | 204 | ||
119 | 198 | def _getTemplateSearchQuery(self, text): | 205 | def _getTemplateSearchQuery(self, text): |
120 | 199 | """Query for finding `text` in msgids of this POFile. | 206 | """Query for finding `text` in msgids of this POFile. |
121 | 200 | """ | 207 | """ |
172 | 201 | english_match = """ | 208 | def select_potmsgsets(column_name): |
173 | 202 | -- Step 1a: get POTMsgSets where msgid_singular contains `text` | 209 | # Get POTMsgSets where `column_name` contains `text`. |
174 | 203 | -- To avoid seqscans on POMsgID table (what LIKE usually | 210 | # To avoid seqscans on POMsgID table (which LIKE usually does), |
175 | 204 | -- does), we do ILIKE comparison on them in a subselect first | 211 | # we do ILIKE comparison on them in a subselect first filtered |
176 | 205 | -- filtered by this POTemplate. | 212 | # by this POTemplate. |
177 | 206 | SELECT POTMsgSet.id | 213 | msgid_column = getattr(POTMsgSet, column_name) |
178 | 207 | FROM POTMsgSet | 214 | msgid_columnID = getattr(POTMsgSet, column_name + "ID") |
179 | 208 | JOIN TranslationTemplateItem | 215 | return Select( |
180 | 209 | ON TranslationTemplateItem.potmsgset=POTMsgSet.id AND | 216 | POTMsgSet.id, |
181 | 210 | TranslationTemplateItem.potemplate=%s | 217 | tables=( |
182 | 211 | WHERE | 218 | POTMsgSet, |
183 | 212 | (POTMsgSet.msgid_singular IS NOT NULL AND | 219 | Join( |
184 | 213 | POTMsgSet.msgid_singular IN ( | 220 | TranslationTemplateItem, |
185 | 214 | SELECT POMsgID.id FROM POMsgID | 221 | And( |
186 | 215 | WHERE id IN ( | 222 | TranslationTemplateItem.potmsgset == POTMsgSet.id, |
187 | 216 | SELECT DISTINCT(POTMsgSet.msgid_singular) | 223 | TranslationTemplateItem.potemplate == |
188 | 217 | FROM POTMsgSet | 224 | self.potemplate))), |
189 | 218 | JOIN TranslationTemplateItem | 225 | where=And( |
190 | 219 | ON TranslationTemplateItem.potmsgset = POTMsgSet.id | 226 | msgid_column != None, |
191 | 220 | WHERE | 227 | msgid_columnID.is_in(Select( |
192 | 221 | TranslationTemplateItem.potemplate=%s AND | 228 | POMsgID.id, |
193 | 222 | TranslationTemplateItem.sequence > 0 | 229 | And( |
194 | 223 | ) AND | 230 | POMsgID.id.is_in(Select( |
195 | 224 | msgid ILIKE '%%' || %s || '%%')) | 231 | msgid_columnID, |
196 | 225 | UNION | 232 | tables=( |
197 | 226 | -- Step 1b: like above, just on msgid_plural. | 233 | POTMsgSet, |
198 | 227 | SELECT POTMsgSet.id | 234 | Join( |
199 | 228 | FROM POTMsgSet | 235 | TranslationTemplateItem, |
200 | 229 | JOIN TranslationTemplateItem | 236 | TranslationTemplateItem.potmsgset == |
201 | 230 | ON TranslationTemplateItem.potmsgset=POTMsgSet.id AND | 237 | POTMsgSet.id)), |
202 | 231 | TranslationTemplateItem.potemplate=%s | 238 | where=And( |
203 | 232 | WHERE | 239 | TranslationTemplateItem.potemplate == |
204 | 233 | (POTMsgSet.msgid_plural IS NOT NULL AND | 240 | self.potemplate, |
205 | 234 | POTMsgSet.msgid_plural IN ( | 241 | TranslationTemplateItem.sequence > 0))), |
206 | 235 | SELECT POMsgID.id FROM POMsgID | 242 | Like( |
207 | 236 | WHERE id IN ( | 243 | POMsgID.msgid, |
208 | 237 | SELECT DISTINCT(POTMsgSet.msgid_plural) | 244 | u"%" + text.translate(like_escape) + u"%", |
209 | 238 | FROM POTMsgSet | 245 | u"!", case_sensitive=False)))))) |
210 | 239 | JOIN TranslationTemplateItem | 246 | |
211 | 240 | ON TranslationTemplateItem.potmsgset = POTMsgSet.id | 247 | return Union( |
212 | 241 | WHERE | 248 | select_potmsgsets("msgid_singular"), |
213 | 242 | TranslationTemplateItem.potemplate=%s AND | 249 | select_potmsgsets("msgid_plural")) |
164 | 243 | TranslationTemplateItem.sequence > 0 | ||
165 | 244 | ) AND | ||
166 | 245 | msgid ILIKE '%%' || %s || '%%')) | ||
167 | 246 | """ % (quote(self.potemplate), quote(self.potemplate), | ||
168 | 247 | quote_like(text), | ||
169 | 248 | quote(self.potemplate), quote(self.potemplate), | ||
170 | 249 | quote_like(text)) | ||
171 | 250 | return english_match | ||
214 | 251 | 250 | ||
215 | 252 | def _getOrderedPOTMsgSets(self, origin_tables, query): | 251 | def _getOrderedPOTMsgSets(self, origin_tables, query): |
216 | 253 | """Find all POTMsgSets matching `query` from `origin_tables`. | 252 | """Find all POTMsgSets matching `query` from `origin_tables`. |
217 | @@ -255,8 +254,6 @@ class POFileMixIn(RosettaStats): | |||
218 | 255 | Orders the result by TranslationTemplateItem.sequence which must | 254 | Orders the result by TranslationTemplateItem.sequence which must |
219 | 256 | be among `origin_tables`. | 255 | be among `origin_tables`. |
220 | 257 | """ | 256 | """ |
221 | 258 | if isinstance(query, six.string_types): | ||
222 | 259 | query = SQL(query) | ||
223 | 260 | results = IMasterStore(POTMsgSet).using(origin_tables).find( | 257 | results = IMasterStore(POTMsgSet).using(origin_tables).find( |
224 | 261 | POTMsgSet, query) | 258 | POTMsgSet, query) |
225 | 262 | return results.order_by(TranslationTemplateItem.sequence) | 259 | return results.order_by(TranslationTemplateItem.sequence) |
226 | @@ -264,10 +261,9 @@ class POFileMixIn(RosettaStats): | |||
227 | 264 | def findPOTMsgSetsContaining(self, text): | 261 | def findPOTMsgSetsContaining(self, text): |
228 | 265 | """See `IPOFile`.""" | 262 | """See `IPOFile`.""" |
229 | 266 | clauses = [ | 263 | clauses = [ |
234 | 267 | 'TranslationTemplateItem.potemplate = %s' % sqlvalues( | 264 | TranslationTemplateItem.potemplate == self.potemplate, |
235 | 268 | self.potemplate), | 265 | TranslationTemplateItem.potmsgset == POTMsgSet.id, |
236 | 269 | 'TranslationTemplateItem.potmsgset = POTMsgSet.id', | 266 | TranslationTemplateItem.sequence > 0, |
233 | 270 | 'TranslationTemplateItem.sequence > 0', | ||
237 | 271 | ] | 267 | ] |
238 | 272 | 268 | ||
239 | 273 | if text is not None: | 269 | if text is not None: |
240 | @@ -291,11 +287,10 @@ class POFileMixIn(RosettaStats): | |||
241 | 291 | self, plural_form, text) | 287 | self, plural_form, text) |
242 | 292 | search_clauses.append(translation_match) | 288 | search_clauses.append(translation_match) |
243 | 293 | 289 | ||
246 | 294 | clauses.append( | 290 | clauses.append(POTMsgSet.id.is_in(Union(*search_clauses))) |
245 | 295 | "POTMsgSet.id IN (" + " UNION ".join(search_clauses) + ")") | ||
247 | 296 | 291 | ||
248 | 297 | return self._getOrderedPOTMsgSets( | 292 | return self._getOrderedPOTMsgSets( |
250 | 298 | [POTMsgSet, TranslationTemplateItem], ' AND '.join(clauses)) | 293 | [POTMsgSet, TranslationTemplateItem], And(clauses)) |
251 | 299 | 294 | ||
252 | 300 | def getFullLanguageCode(self): | 295 | def getFullLanguageCode(self): |
253 | 301 | """See `IPOFile`.""" | 296 | """See `IPOFile`.""" |
254 | @@ -422,27 +417,22 @@ class POFile(SQLBase, POFileMixIn): | |||
255 | 422 | @property | 417 | @property |
256 | 423 | def contributors(self): | 418 | def contributors(self): |
257 | 424 | """See `IPOFile`.""" | 419 | """See `IPOFile`.""" |
259 | 425 | # Avoid circular import. | 420 | # Avoid circular imports. |
260 | 426 | from lp.registry.model.person import Person | 421 | from lp.registry.model.person import Person |
261 | 422 | from lp.translations.model.pofiletranslator import POFileTranslator | ||
262 | 427 | 423 | ||
263 | 428 | # Translation credit messages are "translated" by | 424 | # Translation credit messages are "translated" by |
264 | 429 | # rosetta_experts. Shouldn't show up in contributors lists | 425 | # rosetta_experts. Shouldn't show up in contributors lists |
265 | 430 | # though. | 426 | # though. |
266 | 431 | admin_team = getUtility(ILaunchpadCelebrities).rosetta_experts | 427 | admin_team = getUtility(ILaunchpadCelebrities).rosetta_experts |
267 | 432 | 428 | ||
281 | 433 | contributors = Person.select(""" | 429 | contributors = IStore(Person).find( |
282 | 434 | POFileTranslator.person = Person.id AND | 430 | Person, |
283 | 435 | POFileTranslator.person <> %s AND | 431 | POFileTranslator.person == Person.id, |
284 | 436 | POFileTranslator.pofile = %s""" % sqlvalues(admin_team, self), | 432 | POFileTranslator.person != admin_team, |
285 | 437 | clauseTables=["POFileTranslator"], | 433 | POFileTranslator.pofile == self).config(distinct=True) |
286 | 438 | distinct=True, | 434 | contributors = contributors.order_by(*Person._storm_sortingColumns) |
287 | 439 | # XXX: kiko 2006-10-19: | 435 | contributors = contributors.config(distinct=True) |
275 | 440 | # We can't use Person.sortingColumns because this is a | ||
276 | 441 | # distinct query. To use it we'd need to add the sorting | ||
277 | 442 | # function to the column results and then ignore it -- just | ||
278 | 443 | # like selectAlso does, ironically. | ||
279 | 444 | orderBy=["Person.displayname", "Person.name"]) | ||
280 | 445 | |||
288 | 446 | return contributors | 436 | return contributors |
289 | 447 | 437 | ||
290 | 448 | def prepareTranslationCredits(self, potmsgset): | 438 | def prepareTranslationCredits(self, potmsgset): |
291 | @@ -548,7 +538,7 @@ class POFile(SQLBase, POFileMixIn): | |||
292 | 548 | """Get query data for fetching all POTMsgSets with translations. | 538 | """Get query data for fetching all POTMsgSets with translations. |
293 | 549 | 539 | ||
294 | 550 | Return a tuple of SQL (clauses, clause_tables) to be used with | 540 | Return a tuple of SQL (clauses, clause_tables) to be used with |
296 | 551 | POTMsgSet.select(). | 541 | POTMsgSet queries. |
297 | 552 | """ | 542 | """ |
298 | 553 | flag_name = getUtility(ITranslationSideTraitsSet).getForTemplate( | 543 | flag_name = getUtility(ITranslationSideTraitsSet).getForTemplate( |
299 | 554 | self.potemplate).flag_name | 544 | self.potemplate).flag_name |
300 | @@ -1480,8 +1470,8 @@ class POFileSet: | |||
301 | 1480 | 1470 | ||
302 | 1481 | def getBatch(self, starting_id, batch_size): | 1471 | def getBatch(self, starting_id, batch_size): |
303 | 1482 | """See `IPOFileSet`.""" | 1472 | """See `IPOFileSet`.""" |
306 | 1483 | return POFile.select( | 1473 | return IStore(POFile).find( |
307 | 1484 | "id >= %s" % quote(starting_id), orderBy="id", limit=batch_size) | 1474 | POFile, POFile.id >= starting_id).order_by(POFile.id)[:batch_size] |
308 | 1485 | 1475 | ||
309 | 1486 | def getPOFilesWithTranslationCredits(self, untranslated=False): | 1476 | def getPOFilesWithTranslationCredits(self, untranslated=False): |
310 | 1487 | """See `IPOFileSet`.""" | 1477 | """See `IPOFileSet`.""" |