Views as schema objects that can be created and dropped

Issue #812 new
Anonymous created an issue

I'd like to ask for ability to define views as schema items, and that they be included in metadata.create_all and drop_all.

So i could do something like this:

v = View(name, metadata, selectable) or
v = View(name, metadata, autoload=True). and then use v as any other selectable.

I think what's needed would be a visit_view in DDLSchemaGenerator and dropper, but i'm not proficient enough at SQLAlchemy internals to implement this myself.

Comments (10)

  1. Michael Bayer repo owner

    autoload=True implies reflection support for views as well. This ticket would be a significant undertaking from an implementation and testing point of view, particularly for reflection. table reflection takes up a huge amount of support time as it is, for a feature which is essentially an unnecessary "convenience" feature... so I'm hesitant to add more support burden in that department. its not trivial to add it on the create side either since the View object would represent a new addition to the schema package along the lines of complexity of Table itself; a lot of tests would be needed for to support a new schema object at that level.

    DDL/reflection support of views has historically been out of SA's scope, since we are not a DDL management tool and views are not a supported feature on many of the databases we support.

    the only key problem we have right now with views is that defining a Table with a view name means create_all() will attempt to create that view as a table, so i think a simple flag to table "creatable=False" should probably be added to address that use case.

  2. Tyler Barrus

    I am using the view recipe in postgres with an assigned schema. It seems that the recipe does not take into account the schema information. The view was created in the public schema and I have not been able to figure out how to get it to read and utilize the metadata.schema information. Any help would be much appreciated.

    This is on SQLAlchemy 1.0.13

  3. Michael Bayer repo owner

    it doesn't, but this should be an easy add, stick the "schema" you care about like this:

    def view(name, metadata, selectable):
        t = table(name)
        for c in selectable.c:
        CreateView(name, metadata.schema, selectable).execute_at('after-create', metadata)
        DropView(name, metadata.schema).execute_at('before-drop', metadata)
        return t

    then add a "schema" argument to CreateView and DropView and use it in the @Mark Espinoza recipe.

  4. Tyler Barrus

    Thank you for your help! I added it into my local version of the recipe. Thank you for pointing me in the right direction!

    Will the view recipe be added directly to SQLAlchemy in the future?

  5. Fredrik Blomqvist

    What's the current status on native view support as schema objects? Is the current development documented anywhere? I've noticed there is support for various parts of it in the code, just not utilized yet, so I'm guessing it's still on the roadmap?

  6. Michael Bayer repo owner

    the current status is indefinite. AFAIK the recipe works well for people. adding the feature would be nice but that adds lots more workload for me since it means choosing an exact API, dealing with bugs in the feature, dealing with API shortcomings, etc. if I could ever solve the problem of project members helping with bug triage, code review, etc. that would make it easier for me to expand the scope of SQLAlchemy's features. right now I have a whole slate of 1.3 features that don't have workarounds and they are mostly higher priority.

  7. Log in to comment