View SQLite

[TKNgu]

I have some problem when create simple model:

import Vapor
import FluentSQLite

final class Raw: SQLiteModel {
    var id: Int?
    var name: String

    init(id: Int?, name: String) {
        self.id = id
        self.name = name
    }    
}

extension Raw: Content {}

extension Raw: Parameter {}

extension Raw: Migration {}

and get by raw query:

    router.get("test", "raw") { req in
        return req.withPooledConnection(to: .sqlite) { conn in
            return conn.raw(
                """
                    select * from Raw
                """
            ).all(decoding: Raw.self)
        }
    }

everything OK. But when I create simple view:

import Vapor
import FluentSQLite

final class RawView: SQLiteModel {
    var id: Int?
    var name: String

    init(id: Int?, name: String) {
        self.id = id
        self.name = name
    }    
}

extension RawView: Content {}

extension RawView: Parameter {}

// View created on db.
extension RawView: Migration {
    static func prepare(on conn: SQLiteConnection) -> Future<Void> {
        return conn.raw(
            """
            create view RawView (id, name) as select * from Raw
            """
        ).run()
    }
}

and get by raw query:

    router.get("test", "raw-view") { req in
        return req.withPooledConnection(to: .sqlite) { conn in
            return conn.raw(
                """
                    select * from RawView
                """
            ).all(decoding: RawView.self)
        }
    }

It not working:

{
    "error": true,
    "reason": "Value of type 'String' required for key 'name'."
}

ngocpt@NgocPT-PC:~/Work/HTML/MyBlog/server$ vapor run --port=9080
Running api ...
[ INFO ] Migrating 'sqlite' database (/home/ngocpt/Work/HTML/MyBlog/server/.build/checkouts/fluent.git-5488790570233128454/Sources/Fluent/Migration/MigrationConfig.swift:69)
[sqlite] [2020-03-15 05:35:53 +0000] SELECT COUNT(*) AS "fluentAggregate" FROM "fluent" []
[sqlite] [2020-03-15 05:35:53 +0000] SELECT * FROM "fluent" ORDER BY "fluent"."batch" DESC LIMIT 1 OFFSET 0 []
[sqlite] [2020-03-15 05:35:53 +0000] SELECT * FROM "fluent" WHERE "fluent"."name" = (?) LIMIT 1 OFFSET 0 ["Todo"]
[sqlite] [2020-03-15 05:35:53 +0000] SELECT * FROM "fluent" WHERE "fluent"."name" = (?) LIMIT 1 OFFSET 0 ["Sample"]
[sqlite] [2020-03-15 05:35:53 +0000] SELECT * FROM "fluent" WHERE "fluent"."name" = (?) LIMIT 1 OFFSET 0 ["User"]
[sqlite] [2020-03-15 05:35:53 +0000] SELECT * FROM "fluent" WHERE "fluent"."name" = (?) LIMIT 1 OFFSET 0 ["Item"]
[sqlite] [2020-03-15 05:35:53 +0000] SELECT * FROM "fluent" WHERE "fluent"."name" = (?) LIMIT 1 OFFSET 0 ["Haha"]
[sqlite] [2020-03-15 05:35:53 +0000] SELECT * FROM "fluent" WHERE "fluent"."name" = (?) LIMIT 1 OFFSET 0 ["Raw"]
[sqlite] [2020-03-15 05:35:53 +0000] SELECT * FROM "fluent" WHERE "fluent"."name" = (?) LIMIT 1 OFFSET 0 ["RawView"]
[ INFO ] Migrations complete (/home/ngocpt/Work/HTML/MyBlog/server/.build/checkouts/fluent.git-5488790570233128454/Sources/Fluent/Migration/MigrationConfig.swift:73)
Running default command: .build/debug/Run serve
[Deprecated] --option=value syntax is deprecated. Please use --option value (with no =) instead.
Server starting on http://localhost:9080
[sqlite] [2020-03-15 05:36:00 +0000]     select * from RawView []
[ ERROR ] DecodingError.valueNotFound: Value of type 'String' required for key 'name'. (ErrorMiddleware.swift:26)

When try with:

    router.get("test", "raw-view") { req in
        return req.withPooledConnection(to: .sqlite) { conn in
            return conn.raw(
                """
                    select * from RawView
                """
            ).all(decoding: Raw.self)
        }
    }

It OK. It happen on Linux Ubuntu 18.04 and SQLite. But working very good on Ubuntu 18.0 and MySQL. What happening on my code. Sorry for my English.

[TKNgu]

Update build model from scratch run OK.

import Vapor
import FluentSQLite

final class RawView: Model {
    typealias Database = SQLiteDatabase
    typealias ID = Int
    static let idKey: IDKey = \.id

    var id: Int?
    var name: String

    init(id: Int?, name: String) {
        self.id = id
        self.name = name
    }    
}

// final class RawView: SQLiteModel {
//     var id: Int?
//     var name: String

//     init(id: Int?, name: String) {
//         self.id = id
//         self.name = name
//     }  
// }

extension RawView: Content {}

extension RawView: Parameter {}

extension RawView: Migration {
    static func prepare(on conn: SQLiteConnection) -> Future<Void> {
        return conn.raw(
            """
            create view RawView (id, name) as select * from Raw
            """
        ).run()
    }
}

But use build-in query not work:

api.get("categories") { req in
    return Category.query(on: req).all()
 }

So some problem with my code use SQLiteMode where I can't find source code SQLiteModel for check. Some can help please. Sorry for my English.