GNU bug report logs - #65998
Sqlite-mode issue deleting records and closing database

Previous Next

Full log

View this message in rfc822 format

From: help-debbugs <at> gnu.org (GNU bug Tracking System)
To: Eli Zaretskii <eliz <at> gnu.org>
Cc: tracker <at> debbugs.gnu.org
Subject: bug#65998: closed (Sqlite-mode issue deleting records and closing
 database)
Date: Sun, 17 Sep 2023 10:07:02 +0000

[Message part 1 (text/plain, inline)]

Your message dated Sun, 17 Sep 2023 13:05:47 +0300
with message-id <8334zdglms.fsf <at> gnu.org>
and subject line Re: bug#65998: Sqlite-mode issue deleting records and closing database
has caused the debbugs.gnu.org bug report #65998,
regarding Sqlite-mode issue deleting records and closing database
to be marked as done.

(If you believe you have received this mail in error, please contact
help-debbugs <at> gnu.org.)


-- 
65998: https://debbugs.gnu.org/cgi/bugreport.cgi?bug=65998
GNU Bug Tracking System
Contact help-debbugs <at> gnu.org with problems

[Message part 2 (message/rfc822, inline)]

From: Thomas Hilke <t.hilke <at> rollomatic.ch>
To: "bug-gnu-emacs <at> gnu.org" <bug-gnu-emacs <at> gnu.org>
Subject: Sqlite-mode issue deleting records and closing database
Date: Fri, 15 Sep 2023 09:02:49 +0000

[Message part 3 (text/plain, inline)]

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
  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.

I did not want to touch the other parts that were already working, but
note that sqlite support a collection of pragma statements that can be
used to inspect the schema of the database in a more structured way
than parsing the content of the sqlite_master table:
https://www.sqlite.org/lang_keywords.html.

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

Thomas Hilke

[Message part 4 (text/html, inline)]

[0001-Remove-column-quoting-and-close-sqlite-db-on-buffer-.patch (application/octet-stream, attachment)]

[Message part 6 (message/rfc822, inline)]

From: Eli Zaretskii <eliz <at> gnu.org>
To: Stefan Kangas <stefankangas <at> gmail.com>
Cc: 65998-done <at> debbugs.gnu.org, larsi <at> gnus.org, t.hilke <at> rollomatic.ch
Subject: Re: bug#65998: Sqlite-mode issue deleting records and closing database
Date: Sun, 17 Sep 2023 13:05:47 +0300

> From: Stefan Kangas <stefankangas <at> gmail.com>
> Date: Sat, 16 Sep 2023 07:07:51 -0700
> Cc: 65998 <at> debbugs.gnu.org
> 
> Eli Zaretskii <eliz <at> gnu.org> writes:
> 
> >> - 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.
> >
> > 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?
> 
> Something like this in an SQL "REMOVE FROM table_name WHERE {foo}" clause
> 
>     'column_name_1' = ?
> 
> will check if the '?' part is equal to the string 'column_name_1', which
> is probably not what we want.
> 
> Whereas this
> 
>     column_name_1 = ?
> 
> will instead check if the '?' part is equal to the value of the column
> column_name_1 in table_name.
> 
> (The "?" is just a placeholder that will be filled in with an actual
> value later.)
> 
> So without having tested the code or studied it in detail, the analysis
> of the problem sounds right to me.

Thanks, so I've now installed the other part on the emacs-29 branch,
and I'm closing the bug.

> Don't we have unit tests in place for this stuff, though?  Perhaps we
> should see this as an opportunity to add some...

I agree, patches are welcome.

Previous Next

GNU bug tracking system
Copyright (C) 1999 Darren O. Benham, 1997,2003 nCipher Corporation Ltd, 1994-97 Ian Jackson.