From mboxrd@z Thu Jan 1 00:00:00 1970 Path: news.gmane.io!.POSTED.blaine.gmane.org!not-for-mail From: Thomas Hilke via "Bug reports for GNU Emacs, the Swiss army knife of text editors" Newsgroups: gmane.emacs.bugs Subject: bug#65998: Sqlite-mode issue deleting records and closing database Date: Fri, 15 Sep 2023 09:02:49 +0000 Message-ID: <125a2ffc4cc34a3eb03ecebb4dfc2f9f@rollomatic.ch> Reply-To: Thomas Hilke Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="_004_125a2ffc4cc34a3eb03ecebb4dfc2f9frollomaticch_" Injection-Info: ciao.gmane.io; posting-host="blaine.gmane.org:116.202.254.214"; logging-data="12625"; mail-complaints-to="usenet@ciao.gmane.io" To: 65998@debbugs.gnu.org Original-X-From: bug-gnu-emacs-bounces+geb-bug-gnu-emacs=m.gmane-mx.org@gnu.org Fri Sep 15 15:05:56 2023 Return-path: Envelope-to: geb-bug-gnu-emacs@m.gmane-mx.org Original-Received: from lists.gnu.org ([209.51.188.17]) by ciao.gmane.io with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qh8WC-0002x1-9F for geb-bug-gnu-emacs@m.gmane-mx.org; Fri, 15 Sep 2023 15:05:56 +0200 Original-Received: from localhost ([::1] helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1qh8VH-0008LS-K9; Fri, 15 Sep 2023 09:04:59 -0400 Original-Received: from eggs.gnu.org ([2001:470:142:3::10]) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1qh8VE-0008Kd-B3 for bug-gnu-emacs@gnu.org; Fri, 15 Sep 2023 09:04:56 -0400 Original-Received: from debbugs.gnu.org ([2001:470:142:5::43]) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.90_1) (envelope-from ) id 1qh8VE-0002vG-0L for bug-gnu-emacs@gnu.org; Fri, 15 Sep 2023 09:04:56 -0400 Original-Received: from Debian-debbugs by debbugs.gnu.org with local (Exim 4.84_2) (envelope-from ) id 1qh8VK-0007kc-Bd for bug-gnu-emacs@gnu.org; Fri, 15 Sep 2023 09:05:02 -0400 X-Loop: help-debbugs@gnu.org Resent-From: Thomas Hilke Original-Sender: "Debbugs-submit" Resent-CC: bug-gnu-emacs@gnu.org Resent-Date: Fri, 15 Sep 2023 13:05:02 +0000 Resent-Message-ID: Resent-Sender: help-debbugs@gnu.org X-GNU-PR-Message: report 65998 X-GNU-PR-Package: emacs X-Debbugs-Original-To: "bug-gnu-emacs@gnu.org" Original-Received: via spool by submit@debbugs.gnu.org id=B.169478304829711 (code B ref -1); Fri, 15 Sep 2023 13:05:02 +0000 Original-Received: (at submit) by debbugs.gnu.org; 15 Sep 2023 13:04:08 +0000 Original-Received: from localhost ([127.0.0.1]:42414 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1qh8UP-0007j1-Eg for submit@debbugs.gnu.org; Fri, 15 Sep 2023 09:04:07 -0400 Original-Received: from lists.gnu.org ([2001:470:142::17]:35138) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1qh4je-0001B7-Ih for submit@debbugs.gnu.org; Fri, 15 Sep 2023 05:03:37 -0400 Original-Received: from eggs.gnu.org ([2001:470:142:3::10]) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1qh4jR-0005B8-TG for bug-gnu-emacs@gnu.org; Fri, 15 Sep 2023 05:03:22 -0400 Original-Received: from egress-ip19a.ess.de.barracuda.com ([18.184.203.242]) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1qh4jP-0000br-89 for bug-gnu-emacs@gnu.org; Fri, 15 Sep 2023 05:03:21 -0400 Original-Received: from PACIFIX.rollomatic.ch (mail.rollomatic.ch [80.83.55.38]) by mx-outbound11-249.eu-central-1a.ess.aws.cudaops.com (version=TLSv1.2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128 verify=NO); Fri, 15 Sep 2023 09:03:01 +0000 DKIM-Signature: v=1; a=rsa-sha256; d=rollomatic.ch; s=pacifix; c=simple/simple; t=1694768570; h=from:subject:to:date:message-id; bh=4qTbPzx8MLWg5/9s2F/aRUh3VS6Rlr+LU45QioT8is0=; b=BQtOs4Df6xuI3AiB1Vq8ISgfUQ9SeCyyX/LbeJLiTDvlOU1UHeLtUerTa+P4Am/zFrxBarGJOAW h6/odZX0tcjFWg4yaZeC7sDHNCOyZV9DDMBt3NX60c6uNhxv7cMxQg1+qn9jEGnDmQ2riMHCsVQ7X p50UzpnouH5DZ9mpIOaniOKznYjynXDQcbp+VpqerFWMorQq2TMHAQ/h2nSr3gBBZ0ATAWH9VpFMO 50SeZ/RbHqjnD8y3euYvtNtoNR+XnM2aI/hopOFQjx4NZ5ZEw91l6arGkJtfIRcvvPkq5TUlHw8wk PYC15j2Gn4E+NLP1t9u62LVFRheLTSX6TRRQ== Original-Received: from PACIFIX.rollomatic.ch (192.168.7.76) by PACIFIX.rollomatic.ch (192.168.7.76) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256) id 15.1.2507.32; Fri, 15 Sep 2023 11:02:49 +0200 Original-Received: from PACIFIX.rollomatic.ch ([fe80::c933:7976:6078:6c75]) by PACIFIX.rollomatic.ch ([fe80::c933:7976:6078:6c75%7]) with mapi id 15.01.2507.032; Fri, 15 Sep 2023 11:02:49 +0200 Thread-Topic: Sqlite-mode issue deleting records and closing database Thread-Index: AdnnsE7HriQvk2YrStuXk1fAlE/kmQ== Accept-Language: fr-CH, en-US Content-Language: en-US X-MS-Has-Attach: yes x-originating-ip: [192.168.19.61] X-BESS-ID: 1694768551-303065-12607-633-2 X-BESS-VER: 2019.1_20230913.1749 X-BESS-Apparent-Source-IP: 80.83.55.38 X-BESS-Parts: H4sIAAAAAAACA02OOwrDQAxE76LaxWqt1Uq+SkixHwlDSFLEgUDw3bOFDWmGYe A95vIF+2ywwDZygucLFkyJR1vHiK6ZI+IsrYbmTb1RLtwJbdZcIuzTya/b/eRDpnQIlH pLTYU4R5KqKRYhl4zBAltuf4L343YKWMPBC85sLC6FqJK7sVWMyjYelN4d9usPL+VZy8 IAAAA= X-BESS-Outbound-Spam-Score: 0.00 X-BESS-Outbound-Spam-Report: Code version 3.2, rules version 3.2.2.250837 [from cloudscan20-157.eu-central-1b.ess.aws.cudaops.com] Rule breakdown below pts rule name description ---- ---------------------- -------------------------------- 0.00 HTML_MESSAGE BODY: HTML included in message 0.00 BSF_BESS_OUTBOUND META: BESS Outbound X-BESS-Outbound-Spam-Status: SCORE=0.00 using account:ESS119469 scores of KILL_LEVEL=7.0 tests=HTML_MESSAGE, BSF_BESS_OUTBOUND X-BESS-BRTS-Status: 1 Received-SPF: pass client-ip=18.184.203.242; envelope-from=t.hilke@rollomatic.ch; helo=egress-ip19a.ess.de.barracuda.com X-Spam_score_int: -27 X-Spam_score: -2.8 X-Spam_bar: -- X-Spam_report: (-2.8 / 5.0 requ) BAYES_00=-1.9, DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, DKIM_VALID_EF=-0.1, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_LOW=-0.7, RCVD_IN_MSPIKE_H5=0.001, RCVD_IN_MSPIKE_WL=0.001, SPF_HELO_NONE=0.001, SPF_PASS=-0.001 autolearn=ham autolearn_force=no X-Spam_action: no action X-Mailman-Approved-At: Fri, 15 Sep 2023 09:04:01 -0400 X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list X-BeenThere: bug-gnu-emacs@gnu.org List-Id: "Bug reports for GNU Emacs, the Swiss army knife of text editors" List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: bug-gnu-emacs-bounces+geb-bug-gnu-emacs=m.gmane-mx.org@gnu.org Original-Sender: bug-gnu-emacs-bounces+geb-bug-gnu-emacs=m.gmane-mx.org@gnu.org Xref: news.gmane.io gmane.emacs.bugs:270537 Archived-At: --_004_125a2ffc4cc34a3eb03ecebb4dfc2f9frollomaticch_ Content-Type: multipart/alternative; boundary="_000_125a2ffc4cc34a3eb03ecebb4dfc2f9frollomaticch_" --_000_125a2ffc4cc34a3eb03ecebb4dfc2f9frollomaticch_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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 =3D ? and 'column_name_1' =3D ? and 'column_name_2' =3D ? 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 --_000_125a2ffc4cc34a3eb03ecebb4dfc2f9frollomaticch_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi everyone,<= /span>

 =

I had the opportunity of= using sqlite-mode recently, which is really

handy for quickly inspec= ting 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 misinterprete= d by sqlite, and eventually do nothing. The query

  is of the form &q= uot;REMOVE FROM table_name WHERE rowid =3D ? and

  'column_name_1' = =3D ? and 'column_name_2' =3D ? and ..."  From<= /p>

  <= span lang=3D"EN-GB">https://www.sqlite.org/lang_keywords.html, if I understand

  correctly, the qu= oted column names in the WHERE clause are

  interpreted as st= rings from sqlite, and as a result not a single row

  is ever matched a= nd deleted.

 =

- The connection to the = sqlite database (file) is never closed, even

  when the buffer i= s killed. As sqlite--db is a local variable,

  it's not even pos= sible 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 modific= ations 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 sche= ma of the database in a more structured way

than parsing the content= of the sqlite_master table:

https://www.sqlite.org/lang_keywords.ht= ml.

 =

I hope it helps, and tha= nks for all the great work!

 =

Thomas Hilke

--_000_125a2ffc4cc34a3eb03ecebb4dfc2f9frollomaticch_-- --_004_125a2ffc4cc34a3eb03ecebb4dfc2f9frollomaticch_ Content-Type: application/octet-stream; name="0001-Remove-column-quoting-and-close-sqlite-db-on-buffer-.patch" Content-Description: 0001-Remove-column-quoting-and-close-sqlite-db-on-buffer-.patch Content-Disposition: attachment; filename="0001-Remove-column-quoting-and-close-sqlite-db-on-buffer-.patch"; size=1690; creation-date="Fri, 15 Sep 2023 08:31:06 GMT"; modification-date="Fri, 15 Sep 2023 08:31:06 GMT" Content-Transfer-Encoding: base64 RnJvbSBmYTdiN2RmOWYxNzRiMjYxMmE2MjAyMjMyNjZlYTQwMTU2NDRlMTc3IE1vbiBTZXAgMTcg MDA6MDA6MDAgMjAwMQpGcm9tOiBUaG9tYXMgSGlsa2UgPHQuaGlsa2VAcm9sbG9tYXRpYy5jaD4K RGF0ZTogRnJpLCAxNSBTZXAgMjAyMyAxMDozMDoyNSArMDIwMApTdWJqZWN0OiBbUEFUQ0hdIFJl bW92ZSBjb2x1bW4gcXVvdGluZyBhbmQgY2xvc2Ugc3FsaXRlIGRiIG9uIGJ1ZmZlciBraWxsCgot LS0KIGxpc3Avc3FsaXRlLW1vZGUuZWwgfCAxOCArKy0tLS0tLS0tLS0tLS0tLS0KIDEgZmlsZSBj aGFuZ2VkLCAyIGluc2VydGlvbnMoKyksIDE2IGRlbGV0aW9ucygtKQoKZGlmZiAtLWdpdCBhL2xp c3Avc3FsaXRlLW1vZGUuZWwgYi9saXNwL3NxbGl0ZS1tb2RlLmVsCmluZGV4IDhjYjk0NDg1MzY5 Li4zOGU5Zjg0Yjg0MiAxMDA2NDQKLS0tIGEvbGlzcC9zcWxpdGUtbW9kZS5lbAorKysgYi9saXNw L3NxbGl0ZS1tb2RlLmVsCkBAIC02Myw2ICs2Myw3IEBACiAgIChzZXRxLWxvY2FsIHNxbGl0ZS0t ZGIgKHNxbGl0ZS1vcGVuIGZpbGUpKQogICAodW5sZXNzIChzcWxpdGVwIHNxbGl0ZS0tZGIpCiAg ICAgKGVycm9yICJgc3FsaXRlLW9wZW4nIGZhaWxlZCB0byBvcGVuIFNRTGl0ZSBmaWxlIikpCisg IChhZGQtaG9vayAna2lsbC1idWZmZXItaG9vayAobGFtYmRhICgpIChzcWxpdGUtY2xvc2Ugc3Fs aXRlLS1kYikpIG5pbCB0KQogICAoc3FsaXRlLW1vZGUtbGlzdC10YWJsZXMpKQogCiAoZGVmdW4g c3FsaXRlLW1vZGUtbGlzdC10YWJsZXMgKCkKQEAgLTEzNSwyMiArMTM2LDcgQEAKIAogKGRlZnVu IHNxbGl0ZS1tb2RlLS1jb2x1bW4tbmFtZXMgKHRhYmxlKQogICAiUmV0dXJuIGEgbGlzdCBvZiB0 aGUgY29sdW1uIG5hbWVzIGZvciBUQUJMRS4iCi0gIChsZXQgKChzcWwKLSAgICAgICAgIChjYWFy Ci0gICAgICAgICAgKHNxbGl0ZS1zZWxlY3QKLSAgICAgICAgICAgc3FsaXRlLS1kYgotICAgICAg ICAgICAic2VsZWN0IHNxbCBmcm9tIHNxbGl0ZV9tYXN0ZXIgd2hlcmUgdGJsX25hbWUgPSA/IEFO RCB0eXBlID0gJ3RhYmxlJyIKLSAgICAgICAgICAgKGxpc3QgdGFibGUpKSkpKQotICAgICh3aXRo LXRlbXAtYnVmZmVyCi0gICAgICAoaW5zZXJ0IHNxbCkKLSAgICAgIChtYXBjYXIgIydzdHJpbmct dHJpbQotICAgICAgICAgICAgICAoc3BsaXQtc3RyaW5nCi0gICAgICAgICAgICAgICA7OyBFeHRy YWN0IHRoZSBhcmdzIHRvIENSRUFURSBUQUJMRS4gIFBvaW50IGlzCi0gICAgICAgICAgICAgICA7 OyBjdXJyZW50bHkgYXQgaXRzIGVuZC4KLSAgICAgICAgICAgICAgIChidWZmZXItc3Vic3RyaW5n Ci0gICAgICAgICAgICAgICAgKDEtIChwb2ludCkpICAgICAgICAgICAgICAgICAgICAgICAgICA7 IHJpZ2h0IGJlZm9yZSApCi0gICAgICAgICAgICAgICAgKDErIChwcm9nbiAoYmFja3dhcmQtc2V4 cCkgKHBvaW50KSkpKSA7IHJpZ2h0IGFmdGVyICgKLSAgICAgICAgICAgICAgICIsIikpKSkpCisg IChtYXBjYXIgKGxhbWJkYSAocm93KSAobnRoIDEgcm93KSkgKHNxbGl0ZS1zZWxlY3Qgc3FsaXRl LS1kYiAoZm9ybWF0ICJwcmFnbWEgdGFibGVfaW5mbyglcykiIHRhYmxlKSkpKQogCiAoZGVmdW4g c3FsaXRlLW1vZGUtbGlzdC1kYXRhICgpCiAgICJMaXN0IHRoZSBkYXRhIGZyb20gdGhlIHRhYmxl IHVuZGVyIHBvaW50LiIKLS0gCjIuNDEuMC53aW5kb3dzLjMKCg== --_004_125a2ffc4cc34a3eb03ecebb4dfc2f9frollomaticch_--