# copyright 2003-2011 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.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.server.sqlutils import SQL_PREFIX

from cubicweb.server.utils import cleanup_solutions

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`, dictionnary 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 = {}

        self.restrictions = []