server/sources/rql2sql.py
author Aurelien Campeas <aurelien.campeas@logilab.fr>
Thu, 17 Mar 2011 13:43:07 +0100
brancholdstable
changeset 7095 1831c3154581
parent 7042 8bcf33c493f8
child 7108 bcdf22734059
permissions -rw-r--r--
[devtools] missing py2.5 compat import
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
7042
8bcf33c493f8 [sql generation] fix exists table scope pb: when iterating other solutions inner to an EXISTS subquery, we should reset variables which have this exists node as scope
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6940
diff changeset
     1
# copyright 2003-2011 LOGILAB S.A. (Paris, FRANCE), all rights reserved.
5421
8167de96c523 proper licensing information (LGPL-2.1). Hope I get it right this time.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5280
diff changeset
     2
# contact http://www.logilab.fr/ -- mailto:contact@logilab.fr
8167de96c523 proper licensing information (LGPL-2.1). Hope I get it right this time.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5280
diff changeset
     3
#
8167de96c523 proper licensing information (LGPL-2.1). Hope I get it right this time.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5280
diff changeset
     4
# This file is part of CubicWeb.
8167de96c523 proper licensing information (LGPL-2.1). Hope I get it right this time.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5280
diff changeset
     5
#
8167de96c523 proper licensing information (LGPL-2.1). Hope I get it right this time.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5280
diff changeset
     6
# CubicWeb is free software: you can redistribute it and/or modify it under the
8167de96c523 proper licensing information (LGPL-2.1). Hope I get it right this time.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5280
diff changeset
     7
# terms of the GNU Lesser General Public License as published by the Free
8167de96c523 proper licensing information (LGPL-2.1). Hope I get it right this time.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5280
diff changeset
     8
# Software Foundation, either version 2.1 of the License, or (at your option)
8167de96c523 proper licensing information (LGPL-2.1). Hope I get it right this time.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5280
diff changeset
     9
# any later version.
8167de96c523 proper licensing information (LGPL-2.1). Hope I get it right this time.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5280
diff changeset
    10
#
5424
8ecbcbff9777 replace logilab-common by CubicWeb in disclaimer
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5421
diff changeset
    11
# CubicWeb is distributed in the hope that it will be useful, but WITHOUT
5421
8167de96c523 proper licensing information (LGPL-2.1). Hope I get it right this time.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5280
diff changeset
    12
# ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
8167de96c523 proper licensing information (LGPL-2.1). Hope I get it right this time.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5280
diff changeset
    13
# FOR A PARTICULAR PURPOSE.  See the GNU Lesser General Public License for more
8167de96c523 proper licensing information (LGPL-2.1). Hope I get it right this time.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5280
diff changeset
    14
# details.
8167de96c523 proper licensing information (LGPL-2.1). Hope I get it right this time.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5280
diff changeset
    15
#
8167de96c523 proper licensing information (LGPL-2.1). Hope I get it right this time.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5280
diff changeset
    16
# You should have received a copy of the GNU Lesser General Public License along
8167de96c523 proper licensing information (LGPL-2.1). Hope I get it right this time.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5280
diff changeset
    17
# with CubicWeb.  If not, see <http://www.gnu.org/licenses/>.
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    18
"""RQL to SQL generator for native sources.
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    19
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    20
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    21
SQL queries optimization
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    22
~~~~~~~~~~~~~~~~~~~~~~~~
1398
5fe84a5f7035 rename internal entity types to have CW prefix instead of E
sylvain.thenault@logilab.fr
parents: 1263
diff changeset
    23
1. CWUser X WHERE X in_group G, G name 'users':
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    24
1398
5fe84a5f7035 rename internal entity types to have CW prefix instead of E
sylvain.thenault@logilab.fr
parents: 1263
diff changeset
    25
   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
    26
   which allow us to do ::
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    27
1398
5fe84a5f7035 rename internal entity types to have CW prefix instead of E
sylvain.thenault@logilab.fr
parents: 1263
diff changeset
    28
     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
    29
     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
    30
     AND CWGroup.name = 'users'
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
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    33
2. Any X WHERE X nonfinal1 Y, Y nonfinal2 Z
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    34
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    35
   -> direct join between nonfinal1 and nonfinal2, whatever X,Y, Z (unless
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    36
      inlined...)
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
    37
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    38
      NOT IMPLEMENTED (and quite hard to implement)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    39
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    40
Potential optimization information is collected by the querier, sql generation
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    41
is done according to this information
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    42
5280
7e13bb484a19 added note about interesting page about SQL implementation differences
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents: 5013
diff changeset
    43
cross RDMS note : read `Comparison of different SQL implementations`_
7e13bb484a19 added note about interesting page about SQL implementation differences
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents: 5013
diff changeset
    44
by Troels Arvin. Features SQL ISO Standard, PG, mysql, Oracle, MS SQL, DB2
7e13bb484a19 added note about interesting page about SQL implementation differences
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents: 5013
diff changeset
    45
and Informix.
7e13bb484a19 added note about interesting page about SQL implementation differences
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents: 5013
diff changeset
    46
5582
3e133b29a1a4 [rql2sql] follow rql 0.26.1 changes: NOT nodes normalization, allowing simplification of sql generation, and fix #XXX
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5426
diff changeset
    47
.. _Comparison of different SQL implementations: http://www.troels.arvin.dk/db/rdbms
5782
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
    48
"""
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    49
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    50
__docformat__ = "restructuredtext en"
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    51
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    52
import threading
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    53
5013
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
    54
from logilab.database import FunctionDescr, SQL_FUNCTIONS_REGISTRY
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
    55
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    56
from rql import BadRQLQuery, CoercionError
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    57
from rql.stmts import Union, Select
5782
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
    58
from rql.nodes import (SortTerm, VariableRef, Constant, Function, Variable, Or,
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
    59
                       Not, Comparison, ColumnAlias, Relation, SubQuery, Exists)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    60
5013
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
    61
from cubicweb import QueryError
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
    62
from cubicweb.server.sqlutils import SQL_PREFIX
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    63
from cubicweb.server.utils import cleanup_solutions
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    64
4794
81075b09ebf1 [rql2sql] fix potential crash when testing _q_invariant on a ColumnAlias instead of a Variable by always considering _q_invariant to false for them
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
    65
ColumnAlias._q_invariant = False # avoid to check for ColumnAlias / Variable
81075b09ebf1 [rql2sql] fix potential crash when testing _q_invariant on a ColumnAlias instead of a Variable by always considering _q_invariant to false for them
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
    66
5013
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
    67
FunctionDescr.source_execute = None
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
    68
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
    69
def default_update_cb_stack(self, stack):
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
    70
    stack.append(self.source_execute)
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
    71
FunctionDescr.update_cb_stack = default_update_cb_stack
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
    72
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
    73
LENGTH = SQL_FUNCTIONS_REGISTRY.get_function('LENGTH')
5630
40d7c7e180f1 [storage] source's callback has a new prototype, update usage
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5593
diff changeset
    74
def length_source_execute(source, session, value):
5013
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
    75
    return len(value.getvalue())
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
    76
LENGTH.source_execute = length_source_execute
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
    77
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
    78
def _new_var(select, varname):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    79
    newvar = select.get_variable(varname)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    80
    if not 'relations' in newvar.stinfo:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    81
        # not yet initialized
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    82
        newvar.prepare_annotation()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    83
        newvar.stinfo['scope'] = select
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    84
        newvar._q_invariant = False
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    85
    return newvar
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    86
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    87
def _fill_to_wrap_rel(var, newselect, towrap, schema):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    88
    for rel in var.stinfo['relations'] - var.stinfo['rhsrelations']:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    89
        rschema = schema.rschema(rel.r_type)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    90
        if rschema.inlined:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    91
            towrap.add( (var, rel) )
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    92
            for vref in rel.children[1].iget_nodes(VariableRef):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    93
                newivar = _new_var(newselect, vref.name)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    94
                newselect.selection.append(VariableRef(newivar))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    95
                _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
    96
        elif rschema.final:
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    97
            towrap.add( (var, rel) )
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
    98
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
    99
def rewrite_unstable_outer_join(select, solutions, unstable, schema):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   100
    """if some optional variables are unstable, they should be selected in a
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   101
    subquery. This function check this and rewrite the rql syntax tree if
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   102
    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
   103
    """
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   104
    modified = False
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   105
    for varname in tuple(unstable):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   106
        var = select.defined_vars[varname]
5004
4cc020ee70e2 le patch rql26 a été importé
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4845
diff changeset
   107
        if not var.stinfo.get('optrelations'):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   108
            continue
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   109
        unstable.remove(varname)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   110
        newselect = Select()
5582
3e133b29a1a4 [rql2sql] follow rql 0.26.1 changes: NOT nodes normalization, allowing simplification of sql generation, and fix #XXX
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5426
diff changeset
   111
        newselect.need_distinct = False
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   112
        myunion = Union()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   113
        myunion.append(newselect)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   114
        # extract aliases / selection
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   115
        newvar = _new_var(newselect, var.name)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   116
        newselect.selection = [VariableRef(newvar)]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   117
        for avar in select.defined_vars.itervalues():
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   118
            if avar.stinfo['attrvar'] is var:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   119
                newavar = _new_var(newselect, avar.name)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   120
                newavar.stinfo['attrvar'] = newvar
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   121
                newselect.selection.append(VariableRef(newavar))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   122
        towrap_rels = set()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   123
        _fill_to_wrap_rel(var, newselect, towrap_rels, schema)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   124
        # extract relations
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   125
        for var, rel in towrap_rels:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   126
            newrel = rel.copy(newselect)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   127
            newselect.add_restriction(newrel)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   128
            select.remove_node(rel)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   129
            var.stinfo['relations'].remove(rel)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   130
            newvar.stinfo['relations'].add(newrel)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   131
            if rel.optional in ('left', 'both'):
5004
4cc020ee70e2 le patch rql26 a été importé
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4845
diff changeset
   132
                newvar.add_optional_relation(newrel)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   133
            for vref in newrel.children[1].iget_nodes(VariableRef):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   134
                var = vref.variable
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   135
                var.stinfo['relations'].add(newrel)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   136
                var.stinfo['rhsrelations'].add(newrel)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   137
                if rel.optional in ('right', 'both'):
5004
4cc020ee70e2 le patch rql26 a été importé
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4845
diff changeset
   138
                    var.add_optional_relation(newrel)
6050
20af96a6fffc [rql2sql] add test for rql bug #37423, which triggered a cw bug fixed by this changeset (infinite loop with some queries...)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6003
diff changeset
   139
        if not select.where and not modified:
20af96a6fffc [rql2sql] add test for rql bug #37423, which triggered a cw bug fixed by this changeset (infinite loop with some queries...)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6003
diff changeset
   140
            # oops, generated the same thing as the original select....
20af96a6fffc [rql2sql] add test for rql bug #37423, which triggered a cw bug fixed by this changeset (infinite loop with some queries...)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6003
diff changeset
   141
            # restore original query, else we'll indefinitly loop
20af96a6fffc [rql2sql] add test for rql bug #37423, which triggered a cw bug fixed by this changeset (infinite loop with some queries...)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6003
diff changeset
   142
            for var, rel in towrap_rels:
20af96a6fffc [rql2sql] add test for rql bug #37423, which triggered a cw bug fixed by this changeset (infinite loop with some queries...)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6003
diff changeset
   143
                select.add_restriction(rel)
20af96a6fffc [rql2sql] add test for rql bug #37423, which triggered a cw bug fixed by this changeset (infinite loop with some queries...)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6003
diff changeset
   144
            continue
20af96a6fffc [rql2sql] add test for rql bug #37423, which triggered a cw bug fixed by this changeset (infinite loop with some queries...)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6003
diff changeset
   145
        modified = True
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   146
        # extract subquery solutions
