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
