Exemple #1
0
    def generate_table_alias(self, src_table_alias, joined_tables=[]):
        """ Generate a standard table alias name. An alias is generated as following:
            - the base is the source table name (that can already be an alias)
            - then, each joined table is added in the alias using a 'link field
              name'
              that is used to render unique aliases for a given path
            - returns a tuple composed of the alias, and the full table alias
              to be
              added in a from condition with quoting done
            Examples:
            - src_table_alias='res_users', join_tables=[]:
                alias = ('res_users','"res_users"')
            - src_model='res_users', join_tables=[(res.partner, 'parent_id')]
                alias = ('res_users__parent_id', '"res_partner" as
                    "res_users__parent_id"')

            :param model src_table_alias: model source of the alias
            :param list joined_tables: list of tuples
                                       (dst_model, link_field, option)

            :return tuple: (table_alias, alias statement for from clause with
                quotes added)
        """
        if not joined_tables:
            return ('%s' % src_table_alias, '%s' % _quote(src_table_alias))

        alias_ref = src_table_alias

        for link in joined_tables:
            alias_ref += '__' + link[1]

        alias_dict = self.table_aliases

        if alias_ref not in alias_dict:
            last_table = joined_tables[-1][0]

            if len(alias_ref) >= 64:
                # Create a smaller alias for the table
                # Exemple: 'res_users__1'
                alias_int = self.next_alias_int
                self.next_alias_int += 1
                alias = '%s__%s' % (last_table, alias_int)
            else:
                alias = alias_ref

            alias_dict[alias_ref] = (
                '%s' % alias,
                '%s as %s' % (_quote(last_table), _quote(alias)))

        return alias_dict[alias_ref]
Exemple #2
0
    def decorate_leaf_to_sql(self, eleaf):
        model = eleaf.model
        leaf = eleaf.leaf
        left, operator, right = leaf
        table_alias = '"%s"' % (eleaf.generate_alias())

        if operator == '%':
            sql_operator = '%%'
            params = []

            if left in model._columns:
                formats = model._columns[left]._symbol_set[0]
                column = '%s.%s' % (table_alias, expression._quote(left))
                query = '(%s %s %s)' % (column, sql_operator, formats)
            elif left in expression.MAGIC_COLUMNS:
                query = "(%s.\"%s\" %s %%s)" % (table_alias, left,
                                                sql_operator)
                params = right
            else:  # Must not happen
                raise ValueError("Invalid field %r in domain term %r" %
                                 (left, leaf))

            if left in model._columns:
                params = model._columns[left]._symbol_set[1](right)

            if isinstance(params, basestring):
                params = [params]
            return query, params
        elif operator == 'inselect':
            right = (right[0].replace(' % ', ' %% '), right[1])
            eleaf.leaf = (left, operator, right)
        return method(self, eleaf)
Exemple #3
0
 def get_tables(self):
     """ Returns the list of tables for SQL queries, like select from ...
     """
     tables = []
     for leaf in self.result:
         for table in leaf.get_tables():
             if table not in tables:
                 tables.append(table)
     table_name = _quote(self.root_model._table)
     if table_name not in tables:
         tables.append(table_name)
     return tables
Exemple #4
0
def generate_table_alias(src_table_alias, joined_tables=[]):
    """ Generate a standard table alias name. An alias is generated as following:
        - the base is the source table name (that can already be an alias)
        - then, each joined table is added in the alias using a 'link field
          name'
          that is used to render unique aliases for a given path
        - returns a tuple composed of the alias, and the full table alias to be
          added in a from condition with quoting done
        Examples:
        - src_table_alias='res_users', join_tables=[]:
            alias = ('res_users','"res_users"')
        - src_model='res_users', join_tables=[(res.partner, 'parent_id')]
            alias = ('res_users__parent_id', '"res_partner" as
                "res_users__parent_id"')

        :param model src_table_alias: model source of the alias
        :param list joined_tables: list of tuples
                                   (dst_model, link_field)

        :return tuple: (table_alias, alias statement for from clause with
            quotes added)
    """
    alias = src_table_alias

    if not joined_tables:
        return '%s' % alias, '%s' % _quote(alias)

    for link in joined_tables:
        alias += '__' + link[1]

    assert len(alias) < 64, (
        'Table alias name %s is longer than the 64 characters '
        'sizeaccepted by default in postgresql.' % alias
    )
    return '%s' % alias, '%s as %s' % (
        _quote(joined_tables[-1][0]), _quote(alias))
Exemple #5
0
    def __leaf_to_sql(self, eleaf):
        model = eleaf.model
        leaf = eleaf.leaf
        left, operator, right = leaf

        # final sanity checks - should never fail
        assert operator in (TERM_OPERATORS + ('inselect', 'not inselect')), \
            "Invalid operator %r in domain term %r" % (operator, leaf)
        assert (
            leaf in (TRUE_LEAF, FALSE_LEAF) or
            left in model._fields or
            left in MAGIC_COLUMNS), (
            "Invalid field %r in domain term %r" % (left, leaf)
        )
        assert not isinstance(right, BaseModel), \
            "Invalid value %r in domain term %r" % (right, leaf)

        table_alias = '"%s"' % (eleaf.generate_alias())

        if leaf == TRUE_LEAF:
            query = 'TRUE'
            params = []

        elif leaf == FALSE_LEAF:
            query = 'FALSE'
            params = []

        elif operator == 'inselect':
            query = '(%s."%s" in (%s))' % (table_alias, left, right[0])
            params = right[1]

        elif operator == 'not inselect':
            query = '(%s."%s" not in (%s))' % (table_alias, left, right[0])
            params = right[1]

        elif operator in ['in', 'not in']:
            # Two cases: right is a boolean or a list. The boolean case is an
            # abuse and handled for backward compatibility.
            if isinstance(right, bool):
                _logger.warning(
                    "The domain term '%s' should use the '=' or "
                    "'!=' operator." %
                    (leaf,))
                if operator == 'in':
                    r = 'NOT NULL' if right else 'NULL'
                else:
                    r = 'NULL' if right else 'NOT NULL'
                query = '(%s."%s" IS %s)' % (table_alias, left, r)
                params = []
            elif isinstance(right, (list, tuple)):
                params = list(right)
                check_nulls = False
                for i in range(len(params))[::-1]:
                    if params[i] is False:
                        check_nulls = True
                        del params[i]

                if params:
                    if left == 'id':
                        instr = ','.join(['%s'] * len(params))
                    else:
                        ss = model._columns[left]._symbol_set
                        instr = ','.join([ss[0]] * len(params))
                        params = map(ss[1], params)
                    query = '(%s."%s" %s (%s))' % (
                        table_alias, left, operator, instr)
                else:
                    # The case for (left, 'in', []) or (left, 'not in', []).
                    query = 'FALSE' if operator == 'in' else 'TRUE'

                if check_nulls and operator == 'in':
                    query = '(%s OR %s."%s" IS NULL)' % (
                        query, table_alias, left)
                elif not check_nulls and operator == 'not in':
                    query = '(%s OR %s."%s" IS NULL)' % (
                        query, table_alias, left)
                elif check_nulls and operator == 'not in':
                    # needed only for TRUE.
                    query = '(%s AND %s."%s" IS NOT NULL)' % (
                        query, table_alias, left)
            else:  # Must not happen
                raise ValueError("Invalid domain term %r" % (leaf,))

        elif (
            right is False and
            (left in model._columns) and
            model._columns[left]._type == "boolean" and
            (operator == '=')
        ):
            query = '(%s."%s" IS NULL or %s."%s" = false )' % (
                table_alias, left, table_alias, left)
            params = []

        elif (right is False or right is None) and (operator == '='):
            query = '%s."%s" IS NULL ' % (table_alias, left)
            params = []

        elif (
            right is False and (left in model._columns) and
            model._columns[left]._type == "boolean" and
            (operator == '!=')
        ):
            query = '(%s."%s" IS NOT NULL and %s."%s" != false)' % (
                table_alias, left, table_alias, left)
            params = []

        elif (right is False or right is None) and (operator == '!='):
            query = '%s."%s" IS NOT NULL' % (table_alias, left)
            params = []

        elif operator == '=?':
            if right is False or right is None:
                # '=?' is a short-circuit that makes the term TRUE if right is
                # None or False
                query = 'TRUE'
                params = []
            else:
                # '=?' behaves like '=' in other cases
                query, params = self.__leaf_to_sql(
                    self.create_substitution_leaf(
                        eleaf, (left, '=', right), model))

        elif left == 'id':
            query = '%s.id %s %%s' % (table_alias, operator)
            params = right

        else:
            need_wildcard = operator in (
                'like', 'ilike', 'not like', 'not ilike')
            sql_operator = {'=like': 'like', '=ilike': 'ilike'}.get(
                operator, operator)
            cast = '::text' if sql_operator.endswith('like') else ''

            if left in model._columns:
                format = need_wildcard and '%s' or model._columns[
                    left]._symbol_set[0]
                unaccent = self._unaccent if sql_operator.endswith(
                    'like') else lambda x: x
                column = '%s.%s' % (table_alias, _quote(left))
                query = '(%s %s %s)' % (
                    unaccent(column + cast), sql_operator, unaccent(format))
            elif left in MAGIC_COLUMNS:
                query = "(%s.\"%s\"%s %s %%s)" % (
                    table_alias, left, cast, sql_operator)
                params = right
            else:  # Must not happen
                raise ValueError(
                    "Invalid field %r in domain term %r" % (left, leaf))

            add_null = False
            if need_wildcard:
                if isinstance(right, str):
                    str_utf8 = right
                elif isinstance(right, unicode):
                    str_utf8 = right.encode('utf-8')
                else:
                    str_utf8 = str(right)
                params = '%%%s%%' % str_utf8
                add_null = not str_utf8
            elif left in model._columns:
                params = model._columns[left]._symbol_set[1](right)

            if add_null:
                query = '(%s OR %s."%s" IS NULL)' % (query, table_alias, left)

        if isinstance(params, basestring):
            params = [params]

        return query, params
Exemple #6
0
    def parse(self, cr, uid, context):
        """ Transform the leaves of the expression

            The principle is to pop elements from a leaf stack one at a time.
            Each leaf is processed. The processing is a if/elif list of various
            cases that appear in the leafs (many2one, function fields, ...).
            Two things can happen as a processing result:
            - the leaf has been modified and/or new leafs have to be introduced
              in the expression; they are pushed into the leaf stack, to be
              processed right after
            - the leaf is added to the result

            Some internal var explanation:
                :var list path: left operand seen as a sequence of field names
                    ("foo.bar" -> ["foo", "bar"])
                :var obj model: model object, model containing the field
                    (the name provided in the left operand)
                :var obj field: the field corresponding to `path[0]`
                :var obj column: the column corresponding to `path[0]`
                :var obj comodel: relational model of field (field.comodel)
                    (res_partner.bank_ids -> res.partner.bank)
        """

        self.result = []
        self.stack = [ExtendedLeaf(self, leaf, self.root_model)
                      for leaf in self.expression]

        # process from right to left; expression is from left to right
        self.stack.reverse()

        while self.stack:
            # Get the next leaf to process
            leaf = self.pop()

            left = leaf.left
            right = leaf.right
            operator = leaf.operator
            path = leaf.path

            model = leaf.model
            field = model._fields.get(path[0])
            column = model._columns.get(path[0])
            comodel = model.pool.get(getattr(field, 'comodel_name', None))

            # ----------------------------------------
            # SIMPLE CASE
            # 1. leaf is an operator
            # 2. leaf is a true/false leaf
            # -> add directly to result
            # ----------------------------------------

            if leaf.is_operator() or leaf.is_true_leaf(
            ) or leaf.is_false_leaf():
                self.push_result(leaf)

            # ----------------------------------------
            # FIELD NOT FOUND
            # -> from inherits'd fields -> work on the related model, and add
            #    a join condition
            # -> ('id', 'child_of', '..') -> use a 'to_ids'
            # -> but is one on the _log_access special fields, add directly to
            #    result
            #    TODO: make these fields explicitly available in self.columns
            #    instead!
            # -> else: crash
            # ----------------------------------------

            elif not column and path[0] in model._inherit_fields:
                # comments about inherits'd fields
                #  { 'field_name': ('parent_model', 'm2o_field_to_reach_parent'
                # field_column_obj, origina_parent_model), ... }
                next_model = model.pool[model._inherit_fields[path[0]][0]]
                leaf.add_join_context(
                    next_model, model._inherits[
                        next_model._name], 'id', model._inherits[
                        next_model._name])
                self.push(leaf)

            elif left == 'id' and operator == 'child_of':
                ids2 = self.to_ids(right, model)
                dom = self.child_of_domain(left, ids2, model)
                for dom_leaf in reversed(dom):
                    self.push_new_leaf(leaf, dom_leaf, model)

            elif not column and path[0] in MAGIC_COLUMNS:
                self.push_result(leaf)

            elif not field:
                raise ValueError(
                    "Invalid field %r in leaf %r" % (left, str(leaf)))

            # ----------------------------------------
            # PATH SPOTTED
            # -> many2one or one2many with _auto_join:
            #    - add a join, then jump into linked column: column.remaining
            #      on
            #      src_table is replaced by remaining on dst_table, and set for
            #      re-evaluation
            #    - if a domain is defined on the column, add it into evaluation
            #      on the relational table
            # -> many2one, many2many, one2many: replace by an equivalent
            #      computed
            #    domain, given by recursively searching on the remaining of the
            #      path
            # -> note: hack about columns.property should not be necessary
            #      anymore
            #    as after transforming the column, it will go through this loop
            #      once again
            # ----------------------------------------

            elif column._type == 'one2many':
                parser = LeafParserOne2many(leaf, column)
                parser.parse()

            elif (
                len(path) > 1 and
                column._type == 'many2one' and
                is_stored_column(column)
            ):
                # res_partner.state_id = res_partner__state_id.id
                leaf.add_join_context(comodel, path[0], 'id', path[0])
                self.push_new_leaf(leaf, (path[1], operator, right), comodel)

            elif (
                len(path) > 1 and
                column._type == 'many2many' and
                is_stored_column(column)
            ):
                leaf.add_join_context_m2m(
                    comodel,
                    column._rel,
                    column._id1,
                    column._id2,
                    path[0],
                )

                self.push_new_leaf(
                    leaf, (path[1], operator, right), comodel)

            elif len(path) > 1 and column._type == 'many2one':
                # Many2one not stored fields
                right_ids = comodel.search(
                    cr, uid, [(path[1], operator, right)], context=context)
                leaf.leaf = (path[0], 'in', right_ids)
                self.push(leaf)

            elif len(path) > 1 and column._type == 'many2many':
                # One2many not stored fields
                right_ids = comodel.search(
                    cr, uid, [(path[1], operator, right)], context=context)
                table_ids = model.search(
                    cr, uid, [(path[0], 'in', right_ids)],
                    context=dict(context, active_test=False))
                leaf.leaf = ('id', 'in', table_ids)
                self.push(leaf)

            elif not column:
                # Non-stored field should provide an implementation of search.
                if not field.search:
                    # field does not support search!
                    _logger.error(
                        "Non-stored field %s cannot be searched.", field)
                    if _logger.isEnabledFor(logging.DEBUG):
                        _logger.debug(''.join(traceback.format_stack()))
                    # Ignore it: generate a dummy leaf.
                    domain = []
                else:
                    # Let the field generate a domain.
                    recs = model.browse(cr, uid, [], context)
                    domain = field.determine_domain(recs, operator, right)

                if not domain:
                    leaf.leaf = TRUE_LEAF
                    self.push(leaf)
                else:
                    for elem in reversed(domain):
                        self.push_new_leaf(leaf, elem, model)

            # -------------------------------------------------
            # FUNCTION FIELD
            # -> not stored: error if no _fnct_search, otherwise handle
            # the result domain
            # -> stored: management done in the remaining of parsing
            # -------------------------------------------------

            elif isinstance(column, fields.function) and not column.store:
                # this is a function field that is not stored
                if not column._fnct_search:
                    _logger.error(
                        "Field '%s' (%s) can not be searched: "
                        "non-stored function field without fnct_search",
                        column.string, left)
                    # avoid compiling stack trace if not needed
                    if _logger.isEnabledFor(logging.DEBUG):
                        _logger.debug(''.join(traceback.format_stack()))
                    # ignore it: generate a dummy leaf
                    fct_domain = []
                else:
                    fct_domain = column.search(
                        cr, uid, model, left, [leaf.leaf], context=context)

                if not fct_domain:
                    leaf.leaf = TRUE_LEAF
                    self.push(leaf)
                else:
                    # we assume that the expression is valid
                    # we create a dummy leaf for forcing the parsing of the
                    # resulting expression
                    for domain_element in reversed(fct_domain):
                        self.push_new_leaf(leaf, domain_element, model)

            # -------------------------------------------------
            # RELATIONAL FIELDS
            # -------------------------------------------------

            # Applying recursivity on field(one2many)
            elif column._type == 'one2many' and operator == 'child_of':
                ids2 = self.to_ids(right, comodel)
                if column._obj != model._name:
                    dom = self.child_of_domain(left, ids2, comodel)
                else:
                    dom = self.child_of_domain('id', ids2, model, parent=left)
                for dom_leaf in reversed(dom):
                    self.push_new_leaf(leaf, dom_leaf, model)

            elif column._type == 'many2many':
                rel_table, rel_id1, rel_id2 = column._sql_names(model)
                # FIXME
                if operator == 'child_of':
                    def _rec_convert(ids):
                        if comodel == model:
                            return ids
                        return select_from_where(
                            cr, rel_id1, rel_table, rel_id2, ids, operator)

                    ids2 = self.to_ids(right, comodel)
                    dom = self.child_of_domain('id', ids2, comodel)
                    ids2 = comodel.search(cr, uid, dom, context=context)
                    self.push_new_leaf(
                        leaf, ('id', 'in', _rec_convert(ids2)), model)
                else:
                    call_null_m2m = True
                    if right is not False:
                        if isinstance(right, basestring):
                            res_ids = [x[0] for x in comodel.name_search(
                                cr, uid, right, [], operator, context=context)]
                            if res_ids:
                                operator = 'in'
                        else:
                            if not isinstance(right, list):
                                res_ids = [right]
                            else:
                                res_ids = right
                        if not res_ids:
                            if operator in ['like', 'ilike', 'in', '=']:
                                # no result found with given search criteria
                                call_null_m2m = False
                                self.push_new_leaf(leaf, FALSE_LEAF, model)
                            else:
                                # operator changed because ids are directly
                                # related to main object
                                operator = 'in'
                        else:
                            call_null_m2m = False
                            m2m_op = (
                                'not in' if operator in NEGATIVE_TERM_OPERATORS
                                else 'in')
                            self.push_new_leaf(
                                leaf, (
                                    'id',
                                    m2m_op,
                                    select_from_where(
                                        cr,
                                        rel_id1,
                                        rel_table,
                                        rel_id2,
                                        res_ids,
                                        operator
                                    ) or [0]),
                                model)

                    if call_null_m2m:
                        m2m_op = (
                            'in' if operator in NEGATIVE_TERM_OPERATORS
                            else 'not in')
                        self.push_new_leaf(
                            leaf, (
                                'id',
                                m2m_op,
                                select_distinct_from_where_not_null(
                                    cr, rel_id1, rel_table)
                            ), model)

            elif column._type == 'many2one':
                if operator == 'child_of':
                    ids2 = self.to_ids(right, comodel)
                    if column._obj != model._name:
                        dom = self.child_of_domain(left, ids2, comodel)
                    else:
                        dom = self.child_of_domain(
                            'id', ids2, model, parent=left)
                    for dom_leaf in reversed(dom):
                        self.push_new_leaf(leaf, dom_leaf, model)
                else:

                    # resolve string-based m2o criterion into IDs
                    if isinstance(
                        right, basestring) or right and isinstance(
                        right, (tuple, list)) and all(
                        isinstance(
                            item, basestring) for item in right):
                        self.push_new_leaf(
                            leaf,
                            self._get_many2one_expression(
                                comodel, left, right, operator),
                            model)
                    else:
                        # right == [] or right == False and all other cases are
                        # handled by __leaf_to_sql()
                        self.push_result(leaf)

            # -------------------------------------------------
            # OTHER FIELDS
            # -> datetime fields: manage time part of the datetime
            #    column when it is not there
            # -> manage translatable fields
            # -------------------------------------------------

            else:
                if column._type == 'datetime' and right and len(right) == 10:
                    if operator in ('>', '<='):
                        right += ' 23:59:59'
                    else:
                        right += ' 00:00:00'
                    self.push_new_leaf(leaf, (left, operator, right), model)

                elif column.translate and right:
                    need_wildcard = operator in (
                        'like', 'ilike', 'not like', 'not ilike')
                    sql_operator = {'=like': 'like', '=ilike': 'ilike'}.get(
                        operator, operator)
                    if need_wildcard:
                        right = '%%%s%%' % right

                    inselect_operator = 'inselect'
                    if sql_operator in NEGATIVE_TERM_OPERATORS:
                        # negate operator (fix lp:1071710)
                        sql_operator = sql_operator[
                            4:] if sql_operator[:3] == 'not' else '='
                        inselect_operator = 'not inselect'

                    unaccent = self._unaccent if sql_operator.endswith(
                        'like') else lambda x: x

                    instr = unaccent('%s')

                    if sql_operator == 'in':
                        # params will be flatten by to_sql() => expand the
                        # placeholders
                        instr = '(%s)' % ', '.join(['%s'] * len(right))

                    subselect = """WITH temp_irt_current (id, name) as (
                            SELECT ct.id, coalesce(it.value,ct.{quote_left})
                            FROM {current_table} ct
                            LEFT JOIN ir_translation it ON (it.name = %s and
                                        it.lang = %s and
                                        it.type = %s and
                                        it.res_id = ct.id and
                                        it.value != '')
                            )
                            SELECT id FROM temp_irt_current
                            WHERE {name} {operator} {right} order by name
                            """.format(
                                current_table=model._table,
                                quote_left=_quote(left),
                                name=unaccent('name'),
                                operator=sql_operator,
                                right=instr)

                    params = (
                        model._name + ',' + left,
                        context.get('lang') or 'en_US',
                        'model',
                        right,
                    )
                    self.push_new_leaf(
                        leaf,
                        ('id', inselect_operator, (subselect, params)),
                        model)

                else:
                    self.push_result(leaf)

        # ----------------------------------------
        # END OF PARSING FULL DOMAIN
        # -> generate joins
        # ----------------------------------------

        joins = set()
        for leaf in self.result:
            joins |= set(leaf.get_join_conditions())
        self.joins = list(joins)