Can I load an SQLite extension in GRDB?

[stsandro]

I want to load an SQLite extension and then use it with raw SQL in GRDB. Is that possible, or am I ill-fated here?

[groue]

Hello @stsandro, my Google search tells that the SQLite library shipped on Apple operating systems is not able to load extensions.

You can link GRDB with a custom SQLite build that is able to load extensions. This is uncharted territory, so please report the results of your investigations if you have time.

[spprichard]

Thank you for the detailed response! There's a lot there to parse.

Forgive me if I am speaking out of me depths here. I was looking over the way the swift-toolchain-sqlite constructs their package, and was shocked how simple it is.

From what I can tell, all the C code is in its own target, which has its own process for updating as new versions of SQLite become available. There's no shims, bridging, or module maps. I could be wrong here, but I think constructing the SQLite dependent package this was make use of the C interop within Swift.

Maybe this is over simplistic & for the reasons you state above this won't work, but could GRDB make a similar type package (or refactor the SQLiteLib dependency) to follow this? In my head, this would simplify the custom SQLite build story for GRDB.

[groue]

Forgive me if I am speaking out of me depths here. I was looking over the way the swift-toolchain-sqlite constructs their package, and was shocked how simple it is.

You're all forgiven.

When I look at the content of the CSQLite target, I see a single sqlite.c file. Because it is the only file, I know it is an SQLite amalgamation. It is the result of the processing of the (many) raw SQLite C files, according to a chosen and fixed set of compile-time options. IIUC, those options also change the output of the Makefile that build the sqlite.c amalgamation (it's not only a matter of concatenating a bunch of files together). But even if I'm wrong I this last sentence, this does not change the net result:

The sqlite.c file is not built by the swiftlang/swift-toolchain-sqlite package. It is pre-built, and only configurable by the authors of the package. It is not configurable by the package users.

SQLiteLib, on the other hand, allows user configuration.

Maybe this is over simplistic & for the reasons you state above this won't work, but could GRDB make a similar type package (or refactor the SQLiteLib dependency) to follow this? In my head, this would simplify the custom SQLite build story for GRDB.

I could ship GRDB with a custom build of SQLite, yes, by following the same technique. It would be fixed on an arbitrary set of compile-time options. I would chose a set that makes consensus, of course, but it would not be configurable by GRDB users. People could not remove code that they don't use in order to reduce the size of the build, or add the features they need.

The README of the Swift SQLite package says:

This is intended as a common infrastructure package for components of the Swift toolchain which depend on SQLite.

Read: it is built with what we need for the Swift toolchain, nothing more, nothing less, and do not ask for more features because:

It is not intended as a general-purpose SQLite package.

If GRDB followed the same route, we'd have the same result: a rigid version of SQLite.

SQLiteLib, if more involved, is more versatile. It addresses the needs of demanding developers, and this fits pretty well the spirit of GRDB.

[spprichard]

That makes total sense. Thank you!

[spprichard]

Wanted to follow up on this.

I am a bit stuck/lost. I think I have followed the directions within SQLiteLib & GRDB for how to embed/update SQLiteLib. However, I am getting this build error for the GRDBCustom scheme.

What I did to get into this state

• Open GRDB workspace
• Within the GRDBCustom project, delete the SQLiteLib project (GRDBCustomSQLite/SQLite.xcproj)
• Drag and drop the version of SQLiteLib xcode project with OMIT_LOAD_EXTENSION removed from SQLiteLib.xcconfig. Here's the draft PR for that change to SQLiteLib.
• Attempt to build the GRDBCustom scheme

My initial thought was to just add the file its complaining about, but that doesn't feel right to me. Because this wasn't failing prior to my change, so its probably something I did (or haven't done) within my changes thats causing the error

[groue]

Thanks for the detailed report. I suggest we move our discussion to #1680 from now on.