From mboxrd@z Thu Jan 1 00:00:00 1970 Path: news.gmane.io!.POSTED.blaine.gmane.org!not-for-mail From: Jean Louis Newsgroups: gmane.emacs.devel Subject: Re: [NonGNU ELPA] New package: sqlite3 Date: Tue, 21 Mar 2023 09:51:23 +0300 Message-ID: References: <87cz5o6csk.fsf@bernoul.li> <87mt4swxsw.fsf@posteo.net> <875ybd7mbh.fsf@bernoul.li> <87y1nzb95o.fsf@posteo.net> Mime-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit Injection-Info: ciao.gmane.io; posting-host="blaine.gmane.org:116.202.254.214"; logging-data="26875"; mail-complaints-to="usenet@ciao.gmane.io" User-Agent: Mutt/2.2.9+54 (af2080d) (2022-11-21) Cc: Jonas Bernoulli , emacs-devel@gnu.org To: Philip Kaludercic Original-X-From: emacs-devel-bounces+ged-emacs-devel=m.gmane-mx.org@gnu.org Tue Mar 21 08:22:17 2023 Return-path: Envelope-to: ged-emacs-devel@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 1peWJz-0006jG-Dd for ged-emacs-devel@m.gmane-mx.org; Tue, 21 Mar 2023 08:22:15 +0100 Original-Received: from localhost ([::1] helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1peWJI-0007QS-Ak; Tue, 21 Mar 2023 03:21:32 -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 1peWJD-0007JS-MC for emacs-devel@gnu.org; Tue, 21 Mar 2023 03:21:28 -0400 Original-Received: from stw1.rcdrun.com ([217.170.207.13]) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1peWJ9-0005w0-Io for emacs-devel@gnu.org; Tue, 21 Mar 2023 03:21:27 -0400 Original-Received: from localhost ([::ffff:197.239.6.79]) (AUTH: PLAIN admin, TLS: TLS1.3,256bits,ECDHE_RSA_AES_256_GCM_SHA384) by stw1.rcdrun.com with ESMTPSA id 00000000000F5CB7.0000000064195AD1.0000504B; Tue, 21 Mar 2023 00:20:48 -0700 Mail-Followup-To: Philip Kaludercic , Jonas Bernoulli , emacs-devel@gnu.org Content-Disposition: inline In-Reply-To: <87y1nzb95o.fsf@posteo.net> Received-SPF: pass client-ip=217.170.207.13; envelope-from=bugs@gnu.support; helo=stw1.rcdrun.com X-Spam_score_int: -17 X-Spam_score: -1.8 X-Spam_bar: - X-Spam_report: (-1.8 / 5.0 requ) BAYES_00=-1.9, RCVD_IN_SBL=0.141, SPF_HELO_PASS=-0.001, SPF_PASS=-0.001 autolearn=no autolearn_force=no X-Spam_action: no action X-BeenThere: emacs-devel@gnu.org X-Mailman-Version: 2.1.29 Precedence: list List-Id: "Emacs development discussions." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: emacs-devel-bounces+ged-emacs-devel=m.gmane-mx.org@gnu.org Original-Sender: emacs-devel-bounces+ged-emacs-devel=m.gmane-mx.org@gnu.org Xref: news.gmane.io gmane.emacs.devel:304628 Archived-At: * Philip Kaludercic [2023-03-14 19:17]: > Jonas Bernoulli writes: > > >> Do you have a link to the package you are talking about? > > > > Ups, here you go: https://github.com/pekingduck/emacs-sqlite3-api > > Would you happen to know if there is some rx-like, s-expression based > language for constructing SQL queries. I am not looking for anything > generic, just a way to avoid writing long strings. While such packages exists, for me I do not find them usable as then I have to forget about the SQL and learn about the new Emacs Lisp structure that is to correspond to SQL. I see personally no benefit in that. Often, I tend to do 2 steps SQL, one for preliminary query, where I fetch only the list of items as in this example: (defun cf-people-by-last-name (&optional last-name) "Return list of people by last name." (interactive) (let* ((last-name (or last-name (rcd-ask-get "Last name: "))) (id-list (rcd-sql-list "SELECT people_id FROM people WHERE people_name ~* $1" cf-db last-name)) (title (format "People by last name `%s'" last-name))) (cf-people-by-id-list id-list title))) And then the other one, more complex which is used for the final report, and from which I only cut this one part: (sql (format "SELECT DISTINCT ON (people_id) people_id, get_full_contacts_name(people_id), coalesce((SELECT get_contacts_name(peoplerelations_people2) FROM peoplerelations WHERE peoplerelations_people1 = people_id ORDER BY peoplerelations_default, peoplerelations_id LIMIT 1), '>>>UNKNOWN<<<') FROM people WHERE people_id IN (%s) ORDER BY people_id DESC" sql-id-list)) By using the two step approach, first selecting IDs necessary, and then running the main report using those ID numbers, I avoid writing larger single functions. In general SQL is so much simpler and logically understandable as compared to Emacs Lisp, writing SQL is beneficial as then you get the habit and can run queries in any other programming language, or directly on command line or in programs, first making sure you are doing it right. -- Jean Take action in Free Software Foundation campaigns: https://www.fsf.org/campaigns In support of Richard M. Stallman https://stallmansupport.org/