# HG changeset patch # User Sylvain Thénault # Date 1475781421 -7200 # Node ID 22b854d3e8b2047260df16f733696fd359630c05 # Parent f5b815f67ce28e2f0cea9a71eb2eb1d14675f265 [rql2sql] Stop generating SQL query from RQL using entities.type This was necessary in the "true multi-sources" time, it's not anymore, while maintaining this index is costly. Related to #15538317 diff -r f5b815f67ce2 -r 22b854d3e8b2 cubicweb/server/rqlannotation.py --- a/cubicweb/server/rqlannotation.py Tue Oct 11 10:30:59 2016 +0200 +++ b/cubicweb/server/rqlannotation.py Thu Oct 06 21:17:01 2016 +0200 @@ -40,22 +40,23 @@ stinfo['invariant'] = False stinfo['principal'] = _select_main_var(stinfo['rhsrelations']) continue - if not stinfo['relations'] and stinfo['typerel'] is None: - # Any X, Any MAX(X)... + if stinfo['typerel'] is None: # those particular queries should be executed using the system # entities table unless there is some type restriction - stinfo['invariant'] = True - stinfo['principal'] = None - continue - if (any(rel for rel in stinfo['relations'] if rel.r_type == 'eid' and rel.operator() != '=') - and not any(r for r in var.stinfo['relations'] - var.stinfo['rhsrelations'] - if r.r_type != 'eid' - and (getrschema(r.r_type).inlined or getrschema(r.r_type).final))): - # Any X WHERE X eid > 2 - # those particular queries should be executed using the system entities table - stinfo['invariant'] = True - stinfo['principal'] = None - continue + if not stinfo['relations']: + # Any X, Any MAX(X)... + stinfo['invariant'] = True + stinfo['principal'] = None + continue + if (any(rel for rel in stinfo['relations'] + if rel.r_type == 'eid' and rel.operator() != '=') + and not any(r for r in var.stinfo['relations'] - var.stinfo['rhsrelations'] + if r.r_type != 'eid' + and (getrschema(r.r_type).inlined or getrschema(r.r_type).final))): + # Any X WHERE X eid > 2 + stinfo['invariant'] = True + stinfo['principal'] = None + continue if stinfo['selected'] and var.valuable_references() == 1 + bool(stinfo['constnode']): # "Any X", "Any X, Y WHERE X attr Y" stinfo['invariant'] = False @@ -235,7 +236,7 @@ set_qdata(getrschema, subquery.query, noinvariant) for var in select.defined_vars.values(): if var.stinfo['invariant']: - if var in noinvariant and not var.stinfo['principal'].r_type == 'has_text': + if var in noinvariant: var._q_invariant = False else: var._q_invariant = True diff -r f5b815f67ce2 -r 22b854d3e8b2 cubicweb/server/sources/rql2sql.py --- a/cubicweb/server/sources/rql2sql.py Tue Oct 11 10:30:59 2016 +0200 +++ b/cubicweb/server/sources/rql2sql.py Thu Oct 06 21:17:01 2016 +0200 @@ -1374,20 +1374,17 @@ lhsvar = lhs.variable me_is_principal = lhsvar.stinfo.get('principal') is rel if me_is_principal: - if lhsvar.stinfo['typerel'] is None: - # the variable is using the fti table, no join needed - jointo = None - else: - # join on entities instead of etype's table to get result for - # external entities on multisources configurations - ealias = lhsvar._q_sqltable = '_' + lhsvar.name - jointo = lhsvar._q_sql = '%s.eid' % ealias - self._state.add_table('entities AS %s' % ealias, ealias) + jointo = None + if lhsvar.stinfo['typerel'] is not None: if not lhsvar._q_invariant or len(lhsvar.stinfo['possibletypes']) == 1: - restriction = " AND %s.type='%s'" % (ealias, self._state.solution[lhs.name]) + ealias = lhsvar._q_sqltable = '_' + lhsvar.name + jointo = lhsvar._q_sql = '%s.cw_eid' % ealias + self._state.add_table('cw_%s AS %s' % (self._state.solution[lhs.name], ealias), + ealias) else: - etypes = ','.join("'%s'" % etype for etype in lhsvar.stinfo['possibletypes']) - restriction = " AND %s.type IN (%s)" % (ealias, etypes) + subquery = ' UNION '.join('SELECT cw_eid FROM cw_%s' % etype + for etype in sorted(lhsvar.stinfo['possibletypes'])) + restriction = ' AND %s IN (%s)' % (lhsvar._q_sql, subquery) if isinstance(rel.parent, Not): self._state.done.add(rel.parent) not_ = True @@ -1546,19 +1543,10 @@ # since variable is invariant, we know we won't found final relation principal = variable.stinfo['principal'] if principal is None: + assert variable.stinfo['typerel'] is None vtablename = '_' + variable.name self._state.add_table('entities AS %s' % vtablename, vtablename) sql = '%s.eid' % vtablename - if variable.stinfo['typerel'] is not None: - # add additional restriction on entities.type column - pts = variable.stinfo['possibletypes'] - if len(pts) == 1: - etype = next(iter(variable.stinfo['possibletypes'])) - restr = "%s.type='%s'" % (vtablename, etype) - else: - etypes = ','.join("'%s'" % et for et in pts) - restr = '%s.type IN (%s)' % (vtablename, etypes) - self._state.add_restriction(restr) elif principal.r_type == 'has_text': sql = '%s.%s' % (self._state.fti_table(principal, self.dbhelper.fti_table), diff -r f5b815f67ce2 -r 22b854d3e8b2 cubicweb/server/test/unittest_rql2sql.py --- a/cubicweb/server/test/unittest_rql2sql.py Tue Oct 11 10:30:59 2016 +0200 +++ b/cubicweb/server/test/unittest_rql2sql.py Thu Oct 06 21:17:01 2016 +0200 @@ -496,9 +496,18 @@ WHERE _X.eid>12'''), ('Any X WHERE X eid > 12, X is Note', - """SELECT _X.eid -FROM entities AS _X -WHERE _X.type='Note' AND _X.eid>12"""), + """SELECT _X.cw_eid +FROM cw_Note AS _X +WHERE _X.cw_eid>12"""), + + ('Any X WHERE X eid > 12, X is IN (Bookmark, Card)', + """SELECT _X.cw_eid +FROM cw_Bookmark AS _X +WHERE _X.cw_eid>12 +UNION ALL +SELECT _X.cw_eid +FROM cw_Card AS _X +WHERE _X.cw_eid>12"""), ('Any X, T WHERE X eid > 12, X title T, X is IN (Bookmark, Card)', """SELECT _X.cw_eid, _X.cw_title @@ -802,13 +811,13 @@ FROM appears AS appears0 WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata'))"""), ('Personne X WHERE X has_text "toto tata"', - """SELECT DISTINCT _X.eid -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'"""), + """SELECT DISTINCT _X.cw_eid +FROM appears AS appears0, cw_Personne AS _X +WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata')) AND appears0.uid=_X.cw_eid"""), ('Personne X WHERE X has_text %(text)s', - """SELECT DISTINCT _X.eid -FROM appears AS appears0, entities AS _X -WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('hip', 'hop', 'momo')) AND appears0.uid=_X.eid AND _X.type='Personne' + """SELECT DISTINCT _X.cw_eid +FROM appears AS appears0, cw_Personne AS _X +WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('hip', 'hop', 'momo')) AND appears0.uid=_X.cw_eid """), ('Any X WHERE X has_text "toto tata", X name "tutu", X is IN (Basket,Folder)', """SELECT DISTINCT _X.cw_eid @@ -1560,14 +1569,19 @@ WHERE appears0.words @@ to_tsquery('default', 'toto&tata')"""), ('Personne X WHERE X has_text "toto tata"', - """SELECT _X.eid -FROM appears AS appears0, entities AS _X -WHERE appears0.words @@ to_tsquery('default', 'toto&tata') AND appears0.uid=_X.eid AND _X.type='Personne'"""), + """SELECT _X.cw_eid +FROM appears AS appears0, cw_Personne AS _X +WHERE appears0.words @@ to_tsquery('default', 'toto&tata') AND appears0.uid=_X.cw_eid"""), ('Personne X WHERE X has_text %(text)s', - """SELECT _X.eid -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'"""), + """SELECT _X.cw_eid +FROM appears AS appears0, cw_Personne AS _X +WHERE appears0.words @@ to_tsquery('default', 'hip&hop&momo') AND appears0.uid=_X.cw_eid"""), + + ('Any X WHERE X has_text "toto tata", X is IN (Basket,Folder)', + """SELECT appears0.uid +FROM appears AS appears0 +WHERE appears0.words @@ to_tsquery('default', 'toto&tata') AND appears0.uid IN (SELECT cw_eid FROM cw_Basket UNION SELECT cw_eid FROM cw_Folder)"""), ('Any X WHERE X has_text "toto tata", X name "tutu", X is IN (Basket,Folder)', """SELECT _X.cw_eid @@ -1579,9 +1593,9 @@ 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 -FROM appears AS appears0, appears AS appears2, entities AS _X, travaille_relation AS rel_travaille1 -WHERE appears0.words @@ to_tsquery('default', 'hip&hop&momo') AND appears0.uid=_X.eid AND _X.type='Personne' AND _X.eid=rel_travaille1.eid_from AND appears2.uid=rel_travaille1.eid_to AND appears2.words @@ to_tsquery('default', 'hip&hop&momo')"""), + """SELECT _X.cw_eid +FROM appears AS appears0, appears AS appears2, cw_Personne AS _X, travaille_relation AS rel_travaille1 +WHERE appears0.words @@ to_tsquery('default', 'hip&hop&momo') AND appears0.uid=_X.cw_eid AND _X.cw_eid=rel_travaille1.eid_from AND appears2.uid=rel_travaille1.eid_to AND appears2.words @@ to_tsquery('default', 'hip&hop&momo')"""), ('Any X ORDERBY FTIRANK(X) DESC WHERE X has_text "toto tata"', """SELECT appears0.uid @@ -1590,15 +1604,15 @@ ORDER BY ts_rank(appears0.words, to_tsquery('default', 'toto&tata'))*appears0.weight DESC"""), ('Personne X ORDERBY FTIRANK(X) WHERE X has_text "toto tata"', - """SELECT _X.eid -FROM appears AS appears0, entities AS _X -WHERE appears0.words @@ to_tsquery('default', 'toto&tata') AND appears0.uid=_X.eid AND _X.type='Personne' + """SELECT _X.cw_eid +FROM appears AS appears0, cw_Personne AS _X +WHERE appears0.words @@ to_tsquery('default', 'toto&tata') AND appears0.uid=_X.cw_eid ORDER BY ts_rank(appears0.words, to_tsquery('default', 'toto&tata'))*appears0.weight"""), ('Personne X ORDERBY FTIRANK(X) WHERE X has_text %(text)s', - """SELECT _X.eid -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' + """SELECT _X.cw_eid +FROM appears AS appears0, cw_Personne AS _X +WHERE appears0.words @@ to_tsquery('default', 'hip&hop&momo') AND appears0.uid=_X.cw_eid ORDER BY ts_rank(appears0.words, to_tsquery('default', 'hip&hop&momo'))*appears0.weight"""), ('Any X ORDERBY FTIRANK(X) WHERE X has_text "toto tata", X name "tutu", X is IN (Basket,Folder)', @@ -1612,9 +1626,9 @@ ORDER BY 2) AS T1"""), ('Personne X ORDERBY FTIRANK(X),FTIRANK(S) WHERE X has_text %(text)s, X travaille S, S has_text %(text)s', - """SELECT _X.eid -FROM appears AS appears0, appears AS appears2, entities AS _X, travaille_relation AS rel_travaille1 -WHERE appears0.words @@ to_tsquery('default', 'hip&hop&momo') AND appears0.uid=_X.eid AND _X.type='Personne' AND _X.eid=rel_travaille1.eid_from AND appears2.uid=rel_travaille1.eid_to AND appears2.words @@ to_tsquery('default', 'hip&hop&momo') + """SELECT _X.cw_eid +FROM appears AS appears0, appears AS appears2, cw_Personne AS _X, travaille_relation AS rel_travaille1 +WHERE appears0.words @@ to_tsquery('default', 'hip&hop&momo') AND appears0.uid=_X.cw_eid AND _X.cw_eid=rel_travaille1.eid_from AND appears2.uid=rel_travaille1.eid_to AND appears2.words @@ to_tsquery('default', 'hip&hop&momo') ORDER BY ts_rank(appears0.words, to_tsquery('default', 'hip&hop&momo'))*appears0.weight,ts_rank(appears2.words, to_tsquery('default', 'hip&hop&momo'))*appears2.weight"""), @@ -2030,9 +2044,9 @@ WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('hip', 'hop', 'momo'))"""), ('Personne X WHERE X has_text "toto tata"', - """SELECT DISTINCT _X.eid -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'"""), + """SELECT DISTINCT _X.cw_eid +FROM appears AS appears0, cw_Personne AS _X +WHERE appears0.word_id IN (SELECT word_id FROM word WHERE word in ('toto', 'tata')) AND appears0.uid=_X.cw_eid"""), ('Any X WHERE X has_text "toto tata", X name "tutu", X is IN (Basket,Folder)', """SELECT DISTINCT _X.cw_eid @@ -2159,13 +2173,13 @@ FROM appears AS appears0 WHERE MATCH (appears0.words) AGAINST ('toto tata' IN BOOLEAN MODE)"""), ('Personne X WHERE X has_text "toto tata"', - """SELECT _X.eid -FROM appears AS appears0, entities AS _X -WHERE MATCH (appears0.words) AGAINST ('toto tata' IN BOOLEAN MODE) AND appears0.uid=_X.eid AND _X.type='Personne'"""), + """SELECT _X.cw_eid +FROM appears AS appears0, cw_Personne AS _X +WHERE MATCH (appears0.words) AGAINST ('toto tata' IN BOOLEAN MODE) AND appears0.uid=_X.cw_eid"""), ('Personne X WHERE X has_text %(text)s', - """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'"""), + """SELECT _X.cw_eid +FROM appears AS appears0, cw_Personne AS _X +WHERE MATCH (appears0.words) AGAINST ('hip hop momo' IN BOOLEAN MODE) AND appears0.uid=_X.cw_eid"""), ('Any X WHERE X has_text "toto tata", X name "tutu", X is IN (Basket,Folder)', """SELECT _X.cw_eid FROM appears AS appears0, cw_Basket AS _X diff -r f5b815f67ce2 -r 22b854d3e8b2 cubicweb/server/test/unittest_rqlannotation.py --- a/cubicweb/server/test/unittest_rqlannotation.py Tue Oct 11 10:30:59 2016 +0200 +++ b/cubicweb/server/test/unittest_rqlannotation.py Thu Oct 06 21:17:01 2016 +0200 @@ -182,7 +182,7 @@ def test_greater_eid_typed(self): with self.session.new_cnx() as cnx: rqlst = self._prepare(cnx, 'Any X WHERE X eid > 5, X is Note') - self.assertEqual(rqlst.defined_vars['X']._q_invariant, True) + self.assertEqual(rqlst.defined_vars['X']._q_invariant, False) def test_max_eid(self): with self.session.new_cnx() as cnx: