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()]
433 # --------------------------------------------------
434 # ExtendedLeaf class for managing leafs and contexts
435 # -------------------------------------------------
437 class ExtendedLeaf(object):
438 """ Class wrapping a domain leaf, and giving some services and management
439 features on it. In particular it managed join contexts to be able to
440 construct queries through multiple models.
443 # --------------------------------------------------
444 # Join / Context manipulation
446 # - res_users.name, like, foo: name is on res_partner, not on res_users
447 # - res_partner.bank_ids.name, like, foo: bank_ids is a one2many with _auto_join
448 # - res_partner.state_id.name, like, foo: state_id is a many2one with _auto_join
450 # - link between src_table and dst_table, using src_field and dst_field
451 # i.e.: inherits: res_users.partner_id = res_partner.id
452 # i.e.: one2many: res_partner.id = res_partner_bank.partner_id
453 # i.e.: many2one: res_partner.state_id = res_country_state.id
454 # - done in the context of a field
455 # i.e.: inherits: 'partner_id'
456 # i.e.: one2many: 'bank_ids'
457 # i.e.: many2one: 'state_id'
458 # - table names use aliases: initial table followed by the context field
459 # names, joined using a '__'
460 # i.e.: inherits: res_partner as res_users__partner_id
461 # i.e.: one2many: res_partner_bank as res_partner__bank_ids
462 # i.e.: many2one: res_country_state as res_partner__state_id
463 # - join condition use aliases
464 # i.e.: inherits: res_users.partner_id = res_users__partner_id.id
465 # i.e.: one2many: res_partner.id = res_partner__bank_ids.parr_id
466 # i.e.: many2one: res_partner.state_id = res_partner__state_id.id
467 # Variables explanation:
468 # - src_table: working table before the join
469 # -> res_users, res_partner, res_partner
470 # - dst_table: working table after the join
471 # -> res_partner, res_partner_bank, res_country_state
472 # - src_table_link_name: field name used to link the src table, not
473 # necessarily a field (because 'id' is not a field instance)
474 # i.e.: inherits: 'partner_id', found in the inherits of the current table
475 # i.e.: one2many: 'id', not a field
476 # i.e.: many2one: 'state_id', the current field name
477 # - dst_table_link_name: field name used to link the dst table, not
478 # necessarily a field (because 'id' is not a field instance)
479 # i.e.: inherits: 'id', not a field
480 # i.e.: one2many: 'partner_id', _fields_id of the current field
481 # i.e.: many2one: 'id', not a field
482 # - context_field_name: field name used as a context to make the alias
483 # i.e.: inherits: 'partner_id': found in the inherits of the current table
484 # i.e.: one2many: 'bank_ids': current field name
485 # i.e.: many2one: 'state_id': current field name
486 # --------------------------------------------------
488 def __init__(self, leaf, model, join_context=None):
489 """ Initialize the ExtendedLeaf
491 :attr [string, tuple] leaf: operator or tuple-formatted domain
493 :attr obj model: current working model
494 :attr list _models: list of chained models, updated when
496 :attr list join_context: list of join contexts. This is a list of
497 tuples like ``(lhs, table, lhs_col, col, link)``
502 source (left hand) model
504 destination (right hand) model
506 source model column for join condition
508 destination model column for join condition
510 link column between source and destination model
511 that is not necessarily (but generally) a real column used
512 in the condition (i.e. in many2one); this link is used to
515 assert model, 'Invalid leaf creation without table'
516 self.join_context = join_context or []
518 # normalize the leaf's operator
519 self.normalize_leaf()
520 # set working variables; handle the context stack and previous tables
523 for item in self.join_context:
524 self._models.append(item[0])
525 self._models.append(model)
530 return '<osv.ExtendedLeaf: %s on %s (ctx: %s)>' % (str(self.leaf), self.model._table, ','.join(self._get_context_debug()))
532 def generate_alias(self):
533 links = [(context[1]._table, context[4]) for context in self.join_context]
534 alias, alias_statement = generate_table_alias(self._models[0]._table, links)
537 def add_join_context(self, model, lhs_col, table_col, link):
538 """ See above comments for more details. A join context is a tuple like:
539 ``(lhs, model, lhs_col, col, link)``
541 After adding the join, the model of the current leaf is updated.
543 self.join_context.append((self.model, model, lhs_col, table_col, link))
544 self._models.append(model)
547 def get_join_conditions(self):
549 alias = self._models[0]._table
550 for context in self.join_context:
551 previous_alias = alias
552 alias += '__' + context[4]
553 conditions.append('"%s"."%s"="%s"."%s"' % (previous_alias, context[2], alias, context[3]))
556 def get_tables(self):
559 for context in self.join_context:
560 links.append((context[1]._table, context[4]))
561 alias, alias_statement = generate_table_alias(self._models[0]._table, links)
562 tables.add(alias_statement)
565 def _get_context_debug(self):
566 names = ['"%s"."%s"="%s"."%s" (%s)' % (item[0]._table, item[2], item[1]._table, item[3], item[4]) for item in self.join_context]
569 # --------------------------------------------------
571 # --------------------------------------------------
573 def check_leaf(self):
574 """ Leaf validity rules:
575 - a valid leaf is an operator or a leaf
576 - a valid leaf has a field objects unless
578 - it is an inherited field
579 - left is id, operator is 'child_of'
580 - left is in MAGIC_COLUMNS
582 if not is_operator(self.leaf) and not is_leaf(self.leaf, True):
583 raise ValueError("Invalid leaf %s" % str(self.leaf))
585 def is_operator(self):
586 return is_operator(self.leaf)
588 def is_true_leaf(self):
589 return self.leaf == TRUE_LEAF
591 def is_false_leaf(self):
592 return self.leaf == FALSE_LEAF
594 def is_leaf(self, internal=False):
595 return is_leaf(self.leaf, internal=internal)
597 def normalize_leaf(self):
598 self.leaf = normalize_leaf(self.leaf)
601 def create_substitution_leaf(leaf, new_elements, new_model=None):
602 """ From a leaf, create a new leaf (based on the new_elements tuple
603 and new_model), that will have the same join context. Used to
604 insert equivalent leafs in the processing stack. """
605 if new_model is None:
606 new_model = leaf.model
607 new_join_context = [tuple(context) for context in leaf.join_context]
608 new_leaf = ExtendedLeaf(new_elements, new_model, join_context=new_join_context)
611 class expression(object):
612 """ Parse a domain expression
613 Use a real polish notation
614 Leafs are still in a ('foo', '=', 'bar') format
615 For more info: http://christophe-simonis-at-tiny.blogspot.com/2008/08/new-new-domain-notation.html
618 def __init__(self, cr, uid, exp, table, context):
619 """ Initialize expression object and automatically parse the expression
620 right after initialization.
622 :param exp: expression (using domain ('foo', '=', 'bar' format))
623 :param table: root model
625 :attr list result: list that will hold the result of the parsing
626 as a list of ExtendedLeaf
627 :attr list joins: list of join conditions, such as
628 (res_country_state."id" = res_partner."state_id")
629 :attr root_model: base model for the query
630 :attr list expression: the domain expression, that will be normalized
633 self.has_unaccent = openerp.modules.registry.RegistryManager.get(cr.dbname).has_unaccent
635 self.root_model = table
637 # normalize and prepare the expression for parsing
638 self.expression = distribute_not(normalize_domain(exp))
640 # parse the domain expression
641 self.parse(cr, uid, context=context)
643 # ----------------------------------------
645 # ----------------------------------------
647 def get_tables(self):
648 """ Returns the list of tables for SQL queries, like select from ... """
650 for leaf in self.result:
651 for table in leaf.get_tables():
652 if table not in tables:
654 table_name = _quote(self.root_model._table)
655 if table_name not in tables:
656 tables.append(table_name)
659 # ----------------------------------------
661 # ----------------------------------------
663 def parse(self, cr, uid, context):
664 """ Transform the leaves of the expression
666 The principle is to pop elements from a leaf stack one at a time.
667 Each leaf is processed. The processing is a if/elif list of various
668 cases that appear in the leafs (many2one, function fields, ...).
669 Two things can happen as a processing result:
670 - the leaf has been modified and/or new leafs have to be introduced
671 in the expression; they are pushed into the leaf stack, to be
672 processed right after
673 - the leaf is added to the result
675 Some internal var explanation:
676 :var obj working_model: model object, model containing the field
677 (the name provided in the left operand)
678 :var list field_path: left operand seen as a path (foo.bar -> [foo, bar])
679 :var obj relational_model: relational model of a field (field._obj)
680 ex: res_partner.bank_ids -> res.partner.bank
683 def to_ids(value, relational_model, context=None, limit=None):
684 """ Normalize a single id or name, or a list of those, into a list of ids
685 :param {int,long,basestring,list,tuple} value:
686 if int, long -> return [value]
687 if basestring, convert it into a list of basestrings, then
688 if list of basestring ->
689 perform a name_search on relational_model for each name
690 return the list of related ids
693 if isinstance(value, basestring):
695 elif value and isinstance(value, (tuple, list)) and all(isinstance(item, basestring) for item in value):
697 elif isinstance(value, (int, long)):
700 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)]
701 return list(set(name_get_list))
704 def child_of_domain(left, ids, left_model, parent=None, prefix='', context=None):
705 """ Return a domain implementing the child_of operator for [(left,child_of,ids)],
706 either as a range using the parent_left/right tree lookup fields
707 (when available), or as an expanded [(left,in,child_ids)] """
708 if left_model._parent_store and (not left_model.pool._init):
709 # TODO: Improve where joins are implemented for many with '.', replace by:
710 # doms += ['&',(prefix+'.parent_left','<',o.parent_right),(prefix+'.parent_left','>=',o.parent_left)]
712 for o in left_model.browse(cr, uid, ids, context=context):
714 doms.insert(0, OR_OPERATOR)
715 doms += [AND_OPERATOR, ('parent_left', '<', o.parent_right), ('parent_left', '>=', o.parent_left)]
717 return [(left, 'in', left_model.search(cr, uid, doms, context=context))]
720 def recursive_children(ids, model, parent_field):
723 ids2 = model.search(cr, uid, [(parent_field, 'in', ids)], context=context)
724 return ids + recursive_children(ids2, model, parent_field)
725 return [(left, 'in', recursive_children(ids, left_model, parent or left_model._parent_name))]
728 """ Pop a leaf to process. """
729 return self.stack.pop()
732 """ Push a leaf to be processed right after. """
733 self.stack.append(leaf)
735 def push_result(leaf):
736 """ Push a leaf to the results. This leaf has been fully processed
738 self.result.append(leaf)
741 self.stack = [ExtendedLeaf(leaf, self.root_model) for leaf in self.expression]
742 # process from right to left; expression is from left to right
746 # Get the next leaf to process
749 # Get working variables
750 working_model = leaf.model
751 if leaf.is_operator():
752 left, operator, right = leaf.leaf, None, None
753 elif leaf.is_true_leaf() or leaf.is_false_leaf():
754 # because we consider left as a string
755 left, operator, right = ('%s' % leaf.leaf[0], leaf.leaf[1], leaf.leaf[2])
757 left, operator, right = leaf.leaf
758 field_path = left.split('.', 1)
759 field = working_model._columns.get(field_path[0])
760 if field and field._obj:
761 relational_model = working_model.pool[field._obj]
763 relational_model = None
765 # ----------------------------------------
767 # 1. leaf is an operator
768 # 2. leaf is a true/false leaf
769 # -> add directly to result
770 # ----------------------------------------
772 if leaf.is_operator() or leaf.is_true_leaf() or leaf.is_false_leaf():
775 # ----------------------------------------
777 # -> from inherits'd fields -> work on the related model, and add
779 # -> ('id', 'child_of', '..') -> use a 'to_ids'
780 # -> but is one on the _log_access special fields, add directly to
782 # TODO: make these fields explicitly available in self.columns instead!
784 # ----------------------------------------
786 elif not field and field_path[0] in working_model._inherit_fields:
787 # comments about inherits'd fields
788 # { 'field_name': ('parent_model', 'm2o_field_to_reach_parent',
789 # field_column_obj, origina_parent_model), ... }
790 next_model = working_model.pool[working_model._inherit_fields[field_path[0]][0]]
791 leaf.add_join_context(next_model, working_model._inherits[next_model._name], 'id', working_model._inherits[next_model._name])
794 elif left == 'id' and operator == 'child_of':
795 ids2 = to_ids(right, working_model, context)
796 dom = child_of_domain(left, ids2, working_model)
797 for dom_leaf in reversed(dom):
798 new_leaf = create_substitution_leaf(leaf, dom_leaf, working_model)
801 elif not field and field_path[0] in MAGIC_COLUMNS:
805 raise ValueError("Invalid field %r in leaf %r" % (left, str(leaf)))
807 # ----------------------------------------
809 # -> many2one or one2many with _auto_join:
810 # - add a join, then jump into linked field: field.remaining on
811 # src_table is replaced by remaining on dst_table, and set for re-evaluation
812 # - if a domain is defined on the field, add it into evaluation
813 # on the relational table
814 # -> many2one, many2many, one2many: replace by an equivalent computed
815 # domain, given by recursively searching on the remaining of the path
816 # -> note: hack about fields.property should not be necessary anymore
817 # as after transforming the field, it will go through this loop once again
818 # ----------------------------------------
820 elif len(field_path) > 1 and field._type == 'many2one' and field._auto_join:
821 # res_partner.state_id = res_partner__state_id.id
822 leaf.add_join_context(relational_model, field_path[0], 'id', field_path[0])
823 push(create_substitution_leaf(leaf, (field_path[1], operator, right), relational_model))
825 elif len(field_path) > 1 and field._type == 'one2many' and field._auto_join:
826 # res_partner.id = res_partner__bank_ids.partner_id
827 leaf.add_join_context(relational_model, 'id', field._fields_id, field_path[0])
828 domain = field._domain(working_model) if callable(field._domain) else field._domain
829 push(create_substitution_leaf(leaf, (field_path[1], operator, right), relational_model))
831 domain = normalize_domain(domain)
832 for elem in reversed(domain):
833 push(create_substitution_leaf(leaf, elem, relational_model))
834 push(create_substitution_leaf(leaf, AND_OPERATOR, relational_model))
836 elif len(field_path) > 1 and field._auto_join:
837 raise NotImplementedError('_auto_join attribute not supported on many2many field %s' % left)
839 elif len(field_path) > 1 and field._type == 'many2one':
840 right_ids = relational_model.search(cr, uid, [(field_path[1], operator, right)], context=context)
841 leaf.leaf = (field_path[0], 'in', right_ids)
844 # Making search easier when there is a left operand as field.o2m or field.m2m
845 elif len(field_path) > 1 and field._type in ['many2many', 'one2many']:
846 right_ids = relational_model.search(cr, uid, [(field_path[1], operator, right)], context=context)
847 table_ids = working_model.search(cr, uid, [(field_path[0], 'in', right_ids)], context=dict(context, active_test=False))
848 leaf.leaf = ('id', 'in', table_ids)
851 # -------------------------------------------------
853 # -> not stored: error if no _fnct_search, otherwise handle the result domain
854 # -> stored: management done in the remaining of parsing
855 # -------------------------------------------------
857 elif isinstance(field, fields.function) and not field.store and not field._fnct_search:
858 # this is a function field that is not stored
859 # the function field doesn't provide a search function and doesn't store
860 # values in the database, so we must ignore it : we generate a dummy leaf
861 leaf.leaf = TRUE_LEAF
863 "The field '%s' (%s) can not be searched: non-stored "
864 "function field without fnct_search",
866 # avoid compiling stack trace if not needed
867 if _logger.isEnabledFor(logging.DEBUG):
868 _logger.debug(''.join(traceback.format_stack()))
871 elif isinstance(field, fields.function) and not field.store:
872 # this is a function field that is not stored
873 fct_domain = field.search(cr, uid, working_model, left, [leaf.leaf], context=context)
875 leaf.leaf = TRUE_LEAF
878 # we assume that the expression is valid
879 # we create a dummy leaf for forcing the parsing of the resulting expression
880 for domain_element in reversed(fct_domain):
881 push(create_substitution_leaf(leaf, domain_element, working_model))
882 # self.push(create_substitution_leaf(leaf, TRUE_LEAF, working_model))
883 # self.push(create_substitution_leaf(leaf, AND_OPERATOR, working_model))
885 # -------------------------------------------------
887 # -------------------------------------------------
889 # Applying recursivity on field(one2many)
890 elif field._type == 'one2many' and operator == 'child_of':
891 ids2 = to_ids(right, relational_model, context)
892 if field._obj != working_model._name:
893 dom = child_of_domain(left, ids2, relational_model, prefix=field._obj)
895 dom = child_of_domain('id', ids2, working_model, parent=left)
896 for dom_leaf in reversed(dom):
897 push(create_substitution_leaf(leaf, dom_leaf, working_model))
899 elif field._type == 'one2many':
902 if right is not False:
903 if isinstance(right, basestring):
904 ids2 = [x[0] for x in relational_model.name_search(cr, uid, right, [], operator, context=context, limit=None)]
908 if not isinstance(right, list):
913 if operator in ['like', 'ilike', 'in', '=']:
914 #no result found with given search criteria
916 push(create_substitution_leaf(leaf, FALSE_LEAF, working_model))
918 ids2 = select_from_where(cr, field._fields_id, relational_model._table, 'id', ids2, operator)
921 o2m_op = 'not in' if operator in NEGATIVE_TERM_OPERATORS else 'in'
922 push(create_substitution_leaf(leaf, ('id', o2m_op, ids2), working_model))
925 o2m_op = 'in' if operator in NEGATIVE_TERM_OPERATORS else 'not in'
926 push(create_substitution_leaf(leaf, ('id', o2m_op, select_distinct_from_where_not_null(cr, field._fields_id, relational_model._table)), working_model))
928 elif field._type == 'many2many':
929 rel_table, rel_id1, rel_id2 = field._sql_names(working_model)
931 if operator == 'child_of':
932 def _rec_convert(ids):
933 if relational_model == working_model:
935 return select_from_where(cr, rel_id1, rel_table, rel_id2, ids, operator)
937 ids2 = to_ids(right, relational_model, context)
938 dom = child_of_domain('id', ids2, relational_model)
939 ids2 = relational_model.search(cr, uid, dom, context=context)
940 push(create_substitution_leaf(leaf, ('id', 'in', _rec_convert(ids2)), working_model))
943 if right is not False:
944 if isinstance(right, basestring):
945 res_ids = [x[0] for x in relational_model.name_search(cr, uid, right, [], operator, context=context)]
949 if not isinstance(right, list):
954 if operator in ['like', 'ilike', 'in', '=']:
955 #no result found with given search criteria
956 call_null_m2m = False
957 push(create_substitution_leaf(leaf, FALSE_LEAF, working_model))
959 operator = 'in' # operator changed because ids are directly related to main object
961 call_null_m2m = False
962 m2m_op = 'not in' if operator in NEGATIVE_TERM_OPERATORS else 'in'
963 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))
966 m2m_op = 'in' if operator in NEGATIVE_TERM_OPERATORS else 'not in'
967 push(create_substitution_leaf(leaf, ('id', m2m_op, select_distinct_from_where_not_null(cr, rel_id1, rel_table)), working_model))
969 elif field._type == 'many2one':
970 if operator == 'child_of':
971 ids2 = to_ids(right, relational_model, context)
972 if field._obj != working_model._name:
973 dom = child_of_domain(left, ids2, relational_model, prefix=field._obj)
975 dom = child_of_domain('id', ids2, working_model, parent=left)
976 for dom_leaf in reversed(dom):
977 push(create_substitution_leaf(leaf, dom_leaf, working_model))
979 def _get_expression(relational_model, cr, uid, left, right, operator, context=None):
983 c['active_test'] = False
984 #Special treatment to ill-formed domains
985 operator = (operator in ['<', '>', '<=', '>=']) and 'in' or operator
987 dict_op = {'not in': '!=', 'in': '=', '=': 'in', '!=': 'not in'}
988 if isinstance(right, tuple):
990 if (not isinstance(right, list)) and operator in ['not in', 'in']:
991 operator = dict_op[operator]
992 elif isinstance(right, list) and operator in ['!=', '=']: # for domain (FIELD,'=',['value1','value2'])
993 operator = dict_op[operator]
994 res_ids = [x[0] for x in relational_model.name_search(cr, uid, right, [], operator, limit=None, context=c)]
995 if operator in NEGATIVE_TERM_OPERATORS:
996 res_ids.append(False) # TODO this should not be appended if False was in 'right'
997 return left, 'in', res_ids
998 # resolve string-based m2o criterion into IDs
999 if isinstance(right, basestring) or \
1000 right and isinstance(right, (tuple, list)) and all(isinstance(item, basestring) for item in right):
1001 push(create_substitution_leaf(leaf, _get_expression(relational_model, cr, uid, left, right, operator, context=context), working_model))
1003 # right == [] or right == False and all other cases are handled by __leaf_to_sql()
1006 # -------------------------------------------------
1008 # -> datetime fields: manage time part of the datetime
1009 # field when it is not there
1010 # -> manage translatable fields
1011 # -------------------------------------------------
1014 if field._type == 'datetime' and right and len(right) == 10:
1015 if operator in ('>', '>=', '='):
1016 right += ' 00:00:00'
1017 elif operator in ('<', '<='):
1018 right += ' 23:59:59'
1019 push(create_substitution_leaf(leaf, (left, operator, right), working_model))
1021 elif field.translate and right:
1022 need_wildcard = operator in ('like', 'ilike', 'not like', 'not ilike')
1023 sql_operator = {'=like': 'like', '=ilike': 'ilike'}.get(operator, operator)
1025 right = '%%%s%%' % right
1027 inselect_operator = 'inselect'
1028 if sql_operator in NEGATIVE_TERM_OPERATORS:
1029 # negate operator (fix lp:1071710)
1030 sql_operator = sql_operator[4:] if sql_operator[:3] == 'not' else '='
1031 inselect_operator = 'not inselect'
1033 subselect = '( SELECT res_id' \
1034 ' FROM ir_translation' \
1035 ' WHERE name = %s' \
1039 #Covering in,not in operators with operands (%s,%s) ,etc.
1040 if sql_operator == 'in':
1041 instr = ','.join(['%s'] * len(right))
1042 subselect += ' AND value ' + sql_operator + ' ' + " (" + instr + ")" \
1045 ' FROM "' + working_model._table + '"' \
1046 ' WHERE "' + left + '" ' + sql_operator + ' ' + " (" + instr + "))"
1048 subselect += ' AND value ' + sql_operator + instr + \
1051 ' FROM "' + working_model._table + '"' \
1052 ' WHERE "' + left + '" ' + sql_operator + instr + ")"
1054 params = [working_model._name + ',' + left,
1055 context.get('lang', False) or 'en_US',
1060 push(create_substitution_leaf(leaf, ('id', inselect_operator, (subselect, params)), working_model))
1065 # ----------------------------------------
1066 # END OF PARSING FULL DOMAIN
1068 # ----------------------------------------
1071 for leaf in self.result:
1072 joins |= set(leaf.get_join_conditions())
1073 self.joins = list(joins)
1075 def __leaf_to_sql(self, eleaf):
1078 left, operator, right = leaf
1080 # final sanity checks - should never fail
1081 assert operator in (TERM_OPERATORS + ('inselect', 'not inselect')), \
1082 "Invalid operator %r in domain term %r" % (operator, leaf)
1083 assert leaf in (TRUE_LEAF, FALSE_LEAF) or left in model._all_columns \
1084 or left in MAGIC_COLUMNS, "Invalid field %r in domain term %r" % (left, leaf)
1086 table_alias = '"%s"' % (eleaf.generate_alias())
1088 if leaf == TRUE_LEAF:
1092 elif leaf == FALSE_LEAF:
1096 elif operator == 'inselect':
1097 query = '(%s."%s" in (%s))' % (table_alias, left, right[0])
1100 elif operator == 'not inselect':
1101 query = '(%s."%s" not in (%s))' % (table_alias, left, right[0])
1104 elif operator in ['in', 'not in']:
1105 # Two cases: right is a boolean or a list. The boolean case is an
1106 # abuse and handled for backward compatibility.
1107 if isinstance(right, bool):
1108 _logger.warning("The domain term '%s' should use the '=' or '!=' operator." % (leaf,))
1109 if operator == 'in':
1110 r = 'NOT NULL' if right else 'NULL'
1112 r = 'NULL' if right else 'NOT NULL'
1113 query = '(%s."%s" IS %s)' % (table_alias, left, r)
1115 elif isinstance(right, (list, tuple)):
1116 params = list(right)
1118 for i in range(len(params))[::-1]:
1119 if params[i] == False:
1125 instr = ','.join(['%s'] * len(params))
1127 ss = model._columns[left]._symbol_set
1128 instr = ','.join([ss[0]] * len(params))
1129 params = map(ss[1], params)
1130 query = '(%s."%s" %s (%s))' % (table_alias, left, operator, instr)
1132 # The case for (left, 'in', []) or (left, 'not in', []).
1133 query = 'FALSE' if operator == 'in' else 'TRUE'
1135 if check_nulls and operator == 'in':
1136 query = '(%s OR %s."%s" IS NULL)' % (query, table_alias, left)
1137 elif not check_nulls and operator == 'not in':
1138 query = '(%s OR %s."%s" IS NULL)' % (query, table_alias, left)
1139 elif check_nulls and operator == 'not in':
1140 query = '(%s AND %s."%s" IS NOT NULL)' % (query, table_alias, left) # needed only for TRUE.
1141 else: # Must not happen
1142 raise ValueError("Invalid domain term %r" % (leaf,))
1144 elif right == False and (left in model._columns) and model._columns[left]._type == "boolean" and (operator == '='):
1145 query = '(%s."%s" IS NULL or %s."%s" = false )' % (table_alias, left, table_alias, left)
1148 elif (right is False or right is None) and (operator == '='):
1149 query = '%s."%s" IS NULL ' % (table_alias, left)
1152 elif right == False and (left in model._columns) and model._columns[left]._type == "boolean" and (operator == '!='):
1153 query = '(%s."%s" IS NOT NULL and %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 NOT NULL' % (table_alias, left)
1160 elif operator == '=?':
1161 if right is False or right is None:
1162 # '=?' is a short-circuit that makes the term TRUE if right is None or False
1166 # '=?' behaves like '=' in other cases
1167 query, params = self.__leaf_to_sql(
1168 create_substitution_leaf(eleaf, (left, '=', right), model))
1171 query = '%s.id %s %%s' % (table_alias, operator)
1175 need_wildcard = operator in ('like', 'ilike', 'not like', 'not ilike')
1176 sql_operator = {'=like': 'like', '=ilike': 'ilike'}.get(operator, operator)
1178 if left in model._columns:
1179 format = need_wildcard and '%s' or model._columns[left]._symbol_set[0]
1180 if self.has_unaccent and sql_operator in ('ilike', 'not ilike'):
1181 query = '(unaccent(%s."%s") %s unaccent(%s))' % (table_alias, left, sql_operator, format)
1183 query = '(%s."%s" %s %s)' % (table_alias, left, sql_operator, format)
1184 elif left in MAGIC_COLUMNS:
1185 query = "(%s.\"%s\" %s %%s)" % (table_alias, left, sql_operator)
1187 else: # Must not happen
1188 raise ValueError("Invalid field %r in domain term %r" % (left, leaf))
1192 if isinstance(right, str):
1194 elif isinstance(right, unicode):
1195 str_utf8 = right.encode('utf-8')
1197 str_utf8 = str(right)
1198 params = '%%%s%%' % str_utf8
1199 add_null = not str_utf8
1200 elif left in model._columns:
1201 params = model._columns[left]._symbol_set[1](right)
1204 query = '(%s OR %s."%s" IS NULL)' % (query, table_alias, left)
1206 if isinstance(params, basestring):
1208 return query, params
1213 # Process the domain from right to left, using a stack, to generate a SQL expression.
1214 self.result.reverse()
1215 for leaf in self.result:
1216 if leaf.is_leaf(internal=True):
1217 q, p = self.__leaf_to_sql(leaf)
1220 elif leaf.leaf == NOT_OPERATOR:
1221 stack.append('(NOT (%s))' % (stack.pop(),))
1223 ops = {AND_OPERATOR: ' AND ', OR_OPERATOR: ' OR '}
1226 stack.append('(%s %s %s)' % (q1, ops[leaf.leaf], q2,))
1228 assert len(stack) == 1
1230 joins = ' AND '.join(self.joins)
1232 query = '(%s) AND %s' % (joins, query)
1234 return query, tools.flatten(params)
1236 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: