Are views supported?

[caseyw]

We've adopted a CakePHP project which uses quite a number of views.

It doesn't have migrations currently, so we're in the process of setting them up. I don't see any View support though.

I saw an old PR adding that functionality, so I doubt it exists now. If not, is there a Phinx standard approach to setting up views?

Thanks for your time!

[Bilge]

I believe they are not explicitly supported, which means you would need to write raw SQL for view migrations. Since the API for views would be similar to tables, it follows they could and probably should be supported the same way.

[oresch]

Are there any ideas to go forward with this?

[MasterOdin]

How about something like this for an API:

/**
 * @param string $viewName the name for the view
 * @param string $viewQuery the query to use for the view
 * @param array $options optional options for the view
 *   $options = [
 *      'columns' => (array) array of column names for the view
 *      'type' => (string) type of view, e.g. temporary or materialized
 *      'security_barrier' => (boolean) postgresql only
 *      'algorithm' => (string) mysql only, algorithm to use, e.g. merge or temptable
 *      'definer' => (string) mysql only, username to use as definer
 *      'sql_security' => (string) mysql only, definer or invoker
 *      'encryption' => (boolean) sqlserver only
 *      'schemabinding' => (boolean) sqlserver only
 *      'view_metadata' => (boolean) sqlserver only
 *   ];
 */
function view(string $viewName, string $viewQuery, array $options = []) {
    // mimic $this->table function
}

Can probably forgo much of the options beyond columns and type for the first draft implementation of this, just to get something into phinx.

[Bilge]

No, just write SQL. SQL wrappers are a complete waste of time.

[MasterOdin]

Having a wrapper would not prevent you from writing the raw SQL instead, same as the $this->table construct?

Unless this is a broader commentary that phinx should just rip out all sql wrappers.

[Bilge]

I understand it would not be preventative, but what I am suggesting is you do not waste time implementing a PHP API for SQL when you can, and should, just write SQL.

[dereuromark]

The whole idea of phinx is an opt-in abstraction People are free to use either that or plain SQL.

I don't think having this implemented takes any options from the table as it is right now.

[Bilge]

Disagree. The whole pint of Phinx is a database migrations framework. You can (and should) strip away all the SQL wrapping API and nothing of value would be lost. On the contrary, the library would be much more maintainable.

[dereuromark]

Yes, and a framework is by its definition an abstraction of low level tasks :)

[Bilge]

Wrapping SQL has nothing to do with the migrations objective.

[dereuromark]

So, anyone wanting to make a PR here? That we can discuss the details then more closely with some actual code maybe?