# copyright 2003-2013 LOGILAB S.A. (Paris, FRANCE), all rights reserved.

# contact http://www.logilab.fr/ -- mailto:contact@logilab.fr

#

# This file is part of CubicWeb.

#

# CubicWeb is free software: you can redistribute it and/or modify it under the

# terms of the GNU Lesser General Public License as published by the Free

# Software Foundation, either version 2.1 of the License, or (at your option)

# any later version.

#

# CubicWeb is distributed in the hope that it will be useful, but WITHOUT

# ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS

# FOR A PARTICULAR PURPOSE.  See the GNU Lesser General Public License for more

# details.

#

# You should have received a copy of the GNU Lesser General Public License along

# with CubicWeb.  If not, see <http://www.gnu.org/licenses/>.

"""RQL to SQL generator for native sources.

SQL queries optimization

~~~~~~~~~~~~~~~~~~~~~~~~

1. CWUser X WHERE X in_group G, G name 'users':

   CWUser is the only subject entity type for the in_group relation,

   which allow us to do ::

     SELECT eid_from FROM in_group, CWGroup

     WHERE in_group.eid_to = CWGroup.eid_from

     AND CWGroup.name = 'users'

2. Any X WHERE X nonfinal1 Y, Y nonfinal2 Z

   -> direct join between nonfinal1 and nonfinal2, whatever X,Y, Z (unless

      inlined...)

      NOT IMPLEMENTED (and quite hard to implement)

Potential optimization information is collected by the querier, sql generation

is done according to this information

cross RDMS note : read `Comparison of different SQL implementations`_

by Troels Arvin. Features SQL ISO Standard, PG, mysql, Oracle, MS SQL, DB2

and Informix.

.. _Comparison of different SQL implementations: http://www.troels.arvin.dk/db/rdbms

"""

__docformat__ = "restructuredtext en"

import threading

from datetime import datetime, time

from logilab.common.date import utcdatetime, utctime

from logilab.database import FunctionDescr, SQL_FUNCTIONS_REGISTRY

from rql import BadRQLQuery, CoercionError

from rql.utils import common_parent

from rql.stmts import Union, Select

from rql.nodes import (SortTerm, VariableRef, Constant, Function, Variable, Or,

                       Not, Comparison, ColumnAlias, Relation, SubQuery, Exists)

from cubicweb import QueryError

from cubicweb.rqlrewrite import cleanup_solutions

from cubicweb.server.sqlutils import SQL_PREFIX

ColumnAlias._q_invariant = False # avoid to check for ColumnAlias / Variable

FunctionDescr.source_execute = None

def default_update_cb_stack(self, stack):

    stack.append(self.source_execute)

FunctionDescr.update_cb_stack = default_update_cb_stack

get_func_descr = SQL_FUNCTIONS_REGISTRY.get_function

LENGTH = get_func_descr('LENGTH')

def length_source_execute(source, session, value):

    return len(value.getvalue())

LENGTH.source_execute = length_source_execute

def _new_var(select, varname):

    newvar = select.get_variable(varname)

    if not 'relations' in newvar.stinfo:

        # not yet initialized

        newvar.prepare_annotation()

        newvar.stinfo['scope'] = select

        newvar._q_invariant = False

        select.selection.append(VariableRef(newvar))

    return newvar

def _fill_to_wrap_rel(var, newselect, towrap, schema):

    for rel in var.stinfo['relations'] - var.stinfo['rhsrelations']:

        rschema = schema.rschema(rel.r_type)

        if rschema.inlined:

            towrap.add( (var, rel) )

            for vref in rel.children[1].iget_nodes(VariableRef):

                newivar = _new_var(newselect, vref.name)

                _fill_to_wrap_rel(vref.variable, newselect, towrap, schema)

        elif rschema.final:

            towrap.add( (var, rel) )

            for vref in rel.children[1].iget_nodes(VariableRef):

                newivar = _new_var(newselect, vref.name)

                newivar.stinfo['attrvar'] = (var, rel.r_type)

def rewrite_unstable_outer_join(select, solutions, unstable, schema):

    """if some optional variables are unstable, they should be selected in a

    subquery. This function check this and rewrite the rql syntax tree if

    necessary (in place). Return a boolean telling if the tree has been modified

    """

    modified = False

    for varname in tuple(unstable):

        var = select.defined_vars[varname]

        if not var.stinfo.get('optrelations'):

            continue

        unstable.remove(varname)

        newselect = Select()

        newselect.need_distinct = False

        myunion = Union()

        myunion.append(newselect)

        # extract aliases / selection

        newvar = _new_var(newselect, var.name)

        newselect.selection = [VariableRef(newvar)]

        towrap_rels = set()

        _fill_to_wrap_rel(var, newselect, towrap_rels, schema)

        # extract relations

        for var, rel in towrap_rels:

            newrel = rel.copy(newselect)

            newselect.add_restriction(newrel)

            select.remove_node(rel)

            var.stinfo['relations'].remove(rel)

            newvar.stinfo['relations'].add(newrel)

            if rel.optional in ('left', 'both'):

                newvar.add_optional_relation(newrel)

            for vref in newrel.children[1].iget_nodes(VariableRef):

                var = vref.variable

                var.stinfo['relations'].add(newrel)

                var.stinfo['rhsrelations'].add(newrel)

                if rel.optional in ('right', 'both'):

                    var.add_optional_relation(newrel)

        if not select.where and not modified:

            # oops, generated the same thing as the original select....

            # restore original query, else we'll indefinitly loop

            for var, rel in towrap_rels:

                select.add_restriction(rel)

            continue

        modified = True

        # extract subquery solutions

        mysolutions = [sol.copy() for sol in solutions]

        cleanup_solutions(newselect, mysolutions)

        newselect.set_possible_types(mysolutions)

        # full sub-query

        aliases = [VariableRef(select.get_variable(avar.name, i))

                   for i, avar in enumerate(newselect.selection)]

        select.add_subquery(SubQuery(aliases, myunion), check=False)

    return modified

def _new_solutions(rqlst, solutions):

    """first filter out subqueries variables from solutions"""

    newsolutions = []

    for origsol in solutions:

        asol = {}

        for vname in rqlst.defined_vars:

            asol[vname] = origsol[vname]

        if not asol in newsolutions:

            newsolutions.append(asol)

    return newsolutions

def remove_unused_solutions(rqlst, solutions, varmap, schema):

    """cleanup solutions: remove solutions where invariant variables are taking

    different types

    """

    newsols = _new_solutions(rqlst, solutions)

    existssols = {}

    unstable = set()

    invariants = {}

    for vname, var in rqlst.defined_vars.iteritems():

        vtype = newsols[0][vname]

        if var._q_invariant or vname in varmap:

            # remove invariant variable from solutions to remove duplicates

            # later, then reinserting a type for the variable even later

            for sol in newsols:

                invariants.setdefault(id(sol), {})[vname] = sol.pop(vname)

        elif var.scope is not rqlst:

            # move appart variables which are in a EXISTS scope and are variating

            try:

                thisexistssols, thisexistsvars = existssols[var.scope]

            except KeyError:

                thisexistssols = [newsols[0]]

                thisexistsvars = set()

                existssols[var.scope] = thisexistssols, thisexistsvars

            for i in xrange(len(newsols)-1, 0, -1):

                if vtype != newsols[i][vname]:

                    thisexistssols.append(newsols.pop(i))

                    thisexistsvars.add(vname)

        else:

            # remember unstable variables

            for i in xrange(1, len(newsols)):

                if vtype != newsols[i][vname]:

                    unstable.add(vname)

    if invariants:

        # filter out duplicates

        newsols_ = []

        for sol in newsols:

            if not sol in newsols_:

                newsols_.append(sol)

        newsols = newsols_

        # reinsert solutions for invariants

        for sol in newsols:

            for invvar, vartype in invariants[id(sol)].iteritems():

                sol[invvar] = vartype

        for sol in existssols:

            try:

                for invvar, vartype in invariants[id(sol)].iteritems():

                    sol[invvar] = vartype

            except KeyError:

                continue

    if len(newsols) > 1:

        if rewrite_unstable_outer_join(rqlst, newsols, unstable, schema):

            # remove variables extracted to subqueries from solutions

            newsols = _new_solutions(rqlst, newsols)

    return newsols, existssols, unstable

def relation_info(relation):

    lhs, rhs = relation.get_variable_parts()

    try:

        lhs = lhs.variable

        lhsconst = lhs.stinfo['constnode']

    except AttributeError:

        lhsconst = lhs

        lhs = None

    except KeyError:

        lhsconst = None # ColumnAlias

    try:

        rhs = rhs.variable

        rhsconst = rhs.stinfo['constnode']

    except AttributeError:

        rhsconst = rhs

        rhs = None

    except KeyError:

        rhsconst = None # ColumnAlias

    return lhs, lhsconst, rhs, rhsconst

def switch_relation_field(sql, table=''):

    switchedsql = sql.replace(table + '.eid_from', '__eid_from__')

    switchedsql = switchedsql.replace(table + '.eid_to',

                                      table + '.eid_from')

    return switchedsql.replace('__eid_from__', table + '.eid_to')

