34 supported_backends = ('postgres', 'sqlite', 'mysql') |
35 supported_backends = ('postgres', 'sqlite', 'mysql') |
35 try: |
36 try: |
36 register_function(stockproc) |
37 register_function(stockproc) |
37 except AssertionError, ex: |
38 except AssertionError, ex: |
38 pass # already registered |
39 pass # already registered |
|
40 |
|
41 from logilab import database as db |
|
42 def monkey_patch_import_driver_module(driver, drivers, quiet=True): |
|
43 if not driver in drivers: |
|
44 raise db.UnknownDriver(driver) |
|
45 for modname in drivers[driver]: |
|
46 try: |
|
47 if not quiet: |
|
48 print >> sys.stderr, 'Trying %s' % modname |
|
49 module = db.load_module_from_name(modname, use_sys=False) |
|
50 break |
|
51 except ImportError: |
|
52 if not quiet: |
|
53 print >> sys.stderr, '%s is not available' % modname |
|
54 continue |
|
55 else: |
|
56 return None, drivers[driver][0] |
|
57 return module, modname |
39 |
58 |
40 |
59 |
41 def setUpModule(): |
60 def setUpModule(): |
42 global config, schema |
61 global config, schema |
43 config = TestServerConfiguration('data', apphome=CWRQLTC.datadir) |
62 config = TestServerConfiguration('data', apphome=CWRQLTC.datadir) |
44 config.bootstrap_cubes() |
63 config.bootstrap_cubes() |
45 schema = config.load_schema() |
64 schema = config.load_schema() |
46 schema['in_state'].inlined = True |
65 schema['in_state'].inlined = True |
47 schema['state_of'].inlined = False |
66 schema['state_of'].inlined = False |
48 schema['comments'].inlined = False |
67 schema['comments'].inlined = False |
|
68 db._backup_import_driver_module = db._import_driver_module |
|
69 db._import_driver_module = monkey_patch_import_driver_module |
49 |
70 |
50 def tearDownModule(): |
71 def tearDownModule(): |
51 global config, schema |
72 global config, schema |
52 del config, schema |
73 del config, schema |
|
74 db._import_driver_module = db._backup_import_driver_module |
|
75 del db._backup_import_driver_module |
53 |
76 |
54 PARSER = [ |
77 PARSER = [ |
55 (r"Personne P WHERE P nom 'Zig\'oto';", |
78 (r"Personne P WHERE P nom 'Zig\'oto';", |
56 '''SELECT _P.cw_eid |
79 '''SELECT _P.cw_eid |
57 FROM cw_Personne AS _P |
80 FROM cw_Personne AS _P |
91 WHERE _P.cw_test=FALSE'''), |
114 WHERE _P.cw_test=FALSE'''), |
92 |
115 |
93 ("Personne P WHERE P eid -1", |
116 ("Personne P WHERE P eid -1", |
94 '''SELECT -1'''), |
117 '''SELECT -1'''), |
95 |
118 |
|
119 ("Personne P WHERE S is Societe, P travaille S, S nom 'Logilab';", |
|
120 '''SELECT rel_travaille0.eid_from |
|
121 FROM cw_Societe AS _S, travaille_relation AS rel_travaille0 |
|
122 WHERE rel_travaille0.eid_to=_S.cw_eid AND _S.cw_nom=Logilab'''), |
|
123 |
|
124 ("Personne P WHERE P concerne A, A concerne S, S nom 'Logilab', S is Societe;", |
|
125 '''SELECT rel_concerne0.eid_from |
|
126 FROM concerne_relation AS rel_concerne0, concerne_relation AS rel_concerne1, cw_Societe AS _S |
|
127 WHERE rel_concerne0.eid_to=rel_concerne1.eid_from AND rel_concerne1.eid_to=_S.cw_eid AND _S.cw_nom=Logilab'''), |
|
128 |
|
129 ("Note N WHERE X evaluee N, X nom 'Logilab';", |
|
130 '''SELECT rel_evaluee0.eid_to |
|
131 FROM cw_Division AS _X, evaluee_relation AS rel_evaluee0 |
|
132 WHERE rel_evaluee0.eid_from=_X.cw_eid AND _X.cw_nom=Logilab |
|
133 UNION ALL |
|
134 SELECT rel_evaluee0.eid_to |
|
135 FROM cw_Personne AS _X, evaluee_relation AS rel_evaluee0 |
|
136 WHERE rel_evaluee0.eid_from=_X.cw_eid AND _X.cw_nom=Logilab |
|
137 UNION ALL |
|
138 SELECT rel_evaluee0.eid_to |
|
139 FROM cw_Societe AS _X, evaluee_relation AS rel_evaluee0 |
|
140 WHERE rel_evaluee0.eid_from=_X.cw_eid AND _X.cw_nom=Logilab |
|
141 UNION ALL |
|
142 SELECT rel_evaluee0.eid_to |
|
143 FROM cw_SubDivision AS _X, evaluee_relation AS rel_evaluee0 |
|
144 WHERE rel_evaluee0.eid_from=_X.cw_eid AND _X.cw_nom=Logilab'''), |
|
145 |
|
146 ("Note N WHERE X evaluee N, X nom in ('Logilab', 'Caesium');", |
|
147 '''SELECT rel_evaluee0.eid_to |
|
148 FROM cw_Division AS _X, evaluee_relation AS rel_evaluee0 |
|
149 WHERE rel_evaluee0.eid_from=_X.cw_eid AND _X.cw_nom IN(Logilab, Caesium) |
|
150 UNION ALL |
|
151 SELECT rel_evaluee0.eid_to |
|
152 FROM cw_Personne AS _X, evaluee_relation AS rel_evaluee0 |
|
153 WHERE rel_evaluee0.eid_from=_X.cw_eid AND _X.cw_nom IN(Logilab, Caesium) |
|
154 UNION ALL |
|
155 SELECT rel_evaluee0.eid_to |
|
156 FROM cw_Societe AS _X, evaluee_relation AS rel_evaluee0 |
|
157 WHERE rel_evaluee0.eid_from=_X.cw_eid AND _X.cw_nom IN(Logilab, Caesium) |
|
158 UNION ALL |
|
159 SELECT rel_evaluee0.eid_to |
|
160 FROM cw_SubDivision AS _X, evaluee_relation AS rel_evaluee0 |
|
161 WHERE rel_evaluee0.eid_from=_X.cw_eid AND _X.cw_nom IN(Logilab, Caesium)'''), |
|
162 |
|
163 ("Any X WHERE X creation_date TODAY, X is Affaire", |
|
164 '''SELECT _X.cw_eid |
|
165 FROM cw_Affaire AS _X |
|
166 WHERE DATE(_X.cw_creation_date)=CURRENT_DATE'''), |
|
167 |
|
168 ("Any N WHERE G is CWGroup, G name N, E eid 12, E read_permission G", |
|
169 '''SELECT _G.cw_name |
|
170 FROM cw_CWGroup AS _G, read_permission_relation AS rel_read_permission0 |
|
171 WHERE rel_read_permission0.eid_from=12 AND rel_read_permission0.eid_to=_G.cw_eid'''), |
|
172 |
|
173 ('Any Y WHERE U login "admin", U login Y', # stupid but valid... |
|
174 """SELECT _U.cw_login |
|
175 FROM cw_CWUser AS _U |
|
176 WHERE _U.cw_login=admin"""), |
|
177 |
|
178 ('Any T WHERE T tags X, X is State', |
|
179 '''SELECT rel_tags0.eid_from |
|
180 FROM cw_State AS _X, tags_relation AS rel_tags0 |
|
181 WHERE rel_tags0.eid_to=_X.cw_eid'''), |
|
182 |
|
183 ('Any X,Y WHERE X eid 0, Y eid 1, X concerne Y', |
|
184 '''SELECT 0, 1 |
|
185 FROM concerne_relation AS rel_concerne0 |
|
186 WHERE rel_concerne0.eid_from=0 AND rel_concerne0.eid_to=1'''), |
|
187 |
|
188 ("Any X WHERE X prenom 'lulu'," |
|
189 "EXISTS(X owned_by U, U in_group G, G name 'lulufanclub' OR G name 'managers');", |
|
190 '''SELECT _X.cw_eid |
|
191 FROM cw_Personne AS _X |
|
192 WHERE _X.cw_prenom=lulu AND EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0, in_group_relation AS rel_in_group1, cw_CWGroup AS _G 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)))'''), |
|
193 |
|
194 ("Any X WHERE X prenom 'lulu'," |
|
195 "NOT EXISTS(X owned_by U, U in_group G, G name 'lulufanclub' OR G name 'managers');", |
|
196 '''SELECT _X.cw_eid |
|
197 FROM cw_Personne AS _X |
|
198 WHERE _X.cw_prenom=lulu AND NOT (EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0, in_group_relation AS rel_in_group1, cw_CWGroup AS _G 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))))'''), |
|
199 |
|
200 ('Any X WHERE X title V, NOT X wikiid V, NOT X title "parent", X is Card', |
|
201 '''SELECT _X.cw_eid |
|
202 FROM cw_Card AS _X |
|
203 WHERE NOT (_X.cw_wikiid=_X.cw_title) AND NOT (_X.cw_title=parent)''') |
|
204 ] |
|
205 |
|
206 BASIC_WITH_LIMIT = [ |
96 ("Personne P LIMIT 20 OFFSET 10", |
207 ("Personne P LIMIT 20 OFFSET 10", |
97 '''SELECT _P.cw_eid |
208 '''SELECT _P.cw_eid |
98 FROM cw_Personne AS _P |
209 FROM cw_Personne AS _P |
99 LIMIT 20 |
210 LIMIT 20 |
100 OFFSET 10'''), |
211 OFFSET 10'''), |
101 |
212 ] |
102 ("Personne P WHERE S is Societe, P travaille S, S nom 'Logilab';", |
|
103 '''SELECT rel_travaille0.eid_from |
|
104 FROM cw_Societe AS _S, travaille_relation AS rel_travaille0 |
|
105 WHERE rel_travaille0.eid_to=_S.cw_eid AND _S.cw_nom=Logilab'''), |
|
106 |
|
107 ("Personne P WHERE P concerne A, A concerne S, S nom 'Logilab', S is Societe;", |
|
108 '''SELECT rel_concerne0.eid_from |
|
109 FROM concerne_relation AS rel_concerne0, concerne_relation AS rel_concerne1, cw_Societe AS _S |
|
110 WHERE rel_concerne0.eid_to=rel_concerne1.eid_from AND rel_concerne1.eid_to=_S.cw_eid AND _S.cw_nom=Logilab'''), |
|
111 |
|
112 ("Note N WHERE X evaluee N, X nom 'Logilab';", |
|
113 '''SELECT rel_evaluee0.eid_to |
|
114 FROM cw_Division AS _X, evaluee_relation AS rel_evaluee0 |
|
115 WHERE rel_evaluee0.eid_from=_X.cw_eid AND _X.cw_nom=Logilab |
|
116 UNION ALL |
|
117 SELECT rel_evaluee0.eid_to |
|
118 FROM cw_Personne AS _X, evaluee_relation AS rel_evaluee0 |
|
119 WHERE rel_evaluee0.eid_from=_X.cw_eid AND _X.cw_nom=Logilab |
|
120 UNION ALL |
|
121 SELECT rel_evaluee0.eid_to |
|
122 FROM cw_Societe AS _X, evaluee_relation AS rel_evaluee0 |
|
123 WHERE rel_evaluee0.eid_from=_X.cw_eid AND _X.cw_nom=Logilab |
|
124 UNION ALL |
|
125 SELECT rel_evaluee0.eid_to |
|
126 FROM cw_SubDivision AS _X, evaluee_relation AS rel_evaluee0 |
|
127 WHERE rel_evaluee0.eid_from=_X.cw_eid AND _X.cw_nom=Logilab'''), |
|
128 |
|
129 ("Note N WHERE X evaluee N, X nom in ('Logilab', 'Caesium');", |
|
130 '''SELECT rel_evaluee0.eid_to |
|
131 FROM cw_Division AS _X, evaluee_relation AS rel_evaluee0 |
|
132 WHERE rel_evaluee0.eid_from=_X.cw_eid AND _X.cw_nom IN(Logilab, Caesium) |
|
133 UNION ALL |
|
134 SELECT rel_evaluee0.eid_to |
|
135 FROM cw_Personne AS _X, evaluee_relation AS rel_evaluee0 |
|
136 WHERE rel_evaluee0.eid_from=_X.cw_eid AND _X.cw_nom IN(Logilab, Caesium) |
|
137 UNION ALL |
|
138 SELECT rel_evaluee0.eid_to |
|
139 FROM cw_Societe AS _X, evaluee_relation AS rel_evaluee0 |
|
140 WHERE rel_evaluee0.eid_from=_X.cw_eid AND _X.cw_nom IN(Logilab, Caesium) |
|
141 UNION ALL |
|
142 SELECT rel_evaluee0.eid_to |
|
143 FROM cw_SubDivision AS _X, evaluee_relation AS rel_evaluee0 |
|
144 WHERE rel_evaluee0.eid_from=_X.cw_eid AND _X.cw_nom IN(Logilab, Caesium)'''), |
|
145 |
|
146 ("Any X WHERE X creation_date TODAY, X is Affaire", |
|
147 '''SELECT _X.cw_eid |
|
148 FROM cw_Affaire AS _X |
|
149 WHERE DATE(_X.cw_creation_date)=CURRENT_DATE'''), |
|
150 |
|
151 ("Any N WHERE G is CWGroup, G name N, E eid 12, E read_permission G", |
|
152 '''SELECT _G.cw_name |
|
153 FROM cw_CWGroup AS _G, read_permission_relation AS rel_read_permission0 |
|
154 WHERE rel_read_permission0.eid_from=12 AND rel_read_permission0.eid_to=_G.cw_eid'''), |
|
155 |
|
156 ('Any Y WHERE U login "admin", U login Y', # stupid but valid... |
|
157 """SELECT _U.cw_login |
|
158 FROM cw_CWUser AS _U |
|
159 WHERE _U.cw_login=admin"""), |
|
160 |
|
161 ('Any T WHERE T tags X, X is State', |
|
162 '''SELECT rel_tags0.eid_from |
|
163 FROM cw_State AS _X, tags_relation AS rel_tags0 |
|
164 WHERE rel_tags0.eid_to=_X.cw_eid'''), |
|
165 |
|
166 ('Any X,Y WHERE X eid 0, Y eid 1, X concerne Y', |
|
167 '''SELECT 0, 1 |
|
168 FROM concerne_relation AS rel_concerne0 |
|
169 WHERE rel_concerne0.eid_from=0 AND rel_concerne0.eid_to=1'''), |
|
170 |
|
171 ("Any X WHERE X prenom 'lulu'," |
|
172 "EXISTS(X owned_by U, U in_group G, G name 'lulufanclub' OR G name 'managers');", |
|
173 '''SELECT _X.cw_eid |
|
174 FROM cw_Personne AS _X |
|
175 WHERE _X.cw_prenom=lulu AND EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0, in_group_relation AS rel_in_group1, cw_CWGroup AS _G 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)))'''), |
|
176 |
|
177 ("Any X WHERE X prenom 'lulu'," |
|
178 "NOT EXISTS(X owned_by U, U in_group G, G name 'lulufanclub' OR G name 'managers');", |
|
179 '''SELECT _X.cw_eid |
|
180 FROM cw_Personne AS _X |
|
181 WHERE _X.cw_prenom=lulu AND NOT (EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0, in_group_relation AS rel_in_group1, cw_CWGroup AS _G 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))))'''), |
|
182 |
|
183 ('Any X WHERE X title V, NOT X wikiid V, NOT X title "parent", X is Card', |
|
184 '''SELECT _X.cw_eid |
|
185 FROM cw_Card AS _X |
|
186 WHERE NOT (_X.cw_wikiid=_X.cw_title) AND NOT (_X.cw_title=parent)''') |
|
187 ] |
|
188 |
213 |
189 |
214 |
190 ADVANCED = [ |
215 ADVANCED = [ |
191 ("Societe S WHERE S nom 'Logilab' OR S nom 'Caesium'", |
216 ("Societe S WHERE S nom 'Logilab' OR S nom 'Caesium'", |
192 '''SELECT _S.cw_eid |
217 '''SELECT _S.cw_eid |
433 '''SELECT (MAX(_X.cw_eid) + MIN(LENGTH(_X.cw_data))), _X.cw_data_name |
445 '''SELECT (MAX(_X.cw_eid) + MIN(LENGTH(_X.cw_data))), _X.cw_data_name |
434 FROM cw_File AS _X |
446 FROM cw_File AS _X |
435 GROUP BY _X.cw_data_name,_X.cw_data_format |
447 GROUP BY _X.cw_data_name,_X.cw_data_format |
436 ORDER BY 1,2,_X.cw_data_format'''), |
448 ORDER BY 1,2,_X.cw_data_format'''), |
437 |
449 |
|
450 # ambiguity in EXISTS() -> should union the sub-query |
|
451 ('Any T WHERE T is Tag, NOT T name in ("t1", "t2"), EXISTS(T tags X, X is IN (CWUser, CWGroup))', |
|
452 '''SELECT _T.cw_eid |
|
453 FROM cw_Tag AS _T |
|
454 WHERE NOT (_T.cw_name IN(t1, t2)) AND EXISTS(SELECT 1 FROM tags_relation AS rel_tags0, cw_CWGroup AS _X WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid UNION SELECT 1 FROM tags_relation AS rel_tags1, cw_CWUser AS _X WHERE rel_tags1.eid_from=_T.cw_eid AND rel_tags1.eid_to=_X.cw_eid)'''), |
|
455 |
|
456 # must not use a relation in EXISTS scope to inline a variable |
|
457 ('Any U WHERE U eid IN (1,2), EXISTS(X owned_by U)', |
|
458 '''SELECT _U.cw_eid |
|
459 FROM cw_CWUser AS _U |
|
460 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)'''), |
|
461 |
|
462 ('Any U WHERE EXISTS(U eid IN (1,2), X owned_by U)', |
|
463 '''SELECT _U.cw_eid |
|
464 FROM cw_CWUser AS _U |
|
465 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)'''), |
|
466 |
|
467 ('Any COUNT(U) WHERE EXISTS (P owned_by U, P is IN (Note, Affaire))', |
|
468 '''SELECT COUNT(_U.cw_eid) |
|
469 FROM cw_CWUser AS _U |
|
470 WHERE EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0, cw_Affaire AS _P WHERE rel_owned_by0.eid_from=_P.cw_eid AND rel_owned_by0.eid_to=_U.cw_eid UNION SELECT 1 FROM owned_by_relation AS rel_owned_by1, cw_Note AS _P WHERE rel_owned_by1.eid_from=_P.cw_eid AND rel_owned_by1.eid_to=_U.cw_eid)'''), |
|
471 |
|
472 ('Any MAX(X)', |
|
473 '''SELECT MAX(_X.eid) |
|
474 FROM entities AS _X'''), |
|
475 |
|
476 ('Any MAX(X) WHERE X is Note', |
|
477 '''SELECT MAX(_X.cw_eid) |
|
478 FROM cw_Note AS _X'''), |
|
479 |
|
480 ('Any X WHERE X eid > 12', |
|
481 '''SELECT _X.eid |
|
482 FROM entities AS _X |
|
483 WHERE _X.eid>12'''), |
|
484 |
|
485 ('Any X WHERE X eid > 12, X is Note', |
|
486 """SELECT _X.eid |
|
487 FROM entities AS _X |
|
488 WHERE _X.type='Note' AND _X.eid>12"""), |
|
489 |
|
490 ('Any X, T WHERE X eid > 12, X title T, X is IN (Bookmark, Card)', |
|
491 """SELECT _X.cw_eid, _X.cw_title |
|
492 FROM cw_Bookmark AS _X |
|
493 WHERE _X.cw_eid>12 |
|
494 UNION ALL |
|
495 SELECT _X.cw_eid, _X.cw_title |
|
496 FROM cw_Card AS _X |
|
497 WHERE _X.cw_eid>12"""), |
|
498 |
|
499 ('Any X', |
|
500 '''SELECT _X.eid |
|
501 FROM entities AS _X'''), |
|
502 |
|
503 ('Any X GROUPBY X WHERE X eid 12', |
|
504 '''SELECT 12'''), |
|
505 |
|
506 ('Any X GROUPBY X ORDERBY Y WHERE X eid 12, X login Y', |
|
507 '''SELECT _X.cw_eid |
|
508 FROM cw_CWUser AS _X |
|
509 WHERE _X.cw_eid=12 |
|
510 GROUP BY _X.cw_eid,_X.cw_login |
|
511 ORDER BY _X.cw_login'''), |
|
512 |
|
513 ('Any U,COUNT(X) GROUPBY U WHERE U eid 12, X owned_by U HAVING COUNT(X) > 10', |
|
514 '''SELECT rel_owned_by0.eid_to, COUNT(rel_owned_by0.eid_from) |
|
515 FROM owned_by_relation AS rel_owned_by0 |
|
516 WHERE rel_owned_by0.eid_to=12 |
|
517 GROUP BY rel_owned_by0.eid_to |
|
518 HAVING COUNT(rel_owned_by0.eid_from)>10'''), |
|
519 |
|
520 |
|
521 ("Any X WHERE X eid 0, X test TRUE", |
|
522 '''SELECT _X.cw_eid |
|
523 FROM cw_Personne AS _X |
|
524 WHERE _X.cw_eid=0 AND _X.cw_test=TRUE'''), |
|
525 |
|
526 ('Any 1 WHERE X in_group G, X is CWUser', |
|
527 '''SELECT 1 |
|
528 FROM in_group_relation AS rel_in_group0'''), |
|
529 |
|
530 ('CWEType X WHERE X name CV, X description V HAVING NOT V=CV AND NOT V = "parent"', |
|
531 '''SELECT _X.cw_eid |
|
532 FROM cw_CWEType AS _X |
|
533 WHERE NOT (EXISTS(SELECT 1 WHERE _X.cw_description=parent)) AND NOT (EXISTS(SELECT 1 WHERE _X.cw_description=_X.cw_name))'''), |
|
534 ('CWEType X WHERE X name CV, X description V HAVING V!=CV AND V != "parent"', |
|
535 '''SELECT _X.cw_eid |
|
536 FROM cw_CWEType AS _X |
|
537 WHERE _X.cw_description!=parent AND _X.cw_description!=_X.cw_name'''), |
|
538 ] |
|
539 |
|
540 ADVANCED_WITH_GROUP_CONCAT = [ |
|
541 ("Any X,GROUP_CONCAT(TN) GROUPBY X ORDERBY XN WHERE T tags X, X name XN, T name TN, X is CWGroup", |
|
542 '''SELECT _X.cw_eid, GROUP_CONCAT(_T.cw_name) |
|
543 FROM cw_CWGroup AS _X, cw_Tag AS _T, tags_relation AS rel_tags0 |
|
544 WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid |
|
545 GROUP BY _X.cw_eid,_X.cw_name |
|
546 ORDER BY _X.cw_name'''), |
|
547 |
|
548 ("Any X,GROUP_CONCAT(TN) GROUPBY X ORDERBY XN WHERE T tags X, X name XN, T name TN", |
|
549 '''SELECT T1.C0, GROUP_CONCAT(T1.C1) FROM (SELECT _X.cw_eid AS C0, _T.cw_name AS C1, _X.cw_name AS C2 |
|
550 FROM cw_CWGroup AS _X, cw_Tag AS _T, tags_relation AS rel_tags0 |
|
551 WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid |
|
552 UNION ALL |
|
553 SELECT _X.cw_eid AS C0, _T.cw_name AS C1, _X.cw_name AS C2 |
|
554 FROM cw_State AS _X, cw_Tag AS _T, tags_relation AS rel_tags0 |
|
555 WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid |
|
556 UNION ALL |
|
557 SELECT _X.cw_eid AS C0, _T.cw_name AS C1, _X.cw_name AS C2 |
|
558 FROM cw_Tag AS _T, cw_Tag AS _X, tags_relation AS rel_tags0 |
|
559 WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid) AS T1 |
|
560 GROUP BY T1.C0,T1.C2 |
|
561 ORDER BY T1.C2'''), |
|
562 |
|
563 ] |
|
564 |
|
565 ADVANCED_WITH_LIMIT_OR_ORDERBY = [ |
|
566 ('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"))', |
|
567 '''SELECT COUNT(rel_concerne0.eid_from), _C.cw_nom |
|
568 FROM concerne_relation AS rel_concerne0, cw_Societe AS _C |
|
569 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 documented_by_relation AS rel_documented_by2, cw_Card AS _N WHERE rel_concerne0.eid_from=rel_documented_by2.eid_from AND rel_documented_by2.eid_to=_N.cw_eid AND _N.cw_title=published))) |
|
570 GROUP BY _C.cw_nom |
|
571 ORDER BY 1 DESC |
|
572 LIMIT 10'''), |
|
573 ('DISTINCT Any S ORDERBY stockproc(SI) WHERE NOT S ecrit_par O, S para SI', |
|
574 '''SELECT T1.C0 FROM (SELECT DISTINCT _S.cw_eid AS C0, STOCKPROC(_S.cw_para) AS C1 |
|
575 FROM cw_Note AS _S |
|
576 WHERE _S.cw_ecrit_par IS NULL |
|
577 ORDER BY 2) AS T1'''), |
|
578 |
438 ('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;', |
579 ('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;', |
439 '''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 |
580 '''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 |
440 FROM cw_File AS _X |
581 FROM cw_File AS _X |
441 GROUP BY _X.cw_data_name,_X.cw_data_format |
582 GROUP BY _X.cw_data_name,_X.cw_data_format |
442 ORDER BY 2,3) AS T1 |
583 ORDER BY 2,3) AS T1 |
443 '''), |
584 '''), |
444 |
|
445 # ambiguity in EXISTS() -> should union the sub-query |
|
446 ('Any T WHERE T is Tag, NOT T name in ("t1", "t2"), EXISTS(T tags X, X is IN (CWUser, CWGroup))', |
|
447 '''SELECT _T.cw_eid |
|
448 FROM cw_Tag AS _T |
|
449 WHERE NOT (_T.cw_name IN(t1, t2)) AND EXISTS(SELECT 1 FROM tags_relation AS rel_tags0, cw_CWGroup AS _X WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid UNION SELECT 1 FROM tags_relation AS rel_tags1, cw_CWUser AS _X WHERE rel_tags1.eid_from=_T.cw_eid AND rel_tags1.eid_to=_X.cw_eid)'''), |
|
450 |
|
451 # must not use a relation in EXISTS scope to inline a variable |
|
452 ('Any U WHERE U eid IN (1,2), EXISTS(X owned_by U)', |
|
453 '''SELECT _U.cw_eid |
|
454 FROM cw_CWUser AS _U |
|
455 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)'''), |
|
456 |
|
457 ('Any U WHERE EXISTS(U eid IN (1,2), X owned_by U)', |
|
458 '''SELECT _U.cw_eid |
|
459 FROM cw_CWUser AS _U |
|
460 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)'''), |
|
461 |
|
462 ('Any COUNT(U) WHERE EXISTS (P owned_by U, P is IN (Note, Affaire))', |
|
463 '''SELECT COUNT(_U.cw_eid) |
|
464 FROM cw_CWUser AS _U |
|
465 WHERE EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0, cw_Affaire AS _P WHERE rel_owned_by0.eid_from=_P.cw_eid AND rel_owned_by0.eid_to=_U.cw_eid UNION SELECT 1 FROM owned_by_relation AS rel_owned_by1, cw_Note AS _P WHERE rel_owned_by1.eid_from=_P.cw_eid AND rel_owned_by1.eid_to=_U.cw_eid)'''), |
|
466 |
|
467 ('Any MAX(X)', |
|
468 '''SELECT MAX(_X.eid) |
|
469 FROM entities AS _X'''), |
|
470 |
|
471 ('Any MAX(X) WHERE X is Note', |
|
472 '''SELECT MAX(_X.cw_eid) |
|
473 FROM cw_Note AS _X'''), |
|
474 |
|
475 ('Any X WHERE X eid > 12', |
|
476 '''SELECT _X.eid |
|
477 FROM entities AS _X |
|
478 WHERE _X.eid>12'''), |
|
479 |
|
480 ('Any X WHERE X eid > 12, X is Note', |
|
481 """SELECT _X.eid |
|
482 FROM entities AS _X |
|
483 WHERE _X.type='Note' AND _X.eid>12"""), |
|
484 |
|
485 ('Any X, T WHERE X eid > 12, X title T, X is IN (Bookmark, Card)', |
|
486 """SELECT _X.cw_eid, _X.cw_title |
|
487 FROM cw_Bookmark AS _X |
|
488 WHERE _X.cw_eid>12 |
|
489 UNION ALL |
|
490 SELECT _X.cw_eid, _X.cw_title |
|
491 FROM cw_Card AS _X |
|
492 WHERE _X.cw_eid>12"""), |
|
493 |
|
494 ('Any X', |
|
495 '''SELECT _X.eid |
|
496 FROM entities AS _X'''), |
|
497 |
|
498 ('Any X GROUPBY X WHERE X eid 12', |
|
499 '''SELECT 12'''), |
|
500 |
|
501 ('Any X GROUPBY X ORDERBY Y WHERE X eid 12, X login Y', |
|
502 '''SELECT _X.cw_eid |
|
503 FROM cw_CWUser AS _X |
|
504 WHERE _X.cw_eid=12 |
|
505 GROUP BY _X.cw_eid,_X.cw_login |
|
506 ORDER BY _X.cw_login'''), |
|
507 |
|
508 ('Any U,COUNT(X) GROUPBY U WHERE U eid 12, X owned_by U HAVING COUNT(X) > 10', |
|
509 '''SELECT rel_owned_by0.eid_to, COUNT(rel_owned_by0.eid_from) |
|
510 FROM owned_by_relation AS rel_owned_by0 |
|
511 WHERE rel_owned_by0.eid_to=12 |
|
512 GROUP BY rel_owned_by0.eid_to |
|
513 HAVING COUNT(rel_owned_by0.eid_from)>10'''), |
|
514 |
585 |
515 ('DISTINCT Any X ORDERBY stockproc(X) WHERE U login X', |
586 ('DISTINCT Any X ORDERBY stockproc(X) WHERE U login X', |
516 '''SELECT T1.C0 FROM (SELECT DISTINCT _U.cw_login AS C0, STOCKPROC(_U.cw_login) AS C1 |
587 '''SELECT T1.C0 FROM (SELECT DISTINCT _U.cw_login AS C0, STOCKPROC(_U.cw_login) AS C1 |
517 FROM cw_CWUser AS _U |
588 FROM cw_CWUser AS _U |
518 ORDER BY 2) AS T1'''), |
589 ORDER BY 2) AS T1'''), |
544 FROM cw_EmailAddress AS _O |
615 FROM cw_EmailAddress AS _O |
545 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)) |
616 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)) |
546 ORDER BY 4 DESC'''), |
617 ORDER BY 4 DESC'''), |
547 |
618 |
548 |
619 |
549 ("Any X WHERE X eid 0, X test TRUE", |
|
550 '''SELECT _X.cw_eid |
|
551 FROM cw_Personne AS _X |
|
552 WHERE _X.cw_eid=0 AND _X.cw_test=TRUE'''), |
|
553 |
|
554 ("Any X,GROUP_CONCAT(TN) GROUPBY X ORDERBY XN WHERE T tags X, X name XN, T name TN, X is CWGroup", |
|
555 '''SELECT _X.cw_eid, GROUP_CONCAT(_T.cw_name) |
|
556 FROM cw_CWGroup AS _X, cw_Tag AS _T, tags_relation AS rel_tags0 |
|
557 WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid |
|
558 GROUP BY _X.cw_eid,_X.cw_name |
|
559 ORDER BY _X.cw_name'''), |
|
560 |
|
561 ("Any X,GROUP_CONCAT(TN) GROUPBY X ORDERBY XN WHERE T tags X, X name XN, T name TN", |
|
562 '''SELECT T1.C0, GROUP_CONCAT(T1.C1) FROM (SELECT _X.cw_eid AS C0, _T.cw_name AS C1, _X.cw_name AS C2 |
|
563 FROM cw_CWGroup AS _X, cw_Tag AS _T, tags_relation AS rel_tags0 |
|
564 WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid |
|
565 UNION ALL |
|
566 SELECT _X.cw_eid AS C0, _T.cw_name AS C1, _X.cw_name AS C2 |
|
567 FROM cw_State AS _X, cw_Tag AS _T, tags_relation AS rel_tags0 |
|
568 WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid |
|
569 UNION ALL |
|
570 SELECT _X.cw_eid AS C0, _T.cw_name AS C1, _X.cw_name AS C2 |
|
571 FROM cw_Tag AS _T, cw_Tag AS _X, tags_relation AS rel_tags0 |
|
572 WHERE rel_tags0.eid_from=_T.cw_eid AND rel_tags0.eid_to=_X.cw_eid) AS T1 |
|
573 GROUP BY T1.C0,T1.C2 |
|
574 ORDER BY T1.C2'''), |
|
575 |
|
576 ('Any 1 WHERE X in_group G, X is CWUser', |
|
577 '''SELECT 1 |
|
578 FROM in_group_relation AS rel_in_group0'''), |
|
579 |
|
580 ('CWEType X WHERE X name CV, X description V HAVING NOT V=CV AND NOT V = "parent"', |
|
581 '''SELECT _X.cw_eid |
|
582 FROM cw_CWEType AS _X |
|
583 WHERE NOT (EXISTS(SELECT 1 WHERE _X.cw_description=parent)) AND NOT (EXISTS(SELECT 1 WHERE _X.cw_description=_X.cw_name))'''), |
|
584 ('CWEType X WHERE X name CV, X description V HAVING V!=CV AND V != "parent"', |
|
585 '''SELECT _X.cw_eid |
|
586 FROM cw_CWEType AS _X |
|
587 WHERE _X.cw_description!=parent AND _X.cw_description!=_X.cw_name'''), |
|
588 ] |
620 ] |
589 |
|
590 |
621 |
591 MULTIPLE_SEL = [ |
622 MULTIPLE_SEL = [ |
592 ("DISTINCT Any X,Y where P is Personne, P nom X , P prenom Y;", |
623 ("DISTINCT Any X,Y where P is Personne, P nom X , P prenom Y;", |
593 '''SELECT DISTINCT _P.cw_nom, _P.cw_prenom |
624 '''SELECT DISTINCT _P.cw_nom, _P.cw_prenom |
594 FROM cw_Personne AS _P'''), |
625 FROM cw_Personne AS _P'''), |
710 |
741 |
711 ('Any S WHERE NOT(X in_state S, S name "somename"), X is CWUser', |
742 ('Any S WHERE NOT(X in_state S, S name "somename"), X is CWUser', |
712 '''SELECT _S.cw_eid |
743 '''SELECT _S.cw_eid |
713 FROM cw_State AS _S |
744 FROM cw_State AS _S |
714 WHERE NOT (EXISTS(SELECT 1 FROM cw_CWUser AS _X WHERE _X.cw_in_state=_S.cw_eid AND _S.cw_name=somename))'''), |
745 WHERE NOT (EXISTS(SELECT 1 FROM cw_CWUser AS _X WHERE _X.cw_in_state=_S.cw_eid AND _S.cw_name=somename))'''), |
|
746 ] |
|
747 |
|
748 HAS_TEXT_LG_INDEXER = [ |
|
749 ('Any X WHERE X has_text "toto tata"', |
|
750 """SELECT DISTINCT appears0.uid |
|
751 FROM appears AS appears0 |
|
752 WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata'))"""), |
|
753 ('Personne X WHERE X has_text "toto tata"', |
|
754 """SELECT DISTINCT _X.eid |
|
755 FROM appears AS appears0, entities AS _X |
|
756 WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata')) AND appears0.uid=_X.eid AND _X.type='Personne'"""), |
|
757 ('Personne X WHERE X has_text %(text)s', |
|
758 """SELECT DISTINCT _X.eid |
|
759 FROM appears AS appears0, entities AS _X |
|
760 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' |
|
761 """), |
|
762 ('Any X WHERE X has_text "toto tata", X name "tutu", X is IN (Basket,Folder)', |
|
763 """SELECT DISTINCT _X.cw_eid |
|
764 FROM appears AS appears0, cw_Basket AS _X |
|
765 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 |
|
766 UNION |
|
767 SELECT DISTINCT _X.cw_eid |
|
768 FROM appears AS appears0, cw_Folder AS _X |
|
769 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""") |
|
770 ] |
|
771 |
|
772 |
715 |
773 |
716 # XXXFIXME fail |
774 # XXXFIXME fail |
717 # ('Any X,RT WHERE X relation_type RT?, NOT X is CWAttribute', |
775 # ('Any X,RT WHERE X relation_type RT?, NOT X is CWAttribute', |
718 # '''SELECT _X.cw_eid, _X.cw_relation_type |
776 # '''SELECT _X.cw_eid, _X.cw_relation_type |
719 # FROM cw_CWRelation AS _X'''), |
777 # FROM cw_CWRelation AS _X'''), |
720 ] |
778 |
721 |
779 |
722 OUTER_JOIN = [ |
780 OUTER_JOIN = [ |
723 ('Any X,S WHERE X travaille S?', |
781 ('Any X,S WHERE X travaille S?', |
724 '''SELECT _X.cw_eid, rel_travaille0.eid_to |
782 '''SELECT _X.cw_eid, rel_travaille0.eid_to |
725 FROM cw_Personne AS _X LEFT OUTER JOIN travaille_relation AS rel_travaille0 ON (rel_travaille0.eid_from=_X.cw_eid)''' |
783 FROM cw_Personne AS _X LEFT OUTER JOIN travaille_relation AS rel_travaille0 ON (rel_travaille0.eid_from=_X.cw_eid)''' |
1506 # We can't actually know if we want to check if there are some |
1566 # We can't actually know if we want to check if there are some |
1507 # X without in_group relation, or some G without it. |
1567 # X without in_group relation, or some G without it. |
1508 self._check('Any 1 WHERE NOT X in_group G, X is CWUser', |
1568 self._check('Any 1 WHERE NOT X in_group G, X is CWUser', |
1509 '''SELECT 1 |
1569 '''SELECT 1 |
1510 WHERE NOT (EXISTS(SELECT 1 FROM in_group_relation AS rel_in_group0))''') |
1570 WHERE NOT (EXISTS(SELECT 1 FROM in_group_relation AS rel_in_group0))''') |
|
1571 |
|
1572 class SqlServer2005SQLGeneratorTC(PostgresSQLGeneratorTC): |
|
1573 backend = 'sqlserver2005' |
|
1574 def _norm_sql(self, sql): |
|
1575 return sql.strip().replace(' SUBSTR', ' SUBSTRING').replace(' || ', ' + ').replace(' ILIKE ', ' LIKE ') |
|
1576 |
|
1577 def test_has_text(self): |
|
1578 for t in self._parse(HAS_TEXT_LG_INDEXER): |
|
1579 yield t |
|
1580 |
|
1581 def test_or_having_fake_terms(self): |
|
1582 self._check('Any X WHERE X is CWUser, X creation_date D HAVING YEAR(D) = "2010" OR D = NULL', |
|
1583 '''SELECT _X.cw_eid |
|
1584 FROM cw_CWUser AS _X |
|
1585 WHERE ((YEAR(_X.cw_creation_date)=2010) OR (_X.cw_creation_date IS NULL))''') |
|
1586 |
|
1587 def test_date_extraction(self): |
|
1588 self._check("Any MONTH(D) WHERE P is Personne, P creation_date D", |
|
1589 '''SELECT MONTH(_P.cw_creation_date) |
|
1590 FROM cw_Personne AS _P''') |
|
1591 |
|
1592 def test_symmetric(self): |
|
1593 for t in self._parse(SYMMETRIC): |
|
1594 yield t |
|
1595 |
|
1596 def test_basic_parse(self): |
|
1597 for t in self._parse(BASIC):# + BASIC_WITH_LIMIT): |
|
1598 yield t |
|
1599 |
|
1600 def test_advanced_parse(self): |
|
1601 for t in self._parse(ADVANCED):# + ADVANCED_WITH_LIMIT_OR_ORDERBY): |
|
1602 yield t |
|
1603 |
|
1604 def test_limit_offset(self): |
|
1605 WITH_LIMIT = [ |
|
1606 ("Personne P LIMIT 20 OFFSET 10", |
|
1607 '''WITH orderedrows AS ( |
|
1608 SELECT |
|
1609 _L01 |
|
1610 , ROW_NUMBER() OVER (ORDER BY _L01) AS __RowNumber |
|
1611 FROM ( |
|
1612 SELECT _P.cw_eid AS _L01 FROM cw_Personne AS _P |
|
1613 ) AS _SQ1 ) |
|
1614 SELECT |
|
1615 _L01 |
|
1616 FROM orderedrows WHERE |
|
1617 __RowNumber <= 30 AND __RowNumber > 10 |
|
1618 '''), |
|
1619 |
|
1620 ('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"))', |
|
1621 '''WITH orderedrows AS ( |
|
1622 SELECT |
|
1623 _L01, _L02 |
|
1624 , ROW_NUMBER() OVER (ORDER BY _L01 DESC) AS __RowNumber |
|
1625 FROM ( |
|
1626 SELECT COUNT(rel_concerne0.eid_from) AS _L01, _C.cw_nom AS _L02 FROM concerne_relation AS rel_concerne0, cw_Societe AS _C |
|
1627 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 documented_by_relation AS rel_documented_by2, cw_Card AS _N WHERE rel_concerne0.eid_from=rel_documented_by2.eid_from AND rel_documented_by2.eid_to=_N.cw_eid AND _N.cw_title=published))) |
|
1628 GROUP BY _C.cw_nom |
|
1629 ) AS _SQ1 ) |
|
1630 SELECT |
|
1631 _L01, _L02 |
|
1632 FROM orderedrows WHERE |
|
1633 __RowNumber <= 10 |
|
1634 '''), |
|
1635 |
|
1636 ('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;', |
|
1637 '''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 |
|
1638 FROM cw_File AS _X |
|
1639 GROUP BY _X.cw_data_name,_X.cw_data_format) AS T1 |
|
1640 ORDER BY T1.C1,T1.C2 |
|
1641 '''), |
|
1642 |
|
1643 |
|
1644 ('DISTINCT Any X ORDERBY Y WHERE B bookmarked_by X, X login Y', |
|
1645 '''SELECT T1.C0 FROM (SELECT DISTINCT _X.cw_eid AS C0, _X.cw_login AS C1 |
|
1646 FROM bookmarked_by_relation AS rel_bookmarked_by0, cw_CWUser AS _X |
|
1647 WHERE rel_bookmarked_by0.eid_to=_X.cw_eid) AS T1 |
|
1648 ORDER BY T1.C1 |
|
1649 '''), |
|
1650 |
|
1651 ('DISTINCT Any X ORDERBY SN WHERE X in_state S, S name SN', |
|
1652 '''SELECT T1.C0 FROM (SELECT DISTINCT _X.cw_eid AS C0, _S.cw_name AS C1 |
|
1653 FROM cw_Affaire AS _X, cw_State AS _S |
|
1654 WHERE _X.cw_in_state=_S.cw_eid |
|
1655 UNION |
|
1656 SELECT DISTINCT _X.cw_eid AS C0, _S.cw_name AS C1 |
|
1657 FROM cw_CWUser AS _X, cw_State AS _S |
|
1658 WHERE _X.cw_in_state=_S.cw_eid |
|
1659 UNION |
|
1660 SELECT DISTINCT _X.cw_eid AS C0, _S.cw_name AS C1 |
|
1661 FROM cw_Note AS _X, cw_State AS _S |
|
1662 WHERE _X.cw_in_state=_S.cw_eid) AS T1 |
|
1663 ORDER BY T1.C1'''), |
|
1664 |
|
1665 ('Any O,AA,AB,AC ORDERBY AC DESC ' |
|
1666 'WHERE NOT S use_email O, S eid 1, O is EmailAddress, O address AA, O alias AB, O modification_date AC, ' |
|
1667 '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', |
|
1668 ''' |
|
1669 SELECT _O.cw_eid, _O.cw_address, _O.cw_alias, _O.cw_modification_date |
|
1670 FROM cw_EmailAddress AS _O |
|
1671 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)) |
|
1672 ORDER BY 4 DESC'''), |
|
1673 ] |
|
1674 for t in self._parse(WITH_LIMIT):# + ADVANCED_WITH_LIMIT_OR_ORDERBY): |
|
1675 yield t |
1511 |
1676 |
1512 |
1677 |
1513 |
1678 |
1514 class SqliteSQLGeneratorTC(PostgresSQLGeneratorTC): |
1679 class SqliteSQLGeneratorTC(PostgresSQLGeneratorTC): |
1515 backend = 'sqlite' |
1680 backend = 'sqlite' |