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