Merge lp:~openerp-dev/openobject-server/7.0-unaccentwrapper-chs into lp:openobject-server/7.0

[Christophe Simonis (OpenERP)]

[lp:~openerp-dev/openobject-server/7.0-unaccentwrapper-chs]

5273. By Christophe Simonis (OpenERP) on 2014-04-04

[IMP] expression: new helper for generating unaccent aware sql queries. Adapt _auto_join tests to use it.

5274. By Christophe Simonis (OpenERP) on 2014-04-04

[FIX] res.partner: name_search: respect unaccent flag

=== modified file 'openerp/addons/base/res/res_partner.py'
--- openerp/addons/base/res/res_partner.py	2014-04-03 14:37:17 +0000
+++ openerp/addons/base/res/res_partner.py	2014-04-04 14:16:38 +0000
@@ -29,6 +29,7 @@
 from openerp import SUPERUSER_ID
 from openerp import pooler, tools
 from openerp.osv import osv, fields
+from openerp.osv.expression import get_unaccent_wrapper
 from openerp.tools.translate import _
 from openerp.tools.yaml_import import is_comment
 
@@ -610,27 +611,32 @@
             if operator in ('=ilike', '=like'):
                 operator = operator[1:]
 
+            unaccent = get_unaccent_wrapper(cr)
+
             # TODO: simplify this in trunk with `display_name`, once it is stored
             # Perf note: a CTE expression (WITH ...) seems to have an even higher cost
             #            than this query with duplicated CASE expressions. The bulk of
             #            the cost is the ORDER BY, and it is inevitable if we want
             #            relevant results for the next step, otherwise we'd return
             #            a random selection of `limit` results.
-            query = ('''SELECT res_partner.id FROM res_partner
-                                          LEFT JOIN res_partner company
-                                               ON res_partner.parent_id = company.id'''
-                        + where_str + ''' (res_partner.email ''' + operator + ''' %s OR
-                              CASE
-                                   WHEN company.id IS NULL OR res_partner.is_company
-                                       THEN res_partner.name
-                                   ELSE company.name || ', ' || res_partner.name
-                              END ''' + operator + ''' %s)
-                        ORDER BY
-                              CASE
-                                   WHEN company.id IS NULL OR res_partner.is_company
-                                       THEN res_partner.name
-                                   ELSE company.name || ', ' || res_partner.name
-                              END''')
+
+            display_name = """CASE WHEN company.id IS NULL OR res_partner.is_company
+                                   THEN {partner_name}
+                                   ELSE {company_name} || ', ' || {partner_name}
+                               END""".format(partner_name=unaccent('res_partner.name'),
+                                             company_name=unaccent('company.name'))
+
+            query = """SELECT res_partner.id
+                         FROM res_partner
+                    LEFT JOIN res_partner company
+                           ON res_partner.parent_id = company.id
+                      {where} ({email} {operator} {percent}
+                           OR {display_name} {operator} {percent})
+                     ORDER BY {display_name}
+                    """.format(where=where_str, operator=operator,
+                               email=unaccent('res_partner.email'),
+                               percent=unaccent('%s'),
+                               display_name=display_name)
 
             where_clause_params += [search_name, search_name]
             if limit:

=== modified file 'openerp/addons/base/tests/test_expression.py'
--- openerp/addons/base/tests/test_expression.py	2014-04-02 11:27:30 +0000
+++ openerp/addons/base/tests/test_expression.py	2014-04-04 14:16:38 +0000
@@ -1,4 +1,6 @@
 import unittest2
+
+from openerp.osv.expression import get_unaccent_wrapper
 from openerp.osv.orm import BaseModel
 import openerp.tests.common as common
 
@@ -123,6 +125,7 @@
 
     def test_20_auto_join(self):
         registry, cr, uid = self.registry, self.cr, self.uid
+        unaccent = get_unaccent_wrapper(cr)
 
         # Get models
         partner_obj = registry('res.partner')
@@ -179,8 +182,11 @@
         sql_query = self.query_list[0].get_sql()
         self.assertIn('res_partner_bank', sql_query[0],
             "_auto_join off: ('bank_ids.name', 'like', '..') first query incorrect main table")
