[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: [NonGNU ELPA] New package: sqlite3
From: |
Jean Louis |
Subject: |
Re: [NonGNU ELPA] New package: sqlite3 |
Date: |
Tue, 21 Mar 2023 09:51:23 +0300 |
User-agent: |
Mutt/2.2.9+54 (af2080d) (2022-11-21) |
* Philip Kaludercic <philipk@posteo.net> [2023-03-14 19:17]:
> Jonas Bernoulli <jonas@bernoul.li> 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/
- [NonGNU ELPA] New package: sqlite3, Jonas Bernoulli, 2023/03/04
- Re: [NonGNU ELPA] New package: sqlite3, Philip Kaludercic, 2023/03/04
- Re: [NonGNU ELPA] New package: sqlite3, Jonas Bernoulli, 2023/03/06
- Re: [NonGNU ELPA] New package: sqlite3,
Jean Louis <=
- Re: [NonGNU ELPA] New package: sqlite3, Lynn Winebarger, 2023/03/21
- Re: [NonGNU ELPA] New package: sqlite3, Philip Kaludercic, 2023/03/21
- Re: [NonGNU ELPA] New package: sqlite3, Lynn Winebarger, 2023/03/21
- Re: [NonGNU ELPA] New package: sqlite3, Philip Kaludercic, 2023/03/21
- Re: [NonGNU ELPA] New package: sqlite3, Lynn Winebarger, 2023/03/21
- Re: [NonGNU ELPA] New package: sqlite3, Philip Kaludercic, 2023/03/21
- Re: [NonGNU ELPA] New package: sqlite3, Tomas Hlavaty, 2023/03/21
- Re: [NonGNU ELPA] New package: sqlite3, Lynn Winebarger, 2023/03/21
- Re: [NonGNU ELPA] New package: sqlite3, Philip Kaludercic, 2023/03/22
- Re: [NonGNU ELPA] New package: sqlite3, Lynn Winebarger, 2023/03/22