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