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

[Thomas Hilke]

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

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