Re: [NonGNU ELPA] New package: sqlite3

[Jean Louis <bugs@gnu.support>]

* 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/