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 from openerp.tools import flatten, reverse_enumerate
26 #.apidoc title: Domain Expressions
32 TRUE_LEAF = (1, '=', 1)
33 FALSE_LEAF = (0, '=', 1)
35 TRUE_DOMAIN = [TRUE_LEAF]
36 FALSE_DOMAIN = [FALSE_LEAF]
38 def normalize(domain):
39 """Returns a normalized version of ``domain_expr``, where all implicit '&' operators
40 have been made explicit. One property of normalized domain expressions is that they
41 can be easily combined together as if they were single domain components.
43 assert isinstance(domain, (list, tuple)), "Domains to normalize must have a 'domain' form: a list or tuple of domain components"
47 expected = 1 # expected number of expressions
48 op_arity = {NOT_OPERATOR: 1, AND_OPERATOR: 2, OR_OPERATOR: 2}
50 if expected == 0: # more than expected, like in [A, B]
51 result[0:0] = ['&'] # put an extra '&' in front
54 if isinstance(token, (list, tuple)): # domain term
57 expected += op_arity.get(token, 0) - 1
61 def combine(operator, unit, zero, domains):
62 """Returns a new domain expression where all domain components from ``domains``
63 have been added together using the binary operator ``operator``. The given
64 domains must be normalized.
66 :param unit: the identity element of the domains "set" with regard to the operation
67 performed by ``operator``, i.e the domain component ``i`` which, when
68 combined with any domain ``x`` via ``operator``, yields ``x``.
69 E.g. [(1,'=',1)] is the typical unit for AND_OPERATOR: adding it
70 to any domain component gives the same domain.
71 :param zero: the absorbing element of the domains "set" with regard to the operation
72 performed by ``operator``, i.e the domain component ``z`` which, when
73 combined with any domain ``x`` via ``operator``, yields ``z``.
74 E.g. [(1,'=',1)] is the typical zero for OR_OPERATOR: as soon as
75 you see it in a domain component the resulting domain is the zero.
76 :param domains: a list of normalized domains.
80 for domain in domains:
88 result = [operator] * (count - 1) + result
92 """ AND([D1,D2,...]) returns a domain representing D1 and D2 and ... """
93 return combine(AND_OPERATOR, TRUE_DOMAIN, FALSE_DOMAIN, domains)
96 """ OR([D1,D2,...]) returns a domain representing D1 or D2 or ... """
97 return combine(OR_OPERATOR, FALSE_DOMAIN, TRUE_DOMAIN, domains)
99 def is_operator(element):
100 return isinstance(element, (str, unicode)) and element in [AND_OPERATOR, OR_OPERATOR, NOT_OPERATOR]
102 # TODO change the share wizard to use this function.
103 def is_leaf(element, internal=False):
104 OPS = ('=', '!=', '<>', '<=', '<', '>', '>=', '=?', '=like', '=ilike', 'like', 'not like', 'ilike', 'not ilike', 'in', 'not in', 'child_of')
105 INTERNAL_OPS = OPS + ('inselect',)
106 return (isinstance(element, tuple) or isinstance(element, list)) \
107 and len(element) == 3 \
108 and (((not internal) and element[1] in OPS) \
109 or (internal and element[1] in INTERNAL_OPS))
111 def select_from_where(cr, s, f, w, ids, op):
112 # todo: merge into parent query as sub-query
115 if op in ['<','>','>=','<=']:
116 cr.execute('SELECT "%s" FROM "%s" WHERE "%s" %s %%s' % \
117 (s, f, w, op), (ids[0],)) # TODO shouldn't this be min/max(ids) ?
118 res = [r[0] for r in cr.fetchall()]
119 else: # TODO op is supposed to be 'in'? It is called with child_of...
120 for i in range(0, len(ids), cr.IN_MAX):
121 subids = ids[i:i+cr.IN_MAX]
122 cr.execute('SELECT "%s" FROM "%s" WHERE "%s" IN %%s' % \
123 (s, f, w), (tuple(subids),))
124 res.extend([r[0] for r in cr.fetchall()])
127 def select_distinct_from_where_not_null(cr, s, f):
128 cr.execute('SELECT distinct("%s") FROM "%s" where "%s" is not null' % (s, f, s))
129 return [r[0] for r in cr.fetchall()]
131 class expression(object):
133 parse a domain expression
134 use a real polish notation
135 leafs are still in a ('foo', '=', 'bar') format
136 For more info: http://christophe-simonis-at-tiny.blogspot.com/2008/08/new-new-domain-notation.html
139 def __init__(self, cr, uid, exp, table, context):
140 # check if the expression is valid
142 if not (is_operator(x) or is_leaf(x)):
143 raise ValueError('Bad domain expression: %r, %r is not a valid operator or a valid term.' % (exp, x))
144 self.__field_tables = {} # used to store the table to use for the sql generation. key = index of the leaf
145 self.__all_tables = set()
147 self.__main_table = None # 'root' table. set by parse()
148 # assign self.__exp with the normalized, parsed domain.
149 self.parse(cr, uid, normalize(exp), table, context)
151 # TODO used only for osv_memory
156 def parse(self, cr, uid, exp, table, context):
157 """ transform the leafs of the expression """
160 def child_of_domain(left, right, table, parent=None, prefix=''):
162 if table._parent_store and (not table.pool._init):
163 # TODO: Improve where joins are implemented for many with '.', replace by:
164 # doms += ['&',(prefix+'.parent_left','<',o.parent_right),(prefix+'.parent_left','>=',o.parent_left)]
166 for o in table.browse(cr, uid, ids, context=context):
168 doms.insert(0, OR_OPERATOR)
169 doms += [AND_OPERATOR, ('parent_left', '<', o.parent_right), ('parent_left', '>=', o.parent_left)]
171 return [(left, 'in', table.search(cr, uid, doms, context=context))]
174 def rg(ids, table, parent):
177 ids2 = table.search(cr, uid, [(parent, 'in', ids)], context=context)
178 return ids + rg(ids2, table, parent)
179 return [(left, 'in', rg(ids, table, parent or table._parent_name))]
181 # TODO rename this function as it is not strictly for 'child_of', but also for 'in'...
182 def child_of_right_to_ids(value, operator, field_obj):
183 """ Normalize a single id, or a string, or a list of ids to a list of ids.
185 if isinstance(value, basestring):
186 return [x[0] for x in field_obj.name_search(cr, uid, value, [], operator, context=context, limit=None)]
187 elif isinstance(value, (int, long)):
192 self.__main_table = table
193 self.__all_tables.add(table)
196 while i + 1<len(self.__exp):
199 if is_operator(e) or e == TRUE_LEAF or e == FALSE_LEAF:
201 left, operator, right = e
202 operator = operator.lower()
203 working_table = table # The table containing the field (the name provided in the left operand)
204 fargs = left.split('.', 1)
206 # If the field is _inherits'd, search for the working_table,
207 # and extract the field.
208 if fargs[0] in table._inherit_fields:
210 field = working_table._columns.get(fargs[0])
212 self.__field_tables[i] = working_table
214 next_table = working_table.pool.get(working_table._inherit_fields[fargs[0]][0])
215 if next_table not in self.__all_tables:
216 self.__joins.append('%s.%s=%s.%s' % (next_table._table, 'id', working_table._table, working_table._inherits[next_table._name]))
217 self.__all_tables.add(next_table)
218 working_table = next_table
219 # Or (try to) directly extract the field.
221 field = working_table._columns.get(fargs[0])
224 if left == 'id' and operator == 'child_of':
225 ids2 = child_of_right_to_ids(right, 'ilike', table)
226 dom = child_of_domain(left, ids2, working_table)
227 self.__exp = self.__exp[:i] + dom + self.__exp[i+1:]
230 field_obj = table.pool.get(field._obj)
232 if field._type == 'many2one':
233 right = field_obj.search(cr, uid, [(fargs[1], operator, right)], context=context)
235 self.__exp[i] = FALSE_LEAF
237 self.__exp[i] = (fargs[0], 'in', right)
238 # Making search easier when there is a left operand as field.o2m or field.m2m
239 if field._type in ['many2many', 'one2many']:
240 right = field_obj.search(cr, uid, [(fargs[1], operator, right)], context=context)
241 right1 = table.search(cr, uid, [(fargs[0], 'in', right)], context=context)
243 self.__exp[i] = FALSE_LEAF
245 self.__exp[i] = ('id', 'in', right1)
247 if not isinstance(field, fields.property):
250 if field._properties and not field.store:
251 # this is a function field that is not stored
252 if not field._fnct_search:
253 # the function field doesn't provide a search function and doesn't store
254 # values in the database, so we must ignore it : we generate a dummy leaf
255 self.__exp[i] = TRUE_LEAF
257 subexp = field.search(cr, uid, table, left, [self.__exp[i]], context=context)
259 self.__exp[i] = TRUE_LEAF
261 # we assume that the expression is valid
262 # we create a dummy leaf for forcing the parsing of the resulting expression
263 self.__exp[i] = AND_OPERATOR
264 self.__exp.insert(i + 1, TRUE_LEAF)
265 for j, se in enumerate(subexp):
266 self.__exp.insert(i + 2 + j, se)
267 # else, the value of the field is store in the database, so we search on it
269 elif field._type == 'one2many':
270 # Applying recursivity on field(one2many)
271 if operator == 'child_of':
272 if field._obj != working_table._name:
273 ids2 = child_of_right_to_ids(right, 'ilike', field_obj)
274 dom = child_of_domain(left, ids2, field_obj, prefix=field._obj)
276 ids2 = child_of_right_to_ids(right, 'ilike', field_obj)
277 dom = child_of_domain('id', ids2, working_table, parent=left)
278 self.__exp = self.__exp[:i] + dom + self.__exp[i+1:]
283 if right is not False:
284 if isinstance(right, basestring):
285 ids2 = [x[0] for x in field_obj.name_search(cr, uid, right, [], operator, context=context, limit=None)]
289 if not isinstance(right, list):
294 if operator in ['like','ilike','in','=']:
295 #no result found with given search criteria
297 self.__exp[i] = FALSE_LEAF
301 if operator in ['not like','not ilike','not in','<>','!=']:
303 self.__exp[i] = ('id', o2m_op, select_from_where(cr, field._fields_id, field_obj._table, 'id', ids2, operator))
307 if operator in ['not like','not ilike','not in','<>','!=']:
309 self.__exp[i] = ('id', o2m_op, select_distinct_from_where_not_null(cr, field._fields_id, field_obj._table) or [0])
311 elif field._type == 'many2many':
313 if operator == 'child_of':
314 def _rec_convert(ids):
315 if field_obj == table:
317 return select_from_where(cr, field._id1, field._rel, field._id2, ids, operator)
319 ids2 = child_of_right_to_ids(right, 'ilike', field_obj)
320 dom = child_of_domain('id', ids2, field_obj)
321 ids2 = field_obj.search(cr, uid, dom, context=context)
322 self.__exp[i] = ('id', 'in', _rec_convert(ids2))
325 if right is not False:
326 if isinstance(right, basestring):
327 res_ids = [x[0] for x in field_obj.name_search(cr, uid, right, [], operator, context=context)]
331 if not isinstance(right, list):
336 if operator in ['like','ilike','in','=']:
337 #no result found with given search criteria
338 call_null_m2m = False
339 self.__exp[i] = FALSE_LEAF
341 operator = 'in' # operator changed because ids are directly related to main object
343 call_null_m2m = False
345 if operator in ['not like','not ilike','not in','<>','!=']:
347 self.__exp[i] = ('id', m2m_op, select_from_where(cr, field._id1, field._rel, field._id2, res_ids, operator) or [0])
351 if operator in ['not like','not ilike','not in','<>','!=']:
353 self.__exp[i] = ('id', m2m_op, select_distinct_from_where_not_null(cr, field._id1, field._rel) or [0])
355 elif field._type == 'many2one':
356 if operator == 'child_of':
357 if field._obj != working_table._name:
358 ids2 = child_of_right_to_ids(right, 'ilike', field_obj)
359 dom = child_of_domain(left, ids2, field_obj, prefix=field._obj)
361 ids2 = child_of_right_to_ids(right, 'ilike', field_obj)
362 dom = child_of_domain('id', ids2, working_table, parent=left)
363 self.__exp = self.__exp[:i] + dom + self.__exp[i+1:]
365 def _get_expression(field_obj, cr, uid, left, right, operator, context=None):
369 c['active_test'] = False
370 #Special treatment to ill-formed domains
371 operator = ( operator in ['<','>','<=','>='] ) and 'in' or operator
373 dict_op = {'not in':'!=','in':'=','=':'in','!=':'not in','<>':'not in'}
374 if isinstance(right, tuple):
376 if (not isinstance(right, list)) and operator in ['not in','in']:
377 operator = dict_op[operator]
378 elif isinstance(right, list) and operator in ['<>','!=','=']: #for domain (FIELD,'=',['value1','value2'])
379 operator = dict_op[operator]
380 res_ids = [x[0] for x in field_obj.name_search(cr, uid, right, [], operator, limit=None, context=c)]
384 return (left, 'in', res_ids)
388 if isinstance(right, basestring): # and not isinstance(field, fields.related):
390 elif isinstance(right, (list, tuple)):
393 if not isinstance(ele, basestring):
398 if operator in ('not in', '!=', '<>'):
399 # (many2one not in []) should return all records
400 self.__exp[i] = TRUE_LEAF
402 self.__exp[i] = FALSE_LEAF
405 if operator in ['not like','not ilike','not in','<>','!=']:
407 #Is it ok to put 'left' and not 'id' ?
408 self.__exp[i] = (left, new_op, False)
411 self.__exp[i] = _get_expression(field_obj, cr, uid, left, right, operator, context=context)
414 # add the time part to datetime field when it's not there:
415 if field._type == 'datetime' and self.__exp[i][2] and len(self.__exp[i][2]) == 10:
417 self.__exp[i] = list(self.__exp[i])
419 if operator in ('>', '>='):
420 self.__exp[i][2] += ' 00:00:00'
421 elif operator in ('<', '<='):
422 self.__exp[i][2] += ' 23:59:59'
424 self.__exp[i] = tuple(self.__exp[i])
427 if operator in ('like', 'ilike', 'not like', 'not ilike'):
428 right = '%%%s%%' % right
430 operator = operator == '=like' and 'like' or operator
432 query1 = '( SELECT res_id' \
433 ' FROM ir_translation' \
438 #Covering in,not in operators with operands (%s,%s) ,etc.
439 if operator in ['in','not in']:
440 instr = ','.join(['%s'] * len(right))
441 query1 += ' AND value ' + operator + ' ' +" (" + instr + ")" \
444 ' FROM "' + working_table._table + '"' \
445 ' WHERE "' + left + '" ' + operator + ' ' +" (" + instr + "))"
447 query1 += ' AND value ' + operator + instr + \
450 ' FROM "' + working_table._table + '"' \
451 ' WHERE "' + left + '" ' + operator + instr + ")"
453 query2 = [working_table._name + ',' + left,
454 context.get('lang', False) or 'en_US',
460 self.__exp[i] = ('id', 'inselect', (query1, query2))
462 def __leaf_to_sql(self, leaf, table):
463 left, operator, right = leaf
465 if leaf == TRUE_LEAF:
469 elif leaf == FALSE_LEAF:
473 elif operator == 'inselect':
474 query = '(%s.%s in (%s))' % (table._table, left, right[0])
477 elif operator in ['in', 'not in']:
478 params = right and right[:] or []
479 len_before = len(params)
480 for i in range(len_before)[::-1]:
481 if params[i] == False:
484 len_after = len(params)
485 check_nulls = len_after != len_before
488 # TODO this code seems broken: 'not in [False]' will become 'true',
489 # i.e. 'not null or null', while I expect it to be 'not null'.
492 instr = ','.join(['%s'] * len_after)
494 instr = ','.join([table._columns[left]._symbol_set[0]] * len_after)
495 query = '(%s.%s %s (%s))' % (table._table, left, operator, instr)
497 # the case for [field, 'in', []] or [left, 'not in', []]
499 query = '(%s.%s IS NULL)' % (table._table, left)
501 query = '(%s.%s IS NOT NULL)' % (table._table, left)
503 query = '(%s OR %s.%s IS NULL)' % (query, table._table, left)
505 elif right == False and (left in table._columns) and table._columns[left]._type=="boolean" and (operator == '='):
506 query = '(%s.%s IS NULL or %s.%s = false )' % (table._table, left, table._table, left)
509 elif (((right == False) and (type(right)==bool)) or (right is None)) and (operator == '='):
510 query = '%s.%s IS NULL ' % (table._table, left)
513 elif right == False and (left in table._columns) and table._columns[left]._type=="boolean" and (operator in ['<>', '!=']):
514 query = '(%s.%s IS NOT NULL and %s.%s != false)' % (table._table, left, table._table, left)
517 elif (((right == False) and (type(right)==bool)) or right is None) and (operator in ['<>', '!=']):
518 query = '%s.%s IS NOT NULL' % (table._table, left)
521 elif (operator == '=?'):
522 if (right is False or right is None):
525 elif left in table._columns:
526 format = table._columns[left]._symbol_set[0]
527 query = '(%s.%s = %s)' % (table._table, left, format)
528 params = table._columns[left]._symbol_set[1](right)
530 query = "(%s.%s = '%%s')" % (table._table, left)
534 query = '%s.id %s %%s' % (table._table, operator)
538 like = operator in ('like', 'ilike', 'not like', 'not ilike')
540 op = {'=like':'like','=ilike':'ilike'}.get(operator, operator)
541 if left in table._columns:
542 format = like and '%s' or table._columns[left]._symbol_set[0]
543 query = '(%s.%s %s %s)' % (table._table, left, op, format)
545 query = "(%s.%s %s '%s')" % (table._table, left, op, right)
549 if isinstance(right, str):
551 elif isinstance(right, unicode):
552 str_utf8 = right.encode('utf-8')
554 str_utf8 = str(right)
555 params = '%%%s%%' % str_utf8
556 add_null = not str_utf8
557 elif left in table._columns:
558 params = table._columns[left]._symbol_set[1](right)
561 query = '(%s OR %s.%s IS NULL)' % (query, table._table, left)
563 if isinstance(params, basestring):
565 return (query, params)
571 # Process the domain from right to left, using a stack, to generate a SQL expression.
572 for i, e in reverse_enumerate(self.__exp):
573 if is_leaf(e, internal=True):
574 table = self.__field_tables.get(i, self.__main_table)
575 q, p = self.__leaf_to_sql(e, table)
579 if e == NOT_OPERATOR:
580 stack.append('(NOT (%s))' % (stack.pop(),))
582 ops = {AND_OPERATOR: ' AND ', OR_OPERATOR: ' OR '}
585 stack.append('(%s %s %s)' % (q1, ops[e], q2,))
587 assert len(stack) == 1
589 joins = ' AND '.join(self.__joins)
591 query = '(%s) AND %s' % (joins, query)
592 return (query, flatten(params))
594 def get_tables(self):
595 return ['"%s"' % t._table for t in self.__all_tables]
597 # vim:expandtab:smartindent:tabstop=4:softtabstop=4:shiftwidth=4: