Re: packages submissions : ob-sql-session and org-blog

Re: packages submissions : ob-sql-session and org-blog

[Phil Estival]

* [2024-09-11 05:01] Adam Porter:
> Hi Phil,
> 
> A quick, initial suggestion: it would be good to reformat the Lisp code 
> in both projects, as I see several cases of incorrect indentation, 
> hanging parens, extra blank lines, etc, and some of them would make the 
> code hard to read.  For my Lisp editing, I use aggressive-indent-mode, 
> which ensures that the code is always indented correctly.  For some 
> other reformatting, Oleh Krehel's Lispy package has commands to help.

Hi Adam,

> Also, for org-sql-session (which sounds very useful), it would be good
> to compare and contrast it with the package it's meant to supersede.

The following comparison was added to the readme file.

ob-sql-mode :
- is very simple : forward the sql source through `sql-redirect'
- has a test suite
- but gives clunky output
- no :results table
- sql client shell commands messes up output
- prompt again for connection parameters when restarting a session

ob-sql-session :
- handle large results
- has :results tables
- accept header variables (:var)
- accept sql client shell commands
- keep login parameters
- prompt only for blank connection parameters
- can use `with-environment-variables'
- provide some more tests

> since it's meant to integrate with org-babel, it might be good to 
> discuss its inclusion on the Org mailing list also.

CC-ing the Org mailing list.

Phil

Re: packages submissions : ob-sql-session and org-blog

[Adam Porter]

Hi Phil,

On 9/17/24 05:00, Phil Estival wrote:

>> Also, for org-sql-session (which sounds very useful), it would be good
>> to compare and contrast it with the package it's meant to supersede.
> 
> The following comparison was added to the readme file.
> 
> ob-sql-mode :
> - is very simple : forward the sql source through `sql-redirect'
> - has a test suite
> - but gives clunky output
> - no :results table
> - sql client shell commands messes up output
> - prompt again for connection parameters when restarting a session
> 
> ob-sql-session :
> - handle large results
> - has :results tables
> - accept header variables (:var)
> - accept sql client shell commands
> - keep login parameters
> - prompt only for blank connection parameters
> - can use `with-environment-variables'
> - provide some more tests
> 
>> since it's meant to integrate with org-babel, it might be good to 
>> discuss its inclusion on the Org mailing list also.
To be clear, this makes 3 implementations of SQL support for Org Babel 
that I know of:

1. The built-in Org Babel SQL support, aka ob-sql, documented at 
<https://orgmode.org/worg/org-contrib/babel/languages/ob-doc-sql.html>

2. ob-sql-mode, hosted at <https://github.com/nikclayton/ob-sql-mode>, 
which advertises itself as "an alternative backend for Org-Babel SQL SRC 
blocks that uses sql-mode to evaluate the query instead of Org-Babel's 
built-in SQL backends."

3. Your new ob-sql-session library.

You've mentioned how your library compares to ob-sql-mode, but how does 
it compare to the built-in ob-sql library?

Thanks,
Adam

Re: package submission : ob-sql-session

[Phil Estival]

Hi Adam,

* [2024-09-18 00:25] Adam Porter:
>
> Hi Phil,
> 
> On 9/17/24 05:00, Phil Estival wrote:
> 
>>> Also, for org-sql-session (which sounds very useful), it would be good
>>> to compare and contrast it with the package it's meant to supersede.
>>
>> The following comparison was added to the readme file.
>>
>> ob-sql-mode :
>> - is very simple : forward the sql source through `sql-redirect'
>> - has a test suite
>> - but gives clunky output
>> - no :results table
>> - sql client shell commands messes up output
>> - prompt again for connection parameters when restarting a session
>>
>> ob-sql-session :
>> - handle large results
>> - has :results tables
>> - accept header variables (:var)
>> - accept sql client shell commands
>> - keep login parameters
>> - prompt only for blank connection parameters
>> - can use `with-environment-variables'
>> - provide some more tests
>>
>>> since it's meant to integrate with org-babel, it might be good to 
>>> discuss its inclusion on the Org mailing list also.
> To be clear, this makes 3 implementations of SQL support for Org Babel 
> that I know of:
> 
> 1. The built-in Org Babel SQL support, aka ob-sql, documented at 
> <https://orgmode.org/worg/org-contrib/babel/languages/ob-doc-sql.html>
> 
> 2. ob-sql-mode, hosted at <https://github.com/nikclayton/ob-sql-mode>, 
> which advertises itself as "an alternative backend for Org-Babel SQL SRC 
> blocks that uses sql-mode to evaluate the query instead of Org-Babel's 
> built-in SQL backends."
> 
> 3. Your new ob-sql-session library.
> 
> You've mentioned how your library compares to ob-sql-mode, but how does 
> it compare to the built-in ob-sql library?
> 

Here is a comparison with ob-sql-mode

ob-sql-session exists is for session support, which is in the TODOs
of ob-sql.

- ob-sql command execution relies on org-babel-eval
   (→ process-file → call-process).

- ob-sql-session runs an inferior process (in which
   sqli-interactive-mode can be activated when needed).
   The process output is filtered (e.g. results and prompts).
   When a session is demanded, this shell stays open for further commands
   and can keep a state (typically, when given special SQL commands).

|-----------+----------------------------+----------------------------|
|           | ob-sql                     | ob-sql-session             |
|-----------+----------------------------+----------------------------|
| Feat.     | - cmdline                  | - support for sessions     |
|           | - colnames as header arg   | - optionnal colnames       |
|-----------+----------------------------+----------------------------|
| TODO      |                            |                            |
|-----------+----------------------------+----------------------------|
|           | - support for sessions     | - colnames as header arg   |
|           | - support for more engines | - support for more engines |
|-----------+----------------------------+----------------------------|
| engines   |                            |                            |
| supported |                            |                            |
|-----------+----------------------------+----------------------------|
|           | - mysql                    | - Postgresql               |
|           | - dbi                      | - sqlite                   |
|           | - mssql                    |                            |
|           | - sqsh                     |                            |
|           | - postgresql               |                            |
|           | - oracle                   |                            |
|           | - vertica                  |                            |
|           | - saphana                  |                            |
|-----------+----------------------------+----------------------------|

ob-sql-session adapts sql-connect of sql.el by declaring
ob-sql-connect, in order to prompt only for missing connection
parameters.

- ob-sql defines org-babel-sql-dbstring-[engine]
    to be provided on a shell command line.

- likewise, ob-sql-session has to define
    - a connection string,
    - the prompt,
    - and the terminal command prefix
      for a every supported SQL client shell (or "engines")

- ob-sql-session requires sql.el.
   With the above defined, it should be compatible
   with most database of the sql.el's zoo. maybe.

Phil