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!

[mbrandonw]

Hi @pschuette22, we do provide tools for easily creating triggers that allow you to touch a date column after the row has been inserted/updated. This makes it so that the full process of having timestamp columns on a table looks like this:

// 1
@Table struct Reminder {
  …
  let createdAt: Date
  let updatedAt: Date?
}

// 2
migrator.registerMigration(…) { db in 
  #sql("""
    CREATE TABLE "reminders" (
      …
      "createdAt" TEXT NOT NULL DEFAULT (datetime('subsec')),
      "updatedAt" TEXT
    """)
    .execute(db)
}

// 3
try Reminder.createTemporaryTrigger(afterUpdateTouch: \.updatedAt).execute(db)

I'm not really sure how much shorter this can be, even if the macro generates some code. One thing you certainly can do is use your Timestamped protocol to create a generic function that creates the temporary triggers for a bunch of tables at once:

try createTimestampTriggers(
  for: Reminder.self, RemindersList.self, User.self, ...
)
.execute(db)

[pschuette22]

Oh nice, definitely missed the .createTemporaryTrigger(afterUpdateTouch:), I prefer this implementation you have laid out.
Agreed, not going to try too hard to solution away 7 lines. I definitely think I will go with your suggestion around just using the protocol to conveniently attach the timestamps.