3736
07196bda2456 take care to already existant solutions local variable
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3689
diff changeset
   147
        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
   148
        cleanup_solutions(newselect, mysolutions)
6050
20af96a6fffc [rql2sql] add test for rql bug #37423, which triggered a cw bug fixed by this changeset (infinite loop with some queries...)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6003
diff changeset
   149
        newselect.set_possible_types(mysolutions)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   150
        # full sub-query
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   151
        aliases = [VariableRef(select.get_variable(avar.name, i))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   152
                   for i, avar in enumerate(newselect.selection)]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   153
        select.add_subquery(SubQuery(aliases, myunion), check=False)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   154
    return modified
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   155
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   156
def _new_solutions(rqlst, solutions):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   157
    """first filter out subqueries variables from solutions"""
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   158
    newsolutions = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   159
    for origsol in solutions:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   160
        asol = {}
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   161
        for vname in rqlst.defined_vars:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   162
            asol[vname] = origsol[vname]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   163
        if not asol in newsolutions:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   164
            newsolutions.append(asol)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   165
    return newsolutions
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   166
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   167
def remove_unused_solutions(rqlst, solutions, varmap, schema):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   168
    """cleanup solutions: remove solutions where invariant variables are taking
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   169
    different types
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   170
    """
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
   171
    newsols = _new_solutions(rqlst, solutions)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   172
    existssols = {}
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   173
    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
   174
    invariants = {}
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   175
    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
   176
        vtype = newsols[0][vname]
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   177
        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
   178
            # 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
   179
            # 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
   180
            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
   181
                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
   182
        elif var.scope is not rqlst:
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   183
            # move appart variables which are in a EXISTS scope and are variating
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   184
            try:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   185
                thisexistssols, thisexistsvars = existssols[var.scope]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   186
            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
   187
                thisexistssols = [newsols[0]]
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   188
                thisexistsvars = set()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   189
                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
   190
            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
   191
                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
   192
                    thisexistssols.append(newsols.pop(i))
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   193
                    thisexistsvars.add(vname)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   194
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   195
            # 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
   196
            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
   197
                if vtype != newsols[i][vname]:
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   198
                    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
   199
    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
   200
        # 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
   201
        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
   202
        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
   203
            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
   204
                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
   205
        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
   206
        # 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
   207
        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
   208
            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
   209
                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
   210
        for sol in existssols:
3866
2783c166ad1a fix potential key error
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3852
diff changeset
   211
            try:
2783c166ad1a fix potential key error
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3852
diff changeset
   212
                for invvar, vartype in invariants[id(sol)].iteritems():
2783c166ad1a fix potential key error
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3852
diff changeset
   213
                    sol[invvar] = vartype
2783c166ad1a fix potential key error
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3852
diff changeset
   214
            except KeyError:
2783c166ad1a fix potential key error
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3852
diff changeset
   215
                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
   216
    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
   217
        if rewrite_unstable_outer_join(rqlst, newsols, unstable, schema):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   218
            # 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
   219
            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
   220
    return newsols, existssols, unstable
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   221
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   222
def relation_info(relation):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   223
    lhs, rhs = relation.get_variable_parts()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   224
    try:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   225
        lhs = lhs.variable
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   226
        lhsconst = lhs.stinfo['constnode']
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   227
    except AttributeError:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   228
        lhsconst = lhs
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   229
        lhs = None
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   230
    except KeyError:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   231
        lhsconst = None # ColumnAlias
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   232
    try:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   233
        rhs = rhs.variable
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   234
        rhsconst = rhs.stinfo['constnode']
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   235
    except AttributeError:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   236
        rhsconst = rhs
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   237
        rhs = None
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   238
    except KeyError:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   239
        rhsconst = None # ColumnAlias
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   240
    return lhs, lhsconst, rhs, rhsconst
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   241
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   242
def switch_relation_field(sql, table=''):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   243
    switchedsql = sql.replace(table + '.eid_from', '__eid_from__')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   244
    switchedsql = switchedsql.replace(table + '.eid_to',
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   245
                                      table + '.eid_from')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   246
    return switchedsql.replace('__eid_from__', table + '.eid_to')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   247
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   248
def sort_term_selection(sorts, selectedidx, rqlst, groups):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   249
    # XXX beurk
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   250
    if isinstance(rqlst, list):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   251
        def append(term):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   252
            rqlst.append(term)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   253
    else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   254
        def append(term):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   255
            rqlst.selection.append(term.copy(rqlst))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   256
    for sortterm in sorts:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   257
        term = sortterm.term
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   258
        if not isinstance(term, Constant) and not str(term) in selectedidx:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   259
            selectedidx.append(str(term))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   260
            append(term)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   261
            if groups:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   262
                for vref in term.iget_nodes(VariableRef):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   263
                    if not vref in groups:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   264
                        groups.append(vref)
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   265
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
   266
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
   267
                            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
   268
    if selectsortterms and sorts:
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   269
        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
   270
    if sorts and groups:
3754
a9c641f6193b document
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3752
diff changeset
   271
        # 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
   272
        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
   273
            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
   274
            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
   275
                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
   276
                    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
   277
                        groups.append(vref)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   278
    if needwrap:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   279
        if groups:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   280
            for vref in groups:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   281
                if not vref.name in selectedidx:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   282
                    selectedidx.append(vref.name)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   283
                    rqlst.selection.append(vref)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   284
        if having:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   285
            for term in having:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   286
                for vref in term.iget_nodes(VariableRef):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   287
                    if not vref.name in selectedidx:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   288
                        selectedidx.append(vref.name)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   289
                        rqlst.selection.append(vref)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   290
5013
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   291
def iter_mapped_var_sels(stmt, variable):
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   292
    # variable is a Variable or ColumnAlias node mapped to a source side
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   293
    # callback
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   294
    if not (len(variable.stinfo['rhsrelations']) <= 1 and # < 1 on column alias
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   295
            variable.stinfo['selected']):
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   296
        raise QueryError("can't use %s as a restriction variable"
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   297
                         % variable.name)
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   298
    for selectidx in variable.stinfo['selected']:
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   299
        vrefs = stmt.selection[selectidx].get_nodes(VariableRef)
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   300
        if len(vrefs) != 1:
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   301
            raise QueryError()
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   302
        yield selectidx, vrefs[0]
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   303
5013
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   304
def update_source_cb_stack(state, stmt, node, stack):
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   305
    while True:
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   306
        node = node.parent
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   307
        if node is stmt:
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   308
            break
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   309
        if not isinstance(node, Function):
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   310
            raise QueryError()
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   311
        func = SQL_FUNCTIONS_REGISTRY.get_function(node.name)
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   312
        if func.source_execute is None:
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   313
            raise QueryError('%s can not be called on mapped attribute'
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   314
                             % node.name)
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   315
        state.source_cb_funcs.add(node)
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   316
        func.update_cb_stack(stack)
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   317
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   318
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   319
# IGenerator implementation for RQL->SQL #######################################
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   320
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   321
class StateInfo(object):
5582
3e133b29a1a4 [rql2sql] follow rql 0.26.1 changes: NOT nodes normalization, allowing simplification of sql generation, and fix #XXX
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5426
diff changeset
   322
    def __init__(self, select, existssols, unstablevars):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   323
        self.existssols = existssols
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   324
        self.unstablevars = unstablevars
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   325
        self.subtables = {}
5013
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   326
        self.needs_source_cb = None
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   327
        self.subquery_source_cb = None
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   328
        self.source_cb_funcs = set()
5582
3e133b29a1a4 [rql2sql] follow rql 0.26.1 changes: NOT nodes normalization, allowing simplification of sql generation, and fix #XXX
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5426
diff changeset
   329
        self.scopes = {select: 0}
3e133b29a1a4 [rql2sql] follow rql 0.26.1 changes: NOT nodes normalization, allowing simplification of sql generation, and fix #XXX
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5426
diff changeset
   330
        self.scope_nodes = []
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   331
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   332
    def reset(self, solution):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   333
        """reset some visit variables"""
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   334
        self.solution = solution
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   335
        self.count = 0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   336
        self.done = set()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   337
        self.tables = self.subtables.copy()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   338
        self.actual_tables = [[]]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   339
        for _, tsql in self.tables.itervalues():
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   340
            self.actual_tables[-1].append(tsql)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   341
        self.outer_tables = {}
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   342
        self.duplicate_switches = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   343
        self.aliases = {}
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   344
        self.restrictions = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   345
        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
   346
        self.ignore_varmap = False
5013
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   347
        self._needs_source_cb = {}
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   348
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   349
    def merge_source_cbs(self, needs_source_cb):
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   350
        if self.needs_source_cb is None:
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   351
            self.needs_source_cb = needs_source_cb
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   352
        elif needs_source_cb != self.needs_source_cb:
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   353
            raise QueryError('query fetch some source mapped attribute, some not')
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   354
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   355
    def finalize_source_cbs(self):
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   356
        if self.subquery_source_cb is not None:
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   357
            self.needs_source_cb.update(self.subquery_source_cb)
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   358
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   359
    def add_restriction(self, restr):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   360
        if restr:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   361
            self.restrictions.append(restr)
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   362
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   363
    def iter_exists_sols(self, exists):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   364
        if not exists in self.existssols:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   365
            yield 1
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   366
            return
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   367
        thisexistssols, thisexistsvars = self.existssols[exists]
7042
8bcf33c493f8 [sql generation] fix exists table scope pb: when iterating other solutions inner to an EXISTS subquery, we should reset variables which have this exists node as scope
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6940
diff changeset
   368
        # when iterating other solutions inner to an EXISTS subquery, we should
8bcf33c493f8 [sql generation] fix exists table scope pb: when iterating other solutions inner to an EXISTS subquery, we should reset variables which have this exists node as scope
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6940
diff changeset
   369
        # reset variables which have this exists node as scope at each iteration
8bcf33c493f8 [sql generation] fix exists table scope pb: when iterating other solutions inner to an EXISTS subquery, we should reset variables which have this exists node as scope
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6940
diff changeset
   370
        for var in exists.stmt.defined_vars.itervalues():
8bcf33c493f8 [sql generation] fix exists table scope pb: when iterating other solutions inner to an EXISTS subquery, we should reset variables which have this exists node as scope
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6940
diff changeset
   371
            if var.scope is exists:
8bcf33c493f8 [sql generation] fix exists table scope pb: when iterating other solutions inner to an EXISTS subquery, we should reset variables which have this exists node as scope
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6940
diff changeset
   372
                thisexistsvars.add(var.name)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   373
        origsol = self.solution
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   374
        origtables = self.tables
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   375
        done = self.done
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   376
        for thisexistssol in thisexistssols:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   377
            for vname in self.unstablevars:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   378
                if thisexistssol[vname] != origsol[vname] and vname in thisexistsvars:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   379
                    break
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   380
            else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   381
                self.tables = origtables.copy()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   382
                self.solution = thisexistssol
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   383
                yield 1
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   384
                # cleanup self.done from stuff specific to exists
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   385
                for var in thisexistsvars:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   386
                    if var in done:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   387
                        done.remove(var)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   388
                for rel in exists.iget_nodes(Relation):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   389
                    if rel in done:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   390
                        done.remove(rel)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   391
        self.solution = origsol
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   392
        self.tables = origtables
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   393
5582
3e133b29a1a4 [rql2sql] follow rql 0.26.1 changes: NOT nodes normalization, allowing simplification of sql generation, and fix #XXX
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5426
diff changeset
   394
    def push_scope(self, scope_node):
3e133b29a1a4 [rql2sql] follow rql 0.26.1 changes: NOT nodes normalization, allowing simplification of sql generation, and fix #XXX
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5426
diff changeset
   395
        self.scope_nodes.append(scope_node)
