# HG changeset patch # User Sylvain Thénault # Date 1311769352 -7200 # Node ID 936530f8d32c05300d6aa2aeabab1caa590aebf3 # Parent 359bc86d28272a4a91cf8112fdf77fd79a5fa43c [sql gen] handle optional on comparison node (eg HAVING expression) and on rhs of final relation. Closes #1859609 diff -r 359bc86d2827 -r 936530f8d32c server/rqlannotation.py --- a/server/rqlannotation.py Wed Jul 27 14:17:47 2011 +0200 +++ b/server/rqlannotation.py Wed Jul 27 14:22:32 2011 +0200 @@ -211,16 +211,22 @@ relation for the rhs variable """ principal = None + others = [] # sort for test predictability for rel in sorted(relations, key=lambda x: (x.children[0].name, x.r_type)): # only equality relation with a variable as rhs may be principal if rel.operator() not in ('=', 'IS') \ or not isinstance(rel.children[1].children[0], VariableRef) or rel.neged(strict=True): continue + if rel.optional: + others.append(rel) + continue if rel.scope is rel.stmt: return rel principal = rel if principal is None: + if others: + return others[0] raise BadRQLQuery('unable to find principal in %s' % ', '.join( r.as_string() for r in relations)) return principal diff -r 359bc86d2827 -r 936530f8d32c server/sources/rql2sql.py --- a/server/sources/rql2sql.py Wed Jul 27 14:17:47 2011 +0200 +++ b/server/sources/rql2sql.py Wed Jul 27 14:22:32 2011 +0200 @@ -1292,9 +1292,16 @@ relation.r_type) try: self._state.ignore_varmap = True - return '%s%s' % (lhssql, relation.children[1].accept(self)) + sql = lhssql + relation.children[1].accept(self) finally: self._state.ignore_varmap = False + if relation.optional == 'right': + leftalias = self._var_table(principal.children[0].variable) + rightalias = self._var_table(relation.children[0].variable) + self._state.replace_tables_by_outer_join( + leftalias, rightalias, 'LEFT', sql) + return '' + return sql return '' def _visit_attribute_relation(self, rel): @@ -1372,12 +1379,15 @@ def visit_comparison(self, cmp): """generate SQL for a comparison""" + optional = getattr(cmp, 'optional', None) # rql < 0.30 if len(cmp.children) == 2: - # XXX occurs ? + # simplified expression from HAVING clause lhs, rhs = cmp.children else: lhs = None rhs = cmp.children[0] + assert not optional + sql = None operator = cmp.operator if operator in ('LIKE', 'ILIKE'): if operator == 'ILIKE' and not self.dbhelper.ilike_support: @@ -1385,18 +1395,39 @@ else: operator = ' %s ' % operator elif operator == 'REGEXP': - return ' %s' % self.dbhelper.sql_regexp_match_expression(rhs.accept(self)) + sql = ' %s' % self.dbhelper.sql_regexp_match_expression(rhs.accept(self)) elif (operator == '=' and isinstance(rhs, Constant) and rhs.eval(self._args) is None): if lhs is None: - return ' IS NULL' - return '%s IS NULL' % lhs.accept(self) + sql = ' IS NULL' + else: + sql = '%s IS NULL' % lhs.accept(self) elif isinstance(rhs, Function) and rhs.name == 'IN': assert operator == '=' operator = ' ' - if lhs is None: - return '%s%s'% (operator, rhs.accept(self)) - return '%s%s%s'% (lhs.accept(self), operator, rhs.accept(self)) + if sql is None: + if lhs is None: + sql = '%s%s'% (operator, rhs.accept(self)) + else: + sql = '%s%s%s'% (lhs.accept(self), operator, rhs.accept(self)) + if optional is None: + return sql + leftvars = cmp.children[0].get_nodes(VariableRef) + assert len(leftvars) == 1 + leftalias = self._var_table(leftvars[0].variable.stinfo['attrvar']) + rightvars = cmp.children[1].get_nodes(VariableRef) + assert len(rightvars) == 1 + rightalias = self._var_table(rightvars[0].variable.stinfo['attrvar']) + if optional == 'right': + self._state.replace_tables_by_outer_join( + leftalias, rightalias, 'LEFT', sql) + elif optional == 'left': + self._state.replace_tables_by_outer_join( + rightalias, leftalias, 'LEFT', sql) + else: + self._state.replace_tables_by_outer_join( + leftalias, rightalias, 'FULL', sql) + return '' def visit_mathexpression(self, mexpr): """generate SQL for a mathematic expression""" diff -r 359bc86d2827 -r 936530f8d32c server/test/unittest_rql2sql.py --- a/server/test/unittest_rql2sql.py Wed Jul 27 14:17:47 2011 +0200 +++ b/server/test/unittest_rql2sql.py Wed Jul 27 14:22:32 2011 +0200 @@ -807,6 +807,11 @@ OUTER_JOIN = [ + + ('Any U,G WHERE U login L, G name L?, G is CWGroup', + '''SELECT _U.cw_eid, _G.cw_eid +FROM cw_CWUser AS _U LEFT OUTER JOIN cw_CWGroup AS _G ON (_G.cw_name=_U.cw_login)'''), + ('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)''' @@ -969,6 +974,18 @@ '''SELECT _CFG.cw_ecrit_par, _CALIBCFG.cw_eid, _CFG.cw_eid FROM cw_Note AS _CFG LEFT OUTER JOIN cw_Note AS _CALIBCFG ON (_CALIBCFG.cw_ecrit_par=_CFG.cw_ecrit_par) WHERE _CFG.cw_ecrit_par=1'''), + + ('Any U,G WHERE U login UL, G name GL, G is CWGroup HAVING UPPER(UL)=UPPER(GL)?', + '''SELECT _U.cw_eid, _G.cw_eid +FROM cw_CWUser AS _U LEFT OUTER JOIN cw_CWGroup AS _G ON (UPPER(_U.cw_login)=UPPER(_G.cw_name))'''), + + ('Any U,G WHERE U login UL, G name GL, G is CWGroup HAVING UPPER(UL)?=UPPER(GL)', + '''SELECT _U.cw_eid, _G.cw_eid +FROM cw_CWGroup AS _G LEFT OUTER JOIN cw_CWUser AS _U ON (UPPER(_U.cw_login)=UPPER(_G.cw_name))'''), + + ('Any U,G WHERE U login UL, G name GL, G is CWGroup HAVING UPPER(UL)?=UPPER(GL)?', + '''SELECT _U.cw_eid, _G.cw_eid +FROM cw_CWUser AS _U FULL OUTER JOIN cw_CWGroup AS _G ON (UPPER(_U.cw_login)=UPPER(_G.cw_name))'''), ] VIRTUAL_VARS = [