bug#65998: Sqlite-mode issue deleting records and closing database

[Eli Zaretskii]

> Date: Fri, 15 Sep 2023 09:02:49 +0000
> From:  Thomas Hilke via "Bug reports for GNU Emacs,
>  the Swiss army knife of text editors" <bug-gnu-emacs@gnu.org>
> 
> I had the opportunity of using sqlite-mode recently, which is really
> handy for quickly inspecting the content of a database. However, I
> noticed two issues when using it from Windows:
> 
> - The sql query built by sqlite-mode-delete is syntactically correct,
>   but misinterpreted by sqlite, and eventually do nothing. The query
>   is of the form "REMOVE FROM table_name WHERE rowid = ? and
>   'column_name_1' = ? and 'column_name_2' = ? and ..."  From
>   https://www.sqlite.org/lang_keywords.html, if I understand
>   correctly, the quoted column names in the WHERE clause are
>   interpreted as strings from sqlite, and as a result not a single row
>   is ever matched and deleted.
> 
> - The connection to the sqlite database (file) is never closed, even
>   when the buffer is killed. As sqlite--db is a local variable,
>   it's not even possible to close the connection by hand once the
>   buffer is killed. That means that once a database file is opened
>   with sqlite-mode-open-file, the file cannot be deleted unless
>   emacs is closed (on Windows).
> 
> Attached are the modifications that makes it work for me. I didn't have
> the opportunity to test it under linux or another environment.

Thanks.  I installed on the emacs-29 branch the first part of your
patch, which closes the DB when the buffer is killed.  As for the
second part, I'd prefer that Lars or someone who knows SQL reviewed it
first, as I find it strange that Lars would code something so basic
which doesn't work at all.

Could someone who knows SQL please review and chime in?