3e133b29a1a4 [rql2sql] follow rql 0.26.1 changes: NOT nodes normalization, allowing simplification of sql generation, and fix #XXX
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5426
diff changeset
   396
        self.scopes[scope_node] = len(self.actual_tables)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   397
        self.actual_tables.append([])
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   398
        self._restr_stack.append(self.restrictions)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   399
        self.restrictions = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   400
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   401
    def pop_scope(self):
5582
3e133b29a1a4 [rql2sql] follow rql 0.26.1 changes: NOT nodes normalization, allowing simplification of sql generation, and fix #XXX
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5426
diff changeset
   402
        del self.scopes[self.scope_nodes[-1]]
3e133b29a1a4 [rql2sql] follow rql 0.26.1 changes: NOT nodes normalization, allowing simplification of sql generation, and fix #XXX
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5426
diff changeset
   403
        self.scope_nodes.pop()
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   404
        restrictions = self.restrictions
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   405
        self.restrictions = self._restr_stack.pop()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   406
        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
   407
5782
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   408
def extract_fake_having_terms(having):
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   409
    """RQL's HAVING may be used to contains stuff that should go in the WHERE
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   410
    clause of the SQL query, due to RQL grammar limitation. Split them...
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   411
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   412
    Return a list nodes that can be ANDed with query's WHERE clause. Having
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   413
    subtrees updated in place.
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   414
    """
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   415
    fakehaving = []
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   416
    for subtree in having:
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   417
        ors, tocheck = set(), []
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   418
        for compnode in subtree.get_nodes(Comparison):
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   419
            for fnode in compnode.get_nodes(Function):
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   420
                if fnode.descr().aggregat:
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   421
                    p = compnode.parent
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   422
                    oor = None
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   423
                    while not isinstance(p, Select):
6929
e9464d975908 [rql2sql] fix bug with NOT in "fake" having terms
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6493
diff changeset
   424
                        if isinstance(p, (Or, Not)):
5782
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   425
                            oor = p
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   426
                        p = p.parent
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   427
                    if oor is not None:
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   428
                        ors.add(oor)
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   429
                    break
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   430
            else:
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   431
                tocheck.append(compnode)
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   432
        # tocheck hold a set of comparison not implying an aggregat function
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   433
        # put them in fakehaving if the don't share an Or node as ancestor
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   434
        # with another comparison containing an aggregat function
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   435
        for compnode in tocheck:
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   436
            parents = set()
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   437
            p = compnode.parent
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   438
            oor = None
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   439
            while not isinstance(p, Select):
5887
3f55f0f10a22 [rql2sql] test and fix crash when OR in HAVING where both expresion are 'fake' having terms (eg not using aggregat function)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5844
diff changeset
   440
                if p in ors or p is None: # p is None for nodes already in fakehaving
5782
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   441
                    break
6929
e9464d975908 [rql2sql] fix bug with NOT in "fake" having terms
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6493
diff changeset
   442
                if isinstance(p, (Or, Not)):
5782
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   443
                    oor = p
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   444
                p = p.parent
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   445
            else:
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   446
                node = oor or compnode
5887
3f55f0f10a22 [rql2sql] test and fix crash when OR in HAVING where both expresion are 'fake' having terms (eg not using aggregat function)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5844
diff changeset
   447
                fakehaving.append(node)
3f55f0f10a22 [rql2sql] test and fix crash when OR in HAVING where both expresion are 'fake' having terms (eg not using aggregat function)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5844
diff changeset
   448
                node.parent.remove(node)
5782
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   449
    return fakehaving
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   450
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   451
class SQLGenerator(object):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   452
    """
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   453
    generation of SQL from the fully expanded RQL syntax tree
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   454
    SQL is designed to be used with a CubicWeb SQL schema
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   455
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   456
    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
   457
    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
   458
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   459
    we should not have errors here !
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   460
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   461
    WARNING: a CubicWebSQLGenerator instance is not thread safe, but generate is
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   462
    protected by a lock
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   463
    """
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   464
5010
b2c5aee8ca3f [cleanup] rename dbms_helper to dbhelper for consistency
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4845
diff changeset
   465
    def __init__(self, schema, dbhelper, attrmap=None):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   466
        self.schema = schema
5010
b2c5aee8ca3f [cleanup] rename dbms_helper to dbhelper for consistency
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4845
diff changeset
   467
        self.dbhelper = dbhelper
b2c5aee8ca3f [cleanup] rename dbms_helper to dbhelper for consistency
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4845
diff changeset
   468
        self.dbencoding = dbhelper.dbencoding
b2c5aee8ca3f [cleanup] rename dbms_helper to dbhelper for consistency
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4845
diff changeset
   469
        self.keyword_map = {'NOW' : self.dbhelper.sql_current_timestamp,
b2c5aee8ca3f [cleanup] rename dbms_helper to dbhelper for consistency
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4845
diff changeset
   470
                            'TODAY': self.dbhelper.sql_current_date,
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   471
                            }
5010
b2c5aee8ca3f [cleanup] rename dbms_helper to dbhelper for consistency
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4845
diff changeset
   472
        if not self.dbhelper.union_parentheses_support:
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   473
            self.union_sql = self.noparen_union_sql
5010
b2c5aee8ca3f [cleanup] rename dbms_helper to dbhelper for consistency
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4845
diff changeset
   474
        if self.dbhelper.fti_need_distinct:
4836
3e3c4917e94e [sql generation] fti_need_distinct quere was not anymore used, potentially causing duplicates on has_text queries with sqlite backend
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4794
diff changeset
   475
            self.__union_sql = self.union_sql
3e3c4917e94e [sql generation] fti_need_distinct quere was not anymore used, potentially causing duplicates on has_text queries with sqlite backend
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4794
diff changeset
   476
            self.union_sql = self.has_text_need_distinct_union_sql
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   477
        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
   478
        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
   479
            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
   480
        self.attr_map = attrmap
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   481
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   482
    def generate(self, union, args=None, varmap=None):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   483
        """return SQL queries and a variable dictionnary from a RQL syntax tree
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   484
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   485
        :partrqls: a list of couple (rqlst, solutions)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   486
        :args: optional dictionary with values of substitutions used in the query
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   487
        :varmap: optional dictionary mapping variable name to a special table
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   488
          name, in case the query as to fetch data from temporary tables
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   489
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   490
        return an sql string and a dictionary with substitutions values
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   491
        """
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   492
        if args is None:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   493
            args = {}
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   494
        if varmap is None:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   495
            varmap =  {}
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   496
        self._lock.acquire()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   497
        self._args = args
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   498
        self._varmap = varmap
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   499
        self._query_attrs = {}
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   500
        self._state = None
5582
3e133b29a1a4 [rql2sql] follow rql 0.26.1 changes: NOT nodes normalization, allowing simplification of sql generation, and fix #XXX
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5426
diff changeset
   501
        # self._not_scope_offset = 0
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   502
        try:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   503
            # union query for each rqlst / solution
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   504
            sql = self.union_sql(union)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   505
            # we are done
5013
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   506
            return sql, self._query_attrs, self._state.needs_source_cb
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   507
        finally:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   508
            self._lock.release()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   509
4836
3e3c4917e94e [sql generation] fti_need_distinct quere was not anymore used, potentially causing duplicates on has_text queries with sqlite backend
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4794
diff changeset
   510
    def has_text_need_distinct_union_sql(self, union, needalias=False):
3e3c4917e94e [sql generation] fti_need_distinct quere was not anymore used, potentially causing duplicates on has_text queries with sqlite backend
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4794
diff changeset
   511
        if getattr(union, 'has_text_query', False):
3e3c4917e94e [sql generation] fti_need_distinct quere was not anymore used, potentially causing duplicates on has_text queries with sqlite backend
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4794
diff changeset
   512
            for select in union.children:
3e3c4917e94e [sql generation] fti_need_distinct quere was not anymore used, potentially causing duplicates on has_text queries with sqlite backend
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4794
diff changeset
   513
                select.need_distinct = True
3e3c4917e94e [sql generation] fti_need_distinct quere was not anymore used, potentially causing duplicates on has_text queries with sqlite backend
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4794
diff changeset
   514
        return self.__union_sql(union, needalias)
3e3c4917e94e [sql generation] fti_need_distinct quere was not anymore used, potentially causing duplicates on has_text queries with sqlite backend
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4794
diff changeset
   515
6491
ee9a10b6620e pylint option update
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6297
diff changeset
   516
    def union_sql(self, union, needalias=False): # pylint: disable=E0202
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   517
        if len(union.children) == 1:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   518
            return self.select_sql(union.children[0], needalias)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   519
        sqls = ('(%s)' % self.select_sql(select, needalias)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   520
                for select in union.children)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   521
        return '\nUNION ALL\n'.join(sqls)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   522
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   523
    def noparen_union_sql(self, union, needalias=False):
5010
b2c5aee8ca3f [cleanup] rename dbms_helper to dbhelper for consistency
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4845
diff changeset
   524
        # needed for sqlite backend which doesn't like parentheses around union
b2c5aee8ca3f [cleanup] rename dbms_helper to dbhelper for consistency
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4845
diff changeset
   525
        # query. This may cause bug in some condition (sort in one of the
b2c5aee8ca3f [cleanup] rename dbms_helper to dbhelper for consistency
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4845
diff changeset
   526
        # subquery) but will work in most case
b2c5aee8ca3f [cleanup] rename dbms_helper to dbhelper for consistency
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4845
diff changeset
   527
        #
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   528
        # see http://www.sqlite.org/cvstrac/tktview?tn=3074
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   529
        sqls = (self.select_sql(select, needalias)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   530
                for i, select in enumerate(union.children))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   531
        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
   532
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   533
    def select_sql(self, select, needalias=False):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   534
        """return SQL queries and a variable dictionnary from a RQL syntax tree
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   535
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   536
        :select: a selection statement of the syntax tree (`rql.stmts.Select`)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   537
        :solution: a dictionnary containing variables binding.
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   538
          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
   539
          types as values
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   540
        :needwrap: boolean telling if the query will be wrapped in an outer
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   541
          query (to deal with aggregat and/or grouping)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   542
        """
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   543
        distinct = selectsortterms = select.need_distinct
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   544
        sorts = select.orderby
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   545
        groups = select.groupby
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   546
        having = select.having
5782
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   547
        morerestr = extract_fake_having_terms(having)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   548
        # remember selection, it may be changed and have to be restored
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   549
        origselection = select.selection[:]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   550
        # 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
   551
        # selection (union or distinct query) and wrapping (union with groups)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   552
        needwrap = False
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   553
        sols = select.solutions
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   554
        if len(sols) > 1:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   555
            # remove invariant from solutions
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   556
            sols, existssols, unstable = remove_unused_solutions(
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   557
                select, sols, self._varmap, self.schema)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   558
            if len(sols) > 1:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   559
                # if there is still more than one solution, a UNION will be
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   560
                # generated and so sort terms have to be selected
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   561
                selectsortterms = True
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   562
                # and if select is using group by or aggregat, a wrapping
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   563
                # query will be necessary
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   564
                if groups or select.has_aggregat:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   565
                    select.select_only_variables()
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   566
                    needwrap = True
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   567
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   568
            existssols, unstable = {}, ()
5582
3e133b29a1a4 [rql2sql] follow rql 0.26.1 changes: NOT nodes normalization, allowing simplification of sql generation, and fix #XXX
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5426
diff changeset
   569
        state = StateInfo(select, existssols, unstable)
5013
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   570
        if self._state is not None:
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   571
            # state from a previous unioned select
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   572
            state.merge_source_cbs(self._state.needs_source_cb)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   573
        # treat subqueries
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   574
        self._subqueries_sql(select, state)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   575
        # generate sql for this select node
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   576
        selectidx = [str(term) for term in select.selection]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   577
        if needwrap:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   578
            outerselection = origselection[:]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   579
            if sorts and selectsortterms:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   580
                outerselectidx = [str(term) for term in outerselection]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   581
                if distinct:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   582
                    sort_term_selection(sorts, outerselectidx,
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   583
                                        outerselection, groups)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   584
            else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   585
                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
   586
        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
   587
                                selectsortterms, sorts, groups, having)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   588
        if needwrap:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   589
            fselectidx = outerselectidx
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   590
            fneedwrap = len(outerselection) != len(origselection)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   591
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   592
            fselectidx = selectidx
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   593
            fneedwrap = len(select.selection) != len(origselection)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   594
        if fneedwrap:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   595
            needalias = True
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   596
        self._in_wrapping_query = False
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   597
        self._state = state
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   598
        try:
5782
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   599
            sql = self._solutions_sql(select, morerestr, sols, distinct,
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   600
                                      needalias or needwrap)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   601
            # generate groups / having before wrapping query selection to
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   602
            # get correct column aliases
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   603
            self._in_wrapping_query = needwrap
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   604
            if groups:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   605
                # no constant should be inserted in GROUP BY else the backend will
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   606
                # interpret it as a positional index in the selection
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   607
                groups = ','.join(vref.accept(self) for vref in groups
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   608
                                  if not isinstance(vref, Constant))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   609
            if having:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   610
                # filter out constants as for GROUP BY
5662
785837baabba HAVING sql clause don't know about comma...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5635
diff changeset
   611
                having = ' AND '.join(term.accept(self) for term in having
785837baabba HAVING sql clause don't know about comma...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5635
diff changeset
   612
                                      if not isinstance(term, Constant))
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   613
            if needwrap:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   614
                sql = '%s FROM (%s) AS T1' % (self._selection_sql(outerselection, distinct,
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   615
                                                                  needalias),
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   616
                                              sql)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   617
            if groups:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   618
                sql += '\nGROUP BY %s' % groups
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   619
            if having:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   620
                sql += '\nHAVING %s' % having
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   621
            # sort
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   622
            if sorts:
5768
1e73a466aa69 [fti] support for fti ranking: has_text query results sorted by relevance, and provides a way to control weight per entity / entity's attribute
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   623
                sqlsortterms = [self._sortterm_sql(sortterm, fselectidx)
1e73a466aa69 [fti] support for fti ranking: has_text query results sorted by relevance, and provides a way to control weight per entity / entity's attribute
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   624
                                for sortterm in sorts]
1e73a466aa69 [fti] support for fti ranking: has_text query results sorted by relevance, and provides a way to control weight per entity / entity's attribute
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   625
                sqlsortterms = [x for x in sqlsortterms if x is not None]
1e73a466aa69 [fti] support for fti ranking: has_text query results sorted by relevance, and provides a way to control weight per entity / entity's attribute
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   626
                if sqlsortterms:
1e73a466aa69 [fti] support for fti ranking: has_text query results sorted by relevance, and provides a way to control weight per entity / entity's attribute
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   627
                    sql += '\nORDER BY %s' % ','.join(sqlsortterms)
1e73a466aa69 [fti] support for fti ranking: has_text query results sorted by relevance, and provides a way to control weight per entity / entity's attribute
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   628
                    if sorts and fneedwrap:
1e73a466aa69 [fti] support for fti ranking: has_text query results sorted by relevance, and provides a way to control weight per entity / entity's attribute
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   629
                        selection = ['T1.C%s' % i for i in xrange(len(origselection))]
1e73a466aa69 [fti] support for fti ranking: has_text query results sorted by relevance, and provides a way to control weight per entity / entity's attribute
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   630
                        sql = 'SELECT %s FROM (%s) AS T1' % (','.join(selection), sql)
5013
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   631
            state.finalize_source_cbs()
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   632
        finally:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   633
            select.selection = origselection
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   634
        # limit / offset
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   635
        limit = select.limit
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   636
        if limit:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   637
            sql += '\nLIMIT %s' % limit
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   638
        offset = select.offset
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   639
        if offset:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   640
            sql += '\nOFFSET %s' % offset
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   641
        return sql
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   642
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   643
    def _subqueries_sql(self, select, state):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   644
        for i, subquery in enumerate(select.with_):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   645
            sql = self.union_sql(subquery.query, needalias=True)
5010
b2c5aee8ca3f [cleanup] rename dbms_helper to dbhelper for consistency
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4845
diff changeset
   646
            tablealias = '_T%s' % i # XXX nested subqueries
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   647
            sql = '(%s) AS %s' % (sql, tablealias)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   648
            state.subtables[tablealias] = (0, sql)
5013
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   649
            latest_state = self._state
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   650
            for vref in subquery.aliases:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   651
                alias = vref.variable
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   652
                alias._q_sqltable = tablealias
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   653
                alias._q_sql = '%s.C%s' % (tablealias, alias.colnum)
5013
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   654
                try:
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   655
                    stack = latest_state.needs_source_cb[alias.colnum]
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   656
                    if state.subquery_source_cb is None:
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   657
                        state.subquery_source_cb = {}
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   658
                    for selectidx, vref in iter_mapped_var_sels(select, alias):
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   659
                        stack = stack[:]
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   660
                        update_source_cb_stack(state, select, vref, stack)
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   661
                        state.subquery_source_cb[selectidx] = stack
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   662
                except KeyError:
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   663
                    continue
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   664
5782
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   665
    def _solutions_sql(self, select, morerestr, solutions, distinct, needalias):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   666
        sqls = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   667
        for solution in solutions:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   668
            self._state.reset(solution)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   669
            # visit restriction subtree
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   670
            if select.where is not None:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   671
                self._state.add_restriction(select.where.accept(self))
5782
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   672
            for restriction in morerestr:
8ff48d1a319f [rql2sql] when using HAVING to by-pass rql limitation (not to filter on result of an aggregat function), we should emit SQL that doesn't use HAVING to avoid potential backend error because variables are not grouped. Closes #1061603.
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   673
                self._state.add_restriction(restriction.accept(self))
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   674
            sql = [self._selection_sql(select.selection, distinct, needalias)]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   675
            if self._state.restrictions:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   676
                sql.append('WHERE %s' % ' AND '.join(self._state.restrictions))
5013
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
   677
            self._state.merge_source_cbs(self._state._needs_source_cb)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   678
            # add required tables
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   679
            assert len(self._state.actual_tables) == 1, self._state.actual_tables
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   680
            tables = self._state.actual_tables[-1]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   681
            if tables:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   682
                # sort for test predictability
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   683
                sql.insert(1, 'FROM %s' % ', '.join(sorted(tables)))
5010
b2c5aee8ca3f [cleanup] rename dbms_helper to dbhelper for consistency
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4845
diff changeset
   684
            elif self._state.restrictions and self.dbhelper.needs_from_clause:
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   685
                sql.insert(1, 'FROM (SELECT 1) AS _T')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   686
            sqls.append('\n'.join(sql))
5582
3e133b29a1a4 [rql2sql] follow rql 0.26.1 changes: NOT nodes normalization, allowing simplification of sql generation, and fix #XXX
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5426
diff changeset
   687
        if distinct:
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   688
            return '\nUNION\n'.join(sqls)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   689
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   690
            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
   691
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   692
    def _selection_sql(self, selected, distinct, needaliasing=False):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   693
        clause = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   694
        for term in selected:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   695
            sql = term.accept(self)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   696
            if needaliasing:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   697
                colalias = 'C%s' % len(clause)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   698
                clause.append('%s AS %s' % (sql, colalias))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   699
                if isinstance(term, VariableRef):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   700
                    self._state.aliases[term.name] = colalias
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   701
            else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   702
                clause.append(sql)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   703
        if distinct:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   704
            return 'SELECT DISTINCT %s' % ', '.join(clause)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   705
        return 'SELECT %s' % ', '.join(clause)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   706
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   707
    def _sortterm_sql(self, sortterm, selectidx):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   708
        term = sortterm.term
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   709
        try:
5768
1e73a466aa69 [fti] support for fti ranking: has_text query results sorted by relevance, and provides a way to control weight per entity / entity's attribute
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   710
            sqlterm = selectidx.index(str(term)) + 1
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   711
        except ValueError:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   712
            # Constant node or non selected term
5768
1e73a466aa69 [fti] support for fti ranking: has_text query results sorted by relevance, and provides a way to control weight per entity / entity's attribute
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   713
            sqlterm = term.accept(self)
1e73a466aa69 [fti] support for fti ranking: has_text query results sorted by relevance, and provides a way to control weight per entity / entity's attribute
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   714
            if sqlterm is None:
1e73a466aa69 [fti] support for fti ranking: has_text query results sorted by relevance, and provides a way to control weight per entity / entity's attribute
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   715
                return None
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   716
        if sortterm.asc:
5768
1e73a466aa69 [fti] support for fti ranking: has_text query results sorted by relevance, and provides a way to control weight per entity / entity's attribute
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
   717
            return str(sqlterm)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   718
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   719
            return '%s DESC' % sqlterm
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   720
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   721
    def visit_and(self, et):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   722
        """generate SQL for a AND subtree"""
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   723
        res = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   724
        for c in et.children:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   725
            part = c.accept(self)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   726
            if part:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   727
                res.append(part)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   728
        return ' AND '.join(res)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   729
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   730
    def visit_or(self, ou):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   731
        """generate SQL for a OR subtree"""
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   732
        res = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   733
        for c in ou.children:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   734
            part = c.accept(self)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   735
            if part:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   736
                res.append('(%s)' % part)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   737
        if res:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   738
            if len(res) > 1:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   739
                return '(%s)' % ' OR '.join(res)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   740
            return res[0]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   741
        return ''
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   742
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   743
    def visit_not(self, node):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   744
        csql = node.children[0].accept(self)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   745
        if node in self._state.done or not csql:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   746
            # already processed or no sql generated by children
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   747
            return csql
5582
3e133b29a1a4 [rql2sql] follow rql 0.26.1 changes: NOT nodes normalization, allowing simplification of sql generation, and fix #XXX
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5426
diff changeset
   748
        return 'NOT (%s)' % csql
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   749
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   750
    def visit_exists(self, exists):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   751
        """generate SQL name for a exists subquery"""
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   752
        sqls = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   753
        for dummy in self._state.iter_exists_sols(exists):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   754
            sql = self._visit_exists(exists)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   755
            if sql:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   756
                sqls.append(sql)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   757
        if not sqls:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   758
            return ''
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   759
        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
   760
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   761
    def _visit_exists(self, exists):
5582
3e133b29a1a4 [rql2sql] follow rql 0.26.1 changes: NOT nodes normalization, allowing simplification of sql generation, and fix #XXX
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5426
diff changeset
   762
        self._state.push_scope(exists)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   763
        restriction = exists.children[0].accept(self)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   764
        restrictions, tables = self._state.pop_scope()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   765
        if restriction:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   766
            restrictions.append(restriction)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   767
        restriction = ' AND '.join(restrictions)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   768
        if not restriction:
6297
23c1e50ff97b [rql] fix bug with query like 'Any 1 WHERE NOT X in_group G': tables should be kept in EXISTS() even when there are no restriction
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6286
diff changeset
   769
            if tables:
