"""unit tests for module cubicweb.server.sources.rql2sql"""importsysfrommx.DateTimeimporttodayfromlogilab.common.testlibimportTestCase,unittest_mainfromrqlimportBadRQLQueryfromindexerimportget_indexer#from cubicweb.server.sources.native import remove_unused_solutionsfromcubicweb.server.sources.rql2sqlimportSQLGeneratorfromrql.utilsimportregister_function,FunctionDescr# add a dumb registered procedureclassstockproc(FunctionDescr):supported_backends=('postgres','sqlite','mysql')try:register_function(stockproc)exceptAssertionError,ex:pass# already registeredfromcubicweb.devtoolsimportTestServerConfigurationfromcubicweb.devtools.repotestimportRQLGeneratorTCconfig=TestServerConfiguration('data')config.bootstrap_cubes()schema=config.load_schema()schema['in_state'].inlined=Trueschema['comments'].inlined=FalsePARSER=[(r"Personne P WHERE P nom 'Zig\'oto';",'''SELECT P.eidFROM Personne AS PWHERE P.nom=Zig\'oto'''),(r'Personne P WHERE P nom ~= "Zig\"oto%";','''SELECT P.eidFROM Personne AS PWHERE P.nom ILIKE Zig"oto%'''),]BASIC=[("Any X WHERE X is Affaire",'''SELECT X.eidFROM Affaire AS X'''),("Any X WHERE X eid 0",'''SELECT 0'''),("Personne P",'''SELECT P.eidFROM Personne AS P'''),("Personne P WHERE P test TRUE",'''SELECT P.eidFROM Personne AS PWHERE P.test=True'''),("Personne P WHERE P test false",'''SELECT P.eidFROM Personne AS PWHERE P.test=False'''),("Personne P WHERE P eid -1",'''SELECT -1'''),("Personne P LIMIT 20 OFFSET 10",'''SELECT P.eidFROM Personne AS PLIMIT 20OFFSET 10'''),("Personne P WHERE S is Societe, P travaille S, S nom 'Logilab';",'''SELECT rel_travaille0.eid_fromFROM Societe AS S, travaille_relation AS rel_travaille0WHERE rel_travaille0.eid_to=S.eid AND S.nom=Logilab'''),("Personne P WHERE P concerne A, A concerne S, S nom 'Logilab', S is Societe;",'''SELECT rel_concerne0.eid_fromFROM Societe AS S, concerne_relation AS rel_concerne0, concerne_relation AS rel_concerne1WHERE rel_concerne0.eid_to=rel_concerne1.eid_from AND rel_concerne1.eid_to=S.eid AND S.nom=Logilab'''),("Note N WHERE X evaluee N, X nom 'Logilab';",'''SELECT rel_evaluee0.eid_toFROM Division AS X, evaluee_relation AS rel_evaluee0WHERE rel_evaluee0.eid_from=X.eid AND X.nom=LogilabUNION ALLSELECT rel_evaluee0.eid_toFROM Personne AS X, evaluee_relation AS rel_evaluee0WHERE rel_evaluee0.eid_from=X.eid AND X.nom=LogilabUNION ALLSELECT rel_evaluee0.eid_toFROM Societe AS X, evaluee_relation AS rel_evaluee0WHERE rel_evaluee0.eid_from=X.eid AND X.nom=LogilabUNION ALLSELECT rel_evaluee0.eid_toFROM SubDivision AS X, evaluee_relation AS rel_evaluee0WHERE rel_evaluee0.eid_from=X.eid AND X.nom=Logilab'''),("Note N WHERE X evaluee N, X nom in ('Logilab', 'Caesium');",'''SELECT rel_evaluee0.eid_toFROM Division AS X, evaluee_relation AS rel_evaluee0WHERE rel_evaluee0.eid_from=X.eid AND X.nom IN(Logilab, Caesium)UNION ALLSELECT rel_evaluee0.eid_toFROM Personne AS X, evaluee_relation AS rel_evaluee0WHERE rel_evaluee0.eid_from=X.eid AND X.nom IN(Logilab, Caesium)UNION ALLSELECT rel_evaluee0.eid_toFROM Societe AS X, evaluee_relation AS rel_evaluee0WHERE rel_evaluee0.eid_from=X.eid AND X.nom IN(Logilab, Caesium)UNION ALLSELECT rel_evaluee0.eid_toFROM SubDivision AS X, evaluee_relation AS rel_evaluee0WHERE rel_evaluee0.eid_from=X.eid AND X.nom IN(Logilab, Caesium)'''),("Any X WHERE X creation_date TODAY, X is Affaire",'''SELECT X.eidFROM Affaire AS XWHERE DATE(X.creation_date)=CURRENT_DATE'''),("Any N WHERE G is EGroup, G name N, E eid 12, E read_permission G",'''SELECT G.nameFROM EGroup AS G, read_permission_relation AS rel_read_permission0WHERE rel_read_permission0.eid_from=12 AND rel_read_permission0.eid_to=G.eid'''),('Any Y WHERE U login "admin", U login Y',# stupid but valid..."""SELECT U.loginFROM EUser AS UWHERE U.login=admin"""),('Any T WHERE T tags X, X is State','''SELECT rel_tags0.eid_fromFROM State AS X, tags_relation AS rel_tags0WHERE rel_tags0.eid_to=X.eid'''),('Any X,Y WHERE X eid 0, Y eid 1, X concerne Y','''SELECT 0, 1FROM concerne_relation AS rel_concerne0WHERE rel_concerne0.eid_from=0 AND rel_concerne0.eid_to=1'''),("Any X WHERE X prenom 'lulu',""EXISTS(X owned_by U, U in_group G, G name 'lulufanclub' OR G name 'managers');",'''SELECT X.eidFROM Personne AS XWHERE X.prenom=lulu AND EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0, in_group_relation AS rel_in_group1, EGroup AS G WHERE rel_owned_by0.eid_from=X.eid AND rel_in_group1.eid_from=rel_owned_by0.eid_to AND rel_in_group1.eid_to=G.eid AND ((G.name=lulufanclub) OR (G.name=managers)))'''),("Any X WHERE X prenom 'lulu',""NOT EXISTS(X owned_by U, U in_group G, G name 'lulufanclub' OR G name 'managers');",'''SELECT X.eidFROM Personne AS XWHERE X.prenom=lulu AND NOT EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0, in_group_relation AS rel_in_group1, EGroup AS G WHERE rel_owned_by0.eid_from=X.eid AND rel_in_group1.eid_from=rel_owned_by0.eid_to AND rel_in_group1.eid_to=G.eid AND ((G.name=lulufanclub) OR (G.name=managers)))'''),]ADVANCED=[('Any X WHERE X is ET, ET eid 2','''SELECT rel_is0.eid_fromFROM is_relation AS rel_is0WHERE rel_is0.eid_to=2'''),("Societe S WHERE S nom 'Logilab' OR S nom 'Caesium'",'''SELECT S.eidFROM Societe AS SWHERE ((S.nom=Logilab) OR (S.nom=Caesium))'''),('Any X WHERE X nom "toto", X eid IN (9700, 9710, 1045, 674)','''SELECT X.eidFROM Division AS XWHERE X.nom=toto AND X.eid IN(9700, 9710, 1045, 674)UNION ALLSELECT X.eidFROM Personne AS XWHERE X.nom=toto AND X.eid IN(9700, 9710, 1045, 674)UNION ALLSELECT X.eidFROM Societe AS XWHERE X.nom=toto AND X.eid IN(9700, 9710, 1045, 674)UNION ALLSELECT X.eidFROM SubDivision AS XWHERE X.nom=toto AND X.eid IN(9700, 9710, 1045, 674)'''),('Any Y, COUNT(N) GROUPBY Y WHERE Y evaluee N;','''SELECT rel_evaluee0.eid_from, COUNT(rel_evaluee0.eid_to)FROM evaluee_relation AS rel_evaluee0GROUP BY rel_evaluee0.eid_from'''),("Any X WHERE X concerne B or C concerne X",'''SELECT X.eidFROM Affaire AS X, concerne_relation AS rel_concerne0, concerne_relation AS rel_concerne1WHERE ((rel_concerne0.eid_from=X.eid) OR (rel_concerne1.eid_to=X.eid))'''),("Any X WHERE X travaille S or X concerne A",'''SELECT X.eidFROM Personne AS X, concerne_relation AS rel_concerne1, travaille_relation AS rel_travaille0WHERE ((rel_travaille0.eid_from=X.eid) OR (rel_concerne1.eid_from=X.eid))'''),("Any N WHERE A evaluee N or N ecrit_par P",'''SELECT N.eidFROM Note AS N, evaluee_relation AS rel_evaluee0WHERE ((rel_evaluee0.eid_to=N.eid) OR (N.ecrit_par IS NOT NULL))'''),("Any N WHERE A evaluee N or EXISTS(N todo_by U)",'''SELECT N.eidFROM Note AS N, evaluee_relation AS rel_evaluee0WHERE ((rel_evaluee0.eid_to=N.eid) OR (EXISTS(SELECT 1 FROM todo_by_relation AS rel_todo_by1 WHERE rel_todo_by1.eid_from=N.eid)))'''),("Any N WHERE A evaluee N or N todo_by U",'''SELECT N.eidFROM Note AS N, evaluee_relation AS rel_evaluee0, todo_by_relation AS rel_todo_by1WHERE ((rel_evaluee0.eid_to=N.eid) OR (rel_todo_by1.eid_from=N.eid))'''),("Any X WHERE X concerne B or C concerne X, B eid 12, C eid 13",'''SELECT X.eidFROM Affaire AS X, concerne_relation AS rel_concerne0, concerne_relation AS rel_concerne1WHERE ((rel_concerne0.eid_from=X.eid AND rel_concerne0.eid_to=12) OR (rel_concerne1.eid_from=13 AND rel_concerne1.eid_to=X.eid))'''),('Any X WHERE X created_by U, X concerne B OR C concerne X, B eid 12, C eid 13','''SELECT rel_created_by0.eid_fromFROM concerne_relation AS rel_concerne1, concerne_relation AS rel_concerne2, created_by_relation AS rel_created_by0WHERE ((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))'''),('Any P WHERE P travaille_subdivision S1 OR P travaille_subdivision S2, S1 nom "logilab", S2 nom "caesium"','''SELECT P.eidFROM Personne AS P, SubDivision AS S1, SubDivision AS S2, travaille_subdivision_relation AS rel_travaille_subdivision0, travaille_subdivision_relation AS rel_travaille_subdivision1WHERE ((rel_travaille_subdivision0.eid_from=P.eid AND rel_travaille_subdivision0.eid_to=S1.eid) OR (rel_travaille_subdivision1.eid_from=P.eid AND rel_travaille_subdivision1.eid_to=S2.eid)) AND S1.nom=logilab AND S2.nom=caesium'''),('Any X WHERE T tags X','''SELECT rel_tags0.eid_toFROM tags_relation AS rel_tags0'''),('Any X WHERE X in_basket B, B eid 12','''SELECT rel_in_basket0.eid_fromFROM in_basket_relation AS rel_in_basket0WHERE rel_in_basket0.eid_to=12'''),('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','''SELECT SE.name, R.name, OE.nameFROM EEType AS OE, EEType AS SE, EFRDef AS X, ERType AS RWHERE X.from_entity=44 AND SE.eid=44 AND X.relation_type=139 AND R.eid=139 AND X.to_entity=42 AND OE.eid=42UNION ALLSELECT SE.name, R.name, OE.nameFROM EEType AS OE, EEType AS SE, ENFRDef AS X, ERType AS RWHERE X.from_entity=44 AND SE.eid=44 AND X.relation_type=139 AND R.eid=139 AND X.to_entity=42 AND OE.eid=42'''),# 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('Any O WHERE NOT S ecrit_par O, S eid 1, S inline1 P, O inline2 P','''SELECT DISTINCT O.eidFROM Note AS S, Personne AS OWHERE (S.ecrit_par IS NULL OR S.ecrit_par!=O.eid) AND S.eid=1 AND O.inline2=S.inline1'''),('DISTINCT Any S ORDERBY stockproc(SI) WHERE NOT S ecrit_par O, S para SI','''SELECT T1.C0 FROM (SELECT DISTINCT S.eid AS C0, STOCKPROC(S.para) AS C1FROM Note AS SWHERE S.ecrit_par IS NULLORDER BY 2) AS T1'''),('Any N WHERE N todo_by U, N is Note, U eid 2, N filed_under T, T eid 3',# N would actually be invarient if U eid 2 had given a specific type to U'''SELECT N.eidFROM Note AS N, filed_under_relation AS rel_filed_under1, todo_by_relation AS rel_todo_by0WHERE rel_todo_by0.eid_from=N.eid AND rel_todo_by0.eid_to=2 AND rel_filed_under1.eid_from=N.eid AND rel_filed_under1.eid_to=3'''),('Any N WHERE N todo_by U, U eid 2, P evaluee N, P eid 3','''SELECT rel_evaluee1.eid_toFROM evaluee_relation AS rel_evaluee1, todo_by_relation AS rel_todo_by0WHERE rel_evaluee1.eid_to=rel_todo_by0.eid_from AND rel_todo_by0.eid_to=2 AND rel_evaluee1.eid_from=3'''),(' Any X,U WHERE C owned_by U, NOT X owned_by U, C eid 1, X eid 2','''SELECT 2, rel_owned_by0.eid_toFROM owned_by_relation AS rel_owned_by0WHERE 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)'''),('Any GN WHERE X in_group G, G name GN, (G name "managers" OR EXISTS(X copain T, T login in ("comme", "cochon")))','''SELECT G.nameFROM EGroup AS G, in_group_relation AS rel_in_group0WHERE rel_in_group0.eid_to=G.eid AND ((G.name=managers) OR (EXISTS(SELECT 1 FROM copain_relation AS rel_copain1, EUser AS T WHERE rel_copain1.eid_from=rel_in_group0.eid_from AND rel_copain1.eid_to=T.eid AND T.login IN(comme, cochon))))'''),('Any C WHERE C is Card, EXISTS(X documented_by C)',"""SELECT C.eidFROM Card AS CWHERE EXISTS(SELECT 1 FROM documented_by_relation AS rel_documented_by0 WHERE rel_documented_by0.eid_to=C.eid)"""),('Any C WHERE C is Card, EXISTS(X documented_by C, X eid 12)',"""SELECT C.eidFROM Card AS CWHERE 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.eid)"""),('Any T WHERE C is Card, C title T, EXISTS(X documented_by C, X eid 12)',"""SELECT C.titleFROM Card AS CWHERE 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.eid)"""),('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"))','''SELECT G.name, X.loginFROM EGroup AS G, EUser AS X, in_group_relation AS rel_in_group0WHERE rel_in_group0.eid_from=X.eid AND rel_in_group0.eid_to=G.eid AND EXISTS(SELECT 1 FROM copain_relation AS rel_copain1, EUser AS T WHERE rel_copain1.eid_from=X.eid AND rel_copain1.eid_to=T.eid AND T.login=X.login AND T.login IN(comme, cochon))'''),('Any X,S, MAX(T) GROUPBY X,S ORDERBY S WHERE X is EUser, T tags X, S eid IN(32), X in_state S','''SELECT X.eid, 32, MAX(rel_tags0.eid_from)FROM EUser AS X, tags_relation AS rel_tags0WHERE rel_tags0.eid_to=X.eid AND X.in_state=32GROUP BY X.eid'''),('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"))','''SELECT COUNT(rel_concerne0.eid_from), C.nomFROM Societe AS C, concerne_relation AS rel_concerne0WHERE rel_concerne0.eid_to=C.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, Card AS N WHERE rel_concerne0.eid_from=rel_documented_by2.eid_from AND rel_documented_by2.eid_to=N.eid AND N.title=published)))GROUP BY C.nomORDER BY 1 DESCLIMIT 10'''),('Any X WHERE Y evaluee X, Y is EUser','''SELECT rel_evaluee0.eid_toFROM EUser AS Y, evaluee_relation AS rel_evaluee0WHERE rel_evaluee0.eid_from=Y.eid'''),('Any L WHERE X login "admin", X identity Y, Y login L','''SELECT Y.loginFROM EUser AS X, EUser AS YWHERE X.login=admin AND X.eid=Y.eid'''),('Any L WHERE X login "admin", NOT X identity Y, Y login L','''SELECT Y.loginFROM EUser AS X, EUser AS YWHERE X.login=admin AND NOT X.eid=Y.eid'''),('Any L WHERE X login "admin", X identity Y?, Y login L','''SELECT Y.loginFROM EUser AS X LEFT OUTER JOIN EUser AS Y ON (X.eid=Y.eid)WHERE X.login=admin'''),('Any XN ORDERBY XN WHERE X name XN','''SELECT X.nameFROM Basket AS XUNION ALLSELECT X.nameFROM ECache AS XUNION ALLSELECT X.nameFROM EConstraintType AS XUNION ALLSELECT X.nameFROM EEType AS XUNION ALLSELECT X.nameFROM EGroup AS XUNION ALLSELECT X.nameFROM EPermission AS XUNION ALLSELECT X.nameFROM ERType AS XUNION ALLSELECT X.nameFROM File AS XUNION ALLSELECT X.nameFROM Folder AS XUNION ALLSELECT X.nameFROM Image AS XUNION ALLSELECT X.nameFROM State AS XUNION ALLSELECT X.nameFROM Tag AS XUNION ALLSELECT X.nameFROM Transition AS XORDER BY 1'''),# ('Any XN WHERE X name XN GROUPBY XN',# ''''''),# ('Any XN, COUNT(X) WHERE X name XN GROUPBY XN',# ''''''),# DISTINCT, can use relatin under exists scope as principal('DISTINCT Any X,Y WHERE X name "EGroup", Y eid IN(1, 2, 3), EXISTS(X read_permission Y)','''SELECT DISTINCT X.eid, rel_read_permission0.eid_toFROM EEType AS X, read_permission_relation AS rel_read_permission0WHERE X.name=EGroup AND rel_read_permission0.eid_to IN(1, 2, 3) AND EXISTS(SELECT 1 WHERE rel_read_permission0.eid_from=X.eid)UNIONSELECT DISTINCT X.eid, rel_read_permission0.eid_toFROM ERType AS X, read_permission_relation AS rel_read_permission0WHERE X.name=EGroup AND rel_read_permission0.eid_to IN(1, 2, 3) AND EXISTS(SELECT 1 WHERE rel_read_permission0.eid_from=X.eid)'''),# no distinct, Y can't be invariant('Any X,Y WHERE X name "EGroup", Y eid IN(1, 2, 3), EXISTS(X read_permission Y)','''SELECT X.eid, Y.eidFROM EEType AS X, EGroup AS YWHERE X.name=EGroup AND Y.eid IN(1, 2, 3) AND EXISTS(SELECT 1 FROM read_permission_relation AS rel_read_permission0 WHERE rel_read_permission0.eid_from=X.eid AND rel_read_permission0.eid_to=Y.eid)UNION ALLSELECT X.eid, Y.eidFROM EEType AS X, RQLExpression AS YWHERE X.name=EGroup AND Y.eid IN(1, 2, 3) AND EXISTS(SELECT 1 FROM read_permission_relation AS rel_read_permission0 WHERE rel_read_permission0.eid_from=X.eid AND rel_read_permission0.eid_to=Y.eid)UNION ALLSELECT X.eid, Y.eidFROM EGroup AS Y, ERType AS XWHERE X.name=EGroup AND Y.eid IN(1, 2, 3) AND EXISTS(SELECT 1 FROM read_permission_relation AS rel_read_permission0 WHERE rel_read_permission0.eid_from=X.eid AND rel_read_permission0.eid_to=Y.eid)UNION ALLSELECT X.eid, Y.eidFROM ERType AS X, RQLExpression AS YWHERE X.name=EGroup AND Y.eid IN(1, 2, 3) AND EXISTS(SELECT 1 FROM read_permission_relation AS rel_read_permission0 WHERE rel_read_permission0.eid_from=X.eid AND rel_read_permission0.eid_to=Y.eid)'''),# DISTINCT but NEGED exists, can't be invariant('DISTINCT Any X,Y WHERE X name "EGroup", Y eid IN(1, 2, 3), NOT EXISTS(X read_permission Y)','''SELECT DISTINCT X.eid, Y.eidFROM EEType AS X, EGroup AS YWHERE X.name=EGroup AND Y.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.eid AND rel_read_permission0.eid_to=Y.eid)UNIONSELECT DISTINCT X.eid, Y.eidFROM EEType AS X, RQLExpression AS YWHERE X.name=EGroup AND Y.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.eid AND rel_read_permission0.eid_to=Y.eid)UNIONSELECT DISTINCT X.eid, Y.eidFROM EGroup AS Y, ERType AS XWHERE X.name=EGroup AND Y.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.eid AND rel_read_permission0.eid_to=Y.eid)UNIONSELECT DISTINCT X.eid, Y.eidFROM ERType AS X, RQLExpression AS YWHERE X.name=EGroup AND Y.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.eid AND rel_read_permission0.eid_to=Y.eid)'''),# should generate the same query as above('DISTINCT Any X,Y WHERE X name "EGroup", Y eid IN(1, 2, 3), NOT X read_permission Y','''SELECT DISTINCT X.eid, Y.eidFROM EEType AS X, EGroup AS YWHERE X.name=EGroup AND Y.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.eid AND rel_read_permission0.eid_to=Y.eid)UNIONSELECT DISTINCT X.eid, Y.eidFROM EEType AS X, RQLExpression AS YWHERE X.name=EGroup AND Y.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.eid AND rel_read_permission0.eid_to=Y.eid)UNIONSELECT DISTINCT X.eid, Y.eidFROM EGroup AS Y, ERType AS XWHERE X.name=EGroup AND Y.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.eid AND rel_read_permission0.eid_to=Y.eid)UNIONSELECT DISTINCT X.eid, Y.eidFROM ERType AS X, RQLExpression AS YWHERE X.name=EGroup AND Y.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.eid AND rel_read_permission0.eid_to=Y.eid)'''),# neged relation, can't be inveriant('Any X,Y WHERE X name "EGroup", Y eid IN(1, 2, 3), NOT X read_permission Y','''SELECT X.eid, Y.eidFROM EEType AS X, EGroup AS YWHERE X.name=EGroup AND Y.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.eid AND rel_read_permission0.eid_to=Y.eid)UNION ALLSELECT X.eid, Y.eidFROM EEType AS X, RQLExpression AS YWHERE X.name=EGroup AND Y.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.eid AND rel_read_permission0.eid_to=Y.eid)UNION ALLSELECT X.eid, Y.eidFROM EGroup AS Y, ERType AS XWHERE X.name=EGroup AND Y.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.eid AND rel_read_permission0.eid_to=Y.eid)UNION ALLSELECT X.eid, Y.eidFROM ERType AS X, RQLExpression AS YWHERE X.name=EGroup AND Y.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.eid AND rel_read_permission0.eid_to=Y.eid)'''),('Any MAX(X)+MIN(X), N GROUPBY N WHERE X name N;','''SELECT (MAX(T1.C0) + MIN(T1.C0)), T1.C1 FROM (SELECT X.eid AS C0, X.name AS C1FROM Basket AS XUNION ALLSELECT X.eid AS C0, X.name AS C1FROM ECache AS XUNION ALLSELECT X.eid AS C0, X.name AS C1FROM EConstraintType AS XUNION ALLSELECT X.eid AS C0, X.name AS C1FROM EEType AS XUNION ALLSELECT X.eid AS C0, X.name AS C1FROM EGroup AS XUNION ALLSELECT X.eid AS C0, X.name AS C1FROM EPermission AS XUNION ALLSELECT X.eid AS C0, X.name AS C1FROM ERType AS XUNION ALLSELECT X.eid AS C0, X.name AS C1FROM File AS XUNION ALLSELECT X.eid AS C0, X.name AS C1FROM Folder AS XUNION ALLSELECT X.eid AS C0, X.name AS C1FROM Image AS XUNION ALLSELECT X.eid AS C0, X.name AS C1FROM State AS XUNION ALLSELECT X.eid AS C0, X.name AS C1FROM Tag AS XUNION ALLSELECT X.eid AS C0, X.name AS C1FROM Transition AS X) AS T1GROUP BY T1.C1'''),('Any MAX(X)+MIN(LENGTH(D)), N GROUPBY N ORDERBY 1, N, DF WHERE X name N, X data D, X data_format DF;','''SELECT (MAX(T1.C1) + MIN(LENGTH(T1.C0))), T1.C2 FROM (SELECT X.data AS C0, X.eid AS C1, X.name AS C2, X.data_format AS C3FROM File AS XUNION ALLSELECT X.data AS C0, X.eid AS C1, X.name AS C2, X.data_format AS C3FROM Image AS X) AS T1GROUP BY T1.C2ORDER BY 1,2,T1.C3'''),('DISTINCT Any S ORDERBY R WHERE A is Affaire, A sujet S, A ref R','''SELECT T1.C0 FROM (SELECT DISTINCT A.sujet AS C0, A.ref AS C1FROM Affaire AS AORDER BY 2) AS T1'''),('DISTINCT Any MAX(X)+MIN(LENGTH(D)), N GROUPBY N ORDERBY 2, DF WHERE X name N, X data D, X data_format DF;','''SELECT T1.C0,T1.C1 FROM (SELECT DISTINCT (MAX(T1.C1) + MIN(LENGTH(T1.C0))) AS C0, T1.C2 AS C1, T1.C3 AS C2 FROM (SELECT DISTINCT X.data AS C0, X.eid AS C1, X.name AS C2, X.data_format AS C3FROM File AS XUNIONSELECT DISTINCT X.data AS C0, X.eid AS C1, X.name AS C2, X.data_format AS C3FROM Image AS X) AS T1GROUP BY T1.C2,T1.C3ORDER BY 2,3) AS T1'''),# ambiguity in EXISTS() -> should union the sub-query('Any T WHERE T is Tag, NOT T name in ("t1", "t2"), EXISTS(T tags X, X is IN (EUser, EGroup))','''SELECT T.eidFROM Tag AS TWHERE NOT (T.name IN(t1, t2)) AND EXISTS(SELECT 1 FROM tags_relation AS rel_tags0, EGroup AS X WHERE rel_tags0.eid_from=T.eid AND rel_tags0.eid_to=X.eid UNION SELECT 1 FROM tags_relation AS rel_tags1, EUser AS X WHERE rel_tags1.eid_from=T.eid AND rel_tags1.eid_to=X.eid)'''),# must not use a relation in EXISTS scope to inline a variable ('Any U WHERE U eid IN (1,2), EXISTS(X owned_by U)','''SELECT U.eidFROM EUser AS UWHERE U.eid IN(1, 2) AND EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0 WHERE rel_owned_by0.eid_to=U.eid)'''),('Any U WHERE EXISTS(U eid IN (1,2), X owned_by U)','''SELECT U.eidFROM EUser AS UWHERE EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0 WHERE U.eid IN(1, 2) AND rel_owned_by0.eid_to=U.eid)'''),('Any COUNT(U) WHERE EXISTS (P owned_by U, P is IN (Note, Affaire))','''SELECT COUNT(U.eid)FROM EUser AS UWHERE EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0, Affaire AS P WHERE rel_owned_by0.eid_from=P.eid AND rel_owned_by0.eid_to=U.eid UNION SELECT 1 FROM owned_by_relation AS rel_owned_by1, Note AS P WHERE rel_owned_by1.eid_from=P.eid AND rel_owned_by1.eid_to=U.eid)'''),('Any MAX(X)','''SELECT MAX(X.eid)FROM entities AS X'''),('Any MAX(X) WHERE X is Note','''SELECT MAX(X.eid)FROM Note AS X'''),('Any X WHERE X eid > 12','''SELECT X.eidFROM entities AS XWHERE X.eid>12'''),('Any X WHERE X eid > 12, X is Note',"""SELECT X.eidFROM entities AS XWHERE X.type='Note' AND X.eid>12"""),('Any X, T WHERE X eid > 12, X title T',"""SELECT X.eid, X.titleFROM Bookmark AS XWHERE X.eid>12UNION ALLSELECT X.eid, X.titleFROM Card AS XWHERE X.eid>12UNION ALLSELECT X.eid, X.titleFROM EmailThread AS XWHERE X.eid>12"""),('Any X','''SELECT X.eidFROM entities AS X'''),('Any X GROUPBY X WHERE X eid 12','''SELECT 12'''),('Any X GROUPBY X ORDERBY Y WHERE X eid 12, X login Y','''SELECT X.eidFROM EUser AS XWHERE X.eid=12GROUP BY X.eidORDER BY X.login'''),('Any U,COUNT(X) GROUPBY U WHERE U eid 12, X owned_by U HAVING COUNT(X) > 10','''SELECT rel_owned_by0.eid_to, COUNT(rel_owned_by0.eid_from)FROM owned_by_relation AS rel_owned_by0WHERE rel_owned_by0.eid_to=12GROUP BY rel_owned_by0.eid_toHAVING COUNT(rel_owned_by0.eid_from)>10'''),('DISTINCT Any X ORDERBY stockproc(X) WHERE U login X','''SELECT T1.C0 FROM (SELECT DISTINCT U.login AS C0, STOCKPROC(U.login) AS C1FROM EUser AS UORDER BY 2) AS T1'''),('DISTINCT Any X ORDERBY Y WHERE B bookmarked_by X, X login Y','''SELECT T1.C0 FROM (SELECT DISTINCT X.eid AS C0, X.login AS C1FROM EUser AS X, bookmarked_by_relation AS rel_bookmarked_by0WHERE rel_bookmarked_by0.eid_to=X.eidORDER BY 2) AS T1'''),('DISTINCT Any X ORDERBY SN WHERE X in_state S, S name SN','''SELECT T1.C0 FROM (SELECT DISTINCT X.eid AS C0, S.name AS C1FROM Affaire AS X, State AS SWHERE X.in_state=S.eidUNIONSELECT DISTINCT X.eid AS C0, S.name AS C1FROM EUser AS X, State AS SWHERE X.in_state=S.eidUNIONSELECT DISTINCT X.eid AS C0, S.name AS C1FROM Note AS X, State AS SWHERE X.in_state=S.eidORDER BY 2) AS T1'''),]MULTIPLE_SEL=[("DISTINCT Any X,Y where P is Personne, P nom X , P prenom Y;",'''SELECT DISTINCT P.nom, P.prenomFROM Personne AS P'''),("Any X,Y where P is Personne, P nom X , P prenom Y, not P nom NULL;",'''SELECT P.nom, P.prenomFROM Personne AS PWHERE NOT (P.nom IS NULL)'''),("Personne X,Y where X nom NX, Y nom NX, X eid XE, not Y eid XE",'''SELECT X.eid, Y.eidFROM Personne AS X, Personne AS YWHERE Y.nom=X.nom AND NOT (Y.eid=X.eid)''')]NEGATIONS=[("Personne X WHERE NOT X evaluee Y;",'''SELECT X.eidFROM Personne AS XWHERE NOT EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=X.eid)'''),("Note N WHERE NOT X evaluee N, X eid 0",'''SELECT N.eidFROM Note AS NWHERE NOT EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=0 AND rel_evaluee0.eid_to=N.eid)'''),('Any X WHERE NOT X travaille S, X is Personne','''SELECT X.eidFROM Personne AS XWHERE NOT EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_from=X.eid)'''),("Personne P where not P datenaiss TODAY",'''SELECT P.eidFROM Personne AS PWHERE NOT (DATE(P.datenaiss)=CURRENT_DATE)'''),("Personne P where NOT P concerne A",'''SELECT P.eidFROM Personne AS PWHERE NOT EXISTS(SELECT 1 FROM concerne_relation AS rel_concerne0 WHERE rel_concerne0.eid_from=P.eid)'''),("Affaire A where not P concerne A",'''SELECT A.eidFROM Affaire AS AWHERE NOT EXISTS(SELECT 1 FROM concerne_relation AS rel_concerne0 WHERE rel_concerne0.eid_to=A.eid)'''),("Personne P where not P concerne A, A sujet ~= 'TEST%'",'''SELECT P.eidFROM Affaire AS A, Personne AS PWHERE NOT EXISTS(SELECT 1 FROM concerne_relation AS rel_concerne0 WHERE rel_concerne0.eid_from=P.eid AND rel_concerne0.eid_to=A.eid) AND A.sujet ILIKE TEST%'''),('Any S WHERE NOT T eid 28258, T tags S','''SELECT rel_tags0.eid_toFROM tags_relation AS rel_tags0WHERE NOT (rel_tags0.eid_from=28258)'''),('Any S WHERE T is Tag, T name TN, NOT T eid 28258, T tags S, S name SN','''SELECT S.eidFROM EGroup AS S, Tag AS T, tags_relation AS rel_tags0WHERE NOT (T.eid=28258) AND rel_tags0.eid_from=T.eid AND rel_tags0.eid_to=S.eidUNION ALLSELECT S.eidFROM State AS S, Tag AS T, tags_relation AS rel_tags0WHERE NOT (T.eid=28258) AND rel_tags0.eid_from=T.eid AND rel_tags0.eid_to=S.eidUNION ALLSELECT S.eidFROM Tag AS S, Tag AS T, tags_relation AS rel_tags0WHERE NOT (T.eid=28258) AND rel_tags0.eid_from=T.eid AND rel_tags0.eid_to=S.eid'''),('Any X,Y WHERE X created_by Y, X eid 5, NOT Y eid 6','''SELECT 5, rel_created_by0.eid_toFROM created_by_relation AS rel_created_by0WHERE rel_created_by0.eid_from=5 AND NOT (rel_created_by0.eid_to=6)'''),('Note X WHERE NOT Y evaluee X','''SELECT X.eidFROM Note AS XWHERE NOT EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_to=X.eid)'''),('Any Y WHERE NOT Y evaluee X','''SELECT Y.eidFROM Division AS YWHERE NOT EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=Y.eid)UNION ALLSELECT Y.eidFROM EUser AS YWHERE NOT EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=Y.eid)UNION ALLSELECT Y.eidFROM Personne AS YWHERE NOT EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=Y.eid)UNION ALLSELECT Y.eidFROM Societe AS YWHERE NOT EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=Y.eid)UNION ALLSELECT Y.eidFROM SubDivision AS YWHERE NOT EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=Y.eid)'''),('Any X WHERE NOT Y evaluee X, Y is EUser','''SELECT X.eidFROM Note AS XWHERE NOT EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0,EUser AS Y WHERE rel_evaluee0.eid_from=Y.eid AND rel_evaluee0.eid_to=X.eid)'''),('Any X,T WHERE X title T, NOT X is Bookmark','''SELECT DISTINCT X.eid, X.titleFROM Card AS XUNIONSELECT DISTINCT X.eid, X.titleFROM EmailThread AS X'''),('Any K,V WHERE P is EProperty, P pkey K, P value V, NOT P for_user U','''SELECT DISTINCT P.pkey, P.valueFROM EProperty AS PWHERE P.for_user IS NULL'''),]OUTER_JOIN=[('Any X,S WHERE X travaille S?','''SELECT X.eid, rel_travaille0.eid_toFROM Personne AS X LEFT OUTER JOIN travaille_relation AS rel_travaille0 ON (rel_travaille0.eid_from=X.eid)'''#SELECT X.eid, S.eid#FROM Personne AS X LEFT OUTER JOIN travaille_relation AS rel_travaille0 ON (rel_travaille0.eid_from=X.eid) LEFT OUTER JOIN Societe AS S ON (rel_travaille0.eid_to=S.eid)'''),('Any S,X WHERE X? travaille S, S is Societe','''SELECT S.eid, rel_travaille0.eid_fromFROM Societe AS S LEFT OUTER JOIN travaille_relation AS rel_travaille0 ON (rel_travaille0.eid_to=S.eid)'''#SELECT S.eid, X.eid#FROM Societe AS S LEFT OUTER JOIN travaille_relation AS rel_travaille0 ON (rel_travaille0.eid_to=S.eid) LEFT OUTER JOIN Personne AS X ON (rel_travaille0.eid_from=X.eid)'''),('Any N,A WHERE N inline1 A?','''SELECT N.eid, N.inline1FROM Note AS N'''),('Any SN WHERE X from_state S?, S name SN','''SELECT S.nameFROM TrInfo AS X LEFT OUTER JOIN State AS S ON (X.from_state=S.eid)'''),('Any A,N WHERE N? inline1 A','''SELECT A.eid, N.eidFROM Affaire AS A LEFT OUTER JOIN Note AS N ON (N.inline1=A.eid)'''),('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?','''SELECT A.eid, A.creation_date, A.modification_date, A.comment, A.from_state, A.to_state, A.wf_info_forFROM TrInfo AS AWHERE A.eid=12'''),('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','''SELECT WF.from_state, WF.to_state, WF.comment, WF.creation_date, rel_owned_by0.eid_toFROM TrInfo AS WF, owned_by_relation AS rel_owned_by0WHERE WF.wf_info_for=1 AND WF.to_state IS NOT NULL AND rel_owned_by0.eid_from=WF.eidORDER BY 4 DESC'''),('Any X WHERE X is Affaire, S is Societe, EXISTS(X owned_by U OR (X concerne S?, S owned_by U))','''SELECT X.eidFROM Affaire AS XWHERE EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0, EUser AS U, Affaire AS A LEFT OUTER JOIN concerne_relation AS rel_concerne1 ON (rel_concerne1.eid_from=A.eid) LEFT OUTER JOIN Societe AS S ON (rel_concerne1.eid_to=S.eid), owned_by_relation AS rel_owned_by2 WHERE ((rel_owned_by0.eid_from=A.eid AND rel_owned_by0.eid_to=U.eid) OR (rel_owned_by2.eid_from=S.eid AND rel_owned_by2.eid_to=U.eid)) AND X.eid=A.eid)'''),('Any C,M WHERE C travaille G?, G evaluee M?, G is Societe','''SELECT C.eid, rel_evaluee1.eid_toFROM Personne AS C LEFT OUTER JOIN travaille_relation AS rel_travaille0 ON (rel_travaille0.eid_from=C.eid) LEFT OUTER JOIN Societe AS G ON (rel_travaille0.eid_to=G.eid) LEFT OUTER JOIN evaluee_relation AS rel_evaluee1 ON (rel_evaluee1.eid_from=G.eid)'''#SELECT C.eid, M.eid#FROM Personne AS C LEFT OUTER JOIN travaille_relation AS rel_travaille0 ON (rel_travaille0.eid_from=C.eid) LEFT OUTER JOIN Societe AS G ON (rel_travaille0.eid_to=G.eid) LEFT OUTER JOIN evaluee_relation AS rel_evaluee1 ON (rel_evaluee1.eid_from=G.eid) LEFT OUTER JOIN Note AS M ON (rel_evaluee1.eid_to=M.eid)'''),('Any A,C WHERE A documented_by C?, (C is NULL) OR (EXISTS(C require_permission F, ''F name "read", F require_group E, U in_group E)), U eid 1','''SELECT A.eid, rel_documented_by0.eid_toFROM Affaire AS A LEFT OUTER JOIN documented_by_relation AS rel_documented_by0 ON (rel_documented_by0.eid_from=A.eid)WHERE ((rel_documented_by0.eid_to IS NULL) OR (EXISTS(SELECT 1 FROM require_permission_relation AS rel_require_permission1, EPermission AS F, require_group_relation AS rel_require_group2, in_group_relation AS rel_in_group3 WHERE rel_documented_by0.eid_to=rel_require_permission1.eid_from AND rel_require_permission1.eid_to=F.eid AND F.name=read AND rel_require_group2.eid_from=F.eid AND rel_in_group3.eid_from=1 AND rel_in_group3.eid_to=rel_require_group2.eid_to)))'''),("Any X WHERE X eid 12, P? connait X",'''SELECT X.eidFROM Personne AS X LEFT OUTER JOIN connait_relation AS rel_connait0 ON (rel_connait0.eid_to=12)WHERE X.eid=12'''#SELECT 12#FROM Personne AS X LEFT OUTER JOIN connait_relation AS rel_connait0 ON (rel_connait0.eid_to=12) LEFT OUTER JOIN Personne AS P ON (rel_connait0.eid_from=P.eid)#WHERE X.eid=12'''),('Any GN, TN ORDERBY GN WHERE T tags G?, T name TN, G name GN','''SELECT _T0.C1, T.nameFROM Tag AS T LEFT OUTER JOIN tags_relation AS rel_tags0 ON (rel_tags0.eid_from=T.eid) LEFT OUTER JOIN (SELECT G.eid AS C0, G.name AS C1FROM EGroup AS GUNION ALLSELECT G.eid AS C0, G.name AS C1FROM State AS GUNION ALLSELECT G.eid AS C0, G.name AS C1FROM Tag AS G) AS _T0 ON (rel_tags0.eid_to=_T0.C0)ORDER BY 1'''),# optional variable with additional restriction('Any T,G WHERE T tags G?, G name "hop", G is EGroup','''SELECT T.eid, G.eidFROM Tag AS T LEFT OUTER JOIN tags_relation AS rel_tags0 ON (rel_tags0.eid_from=T.eid) LEFT OUTER JOIN EGroup AS G ON (rel_tags0.eid_to=G.eid AND G.name=hop)'''),# optional variable with additional invariant restriction('Any T,G WHERE T tags G?, G eid 12','''SELECT T.eid, rel_tags0.eid_toFROM Tag AS T LEFT OUTER JOIN tags_relation AS rel_tags0 ON (rel_tags0.eid_from=T.eid AND rel_tags0.eid_to=12)'''),# optional variable with additional restriction appearing before the relation('Any T,G WHERE G name "hop", T tags G?, G is EGroup','''SELECT T.eid, G.eidFROM Tag AS T LEFT OUTER JOIN tags_relation AS rel_tags0 ON (rel_tags0.eid_from=T.eid) LEFT OUTER JOIN EGroup AS G ON (rel_tags0.eid_to=G.eid AND G.name=hop)'''),# optional variable with additional restriction on inlined relation# XXX the expected result should be as the query below. So what, raise BadRQLQuery ?('Any T,G,S WHERE T tags G?, G in_state S, S name "hop", G is EUser','''SELECT T.eid, G.eid, S.eidFROM State AS S, Tag AS T LEFT OUTER JOIN tags_relation AS rel_tags0 ON (rel_tags0.eid_from=T.eid) LEFT OUTER JOIN EUser AS G ON (rel_tags0.eid_to=G.eid)WHERE G.in_state=S.eid AND S.name=hop'''),# optional variable with additional invariant restriction on an inlined relation('Any T,G,S WHERE T tags G, G in_state S?, S eid 1, G is EUser','''SELECT rel_tags0.eid_from, G.eid, G.in_stateFROM EUser AS G, tags_relation AS rel_tags0WHERE rel_tags0.eid_to=G.eid AND (G.in_state=1 OR G.in_state IS NULL)'''),# two optional variables with additional invariant restriction on an inlined relation('Any T,G,S WHERE T tags G?, G in_state S?, S eid 1, G is EUser','''SELECT T.eid, G.eid, G.in_stateFROM Tag AS T LEFT OUTER JOIN tags_relation AS rel_tags0 ON (rel_tags0.eid_from=T.eid) LEFT OUTER JOIN EUser AS G ON (rel_tags0.eid_to=G.eid AND (G.in_state=1 OR G.in_state IS NULL))'''),# two optional variables with additional restriction on an inlined relation('Any T,G,S WHERE T tags G?, G in_state S?, S name "hop", G is EUser','''SELECT T.eid, G.eid, S.eidFROM Tag AS T LEFT OUTER JOIN tags_relation AS rel_tags0 ON (rel_tags0.eid_from=T.eid) LEFT OUTER JOIN EUser AS G ON (rel_tags0.eid_to=G.eid) LEFT OUTER JOIN State AS S ON (G.in_state=S.eid AND S.name=hop)'''),# two optional variables with additional restriction on an ambigous inlined relation('Any T,G,S WHERE T tags G?, G in_state S?, S name "hop"','''SELECT T.eid, _T0.C0, _T0.C1FROM Tag AS T LEFT OUTER JOIN tags_relation AS rel_tags0 ON (rel_tags0.eid_from=T.eid) LEFT OUTER JOIN (SELECT G.eid AS C0, S.eid AS C1FROM Affaire AS G LEFT OUTER JOIN State AS S ON (G.in_state=S.eid AND S.name=hop) UNION ALLSELECT G.eid AS C0, S.eid AS C1FROM EUser AS G LEFT OUTER JOIN State AS S ON (G.in_state=S.eid AND S.name=hop) UNION ALLSELECT G.eid AS C0, S.eid AS C1FROM Note AS G LEFT OUTER JOIN State AS S ON (G.in_state=S.eid AND S.name=hop) ) AS _T0 ON (rel_tags0.eid_to=_T0.C0)'''),]VIRTUAL_VARS=[("Personne P WHERE P travaille S, S tel T, S fax T, S is Societe;",'''SELECT rel_travaille0.eid_fromFROM Societe AS S, travaille_relation AS rel_travaille0WHERE rel_travaille0.eid_to=S.eid AND S.fax=S.tel'''),("Personne P where X eid 0, X creation_date D, P datenaiss < D, X is Affaire",'''SELECT P.eidFROM Affaire AS X, Personne AS PWHERE X.eid=0 AND P.datenaiss<X.creation_date'''),("Any N,T WHERE N is Note, N type T;",'''SELECT N.eid, N.typeFROM Note AS N'''),("Personne P where X is Personne, X tel T, X fax F, P fax T+F",'''SELECT P.eidFROM Personne AS P, Personne AS XWHERE P.fax=(X.tel + X.fax)'''),("Personne P where X tel T, X fax F, P fax IN (T,F)",'''SELECT P.eidFROM Division AS X, Personne AS PWHERE P.fax IN(X.tel, X.fax)UNION ALLSELECT P.eidFROM Personne AS P, Personne AS XWHERE P.fax IN(X.tel, X.fax)UNION ALLSELECT P.eidFROM Personne AS P, Societe AS XWHERE P.fax IN(X.tel, X.fax)UNION ALLSELECT P.eidFROM Personne AS P, SubDivision AS XWHERE P.fax IN(X.tel, X.fax)'''),("Personne P where X tel T, X fax F, P fax IN (T,F,0832542332)",'''SELECT P.eidFROM Division AS X, Personne AS PWHERE P.fax IN(X.tel, X.fax, 832542332)UNION ALLSELECT P.eidFROM Personne AS P, Personne AS XWHERE P.fax IN(X.tel, X.fax, 832542332)UNION ALLSELECT P.eidFROM Personne AS P, Societe AS XWHERE P.fax IN(X.tel, X.fax, 832542332)UNION ALLSELECT P.eidFROM Personne AS P, SubDivision AS XWHERE P.fax IN(X.tel, X.fax, 832542332)'''),]FUNCS=[("Any COUNT(P) WHERE P is Personne",'''SELECT COUNT(P.eid)FROM Personne AS P'''),## ("Personne X where X nom upper('TOTO')",## '''SELECT X.eid\nFROM Personne AS X\nWHERE UPPER(X.nom) = TOTO'''),## ("Personne X where X nom Y, UPPER(X) prenom upper(Y)",## '''SELECT X.eid\nFROM Personne AS X\nWHERE UPPER(X.prenom) = UPPER(X.nom)'''),]SYMETRIC=[('Any P WHERE X eid 0, X connait P','''SELECT DISTINCT P.eidFROM Personne AS P, connait_relation AS rel_connait0WHERE (rel_connait0.eid_from=0 AND rel_connait0.eid_to=P.eid OR rel_connait0.eid_to=0 AND rel_connait0.eid_from=P.eid)'''# '''SELECT rel_connait0.eid_to# FROM connait_relation AS rel_connait0# WHERE rel_connait0.eid_from=0# UNION# SELECT rel_connait0.eid_from# FROM connait_relation AS rel_connait0# WHERE rel_connait0.eid_to=0'''),('Any P WHERE X connait P','''SELECT DISTINCT P.eidFROM Personne AS P, connait_relation AS rel_connait0WHERE (rel_connait0.eid_to=P.eid OR rel_connait0.eid_from=P.eid)'''),('Any X WHERE X connait P','''SELECT DISTINCT X.eidFROM Personne AS X, connait_relation AS rel_connait0WHERE (rel_connait0.eid_from=X.eid OR rel_connait0.eid_to=X.eid)'''),('Any P WHERE X eid 0, NOT X connait P','''SELECT P.eidFROM Personne AS PWHERE NOT EXISTS(SELECT 1 FROM connait_relation AS rel_connait0 WHERE (rel_connait0.eid_from=0 AND rel_connait0.eid_to=P.eid OR rel_connait0.eid_to=0 AND rel_connait0.eid_from=P.eid))'''),('Any P WHERE NOT X connait P','''SELECT P.eidFROM Personne AS PWHERE NOT EXISTS(SELECT 1 FROM connait_relation AS rel_connait0 WHERE (rel_connait0.eid_to=P.eid OR rel_connait0.eid_from=P.eid))'''),('Any X WHERE NOT X connait P','''SELECT X.eidFROM Personne AS XWHERE NOT EXISTS(SELECT 1 FROM connait_relation AS rel_connait0 WHERE (rel_connait0.eid_from=X.eid OR rel_connait0.eid_to=X.eid))'''),('Any P WHERE X connait P, P nom "nom"','''SELECT DISTINCT P.eidFROM Personne AS P, connait_relation AS rel_connait0WHERE (rel_connait0.eid_to=P.eid OR rel_connait0.eid_from=P.eid) AND P.nom=nom'''),('Any X WHERE X connait P, P nom "nom"','''SELECT DISTINCT X.eidFROM Personne AS P, Personne AS X, connait_relation AS rel_connait0WHERE (rel_connait0.eid_from=X.eid AND rel_connait0.eid_to=P.eid OR rel_connait0.eid_to=X.eid AND rel_connait0.eid_from=P.eid) AND P.nom=nom'''),('Any X ORDERBY X DESC LIMIT 9 WHERE E eid 0, E connait X','''SELECT DISTINCT X.eidFROM Personne AS X, connait_relation AS rel_connait0WHERE (rel_connait0.eid_from=0 AND rel_connait0.eid_to=X.eid OR rel_connait0.eid_to=0 AND rel_connait0.eid_from=X.eid)ORDER BY 1 DESCLIMIT 9'''),('DISTINCT Any P WHERE P connait S OR S connait P, S nom "chouette"','''SELECT DISTINCT P.eidFROM Personne AS P, Personne AS S, connait_relation AS rel_connait0WHERE (rel_connait0.eid_from=P.eid AND rel_connait0.eid_to=S.eid OR rel_connait0.eid_to=P.eid AND rel_connait0.eid_from=S.eid) AND S.nom=chouette''')]INLINE=[('Any P, L WHERE N ecrit_par P, P nom L, N eid 0','''SELECT P.eid, P.nomFROM Note AS N, Personne AS PWHERE N.ecrit_par=P.eid AND N.eid=0'''),('Any N WHERE NOT N ecrit_par P, P nom "toto"','''SELECT DISTINCT N.eidFROM Note AS N, Personne AS PWHERE (N.ecrit_par IS NULL OR N.ecrit_par!=P.eid) AND P.nom=toto'''),('Any P WHERE N ecrit_par P, N eid 0','''SELECT N.ecrit_parFROM Note AS NWHERE N.ecrit_par IS NOT NULL AND N.eid=0'''),('Any P WHERE N ecrit_par P, P is Personne, N eid 0','''SELECT P.eidFROM Note AS N, Personne AS PWHERE N.ecrit_par=P.eid AND N.eid=0'''),('Any P WHERE NOT N ecrit_par P, P is Personne, N eid 512','''SELECT DISTINCT P.eidFROM Note AS N, Personne AS PWHERE (N.ecrit_par IS NULL OR N.ecrit_par!=P.eid) AND N.eid=512'''),('Any S,ES,T WHERE S state_of ET, ET name "EUser", ES allowed_transition T, T destination_state S','''SELECT T.destination_state, rel_allowed_transition1.eid_from, T.eidFROM EEType AS ET, Transition AS T, allowed_transition_relation AS rel_allowed_transition1, state_of_relation AS rel_state_of0WHERE T.destination_state=rel_state_of0.eid_from AND rel_state_of0.eid_to=ET.eid AND ET.name=EUser AND rel_allowed_transition1.eid_to=T.eid'''),('Any O WHERE S eid 0, S in_state O','''SELECT S.in_stateFROM Affaire AS SWHERE S.eid=0 AND S.in_state IS NOT NULLUNION ALLSELECT S.in_stateFROM EUser AS SWHERE S.eid=0 AND S.in_state IS NOT NULLUNION ALLSELECT S.in_stateFROM Note AS SWHERE S.eid=0 AND S.in_state IS NOT NULL''')]fromlogilab.common.adbhimportADV_FUNC_HELPER_DIRECTORYclassPostgresSQLGeneratorTC(RQLGeneratorTC):schema=schema#capture = TruedefsetUp(self):RQLGeneratorTC.setUp(self)indexer=get_indexer('postgres','utf8')dbms_helper=ADV_FUNC_HELPER_DIRECTORY['postgres']dbms_helper.fti_uid_attr=indexer.uid_attrdbms_helper.fti_table=indexer.tabledbms_helper.fti_restriction_sql=indexer.restriction_sqldbms_helper.fti_need_distinct_query=indexer.need_distinctself.o=SQLGenerator(schema,dbms_helper)def_norm_sql(self,sql):returnsql.strip()def_check(self,rql,sql,varmap=None):try:union=self._prepare(rql)r,args=self.o.generate(union,{'text':'hip hop momo'},varmap=varmap)self.assertLinesEquals((r%args).strip(),self._norm_sql(sql))exceptException,ex:printrqlif'r'inlocals():print(r%args).strip()print'!='printsql.strip()raisedef_parse(self,rqls):forrql,sqlinrqls:yieldself._check,rql,sqldef_checkall(self,rql,sql):try:rqlst=self._prepare(rql)r,args=self.o.generate(rqlst)self.assertEqual((r.strip(),args),sql)exceptException,ex:printrqlif'r'inlocals():printr.strip()print'!='printsql[0].strip()raisereturn# rqlst, solutions = self._prepare(rql)# for i, sol in enumerate(solutions):# try:# r, args = self.o.generate([(rqlst, sol)])# self.assertEqual((r.strip(), args), sqls[i])# except Exception, ex:# print rql# raisedeftest1(self):self._checkall('Any count(RDEF) WHERE RDEF relation_type X, X eid %(x)s',("""SELECT COUNT(T1.C0) FROM (SELECT RDEF.eid AS C0FROM EFRDef AS RDEFWHERE RDEF.relation_type=%(x)sUNION ALLSELECT RDEF.eid AS C0FROM ENFRDef AS RDEFWHERE RDEF.relation_type=%(x)s) AS T1""",{}),)deftest2(self):self._checkall('Any X WHERE C comments X, C eid %(x)s',('''SELECT rel_comments0.eid_toFROM comments_relation AS rel_comments0WHERE rel_comments0.eid_from=%(x)s''',{}))deftest_cache_1(self):self._check('Any X WHERE X in_basket B, B eid 12','''SELECT rel_in_basket0.eid_fromFROM in_basket_relation AS rel_in_basket0WHERE rel_in_basket0.eid_to=12''')self._check('Any X WHERE X in_basket B, B eid 12','''SELECT rel_in_basket0.eid_fromFROM in_basket_relation AS rel_in_basket0WHERE rel_in_basket0.eid_to=12''')deftest_varmap(self):self._check('Any X,L WHERE X is EUser, X in_group G, X login L, G name "users"','''SELECT T00.x, T00.lFROM EGroup AS G, T00, in_group_relation AS rel_in_group0WHERE rel_in_group0.eid_from=T00.x AND rel_in_group0.eid_to=G.eid AND G.name=users''',varmap={'X':'T00.x','X.login':'T00.l'})self._check('Any X,L,GN WHERE X is EUser, X in_group G, X login L, G name GN','''SELECT T00.x, T00.l, G.nameFROM EGroup AS G, T00, in_group_relation AS rel_in_group0WHERE rel_in_group0.eid_from=T00.x AND rel_in_group0.eid_to=G.eid''',varmap={'X':'T00.x','X.login':'T00.l'})deftest_parser_parse(self):fortinself._parse(PARSER):yieldtdeftest_basic_parse(self):fortinself._parse(BASIC):yieldtdeftest_advanced_parse(self):fortinself._parse(ADVANCED):yieldtdeftest_outer_join_parse(self):fortinself._parse(OUTER_JOIN):yieldtdeftest_virtual_vars_parse(self):fortinself._parse(VIRTUAL_VARS):yieldtdeftest_multiple_sel_parse(self):fortinself._parse(MULTIPLE_SEL):yieldtdeftest_functions(self):fortinself._parse(FUNCS):yieldtdeftest_negation(self):fortinself._parse(NEGATIONS):yieldtdeftest_union(self):fortinself._parse((('(Any N ORDERBY 1 WHERE X name N, X is State)'' UNION ''(Any NN ORDERBY 1 WHERE XX name NN, XX is Transition)','''(SELECT X.nameFROM State AS XORDER BY 1)UNION ALL(SELECT XX.nameFROM Transition AS XXORDER BY 1)'''),)):yieldtdeftest_subquery(self):fortinself._parse((('Any N ORDERBY 1 WITH N BEING ''((Any N WHERE X name N, X is State)'' UNION ''(Any NN WHERE XX name NN, XX is Transition))','''SELECT _T0.C0FROM ((SELECT X.name AS C0FROM State AS X)UNION ALL(SELECT XX.name AS C0FROM Transition AS XX)) AS _T0ORDER BY 1'''),('Any N,NX ORDERBY NX WITH N,NX BEING ''((Any N,COUNT(X) GROUPBY N WHERE X name N, X is State HAVING COUNT(X)>1)'' UNION ''(Any N,COUNT(X) GROUPBY N WHERE X name N, X is Transition HAVING COUNT(X)>1))','''SELECT _T0.C0, _T0.C1FROM ((SELECT X.name AS C0, COUNT(X.eid) AS C1FROM State AS XGROUP BY X.nameHAVING COUNT(X.eid)>1)UNION ALL(SELECT X.name AS C0, COUNT(X.eid) AS C1FROM Transition AS XGROUP BY X.nameHAVING COUNT(X.eid)>1)) AS _T0ORDER BY 2'''),('Any N,COUNT(X) GROUPBY N HAVING COUNT(X)>1 ''WITH X, N BEING ((Any X, N WHERE X name N, X is State) UNION '' (Any X, N WHERE X name N, X is Transition))','''SELECT _T0.C1, COUNT(_T0.C0)FROM ((SELECT X.eid AS C0, X.name AS C1FROM State AS X)UNION ALL(SELECT X.eid AS C0, X.name AS C1FROM Transition AS X)) AS _T0GROUP BY _T0.C1HAVING COUNT(_T0.C0)>1'''),('Any ETN,COUNT(X) GROUPBY ETN WHERE X is ET, ET name ETN ''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)))))','''SELECT ET.name, COUNT(_T0.C0)FROM ((SELECT X.eid AS C0FROM Societe AS X)UNION ALL(SELECT X.eid AS C0FROM Affaire AS XWHERE ((EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0 WHERE rel_owned_by0.eid_from=X.eid AND rel_owned_by0.eid_to=1)) OR (((EXISTS(SELECT 1 FROM Affaire AS D LEFT OUTER JOIN concerne_relation AS rel_concerne1 ON (rel_concerne1.eid_from=D.eid) LEFT OUTER JOIN Note AS B ON (rel_concerne1.eid_to=B.eid), owned_by_relation AS rel_owned_by2 WHERE rel_owned_by2.eid_from=B.eid AND rel_owned_by2.eid_to=1 AND X.eid=D.eid)) OR (EXISTS(SELECT 1 FROM Affaire AS F LEFT OUTER JOIN concerne_relation AS rel_concerne3 ON (rel_concerne3.eid_from=F.eid) LEFT OUTER JOIN Societe AS E ON (rel_concerne3.eid_to=E.eid), owned_by_relation AS rel_owned_by4 WHERE rel_owned_by4.eid_from=E.eid AND rel_owned_by4.eid_to=1 AND X.eid=F.eid))))))) AS _T0, EEType AS ET, is_relation AS rel_is0WHERE rel_is0.eid_from=_T0.C0 AND rel_is0.eid_to=ET.eidGROUP BY ET.name'''),)):yieldtdeftest_subquery_error(self):rql=('Any N WHERE X name N WITH X BEING ''((Any X WHERE X is State)'' UNION '' (Any X WHERE X is Transition))')rqlst=self._prepare(rql)self.assertRaises(BadRQLQuery,self.o.generate,rqlst)deftest_symetric(self):fortinself._parse(SYMETRIC):yieldtdeftest_inline(self):fortinself._parse(INLINE):yieldtdeftest_has_text(self):fortinself._parse((('Any X WHERE X has_text "toto tata"',"""SELECT appears0.uidFROM appears AS appears0WHERE appears0.words @@ to_tsquery('default', 'toto&tata')"""),('Personne X WHERE X has_text "toto tata"',"""SELECT X.eidFROM appears AS appears0, entities AS XWHERE appears0.words @@ to_tsquery('default', 'toto&tata') AND appears0.uid=X.eid AND X.type='Personne'"""),('Personne X WHERE X has_text %(text)s',"""SELECT X.eidFROM appears AS appears0, entities AS XWHERE appears0.words @@ to_tsquery('default', 'hip&hop&momo') AND appears0.uid=X.eid AND X.type='Personne'"""),('Any X WHERE X has_text "toto tata", X name "tutu"',"""SELECT X.eidFROM Basket AS X, appears AS appears0WHERE appears0.words @@ to_tsquery('default', 'toto&tata') AND appears0.uid=X.eid AND X.name=tutuUNION ALLSELECT X.eidFROM File AS X, appears AS appears0WHERE appears0.words @@ to_tsquery('default', 'toto&tata') AND appears0.uid=X.eid AND X.name=tutuUNION ALLSELECT X.eidFROM Folder AS X, appears AS appears0WHERE appears0.words @@ to_tsquery('default', 'toto&tata') AND appears0.uid=X.eid AND X.name=tutuUNION ALLSELECT X.eidFROM Image AS X, appears AS appears0WHERE appears0.words @@ to_tsquery('default', 'toto&tata') AND appears0.uid=X.eid AND X.name=tutuUNION ALLSELECT X.eidFROM State AS X, appears AS appears0WHERE appears0.words @@ to_tsquery('default', 'toto&tata') AND appears0.uid=X.eid AND X.name=tutuUNION ALLSELECT X.eidFROM Tag AS X, appears AS appears0WHERE appears0.words @@ to_tsquery('default', 'toto&tata') AND appears0.uid=X.eid AND X.name=tutuUNION ALLSELECT X.eidFROM Transition AS X, appears AS appears0WHERE appears0.words @@ to_tsquery('default', 'toto&tata') AND appears0.uid=X.eid AND X.name=tutu"""),('Personne X where X has_text %(text)s, X travaille S, S has_text %(text)s',"""SELECT X.eidFROM appears AS appears0, appears AS appears2, entities AS X, travaille_relation AS rel_travaille1WHERE 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')"""),)):yieldtdeftest_from_clause_needed(self):queries=[("Any 1 WHERE EXISTS(T is EGroup, T name 'managers')",'''SELECT 1WHERE EXISTS(SELECT 1 FROM EGroup AS T WHERE T.name=managers)'''),('Any X,Y WHERE NOT X created_by Y, X eid 5, Y eid 6','''SELECT 5, 6WHERE 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)'''),]fortinself._parse(queries):yieldtdeftest_ambigous_exists_no_from_clause(self):self._check('Any COUNT(U) WHERE U eid 1, EXISTS (P owned_by U, P is IN (Note, Affaire))','''SELECT COUNT(1)WHERE EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0, Affaire AS P WHERE rel_owned_by0.eid_from=P.eid AND rel_owned_by0.eid_to=1 UNION SELECT 1 FROM owned_by_relation AS rel_owned_by1, Note AS P WHERE rel_owned_by1.eid_from=P.eid AND rel_owned_by1.eid_to=1)''')classSqliteSQLGeneratorTC(PostgresSQLGeneratorTC):defsetUp(self):RQLGeneratorTC.setUp(self)indexer=get_indexer('sqlite','utf8')dbms_helper=ADV_FUNC_HELPER_DIRECTORY['sqlite']dbms_helper.fti_uid_attr=indexer.uid_attrdbms_helper.fti_table=indexer.tabledbms_helper.fti_restriction_sql=indexer.restriction_sqldbms_helper.fti_need_distinct_query=indexer.need_distinctself.o=SQLGenerator(schema,dbms_helper)def_norm_sql(self,sql):returnsql.strip().replace(' ILIKE ',' LIKE ')deftest_union(self):fortinself._parse((('(Any N ORDERBY 1 WHERE X name N, X is State)'' UNION ''(Any NN ORDERBY 1 WHERE XX name NN, XX is Transition)','''SELECT X.nameFROM State AS XORDER BY 1UNION ALLSELECT XX.nameFROM Transition AS XXORDER BY 1'''),)):yieldtdeftest_subquery(self):# NOTE: no paren around UNION with sqlitebackendfortinself._parse((('Any N ORDERBY 1 WITH N BEING ''((Any N WHERE X name N, X is State)'' UNION ''(Any NN WHERE XX name NN, XX is Transition))','''SELECT _T0.C0FROM (SELECT X.name AS C0FROM State AS XUNION ALLSELECT XX.name AS C0FROM Transition AS XX) AS _T0ORDER BY 1'''),('Any N,NX ORDERBY NX WITH N,NX BEING ''((Any N,COUNT(X) GROUPBY N WHERE X name N, X is State HAVING COUNT(X)>1)'' UNION ''(Any N,COUNT(X) GROUPBY N WHERE X name N, X is Transition HAVING COUNT(X)>1))','''SELECT _T0.C0, _T0.C1FROM (SELECT X.name AS C0, COUNT(X.eid) AS C1FROM State AS XGROUP BY X.nameHAVING COUNT(X.eid)>1UNION ALLSELECT X.name AS C0, COUNT(X.eid) AS C1FROM Transition AS XGROUP BY X.nameHAVING COUNT(X.eid)>1) AS _T0ORDER BY 2'''),('Any N,COUNT(X) GROUPBY N HAVING COUNT(X)>1 ''WITH X, N BEING ((Any X, N WHERE X name N, X is State) UNION '' (Any X, N WHERE X name N, X is Transition))','''SELECT _T0.C1, COUNT(_T0.C0)FROM (SELECT X.eid AS C0, X.name AS C1FROM State AS XUNION ALLSELECT X.eid AS C0, X.name AS C1FROM Transition AS X) AS _T0GROUP BY _T0.C1HAVING COUNT(_T0.C0)>1'''),)):yieldtdeftest_has_text(self):fortinself._parse((('Any X WHERE X has_text "toto tata"',"""SELECT appears0.uidFROM appears AS appears0WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata'))"""),('Any X WHERE X has_text %(text)s',"""SELECT appears0.uidFROM appears AS appears0WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('hip', 'hop', 'momo'))"""),('Personne X WHERE X has_text "toto tata"',"""SELECT X.eidFROM appears AS appears0, entities AS XWHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata')) AND appears0.uid=X.eid AND X.type='Personne'"""),('Any X WHERE X has_text "toto tata", X name "tutu"',"""SELECT X.eidFROM Basket AS X, appears AS appears0WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata')) AND appears0.uid=X.eid AND X.name=tutuUNION ALLSELECT X.eidFROM File AS X, appears AS appears0WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata')) AND appears0.uid=X.eid AND X.name=tutuUNION ALLSELECT X.eidFROM Folder AS X, appears AS appears0WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata')) AND appears0.uid=X.eid AND X.name=tutuUNION ALLSELECT X.eidFROM Image AS X, appears AS appears0WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata')) AND appears0.uid=X.eid AND X.name=tutuUNION ALLSELECT X.eidFROM State AS X, appears AS appears0WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata')) AND appears0.uid=X.eid AND X.name=tutuUNION ALLSELECT X.eidFROM Tag AS X, appears AS appears0WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata')) AND appears0.uid=X.eid AND X.name=tutuUNION ALLSELECT X.eidFROM Transition AS X, appears AS appears0WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata')) AND appears0.uid=X.eid AND X.name=tutu"""),)):yieldtclassMySQLGenerator(PostgresSQLGeneratorTC):defsetUp(self):RQLGeneratorTC.setUp(self)indexer=get_indexer('mysql','utf8')dbms_helper=ADV_FUNC_HELPER_DIRECTORY['mysql']dbms_helper.fti_uid_attr=indexer.uid_attrdbms_helper.fti_table=indexer.tabledbms_helper.fti_restriction_sql=indexer.restriction_sqldbms_helper.fti_need_distinct_query=indexer.need_distinctself.o=SQLGenerator(schema,dbms_helper)def_norm_sql(self,sql):returnsql.strip().replace(' ILIKE ',' LIKE ')deftest_from_clause_needed(self):queries=[("Any 1 WHERE EXISTS(T is EGroup, T name 'managers')",'''SELECT 1FROM (SELECT 1) AS _TWHERE EXISTS(SELECT 1 FROM EGroup AS T WHERE T.name=managers)'''),('Any X,Y WHERE NOT X created_by Y, X eid 5, Y eid 6','''SELECT 5, 6FROM (SELECT 1) AS _TWHERE 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)'''),]fortinself._parse(queries):yieldtdeftest_has_text(self):queries=[('Any X WHERE X has_text "toto tata"',"""SELECT appears0.uidFROM appears AS appears0WHERE MATCH (appears0.words) AGAINST ('toto tata' IN BOOLEAN MODE)"""),('Personne X WHERE X has_text "toto tata"',"""SELECT X.eidFROM appears AS appears0, entities AS XWHERE MATCH (appears0.words) AGAINST ('toto tata' IN BOOLEAN MODE) AND appears0.uid=X.eid AND X.type='Personne'"""),('Personne X WHERE X has_text %(text)s',"""SELECT X.eidFROM appears AS appears0, entities AS XWHERE MATCH (appears0.words) AGAINST ('hip hop momo' IN BOOLEAN MODE) AND appears0.uid=X.eid AND X.type='Personne'"""),('Any X WHERE X has_text "toto tata", X name "tutu"',"""SELECT X.eidFROM Basket AS X, appears AS appears0WHERE MATCH (appears0.words) AGAINST ('toto tata' IN BOOLEAN MODE) AND appears0.uid=X.eid AND X.name=tutuUNION ALLSELECT X.eidFROM File AS X, appears AS appears0WHERE MATCH (appears0.words) AGAINST ('toto tata' IN BOOLEAN MODE) AND appears0.uid=X.eid AND X.name=tutuUNION ALLSELECT X.eidFROM Folder AS X, appears AS appears0WHERE MATCH (appears0.words) AGAINST ('toto tata' IN BOOLEAN MODE) AND appears0.uid=X.eid AND X.name=tutuUNION ALLSELECT X.eidFROM Image AS X, appears AS appears0WHERE MATCH (appears0.words) AGAINST ('toto tata' IN BOOLEAN MODE) AND appears0.uid=X.eid AND X.name=tutuUNION ALLSELECT X.eidFROM State AS X, appears AS appears0WHERE MATCH (appears0.words) AGAINST ('toto tata' IN BOOLEAN MODE) AND appears0.uid=X.eid AND X.name=tutuUNION ALLSELECT X.eidFROM Tag AS X, appears AS appears0WHERE MATCH (appears0.words) AGAINST ('toto tata' IN BOOLEAN MODE) AND appears0.uid=X.eid AND X.name=tutuUNION ALLSELECT X.eidFROM Transition AS X, appears AS appears0WHERE MATCH (appears0.words) AGAINST ('toto tata' IN BOOLEAN MODE) AND appears0.uid=X.eid AND X.name=tutu""")]fortinself._parse(queries):yieldtdeftest_ambigous_exists_no_from_clause(self):self._check('Any COUNT(U) WHERE U eid 1, EXISTS (P owned_by U, P is IN (Note, Affaire))','''SELECT COUNT(1)FROM (SELECT 1) AS _TWHERE EXISTS(SELECT 1 FROM owned_by_relation AS rel_owned_by0, Affaire AS P WHERE rel_owned_by0.eid_from=P.eid AND rel_owned_by0.eid_to=1 UNION SELECT 1 FROM owned_by_relation AS rel_owned_by1, Note AS P WHERE rel_owned_by1.eid_from=P.eid AND rel_owned_by1.eid_to=1)''')if__name__=='__main__':unittest_main()