Implicit Timestamps

[pschuette22]

First off, thank you for the great work. iOS Development is more clear, stable, and maintainable using Pointfree libraries.

One of the things I added recently was implicit timestamps for table models using database triggers. The implementation we have is somewhat manual and I wonder if there's an appetite to try to work this into the Table macro (if it's not already there?) by default.

Here's the gist of what I have:

1. Timestamped protocol
   Simple protocol just enforces the existence of the created and updated columns

public protocol Timestamped {
    /// Date the row was created
    var created: Date? { get set }

    /// Date the row was modified
    var updated: Date? { get set }
}

2. A basic set of migration fragments to retroactively apply the Timestamped model
   Note: these are internal because of how our app is modularized

extension Timestamped {
    /// Alters table to add "created" column for timestamp triggers
    /// - Parameter table: SQL Table being altered
    /// - Returns: ``QueryFragment`` for executing alteration
    internal static func insertCreatedColumn(on table: StaticString) -> QueryFragment {
        QueryFragment(
            stringLiteral: "ALTER TABLE \(table) ADD COLUMN created TEXT;"
        )
    }

    /// Alters table to add "updated" column for timestamp triggers
    /// - Parameter table: SQL Table being altered
    /// - Returns: ``QueryFragment`` for executing alteration
    internal static func insertUpdatedColumn(on table: StaticString) -> QueryFragment {
        QueryFragment(
            stringLiteral: "ALTER TABLE \(table) ADD COLUMN updated TEXT;"
        )
    }
}

3. A set of database triggers to maintain the created / updated fields

extension Timestamped {
    /// Add "created" timestamp trigger for a given table
    /// - Parameter table: SQL Table being altered
    /// - Returns: ``QueryFragment`` creating triggers
    internal static func addCreatedColumnTrigger(on table: StaticString) -> QueryFragment {
        // Triggers respond to record update
        // When a row is modified, updated column is set to transaction time
        QueryFragment(
            stringLiteral: """
              CREATE TRIGGER \(table)_insert_timestamp
              AFTER INSERT ON \(table)
              FOR EACH ROW
              WHEN NEW.created IS NULL
              BEGIN
                  UPDATE \(table)
                  SET created = strftime('%Y-%m-%d %H:%M:%f', 'now'),
                      updated = strftime('%Y-%m-%d %H:%M:%f', 'now')
                  WHERE id = NEW.id;
              END;
            """
        )
    }

    /// Add "updated" timestamp trigger for a given table
    /// - Parameter table: SQL Table being altered
    /// - Returns: ``QueryFragment`` creating triggers
    internal static func addUpdatedColumnTrigger(on table: StaticString) -> QueryFragment {
        // Triggers respond to record update
        // When a row is modified, updated column is set to transaction time
        QueryFragment(
            stringLiteral: """
              CREATE TRIGGER \(table)_update_timestamp
              AFTER UPDATE ON \(table)
              FOR EACH ROW
              BEGIN
                  UPDATE \(table)
                  SET updated = strftime('%Y-%m-%d %H:%M:%f', 'now')
                  WHERE id = NEW.id;
              END;
            """
        )
    }
}

4. Finally, apply the migration during database setup

      migrator.registerMigration("Insert myModels timestamps") { db in
            try #sql(Schedule.insertCreatedColumn(on: "myModels")).execute(db)
            try #sql(Schedule.insertUpdatedColumn(on: "myModels")).execute(db)
            try #sql(Schedule.addCreatedColumnTrigger(on: "myModels")).execute(db)
            try #sql(Schedule.addUpdatedColumnTrigger(on: "myModels")).execute(db)
        }

While this is all fine and dandy, I wonder if this is interesting enough to work into the table macro? As a relative novice to swift macro writing, would it be possible to aggregate the boilerplate from above into the table macro, something like:

@Table(timestamped: true)
struct MyModel {
...

Are there other solutions to this I have not considered or didn't see? Are there any caveats to this that complicate it? I have done a lot of iOS development, but not too much Swift macro, so I'd love a trained eye here.

Thanks again!

[pschuette22]

Oops, moving this to structured queries 😅