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 alternive 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
130 #.apidoc title: Domain Expressions
136 DOMAIN_OPERATORS = (NOT_OPERATOR, OR_OPERATOR, AND_OPERATOR)
138 # List of available term operators. It is also possible to use the '<>'
139 # operator, which is strictly the same as '!='; the later should be prefered
140 # for consistency. This list doesn't contain '<>' as it is simpified to '!='
141 # by the normalize_operator() function (so later part of the code deals with
142 # only one representation).
143 # An internal (i.e. not available to the user) 'inselect' operator is also
144 # used. In this case its right operand has the form (subselect, params).
145 TERM_OPERATORS = ('=', '!=', '<=', '<', '>', '>=', '=?', '=like', '=ilike',
146 'like', 'not like', 'ilike', 'not ilike', 'in', 'not in',
149 # A subset of the above operators, with a 'negative' semantic. When the
150 # expressions 'in NEGATIVE_TERM_OPERATORS' or 'not in NEGATIVE_TERM_OPERATORS' are used in the code
151 # below, this doesn't necessarily mean that any of those NEGATIVE_TERM_OPERATORS is
152 # legal in the processed term.
153 NEGATIVE_TERM_OPERATORS = ('!=', 'not like', 'not ilike', 'not in')
155 TRUE_LEAF = (1, '=', 1)
156 FALSE_LEAF = (0, '=', 1)
158 TRUE_DOMAIN = [TRUE_LEAF]
159 FALSE_DOMAIN = [FALSE_LEAF]
161 _logger = logging.getLogger('expression')
163 def normalize(domain):
164 """Returns a normalized version of ``domain_expr``, where all implicit '&' operators
165 have been made explicit. One property of normalized domain expressions is that they
166 can be easily combined together as if they were single domain components.
168 assert isinstance(domain, (list, tuple)), "Domains to normalize must have a 'domain' form: a list or tuple of domain components"
172 expected = 1 # expected number of expressions
173 op_arity = {NOT_OPERATOR: 1, AND_OPERATOR: 2, OR_OPERATOR: 2}
175 if expected == 0: # more than expected, like in [A, B]
176 result[0:0] = [AND_OPERATOR] # put an extra '&' in front
179 if isinstance(token, (list, tuple)): # domain term
182 expected += op_arity.get(token, 0) - 1
186 def combine(operator, unit, zero, domains):
187 """Returns a new domain expression where all domain components from ``domains``
188 have been added together using the binary operator ``operator``. The given
189 domains must be normalized.
191 :param unit: the identity element of the domains "set" with regard to the operation
192 performed by ``operator``, i.e the domain component ``i`` which, when
193 combined with any domain ``x`` via ``operator``, yields ``x``.
194 E.g. [(1,'=',1)] is the typical unit for AND_OPERATOR: adding it
195 to any domain component gives the same domain.
196 :param zero: the absorbing element of the domains "set" with regard to the operation
197 performed by ``operator``, i.e the domain component ``z`` which, when
198 combined with any domain ``x`` via ``operator``, yields ``z``.
199 E.g. [(1,'=',1)] is the typical zero for OR_OPERATOR: as soon as
200 you see it in a domain component the resulting domain is the zero.
201 :param domains: a list of normalized domains.
205 for domain in domains:
213 result = [operator] * (count - 1) + result
217 """AND([D1,D2,...]) returns a domain representing D1 and D2 and ... """
218 return combine(AND_OPERATOR, TRUE_DOMAIN, FALSE_DOMAIN, domains)
221 """OR([D1,D2,...]) returns a domain representing D1 or D2 or ... """
222 return combine(OR_OPERATOR, FALSE_DOMAIN, TRUE_DOMAIN, domains)
224 def is_operator(element):
225 """Test whether an object is a valid domain operator. """
226 return isinstance(element, basestring) and element in DOMAIN_OPERATORS
228 # TODO change the share wizard to use this function.
229 def is_leaf(element, internal=False):
230 """ Test whether an object is a valid domain term.
232 :param internal: allow or not the 'inselect' internal operator in the term.
233 This normally should be always left to False.
235 INTERNAL_OPS = TERM_OPERATORS + ('inselect',)
236 return (isinstance(element, tuple) or isinstance(element, list)) \
237 and len(element) == 3 \
238 and (((not internal) and element[1] in TERM_OPERATORS + ('<>',)) \
239 or (internal and element[1] in INTERNAL_OPS + ('<>',)))
241 def normalize_leaf(left, operator, right):
242 """ Change a term's operator to some canonical form, simplifying later
246 operator = operator.lower()
249 if isinstance(right, bool) and operator in ('in', 'not in'):
250 _logger.warning("The domain term '%s' should use the '=' or '!=' operator." % ((left, original, right),))
251 operator = '=' if operator == 'in' else '!='
252 if isinstance(right, (list, tuple)) and operator in ('=', '!='):
253 _logger.warning("The domain term '%s' should use the 'in' or 'not in' operator." % ((left, original, right),))
254 operator = 'in' if operator == '=' else 'not in'
255 return left, operator, right
257 def distribute_not(domain):
258 """ Distribute any '!' domain operators found inside a normalized domain.
260 Because we don't use SQL semantic for processing a 'left not in right'
261 query (i.e. our 'not in' is not simply translated to a SQL 'not in'),
262 it means that a '! left in right' can not be simply processed
263 by __leaf_to_sql by first emitting code for 'left in right' then wrapping
264 the result with 'not (...)', as it would result in a 'not in' at the SQL
267 This function is thus responsible for pushing any '!' domain operators
268 inside the terms themselves. For example::
270 ['!','&',('user_id','=',4),('partner_id','in',[1,2])]
272 ['|',('user_id','!=',4),('partner_id','not in',[1,2])]
276 """Negates and returns a single domain leaf term,
277 using the opposite operator if possible"""
278 left, operator, right = leaf
287 if operator in ('in', 'like', 'ilike'):
288 operator = 'not ' + operator
289 return [(left, operator, right)]
290 if operator in ('not in', 'not like', 'not ilike'):
291 operator = operator[4:]
292 return [(left, operator, right)]
293 if operator in mapping:
294 operator = mapping[operator]
295 return [(left, operator, right)]
296 return [NOT_OPERATOR, (left, operator, right)]
297 def distribute_negate(domain):
298 """Negate the domain ``subtree`` rooted at domain[0],
299 leaving the rest of the domain intact, and return
300 (negated_subtree, untouched_domain_rest)
302 if is_leaf(domain[0]):
303 return negate(domain[0]), domain[1:]
304 if domain[0] == AND_OPERATOR:
305 done1, todo1 = distribute_negate(domain[1:])
306 done2, todo2 = distribute_negate(todo1)
307 return [OR_OPERATOR] + done1 + done2, todo2
308 if domain[0] == OR_OPERATOR:
309 done1, todo1 = distribute_negate(domain[1:])
310 done2, todo2 = distribute_negate(todo1)
311 return [AND_OPERATOR] + done1 + done2, todo2
314 if domain[0] != NOT_OPERATOR:
315 return [domain[0]] + distribute_not(domain[1:])
316 if domain[0] == NOT_OPERATOR:
317 done, todo = distribute_negate(domain[1:])
318 return done + distribute_not(todo)
320 def select_from_where(cr, select_field, from_table, where_field, where_ids, where_operator):
321 # todo: merge into parent query as sub-query
324 if where_operator in ['<','>','>=','<=']:
325 cr.execute('SELECT "%s" FROM "%s" WHERE "%s" %s %%s' % \
326 (select_field, from_table, where_field, where_operator),
327 (where_ids[0],)) # TODO shouldn't this be min/max(where_ids) ?
328 res = [r[0] for r in cr.fetchall()]
329 else: # TODO where_operator is supposed to be 'in'? It is called with child_of...
330 for i in range(0, len(where_ids), cr.IN_MAX):
331 subids = where_ids[i:i+cr.IN_MAX]
332 cr.execute('SELECT "%s" FROM "%s" WHERE "%s" IN %%s' % \
333 (select_field, from_table, where_field), (tuple(subids),))
334 res.extend([r[0] for r in cr.fetchall()])
337 def select_distinct_from_where_not_null(cr, select_field, from_table):
338 cr.execute('SELECT distinct("%s") FROM "%s" where "%s" is not null' % \
339 (select_field, from_table, select_field))
340 return [r[0] for r in cr.fetchall()]
342 class expression(object):
344 parse a domain expression
345 use a real polish notation
346 leafs are still in a ('foo', '=', 'bar') format
347 For more info: http://christophe-simonis-at-tiny.blogspot.com/2008/08/new-new-domain-notation.html
350 def __init__(self, cr, uid, exp, table, context):
351 self.has_unaccent = openerp.modules.registry.RegistryManager.get(cr.dbname).has_unaccent
352 self.__field_tables = {} # used to store the table to use for the sql generation. key = index of the leaf
353 self.__all_tables = set()
355 self.__main_table = None # 'root' table. set by parse()
356 # assign self.__exp with the normalized, parsed domain.
357 self.parse(cr, uid, distribute_not(normalize(exp)), table, context)
359 # TODO used only for osv_memory
364 def parse(self, cr, uid, exp, table, context):
365 """ transform the leaves of the expression """
367 self.__main_table = table
368 self.__all_tables.add(table)
370 def child_of_domain(left, ids, left_model, parent=None, prefix=''):
371 """Returns a domain implementing the child_of operator for [(left,child_of,ids)],
372 either as a range using the parent_left/right tree lookup fields (when available),
373 or as an expanded [(left,in,child_ids)]"""
374 if left_model._parent_store and (not left_model.pool._init):
375 # TODO: Improve where joins are implemented for many with '.', replace by:
376 # doms += ['&',(prefix+'.parent_left','<',o.parent_right),(prefix+'.parent_left','>=',o.parent_left)]
378 for o in left_model.browse(cr, uid, ids, context=context):
380 doms.insert(0, OR_OPERATOR)
381 doms += [AND_OPERATOR, ('parent_left', '<', o.parent_right), ('parent_left', '>=', o.parent_left)]
383 return [(left, 'in', left_model.search(cr, uid, doms, context=context))]
386 def recursive_children(ids, model, parent_field):
389 ids2 = model.search(cr, uid, [(parent_field, 'in', ids)], context=context)
390 return ids + recursive_children(ids2, model, parent_field)
391 return [(left, 'in', recursive_children(ids, left_model, parent or left_model._parent_name))]
393 def to_ids(value, field_obj):
394 """ Normalize a single id, or a string, or a list of ids to a list of ids.
396 if isinstance(value, basestring):
397 return [x[0] for x in field_obj.name_search(cr, uid, value, [], 'ilike', context=context, limit=None)]
398 elif isinstance(value, (int, long)):
404 while i + 1<len(self.__exp):
407 if is_operator(e) or e == TRUE_LEAF or e == FALSE_LEAF:
410 # check if the expression is valid
412 raise ValueError('Bad domain expression: %r, %r is not a valid term.' % (exp, e))
414 # normalize the leaf's operator
415 e = normalize_leaf(*e)
417 left, operator, right = e
419 working_table = table # The table containing the field (the name provided in the left operand)
420 fargs = left.split('.', 1)
422 # If the field is _inherits'd, search for the working_table,
423 # and extract the field.
424 if fargs[0] in table._inherit_fields:
426 field = working_table._columns.get(fargs[0])
428 self.__field_tables[i] = working_table
430 next_table = working_table.pool.get(working_table._inherit_fields[fargs[0]][0])
431 if next_table not in self.__all_tables:
432 self.__joins.append('%s."%s"=%s."%s"' % (next_table._table, 'id', working_table._table, working_table._inherits[next_table._name]))
433 self.__all_tables.add(next_table)
434 working_table = next_table
435 # Or (try to) directly extract the field.
437 field = working_table._columns.get(fargs[0])
440 if left == 'id' and operator == 'child_of':
441 ids2 = to_ids(right, table)
442 dom = child_of_domain(left, ids2, working_table)
443 self.__exp = self.__exp[:i] + dom + self.__exp[i+1:]
446 field_obj = table.pool.get(field._obj)
448 if field._type == 'many2one':
449 right = field_obj.search(cr, uid, [(fargs[1], operator, right)], context=context)
450 self.__exp[i] = (fargs[0], 'in', right)
451 # Making search easier when there is a left operand as field.o2m or field.m2m
452 if field._type in ['many2many', 'one2many']:
453 right = field_obj.search(cr, uid, [(fargs[1], operator, right)], context=context)
454 right1 = table.search(cr, uid, [(fargs[0], 'in', right)], context=context)
455 self.__exp[i] = ('id', 'in', right1)
457 if not isinstance(field, fields.property):
460 if field._properties and not field.store:
461 # this is a function field that is not stored
462 if not field._fnct_search:
463 # the function field doesn't provide a search function and doesn't store
464 # values in the database, so we must ignore it : we generate a dummy leaf
465 self.__exp[i] = TRUE_LEAF
467 subexp = field.search(cr, uid, table, left, [self.__exp[i]], context=context)
469 self.__exp[i] = TRUE_LEAF
471 # we assume that the expression is valid
472 # we create a dummy leaf for forcing the parsing of the resulting expression
473 self.__exp[i] = AND_OPERATOR
474 self.__exp.insert(i + 1, TRUE_LEAF)
475 for j, se in enumerate(subexp):
476 self.__exp.insert(i + 2 + j, se)
477 # else, the value of the field is store in the database, so we search on it
479 elif field._type == 'one2many':
480 # Applying recursivity on field(one2many)
481 if operator == 'child_of':
482 ids2 = to_ids(right, field_obj)
483 if field._obj != working_table._name:
484 dom = child_of_domain(left, ids2, field_obj, prefix=field._obj)
486 dom = child_of_domain('id', ids2, working_table, parent=left)
487 self.__exp = self.__exp[:i] + dom + self.__exp[i+1:]
492 if right is not False:
493 if isinstance(right, basestring):
494 ids2 = [x[0] for x in field_obj.name_search(cr, uid, right, [], operator, context=context, limit=None)]
498 if not isinstance(right, list):
503 if operator in ['like','ilike','in','=']:
504 #no result found with given search criteria
506 self.__exp[i] = FALSE_LEAF
509 o2m_op = 'not in' if operator in NEGATIVE_TERM_OPERATORS else 'in'
510 self.__exp[i] = ('id', o2m_op, select_from_where(cr, field._fields_id, field_obj._table, 'id', ids2, operator))
513 o2m_op = 'in' if operator in NEGATIVE_TERM_OPERATORS else 'not in'
514 self.__exp[i] = ('id', o2m_op, select_distinct_from_where_not_null(cr, field._fields_id, field_obj._table))
516 elif field._type == 'many2many':
518 if operator == 'child_of':
519 def _rec_convert(ids):
520 if field_obj == table:
522 return select_from_where(cr, field._id1, field._rel, field._id2, ids, operator)
524 ids2 = to_ids(right, field_obj)
525 dom = child_of_domain('id', ids2, field_obj)
526 ids2 = field_obj.search(cr, uid, dom, context=context)
527 self.__exp[i] = ('id', 'in', _rec_convert(ids2))
530 if right is not False:
531 if isinstance(right, basestring):
532 res_ids = [x[0] for x in field_obj.name_search(cr, uid, right, [], operator, context=context)]
536 if not isinstance(right, list):
541 if operator in ['like','ilike','in','=']:
542 #no result found with given search criteria
543 call_null_m2m = False
544 self.__exp[i] = FALSE_LEAF
546 operator = 'in' # operator changed because ids are directly related to main object
548 call_null_m2m = False
549 m2m_op = 'not in' if operator in NEGATIVE_TERM_OPERATORS else 'in'
550 self.__exp[i] = ('id', m2m_op, select_from_where(cr, field._id1, field._rel, field._id2, res_ids, operator) or [0])
553 m2m_op = 'in' if operator in NEGATIVE_TERM_OPERATORS else 'not in'
554 self.__exp[i] = ('id', m2m_op, select_distinct_from_where_not_null(cr, field._id1, field._rel))
556 elif field._type == 'many2one':
557 if operator == 'child_of':
558 ids2 = to_ids(right, field_obj)
559 if field._obj != working_table._name:
560 dom = child_of_domain(left, ids2, field_obj, prefix=field._obj)
562 dom = child_of_domain('id', ids2, working_table, parent=left)
563 self.__exp = self.__exp[:i] + dom + self.__exp[i+1:]
565 def _get_expression(field_obj, cr, uid, left, right, operator, context=None):
569 c['active_test'] = False
570 #Special treatment to ill-formed domains
571 operator = ( operator in ['<','>','<=','>='] ) and 'in' or operator
573 dict_op = {'not in':'!=','in':'=','=':'in','!=':'not in'}
574 if isinstance(right, tuple):
576 if (not isinstance(right, list)) and operator in ['not in','in']:
577 operator = dict_op[operator]
578 elif isinstance(right, list) and operator in ['!=','=']: #for domain (FIELD,'=',['value1','value2'])
579 operator = dict_op[operator]
580 res_ids = [x[0] for x in field_obj.name_search(cr, uid, right, [], operator, limit=None, context=c)]
581 if operator in NEGATIVE_TERM_OPERATORS:
582 res_ids.append(False) # TODO this should not be appended if False was in 'right'
583 return (left, 'in', res_ids)
587 if isinstance(right, basestring): # and not isinstance(field, fields.related):
589 elif isinstance(right, (list, tuple)):
592 if not isinstance(ele, basestring):
596 self.__exp[i] = _get_expression(field_obj, cr, uid, left, right, operator, context=context)
598 pass # Handled by __leaf_to_sql().
599 else: # right is False
600 pass # Handled by __leaf_to_sql().
604 # add the time part to datetime field when it's not there:
605 if field._type == 'datetime' and self.__exp[i][2] and len(self.__exp[i][2]) == 10:
607 self.__exp[i] = list(self.__exp[i])
609 if operator in ('>', '>='):
610 self.__exp[i][2] += ' 00:00:00'
611 elif operator in ('<', '<='):
612 self.__exp[i][2] += ' 23:59:59'
614 self.__exp[i] = tuple(self.__exp[i])
617 need_wildcard = operator in ('like', 'ilike', 'not like', 'not ilike')
618 sql_operator = {'=like':'like','=ilike':'ilike'}.get(operator,operator)
620 right = '%%%s%%' % right
622 subselect = '( SELECT res_id' \
623 ' FROM ir_translation' \
628 #Covering in,not in operators with operands (%s,%s) ,etc.
629 if sql_operator in ['in','not in']:
630 instr = ','.join(['%s'] * len(right))
631 subselect += ' AND value ' + sql_operator + ' ' +" (" + instr + ")" \
634 ' FROM "' + working_table._table + '"' \
635 ' WHERE "' + left + '" ' + sql_operator + ' ' +" (" + instr + "))"
637 subselect += ' AND value ' + sql_operator + instr + \
640 ' FROM "' + working_table._table + '"' \
641 ' WHERE "' + left + '" ' + sql_operator + instr + ")"
643 params = [working_table._name + ',' + left,
644 context.get('lang', False) or 'en_US',
650 self.__exp[i] = ('id', 'inselect', (subselect, params))
652 def __leaf_to_sql(self, leaf, table):
653 left, operator, right = leaf
655 if leaf == TRUE_LEAF:
659 elif leaf == FALSE_LEAF:
663 elif operator == 'inselect':
664 query = '(%s."%s" in (%s))' % (table._table, left, right[0])
667 elif operator in ['in', 'not in']:
668 # Two cases: right is a boolean or a list. The boolean case is an
669 # abuse and handled for backward compatibility.
670 if isinstance(right, bool):
671 _logger.warning("The domain term '%s' should use the '=' or '!=' operator." % (leaf,))
673 r = 'NOT NULL' if right else 'NULL'
675 r = 'NULL' if right else 'NOT NULL'
676 query = '(%s."%s" IS %s)' % (table._table, left, r)
678 elif isinstance(right, (list, tuple)):
681 for i in range(len(params))[::-1]:
682 if params[i] == False:
688 instr = ','.join(['%s'] * len(params))
690 instr = ','.join([table._columns[left]._symbol_set[0]] * len(params))
691 query = '(%s."%s" %s (%s))' % (table._table, left, operator, instr)
693 # The case for (left, 'in', []) or (left, 'not in', []).
694 query = 'FALSE' if operator == 'in' else 'TRUE'
696 if check_nulls and operator == 'in':
697 query = '(%s OR %s."%s" IS NULL)' % (query, table._table, left)
698 elif not check_nulls and operator == 'not in':
699 query = '(%s OR %s."%s" IS NULL)' % (query, table._table, left)
700 elif check_nulls and operator == 'not in':
701 query = '(%s AND %s."%s" IS NOT NULL)' % (query, table._table, left) # needed only for TRUE.
702 else: # Must not happen.
705 elif right == False and (left in table._columns) and table._columns[left]._type=="boolean" and (operator == '='):
706 query = '(%s."%s" IS NULL or %s."%s" = false )' % (table._table, left, table._table, left)
709 elif (right is False or right is None) and (operator == '='):
710 query = '%s."%s" IS NULL ' % (table._table, left)
713 elif right == False and (left in table._columns) and table._columns[left]._type=="boolean" and (operator == '!='):
714 query = '(%s."%s" IS NOT NULL and %s."%s" != false)' % (table._table, left, table._table, left)
717 elif (right is False or right is None) and (operator == '!='):
718 query = '%s."%s" IS NOT NULL' % (table._table, left)
721 elif (operator == '=?'):
722 if (right is False or right is None):
725 elif left in table._columns:
726 format = table._columns[left]._symbol_set[0]
727 query = '(%s."%s" = %s)' % (table._table, left, format)
728 params = table._columns[left]._symbol_set[1](right)
730 query = "(%s.\"%s\" = '%%s')" % (table._table, left)
734 query = '%s.id %s %%s' % (table._table, operator)
738 need_wildcard = operator in ('like', 'ilike', 'not like', 'not ilike')
739 sql_operator = {'=like':'like','=ilike':'ilike'}.get(operator,operator)
741 if left in table._columns:
742 format = need_wildcard and '%s' or table._columns[left]._symbol_set[0]
743 if self.has_unaccent and sql_operator in ('ilike', 'not ilike'):
744 query = '(unaccent(%s."%s") %s unaccent(%s))' % (table._table, left, sql_operator, format)
746 query = '(%s."%s" %s %s)' % (table._table, left, sql_operator, format)
748 if self.has_unaccent and sql_operator in ('ilike', 'not ilike'):
749 query = "(unaccent(%s.\"%s\") %s unaccent('%s'))" % (table._table, left, sql_operator, right)
751 query = "(%s.\"%s\" %s '%s')" % (table._table, left, sql_operator, right)
755 if isinstance(right, str):
757 elif isinstance(right, unicode):
758 str_utf8 = right.encode('utf-8')
760 str_utf8 = str(right)
761 params = '%%%s%%' % str_utf8
762 add_null = not str_utf8
763 elif left in table._columns:
764 params = table._columns[left]._symbol_set[1](right)
767 query = '(%s OR %s."%s" IS NULL)' % (query, table._table, left)
769 if isinstance(params, basestring):
771 return (query, params)
777 # Process the domain from right to left, using a stack, to generate a SQL expression.
778 for i, e in reverse_enumerate(self.__exp):
779 if is_leaf(e, internal=True):
780 table = self.__field_tables.get(i, self.__main_table)
781 q, p = self.__leaf_to_sql(e, table)
784 elif e == NOT_OPERATOR:
785 stack.append('(NOT (%s))' % (stack.pop(),))
787 ops = {AND_OPERATOR: ' AND ', OR_OPERATOR: ' OR '}
790 stack.append('(%s %s %s)' % (q1, ops[e], q2,))
792 assert len(stack) == 1
794 joins = ' AND '.join(self.__joins)
796 query = '(%s) AND %s' % (joins, query)
797 return (query, flatten(params))
799 def get_tables(self):
800 return ['"%s"' % t._table for t in self.__all_tables]
802 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: