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 normalize_leaf(element):
317 """ Change a term's operator to some canonical form, simplifying later
319 if not is_leaf(element):
321 left, operator, right = element
323 operator = operator.lower()
326 if isinstance(right, bool) and operator in ('in', 'not in'):
327 _logger.warning("The domain term '%s' should use the '=' or '!=' operator." % ((left, original, right),))
328 operator = '=' if operator == 'in' else '!='
329 if isinstance(right, (list, tuple)) and operator in ('=', '!='):
330 _logger.warning("The domain term '%s' should use the 'in' or 'not in' operator." % ((left, original, right),))
331 operator = 'in' if operator == '=' else 'not in'
332 return (left, operator, right)
335 def is_operator(element):
336 """Test whether an object is a valid domain operator. """
337 return isinstance(element, basestring) and element in DOMAIN_OPERATORS
340 # TODO change the share wizard to use this function.
341 def is_leaf(element, internal=False):
342 """ Test whether an object is a valid domain term:
345 - second element if a valid op
347 :param tuple element: a leaf in form (left, operator, right)
348 :param boolean internal: allow or not the 'inselect' internal operator
349 in the term. This should be always left to False.
351 INTERNAL_OPS = TERM_OPERATORS + ('<>',)
353 INTERNAL_OPS += ('inselect',)
354 return (isinstance(element, tuple) or isinstance(element, list)) \
355 and len(element) == 3 \
356 and element[1] in INTERNAL_OPS
359 # --------------------------------------------------
361 # --------------------------------------------------
363 def select_from_where(cr, select_field, from_table, where_field, where_ids, where_operator):
364 # todo: merge into parent query as sub-query
367 if where_operator in ['<', '>', '>=', '<=']:
368 cr.execute('SELECT "%s" FROM "%s" WHERE "%s" %s %%s' % \
369 (select_field, from_table, where_field, where_operator),
370 (where_ids[0],)) # TODO shouldn't this be min/max(where_ids) ?
371 res = [r[0] for r in cr.fetchall()]
372 else: # TODO where_operator is supposed to be 'in'? It is called with child_of...
373 for i in range(0, len(where_ids), cr.IN_MAX):
374 subids = where_ids[i:i + cr.IN_MAX]
375 cr.execute('SELECT "%s" FROM "%s" WHERE "%s" IN %%s' % \
376 (select_field, from_table, where_field), (tuple(subids),))
377 res.extend([r[0] for r in cr.fetchall()])
381 def select_distinct_from_where_not_null(cr, select_field, from_table):
382 cr.execute('SELECT distinct("%s") FROM "%s" where "%s" is not null' % (select_field, from_table, select_field))
383 return [r[0] for r in cr.fetchall()]
386 # --------------------------------------------------
387 # ExtendedLeaf class for managing leafs and contexts
388 # -------------------------------------------------
390 class ExtendedLeaf(object):
392 def __init__(self, leaf, table, context_stack=None):
393 """ Initialize the ExtendedLeaf
395 :attr [string, tuple] leaf: operator or tuple-formatted domain
397 :attr object table: table object
398 :attr list _tables: list of chained table objects, updated when
400 :attr tuple elements: manipulation-friendly leaf
401 :attr object field: field obj, taken from table, not necessarily
402 found (inherits, 'id')
403 :attr list field_path: exploded left of elements
404 (partner_id.name -> ['partner_id', 'name'])
405 :attr object relational_table: distant table for relational fields
407 assert table, 'Invalid leaf creation without table'
408 self.context_stack = context_stack or []
411 # normalize the leaf's operator
412 self.normalize_leaf()
413 # set working variables; handle the context stack and previous tables
416 for item in self.context_stack:
417 self._tables.append(item[0])
418 self._tables.append(table)
419 if self.is_operator():
420 self.elements = self.leaf, None, None
421 elif self.is_true_leaf() or self.is_false_leaf():
422 # because we consider left as a string
423 self.elements = ('%s' % leaf[0], leaf[1], leaf[2])
426 self.field_path = self.elements[0].split('.', 1)
427 self.field = self.table._columns.get(self.field_path[0])
428 if self.field and self.field._obj:
429 self.relational_table = self.table.pool.get(self.field._obj)
431 self.relational_table = None
436 return '<osv.ExtendedLeaf: %s on %s (ctx: %s)>' % (str(self.leaf), self.table._table, ','.join(self._get_context_debug()))
438 # def create_substitution_leaf(self, new_leaf, new_table=None):
439 # if new_table is None:
440 # new_table = self.table
441 # return ExtendedLeaf(new_leaf, new_table, self.context_stack)
443 # def create_sibling_leaf(self, new_leaf):
446 # --------------------------------------------------
447 # Join / Context manipulation
449 # - res_users.name, like, foo: name is on res_partner, not on res_users
450 # - res_partner.bank_ids.name, like, foo: bank_ids is a one2many with _auto_join
451 # - res_partner.state_id.name, like, foo: state_id is a many2one with _auto_join
453 # - link between src_table and dst_table, using src_field and dst_field
454 # i.e.: inherits: res_users.partner_id = res_partner.id
455 # i.e.: one2many: res_partner.id = res_partner_bank.partner_id
456 # i.e.: many2one: res_partner.state_id = res_country_state.id
457 # - done in the context of a field
458 # i.e.: inherits: 'partner_id'
459 # i.e.: one2many: 'bank_ids'
460 # i.e.: many2one: 'state_id'
461 # - table names use aliases: initial table followed by the context field
462 # names, joined using a '__'
463 # i.e.: inherits: res_partner as res_users__partner_id
464 # i.e.: one2many: res_partner_bank as res_partner__bank_ids
465 # i.e.: many2one: res_country_state as res_partner__state_id
466 # - join condition use aliases
467 # i.e.: inherits: res_users.partner_id = res_users__partner_id.id
468 # i.e.: one2many: res_partner.id = res_partner__bank_ids.parr_id
469 # i.e.: many2one: res_partner.state_id = res_partner__state_id.id
470 # Variables explanation:
471 # - src_table: working table before the join
472 # -> res_users, res_partner, res_partner
473 # - dst_table: working table after the join
474 # -> res_partner, res_partner_bank, res_country_state
475 # - src_table_link_name: field name used to link the src table, not
476 # necessarily a field (because 'id' is not a field instance)
477 # i.e.: inherits: 'partner_id', found in the inherits of the current table
478 # i.e.: one2many: 'id', not a field
479 # i.e.: many2one: 'state_id', the current field name
480 # - dst_table_link_name: field name used to link the dst table, not
481 # necessarily a field (because 'id' is not a field instance)
482 # i.e.: inherits: 'id', not a field
483 # i.e.: one2many: 'partner_id', _fields_id of the current field
484 # i.e.: many2one: 'id', not a field
485 # - context_field_name: field name used as a context to make the alias
486 # i.e.: inherits: 'partner_id': found in the inherits of the current table
487 # i.e.: one2many: 'bank_ids': current field name
488 # i.e.: many2one: 'state_id': current field name
489 # --------------------------------------------------
491 def _generate_alias(self):
492 alias = self._tables[0]._table
493 for context in self.context_stack:
494 alias += '__' + context[4]
497 def add_join_context(self, context_field_name, src_table_link_name, dst_table, dst_table_link_name):
498 """ See above comments for more details. A join context is a tuple structure
499 holding the following elements: (src_table (self.table), src_table_link_name,
500 dst_table, dst_table_link_name, context_field_name)
501 After adding the join, the table of the current leaf is updated.
503 self.context_stack.append((self.table, src_table_link_name, dst_table, dst_table_link_name, context_field_name))
504 self._tables.append(dst_table)
505 self.table = dst_table
507 def get_join_conditions(self):
509 alias = self._tables[0]._table
510 for context in self.context_stack:
511 previous_alias = alias
512 alias += '__' + context[4]
513 names.append('%s."%s"=%s."%s"' % (previous_alias, context[1], alias, context[3]))
516 def get_tables(self):
518 alias = self._tables[0]._table
519 for context in self.context_stack:
520 alias += '__' + context[4]
521 table_full_alias = '"%s" as %s' % (context[2]._table, alias)
522 tables.add(table_full_alias)
525 def _get_context_debug(self):
526 names = ['%s."%s"=%s."%s" (%s)' % (table[0]._table, table[1], table[2]._table, table[3], table[4]) for table in self.context_stack]
529 # --------------------------------------------------
531 # --------------------------------------------------
533 def check_leaf(self):
534 """ Leaf validity rules:
535 - a valid leaf is an operator or a leaf
536 - a valid leaf has a field objects unless
538 - it is an inherited field
539 - left is id, operator is 'child_of'
540 - left is in MAGIC_COLUMNS
542 if not is_operator(self.leaf) and not is_leaf(self.leaf, True):
543 raise ValueError("Invalid leaf %s" % str(self.leaf))
545 # if self.is_leaf() and not (self.is_true_leaf() or self.is_false_leaf()) \
546 # and not self.field and not self.field_path[0] in self.table._inherit_fields \
547 # and not (self.leaf[0] == 'id' and self.leaf[1] == 'child_of') \
548 # and not (self.field_path[0] in MAGIC_COLUMNS):
549 # raise ValueError("Invalid field %r in leaf %r" % (self.leaf[0], self.leaf))
551 def is_operator(self):
552 return is_operator(self.leaf)
554 def is_true_leaf(self):
555 return self.leaf == TRUE_LEAF
557 def is_false_leaf(self):
558 return self.leaf == FALSE_LEAF
560 def is_leaf(self, internal=False):
561 return is_leaf(self.leaf, internal=internal)
563 def normalize_leaf(self):
564 self.leaf = normalize_leaf(self.leaf)
568 class expression(object):
569 """ Parse a domain expression
570 Use a real polish notation
571 Leafs are still in a ('foo', '=', 'bar') format
572 For more info: http://christophe-simonis-at-tiny.blogspot.com/2008/08/new-new-domain-notation.html
575 def __init__(self, cr, uid, exp, table, context):
576 """ Initialize expression object and automatically parse the expression
577 right after initialization.
579 :param exp: expression (using domain ('foo', '=', 'bar' format))
580 :param table: root table object
582 :attr list result: list that will hold the result of the parsing
583 as a list of ExtendedLeaf
584 :attr list joins: list of join conditions, such as
585 (res_country_state."id" = res_partner."state_id")
586 :attr root_table: base table for the query
587 :attr list expression: the domain expression, that will be normalized
590 self.has_unaccent = openerp.modules.registry.RegistryManager.get(cr.dbname).has_unaccent
593 self.root_table = table
595 # normalize and prepare the expression for parsing
596 self.expression = distribute_not(normalize_domain(exp))
598 # parse the domain expression
599 self.parse(cr, uid, context=context)
601 # ----------------------------------------
602 # Tools for domain manipulation
603 # ----------------------------------------
605 def to_ids(self, cr, uid, value, relational_table, context=None, limit=None):
606 """ Normalize a single id or name, or a list of those, into a list of ids
607 :param {int,long,basestring,list,tuple} value:
608 if int, long -> return [value]
609 if basestring, convert it into a list of basestrings, then
610 if list of basestring ->
611 perform a name_search on relational_table for each name
612 return the list of related ids
615 if isinstance(value, basestring):
617 elif value and isinstance(value, (tuple, list)) and all(isinstance(item, basestring) for item in value):
619 elif isinstance(value, (int, long)):
622 name_get_list = [name_get[0] for name in names for name_get in relational_table.name_search(cr, uid, name, [], 'ilike', context=context, limit=limit)]
623 return list(set(name_get_list))
626 def child_of_domain(self, cr, uid, left, ids, left_model, parent=None, prefix='', context=None):
627 """ Return a domain implementing the child_of operator for [(left,child_of,ids)],
628 either as a range using the parent_left/right tree lookup fields
629 (when available), or as an expanded [(left,in,child_ids)] """
630 if left_model._parent_store and (not left_model.pool._init):
631 # TODO: Improve where joins are implemented for many with '.', replace by:
632 # doms += ['&',(prefix+'.parent_left','<',o.parent_right),(prefix+'.parent_left','>=',o.parent_left)]
634 for o in left_model.browse(cr, uid, ids, context=context):
636 doms.insert(0, OR_OPERATOR)
637 doms += [AND_OPERATOR, ('parent_left', '<', o.parent_right), ('parent_left', '>=', o.parent_left)]
639 return [(left, 'in', left_model.search(cr, uid, doms, context=context))]
642 def recursive_children(ids, model, parent_field):
645 ids2 = model.search(cr, uid, [(parent_field, 'in', ids)], context=context)
646 return ids + recursive_children(ids2, model, parent_field)
647 return [(left, 'in', recursive_children(ids, left_model, parent or left_model._parent_name))]
649 # ----------------------------------------
651 # ----------------------------------------
653 def get_tables(self):
654 """ Returns the list of tables for SQL queries, like select from ... """
656 for leaf in self.result:
657 for table in leaf.get_tables():
658 if table not in tables:
660 table_name = '"%s"' % self.root_table._table
661 if table_name not in tables:
662 tables.append(table_name)
665 def create_substitution_leaf(self, leaf, new_elements, new_table=None):
666 if new_table is None:
667 new_table = leaf.table
668 new_context_stack = [tuple(context) for context in leaf.context_stack]
669 new_leaf = ExtendedLeaf(new_elements, new_table, context_stack=new_context_stack)
672 def create_sibling_leaf(self, leaf, new_elements):
675 # ----------------------------------------
677 # ----------------------------------------
679 def parse(self, cr, uid, context):
680 """ Transform the leaves of the expression
682 The principle is to pop elements from the left of a leaf stack. Each
683 leaf is processed. The processing is a if/elif list of various cases
684 that appear in the leafs (many2one, function fields, ...). Two results
685 can appear at the end of a leaf processing:
686 - the leaf is modified or new leafs introduced in the domain: they
687 are added at the left of the stack, to be processed next
688 - the leaf is added to the result
690 Some var explanation:
691 :var obj working_table: table object, table containing the field
692 (the name provided in the left operand)
693 :var list field_path: left operand seen as a path (foo.bar -> [foo, bar])
694 :var obj relational_table: relational table of a field (field._obj)
695 ex: res_partner.bank_ids -> res_partner_bank
698 stack = [ExtendedLeaf(leaf, self.root_table) for leaf in self.expression]
701 # Get the next leaf to process
704 results_to_stack = []
706 # Get working variables
707 working_table = leaf.table
708 # left, operator, right = leaf.elements
709 # field_path = leaf.field_path
711 # relational_table = leaf.relational_table
713 if leaf.is_operator():
714 left, operator, right = leaf.leaf, None, None
715 elif leaf.is_true_leaf() or leaf.is_false_leaf():
716 # because we consider left as a string
717 left, operator, right = ('%s' % leaf.leaf[0], leaf.leaf[1], leaf.leaf[2])
719 left, operator, right = leaf.leaf
720 # field_path = leaf.field_path
722 field_path = left.split('.', 1)
723 field = working_table._columns.get(field_path[0])
725 if field and field._obj:
726 relational_table = working_table.pool.get(field._obj)
728 relational_table = None
730 # ----------------------------------------
732 # 1. leaf is an operator
733 # 2. leaf is a true/false leaf
734 # -> add directly to result
735 # ----------------------------------------
737 if leaf.is_operator():
738 results_to_stack.append(leaf)
739 elif leaf.is_true_leaf() or leaf.is_false_leaf():
740 results_to_stack.append(leaf)
742 # ----------------------------------------
744 # -> from inherits'd fields -> work on the related table, and add
746 # -> ('id', 'child_of', '..') -> use a 'to_ids'
747 # -> but is one on the _log_access special fields, add directly to
749 # TODO: make these fields explicitly available in self.columns instead!
751 # ----------------------------------------
753 elif not field and field_path[0] in working_table._inherit_fields:
754 # comments about inherits'd fields
755 # { 'field_name': ('parent_model', 'm2o_field_to_reach_parent',
756 # field_column_obj, origina_parent_model), ... }
757 next_table = working_table.pool.get(working_table._inherit_fields[field_path[0]][0])
758 leaf.add_join_context(working_table._inherits[next_table._name], working_table._inherits[next_table._name], next_table, 'id')
759 leafs_to_stack.append(leaf)
761 elif not field and left == 'id' and operator == 'child_of':
762 ids2 = self.to_ids(cr, uid, right, working_table, context)
763 dom = self.child_of_domain(cr, uid, left, ids2, working_table)
764 leafs_to_stack += [self.create_substitution_leaf(leaf, dom_leaf, working_table) for dom_leaf in dom]
766 elif not field and field_path[0] in MAGIC_COLUMNS:
767 results_to_stack.append(leaf)
770 raise ValueError("Invalid field %r in leaf %r" % (left, str(leaf)))
772 # ----------------------------------------
774 # -> many2one or one2many with _auto_join:
775 # - add a join, then jump into linked field: field.remaining on
776 # src_table is replaced by remaining on dst_table, and set for re-evaluation
777 # - if a domain is defined on the field, add it into evaluation
778 # on the relational table
779 # -> many2one, many2many, one2many: replace by an equivalent computed
780 # domain, given by recursively searching on the remaining of the path
781 # -> note: hack about fields.property should not be necessary anymore
782 # as after transforming the field, it will go through this loop once again
783 # ----------------------------------------
785 elif len(field_path) > 1 and field._type == 'many2one' and field._auto_join:
786 # res_partner.state_id = res_partner__state_id.id
787 leaf.add_join_context(field_path[0], field_path[0], relational_table, 'id')
788 leafs_to_stack.append(self.create_substitution_leaf(leaf, (field_path[1], operator, right), relational_table))
790 elif len(field_path) > 1 and field._type == 'one2many' and field._auto_join:
791 # res_partner.id = res_partner__bank_ids.partner_id
792 leaf.add_join_context(field_path[0], 'id', relational_table, field._fields_id)
793 domain = field._domain(working_table) if callable(field._domain) else field._domain
795 domain = normalize_domain(domain)
796 leafs_to_stack.append(self.create_substitution_leaf(leaf, AND_OPERATOR, relational_table))
798 leafs_to_stack.append(self.create_substitution_leaf(leaf, elem, relational_table))
799 print '--> appending %s' % str(leafs_to_stack[-1])
800 leafs_to_stack.append(self.create_substitution_leaf(leaf, (field_path[1], operator, right), relational_table))
802 elif len(field_path) > 1 and field._auto_join:
804 '_auto_join attribute on something else than a many2one or one2many is currently not supported'
806 elif len(field_path) > 1 and field._type == 'many2one':
807 right_ids = relational_table.search(cr, uid, [(field_path[1], operator, right)], context=context)
808 leaf.leaf = (field_path[0], 'in', right_ids)
809 leafs_to_stack.append(leaf)
811 # Making search easier when there is a left operand as field.o2m or field.m2m
812 elif len(field_path) > 1 and field._type in ['many2many', 'one2many']:
813 right_ids = relational_table.search(cr, uid, [(field_path[1], operator, right)], context=context)
814 table_ids = working_table.search(cr, uid, [(field_path[0], 'in', right_ids)], context=dict(context, active_test=False))
815 leaf.leaf = ('id', 'in', table_ids)
816 leafs_to_stack.append(leaf)
818 # -------------------------------------------------
820 # -> not stored: error if no _fnct_search, otherwise handle the result domain
821 # -> stored: management done in the remaining of parsing
822 # -------------------------------------------------
824 elif isinstance(field, fields.function) and not field.store and not field._fnct_search:
825 # this is a function field that is not stored
826 # the function field doesn't provide a search function and doesn't store
827 # values in the database, so we must ignore it : we generate a dummy leaf
828 leaf.leaf = TRUE_LEAF
830 "The field '%s' (%s) can not be searched: non-stored "
831 "function field without fnct_search",
833 # avoid compiling stack trace if not needed
834 if _logger.isEnabledFor(logging.DEBUG):
835 _logger.debug(''.join(traceback.format_stack()))
836 leafs_to_stack.append(leaf)
838 elif isinstance(field, fields.function) and not field.store:
839 # this is a function field that is not stored
840 fct_domain = field.search(cr, uid, working_table, left, [leaf.leaf], context=context)
842 leaf.leaf = TRUE_LEAF
843 leafs_to_stack.append(leaf)
845 # we assume that the expression is valid
846 # we create a dummy leaf for forcing the parsing of the resulting expression
847 leafs_to_stack.append(self.create_substitution_leaf(leaf, AND_OPERATOR, working_table))
848 leafs_to_stack.append(self.create_substitution_leaf(leaf, TRUE_LEAF, working_table))
849 for domain_element in fct_domain:
850 leafs_to_stack.append(self.create_substitution_leaf(leaf, domain_element, working_table))
852 # Applying recursivity on field(one2many)
853 elif field._type == 'one2many' and operator == 'child_of':
854 ids2 = self.to_ids(cr, uid, right, relational_table, context)
855 if field._obj != working_table._name:
856 dom = self.child_of_domain(cr, uid, left, ids2, relational_table, prefix=field._obj)
858 dom = self.child_of_domain(cr, uid, 'id', ids2, working_table, parent=left)
859 leafs_to_stack += [self.create_substitution_leaf(leaf, dom_leaf, working_table) for dom_leaf in dom]
861 elif field._type == 'one2many':
864 if right is not False:
865 if isinstance(right, basestring):
866 ids2 = [x[0] for x in relational_table.name_search(cr, uid, right, [], operator, context=context, limit=None)]
870 if not isinstance(right, list):
875 if operator in ['like', 'ilike', 'in', '=']:
876 #no result found with given search criteria
878 leafs_to_stack.append(self.create_substitution_leaf(leaf, FALSE_LEAF, working_table))
880 ids2 = select_from_where(cr, field._fields_id, relational_table._table, 'id', ids2, operator)
883 o2m_op = 'not in' if operator in NEGATIVE_TERM_OPERATORS else 'in'
884 leafs_to_stack.append(self.create_substitution_leaf(leaf, ('id', o2m_op, ids2), working_table))
887 o2m_op = 'in' if operator in NEGATIVE_TERM_OPERATORS else 'not in'
888 leafs_to_stack.append(self.create_substitution_leaf(leaf, ('id', o2m_op, select_distinct_from_where_not_null(cr, field._fields_id, relational_table._table)), working_table))
890 elif field._type == 'many2many':
891 rel_table, rel_id1, rel_id2 = field._sql_names(working_table)
893 if operator == 'child_of':
894 def _rec_convert(ids):
895 if relational_table == working_table:
897 return select_from_where(cr, rel_id1, rel_table, rel_id2, ids, operator)
899 ids2 = self.to_ids(cr, uid, right, relational_table, context)
900 dom = self.child_of_domain(cr, uid, 'id', ids2, relational_table)
901 ids2 = relational_table.search(cr, uid, dom, context=context)
902 leafs_to_stack.append(self.create_substitution_leaf(leaf, ('id', 'in', _rec_convert(ids2)), working_table))
905 if right is not False:
906 if isinstance(right, basestring):
907 res_ids = [x[0] for x in relational_table.name_search(cr, uid, right, [], operator, context=context)]
911 if not isinstance(right, list):
916 if operator in ['like', 'ilike', 'in', '=']:
917 #no result found with given search criteria
918 call_null_m2m = False
919 leafs_to_stack.append(self.create_substitution_leaf(leaf, FALSE_LEAF, working_table))
921 operator = 'in' # operator changed because ids are directly related to main object
923 call_null_m2m = False
924 m2m_op = 'not in' if operator in NEGATIVE_TERM_OPERATORS else 'in'
925 leafs_to_stack.append(self.create_substitution_leaf(leaf, ('id', m2m_op, select_from_where(cr, rel_id1, rel_table, rel_id2, res_ids, operator) or [0]), working_table))
928 m2m_op = 'in' if operator in NEGATIVE_TERM_OPERATORS else 'not in'
929 leafs_to_stack.append(self.create_substitution_leaf(leaf, ('id', m2m_op, select_distinct_from_where_not_null(cr, rel_id1, rel_table)), working_table))
931 elif field._type == 'many2one':
932 if operator == 'child_of':
933 ids2 = self.to_ids(cr, uid, right, relational_table, context)
934 if field._obj != working_table._name:
935 dom = self.child_of_domain(cr, uid, left, ids2, relational_table, prefix=field._obj)
937 dom = self.child_of_domain(cr, uid, 'id', ids2, working_table, parent=left)
938 leafs_to_stack += [self.create_substitution_leaf(leaf, dom_leaf, working_table) for dom_leaf in dom]
940 def _get_expression(relational_table, cr, uid, left, right, operator, context=None):
944 c['active_test'] = False
945 #Special treatment to ill-formed domains
946 operator = (operator in ['<', '>', '<=', '>=']) and 'in' or operator
948 dict_op = {'not in': '!=', 'in': '=', '=': 'in', '!=': 'not in'}
949 if isinstance(right, tuple):
951 if (not isinstance(right, list)) and operator in ['not in', 'in']:
952 operator = dict_op[operator]
953 elif isinstance(right, list) and operator in ['!=', '=']: # for domain (FIELD,'=',['value1','value2'])
954 operator = dict_op[operator]
955 res_ids = [x[0] for x in relational_table.name_search(cr, uid, right, [], operator, limit=None, context=c)]
956 if operator in NEGATIVE_TERM_OPERATORS:
957 res_ids.append(False) # TODO this should not be appended if False was in 'right'
958 return (left, 'in', res_ids)
959 # resolve string-based m2o criterion into IDs
960 if isinstance(right, basestring) or \
961 right and isinstance(right, (tuple, list)) and all(isinstance(item, basestring) for item in right):
962 leafs_to_stack.append(self.create_substitution_leaf(leaf, _get_expression(relational_table, cr, uid, left, right, operator, context=context), working_table))
964 # right == [] or right == False and all other cases are handled by __leaf_to_sql()
965 results_to_stack.append(leaf)
969 # add the time part to datetime field when it's not there:
970 if field._type == 'datetime' and right and len(right) == 10:
972 if operator in ('>', '>='):
974 elif operator in ('<', '<='):
977 leafs_to_stack.append(self.create_substitution_leaf(leaf, (left, operator, right), working_table))
979 elif field.translate:
980 need_wildcard = operator in ('like', 'ilike', 'not like', 'not ilike')
981 sql_operator = {'=like': 'like', '=ilike': 'ilike'}.get(operator, operator)
983 right = '%%%s%%' % right
985 subselect = '( SELECT res_id' \
986 ' FROM ir_translation' \
991 #Covering in,not in operators with operands (%s,%s) ,etc.
992 if sql_operator in ['in', 'not in']:
993 instr = ','.join(['%s'] * len(right))
994 subselect += ' AND value ' + sql_operator + ' ' + " (" + instr + ")" \
997 ' FROM "' + working_table._table + '"' \
998 ' WHERE "' + left + '" ' + sql_operator + ' ' + " (" + instr + "))"
1000 subselect += ' AND value ' + sql_operator + instr + \
1003 ' FROM "' + working_table._table + '"' \
1004 ' WHERE "' + left + '" ' + sql_operator + instr + ")"
1006 params = [working_table._name + ',' + left,
1007 context.get('lang', False) or 'en_US',
1012 leafs_to_stack.append(self.create_substitution_leaf(leaf, ('id', 'inselect', (subselect, params)), working_table))
1015 results_to_stack.append(leaf)
1017 # ----------------------------------------
1018 # END OF PROCESS OF CURRENT LEAF
1019 # -> results_to_stack elements are added in result
1020 # -> leafs_to_stack elements are inserted back in the processed
1021 # stack to be immediately processed
1022 # ----------------------------------------
1024 leafs_to_stack.reverse()
1025 for leaf in results_to_stack:
1027 for leaf in leafs_to_stack:
1028 stack.insert(0, leaf)
1030 # ----------------------------------------
1031 # END OF PARSING FULL DOMAIN
1032 # ----------------------------------------
1034 self.result = result
1038 for leaf in self.result:
1039 joins |= set(leaf.get_join_conditions())
1040 self.joins = list(joins)
1042 def __leaf_to_sql(self, eleaf):
1045 left, operator, right = leaf
1047 # final sanity checks - should never fail
1048 assert operator in (TERM_OPERATORS + ('inselect',)), \
1049 "Invalid operator %r in domain term %r" % (operator, leaf)
1050 assert leaf in (TRUE_LEAF, FALSE_LEAF) or left in table._all_columns \
1051 or left in MAGIC_COLUMNS, \
1052 "Invalid field %r in domain term %r" % (left, leaf)
1054 table_alias = '"%s"' % (eleaf._generate_alias())
1056 if leaf == TRUE_LEAF:
1060 elif leaf == FALSE_LEAF:
1064 elif operator == 'inselect':
1065 query = '(%s."%s" in (%s))' % (table_alias, left, right[0])
1068 elif operator in ['in', 'not in']:
1069 # Two cases: right is a boolean or a list. The boolean case is an
1070 # abuse and handled for backward compatibility.
1071 if isinstance(right, bool):
1072 _logger.warning("The domain term '%s' should use the '=' or '!=' operator." % (leaf,))
1073 if operator == 'in':
1074 r = 'NOT NULL' if right else 'NULL'
1076 r = 'NULL' if right else 'NOT NULL'
1077 query = '(%s."%s" IS %s)' % (table_alias, left, r)
1079 elif isinstance(right, (list, tuple)):
1082 for i in range(len(params))[::-1]:
1083 if params[i] == False:
1089 instr = ','.join(['%s'] * len(params))
1091 instr = ','.join([table._columns[left]._symbol_set[0]] * len(params))
1092 query = '(%s."%s" %s (%s))' % (table_alias, left, operator, instr)
1094 # The case for (left, 'in', []) or (left, 'not in', []).
1095 query = 'FALSE' if operator == 'in' else 'TRUE'
1097 if check_nulls and operator == 'in':
1098 query = '(%s OR %s."%s" IS NULL)' % (query, table_alias, left)
1099 elif not check_nulls and operator == 'not in':
1100 query = '(%s OR %s."%s" IS NULL)' % (query, table_alias, left)
1101 elif check_nulls and operator == 'not in':
1102 query = '(%s AND %s."%s" IS NOT NULL)' % (query, table_alias, left) # needed only for TRUE.
1103 else: # Must not happen
1104 raise ValueError("Invalid domain term %r" % (leaf,))
1106 elif right == False and (left in table._columns) and table._columns[left]._type == "boolean" and (operator == '='):
1107 query = '(%s."%s" IS NULL or %s."%s" = false )' % (table_alias, left, table_alias, left)
1110 elif (right is False or right is None) and (operator == '='):
1111 query = '%s."%s" IS NULL ' % (table_alias, left)
1114 elif right == False and (left in table._columns) and table._columns[left]._type == "boolean" and (operator == '!='):
1115 query = '(%s."%s" IS NOT NULL and %s."%s" != false)' % (table_alias, left, table_alias, left)
1118 elif (right is False or right is None) and (operator == '!='):
1119 query = '%s."%s" IS NOT NULL' % (table_alias, left)
1122 elif (operator == '=?'):
1123 if (right is False or right is None):
1124 # '=?' is a short-circuit that makes the term TRUE if right is None or False
1128 # '=?' behaves like '=' in other cases
1129 query, params = self.__leaf_to_sql((left, '=', right), table)
1132 query = '%s.id %s %%s' % (table_alias, operator)
1136 need_wildcard = operator in ('like', 'ilike', 'not like', 'not ilike')
1137 sql_operator = {'=like': 'like', '=ilike': 'ilike'}.get(operator, operator)
1139 if left in table._columns:
1140 format = need_wildcard and '%s' or table._columns[left]._symbol_set[0]
1141 if self.has_unaccent and sql_operator in ('ilike', 'not ilike'):
1142 query = '(unaccent(%s."%s") %s unaccent(%s))' % (table_alias, left, sql_operator, format)
1144 query = '(%s."%s" %s %s)' % (table_alias, left, sql_operator, format)
1145 elif left in MAGIC_COLUMNS:
1146 query = "(%s.\"%s\" %s %%s)" % (table_alias, left, sql_operator)
1148 else: # Must not happen
1149 raise ValueError("Invalid field %r in domain term %r" % (left, leaf))
1153 if isinstance(right, str):
1155 elif isinstance(right, unicode):
1156 str_utf8 = right.encode('utf-8')
1158 str_utf8 = str(right)
1159 params = '%%%s%%' % str_utf8
1160 add_null = not str_utf8
1161 elif left in table._columns:
1162 params = table._columns[left]._symbol_set[1](right)
1165 query = '(%s OR %s."%s" IS NULL)' % (query, table_alias, left)
1167 if isinstance(params, basestring):
1169 return (query, params)
1174 # Process the domain from right to left, using a stack, to generate a SQL expression.
1175 self.result.reverse()
1176 for leaf in self.result:
1177 if leaf.is_leaf(internal=True):
1178 q, p = self.__leaf_to_sql(leaf)
1181 elif leaf.leaf == NOT_OPERATOR:
1182 stack.append('(NOT (%s))' % (stack.pop(),))
1184 ops = {AND_OPERATOR: ' AND ', OR_OPERATOR: ' OR '}
1187 stack.append('(%s %s %s)' % (q1, ops[leaf.leaf], q2,))
1189 assert len(stack) == 1
1191 joins = ' AND '.join(self.joins)
1193 query = '(%s) AND %s' % (joins, query)
1195 return (query, tools.flatten(params))
1197 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: