Merge lp:~wgrant/launchpad/bug-736005-trivialise into lp:launchpad

[William Grant]

Rewrite the POFile:+translate text search query to perform adequately.

Rewrite translations text search to use the simplest query that can work, which happens to optimise best nowadays. The old code is littered with comments describing how simpler queries degrade to seqscans of POMsgID and POTranslation, but that's not the case in 9.3 with a sensible simple query.

https://pastebin.canonical.com/126018/ compares the new and old queries. Further optimisation will come soon once POTMsgSet.msgid_(singular|plural) are denormalised to TranslationTemplateItem, and the COUNT(*) queries are eliminated from large template views.

[lp:~wgrant/launchpad/bug-736005-trivialise]

17361. By William Grant on 2015-02-21

Drop duplicate join condition.

17362. By William Grant on 2015-02-21

Fix distinct by including TTI in the result.

17363. By William Grant on 2015-02-21

Don't double-join POMsgID and POTranslation.

Unmerged revisions

17363. By William Grant on 2015-02-21

Don't double-join POMsgID and POTranslation.

17362. By William Grant on 2015-02-21

Fix distinct by including TTI in the result.

17361. By William Grant on 2015-02-21

Drop duplicate join condition.

17360. By William Grant on 2015-02-20

Distinctify.

17359. By William Grant on 2015-02-20

Reimplement XPI translation search.

17358. By William Grant on 2015-02-20

Support multiple plural forms in search rewrite.

17357. By William Grant on 2015-02-20

Rewrite translation search to the simplest form that happens to perform a lot better than the old planner workarounds.

=== modified file 'lib/lp/translations/model/pofile.py'
--- lib/lp/translations/model/pofile.py	2013-06-20 05:50:00 +0000
+++ lib/lp/translations/model/pofile.py	2015-02-21 03:06:32 +0000
@@ -13,6 +13,7 @@
     ]
 
 import datetime
