bug#65274: 29.0.91; [feature request] Readonly argument for emacs built-in sqlite support.

[Nicolas Graves via "Bug reports for GNU Emacs, the Swiss army knife of text editors" <bug-gnu-emacs@gnu.org>]

On 2023-08-14 19:40, Eli Zaretskii wrote:

>> Date: Sun, 13 Aug 2023 22:35:17 +0200
>> From:  Nicolas Graves via "Bug reports for GNU Emacs,
>>  the Swiss army knife of text editors" <bug-gnu-emacs@gnu.org>
>>
>>
>> The sqlite3 cli tool has an option -readonly, which doesn't seem to be
>> available in Emacs for now. This means that if someone wants to inspect
>> a locked database, we would necessarily need to make a copy, while the
>> -readonly flag would be enough using a shell-command.
>
> Could you please explain what is a "locked database"?
>
> The sqlite3 documentation says:
>
>   SQLITE_OPEN_READWRITE
>       The database is opened for reading and writing if possible, or
>       reading only if the file is write protected by the operating
>       system. In either case the database must already exist, otherwise
>       an error is returned. For historical reasons, if opening in
>       read-write mode fails due to OS-level permissions, an attempt is
>       made to open it in read-only mode. sqlite3_db_readonly() can be
>       used to determine whether the database is actually read-write.
>
> My interpretation of this is that if opening for reading and writing
> fails, SQlite will automatically attempt to open in read-only mode.
> Does this not work in some cases?  IOW, in which cases would this
> readonly option you are asking to add be useful?
>
> Thanks.

I'm sorry, I've resent this unsent patch from July which is rather not
precise, I now have more information.

My use-case is the package ibrowse.el, which uses sql commands to parse
in real time the database of browser (for history or bookmarks,
depending on the case)
(https://git.sr.ht/~ngraves/ibrowse.el/tree/master/item/ibrowse-sql.el).

Web browsers use very frequent updates and most of the time, even
trying to access it with readonly mode is not enough (I don't remember
all the details, but it's something along the lines of you can't open
even in read-only mode if the database file is currently being
updated).

Most other emacs packages which try to do the same thing circumvent the
issue by copying the whole database to /tmp and parsing it from
there. I've found another solution which doesn't require to copy the
file and it's to open it in immutable mode
(https://git.sr.ht/~ngraves/ibrowse.el/tree/master/item/ibrowse-sql.el#L58)
I remember the following stackoverflow link lead me to try this:
https://stackoverflow.com/questions/7857755/is-it-possible-to-open-a-locked-sqlite-database-in-read-only-mode

On the CLI, it's simply done by adding the uri file:...?immutable=1. The SQL
manual warns for possible errors if the database is being updated at the
same time, but I don't keep an open connection so I don't experience any
error whatsoever. One stackoverflow comment also hints about how it
could be done, this probably applies to emacs as well:

"To achieve the same thing with most SQLite drivers, because we can't directly set SQLITE_IOCAP_IMMUTABLE, the best way is to open the connection with the flag SQLITE_OPEN_READONLY | SQLITE_OPEN_URI to allow passing the file:...?immutable=1 URI as filename."

I've verified that this cannot be done on my version of emacs+sqlite
(currently GNU Emacs 29.0.92 (build 1, x86_64-pc-linux-gnu, GTK+ Version
3.24.37, cairo version 1.16.0)).

I know this is not the recommended way to use the sqlite
builtin-support (not a database by/for emacs, but parsing another app
data), but I would greatly appreciate if I can use this option with the
builtin support.

Thanks a lot for your work on emacs,

-- 
Best regards,
Nicolas Graves