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
143 #.apidoc title: Domain Expressions
149 DOMAIN_OPERATORS = (NOT_OPERATOR, OR_OPERATOR, AND_OPERATOR)
151 # List of available term operators. It is also possible to use the '<>'
152 # operator, which is strictly the same as '!='; the later should be prefered
153 # for consistency. This list doesn't contain '<>' as it is simpified to '!='
154 # by the normalize_operator() function (so later part of the code deals with
155 # only one representation).
156 # Internals (i.e. not available to the user) 'inselect' and 'not inselect'
157 # operators are also used. In this case its right operand has the form (subselect, params).
158 TERM_OPERATORS = ('=', '!=', '<=', '<', '>', '>=', '=?', '=like', '=ilike',
159 'like', 'not like', 'ilike', 'not ilike', 'in', 'not in',
162 # A subset of the above operators, with a 'negative' semantic. When the
163 # expressions 'in NEGATIVE_TERM_OPERATORS' or 'not in NEGATIVE_TERM_OPERATORS' are used in the code
164 # below, this doesn't necessarily mean that any of those NEGATIVE_TERM_OPERATORS is
165 # legal in the processed term.
166 NEGATIVE_TERM_OPERATORS = ('!=', 'not like', 'not ilike', 'not in')
168 TRUE_LEAF = (1, '=', 1)
169 FALSE_LEAF = (0, '=', 1)
171 TRUE_DOMAIN = [TRUE_LEAF]
172 FALSE_DOMAIN = [FALSE_LEAF]
174 _logger = logging.getLogger(__name__)
177 # --------------------------------------------------
178 # Generic domain manipulation
179 # --------------------------------------------------
181 def normalize_domain(domain):
182 """Returns a normalized version of ``domain_expr``, where all implicit '&' operators
183 have been made explicit. One property of normalized domain expressions is that they
184 can be easily combined together as if they were single domain components.
186 assert isinstance(domain, (list, tuple)), "Domains to normalize must have a 'domain' form: a list or tuple of domain components"
190 expected = 1 # expected number of expressions
191 op_arity = {NOT_OPERATOR: 1, AND_OPERATOR: 2, OR_OPERATOR: 2}
193 if expected == 0: # more than expected, like in [A, B]
194 result[0:0] = [AND_OPERATOR] # put an extra '&' in front
197 if isinstance(token, (list, tuple)): # domain term
200 expected += op_arity.get(token, 0) - 1
201 assert expected == 0, 'This domain is syntactically not correct: %s' % (domain)
205 def combine(operator, unit, zero, domains):
206 """Returns a new domain expression where all domain components from ``domains``
207 have been added together using the binary operator ``operator``. The given
208 domains must be normalized.
210 :param unit: the identity element of the domains "set" with regard to the operation
211 performed by ``operator``, i.e the domain component ``i`` which, when
212 combined with any domain ``x`` via ``operator``, yields ``x``.
213 E.g. [(1,'=',1)] is the typical unit for AND_OPERATOR: adding it
214 to any domain component gives the same domain.
215 :param zero: the absorbing element of the domains "set" with regard to the operation
216 performed by ``operator``, i.e the domain component ``z`` which, when
217 combined with any domain ``x`` via ``operator``, yields ``z``.
218 E.g. [(1,'=',1)] is the typical zero for OR_OPERATOR: as soon as
219 you see it in a domain component the resulting domain is the zero.
220 :param domains: a list of normalized domains.
224 for domain in domains:
232 result = [operator] * (count - 1) + result
237 """AND([D1,D2,...]) returns a domain representing D1 and D2 and ... """
238 return combine(AND_OPERATOR, TRUE_DOMAIN, FALSE_DOMAIN, domains)
242 """OR([D1,D2,...]) returns a domain representing D1 or D2 or ... """
243 return combine(OR_OPERATOR, FALSE_DOMAIN, TRUE_DOMAIN, domains)
246 def distribute_not(domain):
247 """ Distribute any '!' domain operators found inside a normalized domain.
249 Because we don't use SQL semantic for processing a 'left not in right'
250 query (i.e. our 'not in' is not simply translated to a SQL 'not in'),
251 it means that a '! left in right' can not be simply processed
252 by __leaf_to_sql by first emitting code for 'left in right' then wrapping
253 the result with 'not (...)', as it would result in a 'not in' at the SQL
256 This function is thus responsible for pushing any '!' domain operators
257 inside the terms themselves. For example::
259 ['!','&',('user_id','=',4),('partner_id','in',[1,2])]
261 ['|',('user_id','!=',4),('partner_id','not in',[1,2])]
265 """Negates and returns a single domain leaf term,
266 using the opposite operator if possible"""
267 left, operator, right = leaf
276 if operator in ('in', 'like', 'ilike'):
277 operator = 'not ' + operator
278 return [(left, operator, right)]
279 if operator in ('not in', 'not like', 'not ilike'):
280 operator = operator[4:]
281 return [(left, operator, right)]
282 if operator in mapping:
283 operator = mapping[operator]
284 return [(left, operator, right)]
285 return [NOT_OPERATOR, (left, operator, right)]
287 def distribute_negate(domain):
288 """Negate the domain ``subtree`` rooted at domain[0],
289 leaving the rest of the domain intact, and return
290 (negated_subtree, untouched_domain_rest)
292 if is_leaf(domain[0]):
293 return negate(domain[0]), domain[1:]
294 if domain[0] == AND_OPERATOR:
295 done1, todo1 = distribute_negate(domain[1:])
296 done2, todo2 = distribute_negate(todo1)
297 return [OR_OPERATOR] + done1 + done2, todo2
298 if domain[0] == OR_OPERATOR:
299 done1, todo1 = distribute_negate(domain[1:])
300 done2, todo2 = distribute_negate(todo1)
301 return [AND_OPERATOR] + done1 + done2, todo2
304 if domain[0] != NOT_OPERATOR:
305 return [domain[0]] + distribute_not(domain[1:])
306 if domain[0] == NOT_OPERATOR:
307 done, todo = distribute_negate(domain[1:])
308 return done + distribute_not(todo)
311 # --------------------------------------------------
312 # Generic leaf manipulation
313 # --------------------------------------------------
315 def _quote(to_quote):
316 if '"' not in to_quote:
317 return '"%s"' % to_quote
321 def generate_table_alias(src_table_alias, joined_tables=[]):
322 """ Generate a standard table alias name. An alias is generated as following:
323 - the base is the source table name (that can already be an alias)
324 - then, each joined table is added in the alias using a 'link field name'
325 that is used to render unique aliases for a given path
326 - returns a tuple composed of the alias, and the full table alias to be
327 added in a from condition with quoting done
329 - src_table_alias='res_users', join_tables=[]:
330 alias = ('res_users','"res_users"')
331 - src_model='res_users', join_tables=[(res.partner, 'parent_id')]
332 alias = ('res_users__parent_id', '"res_partner" as "res_users__parent_id"')
334 :param model src_table_alias: model source of the alias
335 :param list joined_tables: list of tuples
336 (dst_model, link_field)
338 :return tuple: (table_alias, alias statement for from clause with quotes added)
340 alias = src_table_alias
341 if not joined_tables:
342 return '%s' % alias, '%s' % _quote(alias)
343 for link in joined_tables:
344 alias += '__' + link[1]
345 assert len(alias) < 64, 'Table alias name %s is longer than the 64 characters size accepted by default in postgresql.' % alias
346 return '%s' % alias, '%s as %s' % (_quote(joined_tables[-1][0]), _quote(alias))
349 def get_alias_from_query(from_query):
350 """ :param string from_query: is something like :
352 - '"res_partner" as "res_users__partner_id"''
354 from_splitted = from_query.split(' as ')
355 if len(from_splitted) > 1:
356 return from_splitted[0].replace('"', ''), from_splitted[1].replace('"', '')
358 return from_splitted[0].replace('"', ''), from_splitted[0].replace('"', '')
361 def normalize_leaf(element):
362 """ Change a term's operator to some canonical form, simplifying later
364 if not is_leaf(element):
366 left, operator, right = element
368 operator = operator.lower()
371 if isinstance(right, bool) and operator in ('in', 'not in'):
372 _logger.warning("The domain term '%s' should use the '=' or '!=' operator." % ((left, original, right),))
373 operator = '=' if operator == 'in' else '!='
374 if isinstance(right, (list, tuple)) and operator in ('=', '!='):
375 _logger.warning("The domain term '%s' should use the 'in' or 'not in' operator." % ((left, original, right),))
376 operator = 'in' if operator == '=' else 'not in'
377 return left, operator, right
380 def is_operator(element):
381 """ Test whether an object is a valid domain operator. """
382 return isinstance(element, basestring) and element in DOMAIN_OPERATORS
385 def is_leaf(element, internal=False):
386 """ Test whether an object is a valid domain term:
389 - second element if a valid op
391 :param tuple element: a leaf in form (left, operator, right)
392 :param boolean internal: allow or not the 'inselect' internal operator
393 in the term. This should be always left to False.
395 Note: OLD TODO change the share wizard to use this function.
397 INTERNAL_OPS = TERM_OPERATORS + ('<>',)
399 INTERNAL_OPS += ('inselect', 'not inselect')
400 return (isinstance(element, tuple) or isinstance(element, list)) \
401 and len(element) == 3 \
402 and element[1] in INTERNAL_OPS \
403 and ((isinstance(element[0], basestring) and element[0])
404 or element in (TRUE_LEAF, FALSE_LEAF))
407 # --------------------------------------------------
409 # --------------------------------------------------
411 def select_from_where(cr, select_field, from_table, where_field, where_ids, where_operator):
412 # todo: merge into parent query as sub-query
415 if where_operator in ['<', '>', '>=', '<=']:
416 cr.execute('SELECT "%s" FROM "%s" WHERE "%s" %s %%s' % \
417 (select_field, from_table, where_field, where_operator),
418 (where_ids[0],)) # TODO shouldn't this be min/max(where_ids) ?
419 res = [r[0] for r in cr.fetchall()]
420 else: # TODO where_operator is supposed to be 'in'? It is called with child_of...
421 for i in range(0, len(where_ids), cr.IN_MAX):
422 subids = where_ids[i:i + cr.IN_MAX]
423 cr.execute('SELECT "%s" FROM "%s" WHERE "%s" IN %%s' % \
424 (select_field, from_table, where_field), (tuple(subids),))
425 res.extend([r[0] for r in cr.fetchall()])
429 def select_distinct_from_where_not_null(cr, select_field, from_table):
430 cr.execute('SELECT distinct("%s") FROM "%s" where "%s" is not null' % (select_field, from_table, select_field))
431 return [r[0] for r in cr.fetchall()]
434 # --------------------------------------------------
435 # ExtendedLeaf class for managing leafs and contexts
436 # -------------------------------------------------
438 class ExtendedLeaf(object):
439 """ Class wrapping a domain leaf, and giving some services and management
440 features on it. In particular it managed join contexts to be able to
441 construct queries through multiple models.
444 # --------------------------------------------------
445 # Join / Context manipulation
447 # - res_users.name, like, foo: name is on res_partner, not on res_users
448 # - res_partner.bank_ids.name, like, foo: bank_ids is a one2many with _auto_join
449 # - res_partner.state_id.name, like, foo: state_id is a many2one with _auto_join
451 # - link between src_table and dst_table, using src_field and dst_field
452 # i.e.: inherits: res_users.partner_id = res_partner.id
453 # i.e.: one2many: res_partner.id = res_partner_bank.partner_id
454 # i.e.: many2one: res_partner.state_id = res_country_state.id
455 # - done in the context of a field
456 # i.e.: inherits: 'partner_id'
457 # i.e.: one2many: 'bank_ids'
458 # i.e.: many2one: 'state_id'
459 # - table names use aliases: initial table followed by the context field
460 # names, joined using a '__'
461 # i.e.: inherits: res_partner as res_users__partner_id
462 # i.e.: one2many: res_partner_bank as res_partner__bank_ids
463 # i.e.: many2one: res_country_state as res_partner__state_id
464 # - join condition use aliases
465 # i.e.: inherits: res_users.partner_id = res_users__partner_id.id
466 # i.e.: one2many: res_partner.id = res_partner__bank_ids.parr_id
467 # i.e.: many2one: res_partner.state_id = res_partner__state_id.id
468 # Variables explanation:
469 # - src_table: working table before the join
470 # -> res_users, res_partner, res_partner
471 # - dst_table: working table after the join
472 # -> res_partner, res_partner_bank, res_country_state
473 # - src_table_link_name: field name used to link the src table, not
474 # necessarily a field (because 'id' is not a field instance)
475 # i.e.: inherits: 'partner_id', found in the inherits of the current table
476 # i.e.: one2many: 'id', not a field
477 # i.e.: many2one: 'state_id', the current field name
478 # - dst_table_link_name: field name used to link the dst table, not
479 # necessarily a field (because 'id' is not a field instance)
480 # i.e.: inherits: 'id', not a field
481 # i.e.: one2many: 'partner_id', _fields_id of the current field
482 # i.e.: many2one: 'id', not a field
483 # - context_field_name: field name used as a context to make the alias
484 # i.e.: inherits: 'partner_id': found in the inherits of the current table
485 # i.e.: one2many: 'bank_ids': current field name
486 # i.e.: many2one: 'state_id': current field name
487 # --------------------------------------------------
489 def __init__(self, leaf, model, join_context=None):
490 """ Initialize the ExtendedLeaf
492 :attr [string, tuple] leaf: operator or tuple-formatted domain
494 :attr obj model: current working model
495 :attr list _models: list of chained models, updated when
497 :attr list join_context: list of join contexts. This is a list of
498 tuples like ``(lhs, table, lhs_col, col, link)``
503 source (left hand) model
505 destination (right hand) model
507 source model column for join condition
509 destination model column for join condition
511 link column between source and destination model
512 that is not necessarily (but generally) a real column used
513 in the condition (i.e. in many2one); this link is used to
516 assert model, 'Invalid leaf creation without table'
517 self.join_context = join_context or []
519 # normalize the leaf's operator
520 self.normalize_leaf()
521 # set working variables; handle the context stack and previous tables
524 for item in self.join_context:
525 self._models.append(item[0])
526 self._models.append(model)
531 return '<osv.ExtendedLeaf: %s on %s (ctx: %s)>' % (str(self.leaf), self.model._table, ','.join(self._get_context_debug()))
533 def generate_alias(self):
534 links = [(context[1]._table, context[4]) for context in self.join_context]
535 alias, alias_statement = generate_table_alias(self._models[0]._table, links)
538 def add_join_context(self, model, lhs_col, table_col, link):
539 """ See above comments for more details. A join context is a tuple like:
540 ``(lhs, model, lhs_col, col, link)``
542 After adding the join, the model of the current leaf is updated.
544 self.join_context.append((self.model, model, lhs_col, table_col, link))
545 self._models.append(model)
548 def get_join_conditions(self):
550 alias = self._models[0]._table
551 for context in self.join_context:
552 previous_alias = alias
553 alias += '__' + context[4]
554 conditions.append('"%s"."%s"="%s"."%s"' % (previous_alias, context[2], alias, context[3]))
557 def get_tables(self):
560 for context in self.join_context:
561 links.append((context[1]._table, context[4]))
562 alias, alias_statement = generate_table_alias(self._models[0]._table, links)
563 tables.add(alias_statement)
566 def _get_context_debug(self):
567 names = ['"%s"."%s"="%s"."%s" (%s)' % (item[0]._table, item[2], item[1]._table, item[3], item[4]) for item in self.join_context]
570 # --------------------------------------------------
572 # --------------------------------------------------
574 def check_leaf(self):
575 """ Leaf validity rules:
576 - a valid leaf is an operator or a leaf
577 - a valid leaf has a field objects unless
579 - it is an inherited field
580 - left is id, operator is 'child_of'
581 - left is in MAGIC_COLUMNS
583 if not is_operator(self.leaf) and not is_leaf(self.leaf, True):
584 raise ValueError("Invalid leaf %s" % str(self.leaf))
586 def is_operator(self):
587 return is_operator(self.leaf)
589 def is_true_leaf(self):
590 return self.leaf == TRUE_LEAF
592 def is_false_leaf(self):
593 return self.leaf == FALSE_LEAF
595 def is_leaf(self, internal=False):
596 return is_leaf(self.leaf, internal=internal)
598 def normalize_leaf(self):
599 self.leaf = normalize_leaf(self.leaf)
602 def create_substitution_leaf(leaf, new_elements, new_model=None):
603 """ From a leaf, create a new leaf (based on the new_elements tuple
604 and new_model), that will have the same join context. Used to
605 insert equivalent leafs in the processing stack. """
606 if new_model is None:
607 new_model = leaf.model
608 new_join_context = [tuple(context) for context in leaf.join_context]
609 new_leaf = ExtendedLeaf(new_elements, new_model, join_context=new_join_context)
612 class expression(object):
613 """ Parse a domain expression
614 Use a real polish notation
615 Leafs are still in a ('foo', '=', 'bar') format
616 For more info: http://christophe-simonis-at-tiny.blogspot.com/2008/08/new-new-domain-notation.html
619 def __init__(self, cr, uid, exp, table, context):
620 """ Initialize expression object and automatically parse the expression
621 right after initialization.
623 :param exp: expression (using domain ('foo', '=', 'bar' format))
624 :param table: root model
626 :attr list result: list that will hold the result of the parsing
627 as a list of ExtendedLeaf
628 :attr list joins: list of join conditions, such as
629 (res_country_state."id" = res_partner."state_id")
630 :attr root_model: base model for the query
631 :attr list expression: the domain expression, that will be normalized
634 self.has_unaccent = openerp.modules.registry.RegistryManager.get(cr.dbname).has_unaccent
636 self.root_model = table
638 # normalize and prepare the expression for parsing
639 self.expression = distribute_not(normalize_domain(exp))
641 # parse the domain expression
642 self.parse(cr, uid, context=context)
644 # ----------------------------------------
646 # ----------------------------------------
648 def get_tables(self):
649 """ Returns the list of tables for SQL queries, like select from ... """
651 for leaf in self.result:
652 for table in leaf.get_tables():
653 if table not in tables:
655 table_name = _quote(self.root_model._table)
656 if table_name not in tables:
657 tables.append(table_name)
660 # ----------------------------------------
662 # ----------------------------------------
664 def parse(self, cr, uid, context):
665 """ Transform the leaves of the expression
667 The principle is to pop elements from a leaf stack one at a time.
668 Each leaf is processed. The processing is a if/elif list of various
669 cases that appear in the leafs (many2one, function fields, ...).
670 Two things can happen as a processing result:
671 - the leaf has been modified and/or new leafs have to be introduced
672 in the expression; they are pushed into the leaf stack, to be
673 processed right after
674 - the leaf is added to the result
676 Some internal var explanation:
677 :var obj working_model: model object, model containing the field
678 (the name provided in the left operand)
679 :var list field_path: left operand seen as a path (foo.bar -> [foo, bar])
680 :var obj relational_model: relational model of a field (field._obj)
681 ex: res_partner.bank_ids -> res.partner.bank
684 def to_ids(value, relational_model, context=None, limit=None):
685 """ Normalize a single id or name, or a list of those, into a list of ids
686 :param {int,long,basestring,list,tuple} value:
687 if int, long -> return [value]
688 if basestring, convert it into a list of basestrings, then
689 if list of basestring ->
690 perform a name_search on relational_model for each name
691 return the list of related ids
694 if isinstance(value, basestring):
696 elif value and isinstance(value, (tuple, list)) and all(isinstance(item, basestring) for item in value):
698 elif isinstance(value, (int, long)):
701 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)]
702 return list(set(name_get_list))
705 def child_of_domain(left, ids, left_model, parent=None, prefix='', context=None):
706 """ Return a domain implementing the child_of operator for [(left,child_of,ids)],
707 either as a range using the parent_left/right tree lookup fields
708 (when available), or as an expanded [(left,in,child_ids)] """
709 if left_model._parent_store and (not left_model.pool._init):
710 # TODO: Improve where joins are implemented for many with '.', replace by:
711 # doms += ['&',(prefix+'.parent_left','<',o.parent_right),(prefix+'.parent_left','>=',o.parent_left)]
713 for o in left_model.browse(cr, uid, ids, context=context):
715 doms.insert(0, OR_OPERATOR)
716 doms += [AND_OPERATOR, ('parent_left', '<', o.parent_right), ('parent_left', '>=', o.parent_left)]
718 return [(left, 'in', left_model.search(cr, uid, doms, context=context))]
721 def recursive_children(ids, model, parent_field):
724 ids2 = model.search(cr, uid, [(parent_field, 'in', ids)], context=context)
725 return ids + recursive_children(ids2, model, parent_field)
726 return [(left, 'in', recursive_children(ids, left_model, parent or left_model._parent_name))]
729 """ Pop a leaf to process. """
730 return self.stack.pop()
733 """ Push a leaf to be processed right after. """
734 self.stack.append(leaf)
736 def push_result(leaf):
737 """ Push a leaf to the results. This leaf has been fully processed
739 self.result.append(leaf)
742 self.stack = [ExtendedLeaf(leaf, self.root_model) for leaf in self.expression]
743 # process from right to left; expression is from left to right
747 # Get the next leaf to process
750 # Get working variables
751 working_model = leaf.model
752 if leaf.is_operator():
753 left, operator, right = leaf.leaf, None, None
754 elif leaf.is_true_leaf() or leaf.is_false_leaf():
755 # because we consider left as a string
756 left, operator, right = ('%s' % leaf.leaf[0], leaf.leaf[1], leaf.leaf[2])
758 left, operator, right = leaf.leaf
759 field_path = left.split('.', 1)
760 field = working_model._columns.get(field_path[0])
761 if field and field._obj:
762 relational_model = working_model.pool.get(field._obj)
764 relational_model = None
766 # ----------------------------------------
768 # 1. leaf is an operator
769 # 2. leaf is a true/false leaf
770 # -> add directly to result
771 # ----------------------------------------
773 if leaf.is_operator() or leaf.is_true_leaf() or leaf.is_false_leaf():
776 # ----------------------------------------
778 # -> from inherits'd fields -> work on the related model, and add
780 # -> ('id', 'child_of', '..') -> use a 'to_ids'
781 # -> but is one on the _log_access special fields, add directly to
783 # TODO: make these fields explicitly available in self.columns instead!
785 # ----------------------------------------
787 elif not field and field_path[0] in working_model._inherit_fields:
788 # comments about inherits'd fields
789 # { 'field_name': ('parent_model', 'm2o_field_to_reach_parent',
790 # field_column_obj, origina_parent_model), ... }
791 next_model = working_model.pool.get(working_model._inherit_fields[field_path[0]][0])
792 leaf.add_join_context(next_model, working_model._inherits[next_model._name], 'id', working_model._inherits[next_model._name])
795 elif left == 'id' and operator == 'child_of':
796 ids2 = to_ids(right, working_model, context)
797 dom = child_of_domain(left, ids2, working_model)
798 for dom_leaf in reversed(dom):
799 new_leaf = create_substitution_leaf(leaf, dom_leaf, working_model)
802 elif not field and field_path[0] in MAGIC_COLUMNS:
806 raise ValueError("Invalid field %r in leaf %r" % (left, str(leaf)))
808 # ----------------------------------------
810 # -> many2one or one2many with _auto_join:
811 # - add a join, then jump into linked field: field.remaining on
812 # src_table is replaced by remaining on dst_table, and set for re-evaluation
813 # - if a domain is defined on the field, add it into evaluation
814 # on the relational table
815 # -> many2one, many2many, one2many: replace by an equivalent computed
816 # domain, given by recursively searching on the remaining of the path
817 # -> note: hack about fields.property should not be necessary anymore
818 # as after transforming the field, it will go through this loop once again
819 # ----------------------------------------
821 elif len(field_path) > 1 and field._type == 'many2one' and field._auto_join:
822 # res_partner.state_id = res_partner__state_id.id
823 leaf.add_join_context(relational_model, field_path[0], 'id', field_path[0])
824 push(create_substitution_leaf(leaf, (field_path[1], operator, right), relational_model))
826 elif len(field_path) > 1 and field._type == 'one2many' and field._auto_join:
827 # res_partner.id = res_partner__bank_ids.partner_id
828 leaf.add_join_context(relational_model, 'id', field._fields_id, field_path[0])
829 domain = field._domain(working_model) if callable(field._domain) else field._domain
830 push(create_substitution_leaf(leaf, (field_path[1], operator, right), relational_model))
832 domain = normalize_domain(domain)
833 for elem in reversed(domain):
834 push(create_substitution_leaf(leaf, elem, relational_model))
835 push(create_substitution_leaf(leaf, AND_OPERATOR, relational_model))
837 elif len(field_path) > 1 and field._auto_join:
838 raise NotImplementedError('_auto_join attribute not supported on many2many field %s' % left)
840 elif len(field_path) > 1 and field._type == 'many2one':
841 right_ids = relational_model.search(cr, uid, [(field_path[1], operator, right)], context=context)
842 leaf.leaf = (field_path[0], 'in', right_ids)
845 # Making search easier when there is a left operand as field.o2m or field.m2m
846 elif len(field_path) > 1 and field._type in ['many2many', 'one2many']:
847 right_ids = relational_model.search(cr, uid, [(field_path[1], operator, right)], context=context)
848 table_ids = working_model.search(cr, uid, [(field_path[0], 'in', right_ids)], context=dict(context, active_test=False))
849 leaf.leaf = ('id', 'in', table_ids)
852 # -------------------------------------------------
854 # -> not stored: error if no _fnct_search, otherwise handle the result domain
855 # -> stored: management done in the remaining of parsing
856 # -------------------------------------------------
858 elif isinstance(field, fields.function) and not field.store and not field._fnct_search:
859 # this is a function field that is not stored
860 # the function field doesn't provide a search function and doesn't store
861 # values in the database, so we must ignore it : we generate a dummy leaf
862 leaf.leaf = TRUE_LEAF
864 "The field '%s' (%s) can not be searched: non-stored "
865 "function field without fnct_search",
867 # avoid compiling stack trace if not needed
868 if _logger.isEnabledFor(logging.DEBUG):
869 _logger.debug(''.join(traceback.format_stack()))
872 elif isinstance(field, fields.function) and not field.store:
873 # this is a function field that is not stored
874 fct_domain = field.search(cr, uid, working_model, left, [leaf.leaf], context=context)
876 leaf.leaf = TRUE_LEAF
879 # we assume that the expression is valid
880 # we create a dummy leaf for forcing the parsing of the resulting expression
881 for domain_element in reversed(fct_domain):
882 push(create_substitution_leaf(leaf, domain_element, working_model))
883 # self.push(create_substitution_leaf(leaf, TRUE_LEAF, working_model))
884 # self.push(create_substitution_leaf(leaf, AND_OPERATOR, working_model))
886 # -------------------------------------------------
888 # -------------------------------------------------
890 # Applying recursivity on field(one2many)
891 elif field._type == 'one2many' and operator == 'child_of':
892 ids2 = to_ids(right, relational_model, context)
893 if field._obj != working_model._name:
894 dom = child_of_domain(left, ids2, relational_model, prefix=field._obj)
896 dom = child_of_domain('id', ids2, working_model, parent=left)
897 for dom_leaf in reversed(dom):
898 push(create_substitution_leaf(leaf, dom_leaf, working_model))
900 elif field._type == 'one2many':
903 if right is not False:
904 if isinstance(right, basestring):
905 ids2 = [x[0] for x in relational_model.name_search(cr, uid, right, [], operator, context=context, limit=None)]
909 if not isinstance(right, list):
914 if operator in ['like', 'ilike', 'in', '=']:
915 #no result found with given search criteria
917 push(create_substitution_leaf(leaf, FALSE_LEAF, working_model))
919 ids2 = select_from_where(cr, field._fields_id, relational_model._table, 'id', ids2, operator)
922 o2m_op = 'not in' if operator in NEGATIVE_TERM_OPERATORS else 'in'
923 push(create_substitution_leaf(leaf, ('id', o2m_op, ids2), working_model))
926 o2m_op = 'in' if operator in NEGATIVE_TERM_OPERATORS else 'not in'
927 push(create_substitution_leaf(leaf, ('id', o2m_op, select_distinct_from_where_not_null(cr, field._fields_id, relational_model._table)), working_model))
929 elif field._type == 'many2many':
930 rel_table, rel_id1, rel_id2 = field._sql_names(working_model)
932 if operator == 'child_of':
933 def _rec_convert(ids):
934 if relational_model == working_model:
936 return select_from_where(cr, rel_id1, rel_table, rel_id2, ids, operator)
938 ids2 = to_ids(right, relational_model, context)
939 dom = child_of_domain('id', ids2, relational_model)
940 ids2 = relational_model.search(cr, uid, dom, context=context)
941 push(create_substitution_leaf(leaf, ('id', 'in', _rec_convert(ids2)), working_model))
944 if right is not False:
945 if isinstance(right, basestring):
946 res_ids = [x[0] for x in relational_model.name_search(cr, uid, right, [], operator, context=context)]
950 if not isinstance(right, list):
955 if operator in ['like', 'ilike', 'in', '=']:
956 #no result found with given search criteria
957 call_null_m2m = False
958 push(create_substitution_leaf(leaf, FALSE_LEAF, working_model))
960 operator = 'in' # operator changed because ids are directly related to main object
962 call_null_m2m = False
963 m2m_op = 'not in' if operator in NEGATIVE_TERM_OPERATORS else 'in'
964 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))
967 m2m_op = 'in' if operator in NEGATIVE_TERM_OPERATORS else 'not in'
968 push(create_substitution_leaf(leaf, ('id', m2m_op, select_distinct_from_where_not_null(cr, rel_id1, rel_table)), working_model))
970 elif field._type == 'many2one':
971 if operator == 'child_of':
972 ids2 = to_ids(right, relational_model, context)
973 if field._obj != working_model._name:
974 dom = child_of_domain(left, ids2, relational_model, prefix=field._obj)
976 dom = child_of_domain('id', ids2, working_model, parent=left)
977 for dom_leaf in reversed(dom):
978 push(create_substitution_leaf(leaf, dom_leaf, working_model))
980 def _get_expression(relational_model, cr, uid, left, right, operator, context=None):
984 c['active_test'] = False
985 #Special treatment to ill-formed domains
986 operator = (operator in ['<', '>', '<=', '>=']) and 'in' or operator
988 dict_op = {'not in': '!=', 'in': '=', '=': 'in', '!=': 'not in'}
989 if isinstance(right, tuple):
991 if (not isinstance(right, list)) and operator in ['not in', 'in']:
992 operator = dict_op[operator]
993 elif isinstance(right, list) and operator in ['!=', '=']: # for domain (FIELD,'=',['value1','value2'])
994 operator = dict_op[operator]
995 res_ids = [x[0] for x in relational_model.name_search(cr, uid, right, [], operator, limit=None, context=c)]
996 if operator in NEGATIVE_TERM_OPERATORS:
997 res_ids.append(False) # TODO this should not be appended if False was in 'right'
998 return left, 'in', res_ids
999 # resolve string-based m2o criterion into IDs
1000 if isinstance(right, basestring) or \
1001 right and isinstance(right, (tuple, list)) and all(isinstance(item, basestring) for item in right):
1002 push(create_substitution_leaf(leaf, _get_expression(relational_model, cr, uid, left, right, operator, context=context), working_model))
1004 # right == [] or right == False and all other cases are handled by __leaf_to_sql()
1007 # -------------------------------------------------
1009 # -> datetime fields: manage time part of the datetime
1010 # field when it is not there
1011 # -> manage translatable fields
1012 # -------------------------------------------------
1015 if field._type == 'datetime' and right and len(right) == 10:
1016 if operator in ('>', '>='):
1017 right += ' 00:00:00'
1018 elif operator in ('<', '<='):
1019 right += ' 23:59:59'
1020 push(create_substitution_leaf(leaf, (left, operator, right), working_model))
1022 elif field.translate and right:
1024 need_wildcard = operator in ('like', 'ilike', 'not like', 'not ilike')
1025 sql_operator = {'=like': 'like', '=ilike': 'ilike'}.get(operator, operator)
1027 right = '%%%s%%' % right
1029 inselect_operator = 'inselect'
1030 if sql_operator in NEGATIVE_TERM_OPERATORS:
1031 # negate operator (fix lp:1071710)
1032 sql_operator = sql_operator[4:] if sql_operator[:3] == 'not' else '='
1033 inselect_operator = 'not inselect'
1035 if sql_operator == 'in':
1036 right = tuple(right)
1038 if self.has_unaccent and sql_operator.endswith('like'):
1039 trans_left = 'unaccent(value)'
1040 left = 'unaccent("%s")' % (left,)
1041 instr = 'unaccent(%s)'
1043 trans_left = 'value'
1044 left = '"%s"' % (left,)
1047 subselect = """(SELECT res_id
1052 AND {trans_left} {operator} {right}
1056 WHERE {left} {operator} {right}
1058 """.format(trans_left=trans_left, operator=sql_operator,
1059 right=instr, table=working_model._table, left=left)
1061 params = [working_model._name + ',' + field,
1062 context.get('lang', False) or 'en_US',
1067 push(create_substitution_leaf(leaf, ('id', inselect_operator, (subselect, params)), working_model))
1072 # ----------------------------------------
1073 # END OF PARSING FULL DOMAIN
1075 # ----------------------------------------
1078 for leaf in self.result:
1079 joins |= set(leaf.get_join_conditions())
1080 self.joins = list(joins)
1082 def __leaf_to_sql(self, eleaf):
1085 left, operator, right = leaf
1087 # final sanity checks - should never fail
1088 assert operator in (TERM_OPERATORS + ('inselect', 'not inselect')), \
1089 "Invalid operator %r in domain term %r" % (operator, leaf)
1090 assert leaf in (TRUE_LEAF, FALSE_LEAF) or left in model._all_columns \
1091 or left in MAGIC_COLUMNS, "Invalid field %r in domain term %r" % (left, leaf)
1093 table_alias = '"%s"' % (eleaf.generate_alias())
1095 if leaf == TRUE_LEAF:
1099 elif leaf == FALSE_LEAF:
1103 elif operator == 'inselect':
1104 query = '(%s."%s" in (%s))' % (table_alias, left, right[0])
1107 elif operator == 'not inselect':
1108 query = '(%s."%s" not in (%s))' % (table_alias, left, right[0])
1111 elif operator in ['in', 'not in']:
1112 # Two cases: right is a boolean or a list. The boolean case is an
1113 # abuse and handled for backward compatibility.
1114 if isinstance(right, bool):
1115 _logger.warning("The domain term '%s' should use the '=' or '!=' operator." % (leaf,))
1116 if operator == 'in':
1117 r = 'NOT NULL' if right else 'NULL'
1119 r = 'NULL' if right else 'NOT NULL'
1120 query = '(%s."%s" IS %s)' % (table_alias, left, r)
1122 elif isinstance(right, (list, tuple)):
1123 params = list(right)
1125 for i in range(len(params))[::-1]:
1126 if params[i] == False:
1132 instr = ','.join(['%s'] * len(params))
1134 instr = ','.join([model._columns[left]._symbol_set[0]] * len(params))
1135 query = '(%s."%s" %s (%s))' % (table_alias, left, operator, instr)
1137 # The case for (left, 'in', []) or (left, 'not in', []).
1138 query = 'FALSE' if operator == 'in' else 'TRUE'
1140 if check_nulls and operator == 'in':
1141 query = '(%s OR %s."%s" IS NULL)' % (query, table_alias, left)
1142 elif not check_nulls and operator == 'not in':
1143 query = '(%s OR %s."%s" IS NULL)' % (query, table_alias, left)
1144 elif check_nulls and operator == 'not in':
1145 query = '(%s AND %s."%s" IS NOT NULL)' % (query, table_alias, left) # needed only for TRUE.
1146 else: # Must not happen
1147 raise ValueError("Invalid domain term %r" % (leaf,))
1149 elif right == False and (left in model._columns) and model._columns[left]._type == "boolean" and (operator == '='):
1150 query = '(%s."%s" IS NULL or %s."%s" = false )' % (table_alias, left, table_alias, left)
1153 elif (right is False or right is None) and (operator == '='):
1154 query = '%s."%s" IS NULL ' % (table_alias, left)
1157 elif right == False and (left in model._columns) and model._columns[left]._type == "boolean" and (operator == '!='):
1158 query = '(%s."%s" IS NOT NULL and %s."%s" != false)' % (table_alias, left, table_alias, left)
1161 elif (right is False or right is None) and (operator == '!='):
1162 query = '%s."%s" IS NOT NULL' % (table_alias, left)
1165 elif operator == '=?':
1166 if right is False or right is None:
1167 # '=?' is a short-circuit that makes the term TRUE if right is None or False
1171 # '=?' behaves like '=' in other cases
1172 query, params = self.__leaf_to_sql(
1173 create_substitution_leaf(eleaf, (left, '=', right), model))
1176 query = '%s.id %s %%s' % (table_alias, operator)
1180 need_wildcard = operator in ('like', 'ilike', 'not like', 'not ilike')
1181 sql_operator = {'=like': 'like', '=ilike': 'ilike'}.get(operator, operator)
1183 if left in model._columns:
1184 format = need_wildcard and '%s' or model._columns[left]._symbol_set[0]
1185 if self.has_unaccent and sql_operator.endswith('like'):
1186 query = '(unaccent(%s."%s") %s unaccent(%s))' % (table_alias, left, sql_operator, format)
1188 query = '(%s."%s" %s %s)' % (table_alias, left, sql_operator, format)
1189 elif left in MAGIC_COLUMNS:
1190 query = "(%s.\"%s\" %s %%s)" % (table_alias, left, sql_operator)
1192 else: # Must not happen
1193 raise ValueError("Invalid field %r in domain term %r" % (left, leaf))
1197 if isinstance(right, str):
1199 elif isinstance(right, unicode):
1200 str_utf8 = right.encode('utf-8')
1202 str_utf8 = str(right)
1203 params = '%%%s%%' % str_utf8
1204 add_null = not str_utf8
1205 elif left in model._columns:
1206 params = model._columns[left]._symbol_set[1](right)
1209 query = '(%s OR %s."%s" IS NULL)' % (query, table_alias, left)
1211 if isinstance(params, basestring):
1213 return query, params
1218 # Process the domain from right to left, using a stack, to generate a SQL expression.
1219 self.result.reverse()
1220 for leaf in self.result:
1221 if leaf.is_leaf(internal=True):
1222 q, p = self.__leaf_to_sql(leaf)
1225 elif leaf.leaf == NOT_OPERATOR:
1226 stack.append('(NOT (%s))' % (stack.pop(),))
1228 ops = {AND_OPERATOR: ' AND ', OR_OPERATOR: ' OR '}
1231 stack.append('(%s %s %s)' % (q1, ops[leaf.leaf], q2,))
1233 assert len(stack) == 1
1235 joins = ' AND '.join(self.joins)
1237 query = '(%s) AND %s' % (joins, query)
1239 return query, tools.flatten(params)
1241 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: