1 """unit tests for module cubicweb.server.sources.rql2sql"""

     2 

     3 import sys

     4 from mx.DateTime import today

     5 

     6 from logilab.common.testlib import TestCase, unittest_main

     7 

     8 from rql import BadRQLQuery

     9 from indexer import get_indexer

    10 

    11 #from cubicweb.server.sources.native import remove_unused_solutions

    12 from cubicweb.server.sources.rql2sql import SQLGenerator

    13 

    14 from rql.utils import register_function, FunctionDescr

    15 # add a dumb registered procedure

    16 class stockproc(FunctionDescr):

    17     supported_backends = ('postgres', 'sqlite', 'mysql')

    18 try:

    19     register_function(stockproc)

    20 except AssertionError, ex:

    21     pass # already registered

    22 

    23 from cubicweb.devtools import TestServerConfiguration

    24 from cubicweb.devtools.repotest import RQLGeneratorTC

    25 

    26 config = TestServerConfiguration('data')

    27 config.bootstrap_cubes()

    28 schema = config.load_schema()

    29 schema['in_state'].inlined = True

    30 schema['comments'].inlined = False

    31 

    32 PARSER = [

    33     (r"Personne P WHERE P nom 'Zig\'oto';",

    34      '''SELECT P.eid

    35 FROM Personne AS P

    36 WHERE P.nom=Zig\'oto'''),

    37 

    38     (r'Personne P WHERE P nom ~= "Zig\"oto%";',

    39      '''SELECT P.eid

    40 FROM Personne AS P

    41 WHERE P.nom ILIKE Zig"oto%'''),

    42     ]

    43 

    44 BASIC = [

    45     

    46     ("Any X WHERE X is Affaire",

    47      '''SELECT X.eid

    48 FROM Affaire AS X'''),

    49     

    50     ("Any X WHERE X eid 0",

    51      '''SELECT 0'''),

    52     

    53     ("Personne P",

    54      '''SELECT P.eid

    55 FROM Personne AS P'''),

    56 

    57     ("Personne P WHERE P test TRUE",

    58      '''SELECT P.eid

    59 FROM Personne AS P

    60 WHERE P.test=True'''),

    61 

    62     ("Personne P WHERE P test false",

    63      '''SELECT P.eid

    64 FROM Personne AS P

    65 WHERE P.test=False'''),

    66 

    67     ("Personne P WHERE P eid -1",

    68      '''SELECT -1'''),

    69 

    70     ("Personne P LIMIT 20 OFFSET 10",

    71      '''SELECT P.eid

    72 FROM Personne AS P

    73 LIMIT 20

    74 OFFSET 10'''),

    75 

    76     ("Personne P WHERE S is Societe, P travaille S, S nom 'Logilab';",

    77      '''SELECT rel_travaille0.eid_from

    78 FROM Societe AS S, travaille_relation AS rel_travaille0

    79 WHERE rel_travaille0.eid_to=S.eid AND S.nom=Logilab'''),

    80 

    81     ("Personne P WHERE P concerne A, A concerne S, S nom 'Logilab', S is Societe;",

    82      '''SELECT rel_concerne0.eid_from

    83 FROM Societe AS S, concerne_relation AS rel_concerne0, concerne_relation AS rel_concerne1

    84 WHERE rel_concerne0.eid_to=rel_concerne1.eid_from AND rel_concerne1.eid_to=S.eid AND S.nom=Logilab'''),

    85 

    86     ("Note N WHERE X evaluee N, X nom 'Logilab';",

    87      '''SELECT rel_evaluee0.eid_to

    88 FROM Division AS X, evaluee_relation AS rel_evaluee0

    89 WHERE rel_evaluee0.eid_from=X.eid AND X.nom=Logilab

    90 UNION ALL

    91 SELECT rel_evaluee0.eid_to

    92 FROM Personne AS X, evaluee_relation AS rel_evaluee0

    93 WHERE rel_evaluee0.eid_from=X.eid AND X.nom=Logilab

    94 UNION ALL

    95 SELECT rel_evaluee0.eid_to

    96 FROM Societe AS X, evaluee_relation AS rel_evaluee0

    97 WHERE rel_evaluee0.eid_from=X.eid AND X.nom=Logilab

    98 UNION ALL

    99 SELECT rel_evaluee0.eid_to

   100 FROM SubDivision AS X, evaluee_relation AS rel_evaluee0

   101 WHERE rel_evaluee0.eid_from=X.eid AND X.nom=Logilab'''),

   102 

   103     ("Note N WHERE X evaluee N, X nom in ('Logilab', 'Caesium');",

   104      '''SELECT rel_evaluee0.eid_to

   105 FROM Division AS X, evaluee_relation AS rel_evaluee0

   106 WHERE rel_evaluee0.eid_from=X.eid AND X.nom IN(Logilab, Caesium)

   107 UNION ALL

   108 SELECT rel_evaluee0.eid_to

   109 FROM Personne AS X, evaluee_relation AS rel_evaluee0

   110 WHERE rel_evaluee0.eid_from=X.eid AND X.nom IN(Logilab, Caesium)

   111 UNION ALL

   112 SELECT rel_evaluee0.eid_to

   113 FROM Societe AS X, evaluee_relation AS rel_evaluee0

   114 WHERE rel_evaluee0.eid_from=X.eid AND X.nom IN(Logilab, Caesium)

   115 UNION ALL

   116 SELECT rel_evaluee0.eid_to

   117 FROM SubDivision AS X, evaluee_relation AS rel_evaluee0

   118 WHERE rel_evaluee0.eid_from=X.eid AND X.nom IN(Logilab, Caesium)'''),

   119 

   120     ("Any X WHERE X creation_date TODAY, X is Affaire",

   121      '''SELECT X.eid

   122 FROM Affaire AS X

   123 WHERE DATE(X.creation_date)=CURRENT_DATE'''),

   124 

   125     ("Any N WHERE G is EGroup, G name N, E eid 12, E read_permission G",

   126      '''SELECT G.name

   127 FROM EGroup AS G, read_permission_relation AS rel_read_permission0

   128 WHERE rel_read_permission0.eid_from=12 AND rel_read_permission0.eid_to=G.eid'''),

   129 

   130     ('Any Y WHERE U login "admin", U login Y', # stupid but valid...

   131      """SELECT U.login

   132 FROM EUser AS U

   133 WHERE U.login=admin"""),

   134 

   135     ('Any T WHERE T tags X, X is State',

   136      '''SELECT rel_tags0.eid_from

   137 FROM State AS X, tags_relation AS rel_tags0

   138 WHERE rel_tags0.eid_to=X.eid'''),

   139 

   140     ('Any X,Y WHERE X eid 0, Y eid 1, X concerne Y',

   141      '''SELECT 0, 1

   142 FROM concerne_relation AS rel_concerne0

   143 WHERE rel_concerne0.eid_from=0 AND rel_concerne0.eid_to=1'''),

   144 

   145     ("Any X WHERE X prenom 'lulu',"

   146      "EXISTS(X owned_by U, U in_group G, G name 'lulufanclub' OR G name 'managers');",

   147      '''SELECT X.eid

   148 FROM Personne AS X

   149 WHERE 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)))'''),

   150 

   151     ("Any X WHERE X prenom 'lulu',"

   152      "NOT EXISTS(X owned_by U, U in_group G, G name 'lulufanclub' OR G name 'managers');",

   153      '''SELECT X.eid

   154 FROM Personne AS X

   155 WHERE 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)))'''),

   156 ]

   157 

   158 ADVANCED= [

   159     ('Any X WHERE X is ET, ET eid 2',

   160      '''SELECT rel_is0.eid_from

   161 FROM is_relation AS rel_is0

   162 WHERE rel_is0.eid_to=2'''),

   163 

   164 

   165     ("Societe S WHERE S nom 'Logilab' OR S nom 'Caesium'",

   166      '''SELECT S.eid

   167 FROM Societe AS S

   168 WHERE ((S.nom=Logilab) OR (S.nom=Caesium))'''),

   169     

   170     ('Any X WHERE X nom "toto", X eid IN (9700, 9710, 1045, 674)',

   171     '''SELECT X.eid

   172 FROM Division AS X

   173 WHERE X.nom=toto AND X.eid IN(9700, 9710, 1045, 674)

   174 UNION ALL

   175 SELECT X.eid

   176 FROM Personne AS X

   177 WHERE X.nom=toto AND X.eid IN(9700, 9710, 1045, 674)

   178 UNION ALL

   179 SELECT X.eid

   180 FROM Societe AS X

   181 WHERE X.nom=toto AND X.eid IN(9700, 9710, 1045, 674)

   182 UNION ALL

   183 SELECT X.eid

   184 FROM SubDivision AS X

   185 WHERE X.nom=toto AND X.eid IN(9700, 9710, 1045, 674)'''),

   186 

   187     ('Any Y, COUNT(N) GROUPBY Y WHERE Y evaluee N;',

   188      '''SELECT rel_evaluee0.eid_from, COUNT(rel_evaluee0.eid_to)

   189 FROM evaluee_relation AS rel_evaluee0

   190 GROUP BY rel_evaluee0.eid_from'''),

   191 

   192     ("Any X WHERE X concerne B or C concerne X",

   193      '''SELECT X.eid

   194 FROM Affaire AS X, concerne_relation AS rel_concerne0, concerne_relation AS rel_concerne1

   195 WHERE ((rel_concerne0.eid_from=X.eid) OR (rel_concerne1.eid_to=X.eid))'''),

   196 

   197     ("Any X WHERE X travaille S or X concerne A",

   198      '''SELECT X.eid

   199 FROM Personne AS X, concerne_relation AS rel_concerne1, travaille_relation AS rel_travaille0

   200 WHERE ((rel_travaille0.eid_from=X.eid) OR (rel_concerne1.eid_from=X.eid))'''),

   201 

   202     ("Any N WHERE A evaluee N or N ecrit_par P",

   203      '''SELECT N.eid

   204 FROM Note AS N, evaluee_relation AS rel_evaluee0

   205 WHERE ((rel_evaluee0.eid_to=N.eid) OR (N.ecrit_par IS NOT NULL))'''),

   206 

   207     ("Any N WHERE A evaluee N or EXISTS(N todo_by U)",

   208      '''SELECT N.eid

   209 FROM Note AS N, evaluee_relation AS rel_evaluee0

   210 WHERE ((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)))'''),

   211 

   212     ("Any N WHERE A evaluee N or N todo_by U",

   213      '''SELECT N.eid

   214 FROM Note AS N, evaluee_relation AS rel_evaluee0, todo_by_relation AS rel_todo_by1

   215 WHERE ((rel_evaluee0.eid_to=N.eid) OR (rel_todo_by1.eid_from=N.eid))'''),

   216     

   217     ("Any X WHERE X concerne B or C concerne X, B eid 12, C eid 13",

   218      '''SELECT X.eid

   219 FROM Affaire AS X, concerne_relation AS rel_concerne0, concerne_relation AS rel_concerne1

   220 WHERE ((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))'''),

   221 

   222     ('Any X WHERE X created_by U, X concerne B OR C concerne X, B eid 12, C eid 13',

   223      '''SELECT rel_created_by0.eid_from

   224 FROM concerne_relation AS rel_concerne1, concerne_relation AS rel_concerne2, created_by_relation AS rel_created_by0

   225 WHERE ((rel_concerne1.eid_from=rel_created_by0.eid_from AND rel_concerne1.eid_to=12) OR (rel_concerne2.eid_from=13 AND rel_concerne2.eid_to=rel_created_by0.eid_from))'''),

   226 

   227     ('Any P WHERE P travaille_subdivision S1 OR P travaille_subdivision S2, S1 nom "logilab", S2 nom "caesium"',

   228      '''SELECT P.eid

   229 FROM Personne AS P, SubDivision AS S1, SubDivision AS S2, travaille_subdivision_relation AS rel_travaille_subdivision0, travaille_subdivision_relation AS rel_travaille_subdivision1

   230 WHERE ((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'''),

   231 

   232     ('Any X WHERE T tags X',

   233      '''SELECT rel_tags0.eid_to

   234 FROM tags_relation AS rel_tags0'''),

   235     

   236     ('Any X WHERE X in_basket B, B eid 12',

   237      '''SELECT rel_in_basket0.eid_from

   238 FROM in_basket_relation AS rel_in_basket0

   239 WHERE rel_in_basket0.eid_to=12'''),

   240     

   241     ('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',

   242      '''SELECT SE.name, R.name, OE.name

   243 FROM EEType AS OE, EEType AS SE, EFRDef AS X, ERType AS R

   244 WHERE 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

   245 UNION ALL

   246 SELECT SE.name, R.name, OE.name

   247 FROM EEType AS OE, EEType AS SE, ENFRDef AS X, ERType AS R

   248 WHERE 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'''),

   249 

   250     # 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

   251     ('Any O WHERE NOT S ecrit_par O, S eid 1, S inline1 P, O inline2 P',

   252      '''SELECT DISTINCT O.eid

   253 FROM Note AS S, Personne AS O

   254 WHERE (S.ecrit_par IS NULL OR S.ecrit_par!=O.eid) AND S.eid=1 AND O.inline2=S.inline1'''),

   255 

   256     ('DISTINCT Any S ORDERBY stockproc(SI) WHERE NOT S ecrit_par O, S para SI',

   257      '''SELECT T1.C0 FROM (SELECT DISTINCT S.eid AS C0, STOCKPROC(S.para) AS C1

   258 FROM Note AS S

   259 WHERE S.ecrit_par IS NULL

   260 ORDER BY 2) AS T1'''),

   261 

   262     ('Any N WHERE N todo_by U, N is Note, U eid 2, N filed_under T, T eid 3',

   263      # N would actually be invarient if U eid 2 had given a specific type to U

   264      '''SELECT N.eid

   265 FROM Note AS N, filed_under_relation AS rel_filed_under1, todo_by_relation AS rel_todo_by0

   266 WHERE 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'''),

   267 

   268     ('Any N WHERE N todo_by U, U eid 2, P evaluee N, P eid 3',

   269      '''SELECT rel_evaluee1.eid_to

   270 FROM evaluee_relation AS rel_evaluee1, todo_by_relation AS rel_todo_by0

   271 WHERE rel_evaluee1.eid_to=rel_todo_by0.eid_from AND rel_todo_by0.eid_to=2 AND rel_evaluee1.eid_from=3'''),

   272 

   273     

   274     (' Any X,U WHERE C owned_by U, NOT X owned_by U, C eid 1, X eid 2',

   275      '''SELECT 2, rel_owned_by0.eid_to

   276 FROM owned_by_relation AS rel_owned_by0

   277 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)'''),

   278 

   279     ('Any GN WHERE X in_group G, G name GN, (G name "managers" OR EXISTS(X copain T, T login in ("comme", "cochon")))',

   280      '''SELECT G.name

   281 FROM EGroup AS G, in_group_relation AS rel_in_group0

   282 WHERE 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))))'''),

   283 

   284     ('Any C WHERE C is Card, EXISTS(X documented_by C)',

   285       """SELECT C.eid

   286 FROM Card AS C

   287 WHERE EXISTS(SELECT 1 FROM documented_by_relation AS rel_documented_by0 WHERE rel_documented_by0.eid_to=C.eid)"""),

   288     

   289     ('Any C WHERE C is Card, EXISTS(X documented_by C, X eid 12)',

   290       """SELECT C.eid

   291 FROM Card AS C

   292 WHERE EXISTS(SELECT 1 FROM documented_by_relation AS rel_documented_by0 WHERE rel_documented_by0.eid_from=12 AND rel_documented_by0.eid_to=C.eid)"""),

   293 

   294     ('Any T WHERE C is Card, C title T, EXISTS(X documented_by C, X eid 12)',

   295       """SELECT C.title

   296 FROM Card AS C

   297 WHERE EXISTS(SELECT 1 FROM documented_by_relation AS rel_documented_by0 WHERE rel_documented_by0.eid_from=12 AND rel_documented_by0.eid_to=C.eid)"""),

   298 

   299     ('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"))',

   300      '''SELECT G.name, X.login

   301 FROM EGroup AS G, EUser AS X, in_group_relation AS rel_in_group0

   302 WHERE 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))'''),

   303 

   304     ('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',

   305      '''SELECT X.eid, 32, MAX(rel_tags0.eid_from)

   306 FROM EUser AS X, tags_relation AS rel_tags0

   307 WHERE rel_tags0.eid_to=X.eid AND X.in_state=32

   308 GROUP BY X.eid'''),

   309 

   310     ('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"))',

   311      '''SELECT COUNT(rel_concerne0.eid_from), C.nom

   312 FROM Societe AS C, concerne_relation AS rel_concerne0

   313 WHERE 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)))

   314 GROUP BY C.nom

   315 ORDER BY 1 DESC

   316 LIMIT 10'''),

   317 

   318     ('Any X WHERE Y evaluee X, Y is EUser',

   319      '''SELECT rel_evaluee0.eid_to

   320 FROM EUser AS Y, evaluee_relation AS rel_evaluee0

   321 WHERE rel_evaluee0.eid_from=Y.eid'''),

   322 

   323     ('Any L WHERE X login "admin", X identity Y, Y login L',

   324      '''SELECT Y.login

   325 FROM EUser AS X, EUser AS Y

   326 WHERE X.login=admin AND X.eid=Y.eid'''),

   327 

   328     ('Any L WHERE X login "admin", NOT X identity Y, Y login L',

   329      '''SELECT Y.login

   330 FROM EUser AS X, EUser AS Y

   331 WHERE X.login=admin AND NOT X.eid=Y.eid'''),

   332     

   333     ('Any L WHERE X login "admin", X identity Y?, Y login L',

   334      '''SELECT Y.login

   335 FROM EUser AS X LEFT OUTER JOIN EUser AS Y ON (X.eid=Y.eid)

   336 WHERE X.login=admin'''),

   337 

   338     ('Any XN ORDERBY XN WHERE X name XN',

   339      '''SELECT X.name

   340 FROM Basket AS X

   341 UNION ALL

   342 SELECT X.name

   343 FROM EConstraintType AS X

   344 UNION ALL

   345 SELECT X.name

   346 FROM EEType AS X

   347 UNION ALL

   348 SELECT X.name

   349 FROM EGroup AS X

   350 UNION ALL

   351 SELECT X.name

   352 FROM EPermission AS X

   353 UNION ALL

   354 SELECT X.name

   355 FROM ERType AS X

   356 UNION ALL

   357 SELECT X.name

   358 FROM File AS X

   359 UNION ALL

   360 SELECT X.name

   361 FROM Folder AS X

   362 UNION ALL

   363 SELECT X.name

   364 FROM Image AS X

   365 UNION ALL

   366 SELECT X.name

   367 FROM State AS X

   368 UNION ALL

   369 SELECT X.name

   370 FROM Tag AS X

   371 UNION ALL

   372 SELECT X.name

   373 FROM Transition AS X

   374 ORDER BY 1'''),

   375 

   376 #    ('Any XN WHERE X name XN GROUPBY XN',

   377 #     ''''''),

   378 #    ('Any XN, COUNT(X) WHERE X name XN GROUPBY XN',

   379 #     ''''''),

   380 

   381     # DISTINCT, can use relatin under exists scope as principal

   382     ('DISTINCT Any X,Y WHERE X name "EGroup", Y eid IN(1, 2, 3), EXISTS(X read_permission Y)',

   383      '''SELECT DISTINCT X.eid, rel_read_permission0.eid_to

   384 FROM EEType AS X, read_permission_relation AS rel_read_permission0

   385 WHERE 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)

   386 UNION

   387 SELECT DISTINCT X.eid, rel_read_permission0.eid_to

   388 FROM ERType AS X, read_permission_relation AS rel_read_permission0

   389 WHERE 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)'''),

   390 

   391     # no distinct, Y can't be invariant

   392     ('Any X,Y WHERE X name "EGroup", Y eid IN(1, 2, 3), EXISTS(X read_permission Y)',

   393      '''SELECT X.eid, Y.eid

   394 FROM EEType AS X, EGroup AS Y

   395 WHERE 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)

   396 UNION ALL

   397 SELECT X.eid, Y.eid

   398 FROM EEType AS X, RQLExpression AS Y

   399 WHERE 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)

   400 UNION ALL

   401 SELECT X.eid, Y.eid

   402 FROM EGroup AS Y, ERType AS X

   403 WHERE 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)

   404 UNION ALL

   405 SELECT X.eid, Y.eid

   406 FROM ERType AS X, RQLExpression AS Y

   407 WHERE 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)'''),

   408 

   409     # DISTINCT but NEGED exists, can't be invariant

   410     ('DISTINCT Any X,Y WHERE X name "EGroup", Y eid IN(1, 2, 3), NOT EXISTS(X read_permission Y)',

   411      '''SELECT DISTINCT X.eid, Y.eid

   412 FROM EEType AS X, EGroup AS Y

   413 WHERE 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)

   414 UNION

   415 SELECT DISTINCT X.eid, Y.eid

   416 FROM EEType AS X, RQLExpression AS Y

   417 WHERE 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)

   418 UNION

   419 SELECT DISTINCT X.eid, Y.eid

   420 FROM EGroup AS Y, ERType AS X

   421 WHERE 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)

   422 UNION

   423 SELECT DISTINCT X.eid, Y.eid

   424 FROM ERType AS X, RQLExpression AS Y

   425 WHERE 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)'''),

   426 

   427     # should generate the same query as above

   428     ('DISTINCT Any X,Y WHERE X name "EGroup", Y eid IN(1, 2, 3), NOT X read_permission Y',

   429      '''SELECT DISTINCT X.eid, Y.eid

   430 FROM EEType AS X, EGroup AS Y

   431 WHERE 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)

   432 UNION

   433 SELECT DISTINCT X.eid, Y.eid

   434 FROM EEType AS X, RQLExpression AS Y

   435 WHERE 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)

   436 UNION

   437 SELECT DISTINCT X.eid, Y.eid

   438 FROM EGroup AS Y, ERType AS X

   439 WHERE 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)

   440 UNION

   441 SELECT DISTINCT X.eid, Y.eid

   442 FROM ERType AS X, RQLExpression AS Y

   443 WHERE 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)'''),

   444     

   445     # neged relation, can't be inveriant

   446     ('Any X,Y WHERE X name "EGroup", Y eid IN(1, 2, 3), NOT X read_permission Y',

   447      '''SELECT X.eid, Y.eid

   448 FROM EEType AS X, EGroup AS Y

   449 WHERE 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)

   450 UNION ALL

   451 SELECT X.eid, Y.eid

   452 FROM EEType AS X, RQLExpression AS Y

   453 WHERE 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)

   454 UNION ALL

   455 SELECT X.eid, Y.eid

   456 FROM EGroup AS Y, ERType AS X

   457 WHERE 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)

   458 UNION ALL

   459 SELECT X.eid, Y.eid

   460 FROM ERType AS X, RQLExpression AS Y

   461 WHERE 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)'''),

   462 

   463     ('Any MAX(X)+MIN(X), N GROUPBY N WHERE X name N;',

   464      '''SELECT (MAX(T1.C0) + MIN(T1.C0)), T1.C1 FROM (SELECT X.eid AS C0, X.name AS C1

   465 FROM Basket AS X

   466 UNION ALL

   467 SELECT X.eid AS C0, X.name AS C1

   468 FROM EConstraintType AS X

   469 UNION ALL

   470 SELECT X.eid AS C0, X.name AS C1

   471 FROM EEType AS X

   472 UNION ALL

   473 SELECT X.eid AS C0, X.name AS C1

   474 FROM EGroup AS X

   475 UNION ALL

   476 SELECT X.eid AS C0, X.name AS C1

   477 FROM EPermission AS X

   478 UNION ALL

   479 SELECT X.eid AS C0, X.name AS C1

   480 FROM ERType AS X

   481 UNION ALL

   482 SELECT X.eid AS C0, X.name AS C1

   483 FROM File AS X

   484 UNION ALL

   485 SELECT X.eid AS C0, X.name AS C1

   486 FROM Folder AS X

   487 UNION ALL

   488 SELECT X.eid AS C0, X.name AS C1

   489 FROM Image AS X

   490 UNION ALL

   491 SELECT X.eid AS C0, X.name AS C1

   492 FROM State AS X

   493 UNION ALL

   494 SELECT X.eid AS C0, X.name AS C1

   495 FROM Tag AS X

   496 UNION ALL

   497 SELECT X.eid AS C0, X.name AS C1

   498 FROM Transition AS X) AS T1

   499 GROUP BY T1.C1'''),

   500     

   501     ('Any MAX(X)+MIN(LENGTH(D)), N GROUPBY N ORDERBY 1, N, DF WHERE X name N, X data D, X data_format DF;',

   502      '''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 C3

   503 FROM File AS X

   504 UNION ALL

   505 SELECT X.data AS C0, X.eid AS C1, X.name AS C2, X.data_format AS C3

   506 FROM Image AS X) AS T1

   507 GROUP BY T1.C2

   508 ORDER BY 1,2,T1.C3'''),

   509 

   510     ('DISTINCT Any S ORDERBY R WHERE A is Affaire, A sujet S, A ref R',

   511      '''SELECT T1.C0 FROM (SELECT DISTINCT A.sujet AS C0, A.ref AS C1

   512 FROM Affaire AS A

   513 ORDER BY 2) AS T1'''),

   514     

   515     ('DISTINCT Any MAX(X)+MIN(LENGTH(D)), N GROUPBY N ORDERBY 2, DF WHERE X name N, X data D, X data_format DF;',

   516      '''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 C3

   517 FROM File AS X

   518 UNION

   519 SELECT DISTINCT X.data AS C0, X.eid AS C1, X.name AS C2, X.data_format AS C3

   520 FROM Image AS X) AS T1

   521 GROUP BY T1.C2,T1.C3

   522 ORDER BY 2,3) AS T1

   523 '''),

   524 

   525     # ambiguity in EXISTS() -> should union the sub-query

   526     ('Any T WHERE T is Tag, NOT T name in ("t1", "t2"), EXISTS(T tags X, X is IN (EUser, EGroup))',

   527      '''SELECT T.eid

   528 FROM Tag AS T

   529 WHERE 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)'''),

   530 

   531     # must not use a relation in EXISTS scope to inline a variable 

   532     ('Any U WHERE U eid IN (1,2), EXISTS(X owned_by U)',

   533      '''SELECT U.eid

   534 FROM EUser AS U

   535 WHERE 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)'''),

   536 

   537     ('Any U WHERE EXISTS(U eid IN (1,2), X owned_by U)',

   538      '''SELECT U.eid

   539 FROM EUser AS U

   540 WHERE 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)'''),

   541 

   542     ('Any COUNT(U) WHERE EXISTS (P owned_by U, P is IN (Note, Affaire))',

   543      '''SELECT COUNT(U.eid)

   544 FROM EUser AS U

   545 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=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)'''),

   546 

   547     ('Any MAX(X)',

   548      '''SELECT MAX(X.eid)

   549 FROM entities AS X'''),

   550 

   551     ('Any MAX(X) WHERE X is Note',

   552      '''SELECT MAX(X.eid)

   553 FROM Note AS X'''),

   554     

   555     ('Any X WHERE X eid > 12',

   556      '''SELECT X.eid

   557 FROM entities AS X

   558 WHERE X.eid>12'''),

   559     

   560     ('Any X WHERE X eid > 12, X is Note',

   561      """SELECT X.eid

   562 FROM entities AS X

   563 WHERE X.type='Note' AND X.eid>12"""),

   564     

   565     ('Any X, T WHERE X eid > 12, X title T',

   566      """SELECT X.eid, X.title

   567 FROM Bookmark AS X

   568 WHERE X.eid>12

   569 UNION ALL

   570 SELECT X.eid, X.title

   571 FROM Card AS X

   572 WHERE X.eid>12

   573 UNION ALL

   574 SELECT X.eid, X.title

   575 FROM EmailThread AS X

   576 WHERE X.eid>12"""),

   577 

   578     ('Any X',

   579      '''SELECT X.eid

   580 FROM entities AS X'''),

   581 

   582     ('Any X GROUPBY X WHERE X eid 12',

   583      '''SELECT 12'''),

   584     

   585     ('Any X GROUPBY X ORDERBY Y WHERE X eid 12, X login Y',

   586      '''SELECT X.eid

   587 FROM EUser AS X

   588 WHERE X.eid=12

   589 GROUP BY X.eid

   590 ORDER BY X.login'''),

   591     

   592     ('Any U,COUNT(X) GROUPBY U WHERE U eid 12, X owned_by U HAVING COUNT(X) > 10',

   593      '''SELECT rel_owned_by0.eid_to, COUNT(rel_owned_by0.eid_from)

   594 FROM owned_by_relation AS rel_owned_by0

   595 WHERE rel_owned_by0.eid_to=12

   596 GROUP BY rel_owned_by0.eid_to

   597 HAVING COUNT(rel_owned_by0.eid_from)>10'''),

   598 

   599     ('DISTINCT Any X ORDERBY stockproc(X) WHERE U login X',

   600      '''SELECT T1.C0 FROM (SELECT DISTINCT U.login AS C0, STOCKPROC(U.login) AS C1

   601 FROM EUser AS U

   602 ORDER BY 2) AS T1'''),

   603     

   604     ('DISTINCT Any X ORDERBY Y WHERE B bookmarked_by X, X login Y',

   605      '''SELECT T1.C0 FROM (SELECT DISTINCT X.eid AS C0, X.login AS C1

   606 FROM EUser AS X, bookmarked_by_relation AS rel_bookmarked_by0

   607 WHERE rel_bookmarked_by0.eid_to=X.eid

   608 ORDER BY 2) AS T1'''),

   609 

   610     ('DISTINCT Any X ORDERBY SN WHERE X in_state S, S name SN',

   611      '''SELECT T1.C0 FROM (SELECT DISTINCT X.eid AS C0, S.name AS C1

   612 FROM Affaire AS X, State AS S

   613 WHERE X.in_state=S.eid

   614 UNION

   615 SELECT DISTINCT X.eid AS C0, S.name AS C1

   616 FROM EUser AS X, State AS S

   617 WHERE X.in_state=S.eid

   618 UNION

   619 SELECT DISTINCT X.eid AS C0, S.name AS C1

   620 FROM Note AS X, State AS S

   621 WHERE X.in_state=S.eid

   622 ORDER BY 2) AS T1'''),

   623 

   624     ]

   625 

   626 MULTIPLE_SEL = [

   627     ("DISTINCT Any X,Y where P is Personne, P nom X , P prenom Y;",

   628      '''SELECT DISTINCT P.nom, P.prenom

   629 FROM Personne AS P'''),

   630     ("Any X,Y where P is Personne, P nom X , P prenom Y, not P nom NULL;",

   631      '''SELECT P.nom, P.prenom

   632 FROM Personne AS P

   633 WHERE NOT (P.nom IS NULL)'''),

   634     ("Personne X,Y where X nom NX, Y nom NX, X eid XE, not Y eid XE",

   635      '''SELECT X.eid, Y.eid

   636 FROM Personne AS X, Personne AS Y

   637 WHERE Y.nom=X.nom AND NOT (Y.eid=X.eid)''')

   638     ]

   639 

   640 NEGATIONS = [

   641     ("Personne X WHERE NOT X evaluee Y;",

   642      '''SELECT X.eid

   643 FROM Personne AS X

   644 WHERE NOT EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=X.eid)'''),

   645     

   646     ("Note N WHERE NOT X evaluee N, X eid 0",

   647      '''SELECT N.eid

   648 FROM Note AS N

   649 WHERE NOT EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=0 AND rel_evaluee0.eid_to=N.eid)'''),

   650     

   651     ('Any X WHERE NOT X travaille S, X is Personne',

   652      '''SELECT X.eid

   653 FROM Personne AS X

   654 WHERE NOT EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_from=X.eid)'''),

   655     

   656     ("Personne P where not P datenaiss TODAY",

   657      '''SELECT P.eid

   658 FROM Personne AS P

   659 WHERE NOT (DATE(P.datenaiss)=CURRENT_DATE)'''),

   660     

   661     ("Personne P where NOT P concerne A",

   662      '''SELECT P.eid

   663 FROM Personne AS P

   664 WHERE NOT EXISTS(SELECT 1 FROM concerne_relation AS rel_concerne0 WHERE rel_concerne0.eid_from=P.eid)'''),

   665     

   666     ("Affaire A where not P concerne A",

   667      '''SELECT A.eid

   668 FROM Affaire AS A

   669 WHERE NOT EXISTS(SELECT 1 FROM concerne_relation AS rel_concerne0 WHERE rel_concerne0.eid_to=A.eid)'''),

   670     ("Personne P where not P concerne A, A sujet ~= 'TEST%'",

   671      '''SELECT P.eid

   672 FROM Affaire AS A, Personne AS P

   673 WHERE 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%'''),

   674 

   675     ('Any S WHERE NOT T eid 28258, T tags S',

   676      '''SELECT rel_tags0.eid_to

   677 FROM tags_relation AS rel_tags0

   678 WHERE NOT (rel_tags0.eid_from=28258)'''),

   679     

   680     ('Any S WHERE T is Tag, T name TN, NOT T eid 28258, T tags S, S name SN',

   681      '''SELECT S.eid

   682 FROM EGroup AS S, Tag AS T, tags_relation AS rel_tags0

   683 WHERE NOT (T.eid=28258) AND rel_tags0.eid_from=T.eid AND rel_tags0.eid_to=S.eid

   684 UNION ALL

   685 SELECT S.eid

   686 FROM State AS S, Tag AS T, tags_relation AS rel_tags0

   687 WHERE NOT (T.eid=28258) AND rel_tags0.eid_from=T.eid AND rel_tags0.eid_to=S.eid

   688 UNION ALL

   689 SELECT S.eid

   690 FROM Tag AS S, Tag AS T, tags_relation AS rel_tags0

   691 WHERE NOT (T.eid=28258) AND rel_tags0.eid_from=T.eid AND rel_tags0.eid_to=S.eid'''),

   692 

   693     

   694     ('Any X,Y WHERE X created_by Y, X eid 5, NOT Y eid 6',

   695      '''SELECT 5, rel_created_by0.eid_to

   696 FROM created_by_relation AS rel_created_by0

   697 WHERE rel_created_by0.eid_from=5 AND NOT (rel_created_by0.eid_to=6)'''),

   698 

   699     ('Note X WHERE NOT Y evaluee X',

   700      '''SELECT X.eid

   701 FROM Note AS X

   702 WHERE NOT EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_to=X.eid)'''),

   703 

   704     ('Any Y WHERE NOT Y evaluee X',

   705      '''SELECT Y.eid

   706 FROM Division AS Y

   707 WHERE NOT EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=Y.eid)

   708 UNION ALL

   709 SELECT Y.eid

   710 FROM EUser AS Y

   711 WHERE NOT EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=Y.eid)

   712 UNION ALL

   713 SELECT Y.eid

   714 FROM Personne AS Y

   715 WHERE NOT EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=Y.eid)

   716 UNION ALL

   717 SELECT Y.eid

   718 FROM Societe AS Y

   719 WHERE NOT EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=Y.eid)

   720 UNION ALL

   721 SELECT Y.eid

   722 FROM SubDivision AS Y

   723 WHERE NOT EXISTS(SELECT 1 FROM evaluee_relation AS rel_evaluee0 WHERE rel_evaluee0.eid_from=Y.eid)'''),

   724 

   725     ('Any X WHERE NOT Y evaluee X, Y is EUser',

   726      '''SELECT X.eid

   727 FROM Note AS X

   728 WHERE 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)'''),

   729     

   730     ('Any X,T WHERE X title T, NOT X is Bookmark',

   731      '''SELECT DISTINCT X.eid, X.title

   732 FROM Card AS X

   733 UNION

   734 SELECT DISTINCT X.eid, X.title

   735 FROM EmailThread AS X'''),

   736 

   737     ('Any K,V WHERE P is EProperty, P pkey K, P value V, NOT P for_user U',

   738      '''SELECT DISTINCT P.pkey, P.value

   739 FROM EProperty AS P

   740 WHERE P.for_user IS NULL'''),

   741 

   742     ]

   743 

   744 OUTER_JOIN = [

   745     ('Any X,S WHERE X travaille S?',

   746      '''SELECT X.eid, rel_travaille0.eid_to

   747 FROM Personne AS X LEFT OUTER JOIN travaille_relation AS rel_travaille0 ON (rel_travaille0.eid_from=X.eid)'''

   748 #SELECT X.eid, S.eid

   749 #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)'''

   750     ),

   751     ('Any S,X WHERE X? travaille S, S is Societe',

   752      '''SELECT S.eid, rel_travaille0.eid_from

   753 FROM Societe AS S LEFT OUTER JOIN travaille_relation AS rel_travaille0 ON (rel_travaille0.eid_to=S.eid)'''

   754 #SELECT S.eid, X.eid

   755 #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)'''

   756     ),

   757 

   758     ('Any N,A WHERE N inline1 A?',

   759      '''SELECT N.eid, N.inline1

   760 FROM Note AS N'''),

   761 

   762     ('Any SN WHERE X from_state S?, S name SN',

   763      '''SELECT S.name

   764 FROM TrInfo AS X LEFT OUTER JOIN State AS S ON (X.from_state=S.eid)'''

   765     ),

   766 

   767     ('Any A,N WHERE N? inline1 A',

   768      '''SELECT A.eid, N.eid

   769 FROM Affaire AS A LEFT OUTER JOIN Note AS N ON (N.inline1=A.eid)'''

   770     ),

   771 

   772     ('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?',

   773     '''SELECT A.eid, A.creation_date, A.modification_date, A.comment, A.from_state, A.to_state, A.wf_info_for

   774 FROM TrInfo AS A

   775 WHERE A.eid=12'''),

   776 

   777     ('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',

   778      '''SELECT WF.from_state, WF.to_state, WF.comment, WF.creation_date, rel_owned_by0.eid_to

   779 FROM TrInfo AS WF, owned_by_relation AS rel_owned_by0

   780 WHERE WF.wf_info_for=1 AND WF.to_state IS NOT NULL AND rel_owned_by0.eid_from=WF.eid

   781 ORDER BY 4 DESC'''),

   782 

   783     ('Any X WHERE X is Affaire, S is Societe, EXISTS(X owned_by U OR (X concerne S?, S owned_by U))',

   784      '''SELECT X.eid

   785 FROM Affaire AS X

   786 WHERE 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)'''),

   787 

   788     ('Any C,M WHERE C travaille G?, G evaluee M?, G is Societe',

   789      '''SELECT C.eid, rel_evaluee1.eid_to

   790 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)'''

   791 #SELECT C.eid, M.eid

   792 #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)'''

   793      ),

   794 

   795     ('Any A,C WHERE A documented_by C?, (C is NULL) OR (EXISTS(C require_permission F, '

   796      'F name "read", F require_group E, U in_group E)), U eid 1',

   797      '''SELECT A.eid, rel_documented_by0.eid_to

   798 FROM Affaire AS A LEFT OUTER JOIN documented_by_relation AS rel_documented_by0 ON (rel_documented_by0.eid_from=A.eid)

   799 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)))'''),

   800 

   801     ("Any X WHERE X eid 12, P? connait X",

   802      '''SELECT X.eid

   803 FROM Personne AS X LEFT OUTER JOIN connait_relation AS rel_connait0 ON (rel_connait0.eid_to=12)

   804 WHERE X.eid=12'''

   805 #SELECT 12

   806 #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)

   807 #WHERE X.eid=12'''

   808     ),

   809 

   810     ('Any GN, TN ORDERBY GN WHERE T tags G?, T name TN, G name GN',

   811     '''SELECT _T0.C1, T.name

   812 FROM 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 C1

   813 FROM EGroup AS G

   814 UNION ALL

   815 SELECT G.eid AS C0, G.name AS C1

   816 FROM State AS G

   817 UNION ALL

   818 SELECT G.eid AS C0, G.name AS C1

   819 FROM Tag AS G) AS _T0 ON (rel_tags0.eid_to=_T0.C0)

   820 ORDER BY 1'''),

   821 

   822 

   823     # optional variable with additional restriction

   824     ('Any T,G WHERE T tags G?, G name "hop", G is EGroup',

   825      '''SELECT T.eid, G.eid

   826 FROM 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)'''),

   827 

   828     # optional variable with additional invariant restriction

   829     ('Any T,G WHERE T tags G?, G eid 12',

   830      '''SELECT T.eid, rel_tags0.eid_to

   831 FROM Tag AS T LEFT OUTER JOIN tags_relation AS rel_tags0 ON (rel_tags0.eid_from=T.eid AND rel_tags0.eid_to=12)'''),

   832 

   833     # optional variable with additional restriction appearing before the relation

   834     ('Any T,G WHERE G name "hop", T tags G?, G is EGroup',

   835      '''SELECT T.eid, G.eid

   836 FROM 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)'''),

   837 

   838     # optional variable with additional restriction on inlined relation

   839     # XXX the expected result should be as the query below. So what, raise BadRQLQuery ?

   840     ('Any T,G,S WHERE T tags G?, G in_state S, S name "hop", G is EUser',

   841      '''SELECT T.eid, G.eid, S.eid

   842 FROM 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)

   843 WHERE G.in_state=S.eid AND S.name=hop

   844 '''),

   845 

   846     # optional variable with additional invariant restriction on an inlined relation

   847     ('Any T,G,S WHERE T tags G, G in_state S?, S eid 1, G is EUser',

   848      '''SELECT rel_tags0.eid_from, G.eid, G.in_state

   849 FROM EUser AS G, tags_relation AS rel_tags0

   850 WHERE rel_tags0.eid_to=G.eid AND (G.in_state=1 OR G.in_state IS NULL)'''),

   851 

   852     # two optional variables with additional invariant restriction on an inlined relation

   853     ('Any T,G,S WHERE T tags G?, G in_state S?, S eid 1, G is EUser',

   854      '''SELECT T.eid, G.eid, G.in_state

   855 FROM 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))'''),

   856 

   857     # two optional variables with additional restriction on an inlined relation

   858     ('Any T,G,S WHERE T tags G?, G in_state S?, S name "hop", G is EUser',

   859      '''SELECT T.eid, G.eid, S.eid

   860 FROM 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)'''),

   861     

   862     # two optional variables with additional restriction on an ambigous inlined relation

   863     ('Any T,G,S WHERE T tags G?, G in_state S?, S name "hop"',

   864      '''SELECT T.eid, _T0.C0, _T0.C1

   865 FROM 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 C1

   866 FROM Affaire AS G LEFT OUTER JOIN State AS S ON (G.in_state=S.eid AND S.name=hop) 

   867 UNION ALL

   868 SELECT G.eid AS C0, S.eid AS C1

   869 FROM EUser AS G LEFT OUTER JOIN State AS S ON (G.in_state=S.eid AND S.name=hop) 

   870 UNION ALL

   871 SELECT G.eid AS C0, S.eid AS C1

   872 FROM 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)'''),

   873 

   874     ]

   875 

   876 VIRTUAL_VARS = [

   877     ("Personne P WHERE P travaille S, S tel T, S fax T, S is Societe;",

   878      '''SELECT rel_travaille0.eid_from

   879 FROM Societe AS S, travaille_relation AS rel_travaille0

   880 WHERE rel_travaille0.eid_to=S.eid AND S.fax=S.tel'''),

   881     

   882     ("Personne P where X eid 0, X creation_date D, P datenaiss < D, X is Affaire",

   883      '''SELECT P.eid

   884 FROM Affaire AS X, Personne AS P

   885 WHERE X.eid=0 AND P.datenaiss<X.creation_date'''),

   886 

   887     ("Any N,T WHERE N is Note, N type T;",

   888      '''SELECT N.eid, N.type

   889 FROM Note AS N'''),

   890 

   891     ("Personne P where X is Personne, X tel T, X fax F, P fax T+F",

   892      '''SELECT P.eid

   893 FROM Personne AS P, Personne AS X

   894 WHERE P.fax=(X.tel + X.fax)'''),

   895 

   896     ("Personne P where X tel T, X fax F, P fax IN (T,F)",

   897      '''SELECT P.eid

   898 FROM Division AS X, Personne AS P

   899 WHERE P.fax IN(X.tel, X.fax)

   900 UNION ALL

   901 SELECT P.eid

   902 FROM Personne AS P, Personne AS X

   903 WHERE P.fax IN(X.tel, X.fax)

   904 UNION ALL

   905 SELECT P.eid

   906 FROM Personne AS P, Societe AS X

   907 WHERE P.fax IN(X.tel, X.fax)

   908 UNION ALL

   909 SELECT P.eid

   910 FROM Personne AS P, SubDivision AS X

   911 WHERE P.fax IN(X.tel, X.fax)'''),

   912 

   913     ("Personne P where X tel T, X fax F, P fax IN (T,F,0832542332)",

   914      '''SELECT P.eid

   915 FROM Division AS X, Personne AS P

   916 WHERE P.fax IN(X.tel, X.fax, 832542332)

   917 UNION ALL

   918 SELECT P.eid

   919 FROM Personne AS P, Personne AS X

   920 WHERE P.fax IN(X.tel, X.fax, 832542332)

   921 UNION ALL

   922 SELECT P.eid

   923 FROM Personne AS P, Societe AS X

   924 WHERE P.fax IN(X.tel, X.fax, 832542332)

   925 UNION ALL

   926 SELECT P.eid

   927 FROM Personne AS P, SubDivision AS X

   928 WHERE P.fax IN(X.tel, X.fax, 832542332)'''),

   929     ]

   930 

   931 FUNCS = [

   932     ("Any COUNT(P) WHERE P is Personne",

   933      '''SELECT COUNT(P.eid)

   934 FROM Personne AS P'''),

   935 ##     ("Personne X where X nom upper('TOTO')",

   936 ##      '''SELECT X.eid\nFROM Personne AS X\nWHERE UPPER(X.nom) = TOTO'''),

   937 ##     ("Personne X where X nom Y, UPPER(X) prenom upper(Y)",

   938 ##      '''SELECT X.eid\nFROM Personne AS X\nWHERE UPPER(X.prenom) = UPPER(X.nom)'''),

   939     ]

   940 

   941 SYMETRIC = [

   942     ('Any P WHERE X eid 0, X connait P',

   943      '''SELECT DISTINCT P.eid

   944 FROM Personne AS P, connait_relation AS rel_connait0

   945 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)'''

   946 #      '''SELECT rel_connait0.eid_to

   947 # FROM connait_relation AS rel_connait0

   948 # WHERE rel_connait0.eid_from=0

   949 # UNION

   950 # SELECT rel_connait0.eid_from

   951 # FROM connait_relation AS rel_connait0

   952 # WHERE rel_connait0.eid_to=0'''

   953      ),

   954     

   955     ('Any P WHERE X connait P',

   956     '''SELECT DISTINCT P.eid

   957 FROM Personne AS P, connait_relation AS rel_connait0

   958 WHERE (rel_connait0.eid_to=P.eid OR rel_connait0.eid_from=P.eid)'''

   959     ),

   960     

   961     ('Any X WHERE X connait P',

   962     '''SELECT DISTINCT X.eid

   963 FROM Personne AS X, connait_relation AS rel_connait0

   964 WHERE (rel_connait0.eid_from=X.eid OR rel_connait0.eid_to=X.eid)'''

   965      ),

   966     

   967     ('Any P WHERE X eid 0, NOT X connait P',

   968      '''SELECT P.eid

   969 FROM Personne AS P

   970 WHERE 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))'''),

   971     

   972     ('Any P WHERE NOT X connait P',

   973     '''SELECT P.eid

   974 FROM Personne AS P

   975 WHERE NOT EXISTS(SELECT 1 FROM connait_relation AS rel_connait0 WHERE (rel_connait0.eid_to=P.eid OR rel_connait0.eid_from=P.eid))'''),

   976     

   977     ('Any X WHERE NOT X connait P',

   978     '''SELECT X.eid

   979 FROM Personne AS X

   980 WHERE NOT EXISTS(SELECT 1 FROM connait_relation AS rel_connait0 WHERE (rel_connait0.eid_from=X.eid OR rel_connait0.eid_to=X.eid))'''),

   981 

   982     ('Any P WHERE X connait P, P nom "nom"',

   983      '''SELECT DISTINCT P.eid

   984 FROM Personne AS P, connait_relation AS rel_connait0

   985 WHERE (rel_connait0.eid_to=P.eid OR rel_connait0.eid_from=P.eid) AND P.nom=nom'''),

   986     

   987     ('Any X WHERE X connait P, P nom "nom"',

   988      '''SELECT DISTINCT X.eid

   989 FROM Personne AS P, Personne AS X, connait_relation AS rel_connait0

   990 WHERE (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'''

   991     ),

   992 

   993     ('Any X ORDERBY X DESC LIMIT 9 WHERE E eid 0, E connait X',

   994     '''SELECT DISTINCT X.eid

   995 FROM Personne AS X, connait_relation AS rel_connait0

   996 WHERE (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)

   997 ORDER BY 1 DESC

   998 LIMIT 9'''

   999      ),

  1000 

  1001     ('DISTINCT Any P WHERE P connait S OR S connait P, S nom "chouette"',

  1002      '''SELECT DISTINCT P.eid

  1003 FROM Personne AS P, Personne AS S, connait_relation AS rel_connait0

  1004 WHERE (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'''

  1005      )

  1006     ]

  1007 

  1008 INLINE = [

  1009     ('Any P, L WHERE N ecrit_par P, P nom L, N eid 0',

  1010      '''SELECT P.eid, P.nom

  1011 FROM Note AS N, Personne AS P

  1012 WHERE N.ecrit_par=P.eid AND N.eid=0'''),

  1013     

  1014     ('Any N WHERE NOT N ecrit_par P, P nom "toto"',

  1015      '''SELECT DISTINCT N.eid

  1016 FROM Note AS N, Personne AS P

  1017 WHERE (N.ecrit_par IS NULL OR N.ecrit_par!=P.eid) AND P.nom=toto'''),

  1018     

  1019     ('Any P WHERE N ecrit_par P, N eid 0',

  1020     '''SELECT N.ecrit_par

  1021 FROM Note AS N

  1022 WHERE N.ecrit_par IS NOT NULL AND N.eid=0'''),

  1023 

  1024     ('Any P WHERE N ecrit_par P, P is Personne, N eid 0',

  1025     '''SELECT P.eid

  1026 FROM Note AS N, Personne AS P

  1027 WHERE N.ecrit_par=P.eid AND N.eid=0'''),

  1028 

  1029     ('Any P WHERE NOT N ecrit_par P, P is Personne, N eid 512',

  1030      '''SELECT DISTINCT P.eid

  1031 FROM Note AS N, Personne AS P

  1032 WHERE (N.ecrit_par IS NULL OR N.ecrit_par!=P.eid) AND N.eid=512'''),

  1033 

  1034     ('Any S,ES,T WHERE S state_of ET, ET name "EUser", ES allowed_transition T, T destination_state S',

  1035      '''SELECT T.destination_state, rel_allowed_transition1.eid_from, T.eid

  1036 FROM EEType AS ET, Transition AS T, allowed_transition_relation AS rel_allowed_transition1, state_of_relation AS rel_state_of0

  1037 WHERE 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'''),

  1038     ('Any O WHERE S eid 0, S in_state O',

  1039      '''SELECT S.in_state

  1040 FROM Affaire AS S

  1041 WHERE S.eid=0 AND S.in_state IS NOT NULL

  1042 UNION ALL

  1043 SELECT S.in_state

  1044 FROM EUser AS S

  1045 WHERE S.eid=0 AND S.in_state IS NOT NULL

  1046 UNION ALL

  1047 SELECT S.in_state

  1048 FROM Note AS S

  1049 WHERE S.eid=0 AND S.in_state IS NOT NULL''')

  1050     

  1051     ]

  1052 

  1053 

  1054 from logilab.common.adbh import ADV_FUNC_HELPER_DIRECTORY

  1055     

  1056 class PostgresSQLGeneratorTC(RQLGeneratorTC):

  1057     schema = schema

  1058     

  1059     #capture = True

  1060     def setUp(self):

  1061         RQLGeneratorTC.setUp(self)

  1062         indexer = get_indexer('postgres', 'utf8')        

  1063         dbms_helper = ADV_FUNC_HELPER_DIRECTORY['postgres']

  1064         dbms_helper.fti_uid_attr = indexer.uid_attr

  1065         dbms_helper.fti_table = indexer.table

  1066         dbms_helper.fti_restriction_sql = indexer.restriction_sql

  1067         dbms_helper.fti_need_distinct_query = indexer.need_distinct

  1068         self.o = SQLGenerator(schema, dbms_helper)

  1069 

  1070     def _norm_sql(self, sql):

  1071         return sql.strip()

  1072     

  1073     def _check(self, rql, sql, varmap=None):

  1074         try:

  1075             union = self._prepare(rql)

  1076             r, args = self.o.generate(union, {'text': 'hip hop momo'},

  1077                                       varmap=varmap)

  1078             self.assertLinesEquals((r % args).strip(), self._norm_sql(sql))

  1079         except Exception, ex:

  1080             print rql

  1081             if 'r' in locals():

  1082                 print (r%args).strip()

  1083                 print '!='

  1084                 print sql.strip()

  1085             raise

  1086     

  1087     def _parse(self, rqls):

  1088         for rql, sql in rqls:

  1089             yield self._check, rql, sql

  1090  

  1091     def _checkall(self, rql, sql):

  1092         try:

  1093             rqlst = self._prepare(rql)

  1094             r, args = self.o.generate(rqlst)

  1095             self.assertEqual((r.strip(), args), sql)

  1096         except Exception, ex:

  1097             print rql

  1098             if 'r' in locals():

  1099                 print r.strip()

  1100                 print '!='

  1101                 print sql[0].strip()

  1102             raise

  1103         return

  1104 #         rqlst, solutions = self._prepare(rql)

  1105 #         for i, sol in enumerate(solutions):

  1106 #             try:

  1107 #                 r, args = self.o.generate([(rqlst, sol)])

  1108 #                 self.assertEqual((r.strip(), args), sqls[i])

  1109 #             except Exception, ex:

  1110 #                 print rql

  1111 #                 raise

  1112 

  1113     def test1(self):

  1114         self._checkall('Any count(RDEF) WHERE RDEF relation_type X, X eid %(x)s',

  1115                        ("""SELECT COUNT(T1.C0) FROM (SELECT RDEF.eid AS C0

  1116 FROM EFRDef AS RDEF

  1117 WHERE RDEF.relation_type=%(x)s

  1118 UNION ALL

  1119 SELECT RDEF.eid AS C0

  1120 FROM ENFRDef AS RDEF

  1121 WHERE RDEF.relation_type=%(x)s) AS T1""", {}),

  1122                        )

  1123 

  1124     def test2(self):

  1125         self._checkall('Any X WHERE C comments X, C eid %(x)s',

  1126                        ('''SELECT rel_comments0.eid_to

  1127 FROM comments_relation AS rel_comments0

  1128 WHERE rel_comments0.eid_from=%(x)s''', {})

  1129                        )

  1130 

  1131     def test_cache_1(self):

  1132         self._check('Any X WHERE X in_basket B, B eid 12',

  1133                     '''SELECT rel_in_basket0.eid_from

  1134 FROM in_basket_relation AS rel_in_basket0

  1135 WHERE rel_in_basket0.eid_to=12''')