1 # -*- coding: utf-8 -*-
2 ##############################################################################
4 # OpenERP, Open Source Management Solution
5 # Copyright (C) 2004-2009 Tiny SPRL (<http://tiny.be>).
7 # This program is free software: you can redistribute it and/or modify
8 # it under the terms of the GNU Affero General Public License as
9 # published by the Free Software Foundation, either version 3 of the
10 # License, or (at your option) any later version.
12 # This program is distributed in the hope that it will be useful,
13 # but WITHOUT ANY WARRANTY; without even the implied warranty of
14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 # GNU Affero General Public License for more details.
17 # You should have received a copy of the GNU Affero General Public License
18 # along with this program. If not, see <http://www.gnu.org/licenses/>.
20 ##############################################################################
22 """ Domain expression processing
24 The main duty of this module is to compile a domain expression into a
25 SQL query. A lot of things should be documented here, but as a first
26 step in the right direction, some tests in test_osv_expression.yml
27 might give you some additional information.
29 For legacy reasons, a domain uses an inconsistent two-levels abstract
30 syntax (domains are regular Python data structures). At the first
31 level, a domain is an expression made of terms (sometimes called
32 leaves) and (domain) operators used in prefix notation. The available
33 operators at this level are '!', '&', and '|'. '!' is a unary 'not',
34 '&' is a binary 'and', and '|' is a binary 'or'. For instance, here
35 is a possible domain. (<term> stands for an arbitrary term, more on
38 ['&', '!', <term1>, '|', <term2>, <term3>]
40 It is equivalent to this pseudo code using infix notation::
42 (not <term1>) and (<term2> or <term3>)
44 The second level of syntax deals with the term representation. A term
45 is a triple of the form (left, operator, right). That is, a term uses
46 an infix notation, and the available operators, and possible left and
47 right operands differ with those of the previous level. Here is a
50 ('company_id.name', '=', 'OpenERP')
52 The left and right operand don't have the same possible values. The
53 left operand is field name (related to the model for which the domain
54 applies). Actually, the field name can use the dot-notation to
55 traverse relationships. The right operand is a Python value whose
56 type should match the used operator and field type. In the above
57 example, a string is used because the name field of a company has type
58 string, and because we use the '=' operator. When appropriate, a 'in'
59 operator can be used, and thus the right operand should be a list.
61 Note: the non-uniform syntax could have been more uniform, but this
62 would hide an important limitation of the domain syntax. Say that the
63 term representation was ['=', 'company_id.name', 'OpenERP']. Used in a
64 complete domain, this would look like::
66 ['!', ['=', 'company_id.name', 'OpenERP']]
68 and you would be tempted to believe something like this would be
71 ['!', ['=', 'company_id.name', ['&', ..., ...]]]
73 That is, a domain could be a valid operand. But this is not the
74 case. A domain is really limited to a two-level nature, and can not
75 take a recursive form: a domain is not a valid second-level operand.
77 Unaccent - Accent-insensitive search
79 OpenERP will use the SQL function 'unaccent' when available for the
80 'ilike' and 'not ilike' operators, and enabled in the configuration.
81 Normally the 'unaccent' function is obtained from `the PostgreSQL
82 'unaccent' contrib module
83 <http://developer.postgresql.org/pgdocs/postgres/unaccent.html>`_.
85 .. todo: The following explanation should be moved in some external
88 The steps to install the module might differ on specific PostgreSQL
89 versions. We give here some instruction for PostgreSQL 9.x on a
92 Ubuntu doesn't come yet with PostgreSQL 9.x, so an alternative package
93 source is used. We use Martin Pitt's PPA available at
95 <https://launchpad.net/~pitti/+archive/postgresql>`_.
99 > sudo add-apt-repository ppa:pitti/postgresql
100 > sudo apt-get update
102 Once the package list is up-to-date, you have to install PostgreSQL
103 9.0 and its contrib modules.
107 > sudo apt-get install postgresql-9.0 postgresql-contrib-9.0
109 When you want to enable unaccent on some database:
113 > psql9 <database> -f /usr/share/postgresql/9.0/contrib/unaccent.sql
115 Here :program:`psql9` is an alias for the newly installed PostgreSQL
116 9.0 tool, together with the correct port if necessary (for instance if
117 PostgreSQL 8.4 is running on 5432). (Other aliases can be used for
118 createdb and dropdb.)
122 > alias psql9='/usr/lib/postgresql/9.0/bin/psql -p 5433'
124 You can check unaccent is working:
128 > psql9 <database> -c"select unaccent('hélène')"
130 Finally, to instruct OpenERP to really use the unaccent function, you have to
131 start the server specifying the ``--unaccent`` flag.
138 import openerp.modules
139 from openerp.osv import fields
140 from openerp.osv.orm import MAGIC_COLUMNS
141 import openerp.tools as tools
148 DOMAIN_OPERATORS = (NOT_OPERATOR, OR_OPERATOR, AND_OPERATOR)
150 # List of available term operators. It is also possible to use the '<>'
151 # operator, which is strictly the same as '!='; the later should be prefered
152 # for consistency. This list doesn't contain '<>' as it is simpified to '!='
153 # by the normalize_operator() function (so later part of the code deals with
154 # only one representation).
155 # Internals (i.e. not available to the user) 'inselect' and 'not inselect'
156 # operators are also used. In this case its right operand has the form (subselect, params).
157 TERM_OPERATORS = ('=', '!=', '<=', '<', '>', '>=', '=?', '=like', '=ilike',
158 'like', 'not like', 'ilike', 'not ilike', 'in', 'not in',
161 # A subset of the above operators, with a 'negative' semantic. When the
162 # expressions 'in NEGATIVE_TERM_OPERATORS' or 'not in NEGATIVE_TERM_OPERATORS' are used in the code
163 # below, this doesn't necessarily mean that any of those NEGATIVE_TERM_OPERATORS is
164 # legal in the processed term.
165 NEGATIVE_TERM_OPERATORS = ('!=', 'not like', 'not ilike', 'not in')
167 TRUE_LEAF = (1, '=', 1)
168 FALSE_LEAF = (0, '=', 1)
170 TRUE_DOMAIN = [TRUE_LEAF]
171 FALSE_DOMAIN = [FALSE_LEAF]
173 _logger = logging.getLogger(__name__)
176 # --------------------------------------------------
177 # Generic domain manipulation
178 # --------------------------------------------------
180 def normalize_domain(domain):
181 """Returns a normalized version of ``domain_expr``, where all implicit '&' operators
182 have been made explicit. One property of normalized domain expressions is that they
183 can be easily combined together as if they were single domain components.
185 assert isinstance(domain, (list, tuple)), "Domains to normalize must have a 'domain' form: a list or tuple of domain components"
189 expected = 1 # expected number of expressions
190 op_arity = {NOT_OPERATOR: 1, AND_OPERATOR: 2, OR_OPERATOR: 2}
192 if expected == 0: # more than expected, like in [A, B]
193 result[0:0] = [AND_OPERATOR] # put an extra '&' in front
196 if isinstance(token, (list, tuple)): # domain term
199 expected += op_arity.get(token, 0) - 1
200 assert expected == 0, 'This domain is syntactically not correct: %s' % (domain)
204 def combine(operator, unit, zero, domains):
205 """Returns a new domain expression where all domain components from ``domains``
206 have been added together using the binary operator ``operator``. The given
207 domains must be normalized.
209 :param unit: the identity element of the domains "set" with regard to the operation
210 performed by ``operator``, i.e the domain component ``i`` which, when
211 combined with any domain ``x`` via ``operator``, yields ``x``.
212 E.g. [(1,'=',1)] is the typical unit for AND_OPERATOR: adding it
213 to any domain component gives the same domain.
214 :param zero: the absorbing element of the domains "set" with regard to the operation
215 performed by ``operator``, i.e the domain component ``z`` which, when
216 combined with any domain ``x`` via ``operator``, yields ``z``.
217 E.g. [(1,'=',1)] is the typical zero for OR_OPERATOR: as soon as
218 you see it in a domain component the resulting domain is the zero.
219 :param domains: a list of normalized domains.
223 for domain in domains:
231 result = [operator] * (count - 1) + result
236 """AND([D1,D2,...]) returns a domain representing D1 and D2 and ... """
237 return combine(AND_OPERATOR, TRUE_DOMAIN, FALSE_DOMAIN, domains)
241 """OR([D1,D2,...]) returns a domain representing D1 or D2 or ... """
242 return combine(OR_OPERATOR, FALSE_DOMAIN, TRUE_DOMAIN, domains)
245 def distribute_not(domain):
246 """ Distribute any '!' domain operators found inside a normalized domain.
248 Because we don't use SQL semantic for processing a 'left not in right'
249 query (i.e. our 'not in' is not simply translated to a SQL 'not in'),
250 it means that a '! left in right' can not be simply processed
251 by __leaf_to_sql by first emitting code for 'left in right' then wrapping
252 the result with 'not (...)', as it would result in a 'not in' at the SQL
255 This function is thus responsible for pushing any '!' domain operators
256 inside the terms themselves. For example::
258 ['!','&',('user_id','=',4),('partner_id','in',[1,2])]
260 ['|',('user_id','!=',4),('partner_id','not in',[1,2])]
264 """Negates and returns a single domain leaf term,
265 using the opposite operator if possible"""
266 left, operator, right = leaf
275 if operator in ('in', 'like', 'ilike'):
276 operator = 'not ' + operator
277 return [(left, operator, right)]
278 if operator in ('not in', 'not like', 'not ilike'):
279 operator = operator[4:]
280 return [(left, operator, right)]
281 if operator in mapping:
282 operator = mapping[operator]
283 return [(left, operator, right)]
284 return [NOT_OPERATOR, (left, operator, right)]
286 def distribute_negate(domain):
287 """Negate the domain ``subtree`` rooted at domain[0],
288 leaving the rest of the domain intact, and return
289 (negated_subtree, untouched_domain_rest)
291 if is_leaf(domain[0]):
292 return negate(domain[0]), domain[1:]
293 if domain[0] == AND_OPERATOR:
294 done1, todo1 = distribute_negate(domain[1:])
295 done2, todo2 = distribute_negate(todo1)
296 return [OR_OPERATOR] + done1 + done2, todo2
297 if domain[0] == OR_OPERATOR:
298 done1, todo1 = distribute_negate(domain[1:])
299 done2, todo2 = distribute_negate(todo1)
300 return [AND_OPERATOR] + done1 + done2, todo2
303 if domain[0] != NOT_OPERATOR:
304 return [domain[0]] + distribute_not(domain[1:])
305 if domain[0] == NOT_OPERATOR:
306 done, todo = distribute_negate(domain[1:])
307 return done + distribute_not(todo)
310 # --------------------------------------------------
311 # Generic leaf manipulation
312 # --------------------------------------------------
314 def _quote(to_quote):
315 if '"' not in to_quote:
316 return '"%s"' % to_quote
320 def generate_table_alias(src_table_alias, joined_tables=[]):
321 """ Generate a standard table alias name. An alias is generated as following:
322 - the base is the source table name (that can already be an alias)
323 - then, each joined table is added in the alias using a 'link field name'
324 that is used to render unique aliases for a given path
325 - returns a tuple composed of the alias, and the full table alias to be
326 added in a from condition with quoting done
328 - src_table_alias='res_users', join_tables=[]:
329 alias = ('res_users','"res_users"')
330 - src_model='res_users', join_tables=[(res.partner, 'parent_id')]
331 alias = ('res_users__parent_id', '"res_partner" as "res_users__parent_id"')
333 :param model src_table_alias: model source of the alias
334 :param list joined_tables: list of tuples
335 (dst_model, link_field)
337 :return tuple: (table_alias, alias statement for from clause with quotes added)
339 alias = src_table_alias
340 if not joined_tables:
341 return '%s' % alias, '%s' % _quote(alias)
342 for link in joined_tables:
343 alias += '__' + link[1]
344 assert len(alias) < 64, 'Table alias name %s is longer than the 64 characters size accepted by default in postgresql.' % alias
345 return '%s' % alias, '%s as %s' % (_quote(joined_tables[-1][0]), _quote(alias))
348 def get_alias_from_query(from_query):
349 """ :param string from_query: is something like :
351 - '"res_partner" as "res_users__partner_id"''
353 from_splitted = from_query.split(' as ')
354 if len(from_splitted) > 1:
355 return from_splitted[0].replace('"', ''), from_splitted[1].replace('"', '')
357 return from_splitted[0].replace('"', ''), from_splitted[0].replace('"', '')
360 def normalize_leaf(element):
361 """ Change a term's operator to some canonical form, simplifying later
363 if not is_leaf(element):
365 left, operator, right = element
367 operator = operator.lower()
370 if isinstance(right, bool) and operator in ('in', 'not in'):
371 _logger.warning("The domain term '%s' should use the '=' or '!=' operator." % ((left, original, right),))
372 operator = '=' if operator == 'in' else '!='
373 if isinstance(right, (list, tuple)) and operator in ('=', '!='):
374 _logger.warning("The domain term '%s' should use the 'in' or 'not in' operator." % ((left, original, right),))
375 operator = 'in' if operator == '=' else 'not in'
376 return left, operator, right
379 def is_operator(element):
380 """ Test whether an object is a valid domain operator. """
381 return isinstance(element, basestring) and element in DOMAIN_OPERATORS
384 def is_leaf(element, internal=False):
385 """ Test whether an object is a valid domain term:
388 - second element if a valid op
390 :param tuple element: a leaf in form (left, operator, right)
391 :param boolean internal: allow or not the 'inselect' internal operator
392 in the term. This should be always left to False.
394 Note: OLD TODO change the share wizard to use this function.
396 INTERNAL_OPS = TERM_OPERATORS + ('<>',)
398 INTERNAL_OPS += ('inselect', 'not inselect')
399 return (isinstance(element, tuple) or isinstance(element, list)) \
400 and len(element) == 3 \
401 and element[1] in INTERNAL_OPS \
402 and ((isinstance(element[0], basestring) and element[0])
403 or element in (TRUE_LEAF, FALSE_LEAF))
406 # --------------------------------------------------
408 # --------------------------------------------------
410 def select_from_where(cr, select_field, from_table, where_field, where_ids, where_operator):
411 # todo: merge into parent query as sub-query
414 if where_operator in ['<', '>', '>=', '<=']:
415 cr.execute('SELECT "%s" FROM "%s" WHERE "%s" %s %%s' % \
416 (select_field, from_table, where_field, where_operator),
417 (where_ids[0],)) # TODO shouldn't this be min/max(where_ids) ?
418 res = [r[0] for r in cr.fetchall()]
419 else: # TODO where_operator is supposed to be 'in'? It is called with child_of...
420 for i in range(0, len(where_ids), cr.IN_MAX):
421 subids = where_ids[i:i + cr.IN_MAX]
422 cr.execute('SELECT "%s" FROM "%s" WHERE "%s" IN %%s' % \
423 (select_field, from_table, where_field), (tuple(subids),))
424 res.extend([r[0] for r in cr.fetchall()])
428 def select_distinct_from_where_not_null(cr, select_field, from_table):
429 cr.execute('SELECT distinct("%s") FROM "%s" where "%s" is not null' % (select_field, from_table, select_field))
430 return [r[0] for r in cr.fetchall()]
432 def get_unaccent_wrapper(cr):
433 if openerp.modules.registry.RegistryManager.get(cr.dbname).has_unaccent:
434 return lambda x: "unaccent(%s)" % (x,)
437 # --------------------------------------------------
438 # ExtendedLeaf class for managing leafs and contexts
439 # -------------------------------------------------
441 class ExtendedLeaf(object):
442 """ Class wrapping a domain leaf, and giving some services and management
443 features on it. In particular it managed join contexts to be able to
444 construct queries through multiple models.
447 # --------------------------------------------------
448 # Join / Context manipulation
450 # - res_users.name, like, foo: name is on res_partner, not on res_users
451 # - res_partner.bank_ids.name, like, foo: bank_ids is a one2many with _auto_join
452 # - res_partner.state_id.name, like, foo: state_id is a many2one with _auto_join
454 # - link between src_table and dst_table, using src_field and dst_field
455 # i.e.: inherits: res_users.partner_id = res_partner.id
456 # i.e.: one2many: res_partner.id = res_partner_bank.partner_id
457 # i.e.: many2one: res_partner.state_id = res_country_state.id
458 # - done in the context of a field
459 # i.e.: inherits: 'partner_id'
460 # i.e.: one2many: 'bank_ids'
461 # i.e.: many2one: 'state_id'
462 # - table names use aliases: initial table followed by the context field
463 # names, joined using a '__'
464 # i.e.: inherits: res_partner as res_users__partner_id
465 # i.e.: one2many: res_partner_bank as res_partner__bank_ids
466 # i.e.: many2one: res_country_state as res_partner__state_id
467 # - join condition use aliases
468 # i.e.: inherits: res_users.partner_id = res_users__partner_id.id
469 # i.e.: one2many: res_partner.id = res_partner__bank_ids.parr_id
470 # i.e.: many2one: res_partner.state_id = res_partner__state_id.id
471 # Variables explanation:
472 # - src_table: working table before the join
473 # -> res_users, res_partner, res_partner
474 # - dst_table: working table after the join
475 # -> res_partner, res_partner_bank, res_country_state
476 # - src_table_link_name: field name used to link the src table, not
477 # necessarily a field (because 'id' is not a field instance)
478 # i.e.: inherits: 'partner_id', found in the inherits of the current table
479 # i.e.: one2many: 'id', not a field
480 # i.e.: many2one: 'state_id', the current field name
481 # - dst_table_link_name: field name used to link the dst table, not
482 # necessarily a field (because 'id' is not a field instance)
483 # i.e.: inherits: 'id', not a field
484 # i.e.: one2many: 'partner_id', _fields_id of the current field
485 # i.e.: many2one: 'id', not a field
486 # - context_field_name: field name used as a context to make the alias
487 # i.e.: inherits: 'partner_id': found in the inherits of the current table
488 # i.e.: one2many: 'bank_ids': current field name
489 # i.e.: many2one: 'state_id': current field name
490 # --------------------------------------------------
492 def __init__(self, leaf, model, join_context=None):
493 """ Initialize the ExtendedLeaf
495 :attr [string, tuple] leaf: operator or tuple-formatted domain
497 :attr obj model: current working model
498 :attr list _models: list of chained models, updated when
500 :attr list join_context: list of join contexts. This is a list of
501 tuples like ``(lhs, table, lhs_col, col, link)``
506 source (left hand) model
508 destination (right hand) model
510 source model column for join condition
512 destination model column for join condition
514 link column between source and destination model
515 that is not necessarily (but generally) a real column used
516 in the condition (i.e. in many2one); this link is used to
519 assert model, 'Invalid leaf creation without table'
520 self.join_context = join_context or []
522 # normalize the leaf's operator
523 self.normalize_leaf()
524 # set working variables; handle the context stack and previous tables
527 for item in self.join_context:
528 self._models.append(item[0])
529 self._models.append(model)
534 return '<osv.ExtendedLeaf: %s on %s (ctx: %s)>' % (str(self.leaf), self.model._table, ','.join(self._get_context_debug()))
536 def generate_alias(self):
537 links = [(context[1]._table, context[4]) for context in self.join_context]
538 alias, alias_statement = generate_table_alias(self._models[0]._table, links)
541 def add_join_context(self, model, lhs_col, table_col, link):
542 """ See above comments for more details. A join context is a tuple like:
543 ``(lhs, model, lhs_col, col, link)``
545 After adding the join, the model of the current leaf is updated.
547 self.join_context.append((self.model, model, lhs_col, table_col, link))
548 self._models.append(model)
551 def get_join_conditions(self):
553 alias = self._models[0]._table
554 for context in self.join_context:
555 previous_alias = alias
556 alias += '__' + context[4]
557 conditions.append('"%s"."%s"="%s"."%s"' % (previous_alias, context[2], alias, context[3]))
560 def get_tables(self):
563 for context in self.join_context:
564 links.append((context[1]._table, context[4]))
565 alias, alias_statement = generate_table_alias(self._models[0]._table, links)
566 tables.add(alias_statement)
569 def _get_context_debug(self):
570 names = ['"%s"."%s"="%s"."%s" (%s)' % (item[0]._table, item[2], item[1]._table, item[3], item[4]) for item in self.join_context]
573 # --------------------------------------------------
575 # --------------------------------------------------
577 def check_leaf(self):
578 """ Leaf validity rules:
579 - a valid leaf is an operator or a leaf
580 - a valid leaf has a field objects unless
582 - it is an inherited field
583 - left is id, operator is 'child_of'
584 - left is in MAGIC_COLUMNS
586 if not is_operator(self.leaf) and not is_leaf(self.leaf, True):
587 raise ValueError("Invalid leaf %s" % str(self.leaf))
589 def is_operator(self):
590 return is_operator(self.leaf)
592 def is_true_leaf(self):
593 return self.leaf == TRUE_LEAF
595 def is_false_leaf(self):
596 return self.leaf == FALSE_LEAF
598 def is_leaf(self, internal=False):
599 return is_leaf(self.leaf, internal=internal)
601 def normalize_leaf(self):
602 self.leaf = normalize_leaf(self.leaf)
605 def create_substitution_leaf(leaf, new_elements, new_model=None):
606 """ From a leaf, create a new leaf (based on the new_elements tuple
607 and new_model), that will have the same join context. Used to
608 insert equivalent leafs in the processing stack. """
609 if new_model is None:
610 new_model = leaf.model
611 new_join_context = [tuple(context) for context in leaf.join_context]
612 new_leaf = ExtendedLeaf(new_elements, new_model, join_context=new_join_context)
615 class expression(object):
616 """ Parse a domain expression
617 Use a real polish notation
618 Leafs are still in a ('foo', '=', 'bar') format
619 For more info: http://christophe-simonis-at-tiny.blogspot.com/2008/08/new-new-domain-notation.html
622 def __init__(self, cr, uid, exp, table, context):
623 """ Initialize expression object and automatically parse the expression
624 right after initialization.
626 :param exp: expression (using domain ('foo', '=', 'bar' format))
627 :param table: root model
629 :attr list result: list that will hold the result of the parsing
630 as a list of ExtendedLeaf
631 :attr list joins: list of join conditions, such as
632 (res_country_state."id" = res_partner."state_id")
633 :attr root_model: base model for the query
634 :attr list expression: the domain expression, that will be normalized
637 self._unaccent = get_unaccent_wrapper(cr)
639 self.root_model = table
641 # normalize and prepare the expression for parsing
642 self.expression = distribute_not(normalize_domain(exp))
644 # parse the domain expression
645 self.parse(cr, uid, context=context)
647 # ----------------------------------------
649 # ----------------------------------------
651 def get_tables(self):
652 """ Returns the list of tables for SQL queries, like select from ... """
654 for leaf in self.result:
655 for table in leaf.get_tables():
656 if table not in tables:
658 table_name = _quote(self.root_model._table)
659 if table_name not in tables:
660 tables.append(table_name)
663 # ----------------------------------------
665 # ----------------------------------------
667 def parse(self, cr, uid, context):
668 """ Transform the leaves of the expression
670 The principle is to pop elements from a leaf stack one at a time.
671 Each leaf is processed. The processing is a if/elif list of various
672 cases that appear in the leafs (many2one, function fields, ...).
673 Two things can happen as a processing result:
674 - the leaf has been modified and/or new leafs have to be introduced
675 in the expression; they are pushed into the leaf stack, to be
676 processed right after
677 - the leaf is added to the result
679 Some internal var explanation:
680 :var obj working_model: model object, model containing the field
681 (the name provided in the left operand)
682 :var list field_path: left operand seen as a path (foo.bar -> [foo, bar])
683 :var obj relational_model: relational model of a field (field._obj)
684 ex: res_partner.bank_ids -> res.partner.bank
687 def to_ids(value, relational_model, context=None, limit=None):
688 """ Normalize a single id or name, or a list of those, into a list of ids
689 :param {int,long,basestring,list,tuple} value:
690 if int, long -> return [value]
691 if basestring, convert it into a list of basestrings, then
692 if list of basestring ->
693 perform a name_search on relational_model for each name
694 return the list of related ids
697 if isinstance(value, basestring):
699 elif value and isinstance(value, (tuple, list)) and all(isinstance(item, basestring) for item in value):
701 elif isinstance(value, (int, long)):
704 name_get_list = [name_get[0] for name in names for name_get in relational_model.name_search(cr, uid, name, [], 'ilike', context=context, limit=limit)]
705 return list(set(name_get_list))
708 def child_of_domain(left, ids, left_model, parent=None, prefix='', context=None):
709 """ Return a domain implementing the child_of operator for [(left,child_of,ids)],
710 either as a range using the parent_left/right tree lookup fields
711 (when available), or as an expanded [(left,in,child_ids)] """
712 if left_model._parent_store and (not left_model.pool._init):
713 # TODO: Improve where joins are implemented for many with '.', replace by:
714 # doms += ['&',(prefix+'.parent_left','<',o.parent_right),(prefix+'.parent_left','>=',o.parent_left)]
716 for o in left_model.browse(cr, uid, ids, context=context):
718 doms.insert(0, OR_OPERATOR)
719 doms += [AND_OPERATOR, ('parent_left', '<', o.parent_right), ('parent_left', '>=', o.parent_left)]
721 return [(left, 'in', left_model.search(cr, uid, doms, context=context))]
724 def recursive_children(ids, model, parent_field):
727 ids2 = model.search(cr, uid, [(parent_field, 'in', ids)], context=context)
728 return ids + recursive_children(ids2, model, parent_field)
729 return [(left, 'in', recursive_children(ids, left_model, parent or left_model._parent_name))]
732 """ Pop a leaf to process. """
733 return self.stack.pop()
736 """ Push a leaf to be processed right after. """
737 self.stack.append(leaf)
739 def push_result(leaf):
740 """ Push a leaf to the results. This leaf has been fully processed
742 self.result.append(leaf)
745 self.stack = [ExtendedLeaf(leaf, self.root_model) for leaf in self.expression]
746 # process from right to left; expression is from left to right
750 # Get the next leaf to process
753 # Get working variables
754 working_model = leaf.model
755 if leaf.is_operator():
756 left, operator, right = leaf.leaf, None, None
757 elif leaf.is_true_leaf() or leaf.is_false_leaf():
758 # because we consider left as a string
759 left, operator, right = ('%s' % leaf.leaf[0], leaf.leaf[1], leaf.leaf[2])
761 left, operator, right = leaf.leaf
762 field_path = left.split('.', 1)
763 field = working_model._columns.get(field_path[0])
764 if field and field._obj:
765 relational_model = working_model.pool[field._obj]
767 relational_model = None
769 # ----------------------------------------
771 # 1. leaf is an operator
772 # 2. leaf is a true/false leaf
773 # -> add directly to result
774 # ----------------------------------------
776 if leaf.is_operator() or leaf.is_true_leaf() or leaf.is_false_leaf():
779 # ----------------------------------------
781 # -> from inherits'd fields -> work on the related model, and add
783 # -> ('id', 'child_of', '..') -> use a 'to_ids'
784 # -> but is one on the _log_access special fields, add directly to
786 # TODO: make these fields explicitly available in self.columns instead!
788 # ----------------------------------------
790 elif not field and field_path[0] in working_model._inherit_fields:
791 # comments about inherits'd fields
792 # { 'field_name': ('parent_model', 'm2o_field_to_reach_parent',
793 # field_column_obj, origina_parent_model), ... }
794 next_model = working_model.pool[working_model._inherit_fields[field_path[0]][0]]
795 leaf.add_join_context(next_model, working_model._inherits[next_model._name], 'id', working_model._inherits[next_model._name])
798 elif left == 'id' and operator == 'child_of':
799 ids2 = to_ids(right, working_model, context)
800 dom = child_of_domain(left, ids2, working_model)
801 for dom_leaf in reversed(dom):
802 new_leaf = create_substitution_leaf(leaf, dom_leaf, working_model)
805 elif not field and field_path[0] in MAGIC_COLUMNS:
809 raise ValueError("Invalid field %r in leaf %r" % (left, str(leaf)))
811 # ----------------------------------------
813 # -> many2one or one2many with _auto_join:
814 # - add a join, then jump into linked field: field.remaining on
815 # src_table is replaced by remaining on dst_table, and set for re-evaluation
816 # - if a domain is defined on the field, add it into evaluation
817 # on the relational table
818 # -> many2one, many2many, one2many: replace by an equivalent computed
819 # domain, given by recursively searching on the remaining of the path
820 # -> note: hack about fields.property should not be necessary anymore
821 # as after transforming the field, it will go through this loop once again
822 # ----------------------------------------
824 elif len(field_path) > 1 and field._type == 'many2one' and field._auto_join:
825 # res_partner.state_id = res_partner__state_id.id
826 leaf.add_join_context(relational_model, field_path[0], 'id', field_path[0])
827 push(create_substitution_leaf(leaf, (field_path[1], operator, right), relational_model))
829 elif len(field_path) > 1 and field._type == 'one2many' and field._auto_join:
830 # res_partner.id = res_partner__bank_ids.partner_id
831 leaf.add_join_context(relational_model, 'id', field._fields_id, field_path[0])
832 domain = field._domain(working_model) if callable(field._domain) else field._domain
833 push(create_substitution_leaf(leaf, (field_path[1], operator, right), relational_model))
835 domain = normalize_domain(domain)
836 for elem in reversed(domain):
837 push(create_substitution_leaf(leaf, elem, relational_model))
838 push(create_substitution_leaf(leaf, AND_OPERATOR, relational_model))
840 elif len(field_path) > 1 and field._auto_join:
841 raise NotImplementedError('_auto_join attribute not supported on many2many field %s' % left)
843 elif len(field_path) > 1 and field._type == 'many2one':
844 right_ids = relational_model.search(cr, uid, [(field_path[1], operator, right)], context=context)
845 leaf.leaf = (field_path[0], 'in', right_ids)
848 # Making search easier when there is a left operand as field.o2m or field.m2m
849 elif len(field_path) > 1 and field._type in ['many2many', 'one2many']:
850 right_ids = relational_model.search(cr, uid, [(field_path[1], operator, right)], context=context)
851 table_ids = working_model.search(cr, uid, [(field_path[0], 'in', right_ids)], context=dict(context, active_test=False))
852 leaf.leaf = ('id', 'in', table_ids)
855 # -------------------------------------------------
857 # -> not stored: error if no _fnct_search, otherwise handle the result domain
858 # -> stored: management done in the remaining of parsing
859 # -------------------------------------------------
861 elif isinstance(field, fields.function) and not field.store and not field._fnct_search:
862 # this is a function field that is not stored
863 # the function field doesn't provide a search function and doesn't store
864 # values in the database, so we must ignore it : we generate a dummy leaf
865 leaf.leaf = TRUE_LEAF
867 "The field '%s' (%s) can not be searched: non-stored "
868 "function field without fnct_search",
870 # avoid compiling stack trace if not needed
871 if _logger.isEnabledFor(logging.DEBUG):
872 _logger.debug(''.join(traceback.format_stack()))
875 elif isinstance(field, fields.function) and not field.store:
876 # this is a function field that is not stored
877 fct_domain = field.search(cr, uid, working_model, left, [leaf.leaf], context=context)
879 leaf.leaf = TRUE_LEAF
882 # we assume that the expression is valid
883 # we create a dummy leaf for forcing the parsing of the resulting expression
884 for domain_element in reversed(fct_domain):
885 push(create_substitution_leaf(leaf, domain_element, working_model))
886 # self.push(create_substitution_leaf(leaf, TRUE_LEAF, working_model))
887 # self.push(create_substitution_leaf(leaf, AND_OPERATOR, working_model))
889 # -------------------------------------------------
891 # -------------------------------------------------
893 # Applying recursivity on field(one2many)
894 elif field._type == 'one2many' and operator == 'child_of':
895 ids2 = to_ids(right, relational_model, context)
896 if field._obj != working_model._name:
897 dom = child_of_domain(left, ids2, relational_model, prefix=field._obj)
899 dom = child_of_domain('id', ids2, working_model, parent=left)
900 for dom_leaf in reversed(dom):
901 push(create_substitution_leaf(leaf, dom_leaf, working_model))
903 elif field._type == 'one2many':
906 if right is not False:
907 if isinstance(right, basestring):
908 ids2 = [x[0] for x in relational_model.name_search(cr, uid, right, [], operator, context=context, limit=None)]
912 if not isinstance(right, list):
917 if operator in ['like', 'ilike', 'in', '=']:
918 #no result found with given search criteria
920 push(create_substitution_leaf(leaf, FALSE_LEAF, working_model))
922 ids2 = select_from_where(cr, field._fields_id, relational_model._table, 'id', ids2, operator)
925 o2m_op = 'not in' if operator in NEGATIVE_TERM_OPERATORS else 'in'
926 push(create_substitution_leaf(leaf, ('id', o2m_op, ids2), working_model))
929 o2m_op = 'in' if operator in NEGATIVE_TERM_OPERATORS else 'not in'
930 push(create_substitution_leaf(leaf, ('id', o2m_op, select_distinct_from_where_not_null(cr, field._fields_id, relational_model._table)), working_model))
932 elif field._type == 'many2many':
933 rel_table, rel_id1, rel_id2 = field._sql_names(working_model)
935 if operator == 'child_of':
936 def _rec_convert(ids):
937 if relational_model == working_model:
939 return select_from_where(cr, rel_id1, rel_table, rel_id2, ids, operator)
941 ids2 = to_ids(right, relational_model, context)
942 dom = child_of_domain('id', ids2, relational_model)
943 ids2 = relational_model.search(cr, uid, dom, context=context)
944 push(create_substitution_leaf(leaf, ('id', 'in', _rec_convert(ids2)), working_model))
947 if right is not False:
948 if isinstance(right, basestring):
949 res_ids = [x[0] for x in relational_model.name_search(cr, uid, right, [], operator, context=context)]
953 if not isinstance(right, list):
958 if operator in ['like', 'ilike', 'in', '=']:
959 #no result found with given search criteria
960 call_null_m2m = False
961 push(create_substitution_leaf(leaf, FALSE_LEAF, working_model))
963 operator = 'in' # operator changed because ids are directly related to main object
965 call_null_m2m = False
966 m2m_op = 'not in' if operator in NEGATIVE_TERM_OPERATORS else 'in'
967 push(create_substitution_leaf(leaf, ('id', m2m_op, select_from_where(cr, rel_id1, rel_table, rel_id2, res_ids, operator) or [0]), working_model))
970 m2m_op = 'in' if operator in NEGATIVE_TERM_OPERATORS else 'not in'
971 push(create_substitution_leaf(leaf, ('id', m2m_op, select_distinct_from_where_not_null(cr, rel_id1, rel_table)), working_model))
973 elif field._type == 'many2one':
974 if operator == 'child_of':
975 ids2 = to_ids(right, relational_model, context)
976 if field._obj != working_model._name:
977 dom = child_of_domain(left, ids2, relational_model, prefix=field._obj)
979 dom = child_of_domain('id', ids2, working_model, parent=left)
980 for dom_leaf in reversed(dom):
981 push(create_substitution_leaf(leaf, dom_leaf, working_model))
983 def _get_expression(relational_model, cr, uid, left, right, operator, context=None):
987 c['active_test'] = False
988 #Special treatment to ill-formed domains
989 operator = (operator in ['<', '>', '<=', '>=']) and 'in' or operator
991 dict_op = {'not in': '!=', 'in': '=', '=': 'in', '!=': 'not in'}
992 if isinstance(right, tuple):
994 if (not isinstance(right, list)) and operator in ['not in', 'in']:
995 operator = dict_op[operator]
996 elif isinstance(right, list) and operator in ['!=', '=']: # for domain (FIELD,'=',['value1','value2'])
997 operator = dict_op[operator]
998 res_ids = [x[0] for x in relational_model.name_search(cr, uid, right, [], operator, limit=None, context=c)]
999 if operator in NEGATIVE_TERM_OPERATORS:
1000 res_ids.append(False) # TODO this should not be appended if False was in 'right'
1001 return left, 'in', res_ids
1002 # resolve string-based m2o criterion into IDs
1003 if isinstance(right, basestring) or \
1004 right and isinstance(right, (tuple, list)) and all(isinstance(item, basestring) for item in right):
1005 push(create_substitution_leaf(leaf, _get_expression(relational_model, cr, uid, left, right, operator, context=context), working_model))
1007 # right == [] or right == False and all other cases are handled by __leaf_to_sql()
1010 # -------------------------------------------------
1012 # -> datetime fields: manage time part of the datetime
1013 # field when it is not there
1014 # -> manage translatable fields
1015 # -------------------------------------------------
1018 if field._type == 'datetime' and right and len(right) == 10:
1019 if operator in ('>', '<='):
1020 right += ' 23:59:59'
1022 right += ' 00:00:00'
1023 push(create_substitution_leaf(leaf, (left, operator, right), working_model))
1025 elif field.translate and right:
1026 need_wildcard = operator in ('like', 'ilike', 'not like', 'not ilike')
1027 sql_operator = {'=like': 'like', '=ilike': 'ilike'}.get(operator, operator)
1029 right = '%%%s%%' % right
1031 inselect_operator = 'inselect'
1032 if sql_operator in NEGATIVE_TERM_OPERATORS:
1033 # negate operator (fix lp:1071710)
1034 sql_operator = sql_operator[4:] if sql_operator[:3] == 'not' else '='
1035 inselect_operator = 'not inselect'
1037 unaccent = self._unaccent if sql_operator.endswith('like') else lambda x: x
1039 trans_left = unaccent('value')
1040 quote_left = unaccent(_quote(left))
1041 instr = unaccent('%s')
1043 if sql_operator == 'in':
1044 # params will be flatten by to_sql() => expand the placeholders
1045 instr = '(%s)' % ', '.join(['%s'] * len(right))
1047 subselect = """(SELECT res_id
1052 AND {trans_left} {operator} {right}
1056 WHERE {left} {operator} {right}
1058 """.format(trans_left=trans_left, operator=sql_operator,
1059 right=instr, table=working_model._table, left=quote_left)
1062 working_model._name + ',' + left,
1063 context.get('lang') or 'en_US',
1068 push(create_substitution_leaf(leaf, ('id', inselect_operator, (subselect, params)), working_model))
1073 # ----------------------------------------
1074 # END OF PARSING FULL DOMAIN
1076 # ----------------------------------------
1079 for leaf in self.result:
1080 joins |= set(leaf.get_join_conditions())
1081 self.joins = list(joins)
1083 def __leaf_to_sql(self, eleaf):
1086 left, operator, right = leaf
1088 # final sanity checks - should never fail
1089 assert operator in (TERM_OPERATORS + ('inselect', 'not inselect')), \
1090 "Invalid operator %r in domain term %r" % (operator, leaf)
1091 assert leaf in (TRUE_LEAF, FALSE_LEAF) or left in model._all_columns \
1092 or left in MAGIC_COLUMNS, "Invalid field %r in domain term %r" % (left, leaf)
1094 table_alias = '"%s"' % (eleaf.generate_alias())
1096 if leaf == TRUE_LEAF:
1100 elif leaf == FALSE_LEAF:
1104 elif operator == 'inselect':
1105 query = '(%s."%s" in (%s))' % (table_alias, left, right[0])
1108 elif operator == 'not inselect':
1109 query = '(%s."%s" not in (%s))' % (table_alias, left, right[0])
1112 elif operator in ['in', 'not in']:
1113 # Two cases: right is a boolean or a list. The boolean case is an
1114 # abuse and handled for backward compatibility.
1115 if isinstance(right, bool):
1116 _logger.warning("The domain term '%s' should use the '=' or '!=' operator." % (leaf,))
1117 if operator == 'in':
1118 r = 'NOT NULL' if right else 'NULL'
1120 r = 'NULL' if right else 'NOT NULL'
1121 query = '(%s."%s" IS %s)' % (table_alias, left, r)
1123 elif isinstance(right, (list, tuple)):
1124 params = list(right)
1126 for i in range(len(params))[::-1]:
1127 if params[i] == False:
1133 instr = ','.join(['%s'] * len(params))
1135 ss = model._columns[left]._symbol_set
1136 instr = ','.join([ss[0]] * len(params))
1137 params = map(ss[1], params)
1138 query = '(%s."%s" %s (%s))' % (table_alias, left, operator, instr)
1140 # The case for (left, 'in', []) or (left, 'not in', []).
1141 query = 'FALSE' if operator == 'in' else 'TRUE'
1143 if check_nulls and operator == 'in':
1144 query = '(%s OR %s."%s" IS NULL)' % (query, table_alias, left)
1145 elif not check_nulls and operator == 'not in':
1146 query = '(%s OR %s."%s" IS NULL)' % (query, table_alias, left)
1147 elif check_nulls and operator == 'not in':
1148 query = '(%s AND %s."%s" IS NOT NULL)' % (query, table_alias, left) # needed only for TRUE.
1149 else: # Must not happen
1150 raise ValueError("Invalid domain term %r" % (leaf,))
1152 elif right == False and (left in model._columns) and model._columns[left]._type == "boolean" and (operator == '='):
1153 query = '(%s."%s" IS NULL or %s."%s" = false )' % (table_alias, left, table_alias, left)
1156 elif (right is False or right is None) and (operator == '='):
1157 query = '%s."%s" IS NULL ' % (table_alias, left)
1160 elif right == False and (left in model._columns) and model._columns[left]._type == "boolean" and (operator == '!='):
1161 query = '(%s."%s" IS NOT NULL and %s."%s" != false)' % (table_alias, left, table_alias, left)
1164 elif (right is False or right is None) and (operator == '!='):
1165 query = '%s."%s" IS NOT NULL' % (table_alias, left)
1168 elif operator == '=?':
1169 if right is False or right is None:
1170 # '=?' is a short-circuit that makes the term TRUE if right is None or False
1174 # '=?' behaves like '=' in other cases
1175 query, params = self.__leaf_to_sql(
1176 create_substitution_leaf(eleaf, (left, '=', right), model))
1179 query = '%s.id %s %%s' % (table_alias, operator)
1183 need_wildcard = operator in ('like', 'ilike', 'not like', 'not ilike')
1184 sql_operator = {'=like': 'like', '=ilike': 'ilike'}.get(operator, operator)
1185 cast = '::text' if sql_operator.endswith('like') else ''
1187 if left in model._columns:
1188 format = need_wildcard and '%s' or model._columns[left]._symbol_set[0]
1189 unaccent = self._unaccent if sql_operator.endswith('like') else lambda x: x
1190 column = '%s.%s' % (table_alias, _quote(left))
1191 query = '(%s%s %s %s)' % (unaccent(column), cast, sql_operator, unaccent(format))
1192 elif left in MAGIC_COLUMNS:
1193 query = "(%s.\"%s\"%s %s %%s)" % (table_alias, left, cast, sql_operator)
1195 else: # Must not happen
1196 raise ValueError("Invalid field %r in domain term %r" % (left, leaf))
1200 if isinstance(right, str):
1202 elif isinstance(right, unicode):
1203 str_utf8 = right.encode('utf-8')
1205 str_utf8 = str(right)
1206 params = '%%%s%%' % str_utf8
1207 add_null = not str_utf8
1208 elif left in model._columns:
1209 params = model._columns[left]._symbol_set[1](right)
1212 query = '(%s OR %s."%s" IS NULL)' % (query, table_alias, left)
1214 if isinstance(params, basestring):
1216 return query, params
1221 # Process the domain from right to left, using a stack, to generate a SQL expression.
1222 self.result.reverse()
1223 for leaf in self.result:
1224 if leaf.is_leaf(internal=True):
1225 q, p = self.__leaf_to_sql(leaf)
1228 elif leaf.leaf == NOT_OPERATOR:
1229 stack.append('(NOT (%s))' % (stack.pop(),))
1231 ops = {AND_OPERATOR: ' AND ', OR_OPERATOR: ' OR '}
1234 stack.append('(%s %s %s)' % (q1, ops[leaf.leaf], q2,))
1236 assert len(stack) == 1
1238 joins = ' AND '.join(self.joins)
1240 query = '(%s) AND %s' % (joins, query)
1242 return query, tools.flatten(params)
1244 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: