From debbugs-submit-bounces@debbugs.gnu.org Fri Sep 15 09:04:08 2023 Received: (at submit) by debbugs.gnu.org; 15 Sep 2023 13:04:08 +0000 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 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 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 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 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== 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 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 From: Thomas Hilke To: "bug-gnu-emacs@gnu.org" Subject: Sqlite-mode issue deleting records and closing database Thread-Topic: Sqlite-mode issue deleting records and closing database Thread-Index: AdnnsE7HriQvk2YrStuXk1fAlE/kmQ== Date: Fri, 15 Sep 2023 09:02:49 +0000 Message-ID: <125a2ffc4cc34a3eb03ecebb4dfc2f9f@rollomatic.ch> Accept-Language: fr-CH, en-US Content-Language: en-US X-MS-Has-Attach: yes X-MS-TNEF-Correlator: x-originating-ip: [192.168.19.61] Content-Type: multipart/mixed; boundary="_004_125a2ffc4cc34a3eb03ecebb4dfc2f9frollomaticch_" MIME-Version: 1.0 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-Spam-Score: 0.9 (/) X-Debbugs-Envelope-To: submit 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 List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -0.1 (/) --_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_-- From debbugs-submit-bounces@debbugs.gnu.org Fri Sep 15 11:45:41 2023 Received: (at control) by debbugs.gnu.org; 15 Sep 2023 15:45:41 +0000 Received: from localhost ([127.0.0.1]:44510 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1qhB0n-0005rQ-9z for submit@debbugs.gnu.org; Fri, 15 Sep 2023 11:45:41 -0400 Received: from eggs.gnu.org ([2001:470:142:3::10]:46268) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1qhB0g-0005OR-6c; Fri, 15 Sep 2023 11:45:38 -0400 Received: from fencepost.gnu.org ([2001:470:142:3::e]) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1qhB0T-0000k8-9m; Fri, 15 Sep 2023 11:45:21 -0400 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=gnu.org; s=fencepost-gnu-org; h=References:Subject:In-Reply-To:To:From:Date: mime-version; bh=GF7XIfA/FSnT53uloLhubO4Jz7cteYciDtCfWcVkrF8=; b=WWj5gWHVDHOL +wvD/gvTpOCEoF+Q9b/Rwe/K17PsxhxenlrctVeUfm4qeM2PYNDilx4yXJ/mk0Xr46mIRcPIWnrPG qnCPlc8EqIE8KkkL+g2eszYihLFgAVDObLWLJyt+jw+QhrjQOuEwK1Mdx9U1WuprAXpc1zJhVJ+CB RlLo78aZKTSJAy4Yezx9d52+TbZDNkQrtKfrc9CfEqiXe6KkQgGjBxQFNhsX6CN5RyIq+PUi/ufOv kjB45vVnpS9F9w1QKdCadUn/eIh6f5dmz0nG9SSxNbKELsPKwz/fdYm8N24RfH47qgCCEYbe7DWKC 94vzMUWQn0hZUPD6DnABDA==; Date: Fri, 15 Sep 2023 18:45:11 +0300 Message-Id: <83jzsrjv94.fsf@gnu.org> From: Eli Zaretskii To: Thomas Hilke In-Reply-To: (bug-gnu-emacs@gnu.org) Subject: Re: bug#65999: [PATCH] Remove column quoting and close sqlite db on buffer kill References: X-Spam-Score: -2.3 (--) X-Debbugs-Envelope-To: control Cc: 65999@debbugs.gnu.org X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -3.3 (---) 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" > > 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. From debbugs-submit-bounces@debbugs.gnu.org Sat Sep 16 06:24:44 2023 Received: (at 65998) by debbugs.gnu.org; 16 Sep 2023 10:24:44 +0000 Received: from localhost ([127.0.0.1]:45464 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1qhSTk-00040V-4F for submit@debbugs.gnu.org; Sat, 16 Sep 2023 06:24:44 -0400 Received: from eggs.gnu.org ([2001:470:142:3::10]:59172) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1qhSTg-00040C-95 for 65998@debbugs.gnu.org; Sat, 16 Sep 2023 06:24:42 -0400 Received: from fencepost.gnu.org ([2001:470:142:3::e]) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1qhSTT-0001SA-8x; Sat, 16 Sep 2023 06:24:27 -0400 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=gnu.org; s=fencepost-gnu-org; h=References:Subject:In-Reply-To:To:From:Date: mime-version; bh=A3eAybH6OFvIEV2cn9geWdG3YmQbTTpeRTeHRKhN3oE=; b=GvNfcP4ohYrl h1oyOe/tmXbwq+dGoWpLhlNFjlw0wHDjEAWuMt1LasCEZSOsMYFSMEi8Hm3WXRKMvWmOaleylOqvL RdMhOpkuasM4uYRtdXr3IDiDR5aEwUFc+elUz+cV1pTvN9KfBbTD/f49cuavmflMisAWOquX9uubc Bpcjl+y1yjWyhNc94x5TJQ8MJt2gIYDfpIVMcCr16xbdmriRKAAOuM3ZN9gAJhFVFQgkhS/FMLLA7 yiMKH7zYBg5BlwMTra5LWlh8cFAE2mRjU1YUvKRtWE7C2iM6xcZOzgpCObuPi/PFkr4aT/vjamk8p lMqOimmuNw8dQ6feDo/w4Q==; Date: Sat, 16 Sep 2023 13:24:20 +0300 Message-Id: <83h6nuiffv.fsf@gnu.org> From: Eli Zaretskii To: Thomas Hilke , Lars Ingebrigtsen In-Reply-To: <125a2ffc4cc34a3eb03ecebb4dfc2f9f@rollomatic.ch> (bug-gnu-emacs@gnu.org) Subject: Re: bug#65998: Sqlite-mode issue deleting records and closing database References: <125a2ffc4cc34a3eb03ecebb4dfc2f9f@rollomatic.ch> X-Spam-Score: -2.3 (--) X-Debbugs-Envelope-To: 65998 Cc: 65998@debbugs.gnu.org X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -3.3 (---) > Date: Fri, 15 Sep 2023 09:02:49 +0000 > From: Thomas Hilke via "Bug reports for GNU Emacs, > the Swiss army knife of text editors" > > 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. 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? From debbugs-submit-bounces@debbugs.gnu.org Sat Sep 16 10:08:08 2023 Received: (at 65998) by debbugs.gnu.org; 16 Sep 2023 14:08:08 +0000 Received: from localhost ([127.0.0.1]:48200 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1qhVxw-0004fx-HI for submit@debbugs.gnu.org; Sat, 16 Sep 2023 10:08:08 -0400 Received: from mail-lj1-x22b.google.com ([2a00:1450:4864:20::22b]:55583) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1qhVxt-0004fR-Ma for 65998@debbugs.gnu.org; Sat, 16 Sep 2023 10:08:06 -0400 Received: by mail-lj1-x22b.google.com with SMTP id 38308e7fff4ca-2bcb89b4767so47543641fa.3 for <65998@debbugs.gnu.org>; Sat, 16 Sep 2023 07:07:58 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1694873272; x=1695478072; darn=debbugs.gnu.org; h=cc:to:subject:message-id:date:mime-version:references:in-reply-to :from:from:to:cc:subject:date:message-id:reply-to; bh=UuVXDkzVV2P9Ugm2vDnU4/gJ6LwQuMYgND0InY9p7Qg=; b=Qdt6JTcjEMp4UIqiif1wvFUJ90+Q/WXPd8vVp9Dk6BvEv0BIEDiXS9OMbx07MOUl4G lV9rC2/WmXm+iLZ3EhnLHi+CKRKn48ft6jZpt0HaiwjCzr8910peljUSo4QPISiXti+v QyUBUgfOk0QQChuDwrTXu74S4RXJj4KNgCDKmZD80HB9gZoNRhLYYh5g880eXQu7Rlat 9osXnwrNtYzXLpiLkkBRLUL/CEAAJRpaH0vpdPhg65cmquv9o2Vtp205xmzp48turb6Q +br/+5XQBbVknk0kA0eTpWnBPwKpQZjzZHjZq574AojxXvyDhD3bC52NcsJQcb0usaAe WrWg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1694873272; x=1695478072; h=cc:to:subject:message-id:date:mime-version:references:in-reply-to :from:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=UuVXDkzVV2P9Ugm2vDnU4/gJ6LwQuMYgND0InY9p7Qg=; b=AolPdwxoUlBGAT4NV23qS9V1sNh3frNPtFjoIcyOf+iCUXZj8ar5eJ3SXngd9jF9GQ gqrtT/CEPcnz6lX8q45gpFs6PjsSTr+3AsBq9mRtsEnF5V2SVid0jGqz20ck459HPAIk uQhLYmdurdm8REtCyaBh/LK0RFqqSZVOguzJsb09Roen84sWmnk0Ut1dCwHD3un0QY6L BclRTVaaAI483TABQfyiczPUvca6kVLDcrr73QMG4nUF01JPyh8feLanMpgyJkxOIxJl toFxPlHcdoYF32lLJZcni2+ohiiLZLpWHOrCAE+5l1WTeD9d9Ue+oH8Iy7J9mCf5Pbub TQLg== X-Gm-Message-State: AOJu0YxAwgXMf/IVsuhJeHZdAGYc5t10KGDaVdYqDgzSpNwNuShEXmXE 9YPyTLVbpHQIvIaB2JihQU34+vNwijAgs0+big4= X-Google-Smtp-Source: AGHT+IFjQ0BjIH0y/PTG/smY16x3P97RdYSM+VlRTgOouvlaICVzM9Y7Vt17zWTMSB4i+SROUmGKV1RectP1Li0UZaE= X-Received: by 2002:a2e:86c6:0:b0:2bd:58b:3a0b with SMTP id n6-20020a2e86c6000000b002bd058b3a0bmr3366069ljj.50.1694873272275; Sat, 16 Sep 2023 07:07:52 -0700 (PDT) Received: from 753933720722 named unknown by gmailapi.google.com with HTTPREST; Sat, 16 Sep 2023 07:07:51 -0700 From: Stefan Kangas In-Reply-To: <83h6nuiffv.fsf@gnu.org> References: <125a2ffc4cc34a3eb03ecebb4dfc2f9f@rollomatic.ch> <83h6nuiffv.fsf@gnu.org> MIME-Version: 1.0 Date: Sat, 16 Sep 2023 07:07:51 -0700 Message-ID: Subject: Re: bug#65998: Sqlite-mode issue deleting records and closing database To: Eli Zaretskii , Thomas Hilke , Lars Ingebrigtsen Content-Type: text/plain; charset="UTF-8" X-Spam-Score: 0.0 (/) X-Debbugs-Envelope-To: 65998 Cc: 65998@debbugs.gnu.org X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -1.0 (-) Eli Zaretskii 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. Don't we have unit tests in place for this stuff, though? Perhaps we should see this as an opportunity to add some... From debbugs-submit-bounces@debbugs.gnu.org Sun Sep 17 06:06:10 2023 Received: (at 65998-done) by debbugs.gnu.org; 17 Sep 2023 10:06:10 +0000 Received: from localhost ([127.0.0.1]:49094 helo=debbugs.gnu.org) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1qhofJ-0003NY-Jd for submit@debbugs.gnu.org; Sun, 17 Sep 2023 06:06:09 -0400 Received: from eggs.gnu.org ([2001:470:142:3::10]:33910) by debbugs.gnu.org with esmtp (Exim 4.84_2) (envelope-from ) id 1qhofH-0003NM-GZ for 65998-done@debbugs.gnu.org; Sun, 17 Sep 2023 06:06:08 -0400 Received: from fencepost.gnu.org ([2001:470:142:3::e]) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1qhof0-0003TP-4o; Sun, 17 Sep 2023 06:05:53 -0400 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=gnu.org; s=fencepost-gnu-org; h=References:Subject:In-Reply-To:To:From:Date: mime-version; bh=GXR49vn12YAIvr6liUNtMciHeD4R7mdR4Ycf3xnZLuE=; b=GLkwR8DHXT7L T9KGWKFvPcLERL0a83ORQFYQPUzJw0Z87ZXlb6YeXypUTW7PbTk5oHf+3XKniXfI8sEL271lDaf9k YATgLkl2pRxpuFaNxnp9Hd4Uz0VdqCKvZKEVMXhzKBbZmWknm/A6jQRkIaSHRyc/irUdwowo2NII0 3prI3ld7VOKY5+dxoPyJ81qUvBvnG6Va9q46Q8tMBnf1Zrrg/xaLeBVcPU14+2DtSejFBC2sd+WCR /D0/kfQ2jtz+rRWoCf62ud3ecxV09TZ6Mta7k1jzHyMUPH3Ij/l4QBdHMipqBCBacBvQzmaujFziA Z34i3mui3ovW6Tx9exjUVA==; Date: Sun, 17 Sep 2023 13:05:47 +0300 Message-Id: <8334zdglms.fsf@gnu.org> From: Eli Zaretskii To: Stefan Kangas In-Reply-To: (message from Stefan Kangas on Sat, 16 Sep 2023 07:07:51 -0700) Subject: Re: bug#65998: Sqlite-mode issue deleting records and closing database References: <125a2ffc4cc34a3eb03ecebb4dfc2f9f@rollomatic.ch> <83h6nuiffv.fsf@gnu.org> X-Spam-Score: -2.3 (--) X-Debbugs-Envelope-To: 65998-done Cc: 65998-done@debbugs.gnu.org, larsi@gnus.org, t.hilke@rollomatic.ch X-BeenThere: debbugs-submit@debbugs.gnu.org X-Mailman-Version: 2.1.18 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: debbugs-submit-bounces@debbugs.gnu.org Sender: "Debbugs-submit" X-Spam-Score: -3.3 (---) > From: Stefan Kangas > Date: Sat, 16 Sep 2023 07:07:51 -0700 > Cc: 65998@debbugs.gnu.org > > Eli Zaretskii 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. From unknown Sat Aug 09 01:28:59 2025 Received: (at fakecontrol) by fakecontrolmessage; To: internal_control@debbugs.gnu.org From: Debbugs Internal Request Subject: Internal Control Message-Id: bug archived. Date: Sun, 15 Oct 2023 11:24:11 +0000 User-Agent: Fakemail v42.6.9 # This is a fake control message. # # The action: # bug archived. thanks # This fakemail brought to you by your local debbugs # administrator