Add ability to configure pragmas that are applied after opening a connection

[insha]

Hi, I have recently started using the wonderful Lighter library as a potential replacement for SwiftData. I wanted to know how do I enable the functionality for the cascade deletes to happen when using Lighter?

I have add the db.set(pragma: "foreign_keys = ON", to: true) after invoking the bootstrap(overwrite: false) method; but that didn't seem to enable the cascade deletes.

Then I tried invoking the db.set(pragma: "foreign_keys = ON", to: true) right before the delete operation; that also did not work.

In both tries, I ended up with orphaned records in the detail table that should have been removed.

Note that when I do the same using CLI for SQLite, the cascade delete works as expected. Any guidance would be greatly appreciated. I will add sample project in a little bit; perhaps I am not using Lighter in the correct way ¯_(ツ)_/¯

[helje5]

Hm, yes. The foreign keys pragma needs to be set on every single connection. We should add a way to set a certain set of pragmas to the SQLConnectionHandler.Configuration.

In the meantime you should be able to do this in an own SQLConnectionHandler subclass. Since SimplePool is market final, you'd probably need to copy the SimplePool, and then apply your custom pragmas in the openConnection function, something like:

    override public func openConnection(_ configuration: Configuration) throws
                         -> OpaquePointer
    {
      lock.lock()
      let entry = (caches[configuration]?.isEmpty ?? true)
                ? nil
                : caches[configuration]?.removeLast()
      lock.unlock()
      if let entry = entry { return entry.handle }
      
      let connection = try super.openConnection(configuration)
      // set pragmas on new connection
      return connection
    }

[insha]

@helje5 Thank you so much for such a quick response. I’ll try to submit a pull request for this functionality.