diff -r dd9f2dd02f85 -r 0e3460341023 server/test/unittest_rql2sql.py --- a/server/test/unittest_rql2sql.py Tue Aug 18 09:25:44 2009 +0200 +++ b/server/test/unittest_rql2sql.py Fri Aug 21 16:26:20 2009 +0200 @@ -339,6 +339,9 @@ ('Any XN ORDERBY XN WHERE X name XN', '''SELECT X.cw_name +FROM cw_BaseTransition AS X +UNION ALL +SELECT X.cw_name FROM cw_Basket AS X UNION ALL SELECT X.cw_name @@ -376,14 +379,15 @@ UNION ALL SELECT X.cw_name FROM cw_Transition AS X +UNION ALL +SELECT X.cw_name +FROM cw_Workflow AS X +UNION ALL +SELECT X.cw_name +FROM cw_WorkflowTransition 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, can use relation under exists scope as principal ('DISTINCT Any X,Y WHERE X name "CWGroup", Y eid IN(1, 2, 3), EXISTS(X read_permission Y)', '''SELECT DISTINCT X.cw_eid, rel_read_permission0.eid_to FROM cw_CWEType AS X, read_permission_relation AS rel_read_permission0 @@ -467,6 +471,9 @@ ('Any MAX(X)+MIN(X), N GROUPBY N WHERE X name N;', '''SELECT (MAX(T1.C0) + MIN(T1.C0)), T1.C1 FROM (SELECT X.cw_eid AS C0, X.cw_name AS C1 +FROM cw_BaseTransition AS X +UNION ALL +SELECT X.cw_eid AS C0, X.cw_name AS C1 FROM cw_Basket AS X UNION ALL SELECT X.cw_eid AS C0, X.cw_name AS C1 @@ -503,7 +510,13 @@ FROM cw_Tag AS X UNION ALL SELECT X.cw_eid AS C0, X.cw_name AS C1 -FROM cw_Transition AS X) AS T1 +FROM cw_Transition AS X +UNION ALL +SELECT X.cw_eid AS C0, X.cw_name AS C1 +FROM cw_Workflow AS X +UNION ALL +SELECT X.cw_eid AS C0, X.cw_name AS C1 +FROM cw_WorkflowTransition AS X) AS T1 GROUP 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;', @@ -760,14 +773,10 @@ ('Any X,S WHERE X travaille S?', '''SELECT X.cw_eid, rel_travaille0.eid_to FROM cw_Personne AS X LEFT OUTER JOIN travaille_relation AS rel_travaille0 ON (rel_travaille0.eid_from=X.cw_eid)''' -#SELECT X.cw_eid, S.cw_eid -#FROM cw_Personne AS X LEFT OUTER JOIN travaille_relation AS rel_travaille0 ON (rel_travaille0.eid_from=X.cw_eid) LEFT OUTER JOIN cw_Societe AS S ON (rel_travaille0.eid_to=S.cw_eid)''' ), ('Any S,X WHERE X? travaille S, S is Societe', '''SELECT S.cw_eid, rel_travaille0.eid_from FROM cw_Societe AS S LEFT OUTER JOIN travaille_relation AS rel_travaille0 ON (rel_travaille0.eid_to=S.cw_eid)''' -#SELECT S.cw_eid, X.cw_eid -#FROM cw_Societe AS S LEFT OUTER JOIN travaille_relation AS rel_travaille0 ON (rel_travaille0.eid_to=S.cw_eid) LEFT OUTER JOIN cw_Personne AS X ON (rel_travaille0.eid_from=X.cw_eid)''' ), ('Any N,A WHERE N inline1 A?', @@ -803,8 +812,6 @@ ('Any C,M WHERE C travaille G?, G evaluee M?, G is Societe', '''SELECT C.cw_eid, rel_evaluee1.eid_to FROM cw_Personne AS C LEFT OUTER JOIN travaille_relation AS rel_travaille0 ON (rel_travaille0.eid_from=C.cw_eid) LEFT OUTER JOIN cw_Societe AS G ON (rel_travaille0.eid_to=G.cw_eid) LEFT OUTER JOIN evaluee_relation AS rel_evaluee1 ON (rel_evaluee1.eid_from=G.cw_eid)''' -#SELECT C.cw_eid, M.cw_eid -#FROM cw_Personne AS C LEFT OUTER JOIN travaille_relation AS rel_travaille0 ON (rel_travaille0.eid_from=C.cw_eid) LEFT OUTER JOIN cw_Societe AS G ON (rel_travaille0.eid_to=G.cw_eid) LEFT OUTER JOIN evaluee_relation AS rel_evaluee1 ON (rel_evaluee1.eid_from=G.cw_eid) LEFT OUTER JOIN cw_Note AS M ON (rel_evaluee1.eid_to=M.cw_eid)''' ), ('Any A,C WHERE A documented_by C?, (C is NULL) OR (EXISTS(C require_permission F, ' @@ -817,9 +824,6 @@ '''SELECT X.cw_eid FROM cw_Personne AS X LEFT OUTER JOIN connait_relation AS rel_connait0 ON (rel_connait0.eid_to=12) WHERE X.cw_eid=12''' -#SELECT 12 -#FROM cw_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.cw_eid) -#WHERE X.cw_eid=12''' ), ('Any GN, TN ORDERBY GN WHERE T tags G?, T name TN, G name GN', @@ -880,14 +884,18 @@ ''' SELECT T.cw_eid, _T0.C0, _T0.C1 FROM cw_Tag AS T LEFT OUTER JOIN tags_relation AS rel_tags0 ON (rel_tags0.eid_from=T.cw_eid) LEFT OUTER JOIN (SELECT G.cw_eid AS C0, S.cw_eid AS C1 -FROM cw_Affaire AS G LEFT OUTER JOIN cw_State AS S ON (G.cw_in_state=S.cw_eid AND S.cw_name=hop) +FROM cw_Affaire AS G LEFT OUTER JOIN cw_State AS S ON (G.cw_in_state=S.cw_eid AND S.cw_name=hop) UNION ALL SELECT G.cw_eid AS C0, S.cw_eid AS C1 -FROM cw_CWUser AS G LEFT OUTER JOIN cw_State AS S ON (G.cw_in_state=S.cw_eid AND S.cw_name=hop) +FROM cw_CWUser AS G LEFT OUTER JOIN cw_State AS S ON (G.cw_in_state=S.cw_eid AND S.cw_name=hop) UNION ALL SELECT G.cw_eid AS C0, S.cw_eid AS C1 FROM cw_Note AS G LEFT OUTER JOIN cw_State AS S ON (G.cw_in_state=S.cw_eid AND S.cw_name=hop) ) AS _T0 ON (rel_tags0.eid_to=_T0.C0)'''), + ('Any O,AD WHERE NOT S inline1 O, S eid 123, O todo_by AD?', + '''SELECT O.cw_eid, rel_todo_by0.eid_to +FROM cw_Affaire AS O LEFT OUTER JOIN todo_by_relation AS rel_todo_by0 ON (rel_todo_by0.eid_from=O.cw_eid), cw_Note AS S +WHERE NOT EXISTS(SELECT 1 WHERE S.cw_inline1=O.cw_eid) AND S.cw_eid=123''') ] VIRTUAL_VARS = [ @@ -949,10 +957,6 @@ ("Any COUNT(P) WHERE P is Personne", '''SELECT COUNT(P.cw_eid) FROM cw_Personne AS P'''), -## ("Personne X where X nom upper('TOTO')", -## '''SELECT X.cw_eid\nFROM cw_Personne AS X\nWHERE UPPER(X.cw_nom) = TOTO'''), -## ("Personne X where X nom Y, UPPER(X) prenom upper(Y)", -## '''SELECT X.cw_eid\nFROM cw_Personne AS X\nWHERE UPPER(X.cw_prenom) = UPPER(X.cw_nom)'''), ] SYMETRIC = [ @@ -960,13 +964,6 @@ '''SELECT DISTINCT P.cw_eid FROM connait_relation AS rel_connait0, cw_Personne AS P WHERE (rel_connait0.eid_from=0 AND rel_connait0.eid_to=P.cw_eid OR rel_connait0.eid_to=0 AND rel_connait0.eid_from=P.cw_eid)''' -# '''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', @@ -1050,8 +1047,9 @@ ('Any S,ES,T WHERE S state_of ET, ET name "CWUser", ES allowed_transition T, T destination_state S', '''SELECT T.cw_destination_state, rel_allowed_transition1.eid_from, T.cw_eid -FROM allowed_transition_relation AS rel_allowed_transition1, cw_CWEType AS ET, cw_Transition AS T, state_of_relation AS rel_state_of0 +FROM allowed_transition_relation AS rel_allowed_transition1, cw_Transition AS T, cw_Workflow AS ET, state_of_relation AS rel_state_of0 WHERE T.cw_destination_state=rel_state_of0.eid_from AND rel_state_of0.eid_to=ET.cw_eid AND ET.cw_name=CWUser AND rel_allowed_transition1.eid_to=T.cw_eid'''), + ('Any O WHERE S eid 0, S in_state O', '''SELECT S.cw_in_state FROM cw_Affaire AS S @@ -1127,11 +1125,11 @@ delete = self.rqlhelper.parse( 'DELETE X read_permission READ_PERMISSIONSUBJECT,X add_permission ADD_PERMISSIONSUBJECT,' 'X in_basket IN_BASKETSUBJECT,X delete_permission DELETE_PERMISSIONSUBJECT,' - 'X initial_state INITIAL_STATESUBJECT,X update_permission UPDATE_PERMISSIONSUBJECT,' + 'X update_permission UPDATE_PERMISSIONSUBJECT,' 'X created_by CREATED_BYSUBJECT,X is ISSUBJECT,X is_instance_of IS_INSTANCE_OFSUBJECT,' 'X owned_by OWNED_BYSUBJECT,X specializes SPECIALIZESSUBJECT,ISOBJECT is X,' - 'SPECIALIZESOBJECT specializes X,STATE_OFOBJECT state_of X,IS_INSTANCE_OFOBJECT is_instance_of X,' - 'TO_ENTITYOBJECT to_entity X,TRANSITION_OFOBJECT transition_of X,FROM_ENTITYOBJECT from_entity X ' + 'SPECIALIZESOBJECT specializes X,IS_INSTANCE_OFOBJECT is_instance_of X,' + 'TO_ENTITYOBJECT to_entity X,FROM_ENTITYOBJECT from_entity X ' 'WHERE X is CWEType') self.rqlhelper.compute_solutions(delete) def var_sols(var): @@ -1171,7 +1169,7 @@ r, nargs = self.o.generate(union, args, varmap=varmap) args.update(nargs) - self.assertLinesEquals((r % args).strip(), self._norm_sql(sql)) + self.assertLinesEquals((r % args).strip(), self._norm_sql(sql), striplines=True) except Exception, ex: if 'r' in locals(): try: @@ -1200,14 +1198,6 @@ print sql[0].strip() raise return -# 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 -# raise def test1(self): self._checkall('Any count(RDEF) WHERE RDEF relation_type X, X eid %(x)s', @@ -1408,7 +1398,7 @@ FROM appears AS appears0, entities AS X WHERE 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"', + ('Any X WHERE X has_text "toto tata", X name "tutu", X is IN (Basket,File,Folder)', """SELECT X.cw_eid FROM appears AS appears0, cw_Basket AS X WHERE appears0.words @@ to_tsquery('default', 'toto&tata') AND appears0.uid=X.cw_eid AND X.cw_name=tutu @@ -1420,22 +1410,7 @@ SELECT X.cw_eid FROM appears AS appears0, cw_Folder AS X WHERE appears0.words @@ to_tsquery('default', 'toto&tata') AND appears0.uid=X.cw_eid AND X.cw_name=tutu -UNION ALL -SELECT X.cw_eid -FROM appears AS appears0, cw_Image AS X -WHERE appears0.words @@ to_tsquery('default', 'toto&tata') AND appears0.uid=X.cw_eid AND X.cw_name=tutu -UNION ALL -SELECT X.cw_eid -FROM appears AS appears0, cw_State AS X -WHERE appears0.words @@ to_tsquery('default', 'toto&tata') AND appears0.uid=X.cw_eid AND X.cw_name=tutu -UNION ALL -SELECT X.cw_eid -FROM appears AS appears0, cw_Tag AS X -WHERE appears0.words @@ to_tsquery('default', 'toto&tata') AND appears0.uid=X.cw_eid AND X.cw_name=tutu -UNION ALL -SELECT X.cw_eid -FROM appears AS appears0, cw_Transition AS X -WHERE appears0.words @@ to_tsquery('default', 'toto&tata') AND appears0.uid=X.cw_eid AND X.cw_name=tutu"""), +"""), ('Personne X where X has_text %(text)s, X travaille S, S has_text %(text)s', """SELECT X.eid @@ -1572,7 +1547,7 @@ FROM appears AS appears0, entities AS X WHERE 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"', + ('Any X WHERE X has_text "toto tata", X name "tutu", X is IN (Basket,File,Folder)', """SELECT X.cw_eid FROM appears AS appears0, cw_Basket AS X WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata')) AND appears0.uid=X.cw_eid AND X.cw_name=tutu @@ -1584,22 +1559,7 @@ SELECT X.cw_eid FROM appears AS appears0, cw_Folder AS X WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata')) AND appears0.uid=X.cw_eid AND X.cw_name=tutu -UNION ALL -SELECT X.cw_eid -FROM appears AS appears0, cw_Image AS X -WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata')) AND appears0.uid=X.cw_eid AND X.cw_name=tutu -UNION ALL -SELECT X.cw_eid -FROM appears AS appears0, cw_State AS X -WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata')) AND appears0.uid=X.cw_eid AND X.cw_name=tutu -UNION ALL -SELECT X.cw_eid -FROM appears AS appears0, cw_Tag AS X -WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata')) AND appears0.uid=X.cw_eid AND X.cw_name=tutu -UNION ALL -SELECT X.cw_eid -FROM appears AS appears0, cw_Transition AS X -WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata')) AND appears0.uid=X.cw_eid AND X.cw_name=tutu"""), +"""), )): yield t @@ -1648,7 +1608,7 @@ """SELECT X.eid FROM appears AS appears0, entities AS X WHERE 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"', + ('Any X WHERE X has_text "toto tata", X name "tutu", X is IN (Basket,File,Folder)', """SELECT X.cw_eid FROM appears AS appears0, cw_Basket AS X WHERE MATCH (appears0.words) AGAINST ('toto tata' IN BOOLEAN MODE) AND appears0.uid=X.cw_eid AND X.cw_name=tutu @@ -1660,22 +1620,7 @@ SELECT X.cw_eid FROM appears AS appears0, cw_Folder AS X WHERE MATCH (appears0.words) AGAINST ('toto tata' IN BOOLEAN MODE) AND appears0.uid=X.cw_eid AND X.cw_name=tutu -UNION ALL -SELECT X.cw_eid -FROM appears AS appears0, cw_Image AS X -WHERE MATCH (appears0.words) AGAINST ('toto tata' IN BOOLEAN MODE) AND appears0.uid=X.cw_eid AND X.cw_name=tutu -UNION ALL -SELECT X.cw_eid -FROM appears AS appears0, cw_State AS X -WHERE MATCH (appears0.words) AGAINST ('toto tata' IN BOOLEAN MODE) AND appears0.uid=X.cw_eid AND X.cw_name=tutu -UNION ALL -SELECT X.cw_eid -FROM appears AS appears0, cw_Tag AS X -WHERE MATCH (appears0.words) AGAINST ('toto tata' IN BOOLEAN MODE) AND appears0.uid=X.cw_eid AND X.cw_name=tutu -UNION ALL -SELECT X.cw_eid -FROM appears AS appears0, cw_Transition AS X -WHERE MATCH (appears0.words) AGAINST ('toto tata' IN BOOLEAN MODE) AND appears0.uid=X.cw_eid AND X.cw_name=tutu""") +""") ] for t in self._parse(queries): yield t