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 takes 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
126 from openerp.tools import flatten, reverse_enumerate
128 import openerp.modules
129 from openerp.osv.orm import MAGIC_COLUMNS
131 #.apidoc title: Domain Expressions
137 DOMAIN_OPERATORS = (NOT_OPERATOR, OR_OPERATOR, AND_OPERATOR)
139 # List of available term operators. It is also possible to use the '<>'
140 # operator, which is strictly the same as '!='; the later should be prefered
141 # for consistency. This list doesn't contain '<>' as it is simpified to '!='
142 # by the normalize_operator() function (so later part of the code deals with
143 # only one representation).
144 # An internal (i.e. not available to the user) 'inselect' operator is also
145 # used. In this case its right operand has the form (subselect, params).
146 TERM_OPERATORS = ('=', '!=', '<=', '<', '>', '>=', '=?', '=like', '=ilike',
147 'like', 'not like', 'ilike', 'not ilike', 'in', 'not in',
150 # A subset of the above operators, with a 'negative' semantic. When the
151 # expressions 'in NEGATIVE_TERM_OPERATORS' or 'not in NEGATIVE_TERM_OPERATORS' are used in the code
152 # below, this doesn't necessarily mean that any of those NEGATIVE_TERM_OPERATORS is
153 # legal in the processed term.
154 NEGATIVE_TERM_OPERATORS = ('!=', 'not like', 'not ilike', 'not in')
156 TRUE_LEAF = (1, '=', 1)
157 FALSE_LEAF = (0, '=', 1)
159 TRUE_DOMAIN = [TRUE_LEAF]
160 FALSE_DOMAIN = [FALSE_LEAF]
162 _logger = logging.getLogger('expression')
164 def normalize(domain):
165 """Returns a normalized version of ``domain_expr``, where all implicit '&' operators
166 have been made explicit. One property of normalized domain expressions is that they
167 can be easily combined together as if they were single domain components.
169 assert isinstance(domain, (list, tuple)), "Domains to normalize must have a 'domain' form: a list or tuple of domain components"
173 expected = 1 # expected number of expressions
174 op_arity = {NOT_OPERATOR: 1, AND_OPERATOR: 2, OR_OPERATOR: 2}
176 if expected == 0: # more than expected, like in [A, B]
177 result[0:0] = [AND_OPERATOR] # put an extra '&' in front
180 if isinstance(token, (list, tuple)): # domain term
183 expected += op_arity.get(token, 0) - 1
187 def combine(operator, unit, zero, domains):
188 """Returns a new domain expression where all domain components from ``domains``
189 have been added together using the binary operator ``operator``. The given
190 domains must be normalized.
192 :param unit: the identity element of the domains "set" with regard to the operation
193 performed by ``operator``, i.e the domain component ``i`` which, when
194 combined with any domain ``x`` via ``operator``, yields ``x``.
195 E.g. [(1,'=',1)] is the typical unit for AND_OPERATOR: adding it
196 to any domain component gives the same domain.
197 :param zero: the absorbing element of the domains "set" with regard to the operation
198 performed by ``operator``, i.e the domain component ``z`` which, when
199 combined with any domain ``x`` via ``operator``, yields ``z``.
200 E.g. [(1,'=',1)] is the typical zero for OR_OPERATOR: as soon as
201 you see it in a domain component the resulting domain is the zero.
202 :param domains: a list of normalized domains.
206 for domain in domains:
214 result = [operator] * (count - 1) + result
218 """AND([D1,D2,...]) returns a domain representing D1 and D2 and ... """
219 return combine(AND_OPERATOR, TRUE_DOMAIN, FALSE_DOMAIN, domains)
222 """OR([D1,D2,...]) returns a domain representing D1 or D2 or ... """
223 return combine(OR_OPERATOR, FALSE_DOMAIN, TRUE_DOMAIN, domains)
225 def is_operator(element):
226 """Test whether an object is a valid domain operator. """
227 return isinstance(element, basestring) and element in DOMAIN_OPERATORS
229 # TODO change the share wizard to use this function.
230 def is_leaf(element, internal=False):
231 """ Test whether an object is a valid domain term.
233 :param internal: allow or not the 'inselect' internal operator in the term.
234 This normally should be always left to False.
236 INTERNAL_OPS = TERM_OPERATORS + ('inselect',)
237 return (isinstance(element, tuple) or isinstance(element, list)) \
238 and len(element) == 3 \
239 and (((not internal) and element[1] in TERM_OPERATORS + ('<>',)) \
240 or (internal and element[1] in INTERNAL_OPS + ('<>',)))
242 def normalize_leaf(left, operator, right):
243 """ Change a term's operator to some canonical form, simplifying later
247 operator = operator.lower()
250 if isinstance(right, bool) and operator in ('in', 'not in'):
251 _logger.warning("The domain term '%s' should use the '=' or '!=' operator." % ((left, original, right),))
252 operator = '=' if operator == 'in' else '!='
253 if isinstance(right, (list, tuple)) and operator in ('=', '!='):
254 _logger.warning("The domain term '%s' should use the 'in' or 'not in' operator." % ((left, original, right),))
255 operator = 'in' if operator == '=' else 'not in'
256 return left, operator, right
258 def distribute_not(domain):
259 """ Distribute any '!' domain operators found inside a normalized domain.
261 Because we don't use SQL semantic for processing a 'left not in right'
262 query (i.e. our 'not in' is not simply translated to a SQL 'not in'),
263 it means that a '! left in right' can not be simply processed
264 by __leaf_to_sql by first emitting code for 'left in right' then wrapping
265 the result with 'not (...)', as it would result in a 'not in' at the SQL
268 This function is thus responsible for pushing any '!' domain operators
269 inside the terms themselves. For example::
271 ['!','&',('user_id','=',4),('partner_id','in',[1,2])]
273 ['|',('user_id','!=',4),('partner_id','not in',[1,2])]
277 """Negates and returns a single domain leaf term,
278 using the opposite operator if possible"""
279 left, operator, right = leaf
288 if operator in ('in', 'like', 'ilike'):
289 operator = 'not ' + operator
290 return [(left, operator, right)]
291 if operator in ('not in', 'not like', 'not ilike'):
292 operator = operator[4:]
293 return [(left, operator, right)]
294 if operator in mapping:
295 operator = mapping[operator]
296 return [(left, operator, right)]
297 return [NOT_OPERATOR, (left, operator, right)]
298 def distribute_negate(domain):
299 """Negate the domain ``subtree`` rooted at domain[0],
300 leaving the rest of the domain intact, and return
301 (negated_subtree, untouched_domain_rest)
303 if is_leaf(domain[0]):
304 return negate(domain[0]), domain[1:]
305 if domain[0] == AND_OPERATOR:
306 done1, todo1 = distribute_negate(domain[1:])
307 done2, todo2 = distribute_negate(todo1)
308 return [OR_OPERATOR] + done1 + done2, todo2
309 if domain[0] == OR_OPERATOR:
310 done1, todo1 = distribute_negate(domain[1:])
311 done2, todo2 = distribute_negate(todo1)
312 return [AND_OPERATOR] + done1 + done2, todo2
315 if domain[0] != NOT_OPERATOR:
316 return [domain[0]] + distribute_not(domain[1:])
317 if domain[0] == NOT_OPERATOR:
318 done, todo = distribute_negate(domain[1:])
319 return done + distribute_not(todo)
321 def select_from_where(cr, select_field, from_table, where_field, where_ids, where_operator):
322 # todo: merge into parent query as sub-query
325 if where_operator in ['<','>','>=','<=']:
326 cr.execute('SELECT "%s" FROM "%s" WHERE "%s" %s %%s' % \
327 (select_field, from_table, where_field, where_operator),
328 (where_ids[0],)) # TODO shouldn't this be min/max(where_ids) ?
329 res = [r[0] for r in cr.fetchall()]
330 else: # TODO where_operator is supposed to be 'in'? It is called with child_of...
331 for i in range(0, len(where_ids), cr.IN_MAX):
332 subids = where_ids[i:i+cr.IN_MAX]
333 cr.execute('SELECT "%s" FROM "%s" WHERE "%s" IN %%s' % \
334 (select_field, from_table, where_field), (tuple(subids),))
335 res.extend([r[0] for r in cr.fetchall()])
338 def select_distinct_from_where_not_null(cr, select_field, from_table):
339 cr.execute('SELECT distinct("%s") FROM "%s" where "%s" is not null' % \
340 (select_field, from_table, select_field))
341 return [r[0] for r in cr.fetchall()]
343 class expression(object):
345 parse a domain expression
346 use a real polish notation
347 leafs are still in a ('foo', '=', 'bar') format
348 For more info: http://christophe-simonis-at-tiny.blogspot.com/2008/08/new-new-domain-notation.html
351 def __init__(self, cr, uid, exp, table, context):
352 self.has_unaccent = openerp.modules.registry.RegistryManager.get(cr.dbname).has_unaccent
353 self.__field_tables = {} # used to store the table to use for the sql generation. key = index of the leaf
354 self.__all_tables = set()
356 self.__main_table = None # 'root' table. set by parse()
357 # assign self.__exp with the normalized, parsed domain.
358 self.parse(cr, uid, distribute_not(normalize(exp)), table, context)
360 # TODO used only for osv_memory
365 def parse(self, cr, uid, exp, table, context):
366 """ transform the leaves of the expression """
368 self.__main_table = table
369 self.__all_tables.add(table)
371 def child_of_domain(left, ids, left_model, parent=None, prefix=''):
372 """Returns a domain implementing the child_of operator for [(left,child_of,ids)],
373 either as a range using the parent_left/right tree lookup fields (when available),
374 or as an expanded [(left,in,child_ids)]"""
375 if left_model._parent_store and (not left_model.pool._init):
376 # TODO: Improve where joins are implemented for many with '.', replace by:
377 # doms += ['&',(prefix+'.parent_left','<',o.parent_right),(prefix+'.parent_left','>=',o.parent_left)]
379 for o in left_model.browse(cr, uid, ids, context=context):
381 doms.insert(0, OR_OPERATOR)
382 doms += [AND_OPERATOR, ('parent_left', '<', o.parent_right), ('parent_left', '>=', o.parent_left)]
384 return [(left, 'in', left_model.search(cr, uid, doms, context=context))]
387 def recursive_children(ids, model, parent_field):
390 ids2 = model.search(cr, uid, [(parent_field, 'in', ids)], context=context)
391 return ids + recursive_children(ids2, model, parent_field)
392 return [(left, 'in', recursive_children(ids, left_model, parent or left_model._parent_name))]
394 def to_ids(value, field_obj):
395 """Normalize a single id or name, or a list of those, into a list of ids"""
397 if isinstance(value, basestring):
399 if value and isinstance(value, (tuple, list)) and isinstance(value[0], basestring):
402 return flatten([[x[0] for x in field_obj.name_search(cr, uid, n, [], 'ilike', context=context, limit=None)] \
404 elif isinstance(value, (int, long)):
409 while i + 1<len(self.__exp):
412 if is_operator(e) or e == TRUE_LEAF or e == FALSE_LEAF:
415 # check if the expression is valid
417 raise ValueError("Invalid term %r in domain expression %r" % (e, exp))
419 # normalize the leaf's operator
420 e = normalize_leaf(*e)
422 left, operator, right = e
424 working_table = table # The table containing the field (the name provided in the left operand)
425 field_path = left.split('.', 1)
427 # If the field is _inherits'd, search for the working_table,
428 # and extract the field.
429 if field_path[0] in table._inherit_fields:
431 field = working_table._columns.get(field_path[0])
433 self.__field_tables[i] = working_table
435 next_table = working_table.pool.get(working_table._inherit_fields[field_path[0]][0])
436 if next_table not in self.__all_tables:
437 self.__joins.append('%s."%s"=%s."%s"' % (next_table._table, 'id', working_table._table, working_table._inherits[next_table._name]))
438 self.__all_tables.add(next_table)
439 working_table = next_table
440 # Or (try to) directly extract the field.
442 field = working_table._columns.get(field_path[0])
445 if left == 'id' and operator == 'child_of':
446 ids2 = to_ids(right, table)
447 dom = child_of_domain(left, ids2, working_table)
448 self.__exp = self.__exp[:i] + dom + self.__exp[i+1:]
450 # field could not be found in model columns, it's probably invalid, unless
451 # it's one of the _log_access special fields
452 # TODO: make these fields explicitly available in self.columns instead!
453 if (field_path[0] not in MAGIC_COLUMNS) and (left not in MAGIC_COLUMNS):
454 raise ValueError("Invalid field %r in domain expression %r" % (left, exp))
457 field_obj = table.pool.get(field._obj)
458 if len(field_path) > 1:
459 if field._type == 'many2one':
460 right = field_obj.search(cr, uid, [(field_path[1], operator, right)], context=context)
461 self.__exp[i] = (field_path[0], 'in', right)
462 # Making search easier when there is a left operand as field.o2m or field.m2m
463 if field._type in ['many2many', 'one2many']:
464 right = field_obj.search(cr, uid, [(field_path[1], operator, right)], context=context)
465 right1 = table.search(cr, uid, [(field_path[0], 'in', right)], context=context)
466 self.__exp[i] = ('id', 'in', right1)
468 if not isinstance(field, fields.property):
471 if field._properties and not field.store:
472 # this is a function field that is not stored
473 if not field._fnct_search:
474 # the function field doesn't provide a search function and doesn't store
475 # values in the database, so we must ignore it : we generate a dummy leaf
476 self.__exp[i] = TRUE_LEAF
478 subexp = field.search(cr, uid, table, left, [self.__exp[i]], context=context)
480 self.__exp[i] = TRUE_LEAF
482 # we assume that the expression is valid
483 # we create a dummy leaf for forcing the parsing of the resulting expression
484 self.__exp[i] = AND_OPERATOR
485 self.__exp.insert(i + 1, TRUE_LEAF)
486 for j, se in enumerate(subexp):
487 self.__exp.insert(i + 2 + j, se)
488 # else, the value of the field is store in the database, so we search on it
490 elif field._type == 'one2many':
491 # Applying recursivity on field(one2many)
492 if operator == 'child_of':
493 ids2 = to_ids(right, field_obj)
494 if field._obj != working_table._name:
495 dom = child_of_domain(left, ids2, field_obj, prefix=field._obj)
497 dom = child_of_domain('id', ids2, working_table, parent=left)
498 self.__exp = self.__exp[:i] + dom + self.__exp[i+1:]
503 if right is not False:
504 if isinstance(right, basestring):
505 ids2 = [x[0] for x in field_obj.name_search(cr, uid, right, [], operator, context=context, limit=None)]
509 if not isinstance(right, list):
514 if operator in ['like','ilike','in','=']:
515 #no result found with given search criteria
517 self.__exp[i] = FALSE_LEAF
519 ids2 = select_from_where(cr, field._fields_id, field_obj._table, 'id', ids2, operator)
522 self.__exp[i] = ('id', 'in', ids2)
525 o2m_op = 'in' if operator in NEGATIVE_TERM_OPERATORS else 'not in'
526 self.__exp[i] = ('id', o2m_op, select_distinct_from_where_not_null(cr, field._fields_id, field_obj._table))
528 elif field._type == 'many2many':
530 if operator == 'child_of':
531 def _rec_convert(ids):
532 if field_obj == table:
534 return select_from_where(cr, field._id1, field._rel, field._id2, ids, operator)
536 ids2 = to_ids(right, field_obj)
537 dom = child_of_domain('id', ids2, field_obj)
538 ids2 = field_obj.search(cr, uid, dom, context=context)
539 self.__exp[i] = ('id', 'in', _rec_convert(ids2))
542 if right is not False:
543 if isinstance(right, basestring):
544 res_ids = [x[0] for x in field_obj.name_search(cr, uid, right, [], operator, context=context)]
548 if not isinstance(right, list):
553 if operator in ['like','ilike','in','=']:
554 #no result found with given search criteria
555 call_null_m2m = False
556 self.__exp[i] = FALSE_LEAF
558 operator = 'in' # operator changed because ids are directly related to main object
560 call_null_m2m = False
561 m2m_op = 'not in' if operator in NEGATIVE_TERM_OPERATORS else 'in'
562 self.__exp[i] = ('id', m2m_op, select_from_where(cr, field._id1, field._rel, field._id2, res_ids, operator) or [0])
565 m2m_op = 'in' if operator in NEGATIVE_TERM_OPERATORS else 'not in'
566 self.__exp[i] = ('id', m2m_op, select_distinct_from_where_not_null(cr, field._id1, field._rel))
568 elif field._type == 'many2one':
569 if operator == 'child_of':
570 ids2 = to_ids(right, field_obj)
571 if field._obj != working_table._name:
572 dom = child_of_domain(left, ids2, field_obj, prefix=field._obj)
574 dom = child_of_domain('id', ids2, working_table, parent=left)
575 self.__exp = self.__exp[:i] + dom + self.__exp[i+1:]
577 def _get_expression(field_obj, cr, uid, left, right, operator, context=None):
581 c['active_test'] = False
582 #Special treatment to ill-formed domains
583 operator = ( operator in ['<','>','<=','>='] ) and 'in' or operator
585 dict_op = {'not in':'!=','in':'=','=':'in','!=':'not in'}
586 if isinstance(right, tuple):
588 if (not isinstance(right, list)) and operator in ['not in','in']:
589 operator = dict_op[operator]
590 elif isinstance(right, list) and operator in ['!=','=']: #for domain (FIELD,'=',['value1','value2'])
591 operator = dict_op[operator]
592 res_ids = [x[0] for x in field_obj.name_search(cr, uid, right, [], operator, limit=None, context=c)]
593 if operator in NEGATIVE_TERM_OPERATORS:
594 res_ids.append(False) # TODO this should not be appended if False was in 'right'
595 return (left, 'in', res_ids)
599 if isinstance(right, basestring): # and not isinstance(field, fields.related):
601 elif isinstance(right, (list, tuple)):
604 if not isinstance(ele, basestring):
608 self.__exp[i] = _get_expression(field_obj, cr, uid, left, right, operator, context=context)
610 pass # Handled by __leaf_to_sql().
611 else: # right is False
612 pass # 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: