server/sources/rql2sql.py
author Sylvain Thénault <sylvain.thenault@logilab.fr>
Mon, 16 Nov 2009 09:36:25 +0100
branchstable
changeset 3852 03121ca1f85e
parent 3815 50b87f759b5d
child 3866 2783c166ad1a
permissions -rw-r--r--
test and fix case where remove_unsused_solutions remove some solutions that should be kept
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
     1
"""RQL to SQL generator for native sources.
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
     2
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
     3
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
     4
SQL queries optimization
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
     5
~~~~~~~~~~~~~~~~~~~~~~~~
1398
5fe84a5f7035 rename internal entity types to have CW prefix instead of E
sylvain.thenault@logilab.fr
parents: 1263
diff changeset
     6
1. CWUser X WHERE X in_group G, G name 'users':
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
     7
1398
5fe84a5f7035 rename internal entity types to have CW prefix instead of E
sylvain.thenault@logilab.fr
parents: 1263
diff changeset
     8
   CWUser is the only subject entity type for the in_group relation,
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
     9
   which allow us to do ::
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    10
1398
5fe84a5f7035 rename internal entity types to have CW prefix instead of E
sylvain.thenault@logilab.fr
parents: 1263
diff changeset
    11
     SELECT eid_from FROM in_group, CWGroup
5fe84a5f7035 rename internal entity types to have CW prefix instead of E
sylvain.thenault@logilab.fr
parents: 1263
diff changeset
    12
     WHERE in_group.eid_to = CWGroup.eid_from
5fe84a5f7035 rename internal entity types to have CW prefix instead of E
sylvain.thenault@logilab.fr
parents: 1263
diff changeset
    13
     AND CWGroup.name = 'users'
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    14
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    15
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    16
2. Any X WHERE X nonfinal1 Y, Y nonfinal2 Z
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    17
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    18
   -> direct join between nonfinal1 and nonfinal2, whatever X,Y, Z (unless
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    19
      inlined...)
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
    20
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    21
      NOT IMPLEMENTED (and quite hard to implement)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    22
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    23
Potential optimization information is collected by the querier, sql generation
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    24
is done according to this information
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    25
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    26
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    27
:organization: Logilab
1977
606923dff11b big bunch of copyright / docstring update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1862
diff changeset
    28
:copyright: 2001-2009 LOGILAB S.A. (Paris, FRANCE), license is LGPL v2.
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    29
:contact: http://www.logilab.fr/ -- mailto:contact@logilab.fr
1977
606923dff11b big bunch of copyright / docstring update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1862
diff changeset
    30
:license: GNU Lesser General Public License, v2.1 - http://www.gnu.org/licenses
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    31
"""
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    32
__docformat__ = "restructuredtext en"
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    33
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    34
import threading
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    35
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    36
from rql import BadRQLQuery, CoercionError
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    37
from rql.stmts import Union, Select
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    38
from rql.nodes import (SortTerm, VariableRef, Constant, Function, Not,
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    39
                       Variable, ColumnAlias, Relation, SubQuery, Exists)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    40
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    41
from cubicweb import server
1251
af40e615dc89 introduce a 'cw_' prefix on entity table and column names so we don't conflict with sql or DBMS specific keywords
sylvain.thenault@logilab.fr
parents: 1124
diff changeset
    42
from cubicweb.server.sqlutils import SQL_PREFIX
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    43
from cubicweb.server.utils import cleanup_solutions
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    44
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
    45
def _new_var(select, varname):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    46
    newvar = select.get_variable(varname)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    47
    if not 'relations' in newvar.stinfo:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    48
        # not yet initialized
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    49
        newvar.prepare_annotation()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    50
        newvar.stinfo['scope'] = select
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    51
        newvar._q_invariant = False
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    52
    return newvar
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    53
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    54
def _fill_to_wrap_rel(var, newselect, towrap, schema):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    55
    for rel in var.stinfo['relations'] - var.stinfo['rhsrelations']:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    56
        rschema = schema.rschema(rel.r_type)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    57
        if rschema.inlined:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    58
            towrap.add( (var, rel) )
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    59
            for vref in rel.children[1].iget_nodes(VariableRef):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    60
                newivar = _new_var(newselect, vref.name)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    61
                newselect.selection.append(VariableRef(newivar))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    62
                _fill_to_wrap_rel(vref.variable, newselect, towrap, schema)
3689
deb13e88e037 follow yams 0.25 api changes to improve performance
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3245
diff changeset
    63
        elif rschema.final:
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    64
            towrap.add( (var, rel) )
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
    65
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    66
def rewrite_unstable_outer_join(select, solutions, unstable, schema):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    67
    """if some optional variables are unstable, they should be selected in a
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    68
    subquery. This function check this and rewrite the rql syntax tree if
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    69
    necessary (in place). Return a boolean telling if the tree has been modified
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    70
    """
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    71
    torewrite = set()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    72
    modified = False
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    73
    for varname in tuple(unstable):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    74
        var = select.defined_vars[varname]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    75
        if not var.stinfo['optrelations']:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    76
            continue
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    77
        modified = True
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    78
        unstable.remove(varname)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    79
        torewrite.add(var)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    80
        newselect = Select()
339
c0a0ce6c0428 in some cases (eg ambiguous neged relations), INTERSECT should be used instead of DISTINCT
Sylvain Thenault <sylvain.thenault@logilab.fr>
parents: 0
diff changeset
    81
        newselect.need_distinct = newselect.need_intersect = False
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    82
        myunion = Union()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    83
        myunion.append(newselect)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    84
        # extract aliases / selection
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    85
        newvar = _new_var(newselect, var.name)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    86
        newselect.selection = [VariableRef(newvar)]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    87
        for avar in select.defined_vars.itervalues():
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    88
            if avar.stinfo['attrvar'] is var:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    89
                newavar = _new_var(newselect, avar.name)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    90
                newavar.stinfo['attrvar'] = newvar
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    91
                newselect.selection.append(VariableRef(newavar))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    92
        towrap_rels = set()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    93
        _fill_to_wrap_rel(var, newselect, towrap_rels, schema)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    94
        # extract relations
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    95
        for var, rel in towrap_rels:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    96
            newrel = rel.copy(newselect)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    97
            newselect.add_restriction(newrel)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    98
            select.remove_node(rel)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    99
            var.stinfo['relations'].remove(rel)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   100
            newvar.stinfo['relations'].add(newrel)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   101
            if rel.optional in ('left', 'both'):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   102
                newvar.stinfo['optrelations'].add(newrel)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   103
            for vref in newrel.children[1].iget_nodes(VariableRef):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   104
                var = vref.variable
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   105
                var.stinfo['relations'].add(newrel)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   106
                var.stinfo['rhsrelations'].add(newrel)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   107
                if rel.optional in ('right', 'both'):
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   108
                    var.stinfo['optrelations'].add(newrel)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   109
        # extract subquery solutions
3736
07196bda2456 take care to already existant solutions local variable
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3689
diff changeset
   110
        mysolutions = [sol.copy() for sol in solutions]
07196bda2456 take care to already existant solutions local variable
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3689
diff changeset
   111
        cleanup_solutions(newselect, mysolutions)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   112
        newselect.set_possible_types(solutions)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   113
        # full sub-query
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   114
        aliases = [VariableRef(select.get_variable(avar.name, i))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   115
                   for i, avar in enumerate(newselect.selection)]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   116
        select.add_subquery(SubQuery(aliases, myunion), check=False)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   117
    return modified
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   118
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   119
def _new_solutions(rqlst, solutions):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   120
    """first filter out subqueries variables from solutions"""
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   121
    newsolutions = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   122
    for origsol in solutions:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   123
        asol = {}
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   124
        for vname in rqlst.defined_vars:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   125
            asol[vname] = origsol[vname]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   126
        if not asol in newsolutions:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   127
            newsolutions.append(asol)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   128
    return newsolutions
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   129
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   130
def remove_unused_solutions(rqlst, solutions, varmap, schema):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   131
    """cleanup solutions: remove solutions where invariant variables are taking
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   132
    different types
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   133
    """
3852
03121ca1f85e test and fix case where remove_unsused_solutions remove some solutions that should be kept
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3815
diff changeset
   134
    newsols = _new_solutions(rqlst, solutions)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   135
    existssols = {}
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   136
    unstable = set()
3852
03121ca1f85e test and fix case where remove_unsused_solutions remove some solutions that should be kept
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3815
diff changeset
   137
    invariants = {}
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   138
    for vname, var in rqlst.defined_vars.iteritems():
3852
03121ca1f85e test and fix case where remove_unsused_solutions remove some solutions that should be kept
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3815
diff changeset
   139
        vtype = newsols[0][vname]
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   140
        if var._q_invariant or vname in varmap:
3852
03121ca1f85e test and fix case where remove_unsused_solutions remove some solutions that should be kept
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3815
diff changeset
   141
            # remove invariant variable from solutions to remove duplicates
03121ca1f85e test and fix case where remove_unsused_solutions remove some solutions that should be kept
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3815
diff changeset
   142
            # later, then reinserting a type for the variable even later
03121ca1f85e test and fix case where remove_unsused_solutions remove some solutions that should be kept
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3815
diff changeset
   143
            for sol in newsols:
03121ca1f85e test and fix case where remove_unsused_solutions remove some solutions that should be kept
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3815
diff changeset
   144
                invariants.setdefault(id(sol), {})[vname] = sol.pop(vname)
03121ca1f85e test and fix case where remove_unsused_solutions remove some solutions that should be kept
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3815
diff changeset
   145
        elif var.scope is not rqlst:
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   146
            # move appart variables which are in a EXISTS scope and are variating
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   147
            try:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   148
                thisexistssols, thisexistsvars = existssols[var.scope]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   149
            except KeyError:
3852
03121ca1f85e test and fix case where remove_unsused_solutions remove some solutions that should be kept
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3815
diff changeset
   150
                thisexistssols = [newsols[0]]
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   151
                thisexistsvars = set()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   152
                existssols[var.scope] = thisexistssols, thisexistsvars
3852
03121ca1f85e test and fix case where remove_unsused_solutions remove some solutions that should be kept
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3815
diff changeset
   153
            for i in xrange(len(newsols)-1, 0, -1):
03121ca1f85e test and fix case where remove_unsused_solutions remove some solutions that should be kept
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3815
diff changeset
   154
                if vtype != newsols[i][vname]:
03121ca1f85e test and fix case where remove_unsused_solutions remove some solutions that should be kept
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3815
diff changeset
   155
                    thisexistssols.append(newsols.pop(i))
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   156
                    thisexistsvars.add(vname)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   157
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   158
            # remember unstable variables
3852
03121ca1f85e test and fix case where remove_unsused_solutions remove some solutions that should be kept
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3815
diff changeset
   159
            for i in xrange(1, len(newsols)):
03121ca1f85e test and fix case where remove_unsused_solutions remove some solutions that should be kept
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3815
diff changeset
   160
                if vtype != newsols[i][vname]:
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   161
                    unstable.add(vname)
3852
03121ca1f85e test and fix case where remove_unsused_solutions remove some solutions that should be kept
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3815
diff changeset
   162
    if invariants:
03121ca1f85e test and fix case where remove_unsused_solutions remove some solutions that should be kept
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3815
diff changeset
   163
        # filter out duplicates
03121ca1f85e test and fix case where remove_unsused_solutions remove some solutions that should be kept
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3815
diff changeset
   164
        newsols_ = []
03121ca1f85e test and fix case where remove_unsused_solutions remove some solutions that should be kept
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3815
diff changeset
   165
        for sol in newsols:
03121ca1f85e test and fix case where remove_unsused_solutions remove some solutions that should be kept
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3815
diff changeset
   166
            if not sol in newsols_:
03121ca1f85e test and fix case where remove_unsused_solutions remove some solutions that should be kept
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3815
diff changeset
   167
                newsols_.append(sol)
03121ca1f85e test and fix case where remove_unsused_solutions remove some solutions that should be kept
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3815
diff changeset
   168
        newsols = newsols_
03121ca1f85e test and fix case where remove_unsused_solutions remove some solutions that should be kept
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3815
diff changeset
   169
        # reinsert solutions for invariants
03121ca1f85e test and fix case where remove_unsused_solutions remove some solutions that should be kept
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3815
diff changeset
   170
        for sol in newsols:
03121ca1f85e test and fix case where remove_unsused_solutions remove some solutions that should be kept
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3815
diff changeset
   171
            for invvar, vartype in invariants[id(sol)].iteritems():
03121ca1f85e test and fix case where remove_unsused_solutions remove some solutions that should be kept
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3815
diff changeset
   172
                sol[invvar] = vartype
03121ca1f85e test and fix case where remove_unsused_solutions remove some solutions that should be kept
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3815
diff changeset
   173
        for sol in existssols:
03121ca1f85e test and fix case where remove_unsused_solutions remove some solutions that should be kept
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3815
diff changeset
   174
            for invvar, vartype in invariants[id(sol)].iteritems():
03121ca1f85e test and fix case where remove_unsused_solutions remove some solutions that should be kept
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3815
diff changeset
   175
                sol[invvar] = vartype
03121ca1f85e test and fix case where remove_unsused_solutions remove some solutions that should be kept
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3815
diff changeset
   176
    if len(newsols) > 1:
03121ca1f85e test and fix case where remove_unsused_solutions remove some solutions that should be kept
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3815
diff changeset
   177
        if rewrite_unstable_outer_join(rqlst, newsols, unstable, schema):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   178
            # remove variables extracted to subqueries from solutions
3852
03121ca1f85e test and fix case where remove_unsused_solutions remove some solutions that should be kept
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3815
diff changeset
   179
            newsols = _new_solutions(rqlst, newsols)
03121ca1f85e test and fix case where remove_unsused_solutions remove some solutions that should be kept
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3815
diff changeset
   180
    return newsols, existssols, unstable
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   181
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   182
def relation_info(relation):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   183
    lhs, rhs = relation.get_variable_parts()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   184
    try:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   185
        lhs = lhs.variable
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   186
        lhsconst = lhs.stinfo['constnode']
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   187
    except AttributeError:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   188
        lhsconst = lhs
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   189
        lhs = None
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   190
    except KeyError:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   191
        lhsconst = None # ColumnAlias
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   192
    try:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   193
        rhs = rhs.variable
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   194
        rhsconst = rhs.stinfo['constnode']
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   195
    except AttributeError:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   196
        rhsconst = rhs
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   197
        rhs = None
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   198
    except KeyError:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   199
        rhsconst = None # ColumnAlias
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   200
    return lhs, lhsconst, rhs, rhsconst
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   201
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   202
def switch_relation_field(sql, table=''):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   203
    switchedsql = sql.replace(table + '.eid_from', '__eid_from__')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   204
    switchedsql = switchedsql.replace(table + '.eid_to',
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   205
                                      table + '.eid_from')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   206
    return switchedsql.replace('__eid_from__', table + '.eid_to')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   207
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   208
def sort_term_selection(sorts, selectedidx, rqlst, groups):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   209
    # XXX beurk
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   210
    if isinstance(rqlst, list):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   211
        def append(term):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   212
            rqlst.append(term)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   213
    else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   214
        def append(term):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   215
            rqlst.selection.append(term.copy(rqlst))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   216
    for sortterm in sorts:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   217
        term = sortterm.term
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   218
        if not isinstance(term, Constant) and not str(term) in selectedidx:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   219
            selectedidx.append(str(term))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   220
            append(term)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   221
            if groups:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   222
                for vref in term.iget_nodes(VariableRef):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   223
                    if not vref in groups:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   224
                        groups.append(vref)
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   225
3752
4c77a1653374 when a query is grouped, ensure sort terms are grouped as well
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3736
diff changeset
   226
def fix_selection_and_group(rqlst, selectedidx, needwrap, selectsortterms,
4c77a1653374 when a query is grouped, ensure sort terms are grouped as well
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3736
diff changeset
   227
                            sorts, groups, having):
4c77a1653374 when a query is grouped, ensure sort terms are grouped as well
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3736
diff changeset
   228
    if selectsortterms and sorts:
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   229
        sort_term_selection(sorts, selectedidx, rqlst, not needwrap and groups)
3752
4c77a1653374 when a query is grouped, ensure sort terms are grouped as well
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3736
diff changeset
   230
    if sorts and groups:
3754
a9c641f6193b document
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3752
diff changeset
   231
        # when a query is grouped, ensure sort terms are grouped as well
3752
4c77a1653374 when a query is grouped, ensure sort terms are grouped as well
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3736
diff changeset
   232
        for sortterm in sorts:
4c77a1653374 when a query is grouped, ensure sort terms are grouped as well
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3736
diff changeset
   233
            term = sortterm.term
4c77a1653374 when a query is grouped, ensure sort terms are grouped as well
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3736
diff changeset
   234
            if not isinstance(term, Constant):
4c77a1653374 when a query is grouped, ensure sort terms are grouped as well
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3736
diff changeset
   235
                for vref in term.iget_nodes(VariableRef):
4c77a1653374 when a query is grouped, ensure sort terms are grouped as well
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3736
diff changeset
   236
                    if not vref in groups:
4c77a1653374 when a query is grouped, ensure sort terms are grouped as well
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3736
diff changeset
   237
                        groups.append(vref)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   238
    if needwrap:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   239
        if groups:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   240
            for vref in groups:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   241
                if not vref.name in selectedidx:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   242
                    selectedidx.append(vref.name)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   243
                    rqlst.selection.append(vref)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   244
        if having:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   245
            for term in having:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   246
                for vref in term.iget_nodes(VariableRef):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   247
                    if not vref.name in selectedidx:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   248
                        selectedidx.append(vref.name)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   249
                        rqlst.selection.append(vref)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   250
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   251
# IGenerator implementation for RQL->SQL ######################################
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   252
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   253
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   254
class StateInfo(object):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   255
    def __init__(self, existssols, unstablevars):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   256
        self.existssols = existssols
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   257
        self.unstablevars = unstablevars
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   258
        self.subtables = {}
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   259
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   260
    def reset(self, solution):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   261
        """reset some visit variables"""
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   262
        self.solution = solution
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   263
        self.count = 0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   264
        self.done = set()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   265
        self.tables = self.subtables.copy()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   266
        self.actual_tables = [[]]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   267
        for _, tsql in self.tables.itervalues():
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   268
            self.actual_tables[-1].append(tsql)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   269
        self.outer_tables = {}
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   270
        self.duplicate_switches = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   271
        self.aliases = {}
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   272
        self.restrictions = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   273
        self._restr_stack = []
3815
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
   274
        self.ignore_varmap = False
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   275
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   276
    def add_restriction(self, restr):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   277
        if restr:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   278
            self.restrictions.append(restr)
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   279
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   280
    def iter_exists_sols(self, exists):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   281
        if not exists in self.existssols:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   282
            yield 1
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   283
            return
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   284
        thisexistssols, thisexistsvars = self.existssols[exists]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   285
        origsol = self.solution
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   286
        origtables = self.tables
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   287
        done = self.done
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   288
        for thisexistssol in thisexistssols:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   289
            for vname in self.unstablevars:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   290
                if thisexistssol[vname] != origsol[vname] and vname in thisexistsvars:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   291
                    break
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   292
            else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   293
                self.tables = origtables.copy()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   294
                self.solution = thisexistssol
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   295
                yield 1
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   296
                # cleanup self.done from stuff specific to exists
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   297
                for var in thisexistsvars:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   298
                    if var in done:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   299
                        done.remove(var)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   300
                for rel in exists.iget_nodes(Relation):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   301
                    if rel in done:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   302
                        done.remove(rel)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   303
        self.solution = origsol
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   304
        self.tables = origtables
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   305
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   306
    def push_scope(self):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   307
        self.actual_tables.append([])
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   308
        self._restr_stack.append(self.restrictions)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   309
        self.restrictions = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   310
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   311
    def pop_scope(self):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   312
        restrictions = self.restrictions
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   313
        self.restrictions = self._restr_stack.pop()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   314
        return restrictions, self.actual_tables.pop()
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   315
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   316
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   317
class SQLGenerator(object):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   318
    """
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   319
    generation of SQL from the fully expanded RQL syntax tree
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   320
    SQL is designed to be used with a CubicWeb SQL schema
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   321
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   322
    Groups and sort are not handled here since they should not be handled at
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   323
    this level (see cubicweb.server.querier)
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   324
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   325
    we should not have errors here !
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   326
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   327
    WARNING: a CubicWebSQLGenerator instance is not thread safe, but generate is
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   328
    protected by a lock
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   329
    """
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   330
2354
9b4bac626977 ability to map attributes to something else than usual cw mapping on sql generation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2199
diff changeset
   331
    def __init__(self, schema, dbms_helper, dbencoding='UTF-8', attrmap=None):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   332
        self.schema = schema
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   333
        self.dbms_helper = dbms_helper
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   334
        self.dbencoding = dbencoding
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   335
        self.keyword_map = {'NOW' : self.dbms_helper.sql_current_timestamp,
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   336
                            'TODAY': self.dbms_helper.sql_current_date,
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   337
                            }
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   338
        if not self.dbms_helper.union_parentheses_support:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   339
            self.union_sql = self.noparen_union_sql
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   340
        self._lock = threading.Lock()
2354
9b4bac626977 ability to map attributes to something else than usual cw mapping on sql generation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2199
diff changeset
   341
        if attrmap is None:
9b4bac626977 ability to map attributes to something else than usual cw mapping on sql generation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2199
diff changeset
   342
            attrmap = {}
9b4bac626977 ability to map attributes to something else than usual cw mapping on sql generation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2199
diff changeset
   343
        self.attr_map = attrmap
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   344
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   345
    def generate(self, union, args=None, varmap=None):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   346
        """return SQL queries and a variable dictionnary from a RQL syntax tree
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   347
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   348
        :partrqls: a list of couple (rqlst, solutions)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   349
        :args: optional dictionary with values of substitutions used in the query
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   350
        :varmap: optional dictionary mapping variable name to a special table
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   351
          name, in case the query as to fetch data from temporary tables
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   352
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   353
        return an sql string and a dictionary with substitutions values
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   354
        """
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   355
        if args is None:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   356
            args = {}
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   357
        if varmap is None:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   358
            varmap =  {}
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   359
        self._lock.acquire()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   360
        self._args = args
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   361
        self._varmap = varmap
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   362
        self._query_attrs = {}
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   363
        self._state = None
3238
988a72e59b2b [querier] fix sql generated w/ NOT relation and shared variable: ensure variable's table is in parent select'scope
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2915
diff changeset
   364
        self._not_scope_offset = 0
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   365
        try:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   366
            # union query for each rqlst / solution
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   367
            sql = self.union_sql(union)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   368
            # we are done
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   369
            return sql, self._query_attrs
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   370
        finally:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   371
            self._lock.release()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   372
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   373
    def union_sql(self, union, needalias=False): # pylint: disable-msg=E0202
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   374
        if len(union.children) == 1:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   375
            return self.select_sql(union.children[0], needalias)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   376
        sqls = ('(%s)' % self.select_sql(select, needalias)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   377
                for select in union.children)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   378
        return '\nUNION ALL\n'.join(sqls)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   379
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   380
    def noparen_union_sql(self, union, needalias=False):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   381
        # needed for sqlite backend which doesn't like parentheses around
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   382
        # union query. This may cause bug in some condition (sort in one of
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   383
        # the subquery) but will work in most case
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   384
        # see http://www.sqlite.org/cvstrac/tktview?tn=3074
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   385
        sqls = (self.select_sql(select, needalias)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   386
                for i, select in enumerate(union.children))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   387
        return '\nUNION ALL\n'.join(sqls)
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   388
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   389
    def select_sql(self, select, needalias=False):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   390
        """return SQL queries and a variable dictionnary from a RQL syntax tree
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   391
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   392
        :select: a selection statement of the syntax tree (`rql.stmts.Select`)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   393
        :solution: a dictionnary containing variables binding.
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   394
          A solution's dictionnary has variable's names as key and variable's
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   395
          types as values
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   396
        :needwrap: boolean telling if the query will be wrapped in an outer
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   397
          query (to deal with aggregat and/or grouping)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   398
        """
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   399
        distinct = selectsortterms = select.need_distinct
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   400
        sorts = select.orderby
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   401
        groups = select.groupby
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   402
        having = select.having
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   403
        # remember selection, it may be changed and have to be restored
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   404
        origselection = select.selection[:]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   405
        # check if the query will have union subquery, if it need sort term
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   406
        # selection (union or distinct query) and wrapping (union with groups)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   407
        needwrap = False
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   408
        sols = select.solutions
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   409
        if len(sols) > 1:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   410
            # remove invariant from solutions
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   411
            sols, existssols, unstable = remove_unused_solutions(
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   412
                select, sols, self._varmap, self.schema)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   413
            if len(sols) > 1:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   414
                # if there is still more than one solution, a UNION will be
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   415
                # generated and so sort terms have to be selected
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   416
                selectsortterms = True
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   417
                # and if select is using group by or aggregat, a wrapping
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   418
                # query will be necessary
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   419
                if groups or select.has_aggregat:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   420
                    select.select_only_variables()
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   421
                    needwrap = True
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   422
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   423
            existssols, unstable = {}, ()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   424
        state = StateInfo(existssols, unstable)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   425
        # treat subqueries
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   426
        self._subqueries_sql(select, state)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   427
        # generate sql for this select node
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   428
        selectidx = [str(term) for term in select.selection]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   429
        if needwrap:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   430
            outerselection = origselection[:]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   431
            if sorts and selectsortterms:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   432
                outerselectidx = [str(term) for term in outerselection]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   433
                if distinct:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   434
                    sort_term_selection(sorts, outerselectidx,
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   435
                                        outerselection, groups)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   436
            else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   437
                outerselectidx = selectidx[:]
3752
4c77a1653374 when a query is grouped, ensure sort terms are grouped as well
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3736
diff changeset
   438
        fix_selection_and_group(select, selectidx, needwrap,
4c77a1653374 when a query is grouped, ensure sort terms are grouped as well
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3736
diff changeset
   439
                                selectsortterms, sorts, groups, having)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   440
        if needwrap:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   441
            fselectidx = outerselectidx
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   442
            fneedwrap = len(outerselection) != len(origselection)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   443
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   444
            fselectidx = selectidx
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   445
            fneedwrap = len(select.selection) != len(origselection)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   446
        if fneedwrap:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   447
            needalias = True
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   448
        self._in_wrapping_query = False
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   449
        self._state = state
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   450
        try:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   451
            sql = self._solutions_sql(select, sols, distinct, needalias or needwrap)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   452
            # generate groups / having before wrapping query selection to
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   453
            # get correct column aliases
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   454
            self._in_wrapping_query = needwrap
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   455
            if groups:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   456
                # no constant should be inserted in GROUP BY else the backend will
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   457
                # interpret it as a positional index in the selection
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   458
                groups = ','.join(vref.accept(self) for vref in groups
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   459
                                  if not isinstance(vref, Constant))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   460
            if having:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   461
                # filter out constants as for GROUP BY
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   462
                having = ','.join(vref.accept(self) for vref in having
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   463
                                  if not isinstance(vref, Constant))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   464
            if needwrap:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   465
                sql = '%s FROM (%s) AS T1' % (self._selection_sql(outerselection, distinct,
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   466
                                                                  needalias),
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   467
                                              sql)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   468
            if groups:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   469
                sql += '\nGROUP BY %s' % groups
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   470
            if having:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   471
                sql += '\nHAVING %s' % having
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   472
            # sort
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   473
            if sorts:
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   474
                sql += '\nORDER BY %s' % ','.join(self._sortterm_sql(sortterm,
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   475
                                                                     fselectidx)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   476
                                                  for sortterm in sorts)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   477
                if fneedwrap:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   478
                    selection = ['T1.C%s' % i for i in xrange(len(origselection))]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   479
                    sql = 'SELECT %s FROM (%s) AS T1' % (','.join(selection), sql)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   480
        finally:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   481
            select.selection = origselection
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   482
        # limit / offset
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   483
        limit = select.limit
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   484
        if limit:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   485
            sql += '\nLIMIT %s' % limit
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   486
        offset = select.offset
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   487
        if offset:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   488
            sql += '\nOFFSET %s' % offset
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   489
        return sql
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   490
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   491
    def _subqueries_sql(self, select, state):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   492
        for i, subquery in enumerate(select.with_):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   493
            sql = self.union_sql(subquery.query, needalias=True)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   494
            tablealias = '_T%s' % i
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   495
            sql = '(%s) AS %s' % (sql, tablealias)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   496
            state.subtables[tablealias] = (0, sql)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   497
            for vref in subquery.aliases:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   498
                alias = vref.variable
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   499
                alias._q_sqltable = tablealias
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   500
                alias._q_sql = '%s.C%s' % (tablealias, alias.colnum)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   501
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   502
    def _solutions_sql(self, select, solutions, distinct, needalias):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   503
        sqls = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   504
        for solution in solutions:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   505
            self._state.reset(solution)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   506
            # visit restriction subtree
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   507
            if select.where is not None:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   508
                self._state.add_restriction(select.where.accept(self))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   509
            sql = [self._selection_sql(select.selection, distinct, needalias)]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   510
            if self._state.restrictions:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   511
                sql.append('WHERE %s' % ' AND '.join(self._state.restrictions))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   512
            # add required tables
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   513
            assert len(self._state.actual_tables) == 1, self._state.actual_tables
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   514
            tables = self._state.actual_tables[-1]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   515
            if tables:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   516
                # sort for test predictability
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   517
                sql.insert(1, 'FROM %s' % ', '.join(sorted(tables)))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   518
            elif self._state.restrictions and self.dbms_helper.needs_from_clause:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   519
                sql.insert(1, 'FROM (SELECT 1) AS _T')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   520
            sqls.append('\n'.join(sql))
339
c0a0ce6c0428 in some cases (eg ambiguous neged relations), INTERSECT should be used instead of DISTINCT
Sylvain Thenault <sylvain.thenault@logilab.fr>
parents: 0
diff changeset
   521
        if select.need_intersect:
2199
bd0a0f219751 fix sql generated on NOT inlined_relation queries. Use exists, so no more needs for extra DISTINCT
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2073
diff changeset
   522
            #if distinct or not self.dbms_helper.intersect_all_support:
bd0a0f219751 fix sql generated on NOT inlined_relation queries. Use exists, so no more needs for extra DISTINCT
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2073
diff changeset
   523
            return '\nINTERSECT\n'.join(sqls)
bd0a0f219751 fix sql generated on NOT inlined_relation queries. Use exists, so no more needs for extra DISTINCT
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2073
diff changeset
   524
            #else:
bd0a0f219751 fix sql generated on NOT inlined_relation queries. Use exists, so no more needs for extra DISTINCT
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2073
diff changeset
   525
            #    return '\nINTERSECT ALL\n'.join(sqls)
339
c0a0ce6c0428 in some cases (eg ambiguous neged relations), INTERSECT should be used instead of DISTINCT
Sylvain Thenault <sylvain.thenault@logilab.fr>
parents: 0
diff changeset
   526
        elif distinct:
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   527
            return '\nUNION\n'.join(sqls)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   528
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   529
            return '\nUNION ALL\n'.join(sqls)
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   530
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   531
    def _selection_sql(self, selected, distinct, needaliasing=False):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   532
        clause = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   533
        for term in selected:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   534
            sql = term.accept(self)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   535
            if needaliasing:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   536
                colalias = 'C%s' % len(clause)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   537
                clause.append('%s AS %s' % (sql, colalias))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   538
                if isinstance(term, VariableRef):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   539
                    self._state.aliases[term.name] = colalias
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   540
            else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   541
                clause.append(sql)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   542
        if distinct:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   543
            return 'SELECT DISTINCT %s' % ', '.join(clause)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   544
        return 'SELECT %s' % ', '.join(clause)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   545
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   546
    def _sortterm_sql(self, sortterm, selectidx):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   547
        term = sortterm.term
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   548
        try:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   549
            sqlterm = str(selectidx.index(str(term)) + 1)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   550
        except ValueError:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   551
            # Constant node or non selected term
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   552
            sqlterm = str(term.accept(self))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   553
        if sortterm.asc:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   554
            return sqlterm
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   555
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   556
            return '%s DESC' % sqlterm
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   557
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   558
    def visit_and(self, et):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   559
        """generate SQL for a AND subtree"""
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   560
        res = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   561
        for c in et.children:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   562
            part = c.accept(self)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   563
            if part:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   564
                res.append(part)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   565
        return ' AND '.join(res)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   566
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   567
    def visit_or(self, ou):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   568
        """generate SQL for a OR subtree"""
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   569
        res = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   570
        for c in ou.children:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   571
            part = c.accept(self)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   572
            if part:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   573
                res.append('(%s)' % part)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   574
        if res:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   575
            if len(res) > 1:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   576
                return '(%s)' % ' OR '.join(res)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   577
            return res[0]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   578
        return ''
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   579
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   580
    def visit_not(self, node):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   581
        self._state.push_scope()
3238
988a72e59b2b [querier] fix sql generated w/ NOT relation and shared variable: ensure variable's table is in parent select'scope
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2915
diff changeset
   582
        if isinstance(node.children[0], Relation):
988a72e59b2b [querier] fix sql generated w/ NOT relation and shared variable: ensure variable's table is in parent select'scope
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2915
diff changeset
   583
            self._not_scope_offset += 1
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   584
        csql = node.children[0].accept(self)
3238
988a72e59b2b [querier] fix sql generated w/ NOT relation and shared variable: ensure variable's table is in parent select'scope
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2915
diff changeset
   585
        if isinstance(node.children[0], Relation):
988a72e59b2b [querier] fix sql generated w/ NOT relation and shared variable: ensure variable's table is in parent select'scope
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2915
diff changeset
   586
            self._not_scope_offset -= 1
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   587
        sqls, tables = self._state.pop_scope()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   588
        if node in self._state.done or not csql:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   589
            # already processed or no sql generated by children
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   590
            self._state.actual_tables[-1] += tables
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   591
            self._state.restrictions += sqls
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   592
            return csql
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   593
        if isinstance(node.children[0], Exists):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   594
            assert not sqls, (sqls, str(node.stmt))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   595
            assert not tables, (tables, str(node.stmt))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   596
            return 'NOT %s' % csql
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   597
        sqls.append(csql)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   598
        if tables:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   599
            select = 'SELECT 1 FROM %s' % ','.join(tables)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   600
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   601
            select = 'SELECT 1'
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   602
        if sqls:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   603
            sql = 'NOT EXISTS(%s WHERE %s)' % (select, ' AND '.join(sqls))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   604
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   605
            sql = 'NOT EXISTS(%s)' % select
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   606
        return sql
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   607
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   608
    def visit_exists(self, exists):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   609
        """generate SQL name for a exists subquery"""
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   610
        sqls = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   611
        for dummy in self._state.iter_exists_sols(exists):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   612
            sql = self._visit_exists(exists)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   613
            if sql:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   614
                sqls.append(sql)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   615
        if not sqls:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   616
            return ''
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   617
        return 'EXISTS(%s)' % ' UNION '.join(sqls)
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   618
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   619
    def _visit_exists(self, exists):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   620
        self._state.push_scope()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   621
        restriction = exists.children[0].accept(self)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   622
        restrictions, tables = self._state.pop_scope()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   623
        if restriction:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   624
            restrictions.append(restriction)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   625
        restriction = ' AND '.join(restrictions)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   626
        if not restriction:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   627
            return ''
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   628
        if not tables:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   629
            # XXX could leave surrounding EXISTS() in this case no?
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   630
            sql = 'SELECT 1 WHERE %s' % restriction
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   631
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   632
            sql = 'SELECT 1 FROM %s WHERE %s' % (', '.join(tables), restriction)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   633
        return sql
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   634
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   635
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   636
    def visit_relation(self, relation):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   637
        """generate SQL for a relation"""
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   638
        rtype = relation.r_type
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   639
        # don't care of type constraint statement (i.e. relation_type = 'is')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   640
        if relation.is_types_restriction():
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   641
            return ''
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   642
        lhs, rhs = relation.get_parts()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   643
        rschema = self.schema.rschema(rtype)
3689
deb13e88e037 follow yams 0.25 api changes to improve performance
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3245
diff changeset
   644
        if rschema.final:
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   645
            if rtype == 'eid' and lhs.variable._q_invariant and \
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   646
                   lhs.variable.stinfo['constnode']:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   647
                # special case where this restriction is already generated by
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   648
                # some other relation
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   649
                return ''
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   650
            # attribute relation
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   651
            if rtype == 'has_text':
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   652
                sql = self._visit_has_text_relation(relation)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   653
            else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   654
                rhs_vars = rhs.get_nodes(VariableRef)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   655
                if rhs_vars:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   656
                    # if variable(s) in the RHS
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   657
                    sql = self._visit_var_attr_relation(relation, rhs_vars)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   658
                else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   659
                    # no variables in the RHS
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   660
                    sql = self._visit_attribute_relation(relation)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   661
                if relation.neged(strict=True):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   662
                    self._state.done.add(relation.parent)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   663
                    sql = 'NOT (%s)' % sql
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   664
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   665
            if rtype == 'is' and rhs.operator == 'IS':
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   666
                # special case "C is NULL"
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   667
                if lhs.name in self._varmap:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   668
                    lhssql = self._varmap[lhs.name]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   669
                else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   670
                    lhssql = lhs.accept(self)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   671
                return '%s%s' % (lhssql, rhs.accept(self))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   672
            if '%s.%s' % (lhs, relation.r_type) in self._varmap:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   673
                # relation has already been processed by a previous step
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   674
                return
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   675
            if relation.optional:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   676
                # check it has not already been treaten (to get necessary
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   677
                # information to add an outer join condition)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   678
                if relation in self._state.done:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   679
                    return
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   680
                # OPTIONAL relation, generate a left|right outer join
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   681
                sql = self._visit_outer_join_relation(relation, rschema)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   682
            elif rschema.inlined:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   683
                sql = self._visit_inlined_relation(relation)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   684
            else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   685
                # regular (non final) relation
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   686
                sql = self._visit_relation(relation, rschema)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   687
        return sql
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   688
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   689
    def _visit_inlined_relation(self, relation):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   690
        lhsvar, _, rhsvar, rhsconst = relation_info(relation)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   691
        # we are sure here to have a lhsvar
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   692
        assert lhsvar is not None
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   693
        if isinstance(relation.parent, Not):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   694
            self._state.done.add(relation.parent)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   695
            if rhsvar is not None and not rhsvar._q_invariant:
2199
bd0a0f219751 fix sql generated on NOT inlined_relation queries. Use exists, so no more needs for extra DISTINCT
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2073
diff changeset
   696
                # if the lhs variable is only linked to this relation, this mean we
bd0a0f219751 fix sql generated on NOT inlined_relation queries. Use exists, so no more needs for extra DISTINCT
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2073
diff changeset
   697
                # only want the relation to NOT exists
bd0a0f219751 fix sql generated on NOT inlined_relation queries. Use exists, so no more needs for extra DISTINCT
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2073
diff changeset
   698
                self._state.push_scope()
bd0a0f219751 fix sql generated on NOT inlined_relation queries. Use exists, so no more needs for extra DISTINCT
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2073
diff changeset
   699
                lhssql = self._inlined_var_sql(lhsvar, relation.r_type)
bd0a0f219751 fix sql generated on NOT inlined_relation queries. Use exists, so no more needs for extra DISTINCT
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2073
diff changeset
   700
                rhssql = rhsvar.accept(self)
bd0a0f219751 fix sql generated on NOT inlined_relation queries. Use exists, so no more needs for extra DISTINCT
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2073
diff changeset
   701
                restrictions, tables = self._state.pop_scope()
bd0a0f219751 fix sql generated on NOT inlined_relation queries. Use exists, so no more needs for extra DISTINCT
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2073
diff changeset
   702
                restrictions.append('%s=%s' % (lhssql, rhssql))
bd0a0f219751 fix sql generated on NOT inlined_relation queries. Use exists, so no more needs for extra DISTINCT
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2073
diff changeset
   703
                if not tables:
bd0a0f219751 fix sql generated on NOT inlined_relation queries. Use exists, so no more needs for extra DISTINCT
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2073
diff changeset
   704
                    sql = 'NOT EXISTS(SELECT 1 WHERE %s)' % (
bd0a0f219751 fix sql generated on NOT inlined_relation queries. Use exists, so no more needs for extra DISTINCT
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2073
diff changeset
   705
                        ' AND '.join(restrictions))
bd0a0f219751 fix sql generated on NOT inlined_relation queries. Use exists, so no more needs for extra DISTINCT
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2073
diff changeset
   706
                else:
bd0a0f219751 fix sql generated on NOT inlined_relation queries. Use exists, so no more needs for extra DISTINCT
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2073
diff changeset
   707
                    sql = 'NOT EXISTS(SELECT 1 FROM %s WHERE %s)' % (
bd0a0f219751 fix sql generated on NOT inlined_relation queries. Use exists, so no more needs for extra DISTINCT
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2073
diff changeset
   708
                        ', '.join(tables), ' AND '.join(restrictions))
bd0a0f219751 fix sql generated on NOT inlined_relation queries. Use exists, so no more needs for extra DISTINCT
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2073
diff changeset
   709
            else:
bd0a0f219751 fix sql generated on NOT inlined_relation queries. Use exists, so no more needs for extra DISTINCT
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2073
diff changeset
   710
                lhssql = self._inlined_var_sql(lhsvar, relation.r_type)
bd0a0f219751 fix sql generated on NOT inlined_relation queries. Use exists, so no more needs for extra DISTINCT
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2073
diff changeset
   711
                sql = '%s IS NULL' % self._inlined_var_sql(lhsvar, relation.r_type)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   712
            return sql
2199
bd0a0f219751 fix sql generated on NOT inlined_relation queries. Use exists, so no more needs for extra DISTINCT
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2073
diff changeset
   713
        lhssql = self._inlined_var_sql(lhsvar, relation.r_type)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   714
        if rhsconst is not None:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   715
            return '%s=%s' % (lhssql, rhsconst.accept(self))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   716
        if isinstance(rhsvar, Variable) and not rhsvar.name in self._varmap:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   717
            # if the rhs variable is only linked to this relation, this mean we
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   718
            # only want the relation to exists, eg NOT NULL in case of inlined
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   719
            # relation
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   720
            if len(rhsvar.stinfo['relations']) == 1 and rhsvar._q_invariant:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   721
                return '%s IS NOT NULL' % lhssql
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   722
            if rhsvar._q_invariant:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   723
                return self._extra_join_sql(relation, lhssql, rhsvar)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   724
        return '%s=%s' % (lhssql, rhsvar.accept(self))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   725
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   726
    def _process_relation_term(self, relation, rid, termvar, termconst, relfield):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   727
        if termconst or isinstance(termvar, ColumnAlias) or not termvar._q_invariant:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   728
            termsql = termconst and termconst.accept(self) or termvar.accept(self)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   729
            yield '%s.%s=%s' % (rid, relfield, termsql)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   730
        elif termvar._q_invariant:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   731
            # if the variable is mapped, generate restriction anyway
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   732
            if termvar.name in self._varmap:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   733
                termsql = termvar.accept(self)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   734
                yield '%s.%s=%s' % (rid, relfield, termsql)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   735
            extrajoin = self._extra_join_sql(relation, '%s.%s' % (rid, relfield), termvar)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   736
            if extrajoin:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   737
                yield extrajoin
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   738
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   739
    def _visit_relation(self, relation, rschema):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   740
        """generate SQL for a relation
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   741
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   742
        implements optimization 1.
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   743
        """
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   744
        if relation.r_type == 'identity':
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   745
            # special case "X identity Y"
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   746
            lhs, rhs = relation.get_parts()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   747
            if isinstance(relation.parent, Not):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   748
                self._state.done.add(relation.parent)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   749
                return 'NOT %s%s' % (lhs.accept(self), rhs.accept(self))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   750
            return '%s%s' % (lhs.accept(self), rhs.accept(self))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   751
        lhsvar, lhsconst, rhsvar, rhsconst = relation_info(relation)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   752
        rid = self._relation_table(relation)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   753
        sqls = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   754
        sqls += self._process_relation_term(relation, rid, lhsvar, lhsconst, 'eid_from')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   755
        sqls += self._process_relation_term(relation, rid, rhsvar, rhsconst, 'eid_to')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   756
        sql = ' AND '.join(sqls)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   757
        if rschema.symetric:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   758
            sql = '(%s OR %s)' % (sql, switch_relation_field(sql))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   759
        return sql
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   760
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   761
    def _visit_outer_join_relation(self, relation, rschema):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   762
        """
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   763
        left outer join syntax (optional=='right'):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   764
          X relation Y?
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   765
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   766
        right outer join syntax (optional=='left'):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   767
          X? relation Y
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   768
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   769
        full outer join syntaxes (optional=='both'):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   770
          X? relation Y?
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   771
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   772
        if relation is inlined:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   773
           if it's a left outer join:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   774
           -> X LEFT OUTER JOIN Y ON (X.relation=Y.eid)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   775
           elif it's a right outer join:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   776
           -> Y LEFT OUTER JOIN X ON (X.relation=Y.eid)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   777
           elif it's a full outer join:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   778
           -> X FULL OUTER JOIN Y ON (X.relation=Y.eid)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   779
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   780
           if it's a left outer join:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   781
           -> X LEFT OUTER JOIN relation ON (relation.eid_from=X.eid)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   782
              LEFT OUTER JOIN Y ON (relation.eid_to=Y.eid)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   783
           elif it's a right outer join:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   784
           -> Y LEFT OUTER JOIN relation ON (relation.eid_to=Y.eid)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   785
              LEFT OUTER JOIN X ON (relation.eid_from=X.eid)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   786
           elif it's a full outer join:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   787
           -> X FULL OUTER JOIN Y ON (X.relation=Y.eid)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   788
        """
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   789
        lhsvar, lhsconst, rhsvar, rhsconst = relation_info(relation)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   790
        if relation.optional == 'right':
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   791
            joinattr, restrattr = 'eid_from', 'eid_to'
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   792
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   793
            lhsvar, rhsvar = rhsvar, lhsvar
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   794
            lhsconst, rhsconst = rhsconst, lhsconst
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   795
            joinattr, restrattr = 'eid_to', 'eid_from'
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   796
        if relation.optional == 'both':
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   797
            outertype = 'FULL'
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   798
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   799
            outertype = 'LEFT'
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   800
        if rschema.inlined or relation.r_type == 'identity':
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   801
            self._state.done.add(relation)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   802
            t1 = self._var_table(lhsvar)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   803
            if relation.r_type == 'identity':
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   804
                attr = 'eid'
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   805
            else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   806
                attr = relation.r_type
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   807
            # reset lhs/rhs, we need the initial order now
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   808
            lhs, rhs = relation.get_variable_parts()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   809
            if '%s.%s' % (lhs.name, attr) in self._varmap:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   810
                lhssql = self._varmap['%s.%s' % (lhs.name, attr)]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   811
            else:
1251
af40e615dc89 introduce a 'cw_' prefix on entity table and column names so we don't conflict with sql or DBMS specific keywords
sylvain.thenault@logilab.fr
parents: 1124
diff changeset
   812
                lhssql = '%s.%s%s' % (self._var_table(lhs.variable), SQL_PREFIX, attr)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   813
            if not rhsvar is None:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   814
                t2 = self._var_table(rhsvar)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   815
                if t2 is None:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   816
                    if rhsconst is not None:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   817
                        # inlined relation with invariant as rhs
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   818
                        condition = '%s=%s' % (lhssql, rhsconst.accept(self))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   819
                        if relation.r_type != 'identity':
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   820
                            condition = '(%s OR %s IS NULL)' % (condition, lhssql)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   821
                        if not lhsvar.stinfo['optrelations']:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   822
                            return condition
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   823
                        self.add_outer_join_condition(lhsvar, t1, condition)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   824
                    return
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   825
            else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   826
                condition = '%s=%s' % (lhssql, rhsconst.accept(self))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   827
                self.add_outer_join_condition(lhsvar, t1, condition)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   828
            join = '%s OUTER JOIN %s ON (%s=%s)' % (
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   829
                outertype, self._state.tables[t2][1], lhssql, rhs.accept(self))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   830
            self.replace_tables_by_outer_join(join, t1, t2)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   831
            return ''
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   832
        lhssql = lhsconst and lhsconst.accept(self) or lhsvar.accept(self)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   833
        rhssql = rhsconst and rhsconst.accept(self) or rhsvar.accept(self)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   834
        rid = self._relation_table(relation)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   835
        if not lhsvar:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   836
            join = ''
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   837
            toreplace = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   838
            maintable = rid
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   839
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   840
            join = '%s OUTER JOIN %s ON (%s.%s=%s' % (
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   841
                outertype, self._state.tables[rid][1], rid, joinattr, lhssql)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   842
            toreplace = [rid]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   843
            maintable = self._var_table(lhsvar)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   844
            if rhsconst:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   845
                join += ' AND %s.%s=%s)' % (rid, restrattr, rhssql)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   846
            else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   847
                join += ')'
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   848
        if not rhsconst:
1122
9f37de24251f fix rql2sq w/ outer join on subquery result
sylvain.thenault@logilab.fr
parents: 438
diff changeset
   849
            rhstable = rhsvar._q_sqltable
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   850
            if rhstable:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   851
                assert rhstable is not None, rhsvar
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   852
                join += ' %s OUTER JOIN %s ON (%s.%s=%s)' % (
1251
af40e615dc89 introduce a 'cw_' prefix on entity table and column names so we don't conflict with sql or DBMS specific keywords
sylvain.thenault@logilab.fr
parents: 1124
diff changeset
   853
                    outertype, self._state.tables[rhstable][1], rid, restrattr,
af40e615dc89 introduce a 'cw_' prefix on entity table and column names so we don't conflict with sql or DBMS specific keywords
sylvain.thenault@logilab.fr
parents: 1124
diff changeset
   854
                    rhssql)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   855
                toreplace.append(rhstable)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   856
        self.replace_tables_by_outer_join(join, maintable, *toreplace)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   857
        return ''
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   858
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   859
    def _visit_var_attr_relation(self, relation, rhs_vars):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   860
        """visit an attribute relation with variable(s) in the RHS
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   861
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   862
        attribute variables are used either in the selection or for
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   863
        unification (eg X attr1 A, Y attr2 A). In case of selection,
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   864
        nothing to do here.
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   865
        """
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   866
        contextrels = {}
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   867
        for var in rhs_vars:
2073
173c646981a7 fix missing from close when using a var map
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 1977
diff changeset
   868
            if var.name in self._varmap:
173c646981a7 fix missing from close when using a var map
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 1977
diff changeset
   869
                # ensure table is added
173c646981a7 fix missing from close when using a var map
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 1977
diff changeset
   870
                self._var_info(var.variable)
3815
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
   871
            principal = var.variable.stinfo.get('principal')
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
   872
            if principal is not None and principal is not relation:
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
   873
                contextrels[var.name] = relation
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   874
        if not contextrels:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   875
            return ''
3815
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
   876
        # we have to generate unification expression
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   877
        lhssql = self._inlined_var_sql(relation.children[0].variable,
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   878
                                       relation.r_type)
3815
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
   879
        try:
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
   880
            self._state.ignore_varmap = True
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
   881
            return '%s%s' % (lhssql, relation.children[1].accept(self))
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
   882
        finally:
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
   883
            self._state.ignore_varmap = False
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   884
2354
9b4bac626977 ability to map attributes to something else than usual cw mapping on sql generation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2199
diff changeset
   885
    def _visit_attribute_relation(self, rel):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   886
        """generate SQL for an attribute relation"""
2354
9b4bac626977 ability to map attributes to something else than usual cw mapping on sql generation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2199
diff changeset
   887
        lhs, rhs = rel.get_parts()
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   888
        rhssql = rhs.accept(self)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   889
        table = self._var_table(lhs.variable)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   890
        if table is None:
2354
9b4bac626977 ability to map attributes to something else than usual cw mapping on sql generation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2199
diff changeset
   891
            assert rel.r_type == 'eid'
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   892
            lhssql = lhs.accept(self)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   893
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   894
            try:
2354
9b4bac626977 ability to map attributes to something else than usual cw mapping on sql generation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2199
diff changeset
   895
                lhssql = self._varmap['%s.%s' % (lhs.name, rel.r_type)]
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   896
            except KeyError:
2354
9b4bac626977 ability to map attributes to something else than usual cw mapping on sql generation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2199
diff changeset
   897
                mapkey = '%s.%s' % (self._state.solution[lhs.name], rel.r_type)
9b4bac626977 ability to map attributes to something else than usual cw mapping on sql generation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2199
diff changeset
   898
                if mapkey in self.attr_map:
9b4bac626977 ability to map attributes to something else than usual cw mapping on sql generation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2199
diff changeset
   899
                    lhssql = self.attr_map[mapkey](self, lhs.variable, rel)
9b4bac626977 ability to map attributes to something else than usual cw mapping on sql generation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2199
diff changeset
   900
                elif rel.r_type == 'eid':
1251
af40e615dc89 introduce a 'cw_' prefix on entity table and column names so we don't conflict with sql or DBMS specific keywords
sylvain.thenault@logilab.fr
parents: 1124
diff changeset
   901
                    lhssql = lhs.variable._q_sql
af40e615dc89 introduce a 'cw_' prefix on entity table and column names so we don't conflict with sql or DBMS specific keywords
sylvain.thenault@logilab.fr
parents: 1124
diff changeset
   902
                else:
2354
9b4bac626977 ability to map attributes to something else than usual cw mapping on sql generation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2199
diff changeset
   903
                    lhssql = '%s.%s%s' % (table, SQL_PREFIX, rel.r_type)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   904
        try:
2354
9b4bac626977 ability to map attributes to something else than usual cw mapping on sql generation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2199
diff changeset
   905
            if rel._q_needcast == 'TODAY':
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   906
                sql = 'DATE(%s)%s' % (lhssql, rhssql)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   907
            # XXX which cast function should be used
2354
9b4bac626977 ability to map attributes to something else than usual cw mapping on sql generation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2199
diff changeset
   908
            #elif rel._q_needcast == 'NOW':
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   909
            #    sql = 'TIMESTAMP(%s)%s' % (lhssql, rhssql)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   910
            else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   911
                sql = '%s%s' % (lhssql, rhssql)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   912
        except AttributeError:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   913
            sql = '%s%s' % (lhssql, rhssql)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   914
        if lhs.variable.stinfo['optrelations']:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   915
            self.add_outer_join_condition(lhs.variable, table, sql)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   916
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   917
            return sql
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   918
2354
9b4bac626977 ability to map attributes to something else than usual cw mapping on sql generation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2199
diff changeset
   919
    def _visit_has_text_relation(self, rel):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   920
        """generate SQL for a has_text relation"""
2354
9b4bac626977 ability to map attributes to something else than usual cw mapping on sql generation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2199
diff changeset
   921
        lhs, rhs = rel.get_parts()
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   922
        const = rhs.children[0]
2354
9b4bac626977 ability to map attributes to something else than usual cw mapping on sql generation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2199
diff changeset
   923
        alias = self._fti_table(rel)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   924
        jointo = lhs.accept(self)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   925
        restriction = ''
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   926
        lhsvar = lhs.variable
2354
9b4bac626977 ability to map attributes to something else than usual cw mapping on sql generation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2199
diff changeset
   927
        me_is_principal = lhsvar.stinfo.get('principal') is rel
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   928
        if me_is_principal:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   929
            if not lhsvar.stinfo['typerels']:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   930
                # the variable is using the fti table, no join needed
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   931
                jointo = None
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   932
            elif not lhsvar.name in self._varmap:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   933
                # join on entities instead of etype's table to get result for
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   934
                # external entities on multisources configurations
3762
e416186fb91c prefix sql aliases for entity table by '_' to avoid pb with variable such as 'AS' (eg a keyword in SQL)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3754
diff changeset
   935
                ealias = lhsvar._q_sqltable = '_' + lhsvar.name
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   936
                jointo = lhsvar._q_sql = '%s.eid' % ealias
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   937
                self.add_table('entities AS %s' % ealias, ealias)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   938
                if not lhsvar._q_invariant or len(lhsvar.stinfo['possibletypes']) == 1:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   939
                    restriction = " AND %s.type='%s'" % (ealias, self._state.solution[lhs.name])
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   940
                else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   941
                    etypes = ','.join("'%s'" % etype for etype in lhsvar.stinfo['possibletypes'])
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   942
                    restriction = " AND %s.type IN (%s)" % (ealias, etypes)
2354
9b4bac626977 ability to map attributes to something else than usual cw mapping on sql generation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2199
diff changeset
   943
        if isinstance(rel.parent, Not):
9b4bac626977 ability to map attributes to something else than usual cw mapping on sql generation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2199
diff changeset
   944
            self._state.done.add(rel.parent)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   945
            not_ = True
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   946
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   947
            not_ = False
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   948
        return self.dbms_helper.fti_restriction_sql(alias, const.eval(self._args),
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   949
                                                    jointo, not_) + restriction
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   950
3815
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
   951
    def visit_comparison(self, cmp):
3787
82bb2c7f083b C - fix typo. make `lgp check` happy.
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 3762
diff changeset
   952
        """generate SQL for a comparison"""
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   953
        if len(cmp.children) == 2:
1862
94dc8ccd320b #343322: should generate IS NULL in sql w/ None values in substitution
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 1802
diff changeset
   954
            # XXX occurs ?
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   955
            lhs, rhs = cmp.children
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   956
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   957
            lhs = None
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   958
            rhs = cmp.children[0]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   959
        operator = cmp.operator
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   960
        if operator in ('IS', 'LIKE', 'ILIKE'):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   961
            if operator == 'ILIKE' and not self.dbms_helper.ilike_support:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   962
                operator = ' LIKE '
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   963
            else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   964
                operator = ' %s ' % operator
1862
94dc8ccd320b #343322: should generate IS NULL in sql w/ None values in substitution
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 1802
diff changeset
   965
        elif (operator == '=' and isinstance(rhs, Constant)
94dc8ccd320b #343322: should generate IS NULL in sql w/ None values in substitution
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 1802
diff changeset
   966
              and rhs.eval(self._args) is None):
94dc8ccd320b #343322: should generate IS NULL in sql w/ None values in substitution
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 1802
diff changeset
   967
            if lhs is None:
94dc8ccd320b #343322: should generate IS NULL in sql w/ None values in substitution
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 1802
diff changeset
   968
                return ' IS NULL'
3815
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
   969
            return '%s IS NULL' % lhs.accept(self)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   970
        elif isinstance(rhs, Function) and rhs.name == 'IN':
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   971
            assert operator == '='
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   972
            operator = ' '
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   973
        if lhs is None:
3815
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
   974
            return '%s%s'% (operator, rhs.accept(self))
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
   975
        return '%s%s%s'% (lhs.accept(self), operator, rhs.accept(self))
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   976
3815
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
   977
    def visit_mathexpression(self, mexpr):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   978
        """generate SQL for a mathematic expression"""
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   979
        lhs, rhs = mexpr.get_parts()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   980
        # check for string concatenation
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   981
        operator = mexpr.operator
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   982
        try:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   983
            if mexpr.operator == '+' and mexpr.get_type(self._state.solution, self._args) == 'String':
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   984
                operator = '||'
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   985
        except CoercionError:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   986
            pass
3815
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
   987
        return '(%s %s %s)'% (lhs.accept(self), operator, rhs.accept(self))
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   988
3815
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
   989
    def visit_function(self, func):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   990
        """generate SQL name for a function"""
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   991
        # function_description will check function is supported by the backend
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   992
        sqlname = self.dbms_helper.func_sqlname(func.name)
3815
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
   993
        return '%s(%s)' % (sqlname, ', '.join(c.accept(self)
1382
618f6aee8d52 use adbh.func_sqlname for more backend compat
sylvain.thenault@logilab.fr
parents: 1251
diff changeset
   994
                                              for c in func.children))
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   995
3815
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
   996
    def visit_constant(self, constant):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   997
        """generate SQL name for a constant"""
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   998
        value = constant.value
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   999
        if constant.type is None:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1000
            return 'NULL'
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1001
        if constant.type == 'Int' and  isinstance(constant.parent, SortTerm):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1002
            return constant.value
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1003
        if constant.type in ('Date', 'Datetime'):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1004
            rel = constant.relation()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1005
            if rel is not None:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1006
                rel._q_needcast = value
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1007
            return self.keyword_map[value]()
1497
54fc5cc52210 use dbmshelper to generate correct boolean value in rql2sql
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1382
diff changeset
  1008
        if constant.type == 'Boolean':
54fc5cc52210 use dbmshelper to generate correct boolean value in rql2sql
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1382
diff changeset
  1009
            value = self.dbms_helper.boolean_value(value)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1010
        if constant.type == 'Substitute':
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1011
            _id = constant.value
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1012
            if isinstance(_id, unicode):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1013
                _id = _id.encode()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1014
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1015
            _id = str(id(constant)).replace('-', '', 1)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1016
            if isinstance(value, unicode):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1017
                value = value.encode(self.dbencoding)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1018
            self._query_attrs[_id] = value
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1019
        return '%%(%s)s' % _id
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
  1020
3815
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
  1021
    def visit_variableref(self, variableref):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1022
        """get the sql name for a variable reference"""
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1023
        # use accept, .variable may be a variable or a columnalias
3815
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
  1024
        return variableref.variable.accept(self)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1025
3815
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
  1026
    def visit_columnalias(self, colalias):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1027
        """get the sql name for a subquery column alias"""
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1028
        if colalias.name in self._varmap:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1029
            sql = self._varmap[colalias.name]
1122
9f37de24251f fix rql2sq w/ outer join on subquery result
sylvain.thenault@logilab.fr
parents: 438
diff changeset
  1030
            table = sql.split('.', 1)[0]
9f37de24251f fix rql2sq w/ outer join on subquery result
sylvain.thenault@logilab.fr
parents: 438
diff changeset
  1031
            colalias._q_sqltable = table
9f37de24251f fix rql2sq w/ outer join on subquery result
sylvain.thenault@logilab.fr
parents: 438
diff changeset
  1032
            colalias._q_sql = sql
9f37de24251f fix rql2sq w/ outer join on subquery result
sylvain.thenault@logilab.fr
parents: 438
diff changeset
  1033
            self.add_table(table)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1034
            return sql
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1035
        return colalias._q_sql
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
  1036
3815
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
  1037
    def visit_variable(self, variable):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1038
        """get the table name and sql string for a variable"""
3815
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
  1039
        #if contextrels is None and variable.name in self._state.done:
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
  1040
        if variable.name in self._state.done:
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1041
            if self._in_wrapping_query:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1042
                return 'T1.%s' % self._state.aliases[variable.name]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1043
            return variable._q_sql
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1044
        self._state.done.add(variable.name)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1045
        vtablename = None
3815
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
  1046
        if not self._state.ignore_varmap and variable.name in self._varmap:
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
  1047
            sql, vtablename = self._var_info(variable)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1048
        elif variable.stinfo['attrvar']:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1049
            # attribute variable (systematically used in rhs of final
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1050
            # relation(s)), get table name and sql from any rhs relation
3815
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
  1051
            sql = self._linked_var_sql(variable)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1052
        elif variable._q_invariant:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1053
            # since variable is invariant, we know we won't found final relation
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1054
            principal = variable.stinfo['principal']
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1055
            if principal is None:
3762
e416186fb91c prefix sql aliases for entity table by '_' to avoid pb with variable such as 'AS' (eg a keyword in SQL)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3754
diff changeset
  1056
                vtablename = '_' + variable.name
1251
af40e615dc89 introduce a 'cw_' prefix on entity table and column names so we don't conflict with sql or DBMS specific keywords
sylvain.thenault@logilab.fr
parents: 1124
diff changeset
  1057
                self.add_table('entities AS %s' % vtablename, vtablename)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1058
                sql = '%s.eid' % vtablename
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1059
                if variable.stinfo['typerels']:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1060
                    # add additional restriction on entities.type column
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1061
                    pts = variable.stinfo['possibletypes']
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1062
                    if len(pts) == 1:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1063
                        etype = iter(variable.stinfo['possibletypes']).next()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1064
                        restr = "%s.type='%s'" % (vtablename, etype)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1065
                    else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1066
                        etypes = ','.join("'%s'" % et for et in pts)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1067
                        restr = '%s.type IN (%s)' % (vtablename, etypes)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1068
                    self._state.add_restriction(restr)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1069
            elif principal.r_type == 'has_text':
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1070
                sql = '%s.%s' % (self._fti_table(principal),
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1071
                                 self.dbms_helper.fti_uid_attr)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1072
            elif principal in variable.stinfo['rhsrelations']:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1073
                if self.schema.rschema(principal.r_type).inlined:
3815
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
  1074
                    sql = self._linked_var_sql(variable)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1075
                else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1076
                    sql = '%s.eid_to' % self._relation_table(principal)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1077
            else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1078
                sql = '%s.eid_from' % self._relation_table(principal)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1079
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1080
            # standard variable: get table name according to etype and use .eid
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1081
            # attribute
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1082
            sql, vtablename = self._var_info(variable)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1083
        variable._q_sqltable = vtablename
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1084
        variable._q_sql = sql
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1085
        return sql
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1086
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1087
    # various utilities #######################################################
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1088
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1089
    def _extra_join_sql(self, relation, sql, var):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1090
        # if rhs var is invariant, and this relation is not its principal,
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1091
        # generate extra join
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1092
        try:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1093
            if not var.stinfo['principal'] is relation:
3815
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
  1094
                op = relation.operator()
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
  1095
                if op == '=':
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
  1096
                    # need a predicable result for tests
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
  1097
                    args = sorted( (sql, var.accept(self)) )
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
  1098
                    args.insert(1, op)
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
  1099
                else:
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
  1100
                    args = (sql, op, var.accept(self))
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
  1101
                return '%s%s%s' % tuple(args)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1102
        except KeyError:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1103
            # no principal defined, relation is necessarily the principal and
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1104
            # so nothing to return here
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1105
            pass
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1106
        return ''
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
  1107
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1108
    def _var_info(self, var):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1109
        # if current var or one of its attribute is selected , it *must*
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1110
        # appear in the toplevel's FROM even if we're currently visiting
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1111
        # a EXISTS node
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1112
        if var.sqlscope is var.stmt:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1113
            scope = 0
3238
988a72e59b2b [querier] fix sql generated w/ NOT relation and shared variable: ensure variable's table is in parent select'scope
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2915
diff changeset
  1114
        # don't consider not_scope_offset if the variable is only used in one
988a72e59b2b [querier] fix sql generated w/ NOT relation and shared variable: ensure variable's table is in parent select'scope
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2915
diff changeset
  1115
        # relation
988a72e59b2b [querier] fix sql generated w/ NOT relation and shared variable: ensure variable's table is in parent select'scope
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2915
diff changeset
  1116
        elif len(var.stinfo['relations']) > 1:
988a72e59b2b [querier] fix sql generated w/ NOT relation and shared variable: ensure variable's table is in parent select'scope
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2915
diff changeset
  1117
            scope = -1 - self._not_scope_offset
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1118
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1119
            scope = -1
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1120
        try:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1121
            sql = self._varmap[var.name]
3762
e416186fb91c prefix sql aliases for entity table by '_' to avoid pb with variable such as 'AS' (eg a keyword in SQL)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3754
diff changeset
  1122
            tablealias = sql.split('.', 1)[0]
3238
988a72e59b2b [querier] fix sql generated w/ NOT relation and shared variable: ensure variable's table is in parent select'scope
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2915
diff changeset
  1123
            if scope < 0:
3762
e416186fb91c prefix sql aliases for entity table by '_' to avoid pb with variable such as 'AS' (eg a keyword in SQL)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3754
diff changeset
  1124
                scope = self._varmap_table_scope(var.stmt, tablealias)
e416186fb91c prefix sql aliases for entity table by '_' to avoid pb with variable such as 'AS' (eg a keyword in SQL)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3754
diff changeset
  1125
            self.add_table(tablealias, scope=scope)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1126
        except KeyError:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1127
            etype = self._state.solution[var.name]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1128
            # XXX this check should be moved in rql.stcheck
3689
deb13e88e037 follow yams 0.25 api changes to improve performance
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3245
diff changeset
  1129
            if self.schema.eschema(etype).final:
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1130
                raise BadRQLQuery(var.stmt.root)
3762
e416186fb91c prefix sql aliases for entity table by '_' to avoid pb with variable such as 'AS' (eg a keyword in SQL)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3754
diff changeset
  1131
            tablealias = '_' + var.name
e416186fb91c prefix sql aliases for entity table by '_' to avoid pb with variable such as 'AS' (eg a keyword in SQL)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3754
diff changeset
  1132
            sql = '%s.%seid' % (tablealias, SQL_PREFIX)
e416186fb91c prefix sql aliases for entity table by '_' to avoid pb with variable such as 'AS' (eg a keyword in SQL)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3754
diff changeset
  1133
            self.add_table('%s%s AS %s' % (SQL_PREFIX, etype, tablealias),
e416186fb91c prefix sql aliases for entity table by '_' to avoid pb with variable such as 'AS' (eg a keyword in SQL)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3754
diff changeset
  1134
                           tablealias, scope=scope)
e416186fb91c prefix sql aliases for entity table by '_' to avoid pb with variable such as 'AS' (eg a keyword in SQL)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3754
diff changeset
  1135
        return sql, tablealias
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
  1136
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1137
    def _inlined_var_sql(self, var, rtype):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1138
        try:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1139
            sql = self._varmap['%s.%s' % (var.name, rtype)]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1140
            scope = var.sqlscope is var.stmt and 0 or -1
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1141
            self.add_table(sql.split('.', 1)[0], scope=scope)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1142
        except KeyError:
1251
af40e615dc89 introduce a 'cw_' prefix on entity table and column names so we don't conflict with sql or DBMS specific keywords
sylvain.thenault@logilab.fr
parents: 1124
diff changeset
  1143
            sql = '%s.%s%s' % (self._var_table(var), SQL_PREFIX, rtype)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1144
            #self._state.done.add(var.name)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1145
        return sql
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
  1146
3815
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
  1147
    def _linked_var_sql(self, variable):
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
  1148
        if not self._state.ignore_varmap:
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1149
            try:
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
  1150
                return self._varmap[variable.name]
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1151
            except KeyError:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1152
                pass
3815
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
  1153
        rel = (variable.stinfo.get('principal') or
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1154
               iter(variable.stinfo['rhsrelations']).next())
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1155
        linkedvar = rel.children[0].variable
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1156
        if rel.r_type == 'eid':
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1157
            return linkedvar.accept(self)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1158
        if isinstance(linkedvar, ColumnAlias):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1159
            raise BadRQLQuery('variable %s should be selected by the subquery'
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1160
                              % variable.name)
2354
9b4bac626977 ability to map attributes to something else than usual cw mapping on sql generation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2199
diff changeset
  1161
        mapkey = '%s.%s' % (self._state.solution[linkedvar.name], rel.r_type)
9b4bac626977 ability to map attributes to something else than usual cw mapping on sql generation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2199
diff changeset
  1162
        if mapkey in self.attr_map:
9b4bac626977 ability to map attributes to something else than usual cw mapping on sql generation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2199
diff changeset
  1163
            return self.attr_map[mapkey](self, linkedvar, rel)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1164
        try:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1165
            sql = self._varmap['%s.%s' % (linkedvar.name, rel.r_type)]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1166
        except KeyError:
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
  1167
            linkedvar.accept(self)
1251
af40e615dc89 introduce a 'cw_' prefix on entity table and column names so we don't conflict with sql or DBMS specific keywords
sylvain.thenault@logilab.fr
parents: 1124
diff changeset
  1168
            sql = '%s.%s%s' % (linkedvar._q_sqltable, SQL_PREFIX, rel.r_type)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1169
        return sql
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1170
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1171
    # tables handling #########################################################
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1172
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1173
    def alias_and_add_table(self, tablename):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1174
        alias = '%s%s' % (tablename, self._state.count)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1175
        self._state.count += 1
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1176
        self.add_table('%s AS %s' % (tablename, alias), alias)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1177
        return alias
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
  1178
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1179
    def add_table(self, table, key=None, scope=-1):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1180
        if key is None:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1181
            key = table
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1182
        if key in self._state.tables:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1183
            return
3238
988a72e59b2b [querier] fix sql generated w/ NOT relation and shared variable: ensure variable's table is in parent select'scope
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2915
diff changeset
  1184
        if scope < 0:
988a72e59b2b [querier] fix sql generated w/ NOT relation and shared variable: ensure variable's table is in parent select'scope
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2915
diff changeset
  1185
            scope = len(self._state.actual_tables) + scope
2915
651bbe1526b6 [rql2sql] test and fix a bug triggered when editing a ticket in jpl
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 2354
diff changeset
  1186
        self._state.tables[key] = (scope, table)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1187
        self._state.actual_tables[scope].append(table)
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
  1188
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1189
    def replace_tables_by_outer_join(self, substitute, lefttable, *tables):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1190
        for table in tables:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1191
            try:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1192
                scope, alias = self._state.tables[table]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1193
                self._state.actual_tables[scope].remove(alias)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1194
            except ValueError: # huum, not sure about what should be done here
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1195
                msg = "%s already used in an outer join, don't know what to do!"
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1196
                raise Exception(msg % table)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1197
        try:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1198
            tablealias = self._state.outer_tables[lefttable]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1199
            actualtables = self._state.actual_tables[-1]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1200
        except KeyError:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1201
            tablescope, tablealias = self._state.tables[lefttable]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1202
            actualtables = self._state.actual_tables[tablescope]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1203
        outerjoin = '%s %s' % (tablealias, substitute)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1204
        self._update_outer_tables(lefttable, actualtables, tablealias, outerjoin)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1205
        for table in tables:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1206
            self._state.outer_tables[table] = outerjoin
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1207
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1208
    def add_outer_join_condition(self, var, table, condition):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1209
        try:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1210
            tablealias = self._state.outer_tables[table]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1211
            actualtables = self._state.actual_tables[-1]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1212
        except KeyError:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1213
            for rel in var.stinfo['optrelations']:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1214
                self.visit_relation(rel)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1215
            assert self._state.outer_tables
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1216
            self.add_outer_join_condition(var, table, condition)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1217
            return
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1218
        before, after = tablealias.split(' AS %s ' % table, 1)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1219
        beforep, afterp = after.split(')', 1)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1220
        outerjoin = '%s AS %s %s AND %s) %s' % (before, table, beforep,
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1221
                                                condition, afterp)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1222
        self._update_outer_tables(table, actualtables, tablealias, outerjoin)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1223
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1224
    def _update_outer_tables(self, table, actualtables, oldalias, newalias):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1225
        actualtables.remove(oldalias)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1226
        actualtables.append(newalias)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1227
        # some tables which have already been used as outer table and replaced
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1228
        # by <oldalias> may not be reused here, though their associated value
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1229
        # in the outer_tables dict has to be updated as well
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1230
        for table, outerexpr in self._state.outer_tables.iteritems():
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1231
            if outerexpr == oldalias:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1232
                self._state.outer_tables[table] = newalias
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
  1233
        self._state.outer_tables[table] = newalias
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
  1234
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1235
    def _var_table(self, var):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1236
        var.accept(self)#.visit_variable(var)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1237
        return var._q_sqltable
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1238
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1239
    def _relation_table(self, relation):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1240
        """return the table alias used by the given relation"""
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1241
        if relation in self._state.done:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1242
            return relation._q_sqltable
3689
deb13e88e037 follow yams 0.25 api changes to improve performance
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3245
diff changeset
  1243
        assert not self.schema.rschema(relation.r_type).final, relation.r_type
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1244
        rid = 'rel_%s%s' % (relation.r_type, self._state.count)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1245
        # relation's table is belonging to the root scope if it is the principal
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
  1246
        # table of one of it's variable and if that variable belong's to parent
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1247
        # scope
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1248
        for varref in relation.iget_nodes(VariableRef):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1249
            var = varref.variable
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1250
            if isinstance(var, ColumnAlias):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1251
                scope = 0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1252
                break
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1253
            # XXX may have a principal without being invariant for this generation,
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1254
            #     not sure this is a pb or not
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1255
            if var.stinfo.get('principal') is relation and var.sqlscope is var.stmt:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1256
                scope = 0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1257
                break
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1258
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1259
            scope = -1
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1260
        self._state.count += 1
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1261
        self.add_table('%s_relation AS %s' % (relation.r_type, rid), rid, scope=scope)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1262
        relation._q_sqltable = rid
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1263
        self._state.done.add(relation)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1264
        return rid
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
  1265
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1266
    def _fti_table(self, relation):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1267
        if relation in self._state.done:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1268
            try:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1269
                return relation._q_sqltable
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1270
            except AttributeError:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1271
                pass
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1272
        self._state.done.add(relation)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1273
        alias = self.alias_and_add_table(self.dbms_helper.fti_table)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1274
        relation._q_sqltable = alias
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1275
        return alias
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
  1276
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1277
    def _varmap_table_scope(self, select, table):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1278
        """since a varmap table may be used for multiple variable, its scope is
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1279
        the most outer scope of each variables
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1280
        """
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1281
        scope = -1
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1282
        for varname, alias in self._varmap.iteritems():
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1283
            # check '.' in varname since there are 'X.attribute' keys in varmap
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1284
            if not '.' in varname and alias.split('.', 1)[0] == table:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1285
                if select.defined_vars[varname].sqlscope is select:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1286
                    return 0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1287
        return scope