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

import sys

from mx.DateTime import today

from logilab.common.testlib import TestCase, unittest_main

from rql import BadRQLQuery

from indexer import get_indexer

#from cubicweb.server.sources.native import remove_unused_solutions

from cubicweb.server.sources.rql2sql import SQLGenerator

from rql.utils import register_function, FunctionDescr

# add a dumb registered procedure

class stockproc(FunctionDescr):

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

try:

    register_function(stockproc)

except AssertionError, ex:

    pass # already registered

from cubicweb.devtools import TestServerConfiguration

from cubicweb.devtools.repotest import RQLGeneratorTC

config = TestServerConfiguration('data')

config.bootstrap_cubes()

schema = config.load_schema()

schema['in_state'].inlined = True

schema['comments'].inlined = False

PARSER = [

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

     '''SELECT P.eid

FROM Personne AS P

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

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

     '''SELECT P.eid

FROM Personne AS P

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

    ]

BASIC = [

    ("Any X WHERE X is Affaire",

     '''SELECT X.eid

FROM Affaire AS X'''),

    ("Any X WHERE X eid 0",

     '''SELECT 0'''),

    ("Personne P",

     '''SELECT P.eid

FROM Personne AS P'''),

    ("Personne P WHERE P test TRUE",

     '''SELECT P.eid

FROM Personne AS P

WHERE P.test=True'''),

    ("Personne P WHERE P test false",

     '''SELECT P.eid

FROM Personne AS P

WHERE P.test=False'''),

    ("Personne P WHERE P eid -1",

     '''SELECT -1'''),

    ("Personne P LIMIT 20 OFFSET 10",

     '''SELECT P.eid

FROM Personne AS P

LIMIT 20

OFFSET 10'''),

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

     '''SELECT rel_travaille0.eid_from

FROM Societe AS S, travaille_relation AS rel_travaille0

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

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

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

FROM Division AS X, evaluee_relation AS rel_evaluee0

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

UNION ALL

SELECT rel_evaluee0.eid_to

FROM Personne AS X, evaluee_relation AS rel_evaluee0

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

UNION ALL

SELECT rel_evaluee0.eid_to

FROM Societe AS X, evaluee_relation AS rel_evaluee0

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

UNION ALL

SELECT rel_evaluee0.eid_to

FROM SubDivision AS X, evaluee_relation AS rel_evaluee0

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

FROM Division AS X, evaluee_relation AS rel_evaluee0

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

UNION ALL

SELECT rel_evaluee0.eid_to

FROM Personne AS X, evaluee_relation AS rel_evaluee0

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

UNION ALL

SELECT rel_evaluee0.eid_to

FROM Societe AS X, evaluee_relation AS rel_evaluee0

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

UNION ALL

SELECT rel_evaluee0.eid_to

FROM SubDivision AS X, evaluee_relation AS rel_evaluee0

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

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

     '''SELECT X.eid

FROM Affaire AS X

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

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

     '''SELECT G.name

FROM EGroup AS G, read_permission_relation AS rel_read_permission0

WHERE 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.login

FROM EUser AS U

WHERE U.login=admin"""),

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

     '''SELECT rel_tags0.eid_from

FROM State AS X, tags_relation AS rel_tags0

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

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

     '''SELECT 0, 1

FROM concerne_relation AS rel_concerne0

WHERE 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.eid

FROM Personne AS X

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

    ("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.eid

FROM Personne AS X

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

]

ADVANCED= [

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

     '''SELECT S.eid

FROM Societe AS S

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

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

    '''SELECT X.eid

FROM Division AS X

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

UNION ALL

SELECT X.eid

FROM Personne AS X

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

UNION ALL

SELECT X.eid

FROM Societe AS X

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

UNION ALL

SELECT X.eid

FROM SubDivision AS X

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

GROUP BY rel_evaluee0.eid_from'''),

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

     '''SELECT X.eid

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

WHERE ((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.eid

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

WHERE ((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.eid

FROM Note AS N, evaluee_relation AS rel_evaluee0

WHERE ((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.eid

FROM Note AS N, evaluee_relation AS rel_evaluee0

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

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

     '''SELECT N.eid

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

WHERE ((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.eid

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

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

    ('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_from

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

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

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

     '''SELECT P.eid

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

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

    ('Any X WHERE T tags X',

     '''SELECT rel_tags0.eid_to

FROM tags_relation AS rel_tags0'''),

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

     '''SELECT rel_in_basket0.eid_from

FROM in_basket_relation AS rel_in_basket0

WHERE 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.name

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

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

UNION ALL

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

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

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

    # 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.eid

FROM Note AS S, Personne AS O

WHERE (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 C1

FROM Note AS S

WHERE S.ecrit_par IS NULL

ORDER 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.eid

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

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

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

     '''SELECT rel_evaluee1.eid_to

FROM evaluee_relation AS rel_evaluee1, todo_by_relation AS rel_todo_by0

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

FROM owned_by_relation AS rel_owned_by0

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

    ('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.name

FROM EGroup AS G, in_group_relation AS rel_in_group0

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

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

      """SELECT C.eid

FROM Card AS C

WHERE 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.eid

FROM Card AS C

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)"""),

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

      """SELECT C.title

FROM Card AS C

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)"""),

    ('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.login

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

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

    ('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_tags0

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

GROUP 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.nom

FROM Societe AS C, concerne_relation AS rel_concerne0

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

GROUP BY C.nom

ORDER BY 1 DESC

LIMIT 10'''),

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

     '''SELECT rel_evaluee0.eid_to

FROM EUser AS Y, evaluee_relation AS rel_evaluee0

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

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

     '''SELECT Y.login

FROM EUser AS X, EUser AS Y

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

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

     '''SELECT Y.login

FROM EUser AS X, EUser AS Y

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

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

     '''SELECT Y.login

FROM 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.name

FROM Basket AS X

UNION ALL

SELECT X.name

FROM ECache AS X

UNION ALL

SELECT X.name

FROM EConstraintType AS X

UNION ALL

SELECT X.name

FROM EEType AS X

UNION ALL

SELECT X.name

FROM EGroup AS X

UNION ALL

SELECT X.name

FROM EPermission AS X

UNION ALL

SELECT X.name

FROM ERType AS X

UNION ALL

SELECT X.name

FROM File AS X

UNION ALL

SELECT X.name

FROM Folder AS X

UNION ALL

SELECT X.name

FROM Image AS X

UNION ALL

SELECT X.name

FROM State AS X

UNION ALL

SELECT X.name

FROM Tag AS X

UNION ALL

SELECT X.name

FROM Transition AS X

ORDER 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_to

FROM EEType AS X, read_permission_relation AS rel_read_permission0

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)

UNION