-        self.assertIn('"res_partner_bank"."name" like %s', sql_query[1],
+
+        expected = "%s like %s" % (unaccent('"res_partner_bank"."name"'), unaccent('%s'))
+        self.assertIn(expected, sql_query[1],
             "_auto_join off: ('bank_ids.name', 'like', '..') first query incorrect where condition")
+        
         self.assertEqual(set(['%' + name_test + '%']), set(sql_query[2]),
             "_auto_join off: ('bank_ids.name', 'like', '..') first query incorrect parameter")
         sql_query = self.query_list[2].get_sql()
@@ -216,8 +222,11 @@
             "_auto_join on: ('bank_ids.name', 'like', '..') query incorrect main table")
         self.assertIn('"res_partner_bank" as "res_partner__bank_ids"', sql_query[0],
             "_auto_join on: ('bank_ids.name', 'like', '..') query incorrect join")
-        self.assertIn('"res_partner__bank_ids"."name" like %s', sql_query[1],
+
+        expected = "%s like %s" % (unaccent('"res_partner__bank_ids"."name"'), unaccent('%s'))
+        self.assertIn(expected, sql_query[1],
             "_auto_join on: ('bank_ids.name', 'like', '..') query incorrect where condition")
+        
         self.assertIn('"res_partner"."id"="res_partner__bank_ids"."partner_id"', sql_query[1],
             "_auto_join on: ('bank_ids.name', 'like', '..') query incorrect join condition")
         self.assertEqual(set(['%' + name_test + '%']), set(sql_query[2]),
@@ -295,8 +304,11 @@
         sql_query = self.query_list[0].get_sql()
         self.assertIn('"res_country"', sql_query[0],
             "_auto_join on for state_id: ('state_id.country_id.code', 'like', '..') query 1 incorrect main table")
-        self.assertIn('"res_country"."code" like %s', sql_query[1],
+
+        expected = "%s like %s" % (unaccent('"res_country"."code"'), unaccent('%s'))
+        self.assertIn(expected, sql_query[1],
             "_auto_join on for state_id: ('state_id.country_id.code', 'like', '..') query 1 incorrect where condition")
+
         self.assertEqual(['%' + name_test + '%'], sql_query[2],
             "_auto_join on for state_id: ('state_id.country_id.code', 'like', '..') query 1 incorrect parameter")
         sql_query = self.query_list[1].get_sql()
@@ -326,8 +338,11 @@
             "_auto_join on for country_id: ('state_id.country_id.code', 'like', '..') query 1 incorrect main table")
         self.assertIn('"res_country" as "res_country_state__country_id"', sql_query[0],
             "_auto_join on for country_id: ('state_id.country_id.code', 'like', '..') query 1 incorrect join")
-        self.assertIn('"res_country_state__country_id"."code" like %s', sql_query[1],
+
+        expected = "%s like %s" % (unaccent('"res_country_state__country_id"."code"'), unaccent('%s'))
+        self.assertIn(expected, sql_query[1],
             "_auto_join on for country_id: ('state_id.country_id.code', 'like', '..') query 1 incorrect where condition")
+        
         self.assertIn('"res_country_state"."country_id"="res_country_state__country_id"."id"', sql_query[1],
             "_auto_join on for country_id: ('state_id.country_id.code', 'like', '..') query 1 incorrect join condition")
         self.assertEqual(['%' + name_test + '%'], sql_query[2],
@@ -357,8 +372,11 @@
             "_auto_join on: ('state_id.country_id.code', 'like', '..') query incorrect join")
         self.assertIn('"res_country" as "res_partner__state_id__country_id"', sql_query[0],
             "_auto_join on: ('state_id.country_id.code', 'like', '..') query incorrect join")
-        self.assertIn('"res_partner__state_id__country_id"."code" like %s', sql_query[1],
+
+        expected = "%s like %s" % (unaccent('"res_partner__state_id__country_id"."code"'), unaccent('%s'))
+        self.assertIn(expected, sql_query[1],
             "_auto_join on: ('state_id.country_id.code', 'like', '..') query incorrect where condition")
+        
         self.assertIn('"res_partner"."state_id"="res_partner__state_id"."id"', sql_query[1],
             "_auto_join on: ('state_id.country_id.code', 'like', '..') query incorrect join condition")
         self.assertIn('"res_partner__state_id"."country_id"="res_partner__state_id__country_id"."id"', sql_query[1],
@@ -384,7 +402,9 @@
             "_auto_join on one2many with domains incorrect result")
         # Test produced queries that domains effectively present
         sql_query = self.query_list[0].get_sql()
-        self.assertIn('"res_partner__child_ids__bank_ids"."acc_number" like %s', sql_query[1],
+        
+        expected = "%s like %s" % (unaccent('"res_partner__child_ids__bank_ids"."acc_number"'), unaccent('%s'))
+        self.assertIn(expected, sql_query[1],
             "_auto_join on one2many with domains incorrect result")
         # TDE TODO: check first domain has a correct table name
         self.assertIn('"res_partner__child_ids"."name" = %s', sql_query[1],

=== modified file 'openerp/osv/expression.py'
--- openerp/osv/expression.py	2014-04-03 08:45:24 +0000
+++ openerp/osv/expression.py	2014-04-04 14:16:38 +0000
@@ -430,6 +430,10 @@
     cr.execute('SELECT distinct("%s") FROM "%s" where "%s" is not null' % (select_field, from_table, select_field))
     return [r[0] for r in cr.fetchall()]
 
+def get_unaccent_wrapper(cr):
+    if openerp.modules.registry.RegistryManager.get(cr.dbname).has_unaccent:
+        return lambda x: "unaccent(%s)" % (x,)
+    return lambda x: x
 
 # --------------------------------------------------
 # ExtendedLeaf class for managing leafs and contexts
@@ -631,7 +635,7 @@
             :attr list expression: the domain expression, that will be normalized
                 and prepared
         """
-        self.has_unaccent = openerp.modules.registry.RegistryManager.get(cr.dbname).has_unaccent
+        self._unaccent = get_unaccent_wrapper(cr)
         self.joins = []
         self.root_model = table
 
@@ -1020,7 +1024,6 @@
                     push(create_substitution_leaf(leaf, (left, operator, right), working_model))
 
                 elif field.translate and right:
-                    field = left
                     need_wildcard = operator in ('like', 'ilike', 'not like', 'not ilike')
                     sql_operator = {'=like': 'like', '=ilike': 'ilike'}.get(operator, operator)
                     if need_wildcard:
@@ -1032,16 +1035,13 @@
                         sql_operator = sql_operator[4:] if sql_operator[:3] == 'not' else '='
                         inselect_operator = 'not inselect'
 
-                    trans_left = 'value'
-                    left = '"%s"' % (left,)
-                    instr = '%s'
-
-                    if self.has_unaccent and sql_operator.endswith('like'):
-                        assert isinstance(right, basestring)
-                        trans_left = 'unaccent(value)'
-                        left = 'unaccent(%s)' % (left,)
-                        instr = 'unaccent(%s)'
-                    elif sql_operator == 'in':
+                    unaccent = self._unaccent if sql_operator.endswith('like') else lambda x: x
+
+                    trans_left = unaccent('value')
+                    quote_left = unaccent(_quote(left))
+                    instr = unaccent('%s')
+
+                    if sql_operator == 'in':
                         # params will be flatten by to_sql() => expand the placeholders
                         instr = '(%s)' % ', '.join(['%s'] * len(right))
 
@@ -1057,10 +1057,10 @@
                                      WHERE {left} {operator} {right}
                                    )
                                 """.format(trans_left=trans_left, operator=sql_operator,
-                                           right=instr, table=working_model._table, left=left)
+                                           right=instr, table=working_model._table, left=quote_left)
 
                     params = (
-                        working_model._name + ',' + field,
+                        working_model._name + ',' + left,
                         context.get('lang') or 'en_US',
                         'model',
                         right,
@@ -1184,10 +1184,9 @@
 
             if left in model._columns:
                 format = need_wildcard and '%s' or model._columns[left]._symbol_set[0]
-                if self.has_unaccent and sql_operator.endswith('like'):
-                    query = '(unaccent(%s."%s") %s unaccent(%s))' % (table_alias, left, sql_operator, format)
-                else:
-                    query = '(%s."%s" %s %s)' % (table_alias, left, sql_operator, format)
+                unaccent = self._unaccent if sql_operator.endswith('like') else lambda x: x
+                column = '%s.%s' % (table_alias, _quote(left))
+                query = '(%s %s %s)' % (unaccent(column), sql_operator, unaccent(format))
             elif left in MAGIC_COLUMNS:
                     query = "(%s.\"%s\" %s %%s)" % (table_alias, left, sql_operator)
                     params = right