server/sources/rql2sql.py
author Anthony Truchet <anthony.truchet@logilab.fr>
Wed, 22 Feb 2012 11:57:42 +0100
changeset 8574 268bc595271b
parent 8245 d53762ae5961
child 8457 69ad9a509bc3
permissions -rw-r--r--
backporting

# 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.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.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`, 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 = {}
        self.restrictions = []
        self._restr_stack = []
        self.ignore_varmap = False
        self._needs_source_cb = {}

    def merge_source_cbs(self, needs_source_cb):
        if self.needs_source_cb is None:
            self.needs_source_cb = needs_source_cb
        elif needs_source_cb != self.needs_source_cb:
            raise QueryError('query fetch some source mapped attribute, some not')

    def finalize_source_cbs(self):
        if self.subquery_source_cb is not None:
            self.needs_source_cb.update(self.subquery_source_cb)

    def add_restriction(self, restr):
        if restr:
            self.restrictions.append(restr)

    def iter_exists_sols(self, exists):
        if not exists in self.existssols:
            yield 1
            return
        thisexistssols, thisexistsvars = self.existssols[exists]
        # when iterating other solutions inner to an EXISTS subquery, we should
        # reset variables which have this exists node as scope at each iteration
        for var in exists.stmt.defined_vars.itervalues():
            if var.scope is exists:
                thisexistsvars.add(var.name)
        origsol = self.solution
        origtables = self.tables
        done = self.done
        for thisexistssol in thisexistssols:
            for vname in self.unstablevars:
                if thisexistssol[vname] != origsol[vname] and vname in thisexistsvars:
                    break
            else:
                self.tables = origtables.copy()
                self.solution = thisexistssol
                yield 1
                # cleanup self.done from stuff specific to exists
                for var in thisexistsvars:
                    if var in done:
                        done.remove(var)
                for rel in exists.iget_nodes(Relation):
                    if rel in done:
                        done.remove(rel)
        self.solution = origsol
        self.tables = origtables

    def push_scope(self, scope_node):
        self.scope_nodes.append(scope_node)
        self.scopes[scope_node] = len(self.actual_tables)
        self.actual_tables.append([])
        self._restr_stack.append(self.restrictions)
        self.restrictions = []

    def pop_scope(self):
        del self.scopes[self.scope_nodes[-1]]
        self.scope_nodes.pop()
        restrictions = self.restrictions
        self.restrictions = self._restr_stack.pop()
        scope = len(self.actual_tables) - 1
        # check if we have some outer chain for this scope
        matching_chains = []
        for chain in self.outer_chains:
            for tablealias in chain:
                if self.tables[tablealias][0] < scope:
                    # chain belongs to outer scope
                    break
            else:
                # chain match current scope
                matching_chains.append(chain)
        # call to `tables_sql` will pop actual_tables
        tables = self.tables_sql(matching_chains)
        # cleanup outer join related structure for tables in matching chains
        for chain in matching_chains:
            self.outer_chains.remove(chain)
            for alias in chain:
                del self.outer_tables[alias]
        return restrictions, tables

    # tables handling #########################################################

    def add_table(self, table, key=None, scope=-1):
        if key is None:
            key = table
        if key in self.tables:
            return
        if scope < 0:
            scope = len(self.actual_tables) + scope
        self.tables[key] = (scope, table)
        self.actual_tables[scope].append(table)

    def alias_and_add_table(self, tablename, scope=-1):
        alias = '%s%s' % (tablename, self.count)
        self.count += 1
        self.add_table('%s AS %s' % (tablename, alias), alias, scope)
        return alias

    def relation_table(self, relation):
        """return the table alias used by the given relation"""
        if relation in self.done:
            return relation._q_sqltable
        rid = 'rel_%s%s' % (relation.r_type, self.count)
        # relation's table is belonging to the root scope if it is the principal
        # table of one of it's variable and if that variable belong's to parent
        # scope
        for varref in relation.iget_nodes(VariableRef):
            var = varref.variable
            if isinstance(var, ColumnAlias):
                scope = 0
                break
            # XXX may have a principal without being invariant for this generation,
            #     not sure this is a pb or not
            if var.stinfo.get('principal') is relation and var.scope is var.stmt:
                scope = 0
                break
        else:
            scope = -1
        self.count += 1
        self.add_table('%s_relation AS %s' % (relation.r_type, rid), rid, scope=scope)
        relation._q_sqltable = rid
        self.done.add(relation)
        return rid

    def fti_table(self, relation, fti_table):
        """return the table alias used by the given has_text relation,
        `fti_table` being the table name for the plain text index
        """
        if relation in self.done:
            try:
                return relation._q_sqltable
            except AttributeError:
                pass
        self.done.add(relation)
        scope = self.scopes[relation.scope]
        alias = self.alias_and_add_table(fti_table, scope=scope)
        relation._q_sqltable = alias
        return alias

    # outer join handling ######################################################

    def mark_as_used_in_outer_join(self, tablealias, addpending=True):
        """Mark table of given alias as used in outer join. This must be called
        after `outer_tables[tablealias]` has been initialized.
        """
        # remove a table from actual_table because it's used in an outer join
        # chain
        scope, tabledef = self.tables[tablealias]
        self.actual_tables[scope].remove(tabledef)
        # check if there are some pending outer join condition for this table
        if addpending:
            try:
                pending_conditions = self.outer_pending.pop(tablealias)
            except KeyError:
                pass
            else:
                self.outer_tables[tablealias][1].extend(pending_conditions)
        else:
            assert not tablealias in self.outer_pending

    def add_outer_join_condition(self, tablealias, condition):
        try:
            outer, conditions, chain = self.outer_tables[tablealias]
            conditions.append(condition)
        except KeyError:
            self.outer_pending.setdefault(tablealias, []).append(condition)

    def replace_tables_by_outer_join(self, leftalias, rightalias,
                                     outertype, condition):
        """tell we need <leftalias> <outertype> JOIN <rightalias> ON <condition>
        """
        assert leftalias != rightalias, leftalias
        outer_tables = self.outer_tables
        louter, lconditions, lchain = outer_tables.get(leftalias,
                                                      (None, None, None))
        router, rconditions, rchain = outer_tables.get(rightalias,
                                                      (None, None, None))
        if lchain is None and rchain is None:
            # create a new outer chaine
            chain = [leftalias, rightalias]
            outer_tables[leftalias] = (None, [], chain)
            outer_tables[rightalias] = (outertype, [condition], chain)
            self.outer_chains.append(chain)
            self.mark_as_used_in_outer_join(leftalias, addpending=False)
            self.mark_as_used_in_outer_join(rightalias)
        elif lchain is None:
            # [A > B > C] + [D > A] -> [D > A > B > C]
            if rightalias == rchain[0]:
                outer_tables[leftalias] = (None, [], rchain)
                conditions = outer_tables[rightalias][1] + [condition]
                outer_tables[rightalias] = (outertype, conditions, rchain)
                rchain.insert(0, leftalias)
            else:
                # [A > B > C] + [D > B] -> [A > B > C < D]
                if outertype == 'LEFT':
                    outertype = 'RIGHT'
                outer_tables[leftalias] = (outertype, [condition], rchain)
                rchain.append(leftalias)
            self.mark_as_used_in_outer_join(leftalias)
        elif rchain is None:
            # [A > B > C] + [B > D] -> [A > B > C > D]
            outer_tables[rightalias] = (outertype, [condition], lchain)
            lchain.append(rightalias)
            self.mark_as_used_in_outer_join(rightalias)
        elif lchain is rchain:
            # already in the same chain, simply check compatibility and append
            # the condition if it's ok
            lidx = lchain.index(leftalias)
            ridx = lchain.index(rightalias)
            if (outertype == 'FULL' and router != 'FULL') \
                   or (lidx < ridx and router != 'LEFT') \
                   or (ridx < lidx and louter != 'RIGHT'):
                raise BadRQLQuery()
            # merge conditions
            if lidx < ridx:
                rconditions.append(condition)
            else:
                lconditions.append(condition)
        elif louter is None:
            # merge chains
            self.outer_chains.remove(lchain)
            rchain += lchain
            self.mark_as_used_in_outer_join(leftalias)
            for alias, (aouter, aconditions, achain) in outer_tables.iteritems():
                if achain is lchain:
                    outer_tables[alias] = (aouter, aconditions, rchain)
        else:
            raise BadRQLQuery()

    # sql generation helpers ###################################################

    def tables_sql(self, outer_chains=None):
        """generate SQL for FROM clause"""
        # sort for test predictability
        tables = sorted(self.actual_tables.pop())
        # process outer joins
        if outer_chains is None:
            assert not self.actual_tables, self.actual_tables
            assert not self.outer_pending
            outer_chains = self.outer_chains
        for chain in sorted(outer_chains):
            tablealias = chain[0]
            outertype, conditions, _ = self.outer_tables[tablealias]
            assert _ is chain, (chain, _)
            assert outertype is None, (chain, self.outer_chains)
            assert not conditions, (chain, self.outer_chains)
            assert len(chain) > 1
            tabledef = self.tables[tablealias][1]
            outerjoin = [tabledef]
            for tablealias in chain[1:]:
                outertype, conditions, _ = self.outer_tables[tablealias]
                assert _ is chain, (chain, self.outer_chains)
                assert outertype in ('LEFT', 'RIGHT', 'FULL'), (
                    tablealias, outertype, conditions)
                assert isinstance(conditions, (list)), (
                    tablealias, outertype, conditions)
                tabledef = self.tables[tablealias][1]
                outerjoin.append('%s OUTER JOIN %s ON (%s)' % (
                    outertype, tabledef, ' AND '.join(conditions)))
            tables.append(' '.join(outerjoin))
        return ', '.join(tables)


def extract_fake_having_terms(having):
    """RQL's HAVING may be used to contains stuff that should go in the WHERE
    clause of the SQL query, due to RQL grammar limitation. Split them...

    Return a list nodes that can be ANDed with query's WHERE clause. Having
    subtrees updated in place.
    """
    fakehaving = []
    for subtree in having:
        ors, tocheck = set(), []
        for compnode in subtree.get_nodes(Comparison):
            for fnode in compnode.get_nodes(Function):
                if fnode.descr().aggregat:
                    p = compnode.parent
                    oor = None
                    while not isinstance(p, Select):
                        if isinstance(p, (Or, Not)):
                            oor = p
                        p = p.parent
                    if oor is not None:
                        ors.add(oor)
                    break
            else:
                tocheck.append(compnode)
        # tocheck hold a set of comparison not implying an aggregat function
        # put them in fakehaving if they don't share an Or node as ancestor
        # with another comparison containing an aggregat function
        for compnode in tocheck:
            parents = set()
            p = compnode.parent
            oor = None
            while not isinstance(p, Select):
                if p in ors or p is None: # p is None for nodes already in fakehaving
                    break
                if isinstance(p, (Or, Not)):
                    oor = p
                p = p.parent
            else:
                node = oor or compnode
                fakehaving.append(node)
                node.parent.remove(node)
    return fakehaving


