# HG changeset patch # User Sylvain Thénault # Date 1312455048 -7200 # Node ID 59fea81647e5b1a2c247b6d4613ecd666db704e9 # Parent 432e1f0d4857c818f60117d7d19a0dada7f03c41 [rql2sql] closes #1892473: enhance handling of optional inlined relation * some outer join aren't supported while they could * consider newly available 'optcomparisons' st information on variables (rql 0.30) diff -r 432e1f0d4857 -r 59fea81647e5 server/rqlannotation.py --- a/server/rqlannotation.py Wed Aug 03 13:28:06 2011 +0200 +++ b/server/rqlannotation.py Thu Aug 04 12:50:48 2011 +0200 @@ -109,8 +109,9 @@ ostinfo = rhs.children[0].variable.stinfo else: ostinfo = lhs.variable.stinfo - if not any(orel for orel in ostinfo['relations'] - if orel.optional and orel is not rel): + if not (ostinfo.get('optcomparisons') or + any(orel for orel in ostinfo['relations'] + if orel.optional and orel is not rel)): break if rschema.final or (onlhs and rschema.inlined): if rschema.type != 'has_text': diff -r 432e1f0d4857 -r 59fea81647e5 server/sources/rql2sql.py --- a/server/sources/rql2sql.py Wed Aug 03 13:28:06 2011 +0200 +++ b/server/sources/rql2sql.py Thu Aug 04 12:50:48 2011 +0200 @@ -1238,35 +1238,47 @@ def _visit_outer_join_inlined_relation(self, relation, rschema): - leftvar, leftconst, rightvar, rightconst = relation_info(relation) - assert not (leftconst and rightconst), "doesn't make sense" - if relation.optional != 'right': - leftvar, rightvar = rightvar, leftvar - leftconst, rightconst = rightconst, leftconst - outertype = 'FULL' if relation.optional == 'both' else 'LEFT' - leftalias = self._var_table(leftvar) + lhsvar, lhsconst, rhsvar, rhsconst = relation_info(relation) + assert not (lhsconst and rhsconst), "doesn't make sense" attr = 'eid' if relation.r_type == 'identity' else relation.r_type - lhs, rhs = relation.get_variable_parts() + lhsalias = self._var_table(lhsvar) + rhsalias = rhsvar and self._var_table(rhsvar) try: - lhssql = self._varmap['%s.%s' % (lhs.name, attr)] + lhssql = self._varmap['%s.%s' % (lhsvar.name, attr)] except KeyError: - lhssql = '%s.%s%s' % (self._var_table(lhs.variable), SQL_PREFIX, attr) - if rightvar is not None: - rightalias = self._var_table(rightvar) - if rightalias is None: - if rightconst is not None: - # inlined relation with invariant as rhs - condition = '%s=%s' % (lhssql, rightconst.accept(self)) - if relation.r_type != 'identity': - condition = '(%s OR %s IS NULL)' % (condition, lhssql) - if not leftvar.stinfo.get('optrelations'): - return condition - self._state.add_outer_join_condition(leftalias, condition) - return - if leftalias is None: - leftalias = leftvar._q_sql.split('.', 1)[0] - self._state.replace_tables_by_outer_join( - leftalias, rightalias, outertype, '%s=%s' % (lhssql, rhs.accept(self))) + if lhsalias is None: + lhssql = lhsconst.accept(self) + else: + lhssql = '%s.%s%s' % (lhsalias, SQL_PREFIX, attr) + condition = '%s=%s' % (lhssql, (rhsconst or rhsvar).accept(self)) + # this is not a typo, rhs optional variable means lhs outer join and vice-versa + if relation.optional == 'left': + lhsvar, rhsvar = rhsvar, lhsvar + lhsconst, rhsconst = rhsconst, lhsconst + lhsalias, rhsalias = rhsalias, lhsalias + outertype = 'LEFT' + elif relation.optional == 'both': + outertype = 'FULL' + else: + outertype = 'LEFT' + if rhsalias is None: + if rhsconst is not None: + # inlined relation with invariant as rhs + if relation.r_type != 'identity': + condition = '(%s OR %s IS NULL)' % (condition, lhssql) + if not lhsvar.stinfo.get('optrelations'): + return condition + self._state.add_outer_join_condition(lhsalias, condition) + return + if lhsalias is None: + if lhsconst is not None and not rhsvar.stinfo.get('optrelations'): + return condition + lhsalias = lhsvar._q_sql.split('.', 1)[0] + if lhsalias == rhsalias: + self._state.add_outer_join_condition(lhsalias, condition) + else: + self._state.replace_tables_by_outer_join( + lhsalias, rhsalias, outertype, condition) return '' def _visit_var_attr_relation(self, relation, rhs_vars): diff -r 432e1f0d4857 -r 59fea81647e5 server/test/unittest_rql2sql.py --- a/server/test/unittest_rql2sql.py Wed Aug 03 13:28:06 2011 +0200 +++ b/server/test/unittest_rql2sql.py Thu Aug 04 12:50:48 2011 +0200 @@ -976,7 +976,7 @@ ('Any CASE, CALIBCFG, CFG ' 'WHERE CASE eid 1, CFG ecrit_par CASE, CALIBCFG? ecrit_par CASE', '''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) +FROM cw_Note AS _CFG LEFT OUTER JOIN cw_Note AS _CALIBCFG ON (_CALIBCFG.cw_ecrit_par=1) WHERE _CFG.cw_ecrit_par=1'''), ('Any U,G WHERE U login UL, G name GL, G is CWGroup HAVING UPPER(UL)=UPPER(GL)?', @@ -990,6 +990,17 @@ ('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))'''), + + ('Any H, COUNT(X), SUM(XCE)/1000 ' + 'WHERE X type "0", X date XSCT, X para XCE, X? ecrit_par F, F eid 999999, F is Personne, ' + 'DH is Affaire, DH ref H ' + 'HAVING XSCT?=H', + '''SELECT _DH.cw_ref, COUNT(_X.cw_eid), (SUM(_X.cw_para) / 1000) +FROM cw_Affaire AS _DH LEFT OUTER JOIN cw_Note AS _X ON (_X.cw_date=_DH.cw_ref AND _X.cw_type=0 AND _X.cw_ecrit_par=999999)'''), + + ('Any C WHERE X ecrit_par C?, X? inline1 F, F eid 1, X type XT, Z is Personne, Z nom ZN HAVING ZN=XT?', + '''SELECT _X.cw_ecrit_par +FROM cw_Personne AS _Z LEFT OUTER JOIN cw_Note AS _X ON (_Z.cw_nom=_X.cw_type AND _X.cw_inline1=1)'''), ] VIRTUAL_VARS = [