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

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

[Thomas Hilke via Bug reports for GNU Emacs, the Swiss army knife of text editors]

[-- Attachment #1: Type: text/plain, Size: 2361 bytes --]

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

[-- Attachment #2: 0001-Remove-column-quoting-and-close-sqlite-db-on-buffer-.patch --]
[-- Type: application/octet-stream, Size: 2413 bytes --]

From 44368e1a4327f3317f902821e92a077d41303f5b Mon Sep 17 00:00:00 2001
From: Thomas Hilke <t.hilke@rollomatic.ch>
Date: Fri, 15 Sep 2023 10:30:25 +0200
Subject: [PATCH] Remove column quoting and close sqlite db on buffer kill

The current implementation of `sqlite-mode--column-names` returns the
list of the table's columns quoted with single quotes. This results in
a malformed sql query in sqlite-mode-delete which prevent the deletion
of the table row.

The fix involves registering a local hook in kill-buffer-hook which
closes the database upon buffer kill.

Also the database opened by sqlite-mode-open-file is stored in the
local variable sqlite--db, but is never closed. Under Windows at
least, that means that emacs must be closed for the connection to be
closed, after the *SQL ...* buffer is killed.

The fix is to get the list of column by evaluating a pragma statement
instead of manually parsing sqlite internal "create table" schema.
---
 lisp/sqlite-mode.el | 18 ++----------------
 1 file changed, 2 insertions(+), 16 deletions(-)

diff --git a/lisp/sqlite-mode.el b/lisp/sqlite-mode.el
index 8cb94485369..38e9f84b842 100644
--- a/lisp/sqlite-mode.el
+++ b/lisp/sqlite-mode.el
@@ -63,6 +63,7 @@
   (setq-local sqlite--db (sqlite-open file))
   (unless (sqlitep sqlite--db)
     (error "`sqlite-open' failed to open SQLite file"))
+  (add-hook 'kill-buffer-hook (lambda () (sqlite-close sqlite--db)) nil t)
   (sqlite-mode-list-tables))
 
 (defun sqlite-mode-list-tables ()
@@ -135,22 +136,7 @@
 
 (defun sqlite-mode--column-names (table)
   "Return a list of the column names for TABLE."
-  (let ((sql
-         (caar
-          (sqlite-select
-           sqlite--db
-           "select sql from sqlite_master where tbl_name = ? AND type = 'table'"
-           (list table)))))
-    (with-temp-buffer
-      (insert sql)
-      (mapcar #'string-trim
-              (split-string
-               ;; Extract the args to CREATE TABLE.  Point is
-               ;; currently at its end.
-               (buffer-substring
-                (1- (point))                          ; right before )
-                (1+ (progn (backward-sexp) (point)))) ; right after (
-               ",")))))
+  (mapcar (lambda (row) (nth 1 row)) (sqlite-select sqlite--db (format "pragma table_info(%s)" table))))
 
 (defun sqlite-mode-list-data ()
   "List the data from the table under point."
-- 
2.41.0.windows.3

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

[Eli Zaretskii]

merge 65999 65998
thanks

> Date: Fri, 15 Sep 2023 11:19:28 +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:

This is an exact duplicate of bug#65998 which you filed a short time
ago, so I'm merging them.