Re: [PATCH] ob-sql: session

[Phil Estival <pe@7d.nz>]

[-- Attachment #1: Type: text/plain, Size: 2986 bytes --]

* [2025-01-07 19:38] Ihor Radchenko:> Phil Estival <pe@7d.nz> writes:
>
> [...] these `sql-set-product-feature' calls are overriding the defaults
> from sql.el. They will not only affect Org babel blocks, but all the
> interactive SQL sessions in Emacs. Such side effects are not acceptable.
> May we somehow avoid modifying pre-existing sql features?

We do. I replaced them with a plist.  The variables given on the
command line are now set by a preamble upon shell connection, at least
for Postgres.  However, some SQL shells, like Mariadb, can only be
configured through command line parameters. These are global in Emacs
and set by `sql-set-product-feature'. So in order to support these,
the function in charge of opening the connection needs different
parameters. I could get it to run to run by setting `sql-mysql-options' 
to "-s" , e.g. silent, but with an error on the prompt because
(sql-get-product-feature 'mysql :prompt-regexp) is "^mysql> ".
In conclusion, they're not exactly interchangeable even if one holds
several functions that are aliases from the other.

> It is probably OK to add Org-specific settings after prepending them
> with org-. For example, :batch-terminate -> :org-batch-terminate.
> Although, I am not sure what is the benefit of storing these _new_
> settings in the `sql-product-alist'.

Right.

>
>> +(defun ob-sql-session-buffer-live-p (buffer)
>
> May you simply use `org-babel-comint-buffer-livep' instead?

Indeed. And it keeps working when sql-interactive is activated in
this comint buffer.

>> +The buffer naming was shortened from
>> +*[session] engine://user@host/database*,
>> +that clearly identifies the connection from Emacs,
>> +to *SQL [session]* in order to retrieve a session with its
>> +name alone, the other parameters in the header args being
>> +no longer needed while the session stays open."

This comment was related to the need to store the connections
and in the future provide a way to quickly visualize
and manage them. Apparently sql.el also store and retrieve connections.

>> +  (sql-set-product in-engine)
>> ...
>
> Is there any specific reason why you are seemingly re-implementing what
> `sql-product-interactive' does? May we re-use it instead?
>

Yes. When opening a new connection `sql-product-interactive' will
systematically ask for input in the mini-buffer prompt and fill the
required information, suggesting inputs from the ones previously given.

The expected behavior is to silently establish the new connection
with the variables provided when the header arguments are complete,
and ask for a prompt on the mini-buffer only if a variable is missing
or if the client forcefully ask for it.
Also we rather not run `sql-login-hook' as they can have side effects
that would prevent the session to behave as expected.
I will check again if sql.el has a function to recall existing closed
connections without prompting for confirmation.

Please, find attached the updated patch for review.

voilà.

Phil

[-- Attachment #2: 0001-ob-sql-new-variables-and-requirements-for-sessio.patch --]
[-- Type: text/x-patch, Size: 11061 bytes --]

From 9fe8bb8cc3b2df74e27c691a5ef771065bc38d3f Mon Sep 17 00:00:00 2001
From: Phil Estival <pe@7d.nz>
Date: Thu, 16 Jan 2025 12:11:01 +0100
Subject: [PATCH 01/11] org-sql.el: new variables and requirements for session
 support.

* lisp/org-sql.el: requires sql.el for a connection to a session.
Custom variables are declared in a new sub-group ob-babel-sql.
SQL clients are configured by a preamble of commands given to the SQL
shell.  The echo of an SQL ANSI comment is appended to the source
block of SQL commands for comint to detect when the commands
terminate.
---
 lisp/ob-sql.el | 213 +++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 213 insertions(+)

diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 14ca6bc48..c149016cf 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -4,6 +4,7 @@
 
 ;; Author: Eric Schulte
 ;; Maintainer: Daniel Kraus <daniel@kraus.my>
+;; Maintainer: Philippe Estival <pe@7d.nz>
 ;; Keywords: literate programming, reproducible research
 ;; URL: https://orgmode.org
 
@@ -75,6 +76,33 @@
 (org-assert-version)
 
 (require 'ob)
+(require 'sql)
+
+(defvar org-babel-sql-session-start-time)
+(defvar org-sql-session-preamble
+  (list
+   'postgres "\\set ON_ERROR_STOP 1
+\\pset footer off
+\\pset pager off
+\\pset format unaligned"	 )
+  "Command preamble to run upon shell start.")
+(defvar org-sql-session-command-terminated nil)
+(defvar org-sql-session--batch-terminate  "---#"  "To print at the end of a command batch.")
+(defvar org-sql-batch-terminate
+  (list 'sqlite (format ".print %s\n" org-sql-session--batch-terminate)
+        'postgres (format "\\echo %s\n" org-sql-session--batch-terminate))
+  "Print the command batch termination as last command.")
+(defvar org-sql-terminal-command-prefix
+  (list 'sqlite "\\."
+        'postgres "\\\\")
+  "Identify a command for the SQL shell.")
+(defvar org-sql-environment
+  (list 'postgres '(("PGPASSWORD" sql-password))))
+(defvar org-sql-session-clean-output nil
+  "Store the regexp used to clear output (prompt1|termination|prompt2).")
+(defvar org-sql-session-start-time)
+(defvar org-sql-session-command-terminated nil)
+(defvar org-sql-session--batch-terminate  "---#"  "To print at the end of a command batch.")
 
 (declare-function org-table-import "org-table" (file arg))
 (declare-function orgtbl-to-csv "org-table" (table params))
@@ -85,6 +113,24 @@
 (defvar sql-connection-alist)
 (defvar org-babel-default-header-args:sql '())
 
+(defcustom org-sql-run-comint-p 'nil
+  "Run non-session SQL commands through comint if not nil."
+  :type '(boolean)
+  :group 'org-babel-sql
+  :safe t)
+
+(defcustom org-sql-timeout '5.0
+  "Abort on timeout."
+  :type '(number)
+  :group 'org-babel-sql
+  :safe t)
+
+(defcustom org-sql-close-out-temp-buffer-p 'nil
+  "To automatically close sql-out-temp buffer."
+  :type '(boolean)
+  :group 'org-babel-sql
+  :safe t)
+
 (defconst org-babel-header-args:sql
   '((engine	       . :any)
     (out-file	       . :any)
@@ -433,6 +479,173 @@ argument mechanism."
   "Raise an error because Sql sessions aren't implemented."
   (error "SQL sessions not yet implemented"))
 
+(defun org-babel-sql-session-connect (in-engine params session)
+  "Start the SQL client of IN-ENGINE if it has not.
+PARAMS provides the sql connection parameters for a new or
+existing SESSION.  Clear the intermediate buffer from previous
+output, and set the process filter.  Return the comint process
+buffer."
+  (let* ((buffer-name (format "%s" (if (string= session "none") ""
+                                     (format "[%s]" session))))
+         (ob-sql-buffer (format "*SQL: %s*" buffer-name)))
+
+    ;; initiate a new connection
+    (when (not (org-babel-comint-buffer-livep ob-sql-buffer))
+      (save-window-excursion
+        (setq ob-sql-buffer  ; start the client
+              (org-babel-sql-connect in-engine buffer-name params)))
+      (let ((sql-term-proc (get-buffer-process ob-sql-buffer)))
+        (unless sql-term-proc
+          (user-error (format "SQL %s didn't start" in-engine)))
+
+        (with-current-buffer (get-buffer ob-sql-buffer)
+          ;; preamble commands
+          (let ((preamble (plist-get org-sql-session-preamble in-engine)))
+            (when preamble
+              (process-send-string ob-sql-buffer preamble)
+              (comint-send-input))))
+        ;; let the preamble execution finish and be filtered
+        (sleep-for 0.1)))
+
+    ;; set the redirection filter and return the SQL client buffer
+    (set-process-filter (get-buffer-process ob-sql-buffer)
+                        #'org-sql-session-comint-output-filter)
+    (get-buffer ob-sql-buffer)))
+
+(defun org-babel-sql-connect (&optional engine sql-cnx params)
+  "Run ENGINE interpreter as an inferior process.
+SQL-CNX is the client buffer.  This is a variant from sql.el that prompt
+parametrs for authentication only if there's a missing parameter.
+Depending on the sql client the password should also be prompted."
+
+  (setq sql-product(cond
+                    ((assoc engine sql-product-alist) ; Product specified
+                     engine)
+                    (t sql-product))) ; or default to sql-engine
+
+  (when (sql-get-product-feature sql-product :sqli-comint-func)
+    (let (;(buf (sql-find-sqli-buffer sql-product sql-connection)) ; unused yet
+          (sql-server    (cdr (assoc :dbhost params)))
+          ;; (sql-port      (cdr (assoc :port params))) ; todo
+          (sql-database  (cdr (assoc :database params)))
+          (sql-user      (cdr (assoc :dbuser params)))
+          (sql-password  (cdr (assoc :dbpassword params)))
+          (prompt-regexp (sql-get-product-feature engine :prompt-regexp ))
+          (prompt-cont-regexp (sql-get-product-feature engine :prompt-cont-regexp))
+          sqli-buffer
+          rpt)
+      ;; store the regexp used to clear output (prompt1|indicator|prompt2)
+      (setq org-sql-session-clean-output
+            (plist-put org-sql-session-clean-output engine
+                       (concat "\\(" prompt-regexp "\\)"
+                               "\\|\\(" org-sql-session--batch-terminate "\n\\)"
+                               (when prompt-cont-regexp
+                                 (concat "\\|\\(" prompt-cont-regexp "\\)")))))
+      ;; Get credentials.
+      ;; either all fields are provided
+      ;; or there's a specific case were no login is needed
+      ;; or trigger the prompt
+      (or (and sql-database sql-user sql-server)
+          (eq sql-product 'sqlite) ;; sqlite allows in-memory db, w/o login
+          (apply #'sql-get-login
+                 (sql-get-product-feature engine :sqli-login)))
+      ;; depending on client, password is forcefully prompted
+
+      ;; The password wallet returns a function
+      ;; which supplies the password. (untested)
+      (when (functionp sql-password)
+        (setq sql-password (funcall sql-password)))
+
+      ;; Erase previous sql-buffer.
+      ;; Will look for it's prompt to indicate session readyness.
+      (let ((previous-session
+             (get-buffer (format "*SQL: %s*" sql-cnx))))
+        (when previous-session
+          (with-current-buffer
+              previous-session (erase-buffer)))
+
+        (setq sqli-buffer
+              (let ((process-environment (copy-sequence process-environment))
+                    (variables (plist-get org-sql-environment engine)))
+                (mapc (lambda (elem)   ; environment variables, evaluated here
+                        (setenv (car elem) (eval (cadr elem))))
+                      variables)
+                (funcall (sql-get-product-feature engine :sqli-comint-func)
+                         engine
+                         (sql-get-product-feature engine :sqli-options)
+                         (format "SQL: %s" sql-cnx))))
+        (setq sql-buffer (buffer-name sqli-buffer))
+
+        (setq rpt (sql-make-progress-reporter nil "Login"))
+        (with-current-buffer sql-buffer
+          (let ((proc (get-buffer-process sqli-buffer))
+                (secs org-sql-timeout)
+                (step 0.2))
+            (while (and proc
+                        (memq (process-status proc) '(open run))
+                        (or (accept-process-output proc step)
+                            (<= 0.0 (setq secs (- secs step))))
+                        (progn (goto-char (point-max))
+                               (not (re-search-backward
+                                     prompt-regexp 0 t))))
+              (sql-progress-reporter-update rpt)))
+
+          ;; no prompt, connexion failed (and process is terminated)
+          (goto-char (point-max))
+          (unless (re-search-backward prompt-regexp 0 t)
+            (user-error "Connection failed"))) ;is this a _user_ error?
+        ;;(run-hooks 'sql-login-hook) ; don't
+        )
+      (sql-progress-reporter-done rpt)
+      (get-buffer sqli-buffer))))
+
+(defun org-sql-session-format-query (str in-engine)
+  "Process then send the command STR to the SQL process.
+Provide IN-ENGINE to retrieve product features.
+Carefully separate client commands from SQL commands
+Concatenate SQL commands as one line is one way to stop on error.
+Otherwise the entire batch will be emitted no matter what.
+Finnally add the termination command."
+  (concat
+   (let ((commands (split-string str "\n"))
+         (terminal-command
+          (concat "^\s*"
+                  (plist-get org-sql-terminal-command-prefix in-engine))))
+     (mapconcat
+      (lambda(s)
+        (when (not
+               (string-match "\\(^[\s\t]*--.*$\\)\\|\\(^[\s\t]*$\\)" s))
+          (concat (replace-regexp-in-string
+                   "[\t]" "" ; filter tabs
+                   (replace-regexp-in-string "--.*" "" s)) ;; remove comments.
+                  ;; Note: additional filtering is required for Vertica C-style comments.
+                  (when (string-match terminal-command s) "\n"))))
+      commands " " ))
+   ";\n"
+   (plist-get org-sql-batch-terminate in-engine)
+   "\n" ))
+
+(defun org-sql-session-comint-output-filter (_proc string)
+  "Process output STRING of PROC gets redirected to a temporary buffer.
+It is called several times consecutively as the shell outputs and flush
+its message buffer"
+
+  ;; Inserting a result in the sql process buffer (to read it as a
+  ;; regular prompt log) inserts it to the terminal, and as a result the
+  ;; ouput would get passed as input onto the next command line; See
+  ;; `comint-redirect-setup' to possibly fix that,
+  ;; (with-current-buffer (process-buffer proc) (insert output))
+
+  (when (or (string-match org-sql-session--batch-terminate string)
+            (> (time-to-seconds
+                (time-subtract (current-time)
+                               org-sql-session-start-time))
+               org-sql-timeout))
+    (setq org-sql-session-command-terminated t))
+
+  (with-current-buffer (get-buffer-create "*ob-sql-result*")
+    (insert string)))
+
 (provide 'ob-sql)
 
 ;;; ob-sql.el ends here
-- 
2.39.5


[-- Attachment #3: 0001-testing-lisp-test-ob-sql.el-add-4-tests-for-sessions.patch --]
[-- Type: text/x-patch, Size: 2381 bytes --]

From f77222069cb5f098be2e1e19290337b3f2b2bcde Mon Sep 17 00:00:00 2001
From: Phil Estival <pe@7d.nz>
Date: Tue, 7 Jan 2025 04:29:05 +0100
Subject: [PATCH 1/1] testing/lisp/test-ob-sql.el: adds 4 tests for sessions on
 sqlite

* test-ob-sql.el: test sessions. Also adds a macro for testing equality
of a string with the result of a given block.
Note : This is not proper to SQL and should move upwards.
---
 testing/lisp/test-ob-sql.el | 36 ++++++++++++++++++++++++++++++++++++
 1 file changed, 36 insertions(+)

diff --git a/testing/lisp/test-ob-sql.el b/testing/lisp/test-ob-sql.el
index ac8a1ccb2..6afffc1e9 100644
--- a/testing/lisp/test-ob-sql.el
+++ b/testing/lisp/test-ob-sql.el
@@ -49,6 +49,18 @@
                      (org-babel-execute-src-block)))))
      (should-not (string-match-p ,regexp command))))

+
+(defmacro ob-sql/command-equals (str sql-block)
+  "Check the equality of STR with the value returned by the evaluation of SQL-BLOCK."
+  `(let ((strings ,(if (listp str) str `(list ,str)))
+         (command (ob-sql/command (org-test-with-temp-text
+                                      ,sql-block
+                                    (org-babel-next-src-block)
+                                    (org-babel-execute-src-block)))))
+     (dolist (s strings)
+       (should (string= s command)))))
+
+
 ;;; dbish
 (ert-deftest ob-sql/engine-dbi-uses-dbish ()
   (ob-sql/command-should-contain "^dbish " "
@@ -377,5 +389,29 @@
   select * from dummy;
 #+end_src"))

+(ert-deftest ob-sql-sesssion-001/engine-sqlite-headers-off ()
+  (ob-sql/command-equals "" "
+#+begin_src sql :engine sqlite :session A :results raw
+.headers off
+#+end_src"))
+
+(ert-deftest ob-sql-sesssion-002/engine-sqlite-session-continuation ()
+  (ob-sql/command-equals "Emacs\n" "
+#+begin_src sql :engine sqlite :session A :results raw
+select 'Emacs' as 'your preffered editor'
+#+end_src"))
+
+(ert-deftest ob-sql-sesssion-003/engine-sqlite-headers-on ()
+  (ob-sql/command-equals "" "
+#+begin_src sql :engine sqlite :session A :results raw
+.headers on
+#+end_src"))
+
+(ert-deftest ob-sql-sesssion-004/engine-sqlite-session-continuation ()
+  (ob-sql/command-equals "your preffered editor\nEmacs\n" "
+#+begin_src sql :engine sqlite :session A :results raw
+select 'Emacs' as 'your preffered editor'
+#+end_src"))
+
 (provide 'test-ob-sql)
 ;;; test-ob-sql.el ends here
--
2.39.5

[-- Attachment #4: 0002-ob-sql-default-header-arguments-are-declared-in-a-cu.patch --]
[-- Type: text/x-patch, Size: 1131 bytes --]

From a62b04c246b3281df572b4c18642b3b10a2e8453 Mon Sep 17 00:00:00 2001
From: Phil Estival <pe@7d.nz>
Date: Thu, 16 Jan 2025 12:34:43 +0100
Subject: [PATCH 02/11] ob-sql: default header arguments are declared in a
 custom variable

lisp/ob-sql.el: default header arguments are declared in a custom
variable with :options of composite types.
---
 lisp/ob-sql.el | 8 +++++++-
 1 file changed, 7 insertions(+), 1 deletion(-)

diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index c149016cf..634cca566 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -111,7 +111,13 @@
 (declare-function sql-set-product "sql" (product))
 
 (defvar sql-connection-alist)
-(defvar org-babel-default-header-args:sql '())
+(defcustom org-babel-default-header-args:sql  '((:engine . "unset"))
+  "Default header args."
+  :type '(alist :key-type symbol :value-type string
+                :options ("dbi" "sqlite" "mysql" "postgres"
+                          "sqsh" "mssql" "vertica" "oracle" "saphana" ))
+  :group 'org-babel-sql
+  :safe t)
 
 (defcustom org-sql-run-comint-p 'nil
   "Run non-session SQL commands through comint if not nil."
-- 
2.39.5


[-- Attachment #5: 0003-ob-sql-remove-org-assert-version-to-stay-compatible-.patch --]
[-- Type: text/x-patch, Size: 609 bytes --]

From 2ce4c4309583154b5a7f2adbbf13460446205140 Mon Sep 17 00:00:00 2001
From: Phil Estival <pe@7d.nz>
Date: Thu, 16 Jan 2025 15:20:30 +0100
Subject: [PATCH 03/11] ob-sql: remove org-assert-version to stay compatible
 with org 9.5

lisp/ob-sql.el: removal of org-assert-version makes org-macs no longer
needed.
---
 lisp/ob-sql.el | 3 ---
 1 file changed, 3 deletions(-)

diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 634cca566..19a1ae4ea 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -72,9 +72,6 @@

 ;;; Code:

-(require 'org-macs)
-(org-assert-version)
-
 (require 'ob)
 (require 'sql)

--
2.39.5

[-- Attachment #6: 0004-ob-sql-realign-variables-for-improved-readability.patch --]
[-- Type: text/x-patch, Size: 1563 bytes --]

From d96d02c1e8b513899f7fcd67a4933997f85f1120 Mon Sep 17 00:00:00 2001
From: Phil Estival <pe@7d.nz>
Date: Thu, 16 Jan 2025 15:21:28 +0100
Subject: [PATCH 04/11] ob-sql: realign variables for improved readability.

---
 lisp/ob-sql.el | 8 ++++----
 1 file changed, 4 insertions(+), 4 deletions(-)

diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 19a1ae4ea..0a9ea5c87 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -298,12 +298,12 @@ database connections."
 This function is called by `org-babel-execute-src-block'."
   (let* ((result-params (cdr (assq :result-params params)))
          (cmdline (cdr (assq :cmdline params)))
-         (dbhost (org-babel-find-db-connection-param params :dbhost))
-         (dbport (org-babel-find-db-connection-param params :dbport))
-         (dbuser (org-babel-find-db-connection-param params :dbuser))
+         (dbhost     (org-babel-find-db-connection-param params :dbhost))
+         (dbport     (org-babel-find-db-connection-param params :dbport))
+         (dbuser     (org-babel-find-db-connection-param params :dbuser))
          (dbpassword (org-babel-find-db-connection-param params :dbpassword))
          (dbinstance (org-babel-find-db-connection-param params :dbinstance))
-         (database (org-babel-find-db-connection-param params :database))
+         (database   (org-babel-find-db-connection-param params :database))
          (engine (cdr (assq :engine params)))
          (colnames-p (not (equal "no" (cdr (assq :colnames params)))))
          (in-file (org-babel-temp-file "sql-in-"))
--
2.39.5

[-- Attachment #7: 0005-ob-sql-turn-a-unique-cond-expression-to-a-when-expre.patch --]
[-- Type: text/x-patch, Size: 1286 bytes --]

From 7df89c484616703b7e1833260812389030e0bdbc Mon Sep 17 00:00:00 2001
From: Phil Estival <pe@7d.nz>
Date: Thu, 16 Jan 2025 15:28:55 +0100
Subject: [PATCH 05/11] ob-sql: turn a unique (cond expression) to a (when
 expression)

---
 lisp/ob-sql.el | 16 +++++++---------
 1 file changed, 7 insertions(+), 9 deletions(-)

diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 0a9ea5c87..976f673ee 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -413,15 +413,13 @@ SET COLSEP '|'
 	(cond
 	 ((memq (intern engine) '(dbi mysql postgresql postgres saphana sqsh vertica))
 	  ;; Add header row delimiter after column-names header in first line
-	  (cond
-	   (colnames-p
-	    (with-temp-buffer
-	      (insert-file-contents out-file)
-	      (goto-char (point-min))
-	      (forward-line 1)
-	      (insert "-\n")
-	      (setq header-delim "-")
-	      (write-file out-file)))))
+	  (when colnames-p (with-temp-buffer
+	                     (insert-file-contents out-file)
+	                     (goto-char (point-min))
+	                     (forward-line 1)
+	                     (insert "-\n")
+	                     (setq header-delim "-")
+	                     (write-file out-file))))
 	 (t
 	  ;; Need to figure out the delimiter for the header row
 	  (with-temp-buffer
--
2.39.5

[-- Attachment #8: 0006-ob-sql-add-support-for-sessions-in-org-babel-execute.patch --]
[-- Type: text/x-patch, Size: 4447 bytes --]

From a7f4aed08c5a90082a1a4e686276ac96644b24fd Mon Sep 17 00:00:00 2001
From: Phil Estival <pe@7d.nz>
Date: Fri, 17 Jan 2025 01:40:47 +0100
Subject: [PATCH 06/11] ob-sql: add support for sessions in
 `org-babel-execute:sql'
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

* lisp/ob-sql.el: regular code block execution can also be run through
the same path as sessions — e.g. functions of sql.el and a comint
buffer — when the custom predicate `org-sql-run-comint-p' is set, but
this is slower.
---
 lisp/ob-sql.el | 47 +++++++++++++++++++++++++++++++++++++++++++++--
 1 file changed, 45 insertions(+), 2 deletions(-)

diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 976f673ee..ca9634a43 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -294,7 +294,7 @@ database connections."
                          (cdr (assoc-string dbconnection sql-connection-alist t))))))))

 (defun org-babel-execute:sql (body params)
-  "Execute a block of Sql code with Babel.
+  "Execute SQL BODY with PARAMS.
 This function is called by `org-babel-execute-src-block'."
   (let* ((result-params (cdr (assq :result-params params)))
          (cmdline (cdr (assq :cmdline params)))
@@ -305,11 +305,52 @@ This function is called by `org-babel-execute-src-block'."
          (dbinstance (org-babel-find-db-connection-param params :dbinstance))
          (database   (org-babel-find-db-connection-param params :database))
          (engine (cdr (assq :engine params)))
+         (in-engine  (intern (or engine (user-error "Missing :engine"))))
          (colnames-p (not (equal "no" (cdr (assq :colnames params)))))
          (in-file (org-babel-temp-file "sql-in-"))
          (out-file (or (cdr (assq :out-file params))
                        (org-babel-temp-file "sql-out-")))
 	 (header-delim "")
+         (session (cdr (assoc :session params)))
+         (session-p (not (string= session "none"))))
+
+	 (if (or session-p org-sql-run-comint-p) ; run through comint
+	     (let ((sql--buffer
+		    (org-babel-sql-session-connect in-engine params session)))
+	       (with-current-buffer (get-buffer-create "*ob-sql-result*")
+		 (erase-buffer))
+	       (setq org-sql-session-start-time (current-time))
+	       (setq org-sql-session-command-terminated nil)
+
+	       (with-current-buffer (get-buffer sql--buffer)
+		 (process-send-string (current-buffer)
+				      (org-sql-session-format-query
+                                       (org-babel-expand-body:sql body params)
+                                       in-engine))
+		 (while (or (not org-sql-session-command-terminated)
+                            (> (time-to-seconds
+                                (time-subtract (current-time)
+                                               org-sql-session-start-time))
+                               org-sql-timeout))
+		   (sleep-for 0.03))
+		 ;; command finished, remove filter
+		 (set-process-filter (get-buffer-process sql--buffer) nil)
+
+		 (when (not session-p)
+		   (comint-quit-subjob)
+		   ;; despite this quit signal, the process may not be finished yet
+		   (let ((kill-buffer-query-functions nil))
+		     (kill-this-buffer))))
+
+	       (with-current-buffer (get-buffer-create "*ob-sql-result*")
+		 (goto-char (point-min))
+		 ;; clear the output or prompt and termination
+		 (let ((clean-output (plist-get org-sql-session-clean-output in-engine)))
+		   (while (re-search-forward clean-output nil t)
+		     (replace-match "")))
+		 (write-file out-file)))
+
+	   (let ( ; else run one command line
          (command (cl-case (intern engine)
                     (dbi (format "dbish --batch %s < %s | sed '%s' > %s"
 				 (or cmdline "")
@@ -405,7 +446,7 @@ SET COLSEP '|'
        (org-babel-expand-body:sql body params)
        ;; "sqsh" requires "go" inserted at EOF.
        (if (string= engine "sqsh") "\ngo" "")))
-    (org-babel-eval command "")
+    (org-babel-eval command "")))
     (org-babel-result-cond result-params
       (with-temp-buffer
 	(progn (insert-file-contents-literally out-file) (buffer-string)))
@@ -435,6 +476,8 @@ SET COLSEP '|'
 	      (forward-char -1))
 	    (write-file out-file))))
 	(org-table-import out-file (if (string= engine "sqsh") '(4) '(16)))
+        (when org-sql-close-out-temp-buffer-p
+	  (kill-buffer (get-file-buffer out-file)))
 	(org-babel-reassemble-table
 	 (mapcar (lambda (x)
 		   (if (string= (car x) header-delim)
--
2.39.5

[-- Attachment #9: 0007-ob-sql-add-support-for-sessions-in-org-babel-execute.patch --]
[-- Type: text/x-patch, Size: 4128 bytes --]

From aa31dd3218fbf6176c996478ac20650db1d4477c Mon Sep 17 00:00:00 2001
From: Phil Estival <pe@7d.nz>
Date: Fri, 17 Jan 2025 03:46:09 +0100
Subject: [PATCH 07/11] ob-sql: add support for sessions in
 `org-babel-execute:sql'
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

* lisp/ob-sql.el: regular code block execution can also be run through
the same path as sessions — with functions from sql.el and a comint
buffer — when the custom predicate `org-sql-run-comint-p' is set, but
this is slower.
---
 lisp/ob-sql.el | 46 ++++++++++++++++++++++++++++++++++++++++++++--
 1 file changed, 44 insertions(+), 2 deletions(-)

diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 976f673ee..d407cd7d3 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -294,7 +294,7 @@ database connections."
                          (cdr (assoc-string dbconnection sql-connection-alist t))))))))

 (defun org-babel-execute:sql (body params)
-  "Execute a block of Sql code with Babel.
+  "Execute SQL BODY with PARAMS.
 This function is called by `org-babel-execute-src-block'."
   (let* ((result-params (cdr (assq :result-params params)))
          (cmdline (cdr (assq :cmdline params)))
@@ -305,11 +305,53 @@ This function is called by `org-babel-execute-src-block'."
          (dbinstance (org-babel-find-db-connection-param params :dbinstance))
          (database   (org-babel-find-db-connection-param params :database))
          (engine (cdr (assq :engine params)))
+         (in-engine  (intern (or engine (user-error "Missing :engine"))))
          (colnames-p (not (equal "no" (cdr (assq :colnames params)))))
          (in-file (org-babel-temp-file "sql-in-"))
          (out-file (or (cdr (assq :out-file params))
                        (org-babel-temp-file "sql-out-")))
 	 (header-delim "")
+         (session (cdr (assoc :session params)))
+         (session-p (not (string= session "none"))))
+
+    (if (or session-p org-sql-run-comint-p) ; run through comint
+        (let ((sql--buffer
+	       (org-babel-sql-session-connect in-engine params session)))
+	  (with-current-buffer (get-buffer-create "*ob-sql-result*")
+	    (erase-buffer))
+	  (setq org-sql-session-start-time (current-time))
+	  (setq org-sql-session-command-terminated nil)
+
+	  (with-current-buffer (get-buffer sql--buffer)
+            ;;(message "%s" (org-babel-expand-body:sql body params))
+	    (process-send-string (current-buffer)
+				 (org-sql-session-format-query
+                                  (org-babel-expand-body:sql body params)
+                                  in-engine))
+	    (while (or (not org-sql-session-command-terminated)
+                       (> (time-to-seconds
+                           (time-subtract (current-time)
+                                          org-sql-session-start-time))
+                          org-sql-timeout))
+	      (sleep-for 0.03))
+	    ;; command finished, remove filter
+	    (set-process-filter (get-buffer-process sql--buffer) nil)
+
+	    (when (not session-p)
+	      (comint-quit-subjob)
+	      ;; despite this quit signal, the process may not be finished yet
+	      (let ((kill-buffer-query-functions nil))
+		(kill-this-buffer))))
+
+	  (with-current-buffer (get-buffer "*ob-sql-result*")
+	    (goto-char (point-min))
+	    ;; clear the output or prompt and termination
+	    (let ((clean-output (plist-get org-sql-session-clean-output in-engine)))
+	      (while (re-search-forward clean-output nil t)
+		(replace-match "")))
+	    (write-file out-file)))
+
+      (let ( ; else run a shell command
          (command (cl-case (intern engine)
                     (dbi (format "dbish --batch %s < %s | sed '%s' > %s"
 				 (or cmdline "")
@@ -405,7 +447,7 @@ SET COLSEP '|'
        (org-babel-expand-body:sql body params)
        ;; "sqsh" requires "go" inserted at EOF.
        (if (string= engine "sqsh") "\ngo" "")))
-    (org-babel-eval command "")
+    (org-babel-eval command "")))
     (org-babel-result-cond result-params
       (with-temp-buffer
 	(progn (insert-file-contents-literally out-file) (buffer-string)))
--
2.39.5

[-- Attachment #10: 0008-ob-sql-Fix-org-babel-expand-body-returning-extra-n.patch --]
[-- Type: text/x-patch, Size: 960 bytes --]

From c0a20d29bdde5c2abc0debb44bdd2e3c64d83043 Mon Sep 17 00:00:00 2001
From: Phil Estival <pe@7d.nz>
Date: Fri, 17 Jan 2025 03:48:04 +0100
Subject: [PATCH 08/11] ob-sql: Fix `org-babel-expand-body' returning extra \n

* lisp/ob-sql.el: empty prologue or epilogue or expanded variables
are no longer replaced by a newline.
---
 lisp/ob-sql.el | 4 ++--
 1 file changed, 2 insertions(+), 2 deletions(-)

diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index d407cd7d3..f9a987435 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -150,11 +150,11 @@
   (let ((prologue (cdr (assq :prologue params)))
 	(epilogue (cdr (assq :epilogue params))))
     (mapconcat 'identity
-               (list
+     (delq nil (list
                 prologue
                 (org-babel-sql-expand-vars
                  body (org-babel--get-vars params))
-                epilogue)
+                epilogue))
                "\n")))

 (defun org-babel-edit-prep:sql (info)
--
2.39.5

[-- Attachment #11: 0009-ob-sql-replace-call-to-intern-engine-by-the-previous.patch --]
[-- Type: text/x-patch, Size: 1624 bytes --]

From 6b32e43c3784cb918e32a9a1486bad7b8c153b5d Mon Sep 17 00:00:00 2001
From: Phil Estival <pe@7d.nz>
Date: Fri, 17 Jan 2025 04:41:34 +0100
Subject: [PATCH 09/11] ob-sql: replace call to (intern engine) by the
 previously declared in-engine

TINYCHANGE
---
 lisp/ob-sql.el | 6 +++---
 1 file changed, 3 insertions(+), 3 deletions(-)

diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index f9a987435..46f10c8e9 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -352,7 +352,7 @@ This function is called by `org-babel-execute-src-block'."
 	    (write-file out-file)))

       (let ( ; else run a shell command
-         (command (cl-case (intern engine)
+         (command (cl-case in-engine
                     (dbi (format "dbish --batch %s < %s | sed '%s' > %s"
 				 (or cmdline "")
 				 (org-babel-process-file-name in-file)
@@ -423,7 +423,7 @@ footer=off -F \"\t\"  %s -f %s -o %s %s"
                     (t (user-error "No support for the %s SQL engine" engine)))))
     (with-temp-file in-file
       (insert
-       (pcase (intern engine)
+       (pcase in-engine
 	 (`dbi "/format partbox\n")
          (`oracle "SET PAGESIZE 50000
 SET NEWPAGE 0
@@ -453,7 +453,7 @@ SET COLSEP '|'
 	(progn (insert-file-contents-literally out-file) (buffer-string)))
       (with-temp-buffer
 	(cond
-	 ((memq (intern engine) '(dbi mysql postgresql postgres saphana sqsh vertica))
+	 ((memq in-engine '(dbi mysql postgresql postgres saphana sqsh vertica))
 	  ;; Add header row delimiter after column-names header in first line
 	  (when colnames-p (with-temp-buffer
 	                     (insert-file-contents out-file)
--
2.39.5

[-- Attachment #12: 0010-ob-sql-restore-support-for-sqlite.patch --]
[-- Type: text/x-patch, Size: 894 bytes --]

From 15be8212d343d5775e9cbb5b8cf7644340fdbb15 Mon Sep 17 00:00:00 2001
From: Phil Estival <pe@7d.nz>
Date: Fri, 17 Jan 2025 04:52:42 +0100
Subject: [PATCH 10/11] ob-sql: restore support for sqlite

---
 lisp/ob-sql.el | 3 +++
 1 file changed, 3 insertions(+)

diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 46f10c8e9..0489e466f 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -358,6 +358,9 @@ This function is called by `org-babel-execute-src-block'."
 				 (org-babel-process-file-name in-file)
 				 "/^+/d;s/^|//;s/(NULL)/ /g;$d"
 				 (org-babel-process-file-name out-file)))
+                    (sqlite (format "sqlite3 < %s > %s"
+				    (org-babel-process-file-name in-file)
+				    (org-babel-process-file-name out-file)))
                     (monetdb (format "mclient -f tab %s < %s > %s"
 				     (or cmdline "")
 				     (org-babel-process-file-name in-file)
--
2.39.5

[-- Attachment #13: 0011-ob-sql-update-commentary-reflecting-recent-changes-o.patch --]
[-- Type: text/x-patch, Size: 1426 bytes --]

From dbd1c288db4cddf37cc5ba85722ef999bc206c2e Mon Sep 17 00:00:00 2001
From: Phil Estival <pe@7d.nz>
Date: Fri, 17 Jan 2025 05:26:50 +0100
Subject: [PATCH 11/11] ob-sql: update commentary reflecting recent changes on
 session

* lisp/ob-sql.el: mention support for session in commentary.  Sqlite
engine support.  Current limitations in sessions: only sqlite and
postgres are configured yet. Error line number are reported as LINE 1.
Default port number only.
---
 lisp/ob-sql.el | 15 ++++++++++++---
 1 file changed, 12 insertions(+), 3 deletions(-)

diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 0489e466f..d4448d0d7 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -37,6 +37,7 @@
 ;; Header args used:
 ;; - engine
 ;; - cmdline
+;; - session
 ;; - dbhost
 ;; - dbport
 ;; - dbuser
@@ -54,20 +55,28 @@
 ;; - rowname-names
 ;;
 ;; Engines supported:
-;; - mysql
+;; - mysql/mariadb
 ;; - dbi
 ;; - mssql
 ;; - sqsh
 ;; - postgresql (postgres)
+;; - sqlite
 ;; - oracle
 ;; - vertica
 ;; - saphana
 ;;
+;; Limitation:
+;; - sessions:
+;;   - engines configured: sqlite, postgres
+;;   - no error line number (stays as LINE 1)
+;;   - default port number only
+;;
 ;; TODO:
 ;;
-;; - support for sessions
 ;; - support for more engines
-;; - what's a reasonable way to drop table data into SQL?
+;; - babel tables as input
+;; - raw replace result
+;; - port number configuration for sessions
 ;;

 ;;; Code:
--
2.39.5