154 FROM Personne AS X |
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)))'''), |
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 ] |
156 ] |
157 |
157 |
158 ADVANCED= [ |
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 |
159 |
165 ("Societe S WHERE S nom 'Logilab' OR S nom 'Caesium'", |
160 ("Societe S WHERE S nom 'Logilab' OR S nom 'Caesium'", |
166 '''SELECT S.eid |
161 '''SELECT S.eid |
167 FROM Societe AS S |
162 FROM Societe AS S |
168 WHERE ((S.nom=Logilab) OR (S.nom=Caesium))'''), |
163 WHERE ((S.nom=Logilab) OR (S.nom=Caesium))'''), |
1062 FROM Note AS S |
1057 FROM Note AS S |
1063 WHERE S.eid=0 AND S.in_state IS NOT NULL''') |
1058 WHERE S.eid=0 AND S.in_state IS NOT NULL''') |
1064 |
1059 |
1065 ] |
1060 ] |
1066 |
1061 |
1067 |
1062 INTERSECT = [ |
|
1063 ('Any SN WHERE NOT X in_state S, S name SN', |
|
1064 '''SELECT DISTINCT S.name |
|
1065 FROM Affaire AS X, State AS S |
|
1066 WHERE (X.in_state IS NULL OR X.in_state!=S.eid) |
|
1067 INTERSECT |
|
1068 SELECT DISTINCT S.name |
|
1069 FROM EUser AS X, State AS S |
|
1070 WHERE (X.in_state IS NULL OR X.in_state!=S.eid) |
|
1071 INTERSECT |
|
1072 SELECT DISTINCT S.name |
|
1073 FROM Note AS X, State AS S |
|
1074 WHERE (X.in_state IS NULL OR X.in_state!=S.eid)'''), |
|
1075 |
|
1076 ('Any PN WHERE NOT X travaille S, X nom PN, S is IN(Division, Societe)', |
|
1077 '''SELECT X.nom |
|
1078 FROM Personne AS X |
|
1079 WHERE NOT EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0,Division AS S WHERE rel_travaille0.eid_from=X.eid AND rel_travaille0.eid_to=S.eid) |
|
1080 INTERSECT ALL |
|
1081 SELECT X.nom |
|
1082 FROM Personne AS X |
|
1083 WHERE NOT EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0,Societe AS S WHERE rel_travaille0.eid_from=X.eid AND rel_travaille0.eid_to=S.eid)'''), |
|
1084 |
|
1085 ('Any PN WHERE NOT X travaille S, S nom PN, S is IN(Division, Societe)', |
|
1086 '''SELECT S.nom |
|
1087 FROM Division AS S |
|
1088 WHERE NOT EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_to=S.eid) |
|
1089 UNION ALL |
|
1090 SELECT S.nom |
|
1091 FROM Societe AS S |
|
1092 WHERE NOT EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_to=S.eid)'''), |
|
1093 |
|
1094 ('Personne X WHERE NOT X travaille S, S nom "chouette"', |
|
1095 '''SELECT X.eid |
|
1096 FROM Division AS S, Personne AS X |
|
1097 WHERE NOT EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_from=X.eid AND rel_travaille0.eid_to=S.eid) AND S.nom=chouette |
|
1098 UNION ALL |
|
1099 SELECT X.eid |
|
1100 FROM Personne AS X, Societe AS S |
|
1101 WHERE NOT EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_from=X.eid AND rel_travaille0.eid_to=S.eid) AND S.nom=chouette |
|
1102 UNION ALL |
|
1103 SELECT X.eid |
|
1104 FROM Personne AS X, SubDivision AS S |
|
1105 WHERE NOT EXISTS(SELECT 1 FROM travaille_relation AS rel_travaille0 WHERE rel_travaille0.eid_from=X.eid AND rel_travaille0.eid_to=S.eid) AND S.nom=chouette'''), |
|
1106 |
|
1107 ('Any X WHERE X is ET, ET eid 2', |
|
1108 '''SELECT rel_is0.eid_from |
|
1109 FROM is_relation AS rel_is0 |
|
1110 WHERE rel_is0.eid_to=2'''), |
|
1111 |
|
1112 ] |
1068 from logilab.common.adbh import ADV_FUNC_HELPER_DIRECTORY |
1113 from logilab.common.adbh import ADV_FUNC_HELPER_DIRECTORY |
1069 |
1114 |
1070 class PostgresSQLGeneratorTC(RQLGeneratorTC): |
1115 class PostgresSQLGeneratorTC(RQLGeneratorTC): |
1071 schema = schema |
1116 schema = schema |
1072 |
1117 |
1194 yield t |
1239 yield t |
1195 |
1240 |
1196 def test_negation(self): |
1241 def test_negation(self): |
1197 for t in self._parse(NEGATIONS): |
1242 for t in self._parse(NEGATIONS): |
1198 yield t |
1243 yield t |
|
1244 |
|
1245 def test_intersection(self): |
|
1246 for t in self._parse(INTERSECT): |
|
1247 yield t |
1199 |
1248 |
1200 def test_union(self): |
1249 def test_union(self): |
1201 for t in self._parse(( |
1250 for t in self._parse(( |
1202 ('(Any N ORDERBY 1 WHERE X name N, X is State)' |
1251 ('(Any N ORDERBY 1 WHERE X name N, X is State)' |
1203 ' UNION ' |
1252 ' UNION ' |
1368 dbms_helper.fti_restriction_sql = indexer.restriction_sql |
1417 dbms_helper.fti_restriction_sql = indexer.restriction_sql |
1369 dbms_helper.fti_need_distinct_query = indexer.need_distinct |
1418 dbms_helper.fti_need_distinct_query = indexer.need_distinct |
1370 self.o = SQLGenerator(schema, dbms_helper) |
1419 self.o = SQLGenerator(schema, dbms_helper) |
1371 |
1420 |
1372 def _norm_sql(self, sql): |
1421 def _norm_sql(self, sql): |
1373 return sql.strip().replace(' ILIKE ', ' LIKE ') |
1422 return sql.strip().replace(' ILIKE ', ' LIKE ').replace('\nINTERSECT ALL\n', '\nINTERSECT\n') |
1374 |
1423 |
1375 def test_union(self): |
1424 def test_union(self): |
1376 for t in self._parse(( |
1425 for t in self._parse(( |
1377 ('(Any N ORDERBY 1 WHERE X name N, X is State)' |
1426 ('(Any N ORDERBY 1 WHERE X name N, X is State)' |
1378 ' UNION ' |
1427 ' UNION ' |