Addition of views to SQLAlchemy is an extremely long term issue that has never been done, largely because we have a recipe that works well enough for most cases, which you have probably seen, at https://github.com/sqlalchemy/sqlalchemy/wiki/Views. The issue itself is https://github.com/sqlalchemy/sqlalchemy/issues/812, keep in mind all those comments are 13 years old.
A real View object would feature the "materialized" part as just a boolean flag since AFAICT the "materialized" aspect of it only presents itself as a keyword to the user and possibly some other options, the rest of what goes on has to do with the internals of the database. The class hierarchy would be the same as that of Table: class View(DialectKWArgs, SchemaItem, TableClause): and the object would need to act pretty much like Table in just about every way, including the metadata support, autoload, etc. Basically it would have an extra field indicating the SELECT source of the data, which based on modern conventions would be presented either as a select() object against other Table objects, or as a text() construct. on the DDL side, a command like metadata.create_all() / metadata.drop_all() would need to be smart about when it creates / drops these views. views are first and foremost dependent on the Table objects that they select against. However, they can also be dependent on each other, as you can make views from views. so a full create/drop sequence would likely need to assemble this list of dependencies in the same way that Table objects are dependent on each other via foreign key relationship, and then ensure creates/drops are done in the right order. There is a lot of infrastructure in lib/sqlalchemy/sql/ddl.py that does work like this which should be leveraged here. The good news for views is that reflection is actually already implemented for views in that they come back as Table objects, and even the "view definition" can be retrieved however that's not integrated into the schema system. So there's a lot to think about here and overall views haven't happened because there is a lot to consider in terms of a fully complete feature that requires no special user intervention, especially the DDL / dependency issue, whereas the recipe plus the reflection support has been most of what people need. If you have proposals for how things would look then you can use issue 812 for notes. On Tue, Apr 28, 2020, at 12:05 PM, Ethan Ralph wrote: > Hello. I am currently working on an implementation of PostgreSQL materialized > views in SQLAlchemy and Alembic. To give a basic explanation, materialized > views are regular views but all the rows are computed statically, and you can > refresh them with a REFRESH MATERIALIZED VIEW query. > I've attached the code I have so far. It uses sqlalchemy.sql.table and column > proxies to simulate a table, and also implements Alembic operations with > autogenerate. I want to make MaterializedView into a proper class like Table. > What would be the best way to go about this? What should I inherit from? > Which methods should I override? Any input is appreciated. > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/ee389a85-cba5-4773-9862-a412aa0e67ca%40googlegroups.com > > <https://groups.google.com/d/msgid/sqlalchemy/ee389a85-cba5-4773-9862-a412aa0e67ca%40googlegroups.com?utm_medium=email&utm_source=footer>. > > > *Attachments:* > * _materialized_view.py -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/dd32137d-4400-4c4f-b6cc-90d2a20c8cb6%40www.fastmail.com.