23c1e50ff97b [rql] fix bug with query like 'Any 1 WHERE NOT X in_group G': tables should be kept in EXISTS() even when there are no restriction
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6286
diff changeset
   770
                return 'SELECT 1 FROM %s' % ', '.join(tables)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   771
            return ''
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   772
        if not tables:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   773
            # 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
   774
            sql = 'SELECT 1 WHERE %s' % restriction
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   775
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   776
            sql = 'SELECT 1 FROM %s WHERE %s' % (', '.join(tables), restriction)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   777
        return sql
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   778
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   779
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   780
    def visit_relation(self, relation):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   781
        """generate SQL for a relation"""
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   782
        rtype = relation.r_type
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   783
        # don't care of type constraint statement (i.e. relation_type = 'is')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   784
        if relation.is_types_restriction():
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   785
            return ''
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   786
        lhs, rhs = relation.get_parts()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   787
        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
   788
        if rschema.final:
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   789
            if rtype == 'eid' and lhs.variable._q_invariant and \
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   790
                   lhs.variable.stinfo['constnode']:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   791
                # special case where this restriction is already generated by
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   792
                # some other relation
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   793
                return ''
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   794
            # attribute relation
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   795
            if rtype == 'has_text':
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   796
                sql = self._visit_has_text_relation(relation)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   797
            else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   798
                rhs_vars = rhs.get_nodes(VariableRef)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   799
                if rhs_vars:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   800
                    # if variable(s) in the RHS
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   801
                    sql = self._visit_var_attr_relation(relation, rhs_vars)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   802
                else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   803
                    # no variables in the RHS
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   804
                    sql = self._visit_attribute_relation(relation)
5706
c2e8290bc7b7 [rql2sql] fix special 'X relation NULL' case (used by security insertion), broken by recent removal of IS operator in RQL
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5702
diff changeset
   805
        elif (rtype == 'is' and isinstance(rhs.children[0], Constant)
c2e8290bc7b7 [rql2sql] fix special 'X relation NULL' case (used by security insertion), broken by recent removal of IS operator in RQL
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5702
diff changeset
   806
              and rhs.children[0].eval(self._args) is None):
c2e8290bc7b7 [rql2sql] fix special 'X relation NULL' case (used by security insertion), broken by recent removal of IS operator in RQL
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5702
diff changeset
   807
            # special case "C is NULL"
c2e8290bc7b7 [rql2sql] fix special 'X relation NULL' case (used by security insertion), broken by recent removal of IS operator in RQL
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5702
diff changeset
   808
            if lhs.name in self._varmap:
c2e8290bc7b7 [rql2sql] fix special 'X relation NULL' case (used by security insertion), broken by recent removal of IS operator in RQL
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5702
diff changeset
   809
                lhssql = self._varmap[lhs.name]
c2e8290bc7b7 [rql2sql] fix special 'X relation NULL' case (used by security insertion), broken by recent removal of IS operator in RQL
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5702
diff changeset
   810
            else:
c2e8290bc7b7 [rql2sql] fix special 'X relation NULL' case (used by security insertion), broken by recent removal of IS operator in RQL
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5702
diff changeset
   811
                lhssql = lhs.accept(self)
c2e8290bc7b7 [rql2sql] fix special 'X relation NULL' case (used by security insertion), broken by recent removal of IS operator in RQL
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5702
diff changeset
   812
            return '%s%s' % (lhssql, rhs.accept(self))
c2e8290bc7b7 [rql2sql] fix special 'X relation NULL' case (used by security insertion), broken by recent removal of IS operator in RQL
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5702
diff changeset
   813
        elif '%s.%s' % (lhs, relation.r_type) in self._varmap:
c2e8290bc7b7 [rql2sql] fix special 'X relation NULL' case (used by security insertion), broken by recent removal of IS operator in RQL
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5702
diff changeset
   814
            # relation has already been processed by a previous step
c2e8290bc7b7 [rql2sql] fix special 'X relation NULL' case (used by security insertion), broken by recent removal of IS operator in RQL
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5702
diff changeset
   815
            return ''
c2e8290bc7b7 [rql2sql] fix special 'X relation NULL' case (used by security insertion), broken by recent removal of IS operator in RQL
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5702
diff changeset
   816
        elif relation.optional:
c2e8290bc7b7 [rql2sql] fix special 'X relation NULL' case (used by security insertion), broken by recent removal of IS operator in RQL
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5702
diff changeset
   817
            # check it has not already been treaten (to get necessary
c2e8290bc7b7 [rql2sql] fix special 'X relation NULL' case (used by security insertion), broken by recent removal of IS operator in RQL
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5702
diff changeset
   818
            # information to add an outer join condition)
c2e8290bc7b7 [rql2sql] fix special 'X relation NULL' case (used by security insertion), broken by recent removal of IS operator in RQL
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5702
diff changeset
   819
            if relation in self._state.done:
c2e8290bc7b7 [rql2sql] fix special 'X relation NULL' case (used by security insertion), broken by recent removal of IS operator in RQL
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5702
diff changeset
   820
                return ''
c2e8290bc7b7 [rql2sql] fix special 'X relation NULL' case (used by security insertion), broken by recent removal of IS operator in RQL
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5702
diff changeset
   821
            # OPTIONAL relation, generate a left|right outer join
c2e8290bc7b7 [rql2sql] fix special 'X relation NULL' case (used by security insertion), broken by recent removal of IS operator in RQL
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5702
diff changeset
   822
            sql = self._visit_outer_join_relation(relation, rschema)
c2e8290bc7b7 [rql2sql] fix special 'X relation NULL' case (used by security insertion), broken by recent removal of IS operator in RQL
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5702
diff changeset
   823
        elif rschema.inlined:
c2e8290bc7b7 [rql2sql] fix special 'X relation NULL' case (used by security insertion), broken by recent removal of IS operator in RQL
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5702
diff changeset
   824
            sql = self._visit_inlined_relation(relation)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   825
        else:
5706
c2e8290bc7b7 [rql2sql] fix special 'X relation NULL' case (used by security insertion), broken by recent removal of IS operator in RQL
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5702
diff changeset
   826
            # regular (non final) relation
c2e8290bc7b7 [rql2sql] fix special 'X relation NULL' case (used by security insertion), broken by recent removal of IS operator in RQL
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5702
diff changeset
   827
            sql = self._visit_relation(relation, rschema)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   828
        return sql
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   829
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   830
    def _visit_inlined_relation(self, relation):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   831
        lhsvar, _, rhsvar, rhsconst = relation_info(relation)
6003
5fbc1c4c13ff [rql2sql] fix bug with NOT of inlined relation: NULL values are not properly handled and hence some rows won't be returned while the should
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5901
diff changeset
   832
        # we are sure lhsvar is not None
5fbc1c4c13ff [rql2sql] fix bug with NOT of inlined relation: NULL values are not properly handled and hence some rows won't be returned while the should
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5901
diff changeset
   833
        lhssql = self._inlined_var_sql(lhsvar, relation.r_type)
5fbc1c4c13ff [rql2sql] fix bug with NOT of inlined relation: NULL values are not properly handled and hence some rows won't be returned while the should
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5901
diff changeset
   834
        if rhsvar is None:
5fbc1c4c13ff [rql2sql] fix bug with NOT of inlined relation: NULL values are not properly handled and hence some rows won't be returned while the should
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5901
diff changeset
   835
            moresql = None
5fbc1c4c13ff [rql2sql] fix bug with NOT of inlined relation: NULL values are not properly handled and hence some rows won't be returned while the should
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5901
diff changeset
   836
        else:
5fbc1c4c13ff [rql2sql] fix bug with NOT of inlined relation: NULL values are not properly handled and hence some rows won't be returned while the should
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5901
diff changeset
   837
            moresql = self._extra_join_sql(relation, lhssql, rhsvar)
5fbc1c4c13ff [rql2sql] fix bug with NOT of inlined relation: NULL values are not properly handled and hence some rows won't be returned while the should
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5901
diff changeset
   838
        if isinstance(relation.parent, Not):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   839
            self._state.done.add(relation.parent)
6003
5fbc1c4c13ff [rql2sql] fix bug with NOT of inlined relation: NULL values are not properly handled and hence some rows won't be returned while the should
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5901
diff changeset
   840
            if rhsvar is not None and rhsvar._q_invariant:
5fbc1c4c13ff [rql2sql] fix bug with NOT of inlined relation: NULL values are not properly handled and hence some rows won't be returned while the should
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5901
diff changeset
   841
                sql = '%s IS NULL' % lhssql
5fbc1c4c13ff [rql2sql] fix bug with NOT of inlined relation: NULL values are not properly handled and hence some rows won't be returned while the should
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5901
diff changeset
   842
            else:
5fbc1c4c13ff [rql2sql] fix bug with NOT of inlined relation: NULL values are not properly handled and hence some rows won't be returned while the should
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5901
diff changeset
   843
                # column != 1234 may not get back rows where column is NULL...
5fbc1c4c13ff [rql2sql] fix bug with NOT of inlined relation: NULL values are not properly handled and hence some rows won't be returned while the should
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5901
diff changeset
   844
                sql = '(%s IS NULL OR %s!=%s)' % (
5fbc1c4c13ff [rql2sql] fix bug with NOT of inlined relation: NULL values are not properly handled and hence some rows won't be returned while the should
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5901
diff changeset
   845
                    lhssql, lhssql, (rhsvar or rhsconst).accept(self))
5fbc1c4c13ff [rql2sql] fix bug with NOT of inlined relation: NULL values are not properly handled and hence some rows won't be returned while the should
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5901
diff changeset
   846
        elif rhsconst is not None:
5fbc1c4c13ff [rql2sql] fix bug with NOT of inlined relation: NULL values are not properly handled and hence some rows won't be returned while the should
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5901
diff changeset
   847
            sql = '%s=%s' % (lhssql, rhsconst.accept(self))
5fbc1c4c13ff [rql2sql] fix bug with NOT of inlined relation: NULL values are not properly handled and hence some rows won't be returned while the should
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5901
diff changeset
   848
        elif isinstance(rhsvar, Variable) and rhsvar._q_invariant and \
5fbc1c4c13ff [rql2sql] fix bug with NOT of inlined relation: NULL values are not properly handled and hence some rows won't be returned while the should
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5901
diff changeset
   849
                 not rhsvar.name in self._varmap:
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   850
            # 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
   851
            # 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
   852
            # relation
6003
5fbc1c4c13ff [rql2sql] fix bug with NOT of inlined relation: NULL values are not properly handled and hence some rows won't be returned while the should
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5901
diff changeset
   853
            if moresql is not None:
5fbc1c4c13ff [rql2sql] fix bug with NOT of inlined relation: NULL values are not properly handled and hence some rows won't be returned while the should
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5901
diff changeset
   854
                return moresql
5fbc1c4c13ff [rql2sql] fix bug with NOT of inlined relation: NULL values are not properly handled and hence some rows won't be returned while the should
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5901
diff changeset
   855
            return '%s IS NOT NULL' % lhssql
5fbc1c4c13ff [rql2sql] fix bug with NOT of inlined relation: NULL values are not properly handled and hence some rows won't be returned while the should
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5901
diff changeset
   856
        else:
5fbc1c4c13ff [rql2sql] fix bug with NOT of inlined relation: NULL values are not properly handled and hence some rows won't be returned while the should
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5901
diff changeset
   857
            sql = '%s=%s' % (lhssql, rhsvar.accept(self))
5fbc1c4c13ff [rql2sql] fix bug with NOT of inlined relation: NULL values are not properly handled and hence some rows won't be returned while the should
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5901
diff changeset
   858
        if moresql is None:
5fbc1c4c13ff [rql2sql] fix bug with NOT of inlined relation: NULL values are not properly handled and hence some rows won't be returned while the should
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5901
diff changeset
   859
            return sql
5fbc1c4c13ff [rql2sql] fix bug with NOT of inlined relation: NULL values are not properly handled and hence some rows won't be returned while the should
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5901
diff changeset
   860
        return '%s AND %s' % (sql, moresql)
