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 SQL
26 query. A lot of things should be documented here, but as a first step in the
27 right direction, some tests in test_osv_expression.yml might give you some
28 additional information.
30 For legacy reasons, a domain uses an inconsistent two-levels abstract syntax
31 (domains are regular Python data structures). At the first level, a domain
32 is an expression made of terms (sometimes called leaves) and (domain) operators
33 used in prefix notation. The available operators at this level are '!', '&',
34 and '|'. '!' is a unary 'not', '&' is a binary 'and', and '|' is a binary 'or'.
35 For instance, here is a possible domain. (<term> stands for an arbitrary term,
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 is
45 a triple of the form (left, operator, right). That is, a term uses an infix
46 notation, and the available operators, and possible left and right operands
47 differ with those of the previous level. Here is a possible term:
49 ('company_id.name', '=', 'OpenERP')
51 The left and right operand don't have the same possible values. The left
52 operand is field name (related to the model for which the domain applies).
53 Actually, the field name can use the dot-notation to traverse relationships.
54 The right operand is a Python value whose type should match the used operator
55 and field type. In the above example, a string is used because the name field
56 of a company has type string, and because we use the '=' operator. When
57 appropriate, a 'in' operator can be used, and thus the right operand should be
60 Note: the non-uniform syntax could have been more uniform, but this would hide
61 an important limitation of the domain syntax. Say that the term representation
62 was ['=', 'company_id.name', 'OpenERP']. Used in a complete domain, this would
65 ['!', ['=', 'company_id.name', 'OpenERP']]
67 and you would be tempted to believe something like this would be possible:
69 ['!', ['=', 'company_id.name', ['&', ..., ...]]]
71 That is, a domain could be a valid operand. But this is not the case. A domain
72 is really limited to a two-level nature, and can not take a recursive form: a
73 domain is not a valid second-level operand.
75 Unaccent - Accent-insensitive search
77 OpenERP will use the SQL function 'unaccent' when available for the 'ilike' and
78 'not ilike' operators, and enabled in the configuration.
79 Normally the 'unaccent' function is obtained from the PostgreSQL 'unaccent'
83 ..todo: The following explanation should be moved in some external installation
86 The steps to install the module might differ on specific PostgreSQL versions.
87 We give here some instruction for PostgreSQL 9.x on a Ubuntu system.
89 Ubuntu doesn't come yet with PostgreSQL 9.x, so an alternative package source
90 is used. We use Martin Pitt's PPA available at ppa:pitti/postgresql[1]. See
91 [2] for instructions. Basically:
93 > sudo add-apt-repository ppa:pitti/postgresql
96 Once the package list is up-to-date, you have to install PostgreSQL 9.0 and
99 > sudo apt-get install postgresql-9.0 postgresql-contrib-9.0
101 When you want to enable unaccent on some database:
103 > psql9 <database> -f /usr/share/postgresql/9.0/contrib/unaccent.sql
105 Here 'psql9' is an alias for the newly installed PostgreSQL 9.0 tool, together
106 with the correct port if necessary (for instance if PostgreSQL 8.4 is running
107 on 5432). (Other aliases can be used for createdb and dropdb.)
109 > alias psql9='/usr/lib/postgresql/9.0/bin/psql -p 5433'
111 You can check unaccent is working:
113 > psql9 <database> -c"select unaccent('hélène')"
115 Finally, to instruct OpenERP to really use the unaccent function, you have to
116 start the server specifying the --unaccent flag.
118 [0] http://developer.postgresql.org/pgdocs/postgres/unaccent.html
119 [1] https://launchpad.net/~pitti/+archive/postgresql
120 [2] https://launchpad.net/+help/soyuz/ppa-sources-list.html
127 from openerp.tools import flatten, reverse_enumerate
129 import openerp.modules
130 from openerp.osv.orm import MAGIC_COLUMNS
132 #.apidoc title: Domain Expressions
138 DOMAIN_OPERATORS = (NOT_OPERATOR, OR_OPERATOR, AND_OPERATOR)
140 # List of available term operators. It is also possible to use the '<>'
141 # operator, which is strictly the same as '!='; the later should be prefered
142 # for consistency. This list doesn't contain '<>' as it is simpified to '!='
143 # by the normalize_operator() function (so later part of the code deals with
144 # only one representation).
145 # An internal (i.e. not available to the user) 'inselect' operator is also
146 # used. In this case its right operand has the form (subselect, params).
147 TERM_OPERATORS = ('=', '!=', '<=', '<', '>', '>=', '=?', '=like', '=ilike',
148 'like', 'not like', 'ilike', 'not ilike', 'in', 'not in',
151 # A subset of the above operators, with a 'negative' semantic. When the
152 # expressions 'in NEGATIVE_TERM_OPERATORS' or 'not in NEGATIVE_TERM_OPERATORS' are used in the code
153 # below, this doesn't necessarily mean that any of those NEGATIVE_TERM_OPERATORS is
154 # legal in the processed term.
155 NEGATIVE_TERM_OPERATORS = ('!=', 'not like', 'not ilike', 'not in')
157 TRUE_LEAF = (1, '=', 1)
158 FALSE_LEAF = (0, '=', 1)
160 TRUE_DOMAIN = [TRUE_LEAF]
161 FALSE_DOMAIN = [FALSE_LEAF]
163 _logger = logging.getLogger(__name__)
165 def normalize(domain):
166 """Returns a normalized version of ``domain_expr``, where all implicit '&' operators
167 have been made explicit. One property of normalized domain expressions is that they
168 can be easily combined together as if they were single domain components.
170 assert isinstance(domain, (list, tuple)), "Domains to normalize must have a 'domain' form: a list or tuple of domain components"
174 expected = 1 # expected number of expressions
175 op_arity = {NOT_OPERATOR: 1, AND_OPERATOR: 2, OR_OPERATOR: 2}
177 if expected == 0: # more than expected, like in [A, B]
178 result[0:0] = [AND_OPERATOR] # put an extra '&' in front
181 if isinstance(token, (list, tuple)): # domain term
184 expected += op_arity.get(token, 0) - 1
188 def combine(operator, unit, zero, domains):
189 """Returns a new domain expression where all domain components from ``domains``
190 have been added together using the binary operator ``operator``. The given
191 domains must be normalized.
193 :param unit: the identity element of the domains "set" with regard to the operation
194 performed by ``operator``, i.e the domain component ``i`` which, when
195 combined with any domain ``x`` via ``operator``, yields ``x``.
196 E.g. [(1,'=',1)] is the typical unit for AND_OPERATOR: adding it
197 to any domain component gives the same domain.
198 :param zero: the absorbing element of the domains "set" with regard to the operation
199 performed by ``operator``, i.e the domain component ``z`` which, when
200 combined with any domain ``x`` via ``operator``, yields ``z``.
201 E.g. [(1,'=',1)] is the typical zero for OR_OPERATOR: as soon as
202 you see it in a domain component the resulting domain is the zero.
203 :param domains: a list of normalized domains.
207 for domain in domains:
215 result = [operator] * (count - 1) + result
219 """AND([D1,D2,...]) returns a domain representing D1 and D2 and ... """
220 return combine(AND_OPERATOR, TRUE_DOMAIN, FALSE_DOMAIN, domains)
223 """OR([D1,D2,...]) returns a domain representing D1 or D2 or ... """
224 return combine(OR_OPERATOR, FALSE_DOMAIN, TRUE_DOMAIN, domains)
226 def is_operator(element):
227 """Test whether an object is a valid domain operator. """
228 return isinstance(element, basestring) and element in DOMAIN_OPERATORS
230 # TODO change the share wizard to use this function.
231 def is_leaf(element, internal=False):
232 """ Test whether an object is a valid domain term.
234 :param internal: allow or not the 'inselect' internal operator in the term.
235 This normally should be always left to False.
237 INTERNAL_OPS = TERM_OPERATORS + ('inselect',)
238 return (isinstance(element, tuple) or isinstance(element, list)) \
239 and len(element) == 3 \
240 and (((not internal) and element[1] in TERM_OPERATORS + ('<>',)) \
241 or (internal and element[1] in INTERNAL_OPS + ('<>',)))
243 def normalize_leaf(left, operator, right):
244 """ Change a term's operator to some canonical form, simplifying later
248 operator = operator.lower()
251 if isinstance(right, bool) and operator in ('in', 'not in'):
252 _logger.warning("The domain term '%s' should use the '=' or '!=' operator." % ((left, original, right),))
253 operator = '=' if operator == 'in' else '!='
254 if isinstance(right, (list, tuple)) and operator in ('=', '!='):
255 _logger.warning("The domain term '%s' should use the 'in' or 'not in' operator." % ((left, original, right),))
256 operator = 'in' if operator == '=' else 'not in'
257 return left, operator, right
259 def distribute_not(domain):
260 """ Distribute any '!' domain operators found inside a normalized domain.
262 Because we don't use SQL semantic for processing a 'left not in right'
263 query (i.e. our 'not in' is not simply translated to a SQL 'not in'),
264 it means that a '! left in right' can not be simply processed
265 by __leaf_to_sql by first emitting code for 'left in right' then wrapping
266 the result with 'not (...)', as it would result in a 'not in' at the SQL
269 This function is thus responsible for pushing any '!' domain operators
270 inside the terms themselves. For example::
272 ['!','&',('user_id','=',4),('partner_id','in',[1,2])]
274 ['|',('user_id','!=',4),('partner_id','not in',[1,2])]
278 """Negates and returns a single domain leaf term,
279 using the opposite operator if possible"""
280 left, operator, right = leaf
289 if operator in ('in', 'like', 'ilike'):
290 operator = 'not ' + operator
291 return [(left, operator, right)]
292 if operator in ('not in', 'not like', 'not ilike'):
293 operator = operator[4:]
294 return [(left, operator, right)]
295 if operator in mapping:
296 operator = mapping[operator]
297 return [(left, operator, right)]
298 return [NOT_OPERATOR, (left, operator, right)]
299 def distribute_negate(domain):
300 """Negate the domain ``subtree`` rooted at domain[0],
301 leaving the rest of the domain intact, and return
302 (negated_subtree, untouched_domain_rest)
304 if is_leaf(domain[0]):
305 return negate(domain[0]), domain[1:]
306 if domain[0] == AND_OPERATOR:
307 done1, todo1 = distribute_negate(domain[1:])
308 done2, todo2 = distribute_negate(todo1)
309 return [OR_OPERATOR] + done1 + done2, todo2
310 if domain[0] == OR_OPERATOR:
311 done1, todo1 = distribute_negate(domain[1:])
312 done2, todo2 = distribute_negate(todo1)
313 return [AND_OPERATOR] + done1 + done2, todo2
316 if domain[0] != NOT_OPERATOR:
317 return [domain[0]] + distribute_not(domain[1:])
318 if domain[0] == NOT_OPERATOR:
319 done, todo = distribute_negate(domain[1:])
320 return done + distribute_not(todo)
322 def select_from_where(cr, select_field, from_table, where_field, where_ids, where_operator):
323 # todo: merge into parent query as sub-query
326 if where_operator in ['<','>','>=','<=']:
327 cr.execute('SELECT "%s" FROM "%s" WHERE "%s" %s %%s' % \
328 (select_field, from_table, where_field, where_operator),
329 (where_ids[0],)) # TODO shouldn't this be min/max(where_ids) ?
330 res = [r[0] for r in cr.fetchall()]
331 else: # TODO where_operator is supposed to be 'in'? It is called with child_of...
332 for i in range(0, len(where_ids), cr.IN_MAX):
333 subids = where_ids[i:i+cr.IN_MAX]
334 cr.execute('SELECT "%s" FROM "%s" WHERE "%s" IN %%s' % \
335 (select_field, from_table, where_field), (tuple(subids),))
336 res.extend([r[0] for r in cr.fetchall()])
339 def select_distinct_from_where_not_null(cr, select_field, from_table):
340 cr.execute('SELECT distinct("%s") FROM "%s" where "%s" is not null' % \
341 (select_field, from_table, select_field))
342 return [r[0] for r in cr.fetchall()]
344 class expression(object):
346 parse a domain expression
347 use a real polish notation
348 leafs are still in a ('foo', '=', 'bar') format
349 For more info: http://christophe-simonis-at-tiny.blogspot.com/2008/08/new-new-domain-notation.html
352 def __init__(self, cr, uid, exp, table, context):
353 self.has_unaccent = openerp.modules.registry.RegistryManager.get(cr.dbname).has_unaccent
354 self.__field_tables = {} # used to store the table to use for the sql generation. key = index of the leaf
355 self.__all_tables = set()
357 self.__main_table = None # 'root' table. set by parse()
358 # assign self.__exp with the normalized, parsed domain.
359 self.parse(cr, uid, distribute_not(normalize(exp)), table, context)
361 # TODO used only for osv_memory
366 def parse(self, cr, uid, exp, table, context):
367 """ transform the leaves of the expression """
369 self.__main_table = table
370 self.__all_tables.add(table)
372 def child_of_domain(left, ids, left_model, parent=None, prefix=''):
373 """Returns a domain implementing the child_of operator for [(left,child_of,ids)],
374 either as a range using the parent_left/right tree lookup fields (when available),
375 or as an expanded [(left,in,child_ids)]"""
376 if left_model._parent_store and (not left_model.pool._init):
377 # TODO: Improve where joins are implemented for many with '.', replace by:
378 # doms += ['&',(prefix+'.parent_left','<',o.parent_right),(prefix+'.parent_left','>=',o.parent_left)]
380 for o in left_model.browse(cr, uid, ids, context=context):
382 doms.insert(0, OR_OPERATOR)
383 doms += [AND_OPERATOR, ('parent_left', '<', o.parent_right), ('parent_left', '>=', o.parent_left)]
385 return [(left, 'in', left_model.search(cr, uid, doms, context=context))]
388 def recursive_children(ids, model, parent_field):
391 ids2 = model.search(cr, uid, [(parent_field, 'in', ids)], context=context)
392 return ids + recursive_children(ids2, model, parent_field)
393 return [(left, 'in', recursive_children(ids, left_model, parent or left_model._parent_name))]
395 def to_ids(value, field_obj):
396 """Normalize a single id or name, or a list of those, into a list of ids"""
398 if isinstance(value, basestring):
400 if value and isinstance(value, (tuple, list)) and isinstance(value[0], basestring):
403 return flatten([[x[0] for x in field_obj.name_search(cr, uid, n, [], 'ilike', context=context, limit=None)] \
405 elif isinstance(value, (int, long)):
410 while i + 1<len(self.__exp):
413 if is_operator(e) or e == TRUE_LEAF or e == FALSE_LEAF:
416 # check if the expression is valid
418 raise ValueError("Invalid term %r in domain expression %r" % (e, exp))
420 # normalize the leaf's operator
421 e = normalize_leaf(*e)
423 left, operator, right = e
425 working_table = table # The table containing the field (the name provided in the left operand)
426 field_path = left.split('.', 1)
428 # If the field is _inherits'd, search for the working_table,
429 # and extract the field.
431 if field_path[0] in table._inherit_fields:
433 field = working_table._columns.get(field_path[0])
435 self.__field_tables[i] = working_table
437 next_table = working_table.pool.get(working_table._inherit_fields[field_path[0]][0])
438 if next_table not in self.__all_tables:
439 self.__joins.append('%s."%s"=%s."%s"' % (next_table._table, 'id', working_table._table, working_table._inherits[next_table._name]))
440 self.__all_tables.add(next_table)
441 working_table = next_table
442 # Or (try to) directly extract the field.
444 field = working_table._columns.get(field_path[0])
447 if left == 'id' and operator == 'child_of':
448 ids2 = to_ids(right, table)
449 dom = child_of_domain(left, ids2, working_table)
450 self.__exp = self.__exp[:i] + dom + self.__exp[i+1:]
452 # field could not be found in model columns, it's probably invalid, unless
453 # it's one of the _log_access special fields
454 # TODO: make these fields explicitly available in self.columns instead!
455 if field_path[0] not in MAGIC_COLUMNS:
456 raise ValueError("Invalid field %r in domain expression %r" % (left, exp))
459 field_obj = table.pool.get(field._obj)
460 if len(field_path) > 1:
461 if field._type == 'many2one':
462 right = field_obj.search(cr, uid, [(field_path[1], operator, right)], context=context)
463 self.__exp[i] = (field_path[0], 'in', right)
464 # Making search easier when there is a left operand as field.o2m or field.m2m
465 if field._type in ['many2many', 'one2many']:
466 right = field_obj.search(cr, uid, [(field_path[1], operator, right)], context=context)
467 right1 = table.search(cr, uid, [(field_path[0],'in', right)], context=dict(context, active_test=False))
468 self.__exp[i] = ('id', 'in', right1)
470 if not isinstance(field, fields.property):
473 if field._properties and not field.store:
474 # this is a function field that is not stored
475 if not field._fnct_search:
476 # the function field doesn't provide a search function and doesn't store
477 # values in the database, so we must ignore it : we generate a dummy leaf
478 self.__exp[i] = TRUE_LEAF
480 "The field '%s' (%s) can not be searched: non-stored "
481 "function field without fnct_search",
483 # avoid compiling stack trace if not needed
484 if _logger.isEnabledFor(logging.DEBUG):
485 _logger.debug(''.join(traceback.format_stack()))
487 subexp = field.search(cr, uid, table, left, [self.__exp[i]], context=context)
489 self.__exp[i] = TRUE_LEAF
491 # we assume that the expression is valid
492 # we create a dummy leaf for forcing the parsing of the resulting expression
493 self.__exp[i] = AND_OPERATOR
494 self.__exp.insert(i + 1, TRUE_LEAF)
495 for j, se in enumerate(subexp):
496 self.__exp.insert(i + 2 + j, se)
497 # else, the value of the field is store in the database, so we search on it
499 elif field._type == 'one2many':
500 # Applying recursivity on field(one2many)
501 if operator == 'child_of':
502 ids2 = to_ids(right, field_obj)
503 if field._obj != working_table._name:
504 dom = child_of_domain(left, ids2, field_obj, prefix=field._obj)
506 dom = child_of_domain('id', ids2, working_table, parent=left)
507 self.__exp = self.__exp[:i] + dom + self.__exp[i+1:]
512 if right is not False:
513 if isinstance(right, basestring):
514 ids2 = [x[0] for x in field_obj.name_search(cr, uid, right, [], operator, context=context, limit=None)]
518 if not isinstance(right, list):
523 if operator in ['like','ilike','in','=']:
524 #no result found with given search criteria
526 self.__exp[i] = FALSE_LEAF
528 ids2 = select_from_where(cr, field._fields_id, field_obj._table, 'id', ids2, operator)
531 self.__exp[i] = ('id', 'in', ids2)
534 o2m_op = 'in' if operator in NEGATIVE_TERM_OPERATORS else 'not in'
535 self.__exp[i] = ('id', o2m_op, select_distinct_from_where_not_null(cr, field._fields_id, field_obj._table))
537 elif field._type == 'many2many':
538 rel_table, rel_id1, rel_id2 = field._sql_names(working_table)
540 if operator == 'child_of':
541 def _rec_convert(ids):
542 if field_obj == table:
544 return select_from_where(cr, rel_id1, rel_table, rel_id2, ids, operator)
546 ids2 = to_ids(right, field_obj)
547 dom = child_of_domain('id', ids2, field_obj)
548 ids2 = field_obj.search(cr, uid, dom, context=context)
549 self.__exp[i] = ('id', 'in', _rec_convert(ids2))
552 if right is not False:
553 if isinstance(right, basestring):
554 res_ids = [x[0] for x in field_obj.name_search(cr, uid, right, [], operator, context=context)]
558 if not isinstance(right, list):
563 if operator in ['like','ilike','in','=']:
564 #no result found with given search criteria
565 call_null_m2m = False
566 self.__exp[i] = FALSE_LEAF
568 operator = 'in' # operator changed because ids are directly related to main object
570 call_null_m2m = False
571 m2m_op = 'not in' if operator in NEGATIVE_TERM_OPERATORS else 'in'
572 self.__exp[i] = ('id', m2m_op, select_from_where(cr, rel_id1, rel_table, rel_id2, res_ids, operator) or [0])
575 m2m_op = 'in' if operator in NEGATIVE_TERM_OPERATORS else 'not in'
576 self.__exp[i] = ('id', m2m_op, select_distinct_from_where_not_null(cr, rel_id1, rel_table))
578 elif field._type == 'many2one':
579 if operator == 'child_of':
580 ids2 = to_ids(right, field_obj)
581 if field._obj != working_table._name:
582 dom = child_of_domain(left, ids2, field_obj, prefix=field._obj)
584 dom = child_of_domain('id', ids2, working_table, parent=left)
585 self.__exp = self.__exp[:i] + dom + self.__exp[i+1:]
587 def _get_expression(field_obj, cr, uid, left, right, operator, context=None):
591 c['active_test'] = False
592 #Special treatment to ill-formed domains
593 operator = ( operator in ['<','>','<=','>='] ) and 'in' or operator
595 dict_op = {'not in':'!=','in':'=','=':'in','!=':'not in'}
596 if isinstance(right, tuple):
598 if (not isinstance(right, list)) and operator in ['not in','in']:
599 operator = dict_op[operator]
600 elif isinstance(right, list) and operator in ['!=','=']: #for domain (FIELD,'=',['value1','value2'])
601 operator = dict_op[operator]
602 res_ids = [x[0] for x in field_obj.name_search(cr, uid, right, [], operator, limit=None, context=c)]
603 if operator in NEGATIVE_TERM_OPERATORS:
604 res_ids.append(False) # TODO this should not be appended if False was in 'right'
605 return (left, 'in', res_ids)
606 # resolve string-based m2o criterion into IDs
607 if isinstance(right, basestring) or \
608 right and isinstance(right, (tuple,list)) and all(isinstance(item, basestring) for item in right):
609 self.__exp[i] = _get_expression(field_obj, cr, uid, left, right, operator, context=context)
611 # right == [] or right == False and all other cases are handled by __leaf_to_sql()
616 # add the time part to datetime field when it's not there:
617 if field._type == 'datetime' and self.__exp[i][2] and len(self.__exp[i][2]) == 10:
619 self.__exp[i] = list(self.__exp[i])
621 if operator in ('>', '>='):
622 self.__exp[i][2] += ' 00:00:00'
623 elif operator in ('<', '<='):
624 self.__exp[i][2] += ' 23:59:59'
626 self.__exp[i] = tuple(self.__exp[i])
629 need_wildcard = operator in ('like', 'ilike', 'not like', 'not ilike')
630 sql_operator = {'=like':'like','=ilike':'ilike'}.get(operator,operator)
632 right = '%%%s%%' % right
634 subselect = '( SELECT res_id' \
635 ' FROM ir_translation' \
640 #Covering in,not in operators with operands (%s,%s) ,etc.
641 if sql_operator in ['in','not in']:
642 instr = ','.join(['%s'] * len(right))
643 subselect += ' AND value ' + sql_operator + ' ' +" (" + instr + ")" \
646 ' FROM "' + working_table._table + '"' \
647 ' WHERE "' + left + '" ' + sql_operator + ' ' +" (" + instr + "))"
649 subselect += ' AND value ' + sql_operator + instr + \
652 ' FROM "' + working_table._table + '"' \
653 ' WHERE "' + left + '" ' + sql_operator + instr + ")"
655 params = [working_table._name + ',' + left,
656 context.get('lang', False) or 'en_US',
662 self.__exp[i] = ('id', 'inselect', (subselect, params))
664 def __leaf_to_sql(self, leaf, table):
665 left, operator, right = leaf
667 # final sanity checks - should never fail
668 assert operator in (TERM_OPERATORS + ('inselect',)), \
669 "Invalid operator %r in domain term %r" % (operator, leaf)
670 assert leaf in (TRUE_LEAF, FALSE_LEAF) or left in table._all_columns \
671 or left in MAGIC_COLUMNS, "Invalid field %r in domain term %r" % (left, leaf)
673 if leaf == TRUE_LEAF:
677 elif leaf == FALSE_LEAF:
681 elif operator == 'inselect':
682 query = '(%s."%s" in (%s))' % (table._table, left, right[0])
685 elif operator in ['in', 'not in']:
686 # Two cases: right is a boolean or a list. The boolean case is an
687 # abuse and handled for backward compatibility.
688 if isinstance(right, bool):
689 _logger.warning("The domain term '%s' should use the '=' or '!=' operator." % (leaf,))
691 r = 'NOT NULL' if right else 'NULL'
693 r = 'NULL' if right else 'NOT NULL'
694 query = '(%s."%s" IS %s)' % (table._table, left, r)
696 elif isinstance(right, (list, tuple)):
699 for i in range(len(params))[::-1]:
700 if params[i] == False:
706 instr = ','.join(['%s'] * len(params))
708 instr = ','.join([table._columns[left]._symbol_set[0]] * len(params))
709 query = '(%s."%s" %s (%s))' % (table._table, left, operator, instr)
711 # The case for (left, 'in', []) or (left, 'not in', []).
712 query = 'FALSE' if operator == 'in' else 'TRUE'
714 if check_nulls and operator == 'in':
715 query = '(%s OR %s."%s" IS NULL)' % (query, table._table, left)
716 elif not check_nulls and operator == 'not in':
717 query = '(%s OR %s."%s" IS NULL)' % (query, table._table, left)
718 elif check_nulls and operator == 'not in':
719 query = '(%s AND %s."%s" IS NOT NULL)' % (query, table._table, left) # needed only for TRUE.
720 else: # Must not happen
721 raise ValueError("Invalid domain term %r" % (leaf,))
723 elif right == False and (left in table._columns) and table._columns[left]._type=="boolean" and (operator == '='):
724 query = '(%s."%s" IS NULL or %s."%s" = false )' % (table._table, left, table._table, left)
727 elif (right is False or right is None) and (operator == '='):
728 query = '%s."%s" IS NULL ' % (table._table, left)
731 elif right == False and (left in table._columns) and table._columns[left]._type=="boolean" and (operator == '!='):
732 query = '(%s."%s" IS NOT NULL and %s."%s" != false)' % (table._table, left, table._table, left)
735 elif (right is False or right is None) and (operator == '!='):
736 query = '%s."%s" IS NOT NULL' % (table._table, left)
739 elif (operator == '=?'):
740 if (right is False or right is None):
741 # '=?' is a short-circuit that makes the term TRUE if right is None or False
745 # '=?' behaves like '=' in other cases
746 query, params = self.__leaf_to_sql((left, '=', right), table)
749 query = '%s.id %s %%s' % (table._table, operator)
753 need_wildcard = operator in ('like', 'ilike', 'not like', 'not ilike')
754 sql_operator = {'=like':'like','=ilike':'ilike'}.get(operator,operator)
756 if left in table._columns:
757 format = need_wildcard and '%s' or table._columns[left]._symbol_set[0]
758 if self.has_unaccent and sql_operator in ('ilike', 'not ilike'):
759 query = '(unaccent(%s."%s") %s unaccent(%s))' % (table._table, left, sql_operator, format)
761 query = '(%s."%s" %s %s)' % (table._table, left, sql_operator, format)
762 elif left in MAGIC_COLUMNS:
763 query = "(%s.\"%s\" %s %%s)" % (table._table, left, sql_operator)
765 else: # Must not happen
766 raise ValueError("Invalid field %r in domain term %r" % (left, leaf))
770 if isinstance(right, str):
772 elif isinstance(right, unicode):
773 str_utf8 = right.encode('utf-8')
775 str_utf8 = str(right)
776 params = '%%%s%%' % str_utf8
777 add_null = not str_utf8
778 elif left in table._columns:
779 params = table._columns[left]._symbol_set[1](right)
782 query = '(%s OR %s."%s" IS NULL)' % (query, table._table, left)
784 if isinstance(params, basestring):
786 return (query, params)
792 # Process the domain from right to left, using a stack, to generate a SQL expression.
793 for i, e in reverse_enumerate(self.__exp):
794 if is_leaf(e, internal=True):
795 table = self.__field_tables.get(i, self.__main_table)
796 q, p = self.__leaf_to_sql(e, table)
799 elif e == NOT_OPERATOR:
800 stack.append('(NOT (%s))' % (stack.pop(),))
802 ops = {AND_OPERATOR: ' AND ', OR_OPERATOR: ' OR '}
805 stack.append('(%s %s %s)' % (q1, ops[e], q2,))
807 assert len(stack) == 1
809 joins = ' AND '.join(self.__joins)
811 query = '(%s) AND %s' % (joins, query)
812 return (query, flatten(params))
814 def get_tables(self):
815 return ['"%s"' % t._table for t in self.__all_tables]
817 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: