Support for cascading parent timestamp triggers from child table changes

[Rspoon3]

It would be nice if the library had a first-class way to express parent updatedAt cascading from child tables. Something like:

// Hypothetical API — specify which child events cascade to the parent
Note.createTemporaryCascadeTriggers(
    touch: \.updatedAt,
    on: [.insert, .update, .delete],
    of: Comment.self, via: \.noteID,
        Attachment.self, via: \.noteID,
        Tag.self, via: \.noteID
)

The on: parameter takes a set of events, so callers can choose which child operations cascade. For example, on: [.insert, .delete] would skip update cascading if only structural changes matter.

Or perhaps leveraging the existing foreign key metadata that the SyncEngine already inspects:

// Hypothetical API — auto-detect child tables via foreign keys
Note.createTemporaryCascadeTriggers(touch: \.updatedAt, on: [.insert, .update, .delete])

This pattern (touch parent timestamp when child records change) is common enough that a built-in API could save significant boilerplate.

[Rspoon3]

This is my current solution but it doesn't feel great.

private func installCascadeTriggers<Parent: Table, Child: Table>(
    on _: Parent.Type,
    touch parentColumn: String,
    for _: Child.Type,
    via foreignKey: String,
    on db: Database
) throws {
    let updateSQL = """
        UPDATE "\(Parent.tableName)" SET "\(parentColumn)" = datetime('subsec')
        WHERE "id" = NEW."\(foreignKey)"
        """
    let deleteSQL = """
        UPDATE "\(Parent.tableName)" SET "\(parentColumn)" = datetime('subsec')
        WHERE "id" = OLD."\(foreignKey)"
        """

    try Child.createTemporaryTrigger(after: .insert { _ in #sql("\(raw: updateSQL)") }).execute(db)
    try Child.createTemporaryTrigger(after: .update { _, _ in #sql("\(raw: updateSQL)") }).execute(db)
    try Child.createTemporaryTrigger(after: .delete { _ in #sql("\(raw: deleteSQL)") }).execute(db)
}

// Usage:
try installCascadeTriggers(on: Note.self, touch: "updatedAt", for: Comment.self, via: "noteID", on: db)
try installCascadeTriggers(on: Note.self, touch: "updatedAt", for: Attachment.self, via: "noteID", on: db)
try installCascadeTriggers(on: Note.self, touch: "updatedAt", for: Tag.self, via: "noteID", on: db)

[stephencelis]

@Rspoon3 Thanks for sharing the problem and your solution to it! One thing you could do to make things a lil safer is to use the TableColumn type to specify the parent column and foreign key:

extension PrimaryKeyedTable {
  static func installCascadeTriggers<Child: Table>(
    touch parentColumn: TableColumn<Self, Date>,
    for _: Child.Type,
    via foreignKey: TableColumn<Child, PrimaryKey>,
    on db: Database
  ) throws {
  // ...
}

// Usage:
try Note.installCascadeTriggers(touch: \.updatedAt, for: Comment.self, via: \.noteID, on: db)

We generally like to keep core library surface area lean, but this tool could be incubated as a third party library if you are so inclined! We could link to it from the README.

[Rspoon3]

Is there an "extras" library someone else made that you recommend or should I roll my own?