def sort_term_selection(sorts, rqlst, groups):

    # XXX beurk

    if isinstance(rqlst, list):

        def append(term):

            rqlst.append(term)

        selectionidx = set(str(term) for term in rqlst)

    else:

        def append(term):

            rqlst.selection.append(term.copy(rqlst))

        selectionidx = set(str(term) for term in rqlst.selection)

    for sortterm in sorts:

        term = sortterm.term

        if not isinstance(term, Constant) and not str(term) in selectionidx:

            selectionidx.add(str(term))

            append(term)

            if groups:

                for vref in term.iget_nodes(VariableRef):

                    if not vref in groups:

                        groups.append(vref)

def fix_selection_and_group(rqlst, needwrap, selectsortterms,

                            sorts, groups, having):

    if selectsortterms and sorts:

        sort_term_selection(sorts, rqlst, not needwrap and groups)

    groupvrefs = [vref for term in groups for vref in term.iget_nodes(VariableRef)]

    if sorts and groups:

        # when a query is grouped, ensure sort terms are grouped as well

        for sortterm in sorts:

            term = sortterm.term

            if not (isinstance(term, Constant) or \

                    (isinstance(term, Function) and

                     get_func_descr(term.name).aggregat)):

                for vref in term.iget_nodes(VariableRef):

                    if not vref in groupvrefs:

                        groups.append(vref)

                        groupvrefs.append(vref)

    if needwrap and (groups or having):

        selectedidx = set(vref.name for term in rqlst.selection

                          for vref in term.get_nodes(VariableRef))

        if groups:

            for vref in groupvrefs:

                if vref.name not in selectedidx:

                    selectedidx.add(vref.name)

                    rqlst.selection.append(vref)

        if having:

            for term in having:

                for vref in term.iget_nodes(VariableRef):

                    if vref.name not in selectedidx:

                        selectedidx.add(vref.name)

                        rqlst.selection.append(vref)

def iter_mapped_var_sels(stmt, variable):

    # variable is a Variable or ColumnAlias node mapped to a source side

    # callback

    if not (len(variable.stinfo['rhsrelations']) <= 1 and # < 1 on column alias

            variable.stinfo['selected']):

        raise QueryError("can't use %s as a restriction variable"

                         % variable.name)

    for selectidx in variable.stinfo['selected']:

        vrefs = stmt.selection[selectidx].get_nodes(VariableRef)

        if len(vrefs) != 1:

            raise QueryError()

        yield selectidx, vrefs[0]

def update_source_cb_stack(state, stmt, node, stack):

    while True:

        node = node.parent

        if node is stmt:

            break

        if not isinstance(node, Function):

            raise QueryError()

        funcd = get_func_descr(node.name)

        if funcd.source_execute is None:

            raise QueryError('%s can not be called on mapped attribute'

                             % node.name)

        state.source_cb_funcs.add(node)

        funcd.update_cb_stack(stack)

# IGenerator implementation for RQL->SQL #######################################

class StateInfo(object):

    """this class stores data accumulated during the RQL syntax tree visit

    for later SQL generation.

    Attributes related to OUTER JOIN handling

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    * `outer_chains`, list of list of strings. Each list represent a tables

      that have to be outer joined together.

    * `outer_tables`, dictionary used as index of tables used in outer join ::

        'table alias': (outertype, [conditions], [chain])

      where:

      * `outertype` is one of None, 'LEFT', 'RIGHT', 'FULL'

      * `conditions` is a list of join conditions (string)

      * `chain` is a list of table alias (the *outer chain*) in which the key

        alias appears

    * `outer_pending` is a dictionary containing some conditions that will have

      to be added to the outer join when the table will be turned into an

      outerjoin ::

       'table alias': [conditions]

    """

    def __init__(self, select, existssols, unstablevars):

        self.existssols = existssols

        self.unstablevars = unstablevars

        self.subtables = {}

        self.needs_source_cb = None

        self.subquery_source_cb = None

        self.source_cb_funcs = set()

        self.scopes = {select: 0}

        self.scope_nodes = []

    def reset(self, solution):

        """reset some visit variables"""

        self.solution = solution

        self.count = 0

        self.done = set()

        self.tables = self.subtables.copy()

        self.actual_tables = [[]]

        for _, tsql in self.tables.itervalues():

            self.actual_tables[-1].append(tsql)

        self.outer_chains = []

        self.outer_tables = {}

        self.outer_pending = {}

        self.duplicate_switches = []

        self.aliases = {}