Migrations database:reverse is not generating some objects on postgres

[jmar1998]

The command database:reverse is not generating several objects inside my postgres database, atm I found out that views ,enums columns and materialized_views are not being generated on my schema.xml.

Is this inside of the scope of the project ?
If not, would you accept a PR ?

[mringler]

I don't think postgres (or any other DBMS) gives metadata for view columns, so it is not possible to automatically generate schema information.
Apart from that, Propel does not have a concept of views and does not manage them. I think the idea is to replace views and materialized views on database level with similar structures on ORM level.

But I guess it is possible to add a view manually in schema.xml as a table with the skipSql flag, which should give you model and query classes. Depending on the view, multiple inheritance might work well, too.

Considering the lack of metadata, I don't see how this can be improved, but if you have something in mind, PRs (and discussions) are always welcome.

As to the enum columns, I'm afraid I don't even know if or how they are supported by Propel.

[jmar1998]

Im actually using propel on my project not directly for the models, but for the migrations system.

We can actually fetch the meta data from pg_catalog;
// Get views definition
select * from pg_views;
// Get materizalied views definition
select * from pg_matviews;
// Get types (not needed directly)
select * from pg_type;
// Get enums (not needed directly)
select * from pg_enum;
// Get enums relevant information
select typname, enumsortorder, enumlabel from pg_type INNER JOIN pg_enum on pg_enum.enumtypid = pg_type.oid where typname = '<enum_name>';

Im talking about this objects, cuz i think is very important for the schema.xml be an abstraction of database;

I actually like the approach of changing the schema and generating the migrations through the diff command.

What do you think @mringler ?

[mringler]

Ah, seems like we are talking about two different things. When you just want to manage the raw SELECT statement of views, pg_views gives you those. But for model/query building, you need the column data, i.e. type, length, not null, etc. For example, Propel uses information_schema.columns when migrating table columns:

        SELECT
            column_name,
            data_type,
            column_default,
            is_nullable,
            numeric_precision,
            numeric_scale,
            character_maximum_length
        FROM information_schema.columns
        WHERE ...

I don't think similar data is available for views. Though you can do \d <viewname> in postgres, it gives you

  Column   |          Type          | Collation | Nullable | Default 
-----------+------------------------+-----------+----------+---------
 title     | character varying      |           |          | 
 last_name | character varying(128) |           |          | 

which should suffice to create simple skipSql tables for views during reverse schema import. Having those would somewhat help to access view data from within Propel. But it does nothing for view version management.

For version management alone, it might be enough to put the view's SELECT statement into tags, as long as you figure out a way to resolve syntax variations (i.e. a.id = b.id vs (a.id = b.id)). But I am not sure if this approach fits into Propel, when it manages a view, but cannot create queries or model data for it. To be honest, I am not enthusiastic about the idea, though maybe other people are.
And considering how adjusting for syntax variations alone is a nightmare, I am not sure if this approach is feasible at all.