Views

[grosch]

Please provide a way to create database views.

[MrMage]

See also vapor/fluent#493.

[grosch]

Here's an even better example of why I want this. To get the data for one of my Leaf views I'm currently using this view from the database. Would you want to try and write that in fluent?

CREATE VIEW public.v_orders AS
WITH cte AS (
	SELECT o.id order_id, SUM(w.amount * pt.amount * (CASE WHEN pt.abbrev = 'ud' THEN 1.0 ELSE o.station_multiplier END)) complete
	FROM work_completed w
	INNER JOIN order_points op ON w.order_point_id = op.id
	INNER JOIN points pt ON op.points_id = pt.id
	INNER JOIN orders o ON op.order_id = o.id
	GROUP BY o.id
)
SELECT o.id,
    c.name AS client_name,
    s.name AS station_name,
    o.created,
    o.due,
    o.month,
    o.comments,
    o.name AS order_name,
    o.station_multiplier,
    o.total_points,
    COALESCE(cte.complete, 0) AS produced,
    COALESCE(cte.complete, 0) >= o.total_points AS complete
FROM orders o
LEFT OUTER JOIN cte ON cte.order_id = o.id
JOIN stations s ON o.station_id = s.id
JOIN clients c ON s.client_id = c.id;

[tanner0101]

@grosch how would you imagine this working through Fluent? If you could share some ideas for what the API might look like that would be great. TBH I haven't really used views much before. Do you need to create them per connection or are they saved to the database?

I imagine we could create a sub-protocol of Model that helps define models that are backed by views instead of tables.

[grosch]

Views are saved in the database just like a table. It's basically just a stored "select" statement so you don't have to rewrite it all the time. I think there's a way to insert via a view, but I've never done that, just the query.

On a model you've got the Database.create() call where it'll create the table. We would just need some way to give a view definition (like above) so that it only ran a single time and didn't try to keep recreating it over and over.

For the view example I showed above I have this in my Xcode project:

import FluentPostgreSQL
import Vapor

final class ViewOrder: Codable {
    static let entity = "v_orders"

    var id: Int? = nil
    var clientName = ""
    var stationName = ""
    var created = Date()
    var due = Date()
    var month = Date()
    var comments: String? = nil
    var name = ""
    var stationMultiplier = 0
    var totalPoints = 0.0
    var produced = 0.0
    var complete = false

    enum CodingKeys: String, CodingKey {
        case id, created, due, month, comments, complete, produced
        case clientName = "client_name"
        case stationName = "station_name"
        case name = "order_name"
        case stationMultiplier = "station_multiplier"
        case totalPoints = "total_points"
    }
}

extension ViewOrder: PostgreSQLModel {}

I had to assign default values to everything because of the constructor, which was a bit annoying, but seems to work OK. The only trick, which @MrMage showed me, was that in configure.swift I also had to do this:

    ViewOrder.defaultDatabase = DatabaseIdentifier<PostgreSQLDatabase>.psql

which seemed to be required because there was no Migration to run, and so it didn't associate it with a database. So I'm honestly not seeing any need/value for a new protocol, just a way to tell vapor to create the view if it doesn't exist.

Obviously the person could just go into the database and do it directly, but it's better to have it as part of the Vapor project I think so that if you deploy to a new box down the road you don't have to remember to do it.

[grosch]

I guess you could have a DBView type protocol that required a static func viewCreateCommand() -> String and then in that they'd return the string, like I showed above. Then, in configure.swift, you'd do something like:

let db = ....
db.configureViews([ViewOrder.self, ViewSomethingElse.self])

and it would thus expect an array of DBView objects, and would call the viewCreateCommand on them if those names or whatever didn't exist in the database's fluent table.

[grosch]

Or, to be more consistent, you could add another method to the builder:

extension ViewOrder: Migration {
    static func prepare(on connection: PostgreSQLConnection) -> Future<Void> {
        return Database.create(self, on: connection) { builder in
            try builder.createView(
                """
                WITH cte AS (
                     ....
                JOIN clients c ON s.client_id = c.id
                """
            )
        }
    }
}

[MrMage]

Or, to be more consistent, you could add another method to the builder:

At that point I guess one could just skip using the builder and execute raw SQL to create the View via the connection instead of using Database.create.

[grosch]

I think it would have to be inside Database.create so that it only ran the one time. But I'm happy to do it however you guys say is the right way.

[MrMage]

AFAIK, Database.create is specifically for creating a schema (currently only tables). The entire migration method will only be called once (I think), so you could just put custom SQL that creates the view into the migration method of your view class.

[foobarzap]

Just a note: view integration might not be as easy as simply reusing the Model-class. Views may be read only and if they are not, they may demand special treatment when it comes to caching. Moreover, there are temporary views whose lifetime is tied to the the session.

[tanner0101]

If a view is saved to the database like a table (not just temporary to the session / connection), then I think the migration system would be the best way to create them.

It should be possible to do something like this currently:

final class ViewOrder: Model {
    // set it up just like a normal model
}

struct CreateViewOrderView: Migration {
    static func prepare(on connection: PostgreSQLConnection) -> Future<Void> {
            return connection.raw(
                """
                CREATE VIEW public.v_orders AS
                WITH cte AS (
                     ....
                JOIN clients c ON s.client_id = c.id
                """
            ).run()
    }

    static func revert() { /* drop the view */ }
}

ViewOrder.defaultDatabase = .psql

If that is the case, then we just need some conveniences around building CREATE VIEW queries. If views are standard SQL, then that should be added to https://github.com/vapor/sql.

If views are specific only to certain SQL dialects, then we should add that capability to the drivers themselves.

[vzsg]

The CREATE VIEW SQL command has been part of the standard since '89 (at least that's the oldest reference I found to it 😄), and all dialects support it. There are some extra features in some of them, but the core syntax and behavior is the same.

[foobarzap]

A view is not a table - even not a read-only one. Updating a view might be impossible (forbidden) and changing an underlying table might effect the view (see CHECK OPTION).