0
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 _process_relation_term(self, relation, rid, termvar, termconst, relfield):
4794
81075b09ebf1 [rql2sql] fix potential crash when testing _q_invariant on a ColumnAlias instead of a Variable by always considering _q_invariant to false for them
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4719
diff changeset
   863
        if termconst or not termvar._q_invariant:
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   864
            termsql = termconst and termconst.accept(self) or termvar.accept(self)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   865
            yield '%s.%s=%s' % (rid, relfield, termsql)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   866
        elif termvar._q_invariant:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   867
            # if the variable is mapped, generate restriction anyway
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   868
            if termvar.name in self._varmap:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   869
                termsql = termvar.accept(self)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   870
                yield '%s.%s=%s' % (rid, relfield, termsql)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   871
            extrajoin = self._extra_join_sql(relation, '%s.%s' % (rid, relfield), termvar)
6003
5fbc1c4c13ff [rql2sql] fix bug with NOT of inlined relation: NULL values are not properly handled and hence some rows won't be returned while the should
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5901
diff changeset
   872
            if extrajoin is not None:
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   873
                yield extrajoin
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   874
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   875
    def _visit_relation(self, relation, rschema):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   876
        """generate SQL for a relation
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   877
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   878
        implements optimization 1.
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   879
        """
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   880
        if relation.r_type == 'identity':
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   881
            # special case "X identity Y"
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   882
            lhs, rhs = relation.get_parts()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   883
            return '%s%s' % (lhs.accept(self), rhs.accept(self))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   884
        lhsvar, lhsconst, rhsvar, rhsconst = relation_info(relation)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   885
        rid = self._relation_table(relation)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   886
        sqls = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   887
        sqls += self._process_relation_term(relation, rid, lhsvar, lhsconst, 'eid_from')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   888
        sqls += self._process_relation_term(relation, rid, rhsvar, rhsconst, 'eid_to')
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   889
        sql = ' AND '.join(sqls)
4467
0e73d299730a fix long-waiting symetric typo: should be spelled symmetric. Add auto database migration on schema deserialization
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4286
diff changeset
   890
        if rschema.symmetric:
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   891
            sql = '(%s OR %s)' % (sql, switch_relation_field(sql))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   892
        return sql
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   893
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   894
    def _visit_outer_join_relation(self, relation, rschema):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   895
        """
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   896
        left outer join syntax (optional=='right'):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   897
          X relation Y?
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   898
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   899
        right outer join syntax (optional=='left'):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   900
          X? relation Y
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
   901
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   902
        full outer join syntaxes (optional=='both'):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   903
          X? relation Y?
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   904
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   905
        if relation is inlined:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   906
           if it's a left outer join:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   907
           -> X LEFT OUTER JOIN Y ON (X.relation=Y.eid)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   908
           elif it's a right outer join:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   909
           -> Y LEFT OUTER JOIN X ON (X.relation=Y.eid)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   910
           elif it's a full outer join:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   911
           -> X FULL OUTER JOIN Y ON (X.relation=Y.eid)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   912
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   913
           if it's a left outer join:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   914
           -> X LEFT OUTER JOIN relation ON (relation.eid_from=X.eid)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   915
              LEFT OUTER JOIN Y ON (relation.eid_to=Y.eid)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   916
           elif it's a right outer join:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   917
           -> Y LEFT OUTER JOIN relation ON (relation.eid_to=Y.eid)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   918
              LEFT OUTER JOIN X ON (relation.eid_from=X.eid)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   919
           elif it's a full outer join:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   920
           -> X FULL OUTER JOIN Y ON (X.relation=Y.eid)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   921
        """
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   922
        lhsvar, lhsconst, rhsvar, rhsconst = relation_info(relation)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   923
        if relation.optional == 'right':
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   924
            joinattr, restrattr = 'eid_from', 'eid_to'
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   925
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   926
            lhsvar, rhsvar = rhsvar, lhsvar
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   927
            lhsconst, rhsconst = rhsconst, lhsconst
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   928
            joinattr, restrattr = 'eid_to', 'eid_from'
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   929
        if relation.optional == 'both':
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   930
            outertype = 'FULL'
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   931
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   932
            outertype = 'LEFT'
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   933
        if rschema.inlined or relation.r_type == 'identity':
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   934
            self._state.done.add(relation)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   935
            t1 = self._var_table(lhsvar)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   936
            if relation.r_type == 'identity':
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   937
                attr = 'eid'
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   938
            else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   939
                attr = relation.r_type
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   940
            # reset lhs/rhs, we need the initial order now
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   941
            lhs, rhs = relation.get_variable_parts()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   942
            if '%s.%s' % (lhs.name, attr) in self._varmap:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   943
                lhssql = self._varmap['%s.%s' % (lhs.name, attr)]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   944
            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
   945
                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
   946
            if not rhsvar is None:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   947
                t2 = self._var_table(rhsvar)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   948
                if t2 is None:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   949
                    if rhsconst is not None:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   950
                        # inlined relation with invariant as rhs
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   951
                        condition = '%s=%s' % (lhssql, rhsconst.accept(self))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   952
                        if relation.r_type != 'identity':
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   953
                            condition = '(%s OR %s IS NULL)' % (condition, lhssql)
5004
4cc020ee70e2 le patch rql26 a été importé
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4845
diff changeset
   954
                        if not lhsvar.stinfo.get('optrelations'):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   955
                            return condition
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   956
                        self.add_outer_join_condition(lhsvar, t1, condition)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   957
                    return
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   958
            else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   959
                condition = '%s=%s' % (lhssql, rhsconst.accept(self))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   960
                self.add_outer_join_condition(lhsvar, t1, condition)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   961
            join = '%s OUTER JOIN %s ON (%s=%s)' % (
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   962
                outertype, self._state.tables[t2][1], lhssql, rhs.accept(self))
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   963
            self.replace_tables_by_outer_join(join, t1, t2)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   964
            return ''
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   965
        lhssql = lhsconst and lhsconst.accept(self) or lhsvar.accept(self)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   966
        rhssql = rhsconst and rhsconst.accept(self) or rhsvar.accept(self)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   967
        rid = self._relation_table(relation)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   968
        if not lhsvar:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   969
            join = ''
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   970
            toreplace = []
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   971
            maintable = rid
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   972
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   973
            join = '%s OUTER JOIN %s ON (%s.%s=%s' % (
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   974
                outertype, self._state.tables[rid][1], rid, joinattr, lhssql)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   975
            toreplace = [rid]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   976
            maintable = self._var_table(lhsvar)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   977
            if rhsconst:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   978
                join += ' AND %s.%s=%s)' % (rid, restrattr, rhssql)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   979
            else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   980
                join += ')'
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   981
        if not rhsconst:
1122
9f37de24251f fix rql2sq w/ outer join on subquery result
sylvain.thenault@logilab.fr
parents: 438
diff changeset
   982
            rhstable = rhsvar._q_sqltable
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   983
            if rhstable:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   984
                assert rhstable is not None, rhsvar
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   985
                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
   986
                    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
   987
                    rhssql)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   988
                toreplace.append(rhstable)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   989
        self.replace_tables_by_outer_join(join, maintable, *toreplace)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   990
        return ''
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   991
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   992
    def _visit_var_attr_relation(self, relation, rhs_vars):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   993
        """visit an attribute relation with variable(s) in the RHS
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   994
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   995
        attribute variables are used either in the selection or for
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   996
        unification (eg X attr1 A, Y attr2 A). In case of selection,
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   997
        nothing to do here.
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   998
        """
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
   999
        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
  1000
            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
  1001
                # 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
  1002
                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
  1003
            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
  1004
            if principal is not None and principal is not relation:
6940
1172c25655b7 [rql2sql] should not select a neged relation as principal + simplify code for sql generation for attribute relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6929
diff changeset
  1005
                # we have to generate unification expression
1172c25655b7 [rql2sql] should not select a neged relation as principal + simplify code for sql generation for attribute relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6929
diff changeset
  1006
                lhssql = self._inlined_var_sql(relation.children[0].variable,
1172c25655b7 [rql2sql] should not select a neged relation as principal + simplify code for sql generation for attribute relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6929
diff changeset
  1007
                                               relation.r_type)
1172c25655b7 [rql2sql] should not select a neged relation as principal + simplify code for sql generation for attribute relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6929
diff changeset
  1008
                try:
1172c25655b7 [rql2sql] should not select a neged relation as principal + simplify code for sql generation for attribute relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6929
diff changeset
  1009
                    self._state.ignore_varmap = True
1172c25655b7 [rql2sql] should not select a neged relation as principal + simplify code for sql generation for attribute relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6929
diff changeset
  1010
                    return '%s%s' % (lhssql, relation.children[1].accept(self))
1172c25655b7 [rql2sql] should not select a neged relation as principal + simplify code for sql generation for attribute relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6929
diff changeset
  1011
                finally:
1172c25655b7 [rql2sql] should not select a neged relation as principal + simplify code for sql generation for attribute relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6929
diff changeset
  1012
                    self._state.ignore_varmap = False
1172c25655b7 [rql2sql] should not select a neged relation as principal + simplify code for sql generation for attribute relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6929
diff changeset
  1013
        return ''
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
  1014
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
  1015
    def _visit_attribute_relation(self, rel):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1016
        """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
  1017
        lhs, rhs = rel.get_parts()
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1018
        rhssql = rhs.accept(self)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1019
        table = self._var_table(lhs.variable)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1020
        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
  1021
            assert rel.r_type == 'eid'
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1022
            lhssql = lhs.accept(self)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1023
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1024
            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
  1025
                lhssql = self._varmap['%s.%s' % (lhs.name, rel.r_type)]
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1026
            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
  1027
                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
  1028
                if mapkey in self.attr_map:
5013
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
  1029
                    cb, sourcecb = self.attr_map[mapkey]
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
  1030
                    if sourcecb:
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
  1031
                        # callback is a source callback, we can't use this
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
  1032
                        # attribute in restriction
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
  1033
                        raise QueryError("can't use %s (%s) in restriction"
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
  1034
                                         % (mapkey, rel.as_string()))
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
  1035
                    lhssql = cb(self, lhs.variable, rel)
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
  1036
                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
  1037
                    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
  1038
                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
  1039
                    lhssql = '%s.%s%s' % (table, SQL_PREFIX, rel.r_type)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1040
        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
  1041
            if rel._q_needcast == 'TODAY':
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1042
                sql = 'DATE(%s)%s' % (lhssql, rhssql)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1043
            # 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
  1044
            #elif rel._q_needcast == 'NOW':
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1045
            #    sql = 'TIMESTAMP(%s)%s' % (lhssql, rhssql)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1046
            else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1047
                sql = '%s%s' % (lhssql, rhssql)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1048
        except AttributeError:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1049
            sql = '%s%s' % (lhssql, rhssql)
5004
4cc020ee70e2 le patch rql26 a été importé
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4845
diff changeset
  1050
        if lhs.variable.stinfo.get('optrelations'):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1051
            self.add_outer_join_condition(lhs.variable, table, sql)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1052
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1053
            return sql
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1054
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
  1055
    def _visit_has_text_relation(self, rel):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1056
        """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
  1057
        lhs, rhs = rel.get_parts()
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1058
        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
  1059
        alias = self._fti_table(rel)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1060
        jointo = lhs.accept(self)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1061
        restriction = ''
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1062
        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
  1063
        me_is_principal = lhsvar.stinfo.get('principal') is rel
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1064
        if me_is_principal:
5004
4cc020ee70e2 le patch rql26 a été importé
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4845
diff changeset
  1065
            if lhsvar.stinfo['typerel'] is None:
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1066
                # the variable is using the fti table, no join needed
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1067
                jointo = None
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1068
            elif not lhsvar.name in self._varmap:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1069
                # join on entities instead of etype's table to get result for
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1070
                # 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
  1071
                ealias = lhsvar._q_sqltable = '_' + lhsvar.name
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1072
                jointo = lhsvar._q_sql = '%s.eid' % ealias
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1073
                self.add_table('entities AS %s' % ealias, ealias)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1074
                if not lhsvar._q_invariant or len(lhsvar.stinfo['possibletypes']) == 1:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1075
                    restriction = " AND %s.type='%s'" % (ealias, self._state.solution[lhs.name])
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1076
                else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1077
                    etypes = ','.join("'%s'" % etype for etype in lhsvar.stinfo['possibletypes'])
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1078
                    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
  1079
        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
  1080
            self._state.done.add(rel.parent)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1081
            not_ = True
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
            not_ = False
