2 # -*- coding: utf-8 -*-
3 ##############################################################################
5 # OpenERP, Open Source Management Solution
6 # Copyright (C) 2004-2009 Tiny SPRL (<http://tiny.be>).
8 # This program is free software: you can redistribute it and/or modify
9 # it under the terms of the GNU Affero General Public License as
10 # published by the Free Software Foundation, either version 3 of the
11 # License, or (at your option) any later version.
13 # This program is distributed in the hope that it will be useful,
14 # but WITHOUT ANY WARRANTY; without even the implied warranty of
15 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
16 # GNU Affero General Public License for more details.
18 # You should have received a copy of the GNU Affero General Public License
19 # along with this program. If not, see <http://www.gnu.org/licenses/>.
21 ##############################################################################
23 """ Domain expression processing
25 The main duty of this module is to compile a domain expression into a
26 SQL query. A lot of things should be documented here, but as a first
27 step in the right direction, some tests in test_osv_expression.yml
28 might give you some additional information.
30 For legacy reasons, a domain uses an inconsistent two-levels abstract
31 syntax (domains are regular Python data structures). At the first
32 level, a domain is an expression made of terms (sometimes called
33 leaves) and (domain) operators used in prefix notation. The available
34 operators at this level are '!', '&', and '|'. '!' is a unary 'not',
35 '&' is a binary 'and', and '|' is a binary 'or'. For instance, here
36 is a possible domain. (<term> stands for an arbitrary term, more on
39 ['&', '!', <term1>, '|', <term2>, <term3>]
41 It is equivalent to this pseudo code using infix notation::
43 (not <term1>) and (<term2> or <term3>)
45 The second level of syntax deals with the term representation. A term
46 is a triple of the form (left, operator, right). That is, a term uses
47 an infix notation, and the available operators, and possible left and
48 right operands differ with those of the previous level. Here is a
51 ('company_id.name', '=', 'OpenERP')
53 The left and right operand don't have the same possible values. The
54 left operand is field name (related to the model for which the domain
55 applies). Actually, the field name can use the dot-notation to
56 traverse relationships. The right operand is a Python value whose
57 type should match the used operator and field type. In the above
58 example, a string is used because the name field of a company has type
59 string, and because we use the '=' operator. When appropriate, a 'in'
60 operator can be used, and thus the right operand should be a list.
62 Note: the non-uniform syntax could have been more uniform, but this
63 would hide an important limitation of the domain syntax. Say that the
64 term representation was ['=', 'company_id.name', 'OpenERP']. Used in a
65 complete domain, this would look like::
67 ['!', ['=', 'company_id.name', 'OpenERP']]
69 and you would be tempted to believe something like this would be
72 ['!', ['=', 'company_id.name', ['&', ..., ...]]]
74 That is, a domain could be a valid operand. But this is not the
75 case. A domain is really limited to a two-level nature, and can not
76 take a recursive form: a domain is not a valid second-level operand.
78 Unaccent - Accent-insensitive search
80 OpenERP will use the SQL function 'unaccent' when available for the
81 'ilike' and 'not ilike' operators, and enabled in the configuration.
82 Normally the 'unaccent' function is obtained from `the PostgreSQL
83 'unaccent' contrib module
84 <http://developer.postgresql.org/pgdocs/postgres/unaccent.html>`_.
86 .. todo: The following explanation should be moved in some external
89 The steps to install the module might differ on specific PostgreSQL
90 versions. We give here some instruction for PostgreSQL 9.x on a
93 Ubuntu doesn't come yet with PostgreSQL 9.x, so an alternative package
94 source is used. We use Martin Pitt's PPA available at
96 <https://launchpad.net/~pitti/+archive/postgresql>`_.
100 > sudo add-apt-repository ppa:pitti/postgresql
101 > sudo apt-get update
103 Once the package list is up-to-date, you have to install PostgreSQL
104 9.0 and its contrib modules.
108 > sudo apt-get install postgresql-9.0 postgresql-contrib-9.0
110 When you want to enable unaccent on some database:
114 > psql9 <database> -f /usr/share/postgresql/9.0/contrib/unaccent.sql
116 Here :program:`psql9` is an alias for the newly installed PostgreSQL
117 9.0 tool, together with the correct port if necessary (for instance if
118 PostgreSQL 8.4 is running on 5432). (Other aliases can be used for
119 createdb and dropdb.)
123 > alias psql9='/usr/lib/postgresql/9.0/bin/psql -p 5433'
125 You can check unaccent is working:
129 > psql9 <database> -c"select unaccent('hélène')"
131 Finally, to instruct OpenERP to really use the unaccent function, you have to
132 start the server specifying the ``--unaccent`` flag.
139 import openerp.modules
140 from openerp.osv import fields
141 from openerp.osv.orm import MAGIC_COLUMNS
142 import openerp.tools as tools
144 #.apidoc title: Domain Expressions
150 DOMAIN_OPERATORS = (NOT_OPERATOR, OR_OPERATOR, AND_OPERATOR)
152 # List of available term operators. It is also possible to use the '<>'
153 # operator, which is strictly the same as '!='; the later should be prefered
154 # for consistency. This list doesn't contain '<>' as it is simpified to '!='
155 # by the normalize_operator() function (so later part of the code deals with
156 # only one representation).
157 # An internal (i.e. not available to the user) 'inselect' operator is also
158 # used. In this case its right operand has the form (subselect, params).
159 TERM_OPERATORS = ('=', '!=', '<=', '<', '>', '>=', '=?', '=like', '=ilike',
160 'like', 'not like', 'ilike', 'not ilike', 'in', 'not in',
163 # A subset of the above operators, with a 'negative' semantic. When the
164 # expressions 'in NEGATIVE_TERM_OPERATORS' or 'not in NEGATIVE_TERM_OPERATORS' are used in the code
165 # below, this doesn't necessarily mean that any of those NEGATIVE_TERM_OPERATORS is
166 # legal in the processed term.
167 NEGATIVE_TERM_OPERATORS = ('!=', 'not like', 'not ilike', 'not in')
169 TRUE_LEAF = (1, '=', 1)
170 FALSE_LEAF = (0, '=', 1)
172 TRUE_DOMAIN = [TRUE_LEAF]
173 FALSE_DOMAIN = [FALSE_LEAF]
175 _logger = logging.getLogger(__name__)
178 # --------------------------------------------------
179 # Generic domain manipulation
180 # --------------------------------------------------
182 def normalize_domain(domain):
183 """Returns a normalized version of ``domain_expr``, where all implicit '&' operators
184 have been made explicit. One property of normalized domain expressions is that they
185 can be easily combined together as if they were single domain components.
187 assert isinstance(domain, (list, tuple)), "Domains to normalize must have a 'domain' form: a list or tuple of domain components"
191 expected = 1 # expected number of expressions
192 op_arity = {NOT_OPERATOR: 1, AND_OPERATOR: 2, OR_OPERATOR: 2}
194 if expected == 0: # more than expected, like in [A, B]
195 result[0:0] = [AND_OPERATOR] # put an extra '&' in front
198 if isinstance(token, (list, tuple)): # domain term
201 expected += op_arity.get(token, 0) - 1
206 def combine(operator, unit, zero, domains):
207 """Returns a new domain expression where all domain components from ``domains``
208 have been added together using the binary operator ``operator``. The given
209 domains must be normalized.
211 :param unit: the identity element of the domains "set" with regard to the operation
212 performed by ``operator``, i.e the domain component ``i`` which, when
213 combined with any domain ``x`` via ``operator``, yields ``x``.
214 E.g. [(1,'=',1)] is the typical unit for AND_OPERATOR: adding it
215 to any domain component gives the same domain.
216 :param zero: the absorbing element of the domains "set" with regard to the operation
217 performed by ``operator``, i.e the domain component ``z`` which, when
218 combined with any domain ``x`` via ``operator``, yields ``z``.
219 E.g. [(1,'=',1)] is the typical zero for OR_OPERATOR: as soon as
220 you see it in a domain component the resulting domain is the zero.
221 :param domains: a list of normalized domains.
225 for domain in domains:
233 result = [operator] * (count - 1) + result
238 """AND([D1,D2,...]) returns a domain representing D1 and D2 and ... """
239 return combine(AND_OPERATOR, TRUE_DOMAIN, FALSE_DOMAIN, domains)
243 """OR([D1,D2,...]) returns a domain representing D1 or D2 or ... """
244 return combine(OR_OPERATOR, FALSE_DOMAIN, TRUE_DOMAIN, domains)
247 def distribute_not(domain):
248 """ Distribute any '!' domain operators found inside a normalized domain.
250 Because we don't use SQL semantic for processing a 'left not in right'
251 query (i.e. our 'not in' is not simply translated to a SQL 'not in'),
252 it means that a '! left in right' can not be simply processed
253 by __leaf_to_sql by first emitting code for 'left in right' then wrapping
254 the result with 'not (...)', as it would result in a 'not in' at the SQL
257 This function is thus responsible for pushing any '!' domain operators
258 inside the terms themselves. For example::
260 ['!','&',('user_id','=',4),('partner_id','in',[1,2])]
262 ['|',('user_id','!=',4),('partner_id','not in',[1,2])]
266 """Negates and returns a single domain leaf term,
267 using the opposite operator if possible"""
268 left, operator, right = leaf
277 if operator in ('in', 'like', 'ilike'):
278 operator = 'not ' + operator
279 return [(left, operator, right)]
280 if operator in ('not in', 'not like', 'not ilike'):
281 operator = operator[4:]
282 return [(left, operator, right)]
283 if operator in mapping:
284 operator = mapping[operator]
285 return [(left, operator, right)]
286 return [NOT_OPERATOR, (left, operator, right)]
288 def distribute_negate(domain):
289 """Negate the domain ``subtree`` rooted at domain[0],
290 leaving the rest of the domain intact, and return
291 (negated_subtree, untouched_domain_rest)
293 if is_leaf(domain[0]):
294 return negate(domain[0]), domain[1:]
295 if domain[0] == AND_OPERATOR:
296 done1, todo1 = distribute_negate(domain[1:])
297 done2, todo2 = distribute_negate(todo1)
298 return [OR_OPERATOR] + done1 + done2, todo2
299 if domain[0] == OR_OPERATOR:
300 done1, todo1 = distribute_negate(domain[1:])
301 done2, todo2 = distribute_negate(todo1)
302 return [AND_OPERATOR] + done1 + done2, todo2
305 if domain[0] != NOT_OPERATOR:
306 return [domain[0]] + distribute_not(domain[1:])
307 if domain[0] == NOT_OPERATOR:
308 done, todo = distribute_negate(domain[1:])
309 return done + distribute_not(todo)
312 # --------------------------------------------------
313 # Generic leaf manipulation
314 # --------------------------------------------------
316 def _quote(to_quote):
317 if '"' not in to_quote:
318 return '"%s"' % to_quote
322 def generate_table_alias(src_table_alias, joined_tables=[]):
323 """ Generate a standard table alias name. An alias is generated as following:
324 - the base is the source table name (that can already be an alias)
325 - then, each joined table is added in the alias using a 'link field name'
326 that is used to render unique aliases for a given path
327 - returns a tuple composed of the alias, and the full table alias to be
328 added in a from condition with quoting done
330 - src_table_alias='res_users', join_tables=[]:
331 alias = ('res_users','"res_users"')
332 - src_model='res_users', join_tables=[(res.partner, 'parent_id')]
333 alias = ('res_users__parent_id', '"res_partner" as "res_users__parent_id"')
335 :param model src_model: model source of the alias
336 :param list join_tables: list of tuples
337 (dst_model, link_field)
339 :return tuple: (table_alias, alias statement for from clause with quotes added)
341 alias = src_table_alias
342 if not joined_tables:
343 return ('%s' % alias, '%s' % _quote(alias))
344 for link in joined_tables:
345 alias += '__' + link[1]
346 assert len(alias) < 64, 'Table alias name %s is longer than the 64 characters size accepted by default in postgresql.' % (alias)
347 return ('%s' % alias, '%s as %s' % (_quote(joined_tables[-1][0]), _quote(alias)))
350 def get_alias_from_query(from_query):
351 """ :param string from_query: is something like :
353 - '"res_partner" as "res_users__partner_id"''
354 :param tuple result: (unquoted table name, unquoted alias)
355 i.e. (res_partners, res_partner) OR (res_partner, res_users__partner_id)
357 from_splitted = from_query.split(' as ')
358 if len(from_splitted) > 1:
359 return (from_splitted[0].replace('"', ''), from_splitted[1].replace('"', ''))
361 return (from_splitted[0].replace('"', ''), from_splitted[0].replace('"', ''))
364 def normalize_leaf(element):
365 """ Change a term's operator to some canonical form, simplifying later
367 if not is_leaf(element):
369 left, operator, right = element
371 operator = operator.lower()
374 if isinstance(right, bool) and operator in ('in', 'not in'):
375 _logger.warning("The domain term '%s' should use the '=' or '!=' operator." % ((left, original, right),))
376 operator = '=' if operator == 'in' else '!='
377 if isinstance(right, (list, tuple)) and operator in ('=', '!='):
378 _logger.warning("The domain term '%s' should use the 'in' or 'not in' operator." % ((left, original, right),))
379 operator = 'in' if operator == '=' else 'not in'
380 return (left, operator, right)
383 def is_operator(element):
384 """ Test whether an object is a valid domain operator. """
385 return isinstance(element, basestring) and element in DOMAIN_OPERATORS
388 def is_leaf(element, internal=False):
389 """ Test whether an object is a valid domain term:
392 - second element if a valid op
394 :param tuple element: a leaf in form (left, operator, right)
395 :param boolean internal: allow or not the 'inselect' internal operator
396 in the term. This should be always left to False.
398 Note: OLD TODO change the share wizard to use this function.
400 INTERNAL_OPS = TERM_OPERATORS + ('<>',)
402 INTERNAL_OPS += ('inselect',)
403 return (isinstance(element, tuple) or isinstance(element, list)) \
404 and len(element) == 3 \
405 and element[1] in INTERNAL_OPS
408 # --------------------------------------------------
410 # --------------------------------------------------
412 def select_from_where(cr, select_field, from_table, where_field, where_ids, where_operator):
413 # todo: merge into parent query as sub-query
416 if where_operator in ['<', '>', '>=', '<=']:
417 cr.execute('SELECT "%s" FROM "%s" WHERE "%s" %s %%s' % \
418 (select_field, from_table, where_field, where_operator),
419 (where_ids[0],)) # TODO shouldn't this be min/max(where_ids) ?
420 res = [r[0] for r in cr.fetchall()]
421 else: # TODO where_operator is supposed to be 'in'? It is called with child_of...
422 for i in range(0, len(where_ids), cr.IN_MAX):
423 subids = where_ids[i:i + cr.IN_MAX]
424 cr.execute('SELECT "%s" FROM "%s" WHERE "%s" IN %%s' % \
425 (select_field, from_table, where_field), (tuple(subids),))
426 res.extend([r[0] for r in cr.fetchall()])
430 def select_distinct_from_where_not_null(cr, select_field, from_table):
431 cr.execute('SELECT distinct("%s") FROM "%s" where "%s" is not null' % (select_field, from_table, select_field))
432 return [r[0] for r in cr.fetchall()]
435 # --------------------------------------------------
436 # ExtendedLeaf class for managing leafs and contexts
437 # -------------------------------------------------
439 class ExtendedLeaf(object):
440 """ Class wrapping a domain leaf, and giving some services and management
441 features on it. In particular it managed join contexts to be able to
442 construct queries through multiple models.
445 # --------------------------------------------------
446 # Join / Context manipulation
448 # - res_users.name, like, foo: name is on res_partner, not on res_users
449 # - res_partner.bank_ids.name, like, foo: bank_ids is a one2many with _auto_join
450 # - res_partner.state_id.name, like, foo: state_id is a many2one with _auto_join
452 # - link between src_table and dst_table, using src_field and dst_field
453 # i.e.: inherits: res_users.partner_id = res_partner.id
454 # i.e.: one2many: res_partner.id = res_partner_bank.partner_id
455 # i.e.: many2one: res_partner.state_id = res_country_state.id
456 # - done in the context of a field
457 # i.e.: inherits: 'partner_id'
458 # i.e.: one2many: 'bank_ids'
459 # i.e.: many2one: 'state_id'
460 # - table names use aliases: initial table followed by the context field
461 # names, joined using a '__'
462 # i.e.: inherits: res_partner as res_users__partner_id
463 # i.e.: one2many: res_partner_bank as res_partner__bank_ids
464 # i.e.: many2one: res_country_state as res_partner__state_id
465 # - join condition use aliases
466 # i.e.: inherits: res_users.partner_id = res_users__partner_id.id
467 # i.e.: one2many: res_partner.id = res_partner__bank_ids.parr_id
468 # i.e.: many2one: res_partner.state_id = res_partner__state_id.id
469 # Variables explanation:
470 # - src_table: working table before the join
471 # -> res_users, res_partner, res_partner
472 # - dst_table: working table after the join
473 # -> res_partner, res_partner_bank, res_country_state
474 # - src_table_link_name: field name used to link the src table, not
475 # necessarily a field (because 'id' is not a field instance)
476 # i.e.: inherits: 'partner_id', found in the inherits of the current table
477 # i.e.: one2many: 'id', not a field
478 # i.e.: many2one: 'state_id', the current field name
479 # - dst_table_link_name: field name used to link the dst table, not
480 # necessarily a field (because 'id' is not a field instance)
481 # i.e.: inherits: 'id', not a field
482 # i.e.: one2many: 'partner_id', _fields_id of the current field
483 # i.e.: many2one: 'id', not a field
484 # - context_field_name: field name used as a context to make the alias
485 # i.e.: inherits: 'partner_id': found in the inherits of the current table
486 # i.e.: one2many: 'bank_ids': current field name
487 # i.e.: many2one: 'state_id': current field name
488 # --------------------------------------------------
490 def __init__(self, leaf, model, join_context=None):
491 """ Initialize the ExtendedLeaf
493 :attr [string, tuple] leaf: operator or tuple-formatted domain
495 :attr obj model: current working model
496 :attr list _models: list of chained models, updated when
498 :attr list join_context: list of join contexts. This is a list of
499 tuples like ``(lhs, table, lhs_col, col, link)``
500 :param obj lhs: source (left hand) model
501 :param obj model: destination (right hand) model
502 :param string lhs_col: source model column for join condition
503 :param string col: destination model column for join condition
504 :param link: link column between source and destination model
505 that is not necessarily (but generally) a real column used
506 in the condition (i.e. in many2one); this link is used to
509 assert model, 'Invalid leaf creation without table'
510 self.join_context = join_context or []
512 # normalize the leaf's operator
513 self.normalize_leaf()
514 # set working variables; handle the context stack and previous tables
517 for item in self.join_context:
518 self._models.append(item[0])
519 self._models.append(model)
524 return '<osv.ExtendedLeaf: %s on %s (ctx: %s)>' % (str(self.leaf), self.model._table, ','.join(self._get_context_debug()))
526 def generate_alias(self):
527 links = [(context[1]._table, context[4]) for context in self.join_context]
528 alias, alias_statement = generate_table_alias(self._models[0]._table, links)
531 def add_join_context(self, model, lhs_col, table_col, link):
532 """ See above comments for more details. A join context is a tuple like:
533 ``(lhs, model, lhs_col, col, link)``
535 After adding the join, the model of the current leaf is updated.
537 self.join_context.append((self.model, model, lhs_col, table_col, link))
538 self._models.append(model)
541 def get_join_conditions(self):
543 alias = self._models[0]._table
544 for context in self.join_context:
545 previous_alias = alias
546 alias += '__' + context[4]
547 conditions.append('"%s"."%s"="%s"."%s"' % (previous_alias, context[2], alias, context[3]))
550 def get_tables(self):
553 for context in self.join_context:
554 links.append((context[1]._table, context[4]))
555 alias, alias_statement = generate_table_alias(self._models[0]._table, links)
556 tables.add(alias_statement)
559 def _get_context_debug(self):
560 names = ['"%s"."%s"="%s"."%s" (%s)' % (item[0]._table, item[2], item[1]._table, item[3], item[4]) for item in self.join_context]
563 # --------------------------------------------------
565 # --------------------------------------------------
567 def check_leaf(self):
568 """ Leaf validity rules:
569 - a valid leaf is an operator or a leaf
570 - a valid leaf has a field objects unless
572 - it is an inherited field
573 - left is id, operator is 'child_of'
574 - left is in MAGIC_COLUMNS
576 if not is_operator(self.leaf) and not is_leaf(self.leaf, True):
577 raise ValueError("Invalid leaf %s" % str(self.leaf))
579 def is_operator(self):
580 return is_operator(self.leaf)
582 def is_true_leaf(self):
583 return self.leaf == TRUE_LEAF
585 def is_false_leaf(self):
586 return self.leaf == FALSE_LEAF
588 def is_leaf(self, internal=False):
589 return is_leaf(self.leaf, internal=internal)
591 def normalize_leaf(self):
592 self.leaf = normalize_leaf(self.leaf)
596 class expression(object):
597 """ Parse a domain expression
598 Use a real polish notation
599 Leafs are still in a ('foo', '=', 'bar') format
600 For more info: http://christophe-simonis-at-tiny.blogspot.com/2008/08/new-new-domain-notation.html
603 def __init__(self, cr, uid, exp, table, context):
604 """ Initialize expression object and automatically parse the expression
605 right after initialization.
607 :param exp: expression (using domain ('foo', '=', 'bar' format))
608 :param table: root model
610 :attr list result: list that will hold the result of the parsing
611 as a list of ExtendedLeaf
612 :attr list joins: list of join conditions, such as
613 (res_country_state."id" = res_partner."state_id")
614 :attr root_model: base model for the query
615 :attr list expression: the domain expression, that will be normalized
618 self.has_unaccent = openerp.modules.registry.RegistryManager.get(cr.dbname).has_unaccent
620 self.root_model = table
622 # normalize and prepare the expression for parsing
623 self.expression = distribute_not(normalize_domain(exp))
625 # parse the domain expression
626 self.parse(cr, uid, context=context)
628 # ----------------------------------------
630 # ----------------------------------------
632 def get_tables(self):
633 """ Returns the list of tables for SQL queries, like select from ... """
635 for leaf in self.result:
636 for table in leaf.get_tables():
637 if table not in tables:
639 table_name = _quote(self.root_model._table)
640 if table_name not in tables:
641 tables.append(table_name)
644 # ----------------------------------------
646 # ----------------------------------------
648 def parse(self, cr, uid, context):
649 """ Transform the leaves of the expression
651 The principle is to pop elements from a leaf stack one at a time.
652 Each leaf is processed. The processing is a if/elif list of various
653 cases that appear in the leafs (many2one, function fields, ...).
654 Two things can happen as a processing result:
655 - the leaf has been modified and/or new leafs have to be introduced
656 in the expression; they are pushed into the leaf stack, to be
657 processed right after
658 - the leaf is added to the result
660 Some internal var explanation:
661 :var obj working_model: model object, model containing the field
662 (the name provided in the left operand)
663 :var list field_path: left operand seen as a path (foo.bar -> [foo, bar])
664 :var obj relational_model: relational model of a field (field._obj)
665 ex: res_partner.bank_ids -> res.partner.bank
668 def to_ids(value, relational_model, context=None, limit=None):
669 """ Normalize a single id or name, or a list of those, into a list of ids
670 :param {int,long,basestring,list,tuple} value:
671 if int, long -> return [value]
672 if basestring, convert it into a list of basestrings, then
673 if list of basestring ->
674 perform a name_search on relational_model for each name
675 return the list of related ids
678 if isinstance(value, basestring):
680 elif value and isinstance(value, (tuple, list)) and all(isinstance(item, basestring) for item in value):
682 elif isinstance(value, (int, long)):
685 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)]
686 return list(set(name_get_list))
689 def child_of_domain(left, ids, left_model, parent=None, prefix='', context=None):
690 """ Return a domain implementing the child_of operator for [(left,child_of,ids)],
691 either as a range using the parent_left/right tree lookup fields
692 (when available), or as an expanded [(left,in,child_ids)] """
693 if left_model._parent_store and (not left_model.pool._init):
694 # TODO: Improve where joins are implemented for many with '.', replace by:
695 # doms += ['&',(prefix+'.parent_left','<',o.parent_right),(prefix+'.parent_left','>=',o.parent_left)]
697 for o in left_model.browse(cr, uid, ids, context=context):
699 doms.insert(0, OR_OPERATOR)
700 doms += [AND_OPERATOR, ('parent_left', '<', o.parent_right), ('parent_left', '>=', o.parent_left)]
702 return [(left, 'in', left_model.search(cr, uid, doms, context=context))]
705 def recursive_children(ids, model, parent_field):
708 ids2 = model.search(cr, uid, [(parent_field, 'in', ids)], context=context)
709 return ids + recursive_children(ids2, model, parent_field)
710 return [(left, 'in', recursive_children(ids, left_model, parent or left_model._parent_name))]
712 def create_substitution_leaf(leaf, new_elements, new_model=None):
713 """ From a leaf, create a new leaf (based on the new_elements tuple
714 and new_model), that will have the same join context. Used to
715 insert equivalent leafs in the processing stack. """
716 if new_model is None:
717 new_model = leaf.model
718 new_join_context = [tuple(context) for context in leaf.join_context]
719 new_leaf = ExtendedLeaf(new_elements, new_model, join_context=new_join_context)
723 """ Pop a leaf to process. """
724 return self.stack.pop()
727 """ Push a leaf to be processed right after. """
728 self.stack.append(leaf)
730 def push_result(leaf):
731 """ Push a leaf to the results. This leaf has been fully processed
733 self.result.append(leaf)
736 self.stack = [ExtendedLeaf(leaf, self.root_model) for leaf in self.expression]
737 # process from right to left; expression is from left to right
741 # Get the next leaf to process
744 # Get working variables
745 working_model = leaf.model
746 if leaf.is_operator():
747 left, operator, right = leaf.leaf, None, None
748 elif leaf.is_true_leaf() or leaf.is_false_leaf():
749 # because we consider left as a string
750 left, operator, right = ('%s' % leaf.leaf[0], leaf.leaf[1], leaf.leaf[2])
752 left, operator, right = leaf.leaf
753 field_path = left.split('.', 1)
754 field = working_model._columns.get(field_path[0])
755 if field and field._obj:
756 relational_model = working_model.pool.get(field._obj)
758 relational_model = None
760 # ----------------------------------------
762 # 1. leaf is an operator
763 # 2. leaf is a true/false leaf
764 # -> add directly to result
765 # ----------------------------------------
767 if leaf.is_operator() or leaf.is_true_leaf() or leaf.is_false_leaf():
770 # ----------------------------------------
772 # -> from inherits'd fields -> work on the related model, and add
774 # -> ('id', 'child_of', '..') -> use a 'to_ids'
775 # -> but is one on the _log_access special fields, add directly to
777 # TODO: make these fields explicitly available in self.columns instead!
779 # ----------------------------------------
781 elif not field and field_path[0] in working_model._inherit_fields:
782 # comments about inherits'd fields
783 # { 'field_name': ('parent_model', 'm2o_field_to_reach_parent',
784 # field_column_obj, origina_parent_model), ... }
785 next_model = working_model.pool.get(working_model._inherit_fields[field_path[0]][0])
786 leaf.add_join_context(next_model, working_model._inherits[next_model._name], 'id', working_model._inherits[next_model._name])
789 elif not field and left == 'id' and operator == 'child_of':
790 ids2 = to_ids(right, working_model, context)
791 dom = child_of_domain(left, ids2, working_model)
792 for dom_leaf in reversed(dom):
793 new_leaf = create_substitution_leaf(leaf, dom_leaf, working_model)
796 elif not field and field_path[0] in MAGIC_COLUMNS:
800 raise ValueError("Invalid field %r in leaf %r" % (left, str(leaf)))
802 # ----------------------------------------
804 # -> many2one or one2many with _auto_join:
805 # - add a join, then jump into linked field: field.remaining on
806 # src_table is replaced by remaining on dst_table, and set for re-evaluation
807 # - if a domain is defined on the field, add it into evaluation
808 # on the relational table
809 # -> many2one, many2many, one2many: replace by an equivalent computed
810 # domain, given by recursively searching on the remaining of the path
811 # -> note: hack about fields.property should not be necessary anymore
812 # as after transforming the field, it will go through this loop once again
813 # ----------------------------------------
815 elif len(field_path) > 1 and field._type == 'many2one' and field._auto_join:
816 # res_partner.state_id = res_partner__state_id.id
817 leaf.add_join_context(relational_model, field_path[0], 'id', field_path[0])
818 push(create_substitution_leaf(leaf, (field_path[1], operator, right), relational_model))
820 elif len(field_path) > 1 and field._type == 'one2many' and field._auto_join:
821 # res_partner.id = res_partner__bank_ids.partner_id
822 leaf.add_join_context(relational_model, 'id', field._fields_id, field_path[0])
823 domain = field._domain(working_model) if callable(field._domain) else field._domain
824 push(create_substitution_leaf(leaf, (field_path[1], operator, right), relational_model))
826 domain = normalize_domain(domain)
827 for elem in reversed(domain):
828 push(create_substitution_leaf(leaf, elem, relational_model))
829 push(create_substitution_leaf(leaf, AND_OPERATOR, relational_model))
831 elif len(field_path) > 1 and field._auto_join:
832 raise NotImplementedError('_auto_join attribute not supported on many2many field %s' % (left))
834 elif len(field_path) > 1 and field._type == 'many2one':
835 right_ids = relational_model.search(cr, uid, [(field_path[1], operator, right)], context=context)
836 leaf.leaf = (field_path[0], 'in', right_ids)
839 # Making search easier when there is a left operand as field.o2m or field.m2m
840 elif len(field_path) > 1 and field._type in ['many2many', 'one2many']:
841 right_ids = relational_model.search(cr, uid, [(field_path[1], operator, right)], context=context)
842 table_ids = working_model.search(cr, uid, [(field_path[0], 'in', right_ids)], context=dict(context, active_test=False))
843 leaf.leaf = ('id', 'in', table_ids)
846 # -------------------------------------------------
848 # -> not stored: error if no _fnct_search, otherwise handle the result domain
849 # -> stored: management done in the remaining of parsing
850 # -------------------------------------------------
852 elif isinstance(field, fields.function) and not field.store and not field._fnct_search:
853 # this is a function field that is not stored
854 # the function field doesn't provide a search function and doesn't store
855 # values in the database, so we must ignore it : we generate a dummy leaf
856 leaf.leaf = TRUE_LEAF
858 "The field '%s' (%s) can not be searched: non-stored "
859 "function field without fnct_search",
861 # avoid compiling stack trace if not needed
862 if _logger.isEnabledFor(logging.DEBUG):
863 _logger.debug(''.join(traceback.format_stack()))
866 elif isinstance(field, fields.function) and not field.store:
867 # this is a function field that is not stored
868 fct_domain = field.search(cr, uid, working_model, left, [leaf.leaf], context=context)
870 leaf.leaf = TRUE_LEAF
873 # we assume that the expression is valid
874 # we create a dummy leaf for forcing the parsing of the resulting expression
875 for domain_element in reversed(fct_domain):
876 push(create_substitution_leaf(leaf, domain_element, working_model))
877 # self.push(create_substitution_leaf(leaf, TRUE_LEAF, working_model))
878 # self.push(create_substitution_leaf(leaf, AND_OPERATOR, working_model))
880 # -------------------------------------------------
882 # -------------------------------------------------
884 # Applying recursivity on field(one2many)
885 elif field._type == 'one2many' and operator == 'child_of':
886 ids2 = to_ids(right, relational_model, context)
887 if field._obj != working_model._name:
888 dom = child_of_domain(left, ids2, relational_model, prefix=field._obj)
890 dom = child_of_domain('id', ids2, working_model, parent=left)
891 for dom_leaf in reversed(dom):
892 push(create_substitution_leaf(leaf, dom_leaf, working_model))
894 elif field._type == 'one2many':
897 if right is not False:
898 if isinstance(right, basestring):
899 ids2 = [x[0] for x in relational_model.name_search(cr, uid, right, [], operator, context=context, limit=None)]
903 if not isinstance(right, list):
908 if operator in ['like', 'ilike', 'in', '=']:
909 #no result found with given search criteria
911 push(create_substitution_leaf(leaf, FALSE_LEAF, working_model))
913 ids2 = select_from_where(cr, field._fields_id, relational_model._table, 'id', ids2, operator)
916 o2m_op = 'not in' if operator in NEGATIVE_TERM_OPERATORS else 'in'
917 push(create_substitution_leaf(leaf, ('id', o2m_op, ids2), working_model))
920 o2m_op = 'in' if operator in NEGATIVE_TERM_OPERATORS else 'not in'
921 push(create_substitution_leaf(leaf, ('id', o2m_op, select_distinct_from_where_not_null(cr, field._fields_id, relational_model._table)), working_model))
923 elif field._type == 'many2many':
924 rel_table, rel_id1, rel_id2 = field._sql_names(working_model)
926 if operator == 'child_of':
927 def _rec_convert(ids):
928 if relational_model == working_model:
930 return select_from_where(cr, rel_id1, rel_table, rel_id2, ids, operator)
932 ids2 = to_ids(right, relational_model, context)
933 dom = child_of_domain('id', ids2, relational_model)
934 ids2 = relational_model.search(cr, uid, dom, context=context)
935 push(create_substitution_leaf(leaf, ('id', 'in', _rec_convert(ids2)), working_model))
938 if right is not False:
939 if isinstance(right, basestring):
940 res_ids = [x[0] for x in relational_model.name_search(cr, uid, right, [], operator, context=context)]
944 if not isinstance(right, list):
949 if operator in ['like', 'ilike', 'in', '=']:
950 #no result found with given search criteria
951 call_null_m2m = False
952 push(create_substitution_leaf(leaf, FALSE_LEAF, working_model))
954 operator = 'in' # operator changed because ids are directly related to main object
956 call_null_m2m = False
957 m2m_op = 'not in' if operator in NEGATIVE_TERM_OPERATORS else 'in'
958 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))
961 m2m_op = 'in' if operator in NEGATIVE_TERM_OPERATORS else 'not in'
962 push(create_substitution_leaf(leaf, ('id', m2m_op, select_distinct_from_where_not_null(cr, rel_id1, rel_table)), working_model))
964 elif field._type == 'many2one':
965 if operator == 'child_of':
966 ids2 = to_ids(right, relational_model, context)
967 if field._obj != working_model._name:
968 dom = child_of_domain(left, ids2, relational_model, prefix=field._obj)
970 dom = child_of_domain('id', ids2, working_model, parent=left)
971 for dom_leaf in reversed(dom):
972 push(create_substitution_leaf(leaf, dom_leaf, working_model))
974 def _get_expression(relational_model, cr, uid, left, right, operator, context=None):
978 c['active_test'] = False
979 #Special treatment to ill-formed domains
980 operator = (operator in ['<', '>', '<=', '>=']) and 'in' or operator
982 dict_op = {'not in': '!=', 'in': '=', '=': 'in', '!=': 'not in'}
983 if isinstance(right, tuple):
985 if (not isinstance(right, list)) and operator in ['not in', 'in']:
986 operator = dict_op[operator]
987 elif isinstance(right, list) and operator in ['!=', '=']: # for domain (FIELD,'=',['value1','value2'])
988 operator = dict_op[operator]
989 res_ids = [x[0] for x in relational_model.name_search(cr, uid, right, [], operator, limit=None, context=c)]
990 if operator in NEGATIVE_TERM_OPERATORS:
991 res_ids.append(False) # TODO this should not be appended if False was in 'right'
992 return (left, 'in', res_ids)
993 # resolve string-based m2o criterion into IDs
994 if isinstance(right, basestring) or \
995 right and isinstance(right, (tuple, list)) and all(isinstance(item, basestring) for item in right):
996 push(create_substitution_leaf(leaf, _get_expression(relational_model, cr, uid, left, right, operator, context=context), working_model))
998 # right == [] or right == False and all other cases are handled by __leaf_to_sql()
1001 # -------------------------------------------------
1003 # -> datetime fields: manage time part of the datetime
1004 # field when it is not there
1005 # -> manage translatable fields
1006 # -------------------------------------------------
1009 if field._type == 'datetime' and right and len(right) == 10:
1010 if operator in ('>', '>='):
1011 right += ' 00:00:00'
1012 elif operator in ('<', '<='):
1013 right += ' 23:59:59'
1014 push(create_substitution_leaf(leaf, (left, operator, right), working_model))
1016 elif field.translate:
1017 need_wildcard = operator in ('like', 'ilike', 'not like', 'not ilike')
1018 sql_operator = {'=like': 'like', '=ilike': 'ilike'}.get(operator, operator)
1020 right = '%%%s%%' % right
1022 subselect = '( SELECT res_id' \
1023 ' FROM ir_translation' \
1024 ' WHERE name = %s' \
1028 #Covering in,not in operators with operands (%s,%s) ,etc.
1029 if sql_operator in ['in', 'not in']:
1030 instr = ','.join(['%s'] * len(right))
1031 subselect += ' AND value ' + sql_operator + ' ' + " (" + instr + ")" \
1034 ' FROM "' + working_model._table + '"' \
1035 ' WHERE "' + left + '" ' + sql_operator + ' ' + " (" + instr + "))"
1037 subselect += ' AND value ' + sql_operator + instr + \
1040 ' FROM "' + working_model._table + '"' \
1041 ' WHERE "' + left + '" ' + sql_operator + instr + ")"
1043 params = [working_model._name + ',' + left,
1044 context.get('lang', False) or 'en_US',
1049 push(create_substitution_leaf(leaf, ('id', 'inselect', (subselect, params)), working_model))
1054 # ----------------------------------------
1055 # END OF PARSING FULL DOMAIN
1057 # ----------------------------------------
1060 for leaf in self.result:
1061 joins |= set(leaf.get_join_conditions())
1062 self.joins = list(joins)
1064 def __leaf_to_sql(self, eleaf):
1067 left, operator, right = leaf
1069 # final sanity checks - should never fail
1070 assert operator in (TERM_OPERATORS + ('inselect',)), \
1071 "Invalid operator %r in domain term %r" % (operator, leaf)
1072 assert leaf in (TRUE_LEAF, FALSE_LEAF) or left in model._all_columns \
1073 or left in MAGIC_COLUMNS, "Invalid field %r in domain term %r" % (left, leaf)
1075 table_alias = '"%s"' % (eleaf.generate_alias())
1077 if leaf == TRUE_LEAF:
1081 elif leaf == FALSE_LEAF:
1085 elif operator == 'inselect':
1086 query = '(%s."%s" in (%s))' % (table_alias, left, right[0])
1089 elif operator in ['in', 'not in']:
1090 # Two cases: right is a boolean or a list. The boolean case is an
1091 # abuse and handled for backward compatibility.
1092 if isinstance(right, bool):
1093 _logger.warning("The domain term '%s' should use the '=' or '!=' operator." % (leaf,))
1094 if operator == 'in':
1095 r = 'NOT NULL' if right else 'NULL'
1097 r = 'NULL' if right else 'NOT NULL'
1098 query = '(%s."%s" IS %s)' % (table_alias, left, r)
1100 elif isinstance(right, (list, tuple)):
1103 for i in range(len(params))[::-1]:
1104 if params[i] == False:
1110 instr = ','.join(['%s'] * len(params))
1112 instr = ','.join([model._columns[left]._symbol_set[0]] * len(params))
1113 query = '(%s."%s" %s (%s))' % (table_alias, left, operator, instr)
1115 # The case for (left, 'in', []) or (left, 'not in', []).
1116 query = 'FALSE' if operator == 'in' else 'TRUE'
1118 if check_nulls and operator == 'in':
1119 query = '(%s OR %s."%s" IS NULL)' % (query, table_alias, left)
1120 elif not check_nulls and operator == 'not in':
1121 query = '(%s OR %s."%s" IS NULL)' % (query, table_alias, left)
1122 elif check_nulls and operator == 'not in':
1123 query = '(%s AND %s."%s" IS NOT NULL)' % (query, table_alias, left) # needed only for TRUE.
1124 else: # Must not happen
1125 raise ValueError("Invalid domain term %r" % (leaf,))
1127 elif right == False and (left in model._columns) and model._columns[left]._type == "boolean" and (operator == '='):
1128 query = '(%s."%s" IS NULL or %s."%s" = false )' % (table_alias, left, table_alias, left)
1131 elif (right is False or right is None) and (operator == '='):
1132 query = '%s."%s" IS NULL ' % (table_alias, left)
1135 elif right == False and (left in model._columns) and model._columns[left]._type == "boolean" and (operator == '!='):
1136 query = '(%s."%s" IS NOT NULL and %s."%s" != false)' % (table_alias, left, table_alias, left)
1139 elif (right is False or right is None) and (operator == '!='):
1140 query = '%s."%s" IS NOT NULL' % (table_alias, left)
1143 elif (operator == '=?'):
1144 if (right is False or right is None):
1145 # '=?' is a short-circuit that makes the term TRUE if right is None or False
1149 # '=?' behaves like '=' in other cases
1150 query, params = self.__leaf_to_sql((left, '=', right), model)
1153 query = '%s.id %s %%s' % (table_alias, operator)
1157 need_wildcard = operator in ('like', 'ilike', 'not like', 'not ilike')
1158 sql_operator = {'=like': 'like', '=ilike': 'ilike'}.get(operator, operator)
1160 if left in model._columns:
1161 format = need_wildcard and '%s' or model._columns[left]._symbol_set[0]
1162 if self.has_unaccent and sql_operator in ('ilike', 'not ilike'):
1163 query = '(unaccent(%s."%s") %s unaccent(%s))' % (table_alias, left, sql_operator, format)
1165 query = '(%s."%s" %s %s)' % (table_alias, left, sql_operator, format)
1166 elif left in MAGIC_COLUMNS:
1167 query = "(%s.\"%s\" %s %%s)" % (table_alias, left, sql_operator)
1169 else: # Must not happen
1170 raise ValueError("Invalid field %r in domain term %r" % (left, leaf))
1174 if isinstance(right, str):
1176 elif isinstance(right, unicode):
1177 str_utf8 = right.encode('utf-8')
1179 str_utf8 = str(right)
1180 params = '%%%s%%' % str_utf8
1181 add_null = not str_utf8
1182 elif left in model._columns:
1183 params = model._columns[left]._symbol_set[1](right)
1186 query = '(%s OR %s."%s" IS NULL)' % (query, table_alias, left)
1188 if isinstance(params, basestring):
1190 return (query, params)
1195 # Process the domain from right to left, using a stack, to generate a SQL expression.
1196 self.result.reverse()
1197 for leaf in self.result:
1198 if leaf.is_leaf(internal=True):
1199 q, p = self.__leaf_to_sql(leaf)
1202 elif leaf.leaf == NOT_OPERATOR:
1203 stack.append('(NOT (%s))' % (stack.pop(),))
1205 ops = {AND_OPERATOR: ' AND ', OR_OPERATOR: ' OR '}
1208 stack.append('(%s %s %s)' % (q1, ops[leaf.leaf], q2,))
1210 assert len(stack) == 1
1212 joins = ' AND '.join(self.joins)
1214 query = '(%s) AND %s' % (joins, query)
1216 return (query, tools.flatten(params))
1218 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: