Question: how can we best support sqlite3_auto_extension for adding extensions?

[zsogitbe]

sqlite3_auto_extension is an exported function and needed for adding extensions to sqlite. I do not immediately see a direct C# equivalent in this library. How can we best do this with this library?

[ericsink]

It might not be difficult for me to add support for that function, but I suspect it would end up being very difficult to use.

The docs indicate this function is for statically linked extensions, but almost everyone that uses SQLitePCLRaw is using the dynamic libraries that come as part of its bundle packages.

Even if we did somehow support that function, what would you pass to it? We currently don't provide any easy way to write SQLite extensions in C#. Where would you get the function pointer?

Currently I'm guessing the most natural way to approach this problem would be to do your own custom SQLite build with your extension compiled in, and initialize the SQLite provider manually instead of using a bundle package.

[zsogitbe]

I think that it would be a very useful extension of the library if you could add a function to load static extensions (usually written in C/C++). Compiling these into the core sqlite library is not a good approach, also because then you need to call the initialization function of each extension in some way.

What would be very useful is to have a function which calls sqlite3_auto_extension internally with the path of the extension dll and the name of the initializer. The function should get the function pointer from the dll. This should then enable the extension for all subsequent connections.

The other solution is the already existing sqlite3_enable_load_extension/sqlite3_load_extension functions, but this needs to be called for each connection.

[ericsink]

Compiling these into the core sqlite library is not a good approach,

I'm not saying it's a good approach. I'm saying it is an approach that is available to you now. Furthermore, if it is true (as the docs suggest) that sqlite3_auto_extension actually requires that the extension be statically linked with SQLite itself, then a custom native build is the only option.

also because then you need to call the initialization function of each extension in some way.

No. What I was suggesting was you could create a DLL that contains a custom build of SQLite AND your extension and do the sqlite3_auto_extension call yourself.

And the reason I made this suggestion, again, is because the docs for sqlite3_auto_extension say that it is for statically linked extensions. You propose passing it a function pointer obtained from a different DLL. Have you verified that this will work?

[zsogitbe]

Yes, I can do this in C++/C with a dll extension (not statically linked to sqlite). It would be great if you could make this in C# with the library.

[zsogitbe]

If you want to make some tests here is an extension that you can compile for testing: https://www.sqlite.org/src/file?name=ext/misc/zipfile.c or this https://www.sqlite.org/src/file?name=ext/misc/spellfix.c

[ericsink]

Okay. I'll investigate the possibility of adding this feature as soon as I can. Thanks.

[sjlombardo]

@zsogitbe. It sounds like you want to make a dynamic library which loads itself persistently and then registers as an automatic extension so that any future connections use it automatically.

I believe the canonical example is https://sqlite.org/src/file/ext/misc/vfsstat.c which calls sqlite3_auto_extension "on itself" and then returns SQLITE_OK_LOAD_PERMANENTLY. This allows the extension it to be loaded dynamically but still used on every connection.

It is most appropriate to do this from the extension itself because the return value from the extension initializer is of critical importance. If you tried this approach with a dynamically loaded extension that was not expecting to be used with sqlite3_auto_extension, it would return SQLITE_OK at the end of its extension initializer instead of SQLITE_OK_LOAD_PERMANENTLY. When the first connection closes the extension would still be unloaded.

In other words, what you want to do is possible, but should probably be done entirely from within the extension itself.

Doing it that way would not require any modifications or extensions to the exposed SQLitePCLRaw API.

[zsogitbe]

@sjlombardo & @ericsink, I was able to modify my C++ code based on @sjlombardo's example and use the existing infrastructure in SQLitePCL.raw to load a module permanently for all future connections with sqlite3_load_extension. There are a few important things to note:

• The example is misleading because it is not enough to just register the sqlite3_create_module part of the extension! You need to recreate everything you have created before registering the extension. For example, if you define functions, then you need to define them again in the sqlite3_auto_extension() callback. If you do not do this, then the auto loading will fail and you will not be able to open the DB in the given process forever.

Thank you for your input!