|
1 # copyright 2003-2012 LOGILAB S.A. (Paris, FRANCE), all rights reserved. |
|
2 # contact http://www.logilab.fr/ -- mailto:contact@logilab.fr |
|
3 # |
|
4 # This file is part of CubicWeb. |
|
5 # |
|
6 # CubicWeb is free software: you can redistribute it and/or modify it under the |
|
7 # terms of the GNU Lesser General Public License as published by the Free |
|
8 # Software Foundation, either version 2.1 of the License, or (at your option) |
|
9 # any later version. |
|
10 # |
|
11 # CubicWeb is distributed in the hope that it will be useful, but WITHOUT |
|
12 # ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS |
|
13 # FOR A PARTICULAR PURPOSE. See the GNU Lesser General Public License for more |
|
14 # details. |
|
15 # |
|
16 # You should have received a copy of the GNU Lesser General Public License along |
|
17 # with CubicWeb. If not, see <http://www.gnu.org/licenses/>. |
|
18 """unit tests for module cubicweb.server.sources.rql2sql""" |
|
19 from __future__ import print_function |
|
20 |
|
21 import sys |
|
22 import os |
|
23 from datetime import date |
|
24 from logilab.common.testlib import TestCase, unittest_main, mock_object |
|
25 |
|
26 from rql import BadRQLQuery |
|
27 from rql.utils import register_function, FunctionDescr |
|
28 |
|
29 from cubicweb.devtools import TestServerConfiguration |
|
30 from cubicweb.devtools.repotest import RQLGeneratorTC |
|
31 from cubicweb.server.sources.rql2sql import remove_unused_solutions |
|
32 |
|
33 |
|
34 # add a dumb registered procedure |
|
35 class stockproc(FunctionDescr): |
|
36 supported_backends = ('postgres', 'sqlite', 'mysql') |
|
37 try: |
|
38 register_function(stockproc) |
|
39 except AssertionError as ex: |
|
40 pass # already registered |
|
41 |
|
42 |
|
43 from logilab import database as db |
|
44 def monkey_patch_import_driver_module(driver, drivers, quiet=True): |
|
45 if not driver in drivers: |
|
46 raise db.UnknownDriver(driver) |
|
47 for modname in drivers[driver]: |
|
48 try: |
|
49 if not quiet: |
|
50 sys.stderr.write('Trying %s\n' % modname) |
|
51 module = db.load_module_from_name(modname, use_sys=False) |
|
52 break |
|
53 except ImportError: |
|
54 if not quiet: |
|
55 sys.stderr.write('%s is not available\n' % modname) |
|
56 continue |
|
57 else: |
|
58 return mock_object(STRING=1, BOOLEAN=2, BINARY=3, DATETIME=4, NUMBER=5), drivers[driver][0] |
|
59 return module, modname |
|
60 |
|
61 |
|
62 def setUpModule(): |
|
63 global config, schema |
|
64 config = TestServerConfiguration('data', apphome=CWRQLTC.datadir) |
|
65 config.bootstrap_cubes() |
|
66 schema = config.load_schema() |
|
67 schema['in_state'].inlined = True |
|
68 schema['state_of'].inlined = False |
|
69 schema['comments'].inlined = False |
|
70 db._backup_import_driver_module = db._import_driver_module |
|
71 db._import_driver_module = monkey_patch_import_driver_module |
|
72 |
|
73 def tearDownModule(): |
|
74 global config, schema |
|
75 del config, schema |
|
76 db._import_driver_module = db._backup_import_driver_module |
|
77 del db._backup_import_driver_module |
|
78 |
|
79 PARSER = [ |
|
80 (r"Personne P WHERE P nom 'Zig\'oto';", |
|
81 '''SELECT _P.cw_eid |
|
82 FROM cw_Personne AS _P |
|
83 WHERE _P.cw_nom=Zig\'oto'''), |
|
84 |
|
85 (r'Personne P WHERE P nom ~= "Zig\"oto%";', |
|
86 '''SELECT _P.cw_eid |
|
87 FROM cw_Personne AS _P |
|
88 WHERE _P.cw_nom ILIKE Zig"oto%'''), |
|
89 ] |
|
90 |
|
91 BASIC = [ |
|
92 ("Any AS WHERE AS is Affaire", |
|
93 '''SELECT _AS.cw_eid |
|
94 FROM cw_Affaire AS _AS'''), |
|
95 |
|
96 ("Any X WHERE X is Affaire", |
|
97 '''SELECT _X.cw_eid |
|
98 FROM cw_Affaire AS _X'''), |
|
99 |
|
100 ("Any X WHERE X eid 0", |
|
101 '''SELECT 0'''), |
|
102 |
|
103 ("Personne P", |
|
104 '''SELECT _P.cw_eid |
|
105 FROM cw_Personne AS _P'''), |
|
106 |
|
107 ("Personne P WHERE P test TRUE", |
|
108 '''SELECT _P.cw_eid |
|
109 FROM cw_Personne AS _P |
|
110 WHERE _P.cw_test=True'''), |
|
111 |
|
112 ("Personne P WHERE P test false", |
|
113 '''SELECT _P.cw_eid |
|
114 FROM cw_Personne AS _P |
|
115 WHERE _P.cw_test=False'''), |
|
116 |
|
117 ("Personne P WHERE P eid -1", |
|
118 '''SELECT -1'''), |
|
119 |
|
120 ("Personne P WHERE S is Societe, P travaille S, S nom 'Logilab';", |
|
121 '''SELECT rel_travaille0.eid_from |
|
122 FROM cw_Societe AS _S, travaille_relation AS rel_travaille0 |
|
123 WHERE rel_travaille0.eid_to=_S.cw_eid AND _S.cw_nom=Logilab'''), |
|
124 |
|
125 ("Personne P WHERE P concerne A, A concerne S, S nom 'Logilab', S is Societe;", |
|
126 '''SELECT rel_concerne0.eid_from |
|
127 FROM concerne_relation AS rel_concerne0, concerne_relation AS rel_concerne1, cw_Societe AS _S |
|
128 WHERE rel_concerne0.eid_to=rel_concerne1.eid_from AND rel_concerne1.eid_to=_S.cw_eid AND _S.cw_nom=Logilab'''), |
|
129 |
|
130 ("Note N WHERE X evaluee N, X nom 'Logilab';", |
|
131 '''SELECT rel_evaluee0.eid_to |
|
132 FROM cw_Division AS _X, evaluee_relation AS rel_evaluee0 |
|
133 WHERE rel_evaluee0.eid_from=_X.cw_eid AND _X.cw_nom=Logilab |
|
134 UNION ALL |
|
135 SELECT rel_evaluee0.eid_to |
|
136 FROM cw_Personne AS _X, evaluee_relation AS rel_evaluee0 |
|
137 WHERE rel_evaluee0.eid_from=_X.cw_eid AND _X.cw_nom=Logilab |
|
138 UNION ALL |
|
139 SELECT rel_evaluee0.eid_to |
|
140 FROM cw_Societe AS _X, evaluee_relation AS rel_evaluee0 |
|
141 WHERE rel_evaluee0.eid_from=_X.cw_eid AND _X.cw_nom=Logilab |
|
142 UNION ALL |
|
143 SELECT rel_evaluee0.eid_to |
|
144 FROM cw_SubDivision AS _X, evaluee_relation AS rel_evaluee0 |
|
145 WHERE rel_evaluee0.eid_from=_X.cw_eid AND _X.cw_nom=Logilab'''), |
|
146 |
|
147 ("Note N WHERE X evaluee N, X nom in ('Logilab', 'Caesium');", |
|
148 '''SELECT rel_evaluee0.eid_to |
|
149 FROM cw_Division AS _X, evaluee_relation AS rel_evaluee0 |
|
150 WHERE rel_evaluee0.eid_from=_X.cw_eid AND _X.cw_nom IN(Logilab, Caesium) |
|
151 UNION ALL |
|
152 SELECT rel_evaluee0.eid_to |
|
153 FROM cw_Personne AS _X, evaluee_relation AS rel_evaluee0 |
|
154 WHERE rel_evaluee0.eid_from=_X.cw_eid AND _X.cw_nom IN(Logilab, Caesium) |
|
155 UNION ALL |
|
156 SELECT rel_evaluee0.eid_to |
|
157 FROM cw_Societe AS _X, evaluee_relation AS rel_evaluee0 |
|
158 WHERE rel_evaluee0.eid_from=_X.cw_eid AND _X.cw_nom IN(Logilab, Caesium) |
|
159 UNION ALL |
|
160 SELECT rel_evaluee0.eid_to |
|
161 FROM cw_SubDivision AS _X, evaluee_relation AS rel_evaluee0 |
|
162 WHERE rel_evaluee0.eid_from=_X.cw_eid AND _X.cw_nom IN(Logilab, Caesium)'''), |
|
163 |
|
164 ("Any N WHERE G is CWGroup, G name N, E eid 12, E read_permission G", |
|
165 '''SELECT _G.cw_name |
|
166 FROM cw_CWGroup AS _G, read_permission_relation AS rel_read_permission0 |
|
167 WHERE rel_read_permission0.eid_from=12 AND rel_read_permission0.eid_to=_G.cw_eid'''), |
|
168 |
|
169 ('Any Y WHERE U login "admin", U login Y', # stupid but valid... |
|
170 """SELECT _U.cw_login |
|
171 FROM cw_CWUser AS _U |
|
172 WHERE _U.cw_login=admin"""), |
|
173 |
|
174 ('Any T WHERE T tags X, X is State', |
|
175 '''SELECT rel_tags0.eid_from |
|
176 FROM cw_State AS _X, tags_relation AS rel_tags0 |
|
177 WHERE rel_tags0.eid_to=_X.cw_eid'''), |
|
178 |
|
179 ('Any X,Y WHERE X eid 0, Y eid 1, X concerne Y', |
|
180 '''SELECT 0, 1 |
|
181 FROM concerne_relation AS rel_concerne0 |
|
182 WHERE rel_concerne0.eid_from=0 AND rel_concerne0.eid_to=1'''), |
|
183 |
|
184 ("Any X WHERE X prenom 'lulu'," |
|
185 "EXISTS(X owned_by U, U in_group G, G name 'lulufanclub' OR G name 'managers');", |
|
186 '''SELECT _X.cw_eid |
|
187 FROM cw_Personne AS _X |
|
188 WHERE _X.cw_prenom=lulu AND EXISTS(SELECT 1 FROM cw_CWGroup AS _G, in_group_relation AS rel_in_group1, owned_by_relation AS rel_owned_by0 WHERE rel_owned_by0.eid_from=_X.cw_eid AND rel_in_group1.eid_from=rel_owned_by0.eid_to AND rel_in_group1.eid_to=_G.cw_eid AND ((_G.cw_name=lulufanclub) OR (_G.cw_name=managers)))'''), |
|
189 |
|
190 ("Any X WHERE X prenom 'lulu'," |
|
191 "NOT EXISTS(X owned_by U, U in_group G, G name 'lulufanclub' OR G name 'managers');", |
|
192 '''SELECT _X.cw_eid |
|
193 FROM cw_Personne AS _X |
|
194 WHERE _X.cw_prenom=lulu AND NOT (EXISTS(SELECT 1 FROM cw_CWGroup AS _G, in_group_relation AS rel_in_group1, owned_by_relation AS rel_owned_by0 WHERE rel_owned_by0.eid_from=_X.cw_eid AND rel_in_group1.eid_from=rel_owned_by0.eid_to AND rel_in_group1.eid_to=_G.cw_eid AND ((_G.cw_name=lulufanclub) OR (_G.cw_name=managers))))'''), |
|
195 |
|
196 ('Any X WHERE X title V, NOT X wikiid V, NOT X title "parent", X is Card', |
|
197 '''SELECT _X.cw_eid |
|
198 FROM cw_Card AS _X |
|
199 WHERE NOT (_X.cw_wikiid=_X.cw_title) AND NOT (_X.cw_title=parent)'''), |
|
200 |
|
201 ("Any -AS WHERE AS is Affaire", |
|
202 '''SELECT -_AS.cw_eid |
|
203 FROM cw_Affaire AS _AS'''), |
|
204 |
|
205 ] |
|
206 |
|
207 BASIC_WITH_LIMIT = [ |
|
208 ("Personne P LIMIT 20 OFFSET 10", |
|
209 '''SELECT _P.cw_eid |
|
210 FROM cw_Personne AS _P |
|
211 LIMIT 20 |
|
212 OFFSET 10'''), |
|
213 ("Any P ORDERBY N LIMIT 1 WHERE P is Personne, P travaille S, S eid %(eid)s, P nom N, P nom %(text)s", |
|
214 '''SELECT _P.cw_eid |
|
215 FROM cw_Personne AS _P, travaille_relation AS rel_travaille0 |
|
216 WHERE rel_travaille0.eid_from=_P.cw_eid AND rel_travaille0.eid_to=12345 AND _P.cw_nom=hip hop momo |
|
217 ORDER BY _P.cw_nom |
|
218 LIMIT 1'''), |
|
219 ] |
|
220 |
|
221 |
|
222 ADVANCED = [ |
|
223 ("Societe S WHERE S2 is Societe, S2 nom SN, S nom 'Logilab' OR S nom SN", |
|
224 '''SELECT _S.cw_eid |
|
225 FROM cw_Societe AS _S, cw_Societe AS _S2 |
|
226 WHERE ((_S.cw_nom=Logilab) OR (_S2.cw_nom=_S.cw_nom))'''), |
|
227 |
|
228 ("Societe S WHERE S nom 'Logilab' OR S nom 'Caesium'", |
|
229 '''SELECT _S.cw_eid |
|
230 FROM cw_Societe AS _S |
|
231 WHERE ((_S.cw_nom=Logilab) OR (_S.cw_nom=Caesium))'''), |
|
232 |
|
233 ('Any X WHERE X nom "toto", X eid IN (9700, 9710, 1045, 674)', |
|
234 '''SELECT _X.cw_eid |
|
235 FROM cw_Division AS _X |
|
236 WHERE _X.cw_nom=toto AND _X.cw_eid IN(9700, 9710, 1045, 674) |
|
237 UNION ALL |
|
238 SELECT _X.cw_eid |
|
239 FROM cw_Personne AS _X |
|
240 WHERE _X.cw_nom=toto AND _X.cw_eid IN(9700, 9710, 1045, 674) |
|
241 UNION ALL |
|
242 SELECT _X.cw_eid |
|
243 FROM cw_Societe AS _X |
|
244 WHERE _X.cw_nom=toto AND _X.cw_eid IN(9700, 9710, 1045, 674) |
|
245 UNION ALL |
|
246 SELECT _X.cw_eid |
|
247 FROM cw_SubDivision AS _X |
|
248 WHERE _X.cw_nom=toto AND _X.cw_eid IN(9700, 9710, 1045, 674)'''), |
|
249 |
|
250 ('Any Y, COUNT(N) GROUPBY Y WHERE Y evaluee N;', |
|
251 '''SELECT rel_evaluee0.eid_from, COUNT(rel_evaluee0.eid_to) |
|
252 FROM evaluee_relation AS rel_evaluee0 |
|
253 GROUP BY rel_evaluee0.eid_from'''), |
|
254 |
|
255 ("Any X WHERE X concerne B or C concerne X", |
|
256 '''SELECT _X.cw_eid |
|
257 FROM concerne_relation AS rel_concerne0, concerne_relation AS rel_concerne1, cw_Affaire AS _X |
|
258 WHERE ((rel_concerne0.eid_from=_X.cw_eid) OR (rel_concerne1.eid_to=_X.cw_eid))'''), |
|
259 |
|
260 ("Any X WHERE X travaille S or X concerne A", |
|
261 '''SELECT _X.cw_eid |
|
262 FROM concerne_relation AS rel_concerne1, cw_Personne AS _X, travaille_relation AS rel_travaille0 |
|
263 WHERE ((rel_travaille0.eid_from=_X.cw_eid) OR (rel_concerne1.eid_from=_X.cw_eid))'''), |
|
264 |
|
265 ("Any N WHERE A evaluee N or N ecrit_par P", |
|
266 '''SELECT _N.cw_eid |
|
267 FROM cw_Note AS _N, evaluee_relation AS rel_evaluee0 |
|
268 WHERE ((rel_evaluee0.eid_to=_N.cw_eid) OR (_N.cw_ecrit_par IS NOT NULL))'''), |
|
269 |
|
270 ("Any N WHERE A evaluee N or EXISTS(N todo_by U)", |
|
271 '''SELECT _N.cw_eid |
|
272 FROM cw_Note AS _N, evaluee_relation AS rel_evaluee0 |
|
273 WHERE ((rel_evaluee0.eid_to=_N.cw_eid) OR (EXISTS(SELECT 1 FROM todo_by_relation AS rel_todo_by1 WHERE rel_todo_by1.eid_from=_N.cw_eid)))'''), |
|
274 |
|
275 ("Any N WHERE A evaluee N or N todo_by U", |
|
276 '''SELECT _N.cw_eid |
|
277 FROM cw_Note AS _N, evaluee_relation AS rel_evaluee0, todo_by_relation AS rel_todo_by1 |
|
278 WHERE ((rel_evaluee0.eid_to=_N.cw_eid) OR (rel_todo_by1.eid_from=_N.cw_eid))'''), |
|
279 |
|
280 ("Any X WHERE X concerne B or C concerne X, B eid 12, C eid 13", |
|
281 '''SELECT _X.cw_eid |
|
282 FROM concerne_relation AS rel_concerne0, concerne_relation AS rel_concerne1, cw_Affaire AS _X |
|
283 WHERE ((rel_concerne0.eid_from=_X.cw_eid AND rel_concerne0.eid_to=12) OR (rel_concerne1.eid_from=13 AND rel_concerne1.eid_to=_X.cw_eid))'''), |
|
284 |
|
285 ('Any X WHERE X created_by U, X concerne B OR C concerne X, B eid 12, C eid 13', |
|
286 '''SELECT rel_created_by0.eid_from |
|
287 FROM concerne_relation AS rel_concerne1, concerne_relation AS rel_concerne2, created_by_relation AS rel_created_by0 |
|
288 WHERE ((rel_concerne1.eid_from=rel_created_by0.eid_from AND rel_concerne1.eid_to=12) OR (rel_concerne2.eid_from=13 AND rel_concerne2.eid_to=rel_created_by0.eid_from))'''), |
|
289 |
|
290 ('Any P WHERE P travaille_subdivision S1 OR P travaille_subdivision S2, S1 nom "logilab", S2 nom "caesium"', |
|
291 '''SELECT _P.cw_eid |
|
292 FROM cw_Personne AS _P, cw_SubDivision AS _S1, cw_SubDivision AS _S2, travaille_subdivision_relation AS rel_travaille_subdivision0, travaille_subdivision_relation AS rel_travaille_subdivision1 |
|
293 WHERE ((rel_travaille_subdivision0.eid_from=_P.cw_eid AND rel_travaille_subdivision0.eid_to=_S1.cw_eid) OR (rel_travaille_subdivision1.eid_from=_P.cw_eid AND rel_travaille_subdivision1.eid_to=_S2.cw_eid)) AND _S1.cw_nom=logilab AND _S2.cw_nom=caesium'''), |
|
294 |
|
295 ('Any X WHERE T tags X', |
|
296 '''SELECT rel_tags0.eid_to |
|
297 FROM tags_relation AS rel_tags0'''), |
|
298 |
|
299 ('Any X WHERE X in_basket B, B eid 12', |
|
300 '''SELECT rel_in_basket0.eid_from |
|
301 FROM in_basket_relation AS rel_in_basket0 |
|
302 WHERE rel_in_basket0.eid_to=12'''), |
|
303 |
|
304 ('Any SEN,RN,OEN WHERE X from_entity SE, SE eid 44, X relation_type R, R eid 139, X to_entity OE, OE eid 42, R name RN, SE name SEN, OE name OEN', |
|
305 '''SELECT _SE.cw_name, _R.cw_name, _OE.cw_name |
|
306 FROM cw_CWAttribute AS _X, cw_CWEType AS _OE, cw_CWEType AS _SE, cw_CWRType AS _R |
|
307 WHERE _X.cw_from_entity=44 AND _SE.cw_eid=44 AND _X.cw_relation_type=139 AND _R.cw_eid=139 AND _X.cw_to_entity=42 AND _OE.cw_eid=42 |
|
308 UNION ALL |
|
309 SELECT _SE.cw_name, _R.cw_name, _OE.cw_name |
|
310 FROM cw_CWEType AS _OE, cw_CWEType AS _SE, cw_CWRType AS _R, cw_CWRelation AS _X |
|
311 WHERE _X.cw_from_entity=44 AND _SE.cw_eid=44 AND _X.cw_relation_type=139 AND _R.cw_eid=139 AND _X.cw_to_entity=42 AND _OE.cw_eid=42'''), |
|
312 |
|
313 # Any O WHERE NOT S corrected_in O, S eid %(x)s, S concerns P, O version_of P, O in_state ST, NOT ST name "published", O modification_date MTIME ORDERBY MTIME DESC LIMIT 9 |
|
314 ('Any O WHERE NOT S ecrit_par O, S eid 1, S inline1 P, O inline2 P', |
|
315 '''SELECT _O.cw_eid |
|
316 FROM cw_Note AS _S, cw_Personne AS _O |
|
317 WHERE (_S.cw_ecrit_par IS NULL OR _S.cw_ecrit_par!=_O.cw_eid) AND _S.cw_eid=1 AND _S.cw_inline1 IS NOT NULL AND _O.cw_inline2=_S.cw_inline1'''), |
|
318 |
|
319 ('Any N WHERE N todo_by U, N is Note, U eid 2, N filed_under T, T eid 3', |
|
320 # N would actually be invarient if U eid 2 had given a specific type to U |
|
321 '''SELECT _N.cw_eid |
|
322 FROM cw_Note AS _N, filed_under_relation AS rel_filed_under1, todo_by_relation AS rel_todo_by0 |
|
323 WHERE rel_todo_by0.eid_from=_N.cw_eid AND rel_todo_by0.eid_to=2 AND rel_filed_under1.eid_from=_N.cw_eid AND rel_filed_under1.eid_to=3'''), |
|
324 |
|
325 ('Any N WHERE N todo_by U, U eid 2, P evaluee N, P eid 3', |
|
326 '''SELECT rel_evaluee1.eid_to |
|
327 FROM evaluee_relation AS rel_evaluee1, todo_by_relation AS rel_todo_by0 |
|
328 WHERE rel_evaluee1.eid_to=rel_todo_by0.eid_from AND rel_todo_by0.eid_to=2 AND rel_evaluee1.eid_from=3'''), |
|
329 |
|
330 |
|
331 (' Any X,U WHERE C owned_by U, NOT X owned_by U, C eid 1, X eid 2', |
|
332 '''SELECT 2, rel_owned_by0.eid_to |
|
333 FROM owned_by_relation AS rel_owned_by0 |
|
334 WHERE rel_owned_by0.eid_from=1 AND NOT (EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by1 WHERE rel_owned_by1.eid_from=2 AND rel_owned_by0.eid_to=rel_owned_by1.eid_to))'''), |
|
335 |
|
336 ('Any GN WHERE X in_group G, G name GN, (G name "managers" OR EXISTS(X copain T, T login in ("comme", "cochon")))', |
|
337 '''SELECT _G.cw_name |
|
338 FROM cw_CWGroup AS _G, in_group_relation AS rel_in_group0 |
|
339 WHERE rel_in_group0.eid_to=_G.cw_eid AND ((_G.cw_name=managers) OR (EXISTS(SELECT 1 FROM copain_relation AS rel_copain1, cw_CWUser AS _T WHERE rel_copain1.eid_from=rel_in_group0.eid_from AND rel_copain1.eid_to=_T.cw_eid AND _T.cw_login IN(comme, cochon))))'''), |
|
340 |
|
341 ('Any C WHERE C is Card, EXISTS(X documented_by C)', |
|
342 """SELECT _C.cw_eid |
|
343 FROM cw_Card AS _C |
|
344 WHERE EXISTS(SELECT 1 FROM documented_by_relation AS rel_documented_by0 WHERE rel_documented_by0.eid_to=_C.cw_eid)"""), |
|
345 |
|
346 ('Any C WHERE C is Card, EXISTS(X documented_by C, X eid 12)', |
|
347 """SELECT _C.cw_eid |
|
348 FROM cw_Card AS _C |
|
349 WHERE EXISTS(SELECT 1 FROM documented_by_relation AS rel_documented_by0 WHERE rel_documented_by0.eid_from=12 AND rel_documented_by0.eid_to=_C.cw_eid)"""), |
|
350 |
|
351 ('Any T WHERE C is Card, C title T, EXISTS(X documented_by C, X eid 12)', |
|
352 """SELECT _C.cw_title |
|
353 FROM cw_Card AS _C |
|
354 WHERE EXISTS(SELECT 1 FROM documented_by_relation AS rel_documented_by0 WHERE rel_documented_by0.eid_from=12 AND rel_documented_by0.eid_to=_C.cw_eid)"""), |
|
355 |
|
356 ('Any GN,L WHERE X in_group G, X login L, G name GN, EXISTS(X copain T, T login L, T login IN("comme", "cochon"))', |
|
357 '''SELECT _G.cw_name, _X.cw_login |
|
358 FROM cw_CWGroup AS _G, cw_CWUser AS _X, in_group_relation AS rel_in_group0 |
|
359 WHERE rel_in_group0.eid_from=_X.cw_eid AND rel_in_group0.eid_to=_G.cw_eid AND EXISTS(SELECT 1 FROM copain_relation AS rel_copain1, cw_CWUser AS _T WHERE rel_copain1.eid_from=_X.cw_eid AND rel_copain1.eid_to=_T.cw_eid AND _T.cw_login=_X.cw_login AND _T.cw_login IN(comme, cochon))'''), |
|
360 |
|
361 ('Any X,S, MAX(T) GROUPBY X,S ORDERBY S WHERE X is CWUser, T tags X, S eid IN(32), X in_state S', |
|
362 '''SELECT _X.cw_eid, 32, MAX(rel_tags0.eid_from) |
|
363 FROM cw_CWUser AS _X, tags_relation AS rel_tags0 |
|
364 WHERE rel_tags0.eid_to=_X.cw_eid AND _X.cw_in_state=32 |
|
365 GROUP BY _X.cw_eid'''), |
|
366 |
|
367 |
|
368 ('Any X WHERE Y evaluee X, Y is CWUser', |
|
369 '''SELECT rel_evaluee0.eid_to |
|
370 FROM cw_CWUser AS _Y, evaluee_relation AS rel_evaluee0 |
|
371 WHERE rel_evaluee0.eid_from=_Y.cw_eid'''), |
|
372 |
|
373 ('Any L WHERE X login "admin", X identity Y, Y login L', |
|
374 '''SELECT _Y.cw_login |
|
375 FROM cw_CWUser AS _X, cw_CWUser AS _Y |
|
376 WHERE _X.cw_login=admin AND _X.cw_eid=_Y.cw_eid'''), |
|
377 |
|
378 ('Any L WHERE X login "admin", NOT X identity Y, Y login L', |
|
379 '''SELECT _Y.cw_login |
|
380 FROM cw_CWUser AS _X, cw_CWUser AS _Y |
|
381 WHERE _X.cw_login=admin AND NOT (_X.cw_eid=_Y.cw_eid)'''), |
|
382 |
|
383 ('Any L WHERE X login "admin", X identity Y?, Y login L', |
|
384 '''SELECT _Y.cw_login |
|
385 FROM cw_CWUser AS _X LEFT OUTER JOIN cw_CWUser AS _Y ON (_X.cw_eid=_Y.cw_eid) |
|
386 WHERE _X.cw_login=admin'''), |
|
387 |
|
388 ('Any XN ORDERBY XN WHERE X name XN, X is IN (Basket,Folder,Tag)', |
|
389 '''SELECT _X.cw_name |
|
390 FROM cw_Basket AS _X |
|
391 UNION ALL |
|
392 SELECT _X.cw_name |
|
393 FROM cw_Folder AS _X |
|
394 UNION ALL |
|
395 SELECT _X.cw_name |
|
396 FROM cw_Tag AS _X |
|
397 ORDER BY 1'''), |
|
398 |
|
399 # DISTINCT, can use relation under exists scope as principal |
|
400 ('DISTINCT Any X,Y WHERE X name "CWGroup", X is CWEType, Y eid IN(1, 2, 3), EXISTS(X read_permission Y)', |
|
401 '''SELECT DISTINCT _X.cw_eid, rel_read_permission0.eid_to |
|
402 FROM cw_CWEType AS _X, read_permission_relation AS rel_read_permission0 |
|
403 WHERE _X.cw_name=CWGroup AND rel_read_permission0.eid_to IN(1, 2, 3) AND EXISTS(SELECT 1 WHERE rel_read_permission0.eid_from=_X.cw_eid)'''), |
|
404 |
|
405 # no distinct, Y can't be invariant |
|
406 ('Any X,Y WHERE X name "CWGroup", X is CWEType, Y eid IN(1, 2, 3), EXISTS(X read_permission Y)', |
|
407 '''SELECT _X.cw_eid, _Y.cw_eid |
|
408 FROM cw_CWEType AS _X, cw_CWGroup AS _Y |
|
409 WHERE _X.cw_name=CWGroup AND _Y.cw_eid IN(1, 2, 3) AND EXISTS(SELECT 1 FROM read_permission_relation AS rel_read_permission0 WHERE rel_read_permission0.eid_from=_X.cw_eid AND rel_read_permission0.eid_to=_Y.cw_eid) |
|
410 UNION ALL |
|
411 SELECT _X.cw_eid, _Y.cw_eid |
|
412 FROM cw_CWEType AS _X, cw_RQLExpression AS _Y |
|
413 WHERE _X.cw_name=CWGroup AND _Y.cw_eid IN(1, 2, 3) AND EXISTS(SELECT 1 FROM read_permission_relation AS rel_read_permission0 WHERE rel_read_permission0.eid_from=_X.cw_eid AND rel_read_permission0.eid_to=_Y.cw_eid)'''), |
|
414 |
|
415 # DISTINCT but NEGED exists, can't be invariant |
|
416 ('DISTINCT Any X,Y WHERE X name "CWGroup", X is CWEType, Y eid IN(1, 2, 3), NOT EXISTS(X read_permission Y)', |
|
417 '''SELECT DISTINCT _X.cw_eid, _Y.cw_eid |
|
418 FROM cw_CWEType AS _X, cw_CWGroup AS _Y |
|
419 WHERE _X.cw_name=CWGroup AND _Y.cw_eid IN(1, 2, 3) AND NOT (EXISTS(SELECT 1 FROM read_permission_relation AS rel_read_permission0 WHERE rel_read_permission0.eid_from=_X.cw_eid AND rel_read_permission0.eid_to=_Y.cw_eid)) |
|
420 UNION |
|
421 SELECT DISTINCT _X.cw_eid, _Y.cw_eid |
|
422 FROM cw_CWEType AS _X, cw_RQLExpression AS _Y |
|
423 WHERE _X.cw_name=CWGroup AND _Y.cw_eid IN(1, 2, 3) AND NOT (EXISTS(SELECT 1 FROM read_permission_relation AS rel_read_permission0 WHERE rel_read_permission0.eid_from=_X.cw_eid AND rel_read_permission0.eid_to=_Y.cw_eid))'''), |
|
424 |
|
425 # should generate the same query as above |
|
426 ('DISTINCT Any X,Y WHERE X name "CWGroup", X is CWEType, Y eid IN(1, 2, 3), NOT X read_permission Y', |
|
427 '''SELECT DISTINCT _X.cw_eid, _Y.cw_eid |
|
428 FROM cw_CWEType AS _X, cw_CWGroup AS _Y |
|
429 WHERE _X.cw_name=CWGroup AND _Y.cw_eid IN(1, 2, 3) AND NOT (EXISTS(SELECT 1 FROM read_permission_relation AS rel_read_permission0 WHERE rel_read_permission0.eid_from=_X.cw_eid AND rel_read_permission0.eid_to=_Y.cw_eid)) |
|
430 UNION |
|
431 SELECT DISTINCT _X.cw_eid, _Y.cw_eid |
|
432 FROM cw_CWEType AS _X, cw_RQLExpression AS _Y |
|
433 WHERE _X.cw_name=CWGroup AND _Y.cw_eid IN(1, 2, 3) AND NOT (EXISTS(SELECT 1 FROM read_permission_relation AS rel_read_permission0 WHERE rel_read_permission0.eid_from=_X.cw_eid AND rel_read_permission0.eid_to=_Y.cw_eid))'''), |
|
434 |
|
435 # neged relation, can't be inveriant |
|
436 ('Any X,Y WHERE X name "CWGroup", X is CWEType, Y eid IN(1, 2, 3), NOT X read_permission Y', |
|
437 '''SELECT _X.cw_eid, _Y.cw_eid |
|
438 FROM cw_CWEType AS _X, cw_CWGroup AS _Y |
|
439 WHERE _X.cw_name=CWGroup AND _Y.cw_eid IN(1, 2, 3) AND NOT (EXISTS(SELECT 1 FROM read_permission_relation AS rel_read_permission0 WHERE rel_read_permission0.eid_from=_X.cw_eid AND rel_read_permission0.eid_to=_Y.cw_eid)) |
|
440 UNION ALL |
|
441 SELECT _X.cw_eid, _Y.cw_eid |
|
442 FROM cw_CWEType AS _X, cw_RQLExpression AS _Y |
|
443 WHERE _X.cw_name=CWGroup AND _Y.cw_eid IN(1, 2, 3) AND NOT (EXISTS(SELECT 1 FROM read_permission_relation AS rel_read_permission0 WHERE rel_read_permission0.eid_from=_X.cw_eid AND rel_read_permission0.eid_to=_Y.cw_eid))'''), |
|
444 |
|
445 ('Any MAX(X)+MIN(X), N GROUPBY N WHERE X name N, X is IN (Basket, Folder, Tag);', |
|
446 '''SELECT (MAX(T1.C0) + MIN(T1.C0)), T1.C1 FROM (SELECT _X.cw_eid AS C0, _X.cw_name AS C1 |
|
447 FROM cw_Basket AS _X |
|
448 UNION ALL |
|
449 SELECT _X.cw_eid AS C0, _X.cw_name AS C1 |
|
450 FROM cw_Folder AS _X |
|
451 UNION ALL |
|
452 SELECT _X.cw_eid AS C0, _X.cw_name AS C1 |
|
453 FROM cw_Tag AS _X) AS T1 |
|
454 GROUP BY T1.C1'''), |
|
455 |
|
456 ('Any MAX(X)+MIN(LENGTH(D)), N GROUPBY N ORDERBY 1, N, DF WHERE X data_name N, X data D, X data_format DF;', |
|
457 '''SELECT (MAX(_X.cw_eid) + MIN(LENGTH(_X.cw_data))), _X.cw_data_name |
|
458 FROM cw_File AS _X |
|
459 GROUP BY _X.cw_data_name,_X.cw_data_format |
|
460 ORDER BY 1,2,_X.cw_data_format'''), |
|
461 |
|
462 # ambiguity in EXISTS() -> should union the sub-query |
|
463 ('Any T WHERE T is Tag, NOT T name in ("t1", "t2"), EXISTS(T tags X, X is IN (CWUser, CWGroup))', |
|
464 '''SELECT _T.cw_eid |
|
465 FROM cw_Tag AS _T |
|
466 WHERE NOT (_T.cw_name IN(t1, t2)) AND EXISTS(SELECT 1 FROM cw_CWGroup AS _X, tags_relation AS rel_tags0 WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid UNION SELECT 1 FROM cw_CWUser AS _X, tags_relation AS rel_tags1 WHERE rel_tags1.eid_from=_T.cw_eid AND rel_tags1.eid_to=_X.cw_eid)'''), |
|
467 |
|
468 # must not use a relation in EXISTS scope to inline a variable |
|
469 ('Any U WHERE U eid IN (1,2), EXISTS(X owned_by U)', |
|
470 '''SELECT _U.cw_eid |
|
471 FROM cw_CWUser AS _U |
|
472 WHERE _U.cw_eid IN(1, 2) AND EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0 WHERE rel_owned_by0.eid_to=_U.cw_eid)'''), |
|
473 |
|
474 ('Any U WHERE EXISTS(U eid IN (1,2), X owned_by U)', |
|
475 '''SELECT _U.cw_eid |
|
476 FROM cw_CWUser AS _U |
|
477 WHERE EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0 WHERE _U.cw_eid IN(1, 2) AND rel_owned_by0.eid_to=_U.cw_eid)'''), |
|
478 |
|
479 ('Any COUNT(U) WHERE EXISTS (P owned_by U, P is IN (Note, Affaire))', |
|
480 '''SELECT COUNT(_U.cw_eid) |
|
481 FROM cw_CWUser AS _U |
|
482 WHERE EXISTS(SELECT 1 FROM cw_Affaire AS _P, owned_by_relation AS rel_owned_by0 WHERE rel_owned_by0.eid_from=_P.cw_eid AND rel_owned_by0.eid_to=_U.cw_eid UNION SELECT 1 FROM cw_Note AS _P, owned_by_relation AS rel_owned_by1 WHERE rel_owned_by1.eid_from=_P.cw_eid AND rel_owned_by1.eid_to=_U.cw_eid)'''), |
|
483 |
|
484 ('Any MAX(X)', |
|
485 '''SELECT MAX(_X.eid) |
|
486 FROM entities AS _X'''), |
|
487 |
|
488 ('Any MAX(X) WHERE X is Note', |
|
489 '''SELECT MAX(_X.cw_eid) |
|
490 FROM cw_Note AS _X'''), |
|
491 |
|
492 ('Any X WHERE X eid > 12', |
|
493 '''SELECT _X.eid |
|
494 FROM entities AS _X |
|
495 WHERE _X.eid>12'''), |
|
496 |
|
497 ('Any X WHERE X eid > 12, X is Note', |
|
498 """SELECT _X.eid |
|
499 FROM entities AS _X |
|
500 WHERE _X.type='Note' AND _X.eid>12"""), |
|
501 |
|
502 ('Any X, T WHERE X eid > 12, X title T, X is IN (Bookmark, Card)', |
|
503 """SELECT _X.cw_eid, _X.cw_title |
|
504 FROM cw_Bookmark AS _X |
|
505 WHERE _X.cw_eid>12 |
|
506 UNION ALL |
|
507 SELECT _X.cw_eid, _X.cw_title |
|
508 FROM cw_Card AS _X |
|
509 WHERE _X.cw_eid>12"""), |
|
510 |
|
511 ('Any X', |
|
512 '''SELECT _X.eid |
|
513 FROM entities AS _X'''), |
|
514 |
|
515 ('Any X GROUPBY X WHERE X eid 12', |
|
516 '''SELECT 12'''), |
|
517 |
|
518 ('Any X GROUPBY X ORDERBY Y WHERE X eid 12, X login Y', |
|
519 '''SELECT _X.cw_eid |
|
520 FROM cw_CWUser AS _X |
|
521 WHERE _X.cw_eid=12 |
|
522 GROUP BY _X.cw_eid,_X.cw_login |
|
523 ORDER BY _X.cw_login'''), |
|
524 |
|
525 ('Any U,COUNT(X) GROUPBY U WHERE U eid 12, X owned_by U HAVING COUNT(X) > 10', |
|
526 '''SELECT rel_owned_by0.eid_to, COUNT(rel_owned_by0.eid_from) |
|
527 FROM owned_by_relation AS rel_owned_by0 |
|
528 WHERE rel_owned_by0.eid_to=12 |
|
529 GROUP BY rel_owned_by0.eid_to |
|
530 HAVING COUNT(rel_owned_by0.eid_from)>10'''), |
|
531 |
|
532 |
|
533 ("Any X WHERE X eid 0, X test TRUE", |
|
534 '''SELECT _X.cw_eid |
|
535 FROM cw_Personne AS _X |
|
536 WHERE _X.cw_eid=0 AND _X.cw_test=True'''), |
|
537 |
|
538 ('Any 1 WHERE X in_group G, X is CWUser', |
|
539 '''SELECT 1 |
|
540 FROM in_group_relation AS rel_in_group0'''), |
|
541 |
|
542 ('CWEType X WHERE X name CV, X description V HAVING NOT V=CV AND NOT V = "parent"', |
|
543 '''SELECT _X.cw_eid |
|
544 FROM cw_CWEType AS _X |
|
545 WHERE NOT (EXISTS(SELECT 1 WHERE _X.cw_description=parent)) AND NOT (EXISTS(SELECT 1 WHERE _X.cw_description=_X.cw_name))'''), |
|
546 ('CWEType X WHERE X name CV, X description V HAVING V!=CV AND V != "parent"', |
|
547 '''SELECT _X.cw_eid |
|
548 FROM cw_CWEType AS _X |
|
549 WHERE _X.cw_description!=parent AND _X.cw_description!=_X.cw_name'''), |
|
550 |
|
551 ('DISTINCT Any X, SUM(C) GROUPBY X ORDERBY SUM(C) DESC WHERE H todo_by X, H duration C', |
|
552 '''SELECT DISTINCT rel_todo_by0.eid_to, SUM(_H.cw_duration) |
|
553 FROM cw_Affaire AS _H, todo_by_relation AS rel_todo_by0 |
|
554 WHERE rel_todo_by0.eid_from=_H.cw_eid |
|
555 GROUP BY rel_todo_by0.eid_to |
|
556 ORDER BY 2 DESC'''), |
|
557 |
|
558 ('Any R2 WHERE R2 concerne R, R eid RE, R2 eid > RE', |
|
559 '''SELECT _R2.eid |
|
560 FROM concerne_relation AS rel_concerne0, entities AS _R2 |
|
561 WHERE _R2.eid=rel_concerne0.eid_from AND _R2.eid>rel_concerne0.eid_to'''), |
|
562 |
|
563 ('Note X WHERE X eid IN (999998, 999999), NOT X cw_source Y', |
|
564 '''SELECT _X.cw_eid |
|
565 FROM cw_Note AS _X |
|
566 WHERE _X.cw_eid IN(999998, 999999) AND NOT (EXISTS(SELECT 1 FROM cw_source_relation AS rel_cw_source0 WHERE rel_cw_source0.eid_from=_X.cw_eid))'''), |
|
567 |
|
568 # Test for https://www.cubicweb.org/ticket/5503548 |
|
569 ('''Any X |
|
570 WHERE X is CWSourceSchemaConfig, |
|
571 EXISTS(X created_by U, U login L), |
|
572 X cw_schema X_CW_SCHEMA, |
|
573 X owned_by X_OWNED_BY? |
|
574 ''', '''SELECT _X.cw_eid |
|
575 FROM cw_CWSourceSchemaConfig AS _X LEFT OUTER JOIN owned_by_relation AS rel_owned_by1 ON (rel_owned_by1.eid_from=_X.cw_eid) |
|
576 WHERE EXISTS(SELECT 1 FROM created_by_relation AS rel_created_by0, cw_CWUser AS _U WHERE rel_created_by0.eid_from=_X.cw_eid AND rel_created_by0.eid_to=_U.cw_eid) AND _X.cw_cw_schema IS NOT NULL |
|
577 ''') |
|
578 ] |
|
579 |
|
580 ADVANCED_WITH_GROUP_CONCAT = [ |
|
581 ("Any X,GROUP_CONCAT(TN) GROUPBY X ORDERBY XN WHERE T tags X, X name XN, T name TN, X is CWGroup", |
|
582 '''SELECT _X.cw_eid, GROUP_CONCAT(_T.cw_name) |
|
583 FROM cw_CWGroup AS _X, cw_Tag AS _T, tags_relation AS rel_tags0 |
|
584 WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid |
|
585 GROUP BY _X.cw_eid,_X.cw_name |
|
586 ORDER BY _X.cw_name'''), |
|
587 |
|
588 ("Any X,GROUP_CONCAT(TN) GROUPBY X ORDERBY XN WHERE T tags X, X name XN, T name TN", |
|
589 '''SELECT T1.C0, GROUP_CONCAT(T1.C1) FROM (SELECT _X.cw_eid AS C0, _T.cw_name AS C1, _X.cw_name AS C2 |
|
590 FROM cw_CWGroup AS _X, cw_Tag AS _T, tags_relation AS rel_tags0 |
|
591 WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid |
|
592 UNION ALL |
|
593 SELECT _X.cw_eid AS C0, _T.cw_name AS C1, _X.cw_name AS C2 |
|
594 FROM cw_State AS _X, cw_Tag AS _T, tags_relation AS rel_tags0 |
|
595 WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid |
|
596 UNION ALL |
|
597 SELECT _X.cw_eid AS C0, _T.cw_name AS C1, _X.cw_name AS C2 |
|
598 FROM cw_Tag AS _T, cw_Tag AS _X, tags_relation AS rel_tags0 |
|
599 WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid) AS T1 |
|
600 GROUP BY T1.C0,T1.C2 |
|
601 ORDER BY T1.C2'''), |
|
602 |
|
603 ] |
|
604 |
|
605 ADVANCED_WITH_LIMIT_OR_ORDERBY = [ |
|
606 ('Any COUNT(S),CS GROUPBY CS ORDERBY 1 DESC LIMIT 10 WHERE S is Affaire, C is Societe, S concerne C, C nom CS, (EXISTS(S owned_by 1)) OR (EXISTS(S documented_by N, N title "published"))', |
|
607 '''SELECT COUNT(rel_concerne0.eid_from), _C.cw_nom |
|
608 FROM concerne_relation AS rel_concerne0, cw_Societe AS _C |
|
609 WHERE rel_concerne0.eid_to=_C.cw_eid AND ((EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by1 WHERE rel_concerne0.eid_from=rel_owned_by1.eid_from AND rel_owned_by1.eid_to=1)) OR (EXISTS(SELECT 1 FROM cw_Card AS _N, documented_by_relation AS rel_documented_by2 WHERE rel_concerne0.eid_from=rel_documented_by2.eid_from AND rel_documented_by2.eid_to=_N.cw_eid AND _N.cw_title=published))) |
|
610 GROUP BY _C.cw_nom |
|
611 ORDER BY 1 DESC |
|
612 LIMIT 10'''), |
|
613 ('DISTINCT Any S ORDERBY stockproc(SI) WHERE NOT S ecrit_par O, S para SI', |
|
614 '''SELECT T1.C0 FROM (SELECT DISTINCT _S.cw_eid AS C0, STOCKPROC(_S.cw_para) AS C1 |
|
615 FROM cw_Note AS _S |
|
616 WHERE _S.cw_ecrit_par IS NULL |
|
617 ORDER BY 2) AS T1'''), |
|
618 |
|
619 ('DISTINCT Any MAX(X)+MIN(LENGTH(D)), N GROUPBY N ORDERBY 2, DF WHERE X data_name N, X data D, X data_format DF;', |
|
620 '''SELECT T1.C0,T1.C1 FROM (SELECT DISTINCT (MAX(_X.cw_eid) + MIN(LENGTH(_X.cw_data))) AS C0, _X.cw_data_name AS C1, _X.cw_data_format AS C2 |
|
621 FROM cw_File AS _X |
|
622 GROUP BY _X.cw_data_name,_X.cw_data_format |
|
623 ORDER BY 2,3) AS T1 |
|
624 '''), |
|
625 |
|
626 ('DISTINCT Any X ORDERBY stockproc(X) WHERE U login X', |
|
627 '''SELECT T1.C0 FROM (SELECT DISTINCT _U.cw_login AS C0, STOCKPROC(_U.cw_login) AS C1 |
|
628 FROM cw_CWUser AS _U |
|
629 ORDER BY 2) AS T1'''), |
|
630 |
|
631 ('DISTINCT Any X ORDERBY Y WHERE B bookmarked_by X, X login Y', |
|
632 '''SELECT T1.C0 FROM (SELECT DISTINCT _X.cw_eid AS C0, _X.cw_login AS C1 |
|
633 FROM bookmarked_by_relation AS rel_bookmarked_by0, cw_CWUser AS _X |
|
634 WHERE rel_bookmarked_by0.eid_to=_X.cw_eid |
|
635 ORDER BY 2) AS T1'''), |
|
636 |
|
637 ('DISTINCT Any X ORDERBY SN WHERE X in_state S, S name SN', |
|
638 '''SELECT T1.C0 FROM (SELECT DISTINCT _X.cw_eid AS C0, _S.cw_name AS C1 |
|
639 FROM cw_Affaire AS _X, cw_State AS _S |
|
640 WHERE _X.cw_in_state=_S.cw_eid |
|
641 UNION |
|
642 SELECT DISTINCT _X.cw_eid AS C0, _S.cw_name AS C1 |
|
643 FROM cw_CWUser AS _X, cw_State AS _S |
|
644 WHERE _X.cw_in_state=_S.cw_eid |
|
645 UNION |
|
646 SELECT DISTINCT _X.cw_eid AS C0, _S.cw_name AS C1 |
|
647 FROM cw_Note AS _X, cw_State AS _S |
|
648 WHERE _X.cw_in_state=_S.cw_eid |
|
649 ORDER BY 2) AS T1'''), |
|
650 |
|
651 ('Any O,AA,AB,AC ORDERBY AC DESC ' |
|
652 'WHERE NOT S use_email O, S eid 1, O is EmailAddress, O address AA, O alias AB, O modification_date AC, ' |
|
653 'EXISTS(A use_email O, EXISTS(A identity B, NOT B in_group D, D name "guests", D is CWGroup), A is CWUser), B eid 2', |
|
654 '''SELECT _O.cw_eid, _O.cw_address, _O.cw_alias, _O.cw_modification_date |
|
655 FROM cw_EmailAddress AS _O |
|
656 WHERE NOT (EXISTS(SELECT 1 FROM use_email_relation AS rel_use_email0 WHERE rel_use_email0.eid_from=1 AND rel_use_email0.eid_to=_O.cw_eid)) AND EXISTS(SELECT 1 FROM use_email_relation AS rel_use_email1 WHERE rel_use_email1.eid_to=_O.cw_eid AND EXISTS(SELECT 1 FROM cw_CWGroup AS _D WHERE rel_use_email1.eid_from=2 AND NOT (EXISTS(SELECT 1 FROM in_group_relation AS rel_in_group2 WHERE rel_in_group2.eid_from=2 AND rel_in_group2.eid_to=_D.cw_eid)) AND _D.cw_name=guests)) |
|
657 ORDER BY 4 DESC'''), |
|
658 |
|
659 |
|
660 ] |
|
661 |
|
662 MULTIPLE_SEL = [ |
|
663 ("DISTINCT Any X,Y where P is Personne, P nom X , P prenom Y;", |
|
664 '''SELECT DISTINCT _P.cw_nom, _P.cw_prenom |
|
665 FROM cw_Personne AS _P'''), |
|
666 ("Any X,Y where P is Personne, P nom X , P prenom Y, not P nom NULL;", |
|
667 '''SELECT _P.cw_nom, _P.cw_prenom |
|
668 FROM cw_Personne AS _P |
|
669 WHERE NOT (_P.cw_nom IS NULL)'''), |
|
670 ("Personne X,Y where X nom NX, Y nom NX, X eid XE, not Y eid XE", |
|
671 '''SELECT _X.cw_eid, _Y.cw_eid |
|
672 FROM cw_Personne AS _X, cw_Personne AS _Y |
|
673 WHERE _Y.cw_nom=_X.cw_nom AND NOT (_Y.cw_eid=_X.cw_eid)'''), |
|
674 |
|
675 ('Any X,Y WHERE X is Personne, Y is Personne, X nom XD, Y nom XD, X eid Z, Y eid > Z', |
|
676 '''SELECT _X.cw_eid, _Y.cw_eid |
|
677 FROM cw_Personne AS _X, cw_Personne AS _Y |
|
678 WHERE _Y.cw_nom=_X.cw_nom AND _Y.cw_eid>_X.cw_eid'''), |
|
679 ] |
|
680 |
|
681 |
|
682 NEGATIONS = [ |
|
683 |
|
684 ("Personne X WHERE NOT X evaluee Y;", |
|
685 '''SELECT _X.cw_eid |
|
686 FROM cw_Personne AS _X |
|
687 WHERE NOT (EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=_X.cw_eid))'''), |
|
688 |
|
689 ("Note N WHERE NOT X evaluee N, X eid 0", |
|
690 '''SELECT _N.cw_eid |
|
691 FROM cw_Note AS _N |
|
692 WHERE NOT (EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=0 AND rel_evaluee0.eid_to=_N.cw_eid))'''), |
|
693 |
|
694 ('Any X WHERE NOT X travaille S, X is Personne', |
|
695 '''SELECT _X.cw_eid |
|
696 FROM cw_Personne AS _X |
|
697 WHERE NOT (EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_from=_X.cw_eid))'''), |
|
698 |
|
699 ("Personne P where NOT P concerne A", |
|
700 '''SELECT _P.cw_eid |
|
701 FROM cw_Personne AS _P |
|
702 WHERE NOT (EXISTS(SELECT 1 FROM concerne_relation AS rel_concerne0 WHERE rel_concerne0.eid_from=_P.cw_eid))'''), |
|
703 |
|
704 ("Affaire A where not P concerne A", |
|
705 '''SELECT _A.cw_eid |
|
706 FROM cw_Affaire AS _A |
|
707 WHERE NOT (EXISTS(SELECT 1 FROM concerne_relation AS rel_concerne0 WHERE rel_concerne0.eid_to=_A.cw_eid))'''), |
|
708 ("Personne P where not P concerne A, A sujet ~= 'TEST%'", |
|
709 '''SELECT _P.cw_eid |
|
710 FROM cw_Affaire AS _A, cw_Personne AS _P |
|
711 WHERE NOT (EXISTS(SELECT 1 FROM concerne_relation AS rel_concerne0 WHERE rel_concerne0.eid_from=_P.cw_eid AND rel_concerne0.eid_to=_A.cw_eid)) AND _A.cw_sujet ILIKE TEST%'''), |
|
712 |
|
713 ('Any S WHERE NOT T eid 28258, T tags S', |
|
714 '''SELECT rel_tags0.eid_to |
|
715 FROM tags_relation AS rel_tags0 |
|
716 WHERE NOT (rel_tags0.eid_from=28258)'''), |
|
717 |
|
718 ('Any S WHERE T is Tag, T name TN, NOT T eid 28258, T tags S, S name SN', |
|
719 '''SELECT _S.cw_eid |
|
720 FROM cw_CWGroup AS _S, cw_Tag AS _T, tags_relation AS rel_tags0 |
|
721 WHERE NOT (_T.cw_eid=28258) AND rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_S.cw_eid |
|
722 UNION ALL |
|
723 SELECT _S.cw_eid |
|
724 FROM cw_State AS _S, cw_Tag AS _T, tags_relation AS rel_tags0 |
|
725 WHERE NOT (_T.cw_eid=28258) AND rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_S.cw_eid |
|
726 UNION ALL |
|
727 SELECT _S.cw_eid |
|
728 FROM cw_Tag AS _S, cw_Tag AS _T, tags_relation AS rel_tags0 |
|
729 WHERE NOT (_T.cw_eid=28258) AND rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_S.cw_eid'''), |
|
730 |
|
731 ('Any X,Y WHERE X created_by Y, X eid 5, NOT Y eid 6', |
|
732 '''SELECT 5, rel_created_by0.eid_to |
|
733 FROM created_by_relation AS rel_created_by0 |
|
734 WHERE rel_created_by0.eid_from=5 AND NOT (rel_created_by0.eid_to=6)'''), |
|
735 |
|
736 ('Note X WHERE NOT Y evaluee X', |
|
737 '''SELECT _X.cw_eid |
|
738 FROM cw_Note AS _X |
|
739 WHERE NOT (EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_to=_X.cw_eid))'''), |
|
740 |
|
741 ('Any Y WHERE NOT Y evaluee X', |
|
742 '''SELECT _Y.cw_eid |
|
743 FROM cw_CWUser AS _Y |
|
744 WHERE NOT (EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=_Y.cw_eid)) |
|
745 UNION ALL |
|
746 SELECT _Y.cw_eid |
|
747 FROM cw_Division AS _Y |
|
748 WHERE NOT (EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=_Y.cw_eid)) |
|
749 UNION ALL |
|
750 SELECT _Y.cw_eid |
|
751 FROM cw_Personne AS _Y |
|
752 WHERE NOT (EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=_Y.cw_eid)) |
|
753 UNION ALL |
|
754 SELECT _Y.cw_eid |
|
755 FROM cw_Societe AS _Y |
|
756 WHERE NOT (EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=_Y.cw_eid)) |
|
757 UNION ALL |
|
758 SELECT _Y.cw_eid |
|
759 FROM cw_SubDivision AS _Y |
|
760 WHERE NOT (EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=_Y.cw_eid))'''), |
|
761 |
|
762 ('Any X WHERE NOT Y evaluee X, Y is CWUser', |
|
763 '''SELECT _X.cw_eid |
|
764 FROM cw_Note AS _X |
|
765 WHERE NOT (EXISTS(SELECT 1 FROM cw_CWUser AS _Y, evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=_Y.cw_eid AND rel_evaluee0.eid_to=_X.cw_eid))'''), |
|
766 |
|
767 ('Any X,RT WHERE X relation_type RT, NOT X is CWAttribute', |
|
768 '''SELECT _X.cw_eid, _X.cw_relation_type |
|
769 FROM cw_CWRelation AS _X |
|
770 WHERE _X.cw_relation_type IS NOT NULL'''), |
|
771 |
|
772 ('Any K,V WHERE P is CWProperty, P pkey K, P value V, NOT P for_user U', |
|
773 '''SELECT _P.cw_pkey, _P.cw_value |
|
774 FROM cw_CWProperty AS _P |
|
775 WHERE _P.cw_for_user IS NULL'''), |
|
776 |
|
777 ('Any S WHERE NOT X in_state S, X is IN(Affaire, CWUser)', |
|
778 '''SELECT _S.cw_eid |
|
779 FROM cw_State AS _S |
|
780 WHERE NOT (EXISTS(SELECT 1 FROM cw_Affaire AS _X WHERE _X.cw_in_state=_S.cw_eid UNION SELECT 1 FROM cw_CWUser AS _X WHERE _X.cw_in_state=_S.cw_eid))'''), |
|
781 |
|
782 ('Any S WHERE NOT(X in_state S, S name "somename"), X is CWUser', |
|
783 '''SELECT _S.cw_eid |
|
784 FROM cw_State AS _S |
|
785 WHERE NOT (EXISTS(SELECT 1 FROM cw_CWUser AS _X WHERE _X.cw_in_state=_S.cw_eid AND _S.cw_name=somename))'''), |
|
786 ] |
|
787 |
|
788 HAS_TEXT_LG_INDEXER = [ |
|
789 ('Any X WHERE X has_text "toto tata"', |
|
790 """SELECT DISTINCT appears0.uid |
|
791 FROM appears AS appears0 |
|
792 WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata'))"""), |
|
793 ('Personne X WHERE X has_text "toto tata"', |
|
794 """SELECT DISTINCT _X.eid |
|
795 FROM appears AS appears0, entities AS _X |
|
796 WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata')) AND appears0.uid=_X.eid AND _X.type='Personne'"""), |
|
797 ('Personne X WHERE X has_text %(text)s', |
|
798 """SELECT DISTINCT _X.eid |
|
799 FROM appears AS appears0, entities AS _X |
|
800 WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('hip', 'hop', 'momo')) AND appears0.uid=_X.eid AND _X.type='Personne' |
|
801 """), |
|
802 ('Any X WHERE X has_text "toto tata", X name "tutu", X is IN (Basket,Folder)', |
|
803 """SELECT DISTINCT _X.cw_eid |
|
804 FROM appears AS appears0, cw_Basket AS _X |
|
805 WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata')) AND appears0.uid=_X.cw_eid AND _X.cw_name=tutu |
|
806 UNION |
|
807 SELECT DISTINCT _X.cw_eid |
|
808 FROM appears AS appears0, cw_Folder AS _X |
|
809 WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata')) AND appears0.uid=_X.cw_eid AND _X.cw_name=tutu""") |
|
810 ] |
|
811 |
|
812 |
|
813 |
|
814 # XXXFIXME fail |
|
815 # ('Any X,RT WHERE X relation_type RT?, NOT X is CWAttribute', |
|
816 # '''SELECT _X.cw_eid, _X.cw_relation_type |
|
817 # FROM cw_CWRelation AS _X'''), |
|
818 |
|
819 |
|
820 OUTER_JOIN = [ |
|
821 |
|
822 ('Any U,G WHERE U login L, G name L?, G is CWGroup', |
|
823 '''SELECT _U.cw_eid, _G.cw_eid |
|
824 FROM cw_CWUser AS _U LEFT OUTER JOIN cw_CWGroup AS _G ON (_G.cw_name=_U.cw_login)'''), |
|
825 |
|
826 ('Any X,S WHERE X travaille S?', |
|
827 '''SELECT _X.cw_eid, rel_travaille0.eid_to |
|
828 FROM cw_Personne AS _X LEFT OUTER JOIN travaille_relation AS rel_travaille0 ON (rel_travaille0.eid_from=_X.cw_eid)''' |
|
829 ), |
|
830 ('Any S,X WHERE X? travaille S, S is Societe', |
|
831 '''SELECT _S.cw_eid, rel_travaille0.eid_from |
|
832 FROM cw_Societe AS _S LEFT OUTER JOIN travaille_relation AS rel_travaille0 ON (rel_travaille0.eid_to=_S.cw_eid)''' |
|
833 ), |
|
834 |
|
835 ('Any N,A WHERE N inline1 A?', |
|
836 '''SELECT _N.cw_eid, _N.cw_inline1 |
|
837 FROM cw_Note AS _N'''), |
|
838 |
|
839 ('Any SN WHERE X from_state S?, S name SN', |
|
840 '''SELECT _S.cw_name |
|
841 FROM cw_TrInfo AS _X LEFT OUTER JOIN cw_State AS _S ON (_X.cw_from_state=_S.cw_eid)''' |
|
842 ), |
|
843 |
|
844 ('Any A,N WHERE N? inline1 A', |
|
845 '''SELECT _A.cw_eid, _N.cw_eid |
|
846 FROM cw_Affaire AS _A LEFT OUTER JOIN cw_Note AS _N ON (_N.cw_inline1=_A.cw_eid)''' |
|
847 ), |
|
848 |
|
849 ('Any A,B,C,D,E,F,G WHERE A eid 12,A creation_date B,A modification_date C,A comment D,A from_state E?,A to_state F?,A wf_info_for G?', |
|
850 '''SELECT _A.cw_eid, _A.cw_creation_date, _A.cw_modification_date, _A.cw_comment, _A.cw_from_state, _A.cw_to_state, _A.cw_wf_info_for |
|
851 FROM cw_TrInfo AS _A |
|
852 WHERE _A.cw_eid=12'''), |
|
853 |
|
854 ('Any FS,TS,C,D,U ORDERBY D DESC WHERE WF wf_info_for X,WF from_state FS?, WF to_state TS, WF comment C,WF creation_date D, WF owned_by U, X eid 1', |
|
855 '''SELECT _WF.cw_from_state, _WF.cw_to_state, _WF.cw_comment, _WF.cw_creation_date, rel_owned_by0.eid_to |
|
856 FROM cw_TrInfo AS _WF, owned_by_relation AS rel_owned_by0 |
|
857 WHERE _WF.cw_wf_info_for=1 AND _WF.cw_to_state IS NOT NULL AND rel_owned_by0.eid_from=_WF.cw_eid |
|
858 ORDER BY 4 DESC'''), |
|
859 |
|
860 ('Any X WHERE X is Affaire, S is Societe, EXISTS(X owned_by U OR (X concerne S?, S owned_by U))', |
|
861 '''SELECT _X.cw_eid |
|
862 FROM cw_Affaire AS _X |
|
863 WHERE EXISTS(SELECT 1 FROM cw_CWUser AS _U, owned_by_relation AS rel_owned_by0, owned_by_relation AS rel_owned_by2, cw_Affaire AS _A LEFT OUTER JOIN concerne_relation AS rel_concerne1 ON (rel_concerne1.eid_from=_A.cw_eid) LEFT OUTER JOIN cw_Societe AS _S ON (rel_concerne1.eid_to=_S.cw_eid) WHERE ((rel_owned_by0.eid_from=_A.cw_eid AND rel_owned_by0.eid_to=_U.cw_eid) OR (rel_owned_by2.eid_from=_S.cw_eid AND rel_owned_by2.eid_to=_U.cw_eid)) AND _X.cw_eid=_A.cw_eid)'''), |
|
864 |
|
865 ('Any C,M WHERE C travaille G?, G evaluee M?, G is Societe', |
|
866 '''SELECT _C.cw_eid, rel_evaluee1.eid_to |
|
867 FROM cw_Personne AS _C LEFT OUTER JOIN travaille_relation AS rel_travaille0 ON (rel_travaille0.eid_from=_C.cw_eid) LEFT OUTER JOIN cw_Societe AS _G ON (rel_travaille0.eid_to=_G.cw_eid) LEFT OUTER JOIN evaluee_relation AS rel_evaluee1 ON (rel_evaluee1.eid_from=_G.cw_eid)''' |
|
868 ), |
|
869 |
|
870 ('Any A,C WHERE A documented_by C?, (C is NULL) OR (EXISTS(C require_permission F, ' |
|
871 'F name "read", F require_group E, U in_group E)), U eid 1', |
|
872 '''SELECT _A.cw_eid, rel_documented_by0.eid_to |
|
873 FROM cw_Affaire AS _A LEFT OUTER JOIN documented_by_relation AS rel_documented_by0 ON (rel_documented_by0.eid_from=_A.cw_eid) |
|
874 WHERE ((rel_documented_by0.eid_to IS NULL) OR (EXISTS(SELECT 1 FROM cw_CWPermission AS _F, in_group_relation AS rel_in_group3, require_group_relation AS rel_require_group2, require_permission_relation AS rel_require_permission1 WHERE rel_documented_by0.eid_to=rel_require_permission1.eid_from AND rel_require_permission1.eid_to=_F.cw_eid AND _F.cw_name=read AND rel_require_group2.eid_from=_F.cw_eid AND rel_in_group3.eid_to=rel_require_group2.eid_to AND rel_in_group3.eid_from=1)))'''), |
|
875 |
|
876 ("Any X WHERE X eid 12, P? connait X", |
|
877 '''SELECT _X.cw_eid |
|
878 FROM cw_Personne AS _X LEFT OUTER JOIN connait_relation AS rel_connait0 ON (rel_connait0.eid_to=_X.cw_eid) |
|
879 WHERE _X.cw_eid=12''' |
|
880 ), |
|
881 ("Any P WHERE X eid 12, P? concerne X, X todo_by S", |
|
882 '''SELECT rel_concerne1.eid_from |
|
883 FROM todo_by_relation AS rel_todo_by0 LEFT OUTER JOIN concerne_relation AS rel_concerne1 ON (rel_concerne1.eid_to=12) |
|
884 WHERE rel_todo_by0.eid_from=12''' |
|
885 ), |
|
886 |
|
887 ('Any GN, TN ORDERBY GN WHERE T tags G?, T name TN, G name GN', |
|
888 ''' |
|
889 SELECT _T0.C1, _T.cw_name |
|
890 FROM cw_Tag AS _T LEFT OUTER JOIN tags_relation AS rel_tags0 ON (rel_tags0.eid_from=_T.cw_eid) LEFT OUTER JOIN (SELECT _G.cw_eid AS C0, _G.cw_name AS C1 |
|
891 FROM cw_CWGroup AS _G |
|
892 UNION ALL |
|
893 SELECT _G.cw_eid AS C0, _G.cw_name AS C1 |
|
894 FROM cw_State AS _G |
|
895 UNION ALL |
|
896 SELECT _G.cw_eid AS C0, _G.cw_name AS C1 |
|
897 FROM cw_Tag AS _G) AS _T0 ON (rel_tags0.eid_to=_T0.C0) |
|
898 ORDER BY 1'''), |
|
899 |
|
900 |
|
901 # optional variable with additional restriction |
|
902 ('Any T,G WHERE T tags G?, G name "hop", G is CWGroup', |
|
903 '''SELECT _T.cw_eid, _G.cw_eid |
|
904 FROM cw_Tag AS _T LEFT OUTER JOIN tags_relation AS rel_tags0 ON (rel_tags0.eid_from=_T.cw_eid) LEFT OUTER JOIN cw_CWGroup AS _G ON (rel_tags0.eid_to=_G.cw_eid AND _G.cw_name=hop)'''), |
|
905 |
|
906 # optional variable with additional invariant restriction |
|
907 ('Any T,G WHERE T tags G?, G eid 12', |
|
908 '''SELECT _T.cw_eid, rel_tags0.eid_to |
|
909 FROM cw_Tag AS _T LEFT OUTER JOIN tags_relation AS rel_tags0 ON (rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=12)'''), |
|
910 |
|
911 # optional variable with additional restriction appearing before the relation |
|
912 ('Any T,G WHERE G name "hop", T tags G?, G is CWGroup', |
|
913 '''SELECT _T.cw_eid, _G.cw_eid |
|
914 FROM cw_Tag AS _T LEFT OUTER JOIN tags_relation AS rel_tags0 ON (rel_tags0.eid_from=_T.cw_eid) LEFT OUTER JOIN cw_CWGroup AS _G ON (rel_tags0.eid_to=_G.cw_eid AND _G.cw_name=hop)'''), |
|
915 |
|
916 # optional variable with additional restriction on inlined relation |
|
917 # XXX the expected result should be as the query below. So what, raise BadRQLQuery ? |
|
918 ('Any T,G,S WHERE T tags G?, G in_state S, S name "hop", G is CWUser', |
|
919 '''SELECT _T.cw_eid, _G.cw_eid, _S.cw_eid |
|
920 FROM cw_State AS _S, cw_Tag AS _T LEFT OUTER JOIN tags_relation AS rel_tags0 ON (rel_tags0.eid_from=_T.cw_eid) LEFT OUTER JOIN cw_CWUser AS _G ON (rel_tags0.eid_to=_G.cw_eid) |
|
921 WHERE _G.cw_in_state=_S.cw_eid AND _S.cw_name=hop |
|
922 '''), |
|
923 |
|
924 # optional variable with additional invariant restriction on an inlined relation |
|
925 ('Any T,G,S WHERE T tags G, G in_state S?, S eid 1, G is CWUser', |
|
926 '''SELECT rel_tags0.eid_from, _G.cw_eid, _G.cw_in_state |
|
927 FROM cw_CWUser AS _G, tags_relation AS rel_tags0 |
|
928 WHERE rel_tags0.eid_to=_G.cw_eid AND (_G.cw_in_state=1 OR _G.cw_in_state IS NULL)'''), |
|
929 |
|
930 # two optional variables with additional invariant restriction on an inlined relation |
|
931 ('Any T,G,S WHERE T tags G?, G in_state S?, S eid 1, G is CWUser', |
|
932 '''SELECT _T.cw_eid, _G.cw_eid, _G.cw_in_state |
|
933 FROM cw_Tag AS _T LEFT OUTER JOIN tags_relation AS rel_tags0 ON (rel_tags0.eid_from=_T.cw_eid) LEFT OUTER JOIN cw_CWUser AS _G ON (rel_tags0.eid_to=_G.cw_eid AND (_G.cw_in_state=1 OR _G.cw_in_state IS NULL))'''), |
|
934 |
|
935 # two optional variables with additional restriction on an inlined relation |
|
936 ('Any T,G,S WHERE T tags G?, G in_state S?, S name "hop", G is CWUser', |
|
937 '''SELECT _T.cw_eid, _G.cw_eid, _S.cw_eid |
|
938 FROM cw_Tag AS _T LEFT OUTER JOIN tags_relation AS rel_tags0 ON (rel_tags0.eid_from=_T.cw_eid) LEFT OUTER JOIN cw_CWUser AS _G ON (rel_tags0.eid_to=_G.cw_eid) LEFT OUTER JOIN cw_State AS _S ON (_G.cw_in_state=_S.cw_eid AND _S.cw_name=hop)'''), |
|
939 |
|
940 # two optional variables with additional restriction on an ambigous inlined relation |
|
941 ('Any T,G,S WHERE T tags G?, G in_state S?, S name "hop"', |
|
942 ''' |
|
943 SELECT _T.cw_eid, _T0.C0, _T0.C1 |
|
944 FROM cw_Tag AS _T LEFT OUTER JOIN tags_relation AS rel_tags0 ON (rel_tags0.eid_from=_T.cw_eid) LEFT OUTER JOIN (SELECT _G.cw_eid AS C0, _S.cw_eid AS C1 |
|
945 FROM cw_Affaire AS _G LEFT OUTER JOIN cw_State AS _S ON (_G.cw_in_state=_S.cw_eid AND _S.cw_name=hop) |
|
946 UNION ALL |
|
947 SELECT _G.cw_eid AS C0, _S.cw_eid AS C1 |
|
948 FROM cw_CWUser AS _G LEFT OUTER JOIN cw_State AS _S ON (_G.cw_in_state=_S.cw_eid AND _S.cw_name=hop) |
|
949 UNION ALL |
|
950 SELECT _G.cw_eid AS C0, _S.cw_eid AS C1 |
|
951 FROM cw_Note AS _G LEFT OUTER JOIN cw_State AS _S ON (_G.cw_in_state=_S.cw_eid AND _S.cw_name=hop)) AS _T0 ON (rel_tags0.eid_to=_T0.C0)'''), |
|
952 |
|
953 ('Any O,AD WHERE NOT S inline1 O, S eid 123, O todo_by AD?', |
|
954 '''SELECT _O.cw_eid, rel_todo_by0.eid_to |
|
955 FROM cw_Note AS _S, cw_Affaire AS _O LEFT OUTER JOIN todo_by_relation AS rel_todo_by0 ON (rel_todo_by0.eid_from=_O.cw_eid) |
|
956 WHERE (_S.cw_inline1 IS NULL OR _S.cw_inline1!=_O.cw_eid) AND _S.cw_eid=123'''), |
|
957 |
|
958 ('Any X,AE WHERE X multisource_inlined_rel S?, S ambiguous_inlined A, A modification_date AE', |
|
959 '''SELECT _X.cw_eid, _T0.C2 |
|
960 FROM cw_Card AS _X LEFT OUTER JOIN (SELECT _S.cw_eid AS C0, _A.cw_eid AS C1, _A.cw_modification_date AS C2 |
|
961 FROM cw_Affaire AS _S, cw_CWUser AS _A |
|
962 WHERE _S.cw_ambiguous_inlined=_A.cw_eid |
|
963 UNION ALL |
|
964 SELECT _S.cw_eid AS C0, _A.cw_eid AS C1, _A.cw_modification_date AS C2 |
|
965 FROM cw_CWUser AS _A, cw_Note AS _S |
|
966 WHERE _S.cw_ambiguous_inlined=_A.cw_eid) AS _T0 ON (_X.cw_multisource_inlined_rel=_T0.C0) |
|
967 UNION ALL |
|
968 SELECT _X.cw_eid, _T0.C2 |
|
969 FROM cw_Note AS _X LEFT OUTER JOIN (SELECT _S.cw_eid AS C0, _A.cw_eid AS C1, _A.cw_modification_date AS C2 |
|
970 FROM cw_Affaire AS _S, cw_CWUser AS _A |
|
971 WHERE _S.cw_ambiguous_inlined=_A.cw_eid |
|
972 UNION ALL |
|
973 SELECT _S.cw_eid AS C0, _A.cw_eid AS C1, _A.cw_modification_date AS C2 |
|
974 FROM cw_CWUser AS _A, cw_Note AS _S |
|
975 WHERE _S.cw_ambiguous_inlined=_A.cw_eid) AS _T0 ON (_X.cw_multisource_inlined_rel=_T0.C0)''' |
|
976 ), |
|
977 |
|
978 ('Any X,T,OT WHERE X tags T, OT? tags X, X is Tag, X eid 123', |
|
979 '''SELECT rel_tags0.eid_from, rel_tags0.eid_to, rel_tags1.eid_from |
|
980 FROM tags_relation AS rel_tags0 LEFT OUTER JOIN tags_relation AS rel_tags1 ON (rel_tags1.eid_to=123) |
|
981 WHERE rel_tags0.eid_from=123'''), |
|
982 |
|
983 ('Any CASE, CALIBCFG, CFG ' |
|
984 'WHERE CASE eid 1, CFG ecrit_par CASE, CALIBCFG? ecrit_par CASE', |
|
985 '''SELECT _CFG.cw_ecrit_par, _CALIBCFG.cw_eid, _CFG.cw_eid |
|
986 FROM cw_Note AS _CFG LEFT OUTER JOIN cw_Note AS _CALIBCFG ON (_CALIBCFG.cw_ecrit_par=1) |
|
987 WHERE _CFG.cw_ecrit_par=1'''), |
|
988 |
|
989 ('Any U,G WHERE U login UL, G name GL, G is CWGroup HAVING UPPER(UL)=UPPER(GL)?', |
|
990 '''SELECT _U.cw_eid, _G.cw_eid |
|
991 FROM cw_CWUser AS _U LEFT OUTER JOIN cw_CWGroup AS _G ON (UPPER(_U.cw_login)=UPPER(_G.cw_name))'''), |
|
992 |
|
993 ('Any U,G WHERE U login UL, G name GL, G is CWGroup HAVING UPPER(UL)?=UPPER(GL)', |
|
994 '''SELECT _U.cw_eid, _G.cw_eid |
|
995 FROM cw_CWGroup AS _G LEFT OUTER JOIN cw_CWUser AS _U ON (UPPER(_U.cw_login)=UPPER(_G.cw_name))'''), |
|
996 |
|
997 ('Any U,G WHERE U login UL, G name GL, G is CWGroup HAVING UPPER(UL)?=UPPER(GL)?', |
|
998 '''SELECT _U.cw_eid, _G.cw_eid |
|
999 FROM cw_CWUser AS _U FULL OUTER JOIN cw_CWGroup AS _G ON (UPPER(_U.cw_login)=UPPER(_G.cw_name))'''), |
|
1000 |
|
1001 ('Any H, COUNT(X), SUM(XCE)/1000 ' |
|
1002 'WHERE X type "0", X date XSCT, X para XCE, X? ecrit_par F, F eid 999999, F is Personne, ' |
|
1003 'DH is Affaire, DH ref H ' |
|
1004 'HAVING XSCT?=H', |
|
1005 '''SELECT _DH.cw_ref, COUNT(_X.cw_eid), (SUM(_X.cw_para) / 1000) |
|
1006 FROM cw_Affaire AS _DH LEFT OUTER JOIN cw_Note AS _X ON (_X.cw_date=_DH.cw_ref AND _X.cw_type=0 AND _X.cw_ecrit_par=999999)'''), |
|
1007 |
|
1008 ('Any C WHERE X ecrit_par C?, X? inline1 F, F eid 1, X type XT, Z is Personne, Z nom ZN HAVING ZN=XT?', |
|
1009 '''SELECT _X.cw_ecrit_par |
|
1010 FROM cw_Personne AS _Z LEFT OUTER JOIN cw_Note AS _X ON (_Z.cw_nom=_X.cw_type AND _X.cw_inline1=1)'''), |
|
1011 ] |
|
1012 |
|
1013 VIRTUAL_VARS = [ |
|
1014 |
|
1015 ('Any X WHERE X is CWUser, X creation_date > D1, Y creation_date D1, Y login "SWEB09"', |
|
1016 '''SELECT _X.cw_eid |
|
1017 FROM cw_CWUser AS _X, cw_CWUser AS _Y |
|
1018 WHERE _X.cw_creation_date>_Y.cw_creation_date AND _Y.cw_login=SWEB09'''), |
|
1019 |
|
1020 ('Any X WHERE X is CWUser, Y creation_date D1, Y login "SWEB09", X creation_date > D1', |
|
1021 '''SELECT _X.cw_eid |
|
1022 FROM cw_CWUser AS _X, cw_CWUser AS _Y |
|
1023 WHERE _Y.cw_login=SWEB09 AND _X.cw_creation_date>_Y.cw_creation_date'''), |
|
1024 |
|
1025 ('Personne P WHERE P travaille S, S tel T, S fax T, S is Societe', |
|
1026 '''SELECT rel_travaille0.eid_from |
|
1027 FROM cw_Societe AS _S, travaille_relation AS rel_travaille0 |
|
1028 WHERE rel_travaille0.eid_to=_S.cw_eid AND _S.cw_tel=_S.cw_fax'''), |
|
1029 |
|
1030 ("Personne P where X eid 0, X creation_date D, P tzdatenaiss < D, X is Affaire", |
|
1031 '''SELECT _P.cw_eid |
|
1032 FROM cw_Affaire AS _X, cw_Personne AS _P |
|
1033 WHERE _X.cw_eid=0 AND _P.cw_tzdatenaiss<_X.cw_creation_date'''), |
|
1034 |
|
1035 ("Any N,T WHERE N is Note, N type T;", |
|
1036 '''SELECT _N.cw_eid, _N.cw_type |
|
1037 FROM cw_Note AS _N'''), |
|
1038 |
|
1039 ("Personne P where X is Personne, X tel T, X fax F, P fax T+F", |
|
1040 '''SELECT _P.cw_eid |
|
1041 FROM cw_Personne AS _P, cw_Personne AS _X |
|
1042 WHERE _P.cw_fax=(_X.cw_tel + _X.cw_fax)'''), |
|
1043 |
|
1044 ("Personne P where X tel T, X fax F, P fax IN (T,F)", |
|
1045 '''SELECT _P.cw_eid |
|
1046 FROM cw_Division AS _X, cw_Personne AS _P |
|
1047 WHERE _P.cw_fax IN(_X.cw_tel, _X.cw_fax) |
|
1048 UNION ALL |
|
1049 SELECT _P.cw_eid |
|
1050 FROM cw_Personne AS _P, cw_Personne AS _X |
|
1051 WHERE _P.cw_fax IN(_X.cw_tel, _X.cw_fax) |
|
1052 UNION ALL |
|
1053 SELECT _P.cw_eid |
|
1054 FROM cw_Personne AS _P, cw_Societe AS _X |
|
1055 WHERE _P.cw_fax IN(_X.cw_tel, _X.cw_fax) |
|
1056 UNION ALL |
|
1057 SELECT _P.cw_eid |
|
1058 FROM cw_Personne AS _P, cw_SubDivision AS _X |
|
1059 WHERE _P.cw_fax IN(_X.cw_tel, _X.cw_fax)'''), |
|
1060 |
|
1061 ("Personne P where X tel T, X fax F, P fax IN (T,F,0832542332)", |
|
1062 '''SELECT _P.cw_eid |
|
1063 FROM cw_Division AS _X, cw_Personne AS _P |
|
1064 WHERE _P.cw_fax IN(_X.cw_tel, _X.cw_fax, 832542332) |
|
1065 UNION ALL |
|
1066 SELECT _P.cw_eid |
|
1067 FROM cw_Personne AS _P, cw_Personne AS _X |
|
1068 WHERE _P.cw_fax IN(_X.cw_tel, _X.cw_fax, 832542332) |
|
1069 UNION ALL |
|
1070 SELECT _P.cw_eid |
|
1071 FROM cw_Personne AS _P, cw_Societe AS _X |
|
1072 WHERE _P.cw_fax IN(_X.cw_tel, _X.cw_fax, 832542332) |
|
1073 UNION ALL |
|
1074 SELECT _P.cw_eid |
|
1075 FROM cw_Personne AS _P, cw_SubDivision AS _X |
|
1076 WHERE _P.cw_fax IN(_X.cw_tel, _X.cw_fax, 832542332)'''), |
|
1077 ] |
|
1078 |
|
1079 FUNCS = [ |
|
1080 ("Any COUNT(P) WHERE P is Personne", |
|
1081 '''SELECT COUNT(_P.cw_eid) |
|
1082 FROM cw_Personne AS _P'''), |
|
1083 ] |
|
1084 |
|
1085 INLINE = [ |
|
1086 |
|
1087 ('Any P WHERE N eid 1, N ecrit_par P, NOT P owned_by P2', |
|
1088 '''SELECT _N.cw_ecrit_par |
|
1089 FROM cw_Note AS _N |
|
1090 WHERE _N.cw_eid=1 AND _N.cw_ecrit_par IS NOT NULL AND NOT (EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0 WHERE _N.cw_ecrit_par=rel_owned_by0.eid_from))'''), |
|
1091 |
|
1092 ('Any P, L WHERE N ecrit_par P, P nom L, N eid 0', |
|
1093 '''SELECT _P.cw_eid, _P.cw_nom |
|
1094 FROM cw_Note AS _N, cw_Personne AS _P |
|
1095 WHERE _N.cw_ecrit_par=_P.cw_eid AND _N.cw_eid=0'''), |
|
1096 |
|
1097 ('Any N WHERE NOT N ecrit_par P, P nom "toto"', |
|
1098 '''SELECT _N.cw_eid |
|
1099 FROM cw_Note AS _N, cw_Personne AS _P |
|
1100 WHERE (_N.cw_ecrit_par IS NULL OR _N.cw_ecrit_par!=_P.cw_eid) AND _P.cw_nom=toto'''), |
|
1101 |
|
1102 ('Any P WHERE NOT N ecrit_par P, P nom "toto"', |
|
1103 '''SELECT _P.cw_eid |
|
1104 FROM cw_Personne AS _P |
|
1105 WHERE NOT (EXISTS(SELECT 1 FROM cw_Note AS _N WHERE _N.cw_ecrit_par=_P.cw_eid)) AND _P.cw_nom=toto'''), |
|
1106 |
|
1107 ('Any P WHERE N ecrit_par P, N eid 0', |
|
1108 '''SELECT _N.cw_ecrit_par |
|
1109 FROM cw_Note AS _N |
|
1110 WHERE _N.cw_ecrit_par IS NOT NULL AND _N.cw_eid=0'''), |
|
1111 |
|
1112 ('Any P WHERE N ecrit_par P, P is Personne, N eid 0', |
|
1113 '''SELECT _P.cw_eid |
|
1114 FROM cw_Note AS _N, cw_Personne AS _P |
|
1115 WHERE _N.cw_ecrit_par=_P.cw_eid AND _N.cw_eid=0'''), |
|
1116 |
|
1117 ('Any P WHERE NOT N ecrit_par P, P is Personne, N eid 512', |
|
1118 '''SELECT _P.cw_eid |
|
1119 FROM cw_Note AS _N, cw_Personne AS _P |
|
1120 WHERE (_N.cw_ecrit_par IS NULL OR _N.cw_ecrit_par!=_P.cw_eid) AND _N.cw_eid=512'''), |
|
1121 |
|
1122 ('Any S,ES,T WHERE S state_of ET, ET name "CWUser", ES allowed_transition T, T destination_state S', |
|
1123 # XXX "_T.cw_destination_state IS NOT NULL" could be avoided here but it's not worth it |
|
1124 '''SELECT _T.cw_destination_state, rel_allowed_transition1.eid_from, _T.cw_eid |
|
1125 FROM allowed_transition_relation AS rel_allowed_transition1, cw_Transition AS _T, cw_Workflow AS _ET, state_of_relation AS rel_state_of0 |
|
1126 WHERE _T.cw_destination_state=rel_state_of0.eid_from AND rel_state_of0.eid_to=_ET.cw_eid AND _ET.cw_name=CWUser AND rel_allowed_transition1.eid_to=_T.cw_eid AND _T.cw_destination_state IS NOT NULL'''), |
|
1127 |
|
1128 ('Any O WHERE S eid 0, S in_state O', |
|
1129 '''SELECT _S.cw_in_state |
|
1130 FROM cw_Affaire AS _S |
|
1131 WHERE _S.cw_eid=0 AND _S.cw_in_state IS NOT NULL |
|
1132 UNION ALL |
|
1133 SELECT _S.cw_in_state |
|
1134 FROM cw_CWUser AS _S |
|
1135 WHERE _S.cw_eid=0 AND _S.cw_in_state IS NOT NULL |
|
1136 UNION ALL |
|
1137 SELECT _S.cw_in_state |
|
1138 FROM cw_Note AS _S |
|
1139 WHERE _S.cw_eid=0 AND _S.cw_in_state IS NOT NULL'''), |
|
1140 |
|
1141 ('Any X WHERE NOT Y for_user X, X eid 123', |
|
1142 '''SELECT 123 |
|
1143 WHERE NOT (EXISTS(SELECT 1 FROM cw_CWProperty AS _Y WHERE _Y.cw_for_user=123))'''), |
|
1144 |
|
1145 ('DISTINCT Any X WHERE X from_entity OET, NOT X from_entity NET, OET name "Image", NET eid 1', |
|
1146 '''SELECT DISTINCT _X.cw_eid |
|
1147 FROM cw_CWAttribute AS _X, cw_CWEType AS _OET |
|
1148 WHERE _X.cw_from_entity=_OET.cw_eid AND (_X.cw_from_entity IS NULL OR _X.cw_from_entity!=1) AND _OET.cw_name=Image |
|
1149 UNION |
|
1150 SELECT DISTINCT _X.cw_eid |
|
1151 FROM cw_CWEType AS _OET, cw_CWRelation AS _X |
|
1152 WHERE _X.cw_from_entity=_OET.cw_eid AND (_X.cw_from_entity IS NULL OR _X.cw_from_entity!=1) AND _OET.cw_name=Image'''), |
|
1153 |
|
1154 ] |
|
1155 |
|
1156 INTERSECT = [ |
|
1157 ('Any SN WHERE NOT X in_state S, S name SN', |
|
1158 '''SELECT _S.cw_name |
|
1159 FROM cw_State AS _S |
|
1160 WHERE NOT (EXISTS(SELECT 1 FROM cw_Affaire AS _X WHERE _X.cw_in_state=_S.cw_eid UNION SELECT 1 FROM cw_Note AS _X WHERE _X.cw_in_state=_S.cw_eid UNION SELECT 1 FROM cw_CWUser AS _X WHERE _X.cw_in_state=_S.cw_eid))'''), |
|
1161 |
|
1162 ('Any PN WHERE NOT X travaille S, X nom PN, S is IN(Division, Societe)', |
|
1163 '''SELECT _X.cw_nom |
|
1164 FROM cw_Personne AS _X |
|
1165 WHERE NOT (EXISTS(SELECT 1 FROM cw_Division AS _S, travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_from=_X.cw_eid AND rel_travaille0.eid_to=_S.cw_eid UNION SELECT 1 FROM cw_Societe AS _S, travaille_relation AS rel_travaille1 WHERE rel_travaille1.eid_from=_X.cw_eid AND rel_travaille1.eid_to=_S.cw_eid))'''), |
|
1166 |
|
1167 ('Any PN WHERE NOT X travaille S, S nom PN, S is IN(Division, Societe)', |
|
1168 '''SELECT _S.cw_nom |
|
1169 FROM cw_Division AS _S |
|
1170 WHERE NOT (EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_to=_S.cw_eid)) |
|
1171 UNION ALL |
|
1172 SELECT _S.cw_nom |
|
1173 FROM cw_Societe AS _S |
|
1174 WHERE NOT (EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_to=_S.cw_eid))'''), |
|
1175 |
|
1176 ('Personne X WHERE NOT X travaille S, S nom "chouette"', |
|
1177 '''SELECT _X.cw_eid |
|
1178 FROM cw_Division AS _S, cw_Personne AS _X |
|
1179 WHERE NOT (EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_from=_X.cw_eid AND rel_travaille0.eid_to=_S.cw_eid)) AND _S.cw_nom=chouette |
|
1180 UNION ALL |
|
1181 SELECT _X.cw_eid |
|
1182 FROM cw_Personne AS _X, cw_Societe AS _S |
|
1183 WHERE NOT (EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_from=_X.cw_eid AND rel_travaille0.eid_to=_S.cw_eid)) AND _S.cw_nom=chouette |
|
1184 UNION ALL |
|
1185 SELECT _X.cw_eid |
|
1186 FROM cw_Personne AS _X, cw_SubDivision AS _S |
|
1187 WHERE NOT (EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_from=_X.cw_eid AND rel_travaille0.eid_to=_S.cw_eid)) AND _S.cw_nom=chouette'''), |
|
1188 |
|
1189 ('Any X WHERE X is ET, ET eid 2', |
|
1190 '''SELECT rel_is0.eid_from |
|
1191 FROM is_relation AS rel_is0 |
|
1192 WHERE rel_is0.eid_to=2'''), |
|
1193 |
|
1194 ] |
|
1195 class CWRQLTC(RQLGeneratorTC): |
|
1196 backend = 'sqlite' |
|
1197 |
|
1198 def setUp(self): |
|
1199 self.__class__.schema = schema |
|
1200 super(CWRQLTC, self).setUp() |
|
1201 |
|
1202 def test_nonregr_sol(self): |
|
1203 delete = self.rqlhelper.parse( |
|
1204 'DELETE X read_permission READ_PERMISSIONSUBJECT,X add_permission ADD_PERMISSIONSUBJECT,' |
|
1205 'X in_basket IN_BASKETSUBJECT,X delete_permission DELETE_PERMISSIONSUBJECT,' |
|
1206 'X update_permission UPDATE_PERMISSIONSUBJECT,' |
|
1207 'X created_by CREATED_BYSUBJECT,X is ISSUBJECT,X is_instance_of IS_INSTANCE_OFSUBJECT,' |
|
1208 'X owned_by OWNED_BYSUBJECT,X specializes SPECIALIZESSUBJECT,ISOBJECT is X,' |
|
1209 'SPECIALIZESOBJECT specializes X,IS_INSTANCE_OFOBJECT is_instance_of X,' |
|
1210 'TO_ENTITYOBJECT to_entity X,FROM_ENTITYOBJECT from_entity X ' |
|
1211 'WHERE X is CWEType') |
|
1212 self.rqlhelper.compute_solutions(delete) |
|
1213 def var_sols(var): |
|
1214 s = set() |
|
1215 for sol in delete.solutions: |
|
1216 s.add(sol.get(var)) |
|
1217 return s |
|
1218 self.assertEqual(var_sols('FROM_ENTITYOBJECT'), set(('CWAttribute', 'CWRelation'))) |
|
1219 self.assertEqual(var_sols('FROM_ENTITYOBJECT'), delete.defined_vars['FROM_ENTITYOBJECT'].stinfo['possibletypes']) |
|
1220 self.assertEqual(var_sols('ISOBJECT'), |
|
1221 set(x.type for x in self.schema.entities() if not x.final)) |
|
1222 self.assertEqual(var_sols('ISOBJECT'), delete.defined_vars['ISOBJECT'].stinfo['possibletypes']) |
|
1223 |
|
1224 |
|
1225 def strip(text): |
|
1226 return '\n'.join(l.strip() for l in text.strip().splitlines()) |
|
1227 |
|
1228 class PostgresSQLGeneratorTC(RQLGeneratorTC): |
|
1229 backend = 'postgres' |
|
1230 |
|
1231 def setUp(self): |
|
1232 self.__class__.schema = schema |
|
1233 super(PostgresSQLGeneratorTC, self).setUp() |
|
1234 |
|
1235 def _norm_sql(self, sql): |
|
1236 return sql.strip() |
|
1237 |
|
1238 def _check(self, rql, sql, varmap=None, args=None): |
|
1239 if args is None: |
|
1240 args = {'text': 'hip hop momo', 'eid': 12345} |
|
1241 try: |
|
1242 union = self._prepare(rql) |
|
1243 r, nargs, cbs = self.o.generate(union, args, |
|
1244 varmap=varmap) |
|
1245 args.update(nargs) |
|
1246 self.assertMultiLineEqual(strip(r % args), self._norm_sql(sql)) |
|
1247 except Exception as ex: |
|
1248 if 'r' in locals(): |
|
1249 try: |
|
1250 print((r%args).strip()) |
|
1251 except KeyError: |
|
1252 print('strange, missing substitution') |
|
1253 print(r, nargs) |
|
1254 print('!=') |
|
1255 print(sql.strip()) |
|
1256 print('RQL:', rql) |
|
1257 raise |
|
1258 |
|
1259 def _parse(self, rqls): |
|
1260 for rql, sql in rqls: |
|
1261 yield self._check, rql, sql |
|
1262 |
|
1263 def _checkall(self, rql, sql): |
|
1264 if isinstance(rql, tuple): |
|
1265 rql, args = rql |
|
1266 else: |
|
1267 args = None |
|
1268 try: |
|
1269 rqlst = self._prepare(rql) |
|
1270 r, args, cbs = self.o.generate(rqlst, args) |
|
1271 self.assertEqual((r.strip(), args), sql) |
|
1272 except Exception as ex: |
|
1273 print(rql) |
|
1274 if 'r' in locals(): |
|
1275 print(r.strip()) |
|
1276 print('!=') |
|
1277 print(sql[0].strip()) |
|
1278 raise |
|
1279 return |
|
1280 |
|
1281 def test1(self): |
|
1282 self._checkall(('Any count(RDEF) WHERE RDEF relation_type X, X eid %(x)s', {'x': None}), |
|
1283 ("""SELECT COUNT(T1.C0) FROM (SELECT _RDEF.cw_eid AS C0 |
|
1284 FROM cw_CWAttribute AS _RDEF |
|
1285 WHERE _RDEF.cw_relation_type=%(x)s |
|
1286 UNION ALL |
|
1287 SELECT _RDEF.cw_eid AS C0 |
|
1288 FROM cw_CWRelation AS _RDEF |
|
1289 WHERE _RDEF.cw_relation_type=%(x)s) AS T1""", {}), |
|
1290 ) |
|
1291 |
|
1292 def test2(self): |
|
1293 self._checkall(('Any X WHERE C comments X, C eid %(x)s', {'x': None}), |
|
1294 ('''SELECT rel_comments0.eid_to |
|
1295 FROM comments_relation AS rel_comments0 |
|
1296 WHERE rel_comments0.eid_from=%(x)s''', {}) |
|
1297 ) |
|
1298 |
|
1299 def test_cache_1(self): |
|
1300 self._check('Any X WHERE X in_basket B, B eid 12', |
|
1301 '''SELECT rel_in_basket0.eid_from |
|
1302 FROM in_basket_relation AS rel_in_basket0 |
|
1303 WHERE rel_in_basket0.eid_to=12''') |
|
1304 |
|
1305 self._check('Any X WHERE X in_basket B, B eid 12', |
|
1306 '''SELECT rel_in_basket0.eid_from |
|
1307 FROM in_basket_relation AS rel_in_basket0 |
|
1308 WHERE rel_in_basket0.eid_to=12''') |
|
1309 |
|
1310 def test_varmap1(self): |
|
1311 self._check('Any X,L WHERE X is CWUser, X in_group G, X login L, G name "users"', |
|
1312 '''SELECT T00.x, T00.l |
|
1313 FROM T00, cw_CWGroup AS _G, in_group_relation AS rel_in_group0 |
|
1314 WHERE rel_in_group0.eid_from=T00.x AND rel_in_group0.eid_to=_G.cw_eid AND _G.cw_name=users''', |
|
1315 varmap={'X': 'T00.x', 'X.login': 'T00.l'}) |
|
1316 |
|
1317 def test_varmap2(self): |
|
1318 self._check('Any X,L,GN WHERE X is CWUser, X in_group G, X login L, G name GN', |
|
1319 '''SELECT T00.x, T00.l, _G.cw_name |
|
1320 FROM T00, cw_CWGroup AS _G, in_group_relation AS rel_in_group0 |
|
1321 WHERE rel_in_group0.eid_from=T00.x AND rel_in_group0.eid_to=_G.cw_eid''', |
|
1322 varmap={'X': 'T00.x', 'X.login': 'T00.l'}) |
|
1323 |
|
1324 def test_varmap3(self): |
|
1325 self._check('Any %(x)s,D WHERE F data D, F is File', |
|
1326 'SELECT 728, _TDF0.C0\nFROM _TDF0', |
|
1327 args={'x': 728}, |
|
1328 varmap={'F.data': '_TDF0.C0', 'D': '_TDF0.C0'}) |
|
1329 |
|
1330 def test_is_null_transform(self): |
|
1331 union = self._prepare('Any X WHERE X login %(login)s') |
|
1332 r, args, cbs = self.o.generate(union, {'login': None}) |
|
1333 self.assertMultiLineEqual((r % args).strip(), |
|
1334 '''SELECT _X.cw_eid |
|
1335 FROM cw_CWUser AS _X |
|
1336 WHERE _X.cw_login IS NULL''') |
|
1337 |
|
1338 def test_today(self): |
|
1339 for t in self._parse([("Any X WHERE X creation_date TODAY, X is Affaire", |
|
1340 '''SELECT _X.cw_eid |
|
1341 FROM cw_Affaire AS _X |
|
1342 WHERE DATE(_X.cw_creation_date)=CAST(clock_timestamp() AS DATE)'''), |
|
1343 ("Personne P where not P datenaiss TODAY", |
|
1344 '''SELECT _P.cw_eid |
|
1345 FROM cw_Personne AS _P |
|
1346 WHERE NOT (DATE(_P.cw_datenaiss)=CAST(clock_timestamp() AS DATE))'''), |
|
1347 ]): |
|
1348 yield t |
|
1349 |
|
1350 def test_date_extraction(self): |
|
1351 self._check("Any MONTH(D) WHERE P is Personne, P creation_date D", |
|
1352 '''SELECT CAST(EXTRACT(MONTH from _P.cw_creation_date) AS INTEGER) |
|
1353 FROM cw_Personne AS _P''') |
|
1354 |
|
1355 def test_weekday_extraction(self): |
|
1356 self._check("Any WEEKDAY(D) WHERE P is Personne, P creation_date D", |
|
1357 '''SELECT (CAST(EXTRACT(DOW from _P.cw_creation_date) AS INTEGER) + 1) |
|
1358 FROM cw_Personne AS _P''') |
|
1359 |
|
1360 def test_substring(self): |
|
1361 self._check("Any SUBSTRING(N, 1, 1) WHERE P nom N, P is Personne", |
|
1362 '''SELECT SUBSTR(_P.cw_nom, 1, 1) |
|
1363 FROM cw_Personne AS _P''') |
|
1364 |
|
1365 def test_cast(self): |
|
1366 self._check("Any CAST(String, P) WHERE P is Personne", |
|
1367 '''SELECT CAST(_P.cw_eid AS text) |
|
1368 FROM cw_Personne AS _P''') |
|
1369 |
|
1370 def test_regexp(self): |
|
1371 self._check("Any X WHERE X login REGEXP '[0-9].*'", |
|
1372 '''SELECT _X.cw_eid |
|
1373 FROM cw_CWUser AS _X |
|
1374 WHERE _X.cw_login ~ [0-9].* |
|
1375 ''') |
|
1376 |
|
1377 def test_parser_parse(self): |
|
1378 for t in self._parse(PARSER): |
|
1379 yield t |
|
1380 |
|
1381 def test_basic_parse(self): |
|
1382 for t in self._parse(BASIC + BASIC_WITH_LIMIT): |
|
1383 yield t |
|
1384 |
|
1385 def test_advanced_parse(self): |
|
1386 for t in self._parse(ADVANCED + ADVANCED_WITH_LIMIT_OR_ORDERBY + ADVANCED_WITH_GROUP_CONCAT): |
|
1387 yield t |
|
1388 |
|
1389 def test_outer_join_parse(self): |
|
1390 for t in self._parse(OUTER_JOIN): |
|
1391 yield t |
|
1392 |
|
1393 def test_virtual_vars_parse(self): |
|
1394 for t in self._parse(VIRTUAL_VARS): |
|
1395 yield t |
|
1396 |
|
1397 def test_multiple_sel_parse(self): |
|
1398 for t in self._parse(MULTIPLE_SEL): |
|
1399 yield t |
|
1400 |
|
1401 def test_functions(self): |
|
1402 for t in self._parse(FUNCS): |
|
1403 yield t |
|
1404 |
|
1405 def test_negation(self): |
|
1406 for t in self._parse(NEGATIONS): |
|
1407 yield t |
|
1408 |
|
1409 def test_intersection(self): |
|
1410 for t in self._parse(INTERSECT): |
|
1411 yield t |
|
1412 |
|
1413 def test_union(self): |
|
1414 for t in self._parse(( |
|
1415 ('(Any N ORDERBY 1 WHERE X name N, X is State)' |
|
1416 ' UNION ' |
|
1417 '(Any NN ORDERBY 1 WHERE XX name NN, XX is Transition)', |
|
1418 '''(SELECT _X.cw_name |
|
1419 FROM cw_State AS _X |
|
1420 ORDER BY 1) |
|
1421 UNION ALL |
|
1422 (SELECT _XX.cw_name |
|
1423 FROM cw_Transition AS _XX |
|
1424 ORDER BY 1)'''), |
|
1425 )): |
|
1426 yield t |
|
1427 |
|
1428 def test_subquery(self): |
|
1429 for t in self._parse(( |
|
1430 |
|
1431 ('Any X,N ' |
|
1432 'WHERE NOT EXISTS(X owned_by U) ' |
|
1433 'WITH X,N BEING ' |
|
1434 '((Any X,N WHERE X name N, X is State)' |
|
1435 ' UNION ' |
|
1436 '(Any XX,NN WHERE XX name NN, XX is Transition))', |
|
1437 '''SELECT _T0.C0, _T0.C1 |
|
1438 FROM ((SELECT _X.cw_eid AS C0, _X.cw_name AS C1 |
|
1439 FROM cw_State AS _X) |
|
1440 UNION ALL |
|
1441 (SELECT _XX.cw_eid AS C0, _XX.cw_name AS C1 |
|
1442 FROM cw_Transition AS _XX)) AS _T0 |
|
1443 WHERE NOT (EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0 WHERE rel_owned_by0.eid_from=_T0.C0))'''), |
|
1444 |
|
1445 ('Any N ORDERBY 1 WITH N BEING ' |
|
1446 '((Any N WHERE X name N, X is State)' |
|
1447 ' UNION ' |
|
1448 '(Any NN WHERE XX name NN, XX is Transition))', |
|
1449 '''SELECT _T0.C0 |
|
1450 FROM ((SELECT _X.cw_name AS C0 |
|
1451 FROM cw_State AS _X) |
|
1452 UNION ALL |
|
1453 (SELECT _XX.cw_name AS C0 |
|
1454 FROM cw_Transition AS _XX)) AS _T0 |
|
1455 ORDER BY 1'''), |
|
1456 |
|
1457 ('Any N,NX ORDERBY NX WITH N,NX BEING ' |
|
1458 '((Any N,COUNT(X) GROUPBY N WHERE X name N, X is State HAVING COUNT(X)>1)' |
|
1459 ' UNION ' |
|
1460 '(Any N,COUNT(X) GROUPBY N WHERE X name N, X is Transition HAVING COUNT(X)>1))', |
|
1461 '''SELECT _T0.C0, _T0.C1 |
|
1462 FROM ((SELECT _X.cw_name AS C0, COUNT(_X.cw_eid) AS C1 |
|
1463 FROM cw_State AS _X |
|
1464 GROUP BY _X.cw_name |
|
1465 HAVING COUNT(_X.cw_eid)>1) |
|
1466 UNION ALL |
|
1467 (SELECT _X.cw_name AS C0, COUNT(_X.cw_eid) AS C1 |
|
1468 FROM cw_Transition AS _X |
|
1469 GROUP BY _X.cw_name |
|
1470 HAVING COUNT(_X.cw_eid)>1)) AS _T0 |
|
1471 ORDER BY 2'''), |
|
1472 |
|
1473 ('Any N,COUNT(X) GROUPBY N HAVING COUNT(X)>1 ' |
|
1474 'WITH X, N BEING ((Any X, N WHERE X name N, X is State) UNION ' |
|
1475 ' (Any X, N WHERE X name N, X is Transition))', |
|
1476 '''SELECT _T0.C1, COUNT(_T0.C0) |
|
1477 FROM ((SELECT _X.cw_eid AS C0, _X.cw_name AS C1 |
|
1478 FROM cw_State AS _X) |
|
1479 UNION ALL |
|
1480 (SELECT _X.cw_eid AS C0, _X.cw_name AS C1 |
|
1481 FROM cw_Transition AS _X)) AS _T0 |
|
1482 GROUP BY _T0.C1 |
|
1483 HAVING COUNT(_T0.C0)>1'''), |
|
1484 |
|
1485 ('Any ETN,COUNT(X) GROUPBY ETN WHERE X is ET, ET name ETN ' |
|
1486 'WITH X BEING ((Any X WHERE X is Societe) UNION (Any X WHERE X is Affaire, (EXISTS(X owned_by 1)) OR ((EXISTS(D concerne B?, B owned_by 1, X identity D, B is Note)) OR (EXISTS(F concerne E?, E owned_by 1, E is Societe, X identity F)))))', |
|
1487 '''SELECT _ET.cw_name, COUNT(_T0.C0) |
|
1488 FROM ((SELECT _X.cw_eid AS C0 |
|
1489 FROM cw_Societe AS _X) |
|
1490 UNION ALL |
|
1491 (SELECT _X.cw_eid AS C0 |
|
1492 FROM cw_Affaire AS _X |
|
1493 WHERE ((EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0 WHERE rel_owned_by0.eid_from=_X.cw_eid AND rel_owned_by0.eid_to=1)) OR (((EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by2, cw_Affaire AS _D LEFT OUTER JOIN concerne_relation AS rel_concerne1 ON (rel_concerne1.eid_from=_D.cw_eid) LEFT OUTER JOIN cw_Note AS _B ON (rel_concerne1.eid_to=_B.cw_eid) WHERE rel_owned_by2.eid_from=_B.cw_eid AND rel_owned_by2.eid_to=1 AND _X.cw_eid=_D.cw_eid)) OR (EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by4, cw_Affaire AS _F LEFT OUTER JOIN concerne_relation AS rel_concerne3 ON (rel_concerne3.eid_from=_F.cw_eid) LEFT OUTER JOIN cw_Societe AS _E ON (rel_concerne3.eid_to=_E.cw_eid) WHERE rel_owned_by4.eid_from=_E.cw_eid AND rel_owned_by4.eid_to=1 AND _X.cw_eid=_F.cw_eid))))))) AS _T0, cw_CWEType AS _ET, is_relation AS rel_is0 |
|
1494 WHERE rel_is0.eid_from=_T0.C0 AND rel_is0.eid_to=_ET.cw_eid |
|
1495 GROUP BY _ET.cw_name'''), |
|
1496 |
|
1497 ('Any A WHERE A ordernum O, A is CWAttribute WITH O BEING (Any MAX(O) WHERE A ordernum O, A is CWAttribute)', |
|
1498 '''SELECT _A.cw_eid |
|
1499 FROM (SELECT MAX(_A.cw_ordernum) AS C0 |
|
1500 FROM cw_CWAttribute AS _A) AS _T0, cw_CWAttribute AS _A |
|
1501 WHERE _A.cw_ordernum=_T0.C0'''), |
|
1502 |
|
1503 ('Any O1 HAVING O1=O2? WITH O1 BEING (Any MAX(O) WHERE A ordernum O, A is CWAttribute), O2 BEING (Any MAX(O) WHERE A ordernum O, A is CWRelation)', |
|
1504 '''SELECT _T0.C0 |
|
1505 FROM (SELECT MAX(_A.cw_ordernum) AS C0 |
|
1506 FROM cw_CWAttribute AS _A) AS _T0 LEFT OUTER JOIN (SELECT MAX(_A.cw_ordernum) AS C0 |
|
1507 FROM cw_CWRelation AS _A) AS _T1 ON (_T0.C0=_T1.C0)'''), |
|
1508 |
|
1509 ('''Any TT1,STD,STDD WHERE TT2 identity TT1? |
|
1510 WITH TT1,STDD BEING (Any T,SUM(TD) GROUPBY T WHERE T is Affaire, T duration TD, TAG? tags T, TAG name "t"), |
|
1511 TT2,STD BEING (Any T,SUM(TD) GROUPBY T WHERE T is Affaire, T duration TD)''', |
|
1512 '''SELECT _T0.C0, _T1.C1, _T0.C1 |
|
1513 FROM (SELECT _T.cw_eid AS C0, SUM(_T.cw_duration) AS C1 |
|
1514 FROM cw_Affaire AS _T |
|
1515 GROUP BY _T.cw_eid) AS _T1 LEFT OUTER JOIN (SELECT _T.cw_eid AS C0, SUM(_T.cw_duration) AS C1 |
|
1516 FROM cw_Affaire AS _T LEFT OUTER JOIN tags_relation AS rel_tags0 ON (rel_tags0.eid_to=_T.cw_eid) LEFT OUTER JOIN cw_Tag AS _TAG ON (rel_tags0.eid_from=_TAG.cw_eid AND _TAG.cw_name=t) |
|
1517 GROUP BY _T.cw_eid) AS _T0 ON (_T1.C0=_T0.C0)'''), |
|
1518 |
|
1519 )): |
|
1520 yield t |
|
1521 |
|
1522 |
|
1523 def test_subquery_error(self): |
|
1524 rql = ('Any N WHERE X name N WITH X BEING ' |
|
1525 '((Any X WHERE X is State)' |
|
1526 ' UNION ' |
|
1527 ' (Any X WHERE X is Transition))') |
|
1528 rqlst = self._prepare(rql) |
|
1529 self.assertRaises(BadRQLQuery, self.o.generate, rqlst) |
|
1530 |
|
1531 def test_inline(self): |
|
1532 for t in self._parse(INLINE): |
|
1533 yield t |
|
1534 |
|
1535 def test_has_text(self): |
|
1536 for t in self._parse(( |
|
1537 ('Any X WHERE X has_text "toto tata"', |
|
1538 """SELECT appears0.uid |
|
1539 FROM appears AS appears0 |
|
1540 WHERE appears0.words @@ to_tsquery('default', 'toto&tata')"""), |
|
1541 |
|
1542 ('Personne X WHERE X has_text "toto tata"', |
|
1543 """SELECT _X.eid |
|
1544 FROM appears AS appears0, entities AS _X |
|
1545 WHERE appears0.words @@ to_tsquery('default', 'toto&tata') AND appears0.uid=_X.eid AND _X.type='Personne'"""), |
|
1546 |
|
1547 ('Personne X WHERE X has_text %(text)s', |
|
1548 """SELECT _X.eid |
|
1549 FROM appears AS appears0, entities AS _X |
|
1550 WHERE appears0.words @@ to_tsquery('default', 'hip&hop&momo') AND appears0.uid=_X.eid AND _X.type='Personne'"""), |
|
1551 |
|
1552 ('Any X WHERE X has_text "toto tata", X name "tutu", X is IN (Basket,Folder)', |
|
1553 """SELECT _X.cw_eid |
|
1554 FROM appears AS appears0, cw_Basket AS _X |
|
1555 WHERE appears0.words @@ to_tsquery('default', 'toto&tata') AND appears0.uid=_X.cw_eid AND _X.cw_name=tutu |
|
1556 UNION ALL |
|
1557 SELECT _X.cw_eid |
|
1558 FROM appears AS appears0, cw_Folder AS _X |
|
1559 WHERE appears0.words @@ to_tsquery('default', 'toto&tata') AND appears0.uid=_X.cw_eid AND _X.cw_name=tutu"""), |
|
1560 |
|
1561 ('Personne X where X has_text %(text)s, X travaille S, S has_text %(text)s', |
|
1562 """SELECT _X.eid |
|
1563 FROM appears AS appears0, appears AS appears2, entities AS _X, travaille_relation AS rel_travaille1 |
|
1564 WHERE appears0.words @@ to_tsquery('default', 'hip&hop&momo') AND appears0.uid=_X.eid AND _X.type='Personne' AND _X.eid=rel_travaille1.eid_from AND appears2.uid=rel_travaille1.eid_to AND appears2.words @@ to_tsquery('default', 'hip&hop&momo')"""), |
|
1565 |
|
1566 ('Any X ORDERBY FTIRANK(X) DESC WHERE X has_text "toto tata"', |
|
1567 """SELECT appears0.uid |
|
1568 FROM appears AS appears0 |
|
1569 WHERE appears0.words @@ to_tsquery('default', 'toto&tata') |
|
1570 ORDER BY ts_rank(appears0.words, to_tsquery('default', 'toto&tata'))*appears0.weight DESC"""), |
|
1571 |
|
1572 ('Personne X ORDERBY FTIRANK(X) WHERE X has_text "toto tata"', |
|
1573 """SELECT _X.eid |
|
1574 FROM appears AS appears0, entities AS _X |
|
1575 WHERE appears0.words @@ to_tsquery('default', 'toto&tata') AND appears0.uid=_X.eid AND _X.type='Personne' |
|
1576 ORDER BY ts_rank(appears0.words, to_tsquery('default', 'toto&tata'))*appears0.weight"""), |
|
1577 |
|
1578 ('Personne X ORDERBY FTIRANK(X) WHERE X has_text %(text)s', |
|
1579 """SELECT _X.eid |
|
1580 FROM appears AS appears0, entities AS _X |
|
1581 WHERE appears0.words @@ to_tsquery('default', 'hip&hop&momo') AND appears0.uid=_X.eid AND _X.type='Personne' |
|
1582 ORDER BY ts_rank(appears0.words, to_tsquery('default', 'hip&hop&momo'))*appears0.weight"""), |
|
1583 |
|
1584 ('Any X ORDERBY FTIRANK(X) WHERE X has_text "toto tata", X name "tutu", X is IN (Basket,Folder)', |
|
1585 """SELECT T1.C0 FROM (SELECT _X.cw_eid AS C0, ts_rank(appears0.words, to_tsquery('default', 'toto&tata'))*appears0.weight AS C1 |
|
1586 FROM appears AS appears0, cw_Basket AS _X |
|
1587 WHERE appears0.words @@ to_tsquery('default', 'toto&tata') AND appears0.uid=_X.cw_eid AND _X.cw_name=tutu |
|
1588 UNION ALL |
|
1589 SELECT _X.cw_eid AS C0, ts_rank(appears0.words, to_tsquery('default', 'toto&tata'))*appears0.weight AS C1 |
|
1590 FROM appears AS appears0, cw_Folder AS _X |
|
1591 WHERE appears0.words @@ to_tsquery('default', 'toto&tata') AND appears0.uid=_X.cw_eid AND _X.cw_name=tutu |
|
1592 ORDER BY 2) AS T1"""), |
|
1593 |
|
1594 ('Personne X ORDERBY FTIRANK(X),FTIRANK(S) WHERE X has_text %(text)s, X travaille S, S has_text %(text)s', |
|
1595 """SELECT _X.eid |
|
1596 FROM appears AS appears0, appears AS appears2, entities AS _X, travaille_relation AS rel_travaille1 |
|
1597 WHERE appears0.words @@ to_tsquery('default', 'hip&hop&momo') AND appears0.uid=_X.eid AND _X.type='Personne' AND _X.eid=rel_travaille1.eid_from AND appears2.uid=rel_travaille1.eid_to AND appears2.words @@ to_tsquery('default', 'hip&hop&momo') |
|
1598 ORDER BY ts_rank(appears0.words, to_tsquery('default', 'hip&hop&momo'))*appears0.weight,ts_rank(appears2.words, to_tsquery('default', 'hip&hop&momo'))*appears2.weight"""), |
|
1599 |
|
1600 |
|
1601 ('Any X, FTIRANK(X) WHERE X has_text "toto tata"', |
|
1602 """SELECT appears0.uid, ts_rank(appears0.words, to_tsquery('default', 'toto&tata'))*appears0.weight |
|
1603 FROM appears AS appears0 |
|
1604 WHERE appears0.words @@ to_tsquery('default', 'toto&tata')"""), |
|
1605 |
|
1606 |
|
1607 ('Any X WHERE NOT A tags X, X has_text "pouet"', |
|
1608 '''SELECT appears1.uid |
|
1609 FROM appears AS appears1 |
|
1610 WHERE NOT (EXISTS(SELECT 1 FROM tags_relation AS rel_tags0 WHERE appears1.uid=rel_tags0.eid_to)) AND appears1.words @@ to_tsquery('default', 'pouet') |
|
1611 '''), |
|
1612 |
|
1613 )): |
|
1614 yield t |
|
1615 |
|
1616 |
|
1617 def test_from_clause_needed(self): |
|
1618 queries = [("Any 1 WHERE EXISTS(T is CWGroup, T name 'managers')", |
|
1619 '''SELECT 1 |
|
1620 WHERE EXISTS(SELECT 1 FROM cw_CWGroup AS _T WHERE _T.cw_name=managers)'''), |
|
1621 ('Any X,Y WHERE NOT X created_by Y, X eid 5, Y eid 6', |
|
1622 '''SELECT 5, 6 |
|
1623 WHERE NOT (EXISTS(SELECT 1 FROM created_by_relation AS rel_created_by0 WHERE rel_created_by0.eid_from=5 AND rel_created_by0.eid_to=6))'''), |
|
1624 ] |
|
1625 for t in self._parse(queries): |
|
1626 yield t |
|
1627 |
|
1628 def test_ambigous_exists_no_from_clause(self): |
|
1629 self._check('Any COUNT(U) WHERE U eid 1, EXISTS (P owned_by U, P is IN (Note, Affaire))', |
|
1630 '''SELECT COUNT(1) |
|
1631 WHERE EXISTS(SELECT 1 FROM cw_Affaire AS _P, owned_by_relation AS rel_owned_by0 WHERE rel_owned_by0.eid_from=_P.cw_eid AND rel_owned_by0.eid_to=1 UNION SELECT 1 FROM cw_Note AS _P, owned_by_relation AS rel_owned_by1 WHERE rel_owned_by1.eid_from=_P.cw_eid AND rel_owned_by1.eid_to=1)''') |
|
1632 |
|
1633 def test_attr_map_sqlcb(self): |
|
1634 def generate_ref(gen, linkedvar, rel): |
|
1635 linkedvar.accept(gen) |
|
1636 return 'VERSION_DATA(%s)' % linkedvar._q_sql |
|
1637 self.o.attr_map['Affaire.ref'] = (generate_ref, False) |
|
1638 try: |
|
1639 self._check('Any R WHERE X ref R', |
|
1640 '''SELECT VERSION_DATA(_X.cw_eid) |
|
1641 FROM cw_Affaire AS _X''') |
|
1642 self._check('Any X WHERE X ref 1', |
|
1643 '''SELECT _X.cw_eid |
|
1644 FROM cw_Affaire AS _X |
|
1645 WHERE VERSION_DATA(_X.cw_eid)=1''') |
|
1646 finally: |
|
1647 self.o.attr_map.clear() |
|
1648 |
|
1649 def test_attr_map_sourcecb(self): |
|
1650 cb = lambda x,y: None |
|
1651 self.o.attr_map['Affaire.ref'] = (cb, True) |
|
1652 try: |
|
1653 union = self._prepare('Any R WHERE X ref R') |
|
1654 r, nargs, cbs = self.o.generate(union, args={}) |
|
1655 self.assertMultiLineEqual(r.strip(), 'SELECT _X.cw_ref\nFROM cw_Affaire AS _X') |
|
1656 self.assertEqual(cbs, {0: [cb]}) |
|
1657 finally: |
|
1658 self.o.attr_map.clear() |
|
1659 |
|
1660 |
|
1661 def test_concat_string(self): |
|
1662 self._check('Any "A"+R WHERE X ref R', |
|
1663 '''SELECT (A || _X.cw_ref) |
|
1664 FROM cw_Affaire AS _X''') |
|
1665 |
|
1666 def test_or_having_fake_terms_base(self): |
|
1667 self._check('Any X WHERE X is CWUser, X creation_date D HAVING YEAR(D) = "2010" OR D = NULL', |
|
1668 '''SELECT _X.cw_eid |
|
1669 FROM cw_CWUser AS _X |
|
1670 WHERE ((CAST(EXTRACT(YEAR from _X.cw_creation_date) AS INTEGER)=2010) OR (_X.cw_creation_date IS NULL))''') |
|
1671 |
|
1672 def test_or_having_fake_terms_exists(self): |
|
1673 # crash with rql <= 0.29.0 |
|
1674 self._check('Any X WHERE X is CWUser, EXISTS(B bookmarked_by X, B creation_date D) HAVING D=2010 OR D=NULL, D=1 OR D=NULL', |
|
1675 '''SELECT _X.cw_eid |
|
1676 FROM cw_CWUser AS _X |
|
1677 WHERE EXISTS(SELECT 1 FROM bookmarked_by_relation AS rel_bookmarked_by0, cw_Bookmark AS _B WHERE rel_bookmarked_by0.eid_from=_B.cw_eid AND rel_bookmarked_by0.eid_to=_X.cw_eid AND ((_B.cw_creation_date=1) OR (_B.cw_creation_date IS NULL)) AND ((_B.cw_creation_date=2010) OR (_B.cw_creation_date IS NULL)))''') |
|
1678 |
|
1679 def test_or_having_fake_terms_nocrash(self): |
|
1680 # crash with rql <= 0.29.0 |
|
1681 self._check('Any X WHERE X is CWUser, X creation_date D HAVING D=2010 OR D=NULL, D=1 OR D=NULL', |
|
1682 '''SELECT _X.cw_eid |
|
1683 FROM cw_CWUser AS _X |
|
1684 WHERE ((_X.cw_creation_date=1) OR (_X.cw_creation_date IS NULL)) AND ((_X.cw_creation_date=2010) OR (_X.cw_creation_date IS NULL))''') |
|
1685 |
|
1686 def test_not_no_where(self): |
|
1687 # XXX will check if some in_group relation exists, that's it. |
|
1688 # We can't actually know if we want to check if there are some |
|
1689 # X without in_group relation, or some G without it. |
|
1690 self._check('Any 1 WHERE NOT X in_group G, X is CWUser', |
|
1691 '''SELECT 1 |
|
1692 WHERE NOT (EXISTS(SELECT 1 FROM in_group_relation AS rel_in_group0))''') |
|
1693 |
|
1694 def test_nonregr_outer_join_multiple(self): |
|
1695 self._check('Any COUNT(P1148),G GROUPBY G ' |
|
1696 'WHERE G owned_by D, D eid 1122, K1148 bookmarked_by P1148, ' |
|
1697 'K1148 eid 1148, P1148? in_group G', |
|
1698 '''SELECT COUNT(rel_bookmarked_by1.eid_to), _G.cw_eid |
|
1699 FROM owned_by_relation AS rel_owned_by0, cw_CWGroup AS _G LEFT OUTER JOIN in_group_relation AS rel_in_group2 ON (rel_in_group2.eid_to=_G.cw_eid) LEFT OUTER JOIN bookmarked_by_relation AS rel_bookmarked_by1 ON (rel_in_group2.eid_from=rel_bookmarked_by1.eid_to) |
|
1700 WHERE rel_owned_by0.eid_from=_G.cw_eid AND rel_owned_by0.eid_to=1122 AND rel_bookmarked_by1.eid_from=1148 |
|
1701 GROUP BY _G.cw_eid''' |
|
1702 ) |
|
1703 |
|
1704 def test_nonregr_outer_join_multiple2(self): |
|
1705 self._check('Any COUNT(P1148),G GROUPBY G ' |
|
1706 'WHERE G owned_by D, D eid 1122, K1148 bookmarked_by P1148?, ' |
|
1707 'K1148 eid 1148, P1148? in_group G', |
|
1708 '''SELECT COUNT(rel_bookmarked_by1.eid_to), _G.cw_eid |
|
1709 FROM owned_by_relation AS rel_owned_by0, cw_CWGroup AS _G LEFT OUTER JOIN in_group_relation AS rel_in_group2 ON (rel_in_group2.eid_to=_G.cw_eid) LEFT OUTER JOIN bookmarked_by_relation AS rel_bookmarked_by1 ON (rel_bookmarked_by1.eid_from=1148 AND rel_in_group2.eid_from=rel_bookmarked_by1.eid_to) |
|
1710 WHERE rel_owned_by0.eid_from=_G.cw_eid AND rel_owned_by0.eid_to=1122 |
|
1711 GROUP BY _G.cw_eid''') |
|
1712 |
|
1713 def test_groupby_orderby_insertion_dont_modify_intention(self): |
|
1714 self._check('Any YEAR(XECT)*100+MONTH(XECT), COUNT(X),SUM(XCE),AVG(XSCT-XECT) ' |
|
1715 'GROUPBY YEAR(XECT),MONTH(XECT) ORDERBY 1 ' |
|
1716 'WHERE X creation_date XSCT, X modification_date XECT, ' |
|
1717 'X ordernum XCE, X is CWAttribute', |
|
1718 '''SELECT ((CAST(EXTRACT(YEAR from _X.cw_modification_date) AS INTEGER) * 100) + CAST(EXTRACT(MONTH from _X.cw_modification_date) AS INTEGER)), COUNT(_X.cw_eid), SUM(_X.cw_ordernum), AVG((_X.cw_creation_date - _X.cw_modification_date)) |
|
1719 FROM cw_CWAttribute AS _X |
|
1720 GROUP BY CAST(EXTRACT(YEAR from _X.cw_modification_date) AS INTEGER),CAST(EXTRACT(MONTH from _X.cw_modification_date) AS INTEGER) |
|
1721 ORDER BY 1'''), |
|
1722 |
|
1723 def test_modulo(self): |
|
1724 self._check('Any 5 % 2', '''SELECT (5 % 2)''') |
|
1725 |
|
1726 |
|
1727 class SqlServer2005SQLGeneratorTC(PostgresSQLGeneratorTC): |
|
1728 backend = 'sqlserver2005' |
|
1729 def _norm_sql(self, sql): |
|
1730 return sql.strip().replace(' SUBSTR', ' SUBSTRING').replace(' || ', ' + ').replace(' ILIKE ', ' LIKE ') |
|
1731 |
|
1732 def test_has_text(self): |
|
1733 for t in self._parse(HAS_TEXT_LG_INDEXER): |
|
1734 yield t |
|
1735 |
|
1736 def test_regexp(self): |
|
1737 self.skipTest('regexp-based pattern matching not implemented in sqlserver') |
|
1738 |
|
1739 def test_or_having_fake_terms_base(self): |
|
1740 self._check('Any X WHERE X is CWUser, X creation_date D HAVING YEAR(D) = "2010" OR D = NULL', |
|
1741 '''SELECT _X.cw_eid |
|
1742 FROM cw_CWUser AS _X |
|
1743 WHERE ((DATEPART(YEAR, _X.cw_creation_date)=2010) OR (_X.cw_creation_date IS NULL))''') |
|
1744 |
|
1745 def test_date_extraction(self): |
|
1746 self._check("Any MONTH(D) WHERE P is Personne, P creation_date D", |
|
1747 '''SELECT DATEPART(MONTH, _P.cw_creation_date) |
|
1748 FROM cw_Personne AS _P''') |
|
1749 |
|
1750 def test_weekday_extraction(self): |
|
1751 self._check("Any WEEKDAY(D) WHERE P is Personne, P creation_date D", |
|
1752 '''SELECT DATEPART(WEEKDAY, _P.cw_creation_date) |
|
1753 FROM cw_Personne AS _P''') |
|
1754 |
|
1755 def test_basic_parse(self): |
|
1756 for t in self._parse(BASIC):# + BASIC_WITH_LIMIT): |
|
1757 yield t |
|
1758 |
|
1759 def test_advanced_parse(self): |
|
1760 for t in self._parse(ADVANCED):# + ADVANCED_WITH_LIMIT_OR_ORDERBY): |
|
1761 yield t |
|
1762 |
|
1763 def test_limit_offset(self): |
|
1764 WITH_LIMIT = [ |
|
1765 ("Personne P LIMIT 20 OFFSET 10", |
|
1766 '''WITH orderedrows AS ( |
|
1767 SELECT |
|
1768 _L01 |
|
1769 , ROW_NUMBER() OVER (ORDER BY _L01) AS __RowNumber |
|
1770 FROM ( |
|
1771 SELECT _P.cw_eid AS _L01 FROM cw_Personne AS _P |
|
1772 ) AS _SQ1 ) |
|
1773 SELECT |
|
1774 _L01 |
|
1775 FROM orderedrows WHERE |
|
1776 __RowNumber <= 30 AND __RowNumber > 10 |
|
1777 '''), |
|
1778 |
|
1779 ('Any COUNT(S),CS GROUPBY CS ORDERBY 1 DESC LIMIT 10 WHERE S is Affaire, C is Societe, S concerne C, C nom CS, (EXISTS(S owned_by 1)) OR (EXISTS(S documented_by N, N title "published"))', |
|
1780 '''WITH orderedrows AS ( |
|
1781 SELECT |
|
1782 _L01, _L02 |
|
1783 , ROW_NUMBER() OVER (ORDER BY _L01 DESC) AS __RowNumber |
|
1784 FROM ( |
|
1785 SELECT COUNT(rel_concerne0.eid_from) AS _L01, _C.cw_nom AS _L02 FROM concerne_relation AS rel_concerne0, cw_Societe AS _C |
|
1786 WHERE rel_concerne0.eid_to=_C.cw_eid AND ((EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by1 WHERE rel_concerne0.eid_from=rel_owned_by1.eid_from AND rel_owned_by1.eid_to=1)) OR (EXISTS(SELECT 1 FROM cw_Card AS _N, documented_by_relation AS rel_documented_by2 WHERE rel_concerne0.eid_from=rel_documented_by2.eid_from AND rel_documented_by2.eid_to=_N.cw_eid AND _N.cw_title=published))) |
|
1787 GROUP BY _C.cw_nom |
|
1788 ) AS _SQ1 ) |
|
1789 SELECT |
|
1790 _L01, _L02 |
|
1791 FROM orderedrows WHERE |
|
1792 __RowNumber <= 10 |
|
1793 '''), |
|
1794 |
|
1795 ('DISTINCT Any MAX(X)+MIN(LENGTH(D)), N GROUPBY N ORDERBY 2, DF WHERE X data_name N, X data D, X data_format DF;', |
|
1796 '''SELECT T1.C0,T1.C1 FROM (SELECT DISTINCT (MAX(_X.cw_eid) + MIN(LENGTH(_X.cw_data))) AS C0, _X.cw_data_name AS C1, _X.cw_data_format AS C2 |
|
1797 FROM cw_File AS _X |
|
1798 GROUP BY _X.cw_data_name,_X.cw_data_format) AS T1 |
|
1799 ORDER BY T1.C1,T1.C2 |
|
1800 '''), |
|
1801 |
|
1802 |
|
1803 ('DISTINCT Any X ORDERBY Y WHERE B bookmarked_by X, X login Y', |
|
1804 '''SELECT T1.C0 FROM (SELECT DISTINCT _X.cw_eid AS C0, _X.cw_login AS C1 |
|
1805 FROM bookmarked_by_relation AS rel_bookmarked_by0, cw_CWUser AS _X |
|
1806 WHERE rel_bookmarked_by0.eid_to=_X.cw_eid) AS T1 |
|
1807 ORDER BY T1.C1 |
|
1808 '''), |
|
1809 |
|
1810 ('DISTINCT Any X ORDERBY SN WHERE X in_state S, S name SN', |
|
1811 '''SELECT T1.C0 FROM (SELECT DISTINCT _X.cw_eid AS C0, _S.cw_name AS C1 |
|
1812 FROM cw_Affaire AS _X, cw_State AS _S |
|
1813 WHERE _X.cw_in_state=_S.cw_eid |
|
1814 UNION |
|
1815 SELECT DISTINCT _X.cw_eid AS C0, _S.cw_name AS C1 |
|
1816 FROM cw_CWUser AS _X, cw_State AS _S |
|
1817 WHERE _X.cw_in_state=_S.cw_eid |
|
1818 UNION |
|
1819 SELECT DISTINCT _X.cw_eid AS C0, _S.cw_name AS C1 |
|
1820 FROM cw_Note AS _X, cw_State AS _S |
|
1821 WHERE _X.cw_in_state=_S.cw_eid) AS T1 |
|
1822 ORDER BY T1.C1'''), |
|
1823 |
|
1824 ('Any O,AA,AB,AC ORDERBY AC DESC ' |
|
1825 'WHERE NOT S use_email O, S eid 1, O is EmailAddress, O address AA, O alias AB, O modification_date AC, ' |
|
1826 'EXISTS(A use_email O, EXISTS(A identity B, NOT B in_group D, D name "guests", D is CWGroup), A is CWUser), B eid 2', |
|
1827 ''' |
|
1828 SELECT _O.cw_eid, _O.cw_address, _O.cw_alias, _O.cw_modification_date |
|
1829 FROM cw_EmailAddress AS _O |
|
1830 WHERE NOT (EXISTS(SELECT 1 FROM use_email_relation AS rel_use_email0 WHERE rel_use_email0.eid_from=1 AND rel_use_email0.eid_to=_O.cw_eid)) AND EXISTS(SELECT 1 FROM use_email_relation AS rel_use_email1 WHERE rel_use_email1.eid_to=_O.cw_eid AND EXISTS(SELECT 1 FROM cw_CWGroup AS _D WHERE rel_use_email1.eid_from=2 AND NOT (EXISTS(SELECT 1 FROM in_group_relation AS rel_in_group2 WHERE rel_in_group2.eid_from=2 AND rel_in_group2.eid_to=_D.cw_eid)) AND _D.cw_name=guests)) |
|
1831 ORDER BY 4 DESC'''), |
|
1832 |
|
1833 ("Any P ORDERBY N LIMIT 1 WHERE P is Personne, P travaille S, S eid %(eid)s, P nom N, P nom %(text)s", |
|
1834 '''WITH orderedrows AS ( |
|
1835 SELECT |
|
1836 _L01 |
|
1837 , ROW_NUMBER() OVER (ORDER BY _L01) AS __RowNumber |
|
1838 FROM ( |
|
1839 SELECT _P.cw_eid AS _L01 FROM cw_Personne AS _P, travaille_relation AS rel_travaille0 |
|
1840 WHERE rel_travaille0.eid_from=_P.cw_eid AND rel_travaille0.eid_to=12345 AND _P.cw_nom=hip hop momo |
|
1841 ) AS _SQ1 ) |
|
1842 SELECT |
|
1843 _L01 |
|
1844 FROM orderedrows WHERE |
|
1845 __RowNumber <= 1'''), |
|
1846 |
|
1847 ("Any P ORDERBY N LIMIT 1 WHERE P is Personne, P nom N", |
|
1848 '''WITH orderedrows AS ( |
|
1849 SELECT |
|
1850 _L01 |
|
1851 , ROW_NUMBER() OVER (ORDER BY _L01) AS __RowNumber |
|
1852 FROM ( |
|
1853 SELECT _P.cw_eid AS _L01 FROM cw_Personne AS _P |
|
1854 ) AS _SQ1 ) |
|
1855 SELECT |
|
1856 _L01 |
|
1857 FROM orderedrows WHERE |
|
1858 __RowNumber <= 1 |
|
1859 '''), |
|
1860 |
|
1861 ("Any PN, N, P ORDERBY N LIMIT 1 WHERE P is Personne, P nom N, P prenom PN", |
|
1862 '''WITH orderedrows AS ( |
|
1863 SELECT |
|
1864 _L01, _L02, _L03 |
|
1865 , ROW_NUMBER() OVER (ORDER BY _L02) AS __RowNumber |
|
1866 FROM ( |
|
1867 SELECT _P.cw_prenom AS _L01, _P.cw_nom AS _L02, _P.cw_eid AS _L03 FROM cw_Personne AS _P |
|
1868 ) AS _SQ1 ) |
|
1869 SELECT |
|
1870 _L01, _L02, _L03 |
|
1871 FROM orderedrows WHERE |
|
1872 __RowNumber <= 1 |
|
1873 '''), |
|
1874 ] |
|
1875 for t in self._parse(WITH_LIMIT):# + ADVANCED_WITH_LIMIT_OR_ORDERBY): |
|
1876 yield t |
|
1877 |
|
1878 def test_cast(self): |
|
1879 self._check("Any CAST(String, P) WHERE P is Personne", |
|
1880 '''SELECT CAST(_P.cw_eid AS nvarchar(max)) |
|
1881 FROM cw_Personne AS _P''') |
|
1882 |
|
1883 def test_groupby_orderby_insertion_dont_modify_intention(self): |
|
1884 self._check('Any YEAR(XECT)*100+MONTH(XECT), COUNT(X),SUM(XCE),AVG(XSCT-XECT) ' |
|
1885 'GROUPBY YEAR(XECT),MONTH(XECT) ORDERBY 1 ' |
|
1886 'WHERE X creation_date XSCT, X modification_date XECT, ' |
|
1887 'X ordernum XCE, X is CWAttribute', |
|
1888 '''SELECT ((DATEPART(YEAR, _X.cw_modification_date) * 100) + DATEPART(MONTH, _X.cw_modification_date)), COUNT(_X.cw_eid), SUM(_X.cw_ordernum), AVG((_X.cw_creation_date - _X.cw_modification_date)) |
|
1889 FROM cw_CWAttribute AS _X |
|
1890 GROUP BY DATEPART(YEAR, _X.cw_modification_date),DATEPART(MONTH, _X.cw_modification_date) |
|
1891 ORDER BY 1''') |
|
1892 |
|
1893 def test_today(self): |
|
1894 for t in self._parse([("Any X WHERE X creation_date TODAY, X is Affaire", |
|
1895 '''SELECT _X.cw_eid |
|
1896 FROM cw_Affaire AS _X |
|
1897 WHERE DATE(_X.cw_creation_date)=%s''' % self.dbhelper.sql_current_date()), |
|
1898 |
|
1899 ("Personne P where not P datenaiss TODAY", |
|
1900 '''SELECT _P.cw_eid |
|
1901 FROM cw_Personne AS _P |
|
1902 WHERE NOT (DATE(_P.cw_datenaiss)=%s)''' % self.dbhelper.sql_current_date()), |
|
1903 ]): |
|
1904 yield t |
|
1905 |
|
1906 |
|
1907 class SqliteSQLGeneratorTC(PostgresSQLGeneratorTC): |
|
1908 backend = 'sqlite' |
|
1909 |
|
1910 def _norm_sql(self, sql): |
|
1911 return sql.strip().replace(' ILIKE ', ' LIKE ') |
|
1912 |
|
1913 def test_date_extraction(self): |
|
1914 self._check("Any MONTH(D) WHERE P is Personne, P creation_date D", |
|
1915 '''SELECT MONTH(_P.cw_creation_date) |
|
1916 FROM cw_Personne AS _P''') |
|
1917 |
|
1918 def test_weekday_extraction(self): |
|
1919 # custom impl. in cw.server.sqlutils |
|
1920 self._check("Any WEEKDAY(D) WHERE P is Personne, P creation_date D", |
|
1921 '''SELECT WEEKDAY(_P.cw_creation_date) |
|
1922 FROM cw_Personne AS _P''') |
|
1923 |
|
1924 def test_regexp(self): |
|
1925 self._check("Any X WHERE X login REGEXP '[0-9].*'", |
|
1926 '''SELECT _X.cw_eid |
|
1927 FROM cw_CWUser AS _X |
|
1928 WHERE _X.cw_login REGEXP [0-9].* |
|
1929 ''') |
|
1930 |
|
1931 |
|
1932 def test_union(self): |
|
1933 for t in self._parse(( |
|
1934 ('(Any N ORDERBY 1 WHERE X name N, X is State)' |
|
1935 ' UNION ' |
|
1936 '(Any NN ORDERBY 1 WHERE XX name NN, XX is Transition)', |
|
1937 '''SELECT _X.cw_name |
|
1938 FROM cw_State AS _X |
|
1939 ORDER BY 1 |
|
1940 UNION ALL |
|
1941 SELECT _XX.cw_name |
|
1942 FROM cw_Transition AS _XX |
|
1943 ORDER BY 1'''), |
|
1944 )): |
|
1945 yield t |
|
1946 |
|
1947 |
|
1948 def test_subquery(self): |
|
1949 # NOTE: no paren around UNION with sqlitebackend |
|
1950 for t in self._parse(( |
|
1951 |
|
1952 ('Any N ORDERBY 1 WITH N BEING ' |
|
1953 '((Any N WHERE X name N, X is State)' |
|
1954 ' UNION ' |
|
1955 '(Any NN WHERE XX name NN, XX is Transition))', |
|
1956 '''SELECT _T0.C0 |
|
1957 FROM (SELECT _X.cw_name AS C0 |
|
1958 FROM cw_State AS _X |
|
1959 UNION ALL |
|
1960 SELECT _XX.cw_name AS C0 |
|
1961 FROM cw_Transition AS _XX) AS _T0 |
|
1962 ORDER BY 1'''), |
|
1963 |
|
1964 ('Any N,NX ORDERBY NX WITH N,NX BEING ' |
|
1965 '((Any N,COUNT(X) GROUPBY N WHERE X name N, X is State HAVING COUNT(X)>1)' |
|
1966 ' UNION ' |
|
1967 '(Any N,COUNT(X) GROUPBY N WHERE X name N, X is Transition HAVING COUNT(X)>1))', |
|
1968 '''SELECT _T0.C0, _T0.C1 |
|
1969 FROM (SELECT _X.cw_name AS C0, COUNT(_X.cw_eid) AS C1 |
|
1970 FROM cw_State AS _X |
|
1971 GROUP BY _X.cw_name |
|
1972 HAVING COUNT(_X.cw_eid)>1 |
|
1973 UNION ALL |
|
1974 SELECT _X.cw_name AS C0, COUNT(_X.cw_eid) AS C1 |
|
1975 FROM cw_Transition AS _X |
|
1976 GROUP BY _X.cw_name |
|
1977 HAVING COUNT(_X.cw_eid)>1) AS _T0 |
|
1978 ORDER BY 2'''), |
|
1979 |
|
1980 ('Any N,COUNT(X) GROUPBY N HAVING COUNT(X)>1 ' |
|
1981 'WITH X, N BEING ((Any X, N WHERE X name N, X is State) UNION ' |
|
1982 ' (Any X, N WHERE X name N, X is Transition))', |
|
1983 '''SELECT _T0.C1, COUNT(_T0.C0) |
|
1984 FROM (SELECT _X.cw_eid AS C0, _X.cw_name AS C1 |
|
1985 FROM cw_State AS _X |
|
1986 UNION ALL |
|
1987 SELECT _X.cw_eid AS C0, _X.cw_name AS C1 |
|
1988 FROM cw_Transition AS _X) AS _T0 |
|
1989 GROUP BY _T0.C1 |
|
1990 HAVING COUNT(_T0.C0)>1'''), |
|
1991 )): |
|
1992 yield t |
|
1993 |
|
1994 def test_has_text(self): |
|
1995 for t in self._parse(( |
|
1996 ('Any X WHERE X has_text "toto tata"', |
|
1997 """SELECT DISTINCT appears0.uid |
|
1998 FROM appears AS appears0 |
|
1999 WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata'))"""), |
|
2000 |
|
2001 ('Any X WHERE X has_text %(text)s', |
|
2002 """SELECT DISTINCT appears0.uid |
|
2003 FROM appears AS appears0 |
|
2004 WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('hip', 'hop', 'momo'))"""), |
|
2005 |
|
2006 ('Personne X WHERE X has_text "toto tata"', |
|
2007 """SELECT DISTINCT _X.eid |
|
2008 FROM appears AS appears0, entities AS _X |
|
2009 WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata')) AND appears0.uid=_X.eid AND _X.type='Personne'"""), |
|
2010 |
|
2011 ('Any X WHERE X has_text "toto tata", X name "tutu", X is IN (Basket,Folder)', |
|
2012 """SELECT DISTINCT _X.cw_eid |
|
2013 FROM appears AS appears0, cw_Basket AS _X |
|
2014 WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata')) AND appears0.uid=_X.cw_eid AND _X.cw_name=tutu |
|
2015 UNION |
|
2016 SELECT DISTINCT _X.cw_eid |
|
2017 FROM appears AS appears0, cw_Folder AS _X |
|
2018 WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata')) AND appears0.uid=_X.cw_eid AND _X.cw_name=tutu |
|
2019 """), |
|
2020 |
|
2021 ('Any X ORDERBY FTIRANK(X) WHERE X has_text "toto tata"', |
|
2022 """SELECT DISTINCT appears0.uid |
|
2023 FROM appears AS appears0 |
|
2024 WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata'))"""), |
|
2025 |
|
2026 ('Any X ORDERBY FTIRANK(X) WHERE X has_text "toto tata", X name "tutu", X is IN (Basket,Folder)', |
|
2027 """SELECT DISTINCT _X.cw_eid |
|
2028 FROM appears AS appears0, cw_Basket AS _X |
|
2029 WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata')) AND appears0.uid=_X.cw_eid AND _X.cw_name=tutu |
|
2030 UNION |
|
2031 SELECT DISTINCT _X.cw_eid |
|
2032 FROM appears AS appears0, cw_Folder AS _X |
|
2033 WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata')) AND appears0.uid=_X.cw_eid AND _X.cw_name=tutu |
|
2034 """), |
|
2035 |
|
2036 ('Any X, FTIRANK(X) WHERE X has_text "toto tata"', |
|
2037 """SELECT DISTINCT appears0.uid, 1.0 |
|
2038 FROM appears AS appears0 |
|
2039 WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata'))"""), |
|
2040 )): |
|
2041 yield t |
|
2042 |
|
2043 |
|
2044 def test_or_having_fake_terms_base(self): |
|
2045 self._check('Any X WHERE X is CWUser, X creation_date D HAVING YEAR(D) = "2010" OR D = NULL', |
|
2046 '''SELECT _X.cw_eid |
|
2047 FROM cw_CWUser AS _X |
|
2048 WHERE ((YEAR(_X.cw_creation_date)=2010) OR (_X.cw_creation_date IS NULL))''') |
|
2049 |
|
2050 def test_groupby_orderby_insertion_dont_modify_intention(self): |
|
2051 self._check('Any YEAR(XECT)*100+MONTH(XECT), COUNT(X),SUM(XCE),AVG(XSCT-XECT) ' |
|
2052 'GROUPBY YEAR(XECT),MONTH(XECT) ORDERBY 1 ' |
|
2053 'WHERE X creation_date XSCT, X modification_date XECT, ' |
|
2054 'X ordernum XCE, X is CWAttribute', |
|
2055 '''SELECT ((YEAR(_X.cw_modification_date) * 100) + MONTH(_X.cw_modification_date)), COUNT(_X.cw_eid), SUM(_X.cw_ordernum), AVG((_X.cw_creation_date - _X.cw_modification_date)) |
|
2056 FROM cw_CWAttribute AS _X |
|
2057 GROUP BY YEAR(_X.cw_modification_date),MONTH(_X.cw_modification_date) |
|
2058 ORDER BY 1'''), |
|
2059 |
|
2060 def test_today(self): |
|
2061 for t in self._parse([("Any X WHERE X creation_date TODAY, X is Affaire", |
|
2062 '''SELECT _X.cw_eid |
|
2063 FROM cw_Affaire AS _X |
|
2064 WHERE DATE(_X.cw_creation_date)=CURRENT_DATE'''), |
|
2065 |
|
2066 ("Personne P where not P datenaiss TODAY", |
|
2067 '''SELECT _P.cw_eid |
|
2068 FROM cw_Personne AS _P |
|
2069 WHERE NOT (DATE(_P.cw_datenaiss)=CURRENT_DATE)'''), |
|
2070 ]): |
|
2071 yield t |
|
2072 |
|
2073 |
|
2074 class MySQLGenerator(PostgresSQLGeneratorTC): |
|
2075 backend = 'mysql' |
|
2076 |
|
2077 def _norm_sql(self, sql): |
|
2078 sql = sql.strip().replace(' ILIKE ', ' LIKE ') |
|
2079 newsql = [] |
|
2080 latest = None |
|
2081 for line in sql.splitlines(False): |
|
2082 firstword = line.split(None, 1)[0] |
|
2083 if firstword == 'WHERE' and latest == 'SELECT': |
|
2084 newsql.append('FROM (SELECT 1) AS _T') |
|
2085 newsql.append(line) |
|
2086 latest = firstword |
|
2087 return '\n'.join(newsql) |
|
2088 |
|
2089 def test_date_extraction(self): |
|
2090 self._check("Any MONTH(D) WHERE P is Personne, P creation_date D", |
|
2091 '''SELECT EXTRACT(MONTH from _P.cw_creation_date) |
|
2092 FROM cw_Personne AS _P''') |
|
2093 |
|
2094 def test_weekday_extraction(self): |
|
2095 self._check("Any WEEKDAY(D) WHERE P is Personne, P creation_date D", |
|
2096 '''SELECT DAYOFWEEK(_P.cw_creation_date) |
|
2097 FROM cw_Personne AS _P''') |
|
2098 |
|
2099 def test_cast(self): |
|
2100 self._check("Any CAST(String, P) WHERE P is Personne", |
|
2101 '''SELECT CAST(_P.cw_eid AS mediumtext) |
|
2102 FROM cw_Personne AS _P''') |
|
2103 |
|
2104 def test_regexp(self): |
|
2105 self._check("Any X WHERE X login REGEXP '[0-9].*'", |
|
2106 '''SELECT _X.cw_eid |
|
2107 FROM cw_CWUser AS _X |
|
2108 WHERE _X.cw_login REGEXP [0-9].* |
|
2109 ''') |
|
2110 |
|
2111 def test_from_clause_needed(self): |
|
2112 queries = [("Any 1 WHERE EXISTS(T is CWGroup, T name 'managers')", |
|
2113 '''SELECT 1 |
|
2114 FROM (SELECT 1) AS _T |
|
2115 WHERE EXISTS(SELECT 1 FROM cw_CWGroup AS _T WHERE _T.cw_name=managers)'''), |
|
2116 ('Any X,Y WHERE NOT X created_by Y, X eid 5, Y eid 6', |
|
2117 '''SELECT 5, 6 |
|
2118 FROM (SELECT 1) AS _T |
|
2119 WHERE NOT (EXISTS(SELECT 1 FROM created_by_relation AS rel_created_by0 WHERE rel_created_by0.eid_from=5 AND rel_created_by0.eid_to=6))'''), |
|
2120 ] |
|
2121 for t in self._parse(queries): |
|
2122 yield t |
|
2123 |
|
2124 |
|
2125 def test_has_text(self): |
|
2126 queries = [ |
|
2127 ('Any X WHERE X has_text "toto tata"', |
|
2128 """SELECT appears0.uid |
|
2129 FROM appears AS appears0 |
|
2130 WHERE MATCH (appears0.words) AGAINST ('toto tata' IN BOOLEAN MODE)"""), |
|
2131 ('Personne X WHERE X has_text "toto tata"', |
|
2132 """SELECT _X.eid |
|
2133 FROM appears AS appears0, entities AS _X |
|
2134 WHERE MATCH (appears0.words) AGAINST ('toto tata' IN BOOLEAN MODE) AND appears0.uid=_X.eid AND _X.type='Personne'"""), |
|
2135 ('Personne X WHERE X has_text %(text)s', |
|
2136 """SELECT _X.eid |
|
2137 FROM appears AS appears0, entities AS _X |
|
2138 WHERE MATCH (appears0.words) AGAINST ('hip hop momo' IN BOOLEAN MODE) AND appears0.uid=_X.eid AND _X.type='Personne'"""), |
|
2139 ('Any X WHERE X has_text "toto tata", X name "tutu", X is IN (Basket,Folder)', |
|
2140 """SELECT _X.cw_eid |
|
2141 FROM appears AS appears0, cw_Basket AS _X |
|
2142 WHERE MATCH (appears0.words) AGAINST ('toto tata' IN BOOLEAN MODE) AND appears0.uid=_X.cw_eid AND _X.cw_name=tutu |
|
2143 UNION ALL |
|
2144 SELECT _X.cw_eid |
|
2145 FROM appears AS appears0, cw_Folder AS _X |
|
2146 WHERE MATCH (appears0.words) AGAINST ('toto tata' IN BOOLEAN MODE) AND appears0.uid=_X.cw_eid AND _X.cw_name=tutu |
|
2147 """) |
|
2148 ] |
|
2149 for t in self._parse(queries): |
|
2150 yield t |
|
2151 |
|
2152 |
|
2153 def test_ambigous_exists_no_from_clause(self): |
|
2154 self._check('Any COUNT(U) WHERE U eid 1, EXISTS (P owned_by U, P is IN (Note, Affaire))', |
|
2155 '''SELECT COUNT(1) |
|
2156 FROM (SELECT 1) AS _T |
|
2157 WHERE EXISTS(SELECT 1 FROM cw_Affaire AS _P, owned_by_relation AS rel_owned_by0 WHERE rel_owned_by0.eid_from=_P.cw_eid AND rel_owned_by0.eid_to=1 UNION SELECT 1 FROM cw_Note AS _P, owned_by_relation AS rel_owned_by1 WHERE rel_owned_by1.eid_from=_P.cw_eid AND rel_owned_by1.eid_to=1)''') |
|
2158 |
|
2159 def test_groupby_multiple_outerjoins(self): |
|
2160 self._check('Any A,U,P,group_concat(TN) GROUPBY A,U,P WHERE A is Affaire, A concerne N, N todo_by U?, T? tags A, T name TN, A todo_by P?', |
|
2161 '''SELECT _A.cw_eid, rel_todo_by1.eid_to, rel_todo_by3.eid_to, GROUP_CONCAT(_T.cw_name) |
|
2162 FROM concerne_relation AS rel_concerne0, cw_Affaire AS _A LEFT OUTER JOIN tags_relation AS rel_tags2 ON (rel_tags2.eid_to=_A.cw_eid) LEFT OUTER JOIN cw_Tag AS _T ON (rel_tags2.eid_from=_T.cw_eid) LEFT OUTER JOIN todo_by_relation AS rel_todo_by3 ON (rel_todo_by3.eid_from=_A.cw_eid), cw_Note AS _N LEFT OUTER JOIN todo_by_relation AS rel_todo_by1 ON (rel_todo_by1.eid_from=_N.cw_eid) |
|
2163 WHERE rel_concerne0.eid_from=_A.cw_eid AND rel_concerne0.eid_to=_N.cw_eid |
|
2164 GROUP BY _A.cw_eid,rel_todo_by1.eid_to,rel_todo_by3.eid_to''') |
|
2165 |
|
2166 def test_substring(self): |
|
2167 self._check("Any SUBSTRING(N, 1, 1) WHERE P nom N, P is Personne", |
|
2168 '''SELECT SUBSTRING(_P.cw_nom, 1, 1) |
|
2169 FROM cw_Personne AS _P''') |
|
2170 |
|
2171 |
|
2172 def test_or_having_fake_terms_base(self): |
|
2173 self._check('Any X WHERE X is CWUser, X creation_date D HAVING YEAR(D) = "2010" OR D = NULL', |
|
2174 '''SELECT _X.cw_eid |
|
2175 FROM cw_CWUser AS _X |
|
2176 WHERE ((EXTRACT(YEAR from _X.cw_creation_date)=2010) OR (_X.cw_creation_date IS NULL))''') |
|
2177 |
|
2178 |
|
2179 def test_not_no_where(self): |
|
2180 self._check('Any 1 WHERE NOT X in_group G, X is CWUser', |
|
2181 '''SELECT 1 |
|
2182 FROM (SELECT 1) AS _T |
|
2183 WHERE NOT (EXISTS(SELECT 1 FROM in_group_relation AS rel_in_group0))''') |
|
2184 |
|
2185 def test_groupby_orderby_insertion_dont_modify_intention(self): |
|
2186 self._check('Any YEAR(XECT)*100+MONTH(XECT), COUNT(X),SUM(XCE),AVG(XSCT-XECT) ' |
|
2187 'GROUPBY YEAR(XECT),MONTH(XECT) ORDERBY 1 ' |
|
2188 'WHERE X creation_date XSCT, X modification_date XECT, ' |
|
2189 'X ordernum XCE, X is CWAttribute', |
|
2190 '''SELECT ((EXTRACT(YEAR from _X.cw_modification_date) * 100) + EXTRACT(MONTH from _X.cw_modification_date)), COUNT(_X.cw_eid), SUM(_X.cw_ordernum), AVG((_X.cw_creation_date - _X.cw_modification_date)) |
|
2191 FROM cw_CWAttribute AS _X |
|
2192 GROUP BY EXTRACT(YEAR from _X.cw_modification_date),EXTRACT(MONTH from _X.cw_modification_date) |
|
2193 ORDER BY 1'''), |
|
2194 |
|
2195 def test_today(self): |
|
2196 for t in self._parse([("Any X WHERE X creation_date TODAY, X is Affaire", |
|
2197 '''SELECT _X.cw_eid |
|
2198 FROM cw_Affaire AS _X |
|
2199 WHERE DATE(_X.cw_creation_date)=CURRENT_DATE'''), |
|
2200 |
|
2201 ("Personne P where not P datenaiss TODAY", |
|
2202 '''SELECT _P.cw_eid |
|
2203 FROM cw_Personne AS _P |
|
2204 WHERE NOT (DATE(_P.cw_datenaiss)=CURRENT_DATE)'''), |
|
2205 ]): |
|
2206 yield t |
|
2207 |
|
2208 class removeUnsusedSolutionsTC(TestCase): |
|
2209 def test_invariant_not_varying(self): |
|
2210 rqlst = mock_object(defined_vars={}) |
|
2211 rqlst.defined_vars['A'] = mock_object(scope=rqlst, stinfo={}, _q_invariant=True) |
|
2212 rqlst.defined_vars['B'] = mock_object(scope=rqlst, stinfo={}, _q_invariant=False) |
|
2213 self.assertEqual(remove_unused_solutions(rqlst, [{'A': 'RugbyGroup', 'B': 'RugbyTeam'}, |
|
2214 {'A': 'FootGroup', 'B': 'FootTeam'}], {}, None), |
|
2215 ([{'A': 'RugbyGroup', 'B': 'RugbyTeam'}, |
|
2216 {'A': 'FootGroup', 'B': 'FootTeam'}], |
|
2217 {}, set('B')) |
|
2218 ) |
|
2219 |
|
2220 def test_invariant_varying(self): |
|
2221 rqlst = mock_object(defined_vars={}) |
|
2222 rqlst.defined_vars['A'] = mock_object(scope=rqlst, stinfo={}, _q_invariant=True) |
|
2223 rqlst.defined_vars['B'] = mock_object(scope=rqlst, stinfo={}, _q_invariant=False) |
|
2224 self.assertEqual(remove_unused_solutions(rqlst, [{'A': 'RugbyGroup', 'B': 'RugbyTeam'}, |
|
2225 {'A': 'FootGroup', 'B': 'RugbyTeam'}], {}, None), |
|
2226 ([{'A': 'RugbyGroup', 'B': 'RugbyTeam'}], {}, set()) |
|
2227 ) |
|
2228 |
|
2229 |
|
2230 if __name__ == '__main__': |
|
2231 unittest_main() |