Support views in migrations

[josevalim]

The idea is to introduce functions called view/1 and view/2 such that:

create view(:my_view, from(u in "users", where: u.type == :student))
drop view(:my_view)

create requires a query to be given to the view, drop does not.

We will also introduce a create_or_replace/1 function, similar to create/1, but mean to be used with views.

[OvermindDL1]

Definitely want that, I have a few view's in my system right now but I setup and tear them down via SQL.

[lukerollans]

Will be keeping an eye on this. The frontend to one of our biggest apps relies fairly heavily on views, would be very nice to manage them natively

[joshuataylor]

What about materialized views?

[josevalim]

@joshuataylor we can support a :materialized option in view/3 or a materialized_view/2 function as well. I will need to study the behaviour in different databases before deciding which one works best.

[eniolopes]

can i work on this one?

[josevalim]

Yes, please!

[hauleth]

I was just thinking about introducing it by myself. What would be useful with this one is:

• triggers
• functions

As it would allow us to create additional things that would be useful with views.

[michalmuskala]

We should carefully consider if we're able in any way to provide something more usable than execute(SQL).
I'm not sure I can imagine providing something more than

create function("""
some sql
""")

and I'm not sure I see advantage over

execute """
CREATE FUNCTION some sql
"""

[hauleth]

@michalmuskala there is one: autocreate down method for rollbacks. However there is only a little (if any) advantage of doing that. Maybe except allowing to reference function types using Ecto.Type.type/0 which would be handy.

[roehst]

Hi, I want to start contributing to Ecto (and Elixir in general) and I was about to suggest this feature. Is anyone working on it? Is it still in discussion? I am new to open source projects so forgive me if I don't understand part of the workflow on this. Thanks!

[josevalim]

I don't think there is someone working on it right now. If you want to start contributing on this area, maybe #1832 is a better starting point, for you to get acquainted with the codebase. :)

[roehst]

@josevalim Nice, I will try that. Tks

[josevalim]

We have stepped down from this feature because using Ecto.Query to generate views had unwarranted side-effects. A discussion of other approaches, which could be encapsulated in helpers that will be part of future Ecto versions, can be found in #1805.

[KristerV]

Sooo, currently the only way is still execute()? I've already used the from() syntax to create my 20 queries, any way to put those into the execution string?

[josevalim]

@KristerV you can use Repo.to_sql to get those queries as SQL statements. But it is probably best if you still pass them as strings.

[KristerV]

Yeah I realized, that your migrations need to be static (and not connected to any schemas or anything), so best is raw strings anyway. For future lurkers :)