[Cubicweb] SQL generation problem with MSSQL backend

Sylvain Thénault sylvain.thenault at logilab.fr
Mon Nov 29 13:09:37 CET 2010


On 26 novembre 08:52, Alexandre Fayolle wrote:
> Hi everyone,

Hi,
 
> I spent ~4hours yesterday evening with Aurélien hunting down a bug which we 
> detected on a test instance running over Sql Server 2005 (it turns out this is 
> significant). I'll skip over the details and jump to the conclusions. 
> 
> First point is that editing cubicweb/server/__init__.py and changing the value 
> of DEBUG to 3 helped tremendously in understanding the problem. 

For the record, the intended usage is:

from cubicweb.server import set_debug, debugged

with debugged('DBG_SQL|DBG_RQL'):
  # code with SQL and RQL debugging activated

set_debug('DBG_SQL|DBG_RQL')
# code with SQL and RQL debugging activated
set_debug(None)
# no more debug

In a CubicWebTC instance, no need to import, simply call self.set_debug or self.debugged.

[snip context]
 
> SELECT _X.cw_eid, 
>        _X.cw_data_type, 
>        _X.cw_unit, 
>        _X.cw_granularity, 
>        _X.cw_start_date, 
>        _T0.C0, 
>        _T0.C1, _X.cw_modification_date
> FROM cw_CWUser AS _B, 
>      cw_TimeSeries AS _X LEFT OUTER JOIN 
>      (SELECT _AE.cw_eid AS C0, 
>              _AE.cw_modification_date AS C1
>       FROM cw_Case AS _AE
>       WHERE EXISTS(
>           SELECT 1 
>           FROM require_permission_relation AS rel_require_permission0,
>                cw_CWPermission AS _D, 
>                has_group_permission_relation AS rel_has_group_permission1
>           WHERE rel_require_permission0.eid_from=_AE.cw_eid 
>             AND rel_require_permission0.eid_to=_D.cw_eid 
>             AND _D.cw_name IN(%(84285504)s, %(84285552)s, %(84285600)s) 
>             AND rel_has_group_permission1.eid_from=%(A)s 
>             AND rel_has_group_permission1.eid_to=_D.cw_eid
>           )
>        ) AS _T0 ON (_X.cw_rcase_of=_T0.C0), 
>        ts_variant_relation AS rel_ts_variant0
> WHERE rel_ts_variant0.eid_from=%(x)s 
>   AND rel_ts_variant0.eid_to=_X.cw_eid 
>   AND _B.cw_eid=%(C)s 
>   AND EXISTS(
>         SELECT 1 
>         FROM require_permission_relation AS rel_require_permission1,  
>              cw_CWPermission AS _A, 
>              has_group_permission_relation AS rel_has_group_permission2 
>         WHERE rel_require_permission1.eid_from=_X.cw_eid 
>           AND rel_require_permission1.eid_to=_A.cw_eid 
>           AND _A.cw_name IN(%(83779088)s, %(83778944)s, %(83778752)s) 
>           AND rel_has_group_permission2.eid_from=%(C)s 
>           AND rel_has_group_permission2.eid_to=_A.cw_eid
>         )
> ORDER BY 2,7 DESC 
> {u'A': 2673, '84285552': u'modeller', u'C': 2673, 
> '84285600': u'pfolio_manager', '83778752': u'pfolio_manager', '84285504': 
> u'operator', 'x': 3227, '83779088': u'operator', '83778944': u'modeller'} 
> <pyodbc.Connection object at 0x04E8FDE0>
>   --> []

I fail to see anything wrong with this query. The only thing is an extra join
on CWUser table that could be avoided, but I don't think it relates to the
pb. IMO you should create a simple test database with minimal data inside,
then run manually parts of this query to isolate the pb and see if the pb
lies in the generated sql or in the dbms. But it seems that mssql doesn't 
like the outer join in that case...

I'm afraid I can't help you more on this without a sql shell on the database.
-- 
Sylvain Thénault                               LOGILAB, Paris (France)
Formations Python, Debian, Méth. Agiles: http://www.logilab.fr/formations
Développement logiciel sur mesure:       http://www.logilab.fr/services
CubicWeb, the semantic web framework:    http://www.cubicweb.org




More information about the Cubicweb mailing list