issue with pagination of security tables on MSSQL

giordyb commented 5 years ago

Environment

Flask-Appbuilder version: 2.1.13

pip freeze output: apispec==1.3.3 asn1crypto==0.24.0 attrs==19.1.0 Babel==2.7.0 certifi==2019.6.16 certsrv==2.1.1 cffi==1.12.3 chardet==3.0.4 Click==7.0 colorama==0.4.1 cryptography==2.7 cyberpandas==1.1.1 defusedxml==0.6.0 Flask==1.1.1 Flask-AppBuilder==2.1.13 Flask-Babel==0.12.2 Flask-JWT-Extended==3.23.0 Flask-Login==0.4.1 Flask-OpenID==1.2.5 Flask-SQLAlchemy==2.4.0 Flask-WTF==0.14.2 gitdb2==2.0.5 GitPython==3.0.2 idna==2.8 itsdangerous==1.1.0 Jinja2==2.10.1 jsonschema==3.0.2 MarkupSafe==1.1.1 marshmallow==2.19.5 marshmallow-enum==1.5.1 marshmallow-sqlalchemy==0.19.0 ntlm-auth==1.4.0 numpy==1.17.2 pandas==0.25.1 prison==0.1.2 ptvsd==4.3.2 pyasn1==0.4.7 pyasn1-modules==0.2.6 pycparser==2.19 PyJWT==1.7.1 pymssql==2.1.4 pyOpenSSL==19.0.0 pyrsistent==0.15.4 python-dateutil==2.8.0 python-ldap==3.1.0 python3-openid==3.1.0 pytz==2019.2 PyYAML==5.1.2 requests==2.22.0 requests-ntlm==1.1.0 six==1.12.0 smmap2==2.0.5 SQLAlchemy==1.3.8 SQLAlchemy-Utils==0.34.2 urllib3==1.25.3 Werkzeug==0.15.6 wincertstore==0.2 WTForms==2.2.1

Describe the expected results

Flask Appbuilder with DB on MSSQL

when switching pages on any of the security tables (users, views), i should get the next page

Describe the actual results

i get the following error: sqlalchemy.exc.CompileError: MSSQL requires an order_by when using an OFFSET or a non-simple LIMIT clause

The issue is that tables on MSSQL require the base_order to be set, I can do that on custom tables defined in the model.py file but it needs to be done on the system tables as well to be compatible with MSSQL

see issue https://github.com/dpgaspar/Flask-AppBuilder/issues/894

dpgaspar commented 5 years ago

MSSQL... probably to solve this I have to create a new MSSQL exception.

Meanwhile you can override all the security ModelViews from FAB and add a base_order attr. Take a look at: https://github.com/dpgaspar/Flask-AppBuilder/blob/master/examples/extendsecurity/app/sec.py#L13

Any further help fell free to ask

giordyb commented 5 years ago

Hi, What about adding the base_order (perhaps by the ID) to the built in views? would it negatively affect other DB backends?

giordyb commented 5 years ago

if anybody is interested this is what I have done: in the 'app' folder I create a file sec.py with the following overrides

from flask_appbuilder.security.sqla.manager import SecurityManager

class MSSQLViewMenuModelView (ViewMenuModelView):
    base_order = ('id','asc')

class MSSQLRoleModelView (RoleModelView):
    base_order = ('id','asc')

class MSSQLPermissionModelView (PermissionModelView):
    base_order = ('id','asc')

class MSSQLPermissionViewModelView (PermissionViewModelView):
    base_order = ('id','asc')

class MSSQLUserLDAPModelView (UserLDAPModelView):
    base_order = ('id','asc')

class MSSQLUserDBModelView (UserDBModelView):
    base_order = ('id','asc')

class MSSQLSecurityManager(SecurityManager):
    viewmenumodelview = MSSQLViewMenuModelView
    rolemodelview = MSSQLRoleModelView
    permissionmodelview = MSSQLPermissionModelView
    permissionviewmodelview = MSSQLPermissionViewModelView
    userldapmodelview = MSSQLUserLDAPModelView
    userdbmodelview = MSSQLUserDBModelView

then in the __init__.py i added the import:

from .sec import MSSQLSecurityManager

and changed the appbuilder initialization

appbuilder = AppBuilder(app, db.session, indexview=MyIndexView, security_manager_class=MSSQLSecurityManager)

perhaps it can be added to the documentation as a work-around.

thanks,

Giordano

dpgaspar commented 5 years ago

Top, glad you have managed to make a workaround. Yet note this is not forgotten I'm currently working on a fix for it.

I've improved tests so that they run on different DB engines, currently with MySQL, PostgreSQL and MSSQL, and next release will include a fix for this issue.

Thank you for reporting this!

dpgaspar / Flask-AppBuilder