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.
139 import openerp.modules
141 from ..models import MAGIC_COLUMNS, BaseModel
142 import openerp.tools as tools
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()]
433 def get_unaccent_wrapper(cr):
434 if openerp.modules.registry.RegistryManager.get(cr.dbname).has_unaccent:
435 return lambda x: "unaccent(%s)" % (x,)
438 # --------------------------------------------------
439 # ExtendedLeaf class for managing leafs and contexts
440 # -------------------------------------------------
442 class ExtendedLeaf(object):
443 """ Class wrapping a domain leaf, and giving some services and management
444 features on it. In particular it managed join contexts to be able to
445 construct queries through multiple models.
448 # --------------------------------------------------
449 # Join / Context manipulation
451 # - res_users.name, like, foo: name is on res_partner, not on res_users
452 # - res_partner.bank_ids.name, like, foo: bank_ids is a one2many with _auto_join
453 # - res_partner.state_id.name, like, foo: state_id is a many2one with _auto_join
455 # - link between src_table and dst_table, using src_field and dst_field
456 # i.e.: inherits: res_users.partner_id = res_partner.id
457 # i.e.: one2many: res_partner.id = res_partner_bank.partner_id
458 # i.e.: many2one: res_partner.state_id = res_country_state.id
459 # - done in the context of a field
460 # i.e.: inherits: 'partner_id'
461 # i.e.: one2many: 'bank_ids'
462 # i.e.: many2one: 'state_id'
463 # - table names use aliases: initial table followed by the context field
464 # names, joined using a '__'
465 # i.e.: inherits: res_partner as res_users__partner_id
466 # i.e.: one2many: res_partner_bank as res_partner__bank_ids
467 # i.e.: many2one: res_country_state as res_partner__state_id
468 # - join condition use aliases
469 # i.e.: inherits: res_users.partner_id = res_users__partner_id.id
470 # i.e.: one2many: res_partner.id = res_partner__bank_ids.parr_id
471 # i.e.: many2one: res_partner.state_id = res_partner__state_id.id
472 # Variables explanation:
473 # - src_table: working table before the join
474 # -> res_users, res_partner, res_partner
475 # - dst_table: working table after the join
476 # -> res_partner, res_partner_bank, res_country_state
477 # - src_table_link_name: field name used to link the src table, not
478 # necessarily a field (because 'id' is not a field instance)
479 # i.e.: inherits: 'partner_id', found in the inherits of the current table
480 # i.e.: one2many: 'id', not a field
481 # i.e.: many2one: 'state_id', the current field name
482 # - dst_table_link_name: field name used to link the dst table, not
483 # necessarily a field (because 'id' is not a field instance)
484 # i.e.: inherits: 'id', not a field
485 # i.e.: one2many: 'partner_id', _fields_id of the current field
486 # i.e.: many2one: 'id', not a field
487 # - context_field_name: field name used as a context to make the alias
488 # i.e.: inherits: 'partner_id': found in the inherits of the current table
489 # i.e.: one2many: 'bank_ids': current field name
490 # i.e.: many2one: 'state_id': current field name
491 # --------------------------------------------------
493 def __init__(self, leaf, model, join_context=None):
494 """ Initialize the ExtendedLeaf
496 :attr [string, tuple] leaf: operator or tuple-formatted domain
498 :attr obj model: current working model
499 :attr list _models: list of chained models, updated when
501 :attr list join_context: list of join contexts. This is a list of
502 tuples like ``(lhs, table, lhs_col, col, link)``
507 source (left hand) model
509 destination (right hand) model
511 source model column for join condition
513 destination model column for join condition
515 link column between source and destination model
516 that is not necessarily (but generally) a real column used
517 in the condition (i.e. in many2one); this link is used to
520 assert isinstance(model, BaseModel), 'Invalid leaf creation without table'
521 self.join_context = join_context or []
523 # normalize the leaf's operator
524 self.normalize_leaf()
525 # set working variables; handle the context stack and previous tables
528 for item in self.join_context:
529 self._models.append(item[0])
530 self._models.append(model)
535 return '<osv.ExtendedLeaf: %s on %s (ctx: %s)>' % (str(self.leaf), self.model._table, ','.join(self._get_context_debug()))
537 def generate_alias(self):
538 links = [(context[1]._table, context[4]) for context in self.join_context]
539 alias, alias_statement = generate_table_alias(self._models[0]._table, links)
542 def add_join_context(self, model, lhs_col, table_col, link):
543 """ See above comments for more details. A join context is a tuple like:
544 ``(lhs, model, lhs_col, col, link)``
546 After adding the join, the model of the current leaf is updated.
548 self.join_context.append((self.model, model, lhs_col, table_col, link))
549 self._models.append(model)
552 def get_join_conditions(self):
554 alias = self._models[0]._table
555 for context in self.join_context:
556 previous_alias = alias
557 alias += '__' + context[4]
558 conditions.append('"%s"."%s"="%s"."%s"' % (previous_alias, context[2], alias, context[3]))
561 def get_tables(self):
564 for context in self.join_context:
565 links.append((context[1]._table, context[4]))
566 alias, alias_statement = generate_table_alias(self._models[0]._table, links)
567 tables.add(alias_statement)
570 def _get_context_debug(self):
571 names = ['"%s"."%s"="%s"."%s" (%s)' % (item[0]._table, item[2], item[1]._table, item[3], item[4]) for item in self.join_context]
574 # --------------------------------------------------
576 # --------------------------------------------------
578 def check_leaf(self):
579 """ Leaf validity rules:
580 - a valid leaf is an operator or a leaf
581 - a valid leaf has a field objects unless
583 - it is an inherited field
584 - left is id, operator is 'child_of'
585 - left is in MAGIC_COLUMNS
587 if not is_operator(self.leaf) and not is_leaf(self.leaf, True):
588 raise ValueError("Invalid leaf %s" % str(self.leaf))
590 def is_operator(self):
591 return is_operator(self.leaf)
593 def is_true_leaf(self):
594 return self.leaf == TRUE_LEAF
596 def is_false_leaf(self):
597 return self.leaf == FALSE_LEAF
599 def is_leaf(self, internal=False):
600 return is_leaf(self.leaf, internal=internal)
602 def normalize_leaf(self):
603 self.leaf = normalize_leaf(self.leaf)
606 def create_substitution_leaf(leaf, new_elements, new_model=None):
607 """ From a leaf, create a new leaf (based on the new_elements tuple
608 and new_model), that will have the same join context. Used to
609 insert equivalent leafs in the processing stack. """
610 if new_model is None:
611 new_model = leaf.model
612 new_join_context = [tuple(context) for context in leaf.join_context]
613 new_leaf = ExtendedLeaf(new_elements, new_model, join_context=new_join_context)
616 class expression(object):
617 """ Parse a domain expression
618 Use a real polish notation
619 Leafs are still in a ('foo', '=', 'bar') format
620 For more info: http://christophe-simonis-at-tiny.blogspot.com/2008/08/new-new-domain-notation.html
623 def __init__(self, cr, uid, exp, table, context):
624 """ Initialize expression object and automatically parse the expression
625 right after initialization.
627 :param exp: expression (using domain ('foo', '=', 'bar' format))
628 :param table: root model
630 :attr list result: list that will hold the result of the parsing
631 as a list of ExtendedLeaf
632 :attr list joins: list of join conditions, such as
633 (res_country_state."id" = res_partner."state_id")
634 :attr root_model: base model for the query
635 :attr list expression: the domain expression, that will be normalized
638 self._unaccent = get_unaccent_wrapper(cr)
640 self.root_model = table
642 # normalize and prepare the expression for parsing
643 self.expression = distribute_not(normalize_domain(exp))
645 # parse the domain expression
646 self.parse(cr, uid, context=context)
648 # ----------------------------------------
650 # ----------------------------------------
652 def get_tables(self):
653 """ Returns the list of tables for SQL queries, like select from ... """
655 for leaf in self.result:
656 for table in leaf.get_tables():
657 if table not in tables:
659 table_name = _quote(self.root_model._table)
660 if table_name not in tables:
661 tables.append(table_name)
664 # ----------------------------------------
666 # ----------------------------------------
668 def parse(self, cr, uid, context):
669 """ Transform the leaves of the expression
671 The principle is to pop elements from a leaf stack one at a time.
672 Each leaf is processed. The processing is a if/elif list of various
673 cases that appear in the leafs (many2one, function fields, ...).
674 Two things can happen as a processing result:
675 - the leaf has been modified and/or new leafs have to be introduced
676 in the expression; they are pushed into the leaf stack, to be
677 processed right after
678 - the leaf is added to the result
680 Some internal var explanation:
681 :var list path: left operand seen as a sequence of field names
682 ("foo.bar" -> ["foo", "bar"])
683 :var obj model: model object, model containing the field
684 (the name provided in the left operand)
685 :var obj field: the field corresponding to `path[0]`
686 :var obj column: the column corresponding to `path[0]`
687 :var obj comodel: relational model of field (field.comodel)
688 (res_partner.bank_ids -> res.partner.bank)
691 def to_ids(value, comodel, context=None, limit=None):
692 """ Normalize a single id or name, or a list of those, into a list of ids
693 :param {int,long,basestring,list,tuple} value:
694 if int, long -> return [value]
695 if basestring, convert it into a list of basestrings, then
696 if list of basestring ->
697 perform a name_search on comodel for each name
698 return the list of related ids
701 if isinstance(value, basestring):
703 elif value and isinstance(value, (tuple, list)) and all(isinstance(item, basestring) for item in value):
705 elif isinstance(value, (int, long)):
708 name_get_list = [name_get[0] for name in names for name_get in comodel.name_search(cr, uid, name, [], 'ilike', context=context, limit=limit)]
709 return list(set(name_get_list))
712 def child_of_domain(left, ids, left_model, parent=None, prefix='', context=None):
713 """ Return a domain implementing the child_of operator for [(left,child_of,ids)],
714 either as a range using the parent_left/right tree lookup fields
715 (when available), or as an expanded [(left,in,child_ids)] """
716 if left_model._parent_store and (not left_model.pool._init):
717 # TODO: Improve where joins are implemented for many with '.', replace by:
718 # doms += ['&',(prefix+'.parent_left','<',o.parent_right),(prefix+'.parent_left','>=',o.parent_left)]
720 for o in left_model.browse(cr, uid, ids, context=context):
722 doms.insert(0, OR_OPERATOR)
723 doms += [AND_OPERATOR, ('parent_left', '<', o.parent_right), ('parent_left', '>=', o.parent_left)]
725 return [(left, 'in', left_model.search(cr, uid, doms, context=context))]
728 def recursive_children(ids, model, parent_field):
731 ids2 = model.search(cr, uid, [(parent_field, 'in', ids)], context=context)
732 return ids + recursive_children(ids2, model, parent_field)
733 return [(left, 'in', recursive_children(ids, left_model, parent or left_model._parent_name))]
736 """ Pop a leaf to process. """
737 return self.stack.pop()
740 """ Push a leaf to be processed right after. """
741 self.stack.append(leaf)
743 def push_result(leaf):
744 """ Push a leaf to the results. This leaf has been fully processed
746 self.result.append(leaf)
749 self.stack = [ExtendedLeaf(leaf, self.root_model) for leaf in self.expression]
750 # process from right to left; expression is from left to right
754 # Get the next leaf to process
757 # Get working variables
758 if leaf.is_operator():
759 left, operator, right = leaf.leaf, None, None
760 elif leaf.is_true_leaf() or leaf.is_false_leaf():
761 # because we consider left as a string
762 left, operator, right = ('%s' % leaf.leaf[0], leaf.leaf[1], leaf.leaf[2])
764 left, operator, right = leaf.leaf
765 path = left.split('.', 1)
768 field = model._fields.get(path[0])
769 column = model._columns.get(path[0])
770 comodel = model.pool.get(getattr(field, 'comodel_name', None))
772 # ----------------------------------------
774 # 1. leaf is an operator
775 # 2. leaf is a true/false leaf
776 # -> add directly to result
777 # ----------------------------------------
779 if leaf.is_operator() or leaf.is_true_leaf() or leaf.is_false_leaf():
782 # ----------------------------------------
784 # -> from inherits'd fields -> work on the related model, and add
786 # -> ('id', 'child_of', '..') -> use a 'to_ids'
787 # -> but is one on the _log_access special fields, add directly to
789 # TODO: make these fields explicitly available in self.columns instead!
791 # ----------------------------------------
793 elif not column and path[0] in model._inherit_fields:
794 # comments about inherits'd fields
795 # { 'field_name': ('parent_model', 'm2o_field_to_reach_parent',
796 # field_column_obj, origina_parent_model), ... }
797 next_model = model.pool[model._inherit_fields[path[0]][0]]
798 leaf.add_join_context(next_model, model._inherits[next_model._name], 'id', model._inherits[next_model._name])
801 elif left == 'id' and operator == 'child_of':
802 ids2 = to_ids(right, model, context)
803 dom = child_of_domain(left, ids2, model)
804 for dom_leaf in reversed(dom):
805 new_leaf = create_substitution_leaf(leaf, dom_leaf, model)
808 elif not column and path[0] in MAGIC_COLUMNS:
812 raise ValueError("Invalid field %r in leaf %r" % (left, str(leaf)))
814 # ----------------------------------------
816 # -> many2one or one2many with _auto_join:
817 # - add a join, then jump into linked column: column.remaining on
818 # src_table is replaced by remaining on dst_table, and set for re-evaluation
819 # - if a domain is defined on the column, add it into evaluation
820 # on the relational table
821 # -> many2one, many2many, one2many: replace by an equivalent computed
822 # domain, given by recursively searching on the remaining of the path
823 # -> note: hack about columns.property should not be necessary anymore
824 # as after transforming the column, it will go through this loop once again
825 # ----------------------------------------
827 elif len(path) > 1 and column._type == 'many2one' and column._auto_join:
828 # res_partner.state_id = res_partner__state_id.id
829 leaf.add_join_context(comodel, path[0], 'id', path[0])
830 push(create_substitution_leaf(leaf, (path[1], operator, right), comodel))
832 elif len(path) > 1 and column._type == 'one2many' and column._auto_join:
833 # res_partner.id = res_partner__bank_ids.partner_id
834 leaf.add_join_context(comodel, 'id', column._fields_id, path[0])
835 domain = column._domain(model) if callable(column._domain) else column._domain
836 push(create_substitution_leaf(leaf, (path[1], operator, right), comodel))
838 domain = normalize_domain(domain)
839 for elem in reversed(domain):
840 push(create_substitution_leaf(leaf, elem, comodel))
841 push(create_substitution_leaf(leaf, AND_OPERATOR, comodel))
843 elif len(path) > 1 and column._auto_join:
844 raise NotImplementedError('_auto_join attribute not supported on many2many column %s' % left)
846 elif len(path) > 1 and column._type == 'many2one':
847 right_ids = comodel.search(cr, uid, [(path[1], operator, right)], context=context)
848 leaf.leaf = (path[0], 'in', right_ids)
851 # Making search easier when there is a left operand as column.o2m or column.m2m
852 elif len(path) > 1 and column._type in ['many2many', 'one2many']:
853 right_ids = comodel.search(cr, uid, [(path[1], operator, right)], context=context)
854 table_ids = model.search(cr, uid, [(path[0], 'in', right_ids)], context=dict(context, active_test=False))
855 leaf.leaf = ('id', 'in', table_ids)
859 # Non-stored field should provide an implementation of search.
861 # field does not support search!
862 _logger.error("Non-stored field %s cannot be searched.", field)
863 if _logger.isEnabledFor(logging.DEBUG):
864 _logger.debug(''.join(traceback.format_stack()))
865 # Ignore it: generate a dummy leaf.
868 # Let the field generate a domain.
869 recs = model.browse(cr, uid, [], context)
870 domain = field.determine_domain(recs, operator, right)
873 leaf.leaf = TRUE_LEAF
876 for elem in reversed(domain):
877 push(create_substitution_leaf(leaf, elem, model))
879 # -------------------------------------------------
881 # -> not stored: error if no _fnct_search, otherwise handle the result domain
882 # -> stored: management done in the remaining of parsing
883 # -------------------------------------------------
885 elif isinstance(column, fields.function) and not column.store:
886 # this is a function field that is not stored
887 if not column._fnct_search:
889 "Field '%s' (%s) can not be searched: "
890 "non-stored function field without fnct_search",
892 # avoid compiling stack trace if not needed
893 if _logger.isEnabledFor(logging.DEBUG):
894 _logger.debug(''.join(traceback.format_stack()))
895 # ignore it: generate a dummy leaf
898 fct_domain = column.search(cr, uid, model, left, [leaf.leaf], context=context)
901 leaf.leaf = TRUE_LEAF
904 # we assume that the expression is valid
905 # we create a dummy leaf for forcing the parsing of the resulting expression
906 for domain_element in reversed(fct_domain):
907 push(create_substitution_leaf(leaf, domain_element, model))
908 # self.push(create_substitution_leaf(leaf, TRUE_LEAF, model))
909 # self.push(create_substitution_leaf(leaf, AND_OPERATOR, model))
911 # -------------------------------------------------
913 # -------------------------------------------------
915 # Applying recursivity on field(one2many)
916 elif column._type == 'one2many' and operator == 'child_of':
917 ids2 = to_ids(right, comodel, context)
918 if column._obj != model._name:
919 dom = child_of_domain(left, ids2, comodel, prefix=column._obj)
921 dom = child_of_domain('id', ids2, model, parent=left)
922 for dom_leaf in reversed(dom):
923 push(create_substitution_leaf(leaf, dom_leaf, model))
925 elif column._type == 'one2many':
928 if right is not False:
929 if isinstance(right, basestring):
930 ids2 = [x[0] for x in comodel.name_search(cr, uid, right, [], operator, context=context, limit=None)]
933 elif isinstance(right, collections.Iterable):
939 if operator in ['like', 'ilike', 'in', '=']:
940 #no result found with given search criteria
942 push(create_substitution_leaf(leaf, FALSE_LEAF, model))
944 ids2 = select_from_where(cr, column._fields_id, comodel._table, 'id', ids2, operator)
947 o2m_op = 'not in' if operator in NEGATIVE_TERM_OPERATORS else 'in'
948 push(create_substitution_leaf(leaf, ('id', o2m_op, ids2), model))
951 o2m_op = 'in' if operator in NEGATIVE_TERM_OPERATORS else 'not in'
952 push(create_substitution_leaf(leaf, ('id', o2m_op, select_distinct_from_where_not_null(cr, column._fields_id, comodel._table)), model))
954 elif column._type == 'many2many':
955 rel_table, rel_id1, rel_id2 = column._sql_names(model)
957 if operator == 'child_of':
958 def _rec_convert(ids):
961 return select_from_where(cr, rel_id1, rel_table, rel_id2, ids, operator)
963 ids2 = to_ids(right, comodel, context)
964 dom = child_of_domain('id', ids2, comodel)
965 ids2 = comodel.search(cr, uid, dom, context=context)
966 push(create_substitution_leaf(leaf, ('id', 'in', _rec_convert(ids2)), model))
969 if right is not False:
970 if isinstance(right, basestring):
971 res_ids = [x[0] for x in comodel.name_search(cr, uid, right, [], operator, context=context)]
975 if not isinstance(right, list):
980 if operator in ['like', 'ilike', 'in', '=']:
981 #no result found with given search criteria
982 call_null_m2m = False
983 push(create_substitution_leaf(leaf, FALSE_LEAF, model))
985 operator = 'in' # operator changed because ids are directly related to main object
987 call_null_m2m = False
988 m2m_op = 'not in' if operator in NEGATIVE_TERM_OPERATORS else 'in'
989 push(create_substitution_leaf(leaf, ('id', m2m_op, select_from_where(cr, rel_id1, rel_table, rel_id2, res_ids, operator) or [0]), model))
992 m2m_op = 'in' if operator in NEGATIVE_TERM_OPERATORS else 'not in'
993 push(create_substitution_leaf(leaf, ('id', m2m_op, select_distinct_from_where_not_null(cr, rel_id1, rel_table)), model))
995 elif column._type == 'many2one':
996 if operator == 'child_of':
997 ids2 = to_ids(right, comodel, context)
998 if column._obj != model._name:
999 dom = child_of_domain(left, ids2, comodel, prefix=column._obj)
1001 dom = child_of_domain('id', ids2, model, parent=left)
1002 for dom_leaf in reversed(dom):
1003 push(create_substitution_leaf(leaf, dom_leaf, model))
1005 def _get_expression(comodel, cr, uid, left, right, operator, context=None):
1009 c['active_test'] = False
1010 #Special treatment to ill-formed domains
1011 operator = (operator in ['<', '>', '<=', '>=']) and 'in' or operator
1013 dict_op = {'not in': '!=', 'in': '=', '=': 'in', '!=': 'not in'}
1014 if isinstance(right, tuple):
1016 if (not isinstance(right, list)) and operator in ['not in', 'in']:
1017 operator = dict_op[operator]
1018 elif isinstance(right, list) and operator in ['!=', '=']: # for domain (FIELD,'=',['value1','value2'])
1019 operator = dict_op[operator]
1020 res_ids = [x[0] for x in comodel.name_search(cr, uid, right, [], operator, limit=None, context=c)]
1021 if operator in NEGATIVE_TERM_OPERATORS:
1022 res_ids.append(False) # TODO this should not be appended if False was in 'right'
1023 return left, 'in', res_ids
1024 # resolve string-based m2o criterion into IDs
1025 if isinstance(right, basestring) or \
1026 right and isinstance(right, (tuple, list)) and all(isinstance(item, basestring) for item in right):
1027 push(create_substitution_leaf(leaf, _get_expression(comodel, cr, uid, left, right, operator, context=context), model))
1029 # right == [] or right == False and all other cases are handled by __leaf_to_sql()
1032 # -------------------------------------------------
1034 # -> datetime fields: manage time part of the datetime
1035 # column when it is not there
1036 # -> manage translatable fields
1037 # -------------------------------------------------
1040 if column._type == 'datetime' and right and len(right) == 10:
1041 if operator in ('>', '<='):
1042 right += ' 23:59:59'
1044 right += ' 00:00:00'
1045 push(create_substitution_leaf(leaf, (left, operator, right), model))
1047 elif column.translate and right:
1048 need_wildcard = operator in ('like', 'ilike', 'not like', 'not ilike')
1049 sql_operator = {'=like': 'like', '=ilike': 'ilike'}.get(operator, operator)
1051 right = '%%%s%%' % right
1053 inselect_operator = 'inselect'
1054 if sql_operator in NEGATIVE_TERM_OPERATORS:
1055 # negate operator (fix lp:1071710)
1056 sql_operator = sql_operator[4:] if sql_operator[:3] == 'not' else '='
1057 inselect_operator = 'not inselect'
1059 unaccent = self._unaccent if sql_operator.endswith('like') else lambda x: x
1061 instr = unaccent('%s')
1063 if sql_operator == 'in':
1064 # params will be flatten by to_sql() => expand the placeholders
1065 instr = '(%s)' % ', '.join(['%s'] * len(right))
1067 subselect = """WITH temp_irt_current (id, name) as (
1068 SELECT ct.id, coalesce(it.value,ct.{quote_left})
1069 FROM {current_table} ct
1070 LEFT JOIN ir_translation it ON (it.name = %s and
1073 it.res_id = ct.id and
1076 SELECT id FROM temp_irt_current WHERE {name} {operator} {right} order by name
1077 """.format(current_table=model._table, quote_left=_quote(left), name=unaccent('name'),
1078 operator=sql_operator, right=instr)
1081 model._name + ',' + left,
1082 context.get('lang') or 'en_US',
1086 push(create_substitution_leaf(leaf, ('id', inselect_operator, (subselect, params)), model))
1091 # ----------------------------------------
1092 # END OF PARSING FULL DOMAIN
1094 # ----------------------------------------
1097 for leaf in self.result:
1098 joins |= set(leaf.get_join_conditions())
1099 self.joins = list(joins)
1101 def __leaf_to_sql(self, eleaf):
1104 left, operator, right = leaf
1106 # final sanity checks - should never fail
1107 assert operator in (TERM_OPERATORS + ('inselect', 'not inselect')), \
1108 "Invalid operator %r in domain term %r" % (operator, leaf)
1109 assert leaf in (TRUE_LEAF, FALSE_LEAF) or left in model._fields \
1110 or left in MAGIC_COLUMNS, "Invalid field %r in domain term %r" % (left, leaf)
1111 assert not isinstance(right, BaseModel), \
1112 "Invalid value %r in domain term %r" % (right, leaf)
1114 table_alias = '"%s"' % (eleaf.generate_alias())
1116 if leaf == TRUE_LEAF:
1120 elif leaf == FALSE_LEAF:
1124 elif operator == 'inselect':
1125 query = '(%s."%s" in (%s))' % (table_alias, left, right[0])
1128 elif operator == 'not inselect':
1129 query = '(%s."%s" not in (%s))' % (table_alias, left, right[0])
1132 elif operator in ['in', 'not in']:
1133 # Two cases: right is a boolean or a list. The boolean case is an
1134 # abuse and handled for backward compatibility.
1135 if isinstance(right, bool):
1136 _logger.warning("The domain term '%s' should use the '=' or '!=' operator." % (leaf,))
1137 if operator == 'in':
1138 r = 'NOT NULL' if right else 'NULL'
1140 r = 'NULL' if right else 'NOT NULL'
1141 query = '(%s."%s" IS %s)' % (table_alias, left, r)
1143 elif isinstance(right, (list, tuple)):
1144 params = list(right)
1146 for i in range(len(params))[::-1]:
1147 if params[i] == False:
1153 instr = ','.join(['%s'] * len(params))
1155 ss = model._columns[left]._symbol_set
1156 instr = ','.join([ss[0]] * len(params))
1157 params = map(ss[1], params)
1158 query = '(%s."%s" %s (%s))' % (table_alias, left, operator, instr)
1160 # The case for (left, 'in', []) or (left, 'not in', []).
1161 query = 'FALSE' if operator == 'in' else 'TRUE'
1163 if check_nulls and operator == 'in':
1164 query = '(%s OR %s."%s" IS NULL)' % (query, table_alias, left)
1165 elif not check_nulls and operator == 'not in':
1166 query = '(%s OR %s."%s" IS NULL)' % (query, table_alias, left)
1167 elif check_nulls and operator == 'not in':
1168 query = '(%s AND %s."%s" IS NOT NULL)' % (query, table_alias, left) # needed only for TRUE.
1169 else: # Must not happen
1170 raise ValueError("Invalid domain term %r" % (leaf,))
1172 elif right == False and (left in model._columns) and model._columns[left]._type == "boolean" and (operator == '='):
1173 query = '(%s."%s" IS NULL or %s."%s" = false )' % (table_alias, left, table_alias, left)
1176 elif (right is False or right is None) and (operator == '='):
1177 query = '%s."%s" IS NULL ' % (table_alias, left)
1180 elif right == False and (left in model._columns) and model._columns[left]._type == "boolean" and (operator == '!='):
1181 query = '(%s."%s" IS NOT NULL and %s."%s" != false)' % (table_alias, left, table_alias, left)
1184 elif (right is False or right is None) and (operator == '!='):
1185 query = '%s."%s" IS NOT NULL' % (table_alias, left)
1188 elif operator == '=?':
1189 if right is False or right is None:
1190 # '=?' is a short-circuit that makes the term TRUE if right is None or False
1194 # '=?' behaves like '=' in other cases
1195 query, params = self.__leaf_to_sql(
1196 create_substitution_leaf(eleaf, (left, '=', right), model))
1199 query = '%s.id %s %%s' % (table_alias, operator)
1203 need_wildcard = operator in ('like', 'ilike', 'not like', 'not ilike')
1204 sql_operator = {'=like': 'like', '=ilike': 'ilike'}.get(operator, operator)
1205 cast = '::text' if sql_operator.endswith('like') else ''
1207 if left in model._columns:
1208 format = need_wildcard and '%s' or model._columns[left]._symbol_set[0]
1209 unaccent = self._unaccent if sql_operator.endswith('like') else lambda x: x
1210 column = '%s.%s' % (table_alias, _quote(left))
1211 query = '(%s%s %s %s)' % (unaccent(column), cast, sql_operator, unaccent(format))
1212 elif left in MAGIC_COLUMNS:
1213 query = "(%s.\"%s\"%s %s %%s)" % (table_alias, left, cast, sql_operator)
1215 else: # Must not happen
1216 raise ValueError("Invalid field %r in domain term %r" % (left, leaf))
1220 if isinstance(right, str):
1222 elif isinstance(right, unicode):
1223 str_utf8 = right.encode('utf-8')
1225 str_utf8 = str(right)
1226 params = '%%%s%%' % str_utf8
1227 add_null = not str_utf8
1228 elif left in model._columns:
1229 params = model._columns[left]._symbol_set[1](right)
1232 query = '(%s OR %s."%s" IS NULL)' % (query, table_alias, left)
1234 if isinstance(params, basestring):
1236 return query, params
1241 # Process the domain from right to left, using a stack, to generate a SQL expression.
1242 self.result.reverse()
1243 for leaf in self.result:
1244 if leaf.is_leaf(internal=True):
1245 q, p = self.__leaf_to_sql(leaf)
1248 elif leaf.leaf == NOT_OPERATOR:
1249 stack.append('(NOT (%s))' % (stack.pop(),))
1251 ops = {AND_OPERATOR: ' AND ', OR_OPERATOR: ' OR '}
1254 stack.append('(%s %s %s)' % (q1, ops[leaf.leaf], q2,))
1256 assert len(stack) == 1
1258 joins = ' AND '.join(self.joins)
1260 query = '(%s) AND %s' % (joins, query)
1262 return query, tools.flatten(params)
1264 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: