Subject: bug#76025: [PATCH 1/1] * lisp/progmodes/sql.el: login without prompting

From: Phil Estival <[email-masked]>
Date: Sun, 09 Feb 2025 14:01:51 +0100

* [2025-02-07 02:42 +0000] Michael Mauger <[email-masked]>:
> On Thursday, February 6th, 2025 at 7:12 PM, Ihor Radchenko <[email-masked]> wrote:
>> Phil Estival [email-masked] writes:
>>
>> > This is a patch of `sql-product-interactive' in order to connect
>> > without prompting for or asking to confirm connection parameters,
>> > using ones already set and stored in` sql-database', `sql-server',
>> > -user, etc.
>>
>>
>> For context, we want this feature to improve Org babel's sql library
>> (ob-sql) where we need to run session programmatically. See
>> https://list.orgmode.org/orgmode/[email-masked]/
>>
>> CCing sql.el maintainer.
>>

Hello Michael,

> For my context, how are ob-sql "sessions" different from what is
> provided by `sql-product-interactive` buffers?

Similar to other channels org-babel can communicate through, an ob-sql
session (info "(org) Environment of a Code Block") can be characterized
as a channel to an interpreter process through a comint shell buffer,
shared among source blocks in a notebook.

> I see you avoiding `sql-product-interactive` buffers but the result is
> that you are duplicating a lot of logic already present in `sql.el`.

This duplicated logic is the connection to the database server, modified
to avoid the prompts asking to confirm the parameters, except if one is
missing, since these parameters are already written in fields
describing, above the sql commands, what the session is: a named
connection to a database.  These parameters can be variables,
expressions, global to a document or locally scoped to a section, a
paragraph or a single code block.

Bypassing this prompts is necessary to automate the tests of the ob-sql
machinery.

If completion, coloring or additional interactions with the shell are
needed, then `sql-interactive-mode' could be set in the shell ob-sql is
communicating with.  "Could", because, this connection's function was
"borrowed" from sql.el with these slight modifications: not prompting
for parameters, no syntax coloring or completion, which make sense if
no direct interaction is expected in such buffers.

Ihor identified the connection's function as a duplication, and proposed
we streamline the code by relying on the existing function in
sql.el. Now ob-sql sessions do call `sql-product-interactive', hence
this proposal for a patch skipping the prompts.

Asking additional confirmations could be fine as a safeguard, but as I
said /supra/, it's an obstacle for automation and not a necessity when
describing a database in a notebook since these information are already
present in the environment of code blocks.

> I have used babel to drive a sqlite3 in-memory database without the
> ob-sql machinery, so I'm wondering how I must warp my old batch card
> deck way of thinking to understand the problem you are trying to
> solve.

One may need of a few stateful variables to designate schema or to give
names to roles, and so on.  These variables exists in the environment of
the SQL shell.  One way to apply changes of values is to reset the db,
before providing a new environment to a next run of the entire program.
But we can also change those values from inside a running environment
and recall only the code blocks that refers to these variables.  This is
what a "session" covers.

Org-mode, as a programming notebook is a convenient way to work on small
units of code, the blocks, organized in sections, and to combine their
execution.  In many situations, it's more operable and more maneuverable
than the files and folders we are used to think in, and that, in many
ways, we still conform to, due to their presence rooted at the core of
our systems.

As for the in-memory sqlite3 DB, it is an appropriate candidate to test
the correctness of the ob-sql sessions behavior: it's a standard
lightweight package assumed to be /de facto/ available on our operating
systems.

> I do see that you are supporting "engines" not present in `sql.el`;
> have interactive support for these engines been made available as
> ELPA/MELPA/... modules? Should they be?

Which ones? CC'ing Daniel to possibly get more advice on the matter.

> I also see hardcoding of sql command processor cli names; why not use
> the name already customized in sql.el?

Because sql.el was not a requirement to ob-sql.el, but you're right,
when it will be one, we can bind these to the sql-[db]-program. The
probability for someone to need two different cli programs, one in an
interactive shell and a different one in a notebook, for a same database
system is very low, and in such case he or she would still be capable to
set local values to adapt the situation.

> I am very interested in supporting this effort within sql.el if I
> can. I inherited sql.el a couple of decades ago and other than adding
> support for using multiple dialects within the same Emacs instance,
> the feature set is pretty bare-bones. I have some time rn to focus on
> this, so if someone has the time/inclination to expand my vision, I'd
> appreciate it.

I will add that a single interface to all these DB systems helps to
underline common requirements expected from these sql cli.  The case of
table formatting on MariaDB, configurable only at startup through a
command line parameter -- and not as a state variable -- is one such
requirement.

Thanks.

--
Phil Estival