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 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
1diff --git a/lib/lp/translations/model/pofile.py b/lib/lp/translations/model/pofile.py
2index 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`."""

Subscribers

People subscribed via source and target branches

to status/vote changes: