Merge ~cjwatson/launchpad:stormify-pofile-queries into launchpad: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)
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.model.pofile to Storm

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
diff --git a/lib/lp/translations/model/pofile.py b/lib/lp/translations/model/pofile.py
index 112a86d..92016e1 100644
--- a/lib/lp/translations/model/pofile.py
+++ b/lib/lp/translations/model/pofile.py
@@ -1,4 +1,4 @@
1# Copyright 2009-2018 Canonical Ltd. This software is licensed under the1# Copyright 2009-2020 Canonical Ltd. This software is licensed under the
2# GNU Affero General Public License version 3 (see the file LICENSE).2# GNU Affero General Public License version 3 (see the file LICENSE).
33
4"""`SQLObject` implementation of `IPOFile` interface."""4"""`SQLObject` implementation of `IPOFile` interface."""
@@ -30,10 +30,13 @@ from storm.expr import (
30 Exists,30 Exists,
31 Join,31 Join,
32 LeftJoin,32 LeftJoin,
33 Like,
34 like_escape,
33 Not,35 Not,
34 Or,36 Or,
35 Select,37 Select,
36 SQL,38 SQL,
39 Union,
37 )40 )
38from storm.info import ClassAlias41from storm.info import ClassAlias
39from storm.store import (42from storm.store import (
@@ -58,9 +61,7 @@ from lp.services.database.interfaces import (
58from lp.services.database.sqlbase import (61from lp.services.database.sqlbase import (
59 flush_database_updates,62 flush_database_updates,
60 quote,63 quote,
61 quote_like,
62 SQLBase,64 SQLBase,
63 sqlvalues,
64 )65 )
65from lp.services.mail.helpers import get_email_template66from lp.services.mail.helpers import get_email_template
66from lp.services.propertycache import cachedproperty67from lp.services.propertycache import cachedproperty
@@ -98,6 +99,7 @@ from lp.translations.model.potmsgset import (
98 credits_message_str,99 credits_message_str,
99 POTMsgSet,100 POTMsgSet,
100 )101 )
102from lp.translations.model.potranslation import POTranslation
101from lp.translations.model.translationimportqueue import collect_import_info103from lp.translations.model.translationimportqueue import collect_import_info
102from lp.translations.model.translationmessage import TranslationMessage104from lp.translations.model.translationmessage import TranslationMessage
103from lp.translations.model.translationtemplateitem import (105from lp.translations.model.translationtemplateitem import (
@@ -161,93 +163,90 @@ class POFileMixIn(RosettaStats):
161 Returned values are POTMsgSet ids containing them, expected to be163 Returned values are POTMsgSet ids containing them, expected to be
162 used in a UNION across all plural forms.164 used in a UNION across all plural forms.
163 """165 """
164 translation_match = """166 # Find translations containing `text`.
165 -- Find translations containing `text`.167 # Like in findPOTMsgSetsContaining(), to avoid seqscans on
166 -- Like in findPOTMsgSetsContaining(), to avoid seqscans on168 # POTranslation table, we do ILIKE comparison on them in a subselect
167 -- POTranslation table, we do ILIKE comparison on them in169 # which is first filtered by the POFile.
168 -- a subselect which is first filtered by the POFile.170 msgstr_column_name = "msgstr%dID" % plural_form
169 SELECT TranslationMessage.potmsgset171 tm_ids = ClassAlias(TranslationMessage, "tm_ids")
170 FROM TranslationMessage172 return Select(
171 JOIN TranslationTemplateItem173 TranslationMessage.potmsgsetID,
172 ON TranslationMessage.potmsgset174 tables=(
173 = TranslationTemplateItem.potmsgset175 TranslationMessage,
174 WHERE176 Join(
175 TranslationTemplateItem.potemplate = %(potemplate)s AND177 TranslationTemplateItem,
176 TranslationMessage.language = %(language)s AND178 TranslationMessage.potmsgset ==
177 TranslationMessage.msgstr%(plural_form)d IN (179 TranslationTemplateItem.potmsgsetID)),
178 SELECT POTranslation.id FROM POTranslation WHERE180 where=And(
179 POTranslation.id IN (181 TranslationTemplateItem.potemplate == pofile.potemplate,
180 SELECT DISTINCT(msgstr%(plural_form)d)182 TranslationMessage.language == pofile.language,
181 FROM TranslationMessage AS tm_ids183 getattr(TranslationMessage, msgstr_column_name).is_in(Select(
182 JOIN TranslationTemplateItem184 POTranslation.id,
183 ON tm_ids.potmsgset=TranslationTemplateItem.potmsgset185 And(
184 WHERE186 POTranslation.id.is_in(Select(
185 TranslationTemplateItem.potemplate187 getattr(tm_ids, msgstr_column_name),
186 = %(potemplate)s AND188 tables=(
187 TranslationTemplateItem.sequence > 0 AND189 tm_ids,
188 tm_ids.language=%(language)s190 Join(
189 ) AND191 TranslationTemplateItem,
190 POTranslation.translation192 tm_ids.potmsgset ==
191 ILIKE '%%' || %(text)s || '%%')193 TranslationTemplateItem.potmsgsetID)),
192 """ % dict(potemplate=quote(pofile.potemplate),194 where=And(
193 language=quote(pofile.language),195 TranslationTemplateItem.potemplate ==
194 plural_form=plural_form,196 pofile.potemplate,
195 text=quote_like(text))197 TranslationTemplateItem.sequence > 0,
196 return translation_match198 tm_ids.language == pofile.language),
199 distinct=True)),
200 Like(
201 POTranslation.translation,
202 u"%" + text.translate(like_escape) + u"%",
203 u"!", case_sensitive=False))))))
197204
198 def _getTemplateSearchQuery(self, text):205 def _getTemplateSearchQuery(self, text):
199 """Query for finding `text` in msgids of this POFile.206 """Query for finding `text` in msgids of this POFile.
200 """207 """
201 english_match = """208 def select_potmsgsets(column_name):
202 -- Step 1a: get POTMsgSets where msgid_singular contains `text`209 # Get POTMsgSets where `column_name` contains `text`.
203 -- To avoid seqscans on POMsgID table (what LIKE usually210 # To avoid seqscans on POMsgID table (which LIKE usually does),
204 -- does), we do ILIKE comparison on them in a subselect first211 # we do ILIKE comparison on them in a subselect first filtered
205 -- filtered by this POTemplate.212 # by this POTemplate.
206 SELECT POTMsgSet.id213 msgid_column = getattr(POTMsgSet, column_name)
207 FROM POTMsgSet214 msgid_columnID = getattr(POTMsgSet, column_name + "ID")
208 JOIN TranslationTemplateItem215 return Select(
209 ON TranslationTemplateItem.potmsgset=POTMsgSet.id AND216 POTMsgSet.id,
210 TranslationTemplateItem.potemplate=%s217 tables=(
211 WHERE218 POTMsgSet,
212 (POTMsgSet.msgid_singular IS NOT NULL AND219 Join(
213 POTMsgSet.msgid_singular IN (220 TranslationTemplateItem,
214 SELECT POMsgID.id FROM POMsgID221 And(
215 WHERE id IN (222 TranslationTemplateItem.potmsgset == POTMsgSet.id,
216 SELECT DISTINCT(POTMsgSet.msgid_singular)223 TranslationTemplateItem.potemplate ==
217 FROM POTMsgSet224 self.potemplate))),
218 JOIN TranslationTemplateItem225 where=And(
219 ON TranslationTemplateItem.potmsgset = POTMsgSet.id226 msgid_column != None,
220 WHERE227 msgid_columnID.is_in(Select(
221 TranslationTemplateItem.potemplate=%s AND228 POMsgID.id,
222 TranslationTemplateItem.sequence > 0229 And(
223 ) AND230 POMsgID.id.is_in(Select(
224 msgid ILIKE '%%' || %s || '%%'))231 msgid_columnID,
225 UNION232 tables=(
226 -- Step 1b: like above, just on msgid_plural.233 POTMsgSet,
227 SELECT POTMsgSet.id234 Join(
228 FROM POTMsgSet235 TranslationTemplateItem,
229 JOIN TranslationTemplateItem236 TranslationTemplateItem.potmsgset ==
230 ON TranslationTemplateItem.potmsgset=POTMsgSet.id AND237 POTMsgSet.id)),
231 TranslationTemplateItem.potemplate=%s238 where=And(
232 WHERE239 TranslationTemplateItem.potemplate ==
233 (POTMsgSet.msgid_plural IS NOT NULL AND240 self.potemplate,
234 POTMsgSet.msgid_plural IN (241 TranslationTemplateItem.sequence > 0))),
235 SELECT POMsgID.id FROM POMsgID242 Like(
236 WHERE id IN (243 POMsgID.msgid,
237 SELECT DISTINCT(POTMsgSet.msgid_plural)244 u"%" + text.translate(like_escape) + u"%",
238 FROM POTMsgSet245 u"!", case_sensitive=False))))))
239 JOIN TranslationTemplateItem246
240 ON TranslationTemplateItem.potmsgset = POTMsgSet.id247 return Union(
241 WHERE248 select_potmsgsets("msgid_singular"),
242 TranslationTemplateItem.potemplate=%s AND249 select_potmsgsets("msgid_plural"))
243 TranslationTemplateItem.sequence > 0
244 ) AND
245 msgid ILIKE '%%' || %s || '%%'))
246 """ % (quote(self.potemplate), quote(self.potemplate),
247 quote_like(text),
248 quote(self.potemplate), quote(self.potemplate),
249 quote_like(text))
250 return english_match
251250
252 def _getOrderedPOTMsgSets(self, origin_tables, query):251 def _getOrderedPOTMsgSets(self, origin_tables, query):
253 """Find all POTMsgSets matching `query` from `origin_tables`.252 """Find all POTMsgSets matching `query` from `origin_tables`.
@@ -255,8 +254,6 @@ class POFileMixIn(RosettaStats):
255 Orders the result by TranslationTemplateItem.sequence which must254 Orders the result by TranslationTemplateItem.sequence which must
256 be among `origin_tables`.255 be among `origin_tables`.
257 """256 """
258 if isinstance(query, six.string_types):
259 query = SQL(query)
260 results = IMasterStore(POTMsgSet).using(origin_tables).find(257 results = IMasterStore(POTMsgSet).using(origin_tables).find(
261 POTMsgSet, query)258 POTMsgSet, query)
262 return results.order_by(TranslationTemplateItem.sequence)259 return results.order_by(TranslationTemplateItem.sequence)
@@ -264,10 +261,9 @@ class POFileMixIn(RosettaStats):
264 def findPOTMsgSetsContaining(self, text):261 def findPOTMsgSetsContaining(self, text):
265 """See `IPOFile`."""262 """See `IPOFile`."""
266 clauses = [263 clauses = [
267 'TranslationTemplateItem.potemplate = %s' % sqlvalues(264 TranslationTemplateItem.potemplate == self.potemplate,
268 self.potemplate),265 TranslationTemplateItem.potmsgset == POTMsgSet.id,
269 'TranslationTemplateItem.potmsgset = POTMsgSet.id',266 TranslationTemplateItem.sequence > 0,
270 'TranslationTemplateItem.sequence > 0',
271 ]267 ]
272268
273 if text is not None:269 if text is not None:
@@ -291,11 +287,10 @@ class POFileMixIn(RosettaStats):
291 self, plural_form, text)287 self, plural_form, text)
292 search_clauses.append(translation_match)288 search_clauses.append(translation_match)
293289
294 clauses.append(290 clauses.append(POTMsgSet.id.is_in(Union(*search_clauses)))
295 "POTMsgSet.id IN (" + " UNION ".join(search_clauses) + ")")
296291
297 return self._getOrderedPOTMsgSets(292 return self._getOrderedPOTMsgSets(
298 [POTMsgSet, TranslationTemplateItem], ' AND '.join(clauses))293 [POTMsgSet, TranslationTemplateItem], And(clauses))
299294
300 def getFullLanguageCode(self):295 def getFullLanguageCode(self):
301 """See `IPOFile`."""296 """See `IPOFile`."""
@@ -422,27 +417,22 @@ class POFile(SQLBase, POFileMixIn):
422 @property417 @property
423 def contributors(self):418 def contributors(self):
424 """See `IPOFile`."""419 """See `IPOFile`."""
425 # Avoid circular import.420 # Avoid circular imports.
426 from lp.registry.model.person import Person421 from lp.registry.model.person import Person
422 from lp.translations.model.pofiletranslator import POFileTranslator
427423
428 # Translation credit messages are "translated" by424 # Translation credit messages are "translated" by
429 # rosetta_experts. Shouldn't show up in contributors lists425 # rosetta_experts. Shouldn't show up in contributors lists
430 # though.426 # though.
431 admin_team = getUtility(ILaunchpadCelebrities).rosetta_experts427 admin_team = getUtility(ILaunchpadCelebrities).rosetta_experts
432428
433 contributors = Person.select("""429 contributors = IStore(Person).find(
434 POFileTranslator.person = Person.id AND430 Person,
435 POFileTranslator.person <> %s AND431 POFileTranslator.person == Person.id,
436 POFileTranslator.pofile = %s""" % sqlvalues(admin_team, self),432 POFileTranslator.person != admin_team,
437 clauseTables=["POFileTranslator"],433 POFileTranslator.pofile == self).config(distinct=True)
438 distinct=True,434 contributors = contributors.order_by(*Person._storm_sortingColumns)
439 # XXX: kiko 2006-10-19:435 contributors = contributors.config(distinct=True)
440 # We can't use Person.sortingColumns because this is a
441 # distinct query. To use it we'd need to add the sorting
442 # function to the column results and then ignore it -- just
443 # like selectAlso does, ironically.
444 orderBy=["Person.displayname", "Person.name"])
445
446 return contributors436 return contributors
447437
448 def prepareTranslationCredits(self, potmsgset):438 def prepareTranslationCredits(self, potmsgset):
@@ -548,7 +538,7 @@ class POFile(SQLBase, POFileMixIn):
548 """Get query data for fetching all POTMsgSets with translations.538 """Get query data for fetching all POTMsgSets with translations.
549539
550 Return a tuple of SQL (clauses, clause_tables) to be used with540 Return a tuple of SQL (clauses, clause_tables) to be used with
551 POTMsgSet.select().541 POTMsgSet queries.
552 """542 """
553 flag_name = getUtility(ITranslationSideTraitsSet).getForTemplate(543 flag_name = getUtility(ITranslationSideTraitsSet).getForTemplate(
554 self.potemplate).flag_name544 self.potemplate).flag_name
@@ -1480,8 +1470,8 @@ class POFileSet:
14801470
1481 def getBatch(self, starting_id, batch_size):1471 def getBatch(self, starting_id, batch_size):
1482 """See `IPOFileSet`."""1472 """See `IPOFileSet`."""
1483 return POFile.select(1473 return IStore(POFile).find(
1484 "id >= %s" % quote(starting_id), orderBy="id", limit=batch_size)1474 POFile, POFile.id >= starting_id).order_by(POFile.id)[:batch_size]
14851475
1486 def getPOFilesWithTranslationCredits(self, untranslated=False):1476 def getPOFilesWithTranslationCredits(self, untranslated=False):
1487 """See `IPOFileSet`."""1477 """See `IPOFileSet`."""

Subscribers

People subscribed via source and target branches

to status/vote changes: