# sql/elements.py
# Copyright (C) 2005-2018 the SQLAlchemy authors and contributors
# <see AUTHORS file>
#
# This module is part of SQLAlchemy and is released under
# the MIT License: http://www.opensource.org/licenses/mit-license.php

"""Core SQL expression elements, including :class:`.ClauseElement`,
:class:`.ColumnElement`, and derived classes.

"""

from __future__ import unicode_literals

from .. import util, exc, inspection
from . import type_api
from . import operators
from .visitors import Visitable, cloned_traverse, traverse
from .annotation import Annotated
import itertools
from .base import Executable, PARSE_AUTOCOMMIT, Immutable, NO_ARG
from .base import _generative
import numbers

import re
import operator


def _clone(element, **kw):
    return element._clone()


def collate(expression, collation):
    """Return the clause ``expression COLLATE collation``.

    e.g.::

        collate(mycolumn, 'utf8_bin')

    produces::

        mycolumn COLLATE utf8_bin

    The collation expression is also quoted if it is a case sensitive
    identifier, e.g. contains uppercase characters.

    .. versionchanged:: 1.2 quoting is automatically applied to COLLATE
       expressions if they are case sensitive.

    """

    expr = _literal_as_binds(expression)
    return BinaryExpression(
        expr,
        CollationClause(collation),
        operators.collate, type_=expr.type)


def between(expr, lower_bound, upper_bound, symmetric=False):
    """Produce a ``BETWEEN`` predicate clause.

    E.g.::

        from sqlalchemy import between
        stmt = select([users_table]).where(between(users_table.c.id, 5, 7))

    Would produce SQL resembling::

        SELECT id, name FROM user WHERE id BETWEEN :id_1 AND :id_2

    The :func:`.between` function is a standalone version of the
    :meth:`.ColumnElement.between` method available on all
    SQL expressions, as in::

        stmt = select([users_table]).where(users_table.c.id.between(5, 7))

    All arguments passed to :func:`.between`, including the left side
    column expression, are coerced from Python scalar values if a
    the value is not a :class:`.ColumnElement` subclass.   For example,
    three fixed values can be compared as in::

        print(between(5, 3, 7))

    Which would produce::

        :param_1 BETWEEN :param_2 AND :param_3

    :param expr: a column expression, typically a :class:`.ColumnElement`
     instance or alternatively a Python scalar expression to be coerced
     into a column expression, serving as the left side of the ``BETWEEN``
     expression.

    :param lower_bound: a column or Python scalar expression serving as the
     lower bound of the right side of the ``BETWEEN`` expression.

    :param upper_bound: a column or Python scalar expression serving as the
     upper bound of the right side of the ``BETWEEN`` expression.

    :param symmetric: if True, will render " BETWEEN SYMMETRIC ". Note
     that not all databases support this syntax.

     .. versionadded:: 0.9.5

    .. seealso::

        :meth:`.ColumnElement.between`

    """
    expr = _literal_as_binds(expr)
    return expr.between(lower_bound, upper_bound, symmetric=symmetric)


def literal(value, type_=None):
    r"""Return a literal clause, bound to a bind parameter.

    Literal clauses are created automatically when non-
    :class:`.ClauseElement` objects (such as strings, ints, dates, etc.) are
    used in a comparison operation with a :class:`.ColumnElement` subclass,
    such as a :class:`~sqlalchemy.schema.Column` object.  Use this function
    to force the generation of a literal clause, which will be created as a
    :class:`BindParameter` with a bound value.

    :param value: the value to be bound. Can be any Python object supported by
        the underlying DB-API, or is translatable via the given type argument.

    :param type\_: an optional :class:`~sqlalchemy.types.TypeEngine` which
        will provide bind-parameter translation for this literal.

    """
    return BindParameter(None, value, type_=type_, unique=True)




def outparam(key, type_=None):
    """Create an 'OUT' parameter for usage in functions (stored procedures),
    for databases which support them.

    The ``outparam`` can be used like a regular function parameter.
    The "output" value will be available from the
    :class:`~sqlalchemy.engine.ResultProxy` object via its ``out_parameters``
    attribute, which returns a dictionary containing the values.

    """
    return BindParameter(
        key, None, type_=type_, unique=False, isoutparam=True)


def not_(clause):
    """Return a negation of the given clause, i.e. ``NOT(clause)``.

    The ``~`` operator is also overloaded on all
    :class:`.ColumnElement` subclasses to produce the
    same result.

    """
    return operators.inv(_literal_as_binds(clause))


@inspection._self_inspects
class ClauseElement(Visitable):
    """Base class for elements of a programmatically constructed SQL
    expression.

    """
    __visit_name__ = 'clause'

    _annotations = {}
    supports_execution = False
    _from_objects = []
    bind = None
    _is_clone_of = None
    is_selectable = False
    is_clause_element = True

    description = None
    _order_by_label_element = None
    _is_from_container = False

    def _clone(self):
        """Create a shallow copy of this ClauseElement.

        This method may be used by a generative API.  Its also used as
        part of the "deep" copy afforded by a traversal that combines
        the _copy_internals() method.

        """
        c = self.__class__.__new__(self.__class__)
        c.__dict__ = self.__dict__.copy()
        ClauseElement._cloned_set._reset(c)
        ColumnElement.comparator._reset(c)

        # this is a marker that helps to "equate" clauses to each other
        # when a Select returns its list of FROM clauses.  the cloning
        # process leaves around a lot of remnants of the previous clause
        # typically in the form of column expressions still attached to the
        # old table.
        c._is_clone_of = self

        return c

    @property
    def _constructor(self):
        """return the 'constructor' for this ClauseElement.

        This is for the purposes for creating a new object of
        this type.   Usually, its just the element's __class__.
        However, the "Annotated" version of the object overrides
        to return the class of its proxied element.

        """
        return self.__class__

    @util.memoized_property
    def _cloned_set(self):
        """Return the set consisting all cloned ancestors of this
        ClauseElement.

        Includes this ClauseElement.  This accessor tends to be used for
        FromClause objects to identify 'equivalent' FROM clauses, regardless
        of transformative operations.

        """
        s = util.column_set()
        f = self
        while f is not None:
            s.add(f)
            f = f._is_clone_of
        return s

    def __getstate__(self):
        d = self.__dict__.copy()
        d.pop('_is_clone_of', None)
        return d

    def _annotate(self, values):
        """return a copy of this ClauseElement with annotations
        updated by the given dictionary.

        """
        return Annotated(self, values)

    def _with_annotations(self, values):
        """return a copy of this ClauseElement with annotations
        replaced by the given dictionary.

        """
        return Annotated(self, values)

    def _deannotate(self, values=None, clone=False):
        """return a copy of this :class:`.ClauseElement` with annotations
        removed.

        :param values: optional tuple of individual values
         to remove.

        """
        if clone:
            # clone is used when we are also copying
            # the expression for a deep deannotation
            return self._clone()
        else:
            # if no clone, since we have no annotations we return
            # self
            return self

    def _execute_on_connection(self, connection, multiparams, params):
        if self.supports_execution:
            return connection._execute_clauseelement(self, multiparams, params)
        else:
            raise exc.ObjectNotExecutableError(self)

    def unique_params(self, *optionaldict, **kwargs):
        """Return a copy with :func:`bindparam()` elements replaced.

        Same functionality as ``params()``, except adds `unique=True`
        to affected bind parameters so that multiple statements can be
        used.

        """
        return self._params(True, optionaldict, kwargs)

    def params(self, *optionaldict, **kwargs):
        """Return a copy with :func:`bindparam()` elements replaced.

        Returns a copy of this ClauseElement with :func:`bindparam()`
        elements replaced with values taken from the given dictionary::

          >>> clause = column('x') + bindparam('foo')
          >>> print clause.compile().params
          {'foo':None}
          >>> print clause.params({'foo':7}).compile().params
          {'foo':7}

        """
        return self._params(False, optionaldict, kwargs)

    def _params(self, unique, optionaldict, kwargs):
        if len(optionaldict) == 1:
            kwargs.update(optionaldict[0])
        elif len(optionaldict) > 1:
            raise exc.ArgumentError(
                "params() takes zero or one positional dictionary argument")

        def visit_bindparam(bind):
            if bind.key in kwargs:
                bind.value = kwargs[bind.key]
                bind.required = False
            if unique:
                bind._convert_to_unique()
        return cloned_traverse(self, {}, {'bindparam': visit_bindparam})

    def compare(self, other, **kw):
        r"""Compare this ClauseElement to the given ClauseElement.

        Subclasses should override the default behavior, which is a
        straight identity comparison.

        \**kw are arguments consumed by subclass compare() methods and
        may be used to modify the criteria for comparison.
        (see :class:`.ColumnElement`)

        """
        return self is other

    def _copy_internals(self, clone=_clone, **kw):
        """Reassign internal elements to be clones of themselves.

        Called during a copy-and-traverse operation on newly
        shallow-copied elements to create a deep copy.

        The given clone function should be used, which may be applying
        additional transformations to the element (i.e. replacement
        traversal, cloned traversal, annotations).

        """
        pass

    def get_children(self, **kwargs):
        r"""Return immediate child elements of this :class:`.ClauseElement`.

        This is used for visit traversal.

        \**kwargs may contain flags that change the collection that is
        returned, for example to return a subset of items in order to