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

[Lennart Vogelsang]

Dear maintainers,

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.

If someone with more experience and knowledge could confirm that this is
indeed the issue and that a patch is wanted, I am happy to make a small
patch that adds the configuration to the sqlite-open function of
sqlite.c (or would there be a better position to add that config
setting?).

Happy greetings,
Lennart

Additional sources:

[0] https://www.sqlite.org/src/artifact?ci=trunk&filename=ext/misc/csv.c
[1] 
https://www.sqlite.org/c3ref/c_dbconfig_defensive.html#sqlitedbconfigenableloadextension
[2] https://www.sqlite.org/c3ref/db_config.html
[3] https://www.sqlite.org/loadext.html

In GNU Emacs 29.0.90 (build 1, x86_64-pc-linux-gnu, GTK+ Version
3.24.37, cairo version 1.17.8) of 2023-04-27 built on Jupiter
Repository revision: c46e93b1f50c9a6f7143f347d96a6385bcdf3a05
Repository branch: emacs-29
System Description: Arch Linux

Configured using:
'configure --with-pgtk --with-treesitter'

Configured features:
ACL CAIRO DBUS FREETYPE GIF GLIB GMP GNUTLS GPM GSETTINGS HARFBUZZ JPEG
JSON LCMS2 LIBOTF LIBSELINUX LIBSYSTEMD LIBXML2 MODULES NOTIFY INOTIFY
PDUMPER PGTK PNG RSVG SECCOMP SOUND SQLITE3 THREADS TIFF
TOOLKIT_SCROLL_BARS TREE_SITTER WEBP XIM GTK3 ZLIB

Important settings:
value of $LANG: en_US.UTF-8
value of $XMODIFIERS: @im=ibus
locale-coding-system: utf-8-unix

Major mode: Lisp Interaction

Minor modes in effect:
tooltip-mode: t
global-eldoc-mode: t
eldoc-mode: t
show-paren-mode: t
electric-indent-mode: t
mouse-wheel-mode: t
tool-bar-mode: t
menu-bar-mode: t
file-name-shadow-mode: t
global-font-lock-mode: t
font-lock-mode: t
blink-cursor-mode: t
line-number-mode: t
indent-tabs-mode: t
transient-mark-mode: t
auto-composition-mode: t
auto-encryption-mode: t
auto-compression-mode: t

Load-path shadows:
None found.

Features:
(shadow sort mail-extr emacsbug message mailcap yank-media puny dired
dired-loaddefs rfc822 mml mml-sec password-cache epa derived epg rfc6068
epg-config gnus-util text-property-search mm-decode mm-bodies mm-encode
mail-parse rfc2231 mailabbrev gmm-utils mailheader sendmail rfc2047
rfc2045 ietf-drums mm-util mail-prsvr mail-utils time-date subr-x
cl-loaddefs cl-lib rmc iso-transl tooltip cconv eldoc paren electric
uniquify ediff-hook vc-hooks lisp-float-type elisp-mode mwheel
term/pgtk-win pgtk-win term/common-win pgtk-dnd tool-bar dnd fontset
image regexp-opt fringe tabulated-list replace newcomment text-mode
lisp-mode prog-mode register page tab-bar menu-bar rfn-eshadow isearch
easymenu timer select scroll-bar mouse jit-lock font-lock syntax
font-core term/tty-colors frame minibuffer nadvice seq simple cl-generic
indonesian philippine cham georgian utf-8-lang misc-lang vietnamese
tibetan thai tai-viet lao korean japanese eucjp-ms cp51932 hebrew greek
romanian slovak czech european ethiopic indian cyrillic chinese
composite emoji-zwj charscript charprop case-table epa-hook
jka-cmpr-hook help abbrev obarray oclosure cl-preloaded button loaddefs
theme-loaddefs faces cus-face macroexp files window text-properties
overlay sha1 md5 base64 format env code-pages mule custom widget keymap
hashtable-print-readable backquote threads dbusbind inotify
dynamic-setting system-font-setting font-render-setting cairo gtk pgtk
lcms2 multi-tty make-network-process emacs)

Memory information:
((conses 16 36722 8121)
(symbols 48 5117 0)
(strings 32 13127 1970)
(string-bytes 1 373207)
(vectors 16 9308)
(vector-slots 8 148657 12041)
(floats 8 22 22)
(intervals 56 310 0)
(buffers 984 11))