author | Sylvain Thénault <sylvain.thenault@logilab.fr> |
Thu, 08 Dec 2011 14:32:57 +0100 | |
changeset 8119 | 29cdde6bb9ef |
parent 7902 | 2ada3052e626 |
child 8238 | 087bb529035c |
permissions | -rw-r--r-- |
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 | 18 |
"""RQL to SQL generator for native sources. |
19 |
||
20 |
||
21 |
SQL queries optimization |
|
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 | 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 | 26 |
which allow us to do :: |
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 | 31 |
|
32 |
||
33 |
2. Any X WHERE X nonfinal1 Y, Y nonfinal2 Z |
|
34 |
||
35 |
-> direct join between nonfinal1 and nonfinal2, whatever X,Y, Z (unless |
|
36 |
inlined...) |
|
1802
d628defebc17
delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
1522
diff
changeset
|
37 |
|
0 | 38 |
NOT IMPLEMENTED (and quite hard to implement) |
39 |
||
40 |
Potential optimization information is collected by the querier, sql generation |
|
41 |
is done according to this information |
|
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 | 49 |
|
50 |
__docformat__ = "restructuredtext en" |
|
51 |
||
52 |
import threading |
|
7166
dde161937d3e
[time zone] support for TZDatetime and TZTime data type
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
53 |
from datetime import datetime, time |
0 | 54 |
|
7166
dde161937d3e
[time zone] support for TZDatetime and TZTime data type
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
55 |
from logilab.common.date import utcdatetime, utctime |
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
|
56 |
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
|
57 |
|
0 | 58 |
from rql import BadRQLQuery, CoercionError |
7672
f31f9882c90f
[rql2sql] closes #1832859: fake HAVING terms w/ EXISTS terms
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7642
diff
changeset
|
59 |
from rql.utils import common_parent |
0 | 60 |
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
|
61 |
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
|
62 |
Not, Comparison, ColumnAlias, Relation, SubQuery, Exists) |
0 | 63 |
|
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
|
64 |
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
|
65 |
from cubicweb.server.sqlutils import SQL_PREFIX |
0 | 66 |
from cubicweb.server.utils import cleanup_solutions |
67 |
||
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
|
68 |
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
|
69 |
|
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
|
70 |
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
|
71 |
|
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 |
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
|
73 |
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
|
74 |
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
|
75 |
|
7421
5115daeeb2ff
[rql2sql] closes #1697862: ORDERBY SUM(C) cancel GROUPBY by wrongly adding C to grouped variables
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7396
diff
changeset
|
76 |
get_func_descr = SQL_FUNCTIONS_REGISTRY.get_function |
5115daeeb2ff
[rql2sql] closes #1697862: ORDERBY SUM(C) cancel GROUPBY by wrongly adding C to grouped variables
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7396
diff
changeset
|
77 |
|
5115daeeb2ff
[rql2sql] closes #1697862: ORDERBY SUM(C) cancel GROUPBY by wrongly adding C to grouped variables
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7396
diff
changeset
|
78 |
LENGTH = get_func_descr('LENGTH') |
5630
40d7c7e180f1
[storage] source's callback has a new prototype, update usage
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
5593
diff
changeset
|
79 |
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
|
80 |
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
|
81 |
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
|
82 |
|
1802
d628defebc17
delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
1522
diff
changeset
|
83 |
def _new_var(select, varname): |
0 | 84 |
newvar = select.get_variable(varname) |
85 |
if not 'relations' in newvar.stinfo: |
|
86 |
# not yet initialized |
|
87 |
newvar.prepare_annotation() |
|
88 |
newvar.stinfo['scope'] = select |
|
89 |
newvar._q_invariant = False |
|
7252
adb1673efa5f
[sql generation] fix crash on outer join + ambiguous inlined relation + attribute selection
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7194
diff
changeset
|
90 |
select.selection.append(VariableRef(newvar)) |
0 | 91 |
return newvar |
92 |
||
93 |
def _fill_to_wrap_rel(var, newselect, towrap, schema): |
|
94 |
for rel in var.stinfo['relations'] - var.stinfo['rhsrelations']: |
|
95 |
rschema = schema.rschema(rel.r_type) |
|
96 |
if rschema.inlined: |
|
97 |
towrap.add( (var, rel) ) |
|
98 |
for vref in rel.children[1].iget_nodes(VariableRef): |
|
99 |
newivar = _new_var(newselect, vref.name) |
|
100 |
_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
|
101 |
elif rschema.final: |
0 | 102 |
towrap.add( (var, rel) ) |
7252
adb1673efa5f
[sql generation] fix crash on outer join + ambiguous inlined relation + attribute selection
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7194
diff
changeset
|
103 |
for vref in rel.children[1].iget_nodes(VariableRef): |
adb1673efa5f
[sql generation] fix crash on outer join + ambiguous inlined relation + attribute selection
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7194
diff
changeset
|
104 |
newivar = _new_var(newselect, vref.name) |
adb1673efa5f
[sql generation] fix crash on outer join + ambiguous inlined relation + attribute selection
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7194
diff
changeset
|
105 |
newivar.stinfo['attrvar'] = (var, rel.r_type) |
1802
d628defebc17
delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
1522
diff
changeset
|
106 |
|
0 | 107 |
def rewrite_unstable_outer_join(select, solutions, unstable, schema): |
108 |
"""if some optional variables are unstable, they should be selected in a |
|
109 |
subquery. This function check this and rewrite the rql syntax tree if |
|
110 |
necessary (in place). Return a boolean telling if the tree has been modified |
|
111 |
""" |
|
112 |
modified = False |
|
113 |
for varname in tuple(unstable): |
|
114 |
var = select.defined_vars[varname] |
|
5004
4cc020ee70e2
le patch rql26 a été importé
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4845
diff
changeset
|
115 |
if not var.stinfo.get('optrelations'): |
0 | 116 |
continue |
117 |
unstable.remove(varname) |
|
118 |
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
|
119 |
newselect.need_distinct = False |
0 | 120 |
myunion = Union() |
121 |
myunion.append(newselect) |
|
122 |
# extract aliases / selection |
|
123 |
newvar = _new_var(newselect, var.name) |
|
124 |
newselect.selection = [VariableRef(newvar)] |
|
125 |
towrap_rels = set() |
|
126 |
_fill_to_wrap_rel(var, newselect, towrap_rels, schema) |
|
127 |
# extract relations |
|
128 |
for var, rel in towrap_rels: |
|
129 |
newrel = rel.copy(newselect) |
|
130 |
newselect.add_restriction(newrel) |
|
131 |
select.remove_node(rel) |
|
132 |
var.stinfo['relations'].remove(rel) |
|
133 |
newvar.stinfo['relations'].add(newrel) |
|
134 |
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
|
135 |
newvar.add_optional_relation(newrel) |
0 | 136 |
for vref in newrel.children[1].iget_nodes(VariableRef): |
137 |
var = vref.variable |
|
138 |
var.stinfo['relations'].add(newrel) |
|
139 |
var.stinfo['rhsrelations'].add(newrel) |
|
140 |
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
|
141 |
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
|
142 |
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
|
143 |
# 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
|
144 |
# 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
|
145 |
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
|
146 |
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
|
147 |
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
|
148 |
modified = True |
0 | 149 |
# extract subquery solutions |
3736
07196bda2456
take care to already existant solutions local variable
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
3689
diff
changeset
|
150 |
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
|
151 |
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
|
152 |
newselect.set_possible_types(mysolutions) |
0 | 153 |
# full sub-query |
154 |
aliases = [VariableRef(select.get_variable(avar.name, i)) |
|
155 |
for i, avar in enumerate(newselect.selection)] |
|
156 |
select.add_subquery(SubQuery(aliases, myunion), check=False) |
|
157 |
return modified |
|
158 |
||
159 |
def _new_solutions(rqlst, solutions): |
|
160 |
"""first filter out subqueries variables from solutions""" |
|
161 |
newsolutions = [] |
|
162 |
for origsol in solutions: |
|
163 |
asol = {} |
|
164 |
for vname in rqlst.defined_vars: |
|
165 |
asol[vname] = origsol[vname] |
|
166 |
if not asol in newsolutions: |
|
167 |
newsolutions.append(asol) |
|
168 |
return newsolutions |
|
169 |
||
170 |
def remove_unused_solutions(rqlst, solutions, varmap, schema): |
|
171 |
"""cleanup solutions: remove solutions where invariant variables are taking |
|
172 |
different types |
|
173 |
""" |
|
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 |
newsols = _new_solutions(rqlst, solutions) |
0 | 175 |
existssols = {} |
176 |
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
|
177 |
invariants = {} |
0 | 178 |
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
|
179 |
vtype = newsols[0][vname] |
0 | 180 |
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
|
181 |
# 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
|
182 |
# 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
|
183 |
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
|
184 |
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
|
185 |
elif var.scope is not rqlst: |
0 | 186 |
# move appart variables which are in a EXISTS scope and are variating |
187 |
try: |
|
188 |
thisexistssols, thisexistsvars = existssols[var.scope] |
|
189 |
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
|
190 |
thisexistssols = [newsols[0]] |
0 | 191 |
thisexistsvars = set() |
192 |
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
|
193 |
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
|
194 |
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
|
195 |
thisexistssols.append(newsols.pop(i)) |
0 | 196 |
thisexistsvars.add(vname) |
197 |
else: |
|
198 |
# 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
|
199 |
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
|
200 |
if vtype != newsols[i][vname]: |
0 | 201 |
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
|
202 |
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
|
203 |
# 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
|
204 |
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
|
205 |
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
|
206 |
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
|
207 |
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
|
208 |
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
|
209 |
# 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
|
210 |
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
|
211 |
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
|
212 |
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
|
213 |
for sol in existssols: |
3866
2783c166ad1a
fix potential key error
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
3852
diff
changeset
|
214 |
try: |
2783c166ad1a
fix potential key error
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
3852
diff
changeset
|
215 |
for invvar, vartype in invariants[id(sol)].iteritems(): |
2783c166ad1a
fix potential key error
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
3852
diff
changeset
|
216 |
sol[invvar] = vartype |
2783c166ad1a
fix potential key error
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
3852
diff
changeset
|
217 |
except KeyError: |
2783c166ad1a
fix potential key error
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
3852
diff
changeset
|
218 |
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
|
219 |
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
|
220 |
if rewrite_unstable_outer_join(rqlst, newsols, unstable, schema): |
0 | 221 |
# 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
|
222 |
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
|
223 |
return newsols, existssols, unstable |
0 | 224 |
|
225 |
def relation_info(relation): |
|
226 |
lhs, rhs = relation.get_variable_parts() |
|
227 |
try: |
|
228 |
lhs = lhs.variable |
|
229 |
lhsconst = lhs.stinfo['constnode'] |
|
230 |
except AttributeError: |
|
231 |
lhsconst = lhs |
|
232 |
lhs = None |
|
233 |
except KeyError: |
|
234 |
lhsconst = None # ColumnAlias |
|
235 |
try: |
|
236 |
rhs = rhs.variable |
|
237 |
rhsconst = rhs.stinfo['constnode'] |
|
238 |
except AttributeError: |
|
239 |
rhsconst = rhs |
|
240 |
rhs = None |
|
241 |
except KeyError: |
|
242 |
rhsconst = None # ColumnAlias |
|
243 |
return lhs, lhsconst, rhs, rhsconst |
|
244 |
||
245 |
def switch_relation_field(sql, table=''): |
|
246 |
switchedsql = sql.replace(table + '.eid_from', '__eid_from__') |
|
247 |
switchedsql = switchedsql.replace(table + '.eid_to', |
|
248 |
table + '.eid_from') |
|
249 |
return switchedsql.replace('__eid_from__', table + '.eid_to') |
|
250 |
||
7579
5a610b34d2d2
[rql2sql] more cases fixed where something is wrongly added to GROUPBY, causing unexpected results for the query
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7472
diff
changeset
|
251 |
def sort_term_selection(sorts, rqlst, groups): |
0 | 252 |
# XXX beurk |
253 |
if isinstance(rqlst, list): |
|
254 |
def append(term): |
|
255 |
rqlst.append(term) |
|
7579
5a610b34d2d2
[rql2sql] more cases fixed where something is wrongly added to GROUPBY, causing unexpected results for the query
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7472
diff
changeset
|
256 |
selectionidx = set(str(term) for term in rqlst) |
0 | 257 |
else: |
258 |
def append(term): |
|
259 |
rqlst.selection.append(term.copy(rqlst)) |
|
7579
5a610b34d2d2
[rql2sql] more cases fixed where something is wrongly added to GROUPBY, causing unexpected results for the query
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7472
diff
changeset
|
260 |
selectionidx = set(str(term) for term in rqlst.selection) |
5a610b34d2d2
[rql2sql] more cases fixed where something is wrongly added to GROUPBY, causing unexpected results for the query
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7472
diff
changeset
|
261 |
|
0 | 262 |
for sortterm in sorts: |
263 |
term = sortterm.term |
|
7579
5a610b34d2d2
[rql2sql] more cases fixed where something is wrongly added to GROUPBY, causing unexpected results for the query
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7472
diff
changeset
|
264 |
if not isinstance(term, Constant) and not str(term) in selectionidx: |
5a610b34d2d2
[rql2sql] more cases fixed where something is wrongly added to GROUPBY, causing unexpected results for the query
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7472
diff
changeset
|
265 |
selectionidx.add(str(term)) |
0 | 266 |
append(term) |
267 |
if groups: |
|
268 |
for vref in term.iget_nodes(VariableRef): |
|
269 |
if not vref in groups: |
|
270 |
groups.append(vref) |
|
1802
d628defebc17
delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
1522
diff
changeset
|
271 |
|
7579
5a610b34d2d2
[rql2sql] more cases fixed where something is wrongly added to GROUPBY, causing unexpected results for the query
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7472
diff
changeset
|
272 |
def fix_selection_and_group(rqlst, needwrap, selectsortterms, |
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
|
273 |
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
|
274 |
if selectsortterms and sorts: |
7579
5a610b34d2d2
[rql2sql] more cases fixed where something is wrongly added to GROUPBY, causing unexpected results for the query
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7472
diff
changeset
|
275 |
sort_term_selection(sorts, rqlst, not needwrap and groups) |
5a610b34d2d2
[rql2sql] more cases fixed where something is wrongly added to GROUPBY, causing unexpected results for the query
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7472
diff
changeset
|
276 |
groupvrefs = [vref for term in groups for vref in term.iget_nodes(VariableRef)] |
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
|
277 |
if sorts and groups: |
3754 | 278 |
# 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
|
279 |
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
|
280 |
term = sortterm.term |
7421
5115daeeb2ff
[rql2sql] closes #1697862: ORDERBY SUM(C) cancel GROUPBY by wrongly adding C to grouped variables
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7396
diff
changeset
|
281 |
if not (isinstance(term, Constant) or \ |
5115daeeb2ff
[rql2sql] closes #1697862: ORDERBY SUM(C) cancel GROUPBY by wrongly adding C to grouped variables
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7396
diff
changeset
|
282 |
(isinstance(term, Function) and |
5115daeeb2ff
[rql2sql] closes #1697862: ORDERBY SUM(C) cancel GROUPBY by wrongly adding C to grouped variables
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7396
diff
changeset
|
283 |
get_func_descr(term.name).aggregat)): |
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
|
284 |
for vref in term.iget_nodes(VariableRef): |
7579
5a610b34d2d2
[rql2sql] more cases fixed where something is wrongly added to GROUPBY, causing unexpected results for the query
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7472
diff
changeset
|
285 |
if not vref in groupvrefs: |
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
|
286 |
groups.append(vref) |
7579
5a610b34d2d2
[rql2sql] more cases fixed where something is wrongly added to GROUPBY, causing unexpected results for the query
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7472
diff
changeset
|
287 |
groupvrefs.append(vref) |
5a610b34d2d2
[rql2sql] more cases fixed where something is wrongly added to GROUPBY, causing unexpected results for the query
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7472
diff
changeset
|
288 |
if needwrap and (groups or having): |
5a610b34d2d2
[rql2sql] more cases fixed where something is wrongly added to GROUPBY, causing unexpected results for the query
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7472
diff
changeset
|
289 |
selectedidx = set(vref.name for term in rqlst.selection |
5a610b34d2d2
[rql2sql] more cases fixed where something is wrongly added to GROUPBY, causing unexpected results for the query
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7472
diff
changeset
|
290 |
for vref in term.get_nodes(VariableRef)) |
0 | 291 |
if groups: |
7579
5a610b34d2d2
[rql2sql] more cases fixed where something is wrongly added to GROUPBY, causing unexpected results for the query
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7472
diff
changeset
|
292 |
for vref in groupvrefs: |
5a610b34d2d2
[rql2sql] more cases fixed where something is wrongly added to GROUPBY, causing unexpected results for the query
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7472
diff
changeset
|
293 |
if vref.name not in selectedidx: |
5a610b34d2d2
[rql2sql] more cases fixed where something is wrongly added to GROUPBY, causing unexpected results for the query
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7472
diff
changeset
|
294 |
selectedidx.add(vref.name) |
0 | 295 |
rqlst.selection.append(vref) |
296 |
if having: |
|
297 |
for term in having: |
|
298 |
for vref in term.iget_nodes(VariableRef): |
|
7579
5a610b34d2d2
[rql2sql] more cases fixed where something is wrongly added to GROUPBY, causing unexpected results for the query
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7472
diff
changeset
|
299 |
if vref.name not in selectedidx: |
5a610b34d2d2
[rql2sql] more cases fixed where something is wrongly added to GROUPBY, causing unexpected results for the query
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7472
diff
changeset
|
300 |
selectedidx.add(vref.name) |
0 | 301 |
rqlst.selection.append(vref) |
302 |
||
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
|
303 |
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
|
304 |
# 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
|
305 |
# 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
|
306 |
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
|
307 |
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
|
308 |
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
|
309 |
% 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
|
310 |
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
|
311 |
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
|
312 |
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
|
313 |
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
|
314 |
yield selectidx, vrefs[0] |
0 | 315 |
|
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
|
316 |
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
|
317 |
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
|
318 |
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
|
319 |
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
|
320 |
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
|
321 |
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
|
322 |
raise QueryError() |
7421
5115daeeb2ff
[rql2sql] closes #1697862: ORDERBY SUM(C) cancel GROUPBY by wrongly adding C to grouped variables
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7396
diff
changeset
|
323 |
funcd = get_func_descr(node.name) |
5115daeeb2ff
[rql2sql] closes #1697862: ORDERBY SUM(C) cancel GROUPBY by wrongly adding C to grouped variables
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7396
diff
changeset
|
324 |
if funcd.source_execute is None: |
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
|
325 |
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
|
326 |
% 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
|
327 |
state.source_cb_funcs.add(node) |
7421
5115daeeb2ff
[rql2sql] closes #1697862: ORDERBY SUM(C) cancel GROUPBY by wrongly adding C to grouped variables
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7396
diff
changeset
|
328 |
funcd.update_cb_stack(stack) |
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
|
329 |
|
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
|
330 |
|
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
|
331 |
# IGenerator implementation for RQL->SQL ####################################### |
0 | 332 |
|
333 |
class StateInfo(object): |
|
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
334 |
"""this class stores data accumulated during the RQL syntax tree visit |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
335 |
for later SQL generation. |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
336 |
|
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
337 |
Attributes related to OUTER JOIN handling |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
338 |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
339 |
* `outer_chains`, list of list of strings. Each list represent a tables |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
340 |
that have to be outer joined together. |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
341 |
|
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
342 |
* `outer_tables`, dictionnary used as index of tables used in outer join :: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
343 |
|
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
344 |
'table alias': (outertype, [conditions], [chain]) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
345 |
|
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
346 |
where: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
347 |
|
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
348 |
* `outertype` is one of None, 'LEFT', 'RIGHT', 'FULL' |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
349 |
* `conditions` is a list of join conditions (string) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
350 |
* `chain` is a list of table alias (the *outer chain*) in which the key |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
351 |
alias appears |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
352 |
|
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
353 |
* `outer_pending` is a dictionary containing some conditions that will have |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
354 |
to be added to the outer join when the table will be turned into an |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
355 |
outerjoin :: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
356 |
|
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
357 |
'table alias': [conditions] |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
358 |
""" |
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
|
359 |
def __init__(self, select, existssols, unstablevars): |
0 | 360 |
self.existssols = existssols |
361 |
self.unstablevars = unstablevars |
|
362 |
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
|
363 |
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
|
364 |
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
|
365 |
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
|
366 |
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
|
367 |
self.scope_nodes = [] |
1802
d628defebc17
delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
1522
diff
changeset
|
368 |
|
0 | 369 |
def reset(self, solution): |
370 |
"""reset some visit variables""" |
|
371 |
self.solution = solution |
|
372 |
self.count = 0 |
|
373 |
self.done = set() |
|
374 |
self.tables = self.subtables.copy() |
|
375 |
self.actual_tables = [[]] |
|
376 |
for _, tsql in self.tables.itervalues(): |
|
377 |
self.actual_tables[-1].append(tsql) |
|
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
378 |
self.outer_chains = [] |
0 | 379 |
self.outer_tables = {} |
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
380 |
self.outer_pending = {} |
0 | 381 |
self.duplicate_switches = [] |
382 |
self.aliases = {} |
|
383 |
self.restrictions = [] |
|
384 |
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
|
385 |
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
|
386 |
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
|
387 |
|
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
|
388 |
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
|
389 |
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
|
390 |
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
|
391 |
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
|
392 |
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
|
393 |
|
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
|
394 |
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
|
395 |
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
|
396 |
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
|
397 |
|
0 | 398 |
def add_restriction(self, restr): |
399 |
if restr: |
|
400 |
self.restrictions.append(restr) |
|
1802
d628defebc17
delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
1522
diff
changeset
|
401 |
|
0 | 402 |
def iter_exists_sols(self, exists): |
403 |
if not exists in self.existssols: |
|
404 |
yield 1 |
|
405 |
return |
|
406 |
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
|
407 |
# 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
|
408 |
# 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
|
409 |
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
|
410 |
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
|
411 |
thisexistsvars.add(var.name) |
0 | 412 |
origsol = self.solution |
413 |
origtables = self.tables |
|
414 |
done = self.done |
|
415 |
for thisexistssol in thisexistssols: |
|
416 |
for vname in self.unstablevars: |
|
417 |
if thisexistssol[vname] != origsol[vname] and vname in thisexistsvars: |
|
418 |
break |
|
419 |
else: |
|
420 |
self.tables = origtables.copy() |
|
421 |
self.solution = thisexistssol |
|
422 |
yield 1 |
|
423 |
# cleanup self.done from stuff specific to exists |
|
424 |
for var in thisexistsvars: |
|
425 |
if var in done: |
|
426 |
done.remove(var) |
|
427 |
for rel in exists.iget_nodes(Relation): |
|
428 |
if rel in done: |
|
429 |
done.remove(rel) |
|
430 |
self.solution = origsol |
|
431 |
self.tables = origtables |
|
432 |
||
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
|
433 |
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
|
434 |
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
|
435 |
self.scopes[scope_node] = len(self.actual_tables) |
0 | 436 |
self.actual_tables.append([]) |
437 |
self._restr_stack.append(self.restrictions) |
|
438 |
self.restrictions = [] |
|
439 |
||
440 |
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
|
441 |
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
|
442 |
self.scope_nodes.pop() |
0 | 443 |
restrictions = self.restrictions |
444 |
self.restrictions = self._restr_stack.pop() |
|
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
445 |
scope = len(self.actual_tables) - 1 |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
446 |
# check if we have some outer chain for this scope |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
447 |
matching_chains = [] |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
448 |
for chain in self.outer_chains: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
449 |
for tablealias in chain: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
450 |
if self.tables[tablealias][0] < scope: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
451 |
# chain belongs to outer scope |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
452 |
break |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
453 |
else: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
454 |
# chain match current scope |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
455 |
matching_chains.append(chain) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
456 |
# call to `tables_sql` will pop actual_tables |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
457 |
tables = self.tables_sql(matching_chains) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
458 |
# cleanup outer join related structure for tables in matching chains |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
459 |
for chain in matching_chains: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
460 |
self.outer_chains.remove(chain) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
461 |
for alias in chain: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
462 |
del self.outer_tables[alias] |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
463 |
return restrictions, tables |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
464 |
|
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
465 |
# tables handling ######################################################### |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
466 |
|
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
467 |
def add_table(self, table, key=None, scope=-1): |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
468 |
if key is None: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
469 |
key = table |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
470 |
if key in self.tables: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
471 |
return |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
472 |
if scope < 0: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
473 |
scope = len(self.actual_tables) + scope |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
474 |
self.tables[key] = (scope, table) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
475 |
self.actual_tables[scope].append(table) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
476 |
|
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
477 |
def alias_and_add_table(self, tablename, scope=-1): |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
478 |
alias = '%s%s' % (tablename, self.count) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
479 |
self.count += 1 |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
480 |
self.add_table('%s AS %s' % (tablename, alias), alias, scope) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
481 |
return alias |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
482 |
|
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
483 |
def relation_table(self, relation): |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
484 |
"""return the table alias used by the given relation""" |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
485 |
if relation in self.done: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
486 |
return relation._q_sqltable |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
487 |
rid = 'rel_%s%s' % (relation.r_type, self.count) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
488 |
# relation's table is belonging to the root scope if it is the principal |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
489 |
# table of one of it's variable and if that variable belong's to parent |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
490 |
# scope |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
491 |
for varref in relation.iget_nodes(VariableRef): |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
492 |
var = varref.variable |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
493 |
if isinstance(var, ColumnAlias): |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
494 |
scope = 0 |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
495 |
break |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
496 |
# XXX may have a principal without being invariant for this generation, |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
497 |
# not sure this is a pb or not |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
498 |
if var.stinfo.get('principal') is relation and var.scope is var.stmt: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
499 |
scope = 0 |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
500 |
break |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
501 |
else: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
502 |
scope = -1 |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
503 |
self.count += 1 |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
504 |
self.add_table('%s_relation AS %s' % (relation.r_type, rid), rid, scope=scope) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
505 |
relation._q_sqltable = rid |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
506 |
self.done.add(relation) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
507 |
return rid |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
508 |
|
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
509 |
def fti_table(self, relation, fti_table): |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
510 |
"""return the table alias used by the given has_text relation, |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
511 |
`fti_table` being the table name for the plain text index |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
512 |
""" |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
513 |
if relation in self.done: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
514 |
try: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
515 |
return relation._q_sqltable |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
516 |
except AttributeError: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
517 |
pass |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
518 |
self.done.add(relation) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
519 |
scope = self.scopes[relation.scope] |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
520 |
alias = self.alias_and_add_table(fti_table, scope=scope) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
521 |
relation._q_sqltable = alias |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
522 |
return alias |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
523 |
|
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
524 |
# outer join handling ###################################################### |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
525 |
|
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
526 |
def mark_as_used_in_outer_join(self, tablealias, addpending=True): |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
527 |
"""Mark table of given alias as used in outer join. This must be called |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
528 |
after `outer_tables[tablealias]` has been initialized. |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
529 |
""" |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
530 |
# remove a table from actual_table because it's used in an outer join |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
531 |
# chain |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
532 |
scope, tabledef = self.tables[tablealias] |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
533 |
self.actual_tables[scope].remove(tabledef) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
534 |
# check if there are some pending outer join condition for this table |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
535 |
if addpending: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
536 |
try: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
537 |
pending_conditions = self.outer_pending.pop(tablealias) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
538 |
except KeyError: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
539 |
pass |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
540 |
else: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
541 |
self.outer_tables[tablealias][1].extend(pending_conditions) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
542 |
else: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
543 |
assert not tablealias in self.outer_pending |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
544 |
|
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
545 |
def add_outer_join_condition(self, tablealias, condition): |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
546 |
try: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
547 |
outer, conditions, chain = self.outer_tables[tablealias] |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
548 |
conditions.append(condition) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
549 |
except KeyError: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
550 |
self.outer_pending.setdefault(tablealias, []).append(condition) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
551 |
|
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
552 |
def replace_tables_by_outer_join(self, leftalias, rightalias, |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
553 |
outertype, condition): |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
554 |
"""tell we need <leftalias> <outertype> JOIN <rightalias> ON <condition> |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
555 |
""" |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
556 |
assert leftalias != rightalias, leftalias |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
557 |
outer_tables = self.outer_tables |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
558 |
louter, lconditions, lchain = outer_tables.get(leftalias, |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
559 |
(None, None, None)) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
560 |
router, rconditions, rchain = outer_tables.get(rightalias, |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
561 |
(None, None, None)) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
562 |
if lchain is None and rchain is None: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
563 |
# create a new outer chaine |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
564 |
chain = [leftalias, rightalias] |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
565 |
outer_tables[leftalias] = (None, [], chain) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
566 |
outer_tables[rightalias] = (outertype, [condition], chain) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
567 |
self.outer_chains.append(chain) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
568 |
self.mark_as_used_in_outer_join(leftalias, addpending=False) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
569 |
self.mark_as_used_in_outer_join(rightalias) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
570 |
elif lchain is None: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
571 |
# [A > B > C] + [D > A] -> [D > A > B > C] |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
572 |
if rightalias == rchain[0]: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
573 |
outer_tables[leftalias] = (None, [], rchain) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
574 |
conditions = outer_tables[rightalias][1] + [condition] |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
575 |
outer_tables[rightalias] = (outertype, conditions, rchain) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
576 |
rchain.insert(0, leftalias) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
577 |
else: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
578 |
# [A > B > C] + [D > B] -> [A > B > C < D] |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
579 |
if outertype == 'LEFT': |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
580 |
outertype = 'RIGHT' |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
581 |
outer_tables[leftalias] = (outertype, [condition], rchain) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
582 |
rchain.append(leftalias) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
583 |
self.mark_as_used_in_outer_join(leftalias) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
584 |
elif rchain is None: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
585 |
# [A > B > C] + [B > D] -> [A > B > C > D] |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
586 |
outer_tables[rightalias] = (outertype, [condition], lchain) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
587 |
lchain.append(rightalias) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
588 |
self.mark_as_used_in_outer_join(rightalias) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
589 |
elif lchain is rchain: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
590 |
# already in the same chain, simply check compatibility and append |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
591 |
# the condition if it's ok |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
592 |
lidx = lchain.index(leftalias) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
593 |
ridx = lchain.index(rightalias) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
594 |
if (outertype == 'FULL' and router != 'FULL') \ |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
595 |
or (lidx < ridx and router != 'LEFT') \ |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
596 |
or (ridx < lidx and louter != 'RIGHT'): |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
597 |
raise BadRQLQuery() |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
598 |
# merge conditions |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
599 |
if lidx < ridx: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
600 |
rconditions.append(condition) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
601 |
else: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
602 |
lconditions.append(condition) |
7357
5ad3154a8810
[rql2sql] fix bug avoiding outer join relation to be used as a variable principal. Closes #1659395
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7252
diff
changeset
|
603 |
elif louter is None: |
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
604 |
# merge chains |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
605 |
self.outer_chains.remove(lchain) |
7357
5ad3154a8810
[rql2sql] fix bug avoiding outer join relation to be used as a variable principal. Closes #1659395
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7252
diff
changeset
|
606 |
rchain += lchain |
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
607 |
self.mark_as_used_in_outer_join(leftalias) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
608 |
for alias, (aouter, aconditions, achain) in outer_tables.iteritems(): |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
609 |
if achain is lchain: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
610 |
outer_tables[alias] = (aouter, aconditions, rchain) |
7357
5ad3154a8810
[rql2sql] fix bug avoiding outer join relation to be used as a variable principal. Closes #1659395
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7252
diff
changeset
|
611 |
else: |
5ad3154a8810
[rql2sql] fix bug avoiding outer join relation to be used as a variable principal. Closes #1659395
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7252
diff
changeset
|
612 |
raise BadRQLQuery() |
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
613 |
|
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
614 |
# sql generation helpers ################################################### |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
615 |
|
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
616 |
def tables_sql(self, outer_chains=None): |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
617 |
"""generate SQL for FROM clause""" |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
618 |
# sort for test predictability |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
619 |
tables = sorted(self.actual_tables.pop()) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
620 |
# process outer joins |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
621 |
if outer_chains is None: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
622 |
assert not self.actual_tables, self.actual_tables |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
623 |
assert not self.outer_pending |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
624 |
outer_chains = self.outer_chains |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
625 |
for chain in sorted(outer_chains): |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
626 |
tablealias = chain[0] |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
627 |
outertype, conditions, _ = self.outer_tables[tablealias] |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
628 |
assert _ is chain, (chain, _) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
629 |
assert outertype is None, (chain, self.outer_chains) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
630 |
assert not conditions, (chain, self.outer_chains) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
631 |
assert len(chain) > 1 |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
632 |
tabledef = self.tables[tablealias][1] |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
633 |
outerjoin = [tabledef] |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
634 |
for tablealias in chain[1:]: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
635 |
outertype, conditions, _ = self.outer_tables[tablealias] |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
636 |
assert _ is chain, (chain, self.outer_chains) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
637 |
assert outertype in ('LEFT', 'RIGHT', 'FULL'), ( |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
638 |
tablealias, outertype, conditions) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
639 |
assert isinstance(conditions, (list)), ( |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
640 |
tablealias, outertype, conditions) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
641 |
tabledef = self.tables[tablealias][1] |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
642 |
outerjoin.append('%s OUTER JOIN %s ON (%s)' % ( |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
643 |
outertype, tabledef, ' AND '.join(conditions))) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
644 |
tables.append(' '.join(outerjoin)) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
645 |
return ', '.join(tables) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
646 |
|
1802
d628defebc17
delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
1522
diff
changeset
|
647 |
|
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
|
648 |
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
|
649 |
"""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
|
650 |
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
|
651 |
|
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
|
652 |
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
|
653 |
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
|
654 |
""" |
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
|
655 |
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
|
656 |
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
|
657 |
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
|
658 |
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
|
659 |
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
|
660 |
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
|
661 |
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
|
662 |
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
|
663 |
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
|
664 |
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
|
665 |
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
|
666 |
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
|
667 |
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
|
668 |
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
|
669 |
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
|
670 |
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
|
671 |
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
|
672 |
# tocheck hold a set of comparison not implying an aggregat function |
7672
f31f9882c90f
[rql2sql] closes #1832859: fake HAVING terms w/ EXISTS terms
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7642
diff
changeset
|
673 |
# put them in fakehaving if they don't share an Or node as ancestor |
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
|
674 |
# 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
|
675 |
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
|
676 |
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
|
677 |
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
|
678 |
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
|
679 |
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
|
680 |
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
|
681 |
break |
6929
e9464d975908
[rql2sql] fix bug with NOT in "fake" having terms
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
6493
diff
changeset
|
682 |
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
|
683 |
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
|
684 |
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
|
685 |
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
|
686 |
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
|
687 |
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
|
688 |
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
|
689 |
return fakehaving |
1802
d628defebc17
delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
1522
diff
changeset
|
690 |
|
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
691 |
|
0 | 692 |
class SQLGenerator(object): |
693 |
""" |
|
694 |
generation of SQL from the fully expanded RQL syntax tree |
|
695 |
SQL is designed to be used with a CubicWeb SQL schema |
|
696 |
||
697 |
Groups and sort are not handled here since they should not be handled at |
|
698 |
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
|
699 |
|
0 | 700 |
we should not have errors here ! |
701 |
||
702 |
WARNING: a CubicWebSQLGenerator instance is not thread safe, but generate is |
|
703 |
protected by a lock |
|
704 |
""" |
|
1802
d628defebc17
delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
1522
diff
changeset
|
705 |
|
5010
b2c5aee8ca3f
[cleanup] rename dbms_helper to dbhelper for consistency
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4845
diff
changeset
|
706 |
def __init__(self, schema, dbhelper, attrmap=None): |
0 | 707 |
self.schema = schema |
5010
b2c5aee8ca3f
[cleanup] rename dbms_helper to dbhelper for consistency
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4845
diff
changeset
|
708 |
self.dbhelper = dbhelper |
b2c5aee8ca3f
[cleanup] rename dbms_helper to dbhelper for consistency
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4845
diff
changeset
|
709 |
self.dbencoding = dbhelper.dbencoding |
b2c5aee8ca3f
[cleanup] rename dbms_helper to dbhelper for consistency
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4845
diff
changeset
|
710 |
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
|
711 |
'TODAY': self.dbhelper.sql_current_date, |
0 | 712 |
} |
5010
b2c5aee8ca3f
[cleanup] rename dbms_helper to dbhelper for consistency
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4845
diff
changeset
|
713 |
if not self.dbhelper.union_parentheses_support: |
0 | 714 |
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
|
715 |
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
|
716 |
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
|
717 |
self.union_sql = self.has_text_need_distinct_union_sql |
0 | 718 |
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
|
719 |
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
|
720 |
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
|
721 |
self.attr_map = attrmap |
1802
d628defebc17
delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
1522
diff
changeset
|
722 |
|
0 | 723 |
def generate(self, union, args=None, varmap=None): |
724 |
"""return SQL queries and a variable dictionnary from a RQL syntax tree |
|
725 |
||
726 |
:partrqls: a list of couple (rqlst, solutions) |
|
727 |
:args: optional dictionary with values of substitutions used in the query |
|
728 |
:varmap: optional dictionary mapping variable name to a special table |
|
729 |
name, in case the query as to fetch data from temporary tables |
|
730 |
||
731 |
return an sql string and a dictionary with substitutions values |
|
732 |
""" |
|
733 |
if args is None: |
|
734 |
args = {} |
|
735 |
if varmap is None: |
|
736 |
varmap = {} |
|
737 |
self._lock.acquire() |
|
738 |
self._args = args |
|
739 |
self._varmap = varmap |
|
740 |
self._query_attrs = {} |
|
741 |
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
|
742 |
# self._not_scope_offset = 0 |
0 | 743 |
try: |
744 |
# union query for each rqlst / solution |
|
745 |
sql = self.union_sql(union) |
|
746 |
# 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
|
747 |
return sql, self._query_attrs, self._state.needs_source_cb |
0 | 748 |
finally: |
749 |
self._lock.release() |
|
750 |
||
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
|
751 |
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
|
752 |
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
|
753 |
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
|
754 |
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
|
755 |
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
|
756 |
|
6491
ee9a10b6620e
pylint option update
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
6297
diff
changeset
|
757 |
def union_sql(self, union, needalias=False): # pylint: disable=E0202 |
0 | 758 |
if len(union.children) == 1: |
759 |
return self.select_sql(union.children[0], needalias) |
|
760 |
sqls = ('(%s)' % self.select_sql(select, needalias) |
|
761 |
for select in union.children) |
|
762 |
return '\nUNION ALL\n'.join(sqls) |
|
763 |
||
764 |
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
|
765 |
# 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
|
766 |
# 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
|
767 |
# 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
|
768 |
# |
0 | 769 |
# see http://www.sqlite.org/cvstrac/tktview?tn=3074 |
770 |
sqls = (self.select_sql(select, needalias) |
|
771 |
for i, select in enumerate(union.children)) |
|
772 |
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
|
773 |
|
0 | 774 |
def select_sql(self, select, needalias=False): |
775 |
"""return SQL queries and a variable dictionnary from a RQL syntax tree |
|
776 |
||
777 |
:select: a selection statement of the syntax tree (`rql.stmts.Select`) |
|
778 |
:solution: a dictionnary containing variables binding. |
|
779 |
A solution's dictionnary has variable's names as key and variable's |
|
780 |
types as values |
|
781 |
:needwrap: boolean telling if the query will be wrapped in an outer |
|
782 |
query (to deal with aggregat and/or grouping) |
|
783 |
""" |
|
784 |
distinct = selectsortterms = select.need_distinct |
|
785 |
sorts = select.orderby |
|
786 |
groups = select.groupby |
|
787 |
having = select.having |
|
7672
f31f9882c90f
[rql2sql] closes #1832859: fake HAVING terms w/ EXISTS terms
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7642
diff
changeset
|
788 |
for restr in extract_fake_having_terms(having): |
f31f9882c90f
[rql2sql] closes #1832859: fake HAVING terms w/ EXISTS terms
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7642
diff
changeset
|
789 |
scope = None |
f31f9882c90f
[rql2sql] closes #1832859: fake HAVING terms w/ EXISTS terms
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7642
diff
changeset
|
790 |
for vref in restr.get_nodes(VariableRef): |
f31f9882c90f
[rql2sql] closes #1832859: fake HAVING terms w/ EXISTS terms
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7642
diff
changeset
|
791 |
vscope = vref.variable.scope |
f31f9882c90f
[rql2sql] closes #1832859: fake HAVING terms w/ EXISTS terms
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7642
diff
changeset
|
792 |
if vscope is select: |
f31f9882c90f
[rql2sql] closes #1832859: fake HAVING terms w/ EXISTS terms
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7642
diff
changeset
|
793 |
continue # ignore select scope, so restriction is added to |
f31f9882c90f
[rql2sql] closes #1832859: fake HAVING terms w/ EXISTS terms
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7642
diff
changeset
|
794 |
# the inner most scope possible |
f31f9882c90f
[rql2sql] closes #1832859: fake HAVING terms w/ EXISTS terms
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7642
diff
changeset
|
795 |
if scope is None: |
f31f9882c90f
[rql2sql] closes #1832859: fake HAVING terms w/ EXISTS terms
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7642
diff
changeset
|
796 |
scope = vscope |
f31f9882c90f
[rql2sql] closes #1832859: fake HAVING terms w/ EXISTS terms
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7642
diff
changeset
|
797 |
elif vscope is not scope: |
f31f9882c90f
[rql2sql] closes #1832859: fake HAVING terms w/ EXISTS terms
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7642
diff
changeset
|
798 |
scope = common_parent(scope, vscope).scope |
f31f9882c90f
[rql2sql] closes #1832859: fake HAVING terms w/ EXISTS terms
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7642
diff
changeset
|
799 |
if scope is None: |
f31f9882c90f
[rql2sql] closes #1832859: fake HAVING terms w/ EXISTS terms
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7642
diff
changeset
|
800 |
scope = select |
f31f9882c90f
[rql2sql] closes #1832859: fake HAVING terms w/ EXISTS terms
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7642
diff
changeset
|
801 |
scope.add_restriction(restr) |
0 | 802 |
# remember selection, it may be changed and have to be restored |
803 |
origselection = select.selection[:] |
|
804 |
# check if the query will have union subquery, if it need sort term |
|
805 |
# selection (union or distinct query) and wrapping (union with groups) |
|
806 |
needwrap = False |
|
807 |
sols = select.solutions |
|
808 |
if len(sols) > 1: |
|
809 |
# remove invariant from solutions |
|
810 |
sols, existssols, unstable = remove_unused_solutions( |
|
811 |
select, sols, self._varmap, self.schema) |
|
812 |
if len(sols) > 1: |
|
813 |
# if there is still more than one solution, a UNION will be |
|
814 |
# generated and so sort terms have to be selected |
|
815 |
selectsortterms = True |
|
816 |
# and if select is using group by or aggregat, a wrapping |
|
817 |
# query will be necessary |
|
818 |
if groups or select.has_aggregat: |
|
819 |
select.select_only_variables() |
|
1802
d628defebc17
delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
1522
diff
changeset
|
820 |
needwrap = True |
0 | 821 |
else: |
822 |
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
|
823 |
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
|
824 |
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
|
825 |
# 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
|
826 |
state.merge_source_cbs(self._state.needs_source_cb) |
0 | 827 |
# treat subqueries |
828 |
self._subqueries_sql(select, state) |
|
829 |
# generate sql for this select node |
|
830 |
if needwrap: |
|
831 |
outerselection = origselection[:] |
|
832 |
if sorts and selectsortterms: |
|
833 |
if distinct: |
|
7579
5a610b34d2d2
[rql2sql] more cases fixed where something is wrongly added to GROUPBY, causing unexpected results for the query
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7472
diff
changeset
|
834 |
sort_term_selection(sorts, outerselection, groups) |
5a610b34d2d2
[rql2sql] more cases fixed where something is wrongly added to GROUPBY, causing unexpected results for the query
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7472
diff
changeset
|
835 |
fix_selection_and_group(select, needwrap, selectsortterms, |
5a610b34d2d2
[rql2sql] more cases fixed where something is wrongly added to GROUPBY, causing unexpected results for the query
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7472
diff
changeset
|
836 |
sorts, groups, having) |
0 | 837 |
if needwrap: |
838 |
fneedwrap = len(outerselection) != len(origselection) |
|
839 |
else: |
|
840 |
fneedwrap = len(select.selection) != len(origselection) |
|
841 |
if fneedwrap: |
|
842 |
needalias = True |
|
843 |
self._in_wrapping_query = False |
|
844 |
self._state = state |
|
845 |
try: |
|
7672
f31f9882c90f
[rql2sql] closes #1832859: fake HAVING terms w/ EXISTS terms
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7642
diff
changeset
|
846 |
sql = self._solutions_sql(select, sols, distinct, |
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
|
847 |
needalias or needwrap) |
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
848 |
# generate groups / having before wrapping query selection to get |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
849 |
# correct column aliases |
0 | 850 |
self._in_wrapping_query = needwrap |
851 |
if groups: |
|
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
852 |
# no constant should be inserted in GROUP BY else the backend |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
853 |
# will interpret it as a positional index in the selection |
0 | 854 |
groups = ','.join(vref.accept(self) for vref in groups |
855 |
if not isinstance(vref, Constant)) |
|
856 |
if having: |
|
857 |
# 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
|
858 |
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
|
859 |
if not isinstance(term, Constant)) |
0 | 860 |
if needwrap: |
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
861 |
sql = '%s FROM (%s) AS T1' % ( |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
862 |
self._selection_sql(outerselection, distinct,needalias), |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
863 |
sql) |
0 | 864 |
if groups: |
865 |
sql += '\nGROUP BY %s' % groups |
|
866 |
if having: |
|
867 |
sql += '\nHAVING %s' % having |
|
868 |
# sort |
|
869 |
if sorts: |
|
7108
bcdf22734059
Abstract the support for ORDER BY and LIMIT/OFFSET SQL generation
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
7042
diff
changeset
|
870 |
sqlsortterms = [] |
7579
5a610b34d2d2
[rql2sql] more cases fixed where something is wrongly added to GROUPBY, causing unexpected results for the query
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7472
diff
changeset
|
871 |
if needwrap: |
5a610b34d2d2
[rql2sql] more cases fixed where something is wrongly added to GROUPBY, causing unexpected results for the query
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7472
diff
changeset
|
872 |
selectidx = [str(term) for term in outerselection] |
5a610b34d2d2
[rql2sql] more cases fixed where something is wrongly added to GROUPBY, causing unexpected results for the query
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7472
diff
changeset
|
873 |
else: |
5a610b34d2d2
[rql2sql] more cases fixed where something is wrongly added to GROUPBY, causing unexpected results for the query
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7472
diff
changeset
|
874 |
selectidx = [str(term) for term in select.selection] |
7108
bcdf22734059
Abstract the support for ORDER BY and LIMIT/OFFSET SQL generation
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
7042
diff
changeset
|
875 |
for sortterm in sorts: |
7579
5a610b34d2d2
[rql2sql] more cases fixed where something is wrongly added to GROUPBY, causing unexpected results for the query
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7472
diff
changeset
|
876 |
_term = self._sortterm_sql(sortterm, selectidx) |
7108
bcdf22734059
Abstract the support for ORDER BY and LIMIT/OFFSET SQL generation
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
7042
diff
changeset
|
877 |
if _term is not None: |
bcdf22734059
Abstract the support for ORDER BY and LIMIT/OFFSET SQL generation
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
7042
diff
changeset
|
878 |
sqlsortterms.append(_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
|
879 |
if sqlsortterms: |
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
880 |
sql = self.dbhelper.sql_add_order_by( |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
881 |
sql, sqlsortterms, origselection, fneedwrap, |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
882 |
select.limit or select.offset) |
7108
bcdf22734059
Abstract the support for ORDER BY and LIMIT/OFFSET SQL generation
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
7042
diff
changeset
|
883 |
else: |
bcdf22734059
Abstract the support for ORDER BY and LIMIT/OFFSET SQL generation
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
7042
diff
changeset
|
884 |
sqlsortterms = None |
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
|
885 |
state.finalize_source_cbs() |
0 | 886 |
finally: |
887 |
select.selection = origselection |
|
888 |
# limit / offset |
|
7108
bcdf22734059
Abstract the support for ORDER BY and LIMIT/OFFSET SQL generation
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
7042
diff
changeset
|
889 |
sql = self.dbhelper.sql_add_limit_offset(sql, |
bcdf22734059
Abstract the support for ORDER BY and LIMIT/OFFSET SQL generation
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
7042
diff
changeset
|
890 |
select.limit, |
bcdf22734059
Abstract the support for ORDER BY and LIMIT/OFFSET SQL generation
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
7042
diff
changeset
|
891 |
select.offset, |
bcdf22734059
Abstract the support for ORDER BY and LIMIT/OFFSET SQL generation
Alexandre Fayolle <alexandre.fayolle@logilab.fr>
parents:
7042
diff
changeset
|
892 |
sqlsortterms) |
0 | 893 |
return sql |
894 |
||
895 |
def _subqueries_sql(self, select, state): |
|
896 |
for i, subquery in enumerate(select.with_): |
|
897 |
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
|
898 |
tablealias = '_T%s' % i # XXX nested subqueries |
0 | 899 |
sql = '(%s) AS %s' % (sql, tablealias) |
900 |
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
|
901 |
latest_state = self._state |
0 | 902 |
for vref in subquery.aliases: |
903 |
alias = vref.variable |
|
904 |
alias._q_sqltable = tablealias |
|
905 |
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
|
906 |
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
|
907 |
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
|
908 |
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
|
909 |
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
|
910 |
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
|
911 |
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
|
912 |
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
|
913 |
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
|
914 |
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
|
915 |
continue |
0 | 916 |
|
7672
f31f9882c90f
[rql2sql] closes #1832859: fake HAVING terms w/ EXISTS terms
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7642
diff
changeset
|
917 |
def _solutions_sql(self, select, solutions, distinct, needalias): |
0 | 918 |
sqls = [] |
919 |
for solution in solutions: |
|
920 |
self._state.reset(solution) |
|
921 |
# visit restriction subtree |
|
922 |
if select.where is not None: |
|
923 |
self._state.add_restriction(select.where.accept(self)) |
|
924 |
sql = [self._selection_sql(select.selection, distinct, needalias)] |
|
925 |
if self._state.restrictions: |
|
926 |
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
|
927 |
self._state.merge_source_cbs(self._state._needs_source_cb) |
0 | 928 |
# add required tables |
929 |
assert len(self._state.actual_tables) == 1, self._state.actual_tables |
|
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
930 |
tables = self._state.tables_sql() |
0 | 931 |
if tables: |
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
932 |
sql.insert(1, 'FROM %s' % tables) |
5010
b2c5aee8ca3f
[cleanup] rename dbms_helper to dbhelper for consistency
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4845
diff
changeset
|
933 |
elif self._state.restrictions and self.dbhelper.needs_from_clause: |
0 | 934 |
sql.insert(1, 'FROM (SELECT 1) AS _T') |
935 |
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
|
936 |
if distinct: |
0 | 937 |
return '\nUNION\n'.join(sqls) |
938 |
else: |
|
939 |
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
|
940 |
|
0 | 941 |
def _selection_sql(self, selected, distinct, needaliasing=False): |
942 |
clause = [] |
|
943 |
for term in selected: |
|
944 |
sql = term.accept(self) |
|
945 |
if needaliasing: |
|
946 |
colalias = 'C%s' % len(clause) |
|
947 |
clause.append('%s AS %s' % (sql, colalias)) |
|
948 |
if isinstance(term, VariableRef): |
|
949 |
self._state.aliases[term.name] = colalias |
|
950 |
else: |
|
951 |
clause.append(sql) |
|
952 |
if distinct: |
|
953 |
return 'SELECT DISTINCT %s' % ', '.join(clause) |
|
954 |
return 'SELECT %s' % ', '.join(clause) |
|
955 |
||
956 |
def _sortterm_sql(self, sortterm, selectidx): |
|
957 |
term = sortterm.term |
|
958 |
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
|
959 |
sqlterm = selectidx.index(str(term)) + 1 |
0 | 960 |
except ValueError: |
961 |
# 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
|
962 |
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
|
963 |
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
|
964 |
return None |
0 | 965 |
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
|
966 |
return str(sqlterm) |
0 | 967 |
else: |
968 |
return '%s DESC' % sqlterm |
|
969 |
||
970 |
def visit_and(self, et): |
|
971 |
"""generate SQL for a AND subtree""" |
|
972 |
res = [] |
|
973 |
for c in et.children: |
|
974 |
part = c.accept(self) |
|
975 |
if part: |
|
976 |
res.append(part) |
|
977 |
return ' AND '.join(res) |
|
978 |
||
979 |
def visit_or(self, ou): |
|
980 |
"""generate SQL for a OR subtree""" |
|
981 |
res = [] |
|
982 |
for c in ou.children: |
|
983 |
part = c.accept(self) |
|
984 |
if part: |
|
985 |
res.append('(%s)' % part) |
|
986 |
if res: |
|
987 |
if len(res) > 1: |
|
988 |
return '(%s)' % ' OR '.join(res) |
|
989 |
return res[0] |
|
990 |
return '' |
|
1802
d628defebc17
delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
1522
diff
changeset
|
991 |
|
0 | 992 |
def visit_not(self, node): |
993 |
csql = node.children[0].accept(self) |
|
994 |
if node in self._state.done or not csql: |
|
995 |
# already processed or no sql generated by children |
|
996 |
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
|
997 |
return 'NOT (%s)' % csql |
0 | 998 |
|
999 |
def visit_exists(self, exists): |
|
1000 |
"""generate SQL name for a exists subquery""" |
|
1001 |
sqls = [] |
|
1002 |
for dummy in self._state.iter_exists_sols(exists): |
|
1003 |
sql = self._visit_exists(exists) |
|
1004 |
if sql: |
|
1005 |
sqls.append(sql) |
|
1006 |
if not sqls: |
|
1007 |
return '' |
|
1008 |
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
|
1009 |
|
0 | 1010 |
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
|
1011 |
self._state.push_scope(exists) |
0 | 1012 |
restriction = exists.children[0].accept(self) |
1013 |
restrictions, tables = self._state.pop_scope() |
|
1014 |
if restriction: |
|
1015 |
restrictions.append(restriction) |
|
1016 |
restriction = ' AND '.join(restrictions) |
|
1017 |
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
|
1018 |
if tables: |
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1019 |
return 'SELECT 1 FROM %s' % tables |
0 | 1020 |
return '' |
1021 |
if not tables: |
|
1022 |
# 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
|
1023 |
sql = 'SELECT 1 WHERE %s' % restriction |
0 | 1024 |
else: |
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1025 |
sql = 'SELECT 1 FROM %s WHERE %s' % (tables, restriction) |
0 | 1026 |
return sql |
1027 |
||
1802
d628defebc17
delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
1522
diff
changeset
|
1028 |
|
0 | 1029 |
def visit_relation(self, relation): |
1030 |
"""generate SQL for a relation""" |
|
1031 |
rtype = relation.r_type |
|
1032 |
# don't care of type constraint statement (i.e. relation_type = 'is') |
|
1033 |
if relation.is_types_restriction(): |
|
1034 |
return '' |
|
1035 |
lhs, rhs = relation.get_parts() |
|
1036 |
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
|
1037 |
if rschema.final: |
0 | 1038 |
if rtype == 'eid' and lhs.variable._q_invariant and \ |
1039 |
lhs.variable.stinfo['constnode']: |
|
1040 |
# special case where this restriction is already generated by |
|
1041 |
# some other relation |
|
1042 |
return '' |
|
1043 |
# attribute relation |
|
1044 |
if rtype == 'has_text': |
|
1045 |
sql = self._visit_has_text_relation(relation) |
|
1046 |
else: |
|
1047 |
rhs_vars = rhs.get_nodes(VariableRef) |
|
1048 |
if rhs_vars: |
|
1049 |
# if variable(s) in the RHS |
|
1050 |
sql = self._visit_var_attr_relation(relation, rhs_vars) |
|
1051 |
else: |
|
1052 |
# no variables in the RHS |
|
1053 |
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
|
1054 |
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
|
1055 |
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
|
1056 |
# 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
|
1057 |
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
|
1058 |
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
|
1059 |
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
|
1060 |
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
|
1061 |
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
|
1062 |
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
|
1063 |
# 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
|
1064 |
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
|
1065 |
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
|
1066 |
# OPTIONAL relation, generate a left|right outer join |
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1067 |
if rtype == 'identity' or rschema.inlined: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1068 |
sql = self._visit_outer_join_inlined_relation(relation, rschema) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1069 |
else: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1070 |
sql = self._visit_outer_join_relation(relation, rschema) |
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
|
1071 |
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
|
1072 |
sql = self._visit_inlined_relation(relation) |
0 | 1073 |
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
|
1074 |
# 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
|
1075 |
sql = self._visit_relation(relation, rschema) |
0 | 1076 |
return sql |
1077 |
||
1078 |
def _visit_inlined_relation(self, relation): |
|
1079 |
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
|
1080 |
# 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
|
1081 |
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
|
1082 |
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
|
1083 |
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
|
1084 |
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
|
1085 |
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
|
1086 |
if isinstance(relation.parent, Not): |
0 | 1087 |
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
|
1088 |
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
|
1089 |
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
|
1090 |
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
|
1091 |
# 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
|
1092 |
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
|
1093 |
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
|
1094 |
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
|
1095 |
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
|
1096 |
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
|
1097 |
not rhsvar.name in self._varmap: |
0 | 1098 |
# if the rhs variable is only linked to this relation, this mean we |
1099 |
# only want the relation to exists, eg NOT NULL in case of inlined |
|
1100 |
# 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
|
1101 |
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
|
1102 |
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
|
1103 |
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
|
1104 |
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
|
1105 |
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
|
1106 |
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
|
1107 |
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
|
1108 |
return '%s AND %s' % (sql, moresql) |
0 | 1109 |
|
1110 |
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
|
1111 |
if termconst or not termvar._q_invariant: |
0 | 1112 |
termsql = termconst and termconst.accept(self) or termvar.accept(self) |
1113 |
yield '%s.%s=%s' % (rid, relfield, termsql) |
|
1114 |
elif termvar._q_invariant: |
|
1115 |
# if the variable is mapped, generate restriction anyway |
|
1116 |
if termvar.name in self._varmap: |
|
1117 |
termsql = termvar.accept(self) |
|
1118 |
yield '%s.%s=%s' % (rid, relfield, termsql) |
|
1119 |
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
|
1120 |
if extrajoin is not None: |
0 | 1121 |
yield extrajoin |
1802
d628defebc17
delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
1522
diff
changeset
|
1122 |
|
0 | 1123 |
def _visit_relation(self, relation, rschema): |
1124 |
"""generate SQL for a relation |
|
1125 |
||
1126 |
implements optimization 1. |
|
1127 |
""" |
|
1128 |
if relation.r_type == 'identity': |
|
1129 |
# special case "X identity Y" |
|
1130 |
lhs, rhs = relation.get_parts() |
|
1131 |
return '%s%s' % (lhs.accept(self), rhs.accept(self)) |
|
1132 |
lhsvar, lhsconst, rhsvar, rhsconst = relation_info(relation) |
|
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1133 |
rid = self._state.relation_table(relation) |
0 | 1134 |
sqls = [] |
1135 |
sqls += self._process_relation_term(relation, rid, lhsvar, lhsconst, 'eid_from') |
|
1136 |
sqls += self._process_relation_term(relation, rid, rhsvar, rhsconst, 'eid_to') |
|
1137 |
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
|
1138 |
if rschema.symmetric: |
0 | 1139 |
sql = '(%s OR %s)' % (sql, switch_relation_field(sql)) |
1140 |
return sql |
|
1141 |
||
1142 |
def _visit_outer_join_relation(self, relation, rschema): |
|
1143 |
""" |
|
1144 |
left outer join syntax (optional=='right'): |
|
1145 |
X relation Y? |
|
1802
d628defebc17
delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
1522
diff
changeset
|
1146 |
|
0 | 1147 |
right outer join syntax (optional=='left'): |
1148 |
X? relation Y |
|
1802
d628defebc17
delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
1522
diff
changeset
|
1149 |
|
0 | 1150 |
full outer join syntaxes (optional=='both'): |
1151 |
X? relation Y? |
|
1152 |
||
1153 |
if relation is inlined: |
|
1154 |
if it's a left outer join: |
|
1155 |
-> X LEFT OUTER JOIN Y ON (X.relation=Y.eid) |
|
1156 |
elif it's a right outer join: |
|
1157 |
-> Y LEFT OUTER JOIN X ON (X.relation=Y.eid) |
|
1158 |
elif it's a full outer join: |
|
1159 |
-> X FULL OUTER JOIN Y ON (X.relation=Y.eid) |
|
1160 |
else: |
|
1161 |
if it's a left outer join: |
|
1162 |
-> X LEFT OUTER JOIN relation ON (relation.eid_from=X.eid) |
|
1163 |
LEFT OUTER JOIN Y ON (relation.eid_to=Y.eid) |
|
1164 |
elif it's a right outer join: |
|
1165 |
-> Y LEFT OUTER JOIN relation ON (relation.eid_to=Y.eid) |
|
1166 |
LEFT OUTER JOIN X ON (relation.eid_from=X.eid) |
|
1167 |
elif it's a full outer join: |
|
1168 |
-> X FULL OUTER JOIN Y ON (X.relation=Y.eid) |
|
1169 |
""" |
|
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1170 |
leftvar, leftconst, rightvar, rightconst = relation_info(relation) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1171 |
assert not (leftconst and rightconst), "doesn't make sense" |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1172 |
if relation.optional == 'left': |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1173 |
leftvar, rightvar = rightvar, leftvar |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1174 |
leftconst, rightconst = rightconst, leftconst |
0 | 1175 |
joinattr, restrattr = 'eid_to', 'eid_from' |
1176 |
else: |
|
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1177 |
joinattr, restrattr = 'eid_from', 'eid_to' |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1178 |
# search table for this variable, to use as left table of the outer join |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1179 |
leftalias = None |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1180 |
if leftvar: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1181 |
# take care, may return None for invariant variable |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1182 |
leftalias = self._var_table(leftvar) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1183 |
if leftalias is None: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1184 |
if leftvar.stinfo['principal'] is not relation: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1185 |
# use variable's principal relation |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1186 |
leftalias = leftvar.stinfo['principal']._q_sqltable |
0 | 1187 |
else: |
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1188 |
# search for relation on which we should join |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1189 |
for orelation in leftvar.stinfo['relations']: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1190 |
if (orelation is not relation and |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1191 |
not self.schema.rschema(orelation.r_type).final): |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1192 |
break |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1193 |
else: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1194 |
for orelation in rightvar.stinfo['relations']: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1195 |
if (orelation is not relation and |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1196 |
not self.schema.rschema(orelation.r_type).final |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1197 |
and orelation.optional): |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1198 |
break |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1199 |
else: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1200 |
# unexpected |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1201 |
assert False, leftvar |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1202 |
leftalias = self._state.relation_table(orelation) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1203 |
# right table of the outer join |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1204 |
rightalias = self._state.relation_table(relation) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1205 |
# compute join condition |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1206 |
if not leftconst or (leftvar and not leftvar._q_invariant): |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1207 |
leftsql = leftvar.accept(self) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1208 |
else: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1209 |
leftsql = leftconst.accept(self) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1210 |
condition = '%s.%s=%s' % (rightalias, joinattr, leftsql) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1211 |
if rightconst: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1212 |
condition += ' AND %s.%s=%s' % (rightalias, restrattr, rightconst.accept(self)) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1213 |
# record outer join |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1214 |
outertype = 'FULL' if relation.optional == 'both' else 'LEFT' |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1215 |
self._state.replace_tables_by_outer_join(leftalias, rightalias, |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1216 |
outertype, condition) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1217 |
# need another join? |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1218 |
if rightconst is None: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1219 |
# we need another outer join for the other side of the relation (e.g. |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1220 |
# for "X relation Y?" in RQL, we treated earlier the (cw_X.eid / |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1221 |
# relation.eid_from) join, now we've to do (relation.eid_to / |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1222 |
# cw_Y.eid) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1223 |
leftalias = rightalias |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1224 |
rightsql = rightvar.accept(self) # accept before using var_table |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1225 |
rightalias = self._var_table(rightvar) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1226 |
if rightalias is None: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1227 |
if rightvar.stinfo['principal'] is not relation: |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1228 |
self._state.replace_tables_by_outer_join( |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1229 |
leftalias, rightvar.stinfo['principal']._q_sqltable, |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1230 |
outertype, '%s.%s=%s' % (leftalias, restrattr, rightvar.accept(self))) |
0 | 1231 |
else: |
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1232 |
self._state.replace_tables_by_outer_join( |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1233 |
leftalias, rightalias, outertype, |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1234 |
'%s.%s=%s' % (leftalias, restrattr, rightvar.accept(self))) |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1235 |
# this relation will hence be expressed in FROM clause, return nothing |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1236 |
# here |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1237 |
return '' |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1238 |
|
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1239 |
|
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1240 |
def _visit_outer_join_inlined_relation(self, relation, rschema): |
7734
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1241 |
lhsvar, lhsconst, rhsvar, rhsconst = relation_info(relation) |
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1242 |
assert not (lhsconst and rhsconst), "doesn't make sense" |
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1243 |
attr = 'eid' if relation.r_type == 'identity' else relation.r_type |
7734
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1244 |
lhsalias = self._var_table(lhsvar) |
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1245 |
rhsalias = rhsvar and self._var_table(rhsvar) |
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1246 |
try: |
7734
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1247 |
lhssql = self._varmap['%s.%s' % (lhsvar.name, attr)] |
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1248 |
except KeyError: |
7734
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1249 |
if lhsalias is None: |
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1250 |
lhssql = lhsconst.accept(self) |
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1251 |
else: |
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1252 |
lhssql = '%s.%s%s' % (lhsalias, SQL_PREFIX, attr) |
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1253 |
condition = '%s=%s' % (lhssql, (rhsconst or rhsvar).accept(self)) |
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1254 |
# this is not a typo, rhs optional variable means lhs outer join and vice-versa |
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1255 |
if relation.optional == 'left': |
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1256 |
lhsvar, rhsvar = rhsvar, lhsvar |
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1257 |
lhsconst, rhsconst = rhsconst, lhsconst |
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1258 |
lhsalias, rhsalias = rhsalias, lhsalias |
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1259 |
outertype = 'LEFT' |
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1260 |
elif relation.optional == 'both': |
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1261 |
outertype = 'FULL' |
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1262 |
else: |
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1263 |
outertype = 'LEFT' |
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1264 |
if rhsalias is None: |
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1265 |
if rhsconst is not None: |
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1266 |
# inlined relation with invariant as rhs |
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1267 |
if relation.r_type != 'identity': |
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1268 |
condition = '(%s OR %s IS NULL)' % (condition, lhssql) |
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1269 |
if not lhsvar.stinfo.get('optrelations'): |
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1270 |
return condition |
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1271 |
self._state.add_outer_join_condition(lhsalias, condition) |
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1272 |
return |
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1273 |
if lhsalias is None: |
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1274 |
if lhsconst is not None and not rhsvar.stinfo.get('optrelations'): |
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1275 |
return condition |
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1276 |
lhsalias = lhsvar._q_sql.split('.', 1)[0] |
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1277 |
if lhsalias == rhsalias: |
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1278 |
self._state.add_outer_join_condition(lhsalias, condition) |
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1279 |
else: |
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1280 |
self._state.replace_tables_by_outer_join( |
59fea81647e5
[rql2sql] closes #1892473: enhance handling of optional inlined relation
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7729
diff
changeset
|
1281 |
lhsalias, rhsalias, outertype, condition) |
0 | 1282 |
return '' |
1283 |
||
1284 |
def _visit_var_attr_relation(self, relation, rhs_vars): |
|
1285 |
"""visit an attribute relation with variable(s) in the RHS |
|
1286 |
||
1287 |
attribute variables are used either in the selection or for |
|
1288 |
unification (eg X attr1 A, Y attr2 A). In case of selection, |
|
1289 |
nothing to do here. |
|
1290 |
""" |
|
7472
9833c09460f1
[rql2sql] fix missing sql restriction w/ attribute restriction where value comes from a subquery
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7421
diff
changeset
|
1291 |
for vref in rhs_vars: |
9833c09460f1
[rql2sql] fix missing sql restriction w/ attribute restriction where value comes from a subquery
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7421
diff
changeset
|
1292 |
var = vref.variable |
2073
173c646981a7
fix missing from close when using a var map
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
1977
diff
changeset
|
1293 |
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
|
1294 |
# ensure table is added |
7472
9833c09460f1
[rql2sql] fix missing sql restriction w/ attribute restriction where value comes from a subquery
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7421
diff
changeset
|
1295 |
self._var_info(var) |
9833c09460f1
[rql2sql] fix missing sql restriction w/ attribute restriction where value comes from a subquery
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7421
diff
changeset
|
1296 |
if isinstance(var, ColumnAlias): |
9833c09460f1
[rql2sql] fix missing sql restriction w/ attribute restriction where value comes from a subquery
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7421
diff
changeset
|
1297 |
# force sql generation whatever the computed principal |
9833c09460f1
[rql2sql] fix missing sql restriction w/ attribute restriction where value comes from a subquery
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7421
diff
changeset
|
1298 |
principal = 1 |
9833c09460f1
[rql2sql] fix missing sql restriction w/ attribute restriction where value comes from a subquery
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7421
diff
changeset
|
1299 |
else: |
9833c09460f1
[rql2sql] fix missing sql restriction w/ attribute restriction where value comes from a subquery
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7421
diff
changeset
|
1300 |
principal = var.stinfo.get('principal') |
3815
50b87f759b5d
test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
3787
diff
changeset
|
1301 |
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
|
1302 |
# 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
|
1303 |
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
|
1304 |
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
|
1305 |
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
|
1306 |
self._state.ignore_varmap = True |
7707
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1307 |
sql = lhssql + relation.children[1].accept(self) |
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
|
1308 |
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
|
1309 |
self._state.ignore_varmap = False |
7707
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1310 |
if relation.optional == 'right': |
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1311 |
leftalias = self._var_table(principal.children[0].variable) |
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1312 |
rightalias = self._var_table(relation.children[0].variable) |
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1313 |
self._state.replace_tables_by_outer_join( |
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1314 |
leftalias, rightalias, 'LEFT', sql) |
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1315 |
return '' |
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1316 |
return sql |
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
|
1317 |
return '' |
1802
d628defebc17
delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
1522
diff
changeset
|
1318 |
|
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
|
1319 |
def _visit_attribute_relation(self, rel): |
0 | 1320 |
"""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
|
1321 |
lhs, rhs = rel.get_parts() |
0 | 1322 |
rhssql = rhs.accept(self) |
1323 |
table = self._var_table(lhs.variable) |
|
1324 |
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
|
1325 |
assert rel.r_type == 'eid' |
0 | 1326 |
lhssql = lhs.accept(self) |
1327 |
else: |
|
1328 |
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
|
1329 |
lhssql = self._varmap['%s.%s' % (lhs.name, rel.r_type)] |
0 | 1330 |
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
|
1331 |
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
|
1332 |
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
|
1333 |
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
|
1334 |
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
|
1335 |
# 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
|
1336 |
# 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
|
1337 |
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
|
1338 |
% (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
|
1339 |
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
|
1340 |
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
|
1341 |
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
|
1342 |
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
|
1343 |
lhssql = '%s.%s%s' % (table, SQL_PREFIX, rel.r_type) |
0 | 1344 |
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
|
1345 |
if rel._q_needcast == 'TODAY': |
0 | 1346 |
sql = 'DATE(%s)%s' % (lhssql, rhssql) |
1347 |
# 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
|
1348 |
#elif rel._q_needcast == 'NOW': |
0 | 1349 |
# sql = 'TIMESTAMP(%s)%s' % (lhssql, rhssql) |
1350 |
else: |
|
1351 |
sql = '%s%s' % (lhssql, rhssql) |
|
1352 |
except AttributeError: |
|
1353 |
sql = '%s%s' % (lhssql, rhssql) |
|
5004
4cc020ee70e2
le patch rql26 a été importé
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4845
diff
changeset
|
1354 |
if lhs.variable.stinfo.get('optrelations'): |
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1355 |
self._state.add_outer_join_condition(table, sql) |
0 | 1356 |
else: |
1357 |
return sql |
|
1358 |
||
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
|
1359 |
def _visit_has_text_relation(self, rel): |
0 | 1360 |
"""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
|
1361 |
lhs, rhs = rel.get_parts() |
0 | 1362 |
const = rhs.children[0] |
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1363 |
alias = self._state.fti_table(rel, self.dbhelper.fti_table) |
0 | 1364 |
jointo = lhs.accept(self) |
1365 |
restriction = '' |
|
1366 |
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
|
1367 |
me_is_principal = lhsvar.stinfo.get('principal') is rel |
0 | 1368 |
if me_is_principal: |
5004
4cc020ee70e2
le patch rql26 a été importé
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4845
diff
changeset
|
1369 |
if lhsvar.stinfo['typerel'] is None: |
0 | 1370 |
# the variable is using the fti table, no join needed |
1371 |
jointo = None |
|
1372 |
elif not lhsvar.name in self._varmap: |
|
1373 |
# join on entities instead of etype's table to get result for |
|
1374 |
# 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
|
1375 |
ealias = lhsvar._q_sqltable = '_' + lhsvar.name |
0 | 1376 |
jointo = lhsvar._q_sql = '%s.eid' % ealias |
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1377 |
self._state.add_table('entities AS %s' % ealias, ealias) |
0 | 1378 |
if not lhsvar._q_invariant or len(lhsvar.stinfo['possibletypes']) == 1: |
1379 |
restriction = " AND %s.type='%s'" % (ealias, self._state.solution[lhs.name]) |
|
1380 |
else: |
|
1381 |
etypes = ','.join("'%s'" % etype for etype in lhsvar.stinfo['possibletypes']) |
|
1382 |
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
|
1383 |
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
|
1384 |
self._state.done.add(rel.parent) |
0 | 1385 |
not_ = True |
1386 |
else: |
|
1387 |
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
|
1388 |
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
|
1389 |
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
|
1390 |
jointo, not_) + restriction |
1802
d628defebc17
delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
1522
diff
changeset
|
1391 |
|
3815
50b87f759b5d
test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
3787
diff
changeset
|
1392 |
def visit_comparison(self, cmp): |
3787
82bb2c7f083b
C - fix typo. make `lgp check` happy.
Nicolas Chauvat <nicolas.chauvat@logilab.fr>
parents:
3762
diff
changeset
|
1393 |
"""generate SQL for a comparison""" |
7707
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1394 |
optional = getattr(cmp, 'optional', None) # rql < 0.30 |
0 | 1395 |
if len(cmp.children) == 2: |
7707
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1396 |
# simplified expression from HAVING clause |
0 | 1397 |
lhs, rhs = cmp.children |
1398 |
else: |
|
1399 |
lhs = None |
|
1400 |
rhs = cmp.children[0] |
|
7707
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1401 |
assert not optional |
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1402 |
sql = None |
0 | 1403 |
operator = cmp.operator |
5702
9fb240cf0f61
[rql] more update to 0.26.2 api
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
5662
diff
changeset
|
1404 |
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
|
1405 |
if operator == 'ILIKE' and not self.dbhelper.ilike_support: |
0 | 1406 |
operator = ' LIKE ' |
1407 |
else: |
|
1408 |
operator = ' %s ' % operator |
|
7257
beea955b45e2
[rql] basic support for regexp-based pattern matching
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
7256
diff
changeset
|
1409 |
elif operator == 'REGEXP': |
7707
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1410 |
sql = ' %s' % self.dbhelper.sql_regexp_match_expression(rhs.accept(self)) |
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
|
1411 |
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
|
1412 |
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
|
1413 |
if lhs is None: |
7707
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1414 |
sql = ' IS NULL' |
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1415 |
else: |
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1416 |
sql = '%s IS NULL' % lhs.accept(self) |
0 | 1417 |
elif isinstance(rhs, Function) and rhs.name == 'IN': |
1418 |
assert operator == '=' |
|
1419 |
operator = ' ' |
|
7707
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1420 |
if sql is None: |
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1421 |
if lhs is None: |
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1422 |
sql = '%s%s'% (operator, rhs.accept(self)) |
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1423 |
else: |
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1424 |
sql = '%s%s%s'% (lhs.accept(self), operator, rhs.accept(self)) |
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1425 |
if optional is None: |
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1426 |
return sql |
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1427 |
leftvars = cmp.children[0].get_nodes(VariableRef) |
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1428 |
assert len(leftvars) == 1 |
7902
2ada3052e626
[rql2sql] support for outer join on column alias (closes #1979645)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7734
diff
changeset
|
1429 |
if leftvars[0].variable.stinfo['attrvar'] is None: |
2ada3052e626
[rql2sql] support for outer join on column alias (closes #1979645)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7734
diff
changeset
|
1430 |
assert isinstance(leftvars[0].variable, ColumnAlias) |
2ada3052e626
[rql2sql] support for outer join on column alias (closes #1979645)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7734
diff
changeset
|
1431 |
leftalias = leftvars[0].variable._q_sqltable |
2ada3052e626
[rql2sql] support for outer join on column alias (closes #1979645)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7734
diff
changeset
|
1432 |
else: |
2ada3052e626
[rql2sql] support for outer join on column alias (closes #1979645)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7734
diff
changeset
|
1433 |
leftalias = self._var_table(leftvars[0].variable.stinfo['attrvar']) |
7707
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1434 |
rightvars = cmp.children[1].get_nodes(VariableRef) |
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1435 |
assert len(rightvars) == 1 |
7902
2ada3052e626
[rql2sql] support for outer join on column alias (closes #1979645)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7734
diff
changeset
|
1436 |
if rightvars[0].variable.stinfo['attrvar'] is None: |
2ada3052e626
[rql2sql] support for outer join on column alias (closes #1979645)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7734
diff
changeset
|
1437 |
assert isinstance(rightvars[0].variable, ColumnAlias) |
2ada3052e626
[rql2sql] support for outer join on column alias (closes #1979645)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7734
diff
changeset
|
1438 |
rightalias = rightvars[0].variable._q_sqltable |
2ada3052e626
[rql2sql] support for outer join on column alias (closes #1979645)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7734
diff
changeset
|
1439 |
else: |
2ada3052e626
[rql2sql] support for outer join on column alias (closes #1979645)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7734
diff
changeset
|
1440 |
rightalias = self._var_table(rightvars[0].variable.stinfo['attrvar']) |
7707
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1441 |
if optional == 'right': |
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1442 |
self._state.replace_tables_by_outer_join( |
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1443 |
leftalias, rightalias, 'LEFT', sql) |
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1444 |
elif optional == 'left': |
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1445 |
self._state.replace_tables_by_outer_join( |
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1446 |
rightalias, leftalias, 'LEFT', sql) |
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1447 |
else: |
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1448 |
self._state.replace_tables_by_outer_join( |
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1449 |
leftalias, rightalias, 'FULL', sql) |
936530f8d32c
[sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7704
diff
changeset
|
1450 |
return '' |
1802
d628defebc17
delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
1522
diff
changeset
|
1451 |
|
3815
50b87f759b5d
test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
3787
diff
changeset
|
1452 |
def visit_mathexpression(self, mexpr): |
0 | 1453 |
"""generate SQL for a mathematic expression""" |
1454 |
lhs, rhs = mexpr.get_parts() |
|
1455 |
# check for string concatenation |
|
1456 |
operator = mexpr.operator |
|
1457 |
try: |
|
1458 |
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
|
1459 |
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
|
1460 |
rhs.accept(self)) |
0 | 1461 |
except CoercionError: |
1462 |
pass |
|
3815
50b87f759b5d
test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
3787
diff
changeset
|
1463 |
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
|
1464 |
|
7729
5f89f11143bc
[rql2sql] closes #1892471: support for unary expression introduced in rql 0.30
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7718
diff
changeset
|
1465 |
def visit_unaryexpression(self, uexpr): |
5f89f11143bc
[rql2sql] closes #1892471: support for unary expression introduced in rql 0.30
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7718
diff
changeset
|
1466 |
"""generate SQL for a unary expression""" |
5f89f11143bc
[rql2sql] closes #1892471: support for unary expression introduced in rql 0.30
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7718
diff
changeset
|
1467 |
return '%s%s'% (uexpr.operator, uexpr.children[0].accept(self)) |
5f89f11143bc
[rql2sql] closes #1892471: support for unary expression introduced in rql 0.30
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7718
diff
changeset
|
1468 |
|
3815
50b87f759b5d
test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
3787
diff
changeset
|
1469 |
def visit_function(self, func): |
0 | 1470 |
"""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
|
1471 |
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
|
1472 |
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
|
1473 |
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
|
1474 |
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
|
1475 |
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
|
1476 |
" '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
|
1477 |
const = rel.get_parts()[1].children[0] |
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1478 |
return self.dbhelper.fti_rank_order( |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1479 |
self._state.fti_table(rel, self.dbhelper.fti_table), |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1480 |
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
|
1481 |
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
|
1482 |
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
|
1483 |
# 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
|
1484 |
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
|
1485 |
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
|
1486 |
# 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
|
1487 |
return self.dbhelper.func_as_sql(func.name, args) |
0 | 1488 |
|
3815
50b87f759b5d
test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
3787
diff
changeset
|
1489 |
def visit_constant(self, constant): |
0 | 1490 |
"""generate SQL name for a constant""" |
1491 |
if constant.type is None: |
|
1492 |
return 'NULL' |
|
6286 | 1493 |
value = constant.value |
7256
ccd44caeb936
[rql] support CAST() Function
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
7252
diff
changeset
|
1494 |
if constant.type == 'etype': |
ccd44caeb936
[rql] support CAST() Function
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
7252
diff
changeset
|
1495 |
return value |
7704
0f49dd8e76b5
[rql2sql] don't introduce substitution for Int and Boolean constants. This is needed for COUNT(DISTINCT) support
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7672
diff
changeset
|
1496 |
if constant.type == 'Int': # XXX Float? |
0f49dd8e76b5
[rql2sql] don't introduce substitution for Int and Boolean constants. This is needed for COUNT(DISTINCT) support
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7672
diff
changeset
|
1497 |
return str(value) |
0 | 1498 |
if constant.type in ('Date', 'Datetime'): |
1499 |
rel = constant.relation() |
|
1500 |
if rel is not None: |
|
1501 |
rel._q_needcast = value |
|
1502 |
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
|
1503 |
if constant.type == 'Boolean': |
7718
0e82e7e5a34f
[rql2sql] take care to boolean_value returning an int (eg sqlite/mysql)
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7713
diff
changeset
|
1504 |
return str(self.dbhelper.boolean_value(value)) |
4124
8f2f5f0a89e7
fix backout
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4121
diff
changeset
|
1505 |
if constant.type == 'Substitute': |
6131
087c5a168010
[ms] more planning bug fixes
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
6050
diff
changeset
|
1506 |
try: |
087c5a168010
[ms] more planning bug fixes
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
6050
diff
changeset
|
1507 |
# 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
|
1508 |
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
|
1509 |
except KeyError: |
6286 | 1510 |
_id = value |
6131
087c5a168010
[ms] more planning bug fixes
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
6050
diff
changeset
|
1511 |
if isinstance(_id, unicode): |
087c5a168010
[ms] more planning bug fixes
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
6050
diff
changeset
|
1512 |
_id = _id.encode() |
7166
dde161937d3e
[time zone] support for TZDatetime and TZTime data type
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1513 |
# convert timestamp to utc. |
dde161937d3e
[time zone] support for TZDatetime and TZTime data type
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1514 |
# expect SET TiME ZONE to UTC at connection opening time. |
dde161937d3e
[time zone] support for TZDatetime and TZTime data type
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1515 |
# This shouldn't change anything for datetime without TZ. |
dde161937d3e
[time zone] support for TZDatetime and TZTime data type
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1516 |
value = self._args[_id] |
dde161937d3e
[time zone] support for TZDatetime and TZTime data type
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1517 |
if isinstance(value, datetime) and value.tzinfo is not None: |
dde161937d3e
[time zone] support for TZDatetime and TZTime data type
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1518 |
self._query_attrs[_id] = utcdatetime(value) |
dde161937d3e
[time zone] support for TZDatetime and TZTime data type
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1519 |
elif isinstance(value, time) and value.tzinfo is not None: |
dde161937d3e
[time zone] support for TZDatetime and TZTime data type
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1520 |
self._query_attrs[_id] = utctime(value) |
0 | 1521 |
else: |
1522 |
_id = str(id(constant)).replace('-', '', 1) |
|
1523 |
self._query_attrs[_id] = value |
|
1524 |
return '%%(%s)s' % _id |
|
1802
d628defebc17
delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
1522
diff
changeset
|
1525 |
|
3815
50b87f759b5d
test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
3787
diff
changeset
|
1526 |
def visit_variableref(self, variableref): |
0 | 1527 |
"""get the sql name for a variable reference""" |
1528 |
# 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
|
1529 |
return variableref.variable.accept(self) |
0 | 1530 |
|
3815
50b87f759b5d
test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
3787
diff
changeset
|
1531 |
def visit_columnalias(self, colalias): |
0 | 1532 |
"""get the sql name for a subquery column alias""" |
1533 |
if colalias.name in self._varmap: |
|
1534 |
sql = self._varmap[colalias.name] |
|
1122
9f37de24251f
fix rql2sq w/ outer join on subquery result
sylvain.thenault@logilab.fr
parents:
438
diff
changeset
|
1535 |
table = sql.split('.', 1)[0] |
9f37de24251f
fix rql2sq w/ outer join on subquery result
sylvain.thenault@logilab.fr
parents:
438
diff
changeset
|
1536 |
colalias._q_sqltable = table |
9f37de24251f
fix rql2sq w/ outer join on subquery result
sylvain.thenault@logilab.fr
parents:
438
diff
changeset
|
1537 |
colalias._q_sql = sql |
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1538 |
self._state.add_table(table) |
0 | 1539 |
return sql |
1540 |
return colalias._q_sql |
|
1802
d628defebc17
delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
1522
diff
changeset
|
1541 |
|
3815
50b87f759b5d
test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
3787
diff
changeset
|
1542 |
def visit_variable(self, variable): |
0 | 1543 |
"""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
|
1544 |
#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
|
1545 |
if variable.name in self._state.done: |
0 | 1546 |
if self._in_wrapping_query: |
1547 |
return 'T1.%s' % self._state.aliases[variable.name] |
|
1548 |
return variable._q_sql |
|
1549 |
self._state.done.add(variable.name) |
|
1550 |
vtablename = None |
|
3815
50b87f759b5d
test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
3787
diff
changeset
|
1551 |
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
|
1552 |
sql, vtablename = self._var_info(variable) |
0 | 1553 |
elif variable.stinfo['attrvar']: |
1554 |
# attribute variable (systematically used in rhs of final |
|
1555 |
# 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
|
1556 |
sql = self._linked_var_sql(variable) |
0 | 1557 |
elif variable._q_invariant: |
1558 |
# since variable is invariant, we know we won't found final relation |
|
1559 |
principal = variable.stinfo['principal'] |
|
1560 |
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
|
1561 |
vtablename = '_' + variable.name |
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1562 |
self._state.add_table('entities AS %s' % vtablename, vtablename) |
0 | 1563 |
sql = '%s.eid' % vtablename |
5004
4cc020ee70e2
le patch rql26 a été importé
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4845
diff
changeset
|
1564 |
if variable.stinfo['typerel'] is not None: |
0 | 1565 |
# add additional restriction on entities.type column |
1566 |
pts = variable.stinfo['possibletypes'] |
|
1567 |
if len(pts) == 1: |
|
1568 |
etype = iter(variable.stinfo['possibletypes']).next() |
|
1569 |
restr = "%s.type='%s'" % (vtablename, etype) |
|
1570 |
else: |
|
1571 |
etypes = ','.join("'%s'" % et for et in pts) |
|
1572 |
restr = '%s.type IN (%s)' % (vtablename, etypes) |
|
1573 |
self._state.add_restriction(restr) |
|
1574 |
elif principal.r_type == 'has_text': |
|
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1575 |
sql = '%s.%s' % (self._state.fti_table(principal, |
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1576 |
self.dbhelper.fti_table), |
5010
b2c5aee8ca3f
[cleanup] rename dbms_helper to dbhelper for consistency
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
4845
diff
changeset
|
1577 |
self.dbhelper.fti_uid_attr) |
0 | 1578 |
elif principal in variable.stinfo['rhsrelations']: |
1579 |
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
|
1580 |
sql = self._linked_var_sql(variable) |
0 | 1581 |
else: |
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1582 |
sql = '%s.eid_to' % self._state.relation_table(principal) |
0 | 1583 |
else: |
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1584 |
sql = '%s.eid_from' % self._state.relation_table(principal) |
0 | 1585 |
else: |
1586 |
# standard variable: get table name according to etype and use .eid |
|
1587 |
# attribute |
|
1588 |
sql, vtablename = self._var_info(variable) |
|
1589 |
variable._q_sqltable = vtablename |
|
1590 |
variable._q_sql = sql |
|
1591 |
return sql |
|
1592 |
||
1593 |
# various utilities ####################################################### |
|
1594 |
||
1595 |
def _extra_join_sql(self, relation, sql, var): |
|
1596 |
# if rhs var is invariant, and this relation is not its principal, |
|
1597 |
# generate extra join |
|
1598 |
try: |
|
1599 |
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
|
1600 |
op = relation.operator() |
50b87f759b5d
test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
3787
diff
changeset
|
1601 |
if op == '=': |
50b87f759b5d
test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
3787
diff
changeset
|
1602 |
# 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
|
1603 |
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
|
1604 |
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
|
1605 |
else: |
50b87f759b5d
test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
3787
diff
changeset
|
1606 |
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
|
1607 |
return '%s%s%s' % tuple(args) |
0 | 1608 |
except KeyError: |
1609 |
# no principal defined, relation is necessarily the principal and |
|
1610 |
# so nothing to return here |
|
1611 |
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
|
1612 |
return None |
1802
d628defebc17
delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
1522
diff
changeset
|
1613 |
|
5593
f6c55bec9326
[rql2sql] properly compute scope for variable from temporary tables
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
5582
diff
changeset
|
1614 |
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
|
1615 |
scope = 9999 |
f6c55bec9326
[rql2sql] properly compute scope for variable from temporary tables
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
5582
diff
changeset
|
1616 |
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
|
1617 |
# 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
|
1618 |
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
|
1619 |
try: |
f6c55bec9326
[rql2sql] properly compute scope for variable from temporary tables
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
5582
diff
changeset
|
1620 |
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
|
1621 |
except KeyError: |
f6c55bec9326
[rql2sql] properly compute scope for variable from temporary tables
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
5582
diff
changeset
|
1622 |
scope = 0 # XXX |
f6c55bec9326
[rql2sql] properly compute scope for variable from temporary tables
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
5582
diff
changeset
|
1623 |
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
|
1624 |
break |
56784e46509f
[rql2sql] fix scope computation of variable from temporary table
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
5630
diff
changeset
|
1625 |
return scope |
5593
f6c55bec9326
[rql2sql] properly compute scope for variable from temporary tables
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
5582
diff
changeset
|
1626 |
|
6131
087c5a168010
[ms] more planning bug fixes
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
6050
diff
changeset
|
1627 |
def _mapped_term(self, term, key): |
087c5a168010
[ms] more planning bug fixes
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
6050
diff
changeset
|
1628 |
"""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
|
1629 |
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
|
1630 |
""" |
087c5a168010
[ms] more planning bug fixes
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
6050
diff
changeset
|
1631 |
sql = self._varmap[key] |
087c5a168010
[ms] more planning bug fixes
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
6050
diff
changeset
|
1632 |
tablealias = sql.split('.', 1)[0] |
087c5a168010
[ms] more planning bug fixes
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
6050
diff
changeset
|
1633 |
scope = self._temp_table_scope(term.stmt, tablealias) |
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1634 |
self._state.add_table(tablealias, scope=scope) |
6131
087c5a168010
[ms] more planning bug fixes
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
6050
diff
changeset
|
1635 |
return sql, tablealias |
087c5a168010
[ms] more planning bug fixes
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
6050
diff
changeset
|
1636 |
|
0 | 1637 |
def _var_info(self, var): |
1638 |
try: |
|
6131
087c5a168010
[ms] more planning bug fixes
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
6050
diff
changeset
|
1639 |
return self._mapped_term(var, var.name) |
0 | 1640 |
except KeyError: |
5593
f6c55bec9326
[rql2sql] properly compute scope for variable from temporary tables
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
5582
diff
changeset
|
1641 |
scope = self._state.scopes[var.scope] |
0 | 1642 |
etype = self._state.solution[var.name] |
1643 |
# 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
|
1644 |
if self.schema.eschema(etype).final: |
0 | 1645 |
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
|
1646 |
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
|
1647 |
sql = '%s.%seid' % (tablealias, SQL_PREFIX) |
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1648 |
self._state.add_table('%s%s AS %s' % (SQL_PREFIX, etype, tablealias), |
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
|
1649 |
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
|
1650 |
return sql, tablealias |
1802
d628defebc17
delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
1522
diff
changeset
|
1651 |
|
0 | 1652 |
def _inlined_var_sql(self, var, rtype): |
1653 |
try: |
|
1654 |
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
|
1655 |
scope = self._state.scopes[var.scope] |
7193
7eaef037ea9d
[sql generation] fix missing join with outer joins w/ rhs invariant variable...
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7108
diff
changeset
|
1656 |
self._state.add_table(sql.split('.', 1)[0], scope=scope) |
0 | 1657 |
except KeyError: |
7493
3c46b9390871
[rql2sql] fix for some queries where eid variable used and entities picked as variable's table
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7473
diff
changeset
|
1658 |
# rtype may be an attribute relation when called from |
3c46b9390871
[rql2sql] fix for some queries where eid variable used and entities picked as variable's table
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7473
diff
changeset
|
1659 |
# _visit_var_attr_relation. take care about 'eid' rtype, since in |
3c46b9390871
[rql2sql] fix for some queries where eid variable used and entities picked as variable's table
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7473
diff
changeset
|
1660 |
# some case we may use the `entities` table, so in that case we've |
3c46b9390871
[rql2sql] fix for some queries where eid variable used and entities picked as variable's table
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7473
diff
changeset
|
1661 |
# to properly use variable'sql |
3c46b9390871
[rql2sql] fix for some queries where eid variable used and entities picked as variable's table
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7473
diff
changeset
|
1662 |
if rtype == 'eid': |
3c46b9390871
[rql2sql] fix for some queries where eid variable used and entities picked as variable's table
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7473
diff
changeset
|
1663 |
sql = var.accept(self) |
3c46b9390871
[rql2sql] fix for some queries where eid variable used and entities picked as variable's table
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7473
diff
changeset
|
1664 |
else: |
3c46b9390871
[rql2sql] fix for some queries where eid variable used and entities picked as variable's table
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
7473
diff
changeset
|
1665 |
sql = '%s.%s%s' % (self._var_table(var), SQL_PREFIX, rtype) |
0 | 1666 |
return sql |
1802
d628defebc17
delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
1522
diff
changeset
|
1667 |
|
3815
50b87f759b5d
test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
3787
diff
changeset
|
1668 |
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
|
1669 |
if not self._state.ignore_varmap: |
0 | 1670 |
try: |
1802
d628defebc17
delete-trailing-whitespace + some copyright update
Adrien Di Mascio <Adrien.DiMascio@logilab.fr>
parents:
1522
diff
changeset
|
1671 |
return self._varmap[variable.name] |
0 | 1672 |
except KeyError: |
1673 |
pass |
|
3815
50b87f759b5d
test and fix http://www.logilab.org/ticket/499838
Sylvain Thénault <sylvain.thenault@logilab.fr>
parents:
3787
diff
changeset
|
1674 |
rel = (variable.stinfo.get('principal') or |
0 | 1675 |
iter(variable.stinfo['rhsrelations']).next()) |
1676 |
linkedvar = rel.children[0].variable |
|
1677 |
if rel.r_type == 'eid': |
|
1678 |
return linkedvar.accept(self) |
|
1679 |
if isinstance(linkedvar, ColumnAlias): |
|
1680 |
raise BadRQLQuery('variable %s should be selected by the subquery' |
|
1681 |
% variable.name) |
|
1682 |
try: |
|
1683 |
sql = self._varmap['%s.%s' % (linkedvar.name, rel.r_type)] |
|
1684 |
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
|
1685 |
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
|
1686 |
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
|
1687 |
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
|
1688 |
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
|
1689 |
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
|
1690 |
# 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
|
1691 |
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
|
1692 |
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
|
1693 |
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
|
1694 |
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
|
1695 |
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
|
1696 |
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
|
1697 |
sql = '%s.%s%s' % (linkedvar._q_sqltable, SQL_PREFIX, rel.r_type) |
0 | 1698 |
return sql |
1699 |
||
1700 |
# tables handling ######################################################### |
|
1701 |
||
1702 |
def _var_table(self, var): |
|
1703 |
var.accept(self)#.visit_variable(var) |
|
1704 |
return var._q_sqltable |