bug#63590: 29.0.90; can't load sqlite extension

[Eli Zaretskii <eliz@gnu.org>]

> Date: Fri, 19 May 2023 15:25:21 +0200
> From:  Lennart Vogelsang via "Bug reports for GNU Emacs,
>  the Swiss army knife of text editors" <bug-gnu-emacs@gnu.org>
> 
> I am experimenting around loading sqlite extensions into the builtin
> sqlite capability of emacs. Sadly I do not seem to be able to load any
> of csv extensions, even when their name appears in the hard coded
> allow list in sqlite.c.
> 
> To reproduce, I've created an empty folder, cd'ed into it, started
> emacs -Q, copied the sqlite's csv extension source code [0] into 
> csvtable.c,
> compiled it with
> 
>       gcc -O3 -Wall -Wno-unknown-pragmas -fPIC -shared -lm -o 
> csvtable.so csvtable.c
> 
> and executed the following elisp forms in the scratch buffer:
> 
>       (setq-local mydb (sqlite-open))
>       (sqlite-load-extension mydb "./csvtable.so")
> 
> I get a nil return value from the second expression, indicating
> that it did not load the extension (verified by using the `csv` module
> in a `sqlite-execute` call). If I try the same from the `sqlite3` cli
> interface, it works:
> 
>       .load ./csvtable.so
> 
> Emacs's sqlite implementation does not setup the load extension config
> (SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION [1]) anywhere as far as I could
> gather, so I suspect that to be the root cause of the issue (the sqlite3
> cli sets that configuration). It has to be setup with the `use_config`
> [2] c function, e.g.
> 
> sqlite3_db_config(db,SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION,1,NULL)
> 
> before one can load extensions via sqlite's c interface
> `sqlite3_load_extension()` that emacs also uses [3].
> 
> For testing, I've tried compiling emacs with a small change
> that adds the sqlite3_db_config call to the sqlite-open function, and
> with this change, the above procedure works as expected.

Are you saying that the SQL-related tests in the Emacs test suite
don't work for you?  See test/src/sqlite-tests.el.

These tests were used to test the Emacs built-in SQL support, and I'm
sure they worked when Lars, who developed that, worked on it.  So how
come this doesn't work for you?