bug#65999: [PATCH] Remove column quoting and close sqlite db on buffer kill

[Thomas Hilke]

Tags: patch

Hi everyone,

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 [1], if I
  understand correctly, the quoted column name 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 is the modifications that makes it work for me. I didn't have
the opportunity to test it under linux or another environment.

I did not want to touch the other parts that were already working, but
note that sqlite support a collection of pragma statements [2] that can
be used to inspect the schema of the database in a more structured why
than parsing the content of the sqlite_master table.

I hope it helps, and thanks for all the great work!

In GNU Emacs 30.0.50 (build 2, x86_64-w64-mingw32)
Repository revision: cb8f39ad6a5129fe18f2ebb0a1619f6949cd3d32
Repository branch: master
Windowing system distributor 'Microsoft Corp.', version 10.0.22621
System Description: Microsoft Windows 10 Pro (v10.0.2009.22621.2134)

Configured using:
 'configure --prefix=/d/a/emacs-build/emacs-build/pkg/master-x86_64
 'CFLAGS=-Ofast -fno-finite-math-only -fomit-frame-pointer'
 --with-native-compilation --with-zlib --with-xpm --with-xml2
 --with-tree-sitter --without-tiff --with-rsvg --with-png --with-lcms2
 --with-json --with-jpeg --with-harfbuzz --with-gnutls --with-gif
 --without-cairo --disable-build-details --without-dbus
 --with-compress-install --with-small-ja-dic'

Footnotes:
[1] https://www.sqlite.org/lang_keywords.html
[2] https://www.sqlite.org/pragma.html

Thomas Hilke

0001-Remove-column-quoting-and-close-sqlite-db-on-buffer-.patch
Description: 0001-Remove-column-quoting-and-close-sqlite-db-on-buffer-.patch