class SQLGenerator(object):
    """
    generation of SQL from the fully expanded RQL syntax tree
    SQL is designed to be used with a CubicWeb SQL schema

    Groups and sort are not handled here since they should not be handled at
    this level (see cubicweb.server.querier)

    we should not have errors here !

    WARNING: a CubicWebSQLGenerator instance is not thread safe, but generate is
    protected by a lock
    """

    def __init__(self, schema, dbhelper, attrmap=None):
        self.schema = schema
        self.dbhelper = dbhelper
        self.dbencoding = dbhelper.dbencoding
        self.keyword_map = {'NOW' : self.dbhelper.sql_current_timestamp,
                            'TODAY': self.dbhelper.sql_current_date,
                            }
        if not self.dbhelper.union_parentheses_support:
            self.union_sql = self.noparen_union_sql
        if self.dbhelper.fti_need_distinct:
            self.__union_sql = self.union_sql
            self.union_sql = self.has_text_need_distinct_union_sql
        self._lock = threading.Lock()
        if attrmap is None:
            attrmap = {}
        self.attr_map = attrmap

    def generate(self, union, args=None, varmap=None):
        """return SQL queries and a variable dictionary from a RQL syntax tree

        :partrqls: a list of couple (rqlst, solutions)
        :args: optional dictionary with values of substitutions used in the query
        :varmap: optional dictionary mapping variable name to a special table
          name, in case the query as to fetch data from temporary tables

        return an sql string and a dictionary with substitutions values
        """
        if args is None:
            args = {}
        if varmap is None:
            varmap =  {}
        self._lock.acquire()
        self._args = args
        self._varmap = varmap
        self._query_attrs = {}
        self._state = None
        # self._not_scope_offset = 0
        try:
            # union query for each rqlst / solution
            sql = self.union_sql(union)
            # we are done
            return sql, self._query_attrs, self._state.needs_source_cb
        finally:
            self._lock.release()

    def has_text_need_distinct_union_sql(self, union, needalias=False):
        if getattr(union, 'has_text_query', False):
            for select in union.children:
                select.need_distinct = True
        return self.__union_sql(union, needalias)

    def union_sql(self, union, needalias=False): # pylint: disable=E0202
        if len(union.children) == 1:
            return self.select_sql(union.children[0], needalias)
        sqls = ('(%s)' % self.select_sql(select, needalias)
                for select in union.children)
        return '\nUNION ALL\n'.join(sqls)

    def noparen_union_sql(self, union, needalias=False):
        # needed for sqlite backend which doesn't like parentheses around union
        # query. This may cause bug in some condition (sort in one of the
        # subquery) but will work in most case
        #
        # see http://www.sqlite.org/cvstrac/tktview?tn=3074
        sqls = (self.select_sql(select, needalias)
                for i, select in enumerate(union.children))
        return '\nUNION ALL\n'.join(sqls)

    def select_sql(self, select, needalias=False):
        """return SQL queries and a variable dictionary from a RQL syntax tree

        :select: a selection statement of the syntax tree (`rql.stmts.Select`)
        :solution: a dictionary containing variables binding.
          A solution's dictionary has variable's names as key and variable's
          types as values
        :needwrap: boolean telling if the query will be wrapped in an outer
          query (to deal with aggregat and/or grouping)
        """
        distinct = selectsortterms = select.need_distinct
        sorts = select.orderby
        groups = select.groupby
        having = select.having
        for restr in extract_fake_having_terms(having):
            scope = None
            for vref in restr.get_nodes(VariableRef):
                vscope = vref.variable.scope
                if vscope is select:
                    continue # ignore select scope, so restriction is added to
                             # the inner most scope possible
                if scope is None:
                    scope = vscope
                elif vscope is not scope:
                    scope = common_parent(scope, vscope).scope
            if scope is None:
                scope = select
            scope.add_restriction(restr)
        # remember selection, it may be changed and have to be restored
        origselection = select.selection[:]
        # check if the query will have union subquery, if it need sort term
        # selection (union or distinct query) and wrapping (union with groups)
        needwrap = False
        sols = select.solutions
        if len(sols) > 1:
            # remove invariant from solutions
            sols, existssols, unstable = remove_unused_solutions(
                select, sols, self._varmap, self.schema)
            if len(sols) > 1:
                # if there is still more than one solution, a UNION will be
                # generated and so sort terms have to be selected
                selectsortterms = True
                # and if select is using group by or aggregat, a wrapping
                # query will be necessary
                if groups or select.has_aggregat:
                    select.select_only_variables()
                    needwrap = True
        else:
            existssols, unstable = {}, ()
        state = StateInfo(select, existssols, unstable)
        if self._state is not None:
            # state from a previous unioned select
            state.merge_source_cbs(self._state.needs_source_cb)
        # treat subqueries
        self._subqueries_sql(select, state)
        # generate sql for this select node
        if needwrap:
            outerselection = origselection[:]
            if sorts and selectsortterms:
                if distinct:
                    sort_term_selection(sorts, outerselection, groups)
        fix_selection_and_group(select, needwrap, selectsortterms,
                                sorts, groups, having)
        if needwrap:
            fneedwrap = len(outerselection) != len(origselection)
        else:
            fneedwrap = len(select.selection) != len(origselection)
        if fneedwrap:
            needalias = True
        self._in_wrapping_query = False
        self._state = state
        try:
            sql = self._solutions_sql(select, sols, distinct,
                                      needalias or needwrap)
            # generate groups / having before wrapping query selection to get
            # correct column aliases
            self._in_wrapping_query = needwrap
            if groups:
                # no constant should be inserted in GROUP BY else the backend
                # will interpret it as a positional index in the selection
                groups = ','.join(vref.accept(self) for vref in groups
                                  if not isinstance(vref, Constant))
            if having:
                # filter out constants as for GROUP BY
                having = ' AND '.join(term.accept(self) for term in having
                                      if not isinstance(term, Constant))
            if needwrap:
                sql = '%s FROM (%s) AS T1' % (
                    self._selection_sql(outerselection, distinct,needalias),
                    sql)
            if groups:
                sql += '\nGROUP BY %s' % groups
            if having:
                sql += '\nHAVING %s' % having
            # sort
            if sorts:
                sqlsortterms = []
                if needwrap:
                    selectidx = [str(term) for term in outerselection]
                else:
                    selectidx = [str(term) for term in select.selection]
                for sortterm in sorts:
                    _term = self._sortterm_sql(sortterm, selectidx)
                    if _term is not None:
                        sqlsortterms.append(_term)
                if sqlsortterms:
                    sql = self.dbhelper.sql_add_order_by(
                        sql, sqlsortterms, origselection, fneedwrap,
                        select.limit or select.offset)
            else:
                sqlsortterms = None
            state.finalize_source_cbs()
        finally:
            select.selection = origselection
        # limit / offset
        sql = self.dbhelper.sql_add_limit_offset(sql,
                                                 select.limit,
                                                 select.offset,
                                                 sqlsortterms)
        return sql

    def _subqueries_sql(self, select, state):
        for i, subquery in enumerate(select.with_):
            sql = self.union_sql(subquery.query, needalias=True)
            tablealias = '_T%s' % i # XXX nested subqueries
            sql = '(%s) AS %s' % (sql, tablealias)
            state.subtables[tablealias] = (0, sql)
            latest_state = self._state
            for vref in subquery.aliases:
                alias = vref.variable
                alias._q_sqltable = tablealias
                alias._q_sql = '%s.C%s' % (tablealias, alias.colnum)
                try:
                    stack = latest_state.needs_source_cb[alias.colnum]
                    if state.subquery_source_cb is None:
                        state.subquery_source_cb = {}
                    for selectidx, vref in iter_mapped_var_sels(select, alias):
                        stack = stack[:]
                        update_source_cb_stack(state, select, vref, stack)
                        state.subquery_source_cb[selectidx] = stack
                except KeyError:
                    continue

    def _solutions_sql(self, select, solutions, distinct, needalias):
        sqls = []
        for solution in solutions:
            self._state.reset(solution)
            # visit restriction subtree
            if select.where is not None:
                self._state.add_restriction(select.where.accept(self))
            sql = [self._selection_sql(select.selection, distinct, needalias)]
            if self._state.restrictions:
                sql.append('WHERE %s' % ' AND '.join(self._state.restrictions))
            self._state.merge_source_cbs(self._state._needs_source_cb)
            # add required tables
            assert len(self._state.actual_tables) == 1, self._state.actual_tables
            tables = self._state.tables_sql()
            if tables:
                sql.insert(1, 'FROM %s' % tables)
            elif self._state.restrictions and self.dbhelper.needs_from_clause:
                sql.insert(1, 'FROM (SELECT 1) AS _T')
            sqls.append('\n'.join(sql))
        if distinct:
            return '\nUNION\n'.join(sqls)
        else:
            return '\nUNION ALL\n'.join(sqls)

    def _selection_sql(self, selected, distinct, needaliasing=False):
        clause = []
        for term in selected:
            sql = term.accept(self)
            if needaliasing:
                colalias = 'C%s' % len(clause)
                clause.append('%s AS %s' % (sql, colalias))
                if isinstance(term, VariableRef):
                    self._state.aliases[term.name] = colalias
            else:
                clause.append(sql)
        if distinct:
            return 'SELECT DISTINCT %s' % ', '.join(clause)
        return 'SELECT %s' % ', '.join(clause)

    def _sortterm_sql(self, sortterm, selectidx):
        term = sortterm.term
        try:
            sqlterm = selectidx.index(str(term)) + 1
        except ValueError:
            # Constant node or non selected term
            sqlterm = term.accept(self)
            if sqlterm is None:
                return None
        if sortterm.asc:
            return str(sqlterm)
        else:
            return '%s DESC' % sqlterm

    def visit_and(self, et):
        """generate SQL for a AND subtree"""
        res = []
        for c in et.children:
            part = c.accept(self)
            if part:
                res.append(part)
        return ' AND '.join(res)

    def visit_or(self, ou):
        """generate SQL for a OR subtree"""
        res = []
        for c in ou.children:
            part = c.accept(self)
            if part:
                res.append('(%s)' % part)
        if res:
            if len(res) > 1:
                return '(%s)' % ' OR '.join(res)
            return res[0]
        return ''

    def visit_not(self, node):
        csql = node.children[0].accept(self)
        if node in self._state.done or not csql:
            # already processed or no sql generated by children
            return csql
        return 'NOT (%s)' % csql

    def visit_exists(self, exists):
        """generate SQL name for a exists subquery"""
        sqls = []
        for dummy in self._state.iter_exists_sols(exists):
            sql = self._visit_exists(exists)
            if sql:
                sqls.append(sql)
        if not sqls:
            return ''
        return 'EXISTS(%s)' % ' UNION '.join(sqls)

    def _visit_exists(self, exists):
        self._state.push_scope(exists)
        restriction = exists.children[0].accept(self)
        restrictions, tables = self._state.pop_scope()
        if restriction:
            restrictions.append(restriction)
        restriction = ' AND '.join(restrictions)
        if not restriction:
            if tables:
                return 'SELECT 1 FROM %s' % tables
            return ''
        if not tables:
            # XXX could leave surrounding EXISTS() in this case no?
            sql = 'SELECT 1 WHERE %s' % restriction
        else:
            sql = 'SELECT 1 FROM %s WHERE %s' % (tables, restriction)
        return sql


    def visit_relation(self, relation):
        """generate SQL for a relation"""
        rtype = relation.r_type
        # don't care of type constraint statement (i.e. relation_type = 'is')
        if relation.is_types_restriction():
            return ''
        lhs, rhs = relation.get_parts()
        rschema = self.schema.rschema(rtype)
        if rschema.final:
            if rtype == 'eid' and lhs.variable._q_invariant and \
                   lhs.variable.stinfo['constnode']:
                # special case where this restriction is already generated by
                # some other relation
                return ''
            # attribute relation
            if rtype == 'has_text':
                sql = self._visit_has_text_relation(relation)
            else:
                rhs_vars = rhs.get_nodes(VariableRef)
                if rhs_vars:
                    # if variable(s) in the RHS
                    sql = self._visit_var_attr_relation(relation, rhs_vars)
                else:
                    # no variables in the RHS
                    sql = self._visit_attribute_relation(relation)
        elif (rtype == 'is' and isinstance(rhs.children[0], Constant)
              and rhs.children[0].eval(self._args) is None):
            # special case "C is NULL"
            if lhs.name in self._varmap:
                lhssql = self._varmap[lhs.name]
            else:
                lhssql = lhs.accept(self)
            return '%s%s' % (lhssql, rhs.accept(self))
        elif '%s.%s' % (lhs, relation.r_type) in self._varmap:
            # relation has already been processed by a previous step
            return ''
        elif relation.optional:
            # OPTIONAL relation, generate a left|right outer join
            if rtype == 'identity' or rschema.inlined:
                sql = self._visit_outer_join_inlined_relation(relation, rschema)
            else:
                sql = self._visit_outer_join_relation(relation, rschema)
        elif rschema.inlined:
            sql = self._visit_inlined_relation(relation)
        else:
            # regular (non final) relation
            sql = self._visit_relation(relation, rschema)
        return sql

    def _visit_inlined_relation(self, relation):
        lhsvar, _, rhsvar, rhsconst = relation_info(relation)
        # we are sure lhsvar is not None
        lhssql = self._inlined_var_sql(lhsvar, relation.r_type)
        if rhsvar is None:
            moresql = None
        else:
            moresql = self._extra_join_sql(relation, lhssql, rhsvar)
        if isinstance(relation.parent, Not):
            self._state.done.add(relation.parent)
            if rhsvar is not None and rhsvar._q_invariant:
                sql = '%s IS NULL' % lhssql
            else:
                # column != 1234 may not get back rows where column is NULL...
                sql = '(%s IS NULL OR %s!=%s)' % (
                    lhssql, lhssql, (rhsvar or rhsconst).accept(self))
        elif rhsconst is not None:
            sql = '%s=%s' % (lhssql, rhsconst.accept(self))
        elif isinstance(rhsvar, Variable) and rhsvar._q_invariant and \
                 not rhsvar.name in self._varmap:
            # if the rhs variable is only linked to this relation, this mean we
            # only want the relation to exists, eg NOT NULL in case of inlined
            # relation
            if moresql is not None:
                return moresql
            return '%s IS NOT NULL' % lhssql
        else:
            sql = '%s=%s' % (lhssql, rhsvar.accept(self))
        if moresql is None:
            return sql
        return '%s AND %s' % (sql, moresql)

    def _process_relation_term(self, relation, rid, termvar, termconst, relfield):
        if termconst or not termvar._q_invariant:
            termsql = termconst and termconst.accept(self) or termvar.accept(self)
            yield '%s.%s=%s' % (rid, relfield, termsql)
        elif termvar._q_invariant:
            # if the variable is mapped, generate restriction anyway
            if termvar.name in self._varmap:
                termsql = termvar.accept(self)
                yield '%s.%s=%s' % (rid, relfield, termsql)
            extrajoin = self._extra_join_sql(relation, '%s.%s' % (rid, relfield), termvar)
            if extrajoin is not None:
                yield extrajoin

    def _visit_relation(self, relation, rschema):
        """generate SQL for a relation

        implements optimization 1.
        """
        if relation.r_type == 'identity':
            # special case "X identity Y"
            lhs, rhs = relation.get_parts()
            return '%s%s' % (lhs.accept(self), rhs.accept(self))
        lhsvar, lhsconst, rhsvar, rhsconst = relation_info(relation)
        rid = self._state.relation_table(relation)
        sqls = []
        sqls += self._process_relation_term(relation, rid, lhsvar, lhsconst, 'eid_from')
        sqls += self._process_relation_term(relation, rid, rhsvar, rhsconst, 'eid_to')
        sql = ' AND '.join(sqls)
        if rschema.symmetric:
            sql = '(%s OR %s)' % (sql, switch_relation_field(sql))
        return sql

    def _visit_outer_join_relation(self, relation, rschema):
        """
        left outer join syntax (optional=='right'):
          X relation Y?

        right outer join syntax (optional=='left'):
          X? relation Y

        full outer join syntaxes (optional=='both'):
          X? relation Y?

        if relation is inlined:
           if it's a left outer join:
           -> X LEFT OUTER JOIN Y ON (X.relation=Y.eid)
           elif it's a right outer join:
           -> Y LEFT OUTER JOIN X ON (X.relation=Y.eid)
           elif it's a full outer join:
           -> X FULL OUTER JOIN Y ON (X.relation=Y.eid)
        else:
           if it's a left outer join:
           -> X LEFT OUTER JOIN relation ON (relation.eid_from=X.eid)
              LEFT OUTER JOIN Y ON (relation.eid_to=Y.eid)
           elif it's a right outer join:
           -> Y LEFT OUTER JOIN relation ON (relation.eid_to=Y.eid)
              LEFT OUTER JOIN X ON (relation.eid_from=X.eid)
           elif it's a full outer join:
           -> X FULL OUTER JOIN Y ON (X.relation=Y.eid)
        """
        leftvar, leftconst, rightvar, rightconst = relation_info(relation)
        assert not (leftconst and rightconst), "doesn't make sense"
        if relation.optional == 'left':
            leftvar, rightvar = rightvar, leftvar
            leftconst, rightconst = rightconst, leftconst
            joinattr, restrattr = 'eid_to', 'eid_from'
        else:
            joinattr, restrattr = 'eid_from', 'eid_to'
        # search table for this variable, to use as left table of the outer join
        leftalias = None
        if leftvar:
            # take care, may return None for invariant variable
            leftalias = self._var_table(leftvar)
        if leftalias is None:
            if leftvar.stinfo['principal'] is not relation:
                # use variable's principal relation
                leftalias = leftvar.stinfo['principal']._q_sqltable
            else:
                # search for relation on which we should join
                for orelation in leftvar.stinfo['relations']:
                    if (orelation is not relation and
                        not self.schema.rschema(orelation.r_type).final):
                        break
                else:
                    for orelation in rightvar.stinfo['relations']:
                        if (orelation is not relation and
                            not self.schema.rschema(orelation.r_type).final
                            and orelation.optional):
                            break
                    else:
                        # unexpected
                        assert False, leftvar
                leftalias = self._state.relation_table(orelation)
        # right table of the outer join
        rightalias = self._state.relation_table(relation)
        # compute join condition
        if not leftconst or (leftvar and not leftvar._q_invariant):
            leftsql = leftvar.accept(self)
        else:
            leftsql = leftconst.accept(self)
        condition = '%s.%s=%s' % (rightalias, joinattr, leftsql)
        if rightconst:
            condition += ' AND %s.%s=%s' % (rightalias, restrattr, rightconst.accept(self))
        # record outer join
        outertype = 'FULL' if relation.optional == 'both' else 'LEFT'
        self._state.replace_tables_by_outer_join(leftalias, rightalias,
                                                 outertype, condition)
        # need another join?
        if rightconst is None:
            # we need another outer join for the other side of the relation (e.g.
            # for "X relation Y?" in RQL, we treated earlier the (cw_X.eid /
            # relation.eid_from) join, now we've to do (relation.eid_to /
            # cw_Y.eid)
            leftalias = rightalias
            rightsql = rightvar.accept(self) # accept before using var_table
            rightalias = self._var_table(rightvar)
            if rightalias is None:
                if rightvar.stinfo['principal'] is not relation:
                    self._state.replace_tables_by_outer_join(
                        leftalias, rightvar.stinfo['principal']._q_sqltable,
                        outertype, '%s.%s=%s' % (leftalias, restrattr, rightvar.accept(self)))
            else:
                self._state.replace_tables_by_outer_join(
                    leftalias, rightalias, outertype,
                    '%s.%s=%s' % (leftalias, restrattr, rightvar.accept(self)))
        # this relation will hence be expressed in FROM clause, return nothing
        # here
        return ''


    def _visit_outer_join_inlined_relation(self, relation, rschema):
        lhsvar, lhsconst, rhsvar, rhsconst = relation_info(relation)
        assert not (lhsconst and rhsconst), "doesn't make sense"
        attr = 'eid' if relation.r_type == 'identity' else relation.r_type
        lhsalias = self._var_table(lhsvar)
        rhsalias = rhsvar and self._var_table(rhsvar)
        try:
            lhssql = self._varmap['%s.%s' % (lhsvar.name, attr)]
        except KeyError:
            if lhsalias is None:
                lhssql = lhsconst.accept(self)
            else:
                lhssql = '%s.%s%s' % (lhsalias, SQL_PREFIX, attr)
        condition = '%s=%s' % (lhssql, (rhsconst or rhsvar).accept(self))
        # this is not a typo, rhs optional variable means lhs outer join and vice-versa
        if relation.optional == 'left':
            lhsvar, rhsvar = rhsvar, lhsvar
            lhsconst, rhsconst = rhsconst, lhsconst
            lhsalias, rhsalias = rhsalias, lhsalias
            outertype = 'LEFT'
        elif relation.optional == 'both':
            outertype = 'FULL'
        else:
            outertype = 'LEFT'
        if rhsalias is None:
            if rhsconst is not None:
                # inlined relation with invariant as rhs
                if relation.r_type != 'identity':
                    condition = '(%s OR %s IS NULL)' % (condition, lhssql)
                if not lhsvar.stinfo.get('optrelations'):
                    return condition
                self._state.add_outer_join_condition(lhsalias, condition)
            return
        if lhsalias is None:
            if lhsconst is not None and not rhsvar.stinfo.get('optrelations'):
                return condition
            lhsalias = lhsvar._q_sql.split('.', 1)[0]
        if lhsalias == rhsalias:
            self._state.add_outer_join_condition(lhsalias, condition)
        else:
            self._state.replace_tables_by_outer_join(
                lhsalias, rhsalias, outertype, condition)
        return ''

    def _visit_var_attr_relation(self, relation, rhs_vars):
        """visit an attribute relation with variable(s) in the RHS

        attribute variables are used either in the selection or for
        unification (eg X attr1 A, Y attr2 A). In case of selection,
        nothing to do here.
        """
        for vref in rhs_vars:
            var = vref.variable
            if var.name in self._varmap:
                # ensure table is added
                self._var_info(var)
            if isinstance(var, ColumnAlias):
                # force sql generation whatever the computed principal
                principal = 1
            else:
                principal = var.stinfo.get('principal')
            if principal is not None and principal is not relation:
                # we have to generate unification expression
                lhssql = self._inlined_var_sql(relation.children[0].variable,
                                               relation.r_type)
                try:
                    self._state.ignore_varmap = True
                    sql = lhssql + relation.children[1].accept(self)
                finally:
                    self._state.ignore_varmap = False
                if relation.optional == 'right':
                    leftalias = self._var_table(principal.children[0].variable)
                    rightalias = self._var_table(relation.children[0].variable)
                    self._state.replace_tables_by_outer_join(
                        leftalias, rightalias, 'LEFT', sql)
                    return ''
                return sql
        return ''

    def _visit_attribute_relation(self, rel):
        """generate SQL for an attribute relation"""
        lhs, rhs = rel.get_parts()
        rhssql = rhs.accept(self)
        table = self._var_table(lhs.variable)
        if table is None:
            assert rel.r_type == 'eid'
            lhssql = lhs.accept(self)
        else:
            try:
                lhssql = self._varmap['%s.%s' % (lhs.name, rel.r_type)]
            except KeyError:
                mapkey = '%s.%s' % (self._state.solution[lhs.name], rel.r_type)
                if mapkey in self.attr_map:
                    cb, sourcecb = self.attr_map[mapkey]
                    if sourcecb:
                        # callback is a source callback, we can't use this
                        # attribute in restriction
                        raise QueryError("can't use %s (%s) in restriction"
                                         % (mapkey, rel.as_string()))
                    lhssql = cb(self, lhs.variable, rel)
                elif rel.r_type == 'eid':
                    lhssql = lhs.variable._q_sql
                else:
                    lhssql = '%s.%s%s' % (table, SQL_PREFIX, rel.r_type)
        try:
            if rel._q_needcast == 'TODAY':
                sql = 'DATE(%s)%s' % (lhssql, rhssql)
            # XXX which cast function should be used
            #elif rel._q_needcast == 'NOW':
            #    sql = 'TIMESTAMP(%s)%s' % (lhssql, rhssql)
            else:
                sql = '%s%s' % (lhssql, rhssql)
        except AttributeError:
            sql = '%s%s' % (lhssql, rhssql)
        if lhs.variable.stinfo.get('optrelations'):
            self._state.add_outer_join_condition(table, sql)
        else:
            return sql

    def _visit_has_text_relation(self, rel):
        """generate SQL for a has_text relation"""
        lhs, rhs = rel.get_parts()
        const = rhs.children[0]
        alias = self._state.fti_table(rel, self.dbhelper.fti_table)
        jointo = lhs.accept(self)
        restriction = ''
        lhsvar = lhs.variable
        me_is_principal = lhsvar.stinfo.get('principal') is rel
        if me_is_principal:
            if lhsvar.stinfo['typerel'] is None:
                # the variable is using the fti table, no join needed
                jointo = None
            elif not lhsvar.name in self._varmap:
                # join on entities instead of etype's table to get result for
                # external entities on multisources configurations
                ealias = lhsvar._q_sqltable = '_' + lhsvar.name
                jointo = lhsvar._q_sql = '%s.eid' % ealias
                self._state.add_table('entities AS %s' % ealias, ealias)
                if not lhsvar._q_invariant or len(lhsvar.stinfo['possibletypes']) == 1:
                    restriction = " AND %s.type='%s'" % (ealias, self._state.solution[lhs.name])
                else:
                    etypes = ','.join("'%s'" % etype for etype in lhsvar.stinfo['possibletypes'])
                    restriction = " AND %s.type IN (%s)" % (ealias, etypes)
        if isinstance(rel.parent, Not):
            self._state.done.add(rel.parent)
            not_ = True
        else:
            not_ = False
        query = const.eval(self._args)
        return self.dbhelper.fti_restriction_sql(alias, query,
                                                 jointo, not_) + restriction

    def visit_comparison(self, cmp):
        """generate SQL for a comparison"""
        optional = getattr(cmp, 'optional', None) # rql < 0.30
        if len(cmp.children) == 2:
            # simplified expression from HAVING clause
            lhs, rhs = cmp.children
        else:
            lhs = None
            rhs = cmp.children[0]
            assert not optional
        sql = None
        operator = cmp.operator
        if operator in ('LIKE', 'ILIKE'):
            if operator == 'ILIKE' and not self.dbhelper.ilike_support:
                operator = ' LIKE '
            else:
                operator = ' %s ' % operator
        elif operator == 'REGEXP':
            sql = ' %s' % self.dbhelper.sql_regexp_match_expression(rhs.accept(self))
        elif (operator == '=' and isinstance(rhs, Constant)
              and rhs.eval(self._args) is None):
            if lhs is None:
                sql = ' IS NULL'
            else:
                sql = '%s IS NULL' % lhs.accept(self)
        elif isinstance(rhs, Function) and rhs.name == 'IN':
            assert operator == '='
            operator = ' '
        if sql is None:
            if lhs is None:
                sql = '%s%s'% (operator, rhs.accept(self))
            else:
                sql = '%s%s%s'% (lhs.accept(self), operator, rhs.accept(self))
        if optional is None:
            return sql
        leftvars = cmp.children[0].get_nodes(VariableRef)
        assert len(leftvars) == 1
        if leftvars[0].variable.stinfo['attrvar'] is None:
            assert isinstance(leftvars[0].variable, ColumnAlias)
            leftalias = leftvars[0].variable._q_sqltable
        else:
            leftalias = self._var_table(leftvars[0].variable.stinfo['attrvar'])
        rightvars = cmp.children[1].get_nodes(VariableRef)
        assert len(rightvars) == 1
        if rightvars[0].variable.stinfo['attrvar'] is None:
            assert isinstance(rightvars[0].variable, ColumnAlias)
            rightalias = rightvars[0].variable._q_sqltable
        else:
            rightalias = self._var_table(rightvars[0].variable.stinfo['attrvar'])
        if optional == 'right':
            self._state.replace_tables_by_outer_join(
                leftalias, rightalias, 'LEFT', sql)
        elif optional == 'left':
            self._state.replace_tables_by_outer_join(
                rightalias, leftalias, 'LEFT', sql)
        else:
            self._state.replace_tables_by_outer_join(
                leftalias, rightalias, 'FULL', sql)
        return ''

    def visit_mathexpression(self, mexpr):
        """generate SQL for a mathematic expression"""
        lhs, rhs = mexpr.get_parts()
        # check for string concatenation
        operator = mexpr.operator
        if operator == '%':
            operator = '%%'
        try:
            if mexpr.operator == '+' and mexpr.get_type(self._state.solution, self._args) == 'String':
                return '(%s)' % self.dbhelper.sql_concat_string(lhs.accept(self),
                                                                rhs.accept(self))
        except CoercionError:
            pass
        return '(%s %s %s)'% (lhs.accept(self), operator, rhs.accept(self))

    def visit_unaryexpression(self, uexpr):
        """generate SQL for a unary expression"""
        return '%s%s'% (uexpr.operator, uexpr.children[0].accept(self))

    def visit_function(self, func):
        """generate SQL name for a function"""
        if func.name == 'FTIRANK':
            try:
                rel = iter(func.children[0].variable.stinfo['ftirels']).next()
            except KeyError:
                raise BadRQLQuery("can't use FTIRANK on variable not used in an"
                                  " 'has_text' relation (eg full-text search)")
            const = rel.get_parts()[1].children[0]
            return self.dbhelper.fti_rank_order(
                self._state.fti_table(rel, self.dbhelper.fti_table),
                const.eval(self._args))
        args = [c.accept(self) for c in func.children]
        if func in self._state.source_cb_funcs:
            # function executed as a callback on the source
            assert len(args) == 1
            return args[0]
        # func_as_sql will check function is supported by the backend
        return self.dbhelper.func_as_sql(func.name, args)

    def visit_constant(self, constant):
        """generate SQL name for a constant"""
        if constant.type is None:
            return 'NULL'
        value = constant.value
        if constant.type == 'etype':
            return value
        if constant.type == 'Int': # XXX Float?
            return str(value)
        if constant.type in ('Date', 'Datetime'):
            rel = constant.relation()
            if rel is not None:
                rel._q_needcast = value
            return self.keyword_map[value]()
        if constant.type == 'Boolean':
            return str(self.dbhelper.boolean_value(value))
        if constant.type == 'Substitute':
            try:
                # we may found constant from simplified var in varmap
                return self._mapped_term(constant, '%%(%s)s' % value)[0]
            except KeyError:
                _id = value
                if isinstance(_id, unicode):
                    _id = _id.encode()
                # convert timestamp to utc.
                # expect SET TiME ZONE to UTC at connection opening time.
                # This shouldn't change anything for datetime without TZ.
                value = self._args[_id]
                if isinstance(value, datetime) and value.tzinfo is not None:
                    self._query_attrs[_id] = utcdatetime(value)
                elif isinstance(value, time) and value.tzinfo is not None:
                    self._query_attrs[_id] = utctime(value)
        else:
            _id = str(id(constant)).replace('-', '', 1)
            self._query_attrs[_id] = value
        return '%%(%s)s' % _id

    def visit_variableref(self, variableref):
        """get the sql name for a variable reference"""
        # use accept, .variable may be a variable or a columnalias
        return variableref.variable.accept(self)

    def visit_columnalias(self, colalias):
        """get the sql name for a subquery column alias"""
        if colalias.name in self._varmap:
            sql = self._varmap[colalias.name]
            table = sql.split('.', 1)[0]
            colalias._q_sqltable = table
            colalias._q_sql = sql
            self._state.add_table(table)
            return sql
        return colalias._q_sql

    def visit_variable(self, variable):
        """get the table name and sql string for a variable"""
        #if contextrels is None and variable.name in self._state.done:
        if variable.name in self._state.done:
            if self._in_wrapping_query:
                return 'T1.%s' % self._state.aliases[variable.name]
            return variable._q_sql
        self._state.done.add(variable.name)
        vtablename = None
        if not self._state.ignore_varmap and variable.name in self._varmap:
            sql, vtablename = self._var_info(variable)
        elif variable.stinfo['attrvar']:
            # attribute variable (systematically used in rhs of final
            # relation(s)), get table name and sql from any rhs relation
            sql = self._linked_var_sql(variable)
        elif variable._q_invariant:
            # since variable is invariant, we know we won't found final relation
            principal = variable.stinfo['principal']
            if principal is None:
                vtablename = '_' + variable.name
                self._state.add_table('entities AS %s' % vtablename, vtablename)
                sql = '%s.eid' % vtablename
                if variable.stinfo['typerel'] is not None:
                    # add additional restriction on entities.type column
                    pts = variable.stinfo['possibletypes']
                    if len(pts) == 1:
                        etype = iter(variable.stinfo['possibletypes']).next()
                        restr = "%s.type='%s'" % (vtablename, etype)
                    else:
                        etypes = ','.join("'%s'" % et for et in pts)
                        restr = '%s.type IN (%s)' % (vtablename, etypes)
                    self._state.add_restriction(restr)
            elif principal.r_type == 'has_text':
                sql = '%s.%s' % (self._state.fti_table(principal,
                                                       self.dbhelper.fti_table),
                                 self.dbhelper.fti_uid_attr)
            elif principal in variable.stinfo['rhsrelations']:
                if self.schema.rschema(principal.r_type).inlined:
                    sql = self._linked_var_sql(variable)
                else:
                    sql = '%s.eid_to' % self._state.relation_table(principal)
            else:
                sql = '%s.eid_from' % self._state.relation_table(principal)
        else:
            # standard variable: get table name according to etype and use .eid
            # attribute
            sql, vtablename = self._var_info(variable)
        variable._q_sqltable = vtablename
        variable._q_sql = sql
        return sql

    # various utilities #######################################################

    def _extra_join_sql(self, relation, sql, var):
        # if rhs var is invariant, and this relation is not its principal,
        # generate extra join
        try:
            if not var.stinfo['principal'] is relation:
                op = relation.operator()
                if op == '=':
                    # need a predicable result for tests
                    args = sorted( (sql, var.accept(self)) )
                    args.insert(1, op)
                else:
                    args = (sql, op, var.accept(self))
                return '%s%s%s' % tuple(args)
        except KeyError:
            # no principal defined, relation is necessarily the principal and
            # so nothing to return here
            pass
        return None

    def _temp_table_scope(self, select, table):
        scope = 9999
        for var, sql in self._varmap.iteritems():
            # skip "attribute variable" in varmap (such 'T.login')
            if not '.' in var and table == sql.split('.', 1)[0]:
                try:
                    scope = min(scope, self._state.scopes[select.defined_vars[var].scope])
                except KeyError:
                    scope = 0 # XXX
                if scope == 0:
                    break
        return scope

    def _mapped_term(self, term, key):
        """return sql and table alias to the `term`, mapped as `key` or raise
        KeyError when the key is not found in the varmap
        """
        sql = self._varmap[key]
        tablealias = sql.split('.', 1)[0]
        scope = self._temp_table_scope(term.stmt, tablealias)
        self._state.add_table(tablealias, scope=scope)
        return sql, tablealias

    def _var_info(self, var):
        try:
            return self._mapped_term(var, var.name)
        except KeyError:
            scope = self._state.scopes[var.scope]
            etype = self._state.solution[var.name]
            # XXX this check should be moved in rql.stcheck
            if self.schema.eschema(etype).final:
                raise BadRQLQuery(var.stmt.root)
            tablealias = '_' + var.name
            sql = '%s.%seid' % (tablealias, SQL_PREFIX)
            self._state.add_table('%s%s AS %s' % (SQL_PREFIX, etype, tablealias),
                           tablealias, scope=scope)
        return sql, tablealias

    def _inlined_var_sql(self, var, rtype):
        try:
            sql = self._varmap['%s.%s' % (var.name, rtype)]
            scope = self._state.scopes[var.scope]
            self._state.add_table(sql.split('.', 1)[0], scope=scope)
        except KeyError:
            # rtype may be an attribute relation when called from
            # _visit_var_attr_relation.  take care about 'eid' rtype, since in
            # some case we may use the `entities` table, so in that case we've
            # to properly use variable'sql
            if rtype == 'eid':
                sql = var.accept(self)
            else:
                sql = '%s.%s%s' % (self._var_table(var), SQL_PREFIX, rtype)
        return sql

    def _linked_var_sql(self, variable):
        if not self._state.ignore_varmap:
            try:
                return self._varmap[variable.name]
            except KeyError:
                pass
        rel = (variable.stinfo.get('principal') or
               iter(variable.stinfo['rhsrelations']).next())
        linkedvar = rel.children[0].variable
        if rel.r_type == 'eid':
            return linkedvar.accept(self)
        if isinstance(linkedvar, ColumnAlias):
            raise BadRQLQuery('variable %s should be selected by the subquery'
                              % variable.name)
        try:
            sql = self._varmap['%s.%s' % (linkedvar.name, rel.r_type)]
        except KeyError:
            mapkey = '%s.%s' % (self._state.solution[linkedvar.name], rel.r_type)
            if mapkey in self.attr_map:
                cb, sourcecb = self.attr_map[mapkey]
                if not sourcecb:
                    return cb(self, linkedvar, rel)
                # attribute mapped at the source level (bfss for instance)
                stmt = rel.stmt
                for selectidx, vref in iter_mapped_var_sels(stmt, variable):
                    stack = [cb]
                    update_source_cb_stack(self._state, stmt, vref, stack)
                    self._state._needs_source_cb[selectidx] = stack
            linkedvar.accept(self)
            sql = '%s.%s%s' % (linkedvar._q_sqltable, SQL_PREFIX, rel.r_type)
        return sql

    # tables handling #########################################################

    def _var_table(self, var):
        var.accept(self)#.visit_variable(var)
        return var._q_sqltable