Question about usage of generated columns

[leotumwattana]

First off and as always. Thanks @groue for creating this wonderful library.

I need some help with the usage of generated columns.

What did you do?

Trying to use generated columns. Say I have the following table and record definitions:

/// Table Definition
db.create(table: "item") { t in

    t.primaryKey("id", .text)
    t.column("startDate", .datetime)
    t.column("duration", .integer)
    t.column("endDate", .datetime)
        .generatedAs(sql: "datetime(startDate, duration || ' minutes')", .stored)

}

/// Record Definition
struct Item: TableRecord, FetchableRecord, PersistableRecord, Codable, Hashable, Identifiable {

    public var id: String
    public var startDate: Date?
    public var duration: Int 
    public let endDate: Date?

    enum Column: String, ColumnExpression {
        case id
        case startDate
        case duration 
        case endDate
    }

}

What did you expect to happen?

I expect being able to save a new Item record as follows:

do {

    try dbPool.write { db in
        var item = Item(...)
        try item.save(db) 
    }   

} catch { ... }

What happened instead?

do {

    try dbPool.write { db in
        var item = Item(...)
        try item.save(db) // <--- Throws Error: cannot UPDATE generated column "endDate" in .....
    }   

} catch { ... }

Question

How should I define the record so try item.save(db) will ignore generated columns?

Many thanks in advance for your help!

Environment

GRDB flavor(s): GRDB GRDB version: .package(url: "https://github.com/groue/GRDB.swift", from: "6.23.0") Installation method: SPM Xcode version: 15.3 Swift version: 5.10 Platform(s) running GRDB: iOS, iPadOS, macOS macOS version running Xcode: 14.4

[leotumwattana]

Possibly answering my own question.

One solution I came up with is to write a @propertyWrapper:

@propertyWrapper
public struct EncodingIgnored<T: Hashable & Decodable>: Codable, Hashable {

    public var wrappedValue: T?

    public init(wrappedValue: T?) {
        self.wrappedValue = wrappedValue
    }

    public init(from decoder: Decoder) throws {
        let value = try decoder.singleValueContainer()
        self.wrappedValue = try value.decode(T?.self)
    }

    public func encode(to encoder: Encoder) throws {
        // Do nothing to skip (ignore) encoding
    }

}

extension KeyedDecodingContainer {

    public func decode<T>(
        _ type: EncodingIgnored<T>.Type,
        forKey key: Self.Key
    ) throws -> EncodingIgnored<T> {

        let value = try decode(T.self, forKey: key)
        return EncodingIgnored(wrappedValue: value)

    }

}

extension KeyedEncodingContainer {

    public mutating func encode<T>(
        _ value: EncodingIgnored<T>,
        forKey key: KeyedEncodingContainer<K>.Key
    ) throws {
        // Do nothing to (skip) ignore encoding
    }

}

Then mark the property I do not want encoded as follows:

struct Item: TableRecord, FetchableRecord, PersistableRecord, Codable, Hashable, Identifiable {

    public var id: String
    public var startDate: Date?
    public var duration: Int 

    @EncodingIgnored
    public let endDate: Date?

    enum Column: String, ColumnExpression {
        case id
        case startDate
        case duration 
        case endDate
    }

}

This will allow the marked property to decode its value from a computed column, but skipped during encoding.

Is there something similar in GRDB already?

If not, would it be an interesting addition to the library? Alternative names can be @ComputedColumn?

[groue]

Hello @leotumwattana,

Thank you for the compliments!

One solution I came up with is to write a @propertyWrapper:

A very good solution 👍

Note that:

• It strictly operates at the level of the standard Encodable.
• It embeds database concerns right into Item. That's not a general problem, but there are apps that don't want that - they like that GRDB does not rely on any property wrapper, and that record types only get their database powers from strictly opt-in protocols.

Those are two reasons why the home of this property wrapper is not this library. But applications can define their owns, just like you did 👍

Is there something similar in GRDB already?

No, there is no such convenience.

One built-in GRDB solution is a custom implementation of encode(to:):

extension Item {
    func encode(to container: inout PersistenceContainer) {
        container["id"] = id
        container["startDate"] = startDate
        container["duration"] = duration
        // Don't encode endDate
    }
}

The inconvenience there is that if Item gets more columns as application evolves, the compiler won't help you notice that you have to persist them in encode(to:). Your property wrapper solves this.

One other solution (more involved, but has some merits in some apps) is to define two distinct types: One without the computed columns (persistable), and one with the computed columns (not persistable):

struct ItemDefinition: Codable {
    var id: String
    var startDate: Date?
    var duration: Int 
}

extension ItemDefinition: FetchableRecord, PersistableRecord {
    static let databaseTableName = Item.databaseTableName
}

struct Item: Decodable /* not Encodable */ {
    var id: String
    var startDate: Date?
    var duration: Int 
    var endDate: Date?
}

extension Item: FetchableRecord, TableRecord { }

// Usage
try dbQueue.write { db in
    // Insert definitions
    try ItemDefinition(...).insert(db)

    // Fetch items
    let items = try Item.fetchAll(db)

    // Insert and fetch in one go
    let itemDefinition = ItemDefinition(...)
    let item = try itemDefinition.insertAndFetch(db, as: Item.self)
}

The advantage of this technique is that Item could have a non-optional var endDate: Date property if the generated column was guaranteed to never be NULL value (not the case in your specific schema because startDate is nullable).

[leotumwattana]

Yup. Fair assessment on that it works on Encodable and not specifically on the database powers.

In that case, I shall close this issue and it can serve as a reference for others looking for a similar solution.

Thanks again @groue.

[groue]

I shall close this issue and it can serve as a reference for others looking for a similar solution.

Yes, this is spot on :-)

To be clear, I'm not opposed to shipping conveniences in the lib - I just can't ship one convenience per specific need of each and every application. I fully understand how, when developing an app, we feel like we have general needs, and we'd like the library to support them right away. The reality is that some generalities hide a lot of specificities, and that the role of the library is to allow the app to code those specific needs. The P in API is "programming" ;-)

It is very appreciated that you opened this issue. Sometimes a truly general need that can ship in the library emerges in this way. Please keep on voicing your concerns!

Happy GRDB!