5768
1e73a466aa69 [fti] support for fti ranking: has_text query results sorted by relevance, and provides a way to control weight per entity / entity's attribute
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
  1084
        query = const.eval(self._args)
1e73a466aa69 [fti] support for fti ranking: has_text query results sorted by relevance, and provides a way to control weight per entity / entity's attribute
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
  1085
        return self.dbhelper.fti_restriction_sql(alias, query,
5582
3e133b29a1a4 [rql2sql] follow rql 0.26.1 changes: NOT nodes normalization, allowing simplification of sql generation, and fix #XXX
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5426
diff changeset
  1086
                                                 jointo, not_) + restriction
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
  1087
3815
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
  1088
    def visit_comparison(self, cmp):
3787
82bb2c7f083b C - fix typo. make `lgp check` happy.
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents: 3762
diff changeset
  1089
        """generate SQL for a comparison"""
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1090
        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
  1091
            # XXX occurs ?
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1092
            lhs, rhs = cmp.children
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1093
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1094
            lhs = None
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1095
            rhs = cmp.children[0]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1096
        operator = cmp.operator
5702
9fb240cf0f61 [rql] more update to 0.26.2 api
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5662
diff changeset
  1097
        if operator in ('LIKE', 'ILIKE'):
5010
b2c5aee8ca3f [cleanup] rename dbms_helper to dbhelper for consistency
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4845
diff changeset
  1098
            if operator == 'ILIKE' and not self.dbhelper.ilike_support:
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1099
                operator = ' LIKE '
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1100
            else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1101
                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
  1102
        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
  1103
              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
  1104
            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
  1105
                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
  1106
            return '%s IS NULL' % lhs.accept(self)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1107
        elif isinstance(rhs, Function) and rhs.name == 'IN':
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1108
            assert operator == '='
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1109
            operator = ' '
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1110
        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
  1111
            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
  1112
        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
  1113
3815
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
  1114
    def visit_mathexpression(self, mexpr):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1115
        """generate SQL for a mathematic expression"""
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1116
        lhs, rhs = mexpr.get_parts()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1117
        # check for string concatenation
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1118
        operator = mexpr.operator
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1119
        try:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1120
            if mexpr.operator == '+' and mexpr.get_type(self._state.solution, self._args) == 'String':
5793
1faff41593df [rql2sql] use new dbhelper.sql_concat_string function to deal with sql string concatenation. This together with lgdb 1.0.4 fixes #1055133: SQL Server support : string concatenation issue
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5782
diff changeset
  1121
                return '(%s)' % self.dbhelper.sql_concat_string(lhs.accept(self),
1faff41593df [rql2sql] use new dbhelper.sql_concat_string function to deal with sql string concatenation. This together with lgdb 1.0.4 fixes #1055133: SQL Server support : string concatenation issue
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5782
diff changeset
  1122
                                                                rhs.accept(self))
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1123
        except CoercionError:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1124
            pass
3815
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
  1125
        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
  1126
3815
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
  1127
    def visit_function(self, func):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1128
        """generate SQL name for a function"""
5768
1e73a466aa69 [fti] support for fti ranking: has_text query results sorted by relevance, and provides a way to control weight per entity / entity's attribute
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
  1129
        if func.name == 'FTIRANK':
1e73a466aa69 [fti] support for fti ranking: has_text query results sorted by relevance, and provides a way to control weight per entity / entity's attribute
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
  1130
            try:
1e73a466aa69 [fti] support for fti ranking: has_text query results sorted by relevance, and provides a way to control weight per entity / entity's attribute
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
  1131
                rel = iter(func.children[0].variable.stinfo['ftirels']).next()
1e73a466aa69 [fti] support for fti ranking: has_text query results sorted by relevance, and provides a way to control weight per entity / entity's attribute
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
  1132
            except KeyError:
1e73a466aa69 [fti] support for fti ranking: has_text query results sorted by relevance, and provides a way to control weight per entity / entity's attribute
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
  1133
                raise BadRQLQuery("can't use FTIRANK on variable not used in an"
1e73a466aa69 [fti] support for fti ranking: has_text query results sorted by relevance, and provides a way to control weight per entity / entity's attribute
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
  1134
                                  " 'has_text' relation (eg full-text search)")
1e73a466aa69 [fti] support for fti ranking: has_text query results sorted by relevance, and provides a way to control weight per entity / entity's attribute
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
  1135
            const = rel.get_parts()[1].children[0]
1e73a466aa69 [fti] support for fti ranking: has_text query results sorted by relevance, and provides a way to control weight per entity / entity's attribute
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
  1136
            return self.dbhelper.fti_rank_order(self._fti_table(rel),
1e73a466aa69 [fti] support for fti ranking: has_text query results sorted by relevance, and provides a way to control weight per entity / entity's attribute
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5706
diff changeset
  1137
                                                const.eval(self._args))
5013
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
  1138
        args = [c.accept(self) for c in func.children]
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
  1139
        if func in self._state.source_cb_funcs:
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
  1140
            # function executed as a callback on the source
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
  1141
            assert len(args) == 1
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
  1142
            return args[0]
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
  1143
        # func_as_sql will check function is supported by the backend
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
  1144
        return self.dbhelper.func_as_sql(func.name, args)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1145
3815
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
  1146
    def visit_constant(self, constant):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1147
        """generate SQL name for a constant"""
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1148
        if constant.type is None:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1149
            return 'NULL'
6286
4d10e9895679 cleanup
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6131
diff changeset
  1150
        value = constant.value
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1151
        if constant.type == 'Int' and  isinstance(constant.parent, SortTerm):
6286
4d10e9895679 cleanup
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6131
diff changeset
  1152
            return value
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1153
        if constant.type in ('Date', 'Datetime'):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1154
            rel = constant.relation()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1155
            if rel is not None:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1156
                rel._q_needcast = value
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1157
            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
  1158
        if constant.type == 'Boolean':
5010
b2c5aee8ca3f [cleanup] rename dbms_helper to dbhelper for consistency
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4845
diff changeset
  1159
            value = self.dbhelper.boolean_value(value)
4124
8f2f5f0a89e7 fix backout
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4121
diff changeset
  1160
        if constant.type == 'Substitute':
6131
087c5a168010 [ms] more planning bug fixes
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6050
diff changeset
  1161
            try:
087c5a168010 [ms] more planning bug fixes
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6050
diff changeset
  1162
                # we may found constant from simplified var in varmap
087c5a168010 [ms] more planning bug fixes
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6050
diff changeset
  1163
                return self._mapped_term(constant, '%%(%s)s' % value)[0]
087c5a168010 [ms] more planning bug fixes
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6050
diff changeset
  1164
            except KeyError:
6286
4d10e9895679 cleanup
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6131
diff changeset
  1165
                _id = value
6131
087c5a168010 [ms] more planning bug fixes
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6050
diff changeset
  1166
                if isinstance(_id, unicode):
087c5a168010 [ms] more planning bug fixes
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6050
diff changeset
  1167
                    _id = _id.encode()
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1168
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1169
            _id = str(id(constant)).replace('-', '', 1)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1170
            self._query_attrs[_id] = value
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1171
        return '%%(%s)s' % _id
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
  1172
3815
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
  1173
    def visit_variableref(self, variableref):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1174
        """get the sql name for a variable reference"""
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1175
        # 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
  1176
        return variableref.variable.accept(self)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1177
3815
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
  1178
    def visit_columnalias(self, colalias):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1179
        """get the sql name for a subquery column alias"""
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1180
        if colalias.name in self._varmap:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1181
            sql = self._varmap[colalias.name]
1122
9f37de24251f fix rql2sq w/ outer join on subquery result
sylvain.thenault@logilab.fr
parents: 438
diff changeset
  1182
            table = sql.split('.', 1)[0]
9f37de24251f fix rql2sq w/ outer join on subquery result
sylvain.thenault@logilab.fr
parents: 438
diff changeset
  1183
            colalias._q_sqltable = table
9f37de24251f fix rql2sq w/ outer join on subquery result
sylvain.thenault@logilab.fr
parents: 438
diff changeset
  1184
            colalias._q_sql = sql
9f37de24251f fix rql2sq w/ outer join on subquery result
sylvain.thenault@logilab.fr
parents: 438
diff changeset
  1185
            self.add_table(table)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1186
            return sql
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1187
        return colalias._q_sql
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
  1188
3815
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
  1189
    def visit_variable(self, variable):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1190
        """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
  1191
        #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
  1192
        if variable.name in self._state.done:
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1193
            if self._in_wrapping_query:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1194
                return 'T1.%s' % self._state.aliases[variable.name]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1195
            return variable._q_sql
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1196
        self._state.done.add(variable.name)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1197
        vtablename = None
3815
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
  1198
        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
  1199
            sql, vtablename = self._var_info(variable)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1200
        elif variable.stinfo['attrvar']:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1201
            # attribute variable (systematically used in rhs of final
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1202
            # 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
  1203
            sql = self._linked_var_sql(variable)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1204
        elif variable._q_invariant:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1205
            # since variable is invariant, we know we won't found final relation
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1206
            principal = variable.stinfo['principal']
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1207
            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
  1208
                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
  1209
                self.add_table('entities AS %s' % vtablename, vtablename)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1210
                sql = '%s.eid' % vtablename
5004
4cc020ee70e2 le patch rql26 a été importé
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4845
diff changeset
  1211
                if variable.stinfo['typerel'] is not None:
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1212
                    # add additional restriction on entities.type column
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1213
                    pts = variable.stinfo['possibletypes']
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1214
                    if len(pts) == 1:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1215
                        etype = iter(variable.stinfo['possibletypes']).next()
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1216
                        restr = "%s.type='%s'" % (vtablename, etype)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1217
                    else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1218
                        etypes = ','.join("'%s'" % et for et in pts)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1219
                        restr = '%s.type IN (%s)' % (vtablename, etypes)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1220
                    self._state.add_restriction(restr)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1221
            elif principal.r_type == 'has_text':
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1222
                sql = '%s.%s' % (self._fti_table(principal),
5010
b2c5aee8ca3f [cleanup] rename dbms_helper to dbhelper for consistency
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4845
diff changeset
  1223
                                 self.dbhelper.fti_uid_attr)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1224
            elif principal in variable.stinfo['rhsrelations']:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1225
                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
  1226
                    sql = self._linked_var_sql(variable)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1227
                else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1228
                    sql = '%s.eid_to' % self._relation_table(principal)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1229
            else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1230
                sql = '%s.eid_from' % self._relation_table(principal)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1231
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1232
            # standard variable: get table name according to etype and use .eid
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1233
            # attribute
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1234
            sql, vtablename = self._var_info(variable)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1235
        variable._q_sqltable = vtablename
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1236
        variable._q_sql = sql
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1237
        return sql
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1238
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1239
    # various utilities #######################################################
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1240
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1241
    def _extra_join_sql(self, relation, sql, var):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1242
        # if rhs var is invariant, and this relation is not its principal,
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1243
        # generate extra join
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1244
        try:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1245
            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
  1246
                op = relation.operator()
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
  1247
                if op == '=':
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
  1248
                    # 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
  1249
                    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
  1250
                    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
  1251
                else:
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
  1252
                    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
  1253
                return '%s%s%s' % tuple(args)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1254
        except KeyError:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1255
            # no principal defined, relation is necessarily the principal and
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1256
            # so nothing to return here
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1257
            pass
6003
5fbc1c4c13ff [rql2sql] fix bug with NOT of inlined relation: NULL values are not properly handled and hence some rows won't be returned while the should
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5901
diff changeset
  1258
        return None
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
  1259
5593
f6c55bec9326 [rql2sql] properly compute scope for variable from temporary tables
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5582
diff changeset
  1260
    def _temp_table_scope(self, select, table):
f6c55bec9326 [rql2sql] properly compute scope for variable from temporary tables
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5582
diff changeset
  1261
        scope = 9999
f6c55bec9326 [rql2sql] properly compute scope for variable from temporary tables
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5582
diff changeset
  1262
        for var, sql in self._varmap.iteritems():
5635
56784e46509f [rql2sql] fix scope computation of variable from temporary table
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5630
diff changeset
  1263
            # skip "attribute variable" in varmap (such 'T.login')
56784e46509f [rql2sql] fix scope computation of variable from temporary table
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5630
diff changeset
  1264
            if not '.' in var and table == sql.split('.', 1)[0]:
5593
f6c55bec9326 [rql2sql] properly compute scope for variable from temporary tables
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5582
diff changeset
  1265
                try:
f6c55bec9326 [rql2sql] properly compute scope for variable from temporary tables
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5582
diff changeset
  1266
                    scope = min(scope, self._state.scopes[select.defined_vars[var].scope])
f6c55bec9326 [rql2sql] properly compute scope for variable from temporary tables
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5582
diff changeset
  1267
                except KeyError:
f6c55bec9326 [rql2sql] properly compute scope for variable from temporary tables
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5582
diff changeset
  1268
                    scope = 0 # XXX
f6c55bec9326 [rql2sql] properly compute scope for variable from temporary tables
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5582
diff changeset
  1269
                if scope == 0:
5635
56784e46509f [rql2sql] fix scope computation of variable from temporary table
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5630
diff changeset
  1270
                    break
56784e46509f [rql2sql] fix scope computation of variable from temporary table
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5630
diff changeset
  1271
        return scope
5593
f6c55bec9326 [rql2sql] properly compute scope for variable from temporary tables
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5582
diff changeset
  1272
6131
087c5a168010 [ms] more planning bug fixes
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6050
diff changeset
  1273
    def _mapped_term(self, term, key):
087c5a168010 [ms] more planning bug fixes
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6050
diff changeset
  1274
        """return sql and table alias to the `term`, mapped as `key` or raise
087c5a168010 [ms] more planning bug fixes
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6050
diff changeset
  1275
        KeyError when the key is not found in the varmap
087c5a168010 [ms] more planning bug fixes
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6050
diff changeset
  1276
        """
087c5a168010 [ms] more planning bug fixes
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6050
diff changeset
  1277
        sql = self._varmap[key]
087c5a168010 [ms] more planning bug fixes
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6050
diff changeset
  1278
        tablealias = sql.split('.', 1)[0]
087c5a168010 [ms] more planning bug fixes
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6050
diff changeset
  1279
        scope = self._temp_table_scope(term.stmt, tablealias)
087c5a168010 [ms] more planning bug fixes
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6050
diff changeset
  1280
        self.add_table(tablealias, scope=scope)
087c5a168010 [ms] more planning bug fixes
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6050
diff changeset
  1281
        return sql, tablealias
087c5a168010 [ms] more planning bug fixes
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6050
diff changeset
  1282
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1283
    def _var_info(self, var):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1284
        try:
6131
087c5a168010 [ms] more planning bug fixes
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6050
diff changeset
  1285
            return self._mapped_term(var, var.name)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1286
        except KeyError:
5593
f6c55bec9326 [rql2sql] properly compute scope for variable from temporary tables
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5582
diff changeset
  1287
            scope = self._state.scopes[var.scope]
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1288
            etype = self._state.solution[var.name]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1289
            # 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
  1290
            if self.schema.eschema(etype).final:
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1291
                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
  1292
            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
  1293
            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
  1294
            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
  1295
                           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
  1296
        return sql, tablealias
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
  1297
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1298
    def _inlined_var_sql(self, var, rtype):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1299
        try:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1300
            sql = self._varmap['%s.%s' % (var.name, rtype)]
5582
3e133b29a1a4 [rql2sql] follow rql 0.26.1 changes: NOT nodes normalization, allowing simplification of sql generation, and fix #XXX
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5426
diff changeset
  1301
            scope = self._state.scopes[var.scope]
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1302
            self.add_table(sql.split('.', 1)[0], scope=scope)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1303
        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
  1304
            sql = '%s.%s%s' % (self._var_table(var), SQL_PREFIX, rtype)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1305
            #self._state.done.add(var.name)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1306
        return sql
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
  1307
3815
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
  1308
    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
  1309
        if not self._state.ignore_varmap:
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1310
            try:
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
  1311
                return self._varmap[variable.name]
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1312
            except KeyError:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1313
                pass
3815
50b87f759b5d test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 3787
diff changeset
  1314
        rel = (variable.stinfo.get('principal') or
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1315
               iter(variable.stinfo['rhsrelations']).next())
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1316
        linkedvar = rel.children[0].variable
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1317
        if rel.r_type == 'eid':
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1318
            return linkedvar.accept(self)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1319
        if isinstance(linkedvar, ColumnAlias):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1320
            raise BadRQLQuery('variable %s should be selected by the subquery'
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1321
                              % variable.name)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1322
        try:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1323
            sql = self._varmap['%s.%s' % (linkedvar.name, rel.r_type)]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1324
        except KeyError:
5013
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
  1325
            mapkey = '%s.%s' % (self._state.solution[linkedvar.name], rel.r_type)
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
  1326
            if mapkey in self.attr_map:
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
  1327
                cb, sourcecb = self.attr_map[mapkey]
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
  1328
                if not sourcecb:
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
  1329
                    return cb(self, linkedvar, rel)
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
  1330
                # attribute mapped at the source level (bfss for instance)
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
  1331
                stmt = rel.stmt
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
  1332
                for selectidx, vref in iter_mapped_var_sels(stmt, variable):
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
  1333
                    stack = [cb]
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
  1334
                    update_source_cb_stack(self._state, stmt, vref, stack)
ad91f93bbb93 [source storage] refactor source sql generation and results handling to allow repository side callbacks
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5010
diff changeset
  1335
                    self._state._needs_source_cb[selectidx] = stack
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
  1336
            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
  1337
            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
  1338
        return sql
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1339
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1340
    # tables handling #########################################################
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1341
6493
019ab5eb37ea [rql2sql] properly compute has_text relation table scope
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6491
diff changeset
  1342
    def alias_and_add_table(self, tablename, scope=-1):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1343
        alias = '%s%s' % (tablename, self._state.count)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1344
        self._state.count += 1
6493
019ab5eb37ea [rql2sql] properly compute has_text relation table scope
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6491
diff changeset
  1345
        self.add_table('%s AS %s' % (tablename, alias), alias, scope)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1346
        return alias
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
  1347
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1348
    def add_table(self, table, key=None, scope=-1):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1349
        if key is None:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1350
            key = table
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1351
        if key in self._state.tables:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1352
            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
  1353
        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
  1354
            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
  1355
        self._state.tables[key] = (scope, table)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1356
        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
  1357
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1358
    def replace_tables_by_outer_join(self, substitute, lefttable, *tables):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1359
        for table in tables:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1360
            try:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1361
                scope, alias = self._state.tables[table]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1362
                self._state.actual_tables[scope].remove(alias)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1363
            except ValueError: # huum, not sure about what should be done here
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1364
                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
  1365
                raise Exception(msg % table)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1366
        try:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1367
            tablealias = self._state.outer_tables[lefttable]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1368
            actualtables = self._state.actual_tables[-1]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1369
        except KeyError:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1370
            tablescope, tablealias = self._state.tables[lefttable]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1371
            actualtables = self._state.actual_tables[tablescope]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1372
        outerjoin = '%s %s' % (tablealias, substitute)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1373
        self._update_outer_tables(lefttable, actualtables, tablealias, outerjoin)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1374
        for table in tables:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1375
            self._state.outer_tables[table] = outerjoin
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1376
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1377
    def add_outer_join_condition(self, var, table, condition):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1378
        try:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1379
            tablealias = self._state.outer_tables[table]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1380
            actualtables = self._state.actual_tables[-1]
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1381
        except KeyError:
5004
4cc020ee70e2 le patch rql26 a été importé
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4845
diff changeset
  1382
            for rel in var.stinfo.get('optrelations'):
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1383
                self.visit_relation(rel)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1384
            assert self._state.outer_tables
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1385
            self.add_outer_join_condition(var, table, condition)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1386
            return
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1387
        before, after = tablealias.split(' AS %s ' % table, 1)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1388
        beforep, afterp = after.split(')', 1)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1389
        outerjoin = '%s AS %s %s AND %s) %s' % (before, table, beforep,
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1390
                                                condition, afterp)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1391
        self._update_outer_tables(table, actualtables, tablealias, outerjoin)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1392
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1393
    def _update_outer_tables(self, table, actualtables, oldalias, newalias):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1394
        actualtables.remove(oldalias)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1395
        actualtables.append(newalias)
4286
6801093af29c fix bug: the 'table' argument is overwritten by the loop variable, causing latter crash due to this missing information
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 4212
diff changeset
  1396
        self._state.outer_tables[table] = newalias
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1397
        # some tables which have already been used as outer table and replaced
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1398
        # by <oldalias> may not be reused here, though their associated value
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1399
        # in the outer_tables dict has to be updated as well
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1400
        for table, outerexpr in self._state.outer_tables.iteritems():
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1401
            if outerexpr == oldalias:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1402
                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
  1403
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1404
    def _var_table(self, var):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1405
        var.accept(self)#.visit_variable(var)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1406
        return var._q_sqltable
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1407
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1408
    def _relation_table(self, relation):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1409
        """return the table alias used by the given relation"""
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1410
        if relation in self._state.done:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1411
            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
  1412
        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
  1413
        rid = 'rel_%s%s' % (relation.r_type, self._state.count)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1414
        # 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
  1415
        # 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
  1416
        # scope
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1417
        for varref in relation.iget_nodes(VariableRef):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1418
            var = varref.variable
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1419
            if isinstance(var, ColumnAlias):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1420
                scope = 0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1421
                break
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1422
            # XXX may have a principal without being invariant for this generation,
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1423
            #     not sure this is a pb or not
5582
3e133b29a1a4 [rql2sql] follow rql 0.26.1 changes: NOT nodes normalization, allowing simplification of sql generation, and fix #XXX
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 5426
diff changeset
  1424
            if var.stinfo.get('principal') is relation and var.scope is var.stmt:
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1425
                scope = 0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1426
                break
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1427
        else:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1428
            scope = -1
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1429
        self._state.count += 1
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1430
        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
  1431
        relation._q_sqltable = rid
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1432
        self._state.done.add(relation)
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1433
        return rid
1802
d628defebc17 delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents: 1522
diff changeset
  1434
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1435
    def _fti_table(self, relation):
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1436
        if relation in self._state.done:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1437
            try:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1438
                return relation._q_sqltable
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1439
            except AttributeError:
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1440
                pass
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1441
        self._state.done.add(relation)
6493
019ab5eb37ea [rql2sql] properly compute has_text relation table scope
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6491
diff changeset
  1442
        scope = self._state.scopes[relation.scope]
019ab5eb37ea [rql2sql] properly compute has_text relation table scope
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents: 6491
diff changeset
  1443
        alias = self.alias_and_add_table(self.dbhelper.fti_table, scope=scope)
0
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1444
        relation._q_sqltable = alias
b97547f5f1fa Showtime !
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
diff changeset
  1445
        return alias