Views as schema objects that can be created and dropped

[Former user]

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.

[Mike Bayer]

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.

[Mike Bayer]

view support for now is at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/Views . We also support reflecting the columns of a view as Table objects, and we have have a feature that can reflect the view's definition. all the components are there though they've yet to be assembled into a coherent feature with tests.

[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

[Mike Bayer]

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:
        c._make_proxy(t)

    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 @compiles recipe.

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

[Mike Bayer]

feel free to edit the wiki I can correct any issues after the fact