+from operator import itemgetter
 
 import pytz
 from sqlobject import (
@@ -49,6 +50,7 @@
 from lp.registry.interfaces.person import validate_public_person
 from lp.services.database.constants import UTC_NOW
 from lp.services.database.datetimecol import UtcDateTimeCol
+from lp.services.database.decoratedresultset import DecoratedResultSet
 from lp.services.database.interfaces import (
     IMasterStore,
     IStore,
@@ -56,7 +58,6 @@
 from lp.services.database.sqlbase import (
     flush_database_updates,
     quote,
-    quote_like,
     SQLBase,
     sqlvalues,
     )
@@ -96,6 +97,7 @@
     credits_message_str,
     POTMsgSet,
     )
+from lp.translations.model.potranslation import POTranslation
 from lp.translations.model.translationimportqueue import collect_import_info
 from lp.translations.model.translationmessage import (
     make_plurals_sql_fragment,
@@ -165,102 +167,6 @@
         header.has_plural_forms = self.potemplate.hasPluralMessage()
         return header
 
-    def _getTranslationSearchQuery(self, pofile, plural_form, text):
-        """Query to find `text` in `plural_form` translations of a `pofile`.
-
-        This produces a list of clauses that can be used to search for
-        TranslationMessages containing `text` in their msgstr[`plural_form`].
-        Returned values are POTMsgSet ids containing them, expected to be
-        used in a UNION across all plural forms.
-        """
-        translation_match = """
-        -- Find translations containing `text`.
-        -- Like in findPOTMsgSetsContaining(), to avoid seqscans on
-        -- POTranslation table, we do ILIKE comparison on them in
-        -- a subselect which is first filtered by the POFile.
-          SELECT TranslationMessage.potmsgset
-            FROM TranslationMessage
-            JOIN TranslationTemplateItem
-              ON TranslationMessage.potmsgset
-                   = TranslationTemplateItem.potmsgset
-            WHERE
-              TranslationTemplateItem.potemplate = %(potemplate)s AND
-              TranslationMessage.language = %(language)s AND
-              TranslationMessage.msgstr%(plural_form)d IN (
-                SELECT POTranslation.id FROM POTranslation WHERE
-                  POTranslation.id IN (
-                    SELECT DISTINCT(msgstr%(plural_form)d)
-                      FROM TranslationMessage AS tm_ids
-                      JOIN TranslationTemplateItem
-                        ON tm_ids.potmsgset=TranslationTemplateItem.potmsgset
-                      WHERE
-                        TranslationTemplateItem.potemplate
-                          = %(potemplate)s AND
-                        TranslationTemplateItem.sequence > 0 AND
-                        tm_ids.language=%(language)s
-                  ) AND
-                  POTranslation.translation
-                    ILIKE '%%' || %(text)s || '%%')
-                    """ % dict(potemplate=quote(pofile.potemplate),
-                               language=quote(pofile.language),
-                               plural_form=plural_form,
-                               text=quote_like(text))
-        return translation_match
-
-    def _getTemplateSearchQuery(self, text):
-        """Query for finding `text` in msgids of this POFile.
-        """
-        english_match = """
-        -- Step 1a: get POTMsgSets where msgid_singular contains `text`
-        -- To avoid seqscans on POMsgID table (what LIKE usually
-        -- does), we do ILIKE comparison on them in a subselect first
-        -- filtered by this POTemplate.
-          SELECT POTMsgSet.id
-            FROM POTMsgSet
-            JOIN TranslationTemplateItem
-              ON TranslationTemplateItem.potmsgset=POTMsgSet.id AND
-                 TranslationTemplateItem.potemplate=%s
-            WHERE
-              (POTMsgSet.msgid_singular IS NOT NULL AND
-               POTMsgSet.msgid_singular IN (
-                 SELECT POMsgID.id FROM POMsgID
-                   WHERE id IN (
-                     SELECT DISTINCT(msgid_singular)
-                       FROM POTMsgSet
-                       JOIN TranslationTemplateItem
-                         ON TranslationTemplateItem.potmsgset = POTMsgSet.id
-                       WHERE
-                         TranslationTemplateItem.potemplate=%s AND
-                         TranslationTemplateItem.sequence > 0
-                   ) AND
-                   msgid ILIKE '%%' || %s || '%%'))
-          UNION
-        -- Step 1b: like above, just on msgid_plural.
-          SELECT POTMsgSet.id
-            FROM POTMsgSet
-            JOIN TranslationTemplateItem
-              ON TranslationTemplateItem.potmsgset=POTMsgSet.id AND
-                 TranslationTemplateItem.potemplate=%s
-            WHERE
-              (POTMsgSet.msgid_plural IS NOT NULL AND
-               POTMsgSet.msgid_plural IN (
-                 SELECT POMsgID.id FROM POMsgID
-                   WHERE id IN (
-                     SELECT DISTINCT(msgid_plural)
-                       FROM POTMsgSet
-                       JOIN TranslationTemplateItem
-                         ON TranslationTemplateItem.potmsgset = POTMsgSet.id
-                       WHERE
-                         TranslationTemplateItem.potemplate=%s AND
-                         TranslationTemplateItem.sequence > 0
-                   ) AND
-                   msgid ILIKE '%%' || %s || '%%'))
-            """ % (quote(self.potemplate), quote(self.potemplate),
-                   quote_like(text),
-                   quote(self.potemplate), quote(self.potemplate),
-                   quote_like(text))
-        return english_match
-
     def _getOrderedPOTMsgSets(self, origin_tables, query):
         """Find all POTMsgSets matching `query` from `origin_tables`.
 
@@ -271,41 +177,81 @@
             POTMsgSet, SQL(query))
         return results.order_by(TranslationTemplateItem.sequence)
 
+    def _getTranslationSearchBits(self, pofile):
+        ThisTM = ClassAlias(TranslationMessage)
+        ThisPT = ClassAlias(POTranslation)
+        str_id_cols = [
+            getattr(ThisTM, 'msgstr%dID' % plural_form)
+            for plural_form in range(pofile.plural_forms)]
+        origin = [
+            LeftJoin(
+                ThisTM,
+                And(
+                    ThisTM.potmsgsetID ==
+                        TranslationTemplateItem.potmsgsetID,
+                    ThisTM.languageID == pofile.languageID)),
+            LeftJoin(
+                ThisPT,
+                Or(*(ThisPT.id == col for col in str_id_cols))),
+            ]
+        search_options = [ThisPT.translation]
+        return origin, search_options
+
     def findPOTMsgSetsContaining(self, text):
         """See `IPOFile`."""
+        origin = [
+            POTMsgSet,
+            Join(
+                TranslationTemplateItem,
+                TranslationTemplateItem.potmsgsetID == POTMsgSet.id),
+            ]
         clauses = [
-            'TranslationTemplateItem.potemplate = %s' % sqlvalues(
-                self.potemplate),
-            'TranslationTemplateItem.potmsgset = POTMsgSet.id',
-            'TranslationTemplateItem.sequence > 0',
+            TranslationTemplateItem.potemplateID == self.potemplate.id,
+            TranslationTemplateItem.sequence > 0,
             ]
 
         if text is not None:
             assert len(text) > 1, (
                 "You can not search for strings shorter than 2 characters.")
 
+            # A list of string columns, of which at least one must match
+            # the search term.
+            search_options = []
+
+            # Search the English strings.
             if self.potemplate.uses_english_msgids:
-                english_match = self._getTemplateSearchQuery(text)
+                origin.extend([
+                    Join(
+                        POMsgID,
+                        Or(
+                            POMsgID.id == POTMsgSet.msgid_singularID,
+                            POMsgID.id == POTMsgSet.msgid_pluralID)),
+                    ])
+                search_options.extend([POMsgID.msgid])
             else:
-                # If msgids are not in English, use English PO file
-                # to fetch original strings instead.
-                en_pofile = self.potemplate.getPOFileByLang('en')
-                english_match = self._getTranslationSearchQuery(
-                    en_pofile, 0, text)
+                en_po = self.potemplate.getPOFileByLang('en')
+                en_origin, en_options = self._getTranslationSearchBits(en_po)
+                origin.extend(en_origin)
+                search_options.extend(en_options)
 
-            # Do not look for translations in a DummyPOFile.
-            search_clauses = [english_match]
+            # Search the translations themselves, unless this is a
+            # DummyPOFile.
             if self.id is not None:
-                for plural_form in range(self.plural_forms):
-                    translation_match = self._getTranslationSearchQuery(
-                        self, plural_form, text)
-                    search_clauses.append(translation_match)
-
-            clauses.append(
-                "POTMsgSet.id IN (" + " UNION ".join(search_clauses) + ")")
-
-        return self._getOrderedPOTMsgSets(
-            [POTMsgSet, TranslationTemplateItem], ' AND '.join(clauses))
+                po_origin, po_options = self._getTranslationSearchBits(self)
+                origin.extend(po_origin)
+                search_options.extend(po_options)
+
+            # Case-insensitively substring-match the search term against
+            # each searchable column.
+            clauses.append(Or(*(
+                str.like('%%%s%%' % text, case_sensitive=False)
+                for str in search_options)))
+
+        result = IMasterStore(POTMsgSet).using(*origin).find(
+            (POTMsgSet, TranslationTemplateItem),
+            *clauses).config(distinct=True).order_by(
+            TranslationTemplateItem.sequence)
+        return DecoratedResultSet(result, itemgetter(0))
 
     def getFullLanguageCode(self):
         """See `IPOFile`."""