Mercurial Mercurial > pub > hg > cubicweb / comparison
summary | shortlog | changelog | graph | tags | bookmarks | branches | files | changeset | file | latest | revisions | annotate | diff | comparison | raw | help
Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
server/test/unittest_rql2sql.py
branchstable
changeset 5582 3e133b29a1a4
parent 5426 0d4853a6e5ee
child 5590 a56eb02f9ce7
child 5793 1faff41593df
equal deleted inserted replaced
5581:0aae5216f99e 5582:3e133b29a1a4 
    13 # FOR A PARTICULAR PURPOSE.  See the GNU Lesser General Public License for more
 
    13 # FOR A PARTICULAR PURPOSE.  See the GNU Lesser General Public License for more
 
    14 # details.
 
    14 # details.
 
    15 #
 
    15 #
 
    16 # You should have received a copy of the GNU Lesser General Public License along
 
    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/>.
 
    17 # with CubicWeb.  If not, see <http://www.gnu.org/licenses/>.
 
    18 """
 
       
    19 
       
    20 """
 
       
    21 
       
    22 """unit tests for module cubicweb.server.sources.rql2sql"""
 
    18 """unit tests for module cubicweb.server.sources.rql2sql"""
 
    23 
    19 
    24 import sys
 
    20 import sys
 
    25 
    21 
    26 from logilab.common.testlib import TestCase, unittest_main, mock_object
 
    22 from logilab.common.testlib import TestCase, unittest_main, mock_object
 
   178 
   174 
   179     ("Any X WHERE X prenom 'lulu',"
 
   175     ("Any X WHERE X prenom 'lulu',"
 
   180      "NOT EXISTS(X owned_by U, U in_group G, G name 'lulufanclub' OR G name 'managers');",
 
   176      "NOT EXISTS(X owned_by U, U in_group G, G name 'lulufanclub' OR G name 'managers');",
 
   181      '''SELECT _X.cw_eid
 
   177      '''SELECT _X.cw_eid
 
   182 FROM cw_Personne AS _X
 
   178 FROM cw_Personne AS _X
 
   183 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)))'''),
 
   179 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))))'''),
 
   184 
   180 
   185 
   181 
   186 
   182 
   187 ]
 
   183 ]
 
   188 
   184 
   274 
   270 
   275     # 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
 
   271     # 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
 
   276     ('Any O WHERE NOT S ecrit_par O, S eid 1, S inline1 P, O inline2 P',
 
   272     ('Any O WHERE NOT S ecrit_par O, S eid 1, S inline1 P, O inline2 P',
 
   277      '''SELECT _O.cw_eid
 
   273      '''SELECT _O.cw_eid
 
   278 FROM cw_Note AS _S, cw_Personne AS _O
 
   274 FROM cw_Note AS _S, cw_Personne AS _O
 
   279 WHERE NOT EXISTS(SELECT 1 WHERE _S.cw_ecrit_par=_O.cw_eid) AND _S.cw_eid=1 AND _O.cw_inline2=_S.cw_inline1'''),
 
   275 WHERE NOT (_S.cw_ecrit_par=_O.cw_eid) AND _S.cw_eid=1 AND _O.cw_inline2=_S.cw_inline1'''),
 
   280 
   276 
   281     ('DISTINCT Any S ORDERBY stockproc(SI) WHERE NOT S ecrit_par O, S para SI',
 
   277     ('DISTINCT Any S ORDERBY stockproc(SI) WHERE NOT S ecrit_par O, S para SI',
 
   282      '''SELECT T1.C0 FROM (SELECT DISTINCT _S.cw_eid AS C0, STOCKPROC(_S.cw_para) AS C1
 
   278      '''SELECT T1.C0 FROM (SELECT DISTINCT _S.cw_eid AS C0, STOCKPROC(_S.cw_para) AS C1
 
   283 FROM cw_Note AS _S
 
   279 FROM cw_Note AS _S
 
   284 WHERE _S.cw_ecrit_par IS NULL
 
   280 WHERE _S.cw_ecrit_par IS NULL
 
   297 
   293 
   298 
   294 
   299     (' Any X,U WHERE C owned_by U, NOT X owned_by U, C eid 1, X eid 2',
 
   295     (' Any X,U WHERE C owned_by U, NOT X owned_by U, C eid 1, X eid 2',
 
   300      '''SELECT 2, rel_owned_by0.eid_to
 
   296      '''SELECT 2, rel_owned_by0.eid_to
 
   301 FROM owned_by_relation AS rel_owned_by0
 
   297 FROM owned_by_relation AS rel_owned_by0
 
   302 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)'''),
 
   298 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))'''),
 
   303 
   299 
   304     ('Any GN WHERE X in_group G, G name GN, (G name "managers" OR EXISTS(X copain T, T login in ("comme", "cochon")))',
 
   300     ('Any GN WHERE X in_group G, G name GN, (G name "managers" OR EXISTS(X copain T, T login in ("comme", "cochon")))',
 
   305      '''SELECT _G.cw_name
 
   301      '''SELECT _G.cw_name
 
   306 FROM cw_CWGroup AS _G, in_group_relation AS rel_in_group0
 
   302 FROM cw_CWGroup AS _G, in_group_relation AS rel_in_group0
 
   307 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))))'''),
 
   303 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))))'''),
 
   351 WHERE _X.cw_login=admin AND _X.cw_eid=_Y.cw_eid'''),
 
   347 WHERE _X.cw_login=admin AND _X.cw_eid=_Y.cw_eid'''),
 
   352 
   348 
   353     ('Any L WHERE X login "admin", NOT X identity Y, Y login L',
 
   349     ('Any L WHERE X login "admin", NOT X identity Y, Y login L',
 
   354      '''SELECT _Y.cw_login
 
   350      '''SELECT _Y.cw_login
 
   355 FROM cw_CWUser AS _X, cw_CWUser AS _Y
 
   351 FROM cw_CWUser AS _X, cw_CWUser AS _Y
 
   356 WHERE _X.cw_login=admin AND NOT _X.cw_eid=_Y.cw_eid'''),
 
   352 WHERE _X.cw_login=admin AND NOT (_X.cw_eid=_Y.cw_eid)'''),
 
   357 
   353 
   358     ('Any L WHERE X login "admin", X identity Y?, Y login L',
 
   354     ('Any L WHERE X login "admin", X identity Y?, Y login L',
 
   359      '''SELECT _Y.cw_login
 
   355      '''SELECT _Y.cw_login
 
   360 FROM cw_CWUser AS _X LEFT OUTER JOIN cw_CWUser AS _Y ON (_X.cw_eid=_Y.cw_eid)
 
   356 FROM cw_CWUser AS _X LEFT OUTER JOIN cw_CWUser AS _Y ON (_X.cw_eid=_Y.cw_eid)
 
   361 WHERE _X.cw_login=admin'''),
 
   357 WHERE _X.cw_login=admin'''),
 
   389 
   385 
   390     # DISTINCT but NEGED exists, can't be invariant
 
   386     # DISTINCT but NEGED exists, can't be invariant
 
   391     ('DISTINCT Any X,Y WHERE X name "CWGroup", Y eid IN(1, 2, 3), NOT EXISTS(X read_permission Y)',
 
   387     ('DISTINCT Any X,Y WHERE X name "CWGroup", Y eid IN(1, 2, 3), NOT EXISTS(X read_permission Y)',
 
   392      '''SELECT DISTINCT _X.cw_eid, _Y.cw_eid
 
   388      '''SELECT DISTINCT _X.cw_eid, _Y.cw_eid
 
   393 FROM cw_CWEType AS _X, cw_CWGroup AS _Y
 
   389 FROM cw_CWEType AS _X, cw_CWGroup AS _Y
 
   394 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)
 
   390 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))
 
   395 UNION
 
   391 UNION
 
   396 SELECT DISTINCT _X.cw_eid, _Y.cw_eid
 
   392 SELECT DISTINCT _X.cw_eid, _Y.cw_eid
 
   397 FROM cw_CWEType AS _X, cw_RQLExpression AS _Y
 
   393 FROM cw_CWEType AS _X, cw_RQLExpression AS _Y
 
   398 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)'''),
 
   394 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))'''),
 
   399 
   395 
   400     # should generate the same query as above
 
   396     # should generate the same query as above
 
   401     ('DISTINCT Any X,Y WHERE X name "CWGroup", Y eid IN(1, 2, 3), NOT X read_permission Y',
 
   397     ('DISTINCT Any X,Y WHERE X name "CWGroup", Y eid IN(1, 2, 3), NOT X read_permission Y',
 
   402      '''SELECT DISTINCT _X.cw_eid, _Y.cw_eid
 
   398      '''SELECT DISTINCT _X.cw_eid, _Y.cw_eid
 
   403 FROM cw_CWEType AS _X, cw_CWGroup AS _Y
 
   399 FROM cw_CWEType AS _X, cw_CWGroup AS _Y
 
   404 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)
 
   400 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))
 
   405 UNION
 
   401 UNION
 
   406 SELECT DISTINCT _X.cw_eid, _Y.cw_eid
 
   402 SELECT DISTINCT _X.cw_eid, _Y.cw_eid
 
   407 FROM cw_CWEType AS _X, cw_RQLExpression AS _Y
 
   403 FROM cw_CWEType AS _X, cw_RQLExpression AS _Y
 
   408 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)'''),
 
   404 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))'''),
 
   409 
   405 
   410     # neged relation, can't be inveriant
 
   406     # neged relation, can't be inveriant
 
   411     ('Any X,Y WHERE X name "CWGroup", Y eid IN(1, 2, 3), NOT X read_permission Y',
 
   407     ('Any X,Y WHERE X name "CWGroup", Y eid IN(1, 2, 3), NOT X read_permission Y',
 
   412      '''SELECT _X.cw_eid, _Y.cw_eid
 
   408      '''SELECT _X.cw_eid, _Y.cw_eid
 
   413 FROM cw_CWEType AS _X, cw_CWGroup AS _Y
 
   409 FROM cw_CWEType AS _X, cw_CWGroup AS _Y
 
   414 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)
 
   410 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))
 
   415 UNION ALL
 
   411 UNION ALL
 
   416 SELECT _X.cw_eid, _Y.cw_eid
 
   412 SELECT _X.cw_eid, _Y.cw_eid
 
   417 FROM cw_CWEType AS _X, cw_RQLExpression AS _Y
 
   413 FROM cw_CWEType AS _X, cw_RQLExpression AS _Y
 
   418 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)'''),
 
   414 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))'''),
 
   419 
   415 
   420     ('Any MAX(X)+MIN(X), N GROUPBY N WHERE X name N, X is IN (Basket, Folder, Tag);',
 
   416     ('Any MAX(X)+MIN(X), N GROUPBY N WHERE X name N, X is IN (Basket, Folder, Tag);',
 
   421      '''SELECT (MAX(T1.C0) + MIN(T1.C0)), T1.C1 FROM (SELECT _X.cw_eid AS C0, _X.cw_name AS C1
 
   417      '''SELECT (MAX(T1.C0) + MIN(T1.C0)), T1.C1 FROM (SELECT _X.cw_eid AS C0, _X.cw_name AS C1
 
   422 FROM cw_Basket AS _X
 
   418 FROM cw_Basket AS _X
 
   423 UNION ALL
 
   419 UNION ALL
 
   550     ('Any O,AA,AB,AC ORDERBY AC DESC '
 
   546     ('Any O,AA,AB,AC ORDERBY AC DESC '
 
   551      'WHERE NOT S use_email O, S eid 1, O is EmailAddress, O address AA, O alias AB, O modification_date AC, '
 
   547      'WHERE NOT S use_email O, S eid 1, O is EmailAddress, O address AA, O alias AB, O modification_date AC, '
 
   552      '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',
 
   548      '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',
 
   553      '''SELECT _O.cw_eid, _O.cw_address, _O.cw_alias, _O.cw_modification_date
 
   549      '''SELECT _O.cw_eid, _O.cw_address, _O.cw_alias, _O.cw_modification_date
 
   554 FROM cw_EmailAddress AS _O
 
   550 FROM cw_EmailAddress AS _O
 
   555 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))
 
   551 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))
 
   556 ORDER BY 4 DESC'''),
 
   552 ORDER BY 4 DESC'''),
 
   557 
   553 
   558 
   554 
   559     ("Any X WHERE X eid 0, X test TRUE",
 
   555     ("Any X WHERE X eid 0, X test TRUE",
 
   560      '''SELECT _X.cw_eid
 
   556      '''SELECT _X.cw_eid
 
   601 
   597 
   602 NEGATIONS = [
 
   598 NEGATIONS = [
 
   603     ("Personne X WHERE NOT X evaluee Y;",
 
   599     ("Personne X WHERE NOT X evaluee Y;",
 
   604      '''SELECT _X.cw_eid
 
   600      '''SELECT _X.cw_eid
 
   605 FROM cw_Personne AS _X
 
   601 FROM cw_Personne AS _X
 
   606 WHERE NOT EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=_X.cw_eid)'''),
 
   602 WHERE NOT (EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=_X.cw_eid))'''),
 
   607 
   603 
   608     ("Note N WHERE NOT X evaluee N, X eid 0",
 
   604     ("Note N WHERE NOT X evaluee N, X eid 0",
 
   609      '''SELECT _N.cw_eid
 
   605      '''SELECT _N.cw_eid
 
   610 FROM cw_Note AS _N
 
   606 FROM cw_Note AS _N
 
   611 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)'''),
 
   607 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))'''),
 
   612 
   608 
   613     ('Any X WHERE NOT X travaille S, X is Personne',
 
   609     ('Any X WHERE NOT X travaille S, X is Personne',
 
   614      '''SELECT _X.cw_eid
 
   610      '''SELECT _X.cw_eid
 
   615 FROM cw_Personne AS _X
 
   611 FROM cw_Personne AS _X
 
   616 WHERE NOT EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_from=_X.cw_eid)'''),
 
   612 WHERE NOT (EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_from=_X.cw_eid))'''),
 
   617 
   613 
   618     ("Personne P where not P datenaiss TODAY",
 
   614     ("Personne P where not P datenaiss TODAY",
 
   619      '''SELECT _P.cw_eid
 
   615      '''SELECT _P.cw_eid
 
   620 FROM cw_Personne AS _P
 
   616 FROM cw_Personne AS _P
 
   621 WHERE NOT (DATE(_P.cw_datenaiss)=CURRENT_DATE)'''),
 
   617 WHERE NOT (DATE(_P.cw_datenaiss)=CURRENT_DATE)'''),
 
   622 
   618 
   623     ("Personne P where NOT P concerne A",
 
   619     ("Personne P where NOT P concerne A",
 
   624      '''SELECT _P.cw_eid
 
   620      '''SELECT _P.cw_eid
 
   625 FROM cw_Personne AS _P
 
   621 FROM cw_Personne AS _P
 
   626 WHERE NOT EXISTS(SELECT 1 FROM concerne_relation AS rel_concerne0 WHERE rel_concerne0.eid_from=_P.cw_eid)'''),
 
   622 WHERE NOT (EXISTS(SELECT 1 FROM concerne_relation AS rel_concerne0 WHERE rel_concerne0.eid_from=_P.cw_eid))'''),
 
   627 
   623 
   628     ("Affaire A where not P concerne A",
 
   624     ("Affaire A where not P concerne A",
 
   629      '''SELECT _A.cw_eid
 
   625      '''SELECT _A.cw_eid
 
   630 FROM cw_Affaire AS _A
 
   626 FROM cw_Affaire AS _A
 
   631 WHERE NOT EXISTS(SELECT 1 FROM concerne_relation AS rel_concerne0 WHERE rel_concerne0.eid_to=_A.cw_eid)'''),
 
   627 WHERE NOT (EXISTS(SELECT 1 FROM concerne_relation AS rel_concerne0 WHERE rel_concerne0.eid_to=_A.cw_eid))'''),
 
   632     ("Personne P where not P concerne A, A sujet ~= 'TEST%'",
 
   628     ("Personne P where not P concerne A, A sujet ~= 'TEST%'",
 
   633      '''SELECT _P.cw_eid
 
   629      '''SELECT _P.cw_eid
 
   634 FROM cw_Affaire AS _A, cw_Personne AS _P
 
   630 FROM cw_Affaire AS _A, cw_Personne AS _P
 
   635 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%'''),
 
   631 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%'''),
 
   636 
   632 
   637     ('Any S WHERE NOT T eid 28258, T tags S',
 
   633     ('Any S WHERE NOT T eid 28258, T tags S',
 
   638      '''SELECT rel_tags0.eid_to
 
   634      '''SELECT rel_tags0.eid_to
 
   639 FROM tags_relation AS rel_tags0
 
   635 FROM tags_relation AS rel_tags0
 
   640 WHERE NOT (rel_tags0.eid_from=28258)'''),
 
   636 WHERE NOT (rel_tags0.eid_from=28258)'''),
 
   658 WHERE rel_created_by0.eid_from=5 AND NOT (rel_created_by0.eid_to=6)'''),
 
   654 WHERE rel_created_by0.eid_from=5 AND NOT (rel_created_by0.eid_to=6)'''),
 
   659 
   655 
   660     ('Note X WHERE NOT Y evaluee X',
 
   656     ('Note X WHERE NOT Y evaluee X',
 
   661      '''SELECT _X.cw_eid
 
   657      '''SELECT _X.cw_eid
 
   662 FROM cw_Note AS _X
 
   658 FROM cw_Note AS _X
 
   663 WHERE NOT EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_to=_X.cw_eid)'''),
 
   659 WHERE NOT (EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_to=_X.cw_eid))'''),
 
   664 
   660 
   665     ('Any Y WHERE NOT Y evaluee X',
 
   661     ('Any Y WHERE NOT Y evaluee X',
 
   666      '''SELECT _Y.cw_eid
 
   662      '''SELECT _Y.cw_eid
 
   667 FROM cw_CWUser AS _Y
 
   663 FROM cw_CWUser AS _Y
 
   668 WHERE NOT EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=_Y.cw_eid)
 
   664 WHERE NOT (EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=_Y.cw_eid))
 
   669 UNION ALL
 
   665 UNION ALL
 
   670 SELECT _Y.cw_eid
 
   666 SELECT _Y.cw_eid
 
   671 FROM cw_Division AS _Y
 
   667 FROM cw_Division AS _Y
 
   672 WHERE NOT EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=_Y.cw_eid)
 
   668 WHERE NOT (EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=_Y.cw_eid))
 
   673 UNION ALL
 
   669 UNION ALL
 
   674 SELECT _Y.cw_eid
 
   670 SELECT _Y.cw_eid
 
   675 FROM cw_Personne AS _Y
 
   671 FROM cw_Personne AS _Y
 
   676 WHERE NOT EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=_Y.cw_eid)
 
   672 WHERE NOT (EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=_Y.cw_eid))
 
   677 UNION ALL
 
   673 UNION ALL
 
   678 SELECT _Y.cw_eid
 
   674 SELECT _Y.cw_eid
 
   679 FROM cw_Societe AS _Y
 
   675 FROM cw_Societe AS _Y
 
   680 WHERE NOT EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=_Y.cw_eid)
 
   676 WHERE NOT (EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=_Y.cw_eid))
 
   681 UNION ALL
 
   677 UNION ALL
 
   682 SELECT _Y.cw_eid
 
   678 SELECT _Y.cw_eid
 
   683 FROM cw_SubDivision AS _Y
 
   679 FROM cw_SubDivision AS _Y
 
   684 WHERE NOT EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=_Y.cw_eid)'''),
 
   680 WHERE NOT (EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=_Y.cw_eid))'''),
 
   685 
   681 
   686     ('Any X WHERE NOT Y evaluee X, Y is CWUser',
 
   682     ('Any X WHERE NOT Y evaluee X, Y is CWUser',
 
   687      '''SELECT _X.cw_eid
 
   683      '''SELECT _X.cw_eid
 
   688 FROM cw_Note AS _X
 
   684 FROM cw_Note AS _X
 
   689 WHERE NOT EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0,cw_CWUser AS _Y WHERE rel_evaluee0.eid_from=_Y.cw_eid AND rel_evaluee0.eid_to=_X.cw_eid)'''),
 
   685 WHERE NOT (EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0, cw_CWUser AS _Y WHERE rel_evaluee0.eid_from=_Y.cw_eid AND rel_evaluee0.eid_to=_X.cw_eid))'''),
 
   690 
   686 
   691     ('Any X,RT WHERE X relation_type RT, NOT X is CWAttribute',
 
   687     ('Any X,RT WHERE X relation_type RT, NOT X is CWAttribute',
 
   692      '''SELECT _X.cw_eid, _X.cw_relation_type
 
   688      '''SELECT _X.cw_eid, _X.cw_relation_type
 
   693 FROM cw_CWRelation AS _X
 
   689 FROM cw_CWRelation AS _X
 
   694 WHERE _X.cw_relation_type IS NOT NULL'''),
 
   690 WHERE _X.cw_relation_type IS NOT NULL'''),
 
   699 WHERE _P.cw_for_user IS NULL'''),
 
   695 WHERE _P.cw_for_user IS NULL'''),
 
   700 
   696 
   701     ('Any S WHERE NOT X in_state S, X is IN(Affaire, CWUser)',
 
   697     ('Any S WHERE NOT X in_state S, X is IN(Affaire, CWUser)',
 
   702      '''SELECT _S.cw_eid
 
   698      '''SELECT _S.cw_eid
 
   703 FROM cw_State AS _S
 
   699 FROM cw_State AS _S
 
   704 WHERE NOT EXISTS(SELECT 1 FROM cw_Affaire AS _X WHERE _X.cw_in_state=_S.cw_eid)
 
   700 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))'''),
 
   705 INTERSECT
 
       
   706 SELECT _S.cw_eid
 
       
   707 FROM cw_State AS _S
 
       
   708 WHERE NOT EXISTS(SELECT 1 FROM cw_CWUser AS _X WHERE _X.cw_in_state=_S.cw_eid)'''),
 
       
   709 
   701 
   710     ('Any S WHERE NOT(X in_state S, S name "somename"), X is CWUser',
 
   702     ('Any S WHERE NOT(X in_state S, S name "somename"), X is CWUser',
 
   711      '''SELECT _S.cw_eid
 
   703      '''SELECT _S.cw_eid
 
   712 FROM cw_State AS _S
 
   704 FROM cw_State AS _S
 
   713 WHERE NOT EXISTS(SELECT 1 FROM cw_CWUser AS _X WHERE _X.cw_in_state=_S.cw_eid AND _S.cw_name=somename)'''),
 
   705 WHERE NOT (EXISTS(SELECT 1 FROM cw_CWUser AS _X WHERE _X.cw_in_state=_S.cw_eid AND _S.cw_name=somename))'''),
 
   714    
   706 
   715 # XXXFIXME fail
 
   707 # XXXFIXME fail
 
   716 #         ('Any X,RT WHERE X relation_type RT?, NOT X is CWAttribute',
 
   708 #         ('Any X,RT WHERE X relation_type RT?, NOT X is CWAttribute',
 
   717 #      '''SELECT _X.cw_eid, _X.cw_relation_type
 
   709 #      '''SELECT _X.cw_eid, _X.cw_relation_type
 
   718 # FROM cw_CWRelation AS _X'''),
 
   710 # FROM cw_CWRelation AS _X'''),
 
   719 ]
 
   711 ]
 
   842 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)'''),
 
   834 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)'''),
 
   843 
   835 
   844     ('Any O,AD  WHERE NOT S inline1 O, S eid 123, O todo_by AD?',
 
   836     ('Any O,AD  WHERE NOT S inline1 O, S eid 123, O todo_by AD?',
 
   845      '''SELECT _O.cw_eid, rel_todo_by0.eid_to
 
   837      '''SELECT _O.cw_eid, rel_todo_by0.eid_to
 
   846 FROM cw_Affaire AS _O LEFT OUTER JOIN todo_by_relation AS rel_todo_by0 ON (rel_todo_by0.eid_from=_O.cw_eid), cw_Note AS _S
 
   838 FROM cw_Affaire AS _O LEFT OUTER JOIN todo_by_relation AS rel_todo_by0 ON (rel_todo_by0.eid_from=_O.cw_eid), cw_Note AS _S
 
   847 WHERE NOT EXISTS(SELECT 1 WHERE _S.cw_inline1=_O.cw_eid) AND _S.cw_eid=123''')
 
   839 WHERE NOT (_S.cw_inline1=_O.cw_eid) AND _S.cw_eid=123''')
 
   848     ]
 
   840     ]
 
   849 
   841 
   850 VIRTUAL_VARS = [
 
   842 VIRTUAL_VARS = [
 
   851 
   843 
   852     ('Any X WHERE X is CWUser, X creation_date > D1, Y creation_date D1, Y login "SWEB09"',
 
   844     ('Any X WHERE X is CWUser, X creation_date > D1, Y creation_date D1, Y login "SWEB09"',
 
   917     ("Any COUNT(P) WHERE P is Personne",
 
   909     ("Any COUNT(P) WHERE P is Personne",
 
   918      '''SELECT COUNT(_P.cw_eid)
 
   910      '''SELECT COUNT(_P.cw_eid)
 
   919 FROM cw_Personne AS _P'''),
 
   911 FROM cw_Personne AS _P'''),
 
   920     ]
 
   912     ]
 
   921 
   913 
   922 SYMETRIC = [
 
   914 SYMMETRIC = [
 
   923     ('Any P WHERE X eid 0, X connait P',
 
   915     ('Any P WHERE X eid 0, X connait P',
 
   924      '''SELECT DISTINCT _P.cw_eid
 
   916      '''SELECT DISTINCT _P.cw_eid
 
   925 FROM connait_relation AS rel_connait0, cw_Personne AS _P
 
   917 FROM connait_relation AS rel_connait0, cw_Personne AS _P
 
   926 WHERE (rel_connait0.eid_from=0 AND rel_connait0.eid_to=_P.cw_eid OR rel_connait0.eid_to=0 AND rel_connait0.eid_from=_P.cw_eid)'''
 
   918 WHERE (rel_connait0.eid_from=0 AND rel_connait0.eid_to=_P.cw_eid OR rel_connait0.eid_to=0 AND rel_connait0.eid_from=_P.cw_eid)'''
 
   927      ),
 
   919      ),
 
   939      ),
 
   931      ),
 
   940 
   932 
   941     ('Any P WHERE X eid 0, NOT X connait P',
 
   933     ('Any P WHERE X eid 0, NOT X connait P',
 
   942      '''SELECT _P.cw_eid
 
   934      '''SELECT _P.cw_eid
 
   943 FROM cw_Personne AS _P
 
   935 FROM cw_Personne AS _P
 
   944 WHERE NOT EXISTS(SELECT 1 FROM connait_relation AS rel_connait0 WHERE (rel_connait0.eid_from=0 AND rel_connait0.eid_to=_P.cw_eid OR rel_connait0.eid_to=0 AND rel_connait0.eid_from=_P.cw_eid))'''),
 
   936 WHERE NOT (EXISTS(SELECT 1 FROM connait_relation AS rel_connait0 WHERE (rel_connait0.eid_from=0 AND rel_connait0.eid_to=_P.cw_eid OR rel_connait0.eid_to=0 AND rel_connait0.eid_from=_P.cw_eid)))'''),
 
   945 
   937 
   946     ('Any P WHERE NOT X connait P',
 
   938     ('Any P WHERE NOT X connait P',
 
   947     '''SELECT _P.cw_eid
 
   939     '''SELECT _P.cw_eid
 
   948 FROM cw_Personne AS _P
 
   940 FROM cw_Personne AS _P
 
   949 WHERE NOT EXISTS(SELECT 1 FROM connait_relation AS rel_connait0 WHERE (rel_connait0.eid_to=_P.cw_eid OR rel_connait0.eid_from=_P.cw_eid))'''),
 
   941 WHERE NOT (EXISTS(SELECT 1 FROM connait_relation AS rel_connait0 WHERE (rel_connait0.eid_to=_P.cw_eid OR rel_connait0.eid_from=_P.cw_eid)))'''),
 
   950 
   942 
   951     ('Any X WHERE NOT X connait P',
 
   943     ('Any X WHERE NOT X connait P',
 
   952     '''SELECT _X.cw_eid
 
   944     '''SELECT _X.cw_eid
 
   953 FROM cw_Personne AS _X
 
   945 FROM cw_Personne AS _X
 
   954 WHERE NOT EXISTS(SELECT 1 FROM connait_relation AS rel_connait0 WHERE (rel_connait0.eid_from=_X.cw_eid OR rel_connait0.eid_to=_X.cw_eid))'''),
 
   946 WHERE NOT (EXISTS(SELECT 1 FROM connait_relation AS rel_connait0 WHERE (rel_connait0.eid_from=_X.cw_eid OR rel_connait0.eid_to=_X.cw_eid)))'''),
 
   955 
   947 
   956     ('Any P WHERE X connait P, P nom "nom"',
 
   948     ('Any P WHERE X connait P, P nom "nom"',
 
   957      '''SELECT DISTINCT _P.cw_eid
 
   949      '''SELECT DISTINCT _P.cw_eid
 
   958 FROM connait_relation AS rel_connait0, cw_Personne AS _P
 
   950 FROM connait_relation AS rel_connait0, cw_Personne AS _P
 
   959 WHERE (rel_connait0.eid_to=_P.cw_eid OR rel_connait0.eid_from=_P.cw_eid) AND _P.cw_nom=nom'''),
 
   951 WHERE (rel_connait0.eid_to=_P.cw_eid OR rel_connait0.eid_from=_P.cw_eid) AND _P.cw_nom=nom'''),
 
   986 WHERE _N.cw_ecrit_par=_P.cw_eid AND _N.cw_eid=0'''),
 
   978 WHERE _N.cw_ecrit_par=_P.cw_eid AND _N.cw_eid=0'''),
 
   987 
   979 
   988     ('Any N WHERE NOT N ecrit_par P, P nom "toto"',
 
   980     ('Any N WHERE NOT N ecrit_par P, P nom "toto"',
 
   989      '''SELECT _N.cw_eid
 
   981      '''SELECT _N.cw_eid
 
   990 FROM cw_Note AS _N, cw_Personne AS _P
 
   982 FROM cw_Note AS _N, cw_Personne AS _P
 
   991 WHERE NOT EXISTS(SELECT 1 WHERE _N.cw_ecrit_par=_P.cw_eid) AND _P.cw_nom=toto'''),
 
   983 WHERE NOT (_N.cw_ecrit_par=_P.cw_eid) AND _P.cw_nom=toto'''),
 
       
   984 
       
   985     ('Any P WHERE NOT N ecrit_par P, P nom "toto"',
 
       
   986      '''SELECT _P.cw_eid
 
       
   987 FROM cw_Personne AS _P
 
       
   988 WHERE NOT (EXISTS(SELECT 1 FROM cw_Note AS _N WHERE _N.cw_ecrit_par=_P.cw_eid)) AND _P.cw_nom=toto'''),
 
   992 
   989 
   993     ('Any P WHERE N ecrit_par P, N eid 0',
 
   990     ('Any P WHERE N ecrit_par P, N eid 0',
 
   994     '''SELECT _N.cw_ecrit_par
 
   991     '''SELECT _N.cw_ecrit_par
 
   995 FROM cw_Note AS _N
 
   992 FROM cw_Note AS _N
 
   996 WHERE _N.cw_ecrit_par IS NOT NULL AND _N.cw_eid=0'''),
 
   993 WHERE _N.cw_ecrit_par IS NOT NULL AND _N.cw_eid=0'''),
 
  1001 WHERE _N.cw_ecrit_par=_P.cw_eid AND _N.cw_eid=0'''),
 
   998 WHERE _N.cw_ecrit_par=_P.cw_eid AND _N.cw_eid=0'''),
 
  1002 
   999 
  1003     ('Any P WHERE NOT N ecrit_par P, P is Personne, N eid 512',
 
  1000     ('Any P WHERE NOT N ecrit_par P, P is Personne, N eid 512',
 
  1004      '''SELECT _P.cw_eid
 
  1001      '''SELECT _P.cw_eid
 
  1005 FROM cw_Note AS _N, cw_Personne AS _P
 
  1002 FROM cw_Note AS _N, cw_Personne AS _P
 
  1006 WHERE NOT EXISTS(SELECT 1 WHERE _N.cw_ecrit_par=_P.cw_eid) AND _N.cw_eid=512'''),
 
  1003 WHERE NOT (_N.cw_ecrit_par=_P.cw_eid) AND _N.cw_eid=512'''),
 
  1007 
  1004 
  1008     ('Any S,ES,T WHERE S state_of ET, ET name "CWUser", ES allowed_transition T, T destination_state S',
 
  1005     ('Any S,ES,T WHERE S state_of ET, ET name "CWUser", ES allowed_transition T, T destination_state S',
 
  1009      '''SELECT _T.cw_destination_state, rel_allowed_transition1.eid_from, _T.cw_eid
 
  1006      '''SELECT _T.cw_destination_state, rel_allowed_transition1.eid_from, _T.cw_eid
 
  1010 FROM allowed_transition_relation AS rel_allowed_transition1, cw_Transition AS _T, cw_Workflow AS _ET, state_of_relation AS rel_state_of0
 
  1007 FROM allowed_transition_relation AS rel_allowed_transition1, cw_Transition AS _T, cw_Workflow AS _ET, state_of_relation AS rel_state_of0
 
  1011 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'''),
 
  1008 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'''),
 
  1023 FROM cw_Note AS _S
 
  1020 FROM cw_Note AS _S
 
  1024 WHERE _S.cw_eid=0 AND _S.cw_in_state IS NOT NULL'''),
 
  1021 WHERE _S.cw_eid=0 AND _S.cw_in_state IS NOT NULL'''),
 
  1025 
  1022 
  1026     ('Any X WHERE NOT Y for_user X, X eid 123',
 
  1023     ('Any X WHERE NOT Y for_user X, X eid 123',
 
  1027      '''SELECT 123
 
  1024      '''SELECT 123
 
  1028 WHERE NOT EXISTS(SELECT 1 FROM cw_CWProperty AS _Y WHERE _Y.cw_for_user=123)
 
  1025 WHERE NOT (EXISTS(SELECT 1 FROM cw_CWProperty AS _Y WHERE _Y.cw_for_user=123))
 
  1029 '''),
 
  1026 '''),
 
  1030 
  1027 
  1031     ]
 
  1028     ]
 
  1032 
  1029 
  1033 INTERSECT = [
 
  1030 INTERSECT = [
 
  1034     ('Any SN WHERE NOT X in_state S, S name SN',
 
  1031     ('Any SN WHERE NOT X in_state S, S name SN',
 
  1035      '''SELECT _S.cw_name
 
  1032      '''SELECT _S.cw_name
 
  1036 FROM cw_State AS _S
 
  1033 FROM cw_State AS _S
 
  1037 WHERE NOT EXISTS(SELECT 1 FROM cw_Affaire AS _X WHERE _X.cw_in_state=_S.cw_eid)
 
  1034 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))'''),
 
  1038 INTERSECT
 
       
  1039 SELECT _S.cw_name
 
       
  1040 FROM cw_State AS _S
 
       
  1041 WHERE NOT EXISTS(SELECT 1 FROM cw_CWUser AS _X WHERE _X.cw_in_state=_S.cw_eid)
 
       
  1042 INTERSECT
 
       
  1043 SELECT _S.cw_name
 
       
  1044 FROM cw_State AS _S
 
       
  1045 WHERE NOT EXISTS(SELECT 1 FROM cw_Note AS _X WHERE _X.cw_in_state=_S.cw_eid)'''),
 
       
  1046 
  1035 
  1047     ('Any PN WHERE NOT X travaille S, X nom PN, S is IN(Division, Societe)',
 
  1036     ('Any PN WHERE NOT X travaille S, X nom PN, S is IN(Division, Societe)',
 
  1048      '''SELECT _X.cw_nom
 
  1037      '''SELECT _X.cw_nom
 
  1049 FROM cw_Personne AS _X
 
  1038 FROM cw_Personne AS _X
 
  1050 WHERE NOT EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0,cw_Division AS _S WHERE rel_travaille0.eid_from=_X.cw_eid AND rel_travaille0.eid_to=_S.cw_eid)
 
  1039 WHERE NOT (EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0, cw_Division AS _S WHERE rel_travaille0.eid_from=_X.cw_eid AND rel_travaille0.eid_to=_S.cw_eid UNION SELECT 1 FROM travaille_relation AS rel_travaille1, cw_Societe AS _S WHERE rel_travaille1.eid_from=_X.cw_eid AND rel_travaille1.eid_to=_S.cw_eid))'''),
 
  1051 INTERSECT
 
       
  1052 SELECT _X.cw_nom
 
       
  1053 FROM cw_Personne AS _X
 
       
  1054 WHERE NOT EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0,cw_Societe AS _S WHERE rel_travaille0.eid_from=_X.cw_eid AND rel_travaille0.eid_to=_S.cw_eid)'''),
 
       
  1055 
  1040 
  1056     ('Any PN WHERE NOT X travaille S, S nom PN, S is IN(Division, Societe)',
 
  1041     ('Any PN WHERE NOT X travaille S, S nom PN, S is IN(Division, Societe)',
 
  1057      '''SELECT _S.cw_nom
 
  1042      '''SELECT _S.cw_nom
 
  1058 FROM cw_Division AS _S
 
  1043 FROM cw_Division AS _S
 
  1059 WHERE NOT EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_to=_S.cw_eid)
 
  1044 WHERE NOT (EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_to=_S.cw_eid))
 
  1060 UNION ALL
 
  1045 UNION ALL
 
  1061 SELECT _S.cw_nom
 
  1046 SELECT _S.cw_nom
 
  1062 FROM cw_Societe AS _S
 
  1047 FROM cw_Societe AS _S
 
  1063 WHERE NOT EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_to=_S.cw_eid)'''),
 
  1048 WHERE NOT (EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_to=_S.cw_eid))'''),
 
  1064 
  1049 
  1065     ('Personne X WHERE NOT X travaille S, S nom "chouette"',
 
  1050     ('Personne X WHERE NOT X travaille S, S nom "chouette"',
 
  1066      '''SELECT _X.cw_eid
 
  1051      '''SELECT _X.cw_eid
 
  1067 FROM cw_Division AS _S, cw_Personne AS _X
 
  1052 FROM cw_Division AS _S, cw_Personne AS _X
 
  1068 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
 
  1053 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
 
  1069 UNION ALL
 
  1054 UNION ALL
 
  1070 SELECT _X.cw_eid
 
  1055 SELECT _X.cw_eid
 
  1071 FROM cw_Personne AS _X, cw_Societe AS _S
 
  1056 FROM cw_Personne AS _X, cw_Societe AS _S
 
  1072 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
 
  1057 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
 
  1073 UNION ALL
 
  1058 UNION ALL
 
  1074 SELECT _X.cw_eid
 
  1059 SELECT _X.cw_eid
 
  1075 FROM cw_Personne AS _X, cw_SubDivision AS _S
 
  1060 FROM cw_Personne AS _X, cw_SubDivision AS _S
 
  1076 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'''),
 
  1061 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'''),
 
  1077 
  1062 
  1078     ('Any X WHERE X is ET, ET eid 2',
 
  1063     ('Any X WHERE X is ET, ET eid 2',
 
  1079      '''SELECT rel_is0.eid_from
 
  1064      '''SELECT rel_is0.eid_from
 
  1080 FROM is_relation AS rel_is0
 
  1065 FROM is_relation AS rel_is0
 
  1081 WHERE rel_is0.eid_to=2'''),
 
  1066 WHERE rel_is0.eid_to=2'''),
 
  1343                ' (Any X WHERE X is Transition))')
 
  1328                ' (Any X WHERE X is Transition))')
 
  1344         rqlst = self._prepare(rql)
 
  1329         rqlst = self._prepare(rql)
 
  1345         self.assertRaises(BadRQLQuery, self.o.generate, rqlst)
 
  1330         self.assertRaises(BadRQLQuery, self.o.generate, rqlst)
 
  1346 
  1331 
  1347     def test_symmetric(self):
 
  1332     def test_symmetric(self):
 
  1348         for t in self._parse(SYMETRIC):
 
  1333         for t in self._parse(SYMMETRIC):
 
  1349             yield t
 
  1334             yield t
 
  1350 
  1335 
  1351     def test_inline(self):
 
  1336     def test_inline(self):
 
  1352         for t in self._parse(INLINE):
 
  1337         for t in self._parse(INLINE):
 
  1353             yield t
 
  1338             yield t
 
  1391         queries = [("Any 1 WHERE EXISTS(T is CWGroup, T name 'managers')",
 
  1376         queries = [("Any 1 WHERE EXISTS(T is CWGroup, T name 'managers')",
 
  1392                     '''SELECT 1
 
  1377                     '''SELECT 1
 
  1393 WHERE EXISTS(SELECT 1 FROM cw_CWGroup AS _T WHERE _T.cw_name=managers)'''),
 
  1378 WHERE EXISTS(SELECT 1 FROM cw_CWGroup AS _T WHERE _T.cw_name=managers)'''),
 
  1394                    ('Any X,Y WHERE NOT X created_by Y, X eid 5, Y eid 6',
 
  1379                    ('Any X,Y WHERE NOT X created_by Y, X eid 5, Y eid 6',
 
  1395                     '''SELECT 5, 6
 
  1380                     '''SELECT 5, 6
 
  1396 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)'''),
 
  1381 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))'''),
 
  1397                    ]
 
  1382                    ]
 
  1398         for t in self._parse(queries):
 
  1383         for t in self._parse(queries):
 
  1399             yield t
 
  1384             yield t
 
  1400 
  1385 
  1401     def test_ambigous_exists_no_from_clause(self):
 
  1386     def test_ambigous_exists_no_from_clause(self):
 
  1437         RQLGeneratorTC.setUp(self)
 
  1422         RQLGeneratorTC.setUp(self)
 
  1438         dbhelper = get_db_helper('sqlite')
 
  1423         dbhelper = get_db_helper('sqlite')
 
  1439         self.o = SQLGenerator(schema, dbhelper)
 
  1424         self.o = SQLGenerator(schema, dbhelper)
 
  1440 
  1425 
  1441     def _norm_sql(self, sql):
 
  1426     def _norm_sql(self, sql):
 
  1442         return sql.strip().replace(' ILIKE ', ' LIKE ').replace('\nINTERSECT ALL\n', '\nINTERSECT\n')
 
  1427         return sql.strip().replace(' ILIKE ', ' LIKE ')
 
  1443 
  1428 
  1444     def test_date_extraction(self):
 
  1429     def test_date_extraction(self):
 
  1445         self._check("Any MONTH(D) WHERE P is Personne, P creation_date D",
 
  1430         self._check("Any MONTH(D) WHERE P is Personne, P creation_date D",
 
  1446                     '''SELECT MONTH(_P.cw_creation_date)
 
  1431                     '''SELECT MONTH(_P.cw_creation_date)
 
  1447 FROM cw_Personne AS _P''')
 
  1432 FROM cw_Personne AS _P''')
 
  1569 FROM (SELECT 1) AS _T
 
  1554 FROM (SELECT 1) AS _T
 
  1570 WHERE EXISTS(SELECT 1 FROM cw_CWGroup AS _T WHERE _T.cw_name=managers)'''),
 
  1555 WHERE EXISTS(SELECT 1 FROM cw_CWGroup AS _T WHERE _T.cw_name=managers)'''),
 
  1571                    ('Any X,Y WHERE NOT X created_by Y, X eid 5, Y eid 6',
 
  1556                    ('Any X,Y WHERE NOT X created_by Y, X eid 5, Y eid 6',
 
  1572                     '''SELECT 5, 6
 
  1557                     '''SELECT 5, 6
 
  1573 FROM (SELECT 1) AS _T
 
  1558 FROM (SELECT 1) AS _T
 
  1574 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)'''),
 
  1559 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))'''),
 
  1575                    ]
 
  1560                    ]
 
  1576         for t in self._parse(queries):
 
  1561         for t in self._parse(queries):
 
  1577             yield t
 
  1562             yield t
 
  1578 
  1563 
  1579 
  1564
cubicweb