[Cubicweb] SQL generation problem with MSSQL backend

Alexandre Fayolle alexandre.fayolle at logilab.fr
Fri Nov 26 08:52:15 CET 2010


Hi everyone,

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. 

Some context. The instance has 2 entities TSBox and TimeSeries, and they are 
linked by the ts_variant relation which bears some security rules. The 
TimeSeries also have an optional inlined relation rcase_of to a Case entity 
which bears other security restrictions. 

tsvariant_permissions = {
    'read': ('users', 'managers'),
    'add': ('managers', 'modellers', 'pfolio_managers',
            sexpr('modeller', 'pfolio_manager'),
            RRQLExpression('S volatile True, '
                           'S require_permission P, P name "operator", '
                           'U has_group_permission P')),
    'delete': ('managers', 'modellers', 'pfolio_managers', 'operators',
               RRQLExpression('U has_delete_permission S OR '
                              '(U has_delete_permission O, S volatile True)'))
    }

rcase_of_permissions = {
    'read': ('users', 'managers'),
    'add': ('managers', 'modellers', 'pfolio_managers',
            sexpr('modeller', 'pfolio_manager')),
    'delete': ('managers', 'modellers', 'pfolio_managers',
               RRQLExpression('U has_delete_permission O'))
    }


The entity class for TimeSeries has a fetch_config class attribute telling to 
fetch several attributes and the rcase_of Case. 

When a TSBox is displayed, one of the TimeSeries is fetched. This works fine 
with a postgresql backend, but fails (i.e. return an empty result set) with a 
MSSQL backend if the require_permission bits of the security come into play. 

If I remove rcase_of from the fetch_config of TimeSeries, MSSQL manages to 
return the correct entity. The rcase_of is set, and we can retrieve it later 
without problems. 

Here's the relevant output (reformatted for view using a fixed-width font). 
Any hint at what could cause MSSQL to choke on this is very welcome. 


with rcase_of in the fetch_config
=================================

querier input
-------------

Any X,AA,AB,AC,AD,AE,AF,AG ORDERBY AA ASC,AF DESC 
WHERE E eid %(x)s, E ts_variant X, X data_type AA, 
      X unit AB, X granularity AC, X start_date AD, 
      X rcase_of AE?, AE modification_date AF, 
      X modification_date AG 
{'x': 3227}

rql for system source
---------------------

Any X,AA,AB,AC,AD,AE,AF,AG ORDERBY AA,AF DESC 
WHERE E eid %(x)s, E ts_variant X, X data_type AA, X unit AB, 
      X granularity AC, X start_date AD, X rcase_of AE?, 
      X modification_date AG, B eid %(C)s, 
      EXISTS(X require_permission A, 
             A name IN("operator", "modeller", "pfolio_manager"), 
             B has_group_permission A, A is CWPermission), 
      X is TimeSeries 
WITH AE,AF BEING (Any AE,AF WHERE AE modification_date AF, 
                  EXISTS(AE require_permission D, 
                         D name IN("operator", "modeller", "pfolio_manager"),        
                         %(A)s has_group_permission D), 
                  AE is Case)

exec
----

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>
  --> []

without rcase_of in the fetch_config
====================================

querier input 
-------------

Any X,AA,AB,AC,AD,AE ORDERBY AA ASC 
WHERE E eid %(x)s, E ts_variant X, X data_type AA, X unit AB, 
      X granularity AC, X start_date AD, 
      X modification_date AE 
{'x': 3227}

rql for system source
---------------------

Any X,AA,AB,AC,AD,AE ORDERBY AA 
WHERE %(x)s ts_variant X, X data_type AA, X unit AB, X granularity AC, 
      X start_date AD, X modification_date AE, 
      EXISTS(X require_permission A, 
             A name IN("operator", "modeller", "pfolio_manager"), 
             %(C)s has_group_permission A, A is CWPermission), 
      X is TimeSeries

exec 
----

SELECT _X.cw_eid, _X.cw_data_type, _X.cw_unit, _X.cw_granularity, 
       _X.cw_start_date, _X.cw_modification_date
FROM cw_TimeSeries AS _X, 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 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(%(83704736)s, %(83704640)s, %(83704592)s) 
          AND rel_has_group_permission2.eid_from=%(C)s 
          AND rel_has_group_permission2.eid_to=_A.cw_eid
       )
ORDER BY 2 
{'x': 3227, u'C': 2673, '83704640': u'modeller', '83704592': 
u'pfolio_manager', '83704736': u'operator'} 
<pyodbc.Connection object at 0x04E8FDE0>
  --> [[3228, u'Float', u'GW', u'constant', datetime.datetime(1904, 1, 1, 0, 
0), datetime.datetime(2010, 11, 25, 21, 9, 37, 500000)]]



-- 
Alexandre Fayolle                              LOGILAB, Paris (France)
Formations Python, CubicWeb, Debian :  http://www.logilab.fr/formations
Développement logiciel sur mesure :      http://www.logilab.fr/services
Informatique scientifique:               http://www.logilab.fr/science



More information about the Cubicweb mailing list